visi-core 0.1.1

use super::*;

#[test]
fn test_trig_and_hyperbolic_functions() {
    let grid = [[
        "=DEGREES(PI())",
        "=RADIANS(180)",
        "=SINH(0)",
        "=COSH(0)",
        "=TANH(0)",
        "=SQRTPI(4)",
    ]];
    let mut sheet = create_sheet(&grid);
    sheet.commit(None).unwrap();

    let r1 = sheet.get_result_data(&CellRef::new(0, 0));
    assert!(matches!(r1, ResultData::Float(v) if (v - 180.0).abs() < 1e-6));

    let r2 = sheet.get_result_data(&CellRef::new(0, 1));
    assert!(matches!(r2, ResultData::Float(v) if (v - std::f64::consts::PI).abs() < 1e-6));

    let r3 = sheet.get_result_data(&CellRef::new(0, 2));
    assert!(matches!(r3, ResultData::Float(v) if v.abs() < 1e-6));

    let r4 = sheet.get_result_data(&CellRef::new(0, 3));
    assert!(matches!(r4, ResultData::Float(v) if (v - 1.0).abs() < 1e-6));

    let r5 = sheet.get_result_data(&CellRef::new(0, 4));
    assert!(matches!(r5, ResultData::Float(v) if v.abs() < 1e-6));

    let r6 = sheet.get_result_data(&CellRef::new(0, 5));
    assert!(
        matches!(r6, ResultData::Float(v) if (v - (4.0 * std::f64::consts::PI).sqrt()).abs() < 1e-6)
    );
}

#[test]
fn test_rounding_and_integers() {
    let grid = [[
        "=EVEN(3)",
        "=ODD(4)",
        "=MROUND(10, 3)",
        "=QUOTIENT(10, 3)",
        "=SIGN(-5)",
        "=TRUNC(3.14159, 2)",
    ]];
    let mut sheet = create_sheet(&grid);
    sheet.commit(None).unwrap();

    let r1 = sheet.get_result_data(&CellRef::new(0, 0));
    assert!(matches!(r1, ResultData::Float(v) if (v - 4.0).abs() < 1e-6));

    let r2 = sheet.get_result_data(&CellRef::new(0, 1));
    assert!(matches!(r2, ResultData::Float(v) if (v - 5.0).abs() < 1e-6));

    let r3 = sheet.get_result_data(&CellRef::new(0, 2));
    assert!(matches!(r3, ResultData::Float(v) if (v - 9.0).abs() < 1e-6));

    let r4 = sheet.get_result_data(&CellRef::new(0, 3));
    assert!(matches!(r4, ResultData::Float(v) if (v - 3.0).abs() < 1e-6));

    let r5 = sheet.get_result_data(&CellRef::new(0, 4));
    assert!(matches!(r5, ResultData::Float(v) if (v + 1.0).abs() < 1e-6));

    let r6 = sheet.get_result_data(&CellRef::new(0, 5));
    #[allow(clippy::approx_constant)]
    let expected = 3.14; // TRUNC(3.14159, 2), not an approximation of PI
    assert!(matches!(r6, ResultData::Float(v) if (v - expected).abs() < 1e-6));
}

#[test]
fn test_base_conversions_and_roman() {
    let grid = [[
        "=BASE(255, 16)",
        "=DECIMAL(\"FF\", 16)",
        "=ARABIC(\"MCMXCIX\")",
        "=ROMAN(1999)",
    ]];
    let mut sheet = create_sheet(&grid);
    sheet.commit(None).unwrap();

    let r1 = sheet.get_result_data(&CellRef::new(0, 0));
    assert!(matches!(r1, ResultData::String(ref s) if s == "FF"));

    let r2 = sheet.get_result_data(&CellRef::new(0, 1));
    assert!(matches!(r2, ResultData::Float(v) if (v - 255.0).abs() < 1e-6));

    let r3 = sheet.get_result_data(&CellRef::new(0, 2));
    assert!(matches!(r3, ResultData::Float(v) if (v - 1999.0).abs() < 1e-6));

    let r4 = sheet.get_result_data(&CellRef::new(0, 3));
    assert!(matches!(r4, ResultData::String(ref s) if s == "MCMXCIX"));
}

