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

let db: TestDb;
let c: Client;
let batchId: number;

async function loadBatch(
  client: Client, company: string, user: string, sha: string, filename: string,
  rows: Array<{ key: string; account: string; debit?: string; credit?: string }>,
): Promise<number> {
  const b = await client.query(
    `INSERT INTO import_batches (company_id, source, filename, file_sha256, created_by)
     VALUES ($1, 'quickbooks_journal', $2, $3, $4) RETURNING id`,
    [company, filename, sha, user]);
  const id = Number(b.rows[0].id);
  for (const r of rows) {
    await client.query(
      `INSERT INTO staging_journal
         (company_id, batch_id, entry_key, txn_date, account_raw, debit_raw, credit_raw, raw)
       VALUES ($1, $2, $3, '2019-03-15', $4, $5, $6, $7::jsonb)`,
      [company, id, r.key, r.account, r.debit ?? "", r.credit ?? "",
       JSON.stringify({ "Trans #": r.key, Account: r.account })]);
  }
  return id;
}

beforeAll(async () => {
  db = await createTestDb();
  c = await db.appClient(db.companyA);
  await c.query(
    `INSERT INTO account_aliases (company_id, alias, account_id)
     VALUES ($1, 'Checking', $2)`, [db.companyA, db.accounts["1000"].A]);
  batchId = await loadBatch(c, db.companyA, db.userA, "b".repeat(64), "alpha-2019.csv", [
    { key: "1", account: "Checking", debit: "1000.00" },          // via alias
    { key: "1", account: "Revenue", credit: "1,000.00" },         // by name, with comma
    { key: "2", account: "Operating Expense", debit: "250.00" },
    { key: "2", account: "Checking", credit: "250.00" },
  ]);
}, 60_000);
afterAll(async () => { await db.close(); });

