//! The lookups, the Quantrix SELECT family, the database functions and
//! ARRAYTOTEXT against LibreOffice (and Excel's and Quantrix's documented
//! rules where LibreOffice has no answer): every dataset of
//! `tests/data/lookup_reduce_goldens.tsv` (regenerated by
//! `scripts/goldens/lookup_reduce.py`) becomes a model with category T (the
//! table's items), optionally G (one item per group) and E (the lookup keys'
//! own category), one input measure per column. Each golden formula, e.g.
//! `XLOOKUP(Q, K OVER T, V)`, is a derived measure evaluated by the dataflow
//! engine and compared, group by group and (for a key over E) item by item,
//! with the expected value.

use improv_core_model::{
    CategoryId, Coordinate, ItemId, Measure, MeasureId, MeasureKind, Model, Name, Value,
    ValueError, ValueErrorKind, ValueType,
};
use improv_engine::{compiler::derived_shape, dataflow::evaluate, CellValue};
use serde_json::Value as J;

const T: CategoryId = CategoryId(1);
const G: CategoryId = CategoryId(2);
const E: CategoryId = CategoryId(3);

fn item(base: u32, i: usize) -> ItemId {
    ItemId(base + i as u32)
}

fn error_kind(j: &J) -> Option<ValueErrorKind> {
    let code = j.get("error")?.as_str()?;
    Some(ValueErrorKind::from_code(code).unwrap_or_else(|| panic!("unknown error {code}")))
}

fn input(j: &J) -> Option<Value> {
    Some(match j {
        J::Null => return None,
        J::Bool(b) => Value::Boolean(*b),
        J::Number(n) => Value::Number(n.as_f64().unwrap()),
        J::String(s) => Value::Text(s.clone()),
        other => Value::Error(ValueError::new(error_kind(other).expect("an error object"))),
    })
}

/// A golden cell: a number, text, an error object, or null for no cell.
fn expected(j: &J) -> Option<CellValue> {
    match j {
        J::Null => None,
        J::Number(n) => Some(CellValue::num(n.as_f64().unwrap())),
        J::String(s) => Some(CellValue::Text(s.clone())),
        other => Some(CellValue::Err(
            error_kind(other)
                .and_then(|k| k.error_type())
                .expect("error code"),
        )),
    }
}

/// Add input measure `name` over `cats` with `cells` (coordinate, value).
fn add_input(
    m: &mut Model,
    id: MeasureId,
    name: &str,
    cats: Vec<CategoryId>,
    cells: Vec<(Vec<(CategoryId, ItemId)>, &J)>,
) {
    let mut vt = ValueType::Number;
    for (coord, v) in cells {
        let Some(v) = input(v) else { continue };
        vt = match &v {
            Value::Text(_) => ValueType::Text,
            Value::Boolean(_) => ValueType::Boolean,
            _ => vt,
        };
        m.set_input(id, Coordinate::from_pairs(coord), v);
    }
    m.add_measure(Measure {
        id,
        name: Name(name.into()),
        value_type: vt,
        categories: cats,
        kind: MeasureKind::Input,
        description: None,
    });
}

/// The dataset's model: categories T (and G, E), one input measure per
/// column.
fn model(data: &J) -> Model {
    let mut m = Model::new();
    let size = |k: &str| data.get(k).and_then(J::as_u64).map(|n| n as usize);
    let (n_t, n_g, n_e) = (size("T").unwrap(), size("G"), size("E"));
    for (cat, name, base, n) in [
        (T, "T", 100, Some(n_t)),
        (G, "G", 200, n_g),
        (E, "E", 300, n_e),
    ] {
        let Some(n) = n else { continue };
        m.add_category(cat, name);
        for i in 0..n {
            m.add_item(item(base, i), cat, format!("{}{i}", name.to_lowercase()));
        }
    }
    let mut next = 10;
    for (name, groups) in data["m"].as_object().unwrap() {
        let mut cells = Vec::new();
        for (g, vals) in groups.as_array().unwrap().iter().enumerate() {
            for (i, v) in vals.as_array().unwrap().iter().enumerate() {
                let mut coord = vec![(T, item(100, i))];
                if n_g.is_some() {
                    coord.push((G, item(200, g)));
                }
                cells.push((coord, v));
            }
        }
        let cats = if n_g.is_some() { vec![T, G] } else { vec![T] };
        add_input(&mut m, MeasureId(next), name, cats, cells);
        next += 1;
    }
    for (name, vals) in data.get("e").and_then(J::as_object).into_iter().flatten() {
        let cells = vals
            .as_array()
            .unwrap()
            .iter()
            .enumerate()
            .map(|(r, v)| (vec![(E, item(300, r))], v))
            .collect();
        add_input(&mut m, MeasureId(next), name, vec![E], cells);
        next += 1;
    }
    m
}

