/**
 * services/export-docx.js
 * DOCX conforme au fichier cible Rapport_RLA_2025_Zone_Sud.docx
 * Structure : Couverture, TOC, Sommaire Exécutif, Synthèse, Alertes,
 *             Modernisation, Synthèse/Région, Estimation, Matrice, Recommandations
 */
const {
  Document, Packer, Paragraph, Table, TableRow, TableCell,
  TextRun, HeadingLevel, AlignmentType, WidthType, PageBreak,
  TableOfContents, ShadingType, BorderStyle, convertInchesToTwip,
} = require('docx');
const {
  parseNum, selectVal, parseDateFR, formatDateFR, formatMontant, buildRef,
  getDelaiRestant, niveauAlerte, DELAI_CRITIQUE, DELAI_ATTENTION,
  SEUIL_STANDARD, SEUIL_MODERNISATION, SEUIL_CRITIQUE_PCT,
} = require('./calc');

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

const REGION_HEX = {
  Gabes:'0891B2', Gafsa:'059669', Kebili:'7C3AED',
  Medenine:'2563EB', Sfax:'0B2A55', Tataouine:'0D9488', Tozeur:'6366F1',
};

// ── Helpers visuels ─────────────────────────────────────────────────────────────

function fmtPct(v) {
  const n = parseNum(v);
  return n === 0 ? '0 %' : `${n.toFixed(0)} %`;
}

function fmtMDT(v) {
  const n = parseNum(v);
  if (!n) return '—';
  if (n >= 1e6) return `${(n / 1e6).toFixed(3)} MDT`;
  if (n >= 1e3) return `${(n / 1e3).toFixed(0)} kDT`;
  return `${n.toFixed(0)} DT`;
}

function periode(r) {
  const d = formatDateFR(r.date_debut || r.debut_marche);
  const f = formatDateFR(r.date_fin   || r.date_fin_marche);
  if (d === '—' && f === '—') return '—';
  return `${d} → ${f}`;
}

function elapsedMonths(dateDebut) {
  const d = parseDateFR(dateDebut);
  if (!d) return 0;
  const now = new Date();
  return Math.max(0, (now.getFullYear() - d.getFullYear()) * 12 + (now.getMonth() - d.getMonth()));
}

function totalMonths(dateDebut, dateFin) {
  const d = parseDateFR(dateDebut);
  const f = parseDateFR(dateFin);
  if (!d || !f) return 0;
  return Math.max(1, (f.getFullYear() - d.getFullYear()) * 12 + (f.getMonth() - d.getMonth()));
}

function projection(r) {
  const marche   = parseNum(r.tot_marche || r.totmarche || r.montant);
  const minDT    = marche * (SEUIL_STANDARD / 100);
  const consomme = parseNum(r.avt_fin);
  const elapsed  = elapsedMonths(r.date_debut || r.debut_marche);
  const total    = totalMonths(r.date_debut || r.debut_marche, r.date_fin || r.date_fin_marche);
  const parMois  = elapsed > 0 ? consomme / elapsed : 0;
  const projete  = parMois * total;
  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 };
}

// ── Constructeurs DOCX ──────────────────────────────────────────────────────────

const NAVY = '0B2A55';
const ALT  = 'F1F5F9';
const WH   = 'FFFFFF';

function cellShade(hex) {
  return { fill: hex, type: ShadingType.SOLID, color: hex };
}

function hdrCell(text, width, hexBg = NAVY) {
  return new TableCell({
    children: [new Paragraph({
      children: [new TextRun({ text: String(text), bold: true, color: WH, size: 18 })],
      alignment: AlignmentType.CENTER,
    })],
    width: { size: width, type: WidthType.DXA },
    shading: cellShade(hexBg),
    margins: { top: 60, bottom: 60, left: 80, right: 80 },
  });
}

function dataCell(text, width, shade, align = AlignmentType.LEFT, bold = false, color = '1E293B') {
  return new TableCell({
    children: [new Paragraph({
      children: [new TextRun({ text: String(text ?? '—'), size: 17, color, bold })],
      alignment: align,
    })],
    width: { size: width, type: WidthType.DXA },
    shading: shade ? cellShade(shade) : undefined,
    margins: { top: 50, bottom: 50, left: 80, right: 80 },
  });
}

function hdrRow(cols, hexBg = NAVY) {
  return new TableRow({
    tableHeader: true,
    children: cols.map(([text, w]) => hdrCell(text, w, hexBg)),
  });
}

function dataRow(cells, isAlt = false) {
  const shade = isAlt ? ALT : undefined;
  return new TableRow({
    children: cells.map(([text, w, align, bold, color]) =>
      dataCell(text, w, shade, align || AlignmentType.LEFT, bold || false, color || '1E293B')),
  });
}

