import { readFileSync, statSync } from 'node:fs';
import { inflateRawSync } from 'node:zlib';

type ZipEntry = {
  compressionMethod: number;
  compressedSize: number;
  uncompressedSize: number;
  fileName: string;
  localHeaderOffset: number;
};

function readUInt16LE(buf: Buffer, offset: number) {
  return buf.readUInt16LE(offset);
}

function readUInt32LE(buf: Buffer, offset: number) {
  return buf.readUInt32LE(offset);
}

function decodeXmlEntities(input: string) {
  return input
    .replaceAll('&lt;', '<')
    .replaceAll('&gt;', '>')
    .replaceAll('&quot;', '"')
    .replaceAll('&apos;', "'")
    .replaceAll('&amp;', '&');
}

function findEndOfCentralDirectory(buf: Buffer) {
  // EOCD signature: 0x06054b50
  const sig = 0x06054b50;
  // EOCD can have a variable-length comment, so scan from the end.
  for (let i = buf.length - 22; i >= 0 && i >= buf.length - 65_557; i--) {
    if (readUInt32LE(buf, i) === sig) return i;
  }
  return -1;
}

function readCentralDirectoryEntries(buf: Buffer): ZipEntry[] {
  const eocdOffset = findEndOfCentralDirectory(buf);
  if (eocdOffset < 0) throw new Error('Invalid XLSX: missing EOCD');

  const cdSize = readUInt32LE(buf, eocdOffset + 12);
  const cdOffset = readUInt32LE(buf, eocdOffset + 16);

  const cdEnd = cdOffset + cdSize;
  const entries: ZipEntry[] = [];

  let offset = cdOffset;
  while (offset < cdEnd) {
    // Central directory file header signature: 0x02014b50
    if (readUInt32LE(buf, offset) !== 0x02014b50) {
      throw new Error('Invalid XLSX: bad central directory header');
    }

    const compressionMethod = readUInt16LE(buf, offset + 10);
    const compressedSize = readUInt32LE(buf, offset + 20);
    const uncompressedSize = readUInt32LE(buf, offset + 24);
    const fileNameLength = readUInt16LE(buf, offset + 28);
    const extraLength = readUInt16LE(buf, offset + 30);
    const commentLength = readUInt16LE(buf, offset + 32);
    const localHeaderOffset = readUInt32LE(buf, offset + 42);

    const fileNameStart = offset + 46;
    const fileNameEnd = fileNameStart + fileNameLength;
    const fileName = buf.toString('utf8', fileNameStart, fileNameEnd);

    entries.push({
      compressionMethod,
      compressedSize,
      uncompressedSize,
      fileName,
      localHeaderOffset,
    });

    offset = fileNameEnd + extraLength + commentLength;
  }

  return entries;
}

function extractZipFile(buf: Buffer, entry: ZipEntry): Buffer {
  // Local file header signature: 0x04034b50
  if (readUInt32LE(buf, entry.localHeaderOffset) !== 0x04034b50) {
    throw new Error(`Invalid XLSX: bad local header for ${entry.fileName}`);
  }

  const fileNameLength = readUInt16LE(buf, entry.localHeaderOffset + 26);
  const extraLength = readUInt16LE(buf, entry.localHeaderOffset + 28);

  const dataStart = entry.localHeaderOffset + 30 + fileNameLength + extraLength;
  const dataEnd = dataStart + entry.compressedSize;
  const compressed = buf.subarray(dataStart, dataEnd);

  if (entry.compressionMethod === 0) return Buffer.from(compressed);
  if (entry.compressionMethod === 8) return inflateRawSync(compressed);

  throw new Error(`Unsupported compression method ${entry.compressionMethod} for ${entry.fileName}`);
}

export function readXlsxXmlFiles(xlsxPath: string) {
  const buffer = readFileSync(xlsxPath);
  const entries = readCentralDirectoryEntries(buffer);

  const files = new Map<string, string>();
  for (const entry of entries) {
    const data = extractZipFile(buffer, entry);
    files.set(entry.fileName, data.toString('utf8'));
  }

  return files;
}

