import { readFileSync } from "node:fs";
import { describe, expect, it, vi } from "vitest";
import {
  basis,
  BASIS_CODES,
  buildAuditRecord,
  buildEnvelope,
  decisionExecute,
  decisionRefuse,
  recordAggregateSnapshot,
  recordAuthoritySnapshot,
  refuse,
  verifyAuditRecord,
} from "@adjudicate/core";
import {
  INSERT_AUDIT_SQL,
  auditInsertParams,
  createPostgresSink,
  partitionMonthOf,
  recordToRow,
  type PostgresWriter,
} from "../src/postgres-sink.js";
import { rowToRecord } from "../src/replay.js";
import { normalizeTimestamptz } from "../src/pg-types.js";

function record(overrides: { decision?: "EXECUTE" | "REFUSE" } = {}) {
  const env = buildEnvelope({
    kind: "order.submit",
    payload: { sku: "X", qty: 1 },
    actor: { principal: "llm", sessionId: "s-1" },
    taint: "TRUSTED",
    nonce: "n-test", createdAt: "2026-04-23T12:00:00.000Z",
  });
  const dec =
    overrides.decision === "REFUSE"
      ? decisionRefuse(refuse("SECURITY", "x", "y", "operator detail"), [
          basis("auth", BASIS_CODES.auth.SCOPE_INSUFFICIENT),
        ])
      : decisionExecute([
          basis("state", BASIS_CODES.state.TRANSITION_VALID),
          basis("auth", BASIS_CODES.auth.SCOPE_SUFFICIENT),
        ]);
  return buildAuditRecord({
    envelope: env,
    decision: dec,
    durationMs: 7,
    resourceVersion: "v3",
    at: "2026-04-23T12:00:01.000Z",
  });
}

describe("partitionMonthOf", () => {
  it("extracts YYYY-MM from ISO-8601", () => {
    expect(partitionMonthOf("2026-04-23T12:00:00.000Z")).toBe("2026-04");
    expect(partitionMonthOf("2025-12-31T23:59:59.999Z")).toBe("2025-12");
  });

  it("accepts all valid months 01-12", () => {
    for (let m = 1; m <= 12; m++) {
      const mm = String(m).padStart(2, "0");
      expect(partitionMonthOf(`2026-${mm}-01T00:00:00.000Z`)).toBe(`2026-${mm}`);
    }
  });

  it("throws on an unparseable timestamp (no YYYY-MM prefix)", () => {
    // Previously silently fell back to new Date() — now deterministically throws.
    expect(() => partitionMonthOf("2026/04/23")).toThrow("partitionMonthOf: unparseable timestamp");
    expect(() => partitionMonthOf("")).toThrow("partitionMonthOf: unparseable timestamp");
    expect(() => partitionMonthOf("not-a-date")).toThrow("partitionMonthOf: unparseable timestamp");
  });

  it("throws on month 00 (invalid)", () => {
    expect(() => partitionMonthOf("2026-00-15T00:00:00.000Z")).toThrow(
      "partitionMonthOf: invalid month 00",
    );
  });

  it("throws on month 13 (invalid)", () => {
    expect(() => partitionMonthOf("2026-13-01T00:00:00.000Z")).toThrow(
      "partitionMonthOf: invalid month 13",
    );
  });

  it("throws on month 99 (impossible)", () => {
    expect(() => partitionMonthOf("2026-99-01T00:00:00.000Z")).toThrow(
      "partitionMonthOf: invalid month 99",
    );
  });
});

