// Pure logic for turning a QuickBooks Trial Balance export into per-account
// debit/credit rows, for year-end close comparison (Task 6 of
// docs/superpowers/plans/2026-07-31-local-app.md). No I/O here on purpose —
// everything is testable without a database or a real xlsx file, matching
// tools/chart-logic.ts and tools/journal-logic.ts.
//
// NOTE (binding): we do NOT have a real QuickBooks Trial Balance export to
// build against yet. QuickBooks Desktop's exports share a family shape
// (see qb-xlsx.ts's TIPS_SHEET_NAME handling, chart-logic.ts, journal-logic.ts):
// a "QuickBooks Desktop Export Tips" sheet (skipped entirely by readQbSheet
// before this module ever sees a row), then a data sheet with some leading
// title rows, a header row, data rows, and a trailing TOTAL row. This parser
// is built against a CONSTRUCTED fixture consistent with that family shape
// and is deliberately tolerant of the parts of the shape that are likely to
// vary (title rows before the header, the exact header row number) so that
// adapting it to the real export, once available, should be a small,
// localized change — not a rewrite.
//
// Unlike the Account Listing / Journal exports (fixed header row 1, fixed
// column numbers), a Trial Balance's header row position and column layout
// aren't assumed fixed: the header row is LOCATED by content (a row
// containing both a "Debit" and a "Credit" cell), and the account-name
// column is inferred as the rightmost non-empty text column to the left of
// Debit — never a hardcoded column index.

import { cellText } from "./chart-logic.js";
import { centsFromAmountString, centsToStr, journalAmount } from "./journal-logic.js";

export interface TbRow {
  account: string;
  side: "debit" | "credit";
  /** ".toFixed(2)" string — always non-negative; sign is carried by `side`. */
  amount: string;
}

interface HeaderLocation {
  rowIndex: number;
  accountCol: number;
  debitCol: number;
  creditCol: number;
}

/** Never throws — used only to scan for the header row / TOTAL row among
 *  rows we haven't committed to treating as real data yet (title rows above
 *  the header may contain merged/decorative cells we don't need to parse
 *  strictly). Real data-row extraction below uses cellText() directly,
 *  which throws loudly on anything it can't read (per CLAUDE.md — never
 *  silently mangle data). */
function safeText(value: unknown): string {
  try {
    return cellText(value);
  } catch {
    return "";
  }
}

/**
 * Locates the header row by scanning for a row containing (case-insensitive)
 * both a "Debit" cell and a "Credit" cell — tolerant of any number of
 * leading title rows (company name, report title, "As of ..." date line).
 * The account-name column is the rightmost non-empty text column to the
 * left of the Debit column, per the plan.
 */
function findHeaderRow(sheetRows: unknown[][]): HeaderLocation {
  for (let i = 1; i < sheetRows.length; i++) {
    const row = sheetRows[i];
    if (!row) continue;

    let debitCol = -1;
    let creditCol = -1;
    for (let c = 0; c < row.length; c++) {
      const text = safeText(row[c]).toLowerCase();
      if (text === "debit") debitCol = c;
      if (text === "credit") creditCol = c;
    }
    if (debitCol === -1 || creditCol === -1) continue;

    let accountCol = -1;
    for (let c = 0; c < debitCol; c++) {
      if (safeText(row[c]) !== "") accountCol = c;
    }
    if (accountCol === -1) {
      throw new Error(
        `row ${i}: found a Debit/Credit header row but no account-name column to its left`,
      );
    }
    return { rowIndex: i, accountCol, debitCol, creditCol };
  }
  throw new Error(
    "no header row found — expected a row with both a 'Debit' cell and a 'Credit' cell",
  );
}

// Debit/Credit cell -> ".toFixed(2)" string conversion is journal-logic.ts's
// journalAmount(), imported above and reused as-is (not reimplemented here):
// a Trial Balance's Debit/Credit cells are the exact same kind of xlsx
// number cell as a Journal's Debit/Credit cells (both QuickBooks Desktop
// exports of a 2-decimal dollar figure), so this module defers to the
// SAME conversion journal-logic.ts already uses rather than a second,
// independently-written copy that could silently diverge from it.

