/**
 * The ledger. One row per question the oracle has ever been asked — asking
 * again returns the same row, because the verdict cannot change — plus the
 * outcome a person recorded afterwards.
 */

import { Database } from "bun:sqlite";
import { mkdirSync } from "node:fs";
import { join } from "node:path";
import { normalize, verdictFor, type Outcome } from "./oracle.ts";

export interface Row {
  id: number;
  question: string;
  normalized: string;
  stance: string;
  confidence: number;
  auspice: string;
  asked_count: number;
  asked_at: string;
  outcome: Outcome;
  judged_at: string | null;
}

export function open(dataDir = process.env.FORGE_APP_DATA ?? "./data"): Database {
  mkdirSync(dataDir, { recursive: true });
  const db = new Database(join(dataDir, "oracle.db"));
  db.exec("PRAGMA journal_mode = WAL");
  db.exec(`CREATE TABLE IF NOT EXISTS verdict (
    id INTEGER PRIMARY KEY,
    question TEXT NOT NULL,
    normalized TEXT NOT NULL UNIQUE,
    stance TEXT NOT NULL,
    confidence INTEGER NOT NULL,
    auspice TEXT NOT NULL,
    asked_count INTEGER NOT NULL DEFAULT 1,
    asked_at TEXT NOT NULL DEFAULT (datetime('now')),
    outcome TEXT NOT NULL DEFAULT 'unjudged' CHECK (outcome IN ('unjudged','right','wrong')),
    judged_at TEXT
  )`);
  return db;
}

/**
 * Ask. A question the ledger already holds is not answered again: its row
 * comes back with one more ask against it, which is the whole conceit —
 * the oracle does not get a second opinion either.
 */
export function ask(db: Database, question: string): Row {
  const trimmed = question.trim().slice(0, 280);
  const key = normalize(trimmed);
  const existing = db
    .query<Row, [string]>("SELECT * FROM verdict WHERE normalized = ?1")
    .get(key);
  if (existing) {
    db.query("UPDATE verdict SET asked_count = asked_count + 1 WHERE id = ?1").run(
      existing.id,
    );
    return { ...existing, asked_count: existing.asked_count + 1 };
  }
  const verdict = verdictFor(trimmed);
  const inserted = db
    .query<Row, [string, string, string, number, string]>(
      `INSERT INTO verdict (question, normalized, stance, confidence, auspice)
       VALUES (?1, ?2, ?3, ?4, ?5) RETURNING *`,
    )
    .get(trimmed, key, verdict.stance, verdict.confidence, verdict.auspice);
  if (!inserted) throw new Error("oracle: the ledger refused a verdict");
  return inserted;
}

export function get(db: Database, id: number): Row | null {
  return db.query<Row, [number]>("SELECT * FROM verdict WHERE id = ?1").get(id);
}

export function recent(db: Database, limit = 8): Row[] {
  return db
    .query<Row, [number]>("SELECT * FROM verdict ORDER BY id DESC LIMIT ?1")
    .all(limit);
}

export type Filter = "all" | "unjudged" | "right" | "wrong";

export function isFilter(value: string): value is Filter {
  return value === "all" || value === "unjudged" || value === "right" || value === "wrong";
}

export function ledger(db: Database, filter: Filter = "all", limit = 200): Row[] {
  if (filter === "all")
    return db.query<Row, [number]>("SELECT * FROM verdict ORDER BY id DESC LIMIT ?1").all(limit);
  return db
    .query<Row, [string, number]>(
      "SELECT * FROM verdict WHERE outcome = ?1 ORDER BY id DESC LIMIT ?2",
    )
    .all(filter, limit);
}

/** Record how it turned out. `unjudged` takes a score back off the board. */
export function judge(db: Database, id: number, outcome: Outcome): Row | null {
  db.query<Row, [string, string | null, number]>(
    "UPDATE verdict SET outcome = ?1, judged_at = ?2 WHERE id = ?3",
  ).run(outcome, outcome === "unjudged" ? null : new Date().toISOString(), id);
  return get(db, id);
}

export function scored(db: Database): { confidence: number; outcome: string }[] {
  return db
    .query<{ confidence: number; outcome: string }, []>(
      "SELECT confidence, outcome FROM verdict",
    )
    .all();
}