/**
 * AST evaluator.
 *
 * `evaluate(ast, host)` walks the tree and returns a {@link CellValue}. It never
 * recurses into other cells directly -- it asks the {@link EngineHost} for their
 * *already computed* values, which the recalculation driver guarantees are ready
 * (topological order). Circular cells are detected up front by the graph module
 * and never reach the evaluator.
 *
 * Evaluation model
 * ----------------
 *   - Arithmetic is numeric. `null` (empty cell) counts as 0. A numeric-looking
 *     string is coerced; any other string yields `#VALUE!`.
 *   - Comparisons (`= <> > < >= <=`) result `1`/`0`. Two numbers (or an empty
 *     cell, which takes on `0`) compare numerically; two texts (or an empty
 *     cell, which takes on `''`) compare case-insensitively and trimmed --
 *     same convention as XLOOKUP's own text matching, see evalXLookup(). A
 *     number against a real (non-empty) text never crossmatches: `=`/`<>`
 *     answer definitively false/true, `> < >= <=` have no sane answer and
 *     yield `#VALUE!` (see compareValues()).
 *   - `&` concatenates as text: a number renders plain (`String(n)`, no
 *     locale formatting), an empty cell renders as `''` (see toText()).
 *   - Any error among operands / arguments propagates unchanged (first wins).
 *   - A range may only appear as a function argument. Used in scalar position it
 *     yields `#VALUE!`.
 *   - A `NamedRefNode` (a bare, unqualified name that isn't a cell) resolves
 *     live against `host.resolveNamedRange()` into the cell/range it stands
 *     for, then evaluates exactly like that reference would have (see
 *     `resolveNamedRef()`). Unknown name -> `#NAME?`.
 *   - Unknown sheet name -> `#REF`. Unknown function -> `#NAME?`.
 *   - Division by zero -> `#DIV/0!`.
 *
 * `kind: 'block'` custom functions (FOR/IF/RETURN "code" bodies -- see
 * userFunctions.ts and src/engine/README.md's "Schleifen" section) are the
 * one thing here that isn't a pure, static AST walk: a `BlockCallNode`
 * embedded in a cell's AST is *interpreted* right here, at evaluation time
 * (`evaluateBlockCall`, below `evalScalar`) -- real cell values are needed
 * to decide an `IF` branch or when an early `RETURN` fires, so unlike an
 * ordinary function call this can't be resolved any earlier.
 */

import type { ActionNode, BlockCallNode, BlockNode, CellNode, FuncNode, JsonLiteralNode, NamedRefNode, Node, RangeNode, Statement } from './parser';
import { getFunction, isTruthy } from './functions';
import { rangeCells } from './refs';
import { substitute } from './userFunctions';
import {
  FormulaError,
  isError,
  type CellRef,
  type CellValue,
  type EngineHost,
  type MacroNetworkHost,
  type MacroWriteHost,
} from './types';

export function evaluate(ast: Node, host: EngineHost): CellValue {
  return evalScalar(ast, host);
}

function evalScalar(node: Node, host: EngineHost): CellValue {
  switch (node.kind) {
    case 'number':
      return node.value;
    case 'string':
      return node.value;
    case 'errorliteral':
      return new FormulaError(node.code);

    case 'unary': {
      const v = toNumber(evalScalar(node.operand, host));
      if (isError(v)) return v;
      return node.op === '-' ? -v : v;
    }

    case 'binary': {
      if (node.op === '^') {
        const l = toNumber(evalScalar(node.left, host));
        if (isError(l)) return l;
        const r = toNumber(evalScalar(node.right, host));
        if (isError(r)) return r;
        return Math.pow(l, r);
      }
      if (isComparisonOp(node.op)) {
        const lv = evalScalar(node.left, host);
        if (isError(lv)) return lv;
        const rv = evalScalar(node.right, host);
        if (isError(rv)) return rv;
        return compareValues(node.op, lv, rv);
      }
      if (node.op === '&') {
        const lv = toText(evalScalar(node.left, host));
        if (isError(lv)) return lv;
        const rv = toText(evalScalar(node.right, host));
        if (isError(rv)) return rv;
        return lv + rv;
      }
      const l = toNumber(evalScalar(node.left, host));
      if (isError(l)) return l;
      const r = toNumber(evalScalar(node.right, host));
      if (isError(r)) return r;
      switch (node.op) {
        case '+':
          return l + r;
        case '-':
          return l - r;
        case '*':
          return l * r;
        case '/':
          return r === 0 ? new FormulaError('#DIV/0!') : l / r;
      }
      return new FormulaError('#ERR', `unknown operator ${node.op}`);
    }

    case 'cell': {
      const tableId =
        node.qualifier == null
          ? host.homeTableId
          : host.resolveTableName(node.qualifier, node.sheetQualifier ?? null);
      if (!tableId) return new FormulaError('#REF', `unknown table '${node.qualifier}'`);
      return host.getComputed(tableId, node.col, node.row);
    }

    case 'range':
      // A bare range can't collapse to a scalar.
      return new FormulaError('#VALUE!', 'range used where a value is expected');

    case 'namedref': {
      const resolved = resolveNamedRef(node, host);
      if (isError(resolved)) return resolved;
      return evalScalar(resolved, host); // a resolved 1x1 range is a 'cell', so this recursion terminates
    }

    case 'func': {
      // IF is the one built-in that must NOT evaluate its arguments eagerly
      // (see functions.ts's FUNCTIONS.IF comment): only the taken branch may
      // be computed, so e.g. IF(B1=0; 0; A1/B1) never divides by zero when
      // B1=0. Special-cased here, before the generic eager-args dispatch.
      const upperName = node.name.toUpperCase();
      if (upperName === 'IF') return evalIf(node, host);
      // XLOOKUP needs its two range arguments kept separate (not flattened
      // into one combined list like every other function gets, see
      // functions.ts's FUNCTIONS.XLOOKUP comment) and its if_not_found
      // argument evaluated lazily -- both require handling here, before the
      // generic eager/flattening dispatch below.
      if (upperName === 'XLOOKUP') return evalXLookup(node, host);
      // JSONGET needs its first argument RAW (a JsonLiteralNode, never
      // reducible to a plain CellValue first) -- special-cased here for the
      // same reason as IF/XLOOKUP. Unlike GET/POST (see execStatementAsync
      // in the "async macro interpreter" section below), JSONGET is pure
      // computation with no `await` anywhere, so it can live in this
      // ordinary, fully-synchronous dispatch and therefore compose in ANY
      // expression position (nested in SETVALUE's value, an IF condition,
      // ...) -- not just as the whole RHS of a macro assignment. By the
      // time evalScalar ever sees `node.args[0]`, evalInEnv has already run
      // substitute() against the current env, so a param/loop-variable
      // bound to a JsonLiteralNode already appears here as that literal
      // node directly, no env access needed. Always collapses its result to
      // a plain CellValue (evalScalar's return type leaves no other
      // choice) -- a macro that wants to keep drilling into/iterating a
      // JSON *structure* instead uses JSONGET as a whole assignment RHS
      // (`x = JSONGET(r; "items")`), which the async interpreter's
      // `evalSpecialAssign` intercepts before ever reaching here.
      if (upperName === 'JSONGET') return evalJsonGet(node, host);

      const fn = getFunction(node.name);
      if (!fn) return new FormulaError('#NAME?', `unknown function ${node.name}`);
      const values: CellValue[] = [];
      for (const arg of node.args) {
        const part = evalArg(arg, host);
        if (isError(part)) return part;
        for (const v of part) {
          if (isError(v)) return v;
          values.push(v);
        }
      }
      return fn(values);
    }

    case 'param':
      // userFunctions.ts's expandUserFunctions() substitutes every ParamNode
      // away before a cell's AST is ever stored -- this should be unreachable
      // in practice. Fails as a cell error rather than however an unhandled
      // case would otherwise behave, matching the engine's "no crash" rule.
      return new FormulaError('#ERR', `unresolved parameter ${node.name}`);

    case 'blockcall':
      return evaluateBlockCall(node, host);

    case 'omitted':
      // an empty argument slot (`XLOOKUP(x;a;b;;-1)`'s skipped 4th
      // argument) evaluates like any other missing value -- same as an
      // empty cell. A function that needs to distinguish "omitted" from
      // "evaluates to null" checks the raw arg Node's kind directly before
      // ever calling evalScalar on it (see evalXLookup below).
      return null;

    case 'json':
      // A JSON value (a GET/POST result, or a JSONGET/FOR-over-array
      // element) used in ordinary scalar position -- e.g. `SETVALUE(x;
      // result)` without first drilling into it via JSONGET. Collapses to
      // the best plain CellValue: a primitive keeps its own type/value
      // (boolean -> 1/0, matching this engine's existing convention that
      // comparisons already return 1/0 rather than a real boolean type);
      // an object/array has no better scalar form than its JSON text.
      return jsonValueToCellValue(node.value);
  }
}

