// ─── Default Named Ranges ────────────────────────────────────────────────────
export const DEFAULT_NAMED_RANGES = [
  { name: 'REVENUE',    range: 'SalesData!$I:$I' },
  { name: 'COGS',       range: 'SalesData!$J:$J' },
  { name: 'GROSSPROFIT',range: 'SalesData!$K:$K' },
  { name: 'EXPENSES',   range: 'SalesData!$L:$L' },
  { name: 'NETPROFIT',  range: 'SalesData!$M:$M' },
  { name: 'MARGIN',     range: 'SalesData!$N:$N' },
  { name: 'UNITSSOLD',  range: 'SalesData!$G:$G' },
  { name: 'UNITPRICE',  range: 'SalesData!$H:$H' },
  { name: 'REGION',     range: 'SalesData!$D:$D' },
  { name: 'PRODUCT',    range: 'SalesData!$C:$C' },
  { name: 'MANAGER',    range: 'SalesData!$E:$E' },
  { name: 'QUARTER',    range: 'SalesData!$F:$F' },
];

// ─── Column name lookup ───────────────────────────────────────────────────────
// Maps EXACT visual column position A-O to the field name.
// Based on SALES_COL_DEFS order:
// A=S.No(null), B=id, C=product, D=region, E=manager, F=quarter,
// G=unitsSold, H=unitPrice, I=revenue, J=cogs, K=grossProfit,
// L=expenses, M=netProfit, N=margin, O=status
export const colNames = [
  null,           // A: S.No. (no data field)
  'id',           // B
  'product',      // C
  'region',       // D
  'manager',      // E
  'quarter',      // F
  'unitsSold',    // G
  'unitPrice',    // H
  'revenue',      // I
  'cogs',         // J
  'grossProfit',  // K
  'expenses',     // L
  'netProfit',    // M
  'margin',       // N
  'status',       // O
];

// Maps named-range strings to their field key
const RANGE_TO_FIELD = {
  'SALESDATA!$A:$A': 'sno',      'SALESDATA!$B:$B': 'id',       
  'SALESDATA!$C:$C': 'product',  'SALESDATA!$D:$D': 'region',   
  'SALESDATA!$E:$E': 'manager',  'SALESDATA!$F:$F': 'quarter',  
  'SALESDATA!$G:$G': 'unitsSold','SALESDATA!$H:$H': 'unitPrice',
  'SALESDATA!$I:$I': 'revenue',  'SALESDATA!$J:$J': 'cogs',     
  'SALESDATA!$K:$K': 'grossProfit', 'SALESDATA!$L:$L': 'expenses', 
  'SALESDATA!$M:$M': 'netProfit',   'SALESDATA!$N:$N': 'margin',   
  'SALESDATA!$O:$O': 'status',
  // Also without $ signs
  'SALESDATA!A:A': 'sno',        'SALESDATA!B:B': 'id',         
  'SALESDATA!C:C': 'product',    'SALESDATA!D:D': 'region',     
  'SALESDATA!E:E': 'manager',    'SALESDATA!F:F': 'quarter',    
  'SALESDATA!G:G': 'unitsSold',  'SALESDATA!H:H': 'unitPrice',  
  'SALESDATA!I:I': 'revenue',    'SALESDATA!J:J': 'cogs',       
  'SALESDATA!K:K': 'grossProfit', 'SALESDATA!L:L': 'expenses',   
  'SALESDATA!M:M': 'netProfit',   'SALESDATA!N:N': 'margin',     
  'SALESDATA!O:O': 'status',
};

// ─── evaluateField ────────────────────────────────────────────────────────────
export const evaluateField = (data, field) => {
  if (!data) return 0;
  const raw = data[field];
  if (typeof raw === 'string' && raw.startsWith('=')) {
    try {
      const u = Number(data.unitsSold) || 0;
      const p = Number(data.unitPrice) || 0;
      const rev = u * p;
      if (field === 'revenue')    return rev;
      if (field === 'cogs')       return rev * 0.4;
      if (field === 'grossProfit')return rev * 0.6;
      if (field === 'expenses')   return rev * 0.1;
      if (field === 'netProfit')  return rev * 0.5;
      if (field === 'margin')     return rev > 0 ? 50 : 0;
    } catch { return 0; }
  }
  return Number(raw) || 0;
};

