visi-core 0.2.2

use super::*;
use std::collections::HashSet;

fn test_eval<T, F>(source: &str, expected: T, val_getter: F) -> Result<(), EngineError>
where
    T: std::cmp::PartialEq + std::fmt::Debug,
    F: Fn(&ResultData) -> Option<T>,
{
    let sheet = Sheet::new(SheetInit::default());
    let (result, _) = sheet.eval(source, None)?;
    let result_value = val_getter(&result).expect("Failed to get value");
    assert_eq!(expected, result_value);
    Ok(())
}

fn get_int_val(r: &ResultData) -> Option<i64> {
    match r {
        ResultData::Integer(i) => Some(*i),
        ResultData::Float(f) => Some(*f as i64),
        _ => None,
    }
}

fn get_float_val(r: &ResultData) -> Option<f64> {
    match r {
        ResultData::Float(f) => Some(*f),
        ResultData::Integer(i) => Some(*i as f64),
        _ => None,
    }
}

fn get_string_val(r: &ResultData) -> Option<String> {
    match r {
        ResultData::String(s) => Some(s.clone()),
        _ => None,
    }
}

fn test_ints(source: &str, expected: i64) -> Result<(), EngineError> {
    test_eval(source, expected, get_int_val)
}

fn test_floats(source: &str, expected: f64) -> Result<(), EngineError> {
    test_eval(source, expected, get_float_val)
}

fn test_strings(source: &str, expected: &str) -> Result<(), EngineError> {
    test_eval(source, expected.to_string(), get_string_val)
}

#[test]
fn test_integer_evaluations() {
    test_ints("-2", -2).unwrap();
    test_ints("=3 + 4", 7).unwrap();
    test_ints("=9 * 7", 63).unwrap();
    test_ints("=12*12", 144).unwrap();
    test_ints("=3**2", 9).unwrap();
    test_floats("=12 / 2", 6.0).unwrap();
    test_floats("=1 / 2", 0.5).unwrap();
}

#[test]
fn test_float_evaluations() {
    test_floats("-2.0", -2.0).unwrap();
    test_floats("=3.0 + 4.0", 7.0).unwrap();
    test_floats("=sum([1, 2, 3.5])", 6.5).unwrap();
}

#[test]
fn test_excel_formula_evaluations() {
    test_floats("=3 + 4", 7.0).unwrap();
    test_floats("=10 - 2 * 3", 4.0).unwrap();
    test_floats("=(10 - 2) * 3", 24.0).unwrap();
    test_floats("=2 ^ 3", 8.0).unwrap();
    test_floats("=10 / 4", 2.5).unwrap();

    test_floats("=SUM(1, 2, 3)", 6.0).unwrap();
    test_floats("=AVERAGE(1, 2, 3)", 2.0).unwrap();
    test_floats("=MIN(5, 3, 9)", 3.0).unwrap();
    test_floats("=MAX(5, 3, 9)", 9.0).unwrap();
    test_floats("=IF(3 > 2, 10, 20)", 10.0).unwrap();
    test_floats("=IF(1 > 2, 10, 20)", 20.0).unwrap();
    test_floats("=COUNT(1, 2, 3, \"foo\")", 3.0).unwrap();

    test_floats("=LN(EXP(1))", 1.0).unwrap();
    test_floats("=LOG10(100)", 2.0).unwrap();
    test_floats("=CEILING(4.2)", 5.0).unwrap();
    test_floats("=FLOOR(4.8)", 4.0).unwrap();
    test_floats("=TAN(0)", 0.0).unwrap();
    test_floats("=ASIN(0)", 0.0).unwrap();
    test_floats("=ACOS(1)", 0.0).unwrap();
    test_floats("=ATAN(0)", 0.0).unwrap();

    test_booleans("=TRUE()", true).unwrap();
    test_booleans("=FALSE()", false).unwrap();
    test_booleans("=AND(TRUE(), TRUE)", true).unwrap();

    let mut sheet = Sheet::new(SheetInit {
        name: Some("table_1".to_string()),
        rows: 5,
        cols: 5,
        ..Default::default()
    });

    sheet.set_cell_src(0, 0, "10".to_string());
    sheet.set_cell_src(0, 1, "20".to_string());
    sheet.set_cell_src(1, 0, "30".to_string());
    sheet.set_cell_src(1, 1, "40".to_string());
    sheet.commit(None).unwrap();

    sheet.set_cell_src(2, 0, "=A1 + B1".to_string());
    sheet.commit(None).unwrap();
    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(2, 0))),
        Some(30.0)
    );

    sheet.set_cell_src(2, 1, "=SUM(A1:B2)".to_string());
    sheet.commit(None).unwrap();
    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(2, 1))),
        Some(100.0)
    );

    sheet.set_cell_src(2, 2, "=AVERAGE(A1:B2)".to_string());
    sheet.commit(None).unwrap();
    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(2, 2))),
        Some(25.0)
    );

    let mut table2 = Sheet::new(SheetInit {
        name: Some("table_2".to_string()),
        rows: 5,
        cols: 5,
        ..Default::default()
    });
    table2.set_cell_src(0, 0, "42".to_string());
    table2.commit(None).unwrap();

    let mut context = Context::default();
    context.sheets.insert("table_2".to_string(), &table2);

    let (res_cross, _) = sheet.eval("=table_2!A1", Some(&context)).unwrap();
    assert_eq!(get_float_val(&res_cross), Some(42.0));
}

fn get_bool_val(r: &ResultData) -> Option<bool> {
    match r {
        ResultData::Boolean(b) => Some(*b),
        _ => None,
    }
}

fn test_booleans(source: &str, expected: bool) -> Result<(), EngineError> {
    test_eval(source, expected, get_bool_val)
}

