import { inflateRawSync } from 'node:zlib';

// Minimal reader for the .xlsx files that national food-table publishers deliver.
// An .xlsx is a ZIP of XML parts; only the parts needed to read cell text are parsed.
// The input is untrusted: sizes are bounded and nothing is evaluated (no formulas, macros,
// external links). Cell values are returned as the delivered text, never reformatted.
const maxEntryBytes = 400 * 1024 * 1024;
const maxEntries = 2000;

export type Cell = string | null;
export type Sheet = { name: string; rows: Cell[][] };

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

function listZip(zip: Buffer): Map<string, ZipEntry> {
  const searchFrom = Math.max(0, zip.length - 65557);
  let end = -1;
  for (let i = zip.length - 22; i >= searchFrom; i--) {
    if (zip.readUInt32LE(i) === 0x06054b50) { end = i; break; }
  }
  if (end < 0) throw new Error('Not a ZIP file');
  const count = zip.readUInt16LE(end + 10);
  let at = zip.readUInt32LE(end + 16);
  if (count === 0xffff || at === 0xffffffff) throw new Error('ZIP64 is not supported');
  if (count > maxEntries) throw new Error('ZIP has too many entries');
  const entries = new Map<string, ZipEntry>();
  for (let i = 0; i < count; i++) {
    if (at + 46 > zip.length || zip.readUInt32LE(at) !== 0x02014b50) throw new Error('Broken ZIP directory');
    const flags = zip.readUInt16LE(at + 8);
    if (flags & 1) throw new Error('Encrypted ZIP entries are not supported');
    const nameLength = zip.readUInt16LE(at + 28);
    const extraLength = zip.readUInt16LE(at + 30);
    const commentLength = zip.readUInt16LE(at + 32);
    const name = zip.toString('utf8', at + 46, at + 46 + nameLength);
    entries.set(name, { name, method: zip.readUInt16LE(at + 10), compressed: zip.readUInt32LE(at + 20),
      size: zip.readUInt32LE(at + 24), offset: zip.readUInt32LE(at + 42) });
    at += 46 + nameLength + extraLength + commentLength;
  }
  return entries;
}

function readEntry(zip: Buffer, entry: ZipEntry): Buffer {
  if (entry.size > maxEntryBytes) throw new Error('ZIP entry is too large');
  const at = entry.offset;
  if (at + 30 > zip.length || zip.readUInt32LE(at) !== 0x04034b50) throw new Error('Broken ZIP entry');
  const start = at + 30 + zip.readUInt16LE(at + 26) + zip.readUInt16LE(at + 28);
  const data = zip.subarray(start, start + entry.compressed);
  if (data.length !== entry.compressed) throw new Error('Truncated ZIP entry');
  let out: Buffer;
  if (entry.method === 0) out = Buffer.from(data);
  else if (entry.method === 8) out = inflateRawSync(data, { maxOutputLength: maxEntryBytes });
  else throw new Error('Unsupported ZIP compression');
  if (out.length !== entry.size) throw new Error('ZIP entry size does not match its directory');
  return out;
}

const entities: Record<string, string> = { amp: '&', lt: '<', gt: '>', quot: '"', apos: "'" };
function decode(text: string): string {
  return text.replace(/&(#x[0-9a-fA-F]+|#[0-9]+|[a-z]+);/g, (whole, body: string) => {
    if (body[0] !== '#') return entities[body] ?? whole;
    const code = body[1] === 'x' ? parseInt(body.slice(2), 16) : parseInt(body.slice(1), 10);
    return Number.isInteger(code) && code >= 0 && code <= 0x10ffff && !(code >= 0xd800 && code <= 0xdfff)
      ? String.fromCodePoint(code) : whole;
  });
}

// Text of every <t> inside a fragment; rich-text runs are concatenated, phonetic runs skipped.
function textOf(fragment: string): string {
  const clean = fragment.replace(/<rPh\b[\s\S]*?<\/rPh>/g, '');
  let out = '';
  for (const match of clean.matchAll(/<t\b[^>]*?(?:\/>|>([\s\S]*?)<\/t>)/g)) out += decode(match[1] ?? '');
  return out;
}

