web/src/customer-grid/formulaEngine.ts

// ---------------------------------------------------------------------------
// customer-grid / formulaEngine.ts
// Wave-5 I-B, grown to EXCEL GRADE 2026-07-31 (owner item 2). A pure module:
// no React, no DOM, no imports from the rest of the grid, so it is testable
// under plain node and portable into the shell unchanged.
//
// GRAMMAR (v2 — the shared contract; v1 remains a strict subset):
//   numbers          1  0.5  .5  1200.75          (no separators, no exponents)
//   strings          "text"  with "" escaping a quote — Excel's own rule
//   booleans         TRUE  FALSE                  (bare constants)
//   field refs       {revenue_ytd}                (slug charset [A-Za-z0-9_])
//   arithmetic       + - * / ^ ( )                (usual precedence; ^ is
//                    right-associative and UNARY MINUS BINDS TIGHTER, Excel's
//                    famous quirk: -2^2 = 4)
//   text             &                            (concatenation; blank -> "")
//   comparisons      < <= > >= = != (== and <> accepted) — numbers with
//                    numbers, text with text (case-insensitive, Excel's rule);
//                    a mixed or blank comparison is BLANK, never a guess.
//   functions        numeric  ABS ROUND ROUNDUP ROUNDDOWN INT MOD SQRT POWER
//                             EXP LN SUM AVERAGE COUNT COUNTA MIN MAX
//                    logic    IF AND OR NOT IFERROR ISBLANK
//                    text     CONCATENATE/CONCAT LEFT RIGHT MID LEN TRIM
//                             UPPER LOWER PROPER VALUE TEXT
//                    date     TODAY YEAR MONTH DAY DAYS      (ISO YYYY-MM-DD)
//
// THE ONE RULE, unchanged: **any error → BLANK, never a wrong number.** An
// unknown ref, a divide-by-zero, a boolean fed to arithmetic, an unreadable
// date, an unknown TEXT() format — every one yields null, and null propagates
// through ARITHMETIC. Inventing 0 for a missing value is how a wrong number
// gets a confident face; this engine refuses instead.
//
// Where v2 deliberately follows EXCEL rather than v1's stricter reading:
//   - THE AGGREGATES SKIP BLANKS. SUM/AVERAGE/COUNT/COUNTA/MIN/MAX ignore
//     blank arguments exactly as Excel ignores empty cells — that selectivity
//     is the entire reason the aggregate forms exist beside `+`. (v1's
//     MIN/MAX-propagate-blank rule is superseded; `+` still propagates.)
//   - TEXT CONTEXT COERCES BLANK TO "". `{a} & {b}` with b blank is a, as in
//     Excel. Text is display, not arithmetic — "" states nothing false.
//   - A BOOLEAN RESULT DISPLAYS AS TRUE/FALSE (v1 blanked it). A comparison
//     the user typed deserves its answer on screen.
//   - FORMULA-OVER-FORMULA IS ALLOWED. Evaluation is topological
//     (`orderFormulas`); a CYCLE refuses at authoring time and every member
//     of one evaluates to blank — never a stale or half-updated number.
//   - IF's condition must still be a real BOOLEAN (no numeric truthiness):
//     this is a financial surface, and 0-means-false is a silent wrong branch.
//   - ROUND stays HALF-AWAY-FROM-ZERO (Excel's convention too).
// ---------------------------------------------------------------------------

/** Source-length cap. The host's structural validation mirrors it; both sides
 *  refuse rather than truncate (a truncated formula is a different formula). */
export const MAX_FORMULA_LENGTH = 500;
/** Token cap — a bounded tree for something re-evaluated per row per render. */
export const MAX_FORMULA_TOKENS = 200;
/** Parse-depth cap (nested parens/calls). */
export const MAX_FORMULA_DEPTH = 24;

export type FormulaAst =
  | { t: "num"; v: number }
  | { t: "str"; v: string }
  | { t: "bool"; v: boolean }
  | { t: "ref"; k: string }
  | { t: "neg"; e: FormulaAst }
  | { t: "bin"; op: "+" | "-" | "*" | "/" | "^" | "&"; l: FormulaAst; r: FormulaAst }
  | { t: "cmp"; op: "<" | "<=" | ">" | ">=" | "=" | "!="; l: FormulaAst; r: FormulaAst }
  | { t: "call"; fn: FormulaFn; args: FormulaAst[] };

