ObsiGate/frontend/js/xlsx/formula.js

/* ObsiGate — minimal client-side spreadsheet formula engine.
 *
 * Evaluates `=…` / `@…` cell content locally (same sheet, plus loaded
 * cross-sheet panels) so functions work without a round-trip: arithmetic,
 * comparisons, %, cell/range references and ~30 FR/EN functions.
 *
 * Safe by construction: own tokenizer + recursive-descent parser, no eval().
 * Anything the parser rejects keeps its original rendering (Excel stays the
 * source of truth on save); evaluation failures surface as Excel-style error
 * codes instead.
 *
 * Limits (documented): same-workbook only, no whole-column (A:A) references,
 * TODAY()/NOW() refresh on edit/render (no timer), array results show their
 * top-left value.
 */

const MAX_COL = 18278; // XFD
const MAX_ROW = 1048576;

/** FR aliases accepted next to the EN canonical names. */
const ALIASES = {
  SOMME: "SUM", MOYENNE: "AVERAGE", NB: "COUNT", NBVAL: "COUNTA",
  PRODUIT: "PRODUCT", MEDIANE: "MEDIAN", ARRONDI: "ROUND", ENT: "INT",
  RACINE: "SQRT", PUISSANCE: "POWER", SI: "IF", ET: "AND", OU: "OR",
  NON: "NOT", SIERREUR: "IFERROR", CONCATENER: "CONCAT",
  MAJUSCULES: "UPPER", MINUSCULES: "LOWER", NBCAR: "LEN",
  SUPPRESPACE: "TRIM", GAUCHE: "LEFT", DROITE: "RIGHT", STXT: "MID",
  AUJOURDHUI: "TODAY", MAINTENANT: "NOW", JOUR: "DAY", MOIS: "MONTH",
  ANNEE: "YEAR", LIEN: "HYPERLINK",
};

const ERRORS = {
  fr: { DIV: "#DIV/0!", VALUE: "#VALEUR!", NAME: "#NOM?", REF: "#REF!", CYCLE: "#CYCLE!" },
  en: { DIV: "#DIV/0!", VALUE: "#VALUE!", NAME: "#NAME?", REF: "#REF!", CIRC: "#CIRC!" },
};

export function errorCodes(locale) {
  return ERRORS[locale === "fr" ? "fr" : "en"];
}

export function isFormulaText(s) {
  if (typeof s !== "string") return false;
  const t = s.trim();
  return t.length > 1 && (t[0] === "=" || t[0] === "@");
}

/** Column letters (no $) → 1-based index, or 0 when invalid. */
export function colToIndex(letters) {
  let n = 0;
  const up = String(letters || "").toUpperCase();
  if (!/^[A-Z]{1,3}$/.test(up)) return 0;
  for (const ch of up) n = n * 26 + (ch.charCodeAt(0) - 64);
  return n > MAX_COL ? 0 : n;
}

export function indexToCol(n) {
  let s = "";
  while (n > 0) {
    const m = (n - 1) % 26;
    s = String.fromCharCode(65 + m) + s;
    n = Math.floor((n - 1) / 26);
  }
  return s;
}

/** Parse "[$]A[$]1" (already stripped of any sheet prefix) → {col,row} | null. */
export function parseA1(ref) {
  const m = /^\$?([A-Za-z]{1,3})\$?([0-9]+)$/.exec(String(ref || "").trim());
  if (!m) return null;
  const col = colToIndex(m[1]);
  const row = Number(m[2]);
  if (!col || !(row >= 1 && row <= MAX_ROW)) return null;
  return { col, row };
}

// ── Tokenizer ─────────────────────────────────────────────────────────────

