numolus/app/frontend/engine/__tests__/evaluator.spec.ts

import { describe, it, expect } from 'vitest';
import { parseFormula } from '../parser';
import { evaluate } from '../evaluator';
import { FormulaError, isError, type CellValue, type EngineHost } from '../types';

type NamedRangeShape = { qualifier: string; sheetQualifier?: string; start: { col: number; row: number }; end: { col: number; row: number } };

/** Minimal in-memory host: two tables addressed by name, plus an optional
 *  name -> named-range map (name lower-cased, same matching convention as
 *  the real Workbook). */
function makeHost(
  data: Record<string, Record<string, CellValue>>,
  home = 'S1',
  namedRanges: Record<string, NamedRangeShape> = {},
): EngineHost {
  const sizes: Record<string, { cols: number; rows: number }> = {
    S1: { cols: 10, rows: 10 },
    S2: { cols: 10, rows: 10 },
  };
  const idFor = (name: string): string | null => {
    const n = name.trim().toLowerCase();
    if (n === 's1') return 'S1';
    if (n === 's2') return 'S2';
    return null;
  };
  return {
    homeTableId: home,
    resolveTableName: idFor,
    resolveNamedRange: (name) => namedRanges[name.trim().toLowerCase()] ?? null,
    tableSize: (id) => sizes[id] ?? null,
    getComputed(tableId, col, row) {
      const cell = String.fromCharCode(65 + col) + (row + 1);
      const v = data[tableId]?.[cell];
      return v === undefined ? null : v;
    },
  };
}

const ev = (src: string, host: EngineHost) => evaluate(parseFormula(src), host);

describe('evaluator arithmetic', () => {
  const host = makeHost({ S1: { B2: 10, C2: 5, D2: 8 } });

  it('respects precedence and associativity', () => {
    expect(ev('=(B2+C2)*2-D2/4', host)).toBe(28); // (15)*2 - 2
    expect(ev('=2^3^2', host)).toBe(512);
    expect(ev('=-2^2', host)).toBe(-4);
    expect(ev('=2+3*4', host)).toBe(14);
  });

  it('treats an empty cell as 0', () => {
    expect(ev('=B2+Z9', host)).toBe(10);
  });

  it('division by zero -> #DIV/0!', () => {
    expect(ev('=1/0', host)).toEqual(new FormulaError('#DIV/0!'));
  });
});

describe('evaluator functions', () => {
  const host = makeHost({
    S1: { B2: 1200, B3: 980, B4: 1550, B5: 1340, B6: 1105 },
  });

  it('SUM / AVG / MIN / MAX / COUNT over a range', () => {
    expect(ev('=SUM(B2:B6)', host)).toBe(6175);
    expect(ev('=AVG(B2:B6)', host)).toBe(1235);
    expect(ev('=MIN(B2:B6)', host)).toBe(980);
    expect(ev('=MAX(B2:B6)', host)).toBe(1550);
    expect(ev('=COUNT(B2:B6)', host)).toBe(5);
  });

  it('AVERAGE is an alias of AVG', () => {
    expect(ev('=AVERAGE(B2:B6)', host)).toBe(1235);
  });

  it('mixes ranges, literals and single cells', () => {
    expect(ev('=SUM(B2:B3; 100; B4)', host)).toBe(1200 + 980 + 100 + 1550);
  });

  it('COUNT ignores empty cells', () => {
    expect(ev('=COUNT(B2:B10)', host)).toBe(5);
  });

  it('AVG of no numbers -> #DIV/0!', () => {
    const r = ev('=AVG(Z1:Z5)', host);
    expect(isError(r) && r.code).toBe('#DIV/0!');
  });

  it('unknown function -> #NAME?', () => {
    const r = ev('=FOO(1)', host);
    expect(isError(r) && r.code).toBe('#NAME?');
  });

  it('an omitted argument slot (e.g. SUM(1;;3)) behaves like an empty cell', () => {
    expect(ev('=SUM(1;;3)', host)).toBe(4);
    expect(ev('=COUNT(1;;3)', host)).toBe(2); // omitted, like an empty cell, isn't counted
  });
});