export type FormulaFn =
  | "ABS" | "ROUND" | "ROUNDUP" | "ROUNDDOWN" | "INT" | "MOD" | "SQRT" | "POWER"
  | "EXP" | "LN" | "SUM" | "AVERAGE" | "COUNT" | "COUNTA" | "MIN" | "MAX"
  | "IF" | "AND" | "OR" | "NOT" | "IFERROR" | "ISBLANK"
  | "CONCATENATE" | "LEFT" | "RIGHT" | "MID" | "LEN" | "TRIM"
  | "UPPER" | "LOWER" | "PROPER" | "VALUE" | "TEXT"
  | "TODAY" | "YEAR" | "MONTH" | "DAY" | "DAYS";

const MANY = 30; // variadic cap — bounded, far above any honest use

const FN_ARITY: Record<FormulaFn, [number, number]> = {
  ABS: [1, 1],
  ROUND: [1, 2],
  ROUNDUP: [1, 2],
  ROUNDDOWN: [1, 2],
  INT: [1, 1],
  MOD: [2, 2],
  SQRT: [1, 1],
  POWER: [2, 2],
  EXP: [1, 1],
  LN: [1, 1],
  SUM: [1, MANY],
  AVERAGE: [1, MANY],
  COUNT: [1, MANY],
  COUNTA: [1, MANY],
  MIN: [1, MANY],
  MAX: [1, MANY],
  IF: [2, 3],
  AND: [1, MANY],
  OR: [1, MANY],
  NOT: [1, 1],
  IFERROR: [2, 2],
  ISBLANK: [1, 1],
  CONCATENATE: [1, MANY],
  LEFT: [1, 2],
  RIGHT: [1, 2],
  MID: [3, 3],
  LEN: [1, 1],
  TRIM: [1, 1],
  UPPER: [1, 1],
  LOWER: [1, 1],
  PROPER: [1, 1],
  VALUE: [1, 1],
  TEXT: [2, 2],
  TODAY: [0, 0],
  YEAR: [1, 1],
  MONTH: [1, 1],
  DAY: [1, 1],
  DAYS: [2, 2],
};

/** Excel spells it CONCAT in modern versions; both names, one function. */
const FN_ALIASES: Record<string, FormulaFn> = { CONCAT: "CONCATENATE" };

export type ParseResult =
  | { ok: true; ast: FormulaAst; refs: string[] }
  | { ok: false; error: string };

type Token =
  | { t: "num"; v: number }
  | { t: "str"; v: string }
  | { t: "ref"; k: string }
  | { t: "ident"; v: string }
  | { t: "op"; v: string };

const CMP_OPS = new Set(["<", "<=", ">", ">=", "=", "!="]);

function tokenize(src: string): Token[] | string {
  const out: Token[] = [];
  let i = 0;
  while (i < src.length) {
    const c = src[i];
    if (c === " " || c === "\t" || c === "\n" || c === "\r") {
      i += 1;
      continue;
    }
    // {field_key}
    if (c === "{") {
      const end = src.indexOf("}", i + 1);
      if (end < 0) return "a field reference is missing its closing }";
      const k = src.slice(i + 1, end).trim();
      if (!/^[A-Za-z0-9_]+$/.test(k))
        return k === ""
          ? "empty field reference {}"
          : `field reference {${k}} may only contain letters, digits and _`;
      out.push({ t: "ref", k });
      i = end + 1;
      continue;
    }
    // "string" — Excel's own escape: "" inside a string is one quote
    if (c === '"') {
      let j = i + 1;
      let s = "";
      for (;;) {
        if (j >= src.length) return "a text value is missing its closing quote";
        if (src[j] === '"') {
          if (src[j + 1] === '"') {
            s += '"';
            j += 2;
            continue;
          }
          break;
        }
        s += src[j];
        j += 1;
      }
      out.push({ t: "str", v: s });
      i = j + 1;
      continue;
    }
    // number: 1  1.5  .5   (a trailing dot — "5." — is refused)
    if (/[0-9]/.test(c) || (c === "." && /[0-9]/.test(src[i + 1] ?? ""))) {
      const m = /^(?:[0-9]+(?:\.[0-9]+)?|\.[0-9]+)/.exec(src.slice(i));
      if (!m) return `unreadable number at "${src.slice(i, i + 8)}"`;
      if (/^[0-9.]/.test(src[i + m[0].length] ?? ""))
        return `unreadable number at "${src.slice(i, i + 8)}"`;
      out.push({ t: "num", v: Number(m[0]) });
      i += m[0].length;
      continue;
    }
    // function names / TRUE / FALSE
    if (/[A-Za-z_]/.test(c)) {
      const m = /^[A-Za-z_][A-Za-z0-9_]*/.exec(src.slice(i))!;
      out.push({ t: "ident", v: m[0] });
      i += m[0].length;
      continue;
    }
    // operators — two-char first. `<>` is Excel's not-equal; `==` tolerated as `=`.
    const two = src.slice(i, i + 2);
    if (two === "<=" || two === ">=" || two === "!=" || two === "==" || two === "<>") {
      out.push({ t: "op", v: two === "==" ? "=" : two === "<>" ? "!=" : two });
      i += 2;
      continue;
    }
    if ("+-*/^&(),<>=".includes(c)) {
      out.push({ t: "op", v: c });
      i += 1;
      continue;
    }
    return `unexpected character "${c}"`;
  }
  return out;
}

