// Shared xlsx-reading helper. Written for QuickBooks Desktop exports (Account
// Listing, Journal) — hence the tips-sheet skip below — but it reads any
// workbook, and the year-end close route (server/src/close.ts) uses it for a
// Trial Balance exported from ANY accounting software. Nothing it throws may
// assume the file came from QuickBooks.
// See docs/superpowers/plans/2026-07-30-qb-migration-loader.md.

import { basename } from "node:path";
import ExcelJS from "exceljs";

export const TIPS_SHEET_NAME = "QuickBooks Desktop Export Tips";

/**
 * Opens a QuickBooks export workbook and returns the data sheet's rows as
 * value arrays. The data sheet is the first worksheet whose name is not
 * TIPS_SHEET_NAME (in practice, "Sheet1").
 *
 * The returned array is 1-indexed by sheet row number (index 0 is unused;
 * index 1 is sheet row 1). Each row is itself 1-indexed by column, matching
 * ExcelJS's `Row.values` convention, so callers can address cells the same
 * way the plan's column numbers ("column 3", "column 5", ...) describe them.
 */
export async function readQbSheet(filePath: string): Promise<unknown[][]> {
  const workbook = new ExcelJS.Workbook();
  try {
    await workbook.xlsx.readFile(filePath);
  } catch (err) {
    // Name the file, never its path: these messages reach API callers verbatim
    // (close.ts / imports.ts send them as 422s) and a server path is both
    // meaningless to the reader and not ours to disclose.
    throw new Error(
      `could not read "${basename(filePath)}" as an Excel workbook: ${(err as Error).message}`,
      { cause: err },
    );
  }

  const sheet = workbook.worksheets.find((ws) => ws.name !== TIPS_SHEET_NAME);
  if (!sheet) {
    throw new Error(
      `no data sheet found in "${basename(filePath)}" — the workbook has no readable data sheet`,
    );
  }

  const rows: unknown[][] = [];
  sheet.eachRow({ includeEmpty: true }, (row) => {
    const values = row.values;
    rows[row.number] = Array.isArray(values) ? values : [];
  });
  return rows;
}