/**
 * The report's grand total (the TOTAL row's own Debit/Credit cells) must
 * equal the sum of every row parsed so far. Compared as integer cents
 * (reusing journal-logic.ts's exact approach) so no rounding drift can hide
 * a real mismatch or flag a false one.
 */
function verifyGrandTotal(
  rowNum: number, fileDebitText: string, fileCreditText: string, rows: readonly TbRow[],
): void {
  const fileDebitCents = centsFromAmountString(fileDebitText);
  const fileCreditCents = centsFromAmountString(fileCreditText);

  let debitCents = 0;
  let creditCents = 0;
  for (const r of rows) {
    if (r.side === "debit") debitCents += centsFromAmountString(r.amount);
    else creditCents += centsFromAmountString(r.amount);
  }

  const mismatches: string[] = [];
  if (fileDebitCents !== debitCents) {
    mismatches.push(
      `file's TOTAL row says ${centsToStr(fileDebitCents)} in debits, parsed rows sum to ` +
        `${centsToStr(debitCents)}`,
    );
  }
  if (fileCreditCents !== creditCents) {
    mismatches.push(
      `file's TOTAL row says ${centsToStr(fileCreditCents)} in credits, parsed rows sum to ` +
        `${centsToStr(creditCents)}`,
    );
  }
  if (mismatches.length > 0) {
    throw new Error(`row ${rowNum}: TOTAL row mismatch: ${mismatches.join("; ")}`);
  }
}

/**
 * Parses a QuickBooks Trial Balance export's rows (see qb-xlsx.ts's
 * readQbSheet — 1-indexed by row number, and each row's own cells 1-indexed
 * by column) into one row per account.
 *
 * Tolerant of any number of leading title rows before the header (located by
 * content, not row number). After the header: a blank row (account name AND
 * both amount cells all empty) is skipped; a row whose account-name cell
 * reads "TOTAL" (case-insensitive) ends parsing after verifying the file's
 * own stated grand total ties to the sum of every row parsed so far (a loud
 * mismatch error otherwise — reusing journal-logic.ts's integer-cents
 * approach); any content after the TOTAL row is rejected loudly. A row with
 * both a Debit and a Credit value set, or an amount with a blank account
 * name, is rejected loudly rather than guessed at.
 */
export function parseTrialBalanceRows(sheetRows: unknown[][]): TbRow[] {
  const header = findHeaderRow(sheetRows);
  const rows: TbRow[] = [];
  let sawTotal = false;

  for (let i = header.rowIndex + 1; i < sheetRows.length; i++) {
    const row = sheetRows[i];
    if (!row) continue;

    if (sawTotal) {
      throw new Error(`row ${i}: unexpected content after the TOTAL row`);
    }

    const name = cellText(row[header.accountCol]);
    const debitText = journalAmount(row[header.debitCol], i, "Debit");
    const creditText = journalAmount(row[header.creditCol], i, "Credit");

    if (name === "" && debitText === "" && creditText === "") continue; // blank row

    if (name.toLowerCase() === "total") {
      verifyGrandTotal(i, debitText, creditText, rows);
      sawTotal = true;
      continue;
    }

    if (debitText === "" && creditText === "") continue; // e.g. a group/section heading row
    if (debitText !== "" && creditText !== "") {
      throw new Error(
        `row ${i}: both Debit and Credit are set for "${name}" — a trial balance row must have exactly one`,
      );
    }
    if (name === "") {
      throw new Error(`row ${i}: an amount is present but the account name is blank`);
    }

    rows.push({
      account: name,
      side: debitText !== "" ? "debit" : "credit",
      amount: debitText !== "" ? debitText : creditText,
    });
  }

  if (!sawTotal) {
    throw new Error("no TOTAL row found — could not verify the file's totals");
  }

  return rows;
}
