export type FormulaValue = string | number | boolean | null;
export type FormulaRow = Record<string, unknown>;

type TokenKind = 'number' | 'string' | 'column' | 'identifier' | 'operator' | 'left' | 'right' | 'comma' | 'eof';
type Token = { kind: TokenKind; value: string; position: number };
type Node =
  | { kind: 'literal'; value: FormulaValue }
  | { kind: 'column'; name: string }
  | { kind: 'unary'; operator: string; value: Node }
  | { kind: 'binary'; operator: string; left: Node; right: Node }
  | { kind: 'call'; name: string; args: Node[] };

export class FormulaError extends Error {
  constructor(message: string, public readonly position?: number) {
    super(position === undefined ? message : `${message} at character ${position + 1}`);
    this.name = 'FormulaError';
  }
}

function tokenize(source: string): Token[] {
  const tokens: Token[] = [];
  let index = 0;
  while (index < source.length) {
    const start = index;
    const char = source[index]!;
    if (/\s/.test(char)) { index += 1; continue; }
    if (char === '[') {
      index += 1;
      let name = '';
      while (index < source.length && source[index] !== ']') name += source[index++]!;
      if (source[index] !== ']') throw new FormulaError('Unclosed column reference', start);
      index += 1;
      if (!name.trim()) throw new FormulaError('Column reference cannot be empty', start);
      tokens.push({ kind: 'column', value: name.trim(), position: start });
      continue;
    }
    if (char === '"') {
      index += 1;
      let value = '';
      let closed = false;
      while (index < source.length) {
        const current = source[index++]!;
        if (current === '"') { closed = true; break; }
        if (current === '\\') {
          const escaped = source[index++];
          if (escaped === undefined) break;
          value += escaped === 'n' ? '\n' : escaped === 't' ? '\t' : escaped;
        } else value += current;
      }
      if (!closed) throw new FormulaError('Unclosed text value', start);
      tokens.push({ kind: 'string', value, position: start });
      continue;
    }
    if (/\d/.test(char) || (char === '.' && /\d/.test(source[index + 1] ?? ''))) {
      const match = source.slice(index).match(/^(?:\d+(?:\.\d*)?|\.\d+)/)![0];
      index += match.length;
      tokens.push({ kind: 'number', value: match, position: start });
      continue;
    }
    if (/[A-Za-z_]/.test(char)) {
      const match = source.slice(index).match(/^[A-Za-z_][A-Za-z0-9_]*/)![0];
      index += match.length;
      tokens.push({ kind: 'identifier', value: match.toUpperCase(), position: start });
      continue;
    }
    const two = source.slice(index, index + 2);
    if (['<=', '>=', '<>', '!=', '=='].includes(two)) {
      tokens.push({ kind: 'operator', value: two, position: start });
      index += 2;
      continue;
    }
    if ('=<>+-*/&'.includes(char)) {
      tokens.push({ kind: 'operator', value: char, position: start });
      index += 1;
      continue;
    }
    if (char === '(' || char === ')' || char === ',') {
      tokens.push({ kind: char === '(' ? 'left' : char === ')' ? 'right' : 'comma', value: char, position: start });
      index += 1;
      continue;
    }
    throw new FormulaError(`Unexpected character "${char}"`, start);
  }
  tokens.push({ kind: 'eof', value: '', position: source.length });
  return tokens;
}