function tokenize(src, sep) {
  const tokens = [];
  let i = 0;
  const n = src.length;
  const isSpace = (c) => c === " " || c === "\t" || c === "\n" || c === "\r";
  while (i < n) {
    const c = src[i];
    if (isSpace(c)) { i++; continue; }
    if (c === '"') {
      let j = i + 1;
      let out = "";
      while (j < n) {
        if (src[j] === '"') {
          if (src[j + 1] === '"') { out += '"'; j += 2; }
          else break;
        } else { out += src[j]; j++; }
      }
      if (j >= n) throw new Error("string");
      tokens.push({ t: "str", v: out });
      i = j + 1;
      continue;
    }
    if (c === "'") {
      // Quoted sheet name: 'Mon Sheet'!A1
      let j = i + 1;
      let out = "";
      while (j < n && src[j] !== "'") { out += src[j]; j++; }
      if (j >= n) throw new Error("sheet");
      tokens.push({ t: "sheet", v: out });
      i = j + 1;
      continue;
    }
    const numMatch = /^[0-9]+(?:[.,][0-9]+)?/.exec(src.slice(i));
    if (numMatch && (sep === ";" || !numMatch[0].includes(","))) {
      tokens.push({ t: "num", v: numMatch[0].replace(",", ".") });
      i += numMatch[0].length;
      continue;
    }
    const absRef = /^\$?[A-Za-z]{1,3}\$?[0-9]+/.exec(src.slice(i));
    if (absRef) {
      tokens.push({ t: "ref", v: absRef[0] });
      i += absRef[0].length;
      continue;
    }
    const two = src.slice(i, i + 2);
    if (["<=", ">=", "<>"].includes(two)) {
      tokens.push({ t: "op", v: two === "<>" ? "!=" : two });
      i += 2;
      continue;
    }
    if ("+-*/^%=()<>,;:&!".includes(c)) {
      tokens.push({ t: c === "," || c === ";" ? "sep" : "op", v: c });
      i++;
      continue;
    }
    const word = /^[A-Za-zÀ-ÿ_][A-Za-zÀ-ÿ0-9_.]*/.exec(src.slice(i));
    if (word) {
      const w = word[0];
      // A bare cell reference (A1, $B$12) — but not TRUE/FALSE yet (bools).
      if (/^\$?[A-Za-z]{1,3}\$?[0-9]+$/.test(w)) {
        tokens.push({ t: "ref", v: w });
      } else {
        const up = w.toUpperCase();
        if (up === "TRUE" || up === "VRAI") tokens.push({ t: "bool", v: true });
        else if (up === "FALSE" || up === "FAUX") tokens.push({ t: "bool", v: false });
        else tokens.push({ t: "word", v: w });
      }
      i += w.length;
      continue;
    }
    throw new Error("char:" + c);
  }
  return tokens;
}

// ── Parser (recursive descent, builds a small AST) ─────────────────────────

