visi_core/core/

parser.rs

use crate::core::{CompiledFormula, FormulaPart, RefType, Sheet, SheetSection, generate_unique_id};

#[derive(Debug, Clone, Copy, PartialEq, Eq, Hash, serde::Serialize, serde::Deserialize)]
pub enum Op {
    Add,
    Sub,
    Mul,
    Div,
    Exp,
    Percent,
    ImplicitIntersection,
    Spill,
    Concat,
    Eq,
    Ne,
    Lt,
    Gt,
    Le,
    Ge,
    Intersect,
    Union,
}

#[derive(Debug, Clone, PartialEq)]
pub enum Expr {
    Number(f64),
    String(String),
    Boolean(bool),
    Error(&'static str),
    CellRef {
        sheet: Option<String>,
        row: usize,
        col: usize,
        row_abs: bool,
        col_abs: bool,
    },
    RangeRef {
        sheet: Option<String>,
        start_row: usize,
        start_col: usize,
        end_row: usize,
        end_col: usize,
        start_row_abs: bool,
        start_col_abs: bool,
        end_row_abs: bool,
        end_col_abs: bool,
    },
    List(Vec<Expr>),
    Slice {
        expr: Box<Expr>,
        start: Option<Box<Expr>>,
        end: Option<Box<Expr>>,
    },
    FunctionCall {
        name: String,
        args: Vec<Expr>,
    },
    BinaryOp {
        op: Op,
        left: Box<Expr>,
        right: Box<Expr>,
    },
    UnaryOp {
        op: Op,
        expr: Box<Expr>,
    },
    Identifier(String),
    StructuredRef {
        sheet: Option<String>,
        column: Option<String>,
        is_this_row: bool,
        section: SheetSection,
    },
}

#[derive(Debug, Clone, PartialEq)]
pub enum EvalToken {
    Number(f64),
    String(String),
    Boolean(bool),
    Identifier(String),
    Error(&'static str),
    Op(Op),
    OpenParen,
    CloseParen,
    OpenBracket,
    CloseBracket,
    Comma,
    Colon,
    Exclamation,
    Dot,
    StructuredRef {
        sheet: Option<String>,
        column: Option<String>,
        is_this_row: bool,
        section: SheetSection,
    },
}

/// Renders a 0-based column index in its letter form: 0 is `A`, 25 is
/// `Z`, 26 is `AA`.
pub fn col_idx_to_letters(mut col: usize) -> String {
    let mut letters = String::new();
    loop {
        let remainder = col % 26;
        letters.insert(0, (b'A' + remainder as u8) as char);
        if col < 26 {
            break;
        }
        col = col / 26 - 1;
    }
    letters
}

/// Convert Excel letter notation (e.g. "A", "Z", "AA")
/// to 0-based column index. Clamps result to 0 if input is invalid.
pub fn col_letters_to_idx(col_str: &str) -> usize {
    let mut col = 0;
    for c in col_str.chars() {
        if c.is_ascii_alphabetic() {
            col = col * 26 + (c.to_ascii_uppercase() as usize - 'A' as usize + 1);
        }
    }
    if col > 0 { col - 1 } else { 0 }
}

/// Converts an already-split A1 reference into a 0-based `(row, col)`.
///
/// `col_str` is the letters and `row_str` the digits, so `("B", "3")` gives
/// `(2, 1)`. Case-insensitive, and non-alphabetic characters in `col_str` are
/// skipped.
///
/// Clamps to index 0 instead of failing.
pub fn parse_a1_coordinates(col_str: &str, row_str: &str) -> (usize, usize) {
    let col_idx = col_letters_to_idx(col_str);

    let row_val: usize = row_str.parse().unwrap_or(1);
    let row_idx = if row_val > 0 { row_val - 1 } else { 0 };

    (row_idx, col_idx)
}

/// Parse a cell reference string like "A1", "C10", or "Sheet1!B5"
/// Returns (optional_sheet_name, row_idx, col_idx)
pub fn parse_cell_ref(cell_str: &str) -> Result<(Option<String>, usize, usize), String> {
    let trimmed = cell_str.trim();
    if trimmed.is_empty() {
        return Err("Cell reference cannot be empty".to_string());
    }

    let (sheet_part, cell_part) = split_sheet_reference(trimmed);
    let (row_idx, col_idx, _, _) = parse_cell_ref_parts(cell_part)
        .ok_or_else(|| format!("Invalid cell reference format: '{}'", cell_str))?;

    Ok((sheet_part, row_idx, col_idx))
}

/// Parse a range reference string (e.g."A1:C10", "Sheet1!A1:B5", or "A1")
/// Returns (optional_sheet_name, start_row, start_col, end_row, end_col)
pub fn parse_range_ref(
    range_str: &str,
) -> Result<(Option<String>, usize, usize, usize, usize), String> {
    let trimmed = range_str.trim();
    if trimmed.is_empty() {
        return Err("Range reference cannot be empty".to_string());
    }

    let (sheet_part, range_part) = split_sheet_reference(trimmed);

    if let Some((start_str, end_str)) = range_part.split_once(':') {
        let (_, start_row, start_col) = parse_cell_ref(start_str)?;
        let (_, end_row, end_col) = parse_cell_ref(end_str)?;

        Ok((
            sheet_part,
            start_row.min(end_row),
            start_col.min(end_col),
            start_row.max(end_row),
            start_col.max(end_col),
        ))
    } else {
        let (_, row_idx, col_idx) = parse_cell_ref(range_part)?;
        Ok((sheet_part, row_idx, col_idx, row_idx, col_idx))
    }
}

fn split_sheet_reference(reference: &str) -> (Option<String>, &str) {
    if let Some(pos) = reference.rfind('!') {
        (
            Some(reference[..pos].trim_matches('\'').to_string()),
            &reference[pos + 1..],
        )
    } else {
        (None, reference)
    }
}

fn parse_cell_ref_parts(s: &str) -> Option<(usize, usize, bool, bool)> {
    let chars: Vec<char> = s.chars().collect();
    let mut idx = 0;

    let mut col_abs = false;
    if idx < chars.len() && chars[idx] == '$' {
        col_abs = true;
        idx += 1;
    }

    let mut col_str = String::new();
    while idx < chars.len() && chars[idx].is_ascii_alphabetic() {
        col_str.push(chars[idx]);
        idx += 1;
    }
    if col_str.is_empty() || col_str.len() > 3 {
        return None;
    }

    let mut row_abs = false;
    if idx < chars.len() && chars[idx] == '$' {
        row_abs = true;
        idx += 1;
    }

    let mut row_str = String::new();
    while idx < chars.len() && chars[idx].is_ascii_digit() {
        row_str.push(chars[idx]);
        idx += 1;
    }
    if row_str.is_empty() {
        return None;
    }
    if row_str.parse::<usize>().ok()? == 0 {
        return None;
    }

    if idx < chars.len() {
        return None;
    }

    let (row_idx, col_idx) = parse_a1_coordinates(&col_str, &row_str);
    Some((row_idx, col_idx, row_abs, col_abs))
}

fn parse_column_ref(s: &str) -> Option<(usize, bool)> {
    let chars: Vec<char> = s.chars().collect();
    let mut idx = 0;
    let mut col_abs = false;
    if idx < chars.len() && chars[idx] == '$' {
        col_abs = true;
        idx += 1;
    }
    let mut col_str = String::new();
    while idx < chars.len() && chars[idx].is_ascii_alphabetic() {
        col_str.push(chars[idx]);
        idx += 1;
    }
    if col_str.is_empty() || col_str.len() > 3 {
        return None;
    }
    if idx < chars.len() {
        return None;
    }
    let mut col = 0;
    for c in col_str.chars() {
        col = col * 26 + (c.to_ascii_uppercase() as usize - 'A' as usize + 1);
    }
    let col_idx = if col > 0 { col - 1 } else { 0 };
    Some((col_idx, col_abs))
}

fn parse_row_ref(s: &str) -> Option<(usize, bool)> {
    let chars: Vec<char> = s.chars().collect();
    let mut idx = 0;
    let mut row_abs = false;
    if idx < chars.len() && chars[idx] == '$' {
        row_abs = true;
        idx += 1;
    }
    let mut row_str = String::new();
    while idx < chars.len() && chars[idx].is_ascii_digit() {
        row_str.push(chars[idx]);
        idx += 1;
    }
    if row_str.is_empty() || idx < chars.len() {
        return None;
    }
    let row_val: usize = row_str.parse().ok()?;
    Some((row_val.saturating_sub(1), row_abs))
}

fn parse_cell_pattern(chars: &[char], mut idx: usize) -> Option<(usize, usize, bool, bool, usize)> {
    let mut col_abs = false;
    if idx < chars.len() && chars[idx] == '$' {
        col_abs = true;
        idx += 1;
    }

    let mut col_str = String::new();
    while idx < chars.len() && chars[idx].is_ascii_alphabetic() {
        col_str.push(chars[idx]);
        idx += 1;
    }
    if col_str.is_empty() || col_str.len() > 3 {
        return None;
    }

    let mut row_abs = false;
    if idx < chars.len() && chars[idx] == '$' {
        row_abs = true;
        idx += 1;
    }

    let mut row_str = String::new();
    while idx < chars.len() && chars[idx].is_ascii_digit() {
        row_str.push(chars[idx]);
        idx += 1;
    }
    if row_str.is_empty() {
        return None;
    }

    if idx < chars.len() && (chars[idx].is_ascii_alphabetic() || chars[idx] == '_') {
        return None;
    }

    let (row_idx, col_idx) = parse_a1_coordinates(&col_str, &row_str);
    Some((row_idx, col_idx, row_abs, col_abs, idx))
}

fn parse_col_pattern(chars: &[char], mut idx: usize) -> Option<(usize, bool, usize)> {
    let mut col_abs = false;
    if idx < chars.len() && chars[idx] == '$' {
        col_abs = true;
        idx += 1;
    }
    let mut col_str = String::new();
    while idx < chars.len() && chars[idx].is_ascii_alphabetic() {
        col_str.push(chars[idx]);
        idx += 1;
    }
    if col_str.is_empty() || col_str.len() > 3 {
        return None;
    }
    if idx < chars.len() && chars[idx].is_ascii_digit() {
        return None;
    }
    if idx < chars.len() && (chars[idx].is_ascii_alphabetic() || chars[idx] == '_') {
        return None;
    }
    let mut col = 0;
    for c in col_str.chars() {
        col = col * 26 + (c.to_ascii_uppercase() as usize - 'A' as usize + 1);
    }
    let col_idx = if col > 0 { col - 1 } else { 0 };
    Some((col_idx, col_abs, idx))
}

fn parse_row_pattern(chars: &[char], mut idx: usize) -> Option<(usize, bool, usize)> {
    let mut row_abs = false;
    if idx < chars.len() && chars[idx] == '$' {
        row_abs = true;
        idx += 1;
    }
    let mut row_str = String::new();
    while idx < chars.len() && chars[idx].is_ascii_digit() {
        row_str.push(chars[idx]);
        idx += 1;
    }
    if row_str.is_empty() {
        return None;
    }
    if idx < chars.len() && (chars[idx].is_ascii_alphabetic() || chars[idx] == '_') {
        return None;
    }
    let row_val: usize = row_str.parse().ok()?;
    Some((row_val.saturating_sub(1), row_abs, idx))
}

#[derive(Debug, Clone)]
enum FoundRef {
    Cell {
        sheet: Option<String>,
        row: usize,
        col: usize,
        row_abs: bool,
        col_abs: bool,
    },
    Range {
        sheet: Option<String>,
        start_row: usize,
        start_col: usize,
        end_row: usize,
        end_col: usize,
        start_row_abs: bool,
        start_col_abs: bool,
        end_row_abs: bool,
        end_col_abs: bool,
    },
    Structured {
        sheet: Option<String>,
        column: Option<String>,
        is_this_row: bool,
        section: SheetSection,
    },
}

fn parse_bracketed_term(chars: &[char], mut idx: usize) -> Option<(String, usize)> {
    if idx >= chars.len() || chars[idx] != '[' {
        return None;
    }
    idx += 1;
    while idx < chars.len() && chars[idx].is_whitespace() {
        idx += 1;
    }
    let mut quote = None;
    if idx < chars.len() && (chars[idx] == '"' || chars[idx] == '\'') {
        quote = Some(chars[idx]);
        idx += 1;
    }
    let mut term = String::new();
    while idx < chars.len() {
        if let Some(q) = quote {
            if chars[idx] == q {
                idx += 1;
                break;
            }
        } else {
            if chars[idx] == ']' {
                break;
            }
            if chars[idx] == ','
                || chars[idx] == ':'
                || chars[idx] == '+'
                || chars[idx] == '-'
                || chars[idx] == '*'
                || chars[idx] == '/'
            {
                return None;
            }
        }
        term.push(chars[idx]);
        idx += 1;
    }
    while idx < chars.len() && chars[idx].is_whitespace() {
        idx += 1;
    }
    if idx < chars.len() && chars[idx] == ']' {
        Some((term.trim().to_string(), idx + 1))
    } else {
        None
    }
}

fn parse_structured_specifier(
    chars: &[char],
    mut idx: usize,
) -> Option<(Option<String>, bool, SheetSection, usize)> {
    if idx >= chars.len() || chars[idx] != '[' {
        return None;
    }
    idx += 1;

    while idx < chars.len() && chars[idx].is_whitespace() {
        idx += 1;
    }

    let mut is_this_row = false;
    let mut section = SheetSection::Data;
    let mut column = None;

    if idx < chars.len() && chars[idx] == '@' {
        is_this_row = true;
        idx += 1;
        if idx < chars.len() && chars[idx] == '[' {
            if let Some((col_name, next_idx)) = parse_bracketed_term(chars, idx) {
                if !col_name.is_empty() {
                    column = Some(col_name);
                }
                idx = next_idx;
            } else {
                return None;
            }
        } else {
            let mut col_name = String::new();
            while idx < chars.len() && chars[idx] != ']' {
                col_name.push(chars[idx]);
                idx += 1;
            }
            let trimmed = col_name.trim().to_string();
            column = if trimmed.is_empty() {
                None
            } else {
                Some(trimmed)
            };
        }
        if idx < chars.len() && chars[idx] == ']' {
            return Some((column, is_this_row, section, idx + 1));
        } else {
            return None;
        }
    }

    if idx < chars.len() && chars[idx] == '[' {
        if let Some((term1, next_idx)) = parse_bracketed_term(chars, idx) {
            idx = next_idx;
            if term1.starts_with('#') {
                match term1.as_str() {
                    "#This Row" => is_this_row = true,
                    "#Headers" => section = SheetSection::Headers,
                    "#Totals" => section = SheetSection::Totals,
                    "#Data" => section = SheetSection::Data,
                    "#All" => section = SheetSection::All,
                    _ => {}
                }
                while idx < chars.len() && (chars[idx].is_whitespace() || chars[idx] == ',') {
                    idx += 1;
                }
                if idx < chars.len() && chars[idx] == '[' {
                    if let Some((col_name, next_idx2)) = parse_bracketed_term(chars, idx) {
                        if !col_name.is_empty() {
                            column = Some(col_name);
                        }
                        idx = next_idx2;
                    } else {
                        return None;
                    }
                }
            } else if !term1.is_empty() {
                column = Some(term1);
            }
        } else {
            return None;
        }
        while idx < chars.len() && chars[idx].is_whitespace() {
            idx += 1;
        }
        if idx < chars.len() && chars[idx] == ']' {
            return Some((column, is_this_row, section, idx + 1));
        } else {
            return None;
        }
    }

    let mut term = String::new();
    let mut quote = None;
    if idx < chars.len() && (chars[idx] == '"' || chars[idx] == '\'') {
        quote = Some(chars[idx]);
        idx += 1;
    }
    while idx < chars.len() {
        if let Some(q) = quote {
            if chars[idx] == q {
                idx += 1;
                break;
            }
        } else {
            if chars[idx] == ']' {
                break;
            }
            if chars[idx] == ','
                || chars[idx] == ':'
                || chars[idx] == '+'
                || chars[idx] == '-'
                || chars[idx] == '*'
                || chars[idx] == '/'
            {
                return None;
            }
        }
        term.push(chars[idx]);
        idx += 1;
    }
    term = term.trim().to_string();
    if term.starts_with('#') {
        match term.as_str() {
            "#This Row" => is_this_row = true,
            "#Headers" => section = SheetSection::Headers,
            "#Totals" => section = SheetSection::Totals,
            "#Data" => section = SheetSection::Data,
            "#All" => section = SheetSection::All,
            _ => {}
        }
    } else if !term.is_empty() {
        column = Some(term);
    }

    if idx < chars.len() && chars[idx] == ']' {
        return Some((column, is_this_row, section, idx + 1));
    }

    None
}

fn try_parse_ref(chars: &[char], start_idx: usize) -> Option<(FoundRef, usize)> {
    if start_idx > 0 {
        let prev = chars[start_idx - 1];
        if prev.is_ascii_alphanumeric() || prev == '_' {
            return None;
        }
    }
    let mut idx = start_idx;
    let mut sheet = None;

    let mut has_explicit_table = false;
    if idx < chars.len() && chars[idx] == '\'' {
        let mut t_name = String::new();
        let mut j = idx + 1;
        while j < chars.len() && chars[j] != '\'' {
            t_name.push(chars[j]);
            j += 1;
        }
        if j < chars.len() && chars[j] == '\'' {
            if j + 1 < chars.len() && chars[j + 1] == '!' {
                sheet = Some(t_name);
                idx = j + 2;
                has_explicit_table = true;
            } else if j + 1 < chars.len() && chars[j + 1] == '[' {
                sheet = Some(t_name);
                idx = j + 1;
                has_explicit_table = true;
            }
        }
    }

    if !has_explicit_table {
        let mut t_name = String::new();
        let mut j = idx;
        if j < chars.len() && (chars[j].is_ascii_alphabetic() || chars[j] == '_') {
            while j < chars.len()
                && (chars[j].is_ascii_alphanumeric() || chars[j] == '_' || chars[j] == '.')
            {
                t_name.push(chars[j]);
                j += 1;
            }
            if j < chars.len() && chars[j] == '!' {
                if t_name != "iloc" && t_name != "get" {
                    sheet = Some(t_name);
                    idx = j + 1;
                }
            } else if j < chars.len() && chars[j] == '[' && t_name != "iloc" && t_name != "get" {
                let mut k = j + 1;
                let mut quote = None;
                if k < chars.len() && (chars[k] == '"' || chars[k] == '\'') {
                    quote = Some(chars[k]);
                    k += 1;
                }
                let mut is_column = true;
                let mut has_chars = false;
                while k < chars.len() {
                    if let Some(q) = quote {
                        if chars[k] == q {
                            break;
                        }
                    } else {
                        if chars[k] == ']' {
                            break;
                        }
                        if chars[k] == ':'
                            || chars[k] == ','
                            || chars[k] == '+'
                            || chars[k] == '-'
                            || chars[k] == '*'
                            || chars[k] == '/'
                        {
                            is_column = false;
                            break;
                        }
                    }
                    has_chars = true;
                    k += 1;
                }
                if is_column && has_chars {
                    sheet = Some(t_name);
                    idx = j;
                }
            }
        }
    }

    if idx < chars.len()
        && chars[idx] == '['
        && let Some((column, is_this_row, section, next_idx)) =
            parse_structured_specifier(chars, idx)
    {
        return Some((
            FoundRef::Structured {
                sheet,
                column,
                is_this_row,
                section,
            },
            next_idx,
        ));
    }

    if let Some((start_col, start_col_abs, next_idx)) = parse_col_pattern(chars, idx)
        && next_idx < chars.len()
        && chars[next_idx] == ':'
        && let Some((end_col, end_col_abs, end_idx)) = parse_col_pattern(chars, next_idx + 1)
    {
        return Some((
            FoundRef::Range {
                sheet: sheet.clone(),
                start_row: 0,
                start_col,
                end_row: usize::MAX,
                end_col,
                start_row_abs: true,
                start_col_abs,
                end_row_abs: true,
                end_col_abs,
            },
            end_idx,
        ));
    }

    if let Some((start_row, start_row_abs, next_idx)) = parse_row_pattern(chars, idx)
        && next_idx < chars.len()
        && chars[next_idx] == ':'
        && let Some((end_row, end_row_abs, end_idx)) = parse_row_pattern(chars, next_idx + 1)
    {
        return Some((
            FoundRef::Range {
                sheet,
                start_row,
                start_col: 0,
                end_row,
                end_col: usize::MAX,
                start_row_abs,
                start_col_abs: true,
                end_row_abs,
                end_col_abs: true,
            },
            end_idx,
        ));
    }

    if let Some((start_row, start_col, start_row_abs, start_col_abs, next_idx)) =
        parse_cell_pattern(chars, idx)
    {
        if next_idx < chars.len()
            && chars[next_idx] == ':'
            && let Some((end_row, end_col, end_row_abs, end_col_abs, end_idx)) =
                parse_cell_pattern(chars, next_idx + 1)
        {
            return Some((
                FoundRef::Range {
                    sheet,
                    start_row,
                    start_col,
                    end_row,
                    end_col,
                    start_row_abs,
                    start_col_abs,
                    end_row_abs,
                    end_col_abs,
                },
                end_idx,
            ));
        }

        return Some((
            FoundRef::Cell {
                sheet,
                row: start_row,
                col: start_col,
                row_abs: start_row_abs,
                col_abs: start_col_abs,
            },
            next_idx,
        ));
    }

    None
}

pub(crate) fn encode_xlsx_spill_references(code: &str) -> String {
    if !code.contains('#') {
        return code.to_string();
    }
    let chars: Vec<char> = code.chars().collect();
    let mut out = String::with_capacity(code.len());
    let mut i = 0;
    while i < chars.len() {
        if let Some((_, end)) = try_parse_ref(&chars, i) {
            let reference: String = chars[i..end].iter().collect();
            if chars.get(end) == Some(&'#') {
                out.push_str("_xlfn.ANCHORARRAY(");
                out.push_str(&reference);
                out.push(')');
                i = end + 1;
            } else {
                out.push_str(&reference);
                i = end;
            }
        } else if matches!(chars[i], '"' | '\'') {
            let quote = chars[i];
            out.push(quote);
            i += 1;
            while i < chars.len() {
                let c = chars[i];
                out.push(c);
                i += 1;
                if c == quote {
                    if chars.get(i) == Some(&quote) {
                        out.push(quote);
                        i += 1;
                    } else {
                        break;
                    }
                }
            }
        } else {
            out.push(chars[i]);
            i += 1;
        }
    }
    out
}

pub fn compile_formula(code: &str, sheets: &[Sheet]) -> CompiledFormula {
    let chars: Vec<char> = code.chars().collect();
    let mut parts = Vec::new();
    let mut last_idx = 0;

    let mut in_quote = None;
    let mut i = 0;
    while i < chars.len() {
        let c = chars[i];
        if let Some(q) = in_quote {
            if c == '\\' {
                i = (i + 2).min(chars.len());
                continue;
            } else if c == q {
                in_quote = None;
            }
            i += 1;
            continue;
        }

        if let Some((found_ref, next_i)) = try_parse_ref(&chars, i) {
            if i > last_idx {
                let text: String = chars[last_idx..i].iter().collect();
                parts.push(FormulaPart::Text(text));
            }

            match found_ref {
                FoundRef::Cell {
                    sheet,
                    row,
                    col,
                    row_abs,
                    col_abs,
                } => {
                    let sheet_id = if let Some(ref name) = sheet {
                        if let Some(t) = sheets.iter().find(|t| t.name == *name) {
                            t.id
                        } else if !sheets.is_empty() {
                            sheets[0].id
                        } else {
                            0
                        }
                    } else if !sheets.is_empty() {
                        sheets[0].id
                    } else {
                        0
                    };

                    parts.push(FormulaPart::SheetReference {
                        sheet_id,
                        row,
                        col,
                        row_ref_type: if row_abs {
                            RefType::Absolute
                        } else {
                            RefType::Relative
                        },
                        col_ref_type: if col_abs {
                            RefType::Absolute
                        } else {
                            RefType::Relative
                        },
                    });
                }
                FoundRef::Range {
                    sheet,
                    start_row,
                    start_col,
                    end_row,
                    end_col,
                    start_row_abs,
                    start_col_abs,
                    end_row_abs,
                    end_col_abs,
                } => {
                    let sheet_id = if let Some(ref name) = sheet {
                        if let Some(t) = sheets.iter().find(|t| t.name == *name) {
                            t.id
                        } else if !sheets.is_empty() {
                            sheets[0].id
                        } else {
                            0
                        }
                    } else if !sheets.is_empty() {
                        sheets[0].id
                    } else {
                        0
                    };

                    parts.push(FormulaPart::RangeReference {
                        sheet_id,
                        start_row,
                        start_col,
                        end_row,
                        end_col,
                        start_row_ref_type: if start_row_abs {
                            RefType::Absolute
                        } else {
                            RefType::Relative
                        },
                        start_col_ref_type: if start_col_abs {
                            RefType::Absolute
                        } else {
                            RefType::Relative
                        },
                        end_row_ref_type: if end_row_abs {
                            RefType::Absolute
                        } else {
                            RefType::Relative
                        },
                        end_col_ref_type: if end_col_abs {
                            RefType::Absolute
                        } else {
                            RefType::Relative
                        },
                    });
                }
                FoundRef::Structured {
                    sheet,
                    column,
                    is_this_row,
                    section,
                } => {
                    let default_sheet_name = sheets.first().map(|s| s.name.clone());
                    let ref_name = sheet.clone().or(default_sheet_name);
                    let matches_real_table = ref_name.is_some_and(|name| {
                        sheets
                            .iter()
                            .any(|s| s.tables.iter().any(|t| t.name.eq_ignore_ascii_case(&name)))
                    });

                    if matches_real_table {
                        let text: String = chars[i..next_i].iter().collect();
                        parts.push(FormulaPart::Text(text));
                    } else {
                        let sheet_id = if let Some(ref name) = sheet {
                            if let Some(named_sheet) = sheets.iter().find(|s| s.name == *name) {
                                named_sheet.id
                            } else if !sheets.is_empty() {
                                sheets[0].id
                            } else {
                                0
                            }
                        } else if !sheets.is_empty() {
                            sheets[0].id
                        } else {
                            0
                        };

                        let col_id = if let Some(ref col_name) = column {
                            if let Some(sheet_obj) = sheets.iter().find(|s| s.id == sheet_id) {
                                if let Some(col) =
                                    sheet_obj.columns.iter().find(|c| c.name == *col_name)
                                {
                                    Some(col.id)
                                } else {
                                    Some(generate_unique_id())
                                }
                            } else {
                                Some(generate_unique_id())
                            }
                        } else {
                            None
                        };

                        parts.push(FormulaPart::StructuredReference {
                            sheet_id,
                            col_id,
                            is_this_row,
                            section,
                        });
                    }
                }
            }

            i = next_i;
            last_idx = i;
        } else {
            if c == '"' || c == '\'' {
                in_quote = Some(c);
            }
            i += 1;
        }
    }

    if i > last_idx {
        let text: String = chars[last_idx..i].iter().collect();
        parts.push(FormulaPart::Text(text));
    }

    CompiledFormula { parts }
}

pub fn serialize_formula(formula: &CompiledFormula, sheets: &[Sheet]) -> String {
    let get_sheet_name = |sheet_id: u64, sheets: &[Sheet]| -> String {
        if let Some(sheet) = sheets.iter().find(|s| s.id == sheet_id) {
            if sheet.name.contains(' ') {
                format!("'{}'", sheet.name)
            } else {
                sheet.name.clone()
            }
        } else {
            "table_deleted".to_string()
        }
    };

    let mut result = String::new();
    for part in &formula.parts {
        match part {
            FormulaPart::Text(s) => result.push_str(s),
            FormulaPart::SheetReference {
                sheet_id,
                row,
                col,
                row_ref_type,
                col_ref_type,
            } => {
                let col_letter = col_idx_to_letters(*col);
                let r_prefix = match row_ref_type {
                    RefType::Absolute => "$",
                    RefType::Relative => "",
                };
                let c_prefix = match col_ref_type {
                    RefType::Absolute => "$",
                    RefType::Relative => "",
                };

                let has_prefix = if sheets.is_empty() {
                    false
                } else {
                    *sheet_id != sheets[0].id
                };

                if has_prefix {
                    let sheet_name = get_sheet_name(*sheet_id, sheets);
                    result.push_str(&format!(
                        "{}!{}{}{}{}",
                        sheet_name,
                        c_prefix,
                        col_letter,
                        r_prefix,
                        row + 1
                    ));
                } else {
                    result.push_str(&format!(
                        "{}{}{}{}",
                        c_prefix,
                        col_letter,
                        r_prefix,
                        row + 1
                    ));
                }
            }
            FormulaPart::RangeReference {
                sheet_id,
                start_row,
                start_col,
                end_row,
                end_col,
                start_row_ref_type,
                start_col_ref_type,
                end_row_ref_type,
                end_col_ref_type,
            } => {
                let has_prefix = if sheets.is_empty() {
                    false
                } else {
                    *sheet_id != sheets[0].id
                };

                if *end_row == usize::MAX {
                    let start_col_letter = col_idx_to_letters(*start_col);
                    let end_col_letter = col_idx_to_letters(*end_col);
                    let sc_prefix = match start_col_ref_type {
                        RefType::Absolute => "$",
                        RefType::Relative => "",
                    };
                    let ec_prefix = match end_col_ref_type {
                        RefType::Absolute => "$",
                        RefType::Relative => "",
                    };
                    if has_prefix {
                        let sheet_name = get_sheet_name(*sheet_id, sheets);
                        result.push_str(&format!(
                            "{}!{}{}:{}{}",
                            sheet_name, sc_prefix, start_col_letter, ec_prefix, end_col_letter,
                        ));
                    } else {
                        result.push_str(&format!(
                            "{}{}:{}{}",
                            sc_prefix, start_col_letter, ec_prefix, end_col_letter,
                        ));
                    }
                } else if *end_col == usize::MAX {
                    let sr_prefix = match start_row_ref_type {
                        RefType::Absolute => "$",
                        RefType::Relative => "",
                    };
                    let er_prefix = match end_row_ref_type {
                        RefType::Absolute => "$",
                        RefType::Relative => "",
                    };
                    if has_prefix {
                        let sheet_name = get_sheet_name(*sheet_id, sheets);
                        result.push_str(&format!(
                            "{}!{}{}:{}{}",
                            sheet_name,
                            sr_prefix,
                            start_row + 1,
                            er_prefix,
                            end_row + 1,
                        ));
                    } else {
                        result.push_str(&format!(
                            "{}{}:{}{}",
                            sr_prefix,
                            start_row + 1,
                            er_prefix,
                            end_row + 1,
                        ));
                    }
                } else {
                    let start_col_letter = col_idx_to_letters(*start_col);
                    let end_col_letter = col_idx_to_letters(*end_col);
                    let sc_prefix = match start_col_ref_type {
                        RefType::Absolute => "$",
                        RefType::Relative => "",
                    };
                    let ec_prefix = match end_col_ref_type {
                        RefType::Absolute => "$",
                        RefType::Relative => "",
                    };
                    let sr_prefix = match start_row_ref_type {
                        RefType::Absolute => "$",
                        RefType::Relative => "",
                    };
                    let er_prefix = match end_row_ref_type {
                        RefType::Absolute => "$",
                        RefType::Relative => "",
                    };

                    if has_prefix {
                        let sheet_name = get_sheet_name(*sheet_id, sheets);
                        result.push_str(&format!(
                            "{}!{}{}{}{}:{}{}{}{}",
                            sheet_name,
                            sc_prefix,
                            start_col_letter,
                            sr_prefix,
                            start_row + 1,
                            ec_prefix,
                            end_col_letter,
                            er_prefix,
                            end_row + 1,
                        ));
                    } else {
                        result.push_str(&format!(
                            "{}{}{}{}:{}{}{}{}",
                            sc_prefix,
                            start_col_letter,
                            sr_prefix,
                            start_row + 1,
                            ec_prefix,
                            end_col_letter,
                            er_prefix,
                            end_row + 1,
                        ));
                    }
                }
            }
            FormulaPart::ColumnReference { sheet_id, col_id } => {
                let sheet_name = get_sheet_name(*sheet_id, sheets);
                let col_name = if let Some(sheet) = sheets.iter().find(|s| s.id == *sheet_id) {
                    if let Some(col) = sheet.columns.iter().find(|c| c.id == *col_id) {
                        col.name.clone()
                    } else {
                        "col_deleted".to_string()
                    }
                } else {
                    "col_deleted".to_string()
                };
                result.push_str(&format!("{}[{}]", sheet_name, col_name));
            }
            FormulaPart::StructuredReference {
                sheet_id,
                col_id,
                is_this_row,
                section,
            } => {
                let sheet_name = get_sheet_name(*sheet_id, sheets);
                let col_name = if let Some(col_id_val) = col_id {
                    if let Some(sheet) = sheets.iter().find(|s| s.id == *sheet_id) {
                        if let Some(col) = sheet.columns.iter().find(|c| c.id == *col_id_val) {
                            col.name.clone()
                        } else {
                            "col_deleted".to_string()
                        }
                    } else {
                        "col_deleted".to_string()
                    }
                } else {
                    String::new()
                };

                let has_prefix = if sheets.is_empty() {
                    false
                } else {
                    *sheet_id != sheets[0].id
                };

                let prefix = if has_prefix {
                    sheet_name
                } else {
                    String::new()
                };

                result.push_str(&render_structured_ref_text(
                    &prefix,
                    &col_name,
                    *is_this_row,
                    *section,
                ));
            }
        }
    }
    result
}

fn render_structured_ref_text(
    prefix: &str,
    col_name: &str,
    is_this_row: bool,
    section: SheetSection,
) -> String {
    if is_this_row {
        if col_name.is_empty() {
            format!("{}[@]", prefix)
        } else {
            format!("{}[@{}]", prefix, col_name)
        }
    } else {
        match section {
            SheetSection::Headers => {
                if col_name.is_empty() {
                    format!("{}[#Headers]", prefix)
                } else {
                    format!("{}[[#Headers], [{}]]", prefix, col_name)
                }
            }
            SheetSection::Totals => {
                if col_name.is_empty() {
                    format!("{}[#Totals]", prefix)
                } else {
                    format!("{}[[#Totals], [{}]]", prefix, col_name)
                }
            }
            SheetSection::Data => {
                if col_name.is_empty() {
                    format!("{}[#Data]", prefix)
                } else {
                    format!("{}[{}]", prefix, col_name)
                }
            }
            SheetSection::All => {
                if col_name.is_empty() {
                    format!("{}[#All]", prefix)
                } else {
                    format!("{}[[#All], [{}]]", prefix, col_name)
                }
            }
        }
    }
}

/// Structural rewrite, provides plumbing for table renames
pub fn rewrite_structured_table_reference(
    formula_src: &str,
    table_name: &str,
    new_table_name: Option<&str>,
    col_rename: Option<(&str, &str)>,
) -> Option<String> {
    if !formula_src.starts_with('=') {
        return None;
    }
    let chars: Vec<char> = formula_src.chars().collect();
    let mut result = String::new();
    let mut last_idx = 0;
    let mut changed = false;

    let mut in_quote = None;
    let mut i = 0;
    while i < chars.len() {
        let c = chars[i];
        if let Some(q) = in_quote {
            if c == '\\' {
                i = (i + 2).min(chars.len());
                continue;
            } else if c == q {
                in_quote = None;
            }
            i += 1;
            continue;
        }

        if let Some((found_ref, next_i)) = try_parse_ref(&chars, i) {
            if let FoundRef::Structured {
                sheet,
                column,
                is_this_row,
                section,
            } = &found_ref
            {
                let matches_table = sheet
                    .as_deref()
                    .is_some_and(|s| s.eq_ignore_ascii_case(table_name));
                if matches_table {
                    let rendered_table_name = new_table_name.unwrap_or(table_name);
                    let rendered_col_name = match (column, col_rename) {
                        (Some(col), Some((old_col, new_col)))
                            if col.eq_ignore_ascii_case(old_col) =>
                        {
                            new_col.to_string()
                        }
                        (Some(col), _) => col.clone(),
                        (None, _) => String::new(),
                    };
                    let rendered = render_structured_ref_text(
                        rendered_table_name,
                        &rendered_col_name,
                        *is_this_row,
                        *section,
                    );

                    let original: String = chars[i..next_i].iter().collect();
                    if rendered != original {
                        result.push_str(&chars[last_idx..i].iter().collect::<String>());
                        result.push_str(&rendered);
                        changed = true;
                        last_idx = next_i;
                    }
                }
            }
            i = next_i;
        } else {
            if c == '"' || c == '\'' {
                in_quote = Some(c);
            }
            i += 1;
        }
    }

    if !changed {
        return None;
    }
    result.push_str(&chars[last_idx..].iter().collect::<String>());
    Some(result)
}

fn take_number_exponent(chars: &[char], i: &mut usize, num_str: &mut String) {
    if *i >= chars.len() || !matches!(chars[*i], 'e' | 'E') {
        return;
    }
    let mut peek = *i + 1;
    if peek < chars.len() && matches!(chars[peek], '+' | '-') {
        peek += 1;
    }
    if peek >= chars.len() || !chars[peek].is_ascii_digit() {
        return;
    }
    num_str.push('E');
    if matches!(chars[*i + 1], '+' | '-') {
        num_str.push(chars[*i + 1]);
    }
    *i = peek;
    while *i < chars.len() && chars[*i].is_ascii_digit() {
        num_str.push(chars[*i]);
        *i += 1;
    }
}

fn match_error_code(chars: &[char], start: usize) -> Option<&'static str> {
    crate::core::engine::result_data::EXCEL_ERROR_CODES
        .iter()
        .filter(|code| {
            let wanted: Vec<char> = code.chars().collect();
            chars.len() - start >= wanted.len()
                && chars[start..start + wanted.len()]
                    .iter()
                    .zip(&wanted)
                    .all(|(a, b)| a.eq_ignore_ascii_case(b))
        })
        .max_by_key(|code| code.len())
        .copied()
}

fn token_can_end_reference(tok: Option<&EvalToken>) -> bool {
    matches!(
        tok,
        Some(
            EvalToken::Identifier(_)
                | EvalToken::Number(_)
                | EvalToken::String(_)
                | EvalToken::CloseParen
                | EvalToken::StructuredRef { .. }
        )
    )
}

fn char_can_start_reference(c: char) -> bool {
    c.is_ascii_alphanumeric() || c == '$' || c == '_' || c == '\''
}

pub fn lex_eval(input: &str) -> Result<Vec<EvalToken>, String> {
    let chars: Vec<char> = input.chars().collect();
    let mut tokens = Vec::new();
    let mut i = 0;

    while i < chars.len() {
        let c = chars[i];
        if c.is_whitespace() {
            let can_end = token_can_end_reference(tokens.last());
            while i < chars.len() && chars[i].is_whitespace() {
                i += 1;
            }
            if can_end
                && i < chars.len()
                && char_can_start_reference(chars[i])
                && !matches!(tokens.last(), Some(EvalToken::String(_)))
            {
                tokens.push(EvalToken::Op(Op::Intersect));
            }
            continue;
        }

        if c == '"' || c == '\'' {
            let quote = c;
            i += 1;
            let mut s = String::new();
            while i < chars.len() {
                if chars[i] == '\\' && i + 1 < chars.len() {
                    s.push(chars[i + 1]);
                    i += 2;
                } else if chars[i] == quote {
                    i += 1;
                    break;
                } else {
                    s.push(chars[i]);
                    i += 1;
                }
            }
            tokens.push(EvalToken::String(s));
            continue;
        }

        if c == '['
            && let Some((column, is_this_row, section, next_i)) =
                parse_structured_specifier(&chars, i)
        {
            let mut sheet = None;
            if let Some(EvalToken::Identifier(_) | EvalToken::String(_)) = tokens.last() {
                let last = tokens.pop().unwrap();
                sheet = match last {
                    EvalToken::Identifier(s) => Some(s),
                    EvalToken::String(s) => Some(s),
                    _ => unreachable!(),
                };
            }
            tokens.push(EvalToken::StructuredRef {
                sheet,
                column,
                is_this_row,
                section,
            });
            i = next_i;
            continue;
        }

        if c == '#'
            && let Some(code) = match_error_code(&chars, i)
        {
            i += code.chars().count();
            tokens.push(EvalToken::Error(code));
            continue;
        }

        match c {
            '(' => {
                tokens.push(EvalToken::OpenParen);
                i += 1;
                continue;
            }
            ')' => {
                tokens.push(EvalToken::CloseParen);
                i += 1;
                continue;
            }
            '[' => {
                tokens.push(EvalToken::OpenBracket);
                i += 1;
                continue;
            }
            ']' => {
                tokens.push(EvalToken::CloseBracket);
                i += 1;
                continue;
            }
            ',' => {
                tokens.push(EvalToken::Comma);
                i += 1;
                continue;
            }
            ':' => {
                tokens.push(EvalToken::Colon);
                i += 1;
                continue;
            }
            _ => {}
        }

        if c == '<' {
            if i + 1 < chars.len() && chars[i + 1] == '>' {
                tokens.push(EvalToken::Op(Op::Ne));
                i += 2;
            } else if i + 1 < chars.len() && chars[i + 1] == '=' {
                tokens.push(EvalToken::Op(Op::Le));
                i += 2;
            } else {
                tokens.push(EvalToken::Op(Op::Lt));
                i += 1;
            }
            continue;
        }
        if c == '>' {
            if i + 1 < chars.len() && chars[i + 1] == '=' {
                tokens.push(EvalToken::Op(Op::Ge));
                i += 2;
            } else {
                tokens.push(EvalToken::Op(Op::Gt));
                i += 1;
            }
            continue;
        }
        if c == '=' {
            if i + 1 < chars.len() && chars[i + 1] == '=' {
                tokens.push(EvalToken::Op(Op::Eq));
                i += 2;
            } else {
                tokens.push(EvalToken::Op(Op::Eq));
                i += 1;
            }
            continue;
        }
        if c == '!' {
            if i + 1 < chars.len() && chars[i + 1] == '=' {
                tokens.push(EvalToken::Op(Op::Ne));
                i += 2;
            } else {
                tokens.push(EvalToken::Exclamation);
                i += 1;
            }
            continue;
        }

        match c {
            '+' => {
                tokens.push(EvalToken::Op(Op::Add));
                i += 1;
                continue;
            }
            '-' => {
                tokens.push(EvalToken::Op(Op::Sub));
                i += 1;
                continue;
            }
            '*' => {
                if i + 1 < chars.len() && chars[i + 1] == '*' {
                    tokens.push(EvalToken::Op(Op::Exp));
                    i += 2;
                } else {
                    tokens.push(EvalToken::Op(Op::Mul));
                    i += 1;
                }
                continue;
            }
            '/' => {
                tokens.push(EvalToken::Op(Op::Div));
                i += 1;
                continue;
            }
            '^' => {
                tokens.push(EvalToken::Op(Op::Exp));
                i += 1;
                continue;
            }
            '%' => {
                tokens.push(EvalToken::Op(Op::Percent));
                i += 1;
                continue;
            }
            '@' => {
                tokens.push(EvalToken::Op(Op::ImplicitIntersection));
                i += 1;
                continue;
            }
            '#' => {
                tokens.push(EvalToken::Op(Op::Spill));
                i += 1;
                continue;
            }
            '&' => {
                tokens.push(EvalToken::Op(Op::Concat));
                i += 1;
                continue;
            }
            _ => {}
        }

        if c == '.' {
            if i + 1 < chars.len() && chars[i + 1].is_ascii_digit() {
                let mut num_str = String::new();
                num_str.push('.');
                i += 1;
                while i < chars.len() && chars[i].is_ascii_digit() {
                    num_str.push(chars[i]);
                    i += 1;
                }
                take_number_exponent(&chars, &mut i, &mut num_str);
                if let Ok(val) = num_str.parse::<f64>() {
                    tokens.push(EvalToken::Number(val));
                } else {
                    return Err(format!("Invalid number: {}", num_str));
                }
            } else {
                tokens.push(EvalToken::Dot);
                i += 1;
            }
            continue;
        }

        if c.is_ascii_digit() {
            let mut num_str = String::new();
            while i < chars.len() && (chars[i].is_ascii_digit() || chars[i] == '.') {
                num_str.push(chars[i]);
                i += 1;
            }
            take_number_exponent(&chars, &mut i, &mut num_str);
            if let Ok(val) = num_str.parse::<f64>() {
                tokens.push(EvalToken::Number(val));
            } else {
                return Err(format!("Invalid number: {}", num_str));
            }
            continue;
        }

        if c.is_ascii_alphabetic() || c == '_' || c == '$' {
            let mut id_str = String::new();
            while i < chars.len()
                && (chars[i].is_ascii_alphanumeric()
                    || chars[i] == '_'
                    || chars[i] == '$'
                    || chars[i] == '.')
            {
                id_str.push(chars[i]);
                i += 1;
            }

            let upper = id_str.to_uppercase();
            let mut lookahead = i;
            while lookahead < chars.len() && chars[lookahead].is_whitespace() {
                lookahead += 1;
            }
            let followed_by_paren = lookahead < chars.len() && chars[lookahead] == '(';
            if upper == "TRUE" && !followed_by_paren {
                tokens.push(EvalToken::Boolean(true));
            } else if upper == "FALSE" && !followed_by_paren {
                tokens.push(EvalToken::Boolean(false));
            } else {
                tokens.push(EvalToken::Identifier(id_str));
            }
            continue;
        }

        return Err(format!("Unexpected character: {}", c));
    }

    Ok(tokens)
}

fn ref_token_text(tok: &EvalToken) -> Option<String> {
    match tok {
        EvalToken::Identifier(s) => Some(s.clone()),
        EvalToken::Number(n) if n.is_finite() && n.fract() == 0.0 && *n >= 1.0 => {
            Some(format!("{n:.0}"))
        }
        _ => None,
    }
}

fn range_ref_from_texts(sheet: Option<String>, start: &str, end: &str) -> Result<Expr, String> {
    if let (
        Some((s_row, s_col, s_row_abs, s_col_abs)),
        Some((e_row, e_col, e_row_abs, e_col_abs)),
    ) = (parse_cell_ref_parts(start), parse_cell_ref_parts(end))
    {
        Ok(Expr::RangeRef {
            sheet,
            start_row: s_row,
            start_col: s_col,
            end_row: e_row,
            end_col: e_col,
            start_row_abs: s_row_abs,
            start_col_abs: s_col_abs,
            end_row_abs: e_row_abs,
            end_col_abs: e_col_abs,
        })
    } else if let (Some((s_col, s_col_abs)), Some((e_col, e_col_abs))) =
        (parse_column_ref(start), parse_column_ref(end))
    {
        Ok(Expr::RangeRef {
            sheet,
            start_row: 0,
            start_col: s_col,
            end_row: usize::MAX,
            end_col: e_col,
            start_row_abs: true,
            start_col_abs: s_col_abs,
            end_row_abs: true,
            end_col_abs: e_col_abs,
        })
    } else if let (Some((s_row, s_row_abs)), Some((e_row, e_row_abs))) =
        (parse_row_ref(start), parse_row_ref(end))
    {
        Ok(Expr::RangeRef {
            sheet,
            start_row: s_row,
            start_col: 0,
            end_row: e_row,
            end_col: usize::MAX,
            start_row_abs: s_row_abs,
            start_col_abs: true,
            end_row_abs: e_row_abs,
            end_col_abs: true,
        })
    } else {
        Err(format!("Invalid range reference: {start}:{end}"))
    }
}

struct Parser<'a> {
    tokens: &'a [EvalToken],
    pos: usize,
}