describe("import pipeline (CLAUDE.md §6, §8)", () => {
  it("resolve -> validate -> post produces posted, balanced entries", async () => {
    await c.query("SELECT resolve_batch($1, $2)", [db.companyA, batchId]);
    const v = await c.query(
      "SELECT validate_batch($1, $2) AS errors", [db.companyA, batchId]);
    expect(Number(v.rows[0].errors)).toBe(0);
    const p = await c.query(
      "SELECT post_batch($1, $2, $3) AS entries", [db.companyA, batchId, db.userA]);
    expect(Number(p.rows[0].entries)).toBe(2);

    const cash = await c.query(
      "SELECT balance FROM trial_balance($1, '2019-12-31') WHERE code = '1000'",
      [db.companyA]);
    expect(cash.rows[0].balance).toBe("750.0000");

    const entries = await c.query(
      `SELECT external_ref, source, status FROM journal_entries
        WHERE source = 'quickbooks_journal' ORDER BY external_ref`);
    expect(entries.rows.map((r) => r.external_ref)).toEqual(["1", "2"]);
    expect(entries.rows.every((r) => r.status === "posted")).toBe(true);
  });

  it("batch reconciliation ties staged totals to posted totals", async () => {
    const r = await c.query(
      "SELECT * FROM batch_reconciliation($1, $2)", [db.companyA, batchId]);
    expect(r.rows[0].matches).toBe(true);
    expect(r.rows[0].staged_debits).toBe("1250.0000");
    expect(r.rows[0].posted_debits).toBe("1250.0000");
  });

  it("rollback_import_batch REVERSES (never deletes) every posted entry", async () => {
    const before = await c.query(
      "SELECT count(*)::int AS n FROM journal_entries WHERE status = 'posted'");
    const r = await c.query(
      "SELECT rollback_import_batch($1, $2, $3) AS reversed",
      [db.companyA, batchId, db.userA]);
    expect(Number(r.rows[0].reversed)).toBe(2);

    // nothing deleted: originals + reversals both present
    const after = await c.query(
      "SELECT count(*)::int AS n FROM journal_entries WHERE status = 'posted'");
    expect(after.rows[0].n).toBe(before.rows[0].n + 2);

    // net effect zero
    const cash = await c.query(
      "SELECT balance FROM trial_balance($1, '2019-12-31') WHERE code = '1000'",
      [db.companyA]);
    expect(cash.rows[0].balance).toBe("0.0000");

    const batch = await c.query(
      "SELECT status FROM import_batches WHERE id = $1", [batchId]);
    expect(batch.rows[0].status).toBe("rolled_back");

    const rec = await c.query(
      "SELECT * FROM batch_reconciliation($1, $2)", [db.companyA, batchId]);
    expect(rec.rows[0].batch_status).toBe("rolled_back");
    expect(rec.rows[0].matches).toBe(false);
  });

  it("resolve/validate cannot run on a rolled-back batch", async () => {
    await expect(c.query("SELECT resolve_batch($1, $2)", [db.companyA, batchId]))
      .rejects.toThrow(/already rolled_back/);
    await expect(c.query("SELECT validate_batch($1, $2)", [db.companyA, batchId]))
      .rejects.toThrow(/already rolled_back/);
  });

  it("an unmapped account fails validation LOUDLY — batch cannot post", async () => {
    const bad = await loadBatch(c, db.companyA, db.userA, "c".repeat(64), "alpha-2020.csv", [
      { key: "9", account: "Mystery New Deduction", debit: "50.00" },
      { key: "9", account: "Checking", credit: "50.00" },
    ]);
    await c.query("SELECT resolve_batch($1, $2)", [db.companyA, bad]);
    const v = await c.query(
      "SELECT validate_batch($1, $2) AS errors", [db.companyA, bad]);
    expect(Number(v.rows[0].errors)).toBeGreaterThan(0);

    const row = await c.query(
      `SELECT status, error FROM staging_journal
        WHERE batch_id = $1 AND account_raw = 'Mystery New Deduction'`, [bad]);
    expect(row.rows[0].status).toBe("error");
    expect(row.rows[0].error).toMatch(/unmapped account/);

    await expect(
      c.query("SELECT post_batch($1, $2, $3)", [db.companyA, bad, db.userA]),
    ).rejects.toThrow(/not validated|validation errors/);
  });

  it("an unbalanced entry_key fails validation", async () => {
    const bad = await loadBatch(c, db.companyA, db.userA, "d".repeat(64), "alpha-2021.csv", [
      { key: "10", account: "Checking", debit: "100.00" },
      { key: "10", account: "Revenue", credit: "99.00" },
    ]);
    await c.query("SELECT resolve_batch($1, $2)", [db.companyA, bad]);
    const v = await c.query(
      "SELECT validate_batch($1, $2) AS errors", [db.companyA, bad]);
    expect(Number(v.rows[0].errors)).toBeGreaterThan(0);
  });

  it("a batch cannot be posted twice", async () => {
    // batchId was posted then rolled back above; its status is 'rolled_back'
    await expect(
      c.query("SELECT post_batch($1, $2, $3)", [db.companyA, batchId, db.userA]),
    ).rejects.toThrow(/not validated/);
  });

  it("an ambiguous account name fails resolution loudly", async () => {
    await db.admin.query(
      `INSERT INTO accounts (company_id, code, name, type)
       VALUES ($1, '6001', 'Operating Expense', 'expense')`, [db.companyA]);
    const bad = await loadBatch(c, db.companyA, db.userA, "e".repeat(64), "alpha-2022.csv", [
      { key: "11", account: "Operating Expense", debit: "10.00" },
      { key: "11", account: "Checking", credit: "10.00" },
    ]);
    await c.query("SELECT resolve_batch($1, $2)", [db.companyA, bad]);
    const v = await c.query(
      "SELECT validate_batch($1, $2) AS errors", [db.companyA, bad]);
    expect(Number(v.rows[0].errors)).toBeGreaterThan(0);
    const row = await c.query(
      `SELECT error FROM staging_journal
        WHERE batch_id = $1 AND account_raw = 'Operating Expense'`, [bad]);
    expect(row.rows[0].error).toMatch(/unmapped account/);
  });

  it("a well-formed zero-amount line becomes 'void' but its entry still posts", async () => {
    const zb = await loadBatch(c, db.companyA, db.userA, "f".repeat(64), "alpha-2023.csv", [
      { key: "20", account: "Checking", debit: "100.00" },
      { key: "20", account: "Revenue", credit: "100.00" },
      { key: "20", account: "Accounts Receivable", debit: "0.00" }, // capped/void line
    ]);
    await c.query("SELECT resolve_batch($1, $2)", [db.companyA, zb]);
    const v = await c.query(
      "SELECT validate_batch($1, $2) AS errors", [db.companyA, zb]);
    expect(Number(v.rows[0].errors)).toBe(0);

    const zeroRow = await c.query(
      `SELECT status, posted_entry_id FROM staging_journal
        WHERE batch_id = $1 AND account_raw = 'Accounts Receivable'`, [zb]);
    expect(zeroRow.rows[0].status).toBe("void");

    await c.query("SELECT post_batch($1, $2, $3)", [db.companyA, zb, db.userA]);

    const zeroAfterPost = await c.query(
      `SELECT status, posted_entry_id FROM staging_journal
        WHERE batch_id = $1 AND account_raw = 'Accounts Receivable'`, [zb]);
    expect(zeroAfterPost.rows[0].status).toBe("void");
    expect(zeroAfterPost.rows[0].posted_entry_id).toBeNull();

    const checkingRow = await c.query(
      `SELECT posted_entry_id FROM staging_journal
        WHERE batch_id = $1 AND account_raw = 'Checking'`, [zb]);
    const entryId = checkingRow.rows[0].posted_entry_id;
    expect(entryId).not.toBeNull();

    const lines = await c.query(
      `SELECT debit, credit FROM journal_lines WHERE entry_id = $1 ORDER BY id`, [entryId]);
    expect(lines.rows.length).toBe(2);
    const sumDebit = lines.rows.reduce((s, r) => s + Number(r.debit), 0);
    const sumCredit = lines.rows.reduce((s, r) => s + Number(r.credit), 0);
    expect(sumDebit).toBe(sumCredit);
  });

  it("a fully-void entry (every line zero) posts nothing; other entries post fine", async () => {
    const vb = await loadBatch(c, db.companyA, db.userA, "g".repeat(64), "alpha-2024.csv", [
      { key: "30", account: "Checking", debit: "0.00" },
      { key: "30", account: "Revenue", credit: "0.00" },
      { key: "31", account: "Checking", debit: "75.00" },
      { key: "31", account: "Revenue", credit: "75.00" },
    ]);
    await c.query("SELECT resolve_batch($1, $2)", [db.companyA, vb]);
    const v = await c.query(
      "SELECT validate_batch($1, $2) AS errors", [db.companyA, vb]);
    expect(Number(v.rows[0].errors)).toBe(0);

    const voidRows = await c.query(
      `SELECT status FROM staging_journal WHERE batch_id = $1 AND entry_key = '30'`, [vb]);
    expect(voidRows.rows.every((r) => r.status === "void")).toBe(true);

    const p = await c.query(
      "SELECT post_batch($1, $2, $3) AS entries", [db.companyA, vb, db.userA]);
    expect(Number(p.rows[0].entries)).toBe(1); // only key 31

    const none = await c.query(
      `SELECT count(*)::int AS n FROM journal_entries
        WHERE source = 'quickbooks_journal' AND external_ref = '30'`);
    expect(none.rows[0].n).toBe(0);

    const posted31 = await c.query(
      `SELECT status FROM journal_entries
        WHERE source = 'quickbooks_journal' AND external_ref = '31'`);
    expect(posted31.rows[0].status).toBe("posted");

    const rec = await c.query(
      "SELECT * FROM batch_reconciliation($1, $2)", [db.companyA, vb]);
    expect(rec.rows[0].matches).toBe(true); // zeros contribute nothing
  });

  it("a zero-amount line with an unmapped account is still a loud error", async () => {
    const ub = await loadBatch(c, db.companyA, db.userA, "h".repeat(64), "alpha-2025.csv", [
      { key: "40", account: "Nonexistent Payroll Code", debit: "0.00" },
      { key: "40", account: "Checking", credit: "0.00" },
    ]);
    await c.query("SELECT resolve_batch($1, $2)", [db.companyA, ub]);
    const v = await c.query(
      "SELECT validate_batch($1, $2) AS errors", [db.companyA, ub]);
    expect(Number(v.rows[0].errors)).toBeGreaterThan(0);

    const row = await c.query(
      `SELECT status, error FROM staging_journal
        WHERE batch_id = $1 AND account_raw = 'Nonexistent Payroll Code'`, [ub]);
    expect(row.rows[0].status).toBe("error");
    expect(row.rows[0].error).toMatch(/unmapped account/);

    await expect(
      c.query("SELECT post_batch($1, $2, $3)", [db.companyA, ub, db.userA]),
    ).rejects.toThrow(/not validated/);
  });
});