/**
 * Recursive-descent parser. Grammar, lowest binding first:
 *   expr    := concat ((< <= > >= = !=) concat)?     -- ONE comparison, no chains
 *   concat  := additive (& additive)*                -- Excel: & binds below + -
 *   additive:= mult ((+|-) mult)*
 *   mult    := power ((*|/) power)*
 *   power   := unary (^ power)?                      -- right-assoc; unary minus
 *                                                       inside, so -2^2 = (-2)^2
 *   unary   := "-" unary | primary
 *   primary := number | string | TRUE | FALSE | ref | "(" expr ")" | FN "(" … ")"
 */
export function parseFormula(src: string): ParseResult {
  if (typeof src !== "string" || src.trim() === "")
    return { ok: false, error: "the formula is empty" };
  if (src.length > MAX_FORMULA_LENGTH)
    return { ok: false, error: `formula longer than ${MAX_FORMULA_LENGTH} characters` };
  const toks = tokenize(src);
  if (typeof toks === "string") return { ok: false, error: toks };
  if (toks.length === 0) return { ok: false, error: "the formula is empty" };
  if (toks.length > MAX_FORMULA_TOKENS)
    return { ok: false, error: `formula has more than ${MAX_FORMULA_TOKENS} tokens` };

  let pos = 0;
  const peek = (): Token | undefined => toks[pos];
  const isOp = (v: string): boolean => {
    const t = toks[pos];
    return t?.t === "op" && t.v === v;
  };
  let err: string | null = null;
  const fail = (message: string): null => {
    if (err === null) err = message;
    return null;
  };

  function parseExpr(depth: number): FormulaAst | null {
    if (depth > MAX_FORMULA_DEPTH) return fail("formula is nested too deeply");
    const left = parseConcat(depth);
    if (!left) return null;
    const t = peek();
    if (t?.t === "op" && CMP_OPS.has(t.v)) {
      pos += 1;
      const right = parseConcat(depth);
      if (!right) return null;
      const again = peek();
      if (again?.t === "op" && CMP_OPS.has(again.v))
        return fail("comparisons cannot be chained (a < b < c)");
      return { t: "cmp", op: t.v as "<", l: left, r: right };
    }
    return left;
  }

  function parseConcat(depth: number): FormulaAst | null {
    let node = parseAdditive(depth);
    if (!node) return null;
    while (isOp("&")) {
      pos += 1;
      const r = parseAdditive(depth);
      if (!r) return null;
      node = { t: "bin", op: "&", l: node, r };
    }
    return node;
  }

  function parseAdditive(depth: number): FormulaAst | null {
    let node = parseMult(depth);
    if (!node) return null;
    for (;;) {
      if (isOp("+") || isOp("-")) {
        const op = (peek() as { v: "+" | "-" }).v;
        pos += 1;
        const r = parseMult(depth);
        if (!r) return null;
        node = { t: "bin", op, l: node, r };
      } else return node;
    }
  }

  function parseMult(depth: number): FormulaAst | null {
    let node = parsePower(depth);
    if (!node) return null;
    for (;;) {
      if (isOp("*") || isOp("/")) {
        const op = (peek() as { v: "*" | "/" }).v;
        pos += 1;
        const r = parsePower(depth);
        if (!r) return null;
        node = { t: "bin", op, l: node, r };
      } else return node;
    }
  }

  function parsePower(depth: number): FormulaAst | null {
    if (depth > MAX_FORMULA_DEPTH) return fail("formula is nested too deeply");
    const base = parseUnary(depth);
    if (!base) return null;
    if (isOp("^")) {
      pos += 1;
      const exp = parsePower(depth + 1); // right-associative: 2^3^2 = 2^(3^2)
      if (!exp) return null;
      return { t: "bin", op: "^", l: base, r: exp };
    }
    return base;
  }

  function parseUnary(depth: number): FormulaAst | null {
    if (depth > MAX_FORMULA_DEPTH) return fail("formula is nested too deeply");
    if (isOp("-")) {
      pos += 1;
      const e = parseUnary(depth + 1);
      return e ? { t: "neg", e } : null;
    }
    return parsePrimary(depth);
  }

  function parsePrimary(depth: number): FormulaAst | null {
    if (depth > MAX_FORMULA_DEPTH) return fail("formula is nested too deeply");
    const t = peek();
    if (!t) return fail("the formula ends unexpectedly");
    if (t.t === "num") {
      pos += 1;
      return { t: "num", v: t.v };
    }
    if (t.t === "str") {
      pos += 1;
      return { t: "str", v: t.v };
    }
    if (t.t === "ref") {
      pos += 1;
      return { t: "ref", k: t.k };
    }
    if (t.t === "op" && t.v === "(") {
      pos += 1;
      const inner = parseExpr(depth + 1);
      if (!inner) return null;
      if (!isOp(")")) return fail("missing closing parenthesis");
      pos += 1;
      return inner;
    }
    if (t.t === "ident") {
      const upper = t.v.toUpperCase();
      if (upper === "TRUE" || upper === "FALSE") {
        pos += 1;
        return { t: "bool", v: upper === "TRUE" };
      }
      const fn = (FN_ALIASES[upper] ?? upper) as FormulaFn;
      if (!(fn in FN_ARITY))
        return fail(`unknown function ${upper} — see the formula help for what is available`);
      pos += 1;
      if (!isOp("(")) return fail(`${fn} must be called with parentheses: ${fn}(…)`);
      pos += 1;
      const args: FormulaAst[] = [];
      if (!isOp(")")) {
        for (;;) {
          const a = parseExpr(depth + 1);
          if (!a) return null;
          args.push(a);
          if (isOp(",")) {
            pos += 1;
            continue;
          }
          break;
        }
      }
      if (!isOp(")")) return fail(`missing closing parenthesis on ${fn}(…)`);
      pos += 1;
      const [lo, hi] = FN_ARITY[fn];
      if (args.length < lo || args.length > hi)
        return fail(
          lo === hi
            ? `${fn} takes exactly ${lo} argument${lo === 1 ? "" : "s"}`
            : `${fn} takes ${lo} to ${hi} arguments`
        );
      return { t: "call", fn, args };
    }
    return fail(`unexpected "${t.t === "op" ? t.v : String((t as { v: unknown }).v)}"`);
  }

  const ast = parseExpr(1);
  if (!ast) return { ok: false, error: err ?? "could not parse the formula" };
  if (pos !== toks.length) {
    const t = toks[pos];
    return {
      ok: false,
      error: `unexpected "${t.t === "op" || t.t === "ident" ? (t as { v: string }).v : t.t === "ref" ? `{${(t as { k: string }).k}}` : String((t as { v: unknown }).v)}" after the formula`,
    };
  }
  return { ok: true, ast, refs: collectRefs(ast) };
}