describe("recordToRow", () => {
  it("flattens envelope + decision into row shape", () => {
    const r = record();
    const row = recordToRow(r);
    expect(row.intent_hash).toBe(r.intentHash);
    expect(row.session_id).toBe("s-1");
    expect(row.kind).toBe("order.submit");
    expect(row.principal).toBe("llm");
    expect(row.taint).toBe("TRUSTED");
    expect(row.decision_kind).toBe("EXECUTE");
    expect(row.refusal_kind).toBe(null);
    expect(row.refusal_code).toBe(null);
    expect(row.resource_version).toBe("v3");
    expect(row.duration_ms).toBe(7);
    expect(row.partition_month).toBe("2026-04");
    expect(row.recorded_at).toBe("2026-04-23T12:00:01.000Z");
  });

  it("includes refusal metadata when decision is REFUSE", () => {
    const r = record({ decision: "REFUSE" });
    const row = recordToRow(r);
    expect(row.decision_kind).toBe("REFUSE");
    expect(row.refusal_kind).toBe("SECURITY");
    expect(row.refusal_code).toBe("x");
  });

  it("flattens decision_basis to category:code strings", () => {
    const r = record();
    const row = recordToRow(r);
    expect(row.decision_basis).toEqual([
      "state:transition_valid",
      "auth:scope_sufficient",
    ]);
  });

  // DataReviewer-012: `decision_basis` (TEXT[]) and `decision_jsonb.basis`
  // are two on-disk encodings of the same data. The column is the indexed,
  // queryable projection; the JSON is the structured source the replay
  // reader (rowToRecord) actually parses back via `decision.basis`. Since
  // recordToRow writes them from independent expressions
  // (`record.decision_basis.map(...)` vs `JSON.stringify(record.decision)`),
  // a future edit could let them drift. This invariant pins them in sync at
  // write time — the least-destructive guard (no column drop, no schema
  // change) called for by the audit.
  describe("decision_basis column ↔ decision_jsonb.basis invariant", () => {
    function assertBasisInSync(r: ReturnType<typeof record>) {
      const row = recordToRow(r);
      const jsonbBasis = (
        JSON.parse(row.decision_jsonb) as {
          basis: { category: string; code: string }[];
        }
      ).basis;
      const flattenedFromJsonb = jsonbBasis.map((b) => `${b.category}:${b.code}`);
      // The flattened TEXT[] column must be exactly the flattening of the
      // basis embedded in decision_jsonb — same entries, same order.
      expect(row.decision_basis).toEqual(flattenedFromJsonb);
    }

    it("EXECUTE decision: column equals flatten(decision_jsonb.basis)", () => {
      assertBasisInSync(record());
    });

    it("REFUSE decision: column equals flatten(decision_jsonb.basis)", () => {
      assertBasisInSync(record({ decision: "REFUSE" }));
    });

    it("count and ordering match between the two encodings", () => {
      const r = record();
      const row = recordToRow(r);
      const jsonbBasis = (JSON.parse(row.decision_jsonb) as { basis: unknown[] })
        .basis;
      expect(row.decision_basis).toHaveLength(jsonbBasis.length);
      // Same length as the kernel's structured basis too (no silent drop).
      expect(row.decision_basis).toHaveLength(r.decision.basis.length);
    });

    // DataReviewer-012: full write→read round-trip. recordToRow writes the
    // TEXT[] projection; rowToRecord reconstructs decision_basis from JSONB
    // (decision.basis), NOT the TEXT[]. Pin that the column equals the
    // flattening of the original basis AND that the reconstructed basis
    // flattens back to the same column — so the dual encoding stays in sync.
    it("decision_basis TEXT[] is consistent with decision_jsonb.basis on round-trip", () => {
      const sampleAuditRecord = record();
      const row = recordToRow(sampleAuditRecord);
      const expected = sampleAuditRecord.decision_basis.map(
        (b) => `${b.category}:${b.code}`,
      );
      expect(row.decision_basis).toEqual(expected);

      // After round-trip, rowToRecord reads from JSONB — must equal original.
      const reconstructed = rowToRecord(row);
      expect(
        reconstructed.decision_basis.map((b) => `${b.category}:${b.code}`),
      ).toEqual(row.decision_basis);
    });
  });

  it("envelope_jsonb is parseable JSON of the original envelope", () => {
    const r = record();
    const row = recordToRow(r);
    const parsed = JSON.parse(row.envelope_jsonb);
    expect(parsed.intentHash).toBe(r.intentHash);
    expect(parsed.payload.sku).toBe("X");
  });

  it("writes the correct nonce value to the row (TypeReviewer-005)", () => {
    // nonce is required on IntentEnvelope (v2+). recordToRow must write
    // the byte-identical value — removing the dead optional probe must not
    // change what gets persisted.
    const r = record();
    const row = recordToRow(r);
    expect(row.nonce).toBe(r.envelope.nonce);
    expect(row.nonce).toBe("n-test");
  });
});