// ─── Column letter → 0-based index ───────────────────────────────────────────
export const getColIdx = (letter) => {
  let idx = 0;
  for (let i = 0; i < letter.length; i++) {
    idx = idx * 26 + (letter.charCodeAt(i) - 64);
  }
  return idx - 1;
};

// ─── Resolve a named-range string to a numeric value or numeric array ─────────
const resolveNamedRangeToData = (rangeStr, salesData, currentCols = colNames) => {
  if (!rangeStr || !salesData) return null;
  const s = rangeStr.trim();

  // Scalar number
  const asNum = Number(s);
  if (!isNaN(asNum) && s !== '') return asNum;

  // Is it a known full-column reference? (e.g. SalesData!$H:$H)
  const normKey = s.replace(/\s/g, '').toUpperCase();
  if (RANGE_TO_FIELD[normKey]) {
    const fk = RANGE_TO_FIELD[normKey];
    return salesData.map(r => Number(evaluateField(r, fk)) || 0);
  }

  // Generic full-column: Sheet!$COL:$COL or Sheet!COL:COL
  const fullColMatch = s.match(/(?:[a-zA-Z0-9_]+!)?\$?([A-Za-z]+):\$?([A-Za-z]+)$/);
  if (fullColMatch && fullColMatch[1].toUpperCase() === fullColMatch[2].toUpperCase()) {
    const col = getColIdx(fullColMatch[1].toUpperCase());
    const fieldKey = currentCols[col];
    if (fieldKey) return salesData.map(r => Number(evaluateField(r, fieldKey)) || 0);
  }

  // Range with row bounds: Sheet!$B$2:$B$5000 or B2:B500
  const rangeMatch = s.match(/(?:[a-zA-Z0-9_]+!)?\$?([A-Za-z]+)\$?(\d+):\$?([A-Za-z]+)\$?(\d+)/);
  if (rangeMatch) {
    const col = getColIdx(rangeMatch[1].toUpperCase());
    const rowStart = Math.max(0, parseInt(rangeMatch[2], 10) - 1);
    const rowEnd   = Math.max(0, parseInt(rangeMatch[4], 10) - 1);
    const fieldKey = currentCols[col];
    if (fieldKey) {
      const vals = [];
      const start = Math.min(rowStart, rowEnd);
      const end = Math.max(rowStart, rowEnd);
      for (let i = start; i <= end && i < salesData.length; i++) {
        vals.push(Number(evaluateField(salesData[i], fieldKey)) || 0);
      }
      return vals;
    }
  }

  return null;
};

// ─── Resolve a named range to its STRING array (for text-based COUNTIF etc.) ──
const resolveNamedRangeToStrings = (rangeStr, salesData, currentCols = colNames) => {
  if (!rangeStr || !salesData) return null;
  const s = rangeStr.trim();

  const normKey = s.replace(/\s/g, '').toUpperCase();
  if (RANGE_TO_FIELD[normKey]) {
    const fk = RANGE_TO_FIELD[normKey];
    return salesData.map(r => String(r[fk] ?? ''));
  }

  const fullColMatch = s.match(/(?:[a-zA-Z0-9_]+!)?\$?([A-Za-z]+):\$?([A-Za-z]+)$/);
  if (fullColMatch && fullColMatch[1].toUpperCase() === fullColMatch[2].toUpperCase()) {
    const col = getColIdx(fullColMatch[1].toUpperCase());
    const fk = currentCols[col];
    if (fk) return salesData.map(r => String(r[fk] ?? ''));
  }

  return null;
};

