import type {
  ColumnType,
  ImportColumnOverride,
  ImportFormat,
  ImportGroupOverride,
  ImportNotes,
  Reading,
} from '@wellformed/shared';
import { slugifyColumnName } from '@wellformed/shared';
import { badRequest } from '../errors.js';
import { inferReading, readDate, readNumber } from './reading.js';
import { readWorkbook, WorkbookError } from './workbook.js';

/**
 * Getting a spreadsheet in.
 *
 * This is the highest-leverage thing in the product's onboarding and also the
 * easiest place to be quietly wrong, so the rules are explicit:
 *
 * - Whatever they have is what they bring. XLSX and ODS are unpacked here as
 *   readily as CSV — see `workbook.ts` — because "export it as CSV first" asks
 *   someone to do work before they are allowed to find out whether this is any
 *   good, and plenty of them will simply not bother.
 * - Whichever format it arrived in, it becomes a grid of strings and takes the
 *   same path from there. One header-finder, one type inference, one set of
 *   tests; a workbook that had its own would drift from the CSV one by the
 *   second bug fix.
 * - The delimiter is **detected**, not assumed. Half of Europe exports CSV with
 *   semicolons, and a file that imports as one giant column reads as "this
 *   product is broken" rather than "wrong delimiter".
 * - Quoting follows RFC 4180: quotes protect delimiters and newlines, and a
 *   doubled quote inside a quoted field is one quote.
 * - Types are inferred from **every** value in the column, not the first few. A
 *   column that is numeric for 900 rows and then says "n/a" is a text column,
 *   and finding that out on row 901 is how imports lose data.
 * - A header becomes the column's *label*, word for word — "Prijs per stuk"
 *   stays "Prijs per stuk". The SQL identifier is derived from it, the same
 *   derivation the column dialog shows, so no header is ever illegal.
 *
 * Real exports are not tidy. They open with a title and a "generated on" line,
 * they breathe with blank rows, and they end their rows with the separators of
 * columns that were deleted in Excel years ago. None of that is the operator's
 * mistake to correct by hand in a text editor, so the parser skips past it —
 * and counts what it skipped, because a silent drop is indistinguishable from a
 * bug when the numbers come out wrong later.
 */
export const MAX_IMPORT_ROWS = 5_000;
export const MAX_IMPORT_COLUMNS = 60;

/** The identifier rules' own ceiling, which a rewritten header has to fit inside. */
const MAX_COLUMN_NAME = 48;

/** The catalogue's label ceiling, which a header has to be cut to fit. */
const MAX_COLUMN_LABEL = 60;

const DELIMITERS = [',', ';', '\t', '|'] as const;

/** Lines a delimiter is judged on. Enough to see a pattern, not the whole file. */
const SNIFF_LINES = 40;

/**
 * The delimiter that cuts the file into the widest consistent grid.
 *
 * Judged on a sample of lines rather than on the first one, because the first
 * line is so often a title: "Verkooprapport Q3" contains no semicolon at all
 * and would vote for a comma out of a file that has none either.
 *
 * The winner is the delimiter that most lines *agree* about — a real separator
 * produces the same field count on line after line, while a stray comma inside
 * prose produces a different count each time.
 */
export function detectDelimiter(text: string): string {
  const lines = text.split('\n').slice(0, SNIFF_LINES).filter((l) => l.trim() !== '');
  let best = ',';
  let bestScore = 0;

  for (const candidate of DELIMITERS) {
    // Counted through the parser, so a comma inside "Smith, Jane" does not vote.
    const counts = lines.map((line) => parseLine(line, candidate).length);
    const width = modeOf(counts.filter((n) => n > 1));
    if (width < 2) continue;

    // Width first, agreement as the tiebreak: a file of "a;b,c" is two columns
    // of semicolon before it is anything else.
    const agreeing = counts.filter((n) => n === width).length;
    const score = width * 1000 + agreeing;
    if (score > bestScore) {
      best = candidate;
      bestScore = score;
    }
  }
  return best;
}

/** The most common value, ties going to the largest. */
function modeOf(values: number[]): number {
  const tally = new Map<number, number>();
  for (const v of values) tally.set(v, (tally.get(v) ?? 0) + 1);

  let best = 0;
  let bestSeen = 0;
  for (const [value, seen] of tally) {
    if (seen > bestSeen || (seen === bestSeen && value > best)) {
      best = value;
      bestSeen = seen;
    }
  }
  return best;
}

function parseLine(line: string, delimiter: string): string[] {
  return parseCsv(line, delimiter)[0] ?? [];
}