describe("INSERT_AUDIT_SQL + auditInsertParams (symmetry with governance)", () => {
  // Declared column order — the single source of truth that
  // auditInsertParams must mirror positionally. Mirrors the governance-log
  // column-order test.
  const COLUMNS = [
    "intent_hash",
    "session_id",
    "kind",
    "principal",
    "taint",
    "decision_kind",
    "refusal_kind",
    "refusal_code",
    "decision_basis",
    "resource_version",
    "envelope_jsonb",
    "decision_jsonb",
    "recorded_at",
    "duration_ms",
    "partition_month",
    "record_version",
    "plan_jsonb",
    "nonce",
    "supersedes_jsonb",
    "kernel_identity_jsonb",
    "policy_version",
    "kernel_version",
    "audit_hash",
    "signature_jsonb",
    "metadata_jsonb",
    // 093 (T6): the inter-record chain link + the two recorded-snapshot columns
    // that complete the 033/052 read-path (so a snapshot-bearing record's
    // auditHash pre-image round-trips intact under 092 verify-on-read).
    "prev_audit_hash",
    "authority_snapshot_jsonb",
    "aggregate_snapshot_jsonb",
  ] as const;

  it("INSERT_AUDIT_SQL declares all 28 columns with $1..$28 and ON CONFLICT DO NOTHING", () => {
    expect(COLUMNS).toHaveLength(28);
    expect(INSERT_AUDIT_SQL).toContain(`(${COLUMNS.join(", ")})`);
    const placeholders = Array.from({ length: 28 }, (_, i) => `$${i + 1}`).join(", ");
    expect(INSERT_AUDIT_SQL).toContain(`VALUES (${placeholders})`);
    expect(INSERT_AUDIT_SQL).toContain("INSERT INTO intent_audit");
    expect(INSERT_AUDIT_SQL).toContain("ON CONFLICT (intent_hash, recorded_at) DO NOTHING");
  });

  it("auditInsertParams returns values in the exact column order of the INSERT SQL", () => {
    const row = recordToRow(v4Record());
    const params = auditInsertParams(row);
    expect(params).toHaveLength(COLUMNS.length);
    // Each param equals the row field named by the column at the same index.
    COLUMNS.forEach((col, i) => {
      expect(params[i]).toBe((row as Record<string, unknown>)[col]);
    });
  });

  it("a writer running INSERT_AUDIT_SQL with auditInsertParams sees the full v4 row", async () => {
    // Smoke test: the sink hands recordToRow output to the writer; an adopter
    // writer would bind auditInsertParams(row) to INSERT_AUDIT_SQL. Assert the
    // params carry the load-bearing v4 tamper-evidence fields (audit_hash et al)
    // so nothing is dropped before the DB sees it.
    const r = v4Record();
    const row = recordToRow(r);
    const params = auditInsertParams(row);
    // audit_hash is at index 22 (0-based) per COLUMNS.
    expect(params[COLUMNS.indexOf("audit_hash")]).toBe(r.auditHash);
    expect(params[COLUMNS.indexOf("policy_version")]).toBe("1.2.3");
    expect(params[COLUMNS.indexOf("kernel_version")]).toBe("1.1.0");
    expect(params[COLUMNS.indexOf("supersedes_jsonb")]).not.toBeNull();
  });
});

describe("PostgresSink — emit", () => {
  it("calls writer.insertAudit with the row", async () => {
    const writer: PostgresWriter = { insertAudit: vi.fn(async () => {}) };
    const sink = createPostgresSink({ writer });
    const r = record();
    await sink.emit(r);
    expect(writer.insertAudit).toHaveBeenCalledOnce();
    const calledRow = (writer.insertAudit as ReturnType<typeof vi.fn>).mock.calls[0]![0];
    expect(calledRow.intent_hash).toBe(r.intentHash);
  });

  it("rethrows on writer failure and invokes onError", async () => {
    const onError = vi.fn();
    const writer: PostgresWriter = {
      insertAudit: async () => {
        throw new Error("db down");
      },
    };
    const sink = createPostgresSink({ writer, onError });
    await expect(sink.emit(record())).rejects.toThrow("db down");
    expect(onError).toHaveBeenCalledOnce();
  });
});