/** {@link evalScalar}'s `case 'json'` collapse rule -- a plain JSON
 *  primitive becomes the matching `CellValue`; an object/array has no
 *  better scalar form than its JSON text. Factored out as its own
 *  function so there is exactly one definition of this rule. */
function jsonValueToCellValue(value: unknown): CellValue {
  if (value === null || value === undefined) return null;
  if (typeof value === 'number') return value;
  if (typeof value === 'string') return value;
  if (typeof value === 'boolean') return value ? 1 : 0;
  return JSON.stringify(value);
}

/** `JSONGET(value; path)` used nested in an ordinary expression (see the
 *  `case 'func'` special-case above) -- `value` must already be a
 *  `JsonLiteralNode` by the time it reaches here (produced earlier in the
 *  same macro by `GET`/`POST`/another `JSONGET`/a `FOR`-over-array
 *  iteration); anything else is a `#VALUE!`, the same error class any
 *  other function-argument type mismatch gets in this engine. `path`
 *  resolves through the ordinary `evalScalar`, so it can itself be a
 *  computed/concatenated string. Uses `jsonGetPath` (defined alongside the
 *  async macro interpreter below, since that is `jsonGetPath`'s only other
 *  caller) -- a missing path resolves to JSON `null`, not an error, see
 *  its own doc comment. */
function evalJsonGet(node: FuncNode, host: EngineHost): CellValue {
  if (node.args.length !== 2) return new FormulaError('#ERR', 'JSONGET expects 2 arguments');
  const target = node.args[0];
  if (target.kind !== 'json') {
    return new FormulaError('#VALUE!', 'JSONGET: first argument is not a JSON result');
  }
  const path = evalScalar(node.args[1], host);
  if (isError(path)) return path;
  const pathText = toText(path);
  if (isError(pathText)) return pathText;
  return jsonValueToCellValue(jsonGetPath(target.value, pathText));
}

/** IF(cond; then; else?) -- the real, short-circuiting implementation (see
 *  the `case 'func'` special-case above and functions.ts's FUNCTIONS.IF
 *  comment). Only `node.args[0]` is always evaluated; whichever of the
 *  other two branches is taken is evaluated next, and the other is never
 *  touched at all -- so an error (or, in a block-mode call, any other side
 *  effect) in the untaken branch never surfaces. Truthy via functions.ts's
 *  `isTruthy()` -- the one convention shared everywhere in this engine a
 *  condition is needed (also `execStatement`'s `ifstmt` case, below, and
 *  `AND`/`OR`/`NOT`). */
function evalIf(node: FuncNode, host: EngineHost): CellValue {
  if (node.args.length !== 2 && node.args.length !== 3) {
    return new FormulaError('#ERR', 'IF expects 2 or 3 arguments');
  }
  const cond = evalScalar(node.args[0], host);
  if (isError(cond)) return cond;
  if (isTruthy(cond)) return evalScalar(node.args[1], host);
  return node.args.length === 3 ? evalScalar(node.args[2], host) : 0;
}