class Parser {
  private index = 0;
  readonly references = new Set<string>();
  constructor(private readonly tokens: Token[]) {}
  private current(): Token { return this.tokens[this.index]!; }
  private take(): Token { return this.tokens[this.index++]!; }
  private operator(value: string): boolean {
    const token = this.current();
    return (token.kind === 'operator' && token.value === value)
      || (token.kind === 'identifier' && token.value === value);
  }
  private expect(kind: TokenKind, message: string): Token {
    const token = this.current();
    if (token.kind !== kind) throw new FormulaError(message, token.position);
    return this.take();
  }
  parse(): Node {
    const node = this.parseOr();
    if (this.current().kind !== 'eof') throw new FormulaError('Unexpected input', this.current().position);
    return node;
  }
  private parseOr(): Node {
    let node = this.parseAnd();
    while (this.operator('OR')) { this.take(); node = { kind: 'binary', operator: 'OR', left: node, right: this.parseAnd() }; }
    return node;
  }
  private parseAnd(): Node {
    let node = this.parseComparison();
    while (this.operator('AND')) { this.take(); node = { kind: 'binary', operator: 'AND', left: node, right: this.parseComparison() }; }
    return node;
  }
  private parseComparison(): Node {
    let node = this.parseAdditive();
    const token = this.current();
    if (token.kind === 'operator' && ['=', '==', '!=', '<>', '<', '<=', '>', '>='].includes(token.value)) {
      this.take(); node = { kind: 'binary', operator: token.value, left: node, right: this.parseAdditive() };
    }
    return node;
  }
  private parseAdditive(): Node {
    let node = this.parseMultiplicative();
    while (this.operator('+') || this.operator('-') || this.operator('&')) {
      const operator = this.take().value;
      node = { kind: 'binary', operator, left: node, right: this.parseMultiplicative() };
    }
    return node;
  }
  private parseMultiplicative(): Node {
    let node = this.parseUnary();
    while (this.operator('*') || this.operator('/')) {
      const operator = this.take().value;
      node = { kind: 'binary', operator, left: node, right: this.parseUnary() };
    }
    return node;
  }
  private parseUnary(): Node {
    if (this.operator('-') || this.operator('+') || this.operator('NOT')) {
      const operator = this.take().value;
      return { kind: 'unary', operator, value: this.parseUnary() };
    }
    return this.parsePrimary();
  }
  private parsePrimary(): Node {
    const token = this.take();
    if (token.kind === 'number') return { kind: 'literal', value: Number(token.value) };
    if (token.kind === 'string') return { kind: 'literal', value: token.value };
    if (token.kind === 'column') { this.references.add(token.value); return { kind: 'column', name: token.value }; }
    if (token.kind === 'left') {
      const node = this.parseOr();
      this.expect('right', 'Expected closing parenthesis');
      return node;
    }
    if (token.kind === 'identifier') {
      if (token.value === 'TRUE') return { kind: 'literal', value: true };
      if (token.value === 'FALSE') return { kind: 'literal', value: false };
      if (token.value === 'NULL' || token.value === 'BLANK') return { kind: 'literal', value: null };
      if (this.current().kind !== 'left') throw new FormulaError(`Unknown keyword "${token.value}"`, token.position);
      this.take();
      const args: Node[] = [];
      if (this.current().kind !== 'right') {
        do {
          args.push(this.parseOr());
          if (this.current().kind !== 'comma') break;
          this.take();
        } while (true);
      }
      this.expect('right', `Expected closing parenthesis for ${token.value}`);
      return { kind: 'call', name: token.value, args };
    }
    throw new FormulaError('Expected a value, column, or function', token.position);
  }
}

const blank = (value: unknown) => value === null || value === undefined || value === '';
const truthy = (value: unknown) => !blank(value) && value !== false && value !== 0;
const number = (value: unknown): number => {
  const result = Number(value);
  if (!Number.isFinite(result)) throw new FormulaError(`"${String(value)}" is not a number`);
  return result;
};
const comparable = (value: unknown) => typeof value === 'string' ? value.toLocaleLowerCase() : value;

