import { Database } from "bun:sqlite";
import type { ProjectConfig, TestRunner, ProjectRow } from "./a31_project_config.ts";
import type { SxDocument, SxDocumentSummary } from "./a31_sxdoc.ts";
import { SX_DOC_VERSION } from "./a31_sxdoc.ts";

const DB_PATH = process.env.TDD_DB_PATH ?? ":memory:";

let db: Database | null = null;

const getDb = (): Database => {
  if (db) return db;
  db = new Database(DB_PATH, { create: true });
  db.exec(`
    CREATE TABLE IF NOT EXISTS runs (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      owner TEXT NOT NULL,
      repo TEXT NOT NULL,
      head_sha TEXT NOT NULL,
      judged_at INTEGER NOT NULL,
      verdict_json TEXT NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_runs_owner_repo
      ON runs(owner, repo, judged_at DESC);

    CREATE TABLE IF NOT EXISTS projects (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      registered_by TEXT NOT NULL,
      repo_owner TEXT NOT NULL,
      repo_name TEXT NOT NULL,
      test_runner TEXT NOT NULL DEFAULT 'none',
      tracked_branches TEXT NOT NULL,
      display_name TEXT,
      team TEXT,
      registered_at INTEGER NOT NULL,
      status TEXT NOT NULL DEFAULT 'active',
      UNIQUE(repo_owner, repo_name)
    );
    CREATE INDEX IF NOT EXISTS idx_projects_registered_by
      ON projects(registered_by);

    CREATE TABLE IF NOT EXISTS sx_documents (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      slug TEXT NOT NULL,
      type TEXT NOT NULL,
      title TEXT NOT NULL,
      doc_json TEXT NOT NULL,
      doc_version INTEGER NOT NULL,
      hash TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT 'published',
      primary_tag TEXT,
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL,
      UNIQUE(slug, type)
    );
    CREATE INDEX IF NOT EXISTS idx_sx_documents_type_updated
      ON sx_documents(type, updated_at DESC);
  `);

  // Note: a `proposals` table existed in earlier versions of this CMS
  // for queueing non-admin edit submissions and recording admin
  // direct-writes for audit. Both roles are now served by Forgejo:
  // admin edits become real commits via c14_forgejo.commitFile, and
  // non-admin proposals are out of scope until they become Forgejo
  // PRs. Legacy data on existing volumes is left untouched (drops
  // would throw on volumes from before the proposals tabel existed
  // anyway). New deployments simply never create the table.

  return db;
};

export type Mode = "strict" | "pragmatic" | "learning";

export interface StepVerdict {
  stepId: string;
  redSha: string | null;
  greenSha: string | null;
  redFailed: boolean | null;
  greenPassed: boolean | null;
  // Whether the kata's authoritative hidden tests pass against the agent's
  // implementation at the green commit. null when no hidden tests exist
  // for the step (unknown kata, or step not registered with the spec).
  hiddenPassed: boolean | null;
  status:
    | "verified"
    | "discipline-only"
    | "no-green"
    | "red-did-not-fail"
    | "green-did-not-pass"
    | "hidden-tests-failed"
    | "test-deleted"
    // Trace-only mode: tests not executed, only commit discipline checked.
    // Used when test_runner: "none" — language-agnostic, useful as a
    // CI gate on real projects where Bun can't run the test suite.
    | "trace-verified"
    | "trace-tests-shrunk";
  scoreDelta: number;
  // Coach-style explanation of the verdict — what happened, why the score
  // is what it is, and (when relevant) how to improve next time.
  explanation: string;
}

export interface RefactorVerdict {
  sha: string;
  stepId: string | null;
  testsPassed: boolean;
  scoreDelta: number;
  explanation: string;
}

export interface Verdict {
  headSha: string;
  mode: Mode;
  steps: StepVerdict[];
  refactors: RefactorVerdict[];
  totalScore: number;
  judgedAt: number;
}

export const saveRun = (owner: string, repo: string, verdict: Verdict): void => {
  getDb().run(
    `INSERT INTO runs (owner, repo, head_sha, judged_at, verdict_json) VALUES (?, ?, ?, ?, ?)`,
    [owner, repo, verdict.headSha, verdict.judgedAt, JSON.stringify(verdict)],
  );
};

