/**
 * CSV export helpers (RFC 4180) for admin downloads such as pilot leads.
 *
 * Two separate concerns:
 *  - Escaping: cells containing a comma, quote or line break are quoted and inner quotes doubled.
 *  - Formula injection (CSV injection): spreadsheet apps execute cells that start with `=`, `+`, `-`
 *    or `@` (and tab/CR, which some apps strip first). Text that comes from the public pilot form
 *    could carry `=HYPERLINK(...)`, so such cells get a leading apostrophe, which Excel, Sheets and
 *    LibreOffice treat as "this is text". Real numbers are left alone: they cannot hold a formula.
 */

const FORMULA_PREFIX = /^[=+\-@\t\r\n]/;
const NEEDS_QUOTES = /[",\r\n]/;

export type CsvValue = string | number | boolean | Date | null | undefined | readonly string[];

/** One escaped cell. Arrays are joined with "; ", dates become ISO 8601 (UTC). */
export function csvCell(value: CsvValue): string {
  if (value === null || value === undefined) return '';
  if (typeof value === 'number') return Number.isFinite(value) ? String(value) : '';
  if (typeof value === 'boolean') return value ? 'true' : 'false';
  let text: string;
  if (value instanceof Date) text = Number.isNaN(value.getTime()) ? '' : value.toISOString();
  else if (Array.isArray(value)) text = value.join('; ');
  else text = String(value);
  if (FORMULA_PREFIX.test(text)) text = `'${text}`;
  return NEEDS_QUOTES.test(text) ? `"${text.replace(/"/g, '""')}"` : text;
}

/**
 * A whole CSV document: header row + data rows, CRLF line endings, with a UTF-8 byte-order mark so
 * Excel opens accented venue names correctly.
 */
export function toCsv(header: readonly string[], rows: readonly (readonly CsvValue[])[]): string {
  const lines = [header.map((h) => csvCell(h)).join(',')];
  for (const row of rows) lines.push(row.map((cell) => csvCell(cell)).join(','));
  return `${lines.join('\r\n')}\r\n`;
}