#[test]
fn test_combinatorics_and_factors() {
    let grid = [[
        "=COMBIN(5, 2)",
        "=COMBINA(5, 2)",
        "=FACT(5)",
        "=FACTDOUBLE(5)",
        "=GCD(12, 18, 24)",
        "=LCM(4, 6)",
    ]];
    let mut sheet = create_sheet(&grid);
    sheet.commit(None).unwrap();

    let r1 = sheet.get_result_data(&CellRef::new(0, 0));
    assert!(matches!(r1, ResultData::Float(v) if (v - 10.0).abs() < 1e-6));

    let r2 = sheet.get_result_data(&CellRef::new(0, 1));
    assert!(matches!(r2, ResultData::Float(v) if (v - 15.0).abs() < 1e-6));

    let r3 = sheet.get_result_data(&CellRef::new(0, 2));
    assert!(matches!(r3, ResultData::Float(v) if (v - 120.0).abs() < 1e-6));

    let r4 = sheet.get_result_data(&CellRef::new(0, 3));
    assert!(matches!(r4, ResultData::Float(v) if (v - 15.0).abs() < 1e-6));

    let r5 = sheet.get_result_data(&CellRef::new(0, 4));
    assert!(matches!(r5, ResultData::Float(v) if (v - 6.0).abs() < 1e-6));

    let r6 = sheet.get_result_data(&CellRef::new(0, 5));
    assert!(matches!(r6, ResultData::Float(v) if (v - 12.0).abs() < 1e-6));
}

#[test]
fn test_array_and_matrix_functions() {
    let grid = [
        ["1", "2", "0", "0"],
        ["3", "4", "0", "0"],
        [
            "=SUMPRODUCT(A1:B1, A2:B2)",
            "=SUMSQ(A1:B2)",
            "=POWER(2, 10)",
            "=LOG(1000, 10)",
        ],
    ];
    let mut sheet = create_sheet(&grid);
    sheet.commit(None).unwrap();

    // SUMPRODUCT([1, 2], [3, 4]) = 1*3 + 2*4 = 11
    let r1 = sheet.get_result_data(&CellRef::new(2, 0));
    assert!(matches!(r1, ResultData::Float(v) if (v - 11.0).abs() < 1e-6));

    // SUMSQ(1, 2, 3, 4) = 1 + 4 + 9 + 16 = 30
    let r2 = sheet.get_result_data(&CellRef::new(2, 1));
    assert!(matches!(r2, ResultData::Float(v) if (v - 30.0).abs() < 1e-6));

    // POWER(2, 10) = 1024
    let r3 = sheet.get_result_data(&CellRef::new(2, 2));
    assert!(matches!(r3, ResultData::Float(v) if (v - 1024.0).abs() < 1e-6));

    // LOG(1000, 10) = 3
    let r4 = sheet.get_result_data(&CellRef::new(2, 3));
    assert!(matches!(r4, ResultData::Float(v) if (v - 3.0).abs() < 1e-6));
}

#[test]
fn test_ceiling_floor_honor_significance_argument() {
    // Legacy 2-arg CEILING/FLOOR were completely ignoring their second
    // (significance) argument and just calling f64::ceil()/floor() --
    // e.g. CEILING(63.55, 5) returned 64 (plain ceil), not a multiple of
    // 5 at all, when it should round up to the nearest multiple of 5
    // (65). Found via differential fuzzing against real Excel.
    let grid = [["=CEILING(63.55, 5)", "=FLOOR(16.34, 10)"]];
    let mut sheet = create_sheet(&grid);
    sheet.commit(None).unwrap();

    let r1 = sheet.get_result_data(&CellRef::new(0, 0));
    assert!(matches!(r1, ResultData::Float(v) if (v - 65.0).abs() < 1e-9));

    let r2 = sheet.get_result_data(&CellRef::new(0, 1));
    assert!(matches!(r2, ResultData::Float(v) if (v - 10.0).abs() < 1e-9));
}

#[test]
fn test_gcd_lcm_error_on_non_numeric_argument() {
    // Unlike SUM/AVERAGE-style aggregates, real Excel's GCD/LCM don't
    // silently ignore a non-numeric cell in a range argument -- they
    // return #VALUE!. visi used to flatten with the same lenient logic
    // SUM uses, silently dropping the text cell and computing GCD/LCM
    // over whatever numbers were left.
    let grid = [["\"not a number\"", "6", "=GCD(A1:B1)", "=LCM(A1:B1)"]];
    let mut sheet = create_sheet(&grid);
    sheet.commit(None).unwrap();

    let r1 = sheet.get_result_data(&CellRef::new(0, 2));
    assert!(matches!(r1, ResultData::Error(ref e) if e == "#VALUE!"));

    let r2 = sheet.get_result_data(&CellRef::new(0, 3));
    assert!(matches!(r2, ResultData::Error(ref e) if e == "#VALUE!"));
}

#[test]
fn test_mmult_error_on_non_numeric_cell() {
    // MMULT used to coerce a non-numeric cell in either operand to 0
    // (via to_f64(..).unwrap_or(0.0)) instead of propagating #VALUE! the
    // way real Excel does.
    let grid = [
        ["1", "2"],
        ["3", "4"],
        ["\"x\"", "5"],
        ["6", "7"],
        ["=INDEX(MMULT(A1:B2, A3:B4), 1, 1)", ""],
    ];
    let mut sheet = create_sheet(&grid);
    sheet.commit(None).unwrap();

    let r = sheet.get_result_data(&CellRef::new(4, 0));
    assert!(matches!(r, ResultData::Error(ref e) if e == "#VALUE!"));
}