function bannerRow(text, hexBg) {
  return new Table({
    width: { size: 100, type: WidthType.PERCENTAGE },
    rows: [
      new TableRow({
        children: [
          new TableCell({
            children: [new Paragraph({
              children: [new TextRun({ text, bold: true, color: WH, size: 22 })],
              alignment: AlignmentType.CENTER,
            })],
            shading: cellShade(hexBg),
            columnSpan: 1,
            margins: { top: 100, bottom: 100 },
          }),
        ],
      }),
    ],
    borders: { top: { style: BorderStyle.NONE }, bottom: { style: BorderStyle.NONE }, left: { style: BorderStyle.NONE }, right: { style: BorderStyle.NONE } },
  });
}

function h1(text) {
  return new Paragraph({
    heading: HeadingLevel.HEADING_1,
    children: [new TextRun({ text, bold: true, color: NAVY, size: 36 })],
    spacing: { before: 400, after: 160 },
    border: { bottom: { style: BorderStyle.SINGLE, size: 6, color: '0680C3' } },
  });
}

function h2(text, hexColor = NAVY) {
  return new Paragraph({
    heading: HeadingLevel.HEADING_2,
    children: [new TextRun({ text, bold: true, color: hexColor, size: 26 })],
    spacing: { before: 280, after: 100 },
  });
}

function para(text, size = 20, color = '374151') {
  return new Paragraph({
    children: [new TextRun({ text, size, color })],
    spacing: { after: 120 },
  });
}

function spacer() {
  return new Paragraph({ text: '', spacing: { before: 80, after: 80 } });
}

// ── Export principal ────────────────────────────────────────────────────────────

