// UQO Éval — Métré : exports CSV et XLSX (exceljs, MIT) d'une révision immuable. SERVEUR seulement.
import ExcelJS from "exceljs";
import type { EstimateVersion, Measurement, Project } from "./types";

const csvCell = (v: unknown): string => { const s = v == null ? "" : String(v); return /[",\n;]/.test(s) ? `"${s.replace(/"/g, '""')}"` : s; };

export function versionToCsv(v: EstimateVersion, lang: "fr" | "en"): string {
  const fr = lang === "fr";
  const head = fr ? ["division", "description", "assemblage", "mesure_id", "quantite_metre", "facteur", "quantite", "unite", "base", "unitaire_materiau", "unitaire_mo", "unitaire_equipement", "unitaire_autres", "unitaire_direct", "materiau", "mo", "equipement", "autres", "direct", "localise", "nature_prix", "source", "date_observation", "confiance", "part_observee", "exclue", "avertissements"]
    : ["division", "description", "assembly", "measurement_id", "takeoff_qty", "factor", "quantity", "unit", "basis", "unit_material", "unit_labour", "unit_equipment", "unit_other", "unit_direct", "material", "labour", "equipment", "other", "direct", "located", "price_kind", "source", "observed_at", "confidence", "observed_share", "excluded", "warnings"];
  const rows = v.result.lines.map((l) => [l.wbs, l.description, l.assemblyCode, l.measurementId, l.takeoffQuantity, l.adjustmentFactor, l.quantity, l.unit, l.basis, l.unitMaterial, l.unitLabour, l.unitEquipment, l.unitOther, l.unitDirect, l.material, l.labour, l.equipment, l.other, l.direct, l.located, l.provenance.kind, l.provenance.source, l.provenance.observedAt, l.provenance.confidence, l.provenance.observedShare, l.excluded ? 1 : 0, l.warnings.join(" | ")]);
  const T = v.result.totals;
  const meta = [["# UQO Éval — Métré", `${fr ? "révision" : "revision"} ${v.number} — ${v.label}`, v.createdAt, `sha256:${v.contentHash}`], ["# catalogue", v.result.catalogueVersion, "# formules", v.result.formulaVersion], ["# total", T.total, "# direct_localise", T.located, "# avant_taxes", T.subtotalBeforeTax]];
  return [...meta, head, ...rows].map((r) => r.map(csvCell).join(",")).join("\n") + "\n";
}

export async function versionToXlsx(p: Project, v: EstimateVersion, measurements: Measurement[], lang: "fr" | "en"): Promise<Buffer> {
  const fr = lang === "fr";
  const wb = new ExcelJS.Workbook();
  wb.creator = "UQO Éval — Métré"; wb.created = new Date();
  const r = v.result;
  const s1 = wb.addWorksheet(fr ? "Résumé" : "Summary");
  s1.columns = [{ width: 48 }, { width: 22 }];
  s1.addRows([[fr ? "Projet" : "Project", p.name], [fr ? "Adresse" : "Address", [p.address, p.municipality].filter(Boolean).join(", ")], [fr ? "Révision" : "Revision", `${v.number} — ${v.label}`], [fr ? "Figée le" : "Frozen on", v.createdAt], ["SHA-256", v.contentHash], [fr ? "Catalogue" : "Catalogue", r.catalogueVersion], [fr ? "Formules" : "Formulas", r.formulaVersion], [fr ? "Prix au" : "Prices as of", r.params.priceDate], [fr ? "Région" : "Region", `${r.location.code} (M ${r.location.materialFactor} · MO ${r.location.labourFactor} · É ${r.location.equipmentFactor})`], []]);
  const T = r.totals;
  const tot: [string, number][] = [[fr ? "Direct" : "Direct", T.direct], [fr ? "Ajustement régional" : "Regional adjustment", T.locationAdjustment], [fr ? "Direct localisé" : "Located direct", T.located], [`${fr ? "Conditions générales" : "General conditions"} ${r.params.generalConditionsPct} %`, T.generalConditions], [`${fr ? "Frais généraux" : "Overhead"} ${r.params.overheadPct} %`, T.overhead], [`Profit ${r.params.profitPct} %`, T.profit], [`${fr ? "Contingence" : "Contingency"} ${r.params.contingencyPct} %`, T.contingency], [fr ? "Actualisation" : "Escalation", T.escalation], [fr ? "Sous-total avant taxes" : "Subtotal before tax", T.subtotalBeforeTax], ["TPS/GST", T.gst], ["TVQ/QST", T.qst], ["TOTAL", T.total]];
  for (const [k, val] of tot) { const row = s1.addRow([k, val]); row.getCell(2).numFmt = '#,##0.00 "$"'; }
  s1.addRow([]); s1.addRow([fr ? "Ordre d'application" : "Application order"]); for (const o of r.applicationOrder) s1.addRow([o]);
  s1.getRow(1).font = { bold: true };

  const s2 = wb.addWorksheet(fr ? "Lignes" : "Lines");
  s2.columns = (fr ? ["Division", "Description", "Assemblage", "Qté métré", "Facteur", "Quantité", "Unité", "Base", "Unit. matériau", "Unit. MO", "Unit. équip.", "Unit. autres", "Unit. direct", "Matériau", "MO", "Équipement", "Autres", "Direct", "Localisé", "Nature", "Source", "Observé le", "Confiance", "Part observée", "Exclue", "Avertissements"]
    : ["Division", "Description", "Assembly", "Takeoff qty", "Factor", "Quantity", "Unit", "Basis", "Unit material", "Unit labour", "Unit equip.", "Unit other", "Unit direct", "Material", "Labour", "Equipment", "Other", "Direct", "Located", "Kind", "Source", "Observed", "Confidence", "Observed share", "Excluded", "Warnings"]).map((h, i) => ({ header: h, key: `c${i}`, width: i === 1 ? 44 : i === 20 ? 36 : 14 }));
  for (const l of r.lines) s2.addRow([l.wbs, l.description, l.assemblyCode, l.takeoffQuantity, l.adjustmentFactor, l.quantity, l.unit, l.basis, l.unitMaterial, l.unitLabour, l.unitEquipment, l.unitOther, l.unitDirect, l.material, l.labour, l.equipment, l.other, l.direct, l.located, l.provenance.kind, l.provenance.source, l.provenance.observedAt, l.provenance.confidence, l.provenance.observedShare, l.excluded ? 1 : 0, l.warnings.join(" | ")]);
  s2.getRow(1).font = { bold: true };
  for (const c of [9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19]) s2.getColumn(c).numFmt = '#,##0.00';

  const s3 = wb.addWorksheet(fr ? "Métré" : "Takeoff");
  s3.columns = (fr ? ["Id", "Mesure", "Formule", "Entrées", "Valeur", "Unité", "Cal. révision", "Périmée", "Confiance", "Vérifiée le", "Calculée le", "Lignage"] : ["Id", "Measurement", "Formula", "Inputs", "Value", "Unit", "Cal. revision", "Stale", "Confidence", "Verified at", "Computed at", "Lineage"]).map((h, i) => ({ header: h, key: `m${i}`, width: i === 3 ? 48 : 16 }));
  for (const m of measurements) s3.addRow([m.id, m.name, m.formulaText, JSON.stringify(m.inputs), m.value, m.unit, m.calibrationRevision, m.stale ? 1 : 0, m.confidence, m.verifiedAt, m.computedAt, m.lineage.join(" ; ")]);
  s3.getRow(1).font = { bold: true };

  const s4 = wb.addWorksheet(fr ? "Divisions" : "Divisions");
  s4.columns = [{ header: "WBS", key: "w", width: 30 }, { header: fr ? "Direct" : "Direct", key: "d", width: 16 }, { header: fr ? "Localisé" : "Located", key: "l", width: 16 }, { header: "%", key: "p", width: 8 }];
  for (const w of r.byWbs) s4.addRow([w.wbs, w.direct, w.located, w.sharePct]);
  s4.getRow(1).font = { bold: true };
  const out = await wb.xlsx.writeBuffer();
  return Buffer.from(out as ArrayBuffer);
}