/**
 * services/export-xlsx.js
 * Génération XLSX comprehensive — Situation des Marchés RLA Zone Sud
 */
const ExcelJS  = require('exceljs');
const { buildRef } = require('./calc');

const C = {
  NAVY:    'FF002D62',
  WHITE:   'FFFFFFFF',
  ACCENT:  'FF00D4FF',
  GREEN:   'FF16A34A',
  ORANGE:  'FFEA580C',
  RED:     'FFDC2626',
  YELLOW:  'FFEAB308',
  GRAY:    'FF64748B',
  ALT:     'FFF1F5F9',
  LIGHT:   'FFE2E8F0',
  CAPEX:   'FFD1FAE5',
  OPEX:    'FFFEF3C7',
  TOTAL:   'FFDBEAFE',
  HEADER:  'FF0F172A',
};

function fill(argb) { return { type: 'pattern', pattern: 'solid', fgColor: { argb } }; }
function font(argb, bold = false, size = 10) { return { color: { argb }, bold, size }; }
function border(color = C.LIGHT) {
  const s = { style: 'thin', color: { argb: color } };
  return { top: s, bottom: s, left: s, right: s };
}

const ALL_REGIONS = ['Gabes', 'Gafsa', 'Kebili', 'Medenine', 'Sfax', 'Tataouine', 'Tozeur'];

function parseNum(v) {
  if (v === null || v === undefined || v === '') return 0;
  if (typeof v === 'object') return 0;
  const n = parseFloat(String(v).replace(/\s/g, '').replace(',', '.'));
  return isNaN(n) ? 0 : n;
}

function selectVal(v) {
  if (!v) return '';
  if (typeof v === 'object' && v.value !== undefined) return String(v.value);
  if (Array.isArray(v)) return v.map(x => x.value !== undefined ? x.value : x).join(', ');
  return String(v);
}

function parseDateFR(d) {
  if (!d) return null;
  const parts = String(d).split(/[\/\-]/);
  if (parts.length === 3) {
    const [a, b, c] = parts;
    if (a.length === 4) return new Date(`${a}-${b}-${c}`);
    if (c.length === 4) return new Date(`${c}-${b}-${a}`);
  }
  const dt = new Date(d);
  return isNaN(dt.getTime()) ? null : dt;
}

function fmtDate(d) {
  const dt = parseDateFR(d);
  if (!dt) return '-';
  return dt.toLocaleDateString('fr-FR', { day: '2-digit', month: '2-digit', year: 'numeric' });
}

function fmtMDT(val) {
  const n = parseNum(val);
  if (n === 0) return '0';
  if (n >= 1000000) return `${(n / 1000000).toFixed(1)} MDT`;
  if (n >= 1000)    return `${(n / 1000).toFixed(0)} kDT`;
  return `${n.toFixed(0)} DT`;
}

function isCloture(r) {
  const obs = selectVal(r.observation).toLowerCase();
  return obs.includes('clôtur') || obs.includes('clotur') || !!r.date_cloture;
}

function getDelai(r) {
  const dField = r.delai_restant;
  if (dField !== null && dField !== undefined && dField !== '') {
    const v = parseInt(String(dField), 10);
    if (!isNaN(v)) return v;
  }
  const fin = r.date_fin || r.date_fin_marche || r.datefin;
  const dt  = parseDateFR(fin);
  if (!dt) return '-';
  return Math.ceil((dt - new Date()) / 86400000);
}

async function generateXlsx(view, data, allRows) {
  const wb = new ExcelJS.Workbook();
  wb.creator  = 'RLA API';
  wb.company  = 'Tunisie Telecom Zone Sud';
  wb.created  = new Date();

  const rows   = allRows || data.items || data.regions || [];
  const actifs = allRows ? rows.filter(r => !isCloture(r)) : rows;

  await buildSheet1(wb, actifs);
  await buildSheet2(wb, actifs);

  return wb.xlsx.writeBuffer();
}