async function generateDocx({ actifs, clotures, filtered, pipelineRows }) {
  const today      = new Date().toLocaleDateString('fr-FR');
  const totalBudget = actifs.reduce((s, r) => s + parseNum(r.tot_marche || r.totmarche || r.montant), 0);
  const phyList    = actifs.map(r => parseNum(r.taux_phy || r.avt_phy)).filter(v => v > 0);
  const avgPhy     = phyList.length ? phyList.reduce((a, b) => a + b, 0) / phyList.length : 0;
  const finList    = actifs.map(r => parseNum(r.taux_fin)).filter(v => v > 0);
  const avgFin     = finList.length ? finList.reduce((a, b) => a + b, 0) / finList.length : 0;

  const capexRows  = actifs.filter(r => String(selectVal(r.nature)).toUpperCase().includes('CAPEX'));
  const opexRows   = actifs.filter(r => String(selectVal(r.nature)).toUpperCase().includes('OPEX'));

  const alertes = actifs
    .map(r => ({ ...r, _d: getDelaiRestant(r) }))
    .filter(r => r._d !== null && r._d <= DELAI_ATTENTION)
    .sort((a, b) => a._d - b._d);

  const modActifs = actifs.filter(r =>
    String(selectVal(r.nature)).toLowerCase().includes('moderni'));

  const pipeline = Array.isArray(pipelineRows) ? pipelineRows : [];

  const children = [];

  // ── Couverture ────────────────────────────────────────────────────────────────
  children.push(new Table({
    width: { size: 100, type: WidthType.PERCENTAGE },
    rows: [
      new TableRow({
        children: [
          new TableCell({
            children: [
              new Paragraph({ children: [new TextRun({ text: 'TUNISIE TELECOM', bold: true, color: WH, size: 28 })], alignment: AlignmentType.CENTER }),
              new Paragraph({ children: [new TextRun({ text: 'Direction Centrale', color: 'CBD5E1', size: 20 })], alignment: AlignmentType.CENTER }),
              new Paragraph({ children: [new TextRun({ text: 'Zone Sud', color: 'CBD5E1', size: 20 })], alignment: AlignmentType.CENTER }),
            ],
            shading: cellShade(NAVY),
            margins: { top: 200, bottom: 200, left: 300, right: 300 },
          }),
        ],
      }),
    ],
    borders: { top: { style: BorderStyle.NONE }, bottom: { style: BorderStyle.NONE }, left: { style: BorderStyle.NONE }, right: { style: BorderStyle.NONE } },
  }));
  children.push(spacer());
  children.push(new Paragraph({
    children: [new TextRun({ text: 'Rapport de Suivi des Marchés RLA', bold: true, color: NAVY, size: 52 })],
    alignment: AlignmentType.CENTER,
    spacing: { before: 200, after: 100 },
  }));
  children.push(new Paragraph({
    children: [new TextRun({ text: 'Situation Actuelle & Analyse Prospective', color: '1E40AF', size: 32 })],
    alignment: AlignmentType.CENTER,
    spacing: { after: 200 },
  }));
  children.push(bannerRow('', '1E40AF'));
  children.push(spacer());
  children.push(new Paragraph({
    children: [
      new TextRun({ text: `📅  ${today}`, size: 24, color: NAVY }),
      new TextRun({ text: `      📊  Données au ${today}`, size: 24, bold: true, color: '475569' }),
    ],
    alignment: AlignmentType.CENTER,
    spacing: { after: 80 },
  }));
  children.push(new Paragraph({
    children: [
      new TextRun({ text: `📋  ${actifs.length} marchés`, size: 24, color: NAVY }),
      new TextRun({ text: `      💰  Budget total : ${fmtMDT(totalBudget)}`, size: 24, bold: true, color: '475569' }),
    ],
    alignment: AlignmentType.CENTER,
    spacing: { after: 200 },
  }));
  children.push(new Paragraph({ children: [new TextRun({ text: 'Nabil Derouiche', bold: true, color: NAVY, size: 28 })], alignment: AlignmentType.CENTER }));
  children.push(new Paragraph({ children: [new TextRun({ text: 'Responsable Achats Zone Sud', color: '475569', size: 20 })], alignment: AlignmentType.CENTER }));
  children.push(new Paragraph({ children: [new TextRun({ text: 'Tunisie Telecom', color: '94A3B8', size: 20 })], alignment: AlignmentType.CENTER, spacing: { after: 300 } }));
  children.push(new Paragraph({ children: [new PageBreak()] }));

  // ── Table des Matières ────────────────────────────────────────────────────────
  children.push(new Paragraph({
    children: [new TextRun({ text: 'Table des Matières', bold: true, color: NAVY, size: 44 })],
    spacing: { before: 100, after: 100 },
  }));
  children.push(new Paragraph({
    children: [new TextRun({ text: 'ℹ️  Mettre à jour : clic droit → Mettre à jour les champs', size: 18, color: '94A3B8' })],
    spacing: { after: 200 },
  }));
  children.push(new TableOfContents('Table des Matières', {
    hyperlink: true,
    headingStyleRange: '1-3',
  }));
  children.push(new Paragraph({ children: [new PageBreak()] }));

  // ── Sommaire Exécutif ─────────────────────────────────────────────────────────
  children.push(h1('Sommaire Exécutif'));
  children.push(bannerRow('', '1E40AF'));
  children.push(spacer());
  const capexBudget = capexRows.reduce((s, r) => s + parseNum(r.tot_marche || r.totmarche || r.montant), 0);
  const opexBudget  = opexRows.reduce((s, r) => s + parseNum(r.tot_marche || r.totmarche || r.montant), 0);

  children.push(para(
    `La Zone Sud gère actuellement ${actifs.length} marchés RLA pour un budget global de ${totalBudget.toLocaleString('fr-FR', { minimumFractionDigits: 3 })} DT, ` +
    `répartis en ${capexRows.length} marchés CAPEX (${fmtMDT(capexBudget)}) et ${opexRows.length} marchés OPEX (${fmtMDT(opexBudget)}).`
  ));
  children.push(para(
    `Sur le plan des alertes, ${alertes.filter(r => r._d <= DELAI_CRITIQUE).length} marché(s) sont en situation critique (délai ≤ ${DELAI_CRITIQUE} jours) ` +
    `et ${alertes.length - alertes.filter(r => r._d <= DELAI_CRITIQUE).length} marché(s) nécessitent une attention renforcée.`
  ));
  const regsSansModerni = ALL_REGIONS.filter(reg => !modActifs.some(r => (r.region || '') === reg));
  if (regsSansModerni.length) {
    children.push(para(
      `Concernant la Modernisation, ${regsSansModerni.length} région(s) ne disposent d'aucun marché actif (${regsSansModerni.join(', ')}). ` +
      `Conformément à la politique TT, un lancement immédiat est requis pour ces régions.`
    ));
  }
  const projs = actifs.map(r => projection(r));
  children.push(para(
    `L'analyse prospective révèle ${projs.filter(p => p.verdict === 'Sous Min').length} marché(s) projetés sous le Montant Minimum ` +
    `et ${projs.filter(p => p.verdict === 'Avenant').length} en situation de dépassement nécessitant un avenant.`
  ));

  // ── Synthèse des Indicateurs ──────────────────────────────────────────────────
  children.push(h1('Synthèse des Indicateurs'));

  const capexPhy = (() => { const v = capexRows.map(r => parseNum(r.taux_phy || r.avt_phy)).filter(x => x > 0); return v.length ? v.reduce((a, b) => a + b) / v.length : 0; })();
  const opexPhy  = (() => { const v = opexRows.map(r => parseNum(r.taux_phy || r.avt_phy)).filter(x => x > 0); return v.length ? v.reduce((a, b) => a + b) / v.length : 0; })();

  children.push(new Table({
    width: { size: 100, type: WidthType.PERCENTAGE },
    rows: [
      hdrRow([['Indicateur',3000],['GLOBAL',1800],['CAPEX',1800],['OPEX',1800]], NAVY),
      new TableRow({ children: [hdrCell('Indicateur',3000,NAVY), hdrCell('GLOBAL',1800,'0B2A55'), hdrCell('CAPEX',1800,'059669'), hdrCell('OPEX',1800,'D97706')] }),
      dataRow([['Marchés actifs',3000],     [actifs.length,1800,AlignmentType.CENTER], [capexRows.length,1800,AlignmentType.CENTER], [opexRows.length,1800,AlignmentType.CENTER]]),
      dataRow([['Budget total',3000],       [fmtMDT(totalBudget),1800,AlignmentType.CENTER], [fmtMDT(capexBudget),1800,AlignmentType.CENTER], [fmtMDT(opexBudget),1800,AlignmentType.CENTER]], true),
      dataRow([['Avancement physique',3000],[fmtPct(avgPhy),1800,AlignmentType.CENTER], [fmtPct(capexPhy),1800,AlignmentType.CENTER], [fmtPct(opexPhy),1800,AlignmentType.CENTER]]),
      dataRow([['Avancement financier',3000],[fmtPct(avgFin),1800,AlignmentType.CENTER], ['—',1800,AlignmentType.CENTER], ['—',1800,AlignmentType.CENTER]], true),
      dataRow([['Alertes (total)',3000],    [alertes.length,1800,AlignmentType.CENTER], ['—',1800,AlignmentType.CENTER], ['—',1800,AlignmentType.CENTER]]),
      dataRow([['Critiques (≤45j)',3000],   [alertes.filter(a=>a._d<=DELAI_CRITIQUE).length,1800,AlignmentType.CENTER,true,'DC2626'], ['—',1800,AlignmentType.CENTER], ['—',1800,AlignmentType.CENTER]], true),
    ],
  }));
  children.push(spacer());
  children.push(new Paragraph({ children: [new PageBreak()] }));

  // ── Alertes ───────────────────────────────────────────────────────────────────
  children.push(h1('Alertes'));
  children.push(para(`${alertes.length} marché(s) nécessitent une attention immédiate : ${alertes.filter(a=>a._d<=DELAI_CRITIQUE).length} critiques et ${alertes.filter(a=>a._d>DELAI_CRITIQUE).length} en surveillance renforcée.`, 20, '475569'));

  if (alertes.length) {
    children.push(new Table({
      width: { size: 100, type: WidthType.PERCENTAGE },
      rows: [
        hdrRow([['Référence',2800],['Projet',2400],['Entrepreneur',2000],['Région',1200],['Av. Phy',900],['Délai',800],['Niveau',1300]]),
        ...alertes.map((r, i) => {
          const al  = niveauAlerte(r._d);
          const alLabel = { critique: 'CRITIQUE', attention: 'ATTENTION' }[al] || String(al || '').toUpperCase();
          const alColor = al === 'critique' ? 'DC2626' : al === 'attention' ? 'EA580C' : '059669';
          return dataRow([
            [buildRef(r),                     2800],
            [r.projet || '',                  2400],
            [r.entrepreneur || '',            2000],
            [r.region || '',                  1200, AlignmentType.CENTER],
            [fmtPct(r.taux_phy || r.avt_phy), 900,  AlignmentType.CENTER],
            [r._d !== null ? r._d + ' j' : '—', 800, AlignmentType.CENTER, true, alColor],
            [alLabel,                         1300, AlignmentType.CENTER, true, alColor],
          ], i % 2 === 1);
        }),
      ],
    }));
  }
  children.push(new Paragraph({ children: [new PageBreak()] }));

  // ── Modernisation ─────────────────────────────────────────────────────────────
  children.push(h1('Modernisation'));
  children.push(para(`Seuil : avancement physique ≥ ${SEUIL_MODERNISATION}% — la région devra procéder au lancement d'un nouveau marché.`, 20, '475569'));

  const modEnSeuil = modActifs.filter(r => parseNum(r.taux_phy || r.avt_phy) >= SEUIL_MODERNISATION);
  children.push(h2(`⚡ Marchés Modernisation ≥ ${SEUIL_MODERNISATION}%`, 'D97706'));
  if (modEnSeuil.length) {
    children.push(new Table({
      width: { size: 100, type: WidthType.PERCENTAGE },
      rows: [
        hdrRow([['Référence',2800],['Entrepreneur',2200],['Région',1400],['Av. Phy',900],['Délai',800],['Action',3100]], 'D97706'),
        ...modEnSeuil.map((r, i) => {
          const phy = parseNum(r.taux_phy || r.avt_phy);
          const d   = getDelaiRestant(r);
          return dataRow([
            [buildRef(r),    2800],
            [r.entrepreneur||'', 2200],
            [r.region||'',  1400, AlignmentType.CENTER],
            [fmtPct(phy),   900,  AlignmentType.CENTER, true, phy>=SEUIL_CRITIQUE_PCT?'DC2626':'D97706'],
            [d!==null?d+' j':'—', 800, AlignmentType.CENTER],
            ['Lancer nouveau marché', 3100, AlignmentType.LEFT, true, 'D97706'],
          ], i % 2 === 1);
        }),
      ],
    }));
  } else {
    children.push(para('Aucun marché modernisation au seuil de déclenchement.', 20, '475569'));
  }
  children.push(spacer());

  children.push(h2('🔴 Régions sans marché Modernisation actif', 'DC2626'));
  if (regsSansModerni.length) {
    for (const reg of regsSansModerni) {
      children.push(new Paragraph({
        children: [new TextRun({ text: reg, bold: true, color: 'DC2626', size: 24 })],
        spacing: { after: 60 },
      }));
    }
    children.push(para('→ Lancement immédiat d\'un nouveau marché Modernisation requis pour chacune de ces régions.', 20, 'DC2626'));
  } else {
    children.push(para('Toutes les régions disposent d\'un marché Modernisation actif.', 20, '059669'));
  }
  children.push(new Paragraph({ children: [new PageBreak()] }));

  // ── Synthèse par Région ───────────────────────────────────────────────────────
  children.push(h1('Synthèse par Région'));
  children.push(para('Vue consolidée des 7 régions de la Zone Sud.', 20, '475569'));

  children.push(new Table({
    width: { size: 100, type: WidthType.PERCENTAGE },
    rows: [
      hdrRow([['Région',1800],['Marchés',1000],['Budget',2000],['Av. Physique',1500],['Av. Financier',1500],['Risque',1600]]),
      ...ALL_REGIONS.map((reg, i) => {
        const ra  = actifs.filter(r => (r.region || '') === reg);
        const bud = ra.reduce((s, r) => s + parseNum(r.tot_marche || r.totmarche || r.montant), 0);
        const pl  = ra.map(r => parseNum(r.taux_phy || r.avt_phy)).filter(v => v > 0);
        const pm  = pl.length ? pl.reduce((a, b) => a + b) / pl.length : 0;
        const fl  = ra.map(r => parseNum(r.taux_fin)).filter(v => v > 0);
        const fm  = fl.length ? fl.reduce((a, b) => a + b) / fl.length : 0;
        const al  = ra.filter(r => { const d = getDelaiRestant(r); return d !== null && d <= DELAI_CRITIQUE; });
        const risque = al.length > 0 ? 'CRITIQUE' : pm < SEUIL_STANDARD ? 'ATTENTION' : 'NORMAL';
        const rColor = risque === 'CRITIQUE' ? 'DC2626' : risque === 'ATTENTION' ? 'D97706' : '059669';
        return dataRow([
          [reg,          1800, AlignmentType.LEFT,   true, REGION_HEX[reg]||NAVY],
          [ra.length,    1000, AlignmentType.CENTER],
          [fmtMDT(bud),  2000, AlignmentType.CENTER],
          [fmtPct(pm),   1500, AlignmentType.CENTER, true, pm >= SEUIL_STANDARD ? '059669' : 'DC2626'],
          [fmtPct(fm),   1500, AlignmentType.CENTER],
          [risque,       1600, AlignmentType.CENTER, true, rColor],
        ], i % 2 === 1);
      }),
    ],
  }));
  children.push(spacer());

  for (const region of ALL_REGIONS) {
    const regActifs = actifs.filter(r => (r.region || '') === region);
    if (!regActifs.length) continue;

    const hexReg = REGION_HEX[region] || NAVY;
    const bud = regActifs.reduce((s, r) => s + parseNum(r.tot_marche || r.totmarche || r.montant), 0);
    const pl  = regActifs.map(r => parseNum(r.taux_phy || r.avt_phy)).filter(v => v > 0);
    const pm  = pl.length ? pl.reduce((a, b) => a + b) / pl.length : 0;
    const fl  = regActifs.map(r => parseNum(r.taux_fin)).filter(v => v > 0);
    const fm  = fl.length ? fl.reduce((a, b) => a + b) / fl.length : 0;

    children.push(h1(`📍 ${region}`));
    children.push(bannerRow(`${regActifs.length} marchés   •   Phy ${pm.toFixed(0)}%   •   Fin ${fm.toFixed(0)}%`, hexReg));

    children.push(para(
      `La région ${region} compte ${regActifs.length} marché(s) pour un budget total de ${bud.toLocaleString('fr-FR', { minimumFractionDigits: 3 })} DT. ` +
      `L'avancement physique moyen est de ${pm.toFixed(0)}% et l'avancement financier de ${fm.toFixed(0)}%.`
    ));

    const enService = regActifs.filter(r =>
      String(selectVal(r.observation)).toLowerCase().includes('en service'));
    const enCours   = regActifs.filter(r =>
      !String(selectVal(r.observation)).toLowerCase().includes('en service'));

    if (enService.length) {
      children.push(h2('✅ Marchés en service', '059669'));
      children.push(new Table({
        width: { size: 100, type: WidthType.PERCENTAGE },
        rows: [
          hdrRow([['Référence',2600],['Projet',2200],['Entrepreneur',1800],['Période',1800],['Montant',1200],['Phy',700],['Fin',700],['Délai',700]], hexReg),
          ...enService.map((r, i) => {
            const phy = parseNum(r.taux_phy || r.avt_phy);
            const fin = parseNum(r.taux_fin);
            const d   = getDelaiRestant(r);
            return dataRow([
              [buildRef(r),          2600],
              [r.projet||'',         2200],
              [r.entrepreneur||'',   1800],
              [periode(r),           1800],
              [fmtMDT(r.tot_marche||r.montant), 1200, AlignmentType.CENTER],
              [fmtPct(phy),          700,  AlignmentType.CENTER],
              [fmtPct(fin),          700,  AlignmentType.CENTER],
              [d!==null?d+' j':'—',  700,  AlignmentType.CENTER],
            ], i % 2 === 1);
          }),
        ],
      }));
      children.push(spacer());
    }

    if (enCours.length) {
      children.push(h2('⏳ Marchés en cours'));
      children.push(new Table({
        width: { size: 100, type: WidthType.PERCENTAGE },
        rows: [
          hdrRow([['Référence',3000],['Projet',2500],['Entrepreneur',2000],['Observation',3900]], '1E40AF'),
          ...enCours.map((r, i) => dataRow([
            [buildRef(r),                   3000],
            [r.projet||'',                  2500],
            [r.entrepreneur||'',            2000],
            [selectVal(r.observation)||'—', 3900],
          ], i % 2 === 1)),
        ],
      }));
      children.push(spacer());
    }
  }
  children.push(new Paragraph({ children: [new PageBreak()] }));

  // ── Estimation & Projection ───────────────────────────────────────────────────
  children.push(h1('Estimation & Projection'));
  children.push(para('Projection fin PO basée sur le taux de consommation mensuel. Modernisation exemptée d\'avenant.', 20, '475569'));

  const projRows = actifs.map(r => ({ r, p: projection(r) }));
  const verdictColors = { Normal: '059669', 'Sous Min': 'DC2626', Avenant: 'D97706' };

  children.push(new Table({
    width: { size: 100, type: WidthType.PERCENTAGE },
    rows: [
      hdrRow([['Référence',2600],['Projet',2000],['Marché DT',1200],['Min DT',1100],['Consommé',1100],['DT/Mois',1100],['Projeté',1100],['Verdict',1100]], '6366F1'),
      ...projRows.map(({ r, p }, i) => dataRow([
        [buildRef(r),            2600],
        [r.projet||'',           2000],
        [fmtMDT(p.marche),       1200, AlignmentType.CENTER],
        [fmtMDT(p.minDT),        1100, AlignmentType.CENTER],
        [fmtMDT(p.consomme),     1100, AlignmentType.CENTER],
        [fmtMDT(p.parMois),      1100, AlignmentType.CENTER],
        [fmtMDT(p.projete),      1100, AlignmentType.CENTER],
        [p.verdict,              1100, AlignmentType.CENTER, true, verdictColors[p.verdict]||'1E293B'],
      ], i % 2 === 1)),
    ],
  }));

  const nNormal = projRows.filter(x => x.p.verdict === 'Normal').length;
  const nSous   = projRows.filter(x => x.p.verdict === 'Sous Min').length;
  const nAv     = projRows.filter(x => x.p.verdict === 'Avenant').length;
  children.push(spacer());
  children.push(new Table({
    width: { size: 100, type: WidthType.PERCENTAGE },
    rows: [
      new TableRow({
        children: [
          new TableCell({ children: [new Paragraph({ children: [new TextRun({ text: `✅ Normal`, bold:true, color:'059669', size:20 }), new TextRun({ text: `   ${nNormal}`, bold:true, size:24 })], alignment: AlignmentType.CENTER })], width:{size:33,type:WidthType.PERCENTAGE} }),
          new TableCell({ children: [new Paragraph({ children: [new TextRun({ text: `❌ Sous Min`, bold:true, color:'DC2626', size:20 }), new TextRun({ text: `   ${nSous}`, bold:true, size:24 })], alignment: AlignmentType.CENTER })], width:{size:33,type:WidthType.PERCENTAGE} }),
          new TableCell({ children: [new Paragraph({ children: [new TextRun({ text: `⚠️ Avenant`, bold:true, color:'D97706', size:20 }), new TextRun({ text: `   ${nAv}`, bold:true, size:24 })], alignment: AlignmentType.CENTER })], width:{size:34,type:WidthType.PERCENTAGE} }),
        ],
      }),
    ],
  }));
  children.push(new Paragraph({ children: [new PageBreak()] }));

  // ── Matrice de Risque Globale ─────────────────────────────────────────────────
  children.push(h1('Matrice de Risque Globale'));

  children.push(new Table({
    width: { size: 100, type: WidthType.PERCENTAGE },
    rows: [
      hdrRow([['Région',1200],['Marchés',800],['Budget',1400],['Phy',800],['Fin',800],['Écart',800],['Projeté',1200],['Tendance',1000],['Risque',1000]]),
      ...ALL_REGIONS.map((reg, i) => {
        const ra  = actifs.filter(r => (r.region || '') === reg);
        const bud = ra.reduce((s, r) => s + parseNum(r.tot_marche || r.totmarche || r.montant), 0);
        const pl  = ra.map(r => parseNum(r.taux_phy || r.avt_phy)).filter(v => v > 0);
        const pm  = pl.length ? pl.reduce((a, b) => a + b) / pl.length : 0;
        const fl  = ra.map(r => parseNum(r.taux_fin)).filter(v => v > 0);
        const fm  = fl.length ? fl.reduce((a, b) => a + b) / fl.length : 0;
        const ecart = pm - fm;
        const rProjs = ra.map(r => projection(r));
        const projTotal = rProjs.reduce((s, p) => s + p.projete, 0);
        const tendance = pm > fm ? '📈 Hausse phy' : pm < fm ? '📉 Hausse fin' : '➡️ Équilibre';
        const critiques = ra.filter(r => { const d = getDelaiRestant(r); return d !== null && d <= DELAI_CRITIQUE; });
        const risque = critiques.length > 0 ? 'CRITIQUE' : pm < SEUIL_STANDARD ? 'ÉLEVÉ' : 'NORMAL';
        const rc = risque === 'CRITIQUE' ? 'DC2626' : risque === 'ÉLEVÉ' ? 'D97706' : '059669';
        return dataRow([
          [reg,               1200, AlignmentType.LEFT, true, REGION_HEX[reg]||NAVY],
          [ra.length,         800,  AlignmentType.CENTER],
          [fmtMDT(bud),       1400, AlignmentType.CENTER],
          [fmtPct(pm),        800,  AlignmentType.CENTER],
          [fmtPct(fm),        800,  AlignmentType.CENTER],
          [`${ecart >= 0 ? '+' : ''}${ecart.toFixed(0)}%`, 800, AlignmentType.CENTER, false, ecart >= 0 ? '059669' : 'DC2626'],
          [fmtMDT(projTotal), 1200, AlignmentType.CENTER],
          [tendance,          1000, AlignmentType.CENTER],
          [risque,            1000, AlignmentType.CENTER, true, rc],
        ], i % 2 === 1);
      }),
    ],
  }));
  children.push(new Paragraph({ children: [new PageBreak()] }));

  // ── Recommandations ───────────────────────────────────────────────────────────
  children.push(h1('Recommandations'));

  const recs = [
    [`1. ⚡ Modernisation ≥${SEUIL_MODERNISATION}%`, `${modEnSeuil.map(r => r.region).filter((v,i,a)=>a.indexOf(v)===i).join(', ')||'Aucun'} — Procéder au lancement d'un nouveau marché pour assurer la continuité du service.`],
    [`2. ❌ Sous-consommation`, `${projRows.filter(x=>x.p.verdict==='Sous Min').length} marché(s) projeté(s) sous le montant minimum. Accélérer les réceptions de travaux.`],
    [`3. 📈 Dépassements`, `${projRows.filter(x=>x.p.verdict==='Avenant').length} marché(s) au-delà du montant marché. Préparer les avenants nécessaires.`],
    [`4. 🚀 Régions sans Modernisation`, `${regsSansModerni.join(', ')||'Aucune'} — Lancement immédiat d'un nouveau marché Modernisation requis.`],
    [`5. 📋 Suivi mensuel`, `Maintenir le reporting mensuel avec mise à jour des avancements physiques et financiers.`],
    [`6. 🎯 Objectif`, `100% d'exécution avant échéance PO pour tous les marchés actifs.`],
  ];

  for (const [titre, contenu] of recs) {
    children.push(new Table({
      width: { size: 100, type: WidthType.PERCENTAGE },
      rows: [
        new TableRow({
          children: [
            new TableCell({
              children: [
                new Paragraph({ children: [new TextRun({ text: titre,   bold: true, color: NAVY, size: 22 })], spacing: { after: 60 } }),
                new Paragraph({ children: [new TextRun({ text: contenu, color: '374151', size: 20 })] }),
              ],
              shading: cellShade('F8FAFC'),
              margins: { top: 120, bottom: 120, left: 200, right: 200 },
            }),
          ],
        }),
      ],
      borders: { top: { style: BorderStyle.SINGLE, size: 4, color: '0680C3' }, bottom: { style: BorderStyle.NONE }, left: { style: BorderStyle.NONE }, right: { style: BorderStyle.NONE } },
    }));
    children.push(spacer());
  }

  // Légende
  children.push(new Table({
    width: { size: 100, type: WidthType.PERCENTAGE },
    rows: [
      new TableRow({
        children: [
          new TableCell({
            children: [
              new Paragraph({ children: [new TextRun({ text: '📖 Légende', bold: true, color: NAVY, size: 22 })], spacing: { after: 80 } }),
              new Paragraph({ children: [new TextRun({ text: `Seuils avancement : 🟢 ≥ ${SEUIL_STANDARD}% Normal  •  🟡 seuil Modernisation ≥ ${SEUIL_MODERNISATION}%  •  🔴 CRITIQUE ≥ ${SEUIL_CRITIQUE_PCT}%`, size: 18, color: '475569' })] }),
              new Paragraph({ children: [new TextRun({ text: `Délais : 🟢 > ${DELAI_ATTENTION}j  •  🟡 ≤ ${DELAI_ATTENTION}j ATTENTION  •  🔴 ≤ ${DELAI_CRITIQUE}j CRITIQUE`, size: 18, color: '475569' })] }),
            ],
            shading: cellShade('F8FAFC'),
            margins: { top: 120, bottom: 120, left: 200, right: 200 },
          }),
        ],
      }),
    ],
    borders: { top: { style: BorderStyle.NONE }, bottom: { style: BorderStyle.NONE }, left: { style: BorderStyle.NONE }, right: { style: BorderStyle.NONE } },
  }));
  children.push(spacer());

  // Pied de page
  children.push(new Table({
    width: { size: 100, type: WidthType.PERCENTAGE },
    rows: [
      new TableRow({
        children: [
          new TableCell({ children: [
            new Paragraph({ children: [new TextRun({ text: 'Nabil Derouiche', bold:true, color:WH, size:22 })], alignment: AlignmentType.CENTER }),
            new Paragraph({ children: [new TextRun({ text: 'Responsable Achats Zone Sud', color:'CBD5E1', size:18 })], alignment: AlignmentType.CENTER }),
          ], shading: cellShade(NAVY), margins: { top:100, bottom:100 }, width:{size:50,type:WidthType.PERCENTAGE} }),
          new TableCell({ children: [
            new Paragraph({ children: [new TextRun({ text: 'TUNISIE TELECOM', bold:true, color:WH, size:22 })], alignment: AlignmentType.CENTER }),
            new Paragraph({ children: [new TextRun({ text: `Sfax — ${today}`, color:'CBD5E1', size:18 })], alignment: AlignmentType.CENTER }),
          ], shading: cellShade(NAVY), margins: { top:100, bottom:100 }, width:{size:50,type:WidthType.PERCENTAGE} }),
        ],
      }),
    ],
  }));

  const doc = new Document({
    creator:     'RLA API',
    description: 'Rapport de Suivi des Marchés RLA Zone Sud — Tunisie Telecom',
    title:       'Rapport RLA Zone Sud',
    styles: {
      paragraphStyles: [
        { id: 'Heading1', name: 'Heading 1', run: { size: 36, bold: true, color: NAVY } },
        { id: 'Heading2', name: 'Heading 2', run: { size: 26, bold: true, color: NAVY } },
      ],
    },
    sections: [{ children }],
  });

  return Packer.toBuffer(doc);
}

module.exports = { generateDocx };