/** Exact-match equality for XLOOKUP -- deliberately its own thing, not the
 *  `=` operator (which coerces both sides through `toNumber()` and so can
 *  never match on text, XLOOKUP's single most common real-world use: "find
 *  the row where this name/ID matches"). Numbers compare numerically, text
 *  compares case-insensitively with surrounding whitespace ignored (matches
 *  this engine's existing case-insensitive conventions -- function/table
 *  names, `!`/`::` qualifiers). A number is never treated as equal to text
 *  (no cross-type coercion -- predictable, no "5" == 5 surprises); two
 *  empty cells are equal to each other. */
function xlookupEquals(a: CellValue, b: CellValue): boolean {
  if (isError(a) || isError(b)) return false;
  if (a === null || b === null) return a === b;
  if (typeof a === 'number' && typeof b === 'number') return a === b;
  if (typeof a === 'string' && typeof b === 'string') {
    return a.trim().toLowerCase() === b.trim().toLowerCase();
  }
  return false;
}

/**
 * XLOOKUP(Suchwert; Suchbereich; Rückgabebereich; [WennNichtGefunden];
 * [Vergleichsmodus]; [Suchmodus]) -- see functions.ts's FUNCTIONS.XLOOKUP
 * comment for why this has to live here instead. `Suchbereich` and
 * `Rückgabebereich` are evaluated separately via `evalArg` (which already
 * returns one argument's values as their own array -- it's only `case
 * 'func'`'s generic dispatch above that flattens every argument together)
 * and must come out the same length.
 *
 * `Vergleichsmodus`: `0` (default) exact match; `-1` exact match or next
 * *smaller*; `1` exact match or next *larger* (both numeric-only -- a
 * nearest match against text isn't supported, documented scope limit,
 * see README.md). `2` (Excel's wildcard mode) is not supported.
 * `Suchmodus`: `1` (default) first-to-last; `-1` last-to-first. Excel's
 * binary-search modes (`2`/`-2`) are not supported -- pointless at this
 * app's table sizes and would require an assumed-sorted precondition this
 * engine can't verify.
 *
 * No match and no `WennNichtGefunden` given -> `#N/A` (a documented
 * extension to this engine's original error set, exactly the same
 * reasoning `#VALUE!` was added under). `WennNichtGefunden` is evaluated
 * lazily -- only when actually needed, same short-circuit principle as
 * `IF`'s branches -- so an error inside it never surfaces on a successful
 * match.
 */
function evalXLookup(node: FuncNode, host: EngineHost): CellValue {
  const args = node.args;
  if (args.length < 3 || args.length > 6) {
    return new FormulaError('#ERR', 'XLOOKUP expects 3 to 6 arguments');
  }
  // An optional argument counts as "given" only if the slot both exists AND
  // wasn't left empty (`XLOOKUP(x;a;b;;-1)`'s skipped 4th argument, an
  // OmittedNode -- see parser.ts). `args.length` alone can't tell "not
  // provided at all" apart from "explicitly skipped to reach a later one".
  const given = (i: number) => i < args.length && args[i].kind !== 'omitted';

  const lookupValue = evalScalar(args[0], host);
  if (isError(lookupValue)) return lookupValue;

  const lookupArr = evalArg(args[1], host);
  if (isError(lookupArr)) return lookupArr;
  const returnArr = evalArg(args[2], host);
  if (isError(returnArr)) return returnArr;
  if (lookupArr.length !== returnArr.length) {
    return new FormulaError(
      '#VALUE!',
      'XLOOKUP: Suchbereich und Rückgabebereich müssen gleich viele Zellen haben',
    );
  }
  for (const v of lookupArr) if (isError(v)) return v;
  for (const v of returnArr) if (isError(v)) return v;

  let matchMode = 0;
  if (given(4)) {
    const mm = toNumber(evalScalar(args[4], host));
    if (isError(mm)) return mm;
    if (mm !== -1 && mm !== 0 && mm !== 1) {
      return new FormulaError(
        '#ERR',
        'XLOOKUP: Vergleichsmodus muss -1, 0 oder 1 sein (2 wird nicht unterstützt)',
      );
    }
    matchMode = mm;
  }

  let searchMode = 1;
  if (given(5)) {
    const sm = toNumber(evalScalar(args[5], host));
    if (isError(sm)) return sm;
    if (sm !== 1 && sm !== -1) {
      return new FormulaError(
        '#ERR',
        'XLOOKUP: Suchmodus muss 1 oder -1 sein (2/-2 werden nicht unterstützt)',
      );
    }
    searchMode = sm;
  }

  const n = lookupArr.length;
  const order = searchMode === -1 ? [...Array(n).keys()].reverse() : [...Array(n).keys()];

  if (matchMode === 0) {
    for (const i of order) {
      if (xlookupEquals(lookupValue, lookupArr[i])) return returnArr[i];
    }
  } else if (typeof lookupValue === 'number') {
    // Nearest-match modes: numeric only. An exact match still wins outright
    // wherever it's found; otherwise track the closest qualifying candidate.
    let bestIdx = -1;
    let bestVal = 0;
    for (const i of order) {
      const cand = lookupArr[i];
      if (xlookupEquals(lookupValue, cand)) return returnArr[i];
      if (typeof cand !== 'number') continue;
      const qualifies = matchMode === -1 ? cand < lookupValue : cand > lookupValue;
      if (!qualifies) continue;
      const better =
        bestIdx === -1 || (matchMode === -1 ? cand > bestVal : cand < bestVal);
      if (better) {
        bestIdx = i;
        bestVal = cand;
      }
    }
    if (bestIdx !== -1) return returnArr[bestIdx];
  }

  if (given(3)) return evalScalar(args[3], host);
  return new FormulaError('#N/A', 'XLOOKUP: kein Treffer');
}