#[test]
fn test_new_excel_formulas() {
    test_booleans("=AND(1 > 0, 2 > 1)", true).unwrap();
    test_booleans("=AND(1 > 0, 2 < 1)", false).unwrap();
    test_booleans("=OR(1 > 0, 2 < 1)", true).unwrap();
    test_booleans("=OR(1 < 0, 2 < 1)", false).unwrap();
    test_booleans("=NOT(1 > 0)", false).unwrap();
    test_booleans("=NOT(1 < 0)", true).unwrap();

    test_strings("=CONCAT(\"Hello\", \" \", \"World\")", "Hello World").unwrap();
    test_strings("=LEFT(\"hello\", 3)", "hel").unwrap();
    test_strings("=LEFT(\"hello\")", "h").unwrap();
    test_strings("=RIGHT(\"hello\", 3)", "llo").unwrap();
    test_strings("=RIGHT(\"hello\")", "o").unwrap();
    test_strings("=MID(\"hello\", 2, 3)", "ell").unwrap();
    test_floats("=LEN(\"hello\")", 5.0).unwrap();
    test_strings("=TRIM(\"  hello   world  \")", "hello world").unwrap();
    test_strings("=UPPER(\"hello\")", "HELLO").unwrap();
    test_strings("=LOWER(\"HELLO\")", "hello").unwrap();
    test_strings("=PROPER(\"hello world\")", "Hello World").unwrap();

    test_booleans("=ISNUMBER(5)", true).unwrap();
    test_booleans("=ISNUMBER(\"foo\")", false).unwrap();
    test_booleans("=ISTEXT(\"foo\")", true).unwrap();
    test_booleans("=ISTEXT(5)", false).unwrap();
    test_booleans("=ISBLANK(GET(10, 10))", true).unwrap();
    test_booleans("=ISERROR(1 / 0)", true).unwrap();
    test_booleans("=ISERROR(5)", false).unwrap();

    test_floats("=PRODUCT(2, 3, 4)", 24.0).unwrap();
    test_floats("=MOD(10, 3)", 1.0).unwrap();
    test_floats("=MOD(-10, 3)", 2.0).unwrap();
    test_floats("=COUNTA(1, \"foo\", GET(10, 10))", 2.0).unwrap();

    test_floats("=IFERROR(5, 10)", 5.0).unwrap();
    test_floats("=IFERROR(1 / 0, 10)", 10.0).unwrap();

    let (today_res, _) = Sheet::new(SheetInit::default())
        .eval("=TODAY()", None)
        .unwrap();
    if let ResultData::String(s) = today_res {
        assert_eq!(s.len(), 10);
        assert_eq!(&s[4..5], "-");
        assert_eq!(&s[7..8], "-");
    } else {
        panic!("Expected TODAY() to be string");
    }

    let (now_res, _) = Sheet::new(SheetInit::default())
        .eval("=NOW()", None)
        .unwrap();
    if let ResultData::String(s) = now_res {
        assert_eq!(s.len(), 19);
        assert_eq!(&s[10..11], " ");
        assert_eq!(&s[13..14], ":");
    } else {
        panic!("Expected NOW() to be string");
    }

    let mut sheet = Sheet::new(SheetInit {
        name: Some("data_table".to_string()),
        rows: 5,
        cols: 5,
        ..Default::default()
    });

    sheet.set_cell_src(0, 0, "1".to_string());
    sheet.set_cell_src(0, 1, "10".to_string());
    sheet.set_cell_src(0, 2, "Apples".to_string());
    sheet.set_cell_src(1, 0, "2".to_string());
    sheet.set_cell_src(1, 1, "20".to_string());
    sheet.set_cell_src(1, 2, "Oranges".to_string());
    sheet.set_cell_src(2, 0, "3".to_string());
    sheet.set_cell_src(2, 1, "30".to_string());
    sheet.set_cell_src(2, 2, "Apples".to_string());
    sheet.commit(None).unwrap();

    sheet.set_cell_src(3, 0, "=MATCH(2, A1:A3, 0)".to_string());

    sheet.set_cell_src(3, 1, "=INDEX(A1:C3, 2, 2)".to_string());

    sheet.set_cell_src(3, 2, "=VLOOKUP(2, A1:C3, 3, FALSE)".to_string());

    sheet.set_cell_src(3, 3, "=SUMIF(C1:C3, \"Apples\", B1:B3)".to_string());

    sheet.set_cell_src(
        4,
        0,
        "=SUMIFS(B1:B3, C1:C3, \"Apples\", A1:A3, \">1\")".to_string(),
    );

    sheet.set_cell_src(4, 1, "=COUNTIF(C1:C3, \"Apples\")".to_string());

    sheet.set_cell_src(
        4,
        2,
        "=COUNTIFS(C1:C3, \"Apples\", A1:A3, \">1\")".to_string(),
    );

    sheet.set_cell_src(4, 3, "=XLOOKUP(30, B1:B3, C1:C3)".to_string());
    sheet.set_cell_src(4, 4, "=COUNTIF(C1:C3, \"app*\")".to_string());
    sheet.commit(None).unwrap();

    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(3, 0))),
        Some(2.0)
    );
    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(3, 1))),
        Some(20.0)
    );
    assert_eq!(
        get_string_val(&sheet.get_result_data(&CellRef::new(3, 2))),
        Some("Oranges".to_string())
    );
    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(3, 3))),
        Some(40.0)
    );
    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(4, 0))),
        Some(30.0)
    );
    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(4, 1))),
        Some(2.0)
    );
    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(4, 4))),
        Some(2.0)
    );
    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(4, 2))),
        Some(1.0)
    );
    assert_eq!(
        get_string_val(&sheet.get_result_data(&CellRef::new(4, 3))),
        Some("Apples".to_string())
    );

    let (mmult_res, _) = sheet.eval("=MMULT(A1:B2, A1:B2)", None).unwrap();
    if let ResultData::List(list) = mmult_res {
        assert_eq!(list.len(), 4);
        assert_eq!(get_float_val(&list[0]), Some(21.0));
        assert_eq!(get_float_val(&list[1]), Some(210.0));
        assert_eq!(get_float_val(&list[2]), Some(42.0));
        assert_eq!(get_float_val(&list[3]), Some(420.0));
    } else {
        panic!("Expected MMULT to return a list");
    }
}

#[test]
fn test_countif_wildcard_criteria_match_excel() {
    let mut sheet = Sheet::new(SheetInit::default());
    sheet.set_cell_src(0, 0, "Alpha".to_string());
    sheet.set_cell_src(1, 0, "alphabet".to_string());
    sheet.set_cell_src(2, 0, "Beta".to_string());
    sheet.set_cell_src(3, 0, "A?pha".to_string());
    sheet.set_cell_src(0, 1, "=COUNTIF(A1:A4, \"Al*\")".to_string());
    sheet.set_cell_src(1, 1, "=COUNTIF(A1:A4, \"A~?pha\")".to_string());
    sheet.commit(None).unwrap();

    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(0, 1))),
        Some(2.0)
    );
    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(1, 1))),
        Some(1.0)
    );
}

fn approx_float(source: &str, expected: f64) {
    let sheet = Sheet::new(SheetInit::default());
    let (result, _) = sheet.eval(source, None).unwrap();
    let f = get_float_val(&result).unwrap_or_else(|| panic!("{source} did not return a number"));
    assert!((f - expected).abs() < 5e-3, "{source}: {f} != {expected}");
}

#[test]
fn test_financial_functions() {
    // These mirror the pure-math tests in `core::finance` but go through
    // the parser/dispatch path end-to-end.
    approx_float("=PMT(0.08/12, 10, 10000)", -1037.03);
    approx_float("=FV(0.06/12, 10, -200, -500, 1)", 2581.40);
    approx_float("=PV(0.08/12, 20*12, 500)", -59777.15);
    approx_float("=NPER(0.12/12, -100, -1000, 10000, 1)", 59.6739);
    approx_float(
        "=IPMT(0.10/12, 1, 36, 8000000) + PPMT(0.10/12, 1, 36, 8000000)",
        -258137.4976,
    );
    approx_float("=ISPMT(0.10/12, 1, 3*12, 8000000)", -64814.81481481482);
    approx_float("=NPV(0.10, -10000, 3000, 4200, 6800)", 1188.44);
    approx_float("=SLN(30000, 7500, 10)", 2250.0);
    approx_float("=SYD(30000, 7500, 10, 1)", 4090.91);
    approx_float("=DDB(2400, 300, 10, 1)", 480.0);
    approx_float("=EFFECT(0.0525, 4)", 0.0535427302);
    approx_float("=NOMINAL(0.0535427302, 4)", 0.0525);
    approx_float("=DOLLARDE(1.02, 16)", 1.125);
    approx_float("=DOLLARFR(1.125, 16)", 1.02);
    approx_float("=RRI(10, 1000, 2000)", 0.0717735);
    approx_float("=PDURATION(0.025, 2000, 2200)", 3.86045);
    approx_float("=CUMIPMT(0.09/12, 30*12, 125000, 13, 24, 0)", -11135.23);
    approx_float("=CUMPRINC(0.09/12, 30*12, 125000, 13, 24, 0)", -934.11);

    // No `{...}` array-literal syntax in the parser, so exercise the
    // range-argument financial functions (IRR/FVSCHEDULE/XNPV) against
    // real cells instead of inline arrays.
    let mut fin_sheet = Sheet::new(SheetInit {
        name: Some("fin".to_string()),
        rows: 6,
        cols: 4,
        ..Default::default()
    });
    for (i, v) in [-70000, 12000, 15000, 18000, 21000, 26000]
        .iter()
        .enumerate()
    {
        fin_sheet.set_cell_src(i, 0, v.to_string());
    }
    for (i, v) in [0.09, 0.11, 0.1].iter().enumerate() {
        fin_sheet.set_cell_src(i, 1, v.to_string());
    }
    for (i, v) in [-10000, 2750, 4250, 3250, 2750].iter().enumerate() {
        fin_sheet.set_cell_src(i, 2, v.to_string());
    }
    // Excel serials for 2008-01-01, 2008-03-01, 2008-10-30, 2009-02-15, 2009-04-01
    for (i, v) in [39448, 39508, 39751, 39859, 39904].iter().enumerate() {
        fin_sheet.set_cell_src(i, 3, v.to_string());
    }
    fin_sheet.commit(None).unwrap();

    let (irr_res, _) = fin_sheet.eval("=IRR(A1:A6)", None).unwrap();
    assert!((get_float_val(&irr_res).unwrap() - 0.0866).abs() < 5e-3);

    let (fvs_res, _) = fin_sheet.eval("=FVSCHEDULE(1, B1:B3)", None).unwrap();
    assert!((get_float_val(&fvs_res).unwrap() - 1.33089).abs() < 5e-3);

    let (xnpv_res, _) = fin_sheet.eval("=XNPV(0.09, C1:C5, D1:D5)", None).unwrap();
    assert!((get_float_val(&xnpv_res).unwrap() - 2086.65).abs() < 5e-2);
}