export const latestRun = (owner: string, repo: string): Verdict | null => {
  const row = getDb()
    .query<{ verdict_json: string }, [string, string]>(
      `SELECT verdict_json FROM runs WHERE owner = ? AND repo = ? ORDER BY judged_at DESC LIMIT 1`,
    )
    .get(owner, repo);
  if (!row) return null;
  return JSON.parse(row.verdict_json) as Verdict;
};

// ProjectRow is now defined in Layer 0 (c31_project_config) alongside
// the rest of the project-config types, so c51 render code can
// reference it without importing from Layer 2.

interface ProjectDbRow {
  id: number;
  registered_by: string;
  repo_owner: string;
  repo_name: string;
  test_runner: string;
  tracked_branches: string;
  display_name: string | null;
  team: string | null;
  registered_at: number;
  status: string;
}

const rowToProject = (r: ProjectDbRow): ProjectRow => ({
  id: r.id,
  registeredBy: r.registered_by,
  repoOwner: r.repo_owner,
  repoName: r.repo_name,
  testRunner: (r.test_runner === "bun" ? "bun" : "none") as TestRunner,
  trackedBranches: JSON.parse(r.tracked_branches) as string[],
  displayName: r.display_name,
  team: r.team,
  registeredAt: r.registered_at,
  status: r.status === "paused" ? "paused" : "active",
});

// Inserts or updates a project. Re-registering the same repo refreshes
// its config (test_runner, tracked_branches, display_name, team) without
// duplicating the row. Returns the stored project.
export const upsertProject = (
  registeredBy: string,
  repoOwner: string,
  repoName: string,
  config: ProjectConfig,
): ProjectRow => {
  const now = Date.now();
  const branches = JSON.stringify(config.tracked_branches);
  const display = config.display_name ?? null;
  const team = config.team ?? null;
  getDb().run(
    `INSERT INTO projects (registered_by, repo_owner, repo_name, test_runner, tracked_branches, display_name, team, registered_at, status)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'active')
     ON CONFLICT(repo_owner, repo_name) DO UPDATE SET
       test_runner = excluded.test_runner,
       tracked_branches = excluded.tracked_branches,
       display_name = excluded.display_name,
       team = excluded.team,
       status = 'active'`,
    [registeredBy, repoOwner, repoName, config.test_runner, branches, display, team, now],
  );
  const row = getDb()
    .query<ProjectDbRow, [string, string]>(
      `SELECT * FROM projects WHERE repo_owner = ? AND repo_name = ?`,
    )
    .get(repoOwner, repoName);
  if (!row) throw new Error("project upsert returned no row");
  return rowToProject(row);
};

export const getProject = (repoOwner: string, repoName: string): ProjectRow | null => {
  const row = getDb()
    .query<ProjectDbRow, [string, string]>(
      `SELECT * FROM projects WHERE repo_owner = ? AND repo_name = ?`,
    )
    .get(repoOwner, repoName);
  return row ? rowToProject(row) : null;
};

export const listActiveProjects = (): ProjectRow[] => {
  const rows = getDb()
    .query<ProjectDbRow, []>(
      `SELECT * FROM projects WHERE status = 'active' ORDER BY registered_at DESC`,
    )
    .all();
  return rows.map(rowToProject);
};

// ─── sx_documents ────────────────────────────────────────────────────────
// Canonical store for sxdoc-backed content (pages + posts). Sibling git
// commits in content/{slug}.{md,sxdoc.json} mirror this table for audit;
// the SQLite row is the source of truth (canon B, locked in plan.md).

export interface SxDocumentRow {
  id: number;
  slug: string;
  type: "page" | "post";
  title: string;
  doc: SxDocument;
  status: "published" | "draft";
  primaryTag: string | null;
  createdAt: number;
  updatedAt: number;
}

// SxDocumentSummary is the public summary shape; defined in Layer 0
// (c31_sxdoc) so render code can reference it without crossing the
// SAMA v2 import direction.

interface SxDocumentDbRow {
  id: number;
  slug: string;
  type: string;
  title: string;
  doc_json: string;
  doc_version: number;
  hash: string;
  status: string;
  primary_tag: string | null;
  created_at: number;
  updated_at: number;
}

