import { looksLikeZip, readZip, ZipError } from './zip.js';

/**
 * Turning a real spreadsheet file into a grid of strings.
 *
 * "Export it as CSV first" is a sentence that loses customers. The file people
 * actually have is an `.xlsx` off a colleague's machine or an `.ods` out of
 * LibreOffice, and asking them to convert it before they can find out whether
 * this product works is asking them to do work in order to be sold to.
 *
 * What comes out is deliberately *strings* — the same shape a CSV parses to —
 * so that everything downstream (finding the header, inferring types, coercing
 * values, reporting what was skipped) stays one code path with one set of
 * tests. A workbook reader that had its own type inference would be a second
 * importer to keep honest, and the two would drift.
 *
 * The two formats are the same idea twice: a ZIP of XML where the cells are
 * numbers and a style tells you which of them are dates. That last part is the
 * whole difficulty. Excel stores 2026-08-08 as `46242`, and a reader that
 * ignores the format writes a column of five-digit integers into what the
 * operator can plainly see is a column of dates.
 *
 * Not supported, and said so out loud rather than half-attempted: the pre-2007
 * binary `.xls`, which is a different format entirely — an OLE2 container of
 * BIFF records — and worth a clear message instead of a shaky parser.
 */

export interface Sheet {
  name: string;
  /** Rows of raw cell text, padded so a row's index matches the spreadsheet's. */
  grid: string[][];
}

export interface Workbook {
  format: WorkbookFormat;
  sheets: Sheet[];
}

export type WorkbookFormat = 'xlsx' | 'ods';

export class WorkbookError extends Error {}

/**
 * A ceiling on how tall a sheet may be.
 *
 * Only ever reached by a file nobody meant to import by hand: the per-import
 * row cap is a small fraction of this, so anything near it is a database
 * export that wants a different tool. Refused rather than truncated, because a
 * silent cut here would make the "rows past the limit" count a lie.
 */
const MAX_SHEET_ROWS = 200_000;

/** The widest a row may get, which is also Excel's own column ceiling. */
const MAX_SHEET_COLUMNS = 16_384;

/** A run of empty rows longer than this is padding, not a gap in the data. */
const EMPTY_ROW_RUN = 1_000;

/** Whether a file name is one of the workbook formats, rather than delimited text. */
export function looksLikeWorkbook(filename: string): boolean {
  return /\.(xlsx|xlsm|xltx|ods|fods|xls|numbers)$/i.test(filename.trim());
}

/**
 * Read a workbook, or say precisely why not.
 *
 * The format is decided by the bytes rather than by the extension, because a
 * file called `report.xlsx` that a system actually wrote as CSV is common
 * enough to be worth getting right — and because the reverse, a real workbook
 * saved as `.txt`, would otherwise import as one giant column.
 */
export function readWorkbook(bytes: Buffer): Workbook {
  if (isOle2(bytes)) {
    throw new WorkbookError(
      'That is the pre-2007 Excel format (.xls). Open it and save as .xlsx, .ods or CSV, and it will import.',
    );
  }
  if (!looksLikeZip(bytes)) {
    throw new WorkbookError('That file is not a spreadsheet this can read.');
  }

  let files: Map<string, Buffer>;
  try {
    files = readZip(bytes);
  } catch (error) {
    throw new WorkbookError(error instanceof ZipError ? error.message : 'That file could not be unpacked.');
  }

  if (files.has('xl/workbook.xml')) return { format: 'xlsx', sheets: readXlsx(files) };
  if (files.has('content.xml')) return { format: 'ods', sheets: readOds(files) };

  // A ZIP that is neither is most often a `.numbers` file or a folder of CSVs
  // someone compressed, and both deserve to be told apart from a broken file.
  throw new WorkbookError('That archive holds no spreadsheet this can read.');
}

/** The OLE2 compound-file signature every `.xls` and old `.doc` opens with. */
function isOle2(bytes: Buffer): boolean {
  return bytes.length > 8 && bytes.readUInt32BE(0) === 0xd0cf11e0 && bytes.readUInt32BE(4) === 0xa1b11ae1;
}

// ---- XLSX -----------------------------------------------------------------