describe("rowToRecord — round-trip with recordToRow", () => {
  it("recovers the original AuditRecord", () => {
    const original = record();
    const row = recordToRow(original);
    const recovered = rowToRecord(row);
    expect(recovered.intentHash).toBe(original.intentHash);
    expect(recovered.envelope.kind).toBe(original.envelope.kind);
    expect(recovered.envelope.taint).toBe(original.envelope.taint);
    expect(recovered.decision.kind).toBe(original.decision.kind);
    expect(recovered.decision.basis.length).toBe(original.decision.basis.length);
    expect(recovered.durationMs).toBe(original.durationMs);
    expect(recovered.at).toBe(original.at);
  });

  it("recovers REFUSE decisions including refusal payload", () => {
    const original = record({ decision: "REFUSE" });
    const row = recordToRow(original);
    const recovered = rowToRecord(row);
    expect(recovered.decision.kind).toBe("REFUSE");
    if (recovered.decision.kind !== "REFUSE") return;
    expect(recovered.decision.refusal.kind).toBe("SECURITY");
    expect(recovered.decision.refusal.code).toBe("x");
  });

  it("round-trips a v3 record carrying a plan snapshot", () => {
    const env = buildEnvelope({
      kind: "order.submit",
      payload: { sku: "X" },
      actor: { principal: "llm", sessionId: "s-1" },
      taint: "TRUSTED",
      nonce: "n-test", createdAt: "2026-04-23T12:00:00.000Z",
    });
    const original = buildAuditRecord({
      envelope: env,
      decision: decisionExecute([
        basis("state", BASIS_CODES.state.TRANSITION_VALID),
      ]),
      durationMs: 7,
      at: "2026-04-23T12:00:01.000Z",
      plan: {
        visibleReadTools: ["search", "view_cart"],
        allowedIntents: ["order.submit"],
      },
    });
    const row = recordToRow(original);
    expect(row.record_version).toBe(5);
    expect(row.plan_jsonb).not.toBeNull();
    const recovered = rowToRecord(row);
    expect(recovered.version).toBe(5);
    expect(recovered.plan).toBeDefined();
    expect(recovered.plan!.visibleReadTools).toEqual(["search", "view_cart"]);
    expect(recovered.plan!.allowedIntents).toEqual(["order.submit"]);
    expect(recovered.plan!.planFingerprint).toBe(original.plan!.planFingerprint);
  });

  it("round-trips a v3 record carrying a supersedes link", () => {
    const env = buildEnvelope({
      kind: "order.submit",
      payload: { sku: "X" },
      actor: { principal: "llm", sessionId: "s-1" },
      taint: "TRUSTED",
      nonce: "n-test", createdAt: "2026-04-23T12:00:00.000Z",
    });
    const original = buildAuditRecord({
      envelope: env,
      decision: decisionExecute([
        basis("state", BASIS_CODES.state.TRANSITION_VALID),
      ]),
      durationMs: 4,
      at: "2026-04-23T12:00:01.000Z",
      supersedes: {
        predecessorIntentHash: "a".repeat(64),
        predecessorAt: "2026-04-23T11:59:00.000Z",
        reason: "confirmation_resolved",
        token: "cr-tok-1",
      },
    });
    const row = recordToRow(original);
    expect(row.record_version).toBe(5);
    expect(row.supersedes_jsonb).not.toBeNull();
    const recovered = rowToRecord(row);
    expect(recovered.version).toBe(5);
    expect(recovered.supersedes).toEqual({
      predecessorIntentHash: "a".repeat(64),
      predecessorAt: "2026-04-23T11:59:00.000Z",
      reason: "confirmation_resolved",
      token: "cr-tok-1",
    });
  });

  it("treats a v2 row (record_version=2, NULL supersedes_jsonb) as a v2 record without supersedes", () => {
    const original = record();
    const row = {
      ...recordToRow(original),
      record_version: 2 as const,
      supersedes_jsonb: null,
    };
    const recovered = rowToRecord(row);
    expect(recovered.version).toBe(2);
    expect(recovered.supersedes).toBeUndefined();
  });

  it("treats record_version=1 with NULL plan_jsonb as a v1 record (back-compat)", () => {
    // Simulate a row written by a pre-v2 backfill: record_version=1, plan_jsonb=null.
    const original = record();
    const row = { ...recordToRow(original), record_version: 1 as const, plan_jsonb: null };
    const recovered = rowToRecord(row);
    expect(recovered.version).toBe(1);
    expect(recovered.plan).toBeUndefined();
  });

  it("round-trips a record produced by adjudicateAndAudit (P0-2 forward-compat smoke test)", async () => {
    /*
     * Post-P0-2 the Anthropic adapter emits AuditRecords through
     * `adjudicateAndAudit` instead of building them by hand. This smoke
     * test pins the property: records produced by the kernel-side
     * emission path round-trip through Postgres serialization with
     * byte-equality on the load-bearing fields. Without this, a future
     * kernel change that adds a field could silently break audit-postgres
     * before P1-2 (supersession) bumps AUDIT_RECORD_VERSION.
     */
    const { adjudicateAndAudit } = await import("@adjudicate/core/kernel");
    const { buildEnvelope } = await import("@adjudicate/core");
    type AuditRecord = import("@adjudicate/core").AuditRecord;
    type AuditSink = import("@adjudicate/core").AuditSink;

    const env = buildEnvelope({
      kind: "order.submit",
      payload: { sku: "X", qty: 1 },
      actor: { principal: "llm", sessionId: "s-roundtrip" },
      taint: "UNTRUSTED",
      nonce: "n-roundtrip-test",
      createdAt: "2026-05-13T12:00:00.000Z",
    });

    const captured: AuditRecord[] = [];
    const captureSink: AuditSink = {
      async emit(r) {
        captured.push(r);
      },
    };

    const policy = {
      stateGuards: [],
      authGuards: [],
      taint: { minimumFor: () => "UNTRUSTED" as const },
      business: [],
      default: "EXECUTE" as const,
    };

    await adjudicateAndAudit(env, {}, policy, {
      sink: captureSink,
      plan: () => ({
        visibleReadTools: ["search"],
        allowedIntents: ["order.submit"],
      }),
    });

    expect(captured).toHaveLength(1);
    const original = captured[0]!;

    const row = recordToRow(original);
    const recovered = rowToRecord(row);

    expect(recovered.intentHash).toBe(original.intentHash);
    expect(recovered.envelope.kind).toBe(original.envelope.kind);
    expect(recovered.envelope.taint).toBe(original.envelope.taint);
    expect(recovered.envelope.nonce).toBe(original.envelope.nonce);
    expect(recovered.envelope.actor.principal).toBe(original.envelope.actor.principal);
    expect(recovered.envelope.actor.sessionId).toBe(original.envelope.actor.sessionId);
    expect(recovered.decision.kind).toBe(original.decision.kind);
    expect(recovered.decision.basis.length).toBe(original.decision.basis.length);
    expect(recovered.version).toBe(original.version);
    expect(recovered.plan).toBeDefined();
    expect(recovered.plan!.visibleReadTools).toEqual(["search"]);
    expect(recovered.plan!.allowedIntents).toEqual(["order.submit"]);
    expect(recovered.plan!.planFingerprint).toBe(original.plan!.planFingerprint);
  });
});

