import { inflateRawSync } from "node:zlib";

// Reads the two formats a sheet is exported as — CSV (also TSV / semicolon
// separated) and .xlsx — into a plain grid of strings. Dependency-free on
// purpose: the published `xlsx` package carries unfixed advisories and we only
// need text cells from the first worksheet.
//
// Both parsers are strict about structure but forgiving about content: an
// unreadable file throws SpreadsheetError with a message fit for the UI.

export class SpreadsheetError extends Error {
  constructor(message: string) {
    super(message);
    this.name = "SpreadsheetError";
  }
}

export type Grid = string[][];

export const MAX_ROWS = 5000;

// ---------------------------------------------------------------------------
// CSV
// ---------------------------------------------------------------------------

function detectDelimiter(text: string): string {
  const firstLine = text.slice(0, text.indexOf("\n") === -1 ? undefined : text.indexOf("\n"));
  const counts = [",", "\t", ";"].map((d) => ({ d, n: firstLine.split(d).length - 1 }));
  counts.sort((a, b) => b.n - a.n);
  return counts[0].n > 0 ? counts[0].d : ",";
}

/** RFC 4180 CSV: quoted fields, doubled quotes, CRLF or LF, optional BOM. */
export function parseCsv(input: string): Grid {
  const text = input.charCodeAt(0) === 0xfeff ? input.slice(1) : input;
  const delimiter = detectDelimiter(text);
  const rows: Grid = [];
  let row: string[] = [];
  let field = "";
  let quoted = false;

  for (let i = 0; i < text.length; i++) {
    const char = text[i];
    if (quoted) {
      if (char === '"') {
        if (text[i + 1] === '"') {
          field += '"';
          i++;
        } else {
          quoted = false;
        }
      } else {
        field += char;
      }
      continue;
    }
    if (char === '"') {
      quoted = true;
    } else if (char === delimiter) {
      row.push(field);
      field = "";
    } else if (char === "\n" || char === "\r") {
      if (char === "\r" && text[i + 1] === "\n") i++;
      row.push(field);
      rows.push(row);
      row = [];
      field = "";
      if (rows.length > MAX_ROWS) throw new SpreadsheetError(`The file has more than ${MAX_ROWS} rows.`);
    } else {
      field += char;
    }
  }
  if (field !== "" || row.length > 0) {
    row.push(field);
    rows.push(row);
  }
  return rows;
}

// ---------------------------------------------------------------------------
// XLSX (a zip of XML files)
// ---------------------------------------------------------------------------

type ZipEntry = { name: string; method: number; compressedSize: number; offset: number };

function readZipEntries(buffer: Buffer): Map<string, ZipEntry> {
  // End of central directory record: scan backwards for its signature.
  const minEocd = 22;
  let eocd = -1;
  for (let i = buffer.length - minEocd; i >= Math.max(0, buffer.length - 65_557); i--) {
    if (buffer.readUInt32LE(i) === 0x06054b50) {
      eocd = i;
      break;
    }
  }
  if (eocd === -1) throw new SpreadsheetError("This is not a valid .xlsx file.");

  const entryCount = buffer.readUInt16LE(eocd + 10);
  let pointer = buffer.readUInt32LE(eocd + 16);
  const entries = new Map<string, ZipEntry>();

  for (let n = 0; n < entryCount; n++) {
    if (pointer + 46 > buffer.length || buffer.readUInt32LE(pointer) !== 0x02014b50) {
      throw new SpreadsheetError("This is not a valid .xlsx file.");
    }
    const method = buffer.readUInt16LE(pointer + 10);
    const compressedSize = buffer.readUInt32LE(pointer + 20);
    const nameLength = buffer.readUInt16LE(pointer + 28);
    const extraLength = buffer.readUInt16LE(pointer + 30);
    const commentLength = buffer.readUInt16LE(pointer + 32);
    const offset = buffer.readUInt32LE(pointer + 42);
    const name = buffer.toString("utf8", pointer + 46, pointer + 46 + nameLength);
    entries.set(name, { name, method, compressedSize, offset });
    pointer += 46 + nameLength + extraLength + commentLength;
  }
  return entries;
}

function readZipFile(buffer: Buffer, entry: ZipEntry): string {
  if (entry.offset + 30 > buffer.length || buffer.readUInt32LE(entry.offset) !== 0x04034b50) {
    throw new SpreadsheetError("This is not a valid .xlsx file.");
  }
  const nameLength = buffer.readUInt16LE(entry.offset + 26);
  const extraLength = buffer.readUInt16LE(entry.offset + 28);
  const start = entry.offset + 30 + nameLength + extraLength;
  const data = buffer.subarray(start, start + entry.compressedSize);
  if (entry.method === 0) return data.toString("utf8");
  if (entry.method === 8) return inflateRawSync(data).toString("utf8");
  throw new SpreadsheetError("This .xlsx file uses an unsupported compression method.");
}