#[test]
fn test_complex_number_trig_exp_log_functions_are_actually_computed() {
    // IMCOS/IMCOSH/IMCOT/IMCSC/IMCSCH/IMEXP/IMLN/IMLOG10/IMLOG2/IMPOWER/
    // IMSEC/IMSECH/IMSIN/IMSINH/IMSQRT/IMTAN were all a single stub arm
    // that just echoed the input string back unchanged (`Ok(ResultData::
    // String(t))`), so e.g. IMCOS("0") returned the text "0" by
    // coincidence, not because it computed cos(0)=1. Found via
    // differential fuzzing (every one of these functions mismatched
    // against real Excel on every run).
    let grid = [[
        "=IMCOS(0)",
        "=IMSIN(0)",
        "=IMTAN(0)",
        "=IMEXP(0)",
        "=IMLN(1)",
        "=IMSQRT(-1)",
        "=IMPOWER(2, 2)",
    ]];
    let mut sheet = create_sheet(&grid);
    sheet.commit(None).unwrap();

    let r1 = sheet.get_result_data(&CellRef::new(0, 0));
    assert!(
        matches!(r1, ResultData::String(ref s) if s == "1"),
        "IMCOS(0) = {r1:?}"
    );

    let r2 = sheet.get_result_data(&CellRef::new(0, 1));
    assert!(
        matches!(r2, ResultData::String(ref s) if s == "0"),
        "IMSIN(0) = {r2:?}"
    );

    let r3 = sheet.get_result_data(&CellRef::new(0, 2));
    assert!(
        matches!(r3, ResultData::String(ref s) if s == "0"),
        "IMTAN(0) = {r3:?}"
    );

    let r4 = sheet.get_result_data(&CellRef::new(0, 3));
    assert!(
        matches!(r4, ResultData::String(ref s) if s == "1"),
        "IMEXP(0) = {r4:?}"
    );

    let r5 = sheet.get_result_data(&CellRef::new(0, 4));
    assert!(
        matches!(r5, ResultData::String(ref s) if s == "0"),
        "IMLN(1) = {r5:?}"
    );

    // sqrt(-1) = i. Real Excel reports this as "6.12323399573677E-17+i",
    // not a clean "i": the polar form's angle is f64's rounded pi/2, and
    // cos(pi/2) in f64 is ~6.12e-17 rather than exactly 0. visi matches
    // that verbatim on purpose -- an earlier version snapped the
    // negligible component to zero, which reads "more correct" but
    // disagrees with the thing this engine is trying to be compatible
    // with (confirmed by probing real Excel directly).
    let r6 = sheet.get_result_data(&CellRef::new(0, 5));
    assert!(
        matches!(r6, ResultData::String(ref s) if s == "6.12323399573677E-17+i"),
        "IMSQRT(-1) = {r6:?}"
    );

    // 2^2 = 4 (a real number stays a plain real, no spurious "+0i")
    let r7 = sheet.get_result_data(&CellRef::new(0, 6));
    assert!(
        matches!(r7, ResultData::String(ref s) if s == "4"),
        "IMPOWER(2,2) = {r7:?}"
    );
}

#[test]
fn test_complex_number_formatting_uses_excel_precision_not_raw_f64() {
    // format_complex used to interpolate the real/imaginary f64 parts
    // directly (`format!("{}", c.re)`), which prints full f64 precision
    // (e.g. 0.1 + 0.2 as raw f64 addition prints "0.30000000000000004")
    // instead of Excel's 15-significant-digit display rules. Every IM*
    // result with a non-exact float component mismatched real Excel by a
    // handful of ULPs in the last few digits.
    let grid = [["=IMSUM(0.1, 0.2)"]];
    let mut sheet = create_sheet(&grid);
    sheet.commit(None).unwrap();

    let r = sheet.get_result_data(&CellRef::new(0, 0));
    assert!(
        matches!(r, ResultData::String(ref s) if s == "0.3"),
        "IMSUM(0.1,0.2) = {r:?}"
    );
}