describe('evaluator IF', () => {
  const host = makeHost({ S1: { A1: 8, B1: 0 } });

  it('picks the then-branch when the condition is truthy', () => {
    expect(ev('=IF(1;2;3)', host)).toBe(2);
    expect(ev('=IF(A1>5;"groß";"klein")', host)).toBe('groß');
  });

  it('picks the else-branch when the condition is falsy', () => {
    expect(ev('=IF(0;2;3)', host)).toBe(3);
    expect(ev('=IF(A1<5;"groß";"klein")', host)).toBe('klein');
  });

  it('returns 0 for a falsy condition with no else-branch', () => {
    expect(ev('=IF(0;2)', host)).toBe(0);
  });

  it('never evaluates the untaken branch (short-circuit)', () => {
    expect(ev('=IF(1;2;1/0)', host)).toBe(2);
    expect(ev('=IF(B1=0;0;A1/B1)', host)).toBe(0);
  });

  it('propagates an error from the condition itself', () => {
    const r = ev('=IF(1/0;2;3)', host);
    expect(isError(r) && r.code).toBe('#DIV/0!');
  });

  it('wrong number of arguments -> #ERR', () => {
    expect(isError(ev('=IF(1)', host))).toBe(true);
    expect(isError(ev('=IF(1;2;3;4)', host))).toBe(true);
  });

  it('works with comparison operators as the condition', () => {
    expect(ev('=IF(A1>=8;1;0)', host)).toBe(1);
    expect(ev('=IF(A1<>8;1;0)', host)).toBe(0);
  });
});

describe('evaluator AND/OR/NOT', () => {
  const host = makeHost({ S1: { A1: 8, B1: 0, C1: 'x' } });

  it('AND is true only when every argument is truthy', () => {
    expect(ev('=AND(1;1;1)', host)).toBe(1);
    expect(ev('=AND(1;0;1)', host)).toBe(0);
    expect(ev('=AND(A1>0;A1<10)', host)).toBe(1);
    expect(ev('=AND(A1>0;A1<5)', host)).toBe(0);
  });

  it('OR is true when at least one argument is truthy', () => {
    expect(ev('=OR(0;0;1)', host)).toBe(1);
    expect(ev('=OR(0;0;0)', host)).toBe(0);
  });

  it('NOT inverts a single condition', () => {
    expect(ev('=NOT(1)', host)).toBe(0);
    expect(ev('=NOT(0)', host)).toBe(1);
    expect(ev('=NOT(B1=0)', host)).toBe(0);
  });

  it('a range argument checks every cell (Excel behaviour)', () => {
    const h = makeHost({ S1: { A1: 1, A2: 1, A3: 0 } });
    expect(ev('=AND(A1:A3)', h)).toBe(0);
    expect(ev('=OR(A1:A3)', h)).toBe(1);
  });

  it('empty cell and non-empty text follow the shared truthy convention', () => {
    expect(ev('=AND(Z9)', host)).toBe(0); // empty cell -> falsy
    expect(ev('=AND(C1)', host)).toBe(1); // non-empty text -> truthy
  });

  it('composes inside IF, unlike a bare chained comparison', () => {
    expect(ev('=IF(AND(A1>0;A1<10);"ok";"nein")', host)).toBe('ok');
  });

  it('does NOT short-circuit -- every argument is evaluated, same as real Excel', () => {
    expect(isError(ev('=AND(1=2;1/0)', host))).toBe(true);
    expect(isError(ev('=OR(1=1;1/0)', host))).toBe(true);
  });

  it('wrong arity -> #ERR', () => {
    expect(isError(ev('=AND()', host))).toBe(true);
    expect(isError(ev('=NOT(1;2)', host))).toBe(true);
    expect(isError(ev('=NOT()', host))).toBe(true);
  });
});