// A v4 record carrying every optional field that participates in the
// auditHash pre-image: resourceVersion, supersedes, kernelIdentity,
// policyVersion, kernelVersion.
function v4Record() {
  const env = buildEnvelope({
    kind: "order.submit",
    payload: { sku: "X", qty: 2 },
    actor: { principal: "llm", sessionId: "s-v4" },
    taint: "UNTRUSTED",
    nonce: "n-v4",
    createdAt: "2026-05-13T12:00:00.000Z",
  });
  return buildAuditRecord({
    envelope: env,
    decision: decisionExecute([
      basis("state", BASIS_CODES.state.TRANSITION_VALID),
    ]),
    durationMs: 9,
    resourceVersion: "rv-1",
    at: "2026-05-13T12:00:01.000Z",
    policyVersion: "1.2.3",
    kernelVersion: "1.1.0",
    kernelIdentity: { id: "kernel-prod", version: "build-42" },
    supersedes: {
      predecessorIntentHash: "b".repeat(64),
      predecessorAt: "2026-05-13T11:59:00.000Z",
      reason: "confirmation_resolved",
      token: "cr-1",
    },
  });
}

describe("v4 tamper-evidence persistence (RC-K1)", () => {
  it("recordToRow populates every v4 column", () => {
    const r = v4Record();
    const row = recordToRow(r);
    expect(row.record_version).toBe(5);
    expect(row.audit_hash).toBe(r.auditHash);
    expect(row.policy_version).toBe("1.2.3");
    expect(row.kernel_version).toBe("1.1.0");
    expect(JSON.parse(row.kernel_identity_jsonb!)).toEqual({
      id: "kernel-prod",
      version: "build-42",
    });
    expect(row.supersedes_jsonb).not.toBeNull();
  });

  it("round-trips so verifyAuditRecord confirms the record is intact", () => {
    const r = v4Record();
    const recovered = rowToRecord(recordToRow(r));
    // auditHash survives the round-trip AND re-derivation matches it →
    // tamper-evidence preserved end-to-end (was missing_hash before RC-K1).
    expect(recovered.auditHash).toBe(r.auditHash);
    expect(verifyAuditRecord(recovered).verified).toBe(true);
    // Structural fidelity of the hashed optional fields.
    expect(recovered.policyVersion).toBe("1.2.3");
    expect(recovered.kernelVersion).toBe("1.1.0");
    expect(recovered.kernelIdentity).toEqual({
      id: "kernel-prod",
      version: "build-42",
    });
  });

  it("verifies a plain v4 record (no v3/v4 extras) after round-trip", () => {
    const recovered = rowToRecord(recordToRow(record()));
    expect(verifyAuditRecord(recovered).verified).toBe(true);
  });

  it("round-trips a signature without disturbing hash verification", () => {
    const signed = {
      ...v4Record(),
      signature: { keyId: "k1", alg: "ed25519", value: "sig-bytes" },
    };
    const recovered = rowToRecord(recordToRow(signed));
    expect(recovered.signature).toEqual({
      keyId: "k1",
      alg: "ed25519",
      value: "sig-bytes",
    });
    // signature is excluded from the hash pre-image, so verification holds.
    expect(verifyAuditRecord(recovered).verified).toBe(true);
  });

  it("flags tampering when a hashed field is mutated post-round-trip", () => {
    const recovered = rowToRecord(recordToRow(v4Record()));
    const tampered = { ...recovered, durationMs: recovered.durationMs + 1000 };
    expect(verifyAuditRecord(tampered).verified).toBe(false);
  });

  // DataReviewer-013 (option B): `record.at` is in the v4 auditHash pre-image,
  // and the real read path (audit-store.ts) coerces the driver's TIMESTAMPTZ
  // `recorded_at` through normalizeTimestamptz before rowToRecord. A wire-format
  // string returned verbatim would diverge from the hashed ISO `at` and trip a
  // false-positive tamper. These pin the faithful read-side round-trip.
  it("a Postgres wire-format recorded_at round-trips so verifyAuditRecord stays verified", () => {
    const r = record(); // at = "2026-04-23T12:00:01.000Z"
    const row = recordToRow(r);
    // postgres.js shape: space separator + `+00` offset, NOT canonical ISO.
    const driverRecordedAt = "2026-04-23 12:00:01+00";
    const coerced = {
      ...row,
      recorded_at: normalizeTimestamptz(driverRecordedAt, "intent_audit.recorded_at"),
    };
    const recovered = rowToRecord(coerced);
    expect(recovered.at).toBe(r.at);
    expect(verifyAuditRecord(recovered).verified).toBe(true);
  });

  it("a native Date recorded_at (node-postgres shape) also round-trips verified", () => {
    const r = record();
    const row = recordToRow(r);
    // node-postgres parses TIMESTAMPTZ into a native Date.
    const coerced = { ...row, recorded_at: normalizeTimestamptz(new Date(r.at)) };
    const recovered = rowToRecord(coerced);
    expect(recovered.at).toBe(r.at);
    expect(verifyAuditRecord(recovered).verified).toBe(true);
  });
});

