import { describe, it, expect } from "vitest";
import ExcelJS from "exceljs";
import { writeFile, mkdtemp, rm } from "node:fs/promises";
import { tmpdir } from "node:os";
import path from "node:path";
import {
  parseJournalRows,
  groupEntriesByYear,
  sliceSha,
  journalAmount,
  type JournalEntry,
} from "../../tools/journal-logic.js";
import { readQbSheet } from "../../tools/qb-xlsx.js";

// Column layout per docs/superpowers/plans/2026-07-30-qb-migration-loader.md
// (1-indexed, matching readQbSheet's convention): 2=Trans #, 4=Type,
// 6=Date, 8=Num, 10=Name, 12=Memo, 14=Account, 16=Debit, 18=Credit.
// Column 1 holds the subtotal marker (a single space).
function dataRow(opts: {
  trans?: string | number;
  type?: string;
  date?: unknown;
  num?: string;
  name?: string;
  memo?: string;
  account?: unknown;
  debit?: unknown;
  credit?: unknown;
  subtotal?: boolean;
}): unknown[] {
  const r: unknown[] = new Array(19).fill(undefined);
  if (opts.subtotal) {
    r[1] = " ";
    return r;
  }
  if (opts.trans !== undefined) r[2] = opts.trans;
  if (opts.type !== undefined) r[4] = opts.type;
  if (opts.date !== undefined) r[6] = opts.date;
  if (opts.num !== undefined) r[8] = opts.num;
  if (opts.name !== undefined) r[10] = opts.name;
  if (opts.memo !== undefined) r[12] = opts.memo;
  if (opts.account !== undefined) r[14] = opts.account;
  if (opts.debit !== undefined) r[16] = opts.debit;
  if (opts.credit !== undefined) r[18] = opts.credit;
  return r;
}

function sheet(...rows: unknown[][]): unknown[][] {
  return [[], [], ...rows]; // index 0 unused, index 1 = header (skipped)
}

/** The report's grand-total row: "TOTAL" in column 1, totals in Debit/Credit. */
function totalRow(debit: unknown, credit: unknown): unknown[] {
  const r: unknown[] = new Array(19).fill(undefined);
  r[1] = "TOTAL";
  r[16] = debit;
  r[18] = credit;
  return r;
}

/** A row with every cell null/undefined — skipped wherever it appears. */
function blankRow(): unknown[] {
  return new Array(19).fill(undefined);
}

const D = (y: number, m: number, d: number) => new Date(Date.UTC(y, m - 1, d));

describe("parseJournalRows: forward-fill + subtotal termination", () => {
  it("groups a first line and continuation lines into one entry, ending at the subtotal row", () => {
    const rows = sheet(
      dataRow({ trans: 1, type: "General Journal", date: D(2019, 3, 15), num: "1001",
        name: "Alice", memo: "rent", account: "Checking", credit: 1000 }),
      dataRow({ name: "Alice", memo: "rent", account: "Rent Expense", debit: 1000 }),
      dataRow({ subtotal: true }),
    );
    const entries = parseJournalRows(rows);
    expect(entries).toHaveLength(1);
    const e = entries[0]!;
    expect(e.entryKey).toBe("1");
    expect(e.dateIso).toBe("2019-03-15");
    expect(e.lines).toHaveLength(2);
    expect(e.lines[0]).toEqual({
      entryKey: "1", type: "General Journal", dateIso: "2019-03-15", num: "1001",
      name: "Alice", memo: "rent", account: "Checking", debit: "", credit: "1000.00",
    });
    // continuation line: entryKey forward-filled, its own dateIso is blank
    expect(e.lines[1]).toEqual({
      entryKey: "1", type: "", dateIso: "", num: "", name: "Alice", memo: "rent",
      account: "Rent Expense", debit: "1000.00", credit: "",
    });
  });

  it("handles multiple entries in sequence, each terminated by its own subtotal row", () => {
    const rows = sheet(
      dataRow({ trans: 1, date: D(2019, 1, 1), account: "Checking", debit: 100 }),
      dataRow({ account: "Revenue", credit: 100 }),
      dataRow({ subtotal: true }),
      dataRow({ trans: 2, date: D(2019, 6, 1), account: "Checking", debit: 50 }),
      dataRow({ account: "Revenue", credit: 50 }),
      dataRow({ subtotal: true }),
    );
    const entries = parseJournalRows(rows);
    expect(entries.map((e) => e.entryKey)).toEqual(["1", "2"]);
    expect(entries.map((e) => e.dateIso)).toEqual(["2019-01-01", "2019-06-01"]);
    expect(entries[0]!.lines).toHaveLength(2);
    expect(entries[1]!.lines).toHaveLength(2);
  });

  it("flushes a final unterminated entry at EOF (no trailing subtotal row)", () => {
    const rows = sheet(
      dataRow({ trans: 1, date: D(2019, 1, 1), account: "Checking", debit: 5 }),
      dataRow({ account: "Revenue", credit: 5 }),
    );
    const entries = parseJournalRows(rows);
    expect(entries).toHaveLength(1);
    expect(entries[0]!.lines).toHaveLength(2);
  });
});