describe('evaluator error literals (ErrorLiteralNode)', () => {
  const host = makeHost({ S1: {} });

  it('evaluates directly to that FormulaError', () => {
    const r = ev('=#REF', host);
    expect(isError(r) && r.code).toBe('#REF');
  });

  it('propagates through arithmetic like any other error', () => {
    const r = ev('=1+#DIV/0!', host);
    expect(isError(r) && r.code).toBe('#DIV/0!');
  });

  it('usable as an IF branch, e.g. a deliberate #N/A', () => {
    expect(ev('=IF(1=0;#N/A;5)', host)).toBe(5);
    const r = ev('=IF(1=1;#N/A;5)', host);
    expect(isError(r) && r.code).toBe('#N/A');
  });
});

describe('evaluator comparisons', () => {
  const host = makeHost({ S1: { A1: 'Nord', B1: 5 } });

  it('numeric comparisons work as before, empty cell counts as 0', () => {
    expect(ev('=B1>=5', host)).toBe(1);
    expect(ev('=B1<>5', host)).toBe(0);
    expect(ev('=Z9=0', host)).toBe(1);
  });

  it('text equality is case-insensitive and trimmed, like XLOOKUP', () => {
    expect(ev('=A1="Nord"', host)).toBe(1);
    expect(ev('=A1="  nord "', host)).toBe(1);
    expect(ev('=A1="Süd"', host)).toBe(0);
  });

  it('text ordering compares lexicographically', () => {
    expect(ev('=A1<"Süd"', host)).toBe(1);
    expect(ev('=A1>"Süd"', host)).toBe(0);
  });

  it('an empty cell takes on whichever type the other side is', () => {
    expect(ev('=Z9=""', host)).toBe(1);
    expect(ev('=Z9="x"', host)).toBe(0);
  });

  it('a real number against real text never crossmatches: = / <> answer definitively, ordering is #VALUE!', () => {
    expect(ev('=B1="5"', host)).toBe(0);
    expect(ev('=B1<>"5"', host)).toBe(1);
    expect(isError(ev('=B1>"5"', host))).toBe(true);
    expect(isError(ev('=A1<5', host))).toBe(true);
  });
});

describe('evaluator & (text concatenation)', () => {
  const host = makeHost({ S1: { A1: 'Herr', B1: 5 } });

  it('joins two strings', () => {
    expect(ev('="a"&"b"', host)).toBe('ab');
  });

  it('renders a number plain, no locale formatting', () => {
    expect(ev('="Wert: "&B1', host)).toBe('Wert: 5');
  });

  it('an empty cell contributes "" (not "0")', () => {
    expect(ev('="["&Z9&"]"', host)).toBe('[]');
  });

  it('is left-associative and chains', () => {
    expect(ev('=A1&" "&"Wild"', host)).toBe('Herr Wild');
  });

  it('binds looser than + but tighter than a comparison', () => {
    expect(ev('="a"&1+2', host)).toBe('a3');
    expect(ev('=A1&"x"="Herrx"', host)).toBe(1);
  });

  it('propagates an error from either side', () => {
    expect(isError(ev('=(1/0)&"x"', host))).toBe(true);
  });
});

describe('evaluator CONCAT/CONCATENATE', () => {
  const host = makeHost({ S1: { A1: 'x', B1: 'y' } });

  it('joins any number of arguments, no separator', () => {
    expect(ev('=CONCAT("a";"b";"c")', host)).toBe('abc');
    expect(ev('=CONCATENATE(A1;" ";B1)', host)).toBe('x y');
  });

  it('an empty cell contributes ""', () => {
    expect(ev('=CONCAT(A1;Z9;B1)', host)).toBe('xy');
  });
});