function decodeXml(text: string): string {
  return text.replace(/&(amp|lt|gt|quot|apos|#x[0-9a-fA-F]+|#\d+);/g, (_, entity: string) => {
    switch (entity) {
      case "amp":
        return "&";
      case "lt":
        return "<";
      case "gt":
        return ">";
      case "quot":
        return '"';
      case "apos":
        return "'";
      default:
        return String.fromCodePoint(entity[1] === "x" ? Number.parseInt(entity.slice(2), 16) : Number(entity.slice(1)));
    }
  });
}

/** All <t> text inside an element, concatenated (rich text is split into runs). */
function textRuns(xml: string): string {
  let text = "";
  for (const match of xml.matchAll(/<t(?:\s[^>]*)?>([\s\S]*?)<\/t>/g)) text += decodeXml(match[1]);
  return text;
}

function sharedStrings(xml: string | undefined): string[] {
  if (!xml) return [];
  return [...xml.matchAll(/<si>([\s\S]*?)<\/si>/g)].map((match) => textRuns(match[1]));
}

function columnIndex(cellRef: string): number {
  let index = 0;
  for (const letter of cellRef.replace(/\d+$/, "")) index = index * 26 + (letter.charCodeAt(0) - 64);
  return index - 1;
}

/** Excel stores every number as a double; show it the way the sheet would. */
function numberText(value: string): string {
  const number = Number(value);
  return Number.isFinite(number) ? String(number) : value;
}

function firstWorksheetPath(entries: Map<string, ZipEntry>, buffer: Buffer): string {
  const workbook = entries.get("xl/workbook.xml");
  const rels = entries.get("xl/_rels/workbook.xml.rels");
  if (workbook && rels) {
    const firstSheet = readZipFile(buffer, workbook).match(/<sheet\s[^>]*?r:id="([^"]+)"/);
    if (firstSheet) {
      const relId = firstSheet[1];
      const relationship = readZipFile(buffer, rels).match(new RegExp(`<Relationship\\s[^>]*?Id="${relId}"[^>]*?>`));
      const target = relationship?.[0].match(/Target="([^"]+)"/)?.[1];
      if (target) return target.startsWith("/") ? target.slice(1) : `xl/${target}`;
    }
  }
  return "xl/worksheets/sheet1.xml";
}

/** The first worksheet of an .xlsx workbook as a grid of display strings. */
export function parseXlsx(buffer: Buffer): Grid {
  const entries = readZipEntries(buffer);
  const sheetPath = firstWorksheetPath(entries, buffer);
  const sheet = entries.get(sheetPath);
  if (!sheet) throw new SpreadsheetError("The workbook has no worksheet.");

  const strings = sharedStrings(entries.has("xl/sharedStrings.xml") ? readZipFile(buffer, entries.get("xl/sharedStrings.xml")!) : undefined);
  const xml = readZipFile(buffer, sheet);
  const grid: Grid = [];

  for (const rowMatch of xml.matchAll(/<row\b[^>]*?(?:\/>|>([\s\S]*?)<\/row>)/g)) {
    const row: string[] = [];
    for (const cell of (rowMatch[1] ?? "").matchAll(/<c\s([^>]*?)(?:\/>|>([\s\S]*?)<\/c>)/g)) {
      const attrs = cell[1];
      const inner = cell[2] ?? "";
      const ref = attrs.match(/\br="([A-Z]+)\d+"/)?.[1];
      const type = attrs.match(/\bt="([^"]+)"/)?.[1] ?? "n";
      const index = ref ? columnIndex(ref) : row.length;

      let value = "";
      if (type === "inlineStr") value = textRuns(inner);
      else {
        const raw = inner.match(/<v>([\s\S]*?)<\/v>/)?.[1];
        if (raw !== undefined) {
          const decoded = decodeXml(raw);
          if (type === "s") value = strings[Number(decoded)] ?? "";
          else if (type === "b") value = decoded === "1" ? "TRUE" : "FALSE";
          else if (type === "n") value = numberText(decoded);
          else value = decoded; // "str" (formula result), "d", "e"
        }
      }
      while (row.length < index) row.push("");
      row[index] = value;
    }
    grid.push(row);
    if (grid.length > MAX_ROWS) throw new SpreadsheetError(`The file has more than ${MAX_ROWS} rows.`);
  }
  return grid;
}

// ---------------------------------------------------------------------------

export const ACCEPTED_EXTENSIONS = [".csv", ".tsv", ".txt", ".xlsx"] as const;

/** Picks the parser from the file name (the browser's MIME type is unreliable for spreadsheets). */
export function parseSpreadsheet(fileName: string, data: Buffer): Grid {
  const extension = fileName.toLowerCase().match(/\.[a-z0-9]+$/)?.[0] ?? "";
  if (extension === ".xlsx") return parseXlsx(data);
  if (extension === ".xls") throw new SpreadsheetError("Legacy .xls files are not supported — save the sheet as .xlsx or .csv.");
  if ((ACCEPTED_EXTENSIONS as readonly string[]).includes(extension)) return parseCsv(data.toString("utf8"));
  // Zip signature: an .xlsx with a wrong or missing extension.
  if (data.length > 4 && data.readUInt32LE(0) === 0x04034b50) return parseXlsx(data);
  throw new SpreadsheetError("Unsupported file type — upload a .csv or .xlsx export of the sheet.");
}