// ─── 093 (T6/T7): inter-record chain link + recorded-snapshot read-path ──────
// prev_audit_hash is EXCLUDED from the auditHash pre-image (round-trips, no
// false-tamper). authority_snapshot_jsonb / aggregate_snapshot_jsonb ARE part of
// the pre-image: this block proves the new persist + rehydrate closes the 092-F1
// false-tamper hazard a snapshot-bearing record would otherwise hit on read.
describe("093 — prev_audit_hash + recorded-snapshot persistence", () => {
  function chainedRecord() {
    const env = buildEnvelope({
      kind: "order.submit",
      payload: { sku: "Y", qty: 3 },
      actor: { principal: "llm", sessionId: "s-093" },
      taint: "UNTRUSTED",
      nonce: "n-093",
      createdAt: "2026-06-18T12:00:00.000Z",
    });
    return buildAuditRecord({
      envelope: env,
      decision: decisionExecute([basis("state", BASIS_CODES.state.TRANSITION_VALID)]),
      durationMs: 4,
      at: "2026-06-18T12:00:01.000Z",
      prevAuditHash: "a".repeat(64),
      authoritySnapshot: recordAuthoritySnapshot({
        edges: [
          {
            principal: "user:42",
            relationship: "owns",
            resource: "acct:7",
            permits: { actions: ["transfer"], limits: { perTx: 1000 } },
          },
        ],
      }),
      aggregateSnapshot: recordAggregateSnapshot({
        windows: { "acct:7|daily": 250 },
        at: "2026-06-18T11:59:00.000Z",
      }),
    });
  }

  it("recordToRow binds prev_audit_hash + both snapshot columns", () => {
    const r = chainedRecord();
    const row = recordToRow(r);
    expect(row.prev_audit_hash).toBe("a".repeat(64));
    expect(row.authority_snapshot_jsonb).not.toBeNull();
    expect(row.aggregate_snapshot_jsonb).not.toBeNull();
    expect(JSON.parse(row.authority_snapshot_jsonb!)).toEqual(r.authoritySnapshot);
    expect(JSON.parse(row.aggregate_snapshot_jsonb!)).toEqual(r.aggregateSnapshot);
  });

  it("auditInsertParams binds the 3 new columns in declared order", () => {
    const row = recordToRow(chainedRecord());
    const params = auditInsertParams(row);
    expect(params).toHaveLength(28);
    // Indices 25,26,27 (0-based) per the INSERT column list.
    expect(params[25]).toBe(row.prev_audit_hash);
    expect(params[26]).toBe(row.authority_snapshot_jsonb);
    expect(params[27]).toBe(row.aggregate_snapshot_jsonb);
  });

  it("round-trips so prevAuditHash + snapshots are preserved AND verifyAuditRecord stays verified (092-F1 false-tamper closure)", () => {
    const r = chainedRecord();
    const recovered = rowToRecord(recordToRow(r));
    expect(recovered.prevAuditHash).toBe("a".repeat(64));
    expect(recovered.authoritySnapshot).toEqual(r.authoritySnapshot);
    expect(recovered.aggregateSnapshot).toEqual(r.aggregateSnapshot);
    expect(recovered.auditHash).toBe(r.auditHash);
    // THE load-bearing assertion: BEFORE 093, the snapshots were not persisted →
    // rowToRecord omitted them → re-derived auditHash differed → verify-on-read
    // FALSELY reported tampered. Now they round-trip, so the record stays intact.
    expect(verifyAuditRecord(recovered).verified).toBe(true);
  });

  it("a record WITHOUT a chain link / snapshots round-trips with NULL columns and stays verified (genesis hash-stable)", () => {
    const r = record(); // no prevAuditHash, no snapshots
    const row = recordToRow(r);
    expect(row.prev_audit_hash).toBeNull();
    expect(row.authority_snapshot_jsonb).toBeNull();
    expect(row.aggregate_snapshot_jsonb).toBeNull();
    const recovered = rowToRecord(row);
    expect(recovered.prevAuditHash).toBeUndefined();
    expect(recovered.authoritySnapshot).toBeUndefined();
    expect(recovered.aggregateSnapshot).toBeUndefined();
    expect(verifyAuditRecord(recovered).verified).toBe(true);
  });
});