#[test]
fn test_complex_tan_cot_stay_precise_for_large_imaginary_parts() {
    // IMTAN/IMCOT used to be computed as the complex quotient
    // sin(z)/cos(z), which loses most of its significant digits once
    // |Im z| grows: both operands pick up components of order
    // cosh(Im z) (already ~550 by Im z = 7) and the result's real part is
    // the tiny residual left after those large nearly-equal terms cancel.
    // Against real Excel that showed up as agreement to only ~10
    // significant digits. Now computed from the double-angle identities,
    // where every intermediate is the same magnitude as the result.
    //
    // Reference values are verbatim real-Excel output. The tolerance is
    // per-component relative 1e-14 rather than string equality: the last
    // displayed digit can still differ by one ulp depending on the exact
    // order f64 operations happen in, which is not something either
    // engine can control, while the bug this guards against was four
    // orders of magnitude larger than that.
    fn parts(s: &str) -> (f64, f64) {
        let c = crate::core::engineering::parse_complex(s).expect("parses as complex");
        (c.re, c.im)
    }
    let grid = [[
        "=IMTAN(\"-1-7i\")",
        "=IMTAN(\"2+6i\")",
        "=IMTAN(\"9-2i\")",
        "=IMCOT(\"-7-7j\")",
        "=IMCOT(\"9-2j\")",
        "=IMCOT(\"-6+8i\")",
    ]];
    let mut sheet = create_sheet(&grid);
    sheet.commit(None).unwrap();

    for (col, expected) in [
        (0, "-1.51221489579179E-06-1.00000069207519i"),
        (1, "-9.29998518085996E-06+1.00000803223943i"),
        (2, "-0.0268511331123278-0.975735876254188i"),
        (3, "-0.0000016474373058602+1.00000022740052j"),
        (4, "-0.0281818376780689+1.02409198026131j"),
        (5, "1.20766677090395E-07-1.00000018992652i"),
    ] {
        let got = sheet.get_result_data(&CellRef::new(0, col));
        let ResultData::String(ref got_s) = got else {
            panic!("col {col}: expected a complex string, got {got:?}");
        };
        let (gr, gi) = parts(got_s);
        let (er, ei) = parts(expected);
        let close = |a: f64, b: f64| (a - b).abs() <= 1e-14 * b.abs().max(1e-300);
        assert!(
            close(gr, er) && close(gi, ei),
            "col {col}: got {got_s}, want {expected}"
        );
        // The i/j suffix must survive verbatim.
        assert_eq!(
            got_s.chars().last(),
            expected.chars().last(),
            "col {col}: suffix changed ({got_s} vs {expected})"
        );
    }
}

#[test]
fn test_roman_concise_forms_match_excel() {
    // ROMAN's `form` argument was ignored entirely, so all five forms
    // rendered as classic notation. Every expectation below is verbatim
    // real-Excel output.
    for (n, form, expected) in [
        (990.0, 0.0, "CMXC"),
        (990.0, 1.0, "LMXL"),
        (990.0, 2.0, "XM"),
        (990.0, 3.0, "XM"),
        (990.0, 4.0, "XM"),
        (1481.0, 0.0, "MCDLXXXI"),
        (1481.0, 1.0, "MLDXXXI"),
        (1481.0, 4.0, "MLDXXXI"),
        (1999.0, 0.0, "MCMXCIX"),
        (1999.0, 1.0, "MLMVLIV"),
        (1999.0, 2.0, "MXMIX"),
        (1999.0, 3.0, "MVMIV"),
        (1999.0, 4.0, "MIM"),
        (499.0, 0.0, "CDXCIX"),
        (499.0, 1.0, "LDVLIV"),
        (499.0, 2.0, "XDIX"),
        (499.0, 3.0, "VDIV"),
        (499.0, 4.0, "ID"),
        (45.0, 0.0, "XLV"),
        (45.0, 1.0, "VL"),
    ] {
        let got = crate::core::math_trig::roman(n, Some(form));
        assert_eq!(
            got,
            Ok(expected.to_string()),
            "ROMAN({n}, {form}) = {got:?}, want {expected:?}"
        );
    }
}

fn eval_one(source: &str) -> ResultData {
    let sheet = Sheet::new(SheetInit::default());
    sheet.eval(source, None).unwrap().0
}

fn num(source: &str) -> f64 {
    match eval_one(source) {
        ResultData::Float(f) => f,
        ResultData::Integer(i) => i as f64,
        other => panic!("expected a number for {source}, got {other:?}"),
    }
}

#[test]
fn test_days360_us_method_pulls_february_month_ends_to_the_30th() {
    // Serial 40602 is 2011-02-28, a February month-end; 42543 is
    // 2016-06-24. The US (NASD) method moves that start day to the 30th
    // and so counts two days fewer than the European method. Real Excel:
    //   DAYS360(40602, 42543, FALSE) = 1912   (also the default)
    //   DAYS360(40602, 42543, TRUE)  = 1914
    // Before the February rule was implemented both spellings returned
    // 1914, because the two methods only diverge on a month-end February.
    assert_eq!(num("=DAYS360(40602, 42543, FALSE)"), 1912.0);
    assert_eq!(num("=DAYS360(40602, 42543)"), 1912.0);
    assert_eq!(num("=DAYS360(40602, 42543, TRUE)"), 1914.0);
}