/// Equal, or within 1e-12 relative (the deviations' last bits).
fn close(got: &CellValue, want: &CellValue) -> bool {
    match (got.as_num(), want.as_num()) {
        (Some(a), Some(b)) => a == b || (a - b).abs() <= 1e-12 * b.abs(),
        _ => got == want,
    }
}

/// A golden row: (line, formula, expected per group, source).
type Case = (usize, String, J, String);

fn datasets() -> Vec<(String, J, Vec<Case>)> {
    let text = include_str!("data/lookup_reduce_goldens.tsv");
    let mut out: Vec<(String, J, Vec<Case>)> = Vec::new();
    for (n, line) in text.lines().enumerate().skip(1) {
        let cols: Vec<&str> = line.split('\t').collect();
        if let Some(name) = cols[0].strip_prefix('@') {
            out.push((
                name.into(),
                serde_json::from_str(cols[1]).unwrap(),
                Vec::new(),
            ));
            continue;
        }
        let ds = out.last_mut().expect("a dataset line first");
        assert_eq!(cols[0], ds.0, "line {}", n + 1);
        let want = serde_json::from_str(cols[2]).unwrap();
        ds.2.push((n + 1, cols[1].into(), want, cols[3].into()));
    }
    out
}

#[test]
fn lookups_match_the_goldens() {
    let mut failures = Vec::new();
    let (mut checked, mut broadcast) = (0, 0);
    for (name, data, cases) in &datasets() {
        let mut m = model(data);
        let mut targets = Vec::new();
        for (k, (line, formula, want, src)) in cases.iter().enumerate() {
            let id = MeasureId(1000 + k as u32);
            let text = format!("R{k} = {formula}");
            let f = match improv_core_model::parse_formula(&m, &text) {
                Ok(f) => f.formula,
                Err(e) => {
                    failures.push(format!("{line}: {formula}: parse error {e}"));
                    continue;
                }
            };
            let (vt, cats) = match derived_shape(&m, id, &format!("R{k}"), &f) {
                Ok(s) => s,
                Err(e) => {
                    failures.push(format!("{line}: {formula}: compile error {e}"));
                    continue;
                }
            };
            m.add_measure(Measure {
                id,
                name: Name(format!("R{k}")),
                value_type: vt,
                categories: cats,
                kind: MeasureKind::Derived(f),
                description: None,
            });
            targets.push((id, *line, formula, want, src));
        }
        let ids: Vec<MeasureId> = targets.iter().map(|t| t.0).collect();
        let out = evaluate(&m, &ids).unwrap_or_else(|e| panic!("dataset {name}: {e}"));
        let grouped = data.get("G").is_some();
        for (id, line, formula, want, src) in targets {
            let res = out.get(&id).cloned().unwrap_or_default();
            let mut expected_cells = 0;
            for (g, w) in want.as_array().unwrap().iter().enumerate() {
                let gk: Vec<(u32, u32)> = if grouped {
                    vec![(G.0, item(200, g).0)]
                } else {
                    vec![]
                };
                let cells: Vec<(Vec<(u32, u32)>, &J)> = match w {
                    // A key over E: one lookup per item of E.
                    J::Array(per) => {
                        broadcast += 1;
                        per.iter()
                            .enumerate()
                            .map(|(r, w)| {
                                let mut k = gk.clone();
                                k.push((E.0, item(300, r).0));
                                k.sort_unstable();
                                (k, w)
                            })
                            .collect()
                    }
                    w => vec![(gk.clone(), w)],
                };
                for (key, w) in cells {
                    let want = expected(w);
                    expected_cells += usize::from(want.is_some());
                    let got = res.get(&key);
                    let ok = match (got, &want) {
                        (Some(g), Some(want)) => close(g, want),
                        (None, None) => true,
                        _ => false,
                    };
                    checked += 1;
                    if !ok {
                        failures.push(format!(
                            "{line}: {formula} at {key:?} = {got:?}, want {want:?} ({src})"
                        ));
                    }
                }
            }
            if res.len() != expected_cells {
                failures.push(format!(
                    "{line}: {formula}: {} cells, want {expected_cells}",
                    res.len()
                ));
            }
        }
    }
    assert!(checked > 150, "only {checked} cells checked");
    assert!(broadcast >= 8, "only {broadcast} broadcast lookups");
    assert!(
        failures.is_empty(),
        "{} of {checked} cells fail:\n{}",
        failures.len(),
        failures.join("\n")
    );
}

