import jsPDF from 'jspdf';
import 'jspdf-autotable';
import ExcelJS from 'exceljs';

const TYPE_LABELS = {
  lost: 'Lost',
  accidentally_damaged: 'Accidentally Damaged',
  burned: 'Burned',
  broken: 'Broken',
  expired: 'Expired',
  contaminated: 'Contaminated',
  spillage: 'Spillage',
  recalled: 'Recalled',
  other: 'Other',
};

// Convert image URL to data URL + read natural dimensions (for aspect-ratio preservation)
async function loadImage(url) {
  if (!url) return null;
  try {
    const res = await fetch(url);
    const blob = await res.blob();
    const dataUrl = await new Promise((resolve, reject) => {
      const reader = new FileReader();
      reader.onload = () => resolve(reader.result);
      reader.onerror = reject;
      reader.readAsDataURL(blob);
    });
    const dims = await new Promise((resolve) => {
      const img = new Image();
      img.onload = () => resolve({ w: img.naturalWidth, h: img.naturalHeight });
      img.onerror = () => resolve({ w: 0, h: 0 });
      img.src = dataUrl;
    });
    return { dataUrl, ...dims };
  } catch {
    return null;
  }
}

// Fit inside maxW × maxH while preserving aspect ratio
function fitBox(natW, natH, maxW, maxH) {
  if (!natW || !natH) return { w: maxW, h: maxH };
  const scale = Math.min(maxW / natW, maxH / natH);
  return { w: natW * scale, h: natH * scale };
}

// Convert data URL to ArrayBuffer for ExcelJS
function dataURLtoArrayBuffer(dataUrl) {
  const base64 = dataUrl.split(',')[1];
  const binary = atob(base64);
  const bytes = new Uint8Array(binary.length);
  for (let i = 0; i < binary.length; i++) bytes[i] = binary.charCodeAt(i);
  return bytes.buffer;
}

