// Shared xlsx-reading helper for the universal import ("Restore from other
// software" — docs/superpowers/plans/2026-08-02-switch-from-anywhere.md,
// Task 4). Mirrors tools/qb-xlsx.ts's ExcelJS conventions, but the output is
// what universal-map-logic consumes: DENSE, 0-indexed string[][] rows — the
// same shape parseCsvRows produces for CSV files — so the engine never knows
// (or cares) which file type the rows came from.
//
// Cell coercion policy (differs from chart-logic's cellText on purpose):
//  - Date cells become ISO "yyyy-mm-dd" (UTC date part). The engine's
//    YYYY-MM-DD date format then parses them exactly; JS's default
//    Date.toString() ("Wed Jan 15 2020 ...") would parse as nothing.
//  - Numbers become String(n) — amounts, account codes, transaction numbers
//    all read back as the digits the sheet stored. Anything that renders
//    with float dirt (3+ decimal places) is refused loudly downstream by
//    parseUniversalAmount, never silently truncated.
//  - Rich text and formula-with-scalar-result cells are unwrapped, like
//    chart-logic.cellText; anything else unreadable throws a loud error
//    naming the cell, rather than mangling data into "[object Object]".

import ExcelJS from "exceljs";

function cellToString(value: unknown, rowNum: number, colNum: number): string {
  if (value === undefined || value === null) return "";
  if (typeof value === "string") return value;
  if (typeof value === "number" || typeof value === "boolean") return String(value);
  if (value instanceof Date) return value.toISOString().slice(0, 10);

  if (typeof value === "object") {
    const obj = value as Record<string, unknown>;

    if (Array.isArray(obj["richText"])) {
      return (obj["richText"] as Array<{ text?: unknown }>)
        .map((run) => (typeof run?.text === "string" ? run.text : ""))
        .join("");
    }

    if ("formula" in obj || "sharedFormula" in obj) {
      const result = obj["result"];
      if (typeof result === "string" || typeof result === "number") return String(result);
      if (result instanceof Date) return result.toISOString().slice(0, 10);
      throw new Error(
        `row ${rowNum}, column ${colNum}: this cell is a formula without a readable result — ` +
          `export the report as values (or CSV) and try again.`,
      );
    }

    // ExcelJS hyperlink cells: { text, hyperlink }.
    if (typeof obj["text"] === "string") return obj["text"];
  }

  throw new Error(
    `row ${rowNum}, column ${colNum}: this cell couldn't be read as text — ` +
      `export the report as values (or CSV) and try again.`,
  );
}

/**
 * Opens a workbook and returns the FIRST worksheet's rows as dense,
 * 0-indexed string[][] (row 0 is sheet row 1; each row's index 0 is column
 * A) — the same shape tools/csv-map-logic.ts's parseCsvRows produces, ready
 * for universal-map-logic's sniffHeaders/applyChartMapping/
 * applyJournalMapping. Empty sheet rows come back as [] (the engine skips
 * blank rows itself).
 */
export async function readUniversalSheet(filePath: string): Promise<string[][]> {
  const workbook = new ExcelJS.Workbook();
  try {
    await workbook.xlsx.readFile(filePath);
  } catch (err) {
    throw new Error(
      `could not read xlsx file: ${(err as Error).message}`,
      { cause: err },
    );
  }

  const sheet = workbook.worksheets[0];
  if (!sheet) {
    throw new Error("that workbook has no worksheets in it.");
  }

  const rows: string[][] = [];
  sheet.eachRow({ includeEmpty: true }, (row) => {
    const values = row.values;
    // ExcelJS Row.values is 1-indexed (index 0 unused) — shift to 0-indexed.
    const cells = Array.isArray(values) ? values.slice(1) : [];
    rows[row.number - 1] = cells.map((v, c) => cellToString(v, row.number, c + 1));
  });
  // eachRow skips leading gaps in sparse sheets — fill holes with [].
  for (let i = 0; i < rows.length; i++) {
    if (rows[i] === undefined) rows[i] = [];
  }
  return rows;
}