impl<'a> Parser<'a> {
    fn new(tokens: &'a [EvalToken]) -> Self {
        Self { tokens, pos: 0 }
    }

    fn peek(&self) -> Option<&EvalToken> {
        self.tokens.get(self.pos)
    }

    fn next(&mut self) -> Option<&EvalToken> {
        if self.pos < self.tokens.len() {
            let tok = &self.tokens[self.pos];
            self.pos += 1;
            Some(tok)
        } else {
            None
        }
    }

    fn consume(&mut self, expected: EvalToken) -> Result<(), String> {
        match self.next() {
            Some(tok) if *tok == expected => Ok(()),
            Some(tok) => Err(format!("Expected {:?}, got {:?}", expected, tok)),
            None => Err(format!("Expected {:?}, got EOF", expected)),
        }
    }

    fn parse(&mut self) -> Result<Expr, String> {
        self.parse_expr(true)
    }

    fn parse_expr(&mut self, allow_union: bool) -> Result<Expr, String> {
        let mut lhs = self.parse_binary(0, allow_union)?;

        while let Some(tok) = self.peek() {
            match tok {
                EvalToken::OpenBracket => {
                    self.next();
                    let mut inner_tokens = Vec::new();
                    let mut depth = 1;
                    while let Some(t) = self.next() {
                        if *t == EvalToken::OpenBracket {
                            depth += 1;
                            inner_tokens.push(t.clone());
                        } else if *t == EvalToken::CloseBracket {
                            depth -= 1;
                            if depth == 0 {
                                break;
                            }
                            inner_tokens.push(t.clone());
                        } else {
                            inner_tokens.push(t.clone());
                        }
                    }

                    if let Some(colon_pos) =
                        inner_tokens.iter().position(|t| *t == EvalToken::Colon)
                    {
                        let start_toks = &inner_tokens[0..colon_pos];
                        let end_toks = &inner_tokens[colon_pos + 1..];

                        let start_val = if start_toks.is_empty() {
                            None
                        } else {
                            let mut p = Parser::new(start_toks);
                            Some(p.parse_expr(false)?)
                        };

                        let end_val = if end_toks.is_empty() {
                            None
                        } else {
                            let mut p = Parser::new(end_toks);
                            Some(p.parse_expr(false)?)
                        };

                        lhs = Expr::Slice {
                            expr: Box::new(lhs),
                            start: start_val.map(Box::new),
                            end: end_val.map(Box::new),
                        };
                    } else {
                        let mut p = Parser::new(&inner_tokens);
                        let index_expr = p.parse_expr(false)?;

                        if let Expr::Identifier(ref sheet_name) = lhs {
                            let col_name_opt = match &index_expr {
                                Expr::String(s) => Some(s.clone()),
                                Expr::Identifier(s) => Some(s.clone()),
                                _ => None,
                            };
                            if let Some(col_name) = col_name_opt {
                                lhs = Expr::StructuredRef {
                                    sheet: Some(sheet_name.clone()),
                                    column: Some(col_name),
                                    is_this_row: false,
                                    section: SheetSection::Data,
                                };
                                continue;
                            }
                        }

                        lhs = Expr::FunctionCall {
                            name: "INDEX".to_string(),
                            args: vec![lhs, index_expr],
                        };
                    }
                }
                EvalToken::Dot => {
                    return Err("Dot member access is not supported".to_string());
                }
                _ => break,
            }
        }

        Ok(lhs)
    }

