import { Database } from "bun:sqlite";
import { readFileSync } from "node:fs";

type Value = string | number | null;

/** A minimal D1 stand-in over bun:sqlite: prepare/bind/run/first/all and transactional batch. */
export function fakeD1() {
  const db = new Database(":memory:");
  db.exec(`
    CREATE TABLE accounts (id TEXT PRIMARY KEY, name TEXT NOT NULL, kind TEXT NOT NULL);
    CREATE TABLE users (account_id TEXT PRIMARY KEY, email_verified_at INTEGER, github_id TEXT, is_admin INTEGER NOT NULL DEFAULT 0);
    CREATE TABLE org_members (org_id TEXT, user_id TEXT, role TEXT, created_at INTEGER, PRIMARY KEY (org_id, user_id));
    CREATE TABLE repos (id TEXT PRIMARY KEY, owner_id TEXT NOT NULL, name TEXT NOT NULL);
    CREATE TABLE workflow_runs (id TEXT PRIMARY KEY, repo_id TEXT NOT NULL, source_repo_id TEXT NOT NULL);
    CREATE TABLE job_steps (job_id TEXT, conclusion TEXT);
    CREATE TABLE access_tokens (id TEXT PRIMARY KEY, user_id TEXT NOT NULL);
    CREATE TABLE mcp_token_agents (token_id TEXT PRIMARY KEY, authorized_by TEXT NOT NULL);
    CREATE TABLE notifications (id TEXT PRIMARY KEY, user_id TEXT, repo_id TEXT, reason TEXT, title TEXT, url TEXT, read_at INTEGER, created_at INTEGER);
    CREATE TABLE lfs_usage (owner_id TEXT PRIMARY KEY, storage_bytes INTEGER, bandwidth_period TEXT, bandwidth_bytes INTEGER);
    CREATE TABLE action_caches (repo_id TEXT, size INTEGER, r2_key TEXT);
    CREATE TABLE sites (id TEXT PRIMARY KEY, owner_id TEXT, custom_domain TEXT);
    CREATE TABLE site_deployments (site_id TEXT, status TEXT, total_bytes INTEGER);
    CREATE TABLE releases (id TEXT PRIMARY KEY, repo_id TEXT);
    CREATE TABLE release_assets (release_id TEXT, size INTEGER);
    CREATE TABLE attachments (repo_id TEXT, size INTEGER);
    CREATE TABLE scope_locators (kind TEXT NOT NULL, key TEXT NOT NULL, scope_id TEXT NOT NULL, PRIMARY KEY (kind, key));
  `);
  for (const file of [
    "0015_metering.sql",
    "0031_billing.sql",
    "0033_credit_pools.sql",
    "0036_plan_override.sql",
    "0041_custom_domain_addon.sql",
    "0042_hosting_cost_controls.sql",
    "0044_account_disable.sql",
    "0047_billing_release.sql",
  ]) {
    db.exec(readFileSync(new URL(`../../../migrations/${file}`, import.meta.url), "utf8"));
  }

  function statement(sql: string, params: Value[] = []) {
    const meta = () => ({ changes: Number(db.query("SELECT changes() AS c").get()?.["c" as never] ?? 0) });
    return {
      bind: (...values: Value[]) => statement(sql, values),
      run: async () => {
        const result = db.query(sql).run(...params);
        return { meta: { changes: result.changes }, results: [] };
      },
      first: async <T>() => (db.query(sql).get(...params) as T | null) ?? null,
      all: async <T>() => ({ results: db.query(sql).all(...params) as T[], meta: meta() }),
      exec: () => db.query(sql).run(...params),
    };
  }

  return {
    db,
    binding: {
      prepare: (sql: string) => statement(sql),
      batch: async (statements: ReturnType<typeof statement>[]) => {
        const out: { meta: { changes: number }; results: unknown[] }[] = [];
        db.transaction(() => {
          for (const item of statements) out.push({ meta: { changes: item.exec().changes }, results: [] });
        })();
        return out;
      },
    },
  };
}