// Dev-stub SQLite "pool" — the local-dev engine of the dual-engine SDK (Vision §1
// / D3). Runs on **Bun** (`bun:sqlite`). It presents the SAME duck-typed surface
// the kernel's `makeDatabaseService` + `makeDataSurface` + migration runner call on
// a `pg.Pool` (`query`, `connect()` → a client with `query`/`release`, `end`), so a
// plugin's byte-identical server code runs on SQLite locally and Postgres in prod —
// the developer never wires the engine; the dev host selects it.
//
// The kernel's data path wraps every scoped query in Postgres ceremony — `BEGIN;
// SET LOCAL ROLE app_authenticated; SET LOCAL search_path = …; set_config('app.*',
// …, true); … ; COMMIT`. SQLite has none of roles/schemas/GUCs/RLS, so this adapter
// **no-ops the ceremony** and runs the real statement. The tenant wall on SQLite is
// the SDK's explicit `WHERE workspace_id` filter standing ALONE (D3: the injection
// must be strong enough without RLS) — which it is, because the same generated SQL
// carries the filter on both engines.
//
// It also translates the three Postgres *introspection* queries the data surface's
// `classify()` runs (`to_regclass`, `pg_attribute`, `pg_constraint`) into SQLite
// `sqlite_master`/`pragma` equivalents — the D5-flagged gap — so table classification
// (direct workspace_id vs FK-to-parent) works on SQLite too.
//
// Bun-only by construction (`bun:sqlite`); excluded from the node/base tsconfig and
// loaded only by the dev host under Bun. Never on the prod PG path.

import { Database } from "bun:sqlite";

interface QueryResult<R = Record<string, unknown>> {
  rows: R[];
  rowCount: number;
}

interface SqliteClient {
  query<R = Record<string, unknown>>(
    text: string,
    params?: readonly unknown[],
  ): Promise<QueryResult<R>>;
  release(): void;
}

export interface SqlitePool {
  query<R = Record<string, unknown>>(
    text: string,
    params?: readonly unknown[],
  ): Promise<QueryResult<R>>;
  connect(): Promise<SqliteClient>;
  end(): Promise<void>;
  on(event: string, handler: (...args: unknown[]) => void): void;
}