describe("migration 012 — additive prev_audit_hash + snapshot columns", () => {
  const sql = readFileSync(
    new URL("../migrations/012-add-prev-audit-hash.sql", import.meta.url),
    "utf-8",
  );

  it("adds prev_audit_hash + both recorded-snapshot columns, all IF NOT EXISTS", () => {
    expect(sql).toMatch(/ADD COLUMN IF NOT EXISTS prev_audit_hash TEXT/);
    expect(sql).toMatch(/ADD COLUMN IF NOT EXISTS authority_snapshot_jsonb JSONB/);
    expect(sql).toMatch(/ADD COLUMN IF NOT EXISTS aggregate_snapshot_jsonb JSONB/);
  });

  it("is purely additive — touches NO constraint, index, or arbiter (009/010 intact)", () => {
    // The migration must not DROP/ADD a CHECK or touch the UNIQUE arbiter, or it
    // re-introduces the 42P10/23514 activation blockers (plan §7 migration risk).
    expect(sql).not.toMatch(/DROP CONSTRAINT/);
    expect(sql).not.toMatch(/ADD CONSTRAINT/);
    expect(sql).not.toMatch(/CREATE\s+(UNIQUE\s+)?INDEX/);
    expect(sql).not.toMatch(/DROP INDEX/);
  });
});

describe("migration 008 — record_version CHECK (DataReviewer-001)", () => {
  const sql = readFileSync(
    new URL("../migrations/008-add-v4-fields.sql", import.meta.url),
    "utf-8",
  );

  it("widens the record_version CHECK to admit v4 inserts", () => {
    expect(sql).toMatch(
      /record_version\s+IN\s*\(\s*1\s*,\s*2\s*,\s*3\s*,\s*4\s*\)/,
    );
  });

  it("adds the kernel_identity_jsonb column", () => {
    expect(sql).toMatch(/kernel_identity_jsonb/);
  });
});

describe("migration 010 — record_version CHECK admits v5 + metadata_jsonb (audit 2026-06-07)", () => {
  const sql = readFileSync(
    new URL("../migrations/010-add-v5-metadata.sql", import.meta.url),
    "utf-8",
  );

  // This is the regression guard for the activation blocker: core stamps
  // record_version=5 unconditionally, so the LATEST effective CHECK must admit
  // 5 or every live audit insert fails with Postgres 23514. (The 008 test above
  // pins 008's historical IN(1,2,3,4); this pins the current ceiling.)
  it("widens the record_version CHECK to admit v5 inserts", () => {
    expect(sql).toMatch(
      /record_version\s+IN\s*\(\s*1\s*,\s*2\s*,\s*3\s*,\s*4\s*,\s*5\s*\)/,
    );
  });

  it("the sink-stamped record_version is within the migration-010 CHECK set", () => {
    // Couples the writer to the schema: recordToRow stamps record.version, and
    // that value MUST be admitted by the latest CHECK. Catches a future vN bump
    // that outruns the constraint (exactly how v4→v5 regressed).
    const stamped = recordToRow(v4Record()).record_version;
    // Parse the EXECUTABLE constraint only — strip `--` comment lines first so
    // the regex can't match the header comment that quotes 008's old IN(1,2,3,4).
    const executable = sql
      .split("\n")
      .filter((line) => !line.trim().startsWith("--"))
      .join("\n");
    const allowed = executable
      .match(/record_version\s+IN\s*\(([^)]*)\)/)?.[1]
      .split(",")
      .map((s) => Number(s.trim()));
    expect(allowed).toBeDefined();
    expect(allowed).toContain(stamped);
  });

  it("adds the nullable metadata_jsonb column", () => {
    expect(sql).toMatch(/ADD COLUMN IF NOT EXISTS metadata_jsonb JSONB/);
  });
});