// ---------------------------------------------------------------------------
// `kind: 'block'` custom functions: a small tree-walking interpreter for a
// BlockCallNode's `block` (see parser.ts's grammar comment and
// src/engine/README.md's "Schleifen" section). Everything else in this file
// is a pure, static AST walk; this is the one place real control flow
// (branching, early exit) happens, so it needs real cell values as it goes
// -- unlike a `kind: 'expression'` call, which is fully resolved to a plain
// expression at parse time and never reaches any special-case code here.
// ---------------------------------------------------------------------------

/** Thrown by a `return` statement to unwind out of `execBlock`/`execStatement`
 *  immediately, carrying the function's result. Caught only by
 *  `evaluateBlockCall`, right where it's thrown from -- a plain, standard
 *  "unwind to a known boundary" pattern for early-exit control flow. */
class ReturnSignal {
  constructor(public readonly value: CellValue) {}
}

/** Total loop iterations one `evaluateBlockCall` may run, summed across
 *  every FOR loop (including nested ones) it executes -- a safety net
 *  against a pathologically large range (e.g. `FOR x IN A1:Z10000 DO ...`),
 *  matching the "no crash, no hang" guarantee. A chain of nested
 *  block-kind calls is separately bounded by expandUserFunctions()'s own
 *  MAX_EXPANSION_DEPTH, so total work across one formula stays finite
 *  either way. Generous for anything this app's tables realistically hold. */
const MAX_LOOP_STEPS = 10_000;

/** Same idea as {@link MAX_LOOP_STEPS}, but much smaller and shared across
 *  BOTH loop iterations and write-action calls within one macro run (see
 *  `runMacroActions`) -- a macro loop iteration can trigger a full
 *  `syncWorkbook()` rebuild (a structural action like `INSERTROW`), so the
 *  budget sized for pure value computation would let one bad macro freeze
 *  the tab for up to 10,000 rebuilds. Still generous for a per-click action. */
const MAX_MACRO_STEPS = 1_000;

type Budget = { steps: number; limit: number };

function evaluateBlockCall(node: BlockCallNode, host: EngineHost): CellValue {
  const env = new Map<string, Node>(node.paramNames.map((p, i) => [p.toUpperCase(), node.args[i]]));
  const budget: Budget = { steps: 0, limit: MAX_LOOP_STEPS };
  try {
    execBlock(node.block, env, host, budget);
  } catch (e) {
    if (e instanceof ReturnSignal) return e.value;
    throw e; // a real problem (bad FOR source, step budget, ...) -- propagates
    // to evaluateCell()'s existing catch-all in workbook.ts, becoming #ERR,
    // exactly like a plain evaluation exception already does.
  }
  throw new Error(`'${node.label}' ended without RETURN`);
}

/**
 * Interprets a macro body (`parseMacroBlock()`'s output) OUTSIDE the normal
 * recalculation pass -- see engine/README.md's "Makros" section. Unlike
 * `evaluateBlockCall`, no RETURN is required: falling off the end simply
 * means "done", returning `null` (a macro has no caller expecting a value;
 * RETURN is still usable for an early exit / a status message, see the
 * `'return'` case in `execStatementAsync`). `writeHost` is what makes the
 * `'action'` statement kind (SETVALUE/INSERTROW/...) possible; `netHost` is
 * what makes the `GET`/`POST` assignment form possible -- see their doc
 * comments in types.ts. `host` MUST be a proxy that re-resolves against
 * the live engine on every call, never a cached reference: a structural
 * action mid-run (INSERTROW, ADDTABLE, ...) replaces the underlying engine
 * instance wholesale (see store/document.ts's `syncWorkbook`), and a cached
 * reference would silently go stale for every read after that point.
 *
 * `async` because `GET`/`POST` genuinely need to wait on a real network
 * round-trip -- but this is a SEPARATE interpreter (`execBlockAsync`/
 * `execStatementAsync`, below) from the synchronous `execBlock`/
 * `execStatement` that `evaluateBlockCall` (ordinary `kind:'block'` custom
 * functions) still uses. That path is reachable from deep inside the
 * fully-synchronous recalculation pass (`evalScalar`'s `case 'blockcall'`,
 * itself called throughout `workbook.ts`) and must never become async --
 * so rather than convert the shared interpreter and risk that cascade, the
 * two statement-kind switches are two separate (structurally near-
 * identical) functions. Everything they call beneath the switch itself
 * (`evalInEnv`, `resolveWriteTarget`, `rangeOrCellToCellNodes`, `execAction`)
 * is pure/synchronous and fully shared -- only the outer dispatch loop and
 * the `'assign'`/`'for'` cases (which need to recognize a network/JSON
 * call, or a JSON-array source) are duplicated.
 */
export async function runMacroActions(
  block: BlockNode,
  host: EngineHost,
  writeHost: MacroWriteHost,
  netHost: MacroNetworkHost,
): Promise<CellValue | null> {
  const env = new Map<string, Node>();
  const budget: Budget = { steps: 0, limit: MAX_MACRO_STEPS };
  try {
    await execBlockAsync(block, env, host, budget, writeHost, netHost);
  } catch (e) {
    if (e instanceof ReturnSignal) return e.value;
    throw e;
  }
  return null;
}

function execBlock(
  block: BlockNode,
  env: Map<string, Node>,
  host: EngineHost,
  budget: Budget,
  writeHost?: MacroWriteHost,
): void {
  for (const stmt of block.statements) {
    execStatement(stmt, env, host, budget, writeHost);
  }
}

/** Resolve `expr` against the current environment (substitute() replaces
 *  any name reference with its bound Node -- the caller's argument, or an
 *  earlier statement's/iteration's literal result) and evaluate it. */
function evalInEnv(expr: Node, env: Map<string, Node>, host: EngineHost): CellValue {
  return evalScalar(substitute(expr, env), host);
}

/** Wrap an already-computed scalar back into a literal `Node` so it can be
 *  stored in `env` and later be resolved/evaluated exactly like any other
 *  bound name (a parameter, or the caller's argument expression). `null`
 *  (an empty cell's value) becomes the number `0`, matching how every other
 *  arithmetic context in this engine already treats an empty cell. Callers
 *  check `isError` before ever calling this -- an error value is never
 *  passed in (it aborts the whole function via `ReturnSignal` instead). */