// ── Postgres ceremony this adapter absorbs (no-op on SQLite). Each pattern matches a
//    statement's leading keyword(s) so a real user query is never misclassified. Kept
//    as an array of SIMPLE regexes (not one mega-alternation) for readability + low
//    individual complexity. ──
//
// NOOP: connection/session ceremony + PG built-ins with no SQLite analogue. Advisory
// locks are unnecessary on the dev host's single serialized connection.
const NOOP: readonly RegExp[] = [
  /^\s*SET\s+(LOCAL\s+)?(ROLE|SESSION|search_path)\b/i,
  /^\s*RESET\b/i,
  /^\s*SELECT\s+set_config\(/i,
  /^\s*SELECT\s+pg_advisory_(un)?lock\b/i,
];
const TXN = /^\s*(BEGIN|COMMIT|ROLLBACK|SAVEPOINT|RELEASE\s+SAVEPOINT)\b/i;
// SKIP_DDL: Postgres-only DDL with no SQLite analogue. The tenant wall (RLS) + roles/
// grants are PG prod concerns the SDK's explicit workspace_id filter already covers;
// procedural blocks / functions / triggers / extensions / comments are migration
// ceremony a plugin author's local routes don't need. (The declarative CREATE TABLE /
// ADD COLUMN / CREATE INDEX still run.)
const SKIP_DDL: readonly RegExp[] = [
  /^\s*DO\s+\$/i,
  /^\s*(CREATE|DROP)\s+SCHEMA\b/i,
  /^\s*(CREATE|DROP|ALTER)\s+SEQUENCE\b/i,
  /^\s*(GRANT|REVOKE)\b/i,
  /^\s*(CREATE|DROP)\s+EXTENSION\b/i,
  /^\s*COMMENT\s+ON\b/i,
  /^\s*CREATE(\s+OR\s+REPLACE)?\s+FUNCTION\b/i,
  /^\s*DROP\s+FUNCTION\b/i,
  /^\s*(CREATE|DROP)\s+TRIGGER\b/i,
  /^\s*(CREATE|DROP|ALTER)\s+POLICY\b/i,
];
// The only `ALTER TABLE` forms SQLite supports are ADD/DROP COLUMN and RENAME; every
// other form (ALTER COLUMN, ADD/DROP/VALIDATE CONSTRAINT, ENABLE RLS, SET …) is
// Postgres-only migration ceremony — no-op'd on the dev engine.
const ALTER_TABLE = /^\s*ALTER\s+TABLE\b/i;
const ALTER_TABLE_SUPPORTED = /\b(ADD\s+COLUMN|DROP\s+COLUMN|RENAME)\b/i;
const RETURNS_ROWS = /^\s*(SELECT|PRAGMA|WITH|VALUES)\b/i;
// DDL (schema statements, migration-only) — best-effort on the dev engine; a failure
// is logged + skipped, never thrown. DML failures still surface.
const IS_DDL = /^\s*(CREATE|ALTER|DROP|REINDEX|TRUNCATE|VACUUM|ANALYZE)\b/i;

const matchesAny = (sql: string, pats: readonly RegExp[]): boolean => pats.some((p) => p.test(sql));

// classify() introspection signatures (kernel-base/workspace-db.ts).
const Q_REGCLASS = /to_regclass\(\$1\)::oid\s+AS\s+oid/i;
const Q_OWN_WORKSPACE_COL = /pg_attribute[\s\S]*attname\s*=\s*'workspace_id'/i;
const Q_FK_TO_PARENT = /pg_constraint[\s\S]*confrelid::regclass::text\s+AS\s+parent_regclass/i;

/** Postgres `$1,$2,…` (may repeat / be out of order) → SQLite positional `?`, with
 *  the bind list rebuilt in placeholder-appearance order (F5 adapter semantics). */
function toSqlite(text: string, params: readonly unknown[]): { sql: string; binds: unknown[] } {
  const binds: unknown[] = [];
  let sql = text.replace(/\$(\d+)/g, (_m, n: string) => {
    binds.push(normalizeBind(params[Number(n) - 1]));
    return "?";
  });
  // Strip Postgres `::type` casts the generated surface emits (e.g. `COUNT(*)::int`,
  // `$1::text`, `x::timestamptz`). One type token only — NO spaces — so `::int AS c`
  // strips just `::int` and leaves ` AS c`. SQLite has no `::` cast; affinity is fine.
  sql = sql.replace(/::"?[a-zA-Z_]\w*"?(\[\])?/g, "");
  sql = translateDialect(sql);
  return { sql, binds };
}

// Postgres built-ins the generated surface + migrations emit that SQLite lacks an
// identical spelling for. Dev-fidelity, not byte-perfection — enough for a plugin
// author to exercise their routes locally (the prod engine is still Postgres).
function translateDialect(sql: string): string {
  return (
    sql
      // time: NOW()/CURRENT_TIMESTAMP both → SQLite's CURRENT_TIMESTAMP (UTC text).
      .replace(/\bNOW\s*\(\s*\)/gi, "CURRENT_TIMESTAMP")
      // a UUID-shaped random hex (not RFC-4122-strict, but unique + the right shape).
      .replace(
        /\bgen_random_uuid\s*\(\s*\)/gi,
        "(lower(hex(randomblob(4)))||'-'||lower(hex(randomblob(2)))||'-'||lower(hex(randomblob(2)))||'-'||lower(hex(randomblob(2)))||'-'||lower(hex(randomblob(6))))",
      )
      // SQLite's ALTER TABLE ADD COLUMN has no `IF NOT EXISTS` (PG-only). Strip it;
      // idempotency is restored by swallowing the duplicate-column error in runReal.
      .replace(/\bADD\s+COLUMN\s+IF\s+NOT\s+EXISTS\b/gi, "ADD COLUMN")
      // `id SERIAL PRIMARY KEY` → `id INTEGER PRIMARY KEY` (SQLite's rowid alias
      // auto-increments). Without this a SERIAL id stays NULL on insert and every
      // by-id route (GET/PUT/DELETE /:id) is dead.
      .replace(/\b(BIG|SMALL)?SERIAL\b/gi, "INTEGER")
      // sequence calls (the SQLite sequences were no-op'd) → NULL, so an INTEGER
      // PRIMARY KEY autoincrements and any other sequence-backed column nulls out.
      .replace(/\b(next|curr)val\s*\([^)]*\)/gi, "NULL")
    // (TRUE/FALSE need no translation — bun:sqlite's SQLite recognizes them as 1/0.)
  );
}

// bun:sqlite binds only number | bigint | string | boolean | Uint8Array | null.
// Coerce the value shapes the SDK passes (Date, undefined, json objects) so a
// scoped write doesn't throw on an otherwise-valid value.
function normalizeBind(v: unknown): unknown {
  if (v === undefined) return null;
  if (v instanceof Date) return v.toISOString();
  if (typeof v === "boolean") return v ? 1 : 0;
  if (v !== null && typeof v === "object") return JSON.stringify(v);
  return v;
}

/** Resolve a table name from a `to_regclass` "oid" stand-in (we return the bare
 *  table name as the oid, so downstream introspection queries get it back). */