#[test]
fn test_supplied_blank_argument_is_zero_not_the_default() {
    // An *omitted* optional argument takes its default; an argument that
    // is supplied but blank is 0. Real Excel draws the line sharply:
    //   LOG(100)        = 2         base defaults to 10
    //   LOG(100, blank) = #NUM!     base is 0
    //   LOG(1, blank)   = #NUM!     still #NUM!, not 0
    //   MROUND(10, blank) = 0
    // Z90 is empty in a fresh sheet.
    assert_eq!(num("=LOG(100)"), 2.0);
    assert_eq!(num("=MROUND(10, Z90)"), 0.0);
    for src in ["=LOG(100, Z90)", "=LOG(1, Z90)"] {
        match eval_one(src) {
            ResultData::Error(e) => assert_eq!(e, "#NUM!", "for {src}"),
            other => panic!("expected #NUM! for {src}, got {other:?}"),
        }
    }
    // Base 1 stays #DIV/0! rather than #NUM! -- log(n)/log(1) divides by 0.
    match eval_one("=LOG(1, 1)") {
        ResultData::Error(e) => assert_eq!(e, "#DIV/0!"),
        other => panic!("expected #DIV/0!, got {other:?}"),
    }
}

#[test]
fn test_power_of_zero_to_the_zero_is_num_error() {
    // Excel declines to pick a value for 0^0 -- both spellings are #NUM!.
    // The `^` operator already did this; the POWER function returned 1,
    // so POWER(<blank>, <blank>) quietly evaluated to 1 and turned
    // OR(POWER(blank, blank) > 0, ...) into TRUE where Excel says #NUM!.
    for src in ["=POWER(0, 0)", "=0^0", "=POWER(Y90, Y91)"] {
        match eval_one(src) {
            ResultData::Error(e) => assert_eq!(e, "#NUM!", "for {src}"),
            other => panic!("expected #NUM! for {src}, got {other:?}"),
        }
    }
    // The neighbouring domain rules are unchanged.
    assert_eq!(num("=POWER(0, 2)"), 0.0);
    assert_eq!(num("=POWER(2, 0)"), 1.0);
    match eval_one("=POWER(0, -1)") {
        ResultData::Error(e) => assert_eq!(e, "#DIV/0!"),
        other => panic!("expected #DIV/0!, got {other:?}"),
    }
}

#[test]
fn test_sumproduct_treats_non_numeric_entries_as_zero() {
    // Real Excel: SUMPRODUCT(2, "abc") = 0 and SUMPRODUCT(A1:B1, A2:B2)
    // with a text cell in the second array = 3. Non-numeric entries count
    // as zero *and keep their slot*, so the arrays stay the same length
    // and the remaining terms still line up. Dropping them instead made
    // the first case #VALUE! -- one array of length 1 against one of
    // length 0 -- which then showed up as TYPE(...) = 16 rather than 1.
    assert_eq!(num("=SUMPRODUCT(2, \"abc\")"), 0.0);
    assert_eq!(num("=TYPE(SUMPRODUCT(2, \"abc\"))"), 1.0);

    let mut sheet = create_sheet(&[["1", "2", "=SUMPRODUCT(A1:B1, A2:B2)"], ["3", "=\"x\"", ""]]);
    sheet.commit(None).unwrap();
    match sheet.get_result_data(&CellRef::new(0, 2)) {
        ResultData::Float(f) => assert!((f - 3.0).abs() < 1e-9, "got {f}"),
        ResultData::Integer(i) => assert_eq!(i, 3),
        other => panic!("expected 3, got {other:?}"),
    }
}

#[test]
fn test_only_a_few_numeric_functions_reject_booleans() {
    // Excel's coercion is not uniform here, and the split does not follow
    // from anything about the functions -- it had to be probed one at a
    // time. These four answer #VALUE! to a boolean:
    for src in [
        "=FACTDOUBLE(TRUE)",
        "=SQRTPI(TRUE)",
        "=ERF(TRUE)",
        "=ERFC(TRUE)",
        "=BIN2DEC(TRUE)",
    ] {
        match eval_one(src) {
            ResultData::Error(e) => assert_eq!(e, "#VALUE!", "for {src}"),
            other => panic!("expected #VALUE! for {src}, got {other:?}"),
        }
    }

    // ... while their neighbours take TRUE as 1 without complaint. All of
    // these values are real Excel's.
    assert_eq!(num("=FACTDOUBLE(6)"), 48.0);
    assert_eq!(num("=FACT(TRUE)"), 1.0);
    assert_eq!(num("=SQRT(TRUE)"), 1.0);
    assert_eq!(num("=SIGN(TRUE)"), 1.0);
    assert_eq!(num("=INT(TRUE)"), 1.0);
    assert_eq!(num("=EVEN(TRUE)"), 2.0);
    assert_eq!(num("=ODD(TRUE)"), 1.0);
    assert_eq!(num("=LN(TRUE)"), 0.0);
    assert_eq!(num("=LOG10(TRUE)"), 0.0);
    assert_eq!(num("=GAMMALN(TRUE)"), 0.0);
    assert!((num("=EXP(TRUE)") - std::f64::consts::E).abs() < 1e-15);
    assert!((num("=DEGREES(TRUE)") - 57.29577951308232).abs() < 1e-13);
}