export async function exportReportsPDF(reports, filename, periodLabel) {
  const doc = new jsPDF({ orientation: 'landscape' });

  // Header
  doc.setFontSize(18); doc.setTextColor(59, 130, 246);
  doc.text('PeptideOS - Stock Reduction Reports', 14, 18);
  doc.setFontSize(10); doc.setTextColor(100);
  doc.text(`Period: ${periodLabel || 'All'}`, 14, 25);
  doc.text(`Generated: ${new Date().toLocaleString('en-US', { dateStyle: 'long', timeStyle: 'short' })}`, 14, 31);
  doc.text(`Total Reports: ${reports.length} | Total Qty Loss: ${reports.reduce((s, r) => s + (r.qty || 0), 0)}`, 14, 37);

  // Summary table
  doc.autoTable({
    startY: 43,
    head: [['#', 'Date', 'Product', 'Type', 'Qty', 'Reporter', 'Status', 'Description']],
    body: reports.map((r, idx) => [
      idx + 1,
      r.created_at ? new Date(r.created_at).toLocaleString() : '',
      `${r.product_name || '?'} (${r.mg || '?'}mg/${r.ml || '?'}ml)`,
      TYPE_LABELS[r.reduction_type] || r.reduction_type,
      r.qty,
      r.reporter_name || r.reporter_username || '?',
      r.status + (r.archived_at ? ' (archived)' : ''),
      (r.description || '').slice(0, 60) + ((r.description || '').length > 60 ? '...' : ''),
    ]),
    styles: { fontSize: 7, cellPadding: 2 },
    headStyles: { fillColor: [220, 38, 38], textColor: 255, fontStyle: 'bold', fontSize: 8 },
    alternateRowStyles: { fillColor: [250, 245, 245] },
    columnStyles: { 0: { cellWidth: 8 }, 4: { halign: 'right', fontStyle: 'bold' } },
  });

  // Per-report detail pages (with photos)
  for (let i = 0; i < reports.length; i++) {
    const r = reports[i];
    doc.addPage();
    doc.setFontSize(14); doc.setTextColor(40);
    doc.text(`Report #${i + 1} — ${r.product_name || '?'}`, 14, 18);
    doc.setFontSize(9); doc.setTextColor(100);
    doc.text(`ID: ${r.id}`, 14, 25);

    doc.autoTable({
      startY: 30,
      theme: 'plain',
      body: [
        ['Date', r.created_at ? new Date(r.created_at).toLocaleString() : '-'],
        ['Product', `${r.product_name || '?'} (${r.mg || '?'}mg/${r.ml || '?'}ml)`],
        ['Batch', r.batch || '-'],
        ['Route', r.route || '-'],
        ['Reduction Type', TYPE_LABELS[r.reduction_type] || r.reduction_type],
        ['Quantity Loss', String(r.qty)],
        ['Status', r.status + (r.archived_at ? ' (archived)' : '')],
        ['Reporter', r.reporter_name || r.reporter_username || '?'],
        ['Processed By', r.processor_name || '-'],
        ['Processed At', r.processed_at ? new Date(r.processed_at).toLocaleString() : '-'],
        ['Notes', r.notes || '-'],
      ],
      styles: { fontSize: 9, cellPadding: 2 },
      columnStyles: { 0: { fontStyle: 'bold', cellWidth: 45, textColor: [60, 60, 60] } },
    });

    const afterInfo = doc.lastAutoTable.finalY + 4;
    doc.setFontSize(10); doc.setFont(undefined, 'bold');
    doc.text('Description:', 14, afterInfo);
    doc.setFont(undefined, 'normal'); doc.setFontSize(9);
    const descLines = doc.splitTextToSize(r.description || '-', 260);
    doc.text(descLines, 14, afterInfo + 5);

    // Photos (aspect-ratio preserved, fit within 90×70 mm box)
    const photoY = afterInfo + 5 + descLines.length * 4 + 5;
    const photos = [r.photo_1, r.photo_2].filter(Boolean);
    if (photos.length > 0) {
      doc.setFontSize(10); doc.setFont(undefined, 'bold');
      doc.text('Evidence Photos:', 14, photoY);
      const imgY = photoY + 4;
      const maxBoxW = 90, maxBoxH = 70;
      for (let pi = 0; pi < photos.length; pi++) {
        const img = await loadImage(photos[pi]);
        if (img?.dataUrl) {
          try {
            const fmt = img.dataUrl.includes('image/png') ? 'PNG' : 'JPEG';
            const { w, h } = fitBox(img.w, img.h, maxBoxW, maxBoxH);
            const xOffset = 14 + pi * (maxBoxW + 10) + (maxBoxW - w) / 2;
            const yOffset = imgY + (maxBoxH - h) / 2;
            doc.addImage(img.dataUrl, fmt, xOffset, yOffset, w, h);
          } catch {}
        }
      }
    }
  }

  // Page numbers
  const pageCount = doc.internal.getNumberOfPages();
  for (let i = 1; i <= pageCount; i++) {
    doc.setPage(i);
    doc.setFontSize(8); doc.setTextColor(150);
    doc.text(
      `PeptideOS Stock Reports — Page ${i} of ${pageCount}`,
      doc.internal.pageSize.getWidth() / 2,
      doc.internal.pageSize.getHeight() - 8,
      { align: 'center' }
    );
  }

  doc.save(filename || 'PeptideOS-Reports.pdf');
}

