1  /**
  2   * Tests for src/cron/precompute-dashboard.js
  3   *
  4   * precomputeDashboard() runs all cache-building queries and stores results
  5   * in the dashboard_cache table. Tests use an in-memory SQLite with the full schema
  6   * via pg-mock so writes are captured in the same db.
  7   */
  8  
  9  import { test, describe, mock } from 'node:test';
 10  import assert from 'node:assert/strict';
 11  import Database from 'better-sqlite3';
 12  import { join } from 'path';
 13  import { readFileSync } from 'fs';
 14  import { createPgMock } from '../helpers/pg-mock.js';
 15  
 16  // Create in-memory DB with full schema
 17  const db = new Database(':memory:');
 18  const schemaPath = join(import.meta.dirname, '..', '..', 'db', 'schema.sql');
 19  const schema = readFileSync(schemaPath, 'utf-8');
 20  // schema.sql may have PG-specific syntax; strip it down to just table definitions
 21  // that SQLite can handle. If exec fails on the full schema, try just creating core tables.
 22  try {
 23    db.exec(schema);
 24  } catch {
 25    // If full schema fails (PG-specific DDL), create minimal tables needed
 26    db.exec(`
 27      CREATE TABLE IF NOT EXISTS sites (id INTEGER PRIMARY KEY, domain TEXT, status TEXT, score REAL, grade TEXT, country_code TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME, rescored_at DATETIME, keyword TEXT, landing_page_url TEXT);
 28      CREATE TABLE IF NOT EXISTS messages (id INTEGER PRIMARY KEY, site_id INTEGER, contact_method TEXT, delivery_status TEXT, approval_status TEXT, sent_at DATETIME, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, direction TEXT DEFAULT 'outbound', message_type TEXT DEFAULT 'outreach');
 29      CREATE TABLE IF NOT EXISTS cron_jobs (id INTEGER PRIMARY KEY, task_key TEXT UNIQUE, interval_value INTEGER, interval_unit TEXT, last_run_at DATETIME, enabled INTEGER DEFAULT 1, status TEXT DEFAULT 'idle');
 30      CREATE TABLE IF NOT EXISTS cron_job_logs (id INTEGER PRIMARY KEY, task_key TEXT, status TEXT, duration_ms INTEGER, summary TEXT, metrics TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP);
 31      CREATE TABLE IF NOT EXISTS llm_usage (id INTEGER PRIMARY KEY, stage TEXT, tokens_in INTEGER DEFAULT 0, tokens_out INTEGER DEFAULT 0, cost_usd REAL DEFAULT 0, model TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP);
 32      CREATE TABLE IF NOT EXISTS agent_tasks (id INTEGER PRIMARY KEY, assigned_to TEXT, status TEXT, task_type TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP);
 33      CREATE TABLE IF NOT EXISTS dashboard_cache (cache_key TEXT PRIMARY KEY, cache_value TEXT, computed_at DATETIME DEFAULT CURRENT_TIMESTAMP, expires_at DATETIME);
 34    `);
 35  }
 36  
 37  // Ensure dashboard_cache exists (may not be in older schema)
 38  try {
 39    db.exec(`CREATE TABLE IF NOT EXISTS dashboard_cache (cache_key TEXT PRIMARY KEY, cache_value TEXT, computed_at DATETIME DEFAULT CURRENT_TIMESTAMP, expires_at DATETIME)`);
 40  } catch { /* already exists */ }
 41  
 42  // Custom db.js mock that handles PG-specific queries
 43  // precompute-dashboard.js uses many PG-only SQL constructs:
 44  //   - pg_database_size(current_database()), pg_tables
 45  //   - date_trunc(), to_char(), EXTRACT(EPOCH FROM AVG(...))
 46  //   - ::date, ::numeric casts (stripped by pg-mock, but date_trunc/to_char still fail)
 47  // Strategy: intercept any query with unsupported PG syntax and return empty/default values.
 48  // The dashboard_cache upserts go through run() which does NOT contain PG-specific syntax,
 49  // so cache writes still succeed.
 50  
 51  function hasPgOnlySyntax(sql) {
 52    return (
 53      sql.includes('pg_database_size') ||
 54      sql.includes('current_database()') ||
 55      sql.includes('pg_tables') ||
 56      sql.includes('information_schema') ||
 57      sql.includes('date_trunc') ||
 58      sql.includes('to_char') ||
 59      // EXTRACT(EPOCH FROM avg/sum/col) — not a simple column difference
 60      /EXTRACT\s*\(\s*EPOCH\s+FROM\s+(?:AVG|SUM|MIN|MAX)\s*\(/i.test(sql) ||
 61      // ROUND(...::numeric ...) — can appear after :: cast strip still leaves ROUND issues
 62      /ROUND\s*\([^)]*::numeric/i.test(sql)
 63    );
 64  }
 65  
 66  const pgMock = createPgMock(db);
 67  const origGetOne = pgMock.getOne;
 68  const origGetAll = pgMock.getAll;
 69  mock.module('../../src/utils/db.js', {
 70    namedExports: {
 71      ...pgMock,
 72      getOne: async (sql, params = []) => {
 73        if (hasPgOnlySyntax(sql)) {
 74          // Return sensible defaults so callers don't crash on null access
 75          if (sql.includes('pg_database_size')) return { size_bytes: 1024 * 1024 * 50 };
 76          if (sql.includes('avg_cost') || sql.includes('daily_cost')) return { avg_cost: 0 };
 77          if (sql.includes('avg_hours') || sql.includes('avg_duration')) return null;
 78          if (sql.includes('expires_at')) return null; // cost_forecast cache check
 79          return null;
 80        }
 81        return origGetOne(sql, params);
 82      },
 83      getAll: async (sql, params = []) => {
 84        if (hasPgOnlySyntax(sql)) {
 85          return [];
 86        }
 87        return origGetAll(sql, params);
 88      },
 89    },
 90  });
 91  
 92  const { precomputeDashboard } = await import('../../src/cron/precompute-dashboard.js');
 93  
 94  describe('precomputeDashboard', () => {
 95    test('runs without error on empty database', async () => {
 96      await assert.doesNotReject(precomputeDashboard());
 97    });
 98  
 99    test('populates dashboard_cache table with keys', async () => {
100      await precomputeDashboard();
101  
102      const rows = db.prepare('SELECT cache_key FROM dashboard_cache').all();
103  
104      assert.ok(rows.length > 0, 'should have cached some metrics');
105      const keys = rows.map(r => r.cache_key);
106      assert.ok(
107        keys.some(
108          k =>
109            k.includes('pipeline') || k.includes('funnel') || k.includes('llm') || k.includes('cron')
110        ),
111        `should have at least one recognizable key, got: ${keys.slice(0, 5).join(', ')}`
112      );
113    });
114  
115    test('cache values are valid JSON', async () => {
116      await precomputeDashboard();
117  
118      const rows = db.prepare('SELECT cache_key, cache_value FROM dashboard_cache').all();
119  
120      for (const row of rows) {
121        assert.doesNotThrow(
122          () => JSON.parse(row.cache_value),
123          `cache_value for key "${row.cache_key}" should be valid JSON`
124        );
125      }
126    });
127  
128    test('cache entries have expires_at set', async () => {
129      await precomputeDashboard();
130  
131      const rows = db.prepare('SELECT cache_key, expires_at FROM dashboard_cache').all();
132  
133      for (const row of rows) {
134        assert.ok(row.expires_at, `expires_at should be set for key "${row.cache_key}"`);
135      }
136    });
137  
138    test('can run twice without error (upsert behavior)', async () => {
139      await precomputeDashboard();
140      await assert.doesNotReject(precomputeDashboard());
141    });
142  
143    test('returns a result object', async () => {
144      const result = await precomputeDashboard();
145      // precomputeDashboard may return undefined or a result object — just shouldn't throw
146      assert.ok(result === undefined || typeof result === 'object');
147    });
148  });