/**
 * Parse a whole document into rows of raw strings.
 *
 * Blank rows are kept here and thrown away by `parseImport`, so that deciding
 * what counts as junk — and reporting how much of it there was — happens in one
 * place rather than being split between the two.
 */
export function parseCsv(text: string, delimiter: string): string[][] {
  const rows: string[][] = [];
  let row: string[] = [];
  let field = '';
  let quoted = false;
  let started = false;

  // A byte-order mark on the first header would become part of the first name.
  const body = text.charCodeAt(0) === 0xfeff ? text.slice(1) : text;

  const endField = () => {
    row.push(field);
    field = '';
    started = false;
  };
  const endRow = () => {
    endField();
    rows.push(row);
    row = [];
  };

  for (let i = 0; i < body.length; i++) {
    const char = body[i]!;

    if (quoted) {
      if (char === '"') {
        if (body[i + 1] === '"') {
          field += '"';
          i++;
        } else {
          quoted = false;
        }
      } else {
        field += char;
      }
      continue;
    }

    if (char === '"' && !started) {
      quoted = true;
      started = true;
      continue;
    }
    if (char === delimiter) {
      endField();
      continue;
    }
    if (char === '\r') continue;
    if (char === '\n') {
      endRow();
      continue;
    }
    field += char;
    started = true;
  }

  if (field !== '' || row.length > 0) endRow();

  return rows;
}

export interface ImportColumn {
  /** The identifier the column gets — or the existing column's, when appending. */
  name: string;
  /** What the column will be called, taken from the file unless overridden. */
  label: string;
  /** What the file called it, so a correction can say what it corrected. */
  header: string;
  /** The group heading this column sits under, or null outside any group. */
  group: string | null;
  type: ColumnType;
  /** Values that will not read as the type — only ever non-zero under an override. */
  unreadable: number;
  /** The first value that would not read, so the count points at something. */
  problem: string | null;
}

export interface ParsedImport {
  columns: ImportColumn[];
  /** One record per data row, keyed by column name, already coerced. */
  rows: Record<string, string | number | boolean | null>[];
  format: ImportFormat;
  /** What cut the text into columns, and `null` when a workbook did. */
  delimiter: string | null;
  /** The sheet the rows came from, and `null` for delimited text. */
  sheet: string | null;
  /** Every sheet in the workbook, in tab order, to choose another from. */
  sheets: string[];
  /** Rows dropped for being past the cap, so the caller can say so. */
  skipped: number;
  /** What was thrown away as junk, so the preview can own up to it. */
  ignored: ImportNotes;
  /** The row the column names came from, guessed unless it was given. */
  headerRow: number;
  /**
   * The row whose headings were folded in front of the column names — the
   * file's merged group cells, "Q1" over Omzet and Kosten — or null when no
   * row was used that way.
   */
  groupRow: number | null;
  /**
   * A row that could serve as the group row but is not being used. Reported
   * so the choice can be shown and taken rather than existing only inside
   * the guesswork.
   */
  groupCandidate: number | null;
  /**
   * The group headings in use, in the order the file shelves them, each with
   * the number of columns under it. What the group editor edits: a
   * correction addresses one of these by position and renames every column
   * in its run.
   */
  groups: { label: string; count: number }[];
  /** Trailing rows left out for being totals rather than data. */
  footerRows: number;
  /** How many the parser would leave out on its own, in use or not. */
  footerCandidate: number;
  /** The rows the footer guess points at, rendered for the preview. */
  footerText: string[];
  /** The opening rows, one line each, to choose another header from. */
  firstRows: string[];
}

export interface ImportOptions {
  /** Overrules the guess when the operator picked a row in the preview. */
  headerRow?: number;
  /**
   * The row whose headings group the columns. Undefined lets the parser
   * decide — it folds the row above the header in when the header repeats
   * its names, which is what grouped columns look like. `null` says no row
   * does, whatever the parser thinks.
   */
  groupRow?: number | null;
  /** Which sheet of a workbook to read; the first with anything in it by default. */
  sheet?: string;
  /**
   * How many trailing rows are totals rather than data. Undefined lets the
   * parser decide; 0 keeps every row, whatever it thinks.
   */
  footerRows?: number;
  /** Corrections to the guessed column labels and types, from the preview. */
  columns?: ImportColumnOverride[];
  /**
   * Corrections to the group headings, addressed by their position in the
   * preview's `groups`. One correction renames every column in the group;
   * corrected to nothing, the group stops prefixing its columns at all.
   */
  groups?: ImportGroupOverride[];
  /**
   * The target table's columns, when importing into one that exists. A header
   * is matched against them — by label first, since the label is what an
   * export would print, then by identifier for the files that carry those —
   * and a match takes the existing column's name and type: the column already
   * *is* a date, and the only question left is whether the values read as one.
   */
  tableColumns?: { name: string; label: string; type: ColumnType }[];
}