function columnIndex(reference: string): number {
  let n = 0;
  for (const char of reference) {
    const code = char.charCodeAt(0);
    if (code < 65 || code > 90) break;
    n = n * 26 + (code - 64);
  }
  return n - 1;
}

function attribute(attributes: string, name: string): string | null {
  const match = attributes.match(new RegExp(`(?:^|\\s)${name}="([^"]*)"`));
  return match ? decode(match[1]) : null;
}

function parseSheet(xml: string, shared: string[]): Cell[][] {
  const rows: Cell[][] = [];
  for (const row of xml.matchAll(/<row\b([^>]*?)(?:\/>|>([\s\S]*?)<\/row>)/g)) {
    const number = Number(attribute(row[1], 'r'));
    if (!Number.isInteger(number) || number < 1 || number > 2_000_000) throw new Error('Invalid sheet row number');
    const cells: Cell[] = [];
    for (const cell of (row[2] ?? '').matchAll(/<c\b([^>]*?)(?:\/>|>([\s\S]*?)<\/c>)/g)) {
      const reference = attribute(cell[1], 'r');
      const index = reference ? columnIndex(reference) : cells.length;
      if (index < 0 || index > 16_383) throw new Error('Invalid sheet column');
      const type = attribute(cell[1], 't');
      const body = cell[2] ?? '';
      const value = body.match(/<v\b[^>]*>([\s\S]*?)<\/v>/)?.[1];
      let text: Cell = null;
      if (type === 'inlineStr') text = textOf(body.match(/<is\b[^>]*>([\s\S]*?)<\/is>/)?.[1] ?? '');
      else if (value !== undefined) {
        if (type === 's') {
          const at = Number(value);
          if (!Number.isInteger(at) || at < 0 || at >= shared.length) throw new Error('Invalid shared string index');
          text = shared[at];
        } else text = decode(value);
        if (type === 'e') text = null;
      }
      while (cells.length <= index) cells.push(null);
      cells[index] = text === '' ? null : text;
    }
    while (rows.length < number - 1) rows.push([]);
    rows[number - 1] = cells;
  }
  return rows;
}

// Read access to the entries of an untrusted ZIP; also used for the CNF CSV bundle.
export function openZip(bytes: Buffer): { names: string[]; read(name: string): Buffer | null } {
  const zip = listZip(bytes);
  return { names: [...zip.keys()], read: name => { const entry = zip.get(name); return entry ? readEntry(bytes, entry) : null; } };
}

export function readXlsx(bytes: Buffer): Sheet[] {
  const archive = openZip(bytes);
  const part = (name: string) => archive.read(name)?.toString('utf8') ?? null;
  const workbook = part('xl/workbook.xml');
  const relationships = part('xl/_rels/workbook.xml.rels');
  if (!workbook || !relationships) throw new Error('Not an .xlsx workbook');
  const targets = new Map<string, string>();
  for (const rel of relationships.matchAll(/<Relationship\b([^>]*?)\/?>/g)) {
    const id = attribute(rel[1], 'Id');
    const target = attribute(rel[1], 'Target');
    if (id && target) targets.set(id, target.startsWith('/') ? target.slice(1) : `xl/${target}`);
  }
  const sharedXml = part('xl/sharedStrings.xml');
  const shared = sharedXml
    ? [...sharedXml.matchAll(/<si\b[^>]*?(?:\/>|>([\s\S]*?)<\/si>)/g)].map(match => textOf(match[1] ?? ''))
    : [];
  const sheets: Sheet[] = [];
  for (const sheet of workbook.matchAll(/<sheet\b([^>]*?)\/?>/g)) {
    const name = attribute(sheet[1], 'name');
    const target = targets.get(attribute(sheet[1], 'r:id') ?? '');
    const xml = target ? part(target) : null;
    if (!name || xml === null) throw new Error('Workbook sheet part is missing');
    sheets.push({ name, rows: parseSheet(xml, shared) });
  }
  return sheets;
}