describe("parseJournalRows: loud rejects", () => {
  it("rejects a data line that appears outside any entry", () => {
    const rows = sheet(
      // continuation-shaped row (no Trans #) with no entry open yet
      dataRow({ account: "Checking", debit: 10 }),
    );
    expect(() => parseJournalRows(rows)).toThrow(/data line outside any entry/);
  });

  it("rejects a non-numeric amount cell", () => {
    const rows = sheet(
      dataRow({ trans: 1, date: D(2019, 1, 1), account: "Checking", debit: "not a number" }),
    );
    expect(() => parseJournalRows(rows)).toThrow(/non-numeric value in Debit column/);
  });

  it("rejects an unsupported object in an amount cell", () => {
    const rows = sheet(
      dataRow({ trans: 1, date: D(2019, 1, 1), account: "Checking", credit: { error: "#REF!" } }),
    );
    expect(() => parseJournalRows(rows)).toThrow(/non-numeric value in Credit column/);
  });

  it("rejects a non-Date value in an entry's first-line Date cell", () => {
    const rows = sheet(
      dataRow({ trans: 1, date: "3/15/2019", account: "Checking", debit: 10 }),
    );
    expect(() => parseJournalRows(rows)).toThrow(/non-Date value in the Date column/);
  });

  it("rejects a missing (undefined) Date on an entry's first line", () => {
    const rows = sheet(
      dataRow({ trans: 1, account: "Checking", debit: 10 }),
    );
    expect(() => parseJournalRows(rows)).toThrow(/non-Date value in the Date column/);
  });

  it("rejects an unsupported cell object in a text column, naming the cell", () => {
    const rows = sheet(
      dataRow({ trans: 1, date: D(2019, 1, 1), account: { error: "#REF!" }, debit: 10 }),
    );
    expect(() => parseJournalRows(rows)).toThrow(/unsupported cell value in Account column/);
  });
});

describe("amount conversion", () => {
  it("converts a plain number to a two-decimal string", () => {
    expect(journalAmount(387.5, 1, "Debit")).toBe("387.50");
    expect(journalAmount(1000, 1, "Debit")).toBe("1000.00");
    expect(journalAmount(0.1, 1, "Debit")).toBe("0.10");
  });

  it("treats null/undefined/empty-string as blank", () => {
    expect(journalAmount(null, 1, "Debit")).toBe("");
    expect(journalAmount(undefined, 1, "Debit")).toBe("");
    expect(journalAmount("", 1, "Debit")).toBe("");
  });

  it("round-trips 387.5 -> \"387.50\" through full entry parsing", () => {
    const rows = sheet(
      dataRow({ trans: 1, date: D(2019, 1, 1), account: "Checking", debit: 387.5 }),
    );
    const entries = parseJournalRows(rows);
    expect(entries[0]!.lines[0]!.debit).toBe("387.50");
  });
});

