// Pure CSV statement parsing + column-mapping logic for bank statement
// import. No I/O here on purpose — everything is testable without a database
// or a real file, matching tools/journal-logic.ts and tools/tb-logic.ts. See
// docs/superpowers/specs/2026-08-01-bank-rec-design.md ("Formats") and
// docs/superpowers/plans/2026-08-01-bank-rec.md, Task 2.
//
// Three layers, used in sequence by the mapping UI:
//  1. parseCsvRows: text -> raw string[][], RFC4180-ish (quoted fields,
//     embedded commas/quotes/newlines, CRLF/LF, BOM).
//  2. detectCsv: raw rows -> a preview + best-effort SUGGESTED mapping (the
//     UI always lets the user confirm/override before anything is applied).
//  3. applyCsvMapping: raw rows + a CONFIRMED mapping -> statement lines,
//     collecting per-row errors rather than throwing (one malformed row must
//     not sink an otherwise-good import).
//
// Money never touches parseFloat/Number on a raw amount string — only
// pure-digit whole/fraction substrings are ever handed to Number(), and the
// final string is produced via journal-logic.ts's centsToStr, exactly like
// every other money value in this codebase.

import { centsToStr } from "./journal-logic.js";

export type AmountMode = "signed" | "debit-credit";
export type DateFormat = "MDY" | "DMY" | "YMD";

export interface CsvDetection {
  headerRow: string[] | null;
  /** First 6 data rows (i.e. excluding headerRow), for a mapping-step preview. */
  sampleRows: string[][];
  columnCount: number;
  dateCol?: number;
  descriptionCol?: number;
  amountCol?: number;
  debitCol?: number;
  creditCol?: number;
  dateFormat?: DateFormat;
}

export interface CsvMapping {
  headerRows: number;
  dateCol: number;
  descriptionCol: number;
  amountMode: AmountMode;
  amountCol?: number;
  debitCol?: number;
  creditCol?: number;
  /** Flips the final sign after amountMode's own convention is applied. */
  negateAmount?: boolean;
  dateFormat: DateFormat;
}

export interface CsvMappedLine {
  postedDate: string;
  description: string;
  /** Signed 2dp string (bank convention: positive = money in). */
  amount: string;
}

export interface CsvMappedError {
  /** 1-indexed row number within the raw parsed rows (matches journal-logic's convention). */
  row: number;
  message: string;
}

function stripBom(text: string): string {
  return text.charCodeAt(0) === 0xfeff ? text.slice(1) : text;
}

/**
 * Excel's "force text" export trick wraps a cell in ="..." (a formula whose
 * result is the literal string) to stop Excel from re-interpreting a
 * zero-padded date/account number on the next open. Since the leading "="
 * means the field never starts with a bare quote, parseCsvRows's quote
 * handling below treats the whole thing as ordinary unquoted text — so we
 * unwrap it here, once the field's full raw text is known.
 */
function unwrapExcelFormula(field: string): string {
  const m = /^="(.*)"$/.exec(field);
  return m ? m[1]! : field;
}

/**
 * RFC4180-ish CSV parser: quoted fields (with embedded commas, quotes via the
 * "" escape, and embedded newlines), CRLF or bare LF line endings, a leading
 * BOM stripped, and trailing wholly-empty lines dropped. Never splits on "\n"
 * first — that would break embedded newlines inside quoted fields — instead
 * walks the text one character at a time with an in-quotes flag.
 *
 * A quote only OPENS a quoted field when it is the field's very FIRST
 * character (i.e. right after a delimiter, a newline, or the start of the
 * file) — per RFC 4180, a bare quote appearing mid-field (an inch mark, a
 * possessive, etc.) is ordinary literal text, not a mode switch. Getting this
 * wrong silently swallows every row between two stray quotes.
 *
 * A quoted field left open at EOF is a loud error naming the row it opened
 * on, rather than silently discarding the rest of the file.
 */