function readXlsx(files: Map<string, Buffer>): Sheet[] {
  const strings = readSharedStrings(files.get('xl/sharedStrings.xml'));
  const dateStyles = readDateStyles(files.get('xl/styles.xml'));
  const rels = readRelationships(files.get('xl/_rels/workbook.xml.rels'));

  const sheets: Sheet[] = [];
  for (const entry of listXlsxSheets(files.get('xl/workbook.xml')!, rels)) {
    const xml = files.get(entry.path);
    if (!xml) continue;
    sheets.push({ name: entry.name, grid: readXlsxSheet(xml.toString('utf8'), strings, dateStyles) });
  }

  if (sheets.length === 0) throw new WorkbookError('That workbook has no sheets in it.');
  return sheets;
}

/**
 * The sheets in the order the tabs are in.
 *
 * Order matters more than it looks: "the first sheet" is what an import
 * defaults to, and a workbook whose sheets were read in ZIP order would open
 * on whichever one happened to be written first.
 */
function listXlsxSheets(
  workbook: Buffer,
  rels: Map<string, string>,
): { name: string; path: string }[] {
  const found: { name: string; path: string }[] = [];

  for (const tag of tagsNamed(workbook.toString('utf8'), 'sheet')) {
    const id = tag.attrs['r:id'] ?? tag.attrs['relationship:id'];
    const target = id ? rels.get(id) : undefined;
    const path = target ? resolveTarget(target) : `xl/worksheets/sheet${found.length + 1}.xml`;
    found.push({ name: decodeXml(tag.attrs.name ?? `Sheet ${found.length + 1}`), path });
  }
  return found;
}

/** A relationship target, as a path from the archive root. */
function resolveTarget(target: string): string {
  const clean = target.replace(/^\.\//, '');
  return clean.startsWith('/') ? clean.slice(1) : `xl/${clean}`;
}

function readRelationships(xml: Buffer | undefined): Map<string, string> {
  const rels = new Map<string, string>();
  if (!xml) return rels;
  for (const tag of tagsNamed(xml.toString('utf8'), 'Relationship')) {
    if (tag.attrs.Id && tag.attrs.Target) rels.set(tag.attrs.Id, decodeXml(tag.attrs.Target));
  }
  return rels;
}

/**
 * The shared string table, which is where nearly every text cell lives.
 *
 * A string can be split into runs — one for each stretch of different
 * formatting — so the text is the concatenation of the `<t>` elements rather
 * than the first of them. Phonetic guides (`<rPh>`, on Japanese workbooks)
 * carry their own `<t>` and are not part of the value.
 */
function readSharedStrings(xml: Buffer | undefined): string[] {
  if (!xml) return [];
  const body = xml.toString('utf8');
  const strings: string[] = [];

  for (const item of body.split('</si>').slice(0, -1)) {
    const text = item.replace(/<rPh[\s\S]*?<\/rPh>/g, '');
    let value = '';
    for (const match of text.matchAll(/<t(?:\s[^>]*)?>([\s\S]*?)<\/t>|<t\s*\/>/g)) {
      value += decodeXml(match[1] ?? '');
    }
    strings.push(value);
  }
  return strings;
}

/**
 * Which style indexes mean "this number is a date".
 *
 * A cell points at a style, the style points at a number format, and only the
 * format knows that 46242 is a day rather than a quantity. Both halves matter:
 * the built-in formats (ids under 164, defined by the spec and never written
 * into the file) and the custom ones, which are recognised by having date
 * fields in their code.
 */
function readDateStyles(xml: Buffer | undefined): Set<number> {
  const dates = new Set<number>();
  if (!xml) return dates;
  const body = xml.toString('utf8');

  const custom = new Map<number, string>();
  for (const tag of tagsNamed(body, 'numFmt')) {
    const id = Number(tag.attrs.numFmtId);
    if (Number.isInteger(id)) custom.set(id, decodeXml(tag.attrs.formatCode ?? ''));
  }

  // Only the `cellXfs` block is what a cell's `s` attribute indexes into;
  // `cellStyleXfs` above it looks identical and would shift every index.
  const cellXfs = between(body, '<cellXfs', '</cellXfs>');
  if (!cellXfs) return dates;

  let index = 0;
  for (const tag of tagsNamed(cellXfs, 'xf')) {
    const id = Number(tag.attrs.numFmtId ?? 0);
    if (isDateFormat(id, custom.get(id))) dates.add(index);
    index++;
  }
  return dates;
}

/** The built-in number formats that are dates, times or both. */
const BUILTIN_DATE_FORMATS = new Set([14, 15, 16, 17, 18, 19, 20, 21, 22, 45, 46, 47]);

function isDateFormat(id: number, code: string | undefined): boolean {
  if (BUILTIN_DATE_FORMATS.has(id)) return true;
  if (!code) return false;

  // A custom code is a date when it has date fields outside of the parts that
  // are not fields: quoted literals, colour and condition brackets, and the
  // escapes. `#,##0.00 "m"` is money and must not count as minutes.
  const fields = code
    .replace(/\\./g, '')
    .replace(/"[^"]*"/g, '')
    .replace(/\[[^\]]*\]/g, '');
  return /[ymdhs]/i.test(fields);
}