describe("year slicing", () => {
  function entry(key: string, dateIso: string): JournalEntry {
    return {
      entryKey: key,
      dateIso,
      lines: [
        { entryKey: key, type: "", dateIso, num: "", name: "", memo: "",
          account: "Checking", debit: "10.00", credit: "" },
        { entryKey: key, type: "", dateIso: "", num: "", name: "", memo: "",
          account: "Revenue", debit: "", credit: "10.00" },
      ],
    };
  }

  it("groups entries by the year of the entry's own date, keeping each entry's lines whole", () => {
    const entries = [
      entry("1", "2019-01-15"),
      entry("2", "2020-06-01"),
      entry("3", "2019-12-31"),
    ];
    const byYear = groupEntriesByYear(entries);
    expect([...byYear.keys()].sort()).toEqual([2019, 2020]);
    expect(byYear.get(2019)!.map((e) => e.entryKey)).toEqual(["1", "3"]);
    expect(byYear.get(2020)!.map((e) => e.entryKey)).toEqual(["2"]);
    // every entry's lines travel with it, unsplit
    for (const list of byYear.values()) {
      for (const e of list) {
        expect(e.lines.length).toBeGreaterThanOrEqual(2);
      }
    }
  });
});

describe("sliceSha", () => {
  function entry(key: string, dateIso: string): JournalEntry {
    return {
      entryKey: key,
      dateIso,
      lines: [
        { entryKey: key, type: "", dateIso, num: "", name: "", memo: "",
          account: "Checking", debit: "10.00", credit: "" },
        { entryKey: key, type: "", dateIso: "", num: "", name: "", memo: "",
          account: "Revenue", debit: "", credit: "10.00" },
      ],
    };
  }

  it("is deterministic: identical input produces the identical sha every time", () => {
    const entries = [entry("1", "2019-01-15")];
    const first = sliceSha("journal.xlsx", 2019, entries);
    const second = sliceSha("journal.xlsx", 2019, entries);
    expect(first).toBe(second);
    expect(first).toMatch(/^[0-9a-f]{64}$/);
  });

  it("produces a different sha for a different year, same entries", () => {
    const entries = [entry("1", "2019-01-15")];
    const a = sliceSha("journal.xlsx", 2019, entries);
    const b = sliceSha("journal.xlsx", 2020, entries);
    expect(a).not.toBe(b);
  });

  it("produces a different sha when the entries differ", () => {
    const a = sliceSha("journal.xlsx", 2019, [entry("1", "2019-01-15")]);
    const b = sliceSha("journal.xlsx", 2019, [entry("2", "2019-01-15")]);
    expect(a).not.toBe(b);
  });

  it("produces a different sha for a different filename, same year and entries", () => {
    const entries = [entry("1", "2019-01-15")];
    const a = sliceSha("journal-a.xlsx", 2019, entries);
    const b = sliceSha("journal-b.xlsx", 2019, entries);
    expect(a).not.toBe(b);
  });
});

describe("qb-xlsx integration: real workbook through parseJournalRows", () => {
  it("parses two entries across two years from a generated xlsx fixture", async () => {
    const dir = await mkdtemp(path.join(tmpdir(), "qb-journal-test-"));
    const file = path.join(dir, "journal.xlsx");
    try {
      const workbook = new ExcelJS.Workbook();
      const sheet1 = workbook.addWorksheet("Sheet1");
      sheet1.getCell(1, 2).value = "Trans #";
      sheet1.getCell(1, 6).value = "Date";
      sheet1.getCell(1, 14).value = "Account";

      // Entry 1, 2019
      sheet1.getCell(2, 2).value = 1;
      sheet1.getCell(2, 4).value = "General Journal";
      sheet1.getCell(2, 6).value = D(2019, 3, 15);
      sheet1.getCell(2, 14).value = "Checking";
      sheet1.getCell(2, 16).value = 500;
      sheet1.getCell(3, 14).value = "Revenue";
      sheet1.getCell(3, 18).value = 500;
      sheet1.getCell(4, 1).value = " "; // subtotal row

      // Entry 2, 2020
      sheet1.getCell(5, 2).value = 2;
      sheet1.getCell(5, 4).value = "General Journal";
      sheet1.getCell(5, 6).value = D(2020, 7, 1);
      sheet1.getCell(5, 14).value = "Checking";
      sheet1.getCell(5, 16).value = 75.25;
      sheet1.getCell(6, 14).value = "Revenue";
      sheet1.getCell(6, 18).value = 75.25;
      sheet1.getCell(7, 1).value = " "; // subtotal row

      await workbook.xlsx.writeFile(file);

      const sheetRows = await readQbSheet(file);
      const entries = parseJournalRows(sheetRows);
      expect(entries.map((e) => e.entryKey)).toEqual(["1", "2"]);
      expect(entries.map((e) => e.dateIso)).toEqual(["2019-03-15", "2020-07-01"]);
      expect(entries[1]!.lines[0]!.debit).toBe("75.25");

      const byYear = groupEntriesByYear(entries);
      expect([...byYear.keys()].sort()).toEqual([2019, 2020]);
    } finally {
      await rm(dir, { recursive: true, force: true });
    }
  });
});

