1  /**
  2   * mock-db.js — SQLite-backed mock for src/utils/db.js
  3   *
  4   * Provides a test helper that creates an in-memory SQLite database and
  5   * mocks the pg-based db.js exports so that source files under test
  6   * transparently use SQLite instead of PostgreSQL.
  7   *
  8   * Usage with node:test mock.module():
  9   *
 10   *   import { mock } from 'node:test';
 11   *   import { createMockDb, teardownMockDb } from '../helpers/mock-db.js';
 12   *
 13   *   // Before tests — register the mock BEFORE importing source modules
 14   *   const mockDb = createMockDb();
 15   *   mock.module('../../src/utils/db.js', { namedExports: mockDb.exports });
 16   *
 17   *   // Now import the module under test (it will use the mocked db.js)
 18   *   const { myFunction } = await import('../../src/some-module.js');
 19   *
 20   *   // Access the raw SQLite db for setup/assertions:
 21   *   mockDb.db.exec('INSERT INTO sites ...');
 22   *
 23   *   // After tests
 24   *   teardownMockDb(mockDb);
 25   *
 26   * The mock converts PG-style $1, $2 params to SQLite ? params automatically.
 27   */
 28  
 29  import Database from 'better-sqlite3';
 30  
 31  /**
 32   * Convert PostgreSQL $N placeholders to SQLite ? placeholders.
 33   * Also handles some PG-specific SQL that doesn't work in SQLite.
 34   */
 35  function pgToSqlite(sql) {
 36    // Replace $1, $2, ... with ?
 37    let converted = sql.replace(/\$\d+/g, '?');
 38  
 39    // NOW() → datetime('now')
 40    converted = converted.replace(/\bNOW\(\)/gi, "datetime('now')");
 41  
 42    // INTERVAL 'N days' → '+N days' (within datetime context — crude but works for tests)
 43    converted = converted.replace(
 44      /NOW\(\)\s*-\s*INTERVAL\s+'(\d+)\s+days'/gi,
 45      "datetime('now', '-$1 days')"
 46    );
 47    converted = converted.replace(
 48      /NOW\(\)\s*\+\s*INTERVAL\s+'(\d+)\s+days'/gi,
 49      "datetime('now', '+$1 days')"
 50    );
 51  
 52    // ::jsonb, ::text, ::integer casts — strip them (SQLite ignores types)
 53    converted = converted.replace(/::\w+/g, '');
 54  
 55    // ON CONFLICT (...) DO NOTHING → OR IGNORE (crude but functional)
 56    // Only handle the simple case
 57    converted = converted.replace(
 58      /INSERT\s+INTO\s+(.+?)\s*\((.+?)\)\s*VALUES\s*\((.+?)\)\s*ON\s+CONFLICT\s*(?:\([^)]*\))?\s*DO\s+NOTHING/gi,
 59      'INSERT OR IGNORE INTO $1 ($2) VALUES ($3)'
 60    );
 61  
 62    // ON CONFLICT (...) DO UPDATE SET ... → OR REPLACE (crude approximation)
 63    converted = converted.replace(
 64      /ON\s+CONFLICT\s*\([^)]*\)\s*DO\s+UPDATE\s+SET\s+[^;]*/gi,
 65      ''
 66    );
 67  
 68    // RETURNING id — SQLite doesn't support this; strip it
 69    // The mock run() uses lastInsertRowid instead
 70    converted = converted.replace(/\s+RETURNING\s+\w+/gi, '');
 71  
 72    // lower() for CITEXT comparison — keep as-is, SQLite handles it
 73    // STRING_AGG → GROUP_CONCAT
 74    converted = converted.replace(/\bSTRING_AGG\s*\(/gi, 'GROUP_CONCAT(');
 75  
 76    // Schema prefixes: m333., ops., tel., msgs. — strip them for SQLite
 77    converted = converted.replace(/\b(m333|ops|tel|msgs)\./g, '');
 78  
 79    return converted;
 80  }
 81  
 82  /**
 83   * Create a mock database backed by in-memory SQLite.
 84   * @param {string} [initSql] — optional SQL to create tables
 85   * @returns {{ db: Database, exports: object, addSchema: function }}
 86   */
 87  export function createMockDb(initSql) {
 88    const db = new Database(':memory:');
 89    db.pragma('foreign_keys = ON');
 90  
 91    if (initSql) {
 92      db.exec(initSql);
 93    }
 94  
 95    // The mock exports match the async API of src/utils/db.js
 96    const exports = {
 97      getPool: () => ({ query: async (text, params) => exports.query(text, params) }),
 98  
 99      query: async (text, params) => {
100        const sql = pgToSqlite(text);
101        const paramArray = params || [];
102        try {
103          // Detect if it's a SELECT or RETURNING query
104          const isSelect = /^\s*(SELECT|WITH)\b/i.test(sql.trim());
105          if (isSelect) {
106            const rows = db.prepare(sql).all(...paramArray);
107            return { rows, rowCount: rows.length };
108          } else {
109            const info = db.prepare(sql).run(...paramArray);
110            return {
111              rows: info.lastInsertRowid ? [{ id: Number(info.lastInsertRowid) }] : [],
112              rowCount: info.changes,
113            };
114          }
115        } catch (e) {
116          // Convert SQLite UNIQUE constraint errors to PG-style error codes
117          if (e.message.includes('UNIQUE constraint')) {
118            const pgError = new Error(e.message);
119            pgError.code = '23505'; // unique_violation
120            throw pgError;
121          }
122          if (e.message.includes('FOREIGN KEY constraint')) {
123            const pgError = new Error(e.message);
124            pgError.code = '23503'; // foreign_key_violation
125            throw pgError;
126          }
127          throw e;
128        }
129      },
130  
131      getOne: async (text, params) => {
132        const { rows } = await exports.query(text, params);
133        return rows[0] || null;
134      },
135  
136      getAll: async (text, params) => {
137        const { rows } = await exports.query(text, params);
138        return rows;
139      },
140  
141      run: async (text, params) => {
142        const { rowCount, rows } = await exports.query(text, params);
143        return { changes: rowCount, lastInsertRowid: rows[0]?.id };
144      },
145  
146      withTransaction: async (fn) => {
147        // Use a simple transaction wrapper with the mock client
148        db.exec('BEGIN');
149        const client = {
150          query: async (sql, p) => exports.query(sql, p),
151        };
152        try {
153          const result = await fn(client);
154          db.exec('COMMIT');
155          return result;
156        } catch (e) {
157          db.exec('ROLLBACK');
158          throw e;
159        }
160      },
161  
162      closePool: async () => {},
163      createDatabaseConnection: () => exports.getPool(),
164      closeDatabaseConnection: async () => {},
165    };
166  
167    /**
168     * Execute raw SQL on the underlying SQLite db (for test setup).
169     * Strips schema prefixes for convenience.
170     */
171    function execSetup(sql) {
172      const cleaned = sql.replace(/\b(m333|ops|tel|msgs)\./g, '');
173      db.exec(cleaned);
174    }
175  
176    return { db, exports, execSetup };
177  }
178  
179  /**
180   * Tear down the mock database.
181   */
182  export function teardownMockDb(mockDb) {
183    if (mockDb && mockDb.db) {
184      try { mockDb.db.close(); } catch { /* ignore */ }
185    }
186  }