#[test]
fn test_precedence() {
    test_ints("=3 + 4 * 5", 23).unwrap();
    test_ints("=3 * 4 + 5", 17).unwrap();
    test_ints("=3 * (4 + 5)", 27).unwrap();
    test_ints("=3 * (4 + 5) * 2", 54).unwrap();
    test_ints("=3 * (4 + 5) * 2 + 1", 55).unwrap();
    test_ints("=3 * (4 + 5) * (2 + 1)", 81).unwrap();
}

#[test]
fn test_load() {
    let mut sheet = Sheet::new(SheetInit::default());

    sheet.insert(
        TextCellRef {
            row: 0,
            col: 0,
            char_offset: 0,
        },
        "1",
    );
    sheet.commit(None).unwrap();

    let (two, _) = sheet.eval("=A1 + 1", None).unwrap();
    match two {
        ResultData::Integer(val) => assert_eq!(val, 2),
        ResultData::Float(val) => assert_eq!(val, 2.0),
        _ => panic!("Expected number result"),
    }

    let (cell_alone, _) = sheet.eval("=A1", None).unwrap();
    match cell_alone {
        ResultData::Integer(val) => assert_eq!(val, 1),
        ResultData::Float(val) => assert_eq!(val, 1.0),
        _ => panic!("Expected number result"),
    }
}

#[test]
fn test_concatenation() {
    test_strings("=\"Hello World\"", "Hello World").unwrap();

    test_strings("=CONCATENATE(\"A\", \"B\")", "AB").unwrap();

    test_strings("=CONCATENATE(\"Hello\", \" World\")", "Hello World").unwrap();
    test_strings("=CONCATENATE(\"ABC\", \"DEF\")", "ABCDEF").unwrap();

    test_strings("=CONCATENATE(str(5), \" items\")", "5 items").unwrap();
    test_strings("=CONCATENATE(\"Value: \", str(42))", "Value: 42").unwrap();
    test_strings("=CONCATENATE(str(3.14), \" is pi\")", "3.14 is pi").unwrap();
    test_strings("=CONCATENATE(\"Result: \", str(True))", "Result: TRUE").unwrap();
}

#[test]
fn test_table_naming() {
    let table1 = Sheet::new(SheetInit::default());
    assert_eq!(table1.name, "table_1");

    let table2 = Sheet::new(SheetInit {
        name: Some("my_table".to_string()),
        ..Default::default()
    });
    assert_eq!(table2.name, "my_table");

    let table3 = Sheet::new(SheetInit {
        name: None,
        ..Default::default()
    });
    assert_eq!(table3.name, "table_1");
}

#[test]
fn test_table_references() {
    let table1 = Sheet::new(SheetInit {
        id: None,
        name: Some("table_1".to_string()),
        rows: 5,
        cols: 5,
    });

    let mut table2 = Sheet::new(SheetInit {
        id: None,
        name: Some("table_2".to_string()),
        rows: 5,
        cols: 5,
    });

    table2.insert(
        TextCellRef {
            row: 0,
            col: 0,
            char_offset: 0,
        },
        "42",
    );
    table2.commit(None).unwrap();

    table2.insert(
        TextCellRef {
            row: 1,
            col: 1,
            char_offset: 0,
        },
        "100",
    );
    table2.commit(None).unwrap();

    let mut context = Context::default();
    context.sheets.insert("table_2".to_string(), &table2);

    let (result, _) = table1.eval("=table_2!A1", Some(&context)).unwrap();
    assert_eq!(get_int_val(&result), Some(42));

    let (result2, _) = table1.eval("=table_2!B2", Some(&context)).unwrap();
    assert_eq!(get_int_val(&result2), Some(100));

    let (result3, _) = table1.eval("=table_2!A1 + 8", Some(&context)).unwrap();
    assert_eq!(get_int_val(&result3), Some(50));
}

#[test]
fn test_context_from_tables() {
    let table1 = Sheet::new(SheetInit {
        id: None,
        name: Some("table_1".to_string()),
        rows: 5,
        cols: 5,
    });

    let mut table2 = Sheet::new(SheetInit {
        id: None,
        name: Some("table_2".to_string()),
        rows: 5,
        cols: 5,
    });

    table2.insert(
        TextCellRef {
            row: 0,
            col: 0,
            char_offset: 0,
        },
        "99",
    );
    table2.commit(None).unwrap();

    let sheets = vec![table1, table2];
    let mut context = Context::new();
    for sheet in &sheets {
        context.add_table(sheet.name.clone(), sheet);
    }

    assert!(context.sheets.contains_key("table_1"));
    assert!(context.sheets.contains_key("table_2"));

    let (result, _) = sheets[0].eval("=table_2!A1", Some(&context)).unwrap();
    assert_eq!(get_int_val(&result), Some(99));
}