/**
 * What arrived: pasted text, or the bytes of a file.
 *
 * A file is not decoded to text before it gets here, because half the formats
 * worth accepting are not text at all — and the ones that are still need their
 * encoding worked out rather than assumed.
 */
export type ImportSource = string | { bytes: Buffer; filename?: string };

/** How far into the file the header may be chosen from. */
const HEADER_CHOICES = 15;

/**
 * The words a tick is written with. Exported because tightening a text column
 * into a checkbox afterwards has to accept exactly what importing it would
 * have, or the same file would mean two different things.
 */
export const TRUE_WORDS = new Set(['true', 'yes', 'y', 'x', '1', 'j', 'ja', 'waar']);
export const FALSE_WORDS = new Set(['false', 'no', 'n', '0', 'nee', 'onwaar']);
const ISO_DATE = /^\d{4}-\d{2}-\d{2}$/;
/**
 * A number, and deliberately not a code that happens to be written in digits.
 *
 * A leading zero is the giveaway: `0101010.02` is a ledger reference, `007` is
 * an account number, and importing either as a number silently drops the zeros
 * that made it unique. Nobody writes a quantity that way.
 */
const NUMERIC = /^-?(0|[1-9]\d*)(\.\d+)?$/;

/**
 * The identifier a label becomes, stepped past what this import already took.
 *
 * The same derivation the column dialog shows — see `slugifyColumnName` — so a
 * column made by import and a column made by hand from the same words carry
 * the same name. Uniqueness is stepped with `_2` exactly as the server steps
 * it, because two headers reading "Total" is a fact about the file, not an
 * error to hand back.
 */
export function columnNameFor(label: string, taken: Set<string>): string {
  const candidate = slugifyColumnName(label);
  let unique = candidate;
  for (let n = 2; taken.has(unique); n++) {
    unique = `${candidate.slice(0, MAX_COLUMN_NAME - 4)}_${n}`;
  }
  taken.add(unique);
  return unique;
}

/** The existing column a header speaks of, when importing into a table. */
function matchColumn(
  columns: { name: string; label: string; type: ColumnType }[],
  label: string,
): { name: string; label: string; type: ColumnType } | undefined {
  const folded = label.trim().toLowerCase();
  return (
    // The label is what an export prints, so it is the first thing to try…
    columns.find((c) => c.label.trim().toLowerCase() === folded) ??
    // …and the identifier answers for files that carry the SQL names.
    columns.find((c) => c.name === label.trim()) ??
    columns.find((c) => c.name === slugifyColumnName(label))
  );
}

/** A number written with money on it, which is what a currency column holds. */
const CURRENCY_MARK = /[€$£¥₹]/;

/**
 * What one column is, guessed through the same readers the tightening uses —
 * so `12-3-2021` and `€ 1.234,50` land as the date and the amount they are,
 * not as text to be repaired later. The reading (decimal comma or dot, day or
 * month first) is inferred from the column as a whole, exactly as a tightening
 * would infer it; a guess is only made when **every** value fits it, so an
 * inferred type can never refuse a row.
 *
 * The leading-zero rule survives from the stricter days: `007` is an account
 * number and `0101010.02` is a ledger reference, and reading either as a
 * number silently drops the zeros that made it unique.
 */