    fn parse_binary(&mut self, min_prec: u8, allow_union: bool) -> Result<Expr, String> {
        let mut lhs = self.parse_prefix(allow_union)?;

        while matches!(self.peek(), Some(EvalToken::Op(Op::Percent | Op::Spill))) {
            let EvalToken::Op(op) = self.next().unwrap() else {
                unreachable!()
            };
            lhs = Expr::UnaryOp {
                op: *op,
                expr: Box::new(lhs),
            };
        }

        while let Some(tok) = self.peek() {
            let op = match tok {
                EvalToken::Op(Op::Percent | Op::Spill | Op::ImplicitIntersection) => break,
                EvalToken::Op(o) => *o,
                EvalToken::Comma if allow_union => Op::Union,
                _ => break,
            };

            let prec = op_precedence(op);
            if prec < min_prec {
                break;
            }

            self.next();

            let rhs = self.parse_binary(prec + 1, allow_union)?;
            lhs = Expr::BinaryOp {
                op,
                left: Box::new(lhs),
                right: Box::new(rhs),
            };
        }

        Ok(lhs)
    }

    fn parse_prefix(&mut self, allow_union: bool) -> Result<Expr, String> {
        let tok = self
            .next()
            .ok_or_else(|| "Unexpected EOF".to_string())?
            .clone();
        match tok {
            EvalToken::Number(val) => {
                if self.peek() == Some(&EvalToken::Colon)
                    && val.is_finite()
                    && val.fract() == 0.0
                    && val >= 1.0
                {
                    self.next();
                    let end_tok = self
                        .next()
                        .ok_or_else(|| "Expected row reference after `:`".to_string())?;
                    let start_str = format!("{val:.0}");
                    let end_str = ref_token_text(end_tok).ok_or_else(|| {
                        format!("Expected row reference after `:`, got {:?}", end_tok)
                    })?;
                    return range_ref_from_texts(None, &start_str, &end_str);
                }
                Ok(Expr::Number(val))
            }
            EvalToken::String(val) => {
                if self.peek() == Some(&EvalToken::Exclamation) {
                    self.next();
                    let target_tok = self
                        .next()
                        .ok_or_else(|| "Expected cell or column reference after `!`".to_string())?
                        .clone();
                    let target_str = ref_token_text(&target_tok).ok_or_else(|| {
                        format!(
                            "Expected cell, row, or column reference after `!`, got {:?}",
                            target_tok
                        )
                    })?;

                    if self.peek() == Some(&EvalToken::Colon) {
                        self.next();
                        let end_tok = self
                            .next()
                            .ok_or_else(|| {
                                "Expected cell, row, or column reference after `:`".to_string()
                            })?
                            .clone();
                        let end_str = ref_token_text(&end_tok).ok_or_else(|| {
                            format!(
                                "Expected cell, row, or column reference after `:`, got {:?}",
                                end_tok
                            )
                        })?;

                        return range_ref_from_texts(Some(val), &target_str, &end_str);
                    } else {
                        let (row, col, row_abs, col_abs) = parse_cell_ref_parts(&target_str)
                            .ok_or_else(|| format!("Invalid cell: {}", target_str))?;
                        return Ok(Expr::CellRef {
                            sheet: Some(val),
                            row,
                            col,
                            row_abs,
                            col_abs,
                        });
                    }
                }
                Ok(Expr::String(val.clone()))
            }
            EvalToken::Boolean(val) => Ok(Expr::Boolean(val)),
            EvalToken::Error(code) => Ok(Expr::Error(code)),
            EvalToken::OpenParen => {
                let expr = self.parse_expr(true)?;
                self.consume(EvalToken::CloseParen)?;
                Ok(expr)
            }
            EvalToken::OpenBracket => {
                let mut list = Vec::new();
                if self.peek() != Some(&EvalToken::CloseBracket) {
                    loop {
                        list.push(self.parse_expr(false)?);
                        if self.peek() == Some(&EvalToken::Comma) {
                            self.next();
                        } else {
                            break;
                        }
                    }
                }
                self.consume(EvalToken::CloseBracket)?;
                Ok(Expr::List(list))
            }
            EvalToken::Op(Op::Sub) => {
                let expr = self.parse_binary(100, allow_union)?;
                Ok(Expr::UnaryOp {
                    op: Op::Sub,
                    expr: Box::new(expr),
                })
            }
            EvalToken::Op(Op::Add) => {
                let expr = self.parse_binary(100, allow_union)?;
                Ok(expr)
            }
            EvalToken::Op(Op::ImplicitIntersection) => {
                let expr = self.parse_binary(100, allow_union)?;
                Ok(Expr::UnaryOp {
                    op: Op::ImplicitIntersection,
                    expr: Box::new(expr),
                })
            }
            EvalToken::Identifier(id_name) => {
                if self.peek() == Some(&EvalToken::OpenParen) {
                    self.next();
                    let mut args = Vec::new();
                    if self.peek() != Some(&EvalToken::CloseParen) {
                        loop {
                            args.push(self.parse_expr(false)?);
                            if self.peek() == Some(&EvalToken::Comma) {
                                self.next();
                            } else {
                                break;
                            }
                        }
                    }
                    self.consume(EvalToken::CloseParen)?;
                    return Ok(Expr::FunctionCall {
                        name: id_name.clone(),
                        args,
                    });
                }

                if self.peek() == Some(&EvalToken::Exclamation) {
                    self.next();
                    let target_tok = self
                        .next()
                        .ok_or_else(|| "Expected cell or column reference after `!`".to_string())?;
                    let target_str = ref_token_text(target_tok).ok_or_else(|| {
                        format!(
                            "Expected cell, row, or column reference after `!`, got {:?}",
                            target_tok
                        )
                    })?;

                    if self.peek() == Some(&EvalToken::Colon) {
                        self.next();
                        let end_tok = self.next().ok_or_else(|| {
                            "Expected cell, row, or column reference after `:`".to_string()
                        })?;
                        let end_str = ref_token_text(end_tok).ok_or_else(|| {
                            format!(
                                "Expected cell, row, or column reference after `:`, got {:?}",
                                end_tok
                            )
                        })?;

                        return range_ref_from_texts(Some(id_name.clone()), &target_str, &end_str);
                    } else {
                        let (row, col, row_abs, col_abs) = parse_cell_ref_parts(&target_str)
                            .ok_or_else(|| format!("Invalid cell: {}", target_str))?;
                        return Ok(Expr::CellRef {
                            sheet: Some(id_name.clone()),
                            row,
                            col,
                            row_abs,
                            col_abs,
                        });
                    }
                }

                if let Some((row, col, row_abs, col_abs)) = parse_cell_ref_parts(&id_name) {
                    if self.peek() == Some(&EvalToken::Colon) {
                        self.next();
                        let end_tok = self
                            .next()
                            .ok_or_else(|| "Expected cell reference after `:`".to_string())?;
                        let end_str = match end_tok {
                            EvalToken::Identifier(s) => s.clone(),
                            _ => {
                                return Err(format!(
                                    "Expected cell reference after `:`, got {:?}",
                                    end_tok
                                ));
                            }
                        };
                        let (e_row, e_col, e_row_abs, e_col_abs) =
                            parse_cell_ref_parts(&end_str)
                                .ok_or_else(|| format!("Invalid end cell: {}", end_str))?;
                        return Ok(Expr::RangeRef {
                            sheet: None,
                            start_row: row,
                            start_col: col,
                            end_row: e_row,
                            end_col: e_col,
                            start_row_abs: row_abs,
                            start_col_abs: col_abs,
                            end_row_abs: e_row_abs,
                            end_col_abs: e_col_abs,
                        });
                    }

                    return Ok(Expr::CellRef {
                        sheet: None,
                        row,
                        col,
                        row_abs,
                        col_abs,
                    });
                }

                if let Some((col, col_abs)) = parse_column_ref(&id_name)
                    && self.peek() == Some(&EvalToken::Colon)
                {
                    self.next();
                    let end_tok = self
                        .next()
                        .ok_or_else(|| "Expected column reference after `:`".to_string())?;
                    let end_str = match end_tok {
                        EvalToken::Identifier(s) => s.clone(),
                        _ => {
                            return Err(format!(
                                "Expected column reference after `:`, got {:?}",
                                end_tok
                            ));
                        }
                    };
                    let (e_col, e_col_abs) = parse_column_ref(&end_str)
                        .ok_or_else(|| format!("Invalid end column: {}", end_str))?;
                    return Ok(Expr::RangeRef {
                        sheet: None,
                        start_row: 0,
                        start_col: col,
                        end_row: usize::MAX,
                        end_col: e_col,
                        start_row_abs: true,
                        start_col_abs: col_abs,
                        end_row_abs: true,
                        end_col_abs: e_col_abs,
                    });
                }

                if let Some((row, row_abs)) = parse_row_ref(&id_name)
                    && self.peek() == Some(&EvalToken::Colon)
                {
                    self.next();
                    let end_tok = self
                        .next()
                        .ok_or_else(|| "Expected row reference after `:`".to_string())?;
                    let end_str = ref_token_text(end_tok).ok_or_else(|| {
                        format!("Expected row reference after `:`, got {:?}", end_tok)
                    })?;
                    let (e_row, e_row_abs) = parse_row_ref(&end_str)
                        .ok_or_else(|| format!("Invalid end row: {}", end_str))?;
                    return Ok(Expr::RangeRef {
                        sheet: None,
                        start_row: row,
                        start_col: 0,
                        end_row: e_row,
                        end_col: usize::MAX,
                        start_row_abs: row_abs,
                        start_col_abs: true,
                        end_row_abs: e_row_abs,
                        end_col_abs: true,
                    });
                }

                Ok(Expr::Identifier(id_name.clone()))
            }
            EvalToken::StructuredRef {
                sheet,
                column,
                is_this_row,
                section,
            } => Ok(Expr::StructuredRef {
                sheet: sheet.clone(),
                column: column.clone(),
                is_this_row,
                section,
            }),
            _ => Err(format!("Unexpected token: {:?}", tok)),
        }
    }
}