function literalNodeOf(v: number | string | null): Node {
  if (v === null) return { kind: 'number', value: 0 };
  return typeof v === 'number' ? { kind: 'number', value: v } : { kind: 'string', value: v };
}

function execStatement(
  stmt: Statement,
  env: Map<string, Node>,
  host: EngineHost,
  budget: Budget,
  writeHost?: MacroWriteHost,
): void {
  switch (stmt.kind) {
    case 'assign': {
      const v = evalInEnv(stmt.value, env, host);
      if (isError(v)) throw new ReturnSignal(v); // an error in a plain assignment aborts the function with that error
      env.set(stmt.name.toUpperCase(), literalNodeOf(v));
      return;
    }
    case 'return': {
      const v = evalInEnv(stmt.value, env, host);
      throw new ReturnSignal(v);
    }
    case 'ifstmt': {
      const c = evalInEnv(stmt.cond, env, host);
      if (isError(c)) throw new ReturnSignal(c);
      if (isTruthy(c)) execBlock(stmt.then, env, host, budget, writeHost);
      else if (stmt.else) execBlock(stmt.else, env, host, budget, writeHost);
      return;
    }
    case 'for': {
      const source = substitute(stmt.source, env);
      const cells = rangeOrCellToCellNodes(source, stmt.varName, host);
      for (const cell of cells) {
        budget.steps++;
        if (budget.steps > budget.limit) {
          throw new Error(`FOR-Schleife(n) über zu viele Zellen (> ${budget.limit} insgesamt)`);
        }
        env.set(stmt.varName.toUpperCase(), cell);
        execBlock(stmt.body, env, host, budget, writeHost);
      }
      return;
    }
    case 'action': {
      if (!writeHost) {
        // Can't happen via parseMacroBlock (only entry point that produces
        // ActionNode) -- a defensive guard, not a reachable user-facing error.
        throw new Error(`Aktion '${stmt.name}' außerhalb eines Makros nicht erlaubt`);
      }
      budget.steps++;
      if (budget.steps > budget.limit) {
        throw new Error(`Makro führt zu viele Schritte aus (> ${budget.limit} insgesamt)`);
      }
      execAction(stmt, env, host, writeHost);
      return;
    }
  }
}

// ---------------------------------------------------------------------------
// Async macro interpreter -- ONLY reachable via `runMacroActions`, never via
// `evaluateBlockCall` (see that function's own doc comment for why the sync
// interpreter above must stay untouched). Structurally mirrors
// `execBlock`/`execStatement` case-for-case; the only real differences are
// the `'assign'` case's GET/POST/JSONGET special-casing and the `'for'`
// case's JSON-array branch. Everything else delegates to the exact same
// synchronous helpers (`evalInEnv`, `resolveWriteTarget`,
// `rangeOrCellToCellNodes`, `execAction`) the sync interpreter already uses.
// ---------------------------------------------------------------------------

const NETWORK_OR_JSON_NAMES = new Set(['GET', 'POST', 'JSONGET']);

/** True iff `node` is exactly `NAME(args)` for one of GET/POST/JSONGET --
 *  the only shape `execStatementAsync`'s `'assign'` case treats specially.
 *  Used anywhere else (nested in an expression, an IF condition, a
 *  function argument) these names fall through to the ordinary, unchanged
 *  `evalScalar` -> `getFunction()` path and become `#NAME?`, since they are
 *  deliberately never registered in functions.ts's builtin table -- the
 *  same containment mechanism the codebase already relies on for the 11
 *  write actions. */
function isNetworkOrJsonCall(node: Node): node is FuncNode {
  return node.kind === 'func' && NETWORK_OR_JSON_NAMES.has(node.name.toUpperCase());
}

/** Resolve a dot-separated path through a JSON value -- a numeric segment
 *  indexes an array, any other segment indexes an object key. A missing
 *  path (wrong type at some segment, out-of-range index, unknown key)
 *  resolves to JSON `null` rather than throwing, so a macro can safely
 *  probe "does this field exist" via a subsequent comparison/IF instead of
 *  needing its own existence-check syntax. */
function jsonGetPath(value: unknown, path: string): unknown {
  let cur = value;
  const trimmed = path.trim();
  if (trimmed === '') return cur;
  for (const segment of trimmed.split('.')) {
    if (cur === null || cur === undefined) return null;
    if (Array.isArray(cur)) {
      const idx = Number(segment);
      if (!Number.isInteger(idx) || idx < 0 || idx >= cur.length) return null;
      cur = cur[idx];
    } else if (typeof cur === 'object') {
      cur = (cur as Record<string, unknown>)[segment] ?? null;
    } else {
      return null; // trying to drill into a string/number/boolean
    }
  }
  return cur;
}

/** Resolves a `JSONGET(value; path)` call to the `JsonLiteralNode` it
 *  produces -- WITHOUT collapsing it to a plain `CellValue` (unlike
 *  `evalJsonGet` above, used when JSONGET is nested in ordinary expression
 *  position). Two callers need this uncollapsed form: `evalSpecialAssign`
 *  (`x = JSONGET(...)`, preserving structure for further drilling/
 *  iteration) and `execStatementAsync`'s `'for'` case (`FOR item IN
 *  JSONGET(r; "items") DO ...` -- a JSONGET call used directly as the FOR
 *  source, not first bound to a variable; found live: `substitute()` alone
 *  never evaluates a function call, so without this the source would still
 *  be an unresolved `FuncNode` by the time the array-ness check runs). */
function resolveJsonGetCall(node: FuncNode, env: Map<string, Node>, host: EngineHost): JsonLiteralNode {
  if (node.args.length !== 2) {
    throw new Error(`JSONGET: erwartet 2 Argumente, ${node.args.length} übergeben`);
  }
  const target = substitute(node.args[0], env);
  if (target.kind !== 'json') {
    throw new Error('JSONGET: das erste Argument muss ein JSON-Ergebnis sein (z. B. von GET/POST/JSONGET)');
  }
  const path = requireString(evalInEnv(node.args[1], env, host), 'JSONGET');
  return { kind: 'json', value: jsonGetPath(target.value, path) };
}