#[test]
fn test_builtin_math_functions() {
    let sheet = Sheet::new(SheetInit::default());

    let (result, _) = sheet.eval("=abs(-5)", None).unwrap();
    assert_eq!(get_int_val(&result), Some(5));

    let (result, _) = sheet.eval("=abs(-3.14)", None).unwrap();
    #[allow(clippy::approx_constant)]
    let expected = 3.14; // ABS(-3.14), not an approximation of PI
    assert_eq!(get_float_val(&result), Some(expected));

    let (result, _) = sheet.eval("=sqrt(16)", None).unwrap();
    assert_eq!(get_float_val(&result), Some(4.0));

    let (result, _) = sheet.eval("=sin(0)", None).unwrap();
    assert_eq!(get_float_val(&result), Some(0.0));

    let (result, _) = sheet.eval("=cos(0)", None).unwrap();
    assert_eq!(get_float_val(&result), Some(1.0));

    let (result, _) = sheet.eval("=floor(3.9)", None).unwrap();
    assert_eq!(get_int_val(&result), Some(3));

    let (result, _) = sheet.eval("=ceiling(3.1)", None).unwrap();
    assert_eq!(get_int_val(&result), Some(4));

    let (result, _) = sheet.eval("=round(3.7)", None).unwrap();
    assert_eq!(get_int_val(&result), Some(4));

    let (result, _) = sheet.eval("=round(3.2)", None).unwrap();
    assert_eq!(get_int_val(&result), Some(3));

    let (result, _) = sheet.eval("=exp(0)", None).unwrap();
    assert_eq!(get_float_val(&result), Some(1.0));

    let (result, _) = sheet.eval("=ln(1)", None).unwrap();
    assert_eq!(get_float_val(&result), Some(0.0));

    let (result, _) = sheet.eval("=log10(10)", None).unwrap();
    assert_eq!(get_float_val(&result), Some(1.0));

    let (result, _) = sheet.eval("=rand()", None).unwrap();
    if let ResultData::Float(val) = result {
        assert!((0.0..1.0).contains(&val));
    } else {
        panic!("Expected RAND to return a Float, got {:?}", result);
    }

    let (result, _) = sheet.eval("=randbetween(5, 10)", None).unwrap();
    if let ResultData::Integer(val) = result {
        assert!((5..=10).contains(&val));
    } else {
        panic!(
            "Expected RANDBETWEEN to return an Integer, got {:?}",
            result
        );
    }
}
#[test]
fn test_dependency_propagation() {
    let mut sheet = Sheet::new(SheetInit::default());

    sheet.insert(
        TextCellRef {
            row: 0,
            col: 0,
            char_offset: 0,
        },
        "10",
    );
    sheet.commit(None).unwrap();

    sheet.insert(
        TextCellRef {
            row: 0,
            col: 1,
            char_offset: 0,
        },
        "=A1 + 5",
    );
    sheet.commit(None).unwrap();

    let b1 = sheet.eval("=B1", None).unwrap().0;
    assert_eq!(get_int_val(&b1), Some(15));

    sheet.columns[0].src[0] = "20".to_string();
    sheet.columns[0].dirty_indices.push(0);

    sheet.commit(None).unwrap();

    let b1_new = sheet.eval("=B1", None).unwrap().0;
    assert_eq!(get_int_val(&b1_new), Some(25));

    sheet.insert(
        TextCellRef {
            row: 0,
            col: 2,
            char_offset: 0,
        },
        "=B1 * 2",
    );
    sheet.commit(None).unwrap();

    let c1 = sheet.eval("=C1", None).unwrap().0;
    assert_eq!(get_int_val(&c1), Some(50));

    sheet.insert(
        TextCellRef {
            row: 0,
            col: 0,
            char_offset: 0,
        },
        "1",
    );

    sheet.columns[0].src[0] = String::new();
    sheet.insert(
        TextCellRef {
            row: 0,
            col: 0,
            char_offset: 0,
        },
        "1",
    );
    sheet.commit(None).unwrap();

    let b1_final = sheet.eval("=B1", None).unwrap().0;
    let c1_final = sheet.eval("=C1", None).unwrap().0;

    assert_eq!(get_int_val(&b1_final), Some(6));
    assert_eq!(get_int_val(&c1_final), Some(12));
}
#[test]
fn test_cross_table_dependency_propagation() {
    let mut table1 = Sheet::new(SheetInit {
        name: Some("Sheet1".to_string()),
        ..Default::default()
    });

    let mut table2 = Sheet::new(SheetInit {
        name: Some("Sheet2".to_string()),
        ..Default::default()
    });

    table1.insert(
        TextCellRef {
            row: 0,
            col: 0,
            char_offset: 0,
        },
        "10",
    );
    table1.commit(None).unwrap();

    table2.insert(
        TextCellRef {
            row: 0,
            col: 0,
            char_offset: 0,
        },
        "=Sheet1!A1 * 2",
    );

    let mut context = Context::new();
    context.add_table("Sheet1".to_string(), &table1);
    table2.commit(Some(&context)).unwrap();

    let b1 = table2.eval("=A1", None).unwrap().0;
    assert_eq!(get_int_val(&b1), Some(20));

    table1.columns[0].src[0] = "20".to_string();
    table1.columns[0].dirty_indices.push(0);

    let updated_cells_1 = table1.commit(None).unwrap();
    assert!(updated_cells_1.contains(&CellRef::new(0, 0)));

    // `commit` propagates local dependencies only, so Sheet2 has no idea
    // Sheet1 moved. Marking it wholesale is how `WorkbookManager::evaluate`
    // drives cross-sheet propagation.
    table2.mark_all_dirty();

    let mut context = Context::new();
    context.add_table("Sheet1".to_string(), &table1);
    table2.commit(Some(&context)).unwrap();

    let b1_new = table2.eval("=A1", None).unwrap().0;
    assert_eq!(get_int_val(&b1_new), Some(40));
}

#[test]
fn test_table_serialization() {
    let mut sheet = Sheet::new(SheetInit {
        name: Some("test_table".to_string()),
        ..Default::default()
    });

    let cell1 = CellRef::new(0, 0);
    let cell2 = CellRef::new(1, 1);
    let dep = Dependency::Local(cell1);

    let mut dependents = HashSet::new();
    dependents.insert(cell2);
    sheet.dependencies.insert(dep.clone(), dependents);

    let mut providers = HashSet::new();
    providers.insert(dep);
    sheet.dependencies_rev.insert(cell2, providers);

    let json = serde_json::to_string(&sheet).expect("Failed to serialize sheet");

    let deserialized: Sheet = serde_json::from_str(&json).expect("Failed to deserialize sheet");

    assert_eq!(deserialized.name, sheet.name);

    assert_eq!(deserialized.dependencies.len(), 0);
    assert_eq!(deserialized.dependencies_rev.len(), 0);
}

#[test]
fn test_error_handling() {
    let mut sheet = Sheet::new(SheetInit::default());

    sheet.insert(
        TextCellRef {
            row: 0,
            col: 0,
            char_offset: 0,
        },
        "=1 / 0",
    );
    sheet.commit(None).unwrap();

    let res = sheet.get_result_data(&CellRef::new(0, 0));
    match res {
        ResultData::Error(e) => {
            assert!(
                e.contains("#DIV/0!")
                    || e.contains("ZeroDivisionError")
                    || e.contains("division by zero")
            )
        }
        _ => panic!("Expected error, got {:?}", res),
    }

    sheet.insert(
        TextCellRef {
            row: 1,
            col: 0,
            char_offset: 0,
        },
        "=A1 + 1",
    );
    sheet.commit(None).unwrap();

    let res2 = sheet.get_result_data(&CellRef::new(1, 0));
    match res2 {
        ResultData::Error(_) => {}
        _ => panic!("Expected error to propagate, got {:?}", res2),
    }
}

#[test]
fn test_structured_reference_basic_column() {
    let mut sheet = Sheet::new(SheetInit {
        name: Some("table_1".to_string()),
        rows: 3,
        cols: 3,
        ..Default::default()
    });
    sheet.columns[0].name = "Sales".to_string();
    sheet.columns[1].name = "Cost".to_string();

    sheet.set_cell_src(0, 0, "10".to_string());
    sheet.set_cell_src(1, 0, "20".to_string());
    sheet.set_cell_src(2, 0, "30".to_string());
    // Unqualified structured reference (same sheet).
    sheet.set_cell_src(0, 2, "=SUM([Sales])".to_string());
    // Qualified with the table (sheet) name.
    sheet.set_cell_src(1, 2, "=SUM(table_1[Sales])".to_string());
    sheet.commit(None).unwrap();

    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(0, 2))),
        Some(60.0)
    );
    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(1, 2))),
        Some(60.0)
    );
}

#[test]
fn test_structured_reference_this_row() {
    let mut sheet = Sheet::new(SheetInit {
        name: Some("table_1".to_string()),
        rows: 2,
        cols: 3,
        ..Default::default()
    });
    sheet.columns[0].name = "Sales".to_string();
    sheet.columns[1].name = "Cost".to_string();
    sheet.columns[2].name = "Profit".to_string();

    sheet.set_cell_src(0, 0, "100".to_string());
    sheet.set_cell_src(0, 1, "40".to_string());
    sheet.set_cell_src(1, 0, "200".to_string());
    sheet.set_cell_src(1, 1, "50".to_string());

    sheet.set_cell_src(0, 2, "=[@Sales] - [@Cost]".to_string());
    sheet.set_cell_src(1, 2, "=[@Sales] - [@Cost]".to_string());
    sheet.commit(None).unwrap();

    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(0, 2))),
        Some(60.0)
    );
    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(1, 2))),
        Some(150.0)
    );
}