function tableExists(db: Database, table: string): boolean {
  const row = db
    .query(`SELECT 1 FROM sqlite_master WHERE type='table' AND name = ? LIMIT 1`)
    .get(table);
  return !!row;
}

function hasWorkspaceIdColumn(db: Database, table: string): boolean {
  const cols = db.query(`PRAGMA table_info(${quote(table)})`).all() as { name: string }[];
  return cols.some((c) => c.name === "workspace_id");
}

function findWorkspaceFk(
  db: Database,
  table: string,
): { fk_column: string; parent_regclass: string } | null {
  const fks = db.query(`PRAGMA foreign_key_list(${quote(table)})`).all() as {
    table: string;
    from: string;
  }[];
  for (const fk of fks) {
    if (hasWorkspaceIdColumn(db, fk.table)) {
      return { fk_column: fk.from, parent_regclass: fk.table };
    }
  }
  return null;
}

function quote(name: string): string {
  return `"${name.replace(/"/g, '""')}"`;
}

/** Classify a SQLite execution error into a tolerable empty result (Vision §5.1
 *  resilience), or null to rethrow. Tolerated: an idempotent re-`ADD COLUMN`; any
 *  statement during the boot migration window or any DDL (best-effort — the plugin
 *  boots with the tables it could create); a Postgres-only runtime READ (degrades to
 *  empty so the plugin's page renders instead of crashing on `undefined.length`). A
 *  runtime WRITE is NOT tolerated — a failing INSERT/UPDATE/DELETE is a real bug. */
function tolerateError<R>(sql: string, err: Error): QueryResult<R> | null {
  const msg = err.message;
  const empty: QueryResult<R> = { rows: [], rowCount: 0 };
  const debug = (kind: string) => {
    console.warn(`[dev-stub] ${kind} on SQLite: ${msg}`);
    if (process.env.KSERP_DEV_SQL_DEBUG) console.warn(`  ↳ ${sql.slice(0, 200)}`);
  };
  if (/duplicate column name/i.test(msg) && /\bADD\s+COLUMN\b/i.test(sql)) return empty;
  const migrating = (globalThis as { __KSERP_DEV_MIGRATING__?: boolean }).__KSERP_DEV_MIGRATING__;
  if (migrating || IS_DDL.test(sql)) return (debug("skipped a Postgres-only statement"), empty);
  if (RETURNS_ROWS.test(sql)) return (debug("degraded a Postgres-only read (empty result)"), empty);
  if (process.env.KSERP_DEV_SQL_DEBUG)
    console.error(`[dev-stub sqlite] FAILED SQL:\n${sql}\n→ ${msg}`);
  return null;
}

/** Run a single (already known to be real) statement against the SQLite db. */
function runReal<R>(db: Database, text: string, params: readonly unknown[]): QueryResult<R> {
  const { sql, binds } = toSqlite(text, params);
  let stmt;
  try {
    stmt = db.query(sql);
  } catch (err) {
    const empty = tolerateError<R>(sql, err as Error);
    if (empty) return empty;
    throw err;
  }
  if (RETURNS_ROWS.test(sql) || /\bRETURNING\b/i.test(sql)) {
    const rows = stmt.all(...(binds as never[])) as R[];
    return { rows, rowCount: rows.length };
  }
  const info = stmt.run(...(binds as never[]));
  return { rows: [], rowCount: Number(info.changes ?? 0) };
}

/** Translate / no-op a statement; returns the QueryResult or null if it's a real
 *  user statement the caller should run with `runReal`. */
function translate<R>(
  db: Database,
  text: string,
  params: readonly unknown[],
): QueryResult<R> | null {
  if (TXN.test(text)) {
    db.exec(text.trim());
    return { rows: [], rowCount: 0 };
  }
  if (matchesAny(text, SKIP_DDL)) {
    return { rows: [], rowCount: 0 };
  }
  if (ALTER_TABLE.test(text) && !ALTER_TABLE_SUPPORTED.test(text)) {
    return { rows: [], rowCount: 0 };
  }
  if (matchesAny(text, NOOP)) {
    // set_config(...) is a SELECT that the caller may read; hand back one empty row.
    return { rows: [{}] as R[], rowCount: 1 };
  }
  if (Q_REGCLASS.test(text)) {
    const table = String(params[0]);
    const oid = tableExists(db, table) ? table : null;
    return { rows: [{ oid }] as R[], rowCount: 1 };
  }
  if (Q_OWN_WORKSPACE_COL.test(text)) {
    const table = String(params[0]); // the "oid" we handed back == the table name
    return hasWorkspaceIdColumn(db, table)
      ? { rows: [{ "?column?": 1 }] as R[], rowCount: 1 }
      : { rows: [], rowCount: 0 };
  }
  if (Q_FK_TO_PARENT.test(text)) {
    const table = String(params[0]);
    const fk = findWorkspaceFk(db, table);
    return fk ? { rows: [fk] as R[], rowCount: 1 } : { rows: [], rowCount: 0 };
  }
  return null;
}