export function inferColumn(values: string[]): { type: ColumnType; reading: Required<Reading> } {
  const present = values.map((v) => v.trim()).filter((v) => v !== '');
  const { reading } = inferReading(values);
  if (present.length === 0) return { type: 'text', reading };

  // A mark column before a number column: a column of nothing but 1s (or x,
  // ja, yes) with gaps is somebody ticking rows, and reading it as the
  // number one loses the gesture. The gaps are what settle it — a column of
  // 1s with every row filled is a quantity and falls through to the number
  // branch — and a column carrying both a yes-word and a no-word is a
  // checkbox however full it is.
  const words = present.map((v) => v.toLowerCase());
  if (words.every((v) => TRUE_WORDS.has(v) || FALSE_WORDS.has(v))) {
    const gaps = present.length < values.length;
    const both = words.some((v) => TRUE_WORDS.has(v)) && words.some((v) => FALSE_WORDS.has(v));
    if (gaps || both) return { type: 'checkbox', reading };
  }

  const codeLike = present.some((v) => /^0\d/.test(v.replace(/^[\s€$£¥₹(+-]+/, '')));
  if (!codeLike && present.every((v) => readNumber(v, reading.decimal) !== null)) {
    return { type: present.some((v) => CURRENCY_MARK.test(v)) ? 'currency' : 'number', reading };
  }

  if (present.every((v) => readDate(v, reading.dayFirst) !== null)) return { type: 'date', reading };

  const lowered = present.map((v) => v.toLowerCase());
  if (lowered.every((v) => TRUE_WORDS.has(v) || FALSE_WORDS.has(v))) {
    return { type: 'checkbox', reading };
  }

  return { type: 'text', reading };
}

/** One cell under the column's type, or the news that it will not go. */
function readValue(
  raw: string,
  type: ColumnType,
  reading: Required<Reading>,
): { ok: boolean; value: string | number | boolean | null } {
  const value = raw.trim();
  if (value === '') return { ok: true, value: null };

  if (type === 'number' || type === 'currency') {
    const n = readNumber(value, reading.decimal);
    return n === null ? { ok: false, value: null } : { ok: true, value: n };
  }
  if (type === 'date') {
    const d = readDate(value, reading.dayFirst);
    return d === null ? { ok: false, value: null } : { ok: true, value: d };
  }
  if (type === 'checkbox') {
    const word = value.toLowerCase();
    if (TRUE_WORDS.has(word)) return { ok: true, value: true };
    if (FALSE_WORDS.has(word)) return { ok: true, value: false };
    return { ok: false, value: null };
  }
  return { ok: true, value };
}

/** How many cells in a row hold anything at all. */
function filled(row: string[]): number {
  return row.reduce((n, cell) => (cell.trim() === '' ? n : n + 1), 0);
}

/** A row of labels: nothing in it reads as a number or a date. */
function looksLikeLabels(row: string[]): boolean {
  const cells = row.map((c) => c.trim()).filter((c) => c !== '');
  return cells.length > 0 && cells.every((c) => !NUMERIC.test(c) && !ISO_DATE.test(c));
}

/** A row carrying at least one value no one would write as a column name. */
function hasTypedValue(row: string[]): boolean {
  return row.some((cell) => {
    const value = cell.trim();
    return value !== '' && (NUMERIC.test(value) || ISO_DATE.test(value));
  });
}

/**
 * The words a summary row announces itself with — "Totaal", "Subtotal",
 * "Grand total", "Gemiddelde". The whole cell must be the word (a colon
 * allowed), because a row is only *announced* as arithmetic when the label
 * stands alone: a product called "Total Repair Kit" is a row of data, and a
 * cell that says more than the word is saying something else.
 */
const SUMMARY_WORD =
  /^(?:(?:(?:sub|eind|grand)[ -]?)?tota(?:a)?l(?:en|s)?|som|sum|gemiddelde?|average):?$/i;

function looksLikeSummary(row: string[]): boolean {
  return row.some((cell) => SUMMARY_WORD.test(cell.trim()));
}

/**
 * A row that ignores the word columns while carrying numbers is arithmetic
 * about the table, however full it happens to be. The word columns are the
 * table's spine — filled in (nearly) every row, never with a bare number:
 * the reference codes, the descriptions. A row of per-column counts leaves
 * every one of them empty, or worse, puts a number there; a real record
 * cannot do that, because filling those columns with words is what made
 * them word columns. One real word in one of them acquits the row outright.
 */
function looksLikeArithmetic(rows: string[][], row: string[], width: number): boolean {
  let wordColumns = 0;
  let offended = 0;
  let numbers = 0;
  for (let index = 0; index < width; index++) {
    const value = (row[index] ?? '').trim();
    if (value !== '' && NUMERIC.test(value)) numbers++;
    const rest = rows.map((r) => (r[index] ?? '').trim());
    const present = rest.filter((v) => v !== '');
    const wordy =
      present.length >= Math.max(2, Math.ceil(rest.length * 0.9)) &&
      present.every((v) => !NUMERIC.test(v));
    if (!wordy) continue;
    wordColumns++;
    if (value === '' || NUMERIC.test(value)) offended++;
    else return false;
  }
  return wordColumns >= 2 && offended === wordColumns && numbers >= 2;
}

/** Columns whose values, taken together, read as something better than text. */
function typedColumns(rows: string[][], width: number): number {
  let typed = 0;
  for (let index = 0; index < width; index++) {
    const values = rows.map((row) => row[index] ?? '');
    if (values.every((value) => value.trim() === '')) continue;
    if (inferColumn(values).type !== 'text') typed++;
  }
  return typed;
}

/**
 * How many rows at the bottom are arithmetic about the table rather than rows
 * of it.
 *
 * A totals row poisons twice: it lands as a row nobody meant to store, and
 * one word of it under a column of numbers makes the whole column text — see
 * the every-value rule in the inference. So the bottom rows are read for two
 * signals, either sufficient: they say so ("Totaal", "Subtotal", "Gemiddelde"
 * — most do), or they are sparser than the table and provably the only reason
 * a column reads as text. A short last row that breaks nothing — somebody's
 * half-filled final record — matches neither and stays data.
 *
 * A guess, and treated like the header guess: reported, shown in the preview
 * with the rows it points at, and the operator's to overrule either way.
 */
function trailingSummaryRows(data: string[][], width: number): number {
  if (data.length < 2) return 0;
  const modal = modeOf(data.map(filled).filter((n) => n > 0));

  let count = 0;
  while (count < 3 && count < data.length - 1) {
    const row = data[data.length - 1 - count]!;
    const above = data.slice(0, data.length - 1 - count);
    if (looksLikeSummary(row)) {
      count++;
      continue;
    }
    // A grand total is not always sparser than the table — a row of
    // per-column counts can be its fullest line. Ignoring the word columns
    // gives it away either way.
    if (looksLikeArithmetic(above, row, width)) {
      count++;
      continue;
    }
    if (
      filled(row) < modal &&
      typedColumns(above, width) > typedColumns(data.slice(0, data.length - count), width)
    ) {
      count++;
      continue;
    }
    break;
  }
  return count;
}

/**
 * Which row is the header.
 *
 * An export that opens with "Verkooprapport Q3 2026", a generated-on stamp and
 * a blank line has three rows above the one that names the columns, and taking
 * the first of them leaves a one-column table called `Verkooprapport_Q3_2026`.
 *
 * Found in two moves. First the **body**: most rows in a CSV agree on how many
 * cells they fill, and the first row to reach that number is where the table
 * starts. Titles and stamps fill one or two cells and lose.
 *
 * Reaching it, not matching it: a header is often the *fullest* row in the
 * file. The RGS ledger scheme names 29 columns and then leaves two thirds of
 * them empty on every line, so a header that had to equal the body's width
 * would be passed over in favour of the first ordinary row.
 *
 * Then one careful step back up, because a header is not always as wide as its
 * data — `,Name,Amount` over `0,First,10` is what half the world's tooling
 * writes, and its header fills fewer cells than every row beneath it. The step
 * is only taken when the row above reads as *labels* — no numbers, no dates —
 * and it either covers more columns than the body does or sits on top of values
 * that are plainly typed. A generated-on stamp fails on its date and stays
 * preamble; a title of one word fails on width.
 *
 * A guess is acceptable here only because it is a *visible* one: the preview
 * shows the names it landed on before a single row is written.
 */
function findHeaderRow(grid: string[][]): number {
  const width = modeOf(grid.map(filled).filter((n) => n > 1));
  if (width < 2) return 0;

  const start = grid.findIndex((row) => filled(row) >= width);
  if (start <= 0) return Math.max(0, start);

  const above = grid[start - 1]!;
  if (filled(above) < 2 || !looksLikeLabels(above)) return start;
  return filled(above) > width || hasTypedValue(grid[start]!) ? start - 1 : start;
}

/**
 * The row above the header, when it is shaped like group headings.
 *
 * A spreadsheet with grouped columns writes the group name once, merged over
 * the columns it covers — "Q1" over Omzet and Kosten — and an export turns
 * the merge into one filled cell with blanks trailing it. So the shape to
 * look for is a row of labels directly above the header with at least two of
 * them, covering no more cells than the header itself. A report title fails
 * on being one cell; a generated-on stamp fails on its date.
 */
function groupRowCandidate(grid: string[][], headerIndex: number): number | null {
  if (headerIndex === 0) return null;
  const above = grid[headerIndex - 1]!;
  const header = grid[headerIndex]!;
  if (!looksLikeLabels(above)) return null;
  const cells = filled(above);
  if (cells < 2 || cells > filled(header)) return null;
  return headerIndex - 1;
}

/**
 * Each column's group heading, read the way the merge meant it: a group
 * covers every column from its own until the next one starts.
 */
function carryGroups(row: string[], width: number): (string | null)[] {
  const carried: (string | null)[] = [];
  let current: string | null = null;
  for (let index = 0; index < width; index++) {
    const cell = (row[index] ?? '').trim();
    if (cell) current = cell;
    carried.push(current);
  }
  return carried;
}

interface ReadGrid {
  grid: string[][];
  format: ImportFormat;
  delimiter: string | null;
  sheet: string | null;
  sheets: string[];
}

/**
 * Whatever arrived, as rows of raw strings.
 *
 * The one place that knows there is more than one kind of file. Everything
 * after it works on the grid and cannot tell where the grid came from.
 */
function readGrid(source: ImportSource, wanted: string | undefined): ReadGrid {
  if (typeof source !== 'string' && isWorkbook(source.bytes)) {
    let book;
    try {
      book = readWorkbook(source.bytes);
    } catch (error) {
      throw badRequest(error instanceof WorkbookError ? error.message : 'That file could not be read.');
    }

    const sheets = book.sheets.map((s) => s.name);
    const named = wanted === undefined ? undefined : book.sheets.find((s) => s.name === wanted);
    if (wanted !== undefined && !named) {
      throw badRequest(`That workbook has no sheet called "${wanted}".`);
    }

    // Workbooks open on a cover sheet, a recap or a page of notes often
    // enough that taking the first one flatly would import prose. Prose is
    // short and tables are long, so the data is on a sheet with lots of
    // filled rows — but between two long sheets (the table, and a diff of
    // the table against its last edition) tab order is the better judge, so
    // the guess is the *first* sheet in reach of the longest rather than the
    // longest itself. The preview lets it be overruled the same way the
    // header row can be.
    const rowsOf = (sheet: { grid: string[][] }) => sheet.grid.filter((row) => filled(row) > 0).length;
    const longest = Math.max(...book.sheets.map(rowsOf));
    const chosen = named ?? book.sheets.find((s) => rowsOf(s) * 2 >= longest) ?? book.sheets[0]!;
    if (chosen.grid.length === 0) throw badRequest(`Sheet "${chosen.name}" is empty.`);

    return { grid: chosen.grid, format: book.format, delimiter: null, sheet: chosen.name, sheets };
  }

  const text = typeof source === 'string' ? source : decodeText(source.bytes);
  if (!text.trim()) throw badRequest('That file is empty.');

  const delimiter = detectDelimiter(text);
  return { grid: parseCsv(text, delimiter), format: 'delimited', delimiter, sheet: null, sheets: [] };
}

/** Whether these bytes are a container rather than text — a ZIP, or an old `.xls`. */
function isWorkbook(bytes: Buffer): boolean {
  if (bytes.length < 8) return false;
  const signature = bytes.readUInt32BE(0);
  return signature === 0x504b0304 || signature === 0xd0cf11e0;
}

/**
 * A text file's bytes as characters.
 *
 * Guessed, because nothing in a CSV says what it is encoded in and the guess
 * has to be made regardless — the only choice is whether to make it carefully
 * or to assume UTF-8 and hand somebody a table full of `ë`.
 *
 * Excel's "Unicode Text" export is UTF-16, and announces itself with a byte
 * order mark. Everything else is UTF-8 until proven otherwise: a decode that
 * comes back carrying replacement characters was not UTF-8, and on a Windows
 * export the answer is almost always the Latin-1 superset Excel writes.
 */
export function decodeText(bytes: Buffer): string {
  if (bytes.length >= 2) {
    if (bytes[0] === 0xff && bytes[1] === 0xfe) return bytes.subarray(2).toString('utf16le');
    if (bytes[0] === 0xfe && bytes[1] === 0xff) return bytes.subarray(2).swap16().toString('utf16le');
  }

  const utf8 = bytes.toString('utf8');
  if (!utf8.includes('�')) return utf8;

  try {
    return new TextDecoder('windows-1252').decode(bytes);
  } catch {
    return bytes.toString('latin1');
  }
}

/**
 * Parse a spreadsheet into columns and rows, ready to be written.
 *
 * Nothing is written here: this is the step whose output the client shows as a
 * preview, so that a mangled delimiter, a misread type or a header found in the
 * wrong place is caught before it becomes a table.
 *
 * `options.headerRow` is how that catching ends: the guess is only ever an
 * opening bid, and an operator who can see it is wrong can name the right row
 * instead of going back to reshape the file by hand.
 */
export function parseImport(source: ImportSource, options: ImportOptions = {}): ParsedImport {
  const { grid, format, delimiter, sheet, sheets } = readGrid(source, options.sheet);

  const chosen = options.headerRow;
  const headerIndex = chosen === undefined ? findHeaderRow(grid) : chosen;
  const header = grid[headerIndex];
  if (!header || filled(header) === 0) {
    throw badRequest(
      chosen === undefined
        ? 'That file has no header row.'
        : `Row ${chosen + 1} is empty, so it cannot name the columns.`,
    );
  }

  // Blank rows are not only a trailing-newline problem: exports space their
  // sections out, and a row of nothing but separators looks full to a parser
  // and empty to a person.
  const under = grid.slice(headerIndex + 1);
  const data = under.filter((row) => filled(row) > 0);

  // The totals rows at the bottom, filtered the way the title rows at the top
  // are — and like the header, a visible and overrulable guess.
  const fullWidth = data.reduce((n, row) => Math.max(n, row.length), header.length);
  const footerCandidate = trailingSummaryRows(data, fullWidth);
  const footerRows =
    options.footerRows === undefined
      ? footerCandidate
      : Math.max(0, Math.min(options.footerRows, Math.max(0, data.length - 1)));
  // The rows the guess points at, rendered for the preview — whether they are
  // being dropped or only offered.
  const footerText = data
    .slice(data.length - Math.min(Math.max(footerRows, footerCandidate), 3))
    .map((row) => row.map((cell) => cell.trim()).filter(Boolean).join(' · '));
  const bodyRows = footerRows > 0 ? data.slice(0, data.length - footerRows) : data;

  // Rows wider than the header are counted in, so a value can never be dropped
  // for sitting under a header cell that Excel forgot to write.
  const width = bodyRows.reduce((n, row) => Math.max(n, row.length), header.length);

  const kept: number[] = [];
  let emptyColumns = 0;
  for (let index = 0; index < width; index++) {
    const named = (header[index] ?? '').trim() !== '';
    const used = bodyRows.some((row) => (row[index] ?? '').trim() !== '');
    // Trailing separators from long-deleted columns: no name, and nothing under
    // it in any row. A column with a name but no values is somebody's intent.
    if (!named && !used) emptyColumns++;
    else kept.push(index);
  }

  if (kept.length === 0) throw badRequest('That file has no columns with anything in them.');
  if (kept.length > MAX_IMPORT_COLUMNS) {
    throw badRequest(
      `That file has ${kept.length} columns; ${MAX_IMPORT_COLUMNS} is the most one import can create.`,
    );
  }

  const skipped = Math.max(0, bodyRows.length - MAX_IMPORT_ROWS);
  const body = bodyRows.slice(0, MAX_IMPORT_ROWS);

  /*
   * Grouped columns. A file that shelves its columns under merged headings —
   * "Q1" over Omzet and Kosten, "Q2" over the same two words again — names
   * its columns uniquely per group and repeats them across groups. The group
   * row is folded in front of the names, so those columns land as "Q1 Omzet"
   * and "Q2 Omzet" rather than as an Omzet and an Omzet 2 that no longer say
   * which quarter is which.
   *
   * Folded on its own only when the header actually repeats a name — that
   * repetition is what grouped columns look like, and without it the row
   * above is more likely a stray line than a shelf. Either reading can be
   * overruled: the candidate is reported even when unused, and `null` turns
   * the fold off however sure the parser is.
   */
  const groupCandidate = groupRowCandidate(grid, headerIndex);
  const headerNames = kept.map((index) => (header[index] ?? '').trim().toLowerCase()).filter(Boolean);
  const headerRepeats = new Set(headerNames).size < headerNames.length;
  let groupRow: number | null;
  if (options.groupRow === undefined) {
    groupRow = headerRepeats ? groupCandidate : null;
  } else if (options.groupRow === null) {
    groupRow = null;
  } else {
    const row = grid[options.groupRow];
    if (options.groupRow >= headerIndex || !row || filled(row) === 0) {
      throw badRequest(`Row ${options.groupRow + 1} cannot group the columns: it must be a filled row above the header.`);
    }
    groupRow = options.groupRow;
  }
  const carried = groupRow !== null ? carryGroups(grid[groupRow]!, width) : null;

  /*
   * The groups as things of their own, so they can be edited as one word
   * instead of once per column. Each run of columns under one heading is one
   * group, listed in the order the file shelves them; a correction addresses
   * a group by that position and lands on every column in the run — and a
   * correction emptied to nothing takes the heading off its run entirely,
   * which is how one messy merged cell is cleaned in one stroke. A stale
   * correction pointing past the list is ignored, exactly as a stale column
   * correction is: previews regenerate, and yesterday's fix must not wedge
   * today's reading.
   */
  const runOf: (number | null)[] = [];
  const groupRuns: { label: string; count: number }[] = [];
  let previousValue: string | null = null;
  for (const index of kept) {
    const value = carried?.[index] ?? null;
    if (value === null) {
      runOf.push(null);
    } else if (value === previousValue) {
      groupRuns[groupRuns.length - 1]!.count++;
      runOf.push(groupRuns.length - 1);
    } else {
      groupRuns.push({ label: value, count: 1 });
      runOf.push(groupRuns.length - 1);
    }
    previousValue = value;
  }
  const groupEdits = new Map((options.groups ?? []).map((o) => [o.index, o.label.trim()]));
  const effectiveGroups = groupRuns.map((run, ordinal) => {
    const edited = groupEdits.get(ordinal);
    return {
      label: (edited === undefined ? run.label : edited).slice(0, MAX_COLUMN_LABEL),
      count: run.count,
    };
  });

  // The operator's corrections, addressed by position in the preview. A
  // correction is a label like any other — there is nothing illegal to type,
  // because the identifier is derived from whatever the words turn out to be.
  //
  // Duplicates that survive the grouping are stepped, not refused: the second
  // remaining "Omzet" becomes "Omzet 2" exactly as the identifier under it
  // becomes omzet_2, with the original kept beside it so the preview can say
  // what happened. Headers are also cut to the label cap for the same
  // reason: whatever the file says, the preview must be able to show a
  // column that the catalogue will actually accept.
  const overrides = new Map((options.columns ?? []).map((o) => [o.index, o]));
  const groupFor = (position: number): string | null => {
    const ordinal = runOf[position];
    if (ordinal === null || ordinal === undefined) return null;
    return effectiveGroups[ordinal]!.label || null;
  };
  const seen = new Set<string>();
  const labels = kept.map((index, position) => {
    const wanted = overrides.get(position)?.label?.trim();
    const named = (header[index] ?? '').trim();
    const group = groupFor(position);
    const worded = group && named ? `${group} ${named}` : named || group || '';
    const base = (wanted || worded || `Column ${position + 1}`).slice(0, MAX_COLUMN_LABEL);
    let unique = base;
    for (let n = 2; seen.has(unique); n++) {
      const suffix = ` ${n}`;
      unique = `${base.slice(0, MAX_COLUMN_LABEL - suffix.length)}${suffix}`;
    }
    seen.add(unique);
    return unique;
  });

  // Appending matches each header to a column the table already has; creating
  // derives fresh identifiers. Matched names claim theirs first, so a header
  // that matched nothing can never derive its way onto one that did.
  const matched = labels.map((label) => (options.tableColumns ? matchColumn(options.tableColumns, label) : undefined));
  const taken = new Set(matched.flatMap((hit) => (hit ? [hit.name] : [])));
  for (const column of options.tableColumns ?? []) taken.add(column.name);
  const names = labels.map((label, position) => matched[position]?.name ?? columnNameFor(label, taken));

  const columns: ImportColumn[] = [];
  const readings: Required<Reading>[] = [];
  kept.forEach((source, position) => {
    const inferred = inferColumn(body.map((row) => row[source] ?? ''));
    readings.push(inferred.reading);
    // A select or link column takes the text as written — `readValue` falls
    // back to text for any type it has no reader for, and the row gate knows
    // the options and the target where this parser does not.
    const type = matched[position]?.type ?? overrides.get(position)?.type ?? inferred.type;
    columns.push({
      name: names[position]!,
      label: labels[position]!,
      header: header[source]?.trim() ?? '',
      group: groupFor(position),
      type,
      unreadable: 0,
      problem: null,
    });
  });

  const rows = body.map((row) => {
    const record: Record<string, string | number | boolean | null> = {};
    columns.forEach((column, index) => {
      const read = readValue(row[kept[index]!] ?? '', column.type, readings[index]!);
      if (!read.ok) {
        column.unreadable++;
        column.problem ??= (row[kept[index]!] ?? '').trim();
      }
      record[column.name] = read.value;
    });
    return record;
  });

  return {
    columns,
    rows,
    format,
    delimiter,
    sheet,
    sheets,
    skipped,
    headerRow: headerIndex,
    groupRow,
    groupCandidate,
    groups: effectiveGroups,
    footerRows,
    footerCandidate,
    footerText,
    // Shown as they were read, so the row that names the columns can be picked
    // out by eye rather than by counting lines in a text editor.
    firstRows: grid
      .slice(0, Math.max(HEADER_CHOICES, headerIndex + 1))
      .map((row) => row.map((cell) => cell.trim()).filter(Boolean).join(' · ')),
    ignored: {
      // A group row in use is not junk that was walked past.
      preamble: headerIndex - (groupRow !== null && groupRow < headerIndex ? 1 : 0),
      blankRows: under.length - data.length,
      emptyColumns,
      footer: footerRows,
    },
  };
}