#[test]
fn test_structured_reference_headers_and_totals_sections() {
    let mut sheet = Sheet::new(SheetInit {
        name: Some("table_1".to_string()),
        rows: 2,
        cols: 2,
        ..Default::default()
    });
    sheet.columns[0].name = "Sales".to_string();
    sheet.columns[1].name = "Cost".to_string();

    let (res, _) = sheet.eval("=table_1[[#Headers],[Sales]]", None).unwrap();
    assert_eq!(get_string_val(&res), Some("Sales".to_string()));

    let (res_bare, _) = sheet.eval("=[[#Headers],[Cost]]", None).unwrap();
    assert_eq!(get_string_val(&res_bare), Some("Cost".to_string()));

    // No totals row concept exists on the underlying table, so a totals
    // reference resolves to an empty/None result rather than erroring.
    let (res_totals, _) = sheet.eval("=[[#Totals],[Sales]]", None).unwrap();
    assert!(matches!(res_totals, ResultData::None));
}

#[test]
fn test_structured_reference_cross_sheet() {
    let mut sheet2 = Sheet::new(SheetInit {
        name: Some("Sheet2".to_string()),
        rows: 2,
        cols: 1,
        ..Default::default()
    });
    sheet2.columns[0].name = "Revenue".to_string();
    sheet2.set_cell_src(0, 0, "111".to_string());
    sheet2.set_cell_src(1, 0, "222".to_string());
    sheet2.commit(None).unwrap();

    let mut sheet1 = Sheet::new(SheetInit {
        name: Some("Sheet1".to_string()),
        rows: 1,
        cols: 1,
        ..Default::default()
    });
    sheet1.set_cell_src(0, 0, "=SUM(Sheet2[Revenue])".to_string());

    let mut context = Context::default();
    context.sheets.insert("Sheet2".to_string(), &sheet2);
    sheet1.commit(Some(&context)).unwrap();

    assert_eq!(
        get_float_val(&sheet1.get_result_data(&CellRef::new(0, 0))),
        Some(333.0)
    );
}

#[test]
fn test_structured_reference_aggregates_ignore_text_like_a_range() {
    let mut sheet = Sheet::new(SheetInit {
        name: Some("table_1".to_string()),
        rows: 3,
        cols: 2,
        ..Default::default()
    });
    sheet.columns[0].name = "Sales".to_string();

    sheet.set_cell_src(0, 0, "10".to_string());
    sheet.set_cell_src(1, 0, "\"n/a\"".to_string());
    sheet.set_cell_src(2, 0, "20".to_string());
    sheet.set_cell_src(0, 1, "=SUM([Sales])".to_string());
    sheet.set_cell_src(1, 1, "=AVERAGE([Sales])".to_string());
    sheet.commit(None).unwrap();

    // A structured reference behaves like a range reference: non-numeric
    // text cells are ignored rather than raising #VALUE!.
    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(0, 1))),
        Some(30.0)
    );
    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(1, 1))),
        Some(15.0)
    );
}

#[test]
fn test_structured_reference_this_row_used_directly_in_sum_ignores_text() {
    // `[@Column]` evaluates to a single scalar cell value (like a plain
    // `CellRef`), not a `List`. When passed directly as a function argument
    // (e.g. `SUM([@Sales])`), a non-numeric text cell must be ignored just
    // like `SUM(A1)` would ignore a text cell in A1 -- not raise #VALUE!.
    let mut sheet = Sheet::new(SheetInit {
        name: Some("table_1".to_string()),
        rows: 2,
        cols: 2,
        ..Default::default()
    });
    sheet.columns[0].name = "Sales".to_string();

    sheet.set_cell_src(0, 0, "10".to_string());
    sheet.set_cell_src(1, 0, "\"n/a\"".to_string());
    sheet.set_cell_src(0, 1, "=SUM([@Sales])".to_string());
    sheet.set_cell_src(1, 1, "=SUM([@Sales])".to_string());
    sheet.commit(None).unwrap();

    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(0, 1))),
        Some(10.0)
    );
    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(1, 1))),
        Some(0.0)
    );
}

#[test]
fn test_structured_reference_whole_table_data_section() {
    let mut sheet2 = Sheet::new(SheetInit {
        name: Some("Sheet2".to_string()),
        rows: 2,
        cols: 2,
        ..Default::default()
    });
    sheet2.columns[0].name = "A".to_string();
    sheet2.columns[1].name = "B".to_string();
    sheet2.set_cell_src(0, 0, "1".to_string());
    sheet2.set_cell_src(1, 0, "2".to_string());
    sheet2.set_cell_src(0, 1, "3".to_string());
    sheet2.set_cell_src(1, 1, "4".to_string());
    sheet2.commit(None).unwrap();

    let mut sheet1 = Sheet::new(SheetInit {
        name: Some("Sheet1".to_string()),
        rows: 1,
        cols: 1,
        ..Default::default()
    });
    // `[#Data]` with no column name spans every column in the table.
    sheet1.set_cell_src(0, 0, "=SUM(Sheet2[#Data])".to_string());

    let mut context = Context::default();
    context.sheets.insert("Sheet2".to_string(), &sheet2);
    sheet1.commit(Some(&context)).unwrap();

    assert_eq!(
        get_float_val(&sheet1.get_result_data(&CellRef::new(0, 0))),
        Some(10.0)
    );
}

#[test]
fn test_structured_reference_whole_row_no_column() {
    let mut sheet = Sheet::new(SheetInit {
        name: Some("table_1".to_string()),
        rows: 2,
        cols: 3,
        ..Default::default()
    });
    sheet.columns[0].name = "Sales".to_string();
    sheet.columns[1].name = "Cost".to_string();
    sheet.columns[2].name = "Extra".to_string();

    sheet.set_cell_src(0, 0, "10".to_string());
    sheet.set_cell_src(0, 1, "3".to_string());
    sheet.set_cell_src(0, 2, "2".to_string());
    sheet.commit(None).unwrap();

    // `[@]` (this row, no column) spans every column of the current row.
    let (res, _) = sheet
        .eval_with_row("=SUM([@])", None, Some(0), None)
        .unwrap();
    assert_eq!(get_float_val(&res), Some(15.0));
}

#[test]
fn test_structured_reference_missing_column_errors() {
    let sheet = Sheet::new(SheetInit {
        name: Some("table_1".to_string()),
        rows: 2,
        cols: 2,
        ..Default::default()
    });
    assert!(sheet.eval("=[NoSuchColumn]", None).is_err());
}

#[test]
fn test_structured_reference_missing_table_errors() {
    let sheet = Sheet::new(SheetInit {
        name: Some("table_1".to_string()),
        rows: 1,
        cols: 1,
        ..Default::default()
    });
    assert!(sheet.eval("=NoSuchTable[Col]", None).is_err());
}

#[test]
fn test_structured_reference_recomputes_on_column_change() {
    let mut sheet = Sheet::new(SheetInit {
        name: Some("table_1".to_string()),
        rows: 2,
        cols: 2,
        ..Default::default()
    });
    sheet.columns[0].name = "Sales".to_string();

    sheet.set_cell_src(0, 0, "10".to_string());
    sheet.set_cell_src(1, 0, "20".to_string());
    sheet.set_cell_src(0, 1, "=SUM([Sales])".to_string());
    sheet.commit(None).unwrap();
    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(0, 1))),
        Some(30.0)
    );

    // Changing a cell elsewhere in the referenced column should invalidate
    // and recompute the dependent structured-reference formula.
    sheet.set_cell_src(1, 0, "50".to_string());
    sheet.commit(None).unwrap();
    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(0, 1))),
        Some(60.0)
    );
}

