import JSZip from 'jszip';
import type { ImportSheetData } from './imports.workbook';

const MAX_SHEETS = 30;
const MAX_ROWS_PER_SHEET = 3000;
const MAX_COLS_PER_SHEET = 60;

export interface ExcelFallbackSheetRows {
  name: string;
  rows: string[][];
}

/**
 * Read an .xlsx workbook without relying on ExcelJS. WPS-produced files
 * sometimes prefix every OOXML element with a namespace that ExcelJS cannot
 * load; this fallback strips the prefixes and parses shared strings directly.
 */
export async function readXlsxSheetsFallback(buffer: Buffer): Promise<ExcelFallbackSheetRows[]> {
  const zip = await JSZip.loadAsync(buffer);
  const readEntry = async (name: string): Promise<string | null> => {
    const entry = zip.file(name);
    return entry ? entry.async('string') : null;
  };
  const workbookXml = await readEntry('xl/workbook.xml');
  if (!workbookXml) throw new Error('workbook.xml missing');
  const stripPrefixes = (value: string): string =>
    value.replace(/<(\/?)([a-zA-Z][\w.]*):/g, '<$1');
  const relsXml = stripPrefixes((await readEntry('xl/_rels/workbook.xml.rels')) ?? '');
  const relTargets = new Map<string, string>();
  for (const match of relsXml.matchAll(
    /<Relationship[^>]*\bId="([^"]+)"[^>]*\bTarget="([^"]+)"/g,
  )) {
    const target = match[2].replace(/^\/+/, '');
    relTargets.set(match[1], target.startsWith('xl/') ? target : `xl/${target}`);
  }

  const sharedStrings = await parseSharedStringsFallback(readEntry);
  const sheets: ExcelFallbackSheetRows[] = [];
  const cleanWorkbook = stripPrefixes(workbookXml);
  for (const match of cleanWorkbook.matchAll(/<sheet\b[^>]*\/?>/g)) {
    const tag = match[0].replace(/<sheet\b/, '<sheet').replace(/\/?>$/, '>');
    const name = tag.match(/\bname="([^"]+)"/)?.[1];
    const rid = tag.match(/\br:id="([^"]+)"/)?.[1];
    if (!name || !rid) continue;
    const target = relTargets.get(rid);
    const sheetXml = target ? await readEntry(target) : null;
    if (!sheetXml) continue;
    sheets.push({
      name: unescapeXml(name),
      rows: sheetRowsFromXmlFallback(sheetXml, sharedStrings),
    });
  }
  return sheets;
}

/** Convert fallback rows (all physical rows as strings) into import sheet views. */
export function fallbackSheetsToImportSheets(
  sheets: ExcelFallbackSheetRows[],
  headerRow = 1,
): ImportSheetData[] {
  const result: ImportSheetData[] = [];
  for (const sheet of sheets.slice(0, MAX_SHEETS)) {
    const headerIndex = headerRow - 1;
    const headers =
      sheet.rows[headerIndex]?.slice(0, MAX_COLS_PER_SHEET).map((header) => header.trim()) ?? [];
    if (!headers.some((header) => header.trim() !== '')) continue;
    const rows: ImportSheetData['rows'] = [];
    const rowNumbers: number[] = [];
    for (let i = headerIndex + 1; i < sheet.rows.length && rows.length < MAX_ROWS_PER_SHEET; i += 1) {
      const values = sheet.rows[i].slice(0, headers.length);
      if (values.every((value) => value === '')) continue;
      rows.push(values);
      rowNumbers.push(i + 1);
    }
    if (rows.length === 0) continue;
    result.push({
      name: sheet.name,
      headers,
      rows,
      headerRow,
      rowNumbers,
    });
  }
  return result;
}

async function parseSharedStringsFallback(
  readEntry: (name: string) => Promise<string | null>,
): Promise<string[]> {
  const xml = await readEntry('xl/sharedStrings.xml');
  if (!xml) return [];
  const clean = xml.replace(/<(\/?)([a-zA-Z][\w.]*):/g, '<$1');
  const strings: string[] = [];
  for (const match of clean.matchAll(/<si\b[^>]*>([\s\S]*?)<\/si>/gs)) {
    const texts = [...match[1].matchAll(/<t\b[^>]*>([\s\S]*?)<\/t>/g)].map((part) =>
      unescapeXml(part[1]),
    );
    strings.push(texts.join(''));
  }
  return strings;
}

function sheetRowsFromXmlFallback(sheetXml: string, sharedStrings: string[]): string[][] {
  const rows: string[][] = [];
  const xml = sheetXml.replace(/<(\/?)([a-zA-Z][\w.]*):/g, '<$1');
  for (const rowMatch of xml.matchAll(/<row\b[^>]*>([\s\S]*?)<\/row>/gs)) {
    const cells = new Map<number, string>();
    let maxColumn = -1;
    for (const cellMatch of rowMatch[1].matchAll(/<c\b([^>]*)\/?>([\s\S]*?)<\/c>/gs)) {
      const attrs = cellMatch[1];
      const refMatch = attrs.match(/\br="([A-Z]+)\d+"/);
      const column = refMatch ? columnIndex(refMatch[1]) : -1;
      const type = attrs.match(/\bt="([^"]+)"/)?.[1] ?? 'n';
      const body = cellMatch[2] ?? '';
      let value = '';
      if (type === 's') {
        const index = Number(body.match(/<v>([^<]*)<\/v>/)?.[1] ?? '');
        value = Number.isInteger(index) ? (sharedStrings[index] ?? '') : '';
      } else if (type === 'inlineStr') {
        const texts = [...body.matchAll(/<t\b[^>]*>([\s\S]*?)<\/t>/g)].map((part) =>
          unescapeXml(part[1]),
        );
        value = texts.join('');
      } else {
        value = unescapeXml(body.match(/<v>([\s\S]*?)<\/v>/)?.[1] ?? '');
        if (type === 'b') value = value === '1' ? 'true' : 'false';
      }
      if (column >= 0) {
        cells.set(column, value);
        maxColumn = Math.max(maxColumn, column);
      }
    }
    if (maxColumn < 0) continue;
    const values = Array.from({ length: maxColumn + 1 }, (_, index) => cells.get(index) ?? '');
    if (values.every((value) => value === '')) continue;
    rows.push(values);
  }
  return rows;
}

function columnIndex(letters: string): number {
  let index = 0;
  for (const char of letters.toUpperCase()) {
    index = index * 26 + (char.charCodeAt(0) - 64);
  }
  return index - 1;
}

function unescapeXml(value: string): string {
  return value
    .replace(/&lt;/g, '<')
    .replace(/&gt;/g, '>')
    .replace(/&quot;/g, '"')
    .replace(/&apos;/g, "'")
    .replace(/&amp;/g, '&')
    .replace(/&#x([0-9a-fA-F]+);/g, (_all, hex: string) =>
      String.fromCodePoint(Number.parseInt(hex, 16)),
    )
    .replace(/&#(\d+);/g, (_all, dec: string) => String.fromCodePoint(Number(dec)));
}
