import { inflateRawSync } from 'zlib';

export type ExportFile = {
  fileName: string;
  mime: string;
  base64: string;
};

export function exportRows(moduleName: string, rows: Record<string, unknown>[], format = 'xlsx'): ExportFile {
  const safeName = moduleName.replace(/[^a-z0-9]+/gi, '_').replace(/^_+|_+$/g, '').toLowerCase();
  const date = new Date().toISOString().slice(0, 10);

  if (format.toLowerCase() === 'csv') {
    return {
      fileName: `${safeName}_${date}.csv`,
      mime: 'text/csv',
      base64: Buffer.from(toCsv(rows), 'utf8').toString('base64'),
    };
  }

  return {
    fileName: `${safeName}_${date}.xlsx`,
    mime: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    base64: createXlsx(rows, safeName.slice(0, 31) || 'export').toString('base64'),
  };
}

export function parseImportRows(fileName: string, content: string) {
  if (fileName.toLowerCase().endsWith('.xlsx')) {
    return parseXlsxRows(decodeImportBuffer(content));
  }

  return parseDelimitedRows(decodeImportContent(content));
}

export function normalizeImportRow(row: Record<string, string>) {
  const normalized: Record<string, string> = {};
  for (const [key, value] of Object.entries(row)) {
    const mapped = clean(key)
      .toLowerCase()
      .replace(/[^a-z0-9]+/g, ' ')
      .trim()
      .replace(/\s+([a-z0-9])/g, (_, char) => String(char).toUpperCase());
    normalized[mapped] = clean(value);
  }
  return normalized;
}

export function toCsv(rows: Record<string, unknown>[]) {
  if (!rows.length) return '';
  const headers = Object.keys(rows[0]);
  return [headers, ...rows.map((row) => headers.map((header) => formatCsvCell(row[header])))]
    .map((row) => row.join(','))
    .join('\n');
}