/** Evaluates a `GET`/`POST`/`JSONGET` call (already confirmed by
 *  `isNetworkOrJsonCall`) and returns the `JsonLiteralNode` to bind the
 *  assigned name to. `JSONGET` is purely synchronous (no `await` inside),
 *  kept in this async function anyway so the one `isNetworkOrJsonCall`
 *  check in `execStatementAsync` covers all three names uniformly. */
async function evalSpecialAssign(
  node: FuncNode,
  env: Map<string, Node>,
  host: EngineHost,
  netHost: MacroNetworkHost,
): Promise<JsonLiteralNode> {
  const upper = node.name.toUpperCase();
  if (upper === 'JSONGET') return resolveJsonGetCall(node, env, host);
  const isPost = upper === 'POST';
  if (isPost ? node.args.length !== 2 : node.args.length !== 1) {
    throw new Error(`${upper}: erwartet ${isPost ? 2 : 1} Argument${isPost ? 'e' : ''}, ${node.args.length} übergeben`);
  }
  const url = requireString(evalInEnv(node.args[0], env, host), upper);
  const body = isPost ? requireString(evalInEnv(node.args[1], env, host), upper) : undefined;
  const value = await netHost.request(isPost ? 'POST' : 'GET', url, body);
  return { kind: 'json', value };
}

async function execBlockAsync(
  block: BlockNode,
  env: Map<string, Node>,
  host: EngineHost,
  budget: Budget,
  writeHost: MacroWriteHost,
  netHost: MacroNetworkHost,
): Promise<void> {
  for (const stmt of block.statements) {
    await execStatementAsync(stmt, env, host, budget, writeHost, netHost);
  }
}

async function execStatementAsync(
  stmt: Statement,
  env: Map<string, Node>,
  host: EngineHost,
  budget: Budget,
  writeHost: MacroWriteHost,
  netHost: MacroNetworkHost,
): Promise<void> {
  switch (stmt.kind) {
    case 'assign': {
      const substituted = substitute(stmt.value, env);
      if (isNetworkOrJsonCall(substituted)) {
        if (substituted.name.toUpperCase() !== 'JSONGET') {
          // GET/POST count toward the step budget like any other action
          // (a real network round-trip); JSONGET is pure computation, same
          // treatment as an ordinary assignment.
          budget.steps++;
          if (budget.steps > budget.limit) {
            throw new Error(`Makro führt zu viele Schritte aus (> ${budget.limit} insgesamt)`);
          }
        }
        env.set(stmt.name.toUpperCase(), await evalSpecialAssign(substituted, env, host, netHost));
        return;
      }
      const v = evalScalar(substituted, host);
      if (isError(v)) throw new ReturnSignal(v);
      env.set(stmt.name.toUpperCase(), literalNodeOf(v));
      return;
    }
    case 'return': {
      const v = evalInEnv(stmt.value, env, host);
      throw new ReturnSignal(v);
    }
    case 'ifstmt': {
      const c = evalInEnv(stmt.cond, env, host);
      if (isError(c)) throw new ReturnSignal(c);
      if (isTruthy(c)) await execBlockAsync(stmt.then, env, host, budget, writeHost, netHost);
      else if (stmt.else) await execBlockAsync(stmt.else, env, host, budget, writeHost, netHost);
      return;
    }
    case 'for': {
      let source = substitute(stmt.source, env);
      // A JSONGET(...) call used directly as the FOR source (not first
      // bound to a variable) is still an unresolved FuncNode at this point
      // -- substitute() only resolves name references, it never evaluates
      // a call. Resolve it here to the JsonLiteralNode it produces before
      // the array-ness check below.
      if (source.kind === 'func' && source.name.toUpperCase() === 'JSONGET') {
        source = resolveJsonGetCall(source, env, host);
      }
      if (source.kind === 'json') {
        if (!Array.isArray(source.value)) {
          throw new Error(`FOR ${stmt.varName} IN ...: JSON-Quelle muss ein Array sein, kein Objekt/Einzelwert`);
        }
        for (const item of source.value) {
          budget.steps++;
          if (budget.steps > budget.limit) {
            throw new Error(`FOR-Schleife(n) über zu viele Elemente (> ${budget.limit} insgesamt)`);
          }
          env.set(stmt.varName.toUpperCase(), { kind: 'json', value: item });
          await execBlockAsync(stmt.body, env, host, budget, writeHost, netHost);
        }
        return;
      }
      const cells = rangeOrCellToCellNodes(source, stmt.varName, host);
      for (const cell of cells) {
        budget.steps++;
        if (budget.steps > budget.limit) {
          throw new Error(`FOR-Schleife(n) über zu viele Zellen (> ${budget.limit} insgesamt)`);
        }
        env.set(stmt.varName.toUpperCase(), cell);
        await execBlockAsync(stmt.body, env, host, budget, writeHost, netHost);
      }
      return;
    }
    case 'action': {
      budget.steps++;
      if (budget.steps > budget.limit) {
        throw new Error(`Makro führt zu viele Schritte aus (> ${budget.limit} insgesamt)`);
      }
      execAction(stmt, env, host, writeHost);
      return;
    }
  }
}

/** Dispatches one macro write-action call by name -- see types.ts's
 *  `MacroWriteHost` for the full vocabulary and engine/README.md's
 *  "Makros" section for the syntax. Only `SETVALUE`'s first argument is
 *  special (resolved as a write TARGET, never evaluated as a value); every
 *  other argument of every action goes through the ordinary `evalInEnv`
 *  pipeline, so local variables/loop variables/`&`-concatenation all work
 *  for free without any per-action plumbing. */
