PIVOTBY

2 pivotby_fn "PIVOTBY" (4, 11) [R "row_fields", R "col_fields", R "values", R "function", V "field_headers", V "row_total_depth", R "row_sort_order", V "col_total_depth", R "col_sort_order", R "filter_array", V "relative_to"] |s, a, sv| { pivotby(s, a, sv) }

/// The lines of an axis whose sorted `groups` have `fields` fields, at total depth `depth`: the
/// groups, a subtotal after (before, for a negative depth) each run of groups sharing their first
/// k fields for each k below the depth, and the grand total over `all`.
fn lines(
    site: &Site<'_>,
    groups: &[(Vec<CalcValue>, Vec<usize>, Vec<CalcValue>)],
    fields: usize,
    depth: i64,
    all: &[usize],
) -> Step<Vec<Line>> {
    let levels = depth.unsigned_abs() as usize;
    let before = depth < 0;
    let mut labels = vec![text(""); fields.max(1)];
    labels[0] = text(if levels >= 2 { "Grand Total" } else { "Total" });
    let grand = Line {
        labels,
        members: all.to_vec(),
        grand: true,
        subtotal: false,
    };
    let mut out = Vec::new();
    subtotalled(site, groups, 0, levels, before, fields, &mut out)?;
    match depth {
        d if d > 0 => out.push(grand),
        d if d < 0 => out.insert(0, grand),
        _ => {}
    }
    Ok(out)
}

/// The lines of `groups`, which share their first `level` fields, with a subtotal for each run of
/// them sharing field `level` while `level + 1` is below `levels`.
fn subtotalled(
    site: &Site<'_>,
    groups: &[(Vec<CalcValue>, Vec<usize>, Vec<CalcValue>)],
    level: usize,
    levels: usize,
    before: bool,
    fields: usize,
    out: &mut Vec<Line>,
) -> Step<()> {
    if level + 1 >= levels {
        for g in groups {
            out.push(Line {
                labels: g.0.clone(),
                members: g.1.clone(),
                grand: false,
                subtotal: false,
            });
        }
        return Ok(());
    }
    let mut at = 0;
    while at < groups.len() {
        let mut end = at + 1;
        while end < groups.len() && same(site, &groups[end].0[level], &groups[at].0[level])? {
            end += 1;
        }
        let run = &groups[at..end];
        let mut labels = vec![text(""); fields];
        labels[..=level].clone_from_slice(&run[0].0[..=level]);
        let subtotal = Line {
            labels,
            members: run.iter().flat_map(|g| g.1.iter().copied()).collect(),
            grand: false,
            subtotal: true,
        };
        if before {
            out.push(subtotal);
            subtotalled(site, run, level + 1, levels, before, fields, out)?;
        } else {
            subtotalled(site, run, level + 1, levels, before, fields, out)?;
            out.push(subtotal);
        }
        at = end;
    }
    Ok(())
}