#[test]
fn test_days360_and_yearfrac_use_different_thirty_360_rules() {
    // Excel's DAYS360 function and its YEARFRAC basis 0 (the NASD
    // convention the bond functions share) genuinely disagree, which is
    // why visi implements them separately. Two rules differ:
    //
    //  - When *both* ends are February month-ends, YEARFRAC pulls the end
    //    date to the 30th and DAYS360 does not.
    //  - DAYS360's "end date on the 31st comes back to the 30th" rule
    //    tests the *adjusted* start day, YEARFRAC's the original -- so a
    //    February month-end start triggers it for one and not the other.
    //
    // Every pair below is a real-Excel value; the ones where the two
    // columns differ are exactly the cases that separate the rules.
    let cases: [(&str, &str, f64, f64); 12] = [
        ("DATE(2003,2,28)", "DATE(2005,2,28)", 718.0, 720.0),
        ("DATE(2004,2,29)", "DATE(2008,2,29)", 1439.0, 1440.0),
        ("DATE(2004,2,29)", "DATE(2005,2,28)", 358.0, 360.0),
        ("DATE(2003,2,28)", "DATE(2004,2,29)", 359.0, 360.0),
        ("DATE(2003,2,28)", "DATE(2005,3,31)", 750.0, 751.0),
        ("DATE(2003,1,31)", "DATE(2005,2,28)", 748.0, 748.0),
        ("DATE(2003,2,28)", "DATE(2005,2,27)", 717.0, 717.0),
        ("DATE(2003,3,31)", "DATE(2005,2,28)", 688.0, 688.0),
        ("DATE(2003,1,31)", "DATE(2005,3,31)", 780.0, 780.0),
        ("DATE(2003,3,15)", "DATE(2005,5,31)", 796.0, 796.0),
        ("DATE(2003,1,30)", "DATE(2005,3,31)", 780.0, 780.0),
        ("DATE(2003,4,30)", "DATE(2005,2,28)", 658.0, 658.0),
    ];
    for (start, end, days360, yearfrac) in cases {
        let d = num(&format!("=DAYS360({start}, {end}, FALSE)"));
        assert!(
            (d - days360).abs() < 1e-9,
            "DAYS360({start}, {end}) expected {days360}, got {d}"
        );
        let y = num(&format!("=YEARFRAC({start}, {end}, 0) * 360"));
        assert!(
            (y - yearfrac).abs() < 1e-6,
            "YEARFRAC({start}, {end}, 0) * 360 expected {yearfrac}, got {y}"
        );
    }
}

#[test]
fn test_oddlprice_treats_a_month_end_coupon_date_as_the_30th() {
    // On basis 0 the two ODDLPRICE spans that *end at a coupon date* --
    // the quasi-coupon period length and last-interest-to-maturity -- pull
    // a month-end end date back to the 30th, February's included. The
    // spans ending at the settlement date use the plain NASD count, so the
    // same date pair counts differently depending on its role.
    //
    // Every expected value is real Excel's. The pairs below were chosen to
    // separate the rule from the plain European one: a leap-year 28 Feb is
    // *not* a month end and must not be adjusted, while 29 Feb is.
    let cases: [(&str, &str, &str, f64, f64); 8] = [
        // last_interest, settlement, maturity, basis, expected
        (
            "DATE(2017,12,27)",
            "DATE(2018,1,4)",
            "DATE(2018,2,28)",
            0.0,
            100.40414916157073,
        ),
        (
            "DATE(2017,12,27)",
            "DATE(2018,1,4)",
            "DATE(2018,1,31)",
            0.0,
            100.17445487014778,
        ),
        (
            "DATE(2017,12,27)",
            "DATE(2018,1,4)",
            "DATE(2018,3,31)",
            0.0,
            100.59075984089594,
        ),
        // 2016 is a leap year: 28 Feb is not the month end, 29 Feb is.
        (
            "DATE(2015,12,27)",
            "DATE(2016,1,4)",
            "DATE(2016,2,28)",
            0.0,
            100.37619967431928,
        ),
        (
            "DATE(2015,12,27)",
            "DATE(2016,1,4)",
            "DATE(2016,2,29)",
            0.0,
            100.39711327585337,
        ),
        // A month-end *last_interest* shortens the quasi-coupon period.
        (
            "DATE(2018,2,28)",
            "DATE(2018,3,10)",
            "DATE(2018,4,30)",
            0.0,
            100.35615901837147,
        ),
        // Basis 4 stays the plain European count -- 29 Feb is not adjusted
        // there, which is what makes it differ from basis 0 above.
        (
            "DATE(2015,12,27)",
            "DATE(2016,1,4)",
            "DATE(2016,2,29)",
            4.0,
            100.38313951056004,
        ),
        (
            "DATE(2015,12,27)",
            "DATE(2016,1,4)",
            "DATE(2016,1,31)",
            4.0,
            100.18148895627503,
        ),
    ];
    for (last_interest, settlement, maturity, basis, expected) in cases {
        let got = num(&format!(
            "=ODDLPRICE({settlement}, {maturity}, {last_interest}, 0.0505, 0.0253, 100, 4, {basis})"
        ));
        assert!(
            (got - expected).abs() < 1e-9,
            "ODDLPRICE(li={last_interest}, mat={maturity}, basis={basis}) \
             expected {expected}, got {got}"
        );
    }
}

