import { describe, it, expect, beforeAll, afterAll } from "vitest";
import { createTestDb, type TestDb, insertDraftEntry, postEntry } from "../helpers/db.js";
import type { Client } from "pg";

let db: TestDb;
let c: Client;

beforeAll(async () => {
  db = await createTestDb();
  c = await db.appClient(db.companyA);
  // a small but non-trivial ledger: revenue, an expense, a receivable collected
  const specs = [
    { date: "2024-01-10", lines: [
      { accountId: db.accounts["1100"].A, debit: "1000.0000" },
      { accountId: db.accounts["4000"].A, credit: "1000.0000" } ] },
    { date: "2024-01-20", lines: [
      { accountId: db.accounts["1000"].A, debit: "600.0000" },
      { accountId: db.accounts["1100"].A, credit: "600.0000" } ] },
    { date: "2024-02-05", lines: [
      { accountId: db.accounts["6000"].A, debit: "250.0000" },
      { accountId: db.accounts["1000"].A, credit: "250.0000" } ] },
  ];
  for (const s of specs) {
    const id = await insertDraftEntry(c, db.companyA, db.userA, s);
    await postEntry(c, db.companyA, id);
  }
}, 60_000);
afterAll(async () => { await db.close(); });

describe("reporting (CLAUDE.md §3.4, §11)", () => {
  it("ledger_out_of_balance() is exactly 0.0000 for every company at every period end", async () => {
    for (const asOf of ["2024-01-31", "2024-02-29", "2024-12-31"]) {
      const r = await c.query(
        "SELECT ledger_out_of_balance($1, $2) AS d", [db.companyA, asOf]);
      expect(r.rows[0].d).toBe("0.0000");
    }
    const cB = await db.appClient(db.companyB);
    const rB = await cB.query(
      "SELECT ledger_out_of_balance($1, '2024-12-31') AS d", [db.companyB]);
    expect(rB.rows[0].d).toBe("0.0000");
  });

  it("a draft entry has no effect on any report", async () => {
    const draft = await insertDraftEntry(c, db.companyA, db.userA, {
      date: "2024-01-15",
      lines: [
        { accountId: db.accounts["1000"].A, debit: "77777.0000" },
        { accountId: db.accounts["4000"].A, credit: "77777.0000" },
      ],
    });
    const r = await c.query(
      "SELECT balance FROM trial_balance($1, '2024-01-31') WHERE code = '1000'",
      [db.companyA]);
    expect(r.rows[0].balance).toBe("600.0000"); // unchanged by the draft
    await c.query("BEGIN");
    await c.query("DELETE FROM journal_lines WHERE entry_id = $1", [draft]);
    await c.query("DELETE FROM journal_entries WHERE id = $1", [draft]);
    await c.query("COMMIT");
  });

  it("trial balance reflects only posted entries up to the as-of date", async () => {
    const r = await c.query(
      "SELECT * FROM trial_balance($1, '2024-01-31') ORDER BY code", [db.companyA]);
    const byCode = new Map(r.rows.map((row) => [row.code, row]));
    expect(byCode.get("1000")!.balance).toBe("600.0000");
    expect(byCode.get("1100")!.balance).toBe("400.0000");
    expect(byCode.get("4000")!.balance).toBe("-1000.0000"); // debit-positive storage
    expect(byCode.get("6000")!.balance).toBe("0.0000");     // Feb expense excluded
  });

  it("trial balance totals equal the balance sheet + P&L rollups", async () => {
    const r = await c.query(
      `SELECT
         sum(balance)::numeric(19,4) AS total,
         sum(balance) FILTER (WHERE type IN ('asset','liability','equity'))::numeric(19,4) AS bs,
         sum(balance) FILTER (WHERE type IN ('income','expense'))::numeric(19,4) AS pl,
         (sum(balance) FILTER (WHERE type IN ('asset','liability','equity'))
          + sum(balance) FILTER (WHERE type IN ('income','expense')))::numeric(19,4) AS bs_plus_pl
       FROM trial_balance($1, '2024-12-31')`, [db.companyA]);
    expect(r.rows[0].total).toBe("0.0000");
    expect(r.rows[0].bs_plus_pl).toBe("0.0000"); // bs = -pl, exactly, in SQL
  });

  it("NULL as-of means all of history for both reporting functions", async () => {
    const oob = await c.query(
      "SELECT ledger_out_of_balance($1) AS d", [db.companyA]);
    expect(oob.rows[0].d).toBe("0.0000");
    const tb = await c.query(
      "SELECT balance FROM trial_balance($1, NULL) WHERE code = '1000'",
      [db.companyA]);
    expect(tb.rows[0].balance).toBe("350.0000"); // 600 collected - 250 spent, full history
  });

  it("no LEDGER-DERIVED balances are stored anywhere (CLAUDE.md §3.4)", async () => {
    const r = await db.admin.query(
      `SELECT table_name, column_name FROM information_schema.columns
        WHERE table_schema = 'public' AND column_name ILIKE '%balance%'`);
    // The only columns anywhere in the schema matching '%balance%' are
    // EXTERNAL facts — what a bank statement itself claims — never a number
    // this database computes from journal_lines. CLAUDE.md §3.4 forbids
    // caching a derived ledger balance; it says nothing about recording what
    // a third-party document says (same category as bills.ref_no, or the
    // raw jsonb kept on every import row per CLAUDE.md §12). See
    // db/migrations/0011_bank_rec.sql's account_reconciliations comment: the
    // one number here that IS ledger-derived (book balance as of a
    // reconciliation) is deliberately NOT a column — it's recomputed on
    // demand via account_book_balance_asof(), precisely because immutability
    // plus the reconciled-through guard make it always safely re-derivable.
    const allowed = new Set([
      "bank_statements.ending_balance",
      "account_reconciliations.statement_balance",
    ]);
    const unexpected = r.rows.filter(
      (row: { table_name: string; column_name: string }) => !allowed.has(`${row.table_name}.${row.column_name}`));
    expect(unexpected).toEqual([]);
  });
});