#[test]
fn test_excel_table_structured_reference_respects_table_row_bounds() {
    // Column 0 has data both above and below the defined table's row range;
    // a structured reference into the table must only see the table's own
    // rows, unlike the legacy whole-sheet fallback which scans every row.
    let mut sheet = Sheet::new(SheetInit {
        name: Some("Sheet1".to_string()),
        rows: 5,
        cols: 2,
        ..Default::default()
    });
    sheet.set_cell_src(0, 0, "999".to_string()); // above the table
    sheet.set_cell_src(1, 0, "Amount".to_string()); // header row
    sheet.set_cell_src(2, 0, "10".to_string());
    sheet.set_cell_src(3, 0, "20".to_string());
    sheet.set_cell_src(4, 0, "888".to_string()); // below the table
    sheet.commit(None).unwrap();

    sheet
        .add_table("Sales".to_string(), 1, 0, 3, 0, true, false)
        .unwrap();

    let (res, _) = sheet.eval("=SUM(Sales[Amount])", None).unwrap();
    assert_eq!(get_float_val(&res), Some(30.0));
}

#[test]
fn test_excel_table_totals_row_returns_real_value() {
    let mut sheet = Sheet::new(SheetInit {
        name: Some("Sheet1".to_string()),
        rows: 4,
        cols: 1,
        ..Default::default()
    });
    sheet.set_cell_src(0, 0, "Amount".to_string());
    sheet.set_cell_src(1, 0, "10".to_string());
    sheet.set_cell_src(2, 0, "20".to_string());
    sheet.set_cell_src(3, 0, "=SUM(A2:A3)".to_string());
    sheet.commit(None).unwrap();

    sheet
        .add_table("Sales".to_string(), 0, 0, 3, 0, true, true)
        .unwrap();

    let (res, _) = sheet.eval("=Sales[[#Totals],[Amount]]", None).unwrap();
    assert_eq!(get_float_val(&res), Some(30.0));
}

#[test]
fn test_excel_table_totals_section_without_totals_row_is_none() {
    let mut sheet = Sheet::new(SheetInit {
        name: Some("Sheet1".to_string()),
        rows: 3,
        cols: 1,
        ..Default::default()
    });
    sheet.set_cell_src(0, 0, "Amount".to_string());
    sheet.set_cell_src(1, 0, "10".to_string());
    sheet.set_cell_src(2, 0, "20".to_string());
    sheet.commit(None).unwrap();

    // has_totals_row = false: no totals row is reserved at all.
    sheet
        .add_table("Sales".to_string(), 0, 0, 2, 0, true, false)
        .unwrap();

    let (res, _) = sheet.eval("=Sales[[#Totals],[Amount]]", None).unwrap();
    assert!(matches!(res, ResultData::None));
}

#[test]
fn test_excel_table_headers_use_stored_table_column_name() {
    let mut sheet = Sheet::new(SheetInit {
        name: Some("Sheet1".to_string()),
        rows: 3,
        cols: 1,
        ..Default::default()
    });
    sheet.set_cell_src(0, 0, "Amount".to_string());
    sheet.set_cell_src(1, 0, "10".to_string());
    sheet.set_cell_src(2, 0, "20".to_string());
    sheet.commit(None).unwrap();

    sheet
        .add_table("Sales".to_string(), 0, 0, 2, 0, true, false)
        .unwrap();

    let (res, _) = sheet.eval("=Sales[[#Headers],[Amount]]", None).unwrap();
    assert_eq!(get_string_val(&res), Some("Amount".to_string()));
}

#[test]
fn test_excel_table_cross_sheet_reference() {
    let mut sheet2 = Sheet::new(SheetInit {
        name: Some("Sheet2".to_string()),
        rows: 3,
        cols: 1,
        ..Default::default()
    });
    sheet2.set_cell_src(0, 0, "Amount".to_string());
    sheet2.set_cell_src(1, 0, "100".to_string());
    sheet2.set_cell_src(2, 0, "200".to_string());
    sheet2.commit(None).unwrap();
    sheet2
        .add_table("Revenue".to_string(), 0, 0, 2, 0, true, false)
        .unwrap();

    let sheet1 = Sheet::new(SheetInit {
        name: Some("Sheet1".to_string()),
        rows: 1,
        cols: 1,
        ..Default::default()
    });

    let mut context = Context::default();
    context.sheets.insert("Sheet2".to_string(), &sheet2);

    let (res, _) = sheet1
        .eval("=SUM(Revenue[Amount])", Some(&context))
        .unwrap();
    assert_eq!(get_float_val(&res), Some(300.0));
}

#[test]
fn test_excel_table_structured_reference_survives_commit() {
    // Regression test: `commit()` re-derives each formula's evaluated
    // source text via `compile_formula`/`serialize_formula` on every run
    // (see Sheet::commit), not just the first time. That recompilation
    // step must recognize a real ExcelTable's columns -- if it instead
    // tried to resolve them the legacy way (as plain DataColumn names, all
    // of which are blank for a table's columns), it would rewrite the
    // formula's column name to a bogus placeholder and break it, even
    // though a direct `sheet.eval()` call (bypassing compile_formula) would
    // have worked fine.
    let mut sheet = Sheet::new(SheetInit {
        name: Some("Sheet1".to_string()),
        rows: 4,
        cols: 3,
        ..Default::default()
    });
    sheet.set_cell_src(0, 0, "Name".to_string());
    sheet.set_cell_src(0, 1, "Amount".to_string());
    sheet.set_cell_src(1, 0, "Widget".to_string());
    sheet.set_cell_src(1, 1, "10".to_string());
    sheet.set_cell_src(2, 0, "Gadget".to_string());
    sheet.set_cell_src(2, 1, "20".to_string());
    sheet.commit(None).unwrap();

    sheet
        .add_table("Sales".to_string(), 0, 0, 2, 1, true, false)
        .unwrap();

    sheet.set_cell_src(0, 2, "=SUM(Sales[Amount])".to_string());
    sheet.set_cell_src(1, 2, "=Sales[[#Headers],[Amount]]".to_string());
    sheet.commit(None).unwrap();

    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(0, 2))),
        Some(30.0)
    );
    assert_eq!(
        get_string_val(&sheet.get_result_data(&CellRef::new(1, 2))),
        Some("Amount".to_string())
    );

    // Committing again (as e.g. re-evaluating an already-saved workbook
    // would) must keep working -- this is what actually exercises the
    // repeated compile_formula/serialize_formula round-trip.
    sheet.mark_all_dirty();
    sheet.commit(None).unwrap();
    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(0, 2))),
        Some(30.0)
    );
}