export function parseCsvRows(text: string): string[][] {
  const s = stripBom(text);
  const rows: string[][] = [];
  let row: string[] = [];
  let field = "";
  let inQuotes = false;
  let quoteOpenedAtRow = 0;
  let i = 0;
  const n = s.length;

  while (i < n) {
    const c = s[i]!;

    if (inQuotes) {
      if (c === '"') {
        if (s[i + 1] === '"') {
          field += '"';
          i += 2;
          continue;
        }
        inQuotes = false;
        i++;
        continue;
      }
      field += c;
      i++;
      continue;
    }

    if (c === '"' && field === "") {
      inQuotes = true;
      quoteOpenedAtRow = rows.length + 1;
      i++;
      continue;
    }
    if (c === ",") {
      row.push(unwrapExcelFormula(field));
      field = "";
      i++;
      continue;
    }
    if (c === "\r") {
      i++; // swallow; the following "\n" (or EOF) ends the row
      continue;
    }
    if (c === "\n") {
      row.push(unwrapExcelFormula(field));
      rows.push(row);
      row = [];
      field = "";
      i++;
      continue;
    }
    field += c;
    i++;
  }

  if (inQuotes) {
    throw new Error(
      `A quote mark opened on row ${quoteOpenedAtRow} was never closed — check row ` +
        `${quoteOpenedAtRow}'s description for a stray quote.`,
    );
  }

  // Flush a final field/row for a file that doesn't end with a newline.
  if (field !== "" || row.length > 0) {
    row.push(unwrapExcelFormula(field));
    rows.push(row);
  }

  // Trailing wholly-empty lines (a single empty-string field) are dropped —
  // a common artifact of spreadsheet exports.
  while (rows.length > 0) {
    const last = rows[rows.length - 1]!;
    if (last.length === 1 && last[0] === "") rows.pop();
    else break;
  }

  return rows;
}

const DATE_HEADER_WORDS = ["date"];
const DESCRIPTION_HEADER_WORDS = ["description", "memo", "payee", "name"];
const AMOUNT_HEADER_WORDS = ["amount"];
const DEBIT_HEADER_WORDS = ["debit", "withdrawal", "withdrawals"];
const CREDIT_HEADER_WORDS = ["credit", "deposit", "deposits"];

function headerMatches(cell: string, words: string[]): boolean {
  const lower = cell.trim().toLowerCase();
  return words.some((w) => lower.includes(w));
}

/**
 * Parses the raw text and returns a preview (header + first 6 data rows)
 * plus best-effort column guesses by header name and, for the date column,
 * by the shape of its sample values. Guesses are SUGGESTIONS — the mapping
 * UI always lets the user confirm or override before applyCsvMapping runs.
 *
 * If row 1 itself looks like DATA (a date-shaped cell alongside an
 * amount-shaped cell) rather than a header, the file is treated as
 * headerless: headerRow is null, row 1 is included in sampleRows, and
 * columns are guessed by cell SHAPE only (no header names to go on).
 */
export function detectCsv(text: string): CsvDetection {
  const rows = parseCsvRows(text);
  if (rows.length === 0) {
    return { headerRow: null, sampleRows: [], columnCount: 0 };
  }

  if (looksLikeDataRow(rows[0]!)) {
    return detectHeaderless(rows);
  }

  const headerRow = rows[0]!;
  const sampleRows = rows.slice(1, 7);
  const columnCount = Math.max(headerRow.length, ...sampleRows.map((r) => r.length), 0);

  const detection: CsvDetection = { headerRow, sampleRows, columnCount };

  for (let c = 0; c < headerRow.length; c++) {
    const cell = headerRow[c]!;
    if (detection.dateCol === undefined && headerMatches(cell, DATE_HEADER_WORDS)) {
      detection.dateCol = c;
    }
    if (detection.descriptionCol === undefined && headerMatches(cell, DESCRIPTION_HEADER_WORDS)) {
      detection.descriptionCol = c;
    }
    if (detection.amountCol === undefined && headerMatches(cell, AMOUNT_HEADER_WORDS)) {
      detection.amountCol = c;
    }
    if (detection.debitCol === undefined && headerMatches(cell, DEBIT_HEADER_WORDS)) {
      detection.debitCol = c;
    }
    if (detection.creditCol === undefined && headerMatches(cell, CREDIT_HEADER_WORDS)) {
      detection.creditCol = c;
    }
  }

  // Debit/credit and a single signed amount column are mutually exclusive
  // modes; when both got guessed (e.g. a header row with stray "Amount" text
  // alongside real Debit/Credit columns), debit/credit wins as the guess.
  if (detection.debitCol !== undefined || detection.creditCol !== undefined) {
    detection.amountCol = undefined;
  }

  if (detection.dateCol !== undefined) {
    for (const sample of sampleRows) {
      const value = sample[detection.dateCol];
      if (value === undefined || value.trim() === "") continue;
      const guessed = guessDateFormat(value);
      if (guessed) detection.dateFormat = guessed;
      break;
    }
  }

  return detection;
}