/** Every {ref} the tree names, deduped, in first-appearance order. */
export function collectRefs(ast: FormulaAst): string[] {
  const out: string[] = [];
  const walk = (n: FormulaAst): void => {
    switch (n.t) {
      case "ref":
        if (!out.includes(n.k)) out.push(n.k);
        return;
      case "neg":
        walk(n.e);
        return;
      case "bin":
      case "cmp":
        walk(n.l);
        walk(n.r);
        return;
      case "call":
        n.args.forEach(walk);
        return;
      default:
        return;
    }
  };
  walk(ast);
  return out;
}

/** number | string | boolean | null inside the tree; the CELL result is
 *  number | string | null (booleans display as "TRUE"/"FALSE"). */
type Value = number | string | boolean | null;

/** What TODAY() and friends resolve against. `today` is the TENANT's day
 *  (payload `today`), never the browser clock. */
export interface FormulaEnv {
  today?: string;
}

/**
 * A cell value coerced for arithmetic. Strict on purpose:
 *   number (finite)      -> itself       0 is a real value, not a blank
 *   "" / null / undefined-> null         blank in, blank out
 *   numeric string       -> its number   (trimmed; Number(), never parseFloat)
 *   anything else        -> null         text is not silently 0
 */
export function toOperand(raw: unknown): number | null {
  if (typeof raw === "number") return Number.isFinite(raw) ? raw : null;
  if (raw == null) return null;
  if (typeof raw === "string") {
    const s = raw.trim();
    if (s === "") return null;
    const n = Number(s);
    return Number.isFinite(n) ? n : null;
  }
  if (typeof raw === "boolean") return null; // a checkbox is not a number
  return null;
}