function makeParser(tokens, sep) {
  let pos = 0;
  const peek = () => tokens[pos] || null;
  const next = () => tokens[pos++] || null;
  const expectOp = (v) => {
    const t = next();
    if (!t || t.t !== "op" || t.v !== v) throw new Error("expected " + v);
  };
  const expectSep = () => {
    const t = next();
    if (!t || t.t !== "sep") throw new Error("expected separator");
  };

  function parseExpr() { return parseComparison(); }
  function parseComparison() {
    let left = parseConcat();
    for (;;) {
      const t = peek();
      if (!t || t.t !== "op" || !["=", "!=", "<", "<=", ">", ">="].includes(t.v)) return left;
      next();
      left = [t.v, left, parseConcat()];
    }
  }
  function parseConcat() {
    let left = parseAdd();
    for (;;) {
      const t = peek();
      if (!t || t.t !== "op" || t.v !== "&") return left;
      next();
      left = ["&", left, parseAdd()];
    }
  }
  function parseAdd() {
    let left = parseMul();
    for (;;) {
      const t = peek();
      if (!t || t.t !== "op" || (t.v !== "+" && t.v !== "-")) return left;
      next();
      left = [t.v, left, parseMul()];
    }
  }
  function parseMul() {
    let left = parsePower();
    for (;;) {
      const t = peek();
      if (!t || t.t !== "op" || (t.v !== "*" && t.v !== "/")) return left;
      next();
      left = [t.v, left, parsePower()];
    }
  }
  // Excel-compatible: a leading sign binds to the base (-2^2 = (-2)^2 = 4),
  // and to the exponent on the right (2^-2).
  function parseSigned() {
    let neg = false;
    for (;;) {
      const t = peek();
      if (t && t.t === "op" && (t.v === "+" || t.v === "-")) {
        if (t.v === "-") neg = !neg;
        next();
      } else break;
    }
    const node = parsePostfix();
    return neg ? ["neg", node] : node;
  }
  function parsePower() {
    const base = parseSigned();
    const t = peek();
    if (t && t.t === "op" && t.v === "^") {
      next();
      return ["^", base, parseSigned()];
    }
    return base;
  }
  function parsePostfix() {
    let node = parsePrimary();
    for (;;) {
      const t = peek();
      if (t && t.t === "op" && t.v === "%") { next(); node = ["%", node]; }
      else return node;
    }
  }
  function parsePrimary() {
    const t = next();
    if (!t) throw new Error("empty");
    if (t.t === "num") return ["n", Number(t.v)];
    if (t.t === "str") return ["s", t.v];
    if (t.t === "bool") return ["b", t.v];
    if (t.t === "ref") {
      const after = peek();
      if (after && after.t === "op" && after.v === ":") {
        next();
        const t2 = next();
        if (!t2 || t2.t !== "ref") throw new Error("range");
        return ["range", normRef(t.v), normRef(t2.v)];
      }
      return ["ref", normRef(t.v)];
    }
    if (t.t === "word" || t.t === "sheet") {
      const isSheet = t.t === "sheet";
      let name = t.v;
      if (!isSheet) {
        // Unquoted sheet name: Name!A1
        const bang = peek();
        if (bang && bang.t === "op" && bang.v === "!") {
          next();
        } else {
          // Function call?
          const paren = peek();
          if (paren && paren.t === "op" && paren.v === "(") {
            next();
            const args = [];
            const closing = peek();
            if (!(closing && closing.t === "op" && closing.v === ")")) {
              for (;;) {
                args.push(parseExpr());
                const s = peek();
                if (s && s.t === "sep") { next(); continue; }
                break;
              }
            }
            expectOp(")");
            return ["call", canonName(name), args];
          }
          throw new Error("name:" + name);
        }
      } else {
        const bang = peek();
        if (!bang || bang.t !== "op" || bang.v !== "!") throw new Error("sheetref");
        next();
      }
      const r1 = next();
      if (!r1 || r1.t !== "ref") throw new Error("sheetref");
      const colon = peek();
      if (colon && colon.t === "op" && colon.v === ":") {
        next();
        const r2 = next();
        if (!r2 || r2.t !== "ref") throw new Error("sheetrange");
        return ["sheetrange", name, normRef(r1.v), normRef(r2.v)];
      }
      return ["sheetref", name, normRef(r1.v)];
    }
    if (t.t === "op" && t.v === "(") {
      const e = parseExpr();
      expectOp(")");
      return e;
    }
    throw new Error("primary");
  }
  function normRef(r) {
    return r.replace(/\$/g, "").toUpperCase();
  }
  function canonName(name) {
    const up = String(name).toUpperCase();
    return ALIASES[up] || up;
  }
  return { parseExpr, atEnd: () => pos >= tokens.length };
}

export function parseFormula(text) {
  const body = String(text || "").trim().replace(/^[=@]/, "");
  const sep = body.includes(";") ? ";" : ",";
  // A comma is a decimal mark (FR style) when ';' is the separator, or when
  // the expression holds no letters at all ("=1,5+1" can only mean 1.5 + 1).
  const commaDecimal = sep === ";" || !/[A-Za-zÀ-ÿ]/.test(body);
  const { parseExpr, atEnd } = makeParser(tokenize(body, commaDecimal ? ";" : sep), sep);
  const ast = parseExpr();
  if (!atEnd()) throw new Error("trailing");
  return ast;
}

// ── Evaluator ───────────────────────────────────────────────────────────────

/** Engine scope: resolve raw cell values + locale + cycle guard. */
export function createScope({ readRaw, sheet, locale = "en", seen = null } = {}) {
  const scope = {
    sheet: sheet || "",
    locale: locale === "fr" ? "fr" : "en",
    seen: seen || new Set(),
    memo: new Map(),
    /** Engine value of `sheet!ref` (formulas recurse, cycles guarded). */
    getCell(targetSheet, ref) {
      const key = `${targetSheet || scope.sheet}!${String(ref).toUpperCase()}`;
      const raw = readRaw ? readRaw(targetSheet || scope.sheet, ref) : null;
      return evalRefValue(raw, scope, key);
    },
  };
  return scope;
}