/** True when a row's cells themselves look like a DATA row (a date-shaped
 *  cell AND an amount-shaped cell present somewhere in it) rather than a
 *  header row of column labels — the signal used to detect a headerless file. */
function looksLikeDataRow(row: string[]): boolean {
  let hasDate = false;
  let hasAmount = false;
  for (const cell of row) {
    if (!hasDate && looksLikeDateCell(cell)) hasDate = true;
    if (!hasAmount && looksLikeAmountCell(cell)) hasAmount = true;
  }
  return hasDate && hasAmount;
}

function looksLikeDateCell(cell: string): boolean {
  return guessDateFormat(cell) !== undefined;
}

/** Shape-only check mirroring parseCsvAmountCents's tolerance (parentheses,
 *  "$", comma thousands, trailing minus) without throwing — used only for
 *  guessing, never for the actual conversion. */
function looksLikeAmountCell(cell: string): boolean {
  let s = cell.trim();
  if (s === "") return false;
  if (s.startsWith("(") && s.endsWith(")")) s = s.slice(1, -1).trim();
  s = s.replace(/\$/g, "").replace(/,/g, "").trim();
  if (s.endsWith("-")) s = s.slice(0, -1).trim();
  if (s.startsWith("-") || s.startsWith("+")) s = s.slice(1).trim();
  return /^\d+(\.\d{1,2})?$/.test(s);
}

/** Headerless-file detection: row 1 is real data, so it's included in the
 *  preview and every column guess comes from cell SHAPE alone. */
function detectHeaderless(rows: string[][]): CsvDetection {
  const sampleRows = rows.slice(0, 6);
  const columnCount = Math.max(0, ...sampleRows.map((r) => r.length));
  const detection: CsvDetection = { headerRow: null, sampleRows, columnCount };

  const firstRow = rows[0]!;
  for (let c = 0; c < firstRow.length; c++) {
    const cell = firstRow[c]!;
    if (detection.dateCol === undefined && looksLikeDateCell(cell)) {
      detection.dateCol = c;
      continue;
    }
    if (detection.amountCol === undefined && looksLikeAmountCell(cell)) {
      detection.amountCol = c;
      continue;
    }
    if (detection.descriptionCol === undefined && cell.trim() !== "") {
      detection.descriptionCol = c;
    }
  }

  if (detection.dateCol !== undefined) {
    for (const sample of sampleRows) {
      const value = sample[detection.dateCol];
      if (value === undefined || value.trim() === "") continue;
      const guessed = guessDateFormat(value);
      if (guessed) detection.dateFormat = guessed;
      break;
    }
  }

  return detection;
}

/** Best-effort date-format guess from a single sample value's shape. */
function guessDateFormat(raw: string): DateFormat | undefined {
  const s = raw.trim();
  const m = /^(\d{1,4})([-/.])(\d{1,2})\2(\d{1,4})$/.exec(s);
  if (!m) return undefined;
  const [, a, , b, c] = m;

  if (a!.length === 4) return "YMD"; // leading 4-digit part can only be a year
  if (c!.length === 4 || c!.length === 2) {
    // trailing part is the year candidate; disambiguate the leading two
    // numeric parts by range when possible, otherwise default to US MDY.
    const first = Number(a);
    const second = Number(b);
    if (first > 12 && first <= 31) return "DMY";
    if (second > 12 && second <= 31) return "MDY";
    return "MDY";
  }
  return undefined;
}