export function parseSharedStrings(sharedStringsXml: string) {
  const strings: string[] = [];
  const siRegex = /<si>([\s\S]*?)<\/si>/g;
  let siMatch: RegExpExecArray | null;
  while ((siMatch = siRegex.exec(sharedStringsXml))) {
    const si = siMatch[1];
    const tRegex = /<t(?:\s[^>]*)?>([\s\S]*?)<\/t>/g;
    let tMatch: RegExpExecArray | null;
    let text = '';
    while ((tMatch = tRegex.exec(si))) {
      text += decodeXmlEntities(tMatch[1]);
    }
    strings.push(text);
  }
  return strings;
}

type CellValue = string | number | null;

function colToIndex(col: string) {
  // A -> 0, B -> 1, ... Z -> 25, AA -> 26
  let n = 0;
  for (let i = 0; i < col.length; i++) {
    n = n * 26 + (col.charCodeAt(i) - 64);
  }
  return n - 1;
}

export function parseFirstSheetToObjects(params: {
  sheetXml: string;
  sharedStrings: string[];
}) {
  const { sheetXml, sharedStrings } = params;

  const rowRegex = /<row[^>]*\sr="(\d+)"[^>]*>([\s\S]*?)<\/row>/g;
  const rows: Record<number, Record<number, CellValue>> = {};

  let rowMatch: RegExpExecArray | null;
  while ((rowMatch = rowRegex.exec(sheetXml))) {
    const rowNumber = Number(rowMatch[1]);
    const rowXml = rowMatch[2];

    const cells: Record<number, CellValue> = {};
    // NOTE: We parse `<c ...>` attributes separately because naive regexes often
    // accidentally consume `t="s"` (shared-string) and then fail to decode values.
    const cellRegex = /<c\b([^>]*)>(?:[\s\S]*?<v>([^<]*)<\/v>)?[\s\S]*?<\/c>|<c\b([^>]*)\/>/g;

    let cellMatch: RegExpExecArray | null;
    while ((cellMatch = cellRegex.exec(rowXml))) {
      const attrs = (cellMatch[1] || cellMatch[3] || '').trim();
      const rawV = (cellMatch[2] ?? null) as string | null;

      const rMatch = attrs.match(/\br="([A-Z]+)\d+"/);
      if (!rMatch) continue;
      const colLetters = rMatch[1];

      const tMatch = attrs.match(/\bt="([^"]+)"/);
      const t = tMatch?.[1] ?? null;

      const idx = colToIndex(colLetters);

      if (rawV == null) {
        cells[idx] = null;
        continue;
      }

      if (t === 's') {
        const sIndex = Number(rawV);
        cells[idx] = sharedStrings[sIndex] ?? '';
        continue;
      }

      const asNumber = Number(rawV);
      cells[idx] = Number.isFinite(asNumber) ? asNumber : rawV;
    }

    rows[rowNumber] = cells;
  }

  const headerRow = rows[1] || {};
  const headers: string[] = [];
  for (const [k, v] of Object.entries(headerRow)) {
    const idx = Number(k);
    headers[idx] = typeof v === 'string' ? v : String(v ?? '');
  }

  const objects: Record<string, CellValue>[] = [];
  for (const [rowNumberStr, cells] of Object.entries(rows)) {
    const rowNumber = Number(rowNumberStr);
    if (rowNumber === 1) continue;

    const obj: Record<string, CellValue> = {};
    let hasAny = false;
    for (let i = 0; i < headers.length; i++) {
      const key = headers[i];
      if (!key) continue;
      const value = cells[i] ?? null;
      if (value !== null && value !== '') hasAny = true;
      obj[key] = value;
    }
    if (hasAny) objects.push(obj);
  }

  return objects;
}

let mtimeCache: { mtimeMs: number; files: Map<string, string> } | null = null;

export function readXlsxXmlFilesCached(xlsxPath: string) {
  const st = statSync(xlsxPath);
  if (mtimeCache && mtimeCache.mtimeMs === st.mtimeMs) return mtimeCache.files;
  const files = readXlsxXmlFiles(xlsxPath);
  mtimeCache = { mtimeMs: st.mtimeMs, files };
  return files;
}