function asNumber(v: Value): number | null {
  if (typeof v === "number" && Number.isFinite(v)) return v;
  if (typeof v === "string") return toOperand(v);
  return null;
}

/** Text context: blank -> "" (Excel), numbers in General format, booleans as
 *  their names. Text is display, not arithmetic — "" states nothing false. */
function asText(v: Value): string {
  if (v == null) return "";
  if (typeof v === "string") return v;
  if (typeof v === "boolean") return v ? "TRUE" : "FALSE";
  return numToText(v);
}

/** Excel's "General": no separators, no trailing zeros, up to ~10 dp. */
function numToText(n: number): string {
  if (Number.isInteger(n)) return String(n);
  return String(Number(n.toFixed(10)));
}

/** Airtable/Excel ROUND: half away from zero, precision may be negative (tens). */
function roundHalfAway(x: number, p: number): number | null {
  if (!Number.isInteger(p) || p < -10 || p > 10) return null;
  const m = Math.pow(10, p);
  const r = Math.sign(x) * Math.round(Math.abs(x) * m);
  const out = r / m;
  return Number.isFinite(out) ? out : null;
}

/** An ISO date's parts, or null. Accepts "YYYY-MM-DD" and anything that starts
 *  with it ("YYYY-MM-DD HH:MM:SS") — the shapes the grid's date fields hold. */
function dateParts(v: Value): { y: number; m: number; d: number } | null {
  if (typeof v !== "string") return null;
  const m = /^(\d{4})-(\d{2})-(\d{2})/.exec(v.trim());
  if (!m) return null;
  const y = Number(m[1]);
  const mo = Number(m[2]);
  const d = Number(m[3]);
  if (mo < 1 || mo > 12 || d < 1 || d > 31) return null;
  return { y, m: mo, d };
}

function dateSerial(p: { y: number; m: number; d: number }): number {
  return Date.UTC(p.y, p.m - 1, p.d) / 86_400_000;
}

/**
 * TEXT(value, format) — the common Excel number formats, fail-closed:
 *   "0"  "0.00"        fixed decimals
 *   "#,##0"  "#,##0.00" thousands separators
 *   "$#,##0.00" …      a literal currency prefix
 *   "0%"  "0.0%"       percent of 1 (0.42 -> "42%")
 * An unrecognised format is BLANK, never a guess at what it meant.
 */