function execAction(stmt: ActionNode, env: Map<string, Node>, host: EngineHost, writeHost: MacroWriteHost): void {
  const upper = stmt.name.toUpperCase();
  const arity = (n: number): void => {
    if (stmt.args.length !== n) {
      throw new Error(`${upper}: erwartet ${n} Argument${n === 1 ? '' : 'e'}, ${stmt.args.length} übergeben`);
    }
  };
  const str = (i: number): string => requireString(evalInEnv(stmt.args[i], env, host), upper);
  const num = (i: number): number => requireNumber(evalInEnv(stmt.args[i], env, host), upper);
  switch (upper) {
    case 'SETVALUE': {
      arity(2);
      const target = resolveWriteTarget(substitute(stmt.args[0], env), host);
      const v = evalInEnv(stmt.args[1], env, host);
      if (isError(v)) throw new Error(`SETVALUE: ${v.code}`);
      const text = toText(v);
      if (isError(text)) throw new Error(`SETVALUE: ${text.code}`);
      writeHost.setValue(target, text);
      return;
    }
    case 'INSERTROW':
      arity(2);
      return writeHost.insertRow(str(0), num(1));
    case 'DELETEROW':
      arity(2);
      return writeHost.deleteRow(str(0), num(1));
    case 'INSERTCOL':
      arity(2);
      return writeHost.insertCol(str(0), num(1));
    case 'DELETECOL':
      arity(2);
      return writeHost.deleteCol(str(0), num(1));
    case 'ADDTABLE':
      arity(2);
      return writeHost.addTable(str(0), str(1));
    case 'RENAMETABLE':
      arity(2);
      return writeHost.renameTable(str(0), str(1));
    case 'DELETETABLE':
      arity(1);
      return writeHost.deleteTable(str(0));
    case 'ADDSHEET':
      arity(1);
      return writeHost.addSheet(str(0));
    case 'RENAMESHEET':
      arity(2);
      return writeHost.renameSheet(str(0), str(1));
    case 'DELETESHEET':
      arity(1);
      return writeHost.deleteSheet(str(0));
    default:
      throw new Error(`Unbekannte Aktion '${stmt.name}'`);
  }
}

function requireString(v: CellValue, ctx: string): string {
  if (isError(v)) throw new Error(`${ctx}: ${v.code}`);
  if (typeof v !== 'string') throw new Error(`${ctx}: erwartet einen Text als Argument`);
  return v;
}

function requireNumber(v: CellValue, ctx: string): number {
  const n = toNumber(v);
  if (isError(n)) throw new Error(`${ctx}: erwartet eine Zahl als Argument`);
  return n;
}

/** Resolve a `SETVALUE` target (already `substitute()`d against the current
 *  `env`, mirroring how `execStatement`'s `'for'` case resolves its own
 *  source) into concrete cell coordinates to write to -- the write-side
 *  twin of `rangeOrCellToCellNodes`. A range target broadcasts the same
 *  value to every cell in it (Excel's Ctrl+Enter fill semantics). Unlike a
 *  normal read, an unqualified `qualifier === null` is a hard error here,
 *  never "this table" -- a macro has no home table (see
 *  store/document.ts#validateMacro, which already rejects this at save
 *  time; reaching it at runtime would mean a target came from a
 *  dynamically-substituted value the static check couldn't see).
 *
 *  The `'namedref'` branch below is currently unreachable through any
 *  actually-parseable macro: `parseMacroBlock` turns off the bare-
 *  identifier named-range fallback, same as a custom-function body does
 *  and for the same reason (a typo'd local variable must fail loudly, not
 *  silently resolve to an unrelated document-wide named range) -- so a
 *  `NamedRefNode` can never appear in a macro's AST at all. Kept anyway,
 *  both as a defensive backstop and because it mirrors
 *  `rangeOrCellToCellNodes`'s identical shape below -- named-range support
 *  for macro targets is a documented, deliberate gap (see README's
 *  "Bekannte Grenzen"), not an oversight. */
function resolveWriteTarget(node: Node, host: EngineHost): CellRef[] {
  let n = node;
  if (n.kind === 'namedref') {
    const resolved = resolveNamedRef(n, host);
    if (isError(resolved)) throw new Error(`SETVALUE: unbekannter Name '${n.name}'`);
    n = resolved;
  }
  if (n.kind === 'cell' || n.kind === 'range') {
    if (n.qualifier == null) {
      throw new Error('SETVALUE: Zellbezüge in Makros müssen die Tabelle nennen, z. B. Umsatz::A1');
    }
    const tableId = host.resolveTableName(n.qualifier, n.sheetQualifier ?? null);
    if (!tableId) throw new Error(`SETVALUE: unbekannte Tabelle '${n.qualifier}'`);
    if (n.kind === 'cell') return [{ tableId, col: n.col, row: n.row }];
    return rangeCells(n, host.tableSize(tableId)).map((c) => ({ tableId, col: c.col, row: c.row }));
  }
  throw new Error('SETVALUE: das erste Argument muss ein Zell- oder Bereichsbezug sein');
}

/** A FOR loop's source, once names are resolved against the current
 *  environment, must be a single cell, a range, or a named range -- never a
 *  computed value (mirrors "a range may only appear as a function argument"
 *  for ordinary formulas). Expands a range to its individual cells via
 *  `expandRange` (the same helper `evalArg` uses for a range function
 *  argument), carrying the same table/sheet qualifier through to each. A
 *  named range resolves first (via `host`), then falls into the same
 *  cell/range handling as any other reference. */
