src/services/formulaEngine.js

// Mini Excel-like formula engine

export function evaluateFormula(formula, getCell) {
  if (!formula || typeof formula !== "string") return null;
  if (!formula.startsWith("=")) return formula;

  try {
    // Strip "="
    const expr = formula.substring(1).toUpperCase();

    // Handle SUM(A1:A5)
    if (expr.startsWith("SUM(")) return sumFunction(expr, getCell);
    if (expr.startsWith("AVG(")) return avgFunction(expr, getCell);
    if (expr.startsWith("MIN(")) return minFunction(expr, getCell);
    if (expr.startsWith("MAX(")) return maxFunction(expr, getCell);

    // Handle simple arithmetic: A1 + B2 * 3
    const parsed = expr.replace(/[A-Z]+\d+/g, (cellRef) => {
      const v = getCell(cellRef);
      return v !== undefined ? v : 0;
    });

    return eval(parsed);
  } catch (e) {
    return null;
  }
}

// ✅ Extract A1:A5 type ranges
function parseRange(ref) {
  const range = ref.replace(/[A-Z]+\(|\)|/g, "").split(":");
  return range;
}

function getRangeValues(range, getCell) {
  const [start, end] = range;
  const col1 = start.match(/[A-Z]+/)[0];
  const row1 = parseInt(start.match(/\d+/)[0]);
  const col2 = end.match(/[A-Z]+/)[0];
  const row2 = parseInt(end.match(/\d+/)[0]);

  const values = [];

  for (let r = row1; r <= row2; r++) {
    const cellRef = `${col1}${r}`;
    const v = getCell(cellRef);
    if (!isNaN(v)) values.push(Number(v));
  }
  return values;
}

// ✅ SUM
function sumFunction(expr, getCell) {
  const range = parseRange(expr.replace("SUM(", "").replace(")", ""));
  return getRangeValues(range, getCell).reduce((a, b) => a + b, 0);
}

// ✅ AVG
function avgFunction(expr, getCell) {
  const range = parseRange(expr.replace("AVG(", "").replace(")", ""));
  const values = getRangeValues(range, getCell);
  return values.reduce((a, b) => a + b, 0) / values.length;
}

// ✅ MIN
function minFunction(expr, getCell) {
  const values = getRangeValues(
    parseRange(expr.replace("MIN(", "").replace(")", "")),
    getCell
  );
  return Math.min(...values);
}

// ✅ MAX
function maxFunction(expr, getCell) {
  const values = getRangeValues(
    parseRange(expr.replace("MAX(", "").replace(")", "")),
    getCell
  );
  return Math.max(...values);
}