visi-core 0.2.14

use super::*;

#[test]
fn test_text_manipulation_functions() {
    let grid = [[
        "=CHAR(65)",
        "=CODE(\"A\")",
        "=EXACT(\"abc\", \"abc\")",
        "=REPT(\"a\", 3)",
        "=SUBSTITUTE(\"banana\", \"a\", \"o\")",
        "=UNICHAR(65)",
    ]];
    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 == "A"));

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

    let r3 = sheet.get_result_data(&CellRef::new(0, 2));
    assert!(matches!(r3, ResultData::Boolean(true)));

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

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

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

#[test]
fn test_text_search_and_split_functions() {
    let grid = [[
        "=FIND(\"bar\", \"foobar\")",
        "=TEXTBEFORE(\"hello-world\", \"-\")",
        "=TEXTAFTER(\"hello-world\", \"-\")",
        "=TEXTJOIN(\", \", TRUE, \"a\", \"b\", \"c\")",
        "=VALUE(\"123.45\")",
    ]];
    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::String(ref s) if s == "hello"));

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

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

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

#[test]
fn test_currency_and_formatting_functions() {
    let grid = [[
        "=DOLLAR(1234.56)",
        "=FIXED(1234.56, 1)",
        "=TEXT(0.25, \"0.0%\")",
        "=NUMBERVALUE(\"1,234.56\", \".\", \",\")",
    ]];
    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,234.56"));

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

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

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

#[test]
fn test_regex_functions_use_real_regex_not_literal_substring() {
    let grid = [[
        "=REGEXEXTRACT(\"order-12345\", \"[0-9]+\")",
        "=REGEXREPLACE(\"order-12345\", \"[0-9]+\", \"X\")",
        "=REGEXTEST(\"order-12345\", \"[0-9]+\")",
        "=REGEXTEST(\"order-abcde\", \"[0-9]+\")",
    ]];
    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 == "12345"));

    let r2 = sheet.get_result_data(&CellRef::new(0, 1));
    assert!(matches!(r2, ResultData::String(ref s) if s == "order-X"));

    let r3 = sheet.get_result_data(&CellRef::new(0, 2));
    assert!(matches!(r3, ResultData::Boolean(true)));

    let r4 = sheet.get_result_data(&CellRef::new(0, 3));
    assert!(matches!(r4, ResultData::Boolean(false)));
}

#[test]
fn test_text_number_format_codes() {
    let grid = [[
        "=TEXT(-7679.0669, \"$#,##0.00\")",
        "=TEXT(3021.1929, \"#,##0\")",
        "=TEXT(6436.3899, \"0%\")",
        "=TEXT(DATE(1910,8,29), \"yyyy-mm-dd\")",
    ]];
    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 == "-$7,679.07"),
        "{r1:?}"
    );

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

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

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

#[test]
fn test_text_custom_number_format_literals_and_sections() {
    for (value, fmt, expected) in [
        (7.5, "0.0\"x\"", "7.5x"),
        (1234.0, "\\$#,##0.00", "$1,234.00"),
        (-1234.0, "#,##0;(#,##0)", "(1,234)"),
        (0.125, "0.##%", "12.5%"),
    ] {
        let got = crate::core::text::text_fn(value, fmt);
        assert_eq!(got, Ok(expected.to_string()), "TEXT({value}, {fmt:?})");
    }
}

#[test]
fn test_proper_capitalizes_letter_after_digits() {
    let grid = [["=PROPER(\"123abc\")"]];
    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 == "123Abc"),
        "{r:?}"
    );
}

#[test]
fn test_arraytotext_joins_every_element_including_text_and_blanks() {
    let grid = [
        ["1", "\"hello\"", "TRUE", ""],
        ["=ARRAYTOTEXT(A1:D1)", "", "", ""],
    ];
    let mut sheet = create_sheet(&grid);
    sheet.commit(None).unwrap();

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

#[test]
fn test_jis_is_inverse_of_asc() {
    let grid = [["=ASC(JIS(\"AB 1\"))", "=JIS(\"AB\")"]];
    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 == "AB 1"));

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

#[test]
fn test_filterxml_basic_and_errors() {
    let grid = [[
        "=FILTERXML(\"<a><b id='2'>hi</b><b id='9'>lo</b></a>\", \"/a/b[@id='9']\")",
        "=FILTERXML(\"<a><b>x</b></a>\", \"/a/b/text()\")",
        "=FILTERXML(\"<a><b>x</b></a>\", \"/a/c\")",
        "=FILTERXML(\"<a><b>x</b>\", \"/a/b\")",
    ]];
    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 == "lo"));

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

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

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

#[test]
fn test_number_to_text_matches_excel_decimal_range() {
    use crate::core::engine::result_data::format_excel_number;
    for (value, expected) in [
        (976121418126.432_f64, "976121418126.432"),
        (1e15, "1000000000000000"),
        (1e18, "1000000000000000000"),
        (1e19, "10000000000000000000"),
        (1e20, "1E+20"),
        (0.000001, "0.000001"),
        (0.000000001, "0.000000001"),
        (0.000001207666770903, "0.000001207666770903"),
        (0.00000120766677090395, "1.20766677090395E-06"),
        (0.5, "0.5"),
        (0.0, "0"),
    ] {
        let got = format_excel_number(value);
        assert_eq!(got, expected, "format_excel_number({value})");
    }
}

#[test]
fn test_text_rounds_half_away_from_zero() {
    for (value, fmt, expected) in [
        (-3873.705_f64, "0.00", "-3873.71"),
        (2.675, "0.00", "2.68"),
        (1.005, "0.00", "1.01"),
    ] {
        let got = crate::core::text::text_fn(value, fmt);
        assert_eq!(got, Ok(expected.to_string()), "TEXT({value}, {fmt:?})");
    }
}