/**
 * Open a dev-stub SQLite pool. `filename` defaults to in-memory; pass a path to
 * persist across restarts. All access is serialized through one connection (a
 * dev host is low-concurrency; correctness over throughput), so the per-query
 * `BEGIN…COMMIT` the kernel emits never interleaves.
 */
export interface KernelStubSeed {
  workspaceId: number;
  userId: string;
  userEmail?: string;
  userName?: string;
  wsRole?: string;
}

/** Seed the minimal kernel-owned tables a plugin references (FK to `workspaces`,
 *  the occasional join to `users`/`workspace_members`) + the synthetic admin's rows,
 *  so a plugin's prod FK/joins resolve locally. Created BEFORE the plugin's migrations
 *  run, so its `REFERENCES workspaces(id)` is satisfiable. Minimal columns only — a
 *  route selecting an exotic kernel column degrades gracefully (the dev-host's job). */
function seedKernelStub(db: Database, seed: KernelStubSeed): void {
  db.exec(`CREATE TABLE IF NOT EXISTS workspaces (
    id INTEGER PRIMARY KEY, name TEXT, slug TEXT, created_at TEXT DEFAULT CURRENT_TIMESTAMP)`);
  db.exec(`CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY, email TEXT, name TEXT, role TEXT, is_active INTEGER DEFAULT 1)`);
  db.exec(`CREATE TABLE IF NOT EXISTS workspace_members (
    workspace_id INTEGER, user_id TEXT, role TEXT, PRIMARY KEY (workspace_id, user_id))`);
  db.query(
    `INSERT OR IGNORE INTO workspaces (id, name, slug) VALUES (?, 'Dev Workspace', 'dev')`,
  ).run(seed.workspaceId);
  db.query(
    `INSERT OR IGNORE INTO users (id, email, name, role, is_active) VALUES (?, ?, ?, ?, 1)`,
  ).run(seed.userId, seed.userEmail ?? "dev@localhost", seed.userName ?? "Dev Admin", "superuser");
  db.query(
    `INSERT OR IGNORE INTO workspace_members (workspace_id, user_id, role) VALUES (?, ?, ?)`,
  ).run(seed.workspaceId, seed.userId, seed.wsRole ?? "Admin");
}

/** SQLite is flat — no schemas. A named-schema plugin's `packages.foo` (or the
 *  kernel `public.workspaces`) must collapse to the bare table `foo`/`workspaces`,
 *  matching the unqualified names the data surface uses under its no-op'd
 *  search_path. Strips `<schema>.` and `"<schema>".` for the plugin's declared
 *  schemas plus `public`. */
function makeSchemaStripper(schemas: readonly string[]): (sql: string) => string {
  const names = ["public", ...schemas].filter(Boolean);
  if (names.length === 0) return (s) => s;
  const alt = names.map((n) => n.replace(/[.*+?^${}()|[\]\\]/g, "\\$&")).join("|");
  const re = new RegExp(`"?\\b(${alt})\\b"?\\.`, "gi");
  return (sql) => sql.replace(re, "");
}

export function makeSqlitePool(
  filename = ":memory:",
  seed?: KernelStubSeed,
  schemas: readonly string[] = [],
): SqlitePool {
  const db = new Database(filename);
  db.exec("PRAGMA foreign_keys = ON");
  if (seed) seedKernelStub(db, seed);
  const stripSchema = makeSchemaStripper(schemas);
  // Serialize: one statement/transaction span at a time over the single handle.
  let tail: Promise<unknown> = Promise.resolve();
  function serialize<T>(fn: () => T): Promise<T> {
    const run = tail.then(fn, fn);
    tail = run.catch(() => {});
    return run;
  }

  async function query<R>(
    rawText: string,
    params: readonly unknown[] = [],
  ): Promise<QueryResult<R>> {
    const text = stripSchema(rawText);
    return serialize(() => {
      const translated = translate<R>(db, text, params);
      return translated ?? runReal<R>(db, text, params);
    });
  }

  return {
    query,
    async connect(): Promise<SqliteClient> {
      // One shared handle; the client just forwards. release() is a no-op (nothing
      // to return to a pool), but the per-call serialization still applies.
      return { query, release() {} };
    },
    async end() {
      await serialize(() => db.close());
    },
    on() {
      // pg's pool.on('error', …) — no idle-client errors on a single sqlite handle.
    },
  };
}