interface SxDocumentSummaryDbRow {
  id: number;
  slug: string;
  type: string;
  title: string;
  status: string;
  primary_tag: string | null;
  updated_at: number;
}

const hashDoc = (json: string): string => {
  const h = new Bun.CryptoHasher("sha1");
  h.update(json);
  return h.digest("hex").slice(0, 16);
};

const rowToSxDocument = (r: SxDocumentDbRow): SxDocumentRow => ({
  id: r.id,
  slug: r.slug,
  type: r.type === "post" ? "post" : "page",
  title: r.title,
  doc: JSON.parse(r.doc_json) as SxDocument,
  status: r.status === "draft" ? "draft" : "published",
  primaryTag: r.primary_tag,
  createdAt: r.created_at,
  updatedAt: r.updated_at,
});

// Upsert a sxdoc keyed by (slug, type). created_at is preserved on
// updates so we can sort by first-publish elsewhere if needed.
export const saveDocument = (input: {
  slug: string;
  type: "page" | "post";
  title: string;
  doc: SxDocument;
  status?: "published" | "draft";
  primaryTag?: string | null;
}): void => {
  const now = Date.now();
  const json = JSON.stringify(input.doc);
  const hash = hashDoc(json);
  const status = input.status ?? "published";
  const primaryTag = input.primaryTag ?? null;
  getDb().run(
    `INSERT INTO sx_documents
       (slug, type, title, doc_json, doc_version, hash, status, primary_tag, created_at, updated_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
     ON CONFLICT(slug, type) DO UPDATE SET
       title = excluded.title,
       doc_json = excluded.doc_json,
       doc_version = excluded.doc_version,
       hash = excluded.hash,
       status = excluded.status,
       primary_tag = excluded.primary_tag,
       updated_at = excluded.updated_at`,
    [input.slug, input.type, input.title, json, SX_DOC_VERSION, hash, status, primaryTag, now, now],
  );
};

export const loadDocument = (slug: string, type: "page" | "post"): SxDocumentRow | null => {
  const row = getDb()
    .query<SxDocumentDbRow, [string, string]>(
      `SELECT * FROM sx_documents WHERE slug = ? AND type = ?`,
    )
    .get(slug, type);
  return row ? rowToSxDocument(row) : null;
};

export const deleteDocument = (slug: string, type: "page" | "post"): number => {
  const r = getDb().run(
    `DELETE FROM sx_documents WHERE slug = ? AND type = ?`,
    [slug, type],
  );
  return r.changes;
};

// Summary rows for the admin list / archive pages. Excludes doc_json so
// listing a thousand documents doesn't drag a megabyte of JSON through
// the query layer.
export const listDocuments = (filter: {
  type?: "page" | "post";
  status?: "published" | "draft";
} = {}): SxDocumentSummary[] => {
  const where: string[] = [];
  const params: string[] = [];
  if (filter.type) { where.push("type = ?"); params.push(filter.type); }
  if (filter.status) { where.push("status = ?"); params.push(filter.status); }
  const whereClause = where.length ? `WHERE ${where.join(" AND ")}` : "";
  const rows = getDb()
    .query<SxDocumentSummaryDbRow, string[]>(
      `SELECT id, slug, type, title, status, primary_tag, updated_at
       FROM sx_documents ${whereClause}
       ORDER BY updated_at DESC`,
    )
    .all(...params);
  return rows.map((r) => ({
    id: r.id,
    slug: r.slug,
    type: r.type === "post" ? "post" : "page",
    title: r.title,
    status: r.status === "draft" ? "draft" : "published",
    primaryTag: r.primary_tag,
    updatedAt: r.updated_at,
  }));
};

// Latest verdict per (owner, repo) across all agents — drives the
// leaderboard and the /agents index.
export const allLatestRuns = (): { owner: string; repo: string; verdict: Verdict }[] => {
  const rows = getDb()
    .query<{ owner: string; repo: string; verdict_json: string }, []>(
      `SELECT owner, repo, verdict_json FROM runs r1
       WHERE judged_at = (
         SELECT MAX(judged_at) FROM runs r2
         WHERE r2.owner = r1.owner AND r2.repo = r1.repo
       )`,
    )
    .all();
  return rows.map((r) => ({
    owner: r.owner,
    repo: r.repo,
    verdict: JSON.parse(r.verdict_json) as Verdict,
  }));
};