// ─── Main formula evaluator ───────────────────────────────────────────────────
export const evaluateSummaryFormula = (formulaStr, salesData, metricName = '', customRanges = [], visibleCols = null) => {
  if (!formulaStr || !formulaStr.startsWith('=')) return formulaStr;

  const sd = salesData || [];
  const currentCols = visibleCols || colNames;

  let expr = formulaStr.substring(1).trim();
  const rawForm = ('=' + expr).toUpperCase().replace(/\s/g, '');

  // ── Built-in keyMap (bareword → field) ─────────────────────────────────────
  const keyMap = {
    REVENUE: 'revenue', COGS: 'cogs', GROSSPROFIT: 'grossProfit',
    EXPENSES: 'expenses', NETPROFIT: 'netProfit', MARGIN: 'margin',
    UNITSSOLD: 'unitsSold', UNITPRICE: 'unitPrice',
    REGION: 'region', PRODUCT: 'product', MANAGER: 'manager',
    QUARTER: 'quarter', STATUS: 'status',
  };

  // ── Merge built-in defaults + custom named ranges ─────────────────────────
  const builtinRanges = DEFAULT_NAMED_RANGES.map(r => ({ name: r.name, range: r.range }));
  const allRanges = [...builtinRanges, ...(customRanges || [])];

  // Build resolved map: NAME → numeric(array) and string array
  const resolvedNum = {};   // name → number | number[]
  const resolvedStr = {};   // name → string[]
  allRanges.forEach(({ name, range }) => {
    if (!name || !range) return;
    const k = name.toUpperCase();
    resolvedNum[k] = resolveNamedRangeToData(range, sd, currentCols);
    resolvedStr[k] = resolveNamedRangeToStrings(range, sd, currentCols);
  });

  // ── INDEX/MATCH shortcuts ─────────────────────────────────────────────────
  if (rawForm === '=INDEX(REGION,MATCH(MAX(REVENUE),REVENUE,0))') {
    const regionMap = {};
    sd.forEach(r => { regionMap[r.region] = (regionMap[r.region] || 0) + evaluateField(r, 'revenue'); });
    return Object.entries(regionMap).sort((a, b) => b[1] - a[1])[0]?.[0] || '-';
  }
  if (rawForm === '=INDEX(PRODUCT,MATCH(MAX(UNITSSOLD),UNITSSOLD,0))') {
    const prodMap = {};
    sd.forEach(r => { prodMap[r.product] = (prodMap[r.product] || 0) + (Number(r.unitsSold) || 0); });
    return Object.entries(prodMap).sort((a, b) => b[1] - a[1])[0]?.[0] || '-';
  }
  if (rawForm === '=INDEX(MANAGER,MATCH(MAX(REVENUE),REVENUE,0))') {
    const mgMap = {};
    sd.forEach(r => { mgMap[r.manager] = (mgMap[r.manager] || 0) + evaluateField(r, 'revenue'); });
    return Object.entries(mgMap).sort((a, b) => b[1] - a[1])[0]?.[0] || '-';
  }

  // ── Helper: get numeric array from arg string ─────────────────────────────
  const getNumArray = (arg) => {
    const a = arg.trim();
    const nameKey = a.toUpperCase();

    // Custom/built-in named range
    if (resolvedNum.hasOwnProperty(nameKey) && resolvedNum[nameKey] !== null) {
      const v = resolvedNum[nameKey];
      return Array.isArray(v) ? v : [v];
    }

    // Full-column ref
    const fullColMatch = a.match(/(?:[a-zA-Z0-9_]+!)?\$?([A-Za-z]+):\$?([A-Za-z]+)$/);
    if (fullColMatch && fullColMatch[1].toUpperCase() === fullColMatch[2].toUpperCase()) {
      const col = getColIdx(fullColMatch[1].toUpperCase());
      const fk = currentCols[col];
      if (fk) return sd.map(r => Number(evaluateField(r, fk)) || 0);
    }

    // Row-bound range
    const rangeMatch = a.match(/(?:[a-zA-Z0-9_]+!)?\$?([A-Za-z]+)\$?(\d+):\$?([A-Za-z]+)\$?(\d+)/);
    if (rangeMatch) {
      const col = getColIdx(rangeMatch[1].toUpperCase());
      const rowStart = Math.max(0, parseInt(rangeMatch[2], 10) - 2);
      const rowEnd   = Math.max(0, parseInt(rangeMatch[4], 10) - 2);
      const fk = currentCols[col];
      if (fk) {
        const vals = [];
        for (let i = Math.min(rowStart, rowEnd); i <= Math.max(rowStart, rowEnd) && i < sd.length; i++) {
          vals.push(Number(evaluateField(sd[i], fk)) || 0);
        }
        return vals;
      }
    }

    // Built-in keyword
    const key = keyMap[nameKey];
    if (key) return sd.map(r => evaluateField(r, key));

    // Plain number
    const n = Number(a);
    if (!isNaN(n)) return [n];

    return [];
  };

  // Helper: get string array from arg
  const getStrArray = (arg) => {
    const a = arg.trim();
    const nameKey = a.toUpperCase();
    if (resolvedStr.hasOwnProperty(nameKey) && resolvedStr[nameKey] !== null) {
      return resolvedStr[nameKey];
    }
    const key = keyMap[nameKey];
    if (key) return sd.map(r => String(r[key] ?? ''));
    return [];
  };

  // ── Aggregate functions ─────────────────────────────────────────────────────
  const agg = {
    SUM:     arr => arr.reduce((a, b) => a + b, 0),
    AVERAGE: arr => arr.length ? arr.reduce((a, b) => a + b, 0) / arr.length : 0,
    MAX:     arr => arr.length ? Math.max(...arr) : 0,
    MIN:     arr => arr.length ? Math.min(...arr) : 0,
    COUNT:   arr => arr.length,
    MEDIAN:  arr => {
      if (!arr.length) return 0;
      const s = [...arr].sort((a, b) => a - b);
      const m = Math.floor(s.length / 2);
      return s.length % 2 ? s[m] : (s[m - 1] + s[m]) / 2;
    },
    STDEV: arr => {
      if (arr.length < 2) return 0;
      const mean = arr.reduce((a, b) => a + b, 0) / arr.length;
      return Math.sqrt(arr.reduce((s, v) => s + (v - mean) ** 2, 0) / (arr.length - 1));
    },
  };

  // ── IFERROR(expr, fallback) ───────────────────────────────────────────────
  expr = expr.replace(/IFERROR\(([^,]+),([^)]+)\)/gi, (_, e, fallback) => {
    try {
      const inner = evaluateSummaryFormula('=' + e.trim(), sd, '', customRanges);
      if (inner === '#ERROR' || inner === null || inner === undefined) return fallback.trim().replace(/^"|"$/g, '');
      return inner;
    } catch { return fallback.trim().replace(/^"|"$/g, ''); }
  });

  // ── IF(condition, true_val, false_val) ────────────────────────────────────
  expr = expr.replace(/\bIF\(([^,]+),([^,]+),([^)]+)\)/gi, (_, cond, tv, fv) => {
    try {
      // eslint-disable-next-line
      const condResult = new Function(`return ${cond}`)();
      return condResult ? tv.trim() : fv.trim();
    } catch { return fv.trim(); }
  });

  // ── SUMIF(range, criteria, [sum_range]) ───────────────────────────────────
  expr = expr.replace(/SUMIF\(([^,]+),([^,)]+)(?:,([^)]+))?\)/gi, (_, rng, crit, sumRng) => {
    const checkArr = getStrArray(rng.trim());
    const valueArr = sumRng ? getNumArray(sumRng.trim()) : getNumArray(rng.trim());
    const criteria = crit.trim().replace(/^"|"$/g, '');
    let total = 0;
    checkArr.forEach((v, i) => {
      if (String(v).toLowerCase() === criteria.toLowerCase()) {
        total += valueArr[i] || 0;
      }
    });
    return total;
  });

  // ── COUNTIF(range, criteria) ──────────────────────────────────────────────
  expr = expr.replace(/COUNTIF\(([^,]+),([^)]+)\)/gi, (_, rng, crit) => {
    const arr = getStrArray(rng.trim());
    const criteria = crit.trim().replace(/^"|"$/g, '');
    return arr.filter(v => String(v).toLowerCase() === criteria.toLowerCase()).length;
  });

  // ── AVERAGEIF(range, criteria, avg_range) ─────────────────────────────────
  expr = expr.replace(/AVERAGEIF\(([^,]+),([^,]+),([^)]+)\)/gi, (_, rng, crit, avgRng) => {
    const checkArr = getStrArray(rng.trim());
    const valueArr = getNumArray(avgRng.trim());
    const criteria = crit.trim().replace(/^"|"$/g, '');
    const filtered = [];
    checkArr.forEach((v, i) => {
      if (String(v).toLowerCase() === criteria.toLowerCase()) filtered.push(valueArr[i] || 0);
    });
    return filtered.length ? filtered.reduce((a, b) => a + b, 0) / filtered.length : 0;
  });

  // ── Standard aggregates: SUM/AVERAGE/MAX/MIN/COUNT/MEDIAN/STDEV ─────────
  expr = expr.replace(/(SUM|AVERAGE|MAX|MIN|COUNT|MEDIAN|STDEV)\(([^)]+)\)/gi, (_, func, args) => {
    const f = func.toUpperCase();
    const arr = getNumArray(args);
    return arr.length ? agg[f](arr) : 0;
  });

  // ── Replace remaining bare custom/builtin names with their totals ─────────
  const allNameKeys = Object.keys(resolvedNum).sort((a, b) => b.length - a.length);
  if (allNameKeys.length > 0) {
    const customRegex = new RegExp(
      `\\b(${allNameKeys.map(k => k.replace(/[.*+?^${}()|[\]\\]/g, '\\$&')).join('|')})\\b`, 'gi'
    );
    expr = expr.replace(customRegex, m => {
      const val = resolvedNum[m.toUpperCase()];
      if (val === null || val === undefined) return '0';
      if (Array.isArray(val)) return val.reduce((a, b) => a + b, 0);
      return val;
    });
  }

  // ── Fallback: replace bareword keywords ──────────────────────────────────
  expr = expr.replace(/\b([a-zA-Z]+)\b/g, match => {
    const key = keyMap[match.toUpperCase()];
    if (key) return sd.map(r => evaluateField(r, key)).reduce((a, b) => a + b, 0);
    return match;
  });

  // ── Evaluate expression ───────────────────────────────────────────────────
  try {
    if (!/^[0-9.+\-*/()%\s,e]+$/i.test(expr)) return '#ERROR';
    // eslint-disable-next-line
    const result = new Function(`return ${expr}`)();
    if (typeof result !== 'number' || isNaN(result) || !isFinite(result)) return '#ERROR';

    const m = metricName.toLowerCase();
    const f = formulaStr.toLowerCase();
    
    // Check metric name AND formula column reference for count/unit fields
    const isCount = m.includes('units') || m.includes('count') || m.includes('qty') || 
                    m.includes('quantity') || m.includes('rows') || m.includes('id') ||
                    /unitssold|quantity|qty/i.test(f) ||
                    // Detect direct column G references (unitsSold) with or without SalesData! prefix
                    /(?:[^!]*!)?\bg\d+/i.test(f) || /\bg:/i.test(f);
                    
    const isPercent = m.includes('margin') || m.includes('%') || m.includes('rate') || m.includes('pct') ||
                      /margin/i.test(f);
                      
    const isPrice = m.includes('price') || m.includes('avg') || /unitprice/i.test(f) || /(?:[^!]*!)?\bh\d+/i.test(f) || /\bh:/i.test(f);

    if (isPercent) return `${result.toFixed(2)}%`;
    if (isCount)   return result.toLocaleString('en-US');
    if (isPrice)   return `$${result.toLocaleString('en-US', { minimumFractionDigits: 2, maximumFractionDigits: 2 })}`;
    return `$${result.toLocaleString('en-US', { minimumFractionDigits: 2, maximumFractionDigits: 2 })}`;
  } catch {
    return '#ERROR';
  }
};