const err = (code) => ({ t: "e", code });
const isErr = (v) => v && v.t === "e";
const isBlank = (v) => !v || v.t === "blank";

function toNumber(v) {
  if (!v || v.t === "blank") return 0;
  if (v.t === "n") return v.v;
  if (v.t === "b") return v.v ? 1 : 0;
  if (v.t === "s") {
    const s = v.v.trim().replace(",", ".");
    if (s === "") return 0;
    const n = Number(s);
    return Number.isFinite(n) ? n : null;
  }
  return null;
}

function toText(v, locale) {
  if (!v || v.t === "blank") return "";
  if (v.t === "s") return v.v;
  if (v.t === "n") return numText(v.v);
  if (v.t === "b") return locale === "fr" ? (v.v ? "VRAI" : "FAUX") : v.v ? "TRUE" : "FALSE";
  if (v.t === "link") return v.label;
  return "";
}

export function numText(n) {
  if (!Number.isFinite(n)) return String(n);
  if (Number.isInteger(n) && Math.abs(n) < 1e15) return String(n);
  return String(parseFloat(n.toPrecision(10)));
}

function flattenArgs(vals) {
  const out = [];
  const walk = (v) => {
    if (v && v.t === "a") v.rows.forEach((r) => r.forEach(walk));
    else out.push(v);
  };
  vals.forEach(walk);
  return out;
}

function evalRefValue(raw, scope, key) {
  // Raw storage text → engine value (formulas recurse, cycles guarded).
  if (raw === null || raw === undefined) return { t: "blank" };
  if (typeof raw === "number") return { t: "n", v: raw };
  if (typeof raw === "boolean") return { t: "b", v: raw };
  const s = String(raw);
  if (scope.memo.has(key)) return scope.memo.get(key);
  let out;
  if (isFormulaText(s)) {
    if (scope.seen.has(key)) {
      out = err("CYCLE");
    } else {
      scope.seen.add(key);
      try {
        out = evaluate(parseFormula(s), scope);
      } catch (_) {
        out = err("NAME");
      }
      scope.seen.delete(key);
    }
  } else if (s.trim() === "") {
    out = { t: "blank" };
  } else {
    const n = Number(s.trim().replace(",", "."));
    out = s.trim() !== "" && Number.isFinite(n) && /^[-+0-9.,\s]+$/.test(s.trim())
      ? { t: "n", v: n }
      : { t: "s", v: s };
  }
  scope.memo.set(key, out);
  return out;
}

function rangeValues(r1, r2, scope) {
  const a = parseA1(r1);
  const b = parseA1(r2);
  if (!a || !b) return err("REF");
  const rStart = Math.min(a.row, b.row);
  const rEnd = Math.max(a.row, b.row);
  const cStart = Math.min(a.col, b.col);
  const cEnd = Math.max(a.col, b.col);
  if ((rEnd - rStart + 1) * (cEnd - cStart + 1) > 100000) return err("VALUE");
  const rows = [];
  for (let r = rStart; r <= rEnd; r++) {
    const line = [];
    for (let c = cStart; c <= cEnd; c++) {
      const ref = indexToCol(c) + r;
      line.push(scope.getCell(scope.sheet, ref));
    }
    rows.push(line);
  }
  return { t: "a", rows };
}

function firstOf(v) {
  if (v && v.t === "a") {
    for (const row of v.rows) for (const cell of row) {
      if (cell && cell.t !== "blank") return cell;
    }
    return { t: "blank" };
  }
  return v;
}

function cmpValues(a, b) {
  // Excel-ish: numbers < text; blank sorts with text "".
  const rank = (v) => (!v || v.t === "blank" ? 0 : v.t === "n" ? 1 : v.t === "b" ? 2 : 3);
  const ra = rank(a);
  const rb = rank(b);
  if (ra !== rb) return ra - rb;
  if (ra === 1) return a.v - b.v;
  if (ra === 2) return (a.v ? 1 : 0) - (b.v ? 1 : 0);
  const sa = toText(a);
  const sb = toText(b);
  return sa < sb ? -1 : sa > sb ? 1 : 0;
}

