1  /**
  2   * Tests for analyzePerformance cron handler
  3   * Verifies detection of missing indexes, fragmentation, and human_review_queue integration
  4   */
  5  
  6  import { test } from 'node:test';
  7  import assert from 'node:assert';
  8  import Database from 'better-sqlite3';
  9  import { createPgMock } from '../helpers/pg-mock.js'; // eslint-disable-line no-unused-vars
 10  
 11  /**
 12   * Create a minimal in-memory DB with the tables analyzePerformance queries
 13   */
 14  function createTestDb() {
 15    const db = new Database(':memory:');
 16  
 17    db.exec(`
 18      CREATE TABLE sites (
 19        id INTEGER PRIMARY KEY AUTOINCREMENT,
 20        domain TEXT, landing_page_url TEXT, keyword TEXT,
 21        screenshot_path TEXT, status TEXT DEFAULT 'found',
 22        error_message TEXT, score REAL, grade TEXT,
 23        country_code TEXT, recapture_at DATETIME,
 24        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 25        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 26        rescored_at DATETIME
 27      );
 28  
 29      CREATE TABLE messages (
 30        id INTEGER PRIMARY KEY AUTOINCREMENT,
 31        site_id INTEGER,
 32        direction TEXT NOT NULL DEFAULT 'outbound' CHECK(direction IN ('inbound', 'outbound')),
 33        message_body TEXT,
 34        contact_method TEXT, contact_uri TEXT,
 35        approval_status TEXT DEFAULT 'pending',
 36        delivery_status TEXT,
 37        read_at TEXT,
 38        sent_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 39        message_type TEXT DEFAULT 'outreach',
 40        raw_payload TEXT
 41      );
 42  CREATE TABLE llm_usage (
 43        id INTEGER PRIMARY KEY AUTOINCREMENT,
 44        site_id INTEGER, stage TEXT, provider TEXT, model TEXT,
 45        prompt_tokens INTEGER, completion_tokens INTEGER, total_tokens INTEGER,
 46        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
 47      );
 48  
 49      CREATE TABLE cron_job_logs (
 50        id INTEGER PRIMARY KEY AUTOINCREMENT,
 51        job_name TEXT, started_at DATETIME, status TEXT,
 52        summary TEXT, full_log TEXT, items_processed INTEGER DEFAULT 0,
 53        items_failed INTEGER DEFAULT 0, error_message TEXT,
 54        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
 55      );
 56  
 57      CREATE TABLE config (
 58        key TEXT PRIMARY KEY, value TEXT, description TEXT,
 59        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
 60      );
 61  
 62      CREATE TABLE keywords (
 63        id INTEGER PRIMARY KEY AUTOINCREMENT,
 64        keyword TEXT, country_code TEXT, status TEXT DEFAULT 'active',
 65        search_volume INTEGER DEFAULT 0
 66      );
 67    `);
 68  
 69    return db;
 70  }
 71  
 72  /**
 73   * Simulate analyzePerformance logic against a given db
 74   * (We test the logic directly rather than importing cron which has many deps)
 75   */
 76  function analyzePerformance(db) {
 77    // Ensure human_review_queue table exists
 78    db.exec(`
 79      CREATE TABLE IF NOT EXISTS human_review_queue (
 80        id INTEGER PRIMARY KEY AUTOINCREMENT,
 81        file TEXT, reason TEXT, type TEXT,
 82        priority TEXT DEFAULT 'medium',
 83        status TEXT DEFAULT 'pending',
 84        created_at TEXT DEFAULT (datetime('now')),
 85        reviewed_at TEXT, reviewed_by TEXT, notes TEXT
 86      )
 87    `);
 88  
 89    // Gather tables
 90    const tables = db
 91      .prepare("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")
 92      .all()
 93      .map(t => t.name);
 94  
 95    const tableStats = [];
 96    for (const table of tables) {
 97      try {
 98        const row = db.prepare(`SELECT COUNT(*) as rows FROM "${table}"`).get();
 99        tableStats.push({ table_name: table, rows: row.rows });
100      } catch {
101        // skip
102      }
103    }
104  
105    // Gather indexes
106    const indexStats = db
107      .prepare("SELECT name, tbl_name, sql FROM sqlite_master WHERE type='index' AND sql IS NOT NULL")
108      .all();
109  
110    // Build indexed column map
111    const indexedColumns = {};
112    for (const idx of indexStats) {
113      if (!indexedColumns[idx.tbl_name]) indexedColumns[idx.tbl_name] = [];
114      const colMatch = idx.sql.match(/\(([^)]+)\)/);
115      if (colMatch) {
116        const cols = colMatch[1]
117          .split(',')
118          .map(c => c.trim().replace(/ (ASC|DESC|COLLATE \w+)/gi, ''));
119        indexedColumns[idx.tbl_name].push(cols);
120      }
121    }
122  
123    const recommendations = [];
124  
125    const commonQueries = [
126      {
127        sql: "SELECT * FROM sites WHERE status = 'prog_scored' AND error_message IS NOT NULL LIMIT 1",
128        table: 'sites',
129        cols: ['status', 'error_message'],
130        desc: 'Retry failed scoring',
131      },
132      {
133        sql: "SELECT * FROM messages WHERE direction = 'inbound' AND read_at IS NULL LIMIT 1",
134        table: 'messages',
135        cols: ['direction', 'read_at'],
136        desc: 'Unread inbound conversations',
137      },
138    ];
139  
140    for (const { sql, table, cols, desc } of commonQueries) {
141      try {
142        const plan = db.prepare(`EXPLAIN QUERY PLAN ${sql}`).all();
143        const scanRow = plan.find(r => r.detail && r.detail.match(/\bSCAN\b/));
144  
145        if (scanRow) {
146          const tableIndexes = indexedColumns[table] || [];
147          const alreadyCovered = tableIndexes.some(idxCols => {
148            return cols.every((c, i) => idxCols[i] && idxCols[i].toLowerCase() === c.toLowerCase());
149          });
150  
151          if (!alreadyCovered) {
152            const idxName = `idx_${table}_${cols.join('_')}`;
153            const idxSql = `CREATE INDEX IF NOT EXISTS ${idxName} ON ${table}(${cols.join(', ')});`;
154            recommendations.push({
155              file: `${table}:${cols.join(',')}`,
156              priority: 'medium',
157              reason: `Table scan on ${table} for: ${desc}\nSuggested: ${idxSql}`,
158            });
159          }
160        }
161      } catch {
162        // skip
163      }
164    }
165  
166    // Store in queue (deduplicated)
167    let newCount = 0;
168    const insertStmt = db.prepare(
169      "INSERT INTO human_review_queue (file, reason, type, priority) VALUES (?, ?, 'performance', ?)"
170    );
171    const checkStmt = db.prepare(
172      "SELECT COUNT(*) as count FROM human_review_queue WHERE type = 'performance' AND file = ? AND status = 'pending'"
173    );
174  
175    for (const rec of recommendations) {
176      const existing = checkStmt.get(rec.file);
177      if (existing.count === 0) {
178        insertStmt.run(rec.file, rec.reason, rec.priority);
179        newCount++;
180      }
181    }
182  
183    return { tableStats, indexStats, recommendations, newCount };
184  }
185  
186  test('analyzePerformance detects table scans on unindexed columns', () => {
187    const db = createTestDb();
188  
189    // No indexes on sites or conversations - should detect table scans
190    const result = analyzePerformance(db);
191  
192    // Should find recommendations for missing indexes
193    assert.ok(
194      result.recommendations.length > 0,
195      'Should find at least one missing index recommendation'
196    );
197  
198    const siteRec = result.recommendations.find(r => r.file.includes('sites:'));
199    assert.ok(siteRec, 'Should recommend index on sites table');
200    assert.ok(siteRec.reason.includes('Table scan'), 'Reason should mention table scan');
201    assert.ok(
202      siteRec.reason.includes('CREATE INDEX'),
203      'Reason should include CREATE INDEX suggestion'
204    );
205  
206    db.close();
207  });
208  
209  test('analyzePerformance does not flag columns that already have indexes', () => {
210    const db = createTestDb();
211  
212    // Add the indexes that would be recommended
213    db.exec(`
214      CREATE INDEX idx_sites_status ON sites(status);
215      CREATE INDEX idx_sites_status_error_message ON sites(status, error_message);
216      CREATE INDEX idx_messages_direction ON messages(direction);
217      CREATE INDEX idx_messages_direction_read_at ON messages(direction, read_at);
218    `);
219  
220    const result = analyzePerformance(db);
221  
222    // Should NOT find recommendations for already-indexed columns
223    const siteRec = result.recommendations.find(r => r.file === 'sites:status,error_message');
224    assert.strictEqual(siteRec, undefined, 'Should not recommend index that already exists');
225  
226    const convRec = result.recommendations.find(r => r.file === 'messages:direction,read_at');
227    assert.strictEqual(convRec, undefined, 'Should not recommend index that already exists');
228  
229    db.close();
230  });
231  
232  test('analyzePerformance stores recommendations in human_review_queue', () => {
233    const db = createTestDb();
234  
235    const result = analyzePerformance(db);
236  
237    // Check queue
238    const queueItems = db
239      .prepare("SELECT * FROM human_review_queue WHERE type = 'performance'")
240      .all();
241  
242    assert.strictEqual(
243      queueItems.length,
244      result.newCount,
245      'Queue count should match new recommendations'
246    );
247    assert.ok(queueItems.length > 0, 'Should have items in queue');
248  
249    for (const item of queueItems) {
250      assert.strictEqual(item.type, 'performance');
251      assert.strictEqual(item.status, 'pending');
252      assert.ok(item.file, 'Should have a file identifier');
253      assert.ok(item.reason, 'Should have a reason');
254    }
255  
256    db.close();
257  });
258  
259  test('analyzePerformance deduplicates recommendations on repeated runs', () => {
260    const db = createTestDb();
261  
262    // Run twice
263    const result1 = analyzePerformance(db);
264    const result2 = analyzePerformance(db);
265  
266    // Second run should find 0 new recommendations
267    assert.strictEqual(result2.newCount, 0, 'Second run should not add duplicate recommendations');
268  
269    // Queue should still have the same count as first run
270    const queueItems = db
271      .prepare("SELECT * FROM human_review_queue WHERE type = 'performance' AND status = 'pending'")
272      .all();
273    assert.strictEqual(queueItems.length, result1.newCount, 'Queue should not grow on repeated runs');
274  
275    db.close();
276  });
277  
278  test('analyzePerformance creates new recommendations after previous ones are reviewed', () => {
279    const db = createTestDb();
280  
281    // First run
282    const result1 = analyzePerformance(db);
283    assert.ok(result1.newCount > 0, 'Should find recommendations');
284  
285    // Mark all as reviewed
286    db.prepare("UPDATE human_review_queue SET status = 'reviewed' WHERE type = 'performance'").run();
287  
288    // Run again - should create new recommendations since old ones are reviewed
289    const result2 = analyzePerformance(db);
290    assert.ok(
291      result2.newCount > 0,
292      'Should create new recommendations after previous ones are reviewed'
293    );
294  
295    db.close();
296  });
297  
298  test('analyzePerformance counts tables correctly', () => {
299    const db = createTestDb();
300  
301    const result = analyzePerformance(db);
302  
303    // Should count all tables we created
304    assert.ok(result.tableStats.length >= 7, 'Should analyze at least 7 tables');
305  
306    const siteStats = result.tableStats.find(t => t.table_name === 'sites');
307    assert.ok(siteStats, 'Should include sites table');
308    assert.strictEqual(siteStats.rows, 0, 'Empty table should have 0 rows');
309  
310    db.close();
311  });
312  
313  test('analyzePerformance counts indexes correctly', () => {
314    const db = createTestDb();
315  
316    // Add some indexes
317    db.exec(`
318      CREATE INDEX idx_sites_status ON sites(status);
319      CREATE INDEX idx_sites_domain ON sites(domain);
320    `);
321  
322    const result = analyzePerformance(db);
323  
324    assert.ok(result.indexStats.length >= 2, 'Should count at least 2 user-defined indexes');
325  
326    db.close();
327  });