function readXlsxSheet(xml: string, strings: string[], dateStyles: Set<number>): string[][] {
  const grid: string[][] = [];
  let row: string[] = [];
  let rowNumber = 0;
  let column = 0;

  const scanner = /<row(\s[^>]*?)?(\/?)>|<\/row>|<c(\s[^>]*?)?\/>|<c(\s[^>]*?)?>([\s\S]*?)<\/c>/g;

  for (let match = scanner.exec(xml); match; match = scanner.exec(xml)) {
    const token = match[0];

    if (token === '</row>') {
      place(grid, rowNumber, row);
      row = [];
      continue;
    }

    if (token.startsWith('<row')) {
      // Rows carry their spreadsheet number, and a run of empty ones is simply
      // left out of the file. Honouring `r` keeps "row 7" here and "row 7" in
      // Excel the same row, which is the whole basis of the header picker.
      const attrs = parseAttrs(match[1] ?? '');
      rowNumber = Number(attrs.r) || rowNumber + 1;
      column = 0;
      if (match[2] === '/') {
        place(grid, rowNumber, []);
        row = [];
      }
      continue;
    }

    const attrs = parseAttrs(match[3] ?? match[4] ?? '');
    const at = attrs.r ? columnOf(attrs.r) : column;
    column = at + 1;
    if (at >= MAX_SHEET_COLUMNS) continue;

    const value = cellText(attrs, match[5] ?? '', strings, dateStyles);
    if (value !== '') {
      while (row.length < at) row.push('');
      row[at] = value;
    }
  }

  if (row.length > 0) place(grid, rowNumber, row);
  return grid;
}

/** Put a row at its spreadsheet position, filling in the empty rows before it. */
function place(grid: string[][], rowNumber: number, row: string[]): void {
  if (rowNumber < 1) return;
  if (rowNumber > MAX_SHEET_ROWS) throw tooTall();
  while (grid.length < rowNumber - 1) grid.push([]);
  grid[rowNumber - 1] = row;
}

function tooTall(): WorkbookError {
  return new WorkbookError(
    `That sheet is over ${MAX_SHEET_ROWS.toLocaleString('en-US')} rows, which is more than an import can take.`,
  );
}

/** What one cell reads as, once its type and number format are taken into account. */
function cellText(
  attrs: Record<string, string>,
  inner: string,
  strings: string[],
  dateStyles: Set<number>,
): string {
  const type = attrs.t ?? 'n';

  if (type === 'inlineStr') {
    let value = '';
    for (const match of inner.matchAll(/<t(?:\s[^>]*)?>([\s\S]*?)<\/t>/g)) value += decodeXml(match[1] ?? '');
    return value;
  }

  // `<f>` holds the formula; `<v>` holds what it last worked out to, which is
  // the only part an import can use. A workbook saved without cached values
  // has no answer to give, and an empty cell is the honest result.
  const raw = decodeXml(between(inner, '<v', '</v>')?.replace(/^[^>]*>/, '') ?? '');
  if (raw === '') return '';

  if (type === 's') return strings[Number(raw)] ?? '';
  if (type === 'str' || type === 'e') return raw;
  if (type === 'b') return raw === '1' ? 'true' : 'false';

  const style = Number(attrs.s ?? -1);
  if (dateStyles.has(style)) {
    const asDate = fromSerial(Number(raw));
    if (asDate) return asDate;
  }
  return numberText(raw);
}

/**
 * A number as a person would have typed it.
 *
 * Round-tripped through `Number` so that the seventeen digits a spreadsheet
 * writes for 0.1 + 0.2 come back as `0.3`, and left alone when it is not a
 * number at all.
 */
function numberText(raw: string): string {
  const value = Number(raw);
  return Number.isFinite(value) ? String(value) : raw;
}