function formatCsvCell(value: unknown) {
  const text = value instanceof Date ? value.toISOString() : String(value ?? '');
  return /[",\n\r]/.test(text) ? `"${text.replace(/"/g, '""')}"` : text;
}

function parseDelimitedRows(text: string) {
  const rows = rawDelimitedRows(text.replace(/^\uFEFF/, '')).filter((row) => row.some((cell) => clean(cell)));
  if (rows.length < 2) return [];
  const headers = rows[0].map((header) => clean(header));
  return rows.slice(1).map((row) =>
    headers.reduce<Record<string, string>>((result, header, index) => {
      result[header || `Column ${index + 1}`] = clean(row[index]);
      return result;
    }, {}),
  );
}

function rawDelimitedRows(text: string) {
  const firstLine = text.split(/\r?\n/, 1)[0] || '';
  const delimiter = firstLine.includes('\t') ? '\t' : ',';
  const rows: string[][] = [];
  let row: string[] = [];
  let cell = '';
  let inQuotes = false;

  for (let index = 0; index < text.length; index += 1) {
    const char = text[index];
    const nextChar = text[index + 1];
    if (char === '"') {
      if (inQuotes && nextChar === '"') {
        cell += '"';
        index += 1;
      } else {
        inQuotes = !inQuotes;
      }
      continue;
    }
    if (!inQuotes && char === delimiter) {
      row.push(cell);
      cell = '';
      continue;
    }
    if (!inQuotes && (char === '\n' || char === '\r')) {
      if (char === '\r' && nextChar === '\n') index += 1;
      row.push(cell);
      rows.push(row);
      row = [];
      cell = '';
      continue;
    }
    cell += char;
  }

  row.push(cell);
  rows.push(row);
  return rows;
}

function parseXlsxRows(buffer: Buffer) {
  const files = readZipFiles(buffer);
  const sharedStrings = parseSharedStrings(files['xl/sharedStrings.xml'] || '');
  const sheet = files['xl/worksheets/sheet1.xml'];
  if (!sheet) return [];

  const rows: string[][] = [];
  for (const rowMatch of sheet.matchAll(/<row[^>]*>([\s\S]*?)<\/row>/g)) {
    const cells: string[] = [];
    for (const cellMatch of rowMatch[1].matchAll(/<c([^>]*)>([\s\S]*?)<\/c>/g)) {
      const attrs = cellMatch[1];
      const body = cellMatch[2];
      const ref = attrs.match(/\sr="([A-Z]+)\d+"/)?.[1] || columnName(cells.length + 1);
      const columnIndex = columnIndexFromName(ref) - 1;
      const type = attrs.match(/\st="([^"]+)"/)?.[1] || '';
      const valueMatch = body.match(/<v>([\s\S]*?)<\/v>/);
      const inlineMatch = body.match(/<t[^>]*>([\s\S]*?)<\/t>/);
      let value = '';
      if (type === 's' && valueMatch) value = sharedStrings[Number(valueMatch[1])] || '';
      else if (inlineMatch) value = decodeXml(inlineMatch[1]);
      else if (valueMatch) value = decodeXml(valueMatch[1]);
      cells[columnIndex] = value;
    }
    rows.push(cells.map((cell) => cell || ''));
  }

  if (rows.length < 2) return [];
  const headers = rows[0].map((header) => clean(header));
  return rows.slice(1).filter((row) => row.some((cell) => clean(cell))).map((row) =>
    headers.reduce<Record<string, string>>((result, header, index) => {
      result[header || `Column ${index + 1}`] = clean(row[index]);
      return result;
    }, {}),
  );
}

function readZipFiles(buffer: Buffer) {
  const files: Record<string, string> = {};
  const eocdOffset = buffer.lastIndexOf(Buffer.from([0x50, 0x4b, 0x05, 0x06]));
  if (eocdOffset < 0) return files;
  const totalEntries = buffer.readUInt16LE(eocdOffset + 10);
  let centralOffset = buffer.readUInt32LE(eocdOffset + 16);

  for (let index = 0; index < totalEntries; index += 1) {
    if (buffer.readUInt32LE(centralOffset) !== 0x02014b50) break;
    const compression = buffer.readUInt16LE(centralOffset + 10);
    const compressedSize = buffer.readUInt32LE(centralOffset + 20);
    const fileNameLength = buffer.readUInt16LE(centralOffset + 28);
    const extraLength = buffer.readUInt16LE(centralOffset + 30);
    const commentLength = buffer.readUInt16LE(centralOffset + 32);
    const localOffset = buffer.readUInt32LE(centralOffset + 42);
    const name = buffer.slice(centralOffset + 46, centralOffset + 46 + fileNameLength).toString('utf8');
    const localNameLength = buffer.readUInt16LE(localOffset + 26);
    const localExtraLength = buffer.readUInt16LE(localOffset + 28);
    const dataStart = localOffset + 30 + localNameLength + localExtraLength;
    const compressed = buffer.slice(dataStart, dataStart + compressedSize);
    const data = compression === 8 ? inflateRawSync(compressed) : compressed;
    files[name] = data.toString('utf8');
    centralOffset += 46 + fileNameLength + extraLength + commentLength;
  }

  return files;
}

function parseSharedStrings(xml: string) {
  const strings: string[] = [];
  for (const match of xml.matchAll(/<si[^>]*>([\s\S]*?)<\/si>/g)) {
    strings.push([...match[1].matchAll(/<t[^>]*>([\s\S]*?)<\/t>/g)].map((item) => decodeXml(item[1])).join(''));
  }
  return strings;
}

function createXlsx(rows: Record<string, unknown>[], sheetName: string) {
  const headers = rows.length ? Object.keys(rows[0]) : ['No Data'];
  const sheetRows = [headers, ...rows.map((row) => headers.map((header) => row[header]))];
  const sheetXml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?><worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData>${sheetRows
    .map(
      (row, rowIndex) =>
        `<row r="${rowIndex + 1}">${row
          .map((cell, cellIndex) => {
            const ref = `${columnName(cellIndex + 1)}${rowIndex + 1}`;
            if (typeof cell === 'number') return `<c r="${ref}"><v>${cell}</v></c>`;
            return `<c r="${ref}" t="inlineStr"><is><t>${escapeXml(String(cell ?? ''))}</t></is></c>`;
          })
          .join('')}</row>`,
    )
    .join('')}</sheetData></worksheet>`;
  const files: Record<string, string> = {
    '[Content_Types].xml':
      '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"><Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/><Default Extension="xml" ContentType="application/xml"/><Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/><Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/></Types>',
    '_rels/.rels':
      '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/></Relationships>',
    'xl/workbook.xml': `<?xml version="1.0" encoding="UTF-8" standalone="yes"?><workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><sheets><sheet name="${escapeXml(sheetName)}" sheetId="1" r:id="rId1"/></sheets></workbook>`,
    'xl/_rels/workbook.xml.rels':
      '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/></Relationships>',
    'xl/worksheets/sheet1.xml': sheetXml,
  };
  return createZip(files);
}

function createZip(files: Record<string, string>) {
  const localParts: Buffer[] = [];
  const centralParts: Buffer[] = [];
  let offset = 0;
  for (const [name, content] of Object.entries(files)) {
    const nameBuffer = Buffer.from(name);
    const data = Buffer.from(content, 'utf8');
    const crc = crc32(data);
    const local = Buffer.alloc(30);
    local.writeUInt32LE(0x04034b50, 0);
    local.writeUInt16LE(20, 4);
    local.writeUInt32LE(crc, 14);
    local.writeUInt32LE(data.length, 18);
    local.writeUInt32LE(data.length, 22);
    local.writeUInt16LE(nameBuffer.length, 26);
    localParts.push(local, nameBuffer, data);

    const central = Buffer.alloc(46);
    central.writeUInt32LE(0x02014b50, 0);
    central.writeUInt16LE(20, 4);
    central.writeUInt16LE(20, 6);
    central.writeUInt32LE(crc, 16);
    central.writeUInt32LE(data.length, 20);
    central.writeUInt32LE(data.length, 24);
    central.writeUInt16LE(nameBuffer.length, 28);
    central.writeUInt32LE(offset, 42);
    centralParts.push(central, nameBuffer);
    offset += local.length + nameBuffer.length + data.length;
  }

  const centralSize = centralParts.reduce((sum, part) => sum + part.length, 0);
  const end = Buffer.alloc(22);
  end.writeUInt32LE(0x06054b50, 0);
  end.writeUInt16LE(Object.keys(files).length, 8);
  end.writeUInt16LE(Object.keys(files).length, 10);
  end.writeUInt32LE(centralSize, 12);
  end.writeUInt32LE(offset, 16);
  return Buffer.concat([...localParts, ...centralParts, end]);
}

function crc32(buffer: Buffer) {
  let crc = -1;
  for (const byte of buffer) crc = (crc >>> 8) ^ crcTable[(crc ^ byte) & 0xff];
  return (crc ^ -1) >>> 0;
}

const crcTable = Array.from({ length: 256 }, (_, index) => {
  let value = index;
  for (let bit = 0; bit < 8; bit += 1) value = value & 1 ? 0xedb88320 ^ (value >>> 1) : value >>> 1;
  return value >>> 0;
});

function decodeImportBuffer(content: string) {
  const value = clean(content);
  const base64Match = value.match(/^data:[^;]+;base64,(.+)$/);
  return base64Match ? Buffer.from(base64Match[1], 'base64') : Buffer.from(value, 'utf8');
}

function decodeImportContent(content: string) {
  const value = clean(content);
  const base64Match = value.match(/^data:[^;]+;base64,(.+)$/);
  return base64Match ? Buffer.from(base64Match[1], 'base64').toString('utf8') : value;
}

function columnName(index: number) {
  let name = '';
  while (index > 0) {
    const mod = (index - 1) % 26;
    name = String.fromCharCode(65 + mod) + name;
    index = Math.floor((index - mod) / 26);
  }
  return name;
}

function columnIndexFromName(name: string) {
  return name.split('').reduce((sum, char) => sum * 26 + char.charCodeAt(0) - 64, 0);
}

function escapeXml(value: string) {
  return value.replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;').replace(/"/g, '&quot;');
}

function decodeXml(value: string) {
  return value
    .replace(/&quot;/g, '"')
    .replace(/&apos;/g, "'")
    .replace(/&lt;/g, '<')
    .replace(/&gt;/g, '>')
    .replace(/&amp;/g, '&');
}

function clean(value: unknown) {
  return String(value ?? '').trim();
}