async function buildSheet1(wb, actifs) {
  const ws = wb.addWorksheet('Situation des Marchés');
  ws.views = [{ state: 'frozen', ySplit: 9 }];

  // Column widths
  const cols = [
    { width: 40 }, { width: 20 }, { width: 22 }, { width: 12 },
    { width: 16 }, { width: 14 }, { width: 8  }, { width: 14 },
    { width: 8  }, { width: 14 }, { width: 14 }, { width: 8  }, { width: 22 },
  ];
  ws.columns = cols.map((c, i) => ({ key: String.fromCharCode(65 + i), width: c.width }));

  const today    = new Date().toLocaleDateString('fr-FR');
  const capex    = actifs.filter(r => String(selectVal(r.nature)).toUpperCase().includes('CAPEX') ||
                                     !String(selectVal(r.nature)).toUpperCase().includes('OPEX'));
  const opex     = actifs.filter(r => String(selectVal(r.nature)).toUpperCase().includes('OPEX'));
  const totalBudget = actifs.reduce((s, r) => s + parseNum(r.tot_marche || r.totmarche || r.montant), 0);

  const avgPhy = (() => {
    const vals = actifs.map(r => parseNum(r.taux_phy || r.avt_phy)).filter(v => v > 0);
    return vals.length ? vals.reduce((a, b) => a + b, 0) / vals.length : 0;
  })();

  // Row 1: Title
  const r1 = ws.addRow(['📊  SITUATION DES MARCHÉS RLA — ZONE SUD', ...Array(12).fill('')]);
  r1.height = 30;
  ws.mergeCells('A1:M1');
  const c1 = r1.getCell(1);
  c1.fill  = fill(C.NAVY);
  c1.font  = { color: { argb: C.WHITE }, bold: true, size: 16 };
  c1.alignment = { horizontal: 'center', vertical: 'middle' };

  // Row 2: Subtitle
  const r2 = ws.addRow([`Tunisie Telecom  •  Direction Centrale Achats  •  Zone Sud`, ...Array(12).fill('')]);
  r2.height = 18;
  ws.mergeCells('A2:M2');
  r2.getCell(1).fill = fill('FF0F172A');
  r2.getCell(1).font = font('FFCBD5E1', false, 11);
  r2.getCell(1).alignment = { horizontal: 'center', vertical: 'middle' };

  // Row 3: Stats bar
  const r3 = ws.addRow([
    `📅 ${today}   │   📋 ${actifs.length} marchés   │   💰 ${fmtMDT(totalBudget)}   │   📈 Phy moy: ${avgPhy.toFixed(0)}%`,
    ...Array(12).fill(''),
  ]);
  r3.height = 16;
  ws.mergeCells('A3:M3');
  r3.getCell(1).fill = fill('FF1E3A5F');
  r3.getCell(1).font = font('FF94A3B8', false, 10);
  r3.getCell(1).alignment = { horizontal: 'center', vertical: 'middle' };

  // Row 4: empty
  ws.addRow([]);

  // Row 5: KPI headers
  const r5 = ws.addRow(['📊  GLOBAL', '', '', '', '', '🟢  CAPEX', '', '', '', '', '🟠  OPEX', '', '']);
  r5.height = 22;
  ws.mergeCells('A5:E5'); ws.mergeCells('F5:J5'); ws.mergeCells('K5:M5');
  const kpiHdrStyle = (cell, argb) => {
    cell.fill = fill(argb);
    cell.font = { color: { argb: C.WHITE }, bold: true, size: 11 };
    cell.alignment = { horizontal: 'center', vertical: 'middle' };
  };
  kpiHdrStyle(r5.getCell(1), C.NAVY);
  kpiHdrStyle(r5.getCell(6), 'FF16A34A');
  kpiHdrStyle(r5.getCell(11), C.ORANGE);

  const capexBudget = capex.reduce((s, r) => s + parseNum(r.tot_marche || r.totmarche || r.montant), 0);
  const opexBudget  = opex.reduce((s, r) => s + parseNum(r.tot_marche || r.totmarche || r.montant), 0);
  const capexPhy    = (() => { const v = capex.map(r => parseNum(r.taux_phy || r.avt_phy)).filter(x => x > 0); return v.length ? v.reduce((a,b)=>a+b,0)/v.length : 0; })();
  const opexPhy     = (() => { const v = opex.map(r => parseNum(r.taux_phy || r.avt_phy)).filter(x => x > 0); return v.length ? v.reduce((a,b)=>a+b,0)/v.length : 0; })();

  // Row 6: KPI values
  const r6 = ws.addRow([`${actifs.length} marchés`, '', '', '', '',
    `${capex.length} marchés`, '', '', '', '', `${opex.length} marchés`, '', '']);
  r6.height = 18;
  ws.mergeCells('A6:E6'); ws.mergeCells('F6:J6'); ws.mergeCells('K6:M6');
  [1, 6, 11].forEach(col => {
    r6.getCell(col).font = { bold: true, size: 14, color: { argb: C.NAVY } };
    r6.getCell(col).alignment = { horizontal: 'center', vertical: 'middle' };
  });

  // Row 7: KPI details
  const r7 = ws.addRow([
    `Budget: ${fmtMDT(totalBudget)}  •  Phy moy: ${avgPhy.toFixed(0)}%`, '', '', '', '',
    `Budget: ${fmtMDT(capexBudget)}  •  Phy: ${capexPhy.toFixed(0)}%`, '', '', '', '',
    `Budget: ${fmtMDT(opexBudget)}  •  Phy: ${opexPhy.toFixed(0)}%`, '', '',
  ]);
  r7.height = 16;
  ws.mergeCells('A7:E7'); ws.mergeCells('F7:J7'); ws.mergeCells('K7:M7');
  [1, 6, 11].forEach(col => {
    r7.getCell(col).font = { size: 9, color: { argb: C.GRAY } };
    r7.getCell(col).alignment = { horizontal: 'center', vertical: 'middle' };
  });

  // Row 8: empty
  ws.addRow([]);

  // Row 9: Column headers
  const HEADERS = ['Référence', 'Projet', 'Entrepreneur', 'Nature',
    'Montant Marché', 'Av. Phy (DT)', 'Phy %', 'Av. Fin (DT)', 'Fin %',
    'Début', 'Fin', 'Délai', 'Observation'];
  const r9 = ws.addRow(HEADERS);
  r9.height = 22;
  r9.eachCell(cell => {
    cell.fill = fill(C.HEADER);
    cell.font = { color: { argb: C.WHITE }, bold: true, size: 10 };
    cell.alignment = { horizontal: 'center', vertical: 'middle', wrapText: true };
    cell.border = border(C.NAVY);
  });

  // Per-region data
  for (const region of ALL_REGIONS) {
    const regRows = actifs.filter(r => (r.region || '') === region);
    if (!regRows.length) continue;

    // Region header row
    const rh = ws.addRow([`📍  ${region}  —  ${regRows.length} marchés`, ...Array(12).fill('')]);
    rh.height = 18;
    ws.mergeCells(`A${rh.number}:M${rh.number}`);
    rh.getCell(1).fill = fill('FF1E3A5F');
    rh.getCell(1).font = { bold: true, size: 11, color: { argb: C.ACCENT } };
    rh.getCell(1).alignment = { horizontal: 'left', vertical: 'middle', indent: 1 };

    let subtotalBudget = 0, subtotalPhy = 0, subtotalFin = 0, phyCount = 0;

    for (let i = 0; i < regRows.length; i++) {
      const r    = regRows[i];
      const nat  = selectVal(r.nature);
      const isCapex = nat.toUpperCase().includes('CAPEX');
      const budget  = parseNum(r.tot_marche || r.totmarche || r.montant);
      const phyDT   = parseNum(r.avt_phy);
      const phyPct  = parseNum(r.taux_phy || r.avt_phy);
      const finDT   = parseNum(r.avt_fin);
      const finPct  = parseNum(r.taux_fin);
      const delai   = getDelai(r);

      subtotalBudget += budget;
      if (phyPct > 0) { subtotalPhy += phyPct; phyCount++; }
      subtotalFin += finDT;

      const rd = ws.addRow([
        buildRef(r),
        r.projet || '',
        r.entrepreneur || '',
        nat,
        budget || '',
        phyDT || '',
        phyPct > 0 ? phyPct / 100 : '',
        finDT || '',
        finPct > 0 ? finPct / 100 : '',
        fmtDate(r.date_debut || r.debut_marche),
        fmtDate(r.date_fin   || r.date_fin_marche),
        delai,
        selectVal(r.observation),
      ]);
      rd.height = 15;

      const altFill = i % 2 === 1 ? fill(C.ALT) : undefined;
      const natFill = isCapex ? fill(C.CAPEX) : fill(C.OPEX);

      rd.eachCell((cell, col) => {
        if (altFill) cell.fill = altFill;
        if (col === 4) cell.fill = natFill;
        cell.border = { bottom: { style: 'thin', color: { argb: C.LIGHT } } };
        cell.alignment = { vertical: 'middle' };
        if ([5, 6, 8].includes(col)) cell.numFmt = '#,##0';
        if ([7, 9].includes(col)) cell.numFmt = '0%';
        if ([12].includes(col)) cell.alignment = { horizontal: 'center', vertical: 'middle' };
      });
    }

    // Subtotal row
    const avgPct = phyCount > 0 ? subtotalPhy / phyCount : 0;
    const rst = ws.addRow([
      `Sous-total ${region}  (${regRows.length})`, '', '',
      '', subtotalBudget, '', avgPct / 100, '', '',
      '', '', '', '',
    ]);
    rst.height = 16;
    ws.mergeCells(`A${rst.number}:D${rst.number}`);
    rst.eachCell(cell => {
      cell.fill = fill('FF1E3A5F');
      cell.font = { bold: true, size: 9, color: { argb: C.WHITE } };
      cell.border = { top: { style: 'medium', color: { argb: C.NAVY } }, bottom: { style: 'medium', color: { argb: C.NAVY } } };
    });
    rst.getCell(5).numFmt = '#,##0';
    rst.getCell(7).numFmt = '0%';
    rst.getCell(1).alignment = { horizontal: 'right', vertical: 'middle' };

    ws.addRow([]); // spacer
  }

  // Grand total row
  const gt = ws.addRow([
    `TOTAL ZONE SUD  (${actifs.length} marchés)`, '', '', '',
    actifs.reduce((s, r) => s + parseNum(r.tot_marche || r.totmarche || r.montant), 0),
    '', avgPhy / 100, '', '', '', '', '', '',
  ]);
  ws.mergeCells(`A${gt.number}:D${gt.number}`);
  gt.height = 22;
  gt.eachCell(cell => {
    cell.fill = fill(C.NAVY);
    cell.font = { bold: true, size: 11, color: { argb: C.WHITE } };
    cell.border = { top: { style: 'medium', color: { argb: C.ACCENT } } };
  });
  gt.getCell(5).numFmt  = '#,##0';
  gt.getCell(7).numFmt  = '0%';
  gt.getCell(1).alignment = { horizontal: 'right', vertical: 'middle' };
}