// ─── getSalesData ─────────────────────────────────────────────────────────────
export const getSalesData = () => {
  const regions   = ['North', 'South', 'East', 'West', 'Central'];
  const products  = ['Laptop', 'Monitor', 'Keyboard', 'Mouse', 'Headset', 'Webcam', 'Tablet', 'Printer', 'Scanner', 'Router'];
  const managers  = ['Alice Johnson', 'Bob Smith', 'Carol White', 'David Brown', 'Eva Martinez'];
  const statuses  = ['Completed', 'Pending', 'In Progress', 'Cancelled'];

  const rows = [];
  for (let i = 1; i <= 100000; i++) {
    const unitsSold  = Math.floor(Math.random() * 500) + 50;
    const unitPrice  = parseFloat((Math.random() * 990 + 10).toFixed(2));
    const revenue    = parseFloat((unitsSold * unitPrice).toFixed(2));
    const costPct    = 0.4 + Math.random() * 0.2;
    const cogs       = parseFloat((revenue * costPct).toFixed(2));
    const grossProfit= parseFloat((revenue - cogs).toFixed(2));
    const expenses   = parseFloat((revenue * (0.05 + Math.random() * 0.1)).toFixed(2));
    const netProfit  = parseFloat((grossProfit - expenses).toFixed(2));
    const margin     = parseFloat(((netProfit / revenue) * 100).toFixed(2));
    const quarter    = `Q${Math.ceil((i % 12 || 12) / 3)} ${2023 + Math.floor(i / 13)}`;

    rows.push({
      id: i,
      product:    products[i % products.length],
      region:     regions[i % regions.length],
      manager:    managers[i % managers.length],
      quarter,
      unitsSold,
      unitPrice,
      revenue,
      cogs,
      grossProfit,
      expenses,
      netProfit,
      margin,
      status:     statuses[i % statuses.length],
    });
  }
  return rows;
};