/**
 * A date serial as an ISO date.
 *
 * Two quirks, both Excel's. Day 0 is 1899-12-30 rather than the 31st, because
 * the format deliberately reproduces a Lotus 1-2-3 bug: 1900 is treated as a
 * leap year, so every serial from 61 onwards is one day past the truth and the
 * epoch is shifted back to cancel it. Serials under 60 predate the phantom
 * 29th of February and need the shift undone again.
 *
 * A fraction is a time of day, and comes back joined to the date rather than
 * silently dropped — the column will import as text, which is the truthful
 * outcome for a value this product has no type for.
 */
export function fromSerial(serial: number): string | null {
  if (!Number.isFinite(serial) || serial < 0 || serial > 2_958_465) return null;

  const whole = Math.floor(serial);
  const seconds = Math.round((serial - whole) * 86_400);
  const days = whole < 60 ? whole + 1 : whole;
  const at = new Date((days - 25_569) * 86_400_000 + seconds * 1_000);
  if (Number.isNaN(at.getTime())) return null;

  const date = at.toISOString().slice(0, 10);
  if (seconds === 0) return date;

  const time = at.toISOString().slice(11, 19).replace(/:00$/, '');
  // A serial under 1 is a time with no date attached — a duration, or a clock
  // reading. Dating it to 1899 would be an invention.
  return whole === 0 ? time : `${date} ${time}`;
}

/** The zero-based column a cell reference names: `A1` is 0, `AB7` is 27. */
export function columnOf(reference: string): number {
  let index = 0;
  for (const char of reference) {
    const letter = char.toUpperCase();
    if (letter < 'A' || letter > 'Z') break;
    index = index * 26 + (letter.charCodeAt(0) - 64);
  }
  return Math.max(0, index - 1);
}

// ---- ODS ------------------------------------------------------------------

/**
 * OpenDocument, which is the same problem with better manners.
 *
 * Every cell states its own type and carries the machine-readable value in an
 * attribute beside the displayed text, so none of the style archaeology above
 * is needed. What it has instead is *repetition*: a row of a hundred identical
 * cells is written once with a count, and so is the empty remainder of every
 * sheet — which is why the trailing repeats have to be dropped rather than
 * expanded into sixteen thousand empty strings.
 */
function readOds(files: Map<string, Buffer>): Sheet[] {
  const content = files.get('content.xml')!.toString('utf8');
  const sheets: Sheet[] = [];

  const scanner = /<table:table(?:\s([^>]*?))?>([\s\S]*?)<\/table:table>/g;
  for (let match = scanner.exec(content); match; match = scanner.exec(content)) {
    const attrs = parseAttrs(match[1] ?? '');
    sheets.push({
      name: decodeXml(attrs['table:name'] ?? `Sheet ${sheets.length + 1}`),
      grid: readOdsRows(match[2] ?? ''),
    });
  }

  if (sheets.length === 0) throw new WorkbookError('That workbook has no sheets in it.');
  return sheets;
}

function readOdsRows(xml: string): string[][] {
  const grid: string[][] = [];
  // The close marker in the lookahead needs its `>`: without it, the row would
  // also end at the first `</table:table-cell>` and every row would read empty.
  const scanner = /<table:table-row(?:\s([^>]*?))?(\/)?>([\s\S]*?)(?=<table:table-row|<\/table:table>|$)/g;

  for (let match = scanner.exec(xml); match; match = scanner.exec(xml)) {
    const attrs = parseAttrs(match[1] ?? '');
    const repeat = countOf(attrs['table:number-rows-repeated'], MAX_SHEET_ROWS);
    const row = match[2] ? [] : readOdsCells(match[3] ?? '');

    // Every ODS file ends by declaring the million empty rows below the data,
    // and there is no point materialising those. A short run of empty rows is
    // a gap inside the sheet, and it is kept: dropping it would shift every
    // row number after it away from the one the operator sees in their editor.
    const times = row.length > 0 || repeat <= EMPTY_ROW_RUN ? repeat : 1;
    for (let n = 0; n < times; n++) {
      if (grid.length >= MAX_SHEET_ROWS) throw tooTall();
      grid.push(row.length === 0 ? [] : [...row]);
    }
  }

  while (grid.length > 0 && grid[grid.length - 1]!.length === 0) grid.pop();
  return grid;
}