#[test]
fn test_excel_table_column_reference_dependency_is_row_scoped_not_whole_column() {
    // A structured column reference must depend on only the table's own
    // data rows (like a bounded range reference, e.g. A1:A100), not the
    // whole sheet column. Verified against real Excel: placing a summary
    // formula like `=SUM(Inventory[Price])` in the same column as the
    // table but outside its rows is NOT circular there, and changing it
    // doesn't need a whole-column dependency to invalidate correctly --
    // only per-row dependencies on the table's own rows do.
    let mut sheet = Sheet::new(SheetInit {
        name: Some("Sheet1".to_string()),
        rows: 6,
        cols: 2,
        ..Default::default()
    });
    sheet.set_cell_src(0, 0, "Item".to_string());
    sheet.set_cell_src(0, 1, "Price".to_string());
    sheet.set_cell_src(1, 0, "Widget".to_string());
    sheet.set_cell_src(1, 1, "9.99".to_string());
    sheet.set_cell_src(2, 0, "Gadget".to_string());
    sheet.set_cell_src(2, 1, "19.99".to_string());
    sheet.set_cell_src(3, 0, "Gizmo".to_string());
    sheet.set_cell_src(3, 1, "4.5".to_string());
    sheet.commit(None).unwrap();

    sheet
        .add_table("Inventory".to_string(), 0, 0, 3, 1, true, false)
        .unwrap();

    // Row 5 (0-based): same "Price" column (1) as the table, but well
    // outside the table's own rows (0..=3).
    sheet.set_cell_src(5, 1, "=SUM(Inventory[Price])".to_string());
    sheet.commit(None).unwrap();

    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(5, 1))),
        Some(34.48)
    );

    // No whole-column dependency should exist for column 1 -- only
    // per-row dependencies on the table's own data rows (1..=3; row 0 is
    // the header, excluded). If this ever regresses to a whole-column
    // dependency, the summary formula would depend on its own cell (since
    // it also lives in column 1) and false-positive as circular, which
    // real Excel does not do.
    assert!(
        !sheet.dependencies.contains_key(&Dependency::LocalColumn(1)),
        "structured table reference must not register a whole-column dependency"
    );
    for r in 1..=3 {
        assert!(
            sheet
                .dependencies
                .contains_key(&Dependency::Local(CellRef::new(r, 1))),
            "expected a per-row dependency on row {r}"
        );
    }

    // Changing an in-table cell must still correctly invalidate and
    // recompute the summary formula.
    sheet.set_cell_src(1, 1, "100".to_string());
    sheet.commit(None).unwrap();
    assert_eq!(
        get_float_val(&sheet.get_result_data(&CellRef::new(5, 1))),
        Some(124.49)
    );
}

/// #26 flags an absence of any circular-reference testing: `commit`'s
/// dirty-cell BFS is bounded by `max_ops`, not real cycle detection (unlike
/// real Excel, which shows a warning and substitutes 0 by default, or
/// converges under user-configured iterative calculation -- neither
/// modeled here). This is a documented, intentional shortcut, not a bug to
/// fix, but nothing previously confirmed it actually holds: these tests
/// lock in the safety property that matters -- a cycle terminates quickly
/// with a finite result rather than hanging or panicking -- so a future
/// change to the dirty-queue/max_ops logic can't silently regress that
/// into an infinite loop.
#[test]
fn test_self_referencing_formula_terminates_without_hanging() {
    let mut sheet = Sheet::new(SheetInit {
        id: None,
        name: Some("s".to_string()),
        rows: 1,
        cols: 1,
    });
    sheet.set_cell_src(0, 0, "=A1+1".to_string());

    let start = std::time::Instant::now();
    let result = sheet.commit(None);
    assert!(
        start.elapsed().as_secs() < 5,
        "self-reference must not hang"
    );
    assert!(result.is_ok());

    match sheet.get_result_data(&CellRef::new(0, 0)) {
        ResultData::Float(f) => assert!(f.is_finite()),
        ResultData::Integer(_) => {}
        other => panic!("expected a finite numeric result, got {other:?}"),
    }
}

#[test]
fn test_multi_cell_circular_chain_terminates_without_hanging() {
    let mut sheet = Sheet::new(SheetInit {
        id: None,
        name: Some("s".to_string()),
        rows: 1,
        cols: 3,
    });
    // A1 -> B1 -> C1 -> A1
    sheet.set_cell_src(0, 0, "=B1+1".to_string());
    sheet.set_cell_src(0, 1, "=C1+1".to_string());
    sheet.set_cell_src(0, 2, "=A1+1".to_string());

    let start = std::time::Instant::now();
    let result = sheet.commit(None);
    assert!(
        start.elapsed().as_secs() < 5,
        "a 3-cell cycle must not hang"
    );
    assert!(result.is_ok());

    for col in 0..3 {
        match sheet.get_result_data(&CellRef::new(0, col)) {
            ResultData::Float(f) => assert!(f.is_finite()),
            ResultData::Integer(_) => {}
            other => panic!("expected a finite numeric result, got {other:?}"),
        }
    }
}

/// Regression for a stack-overflow/hang found via visi-core/fuzz's
/// formula_eval target within its first extended run (#26 -- zero
/// formula-level Rust fuzz coverage existed before that target). A bare,
/// unaggregated range reference that includes the very cell its own
/// formula lives in (e.g. `=C:P` sitting in column K, inside the C..P
/// span) reads its own currently-stored value back as one element of the
/// range on every recompute; since that stored value *is* the previous
/// recompute's `ResultData::List`, each pass nested a List one level
/// deeper inside itself. Per-op cost grew visibly superlinearly (measured
/// via temporary instrumentation: ~350ms for ops 1-200, ~3.6s for ops
/// 1000-1200) and the process stack-overflowed via recursive Clone/Drop
/// around op ~1300 -- well before commit()'s own max_ops=10000 circuit
/// breaker ever got a chance to trip, since each op was individually
/// getting more expensive rather than the op *count* running away.
#[test]
fn test_self_referential_whole_column_range_does_not_grow_unbounded() {
    let mut sheet = Sheet::new(SheetInit {
        id: None,
        name: Some("Sheet1".to_string()),
        rows: 20,
        cols: 20,
    });
    sheet.set_cell_src(0, 0, "1".to_string());
    sheet.set_cell_src(1, 0, "2".to_string());
    sheet.set_cell_src(0, 1, "3.5".to_string());

    // Column K (index 10) is squarely inside the C..P (2..15) span this
    // formula itself references.
    sheet.set_cell_src(10, 10, "=C:P".to_string());

    let start = std::time::Instant::now();
    let result = sheet.commit(None);
    assert!(
        start.elapsed().as_secs() < 5,
        "self-referential whole-column range must not hang"
    );
    assert!(result.is_ok());

    // The formula's own cell must not have grown into a deeply-nested
    // List -- it should still just be the (blank-for-self, per the fix)
    // range's List, one level deep.
    match sheet.get_result_data(&CellRef::new(10, 10)) {
        ResultData::List(items) => {
            assert!(
                items
                    .iter()
                    .all(|item| !matches!(item, ResultData::List(_))),
                "range result must not contain a nested List"
            );
        }
        other => panic!("expected a flat List result, got {other:?}"),
    }
}

/// A date literal is a *number* that displays as a date, exactly as in Excel:
/// the value is the serial, so arithmetic and the numeric functions see it,
/// and the notation survives only as the cell's number format.
#[test]
fn test_date_literal_becomes_a_serial_with_a_number_format() {
    let mut sheet = create_sheet(&[["6/22/26", "=ISNUMBER(A1)"], ["22-Jun-2026", "=YEAR(A2)"]]);
    sheet.commit(None).unwrap();

    assert!(matches!(
        sheet.get_result_data(&CellRef::new(0, 0)),
        ResultData::Float(f) if f == 46195.0
    ));
    assert!(matches!(
        sheet.get_result_data(&CellRef::new(0, 1)),
        ResultData::Boolean(true)
    ));
    assert_eq!(
        sheet.to_f64(&sheet.get_result_data(&CellRef::new(1, 1))),
        Some(2026.0)
    );

    // ... and displays back in the notation it was typed in.
    assert_eq!(sheet.get_display_string(&CellRef::new(0, 0)), "6/22/26");
    assert_eq!(sheet.get_display_string(&CellRef::new(1, 0)), "22-Jun-2026");
    assert_eq!(
        sheet
            .get_cell_style(0, 0)
            .and_then(|s| s.num_format.clone()),
        Some("m/d/yy".to_string())
    );
}