// ─── computeSummary ───────────────────────────────────────────────────────────
export const computeSummary = (salesData, existingSummaryData = null, customRanges = [], visibleCols = null) => {
  const sd = salesData || [];

  if (existingSummaryData && existingSummaryData.length > 0) {
    return existingSummaryData.map(row => ({
      ...row,
      value: evaluateSummaryFormula(row.formula, sd, row.metric, customRanges, visibleCols),
    }));
  }

  if (sd.length === 0) {
    return [
      { metric: 'Total Revenue',    formula: '=SUM(Revenue)',                                         value: '$0.00',  note: 'Sum of all revenue' },
      { metric: 'Total COGS',       formula: '=SUM(COGS)',                                            value: '$0.00',  note: 'Cost of Goods Sold' },
      { metric: 'Gross Profit',     formula: '=Revenue - COGS',                                       value: '$0.00',  note: 'Revenue minus COGS' },
      { metric: 'Total Expenses',   formula: '=SUM(Expenses)',                                        value: '$0.00',  note: 'Operating expenses' },
      { metric: 'Net Profit',       formula: '=GrossProfit - Expenses',                               value: '$0.00',  note: 'Gross Profit minus Expenses' },
      { metric: 'Avg Margin %',     formula: '=AVERAGE(Margin)',                                      value: '0.00%',  note: 'Average net margin' },
      { metric: 'Top Region',       formula: '=INDEX(Region,MATCH(MAX(Revenue),Revenue,0))',           value: '-',      note: 'Region with max revenue' },
      { metric: 'Top Product',      formula: '=INDEX(Product,MATCH(MAX(UnitsSold),UnitsSold,0))',     value: '-',      note: 'Product with max units' },
      { metric: 'Total Units Sold', formula: '=SUM(UnitsSold)',                                       value: '0',      note: 'Sum of all units sold' },
      { metric: 'Avg Unit Price',   formula: '=AVERAGE(UnitPrice)',                                   value: '$0.00',  note: 'Average selling price' },
    ];
  }

  const totalRevenue  = sd.reduce((s, r) => s + evaluateField(r, 'revenue'), 0);
  const totalCOGS     = sd.reduce((s, r) => s + evaluateField(r, 'cogs'), 0);
  const grossProfit   = totalRevenue - totalCOGS;
  const totalExpenses = sd.reduce((s, r) => s + evaluateField(r, 'expenses'), 0);
  const netProfit     = grossProfit - totalExpenses;
  const avgMargin     = sd.length ? sd.reduce((s, r) => s + evaluateField(r, 'margin'), 0) / sd.length : 0;
  const totalUnits    = sd.reduce((s, r) => s + (Number(r.unitsSold) || 0), 0);
  const avgPrice      = sd.length ? sd.reduce((s, r) => s + (Number(r.unitPrice) || 0), 0) / sd.length : 0;

  const regionMap = {};
  sd.forEach(r => { regionMap[r.region] = (regionMap[r.region] || 0) + evaluateField(r, 'revenue'); });
  const topRegion = Object.entries(regionMap).sort((a, b) => b[1] - a[1])[0]?.[0] || '-';

  const prodMap = {};
  sd.forEach(r => { prodMap[r.product] = (prodMap[r.product] || 0) + (Number(r.unitsSold) || 0); });
  const topProduct = Object.entries(prodMap).sort((a, b) => b[1] - a[1])[0]?.[0] || '-';

  const fmt  = n => `$${n.toLocaleString('en-US', { minimumFractionDigits: 2, maximumFractionDigits: 2 })}`;

  return [
    { metric: 'Total Revenue',    formula: '=SUM(Revenue)',                                       value: fmt(totalRevenue),  note: 'Sum of all revenue' },
    { metric: 'Total COGS',       formula: '=SUM(COGS)',                                          value: fmt(totalCOGS),     note: 'Cost of Goods Sold' },
    { metric: 'Gross Profit',     formula: '=Revenue - COGS',                                     value: fmt(grossProfit),   note: 'Revenue minus COGS' },
    { metric: 'Total Expenses',   formula: '=SUM(Expenses)',                                      value: fmt(totalExpenses), note: 'Operating expenses' },
    { metric: 'Net Profit',       formula: '=GrossProfit - Expenses',                             value: fmt(netProfit),     note: 'Gross Profit minus Expenses' },
    { metric: 'Avg Margin %',     formula: '=AVERAGE(Margin)',                                    value: `${avgMargin.toFixed(2)}%`, note: 'Average net margin' },
    { metric: 'Top Region',       formula: '=INDEX(Region,MATCH(MAX(Revenue),Revenue,0))',         value: topRegion,          note: 'Region with max revenue' },
    { metric: 'Top Product',      formula: '=INDEX(Product,MATCH(MAX(UnitsSold),UnitsSold,0))',   value: topProduct,         note: 'Product with max units sold' },
    { metric: 'Total Units Sold', formula: '=SUM(UnitsSold)',                                     value: totalUnits.toLocaleString(), note: 'Sum of all units sold' },
    { metric: 'Avg Unit Price',   formula: '=AVERAGE(UnitPrice)',                                 value: `$${avgPrice.toFixed(2)}`,   note: 'Average selling price' },
  ];
};

// ─── getProjectData ───────────────────────────────────────────────────────────
export const getProjectData = (project) => {
  const data = {
    FINANCE:   getSalesData(),
    BANKING:   getSalesData().map(r => ({ ...r, product: r.product + ' Loan',   region: r.region })),
    INSURANCE: getSalesData().map(r => ({ ...r, product: r.product + ' Policy', region: r.region })),
  };
  return data[project] || [];
};

// ─── Excel column-letter map for export ──────────────────────────────────────
// fieldName → Excel column letter (1-indexed: A=1st col of SalesData sheet)
export const FIELD_TO_EXCEL_COL = {
  id: 'A', product: 'B', region: 'C', manager: 'D', quarter: 'E',
  unitsSold: 'F', unitPrice: 'G', revenue: 'H', cogs: 'I',
  grossProfit: 'J', expenses: 'K', netProfit: 'L', margin: 'M', status: 'N',
};