function readOdsCells(xml: string): string[] {
  const row: string[] = [];
  const scanner =
    /<table:(?:covered-)?table-cell(?:\s([^>]*?))?\/>|<table:(?:covered-)?table-cell(?:\s([^>]*?))?>([\s\S]*?)<\/table:(?:covered-)?table-cell>/g;

  for (let match = scanner.exec(xml); match; match = scanner.exec(xml)) {
    const attrs = parseAttrs(match[1] ?? match[2] ?? '');
    const repeat = countOf(attrs['table:number-columns-repeated'], MAX_SHEET_COLUMNS);
    const value = odsCellText(attrs, match[3] ?? '');

    // Empty repeats are expanded like any other: a gap of three between two
    // filled cells is written exactly the way the trailing padding is, and
    // collapsing it would slide every column after it one place left. The
    // padding costs nothing here because the trailing run is trimmed below.
    for (let n = 0; n < repeat && row.length < MAX_SHEET_COLUMNS; n++) row.push(value);
  }

  while (row.length > 0 && row[row.length - 1] === '') row.pop();
  return row;
}

function odsCellText(attrs: Record<string, string>, inner: string): string {
  const type = attrs['office:value-type'];

  if (type === 'float' || type === 'percentage' || type === 'currency') {
    return numberText(decodeXml(attrs['office:value'] ?? ''));
  }
  if (type === 'date') return decodeXml(attrs['office:date-value'] ?? '').replace('T00:00:00', '');
  if (type === 'time') return decodeXml(attrs['office:time-value'] ?? '');
  if (type === 'boolean') return decodeXml(attrs['office:boolean-value'] ?? '');

  // Text, and anything unlabelled: the displayed paragraphs, which a
  // multi-line cell writes as several. `<text:s/>` is a run of spaces.
  const lines: string[] = [];
  for (const match of inner.matchAll(/<text:p(?:\s[^>]*)?>([\s\S]*?)<\/text:p>/g)) {
    lines.push(decodeXml(stripTags(match[1] ?? '')));
  }
  return lines.join('\n').trim();
}

/** A repeat count, defaulting to one and never past the ceiling it is bounded by. */
function countOf(raw: string | undefined, ceiling: number): number {
  const value = Number(raw ?? 1);
  return Number.isInteger(value) && value > 0 ? Math.min(value, ceiling) : 1;
}

// ---- XML ------------------------------------------------------------------

interface Tag {
  attrs: Record<string, string>;
}

/** Every occurrence of one element, self-closing or not, with its attributes. */
function* tagsNamed(xml: string, name: string): Generator<Tag> {
  const scanner = new RegExp(`<${name}(?:\\s([^>]*?))?\\s*/?>`, 'g');
  for (let match = scanner.exec(xml); match; match = scanner.exec(xml)) {
    yield { attrs: parseAttrs(match[1] ?? '') };
  }
}

function parseAttrs(source: string): Record<string, string> {
  const attrs: Record<string, string> = {};
  for (const match of source.matchAll(/([\w:.-]+)\s*=\s*"([^"]*)"|([\w:.-]+)\s*=\s*'([^']*)'/g)) {
    attrs[match[1] ?? match[3]!] = match[2] ?? match[4] ?? '';
  }
  return attrs;
}

/** The slice between an opening marker and its close, or nothing. */
function between(xml: string, open: string, close: string): string | null {
  const start = xml.indexOf(open);
  if (start < 0) return null;
  const end = xml.indexOf(close, start);
  return end < 0 ? null : xml.slice(start, end);
}

function stripTags(xml: string): string {
  return xml.replace(/<text:s(?:\s[^>]*)?\/>/g, ' ').replace(/<[^>]*>/g, '');
}

const ENTITIES: Record<string, string> = {
  amp: '&',
  lt: '<',
  gt: '>',
  quot: '"',
  apos: "'",
};

function decodeXml(text: string): string {
  if (!text.includes('&')) return text;
  return text.replace(/&(#x?[0-9a-fA-F]+|[a-zA-Z]+);/g, (whole, body: string) => {
    if (body.startsWith('#')) {
      const code = body[1] === 'x' || body[1] === 'X' ? parseInt(body.slice(2), 16) : Number(body.slice(1));
      return Number.isInteger(code) && code > 0 && code <= 0x10ffff ? String.fromCodePoint(code) : whole;
    }
    return ENTITIES[body] ?? whole;
  });
}