/**
 * # ledger — the run / plan / lineage store over `bun:sqlite` (ADR-0006, ADR-0007)
 *
 * The durable ledger of graded runs. A {@link EpisodeReport} is an in-memory grading of one
 * `sim` {@link https://…|Instance} (ADR-0007: definition × mode × run); the ledger is where that
 * grading **lands** so it can be queried across time. Its whole reason to exist is ADR-0006's
 * "names are data": a plan's free-text **name is its lineage key**, so the same authored name run
 * on 40 days is one strategy with 40 instances — and `plan_instances.name` is the natural
 * aggregation key that `lineage()` and `leaderboard()` roll up.
 *
 * ## Determinism doctrine (ADR-0007 / RUNTIME §0)
 * A run's identity is a pure function of its inputs: `run_id = sha256(bus_sha256 + plans_sha256 +
 * fill_model + r_usd)`. Re-recording the same run is therefore an **idempotent upsert** (`INSERT OR
 * REPLACE`) — same inputs collapse to the same row, never a duplicate. Two records of one run leave
 * the store byte-identical.
 *
 * ## Honesty rules this module holds (ADR-0006)
 * - **Every grade stamps its judge.** `fill_model` is a first-class run column; comparisons across
 *   fill models are the caller's discipline, but the judge is always recorded.
 * - **Nothing is wall-clock.** `recorded_at` is a caller-injected `now` (never `Date.now()` inside
 *   the module), so a recorded run is reproducible and orderable by the caller's clock.
 * - **Fail-closed on version skew.** {@link openLedger} refuses (loudly) to open a database whose
 *   `user_version` is newer than this build knows — a future schema is never silently misread.
 *
 * The module is pure mapping + parameterized SQL: no string interpolation of values ever reaches a
 * statement, and the default database path is a caller concern (tests use `:memory:` / a temp file).
 */

import { Database } from "bun:sqlite";

import type { EpisodeReport } from "../session/sim.ts";
import { aggregatePlans, deriveRunId, plansSha256Of } from "./core.ts";
import type {
  LeaderboardFilter,
  LeaderboardRow,
  LineageInstance,
  ListRunsFilter,
  PlanInstanceRow,
  RecordOptions,
  RecordResult,
  RunRow,
  RunWithPlans,
} from "./core.ts";

// The storage-agnostic heart — run-identity hash, report→rows reduction, and the row/query types —
// lives in {@link ./core.ts} and is shared byte-for-byte with the Worker-portable {@link ./memory.ts}
// backend (kestrel-alw.17). Re-exported here so this module stays the durable ledger's single surface.
export { deriveRunId, aggregatePlans } from "./core.ts";
export type {
  AggregatedPlan,
  LeaderboardFilter,
  LeaderboardRow,
  LineageInstance,
  ListRunsFilter,
  OrderSummaryRow,
  PlanInstanceRow,
  RecordOptions,
  RecordResult,
  RunRow,
  RunWithPlans,
} from "./core.ts";

// The regenerable sim_runs + agent_configs projection (kestrel-5zl.9) — a QUERY-ONLY, PURE index over the
// finalized Blotters (a deterministic function of the source; no `bun:sqlite`, no stored state), surfaced
// additively here. It never touches the durable run/plan/lineage ledger above.
export {
  projectSimRuns,
  simRunById,
  runsByCell,
  runsByConfig,
  type SimRunRow,
  type AgentConfigRow,
  type ExcludedRow,
  type SimRunsIndex,
} from "./sim-runs.ts";

// ─────────────────────────────────────────────────────────────────────────────
// Schema
// ─────────────────────────────────────────────────────────────────────────────

/** The schema version this build writes and understands. Bumped only alongside a migration. A
 * database stamped higher than this is from a newer build → {@link openLedger} refuses it. */
export const SCHEMA_VERSION = 1;