#[test]
fn test_mod_reports_num_once_the_quotient_stops_being_meaningful() {
    // Excel gives up on MOD once the quotient is large enough that
    // `n - d * INT(n / d)` is noise, rather than returning a number built
    // out of it. The cutoff is on the *quotient*: MOD over a huge dividend
    // is fine as long as the divisor is huge too.
    //
    // All expected values are real Excel's.
    for src in [
        "=MOD(POWER(28, 31), 3)",
        "=MOD(10000000000000, 3)",
        "=MOD(-1000000000000000, 3)",
    ] {
        match eval_one(src) {
            ResultData::Error(e) => assert_eq!(e, "#NUM!", "for {src}"),
            other => panic!("expected #NUM! for {src}, got {other:?}"),
        }
    }
    assert_eq!(num("=MOD(1000000000000, 3)"), 1.0);
    assert_eq!(num("=MOD(1000000000000000, 10000000)"), 0.0);
    assert_eq!(num("=MOD(1000000000000000, 1000000)"), 0.0);
    // 2^40 * 3 is inside the limit, 2^41 * 3 is past it.
    assert_eq!(num("=MOD(3298534883328, 3)"), 0.0);
    match eval_one("=MOD(6597069766656, 3)") {
        ResultData::Error(e) => assert_eq!(e, "#NUM!"),
        other => panic!("expected #NUM!, got {other:?}"),
    }
    // Ordinary MOD is untouched, including its sign convention.
    assert_eq!(num("=MOD(10, 3)"), 1.0);
    assert_eq!(num("=MOD(-10, 3)"), 2.0);
    assert_eq!(num("=MOD(10, -3)"), -2.0);
    assert_eq!(num("=MOD(TRUE, 2)"), 1.0);
}

#[test]
// `exact` is a reference value carried at more digits than f64 holds, so it
// can be read against the oracle that produced it; see the comment on it.
#[allow(clippy::excessive_precision)]
fn test_coupdaysnc_and_acoth_precision() {
    // COUPDAYSNC's span ends at a coupon date, so on basis 0 a month-end
    // coupon is pulled back to the 30th -- the same rule ODDLPRICE's
    // coupon-ended spans use. Settlement 2011-08-28 against a 2013-02-28
    // maturity has its next coupon on 2011-08-31, and real Excel counts 2
    // days there, not the 3 the plain NASD rule gives. COUPDAYBS is
    // unaffected: its span ends at the settlement date.
    let s = "DATE(2011,8,28)";
    let m = "EDATE(DATE(2011,8,28),18)";
    assert_eq!(num(&format!("=COUPDAYSNC({s}, {m}, 2, 0)")), 2.0);
    assert_eq!(num(&format!("=COUPDAYBS({s}, {m}, 2, 0)")), 178.0);
    assert_eq!(num(&format!("=COUPDAYS({s}, {m}, 2, 0)")), 180.0);
    // Other bases and a non-month-end coupon are unchanged.
    assert_eq!(
        num("=COUPDAYSNC(DATE(2003,12,21), EDATE(DATE(2003,12,21),108), 1, 1)"),
        366.0
    );
    assert_eq!(
        num("=COUPDAYSNC(DATE(2017,9,22), EDATE(DATE(2017,9,22),36), 2, 3)"),
        181.0
    );
    assert_eq!(
        num("=COUPDAYSNC(DATE(2011,8,15), EDATE(DATE(2011,8,15),18), 2, 0)"),
        180.0
    );

    // ACOTH via atanh(1/x). The 0.5 * ln((x+1)/(x-1)) form loses its
    // significant digits for large |x|: the true ACOTH(-165) is
    // -0.006060680266172405095..., and it returned
    // -0.006060680266172425 -- wrong from the 15th digit, which is
    // precisely where Excel's display lands.
    // Tolerance is relative: the old form was off by 3.3e-15 relative,
    // this one by under 2e-16.
    let exact = -0.0060606802661724050957;
    let got = num("=ACOTH(-165)");
    assert!(
        ((got - exact) / exact).abs() < 1e-15,
        "ACOTH(-165) expected {exact}, got {got}"
    );
    assert!((num("=ACOTH(2)") - 0.5493061443340549).abs() < 1e-16);
}