describe("v5 metadata_jsonb round-trip (ADR-124)", () => {
  const env = buildEnvelope({
    kind: "order.submit",
    payload: { sku: "X", qty: 1 },
    actor: { principal: "llm", sessionId: "s-1" },
    taint: "TRUSTED",
    nonce: "n-meta",
    createdAt: "2026-06-07T12:00:00.000Z",
  });
  const decision = decisionExecute([basis("state", BASIS_CODES.state.TRANSITION_VALID)]);

  it("persists and recovers metadata losslessly without perturbing auditHash", () => {
    const withMeta = buildAuditRecord({
      envelope: env,
      decision,
      durationMs: 7,
      at: "2026-06-07T12:00:01.000Z",
      metadata: { hallucination_score: 0.42, bucket: "low" },
    });
    const withoutMeta = buildAuditRecord({
      envelope: env,
      decision,
      durationMs: 7,
      at: "2026-06-07T12:00:01.000Z",
    });
    // metadata is excluded from the auditHash pre-image → same hash either way.
    expect(withMeta.auditHash).toBe(withoutMeta.auditHash);

    const row = recordToRow(withMeta);
    expect(row.metadata_jsonb).toBe(JSON.stringify({ hallucination_score: 0.42, bucket: "low" }));

    const recovered = rowToRecord(row);
    expect(recovered.metadata).toEqual({ hallucination_score: 0.42, bucket: "low" });
    // auditHash survives the DB round-trip AND still verifies (metadata is not
    // part of the pre-image, so attaching it can never flip a record to tampered).
    expect(recovered.auditHash).toBe(withMeta.auditHash);
    expect(verifyAuditRecord(recovered).verified).toBe(true);
  });

  it("a record with no metadata writes NULL and recovers without a metadata field", () => {
    const row = recordToRow(
      buildAuditRecord({ envelope: env, decision, durationMs: 7, at: "2026-06-07T12:00:01.000Z" }),
    );
    expect(row.metadata_jsonb).toBeNull();
    expect(rowToRecord(row).metadata).toBeUndefined();
  });
});

describe("migration 001 — intent_hash format CHECK (CryptoReviewer-009)", () => {
  const sql = readFileSync(
    new URL("../migrations/001-create-intent-audit.sql", import.meta.url),
    "utf-8",
  );

  it("constrains intent_hash to the sha256 lowercase-hex shape", () => {
    // Inline CHECK on the column, matching the principal/taint convention
    // in the same CREATE TABLE. Anchored 64-char lowercase-hex pattern.
    expect(sql).toMatch(
      /intent_hash\s+TEXT\s+NOT\s+NULL\s+CHECK\s*\(\s*intent_hash\s*~\s*'\^\[a-f0-9\]\{64\}\$'\s*\)/,
    );
  });

  it("the CHECK pattern accepts a real kernel intentHash and rejects malformed values", () => {
    // Mirror the DB regex in JS to prove the chosen pattern is correct: it
    // must accept a 64-char lowercase-hex hash and reject truncated /
    // upper-cased / non-hex strings.
    const pattern = /^[a-f0-9]{64}$/;
    const realHash =
      "ccaaf1c710d6956f00b84cbed4fc8a31c148a9e3e1c932d21f377c472c690bc0";
    expect(realHash).toHaveLength(64);
    expect(pattern.test(realHash)).toBe(true);
    expect(pattern.test(realHash.toUpperCase())).toBe(false); // upper-case hex
    expect(pattern.test(realHash.slice(0, 63))).toBe(false); // truncated
    expect(pattern.test(realHash + "0")).toBe(false); // too long
    expect(pattern.test("v1hash".repeat(11) + "ab")).toBe(false); // non-hex legacy fixture
  });
});