const SCHEMA_SQL = `
CREATE TABLE IF NOT EXISTS runs (
  run_id             TEXT    PRIMARY KEY,
  recorded_at        INTEGER NOT NULL,
  session_date       TEXT    NOT NULL,
  mode               TEXT    NOT NULL,
  instruments        TEXT    NOT NULL,
  bus_sha256         TEXT    NOT NULL,
  plans_sha256       TEXT    NOT NULL,
  fill_model         TEXT    NOT NULL,
  calibrated         INTEGER NOT NULL,
  determinism_hash   TEXT    NOT NULL,
  r_usd              REAL    NOT NULL,
  realized_floor_usd REAL    NOT NULL,
  expected_usd       REAL    NOT NULL,
  premium_spent      REAL    NOT NULL,
  report_path        TEXT,
  bus_events         INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS plan_instances (
  run_id           TEXT    NOT NULL,
  name             TEXT    NOT NULL,
  canonical_sha256 TEXT    NOT NULL,
  final_state      TEXT    NOT NULL,
  outcome          TEXT,
  fired            INTEGER NOT NULL,
  orders           INTEGER NOT NULL,
  fills            INTEGER NOT NULL,
  realized_usd     REAL    NOT NULL,
  expected_usd     REAL    NOT NULL,
  PRIMARY KEY (run_id, name),
  FOREIGN KEY (run_id) REFERENCES runs(run_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS orders_summary (
  run_id        TEXT    NOT NULL,
  name          TEXT    NOT NULL,
  role          TEXT    NOT NULL,
  count         INTEGER NOT NULL,
  esc_stages_max INTEGER NOT NULL,
  reprice_count INTEGER NOT NULL,
  PRIMARY KEY (run_id, name, role),
  FOREIGN KEY (run_id) REFERENCES runs(run_id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_runs_session_date ON runs(session_date);
CREATE INDEX IF NOT EXISTS idx_runs_recorded_at ON runs(recorded_at);
CREATE INDEX IF NOT EXISTS idx_plan_instances_name ON plan_instances(name);
`;

// ─────────────────────────────────────────────────────────────────────────────
// Open
// ─────────────────────────────────────────────────────────────────────────────

/**
 * Open (creating if absent) a ledger database at `dbPath` and return the live {@link Database}.
 * Enables WAL, then reconciles `PRAGMA user_version`:
 * - `0` (fresh) → create the schema and stamp {@link SCHEMA_VERSION}.
 * - `SCHEMA_VERSION` → an existing store of this build; used as-is (schema is `IF NOT EXISTS`).
 * - `> SCHEMA_VERSION` → **refuse loudly**: a future schema is never silently misread (ADR-0006).
 *
 * `dbPath` defaults to nothing here — the path is a caller concern; pass `":memory:"` for a
 * throwaway store.
 */
export function openLedger(dbPath: string): Database {
  const db = new Database(dbPath);
  db.exec("PRAGMA journal_mode = WAL");
  db.exec("PRAGMA foreign_keys = ON");

  const version = currentUserVersion(db);
  if (version > SCHEMA_VERSION) {
    db.close();
    throw new Error(
      `ledger: database at ${JSON.stringify(dbPath)} is schema version ${version}, but this build only understands ${SCHEMA_VERSION} — refusing to open a future schema (fail-closed, ADR-0006)`,
    );
  }
  if (version === 0) {
    db.exec(SCHEMA_SQL);
    db.exec(`PRAGMA user_version = ${SCHEMA_VERSION}`);
  }
  return db;
}

/** Read `PRAGMA user_version` as a number (a fresh database reports `0`). */
function currentUserVersion(db: Database): number {
  const row = db.query<{ user_version: number }, []>("PRAGMA user_version").get();
  return row?.user_version ?? 0;
}

// ─────────────────────────────────────────────────────────────────────────────
// Record
// ─────────────────────────────────────────────────────────────────────────────

/**
 * Record one graded {@link EpisodeReport} into the ledger (one transaction, `INSERT OR REPLACE`
 * throughout so a re-record of the same run is a clean idempotent upsert — no duplicate rows). Pure
 * mapping: it reads the report shape (session / totals / plans / orders) and derives the run row,
 * per-plan lineage instances, and the lean per-plan × role order summary. Returns the run identity.
 */