fn pivotby(site: &Site<'_>, a: &[CalcValue], sv: &mut EvalServices<'_>) -> Step<CalcValue> {
    let row_fields = table(site, a, 0, sv)?;
    let col_fields = table(site, a, 1, sv)?;
    let values = table(site, a, 2, sv)?;
    let functions = functions(a, 3)?;
    if row_fields.height() != values.height() || col_fields.height() != values.height() {
        return fail(W::Value);
    }
    let (has_header, show) = headers(site, a, 4, &values)?;
    let row_depth = whole(site, a, 5)?.unwrap_or(1);
    let row_entries = sort_entries(site, a, 6)?;
    let col_depth = whole(site, a, 7)?.unwrap_or(1);
    let col_entries = sort_entries(site, a, 8)?;
    let relative_to = whole(site, a, 10)?.unwrap_or(0);
    let (kr, kc) = (row_fields.cols, col_fields.cols);
    for (depth, fields) in [(row_depth, kr), (col_depth, kc)] {
        if depth.unsigned_abs() > 1 && depth.unsigned_abs() as usize > fields {
            return fail(W::Value);
        }
    }
    if !(0..=4).contains(&relative_to) {
        return fail(W::Value);
    }
    let pairs = pairs(&functions, values.cols)?;
    let start = usize::from(has_header);
    if values.height() <= start {
        return fail(W::Value);
    }
    let count = values.height() - start;
    let keep = filter(site, a, 9, count, values.height(), sv)?;
    let rows: Vec<usize> = (start..values.height()).filter(|r| keep[r - start]).collect();
    let row_keys = canonical(site, &row_fields.rows, &rows)?;
    let col_keys = canonical(site, &col_fields.rows, &rows)?;
    let mut row_groups: Vec<(Vec<CalcValue>, Vec<usize>, Vec<CalcValue>)> = group(site, &row_keys, &rows)?
        .into_iter()
        .map(|(k, m)| (k, m, Vec::new()))
        .collect();
    let mut col_groups: Vec<(Vec<CalcValue>, Vec<usize>, Vec<CalcValue>)> = group(site, &col_keys, &rows)?
        .into_iter()
        .map(|(k, m)| (k, m, Vec::new()))
        .collect();
    sort_groups(site, &mut row_groups, kr, &row_entries, false)?;
    sort_groups(site, &mut col_groups, kc, &col_entries, false)?;
    let np = pairs.len();
    // The rows each cell of the result aggregates, and the rows its two-argument function is
    // relative to.
    let intersect = |x: &[usize], y: &[usize]| -> Vec<usize> { x.iter().copied().filter(|r| y.contains(r)).collect() };
    let relative = |row_members: &[usize], col_members: &[usize]| -> Vec<usize> {
        match relative_to {
            1 | 3 => row_members.to_vec(),
            2 => rows.clone(),
            _ => col_members.to_vec(),
        }
    };
    let mut out: Vec<Vec<CalcValue>> = Vec::new();
    // Column layout: the column lines, each with np value columns.
    let columns = lines(site, &col_groups, kc, col_depth, &rows)?;
    // With several value columns and one function, the total and subtotal columns are blank.
    let blank_totals = values.cols > 1 && functions.len() == 1;
    // Header rows.
    if show {
        for level in 0..kc {
            let mut header = vec![text(""); kr];
            if level == 0 {
                header.push(if has_header { col_fields.rows[0][0].clone() } else { text("Column Field 1") });
                header.extend(std::iter::repeat_n(text(""), columns.len() * np - 1));
            } else {
                header.extend(std::iter::repeat_n(text(""), columns.len() * np));
            }
            out.push(header);
        }
    }
    for level in 0..kc {
        let mut header = vec![text(""); kr];
        for column in &columns {
            for _ in 0..np {
                header.push(column.labels[level].clone());
            }
        }
        out.push(header);
    }
    if show {
        let mut header: Vec<CalcValue> = (0..kr)
            .map(|c| if has_header { row_fields.rows[0][c].clone() } else { text(&format!("Row Field {}", c + 1)) })
            .collect();
        for _ in &columns {
            for &(v, f) in &pairs {
                header.push(if functions.len() > 1 {
                    function_name(&functions[f])
                } else if has_header {
                    values.rows[0][v].clone()
                } else {
                    text(&format!("Value {}", v + 1))
                });
            }
        }
        out.push(header);
    } else if functions.len() > 1 {
        let mut header = vec![text(""); kr];
        for _ in &columns {
            header.extend(pairs.iter().map(|&(_, f)| function_name(&functions[f])));
        }
        out.push(header);
    }
    let body = |site: &Site<'_>, sv: &mut EvalServices<'_>, label: Vec<CalcValue>, row_members: &[usize]| -> Step<Vec<CalcValue>> {
        let mut row = label;
        for column in &columns {
            let col_members = &column.members;
            let members = intersect(row_members, col_members);
            for &(v, f) in &pairs {
                if (column.grand || column.subtotal) && blank_totals {
                    row.push(text(""));
                    continue;
                }
                if members.is_empty() {
                    row.push(text(""));
                    continue;
                }
                let rel = relative(row_members, col_members);
                row.push(aggregate(site, sv, &functions[f], &values, v, &members, &rel)?);
            }
        }
        Ok(row)
    };
    for line in lines(site, &row_groups, kr, row_depth, &rows)? {
        out.push(body(site, sv, line.labels, &line.members)?);
    }
    for row in &mut out {
        for cell in row.iter_mut() {
            if matches!(cell.core_ref(), CoreRef::Missing) {
                *cell = text("");
            }
        }
    }
    result(out)
}