src/utils/excelExport.js

import * as XLSX from 'xlsx';
import { P6_FIELDS } from './constants.js';

export function buildAndDownload(state) {
  const { dictCSV, panelCSV, selectedPanels, allRows, contributions, panelMap, fixedVars, modelParams, config } = state;
  if (!dictCSV || !panelCSV) { alert('Complete all steps first.'); return; }

  const selPanelRows = panelCSV.rows.filter(r => selectedPanels.has(String(r.__id)));
  const selVars = allRows.filter(r => r._sel).map(r => r.VARIABLE);
  const pCols = panelCSV.headers;
  const dh = dictCSV.headers;
  const pmI = dh.indexOf('PRIMARY_METRICS');
  const vI = dh.indexOf('VARIABLE');

  // Validation warnings
  const warnings = [];
  if (!selPanelRows.length) warnings.push('No panels selected (Page 1)');
  if (!selVars.length) warnings.push('No variables selected (Page 2)');
  const groups = getActivityGroups(allRows);
  if (!Object.keys(groups).length) warnings.push('No activity groups found (check Page 2)');
  const anyMapped = Object.values(panelMap).some(row => Object.values(row).some(v => v === true));
  if (!anyMapped) warnings.push('No variables mapped to any panel (Page 4)');
  if (warnings.length) {
    const ok = window.confirm('⚠ Incomplete data:\n\n' + warnings.map(w => '  • ' + w).join('\n') + '\n\nProceed anyway?');
    if (!ok) return;
  }

  const s1 = buildSheet1(pCols, selPanelRows);
  const s2 = buildSheet2(dh, pmI, vI, allRows, dictCSV);
  const s3 = buildSheet3(pCols, selPanelRows, allRows, contributions);
  const s4 = buildSheet4(pCols, selVars, panelMap, panelCSV);
  const s5 = buildSheet5(pCols, selVars, selPanelRows, panelMap, fixedVars);
  const s6 = buildSheet6(pCols, selVars, selPanelRows, modelParams);

  const wb = XLSX.utils.book_new();
  const sheets = [
    { name: 'Page 1 - Panel Selection', data: s1 },
    { name: 'Page 2 - Dictionary',      data: s2 },
    { name: 'Page 3 - Contributions',   data: s3 },
    { name: 'Page 4 - Panel Mapping',   data: s4 },
    { name: 'Page 5 - Fixed Variables', data: s5 },
    { name: 'Model_Params',             data: s6 },
    { name: 'Config_Params',            data: [['JSON'], [JSON.stringify(config, null, 2)]] },
  ];

  sheets.forEach(({ name, data }) => XLSX.utils.book_append_sheet(wb, makeSheet(data), name));
  const out = XLSX.write(wb, { bookType: 'xlsx', type: 'array' });
  const blob = new Blob([out], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
  triggerDownload(blob, 'Dabur_Model_Output.xlsx');
  return true;
}

function getActivityGroups(allRows) {
  const g = {};
  allRows.filter(r => r._sel).forEach(r => {
    const a = r.SN_ACTIVITY_LEVEL_1 || '(Unassigned)';
    if (!g[a]) g[a] = [];
    g[a].push(r.VARIABLE);
  });
  return g;
}

function getContrib(contributions, act, pk) {
  const c = contributions[act];
  if (!c) return { min: 0, max: 50 };
  if (typeof c.min === 'number') return c;
  return c[pk] || { min: 0, max: 50 };
}

function makeSheet(data) {
  const ws = XLSX.utils.aoa_to_sheet(data);
  const range = XLSX.utils.decode_range(ws['!ref'] || 'A1');
  for (let col = range.s.c; col <= range.e.c; col++) {
    const addr = XLSX.utils.encode_cell({ r: 0, c: col });
    if (ws[addr]) ws[addr].s = { font: { bold: true }, fill: { fgColor: { rgb: 'D4EDDA' } } };
  }
  return ws;
}

function buildSheet1(pCols, selPanelRows) {
  return [pCols.slice(), ...selPanelRows.map(row => pCols.map(h => row[h] ?? ''))];
}

function buildSheet2(dh, pmI, vI, allRows, dictCSV) {
  const rows = [[...dh]];
  dictCSV.rows.forEach(row => {
    const r = dh.map(h => row[h] ?? '');
    if (pmI >= 0 && vI >= 0) {
      const vName = String(row[dh[vI]] || '').trim();
      const selRow = allRows.find(ar => ar.VARIABLE === vName);
      r[pmI] = (selRow && selRow._sel) ? 'Y' : 'N';
    }
    rows.push(r);
  });
  return rows;
}

function buildSheet3(pCols, selPanelRows, allRows, contributions) {
  const groups = getActivityGroups(allRows);
  const actEntries = Object.entries(groups).sort(([a],[b]) => a.localeCompare(b));
  const rows = [[...pCols, 'ACTIVITY_LEVEL_1', 'VARIABLES', 'MIN_PERCENT', 'MAX_PERCENT']];
  selPanelRows.forEach(pr => {
    const pk = String(pr.__id);
    actEntries.forEach(([act, avars]) => {
      const cv = getContrib(contributions, act, pk);
      rows.push([...pCols.map(h => pr[h] ?? ''), act, avars.join(', '), cv.min, cv.max]);
    });
  });
  return rows;
}

function buildSheet4(pCols, selVars, panelMap, panelCSV) {
  const rows = [[...pCols, 'Remove_var']];
  panelCSV.rows.forEach(row => {
    const k = String(row.__id);
    const mapped = panelMap[k] || {};
    const mappedSet = new Set(selVars.filter(v => mapped[v] === true));
    const unselected = selVars.filter(v => !mappedSet.has(v));
    rows.push([...pCols.map(h => row[h] ?? ''), unselected.join(', ')]);
  });
  return rows;
}

function buildSheet5(pCols, selVars, selPanelRows, panelMap, fixedVars) {
  const rows = [[...pCols, 'Fix_var']];
  selPanelRows.forEach(row => {
    const k = String(row.__id);
    const mapped = panelMap[k] || {};
    const mappedSet = new Set(Object.keys(mapped).filter(v => mapped[v] === true));
    const fixSet = fixedVars[k] ? new Set(fixedVars[k]) : new Set();
    const fixList = [...fixSet].filter(v => mappedSet.has(v) && selVars.includes(v));
    rows.push([...pCols.map(h => row[h] ?? ''), fixList.join(', ')]);
  });
  return rows;
}

function buildSheet6(pCols, selVars, selPanelRows, modelParams) {
  const fieldKeys = P6_FIELDS.map(f => f.key);
  const header = [...pCols, 'Variable', ...fieldKeys];
  const rows = [header];
  selPanelRows.forEach(pr => {
    const pk = String(pr.__id);
    selVars.forEach(varName => {
      const rec = (modelParams || {})[`${pk}|${varName}`] || {};
      const hasVal = fieldKeys.some(k => rec[k] !== undefined && rec[k] !== '');
      if (!hasVal) return;
      rows.push([...pCols.map(h => pr[h] ?? ''), varName, ...fieldKeys.map(k => rec[k] !== undefined ? rec[k] : '')]);
    });
  });
  return rows;
}

function triggerDownload(blob, filename) {
  const url = URL.createObjectURL(blob);
  const a = document.createElement('a');
  a.href = url; a.download = filename;
  document.body.appendChild(a); a.click();
  setTimeout(() => { URL.revokeObjectURL(url); document.body.removeChild(a); }, 500);
}