excel-tools

import ExcelJS from "exceljs";
import * as XLSX from "xlsx";
import * as path from "path";

// ─── Loading ──────────────────────────────────────────────────────────────────

/**
 * Load any Excel/CSV format into an ExcelJS Workbook.
 * ExcelJS handles .xlsx/.xlsm/.csv natively.
 * SheetJS converts .xls/.xlsb/.ods/.tsv → xlsx buffer → ExcelJS.
 */
export async function loadWorkbook(filePath: string): Promise<ExcelJS.Workbook> {
  const ext = path.extname(filePath).toLowerCase();
  const wb = new ExcelJS.Workbook();

  if (ext === ".xlsx" || ext === ".xlsm") {
    await wb.xlsx.readFile(filePath);
  } else if (ext === ".csv") {
    await wb.csv.readFile(filePath);
  } else {
    // SheetJS → xlsx buffer → ExcelJS
    const sheetJsWb = XLSX.readFile(filePath, { bookVBA: true });
    const buf: Buffer = XLSX.write(sheetJsWb, { type: "buffer", bookType: "xlsx" });
    await wb.xlsx.load(buf.buffer as ArrayBuffer);
  }

  return wb;
}

/** Save workbook — always as .xlsx */
export async function saveWorkbook(wb: ExcelJS.Workbook, filePath: string): Promise<void> {
  await wb.xlsx.writeFile(filePath);
}

// ─── Sheet helpers ────────────────────────────────────────────────────────────

export function getSheet(wb: ExcelJS.Workbook, name?: string): ExcelJS.Worksheet | string {
  const ws = name ? wb.getWorksheet(name) : wb.worksheets[0];
  if (!ws) {
    const available = wb.worksheets.map((s) => s.name).join(", ");
    return name
      ? `Sheet "${name}" not found. Available: ${available}`
      : "Workbook has no sheets";
  }
  return ws;
}

// ─── Cell value extraction ────────────────────────────────────────────────────

export function getCellValue(cell: ExcelJS.Cell): unknown {
  const val = cell.value;
  if (val === null || val === undefined) return null;
  if (val instanceof Date) return val.toISOString().split("T")[0];
  if (typeof val === "object") {
    if ("result" in val) return (val as ExcelJS.CellFormulaValue).result ?? null;
    if ("richText" in val) return (val as ExcelJS.CellRichTextValue).richText.map((r) => r.text).join("");
    if ("text" in val) return (val as ExcelJS.CellHyperlinkValue).text;
    if ("error" in val) return `#${(val as ExcelJS.CellErrorValue).error}`;
  }
  return val;
}

// ─── Conversion helpers ───────────────────────────────────────────────────────

export function worksheetToJson(ws: ExcelJS.Worksheet): Record<string, unknown>[] {
  const headers: string[] = [];
  ws.getRow(1).eachCell({ includeEmpty: false }, (cell, col) => {
    headers[col - 1] = String(getCellValue(cell) ?? "");
  });
  if (!headers.length) return [];

  const rows: Record<string, unknown>[] = [];
  ws.eachRow((row, rowNum) => {
    if (rowNum === 1) return;
    const obj: Record<string, unknown> = {};
    let hasData = false;
    headers.forEach((h, i) => {
      if (!h) return;
      const v = getCellValue(row.getCell(i + 1));
      obj[h] = v;
      if (v !== null && v !== undefined) hasData = true;
    });
    if (hasData) rows.push(obj);
  });
  return rows;
}

export interface WriteOptions {
  boldHeaders?: boolean;
  autoWidth?: boolean;
  headerFill?: string; // ARGB hex, default "FFE0E0E0"
}

export function jsonToWorksheet(
  ws: ExcelJS.Worksheet,
  data: Record<string, unknown>[],
  opts: WriteOptions = {}
): void {
  if (!data.length) return;
  const headers = Object.keys(data[0]);
  const { boldHeaders = true, autoWidth = true, headerFill = "FFE0E0E0" } = opts;

  const hRow = ws.addRow(headers);
  if (boldHeaders) {
    hRow.font = { bold: true };
    hRow.fill = { type: "pattern", pattern: "solid", fgColor: { argb: headerFill } };
  }
  hRow.commit();

  data.forEach((row) => {
    ws.addRow(headers.map((h) => row[h] ?? null)).commit();
  });

  if (autoWidth) {
    ws.columns.forEach((col, i) => {
      const h = headers[i] ?? "";
      const max = Math.max(h.length, ...data.slice(0, 200).map((r) => String(r[h] ?? "").length));
      col.width = Math.min(max + 2, 60);
    });
  }
}

// ─── Filter helpers ───────────────────────────────────────────────────────────

export interface FilterCondition {
  column: string;
  operator: "=" | "!=" | ">" | "<" | ">=" | "<=" | "contains" | "startsWith" | "endsWith" | "regex" | "isNull" | "isNotNull";
  value?: string | number;
}

export function matchesFilters(row: Record<string, unknown>, filters: FilterCondition[]): boolean {
  return filters.every(({ column, operator, value }) => {
    const cell = row[column];
    const cs = String(cell ?? "").toLowerCase();
    const vs = String(value ?? "").toLowerCase();
    switch (operator) {
      case "=":          return cs === vs;
      case "!=":         return cs !== vs;
      case ">":          return Number(cell) > Number(value);
      case "<":          return Number(cell) < Number(value);
      case ">=":         return Number(cell) >= Number(value);
      case "<=":         return Number(cell) <= Number(value);
      case "contains":   return cs.includes(vs);
      case "startsWith": return cs.startsWith(vs);
      case "endsWith":   return cs.endsWith(vs);
      case "regex":      return new RegExp(String(value ?? ""), "i").test(String(cell ?? ""));
      case "isNull":     return cell === null || cell === undefined || cell === "";
      case "isNotNull":  return cell !== null && cell !== undefined && cell !== "";
      default:           return true;
    }
  });
}

// ─── Misc ─────────────────────────────────────────────────────────────────────

export function colIndexToLetter(n: number): string {
  let r = "";
  while (n > 0) { r = String.fromCharCode(64 + ((n - 1) % 26 + 1)) + r; n = Math.floor((n - 1) / 26); }
  return r;
}

export function preview(rows: Record<string, unknown>[], n = 5): Record<string, unknown>[] {
  return rows.slice(0, n);
}

/**
 * Reliably clear all rows from a worksheet.
 * ExcelJS spliceRows(1, rowCount) can under-count rows → duplication.
 * This iterates from the last actual row down to 1 and splices each.
 */
export function clearWorksheet(ws: ExcelJS.Worksheet): void {
  const count = ws.actualRowCount;
  for (let i = count; i >= 1; i--) {
    ws.spliceRows(i, 1);
  }
}