Workflow-App/Workflow/lib/sars-export.ts

// lib/sars-export.ts
// CSV export for a captured SARS run.
//
// Follows the existing client-side export pattern in this app
// (app/registrations/page.tsx, components/GroupReport.tsx): build a
// string, wrap it in a Blob, click a synthetic <a download>. No library
// — and deliberately none added, since a .xlsx writer would be a new
// dependency for a one-screen need.
//
// WHY A FLAT LEDGER RATHER THAN "JUST THE TABLE"
//   The captured rows alone are the least useful part of a run: they only
//   cover periods SARS had data for. The `not_filed` list is what the
//   accountant acts on. Exporting one row per (company, period) with an
//   explicit Status keeps both in one file and makes the file sortable and
//   pivotable — which is what an accountant does with it next.
//
// STATUS VALUES
//   "Filed"     — the .py captured a row for that period
//   "Not filed" — requested, and SARS's period list had no row for it
//   "Error"     — a runtime/script failure, carried as its own row so a
//                  failure can never be mistaken for an unfiled return

export interface ExportColumn {
  key: string;
  label: string;
}

export interface ExportResultRow {
  period: string;
  [key: string]: unknown;
}

export interface ExportCompany {
  name: string;
  results: ExportResultRow[];
  not_filed: string[];
  errors: string[];
}

export interface ExportRun {
  return_type: string;
  periods_requested: string[];
  captured_at: string;
  companies: ExportCompany[];
}

/**
 * Escape one CSV cell.
 *
 * Every cell is quoted, unconditionally. Amounts from the .py can carry
 * odd whitespace and a stray quote would silently shift every following
 * column into the wrong field — the failure mode is invisible until a
 * total is wrong, so quoting everything is the cheap insurance.
 */
function cell(value: unknown): string {
  const s = value == null ? "" : String(value);
  return `"${s.replace(/"/g, '""')}"`;
}

/**
 * Build the CSV body (header + rows).
 *
 * Exported separately from the download so it can be unit-tested without
 * touching the DOM.
 */
export function buildSarsRunCsv(run: ExportRun, columns: ExportColumn[]): string {
  const header = ["Company", "Period", "Status", ...columns.map((c) => c.label), "Errors"];

  const lines: string[] = [header.map(cell).join(",")];

  for (const company of run.companies ?? []) {
    const byPeriod = new Map<string, ExportResultRow>();
    for (const r of company.results ?? []) {
      if (r?.period) byPeriod.set(String(r.period), r);
    }

    // One row per period the run actually asked about: everything with
    // data, plus everything reported as missing. YYYYMM sorts correctly
    // as a plain string, so no date parsing is needed.
    const periods = Array.from(
      new Set([...byPeriod.keys(), ...(company.not_filed ?? []).filter(Boolean).map(String)])
    ).sort();

    for (const period of periods) {
      const row = byPeriod.get(period);
      const status = row ? "Filed" : "Not filed";
      lines.push(
        [
          company.name,
          period,
          status,
          ...columns.map((c) => (row ? row[c.key] ?? "" : "")),
          "",
        ]
          .map(cell)
          .join(",")
      );
    }

    // Failures are their own rows so they are never silently dropped and
    // never read as "this company simply filed nothing".
    for (const err of company.errors ?? []) {
      lines.push(
        [company.name, "", "Error", ...columns.map(() => ""), err].map(cell).join(",")
      );
    }
  }

  return lines.join("\n");
}

/**
 * Trigger a browser download of the CSV.
 *
 * A UTF-8 BOM is prepended because Excel on Windows assumes the ANSI code
 * page for a bare .csv — without it, accented characters in a company name
 * (é, ô, ñ) come out as mojibake. Excel's "import" dialog is not needed
 * once the BOM is present.
 */
export function downloadSarsRunCsv(run: ExportRun, columns: ExportColumn[]): void {
  const csv = buildSarsRunCsv(run, columns);
  const stamp = new Date().toISOString().slice(0, 10);
  const name = `sars-${run.return_type || "returns"}-${stamp}.csv`;

  const blob = new Blob(["\uFEFF", csv], { type: "text/csv;charset=utf-8" });
  const url = URL.createObjectURL(blob);
  const a = document.createElement("a");
  a.href = url;
  a.download = name;
  a.click();
  URL.revokeObjectURL(url);
}

/** Row count excluding the header — used for the button's title/tooltip. */
export function countSarsRunRows(run: ExportRun): number {
  let n = 0;
  for (const c of run.companies ?? []) {
    n += new Set([
      ...(c.results ?? []).map((r) => String(r.period)),
      ...(c.not_filed ?? []).map(String),
    ]).size;
    n += (c.errors ?? []).length;
  }
  return n;
}