async function buildSheet2(wb, actifs) {
  const ws = wb.addWorksheet('Estimation Évolution');
  ws.views = [{ state: 'frozen', ySplit: 6 }];

  const SEUIL_STD = parseFloat(process.env.SEUIL_STANDARD || 70);

  function projection(r) {
    const marche   = parseNum(r.tot_marche || r.totmarche || r.montant);
    const minDT    = marche * (SEUIL_STD / 100);
    const consomme = parseNum(r.avt_fin);
    const debut    = parseDateFR(r.date_debut || r.debut_marche);
    const fin      = parseDateFR(r.date_fin   || r.date_fin_marche);
    const now      = new Date();
    const elapsed  = debut ? Math.max(0, (now.getFullYear()-debut.getFullYear())*12+(now.getMonth()-debut.getMonth())) : 0;
    const total    = (debut && fin) ? Math.max(1, (fin.getFullYear()-debut.getFullYear())*12+(fin.getMonth()-debut.getMonth())) : 0;
    const parMois  = elapsed > 0 ? consomme / elapsed : 0;
    const projete  = parMois * (total || elapsed);
    let verdict;
    if (projete > marche * 1.03)  verdict = 'Avenant';
    else if (projete >= minDT)    verdict = 'Normal';
    else                          verdict = 'Sous Min';
    return { marche, minDT, consomme, parMois, projete, verdict };
  }

  ws.columns = [
    { width: 42 }, { width: 22 }, { width: 22 }, { width: 16 },
    { width: 14 }, { width: 14 }, { width: 14 }, { width: 14 }, { width: 18 },
  ];

  const today = new Date().toLocaleDateString('fr-FR');
  const COLS = 9;

  // Row 1: Title
  const r1 = ws.addRow(['ESTIMATION ÉVOLUTION — ZONE SUD', ...Array(COLS - 1).fill('')]);
  r1.height = 30;
  ws.mergeCells(`A1:I1`);
  r1.getCell(1).fill = fill('FF6366F1');
  r1.getCell(1).font = { color: { argb: C.WHITE }, bold: true, size: 16 };
  r1.getCell(1).alignment = { horizontal: 'center', vertical: 'middle' };

  const r2 = ws.addRow([`Tunisie Telecom  •  Direction Centrale Achats  •  Zone Sud`, ...Array(COLS - 1).fill('')]);
  r2.height = 18;
  ws.mergeCells('A2:I2');
  r2.getCell(1).fill = fill('FF4F46E5');
  r2.getCell(1).font = font(C.WHITE, false, 11);
  r2.getCell(1).alignment = { horizontal: 'center', vertical: 'middle' };

  const r3 = ws.addRow([`📅 ${today}   │   📋 ${actifs.length} marchés`, ...Array(COLS - 1).fill('')]);
  r3.height = 16;
  ws.mergeCells('A3:I3');
  r3.getCell(1).fill = fill('FF4338CA');
  r3.getCell(1).font = font('FFFDE8EC', false, 10);
  r3.getCell(1).alignment = { horizontal: 'center', vertical: 'middle' };

  ws.addRow([]);

  // Column headers
  const HEADERS2 = ['Référence','Projet','Entrepreneur','Marché DT','Min DT','Consommé DT','DT/Mois','Projeté DT','Résultat'];
  const r5 = ws.addRow(HEADERS2);
  r5.height = 22;
  r5.eachCell(cell => {
    cell.fill = fill('FF6366F1');
    cell.font = { color: { argb: C.WHITE }, bold: true, size: 10 };
    cell.alignment = { horizontal: 'center', vertical: 'middle', wrapText: true };
    cell.border = border(C.NAVY);
  });

  const VERDICT_COLOR = { Normal: C.GREEN, 'Sous Min': 'FFDC2626', Avenant: 'FFEA580C' };

  for (let i = 0; i < actifs.length; i++) {
    const r = actifs[i];
    const p = projection(r);

    const rd = ws.addRow([
      buildRef(r),
      r.projet || '',
      r.entrepreneur || '',
      p.marche || '',
      p.minDT  || '',
      p.consomme || '',
      p.parMois || '',
      p.projete || '',
      p.verdict,
    ]);
    rd.height = 15;
    if (i % 2 === 1) rd.eachCell(cell => { cell.fill = fill(C.ALT); });

    [4, 5, 6, 7, 8].forEach(col => {
      rd.getCell(col).numFmt = '#,##0';
      rd.getCell(col).alignment = { horizontal: 'right', vertical: 'middle' };
    });
    rd.getCell(9).font = { color: { argb: VERDICT_COLOR[p.verdict] || C.GRAY }, bold: true };
    rd.eachCell(cell => {
      cell.border = { bottom: { style: 'thin', color: { argb: C.LIGHT } } };
      if (!cell.alignment) cell.alignment = { vertical: 'middle' };
    });
  }

  // Synthèse en bas
  ws.addRow([]);
  const nNormal = actifs.filter(r => projection(r).verdict === 'Normal').length;
  const nSous   = actifs.filter(r => projection(r).verdict === 'Sous Min').length;
  const nAv     = actifs.filter(r => projection(r).verdict === 'Avenant').length;
  const rSum = ws.addRow([`✅ Normal: ${nNormal}`, '', '', `❌ Sous Min: ${nSous}`, '', '', `⚠️ Avenant: ${nAv}`, '', '']);
  ws.mergeCells(`A${rSum.number}:C${rSum.number}`);
  ws.mergeCells(`D${rSum.number}:F${rSum.number}`);
  ws.mergeCells(`G${rSum.number}:I${rSum.number}`);
  rSum.height = 20;
  rSum.getCell(1).font = { color: { argb: C.GREEN },     bold: true, size: 10 };
  rSum.getCell(4).font = { color: { argb: 'FFDC2626' },  bold: true, size: 10 };
  rSum.getCell(7).font = { color: { argb: C.ORANGE },    bold: true, size: 10 };
  [1, 4, 7].forEach(col => {
    rSum.getCell(col).alignment = { horizontal: 'center', vertical: 'middle' };
    rSum.getCell(col).fill = fill(C.ALT);
  });
}

module.exports = { generateXlsx };