import { createRequire } from 'node:module';

// node:sqlite is a builtin; load it via runtime require so bundlers (vite/vitest
// and the Electron esbuild bundle) don't try to transform/resolve it statically.
// Works in both ESM (import.meta.url) and CJS (__filename); for a builtin the base
// path is irrelevant.
const requireBase = typeof __filename !== 'undefined' ? __filename : import.meta.url;
const nodeRequire = createRequire(requireBase);

export type SqlValue = string | number | bigint | null | Uint8Array;

export interface RunResult {
  changes: number | bigint;
  lastInsertRowid: number | bigint;
}

export interface Stmt {
  run(...params: SqlValue[]): RunResult;
  get<T = unknown>(...params: SqlValue[]): T | undefined;
  all<T = unknown>(...params: SqlValue[]): T[];
}

/** Minimal SQLite surface so the engine is swappable (node:sqlite now, better-sqlite3 in Electron). */
export interface Driver {
  exec(sql: string): void;
  prepare(sql: string): Stmt;
  close(): void;
}

/** Driver backed by node:sqlite — used in plain Node (tests, CLI, node >= 22.5). */
export function openNodeSqlite(path: string): Driver {
  // Lazily required so importing this module doesn't pull node:sqlite into
  // runtimes that lack it (e.g. Electron's bundled Node, which uses better-sqlite3).
  const { DatabaseSync } = nodeRequire('node:sqlite') as typeof import('node:sqlite');
  const sqlite = new DatabaseSync(path);
  // WAL + a busy timeout let the GUI process and the headless --collect process
  // share the file without immediate "database is locked" errors.
  sqlite.exec('PRAGMA foreign_keys = ON; PRAGMA journal_mode = WAL; PRAGMA busy_timeout = 5000;');
  return {
    exec: (sql) => sqlite.exec(sql),
    prepare: (sql) => {
      const s = sqlite.prepare(sql);
      return {
        run: (...params) => s.run(...params) as RunResult,
        get: <T,>(...params: SqlValue[]) => s.get(...params) as T | undefined,
        all: <T,>(...params: SqlValue[]) => s.all(...params) as T[],
      };
    },
    close: () => sqlite.close(),
  };
}

/**
 * Driver backed by better-sqlite3 — used inside Electron, whose bundled Node
 * (20) has no node:sqlite. Lazily required so non-Electron runtimes never load
 * the native addon. better-sqlite3 must be rebuilt for Electron's ABI.
 */
export function openBetterSqlite(path: string): Driver {
  const BetterSqlite3 = nodeRequire('better-sqlite3') as typeof import('better-sqlite3');
  const sqlite = new BetterSqlite3(path);
  sqlite.pragma('foreign_keys = ON');
  // Concurrency-safe for the GUI + headless --collect processes sharing one file.
  sqlite.pragma('journal_mode = WAL');
  sqlite.pragma('busy_timeout = 5000');
  return {
    exec: (sql) => {
      sqlite.exec(sql);
    },
    prepare: (sql) => {
      const s = sqlite.prepare(sql);
      return {
        run: (...params) => s.run(...params) as RunResult,
        get: <T,>(...params: SqlValue[]) => s.get(...params) as T | undefined,
        all: <T,>(...params: SqlValue[]) => s.all(...params) as T[],
      };
    },
    close: () => sqlite.close(),
  };
}