function rangeOrCellToCellNodes(node: Node, loopVarName: string, host: EngineHost): CellNode[] {
  if (node.kind === 'namedref') {
    const resolved = resolveNamedRef(node, host);
    if (isError(resolved)) {
      throw new Error(`FOR ${loopVarName} IN ...: unbekannter Name '${node.name}'`);
    }
    node = resolved;
  }
  if (node.kind === 'cell') return [node];
  if (node.kind === 'range') {
    const range = node;
    // a whole column/row needs its table's size to know where it ends
    const tableId = range.whole
      ? range.qualifier == null
        ? host.homeTableId
        : host.resolveTableName(range.qualifier, range.sheetQualifier ?? null)
      : null;
    if (range.whole && !tableId) {
      throw new Error(`FOR ${loopVarName} IN ...: unbekannte Tabelle '${range.qualifier}'`);
    }
    return rangeCells(range, tableId ? host.tableSize(tableId) : null).map(
      (c): CellNode => ({
        kind: 'cell',
        qualifier: node.qualifier,
        ...(node.sheetQualifier === undefined ? {} : { sheetQualifier: node.sheetQualifier }),
        col: c.col,
        row: c.row,
      }),
    );
  }
  throw new Error(`FOR ${loopVarName} IN ...: die Quelle muss ein Zell- oder Bereichsbezug sein`);
}

/** Evaluate one function argument into a flat list of values (ranges expand). */
function evalArg(node: Node, host: EngineHost): FormulaError | CellValue[] {
  if (node.kind === 'namedref') {
    const resolved = resolveNamedRef(node, host);
    if (isError(resolved)) return resolved;
    return evalArg(resolved, host); // now a plain 'cell'/'range', handled below/by evalScalar
  }
  if (node.kind === 'range') {
    const tableId =
      node.qualifier == null
        ? host.homeTableId
        : host.resolveTableName(node.qualifier, node.sheetQualifier ?? null);
    if (!tableId) return new FormulaError('#REF', `unknown table '${node.qualifier}'`);
    return rangeCells(node, host.tableSize(tableId)).map((c) => host.getComputed(tableId, c.col, c.row));
  }
  const v = evalScalar(node, host);
  return [v];
}

/** Resolve a `NamedRefNode` to the concrete `CellNode`/`RangeNode` it stands
 *  for right now (via `host.resolveNamedRange()`), so every other place that
 *  already knows how to evaluate a cell/range (`evalScalar`, `evalArg`,
 *  `rangeOrCellToCellNodes`) can just fall through to that existing logic
 *  instead of duplicating it. `#NAME?` when the name is unknown -- the same
 *  error class an unknown function name gets. */
function resolveNamedRef(node: NamedRefNode, host: EngineHost): CellNode | RangeNode | FormulaError {
  const def = host.resolveNamedRange(node.name);
  if (!def) return new FormulaError('#NAME?', `unknown name '${node.name}'`);
  const sheetField = def.sheetQualifier === undefined ? {} : { sheetQualifier: def.sheetQualifier };
  if (def.start.col === def.end.col && def.start.row === def.end.row) {
    return { kind: 'cell', qualifier: def.qualifier, ...sheetField, col: def.start.col, row: def.start.row };
  }
  return { kind: 'range', qualifier: def.qualifier, ...sheetField, start: def.start, end: def.end };
}

/** Coerce a cell value to a number for arithmetic, or return an error value. */
function toNumber(v: CellValue): number | FormulaError {
  if (isError(v)) return v;
  if (v === null) return 0;
  if (typeof v === 'number') return v;
  const trimmed = v.trim();
  if (trimmed === '') return 0;
  const n = Number(trimmed);
  return Number.isFinite(n) ? n : new FormulaError('#VALUE!', `'${v}' is not a number`);
}

/** Coerce a cell value to text for `&` concatenation, or return an error
 *  value. An empty cell becomes `''` (not `'0'`), matching how it's ignored
 *  everywhere else in this engine rather than treated as a literal number.
 *  A number renders plain (`String(n)`, no thousands separator/locale --
 *  same "locale-neutral" convention as CSV/XLSX export), never `Intl`. */
function toText(v: CellValue): string | FormulaError {
  if (isError(v)) return v;
  if (v === null) return '';
  return typeof v === 'string' ? v : String(v);
}

type ComparisonOp = '=' | '<>' | '>' | '<' | '>=' | '<=';

function isComparisonOp(op: string): op is ComparisonOp {
  return op === '=' || op === '<>' || op === '>' || op === '<' || op === '>=' || op === '<=';
}

/** `= <> > < >= <=` on two already-evaluated scalars -- see the module
 *  comment's "Comparisons" bullet for the full rule. An empty cell (`null`)
 *  takes on whichever type the other side is (`0` for a number, `''` for
 *  text), so both `=A1=0` and `=A1=""` work on a genuinely empty `A1`. Text
 *  is matched the same way XLOOKUP matches text (case-insensitive, trimmed)
 *  -- kept as one convention across the engine rather than two. */
function compareValues(op: ComparisonOp, l: CellValue, r: CellValue): CellValue {
  const lIsText = typeof l === 'string';
  const rIsText = typeof r === 'string';

  // A real number against real (non-empty) text is a genuine type mismatch
  // -- an empty cell (null) is handled below, taking on whichever type the
  // OTHER side is, so it never reaches this branch.
  if ((typeof l === 'number' && rIsText) || (lIsText && typeof r === 'number')) {
    if (op === '=') return 0;
    if (op === '<>') return 1;
    return new FormulaError('#VALUE!', 'text and number are not comparable with > < >= <=');
  }

  if (lIsText || rIsText) {
    const lt = (l ?? '').toString().trim().toLowerCase();
    const rt = (r ?? '').toString().trim().toLowerCase();
    return applyCompare(op, lt, rt);
  }
  const ln = l === null ? 0 : (l as number);
  const rn = r === null ? 0 : (r as number);
  return applyCompare(op, ln, rn);
}

function applyCompare(op: ComparisonOp, l: number, r: number): CellValue;
function applyCompare(op: ComparisonOp, l: string, r: string): CellValue;
function applyCompare(op: ComparisonOp, l: number | string, r: number | string): CellValue {
  switch (op) {
    case '=':
      return l === r ? 1 : 0;
    case '<>':
      return l !== r ? 1 : 0;
    case '>':
      return l > r ? 1 : 0;
    case '<':
      return l < r ? 1 : 0;
    case '>=':
      return l >= r ? 1 : 0;
    case '<=':
      return l <= r ? 1 : 0;
  }
}