export async function exportReportsExcel(reports, filename, periodLabel) {
  const wb = new ExcelJS.Workbook();
  wb.creator = 'PeptideOS';
  wb.created = new Date();

  const ws = wb.addWorksheet('Reports', {
    properties: { defaultRowHeight: 18 },
    views: [{ state: 'frozen', ySplit: 4 }],
  });

  // Title
  ws.mergeCells('A1:I1');
  ws.getCell('A1').value = 'PeptideOS — Stock Reduction Reports';
  ws.getCell('A1').font = { size: 16, bold: true, color: { argb: 'FFDC2626' } };
  ws.getCell('A1').alignment = { horizontal: 'center' };

  ws.mergeCells('A2:I2');
  ws.getCell('A2').value = `Period: ${periodLabel || 'All'} · Generated: ${new Date().toLocaleString()}`;
  ws.getCell('A2').font = { size: 10, color: { argb: 'FF666666' } };
  ws.getCell('A2').alignment = { horizontal: 'center' };

  ws.addRow([]);

  // Header
  const headerRow = ws.addRow([
    '#', 'Date', 'Product', 'Batch', 'Type', 'Qty Loss',
    'Reporter', 'Status', 'Description',
  ]);
  headerRow.eachCell((cell) => {
    cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFDC2626' } };
    cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, size: 11 };
    cell.alignment = { vertical: 'middle', horizontal: 'center' };
    cell.border = {
      top: { style: 'thin' }, bottom: { style: 'thin' },
      left: { style: 'thin' }, right: { style: 'thin' },
    };
  });

  // Data rows
  reports.forEach((r, idx) => {
    const row = ws.addRow([
      idx + 1,
      r.created_at ? new Date(r.created_at).toLocaleString() : '',
      `${r.product_name || '?'} (${r.mg || '?'}mg/${r.ml || '?'}ml)`,
      r.batch || '-',
      TYPE_LABELS[r.reduction_type] || r.reduction_type,
      r.qty,
      r.reporter_name || r.reporter_username || '?',
      r.status + (r.archived_at ? ' (archived)' : ''),
      r.description || '',
    ]);
    if (idx % 2 === 1) {
      row.eachCell((cell) => {
        cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFFAF5F5' } };
      });
    }
    row.eachCell((cell) => {
      cell.border = {
        top: { style: 'thin', color: { argb: 'FFE5E7EB' } },
        bottom: { style: 'thin', color: { argb: 'FFE5E7EB' } },
        left: { style: 'thin', color: { argb: 'FFE5E7EB' } },
        right: { style: 'thin', color: { argb: 'FFE5E7EB' } },
      };
      cell.alignment = { vertical: 'middle', wrapText: true };
    });
    row.getCell(6).font = { bold: true, color: { argb: 'FFDC2626' } };
    row.getCell(6).alignment = { horizontal: 'right' };
  });

  // Column widths
  ws.getColumn(1).width = 5;
  ws.getColumn(2).width = 20;
  ws.getColumn(3).width = 28;
  ws.getColumn(4).width = 16;
  ws.getColumn(5).width = 18;
  ws.getColumn(6).width = 10;
  ws.getColumn(7).width = 20;
  ws.getColumn(8).width = 14;
  ws.getColumn(9).width = 50;

  ws.autoFilter = { from: 'A4', to: `I${4 + reports.length}` };

  // Summary row
  const summaryRow = ws.addRow([]);
  ws.addRow([]);
  const totalRow = ws.addRow(['', '', '', '', 'TOTAL LOSS', reports.reduce((s, r) => s + (r.qty || 0), 0)]);
  totalRow.getCell(5).font = { bold: true };
  totalRow.getCell(6).font = { bold: true, color: { argb: 'FFDC2626' }, size: 12 };
  totalRow.getCell(6).alignment = { horizontal: 'right' };

  // Second sheet: Photos
  const photoSheet = wb.addWorksheet('Photos', { properties: { defaultRowHeight: 20 } });
  photoSheet.columns = [
    { header: '#', width: 5 },
    { header: 'Report ID', width: 18 },
    { header: 'Product', width: 25 },
    { header: 'Photo 1', width: 30 },
    { header: 'Photo 2', width: 30 },
  ];
  photoSheet.getRow(1).eachCell((cell) => {
    cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFDC2626' } };
    cell.font = { bold: true, color: { argb: 'FFFFFFFF' } };
  });

  for (let i = 0; i < reports.length; i++) {
    const r = reports[i];
    const rowNum = i + 2;
    const row = photoSheet.addRow([i + 1, r.id, r.product_name || '?', '', '']);
    photoSheet.getRow(rowNum).height = 120;

    for (let pi = 0; pi < 2; pi++) {
      const photo = pi === 0 ? r.photo_1 : r.photo_2;
      if (!photo) continue;
      const img = await loadImage(photo);
      if (!img?.dataUrl) continue;
      try {
        const ext = img.dataUrl.includes('image/png') ? 'png' : 'jpeg';
        const imageId = wb.addImage({
          buffer: dataURLtoArrayBuffer(img.dataUrl),
          extension: ext,
        });
        // Preserve aspect ratio — fit inside 200×150 px box
        const { w, h } = fitBox(img.w, img.h, 200, 150);
        photoSheet.addImage(imageId, {
          tl: { col: 3 + pi, row: rowNum - 1 },
          ext: { width: w, height: h },
          editAs: 'oneCell',
        });
      } catch {}
    }
    row.commit();
  }

  const buf = await wb.xlsx.writeBuffer();
  const blob = new Blob([buf], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
  const url = URL.createObjectURL(blob);
  const a = document.createElement('a');
  a.href = url;
  a.download = filename || 'PeptideOS-Reports.xlsx';
  a.click();
  URL.revokeObjectURL(url);
}
