1  /**
  2   * Human Review Queue Manager
  3   *
  4   * Central system for tracking items that need human review:
  5   * - Architectural decisions
  6   * - Breaking changes
  7   * - Critical security issues
  8   * - Complex refactoring decisions
  9   *
 10   * Used by automated maintenance scripts to flag items for human attention.
 11   */
 12  
 13  import { run, getOne, getAll } from './db.js';
 14  
 15  /**
 16   * Initialize human review queue table
 17   */
 18  export async function initializeQueue() {
 19    await run(`
 20      CREATE TABLE IF NOT EXISTS human_review_queue (
 21        id SERIAL PRIMARY KEY,
 22        file TEXT,
 23        reason TEXT,
 24        type TEXT,
 25        priority TEXT DEFAULT 'medium',
 26        status TEXT DEFAULT 'pending',
 27        created_at TIMESTAMPTZ DEFAULT NOW(),
 28        reviewed_at TIMESTAMPTZ,
 29        reviewed_by TEXT,
 30        notes TEXT
 31      )
 32    `);
 33  }
 34  
 35  /**
 36   * Add item to review queue
 37   *
 38   * If a pending item with the same file+type already exists, it will be updated
 39   * with the new reason and timestamp (preventing duplicates).
 40   *
 41   * @param {Object} item
 42   * @param {string} item.file - File or module affected
 43   * @param {string} item.reason - Why human review is needed
 44   * @param {string} item.type - Type: 'architecture', 'breaking_change', 'security', 'documentation', 'test'
 45   * @param {string} [item.priority] - Priority: 'critical', 'high', 'medium', 'low'
 46   */
 47  export async function addReviewItem(item) {
 48    await initializeQueue();
 49  
 50    // Check if a pending item with same file+type already exists
 51    const existing = await getOne(
 52      `SELECT id FROM human_review_queue
 53       WHERE file = $1 AND type = $2 AND status = 'pending'`,
 54      [item.file, item.type]
 55    );
 56  
 57    if (existing) {
 58      // Update existing pending item with new reason and timestamp
 59      await run(
 60        `UPDATE human_review_queue
 61         SET reason = $1,
 62             priority = $2,
 63             created_at = NOW()
 64         WHERE id = $3`,
 65        [item.reason, item.priority || 'medium', existing.id]
 66      );
 67    } else {
 68      // Insert new item
 69      await run(
 70        `INSERT INTO human_review_queue (file, reason, type, priority)
 71         VALUES ($1, $2, $3, $4)`,
 72        [item.file, item.reason, item.type, item.priority || 'medium']
 73      );
 74    }
 75  }
 76  
 77  /**
 78   * Add multiple items to review queue
 79   *
 80   * Deduplicates by file+type for pending items (updates existing instead of creating duplicates).
 81   */
 82  export async function addReviewItems(items) {
 83    await initializeQueue();
 84  
 85    for (const item of items) {
 86      const existing = await getOne(
 87        `SELECT id FROM human_review_queue
 88         WHERE file = $1 AND type = $2 AND status = 'pending'`,
 89        [item.file, item.type]
 90      );
 91  
 92      if (existing) {
 93        // Update existing pending item
 94        await run(
 95          `UPDATE human_review_queue
 96           SET reason = $1,
 97               priority = $2,
 98               created_at = NOW()
 99           WHERE id = $3`,
100          [item.reason, item.priority || 'medium', existing.id]
101        );
102      } else {
103        // Insert new item
104        await run(
105          `INSERT INTO human_review_queue (file, reason, type, priority)
106           VALUES ($1, $2, $3, $4)`,
107          [item.file, item.reason, item.type, item.priority || 'medium']
108        );
109      }
110    }
111  }
112  
113  /**
114   * Get pending review items
115   */
116  export async function getPendingReviews(options = {}) {
117    const { type, priority, limit } = options;
118    await initializeQueue();
119  
120    let sql = `SELECT * FROM human_review_queue WHERE status = 'pending'`;
121    const params = [];
122    let idx = 1;
123  
124    if (type) {
125      sql += ` AND type = $${idx++}`;
126      params.push(type);
127    }
128  
129    if (priority) {
130      sql += ` AND priority = $${idx++}`;
131      params.push(priority);
132    }
133  
134    sql += ` ORDER BY
135      CASE priority
136        WHEN 'critical' THEN 1
137        WHEN 'high' THEN 2
138        WHEN 'medium' THEN 3
139        WHEN 'low' THEN 4
140      END,
141      created_at ASC`;
142  
143    if (limit) {
144      sql += ` LIMIT $${idx++}`;
145      params.push(limit);
146    }
147  
148    return await getAll(sql, params);
149  }
150  
151  /**
152   * Mark item as reviewed
153   */
154  export async function markReviewed(id, reviewedBy, notes = null) {
155    await run(
156      `UPDATE human_review_queue
157       SET status = 'reviewed',
158           reviewed_at = NOW(),
159           reviewed_by = $1,
160           notes = $2
161       WHERE id = $3`,
162      [reviewedBy, notes, id]
163    );
164  }
165  
166  /**
167   * Mark item as dismissed (not needed)
168   */
169  export async function dismissReview(id, reason = null) {
170    await run(
171      `UPDATE human_review_queue
172       SET status = 'dismissed',
173           reviewed_at = NOW(),
174           notes = $1
175       WHERE id = $2`,
176      [reason, id]
177    );
178  }
179  
180  /**
181   * Get review queue statistics
182   */
183  export async function getQueueStats() {
184    await initializeQueue();
185  
186    return await getOne(
187      `SELECT
188        COUNT(*) as total,
189        SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending,
190        SUM(CASE WHEN status = 'reviewed' THEN 1 ELSE 0 END) as reviewed,
191        SUM(CASE WHEN priority = 'critical' AND status = 'pending' THEN 1 ELSE 0 END) as critical,
192        SUM(CASE WHEN priority = 'high' AND status = 'pending' THEN 1 ELSE 0 END) as high
193       FROM human_review_queue`
194    );
195  }
196  
197  /**
198   * Clear old reviewed items (older than 30 days)
199   */
200  export async function cleanupReviewedItems() {
201    const result = await run(
202      `DELETE FROM human_review_queue
203       WHERE status IN ('reviewed', 'dismissed')
204         AND reviewed_at < NOW() - INTERVAL '30 days'`
205    );
206    return result.changes;
207  }