/// Quoting is the escape hatch for text that happens to look like a date.
#[test]
fn test_quoted_date_text_stays_text() {
    let mut sheet = create_sheet(&[["\"22-Jun\"", "=ISTEXT(A1)"]]);
    sheet.commit(None).unwrap();

    assert!(matches!(
        sheet.get_result_data(&CellRef::new(0, 0)),
        ResultData::String(ref s) if s == "22-Jun"
    ));
    assert!(matches!(
        sheet.get_result_data(&CellRef::new(0, 1)),
        ResultData::Boolean(true)
    ));
    assert_eq!(sheet.get_display_string(&CellRef::new(0, 0)), "22-Jun");
    assert!(sheet.get_cell_style(0, 0).is_none());
}

/// A formula reading exactly one date cell inherits its notation, so `=A1+1`
/// on a date shows the next day rather than a bare serial. Formulas that read
/// two cells or a range do not -- a difference of dates is a count of days,
/// and a sum of dates is nothing at all.
#[test]
fn test_date_format_inheritance_is_limited_to_single_cell_formulas() {
    let mut sheet = create_sheet(&[
        ["6/22/26", "=A1+1", "=A1-A2"],
        ["22-Jun-2026", "=A2+10", "=SUM(A1:A2)"],
    ]);
    sheet.commit(None).unwrap();

    assert_eq!(sheet.get_display_string(&CellRef::new(0, 1)), "6/23/26");
    assert_eq!(sheet.get_display_string(&CellRef::new(1, 1)), "2-Jul-2026");
    // Two cell references: a day count, left as a number.
    assert_eq!(sheet.get_display_string(&CellRef::new(0, 2)), "0");
    // A range reference: also left alone.
    assert_eq!(sheet.get_display_string(&CellRef::new(1, 2)), "92390");
}

/// A number format that isn't a date format leaves rendering alone -- visi
/// does not implement Excel's numeric format codes, and mangling a `0.00`
/// cell into a date would be worse than ignoring it.
#[test]
fn test_non_date_number_format_does_not_affect_display() {
    let mut sheet = create_sheet(&[["1234.5"]]);
    sheet.commit(None).unwrap();
    sheet.update_cell_style(0, 0, |style| {
        style.num_format = Some("0.00".to_string());
    });
    assert_eq!(sheet.get_display_string(&CellRef::new(0, 0)), "1234.5");
}

/// Inheritance follows the operator, not the number of cells read: `=YEAR(A1)`
/// touches exactly one date cell and returns a year, which must stay a plain
/// number rather than being rendered as a 1905 date.
#[test]
fn test_date_component_functions_do_not_inherit_the_date_format() {
    let mut sheet = create_sheet(&[["22-Jun-2026", "=YEAR(A1)", "=MONTH(A1)", "=A1*2"]]);
    sheet.commit(None).unwrap();

    assert_eq!(sheet.get_display_string(&CellRef::new(0, 1)), "2026");
    assert_eq!(sheet.get_display_string(&CellRef::new(0, 2)), "6");
    assert_eq!(sheet.get_display_string(&CellRef::new(0, 3)), "92390");
}

/// `src`, `data`, `compiled_src` and `styles` must all stay the same length.
/// Asserts it across every column of a sheet.
fn assert_columns_aligned(sheet: &Sheet, context: &str) {
    for (idx, col) in sheet.columns.iter().enumerate() {
        let len = col.len();
        assert_eq!(
            col.data.len(),
            len,
            "{context}: column {idx} data desynced from src"
        );
        assert_eq!(
            col.compiled_src.len(),
            len,
            "{context}: column {idx} compiled_src desynced from src"
        );
        assert_eq!(
            col.styles.len(),
            len,
            "{context}: column {idx} styles desynced from src"
        );
    }
}

fn bold() -> crate::core::CellStyle {
    crate::core::CellStyle {
        bold: Some(true),
        ..Default::default()
    }
}

/// `extend` used to grow `src`/`data`/`compiled_src` and leave `styles`
/// behind, which made the new row unstylable: `set_cell_style` computed a
/// row count from `src`, found nothing to grow, then silently dropped the
/// style because the index was past `styles`' end.
#[test]
fn test_extend_down_keeps_styles_aligned() {
    let mut sheet = Sheet::new(SheetInit {
        rows: 3,
        cols: 2,
        ..Default::default()
    });
    sheet.extend(Direction::Down);
    assert_columns_aligned(&sheet, "after extend(Down)");

    let last = sheet.row_count() - 1;
    sheet.set_cell_style(last, 0, bold());
    assert_eq!(
        sheet.get_cell_style(last, 0).and_then(|s| s.bold),
        Some(true),
        "style set on the row extend() added was dropped"
    );
}

#[test]
fn test_extend_up_keeps_styles_aligned() {
    let mut sheet = Sheet::new(SheetInit {
        rows: 3,
        cols: 2,
        ..Default::default()
    });
    sheet.set_cell_style(0, 0, bold());
    sheet.extend(Direction::Up);
    assert_columns_aligned(&sheet, "after extend(Up)");

    // The styled row moved down one; the inserted row is unstyled.
    assert_eq!(sheet.get_cell_style(0, 0).and_then(|s| s.bold), None);
    assert_eq!(
        sheet.get_cell_style(1, 0).and_then(|s| s.bold),
        Some(true),
        "extend(Up) did not shift styles down with their rows"
    );
}

/// The multi-row branch of `delete` drained `src`/`data`/`compiled_src` but
/// not `styles`, leaving every style below the deleted range attached to the
/// wrong row.
#[test]
fn test_delete_rows_keeps_styles_aligned() {
    let mut sheet = Sheet::new(SheetInit {
        rows: 4,
        cols: 1,
        ..Default::default()
    });
    sheet.set_cell_style(3, 0, bold());

    sheet.delete(
        TextCellRef {
            row: 0,
            col: 0,
            char_offset: 0,
        },
        TextCellRef {
            row: 1,
            col: 0,
            char_offset: 0,
        },
    );
    assert_columns_aligned(&sheet, "after delete of rows 0..=1");

    // Two rows went, so the styled row 3 is now row 1.
    assert_eq!(
        sheet.get_cell_style(1, 0).and_then(|s| s.bold),
        Some(true),
        "styles did not shift up with their rows"
    );
}

#[test]
fn test_insert_and_delete_row_keep_columns_aligned() {
    let mut sheet = Sheet::new(SheetInit {
        rows: 3,
        cols: 2,
        ..Default::default()
    });
    sheet.insert_row(1);
    assert_columns_aligned(&sheet, "after insert_row(1)");
    sheet.insert_row(99); // past the end: appends
    assert_columns_aligned(&sheet, "after appending insert_row");
    sheet.delete_row(0);
    assert_columns_aligned(&sheet, "after delete_row(0)");
    sheet.delete_row(99); // past the end: ignored
    assert_columns_aligned(&sheet, "after out-of-range delete_row");
}

#[test]
fn test_ensure_capacity_keeps_columns_aligned() {
    let mut sheet = Sheet::new(SheetInit {
        rows: 2,
        cols: 1,
        ..Default::default()
    });
    sheet.set_cell_style(5, 3, bold());
    assert_columns_aligned(&sheet, "after ensure_capacity grew the sheet");
    assert_eq!(sheet.get_cell_style(5, 3).and_then(|s| s.bold), Some(true));
}

/// `styles` is `#[serde(default)]`, so a workbook serialized without it
/// deserializes with an empty one. The load path has to size it back up.
#[test]
fn test_setup_after_deserialization_restores_styles_length() {
    let mut sheet = Sheet::new(SheetInit {
        rows: 3,
        cols: 2,
        ..Default::default()
    });
    // Simulate a payload that carried no styles at all.
    for col in &mut sheet.columns {
        col.styles = Vec::new().into();
    }
    sheet.setup_after_deserialization();
    assert_columns_aligned(&sheet, "after setup_after_deserialization");
}