function isEmptyRow(row: string[]): boolean {
  return row.every((c) => c.trim() === "");
}

/**
 * Applies a CONFIRMED mapping to raw parsed rows. Per-row date/amount errors
 * are collected into `errors` (not thrown) so one bad row doesn't sink an
 * otherwise-good import; a structurally invalid mapping (e.g. amountMode
 * "signed" with no amountCol) throws immediately since that's a configuration
 * mistake, not a data-quality one. Blank rows are skipped silently.
 */
export function applyCsvMapping(
  rows: string[][],
  mapping: CsvMapping,
): { lines: CsvMappedLine[]; errors: CsvMappedError[] } {
  if (mapping.amountMode === "signed" && mapping.amountCol === undefined) {
    throw new Error("amountMode is \"signed\" but no amountCol was provided.");
  }
  if (
    mapping.amountMode === "debit-credit" &&
    (mapping.debitCol === undefined || mapping.creditCol === undefined)
  ) {
    throw new Error(
      "amountMode is \"debit-credit\" but debitCol and/or creditCol was not provided.",
    );
  }

  const lines: CsvMappedLine[] = [];
  const errors: CsvMappedError[] = [];

  for (let i = mapping.headerRows; i < rows.length; i++) {
    const row = rows[i]!;
    const rowNum = i + 1;
    if (isEmptyRow(row)) continue;

    let postedDate: string;
    try {
      postedDate = parseCsvDate((row[mapping.dateCol] ?? "").trim(), mapping.dateFormat);
    } catch (e) {
      errors.push({ row: rowNum, message: (e as Error).message });
      continue;
    }

    const description = (row[mapping.descriptionCol] ?? "").trim();

    let amountCents: number;
    try {
      if (mapping.amountMode === "signed") {
        amountCents = parseCsvAmountCents(row[mapping.amountCol!] ?? "");
      } else {
        const debitRaw = (row[mapping.debitCol!] ?? "").trim();
        const creditRaw = (row[mapping.creditCol!] ?? "").trim();
        const debitCents = debitRaw === "" ? 0 : Math.abs(parseCsvAmountCents(debitRaw));
        const creditCents = creditRaw === "" ? 0 : Math.abs(parseCsvAmountCents(creditRaw));
        if (debitCents !== 0 && creditCents !== 0) {
          throw new Error(
            `both debit ("${debitRaw}") and credit ("${creditRaw}") are set — a statement line must have exactly one.`,
          );
        }
        if (debitCents === 0 && creditCents === 0) {
          throw new Error("neither debit nor credit has a value.");
        }
        // Bank convention: debit = money OUT = negative; credit = money IN = positive.
        amountCents = debitCents !== 0 ? -debitCents : creditCents;
      }
      if (mapping.negateAmount) amountCents = -amountCents;
    } catch (e) {
      errors.push({ row: rowNum, message: (e as Error).message });
      continue;
    }

    lines.push({ postedDate, description, amount: centsToStr(amountCents) });
  }

  return { lines, errors };
}

/**
 * Explicit-format date parsing (MDY/DMY/YMD), separators -/. , and 2- or
 * 4-digit years (2-digit years are assumed 20xx). Validates the calendar
 * (month <= 12, day within the month) rather than trusting the input.
 */