const FUNCTIONS = {
  SUM: (vals) => num(flattenArgs(vals).reduce((acc, v) => {
    if (isBlank(v)) return acc;
    const n = toNumber(v);
    return n === null ? acc : acc + n;
  }, 0)),
  AVERAGE: (vals, scope) => {
    const nums = flattenArgs(vals).map(toNumber).filter((n) => n !== null);
    if (!nums.length) return err("DIV");
    return num(nums.reduce((a, b) => a + b, 0) / nums.length);
  },
  MIN: (vals) => {
    const nums = flattenArgs(vals).map(toNumber).filter((n) => n !== null);
    return nums.length ? num(Math.min(...nums)) : num(0);
  },
  MAX: (vals) => {
    const nums = flattenArgs(vals).map(toNumber).filter((n) => n !== null);
    return nums.length ? num(Math.max(...nums)) : num(0);
  },
  COUNT: (vals) => num(flattenArgs(vals).filter((v) => v && v.t === "n").length),
  COUNTA: (vals) => num(flattenArgs(vals).filter((v) => !isBlank(v)).length),
  PRODUCT: (vals) => num(flattenArgs(vals).reduce((acc, v) => {
    if (isBlank(v)) return acc;
    const n = toNumber(v);
    return n === null ? acc : acc * n;
  }, 1)),
  MEDIAN: (vals) => {
    const nums = flattenArgs(vals).map(toNumber).filter((n) => n !== null).sort((a, b) => a - b);
    if (!nums.length) return err("DIV");
    const m = Math.floor(nums.length / 2);
    return num(nums.length % 2 ? nums[m] : (nums[m - 1] + nums[m]) / 2);
  },
  ABS: ([v]) => {
    const n = toNumber(firstOf(v));
    return n === null ? err("VALUE") : num(Math.abs(n));
  },
  ROUND: ([v, d]) => {
    const n = toNumber(firstOf(v));
    const digits = d === undefined ? 0 : toNumber(firstOf(d));
    if (n === null || digits === null) return err("VALUE");
    const f = 10 ** Math.trunc(digits);
    return num(Math.round(n * f) / f);
  },
  INT: ([v]) => {
    const n = toNumber(firstOf(v));
    return n === null ? err("VALUE") : num(Math.floor(n));
  },
  SQRT: ([v]) => {
    const n = toNumber(firstOf(v));
    if (n === null) return err("VALUE");
    if (n < 0) return err("VALUE");
    return num(Math.sqrt(n));
  },
  POWER: ([a, b]) => {
    const x = toNumber(firstOf(a));
    const y = toNumber(firstOf(b));
    if (x === null || y === null) return err("VALUE");
    const r = x ** y;
    return Number.isFinite(r) ? num(r) : err("VALUE");
  },
  MOD: ([a, b]) => {
    const x = toNumber(firstOf(a));
    const y = toNumber(firstOf(b));
    if (x === null || y === null || y === 0) return y === 0 ? err("DIV") : err("VALUE");
    return num(((x % y) + y) % y);
  },
  CONCAT: (vals, scope) => ({ t: "s", v: flattenArgs(vals).map((v) => toText(v, scope.locale)).join("") }),
  UPPER: ([v], scope) => ({ t: "s", v: toText(firstOf(v), scope.locale).toUpperCase() }),
  LOWER: ([v], scope) => ({ t: "s", v: toText(firstOf(v), scope.locale).toLowerCase() }),
  LEN: ([v], scope) => num([...toText(firstOf(v), scope.locale)].length),
  TRIM: ([v], scope) => ({ t: "s", v: toText(firstOf(v), scope.locale).trim().replace(/\s+/g, " ") }),
  LEFT: ([v, n]) => {
    const s = toText(firstOf(v), "en");
    const k = n === undefined ? 1 : toNumber(firstOf(n));
    if (k === null) return err("VALUE");
    return { t: "s", v: s.slice(0, Math.max(0, Math.trunc(k))) };
  },
  RIGHT: ([v, n]) => {
    const s = toText(firstOf(v), "en");
    const k = n === undefined ? 1 : toNumber(firstOf(n));
    if (k === null) return err("VALUE");
    return { t: "s", v: k <= 0 ? "" : s.slice(-Math.trunc(k)) };
  },
  MID: ([v, start, len]) => {
    const s = toText(firstOf(v), "en");
    const a = toNumber(firstOf(start));
    const k = len === undefined ? s.length : toNumber(firstOf(len));
    if (a === null || k === null || a < 1) return err("VALUE");
    return { t: "s", v: s.slice(Math.trunc(a) - 1, Math.trunc(a) - 1 + Math.max(0, Math.trunc(k))) };
  },
  TODAY: () => ({ t: "s", v: new Date().toLocaleDateString() }),
  NOW: () => ({ t: "s", v: new Date().toLocaleString() }),
  DAY: ([v]) => datePart(v, "day"),
  MONTH: ([v]) => datePart(v, "month"),
  YEAR: ([v]) => datePart(v, "year"),
  NOT: ([v]) => ({ t: "b", v: !toBool(firstOf(v)) }),
  HYPERLINK: ([url, label], scope) => {
    const u = toText(firstOf(url), scope.locale).trim();
    if (!/^https?:\/\//i.test(u)) return err("VALUE");
    const text = label === undefined ? u : toText(firstOf(label), scope.locale);
    return { t: "link", url: u, label: text };
  },
};

function num(n) {
  return { t: "n", v: n };
}

function toBool(v) {
  if (!v || v.t === "blank") return false;
  if (v.t === "b") return v.v;
  if (v.t === "n") return v.v !== 0;
  const s = v.t === "s" ? v.v.trim().toUpperCase() : "";
  if (["TRUE", "VRAI", "1", "YES", "OUI"].includes(s)) return true;
  if (["FALSE", "FAUX", "0", "", "NO", "NON"].includes(s)) return false;
  return s !== "";
}

function datePart(v, part) {
  const first = firstOf(v);
  let d = null;
  if (first && first.t === "s") {
    const parsed = new Date(first.v);
    if (!Number.isNaN(parsed.getTime())) d = parsed;
  } else if (first && first.t === "n") {
    // Excel serial date (1900 system).
    const base = Date.UTC(1899, 11, 30);
    d = new Date(base + Math.trunc(first.v) * 86400000);
  }
  if (!d) return err("VALUE");
  if (part === "day") return num(d.getDate());
  if (part === "month") return num(d.getMonth() + 1);
  return num(d.getFullYear());
}

function callFunction(name, argNodes, scope) {
  if (name === "IF") {
    if (argNodes.length < 2 || argNodes.length > 3) return err("NAME");
    const cond = evaluate(argNodes[0], scope);
    if (isErr(cond)) return cond;
    const branch = toBool(cond) ? argNodes[1] : argNodes[2];
    if (!branch) return { t: "b", v: false };
    return evaluate(branch, scope);
  }
  if (name === "AND" || name === "OR") {
    let out = name === "AND";
    for (const node of argNodes) {
      const v = evaluate(node, scope);
      if (isErr(v)) return v;
      const b = toBool(v);
      if (name === "AND" && !b) return { t: "b", v: false };
      if (name === "OR" && b) return { t: "b", v: true };
      out = b;
    }
    return { t: "b", v: out };
  }
  if (name === "IFERROR") {
    if (!argNodes.length || argNodes.length > 2) return err("NAME");
    const v = evaluate(argNodes[0], scope);
    if (!isErr(v)) return v;
    if (v.code === "CYCLE" || v.code === "CIRC") return v;
    return argNodes[1] ? evaluate(argNodes[1], scope) : { t: "blank" };
  }
  const fn = FUNCTIONS[name];
  if (!fn) return err("NAME");
  const vals = argNodes.map((node) => evaluate(node, scope));
  for (const v of vals) {
    if (isErr(v)) {
      // Errors propagate, except through aggregations over ranges.
      if (["SUM", "AVERAGE", "MIN", "MAX", "COUNT", "COUNTA", "PRODUCT", "MEDIAN", "CONCAT"].includes(name)) {
        continue;
      }
      return v;
    }
  }
  return fn(vals, scope);
}

export function evaluate(node, scope) {
  if (Array.isArray(node)) {
    const [kind, ...rest] = node;
    switch (kind) {
      case "n": return { t: "n", v: rest[0] };
      case "s": return { t: "s", v: rest[0] };
      case "b": return { t: "b", v: rest[0] };
      case "neg": {
        const v = firstOf(evaluate(rest[0], scope));
        if (isErr(v)) return v;
        const n = toNumber(v);
        return n === null ? err("VALUE") : num(-n);
      }
      case "%": {
        const v = firstOf(evaluate(rest[0], scope));
        if (isErr(v)) return v;
        const n = toNumber(v);
        return n === null ? err("VALUE") : num(n / 100);
      }
      case "ref": {
        const p = parseA1(rest[0]);
        if (!p) return err("REF");
        return scope.getCell(scope.sheet, indexToCol(p.col) + p.row);
      }
      case "range": return rangeValues(rest[0], rest[1], scope);
      case "sheetref": {
        const [, sheet, ref] = node;
        const p = parseA1(ref);
        if (!p) return err("REF");
        return scope.getCell(sheet, indexToCol(p.col) + p.row);
      }
      case "sheetrange": {
        const [, sheet, r1, r2] = node;
        const a = parseA1(r1);
        const b = parseA1(r2);
        if (!a || !b) return err("REF");
        const rows = [];
        for (let r = Math.min(a.row, b.row); r <= Math.max(a.row, b.row); r++) {
          const line = [];
          for (let c = Math.min(a.col, b.col); c <= Math.max(a.col, b.col); c++) {
            line.push(scope.getCell(sheet, indexToCol(c) + r));
          }
          rows.push(line);
        }
        return { t: "a", rows };
      }
      case "call": return callFunction(rest[0], rest[1], scope);
      case "+":
      case "-":
      case "*":
      case "/":
      case "^":
      case "&": {
        const l = firstOf(evaluate(rest[0], scope));
        const r = firstOf(evaluate(rest[1], scope));
        if (isErr(l)) return l;
        if (isErr(r)) return r;
        if (kind === "&") return { t: "s", v: toText(l, scope.locale) + toText(r, scope.locale) };
        const a = toNumber(l);
        const b = toNumber(r);
        if (a === null || b === null) return err("VALUE");
        if (kind === "+") return num(a + b);
        if (kind === "-") return num(a - b);
        if (kind === "*") return num(a * b);
        if (kind === "/") return b === 0 ? err("DIV") : num(a / b);
        if (kind === "^") {
          const v = a ** b;
          return Number.isFinite(v) ? num(v) : err("VALUE");
        }
        return err("VALUE");
      }
      case "=":
      case "!=":
      case "<":
      case "<=":
      case ">":
      case ">=": {
        const l = firstOf(evaluate(rest[0], scope));
        const r = firstOf(evaluate(rest[1], scope));
        if (isErr(l)) return l;
        if (isErr(r)) return r;
        const c = cmpValues(l, r);
        const out = kind === "=" ? c === 0 : kind === "!=" ? c !== 0
          : kind === "<" ? c < 0 : kind === "<=" ? c <= 0
          : kind === ">" ? c > 0 : c >= 0;
        return { t: "b", v: out };
      }
      default:
        return err("NAME");
    }
  }
  return err("NAME");
}

/** Render an evaluated value for display (localized booleans/errors). */
export function displayValue(v, locale = "en") {
  const codes = errorCodes(locale);
  if (!v || v.t === "blank") return "";
  if (v.t === "n") return numText(v.v);
  if (v.t === "s") return v.v;
  if (v.t === "b") return locale === "fr" ? (v.v ? "VRAI" : "FAUX") : v.v ? "TRUE" : "FALSE";
  if (v.t === "link") return v.label;
  if (v.t === "e") {
    if (v.code === "CYCLE" || v.code === "CIRC") {
      return locale === "fr" ? codes.CYCLE : codes.CIRC;
    }
    return codes[v.code] || codes.VALUE;
  }
  if (v.t === "a") {
    for (const row of v.rows) for (const cell of row) {
      if (cell && cell.t !== "blank") return displayValue(cell, locale);
    }
    return "";
  }
  return "";
}