catalogue/subtotal

/// The values of the reference arguments `from..`, with the cells `rule` leaves out removed.
fn held_values(
    site: &Site<'_>,
    a: &[CalcValue],
    from: usize,
    sv: &mut EvalServices<'_>,
    rule: Rule,
) -> Step<Vec<CalcValue>> {
    let mut found = Vec::new();
    for (index, argument) in a.iter().enumerate().skip(from) {
        let CoreRef::Reference(reference) = argument.core_ref() else {
            // SUBTOTAL and AGGREGATE's reference form read references only; an array here is
            // not modelled.
            return Err(site.not_modelled(Some(index)));
        };
        let needs_knowledge = rule.nested || rule.hidden || rule.filtered;
        let mut excluded_cells = BTreeSet::new();
        let mut excluded_rows = BTreeSet::new();
        if needs_knowledge {
            let reader = aggregate::reader(site, sv, index)?;
            for &area in reference.areas() {
                let Some(SubtotalExclusions {
                    nested_subtotals,
                    hidden_rows,
                    filtered_rows,
                }) = reader.subtotal_exclusions(area)
                else {
                    return Err(site.not_modelled(Some(index)));
                };
                if rule.nested {
                    excluded_cells.extend(nested_subtotals.iter().map(|k| (k.row(), k.column())));
                }
                if rule.hidden {
                    excluded_rows.extend(hidden_rows);
                }
                if rule.filtered {
                    excluded_rows.extend(filtered_rows);
                }
            }
        }
        let reader = aggregate::reader(site, sv, index)?;
        read_reference(site, reader, reference, index, &mut |_, key, value| {
            if excluded_rows.contains(&key.row())
                || excluded_cells.contains(&(key.row(), key.column()))
            {
                return Ok(());
            }
            if rule.errors && matches!(value.core_ref(), CoreRef::Error(_)) {
                return Ok(());
            }
            found.push(value.clone());
            Ok(())
        })?;
    }
    Ok(found)
}

/// The numbers among held values; the first error is the result.
fn numbers_of(values: &[CalcValue]) -> Step<Vec<f64>> {
    let mut found = Vec::new();
    for value in values {
        match value.core_ref() {
            CoreRef::Number(n) => found.push(n),
            CoreRef::Error(code) => return fail(code),
            _ => {}
        }
    }
    Ok(found)
}

/// Function `number` (1 to 13, SUBTOTAL's numbering for 1 to 11) over the held values.
fn compute(site: &Site<'_>, number: i64, values: &[CalcValue]) -> Step<CalcValue> {
    if number == 3 {
        let counted = values
            .iter()
            .filter(|v| !matches!(v.core_ref(), CoreRef::Empty | CoreRef::Missing))
            .count();
        return site.number(counted as f64);
    }
    if number == 2 {
        let counted = values
            .iter()
            .filter(|v| matches!(v.core_ref(), CoreRef::Number(_)))
            .count();
        return site.number(counted as f64);
    }
    let numbers = numbers_of(values)?;
    let result = match number {
        1 => {
            let mut total = 0.0;
            for n in &numbers {
                total = site.add(total, *n)?;
            }
            site.divide(total, numbers.len() as f64)?
        }
        4 => numbers.iter().copied().fold(None, |m: Option<f64>, n| Some(m.map_or(n, |m| m.max(n)))).unwrap_or(0.0),
        5 => numbers.iter().copied().fold(None, |m: Option<f64>, n| Some(m.map_or(n, |m| m.min(n)))).unwrap_or(0.0),
        6 => {
            if numbers.is_empty() {
                0.0
            } else {
                let mut product = 1.0;
                for n in &numbers {
                    product = site.multiply(product, *n)?;
                }
                product
            }
        }
        7 => stdev_from_values(&numbers, VarianceDivisor::Sample).map_err(Exit::Error)?,
        8 => stdev_from_values(&numbers, VarianceDivisor::Population).map_err(Exit::Error)?,
        9 => {
            let mut total = 0.0;
            for n in &numbers {
                total = site.add(total, *n)?;
            }
            total
        }
        10 => variance_from_values(&numbers, VarianceDivisor::Sample).map_err(Exit::Error)?,
        11 => variance_from_values(&numbers, VarianceDivisor::Population).map_err(Exit::Error)?,
        12 => {
            let mut sorted = numbers.clone();
            sorted.sort_by(f64::total_cmp);
            if sorted.is_empty() {
                return fail(WorksheetErrorCode::Num);
            }
            let mid = sorted.len() / 2;
            if sorted.len() % 2 == 1 {
                sorted[mid]
            } else {
                sorted[mid - 1] + (sorted[mid] - sorted[mid - 1]) / 2.0
            }
        }
        13 => {
            // MODE.SNGL: the first most frequent number, in reading order.
            let mut best: Option<(usize, f64)> = None;
            for (i, n) in numbers.iter().enumerate() {
                let count = numbers.iter().filter(|m| *m == n).count();
                if count >= 2 && best.is_none_or(|(c, _)| count > c) && numbers[..i].iter().all(|m| m != n) {
                    best = Some((count, *n));
                }
            }
            match best {
                Some((_, n)) => n,
                None => return fail(WorksheetErrorCode::NA),
            }
        }
        _ => return fail(WorksheetErrorCode::Value),
    };
    site.number(result)
}