#[test]
fn test_iseven_and_isodd_past_the_i64_range() {
    // Parity is decided in f64, not through an i64 cast: that cast
    // saturates at i64::MAX (odd) for anything past ~9.2e18, so
    // ISEVEN(19^24) came out FALSE. Excel works from the double it holds --
    // 19^24 is odd mathematically, but its f64 is a multiple of a large
    // power of two, and Excel answers TRUE.
    assert!(matches!(
        eval_one("=ISEVEN(INT(19^24))"),
        ResultData::Boolean(true)
    ));
    assert!(matches!(
        eval_one("=ISODD(INT(19^24))"),
        ResultData::Boolean(false)
    ));
    // Ordinary parity is unchanged, including negatives and truncation.
    for (src, want) in [
        ("=ISEVEN(4)", true),
        ("=ISEVEN(3)", false),
        ("=ISEVEN(-4)", true),
        ("=ISEVEN(0)", true),
        ("=ISEVEN(2.5)", true),
        ("=ISODD(3)", true),
        ("=ISODD(4)", false),
        ("=ISODD(-3)", true),
    ] {
        match eval_one(src) {
            ResultData::Boolean(b) => assert_eq!(b, want, "for {src}"),
            other => panic!("expected a boolean for {src}, got {other:?}"),
        }
    }
}

#[test]
fn test_prob_checks_only_that_the_probabilities_sum_to_one() {
    // Excel does not reject an individual probability outside [0, 1] --
    // only the total matters. PROB({1,2}, {1.5,-0.5}, 0, 3) is 1 in real
    // Excel, and rejecting the negative turned a pairwise-excluded range
    // that legitimately summed to 1 into #NUM!.
    let mut sheet = create_sheet(&[
        ["1", "0.5", "1.5", "=PROB(A1:A2, B1:B2, 0, 3)"],
        ["2", "0.5", "-0.5", "=PROB(A1:A2, C1:C2, 0, 3)"],
        ["", "", "", "=PROB(A1:A2, A1:A2, 0, 3)"],
    ]);
    sheet.commit(None).unwrap();
    assert_eq!(num_of(&sheet.get_result_data(&CellRef::new(0, 3))), 1.0);
    assert_eq!(num_of(&sheet.get_result_data(&CellRef::new(1, 3))), 1.0);
    // A total that is not 1 is still #NUM!.
    match sheet.get_result_data(&CellRef::new(2, 3)) {
        ResultData::Error(e) => assert_eq!(e, "#NUM!"),
        other => panic!("expected #NUM!, got {other:?}"),
    }
}

fn num_of(r: &ResultData) -> f64 {
    match r {
        ResultData::Float(f) => *f,
        ResultData::Integer(i) => *i as f64,
        other => panic!("expected a number, got {other:?}"),
    }
}

#[test]
fn test_serial_zero_is_excels_phantom_january_zero() {
    // Excel's serial 0 is "January 0, 1900", not 1 January, and it is
    // consistent about it: DAY(0) is 0 while MONTH(0) is 1 and YEAR(0) is
    // 1900. Returning day 1 there put every one of these out by a day.
    assert_eq!(num("=DAY(0)"), 0.0);
    assert_eq!(num("=MONTH(0)"), 1.0);
    assert_eq!(num("=YEAR(0)"), 1900.0);
    // A fraction of a day is still day 0.
    assert_eq!(num("=DAY(0.6299)"), 0.0);
    match eval_one("=TEXT(0.6299, \"yyyy-mm-dd\")") {
        ResultData::String(s) => assert_eq!(s, "1900-01-00"),
        other => panic!("expected 1900-01-00, got {other:?}"),
    }
    // Serial 1 onwards is unchanged, including the 1900 leap-year bug.
    assert_eq!(num("=DAY(1)"), 1.0);
    for (serial, want) in [
        (59.0, "1900-02-28"),
        (60.0, "1900-02-29"),
        (61.0, "1900-03-01"),
    ] {
        match eval_one(&format!("=TEXT({serial}, \"yyyy-mm-dd\")")) {
            ResultData::String(s) => assert_eq!(s, want, "for serial {serial}"),
            other => panic!("expected {want}, got {other:?}"),
        }
    }
}