fn op_precedence(op: Op) -> u8 {
    match op {
        Op::Union => 40,
        Op::Intersect => 50,
        Op::Add | Op::Sub => 10,
        Op::Mul | Op::Div => 20,
        Op::Exp => 30,
        Op::Concat => 8,
        Op::Eq | Op::Ne | Op::Lt | Op::Gt | Op::Le | Op::Ge => 5,
        Op::Percent | Op::ImplicitIntersection | Op::Spill => 60,
    }
}

pub fn parse_excel_formula(input: &str) -> Result<Expr, String> {
    let tokens = lex_eval(input)?;
    let mut parser = Parser::new(&tokens);
    let expr = parser.parse()?;
    if parser.peek().is_some() {
        return Err("Trailing tokens after expression".to_string());
    }
    Ok(expr)
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn test_parse_cell_ref() {
        let (sheet, row, col) = parse_cell_ref("A1").unwrap();
        assert_eq!(sheet, None);
        assert_eq!(row, 0);
        assert_eq!(col, 0);

        let (sheet, row, col) = parse_cell_ref("Sheet1!C5").unwrap();
        assert_eq!(sheet, Some("Sheet1".to_string()));
        assert_eq!(row, 4);
        assert_eq!(col, 2);
    }

    #[test]
    fn test_parse_range_ref() {
        let (sheet, s_row, s_col, e_row, e_col) = parse_range_ref("A1:C10").unwrap();
        assert_eq!(sheet, None);
        assert_eq!((s_row, s_col), (0, 0));
        assert_eq!((e_row, e_col), (9, 2));

        let (sheet, s_row, s_col, e_row, e_col) = parse_range_ref("'Data Sheet'!B2:D4").unwrap();
        assert_eq!(sheet, Some("Data Sheet".to_string()));
        assert_eq!((s_row, s_col), (1, 1));
        assert_eq!((e_row, e_col), (3, 3));
    }

    #[test]
    fn test_col_conversions() {
        assert_eq!(col_idx_to_letters(0), "A");
        assert_eq!(col_idx_to_letters(25), "Z");
        assert_eq!(col_idx_to_letters(26), "AA");
        assert_eq!(col_idx_to_letters(27), "AB");

        assert_eq!(col_letters_to_idx("A"), 0);
        assert_eq!(col_letters_to_idx("z"), 25);
        assert_eq!(col_letters_to_idx("AA"), 26);
        assert_eq!(col_letters_to_idx("AB"), 27);
    }

    #[test]
    fn test_lex_scientific_notation_literals() {
        for (src, want) in [
            ("1E5", 1e5),
            ("1E+5", 1e5),
            ("1e5", 1e5),
            ("2.5E-3", 2.5e-3),
            (".5E3", 0.5e3),
            ("1E+300", 1e300),
        ] {
            match lex_eval(src).unwrap().as_slice() {
                [EvalToken::Number(got)] => {
                    assert_eq!(*got, want, "{src} lexed to the wrong value")
                }
                other => panic!("{src} did not lex as a single number: {other:?}"),
            }
        }
    }

    #[test]
    fn test_lex_does_not_eat_e_that_starts_a_reference() {
        let tokens = lex_eval("A1:E5").unwrap();
        assert_eq!(
            tokens.len(),
            3,
            "A1:E5 must stay ident/colon/ident, got {tokens:?}"
        );
        assert!(matches!(
            lex_eval("1E").unwrap().as_slice(),
            [EvalToken::Number(n), _] if *n == 1.0
        ));
    }

    #[test]
    fn test_compile_and_serialize() {
        let sheet1 = Sheet::new(crate::core::SheetInit {
            id: Some(123),
            name: Some("Sheet1".to_string()),
            rows: 5,
            cols: 5,
        });
        let sheet2 = Sheet::new(crate::core::SheetInit {
            id: Some(456),
            name: Some("Sheet2".to_string()),
            rows: 5,
            cols: 5,
        });

        let sheets = vec![sheet1, sheet2];

        let formula = compile_formula("=Sheet2!B1 + 10", &sheets);
        assert_eq!(formula.parts.len(), 3);
        match &formula.parts[1] {
            FormulaPart::SheetReference {
                sheet_id, row, col, ..
            } => {
                assert_eq!(*sheet_id, 456);
                assert_eq!(*row, 0);
                assert_eq!(*col, 1);
            }
            _ => panic!("Expected SheetReference"),
        }

        let mut renamed_sheet2 = sheets[1].clone();
        renamed_sheet2.name = "Sheet3".to_string();

        let serialized = serialize_formula(&formula, &[sheets[0].clone(), renamed_sheet2]);
        assert_eq!(serialized, "=Sheet3!B1 + 10");
    }

    #[test]
    fn test_table_names_with_spaces() {
        let sheet1 = Sheet::new(crate::core::SheetInit {
            id: Some(123),
            name: Some("Sheet1".to_string()),
            rows: 5,
            cols: 5,
        });
        let sheet2 = Sheet::new(crate::core::SheetInit {
            id: Some(456),
            name: Some("My Sheet".to_string()),
            rows: 5,
            cols: 5,
        });

        let sheets = vec![sheet1, sheet2];

        let formula_quote = compile_formula("='My Sheet'!B1 + 10", &sheets);
        match &formula_quote.parts[1] {
            FormulaPart::SheetReference {
                sheet_id, row, col, ..
            } => {
                assert_eq!(*sheet_id, 456);
                assert_eq!(*row, 0);
                assert_eq!(*col, 1);
            }
            _ => panic!("Expected SheetReference"),
        }

        let serialized_quote = serialize_formula(&formula_quote, &sheets);
        assert_eq!(serialized_quote, "='My Sheet'!B1 + 10");

        let ast_quote = parse_excel_formula("'My Sheet'!B1 + 10").unwrap();
        match ast_quote {
            Expr::BinaryOp { left, .. } => match *left {
                Expr::CellRef {
                    sheet, row, col, ..
                } => {
                    assert_eq!(sheet, Some("My Sheet".to_string()));
                    assert_eq!(row, 0);
                    assert_eq!(col, 1);
                }
                _ => panic!("Expected CellRef"),
            },
            _ => panic!("Expected BinaryOp"),
        }
    }

    #[test]
    fn test_row_ranges_compile_serialize_and_parse() {
        let sheet1 = Sheet::new(crate::core::SheetInit {
            id: Some(123),
            name: Some("Sheet1".to_string()),
            rows: 5,
            cols: 5,
        });
        let sheet2 = Sheet::new(crate::core::SheetInit {
            id: Some(456),
            name: Some("Sheet2".to_string()),
            rows: 5,
            cols: 5,
        });
        let sheets = vec![sheet1, sheet2];

        let formula = compile_formula("=SUM(1:3)", &sheets);
        match &formula.parts[1] {
            FormulaPart::RangeReference {
                sheet_id,
                start_row,
                start_col,
                end_row,
                end_col,
                ..
            } => {
                assert_eq!(*sheet_id, 123);
                assert_eq!((*start_row, *start_col), (0, 0));
                assert_eq!((*end_row, *end_col), (2, usize::MAX));
            }
            _ => panic!("Expected whole-row RangeReference"),
        }
        assert_eq!(serialize_formula(&formula, &sheets), "=SUM(1:3)");

        let cross = compile_formula("=SUM(Sheet2!$2:$4)", &sheets);
        assert_eq!(serialize_formula(&cross, &sheets), "=SUM(Sheet2!$2:$4)");

        let ast = parse_excel_formula("SUM(Sheet2!1:3)").unwrap();
        match ast {
            Expr::FunctionCall { args, .. } => match &args[0] {
                Expr::RangeRef {
                    sheet,
                    start_row,
                    start_col,
                    end_row,
                    end_col,
                    ..
                } => {
                    assert_eq!(sheet.as_deref(), Some("Sheet2"));
                    assert_eq!((*start_row, *start_col), (0, 0));
                    assert_eq!((*end_row, *end_col), (2, usize::MAX));
                }
                _ => panic!("Expected whole-row RangeRef"),
            },
            _ => panic!("Expected FunctionCall"),
        }
    }

    #[test]
    fn test_table_names_with_periods() {
        let sheet1 = Sheet::new(crate::core::SheetInit {
            id: Some(123),
            name: Some("Model_SL_5.5Yr".to_string()),
            rows: 5,
            cols: 5,
        });

        let sheets = vec![sheet1];

        let formula = compile_formula("=Model_SL_5.5Yr!B10 + 10", &sheets);
        assert_eq!(formula.parts.len(), 3);
        match &formula.parts[1] {
            FormulaPart::SheetReference {
                sheet_id, row, col, ..
            } => {
                assert_eq!(*sheet_id, 123);
                assert_eq!(*row, 9);
                assert_eq!(*col, 1);
            }
            _ => panic!("Expected SheetReference"),
        }

        let ast = parse_excel_formula("Model_SL_5.5Yr!B10 + 10").unwrap();
        match ast {
            Expr::BinaryOp { left, .. } => match *left {
                Expr::CellRef {
                    sheet, row, col, ..
                } => {
                    assert_eq!(sheet, Some("Model_SL_5.5Yr".to_string()));
                    assert_eq!(row, 9);
                    assert_eq!(col, 1);
                }
                _ => panic!("Expected CellRef"),
            },
            _ => panic!("Expected BinaryOp"),
        }
    }

    #[test]
    fn test_column_ranges() {
        let sheet1 = Sheet::new(crate::core::SheetInit {
            id: Some(123),
            name: Some("Sheet1".to_string()),
            rows: 5,
            cols: 5,
        });
        let sheets = vec![sheet1];

        let formula = compile_formula("=SUM(A:B)", &sheets);
        assert_eq!(formula.parts.len(), 3);
        match &formula.parts[1] {
            FormulaPart::RangeReference {
                sheet_id,
                start_row,
                start_col,
                end_row,
                end_col,
                ..
            } => {
                assert_eq!(*sheet_id, 123);
                assert_eq!(*start_row, 0);
                assert_eq!(*start_col, 0);
                assert_eq!(*end_row, usize::MAX);
                assert_eq!(*end_col, 1);
            }
            _ => panic!("Expected RangeReference"),
        }

        let serialized = serialize_formula(&formula, &sheets);
        assert_eq!(serialized, "=SUM(A:B)");

        let sheet2 = Sheet::new(crate::core::SheetInit {
            id: Some(456),
            name: Some("Sheet2".to_string()),
            rows: 5,
            cols: 5,
        });
        let sheets_multi = vec![sheets[0].clone(), sheet2];
        let formula_cross = compile_formula("=SUM(Sheet2!$A:$C)", &sheets_multi);
        match &formula_cross.parts[1] {
            FormulaPart::RangeReference {
                sheet_id,
                start_row,
                start_col,
                end_row,
                end_col,
                start_col_ref_type,
                end_col_ref_type,
                ..
            } => {
                assert_eq!(*sheet_id, 456);
                assert_eq!(*start_row, 0);
                assert_eq!(*start_col, 0);
                assert_eq!(*end_row, usize::MAX);
                assert_eq!(*end_col, 2);
                assert_eq!(*start_col_ref_type, RefType::Absolute);
                assert_eq!(*end_col_ref_type, RefType::Absolute);
            }
            _ => panic!("Expected RangeReference for cross sheet"),
        }

        let serialized_cross = serialize_formula(&formula_cross, &sheets_multi);
        assert_eq!(serialized_cross, "=SUM(Sheet2!$A:$C)");

        let ast = parse_excel_formula("SUM(A:A)").unwrap();
        match ast {
            Expr::FunctionCall { name, args } => {
                assert_eq!(name, "SUM");
                assert_eq!(args.len(), 1);
                match &args[0] {
                    Expr::RangeRef {
                        sheet,
                        start_row,
                        start_col,
                        end_row,
                        end_col,
                        ..
                    } => {
                        assert_eq!(*sheet, None);
                        assert_eq!(*start_row, 0);
                        assert_eq!(*start_col, 0);
                        assert_eq!(*end_row, usize::MAX);
                        assert_eq!(*end_col, 0);
                    }
                    _ => panic!("Expected RangeRef inside SUM"),
                }
            }
            _ => panic!("Expected FunctionCall"),
        }
    }

    #[test]
    fn test_excel_structured_references() {
        let mut sheet1 = Sheet::new(crate::core::SheetInit {
            id: Some(123),
            name: Some("Sheet1".to_string()),
            rows: 5,
            cols: 2,
        });
        sheet1.columns[0].name = "Sales".to_string();
        sheet1.columns[0].id = 1;
        sheet1.columns[1].name = "Cost".to_string();
        sheet1.columns[1].id = 2;

        let sheets = vec![sheet1];

        let f1 = compile_formula("=Sheet1[Sales]", &sheets);
        assert_eq!(f1.parts.len(), 2);
        match &f1.parts[1] {
            FormulaPart::StructuredReference {
                sheet_id,
                col_id,
                is_this_row,
                section,
            } => {
                assert_eq!(*sheet_id, 123);
                assert_eq!(*col_id, Some(1));
                assert!(!is_this_row);
                assert_eq!(*section, SheetSection::Data);
            }
            _ => panic!("Expected StructuredReference, got {:?}", f1.parts[1]),
        }

        let s1 = serialize_formula(&f1, &sheets);
        assert_eq!(s1, "=[Sales]");

        let f2 = compile_formula("=[@Cost]", &sheets);
        match &f2.parts[1] {
            FormulaPart::StructuredReference {
                sheet_id,
                col_id,
                is_this_row,
                section,
            } => {
                assert_eq!(*sheet_id, 123);
                assert_eq!(*col_id, Some(2));
                assert!(is_this_row);
                assert_eq!(*section, SheetSection::Data);
            }
            _ => panic!("Expected StructuredReference"),
        }
        let s2 = serialize_formula(&f2, &sheets);
        assert_eq!(s2, "=[@Cost]");

        let f3 = compile_formula("=Sheet1[[#Headers],[Sales]]", &sheets);
        match &f3.parts[1] {
            FormulaPart::StructuredReference {
                sheet_id,
                col_id,
                section,
                ..
            } => {
                assert_eq!(*sheet_id, 123);
                assert_eq!(*col_id, Some(1));
                assert_eq!(*section, SheetSection::Headers);
            }
            _ => panic!("Expected StructuredReference"),
        }
        let s3 = serialize_formula(&f3, &sheets);
        assert_eq!(s3, "=[[#Headers], [Sales]]");

        let mut sheet2 = Sheet::new(crate::core::SheetInit {
            id: Some(456),
            name: Some("Sheet2".to_string()),
            rows: 5,
            cols: 2,
        });
        sheet2.columns[0].name = "Revenue".to_string();
        sheet2.columns[0].id = 3;
        sheet2.columns[1].name = "Expenses".to_string();
        sheet2.columns[1].id = 4;

        let multi_sheets = vec![sheets[0].clone(), sheet2];
        let f4 = compile_formula("=Sheet2[Revenue]", &multi_sheets);
        let s4 = serialize_formula(&f4, &multi_sheets);
        assert_eq!(s4, "=Sheet2[Revenue]");

        let ast = parse_excel_formula("Sheet1[@Sales] + 10").unwrap();
        match ast {
            Expr::BinaryOp { op, left, right: _ } => {
                assert_eq!(op, Op::Add);
                match &*left {
                    Expr::StructuredRef {
                        sheet,
                        column,
                        is_this_row,
                        ..
                    } => {
                        assert_eq!(sheet.as_deref(), Some("Sheet1"));
                        assert_eq!(column.as_deref(), Some("Sales"));
                        assert!(is_this_row);
                    }
                    _ => panic!("Expected StructuredRef"),
                }
            }
            _ => panic!("Expected BinaryOp"),
        }
    }

    #[test]
    fn test_structured_reference_whole_row_no_column() {
        let mut sheet1 = Sheet::new(crate::core::SheetInit {
            id: Some(123),
            name: Some("Sheet1".to_string()),
            rows: 5,
            cols: 2,
        });
        sheet1.columns[0].name = "Sales".to_string();
        sheet1.columns[0].id = 1;
        sheet1.columns[1].name = "Cost".to_string();
        sheet1.columns[1].id = 2;
        let sheets = vec![sheet1];

        let f = compile_formula("=[@]", &sheets);
        match &f.parts[1] {
            FormulaPart::StructuredReference {
                col_id,
                is_this_row,
                section,
                ..
            } => {
                assert_eq!(*col_id, None);
                assert!(is_this_row);
                assert_eq!(*section, SheetSection::Data);
            }
            _ => panic!("Expected StructuredReference, got {:?}", f.parts[1]),
        }
        assert_eq!(serialize_formula(&f, &sheets), "=[@]");

        let ast = parse_excel_formula("[@]").unwrap();
        match ast {
            Expr::StructuredRef {
                column,
                is_this_row,
                ..
            } => {
                assert_eq!(column, None);
                assert!(is_this_row);
            }
            _ => panic!("Expected StructuredRef"),
        }
    }

    #[test]
    fn test_structured_reference_whole_table_sections_no_column() {
        let mut sheet1 = Sheet::new(crate::core::SheetInit {
            id: Some(123),
            name: Some("Sheet1".to_string()),
            rows: 5,
            cols: 2,
        });
        sheet1.columns[0].name = "Sales".to_string();
        sheet1.columns[1].name = "Cost".to_string();
        let sheets = vec![sheet1];

        for (input, expected_section) in [
            ("=[#Data]", SheetSection::Data),
            ("=[#All]", SheetSection::All),
            ("=[#Headers]", SheetSection::Headers),
            ("=[#Totals]", SheetSection::Totals),
        ] {
            let f = compile_formula(input, &sheets);
            match &f.parts[1] {
                FormulaPart::StructuredReference {
                    col_id,
                    is_this_row,
                    section,
                    ..
                } => {
                    assert_eq!(*col_id, None, "input: {input}");
                    assert!(!is_this_row, "input: {input}");
                    assert_eq!(*section, expected_section, "input: {input}");
                }
                _ => panic!(
                    "Expected StructuredReference for {input}, got {:?}",
                    f.parts[1]
                ),
            }
            assert_eq!(serialize_formula(&f, &sheets), input);
        }
    }

    #[test]
    fn test_rewrite_structured_table_reference_renames_table() {
        let rewritten = rewrite_structured_table_reference(
            "=SUM(Sales[Amount])",
            "Sales",
            Some("Revenue"),
            None,
        );
        assert_eq!(rewritten.as_deref(), Some("=SUM(Revenue[Amount])"));
    }

    #[test]
    fn test_rewrite_structured_table_reference_renames_column() {
        let rewritten = rewrite_structured_table_reference(
            "=SUM(Sales[Amount])",
            "Sales",
            None,
            Some(("Amount", "Total")),
        );
        assert_eq!(rewritten.as_deref(), Some("=SUM(Sales[Total])"));
    }

    #[test]
    fn test_rewrite_structured_table_reference_handles_multiple_refs_and_forms() {
        let rewritten = rewrite_structured_table_reference(
            "=Sales[@Amount] + SUM(Sales[Amount]) + Sales[[#Headers],[Amount]]",
            "Sales",
            Some("Revenue"),
            Some(("Amount", "Total")),
        );
        assert_eq!(
            rewritten.as_deref(),
            Some("=Revenue[@Total] + SUM(Revenue[Total]) + Revenue[[#Headers], [Total]]")
        );
    }

    #[test]
    fn test_rewrite_structured_table_reference_ignores_other_tables_and_columns() {
        let rewritten = rewrite_structured_table_reference(
            "=SUM(Other[Amount]) + A1",
            "Sales",
            Some("Revenue"),
            None,
        );
        assert_eq!(rewritten, None);

        let rewritten2 = rewrite_structured_table_reference(
            "=Sales[Quantity]",
            "Sales",
            None,
            Some(("Amount", "Total")),
        );
        assert_eq!(rewritten2, None);
    }

    #[test]
    fn test_rewrite_structured_table_reference_ignores_non_formula_cells() {
        let rewritten = rewrite_structured_table_reference(
            "Sales[Amount] is a great product",
            "Sales",
            Some("Revenue"),
            None,
        );
        assert_eq!(rewritten, None);
    }

    #[test]
    fn test_compile_formula_never_panics_on_unterminated_quote_ending_in_backslash() {
        let _ = compile_formula("=\"\\", &[]);
        let _ = compile_formula("=\"unterminated\\", &[]);
    }

    #[test]
    fn test_rewrite_structured_table_reference_never_panics_on_unterminated_quote() {
        let _ = rewrite_structured_table_reference("=Sales[Amount]&\"\\", "Sales", None, None);
    }

    #[test]
    fn an_error_value_lexes_as_a_literal_rather_than_as_punctuation() {
        assert_eq!(lex_eval("#REF!").unwrap(), vec![EvalToken::Error("#REF!")]);
        assert_eq!(
            lex_eval("#DIV/0!").unwrap(),
            vec![EvalToken::Error("#DIV/0!")]
        );
        assert_eq!(lex_eval("#N/A").unwrap(), vec![EvalToken::Error("#N/A")]);
        assert_eq!(lex_eval("#ref!").unwrap(), vec![EvalToken::Error("#REF!")]);
        assert_eq!(
            lex_eval("1+#REF!").unwrap(),
            vec![
                EvalToken::Number(1.0),
                EvalToken::Op(Op::Add),
                EvalToken::Error("#REF!"),
            ]
        );
    }

    #[test]
    fn a_hash_that_starts_nothing_recognisable_is_left_alone() {
        assert!(match_error_code(&"#NOPE".chars().collect::<Vec<_>>(), 0).is_none());
        assert!(match_error_code(&"#RE".chars().collect::<Vec<_>>(), 0).is_none());
    }

    #[test]
    fn ampersand_lexes_and_parses_as_concatenation() {
        assert_eq!(
            lex_eval("\"A\"&\"B\"").unwrap(),
            vec![
                EvalToken::String("A".to_string()),
                EvalToken::Op(Op::Concat),
                EvalToken::String("B".to_string()),
            ]
        );

        assert_eq!(
            parse_excel_formula("\"x\" & 1 + 2").unwrap(),
            Expr::BinaryOp {
                op: Op::Concat,
                left: Box::new(Expr::String("x".to_string())),
                right: Box::new(Expr::BinaryOp {
                    op: Op::Add,
                    left: Box::new(Expr::Number(1.0)),
                    right: Box::new(Expr::Number(2.0)),
                }),
            }
        );

        match parse_excel_formula("\"a\" & \"b\" = \"ab\"").unwrap() {
            Expr::BinaryOp { op, left, .. } => {
                assert_eq!(op, Op::Eq);
                assert!(matches!(*left, Expr::BinaryOp { op: Op::Concat, .. }));
            }
            other => panic!("Expected equality, got {other:?}"),
        }
    }

    #[test]
    fn test_bracket_slice_parsing() {
        let ast = parse_excel_formula("arr[1:3]").unwrap();
        assert_eq!(
            ast,
            Expr::Slice {
                expr: Box::new(Expr::Identifier("arr".to_string())),
                start: Some(Box::new(Expr::Number(1.0))),
                end: Some(Box::new(Expr::Number(3.0))),
            }
        );

        let ast_no_end = parse_excel_formula("arr[2:]").unwrap();
        assert_eq!(
            ast_no_end,
            Expr::Slice {
                expr: Box::new(Expr::Identifier("arr".to_string())),
                start: Some(Box::new(Expr::Number(2.0))),
                end: None,
            }
        );

        let ast_no_start = parse_excel_formula("arr[:2]").unwrap();
        assert_eq!(
            ast_no_start,
            Expr::Slice {
                expr: Box::new(Expr::Identifier("arr".to_string())),
                start: None,
                end: Some(Box::new(Expr::Number(2.0))),
            }
        );
    }

    #[test]
    fn test_sheet_column_bracket_indexing() {
        let ast = parse_excel_formula("Sheet1[\"Sales\"]").unwrap();
        assert_eq!(
            ast,
            Expr::StructuredRef {
                sheet: Some("Sheet1".to_string()),
                column: Some("Sales".to_string()),
                is_this_row: false,
                section: SheetSection::Data,
            }
        );
    }

    #[test]
    fn at_lexes_and_parses_as_implicit_intersection() {
        assert_eq!(
            lex_eval("@A1:A3").unwrap(),
            vec![
                EvalToken::Op(Op::ImplicitIntersection),
                EvalToken::Identifier("A1".to_string()),
                EvalToken::Colon,
                EvalToken::Identifier("A3".to_string()),
            ]
        );

        assert_eq!(
            parse_excel_formula("@A1:A3").unwrap(),
            Expr::UnaryOp {
                op: Op::ImplicitIntersection,
                expr: Box::new(Expr::RangeRef {
                    sheet: None,
                    start_row: 0,
                    start_col: 0,
                    end_row: 2,
                    end_col: 0,
                    start_row_abs: false,
                    start_col_abs: false,
                    end_row_abs: false,
                    end_col_abs: false,
                }),
            }
        );
    }

    #[test]
    fn xlsx_spill_encoding_preserves_strings_errors_and_table_headers() {
        for (source, expected) in [
            ("SUM($A$1#)", "SUM(_xlfn.ANCHORARRAY($A$1))"),
            (
                "SUM('Data Sheet'!A1#,Sheet2!B2#)",
                "SUM(_xlfn.ANCHORARRAY('Data Sheet'!A1),_xlfn.ANCHORARRAY(Sheet2!B2))",
            ),
            (
                "IFERROR(A1#,\"A1#\"\"B2#\")",
                "IFERROR(_xlfn.ANCHORARRAY(A1),\"A1#\"\"B2#\")",
            ),
            (
                "SUM(Table1[[#Headers],[A1#]])",
                "SUM(Table1[[#Headers],[A1#]])",
            ),
            ("IFERROR(#REF!,\"#VALUE!\")", "IFERROR(#REF!,\"#VALUE!\")"),
            ("SUM(_xlfn.ANCHORARRAY(A1))", "SUM(_xlfn.ANCHORARRAY(A1))"),
        ] {
            assert_eq!(encode_xlsx_spill_references(source), expected, "{source}");
        }
    }

    #[test]
    fn hash_lexes_and_parses_as_spill_operator_after_references() {
        assert_eq!(
            lex_eval("A1#").unwrap(),
            vec![
                EvalToken::Identifier("A1".to_string()),
                EvalToken::Op(Op::Spill),
            ]
        );

        assert_eq!(
            parse_excel_formula("A1#").unwrap(),
            Expr::UnaryOp {
                op: Op::Spill,
                expr: Box::new(Expr::CellRef {
                    sheet: None,
                    row: 0,
                    col: 0,
                    row_abs: false,
                    col_abs: false,
                }),
            }
        );
    }
}