1  /**
  2   * pg-mock.js
  3   *
  4   * Provides `createPgMock(db)` — takes a better-sqlite3 Database instance and returns
  5   * a namedExports object suitable for `mock.module('../../src/utils/db.js', { namedExports })`.
  6   *
  7   * This lets tests stay fast (SQLite in-memory, no real PG needed) while the module under
  8   * test receives its db.js imports via the mock.
  9   *
 10   * SQL translation applied automatically:
 11   *   - $1, $2, ...  →  ?  (positional params stripped of number)
 12   *   - NOW()        →  datetime('now')
 13   *   - CURRENT_DATE →  date('now')
 14   *   - RETURNING id (stripped — better-sqlite3 uses lastInsertRowid)
 15   *   - ON CONFLICT (...) DO UPDATE SET ... (converted to ON CONFLICT DO REPLACE or stripped)
 16   *
 17   * Usage:
 18   *   import { mock } from 'node:test';
 19   *   import Database from 'better-sqlite3';
 20   *   import { createPgMock } from '../helpers/pg-mock.js';
 21   *
 22   *   const db = new Database(':memory:');
 23   *   db.exec(`CREATE TABLE opt_outs (...)`);
 24   *
 25   *   mock.module('../../src/utils/db.js', {
 26   *     namedExports: createPgMock(db),
 27   *   });
 28   *
 29   *   // AFTER mock.module, import the module under test:
 30   *   const { addOptOut } = await import('../../src/utils/compliance.js');
 31   */
 32  
 33  /**
 34   * Convert PostgreSQL-style SQL to SQLite-compatible SQL.
 35   *
 36   * @param {string} sql
 37   * @returns {string}
 38   */
 39  export function pgToSqlite(sql) {
 40    const translated = sql
 41      // $1, $2, ... → ?
 42      .replace(/\$\d+/g, '?')
 43      // Strip schema-qualified table names: tel.table → table, ops.table → table
 44      // Covers schemas used by process-guardian (tel., ops.) and weekly-learning-report (ops.)
 45      .replace(/\b(?:tel|ops)\./g, '')
 46      // CURRENT_TIMESTAMP in DEFAULT clause → (datetime('now')) — SQLite requires parens for function defaults
 47      .replace(/\bDEFAULT\s+CURRENT_TIMESTAMP\b/gi, "DEFAULT (datetime('now'))")
 48      // NOW() in DEFAULT clause → (datetime('now')) — must wrap in parens for SQLite DDL
 49      .replace(/\bDEFAULT\s+NOW\(\)/gi, "DEFAULT (datetime('now'))")
 50      // NOW() elsewhere → datetime('now')
 51      .replace(/\bNOW\(\)/gi, "datetime('now')")
 52      // CURRENT_DATE → date('now')
 53      .replace(/\bCURRENT_DATE\b/gi, "date('now')")
 54      // EXTRACT(EPOCH FROM (expr1 - expr2)) → (julianday(expr1) - julianday(expr2)) * 86400
 55      // Handles timestamp difference in seconds (PostgreSQL-specific)
 56      // Uses a two-pass approach: first translate NOW() inside EXTRACT, then translate EXTRACT
 57      .replace(
 58        /EXTRACT\s*\(\s*EPOCH\s+FROM\s*\((.+?)\)\s*\)/gi,
 59        (_, inner) => {
 60          // inner is "expr1 - expr2"; split on last occurrence of ' - '
 61          const dashIdx = inner.lastIndexOf(' - ');
 62          if (dashIdx === -1) return `((julianday(${inner.trim()}) - julianday('1970-01-01')) * 86400)`;
 63          const e1 = inner.slice(0, dashIdx).trim().replace(/\bNOW\(\)/gi, "datetime('now')");
 64          const e2 = inner.slice(dashIdx + 3).trim().replace(/\bNOW\(\)/gi, "datetime('now')");
 65          return `((julianday(${e1}) - julianday(${e2})) * 86400)`;
 66        }
 67      )
 68      // EXTRACT(EPOCH FROM expr) without leading explicit parens
 69      // (e.g. EXTRACT(EPOCH FROM AVG(l.finished_at - l.started_at)))
 70      // Replace with 0 using a manual balanced-paren scan on the full string.
 71      // INTERVAL arithmetic: expr ± INTERVAL 'N unit' → datetime(expr, '±N unit')
 72      // Handles: NOW() - INTERVAL '3 minutes', created_at > NOW() - INTERVAL '7 days',
 73      //          NOW() + INTERVAL '30 days', etc.
 74      .replace(
 75        /datetime\('now'\)\s*-\s*INTERVAL\s*'([^']+)'/gi,
 76        (_, interval) => `datetime('now', '-${interval}')`
 77      )
 78      .replace(
 79        /NOW\(\)\s*-\s*INTERVAL\s*'([^']+)'/gi,
 80        (_, interval) => `datetime('now', '-${interval}')`
 81      )
 82      .replace(
 83        /datetime\('now'\)\s*\+\s*INTERVAL\s*'([^']+)'/gi,
 84        (_, interval) => `datetime('now', '+${interval}')`
 85      )
 86      .replace(
 87        /NOW\(\)\s*\+\s*INTERVAL\s*'([^']+)'/gi,
 88        (_, interval) => `datetime('now', '+${interval}')`
 89      )
 90      // Dynamic interval: (? || ' hours')::interval, (? || ' minutes')::interval, etc.
 91      // Translate by keeping only the ? value; the datetime() modifier will be applied around it.
 92      // Pattern: datetime('now') - (? || ' hours')::interval → datetime('now', '-' || ? || ' hours')
 93      // We match: datetime('now') - (? || ' unit')::interval (after NOW() was already translated)
 94      .replace(
 95        /datetime\('now'\)\s*-\s*\(\?\s*\|\|\s*'(\s*\w+)'\)::interval/gi,
 96        (_, unit) => `datetime('now', '-' || ? || '${unit}')`
 97      )
 98      .replace(
 99        /datetime\('now'\)\s*\+\s*\(\?\s*\|\|\s*'(\s*\w+)'\)::interval/gi,
100        (_, unit) => `datetime('now', '+' || ? || '${unit}')`
101      )
102      // DDL type translations (for CREATE TABLE statements passed through pg-mock)
103      // SERIAL / BIGSERIAL → INTEGER (SQLite auto-increments INTEGER PRIMARY KEY)
104      .replace(/\bBIGSERIAL\b/gi, 'INTEGER')
105      .replace(/\bSERIAL\b/gi, 'INTEGER')
106      // TIMESTAMPTZ, TIMESTAMP WITH TIME ZONE → DATETIME
107      .replace(/\bTIMESTAMP\s+WITH\s+TIME\s+ZONE\b/gi, 'DATETIME')
108      .replace(/\bTIMESTAMPTZ\b/gi, 'DATETIME')
109      // NOTE: bare TIMESTAMP → DATETIME replacement is handled AFTER the chain
110      // because it needs to be context-aware (DDL only, not DML column names).
111      // BOOLEAN → INTEGER (SQLite uses 0/1)
112      .replace(/\bBOOLEAN\b/gi, 'INTEGER')
113      // VARCHAR(n) → TEXT
114      .replace(/\bVARCHAR\s*\(\d+\)/gi, 'TEXT')
115      // Generic ::type cast removal (e.g., ::interval, ::text, ::integer, ::jsonb)
116      // Must come AFTER specific patterns that use :: for meaningful translation
117      .replace(/::\w+(?:\[\])?/g, '')
118      // Param/column ± INTERVAL 'N unit' — must come AFTER ::type cast removal
119      // e.g., $3::timestamptz - INTERVAL '5 minutes' → ? - INTERVAL '5 minutes' → datetime(?, '-5 minutes')
120      // Handles both ? (parameter) and column_name (identifier)
121      .replace(
122        /(\?|[\w.]+)\s*-\s*INTERVAL\s*'([^']+)'/gi,
123        (_, expr, interval) => `datetime(${expr}, '-${interval}')`
124      )
125      .replace(
126        /(\?|[\w.]+)\s*\+\s*INTERVAL\s*'([^']+)'/gi,
127        (_, expr, interval) => `datetime(${expr}, '+${interval}')`
128      )
129      // Standalone INTERVAL 'N unit' (e.g. in CASE THEN INTERVAL '1 day') →
130      // SQLite datetime modifier string '-N unit' so date arithmetic still compiles.
131      // This covers patterns like: expr >= NOW() - (CASE WHEN ... THEN INTERVAL '1 day' END)
132      // We convert the whole `expr >= datetime('now') - (CASE ...)` to a date modifier string
133      // by replacing bare INTERVAL literals with their negative modifier.
134      // Pattern: INTERVAL 'N unit' (not preceded by - or + which are already handled above)
135      .replace(/\bINTERVAL\s+'([^']+)'/gi, (_, interval) => `'-${interval}'`)
136      // STRING_AGG(expr, sep) → GROUP_CONCAT(expr, sep)
137      .replace(/\bSTRING_AGG\s*\(/gi, 'GROUP_CONCAT(')
138      // col::jsonb->>'key' → json_extract(col, '$.key')
139      // Simple case only — complex JSONB queries should be mocked at a higher level
140      .replace(/(\w+)::jsonb->>'(\w+)'/g, "json_extract($1, '\\$$2')")
141      // col = ANY($1::text[]) / col = ANY($1::int[]) → col ANY_EXPAND(?)
142      // The execution layer will expand array params into IN (?, ?, ...) at runtime.
143      // The leading `=` is consumed here so the expansion yields valid `col IN (...)` not `col = IN (...)`.
144      .replace(/=\s*ANY\(\?::(?:text|int|bigint|uuid)\[\]\)/gi, 'ANY_EXPAND(?)')
145      .replace(/=\s*ANY\(\?\)/gi, 'ANY_EXPAND(?)')
146      // Strip RETURNING clause entirely (SQLite uses lastInsertRowid)
147      .replace(/\bRETURNING\s+\w+(\s*,\s*\w+)*/gi, '')
148      // ON CONFLICT (...) WHERE condition DO UPDATE SET ... — conditional conflict (PG partial index)
149      // SQLite doesn't support WHERE on the conflict target; strip the entire ON CONFLICT block.
150      // Must run AFTER RETURNING is already stripped so we can match to end-of-string or semicolon.
151      .replace(/\s*ON CONFLICT\s*\([^)]+\)\s*WHERE\s+[\s\S]+?(?=\s*RETURNING\b|\s*;|\s*$)/gi, '')
152      // ON CONFLICT (...) DO NOTHING — strip the clause but convert INSERT to INSERT OR IGNORE
153      // so SQLite honours the "skip duplicate" intent without a UNIQUE constraint error.
154      // We do a two-step: first mark the INSERT, then strip the conflict clause.
155      .replace(/\bINSERT\b(?=[\s\S]*?ON CONFLICT\s*\([^)]+\)\s*DO NOTHING)/gi, 'INSERT OR IGNORE')
156      .replace(/ON CONFLICT\s*\([^)]+\)\s*DO NOTHING/gi, '')
157      // ON CONFLICT (...) DO UPDATE SET col = excluded.col
158      // Convert to ON CONFLICT DO REPLACE when it's a simple upsert
159      .replace(
160        /ON CONFLICT\s*\([^)]+\)\s*DO UPDATE SET\s+([^;]+?)(?=\s*(WHERE|RETURNING|;|$))/gi,
161        (match, setClauses) => {
162          // If the SET clauses only reference EXCLUDED.*, convert to INSERT OR REPLACE
163          // Otherwise keep partial — SQLite 3.24+ supports upsert natively with ON CONFLICT
164          return `ON CONFLICT DO UPDATE SET ${setClauses}`;
165        }
166      )
167      // ILIKE → LIKE (SQLite is case-insensitive for ASCII by default)
168      .replace(/\bILIKE\b/gi, 'LIKE')
169      // Boolean literals in WHERE/SET: = true → = 1, = false → = 0, IS true → IS 1, etc.
170      .replace(/=\s*\btrue\b/gi, '= 1')
171      .replace(/=\s*\bfalse\b/gi, '= 0')
172      .replace(/\bIS\s+true\b/gi, '= 1')
173      .replace(/\bIS\s+false\b/gi, '= 0')
174      // lower(col) = lower(?) is fine in both — no translation needed
175      .trim();
176  
177    // Post-process: replace any remaining EXTRACT(EPOCH FROM ...) that has a function call
178    // as the argument (e.g. EXTRACT(EPOCH FROM AVG(...))). The regex chain above handles the
179    // `(expr1 - expr2)` form; this handles the bare function-call form.
180    // We use a character-scan approach to find balanced parens.
181    let result = replaceExtractEpochFunctions(translated);
182  
183    // Post-process: TIMESTAMP → DATETIME only in DDL context (CREATE TABLE).
184    // In DML queries, "timestamp" is a column name and must be left alone.
185    // We detect DDL by checking if the SQL starts with CREATE.
186    if (/^\s*CREATE\b/i.test(result)) {
187      result = result.replace(/\bTIMESTAMP\b/gi, 'DATETIME');
188    }
189  
190    return result;
191  }
192  
193  /**
194   * Replace EXTRACT(EPOCH FROM funcCall(...)) with 0 using a balanced-paren scan.
195   * Only fires when the pattern is still present after the regex chain above.
196   */
197  function replaceExtractEpochFunctions(sql) {
198    const pattern = /EXTRACT\s*\(\s*EPOCH\s+FROM\s+/gi;
199    let result = '';
200    let lastIdx = 0;
201    let match;
202    pattern.lastIndex = 0;
203    while ((match = pattern.exec(sql)) !== null) {
204      result += sql.slice(lastIdx, match.index);
205      // Find the matching close paren of EXTRACT(
206      let depth = 1;
207      let i = match.index + match[0].length;
208      while (i < sql.length && depth > 0) {
209        if (sql[i] === '(') depth++;
210        else if (sql[i] === ')') depth--;
211        i++;
212      }
213      // i now points to char after the closing paren of EXTRACT(...)
214      result += '0';
215      lastIdx = i;
216      pattern.lastIndex = i; // skip past the replaced span
217    }
218    result += sql.slice(lastIdx);
219    return result;
220  }
221  
222  /**
223   * Create a pg-mock namedExports object backed by a real SQLite database.
224   *
225   * All async functions (run, getOne, getAll, query) translate PG SQL to SQLite
226   * and execute against the provided `db` instance.
227   *
228   * withTransaction provides a fake client that also routes to SQLite.
229   *
230   * @param {import('better-sqlite3').Database} db
231   * @returns {object} namedExports for mock.module('../../src/utils/db.js', ...)
232   */
233  export function createPgMock(db) {
234  
235    /**
236     * SQLite does not accept JavaScript booleans — coerce true/false to 1/0.
237     * All other types (string, number, bigint, Buffer, null) pass through unchanged.
238     */
239    function coerceParams(params) {
240      return params.map(p => (typeof p === 'boolean' ? (p ? 1 : 0) : p));
241    }
242  
243    /**
244     * Expand ANY_EXPAND(?) placeholders by replacing them with IN (?, ?, ...) and
245     * flattening array params at those positions.
246     * Returns [expandedSql, expandedParams].
247     */
248    function expandAnyPlaceholders(sql, params) {
249      if (!sql.includes('ANY_EXPAND(?)')) {
250        return [sql, params];
251      }
252      let paramIdx = 0;
253      const expandedParams = [];
254      const expandedSql = sql.replace(/\?|ANY_EXPAND\(\?\)/g, match => {
255        const param = params[paramIdx++];
256        if (match === 'ANY_EXPAND(?)') {
257          if (Array.isArray(param)) {
258            expandedParams.push(...param);
259            return `IN (${param.map(() => '?').join(', ')})`;
260          } else {
261            // Scalar — treat as single-element IN
262            expandedParams.push(param);
263            return 'IN (?)';
264          }
265        } else {
266          expandedParams.push(param);
267          return '?';
268        }
269      });
270      return [expandedSql, expandedParams];
271    }
272  
273    async function query(sql, params = []) {
274      const [expanded, expandedParams] = expandAnyPlaceholders(pgToSqlite(sql), params);
275      const stmt = db.prepare(expanded);
276      const trimmed = expanded.trim().toUpperCase();
277      const bound = coerceParams(expandedParams);
278  
279      if (
280        trimmed.startsWith('SELECT') ||
281        trimmed.startsWith('WITH') ||
282        trimmed.startsWith('PRAGMA')
283      ) {
284        const rows = stmt.all(...bound);
285        return { rows, rowCount: rows.length };
286      } else {
287        const result = stmt.run(...bound);
288        const rows = result.lastInsertRowid !== null && result.lastInsertRowid !== undefined
289          ? [{ id: result.lastInsertRowid }]
290          : [];
291        return { rows, rowCount: result.changes };
292      }
293    }
294  
295    async function getOne(sql, params = []) {
296      const [expanded, expandedParams] = expandAnyPlaceholders(pgToSqlite(sql), params);
297      const stmt = db.prepare(expanded);
298      return stmt.get(...coerceParams(expandedParams)) || null;
299    }
300  
301    async function getAll(sql, params = []) {
302      const [expanded, expandedParams] = expandAnyPlaceholders(pgToSqlite(sql), params);
303      const stmt = db.prepare(expanded);
304      return stmt.all(...coerceParams(expandedParams));
305    }
306  
307    async function run(sql, params = []) {
308      const [expanded, expandedParams] = expandAnyPlaceholders(pgToSqlite(sql), params);
309      const stmt = db.prepare(expanded);
310      const result = stmt.run(...coerceParams(expandedParams));
311      return { changes: result.changes, lastInsertRowid: result.lastInsertRowid };
312    }
313  
314    async function withTransaction(fn) {
315      // Wrap in a SQLite transaction
316      let result;
317      db.transaction(() => {
318        // Provide a fake pg client-like object with a query method
319        const fakeClient = {
320          query: async (sql, params = []) => {
321            const [expanded, expandedParams] = expandAnyPlaceholders(pgToSqlite(sql), params);
322            const stmt = db.prepare(expanded);
323            const trimmed = expanded.trim().toUpperCase();
324            const bound = coerceParams(expandedParams);
325            if (trimmed.startsWith('SELECT') || trimmed.startsWith('WITH')) {
326              const rows = stmt.all(...bound);
327              return { rows, rowCount: rows.length };
328            } else {
329              const r = stmt.run(...bound);
330              const hasId = r.lastInsertRowid !== null && r.lastInsertRowid !== undefined;
331              return { rows: hasId ? [{ id: r.lastInsertRowid }] : [], rowCount: r.changes };
332            }
333          },
334        };
335        result = fn(fakeClient);
336      })();
337      // fn may return a promise — await it
338      return result;
339    }
340  
341    return {
342      getPool: () => ({}),
343      query,
344      getOne,
345      getAll,
346      run,
347      withTransaction,
348      closePool: async () => {},
349      createDatabaseConnection: () => ({}),
350      closeDatabaseConnection: async () => {},
351    };
352  }
353  
354  /**
355   * Create a pg-mock namedExports object backed by a *lazily-resolved* SQLite database.
356   *
357   * Unlike `createPgMock(db)`, this variant accepts a getter function `getDb` that is
358   * called on every SQL operation.  This lets tests swap their per-test database in a
359   * `beforeEach` hook while keeping a single `mock.module('db.js', ...)` call at the
360   * top of the test file.
361   *
362   * Usage:
363   *   let currentDb = null;
364   *   mock.module('../../src/utils/db.js', {
365   *     namedExports: createLazyPgMock(() => currentDb),
366   *   });
367   *
368   *   beforeEach(() => {
369   *     currentDb = new Database(':memory:');
370   *     currentDb.exec(schema);
371   *   });
372   *   afterEach(() => { currentDb.close(); currentDb = null; });
373   *
374   * @param {() => import('better-sqlite3').Database} getDb  Getter that returns the current DB.
375   * @returns {object} namedExports for mock.module('../../src/utils/db.js', ...)
376   */
377  export function createLazyPgMock(getDb) {
378    function coerceParams(params) {
379      return params.map(p => (typeof p === 'boolean' ? (p ? 1 : 0) : p));
380    }
381  
382    function expandAnyPlaceholders(sql, params) {
383      if (!sql.includes('ANY_EXPAND(?)')) return [sql, params];
384      let paramIdx = 0;
385      const expandedParams = [];
386      const expandedSql = sql.replace(/\?|ANY_EXPAND\(\?\)/g, match => {
387        const param = params[paramIdx++];
388        if (match === 'ANY_EXPAND(?)') {
389          if (Array.isArray(param)) {
390            expandedParams.push(...param);
391            return `IN (${param.map(() => '?').join(', ')})`;
392          }
393          expandedParams.push(param);
394          return 'IN (?)';
395        }
396        expandedParams.push(param);
397        return '?';
398      });
399      return [expandedSql, expandedParams];
400    }
401  
402    async function query(sql, params = []) {
403      const db = getDb();
404      const [expanded, expandedParams] = expandAnyPlaceholders(pgToSqlite(sql), params);
405      const stmt = db.prepare(expanded);
406      const trimmed = expanded.trim().toUpperCase();
407      const bound = coerceParams(expandedParams);
408      if (trimmed.startsWith('SELECT') || trimmed.startsWith('WITH') || trimmed.startsWith('PRAGMA')) {
409        const rows = stmt.all(...bound);
410        return { rows, rowCount: rows.length };
411      }
412      const result = stmt.run(...bound);
413      const rows =
414        result.lastInsertRowid !== null && result.lastInsertRowid !== undefined
415          ? [{ id: result.lastInsertRowid }]
416          : [];
417      return { rows, rowCount: result.changes };
418    }
419  
420    async function getOne(sql, params = []) {
421      const db = getDb();
422      const [expanded, expandedParams] = expandAnyPlaceholders(pgToSqlite(sql), params);
423      return db.prepare(expanded).get(...coerceParams(expandedParams)) || null;
424    }
425  
426    async function getAll(sql, params = []) {
427      const db = getDb();
428      const [expanded, expandedParams] = expandAnyPlaceholders(pgToSqlite(sql), params);
429      return db.prepare(expanded).all(...coerceParams(expandedParams));
430    }
431  
432    async function run(sql, params = []) {
433      const db = getDb();
434      const [expanded, expandedParams] = expandAnyPlaceholders(pgToSqlite(sql), params);
435      const stmt = db.prepare(expanded);
436      const result = stmt.run(...coerceParams(expandedParams));
437      return { changes: result.changes, lastInsertRowid: result.lastInsertRowid };
438    }
439  
440    async function withTransaction(fn) {
441      const db = getDb();
442      let result;
443      db.transaction(() => {
444        const fakeClient = {
445          query: async (sql2, params2 = []) => {
446            const [expanded, expandedParams] = expandAnyPlaceholders(pgToSqlite(sql2), params2);
447            const stmt = db.prepare(expanded);
448            const trimmed = expanded.trim().toUpperCase();
449            const bound = coerceParams(expandedParams);
450            if (trimmed.startsWith('SELECT') || trimmed.startsWith('WITH')) {
451              const rows = stmt.all(...bound);
452              return { rows, rowCount: rows.length };
453            }
454            const r = stmt.run(...bound);
455            const hasId = r.lastInsertRowid !== null && r.lastInsertRowid !== undefined;
456            return { rows: hasId ? [{ id: r.lastInsertRowid }] : [], rowCount: r.changes };
457          },
458        };
459        result = fn(fakeClient);
460      })();
461      return result;
462    }
463  
464    return {
465      getPool: () => ({}),
466      query,
467      getOne,
468      getAll,
469      run,
470      withTransaction,
471      closePool: async () => {},
472      createDatabaseConnection: () => ({}),
473      closeDatabaseConnection: async () => {},
474    };
475  }