export function record(db: Database, report: EpisodeReport, opts: RecordOptions): RecordResult {
  const plansSha256 = plansSha256Of(opts.plansText);
  const busSha256 = report.session.bus_sha256;
  const fillModel = report.session.fill_model;
  const runId = deriveRunId(busSha256, plansSha256, fillModel, opts.rUsd);

  // Per-plan aggregation over the report's orders, keyed by the lineage name.
  const perPlan = aggregatePlans(report);

  const tx = db.transaction(() => {
    db.query(
      `INSERT OR REPLACE INTO runs
         (run_id, recorded_at, session_date, mode, instruments, bus_sha256, plans_sha256,
          fill_model, calibrated, determinism_hash, r_usd, realized_floor_usd, expected_usd,
          premium_spent, report_path, bus_events)
       VALUES ($run_id, $recorded_at, $session_date, $mode, $instruments, $bus_sha256, $plans_sha256,
          $fill_model, $calibrated, $determinism_hash, $r_usd, $realized_floor_usd, $expected_usd,
          $premium_spent, $report_path, $bus_events)`,
    ).run({
      $run_id: runId,
      $recorded_at: opts.now,
      $session_date: report.session.date,
      $mode: report.session.mode,
      $instruments: report.session.instruments.join(","),
      $bus_sha256: busSha256,
      $plans_sha256: plansSha256,
      $fill_model: fillModel,
      $calibrated: opts.calibrated ? 1 : 0,
      $determinism_hash: report.session.determinism_hash,
      $r_usd: opts.rUsd,
      $realized_floor_usd: report.totals.realized_floor_usd,
      $expected_usd: report.totals.expected_usd,
      $premium_spent: report.totals.premium_spent,
      $report_path: opts.reportPath ?? null,
      $bus_events: report.session.bus_events,
    });

    // Re-recording an existing run must not leave stale child rows behind (a plan/role that
    // vanished between records). Clear this run's children, then re-insert from the report.
    db.query("DELETE FROM plan_instances WHERE run_id = $run_id").run({ $run_id: runId });
    db.query("DELETE FROM orders_summary WHERE run_id = $run_id").run({ $run_id: runId });

    const insertPlan = db.query(
      `INSERT INTO plan_instances
         (run_id, name, canonical_sha256, final_state, outcome, fired, orders, fills,
          realized_usd, expected_usd)
       VALUES ($run_id, $name, $canonical_sha256, $final_state, $outcome, $fired, $orders, $fills,
          $realized_usd, $expected_usd)`,
    );
    const insertRole = db.query(
      `INSERT INTO orders_summary (run_id, name, role, count, esc_stages_max, reprice_count)
       VALUES ($run_id, $name, $role, $count, $esc_stages_max, $reprice_count)`,
    );

    for (const p of perPlan) {
      insertPlan.run({
        $run_id: runId,
        $name: p.name,
        // Per-plan canonical text is not surfaced on the Report yet; the document-canonical
        // plans hash stands in (one authored document → one canonical hash per run).
        $canonical_sha256: plansSha256,
        $final_state: p.finalState,
        $outcome: p.outcome ?? null,
        $fired: p.fired ? 1 : 0,
        $orders: p.orders,
        $fills: p.fills,
        $realized_usd: p.realizedUsd,
        $expected_usd: p.expectedUsd,
      });
      for (const r of p.roles) {
        insertRole.run({
          $run_id: runId,
          $name: p.name,
          $role: r.role,
          $count: r.count,
          $esc_stages_max: r.escStagesMax,
          // Reprice is a session-wide tally on the Report, not per-plan; stamp the session value
          // on each plan/role row so the summary is self-contained without a runs join.
          $reprice_count: report.session.reprice_count,
        });
      }
    }
  });
  tx();

  return { runId, plansSha256 };
}

// ─────────────────────────────────────────────────────────────────────────────
// Query API (row/filter types live in ./core.ts and are re-exported at the top of this module)
// ─────────────────────────────────────────────────────────────────────────────

