import * as XLSX from 'xlsx';

const _nrm = v => String(v || '').trim();
const _nrl = v => _nrm(v).toLowerCase();
const _nrk = v => _nrl(v).replace(/[_\s]/g, '');

function xlsRows(wb, name) {
  if (!name) return [];
  return XLSX.utils.sheet_to_json(wb.Sheets[name], { defval: '' });
}

export function parsePastXLSX(arrayBuffer) {
  try {
    const wb = XLSX.read(arrayBuffer, { type: 'array' });
    const result = { panels: null, vars: null, contributions: null, mapping: null, fixedVars: null, config: null };

    // Sheet 1 — Panel Selection
    const s1n = wb.SheetNames.find(n => ['page 1','panel'].every(k => n.toLowerCase().includes(k)));
    if (s1n) {
      const rows = xlsRows(wb, s1n);
      if (rows.length) {
        result.panels = rows.map(r => Object.fromEntries(Object.entries(r).map(([k,v]) => [_nrm(k), _nrm(v)])));
      }
    }

    // Sheet 2 — Dictionary / selected vars (PRIMARY_METRICS = Y)
    const s2n = wb.SheetNames.find(n => ['page 2','dict'].every(k => n.toLowerCase().includes(k)));
    if (s2n) {
      const rows = xlsRows(wb, s2n);
      if (rows.length) {
        const keys = Object.keys(rows[0]);
        const varKey = keys.find(k => _nrl(k) === 'variable');
        const pmKey = keys.find(k => _nrl(k) === 'primary_metrics');
        if (varKey && pmKey) {
          result.vars = new Set();
          rows.forEach(r => {
            const v = _nrm(r[varKey]);
            if (v && _nrm(r[pmKey]).toUpperCase() === 'Y') result.vars.add(v);
          });
        }
      }
    }

    // Sheet 3 — Contributions
    const s3n = wb.SheetNames.find(n => ['page 3','contrib'].every(k => n.toLowerCase().includes(k)));
    if (s3n) {
      const rows = xlsRows(wb, s3n);
      if (rows.length) {
        const keys = Object.keys(rows[0]);
        const actKey = keys.find(k => _nrk(k).includes('activitylevel') || _nrk(k).includes('activity1'));
        const mnKey = keys.find(k => _nrl(k).includes('min'));
        const mxKey = keys.find(k => _nrl(k).includes('max'));
        if (actKey && mnKey && mxKey) {
          result.contributions = {};
          rows.forEach(r => {
            const act = _nrm(r[actKey]); if (!act) return;
            const mn = parseFloat(r[mnKey]) || 0;
            const mx = parseFloat(r[mxKey]) || 50;
            if (!result.contributions[act]) result.contributions[act] = { min: mn, max: mx };
          });
        }
      }
    }

    // Sheet 4 — Panel Mapping
    const s4n = wb.SheetNames.find(n => ['page 4','mapping'].every(k => n.toLowerCase().includes(k)));
    if (s4n) {
      const rows = xlsRows(wb, s4n);
      if (rows.length) {
        const keys = Object.keys(rows[0]);
        const rmKey = keys.find(k => _nrk(k).includes('removevar'));
        if (rmKey) {
          result.mapping = rows.map(r => {
            const vars = _nrm(r[rmKey]).split(',').map(v => v.trim()).filter(Boolean);
            const pd = {};
            keys.forEach(k => { if (k !== rmKey) pd[_nrm(k)] = _nrm(r[k]); });
            return { panelData: pd, removedVars: vars };
          });
        }
      }
    }

    // Sheet 5 — Fixed Variables
    const s5n = wb.SheetNames.find(n => ['page 5','fixed'].every(k => n.toLowerCase().includes(k)));
    if (s5n) {
      const rows = xlsRows(wb, s5n);
      if (rows.length) {
        const keys = Object.keys(rows[0]);
        const fvKey = keys.find(k => _nrk(k).includes('fixvar'));
        if (fvKey) {
          result.fixedVars = rows.map(r => {
            const vars = _nrm(r[fvKey]).split(',').map(v => v.trim()).filter(Boolean);
            const pd = {};
            keys.forEach(k => { if (k !== fvKey) pd[_nrm(k)] = _nrm(r[k]); });
            return { panelData: pd, vars };
          });
        } else {
          result.fixedVars = [];
        }
      }
    }

    // Config_Params sheet
    const cfgSheet = wb.SheetNames.find(n => n.toLowerCase().replace(/[\s_]/g,'').includes('configparam'));
    if (cfgSheet) {
      try {
        const cfgRows = XLSX.utils.sheet_to_json(wb.Sheets[cfgSheet], { header: 1, defval: '' });
        const jsonStr = cfgRows[1] && cfgRows[1][0] ? String(cfgRows[1][0]).trim() : '';
        if (jsonStr) result.config = JSON.parse(jsonStr);
      } catch (e) { /* ignore */ }
    }

    // Build synthesised panelCSV from Sheet 1
    if (result.panels && result.panels.length) {
      const pHeaders = Object.keys(result.panels[0]);
      const parsedPanelRows = result.panels.map((r, i) => {
        const o = { __id: i + 1 };
        pHeaders.forEach(h => { o[h] = _nrm(r[h]); });
        return o;
      });
      result.synthPanelCSV = { headers: pHeaders, rows: parsedPanelRows };
    }

    // Build synthesised dictCSV from Sheet 2 (only selected variables)
    const s2nRaw = wb.SheetNames.find(n => ['page 2','dict'].every(k => n.toLowerCase().includes(k)));
    if (s2nRaw) {
      const rawRows = XLSX.utils.sheet_to_json(wb.Sheets[s2nRaw], { defval: '' });
      if (rawRows.length) {
        const dHeaders = Object.keys(rawRows[0]);
        const parsedDictRows = rawRows.map((r, i) => {
          const o = { __id: i + 1 };
          dHeaders.forEach(h => { o[h] = String(r[h] ?? '').trim(); });
          o._sel = (o['PRIMARY_METRICS'] || '').toUpperCase() === 'Y';
          return o;
        });
        result.synthDictCSV = { headers: dHeaders, rows: parsedDictRows };
      }
    }

    return result;
  } catch (e) {
    console.error('[PastResult] parse error', e);
    return null;
  }
}