/* Excel Import/Export via SheetJS */
/* global window, XLSX */

const FIELD_LABELS = {
  projectName: 'Projektname',
  customer: 'Kunde / Auftrag',
  materialType: 'Materialtyp',
  materialCostPerKg: 'Materialkosten pro kg (EUR)',
  materialUsageG: 'Materialverbrauch (g)',
  printTimeH: 'Druckzeit (h)',
  machineRate: 'Maschinenstundensatz (EUR/h)',
  powerKwh: 'Stromverbrauch (kWh)',
  powerPrice: 'Strompreis (EUR/kWh)',
  postMin: 'Nachbearbeitungszeit (min)',
  postRate: 'Nachbearbeitungs-Stundensatz (EUR/h)',
  packagingCost: 'Verpackungskosten (EUR)',
  shippingCost: 'Versandkosten (EUR)',
  setupCost: 'Ruestkosten (EUR)',
  scrapPct: 'Ausschussrisiko (%)',
  marginPct: 'Gewinnaufschlag (%)',
  quantity: 'Stueckzahl',
  individualAdjustment: 'Individueller Zuschlag/Rabatt (EUR)',
  vatPct: 'MwSt (%)',
  notes: 'Notizen',
};

const LABEL_TO_FIELD = Object.fromEntries(
  Object.entries(FIELD_LABELS).map(([k, v]) => [v, k])
);

const exportXlsx = (project, results) => {
  const wb = XLSX.utils.book_new();

  /* Reiter Eingabe */
  const inputRows = [['Feld', 'Wert']];
  window.Store.FIELDS.forEach((f) => {
    inputRows.push([FIELD_LABELS[f] || f, project[f] ?? '']);
  });
  const wsInput = XLSX.utils.aoa_to_sheet(inputRows);
  wsInput['!cols'] = [{ wch: 40 }, { wch: 24 }];
  XLSX.utils.book_append_sheet(wb, wsInput, 'Eingabe');

  /* Reiter Kalkulation */
  const calcRows = [
    ['Position', 'Betrag (EUR)'],
    ['1. Materialkosten', results.materialCost],
    ['2. Maschinenkosten', results.machineCost],
    ['3. Energiekosten', results.energyCost],
    ['4. Nachbearbeitungskosten', results.postCost],
    ['5. Gesamtherstellungskosten', results.totalProduction],
    ['6. Ausschuss-Zuschlag', results.scrapSurcharge],
    ['7. Zwischensumme netto', results.subtotalNet],
    ['8. Marge', results.margin],
    ['9. Kundenpreis netto', results.customerNet],
    ['10. Stueckpreis netto', results.unitNet],
    ['11. Stueckpreis brutto', results.unitGross],
    ['12. Gesamtpreis brutto', results.totalGross],
  ];
  const wsCalc = XLSX.utils.aoa_to_sheet(calcRows);
  wsCalc['!cols'] = [{ wch: 36 }, { wch: 18 }];
  XLSX.utils.book_append_sheet(wb, wsCalc, 'Kalkulation');

  /* Reiter Angebot */
  const offerRows = [
    ['Angebot'],
    [],
    ['Projekt', project.projectName || ''],
    ['Kunde', project.customer || ''],
    ['Datum', new Date().toLocaleDateString('de-DE')],
    [],
    ['Position', 'Menge', 'Stueckpreis brutto', 'Gesamt'],
    [project.projectName || 'Leistung', project.quantity || 1, results.unitGross, results.totalGross],
    [],
    ['Gesamt brutto', '', '', results.totalGross],
  ];
  const wsOffer = XLSX.utils.aoa_to_sheet(offerRows);
  wsOffer['!cols'] = [{ wch: 30 }, { wch: 12 }, { wch: 20 }, { wch: 14 }];
  XLSX.utils.book_append_sheet(wb, wsOffer, 'Angebot');

  const filename = `Kalkulation_${(project.projectName || 'Projekt').replace(/[^\w\-]+/g, '_')}.xlsx`;
  XLSX.writeFile(wb, filename);
};

const importXlsx = (file) => new Promise((resolve, reject) => {
  const reader = new FileReader();
  reader.onerror = () => reject(new Error('Datei konnte nicht gelesen werden.'));
  reader.onload = (ev) => {
    try {
      const data = new Uint8Array(ev.target.result);
      const wb = XLSX.read(data, { type: 'array' });
      const sheetName = wb.SheetNames.includes('Eingabe') ? 'Eingabe' : wb.SheetNames[0];
      const ws = wb.Sheets[sheetName];
      const rows = XLSX.utils.sheet_to_json(ws, { header: 1 });
      const updates = {};
      rows.forEach((r) => {
        if (!r || r.length < 2) return;
        const label = String(r[0]).trim();
        const field = LABEL_TO_FIELD[label];
        if (field) updates[field] = r[1];
      });
      resolve(updates);
    } catch (e) {
      reject(e);
    }
  };
  reader.readAsArrayBuffer(file);
});

window.Excel = { exportXlsx, importXlsx, FIELD_LABELS };