describe('evaluator named ranges (NamedRefNode)', () => {
  it('a single-cell named range behaves exactly like that cell in scalar position', () => {
    const host = makeHost({ S1: { B2: 42 } }, 'S1', {
      preis: { qualifier: 'S1', start: { col: 1, row: 1 }, end: { col: 1, row: 1 } },
    });
    expect(ev('=Preis + 1', host)).toBe(43);
  });

  it('matching is case-insensitive and trimmed, like a table qualifier', () => {
    const host = makeHost({ S1: { B2: 42 } }, 'S1', {
      preis: { qualifier: 'S1', start: { col: 1, row: 1 }, end: { col: 1, row: 1 } },
    });
    expect(ev('=PREIS', host)).toBe(42);
  });

  it('a multi-cell named range works as a function argument (range position), same as SUM(B2:B3)', () => {
    const host = makeHost({ S1: { B2: 10, B3: 5 } }, 'S1', {
      bereich: { qualifier: 'S1', start: { col: 1, row: 1 }, end: { col: 1, row: 2 } },
    });
    expect(ev('=SUM(Bereich)', host)).toBe(15);
  });

  it('a multi-cell named range in scalar position is #VALUE!, same as a bare range', () => {
    const host = makeHost({ S1: { B2: 10, B3: 5 } }, 'S1', {
      bereich: { qualifier: 'S1', start: { col: 1, row: 1 }, end: { col: 1, row: 2 } },
    });
    expect(isError(ev('=Bereich', host))).toBe(true);
  });

  it('an unknown name is #NAME?', () => {
    expect(isError(ev('=DoesNotExist', makeHost({})))).toBe(true);
  });

  it('resolves through another table via its qualifier', () => {
    const host = makeHost({ S2: { C3: 7 } }, 'S1', {
      andernorts: { qualifier: 'S2', start: { col: 2, row: 2 }, end: { col: 2, row: 2 } },
    });
    expect(ev('=Andernorts * 2', host)).toBe(14);
  });
});