function textFormat(n: number, fmt: string): string | null {
  const m = /^(\$?)(#,##0|0)(?:\.(0+))?(%?)$/.exec(fmt.trim());
  if (!m) return null;
  const [, cur, intPart, decimals, pct] = m;
  let x = n;
  if (pct === "%") x *= 100;
  const dp = decimals ? decimals.length : 0;
  const neg = x < 0 || Object.is(x, -0) ? "-" : "";
  const fixed = Math.abs(x).toFixed(dp);
  let [ints, frac] = fixed.split(".");
  if (intPart === "#,##0") ints = ints.replace(/\B(?=(\d{3})+(?!\d))/g, ",");
  return neg + cur + ints + (dp ? "." + frac : "") + (pct === "%" ? "%" : "");
}

/** The aggregate forms: Excel-style, blanks SKIPPED (that selectivity is why
 *  they exist beside `+`). Returns the numeric args that resolved. */
function numericArgs(nodes: FormulaAst[], ev: (n: FormulaAst) => Value): number[] {
  const out: number[] = [];
  for (const a of nodes) {
    const v = ev(a);
    if (v == null) continue;
    const n = asNumber(v);
    if (n != null) out.push(n);
  }
  return out;
}

function evalNode(n: FormulaAst, get: (key: string) => unknown, env: FormulaEnv): Value {
  const ev = (x: FormulaAst): Value => evalNode(x, get, env);
  switch (n.t) {
    case "num":
      return n.v;
    case "str":
      return n.v;
    case "bool":
      return n.v;
    case "ref": {
      const raw = get(n.k);
      // Text stays text (LEFT/&/comparisons need it); numbers stay numbers.
      if (typeof raw === "string") return raw;
      if (typeof raw === "number") return Number.isFinite(raw) ? raw : null;
      if (typeof raw === "boolean") return null; // a checkbox is not a value here
      return null;
    }
    case "neg": {
      const v = asNumber(ev(n.e));
      return v == null ? null : -v;
    }
    case "bin": {
      if (n.op === "&") {
        // Concatenation is TEXT context: blanks become "", nothing errors.
        return asText(ev(n.l)) + asText(ev(n.r));
      }
      const l = asNumber(ev(n.l));
      if (l == null) return null;
      const r = asNumber(ev(n.r));
      if (r == null) return null;
      let out: number;
      switch (n.op) {
        case "+": out = l + r; break;
        case "-": out = l - r; break;
        case "*": out = l * r; break;
        case "/":
          if (r === 0) return null;
          out = l / r;
          break;
        case "^":
          out = Math.pow(l, r);
          break;
      }
      return Number.isFinite(out) ? out : null;
    }
    case "cmp": {
      const lv = ev(n.l);
      const rv = ev(n.r);
      const ln = typeof lv === "number" ? lv : null;
      const rn = typeof rv === "number" ? rv : null;
      // numbers with numbers (numeric strings count as numbers, Excel's coercion)…
      const lAsN = ln ?? (typeof lv === "string" ? toOperand(lv) : null);
      const rAsN = rn ?? (typeof rv === "string" ? toOperand(rv) : null);
      if (lAsN != null && rAsN != null) return cmpNums(n.op, lAsN, rAsN);
      // …text with text, case-insensitively (Excel's rule)…
      if (typeof lv === "string" && typeof rv === "string") {
        const a = lv.trim().toLowerCase();
        const b = rv.trim().toLowerCase();
        if (a === "" || b === "") return null; // a blank comparison is not an answer
        return cmpNums(n.op, a < b ? -1 : a > b ? 1 : 0, 0);
      }
      // …anything mixed or blank is BLANK, never a guess.
      return null;
    }
    case "call": {
      switch (n.fn) {
        case "ABS": {
          const v = asNumber(ev(n.args[0]));
          return v == null ? null : Math.abs(v);
        }
        case "ROUND":
        case "ROUNDUP":
        case "ROUNDDOWN": {
          const v = asNumber(ev(n.args[0]));
          if (v == null) return null;
          const p = n.args.length > 1 ? asNumber(ev(n.args[1])) : 0;
          if (p == null || !Number.isInteger(p) || p < -10 || p > 10) return null;
          if (n.fn === "ROUND") return roundHalfAway(v, p);
          const m = Math.pow(10, p);
          const scaled = v * m;
          const r = n.fn === "ROUNDUP"
            ? Math.sign(scaled) * Math.ceil(Math.abs(scaled))
            : Math.sign(scaled) * Math.floor(Math.abs(scaled));
          const out = r / m;
          return Number.isFinite(out) ? out : null;
        }
        case "INT": {
          const v = asNumber(ev(n.args[0]));
          return v == null ? null : Math.floor(v); // Excel INT floors toward -∞
        }
        case "MOD": {
          const a = asNumber(ev(n.args[0]));
          const b = asNumber(ev(n.args[1]));
          if (a == null || b == null || b === 0) return null;
          return a - b * Math.floor(a / b); // Excel: sign follows the divisor
        }
        case "SQRT": {
          const v = asNumber(ev(n.args[0]));
          return v == null || v < 0 ? null : Math.sqrt(v);
        }
        case "POWER": {
          const a = asNumber(ev(n.args[0]));
          const b = asNumber(ev(n.args[1]));
          if (a == null || b == null) return null;
          const out = Math.pow(a, b);
          return Number.isFinite(out) ? out : null;
        }
        case "EXP": {
          const v = asNumber(ev(n.args[0]));
          if (v == null) return null;
          const out = Math.exp(v);
          return Number.isFinite(out) ? out : null;
        }
        case "LN": {
          const v = asNumber(ev(n.args[0]));
          return v == null || v <= 0 ? null : Math.log(v);
        }
        case "SUM": {
          const vals = numericArgs(n.args, ev);
          return vals.reduce((a, b) => a + b, 0);
        }
        case "AVERAGE": {
          const vals = numericArgs(n.args, ev);
          return vals.length === 0 ? null : vals.reduce((a, b) => a + b, 0) / vals.length;
        }
        case "COUNT":
          return numericArgs(n.args, ev).length;
        case "COUNTA": {
          let c = 0;
          for (const a of n.args) {
            const v = ev(a);
            if (v != null && v !== "") c += 1;
          }
          return c;
        }
        case "MIN":
        case "MAX": {
          const vals = numericArgs(n.args, ev);
          if (vals.length === 0) return null;
          return n.fn === "MIN" ? Math.min(...vals) : Math.max(...vals);
        }
        case "IF": {
          const c = ev(n.args[0]);
          if (typeof c !== "boolean") return null; // no truthiness
          // LAZY: only the taken branch runs — IF({x} = 0, 0, 1/{x}) is the
          // guard pattern this exists for.
          if (c) return ev(n.args[1]);
          return n.args.length > 2 ? ev(n.args[2]) : null;
        }
        case "AND":
        case "OR": {
          // Strict booleans, evaluated lazily left to right (Excel short-circuits too).
          for (const a of n.args) {
            const v = ev(a);
            if (typeof v !== "boolean") return null;
            if (n.fn === "AND" && !v) return false;
            if (n.fn === "OR" && v) return true;
          }
          return n.fn === "AND";
        }
        case "NOT": {
          const v = ev(n.args[0]);
          return typeof v === "boolean" ? !v : null;
        }
        case "IFERROR": {
          // Our errors ARE blanks (the one rule), so IFERROR is the blank-coalesce.
          const v = ev(n.args[0]);
          return v == null ? ev(n.args[1]) : v;
        }
        case "ISBLANK": {
          const v = ev(n.args[0]);
          return v == null || v === "";
        }
        case "CONCATENATE":
          return n.args.map((a) => asText(ev(a))).join("");
        case "LEFT":
        case "RIGHT": {
          const s = asText(ev(n.args[0]));
          const k = n.args.length > 1 ? asNumber(ev(n.args[1])) : 1;
          if (k == null || !Number.isInteger(k) || k < 0) return null;
          return n.fn === "LEFT" ? s.slice(0, k) : k === 0 ? "" : s.slice(-k);
        }
        case "MID": {
          const s = asText(ev(n.args[0]));
          const start = asNumber(ev(n.args[1]));
          const len = asNumber(ev(n.args[2]));
          if (start == null || len == null) return null;
          if (!Number.isInteger(start) || !Number.isInteger(len) || start < 1 || len < 0)
            return null; // Excel: MID is 1-based and refuses a 0 start
          return s.slice(start - 1, start - 1 + len);
        }
        case "LEN":
          return asText(ev(n.args[0])).length;
        case "TRIM":
          return asText(ev(n.args[0])).replace(/\s+/g, " ").trim();
        case "UPPER":
          return asText(ev(n.args[0])).toUpperCase();
        case "LOWER":
          return asText(ev(n.args[0])).toLowerCase();
        case "PROPER":
          return asText(ev(n.args[0])).replace(
            /[A-Za-z]+/g,
            (w) => w[0].toUpperCase() + w.slice(1).toLowerCase()
          );
        case "VALUE": {
          const v = ev(n.args[0]);
          return asNumber(v);
        }
        case "TEXT": {
          const v = asNumber(ev(n.args[0]));
          const fmt = ev(n.args[1]);
          if (v == null || typeof fmt !== "string") return null;
          return textFormat(v, fmt);
        }
        case "TODAY":
          return typeof env.today === "string" && dateParts(env.today) ? env.today : null;
        case "YEAR":
        case "MONTH":
        case "DAY": {
          const p = dateParts(ev(n.args[0]));
          if (!p) return null;
          return n.fn === "YEAR" ? p.y : n.fn === "MONTH" ? p.m : p.d;
        }
        case "DAYS": {
          // Excel argument order: DAYS(end, start).
          const end = dateParts(ev(n.args[0]));
          const start = dateParts(ev(n.args[1]));
          if (!end || !start) return null;
          return Math.round(dateSerial(end) - dateSerial(start));
        }
      }
      return null;
    }
    default:
      return null;
  }
}

function cmpNums(op: "<" | "<=" | ">" | ">=" | "=" | "!=", l: number, r: number): boolean {
  switch (op) {
    case "<": return l < r;
    case "<=": return l <= r;
    case ">": return l > r;
    case ">=": return l >= r;
    case "=": return l === r;
    case "!=": return l !== r;
  }
}

/**
 * Evaluate a parsed formula over one row's values. Returns a FINITE number, a
 * STRING (text results, and booleans as "TRUE"/"FALSE"), or null (blank).
 * NEVER throws, whatever the inputs: the belt around the whole engine, because
 * a cell renderer that throws takes the grid with it.
 */
export function evalFormula(
  ast: FormulaAst,
  get: (key: string) => unknown,
  env: FormulaEnv = {}
): number | string | null {
  try {
    const v = evalNode(ast, get, env);
    if (typeof v === "number") return Number.isFinite(v) ? v : null;
    if (typeof v === "string") return v;
    if (typeof v === "boolean") return v ? "TRUE" : "FALSE";
    return null;
  } catch {
    return null;
  }
}

/**
 * Dependency order for FORMULA-OVER-FORMULA (2026-07-31). Given every formula
 * field's source by key, returns the keys in an evaluation order where a
 * formula runs AFTER the formulas it references, plus the set caught in a
 * CYCLE — those evaluate to blank (their members never run), never to a stale
 * or half-updated number. A formula depending on a cyclic one still runs; the
 * cyclic ref simply reads blank.
 */
export function orderFormulas(sources: ReadonlyMap<string, string>): {
  order: string[];
  cyclic: Set<string>;
} {
  const deps = new Map<string, string[]>();
  for (const [key, src] of sources) {
    const p = parseFormula(src);
    deps.set(key, p.ok ? p.refs.filter((r) => sources.has(r) && r !== key) : []);
  }
  const order: string[] = [];
  const state = new Map<string, 0 | 1 | 2>(); // 0 visiting-guard absent, 1 = on stack, 2 = done
  const cyclic = new Set<string>();
  const visit = (key: string, stack: string[]): boolean => {
    const s = state.get(key);
    if (s === 2) return !cyclic.has(key);
    if (s === 1) {
      // Everything from the first appearance of `key` on the stack is the cycle.
      for (let i = stack.lastIndexOf(key); i < stack.length; i += 1) cyclic.add(stack[i]);
      return false;
    }
    state.set(key, 1);
    stack.push(key);
    for (const d of deps.get(key) ?? []) visit(d, stack);
    stack.pop();
    state.set(key, 2);
    if (!cyclic.has(key)) order.push(key);
    return !cyclic.has(key);
  };
  for (const key of sources.keys()) visit(key, []);
  return { order, cyclic };
}

/**
 * Create-form validation: parse + check every ref against the fields that
 * exist. Referencing ANOTHER formula is allowed (evaluation is topological);
 * what is refused is a CYCLE — including the self-reference, its smallest
 * case. `formulaSources` carries every formula field's source so the check
 * can walk the graph; `selfKey` names the field being edited (absent on
 * create, where no cycle is possible yet).
 */
export function validateFormula(
  src: string,
  knownKeys: ReadonlySet<string>,
  formulaSources: ReadonlyMap<string, string>,
  selfKey?: string
): { ok: boolean; error?: string; refs: string[] } {
  const parsed = parseFormula(src);
  if (!parsed.ok) return { ok: false, error: parsed.error, refs: [] };
  for (const k of parsed.refs) {
    if (!knownKeys.has(k) && !formulaSources.has(k))
      return { ok: false, error: `{${k}} is not a field of this table`, refs: parsed.refs };
    if (selfKey && k === selfKey)
      return {
        ok: false,
        error: "a formula cannot reference itself",
        refs: parsed.refs,
      };
  }
  if (selfKey) {
    const all = new Map(formulaSources);
    all.set(selfKey, src);
    const { cyclic } = orderFormulas(all);
    if (cyclic.has(selfKey))
      return {
        ok: false,
        error: "this formula would create a loop between formulas",
        refs: parsed.refs,
      };
  }
  return { ok: true, refs: parsed.refs };
}