import type { Database } from "bun:sqlite";

const DDL = `
CREATE TABLE IF NOT EXISTS requests (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  timestamp TEXT NOT NULL,
  project TEXT,
  session_id TEXT,
  model TEXT,
  raw_input_tokens INTEGER NOT NULL DEFAULT 0,
  compressed_input_tokens INTEGER NOT NULL DEFAULT 0,
  raw_output_tokens INTEGER NOT NULL DEFAULT 0,
  cache_hit_input_tokens INTEGER NOT NULL DEFAULT 0,
  cache_creation_input_tokens INTEGER NOT NULL DEFAULT 0,
  latency_ms INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_requests_ts ON requests (timestamp);
CREATE INDEX IF NOT EXISTS idx_requests_proj ON requests (project);

CREATE TABLE IF NOT EXISTS saving_events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  request_id INTEGER NOT NULL REFERENCES requests(id) ON DELETE CASCADE,
  layer_id TEXT NOT NULL,
  kind TEXT NOT NULL,
  raw_bytes INTEGER NOT NULL,
  sent_bytes INTEGER NOT NULL,
  meta TEXT
);
CREATE INDEX IF NOT EXISTS idx_se_req ON saving_events (request_id);
CREATE INDEX IF NOT EXISTS idx_se_layer ON saving_events (layer_id);

CREATE INDEX IF NOT EXISTS idx_requests_session ON requests (session_id);

CREATE TABLE IF NOT EXISTS schema_version (version INTEGER PRIMARY KEY);
INSERT OR IGNORE INTO schema_version (version) VALUES (1);
`;

// Split DDL into one statement per db.run() — see "SQL DDL + Regex Conventions".
const DDL_STATEMENTS: readonly string[] = DDL
  .split(/;\s*\n/)
  .map((s) => s.trim())
  .filter((s) => s.length > 0);

function ensureProviderColumn(db: Database): void {
  const cols = db.query<{ name: string }, []>("PRAGMA table_info(requests)").all();
  if (!cols.some((c) => c.name === "provider")) {
    db.run("ALTER TABLE requests ADD COLUMN provider TEXT");
  }
}

function dropExpandedOutputTokensColumn(db: Database): void {
  const cols = db.query<{ name: string }, []>("PRAGMA table_info(requests)").all();
  if (cols.some((c) => c.name === "expanded_output_tokens")) {
    db.run("ALTER TABLE requests DROP COLUMN expanded_output_tokens");
  }
}

export function initStatsSchema(db: Database): void {
  // Under steady N=2 (Phase 3) both children open + init this shared DB at boot. The
  // journal_mode/DDL writes below take a write lock; without a busy timeout the loser
  // of that race throws SQLITE_BUSY and the child crashes. busy_timeout makes a blocked
  // writer wait (schema init is sub-ms) instead — the standard concurrent-writer fix.
  db.run("PRAGMA busy_timeout = 5000");
  db.run("PRAGMA journal_mode = WAL");
  db.run("PRAGMA synchronous = NORMAL");
  db.run("PRAGMA foreign_keys = ON");
  for (const stmt of DDL_STATEMENTS) db.run(stmt);
  ensureProviderColumn(db);
  dropExpandedOutputTokensColumn(db);
}

/** Alias for initStatsSchema — used by tests. */
export const ensureSchema = initStatsSchema;