describe('evaluator XLOOKUP', () => {
  const textHost = makeHost({
    S1: {
      A2: 'Apfel', B2: 1.5,
      A3: 'Birne', B3: 2,
      A4: 'Kirsche', B4: 5,
      A5: 'Birne', B5: 99, // duplicate, for search-mode tests
    },
  });
  const bracketHost = makeHost({
    S1: { D2: 0, E2: 'A', D3: 10, E3: 'B', D4: 20, E4: 'C', D5: 30, E5: 'D' },
  });

  it('exact-matches text, case-insensitively and trimmed', () => {
    expect(ev('=XLOOKUP("Birne";A2:A4;B2:B4)', textHost)).toBe(2);
    expect(ev('=XLOOKUP("  BIRNE ";A2:A4;B2:B4)', textHost)).toBe(2);
  });

  it('exact-matches numbers', () => {
    expect(ev('=XLOOKUP(10;D2:D5;E2:E5)', bracketHost)).toBe('B');
  });

  it('does not cross-match a number against text', () => {
    const r = ev('=XLOOKUP(5;A2:A4;B2:B4)', textHost);
    expect(isError(r) && r.code).toBe('#N/A');
  });

  it('no match and no if_not_found -> #N/A', () => {
    const r = ev('=XLOOKUP("Traube";A2:A4;B2:B4)', textHost);
    expect(isError(r) && r.code).toBe('#N/A');
  });

  it('no match falls back to if_not_found', () => {
    expect(ev('=XLOOKUP("Traube";A2:A4;B2:B4;"?")', textHost)).toBe('?');
  });

  it('if_not_found is evaluated lazily (never touched on a match)', () => {
    expect(ev('=XLOOKUP("Birne";A2:A4;B2:B4;1/0)', textHost)).toBe(2);
  });

  it('search_mode: 1 (default) finds the first match, -1 the last', () => {
    expect(ev('=XLOOKUP("Birne";A2:A5;B2:B5)', textHost)).toBe(2);
    expect(ev('=XLOOKUP("Birne";A2:A5;B2:B5;;0;-1)', textHost)).toBe(99);
  });

  it('match_mode -1/1: exact match still wins outright', () => {
    expect(ev('=XLOOKUP(10;D2:D5;E2:E5;;-1)', bracketHost)).toBe('B');
    expect(ev('=XLOOKUP(10;D2:D5;E2:E5;;1)', bracketHost)).toBe('B');
  });

  it('match_mode -1: nearest smaller', () => {
    expect(ev('=XLOOKUP(15;D2:D5;E2:E5;;-1)', bracketHost)).toBe('B'); // 10
  });

  it('match_mode 1: nearest larger', () => {
    expect(ev('=XLOOKUP(15;D2:D5;E2:E5;;1)', bracketHost)).toBe('C'); // 20
  });

  it('match_mode -1/1: no qualifying candidate -> #N/A (or if_not_found)', () => {
    expect(isError(ev('=XLOOKUP(-5;D2:D5;E2:E5;;-1)', bracketHost))).toBe(true);
    expect(ev('=XLOOKUP(-5;D2:D5;E2:E5;"keine";-1)', bracketHost)).toBe('keine');
  });

  it('unsupported match_mode (2) or search_mode (2/-2) -> #ERR', () => {
    expect(isError(ev('=XLOOKUP("x";A2:A4;B2:B4;;2)', textHost))).toBe(true);
    expect(isError(ev('=XLOOKUP("x";A2:A4;B2:B4;;0;2)', textHost))).toBe(true);
  });

  it('mismatched lookup/return range sizes -> #VALUE!', () => {
    const r = ev('=XLOOKUP("x";A2:A4;B2:B5)', textHost);
    expect(isError(r) && r.code).toBe('#VALUE!');
  });

  it('wrong number of arguments -> #ERR', () => {
    expect(isError(ev('=XLOOKUP("x";A2:A4)', textHost))).toBe(true);
    expect(isError(ev('=XLOOKUP("x";A2:A4;B2:B4;1;2;3;4)', textHost))).toBe(true);
  });

  it('works with single cells (a 1-cell "range") for lookup/return', () => {
    expect(ev('=XLOOKUP("Apfel";A2;B2)', textHost)).toBe(1.5);
  });
});

describe('evaluator cross-sheet & errors', () => {
  const host = makeHost({
    S1: { A1: 3 },
    S2: { A1: 7, B1: 100 },
  });

  it('resolves Name!Ref and is case-insensitive', () => {
    expect(ev('=S2!A1 + S2!B1', host)).toBe(107);
    expect(ev('=s2!a1', host)).toBe(7);
  });

  it('accepts "::" the same as "!"', () => {
    expect(ev('=S2::A1 + S2::B1', host)).toBe(107);
    expect(ev('=s2::a1', host)).toBe(ev('=S2!A1', host));
  });

  it('unknown sheet -> #REF', () => {
    const r = ev('=Nix!A1', host);
    expect(isError(r) && r.code).toBe('#REF');
  });

  it('propagates an error from a referenced cell', () => {
    const h = makeHost({ S1: { A1: new FormulaError('#DIV/0!') } });
    const r = ev('=A1 + 1', h);
    expect(isError(r) && r.code).toBe('#DIV/0!');
    const r2 = ev('=SUM(A1; 5)', h);
    expect(isError(r2) && r2.code).toBe('#DIV/0!');
  });

  it('a bare range in scalar position -> #VALUE!', () => {
    const r = ev('=A1:A3 + 1', host);
    expect(isError(r) && r.code).toBe('#VALUE!');
  });

  it('text in arithmetic -> #VALUE!, numeric text is coerced', () => {
    const h = makeHost({ S1: { A1: 'abc', A2: '12' } });
    expect(isError(ev('=A1*2', h))).toBe(true);
    expect(ev('=A2*2', h)).toBe(24);
  });
});