describe("parseJournalRows: grand-total row and blank rows (real-file edge cases)", () => {
  it("parses cleanly through a TOTAL row followed by a trailing blank row, verifying totals", () => {
    const rows = sheet(
      dataRow({ trans: 1, date: D(2019, 1, 1), account: "Checking", debit: 100 }),
      dataRow({ account: "Revenue", credit: 100 }),
      dataRow({ subtotal: true }),
      dataRow({ trans: 2, date: D(2020, 6, 1), account: "Checking", debit: 50 }),
      dataRow({ account: "Revenue", credit: 50 }),
      dataRow({ subtotal: true }),
      totalRow(150, 150),
      blankRow(),
    );
    const entries = parseJournalRows(rows);
    expect(entries.map((e) => e.entryKey)).toEqual(["1", "2"]);
    expect(entries).toHaveLength(2);
  });

  it("skips a fully-blank row in the middle of the file without disrupting parsing", () => {
    const rows = sheet(
      dataRow({ trans: 1, date: D(2019, 1, 1), account: "Checking", debit: 10 }),
      blankRow(),
      dataRow({ account: "Revenue", credit: 10 }),
      dataRow({ subtotal: true }),
    );
    const entries = parseJournalRows(rows);
    expect(entries).toHaveLength(1);
    expect(entries[0]!.lines).toHaveLength(2);
  });

  it("rejects a TOTAL row whose debit total does not match the sum of parsed entries", () => {
    const rows = sheet(
      dataRow({ trans: 1, date: D(2019, 1, 1), account: "Checking", debit: 100 }),
      dataRow({ account: "Revenue", credit: 100 }),
      dataRow({ subtotal: true }),
      totalRow(999, 100), // wrong debit total
    );
    expect(() => parseJournalRows(rows)).toThrow(
      /grand total mismatch: file says 999\.00 debits, parsed entries sum to 100\.00/,
    );
  });

  it("rejects a TOTAL row whose credit total does not match the sum of parsed entries", () => {
    const rows = sheet(
      dataRow({ trans: 1, date: D(2019, 1, 1), account: "Checking", debit: 100 }),
      dataRow({ account: "Revenue", credit: 100 }),
      dataRow({ subtotal: true }),
      totalRow(100, 1), // wrong credit total
    );
    expect(() => parseJournalRows(rows)).toThrow(
      /grand total mismatch: file says 1\.00 credits, parsed entries sum to 100\.00/,
    );
  });

  it("uses exact cent comparison, not float accumulation (0.1 + 0.2 style sums must tie exactly)", () => {
    const rows = sheet(
      dataRow({ trans: 1, date: D(2019, 1, 1), account: "Checking", debit: 0.1 }),
      dataRow({ account: "Checking", debit: 0.2 }),
      dataRow({ account: "Revenue", credit: 0.3 }),
      dataRow({ subtotal: true }),
      totalRow(0.3, 0.3),
    );
    expect(() => parseJournalRows(rows)).not.toThrow();
  });

  it("rejects any non-blank row appearing after the grand total row", () => {
    const rows = sheet(
      dataRow({ trans: 1, date: D(2019, 1, 1), account: "Checking", debit: 100 }),
      dataRow({ account: "Revenue", credit: 100 }),
      dataRow({ subtotal: true }),
      totalRow(100, 100),
      dataRow({ trans: 2, date: D(2019, 2, 1), account: "Checking", debit: 5 }), // bogus trailer
    );
    expect(() => parseJournalRows(rows)).toThrow(
      /unexpected content after the grand total row/,
    );
  });
});