/** List runs recent-first (by injected `recorded_at`), narrowed by any of the given filters. Every
 * predicate is a bound parameter — no value is ever interpolated into SQL. */
export function listRuns(db: Database, filter: ListRunsFilter = {}): RunRow[] {
  const where: string[] = [];
  const params: Record<string, string> = {};
  if (filter.sessionDate !== undefined) {
    where.push("session_date = $session_date");
    params.$session_date = filter.sessionDate;
  }
  if (filter.mode !== undefined) {
    where.push("mode = $mode");
    params.$mode = filter.mode;
  }
  if (filter.fillModel !== undefined) {
    where.push("fill_model = $fill_model");
    params.$fill_model = filter.fillModel;
  }
  if (filter.lineage !== undefined) {
    where.push("run_id IN (SELECT run_id FROM plan_instances WHERE name = $lineage)");
    params.$lineage = filter.lineage;
  }
  const clause = where.length > 0 ? `WHERE ${where.join(" AND ")}` : "";
  return db
    .query<RunRow, [Record<string, string>]>(
      `SELECT * FROM runs ${clause} ORDER BY recorded_at DESC, run_id ASC`,
    )
    .all(params);
}

/** Fetch one run with its plan instances, or `undefined` if the run is unknown. */
export function getRun(db: Database, runId: string): RunWithPlans | undefined {
  const run = db
    .query<RunRow, [{ $run_id: string }]>("SELECT * FROM runs WHERE run_id = $run_id")
    .get({ $run_id: runId });
  if (run === null) return undefined;
  const plans = db
    .query<PlanInstanceRow, [{ $run_id: string }]>(
      "SELECT * FROM plan_instances WHERE run_id = $run_id ORDER BY name ASC",
    )
    .all({ $run_id: runId });
  return { run, plans };
}

/** The ADR-0006 lineage view: every instance of a plan `name` across runs, recent-first — the same
 * strategy authored many times, laid out over time with its run context. */
export function lineage(db: Database, name: string): LineageInstance[] {
  return db
    .query<LineageInstance, [{ $name: string }]>(
      `SELECT pi.run_id, pi.name, pi.canonical_sha256, pi.final_state, pi.outcome, pi.fired,
              pi.orders, pi.fills, pi.realized_usd, pi.expected_usd,
              r.recorded_at, r.session_date, r.mode, r.fill_model
         FROM plan_instances pi
         JOIN runs r ON r.run_id = pi.run_id
        WHERE pi.name = $name
        ORDER BY r.recorded_at DESC, pi.run_id ASC`,
    )
    .all({ $name: name });
}

/** Per-lineage aggregate across all recorded instances, ordered by `realized_sum` desc — the
 * standing leaderboard over the lineage key (ADR-0006). `runs` counts distinct runs; sums/averages
 * are over the matching plan instances. */
export function leaderboard(db: Database, filter: LeaderboardFilter = {}): LeaderboardRow[] {
  const where: string[] = [];
  const params: Record<string, string | number> = {};
  if (filter.mode !== undefined) {
    where.push("r.mode = $mode");
    params.$mode = filter.mode;
  }
  if (filter.since !== undefined) {
    where.push("r.recorded_at >= $since");
    params.$since = filter.since;
  }
  const clause = where.length > 0 ? `WHERE ${where.join(" AND ")}` : "";
  return db
    .query<LeaderboardRow, [Record<string, string | number>]>(
      `SELECT pi.name AS name,
              COUNT(DISTINCT pi.run_id) AS runs,
              SUM(pi.fired) AS fired,
              SUM(pi.fills) AS fills,
              SUM(pi.realized_usd) AS realized_sum,
              SUM(pi.expected_usd) AS expected_sum,
              AVG(pi.realized_usd) AS realized_avg,
              AVG(pi.expected_usd) AS expected_avg
         FROM plan_instances pi
         JOIN runs r ON r.run_id = pi.run_id
         ${clause}
        GROUP BY pi.name
        ORDER BY realized_sum DESC, pi.name ASC`,
    )
    .all(params);
}