/// The brief's example: a rate table over Band, each employee's grade
/// looked up in it. The keys and rates are both numbers here (see the
/// typing note in `lookup_result_takes_the_keys_type`).
#[test]
fn rate_per_employee_from_a_band_table() {
    let (band, emp) = (CategoryId(1), CategoryId(2));
    let mut m = Model::new();
    m.add_category(band, "Band");
    m.add_category(emp, "Employee");
    for i in 0..3 {
        m.add_item(item(100, i), band, format!("b{i}"));
    }
    for i in 0..4 {
        m.add_item(item(200, i), emp, format!("e{i}"));
    }
    let j = |v: f64| J::from(v);
    let (keys, rates, grades) = (
        [j(1.0), j(2.0), j(3.0)],
        [j(10.0), j(20.0), j(30.0)],
        [j(2.0), j(3.0), j(9.0), j(1.0)],
    );
    let on = |c, base, vs: &[J]| -> Vec<(Vec<(CategoryId, ItemId)>, J)> {
        vs.iter()
            .enumerate()
            .map(|(i, v)| (vec![(c, item(base, i))], v.clone()))
            .collect()
    };
    for (id, name, cat, cells) in [
        (10, "GradeKey", band, on(band, 100, &keys)),
        (11, "GradeRate", band, on(band, 100, &rates)),
        (12, "Grade", emp, on(emp, 200, &grades)),
    ] {
        let cells = cells.iter().map(|(k, v)| (k.clone(), v)).collect();
        add_input(&mut m, MeasureId(id), name, vec![cat], cells);
    }
    let f = improv_core_model::parse_formula(
        &m,
        "Rate = XLOOKUP(Grade, GradeKey OVER Band, GradeRate, 0)",
    )
    .unwrap()
    .formula;
    let (vt, cats) = derived_shape(&m, MeasureId(20), "Rate", &f).unwrap();
    assert_eq!((vt, cats.clone()), (ValueType::Number, vec![emp]));
    m.add_measure(Measure {
        id: MeasureId(20),
        name: Name("Rate".into()),
        value_type: vt,
        categories: cats,
        kind: MeasureKind::Derived(f),
        description: None,
    });
    let out = evaluate(&m, &[MeasureId(20)]).unwrap();
    let rate = |e: usize| out[&MeasureId(20)][&vec![(emp.0, item(200, e).0)]].clone();
    assert_eq!(
        (0..4).map(rate).collect::<Vec<_>>(),
        [20.0, 30.0, 0.0, 10.0].map(CellValue::num)
    );
}

/// Known gap: `Ret::Unify` types an aggregation by its FIRST reduced
/// argument, which for XLOOKUP and LOOKUP is the keys, not the values. With
/// text keys and number values the result is declared Text. Fixing it needs
/// a compiler change (a `Ret` that names the argument); this test pins the
/// current behavior so the fix shows up here.
#[test]
fn lookup_result_takes_the_values_type() {
    let data: J =
        serde_json::from_str(r#"{"T": 2, "m": {"K": [["a", "b"]], "V": [[1, 2]]}}"#).unwrap();
    let m = model(&data);
    let f = improv_core_model::parse_formula(&m, r#"R = XLOOKUP("b", K OVER T, V)"#)
        .unwrap()
        .formula;
    let (vt, _) = derived_shape(&m, MeasureId(99), "R", &f).unwrap();
    assert_eq!(vt, ValueType::Number, "XLOOKUP is typed by its values");
}