function evaluate(node: Node, row: FormulaRow): FormulaValue {
  if (node.kind === 'literal') return node.value;
  if (node.kind === 'column') return (row[node.name] ?? null) as FormulaValue;
  if (node.kind === 'unary') {
    const value = evaluate(node.value, row);
    if (node.operator === 'NOT') return !truthy(value);
    return node.operator === '-' ? -number(value) : number(value);
  }
  if (node.kind === 'binary') {
    if (node.operator === 'AND') return truthy(evaluate(node.left, row)) && truthy(evaluate(node.right, row));
    if (node.operator === 'OR') return truthy(evaluate(node.left, row)) || truthy(evaluate(node.right, row));
    const left = evaluate(node.left, row);
    const right = evaluate(node.right, row);
    if (node.operator === '&') return `${blank(left) ? '' : String(left)}${blank(right) ? '' : String(right)}`;
    if (node.operator === '+') return number(left) + number(right);
    if (node.operator === '-') return number(left) - number(right);
    if (node.operator === '*') return number(left) * number(right);
    if (node.operator === '/') { const divisor = number(right); if (divisor === 0) throw new FormulaError('Cannot divide by zero'); return number(left) / divisor; }
    const a = comparable(left) as any;
    const b = comparable(right) as any;
    if (node.operator === '=' || node.operator === '==') return a === b;
    if (node.operator === '!=' || node.operator === '<>') return a !== b;
    if (node.operator === '<') return a < b;
    if (node.operator === '<=') return a <= b;
    if (node.operator === '>') return a > b;
    return a >= b;
  }
  const args = node.args.map((arg) => evaluate(arg, row));
  const count = (minimum: number, maximum = minimum) => {
    if (args.length < minimum || args.length > maximum) throw new FormulaError(`${node.name} expects ${minimum === maximum ? minimum : `${minimum}-${maximum}`} argument(s)`);
  };
  switch (node.name) {
    case 'IF': count(3); return truthy(args[0]) ? args[1]! : args[2]!;
    case 'AND': return args.every(truthy);
    case 'OR': return args.some(truthy);
    case 'NOT': count(1); return !truthy(args[0]);
    case 'ISBLANK': count(1); return blank(args[0]);
    case 'CONTAINS': count(2); return String(args[0] ?? '').toLocaleLowerCase().includes(String(args[1] ?? '').toLocaleLowerCase());
    case 'STARTSWITH': count(2); return String(args[0] ?? '').toLocaleLowerCase().startsWith(String(args[1] ?? '').toLocaleLowerCase());
    case 'ENDSWITH': count(2); return String(args[0] ?? '').toLocaleLowerCase().endsWith(String(args[1] ?? '').toLocaleLowerCase());
    case 'LOWER': count(1); return String(args[0] ?? '').toLocaleLowerCase();
    case 'UPPER': count(1); return String(args[0] ?? '').toLocaleUpperCase();
    case 'TRIM': count(1); return String(args[0] ?? '').trim();
    case 'LEN': count(1); return String(args[0] ?? '').length;
    case 'ABS': count(1); return Math.abs(number(args[0]));
    case 'ROUND': count(1, 2); return Number(number(args[0]).toFixed(Math.max(0, Math.min(10, Number(args[1] ?? 0)))));
    case 'COALESCE': if (!args.length) throw new FormulaError('COALESCE expects at least 1 argument'); return args.find((value) => !blank(value)) ?? null;
    default: throw new FormulaError(`Unknown function "${node.name}"`);
  }
}

const FUNCTION_ARGUMENTS: Record<string, [number, number]> = {
  IF: [3, 3], AND: [1, Number.POSITIVE_INFINITY], OR: [1, Number.POSITIVE_INFINITY],
  NOT: [1, 1], ISBLANK: [1, 1], CONTAINS: [2, 2], STARTSWITH: [2, 2],
  ENDSWITH: [2, 2], LOWER: [1, 1], UPPER: [1, 1], TRIM: [1, 1], LEN: [1, 1],
  ABS: [1, 1], ROUND: [1, 2], COALESCE: [1, Number.POSITIVE_INFINITY],
};

function validateNode(node: Node): void {
  if (node.kind === 'unary') validateNode(node.value);
  if (node.kind === 'binary') { validateNode(node.left); validateNode(node.right); }
  if (node.kind !== 'call') return;
  const limits = FUNCTION_ARGUMENTS[node.name];
  if (!limits) throw new FormulaError(`Unknown function "${node.name}"`);
  const [minimum, maximum] = limits;
  if (node.args.length < minimum || node.args.length > maximum) {
    const expected = maximum === Number.POSITIVE_INFINITY
      ? `at least ${minimum}`
      : minimum === maximum ? String(minimum) : `${minimum}-${maximum}`;
    throw new FormulaError(`${node.name} expects ${expected} argument(s)`);
  }
  node.args.forEach(validateNode);
}

export function compileFormula(source: string, columns: string[]) {
  if (!source.trim()) throw new FormulaError('Formula is required');
  const parser = new Parser(tokenize(source));
  const node = parser.parse();
  validateNode(node);
  const available = new Set(columns);
  for (const reference of parser.references) {
    if (!available.has(reference)) throw new FormulaError(`Column "${reference}" does not exist`);
  }
  return {
    references: [...parser.references],
    evaluate: (row: FormulaRow): FormulaValue => evaluate(node, row),
  };
}