function parseCsvDate(raw: string, format: DateFormat): string {
  if (raw === "") {
    throw new Error("date is blank.");
  }
  const m = /^(\d{1,4})([-/.])(\d{1,2})\2(\d{1,4})$/.exec(raw);
  if (!m) {
    throw new Error(`unparseable date "${raw}".`);
  }
  const [, a, , b, c] = m as unknown as [string, string, string, string, string];

  let yearStr: string, monthStr: string, dayStr: string;
  if (format === "YMD") {
    [yearStr, monthStr, dayStr] = [a, b, c];
  } else if (format === "MDY") {
    [monthStr, dayStr, yearStr] = [a, b, c];
  } else {
    [dayStr, monthStr, yearStr] = [a, b, c];
  }

  if (yearStr.length !== 2 && yearStr.length !== 4) {
    throw new Error(`unparseable date "${raw}" — year must be 2 or 4 digits.`);
  }
  let year = Number(yearStr);
  if (yearStr.length === 2) year += 2000;

  const month = Number(monthStr);
  const day = Number(dayStr);
  if (month < 1 || month > 12) {
    throw new Error(`invalid month in date "${raw}".`);
  }
  const maxDay = new Date(Date.UTC(year, month, 0)).getUTCDate();
  if (day < 1 || day > maxDay) {
    throw new Error(`invalid day in date "${raw}".`);
  }

  return `${String(year).padStart(4, "0")}-${String(month).padStart(2, "0")}-${String(day).padStart(2, "0")}`;
}

/**
 * Signed decimal with tolerance for "(123.45)" (parentheses negative), a
 * leading "$", comma thousands separators, and a trailing minus ("123.45-").
 * Never parsed with parseFloat/Number on the raw string — only pure-digit
 * whole/fraction substrings are ever handed to Number(), exactly like
 * journal-logic.ts's centsFromAmountString.
 */
function parseCsvAmountCents(raw: string): number {
  let s = raw.trim();
  if (s === "") {
    throw new Error("amount is blank.");
  }

  let negative = false;
  if (s.startsWith("(") && s.endsWith(")")) {
    negative = true;
    s = s.slice(1, -1).trim();
  }

  s = s.replace(/\$/g, "").replace(/,/g, "").trim();

  if (s.endsWith("-")) {
    negative = true;
    s = s.slice(0, -1).trim();
  }
  if (s.startsWith("-")) {
    negative = true;
    s = s.slice(1).trim();
  } else if (s.startsWith("+")) {
    s = s.slice(1).trim();
  }

  if (!/^\d+(\.\d{1,2})?$/.test(s)) {
    throw new Error(`unparseable amount "${raw}".`);
  }
  const [wholePart, fractionPartRaw] = s.split(".");
  const fractionPart = (fractionPartRaw ?? "").padEnd(2, "0");
  const cents = Number(wholePart) * 100 + Number(fractionPart);
  return negative ? -cents : cents;
}

/**
 * English validation errors for a mapping the user is about to confirm:
 * required columns missing, out-of-range column indexes, and the same column
 * mapped to more than one field.
 */
export function validateMapping(mapping: CsvMapping, columnCount: number): string[] {
  const errors: string[] = [];
  const usedBy = new Map<number, string[]>();

  function checkCol(label: string, col: number | undefined, required: boolean): void {
    if (col === undefined) {
      if (required) errors.push(`${label} is not set.`);
      return;
    }
    if (!Number.isInteger(col) || col < 0 || col >= columnCount) {
      errors.push(
        `${label} (column ${col}) is out of range — this file has ${columnCount} column(s).`,
      );
      return;
    }
    const list = usedBy.get(col) ?? [];
    list.push(label);
    usedBy.set(col, list);
  }

  checkCol("Date column", mapping.dateCol, true);
  checkCol("Description column", mapping.descriptionCol, true);

  if (mapping.amountMode === "signed") {
    checkCol("Amount column", mapping.amountCol, true);
  } else if (mapping.amountMode === "debit-credit") {
    checkCol("Debit column", mapping.debitCol, true);
    checkCol("Credit column", mapping.creditCol, true);
  } else {
    errors.push(`Unknown amount mode "${String(mapping.amountMode)}".`);
  }

  if (!Number.isInteger(mapping.headerRows) || mapping.headerRows < 0) {
    errors.push("headerRows must be a non-negative whole number.");
  }

  for (const [col, labels] of usedBy) {
    if (labels.length > 1) {
      errors.push(`Column ${col} is mapped to more than one field: ${labels.join(", ")}.`);
    }
  }

  return errors;
}
