spreadsheet_kit/

verification.rs

use crate::model::{CellValue, NamedItemKind, NamedRangeDescriptor, NamedRangeScope};
use crate::workbook::{WorkbookContext, cell_to_value};
use anyhow::{Result, anyhow, bail};
use schemars::JsonSchema;
use serde::Serialize;
use std::collections::{BTreeMap, BTreeSet};

#[derive(Debug, Clone, Serialize, JsonSchema, Default)]
pub struct TargetClassificationCounts {
    pub unchanged: u32,
    pub direct_edit: u32,
    pub recalc_result: u32,
    pub formula_shift: u32,
    pub new_error: u32,
}

#[derive(Debug, Clone, Serialize, JsonSchema)]
pub struct VerifySummary {
    pub target_count: u32,
    pub changed_targets: u32,
    pub new_error_count: u32,
    pub resolved_error_count: u32,
    pub preexisting_error_count: u32,
    pub named_range_delta_count: u32,
    pub target_classification_counts: TargetClassificationCounts,
}

#[derive(Debug, Clone, Serialize, JsonSchema)]
pub struct TargetDelta {
    pub address: String,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub before: Option<CellValue>,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub after: Option<CellValue>,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub before_formula: Option<String>,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub after_formula: Option<String>,
    pub classification: String,
    pub changed: bool,
}

#[derive(Debug, Clone, Serialize, JsonSchema)]
pub struct ErrorDelta {
    pub address: String,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub before_error: Option<String>,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub after_error: Option<String>,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub before_formula: Option<String>,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub after_formula: Option<String>,
}

#[derive(Debug, Clone, Serialize, JsonSchema)]
pub struct NamedRangeDelta {
    pub name: String,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub scope_kind: Option<NamedRangeScope>,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub scope_sheet_name: Option<String>,
    pub change: String,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub before_refers_to: Option<String>,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub after_refers_to: Option<String>,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub before_kind: Option<NamedItemKind>,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub after_kind: Option<NamedItemKind>,
}

#[derive(Debug, Clone, Serialize, JsonSchema)]
pub struct VerifyResponse {
    pub baseline: String,
    pub current: String,
    pub target_deltas: Vec<TargetDelta>,
    pub new_errors: Vec<ErrorDelta>,
    pub resolved_errors: Vec<ErrorDelta>,
    pub preexisting_errors: Vec<ErrorDelta>,
    pub named_range_deltas: Vec<NamedRangeDelta>,
    pub summary: VerifySummary,
}

#[derive(Debug, Clone, Default)]
pub struct VerifyOptions {
    pub targets: Vec<String>,
    pub sheet_filter: Option<String>,
    pub include_named_range_deltas: bool,
    pub errors_only: bool,
    pub targets_only: bool,
}

#[derive(Debug, Clone)]
struct TargetCellSnapshot {
    value: Option<CellValue>,
    formula: Option<String>,
}

#[derive(Debug, Clone)]
struct ErrorCellSnapshot {
    error: String,
    formula: Option<String>,
}

impl VerifyOptions {
    pub fn validate(&self) -> Result<()> {
        if self.errors_only && self.targets_only {
            bail!("invalid argument: --errors-only and --targets-only cannot be combined");
        }
        if self.errors_only && !self.targets.is_empty() {
            bail!(
                "invalid argument: --errors-only cannot be combined with explicit --targets; drop --targets or use default verify mode"
            );
        }
        if self.errors_only && self.include_named_range_deltas {
            bail!(
                "invalid argument: --errors-only cannot be combined with --named-ranges; use default verify mode to include named-range deltas"
            );
        }
        if self.targets_only && self.targets.is_empty() {
            bail!(
                "invalid argument: --targets-only requires --targets Sheet!A1,... to define the proof scope"
            );
        }
        if self.targets_only && self.include_named_range_deltas {
            bail!(
                "invalid argument: --targets-only cannot be combined with --named-ranges; use default verify mode to include named-range deltas"
            );
        }
        Ok(())
    }
}

pub fn compare_workbooks(
    baseline_label: impl Into<String>,
    current_label: impl Into<String>,
    baseline: &WorkbookContext,
    current: &WorkbookContext,
    options: &VerifyOptions,
    baseline_named_ranges: Option<&[NamedRangeDescriptor]>,
    current_named_ranges: Option<&[NamedRangeDescriptor]>,
) -> Result<VerifyResponse> {
    options.validate()?;

    let target_deltas = if options.errors_only {
        Vec::new()
    } else {
        collect_target_deltas(baseline, current, options.targets.clone())?
    };

    let (new_errors, resolved_errors, preexisting_errors) = if options.targets_only {
        (Vec::new(), Vec::new(), Vec::new())
    } else {
        let baseline_errors = collect_error_cells(baseline, options.sheet_filter.as_deref())?;
        let current_errors = collect_error_cells(current, options.sheet_filter.as_deref())?;
        compare_error_maps(&baseline_errors, &current_errors)
    };

    let named_range_deltas = if options.include_named_range_deltas {
        let baseline_named_ranges = baseline_named_ranges.ok_or_else(|| {
            anyhow!("internal error: baseline named ranges were not loaded for verification")
        })?;
        let current_named_ranges = current_named_ranges.ok_or_else(|| {
            anyhow!("internal error: current named ranges were not loaded for verification")
        })?;
        compare_named_ranges(baseline_named_ranges, current_named_ranges)
    } else {
        Vec::new()
    };

    let target_classification_counts = count_target_classifications(&target_deltas);
    Ok(VerifyResponse {
        baseline: baseline_label.into(),
        current: current_label.into(),
        summary: VerifySummary {
            target_count: target_deltas.len() as u32,
            changed_targets: target_deltas.iter().filter(|d| d.changed).count() as u32,
            new_error_count: new_errors.len() as u32,
            resolved_error_count: resolved_errors.len() as u32,
            preexisting_error_count: preexisting_errors.len() as u32,
            named_range_delta_count: named_range_deltas.len() as u32,
            target_classification_counts,
        },
        target_deltas,
        new_errors,
        resolved_errors,
        preexisting_errors,
        named_range_deltas,
    })
}

fn count_target_classifications(target_deltas: &[TargetDelta]) -> TargetClassificationCounts {
    let mut counts = TargetClassificationCounts::default();
    for delta in target_deltas {
        match delta.classification.as_str() {
            "unchanged" => counts.unchanged += 1,
            "direct_edit" => counts.direct_edit += 1,
            "recalc_result" => counts.recalc_result += 1,
            "formula_shift" => counts.formula_shift += 1,
            "new_error" => counts.new_error += 1,
            _ => {}
        }
    }
    counts
}

fn collect_target_deltas(
    baseline: &WorkbookContext,
    current: &WorkbookContext,
    targets: Vec<String>,
) -> Result<Vec<TargetDelta>> {
    let mut deltas = Vec::new();
    for target in targets {
        let (sheet_name, cell_ref) = parse_sheet_cell_ref(&target)?;
        let before = read_target_cell(baseline, &sheet_name, &cell_ref)?;
        let after = read_target_cell(current, &sheet_name, &cell_ref)?;
        let changed = !cell_values_equal(before.value.as_ref(), after.value.as_ref())
            || before.formula != after.formula;
        let classification = classify_target_delta(&before, &after, changed).to_string();
        deltas.push(TargetDelta {
            address: target,
            before: before.value,
            after: after.value,
            before_formula: before.formula,
            after_formula: after.formula,
            classification,
            changed,
        });
    }
    Ok(deltas)
}

fn parse_sheet_cell_ref(raw: &str) -> Result<(String, String)> {
    let (sheet_name, cell_ref) = raw.rsplit_once('!').ok_or_else(|| {
        anyhow!(
            "invalid argument: target '{}' must use Sheet!A1 notation",
            raw
        )
    })?;
    if sheet_name.trim().is_empty() || cell_ref.trim().is_empty() {
        bail!(
            "invalid argument: target '{}' must use Sheet!A1 notation",
            raw
        );
    }

    let sheet_name = extract_sheet_name(sheet_name);
    let cell_ref = parse_target_cell_ref(raw, cell_ref)?;
    Ok((sheet_name, cell_ref))
}

fn extract_sheet_name(raw: &str) -> String {
    let trimmed = raw.trim();
    if let Some(stripped) = trimmed.strip_prefix('\'')
        && let Some(inner) = stripped.strip_suffix('\'')
    {
        return inner.replace("''", "'");
    }
    trimmed.to_string()
}

fn parse_target_cell_ref(target: &str, raw_cell_ref: &str) -> Result<String> {
    let cell_ref = raw_cell_ref.trim();
    let (col, row, _, _) = umya_spreadsheet::helper::coordinate::index_from_coordinate(cell_ref);
    match (col, row) {
        (Some(c), Some(r)) if c > 0 && r > 0 => Ok(cell_ref.to_string()),
        _ => bail!(
            "invalid argument: target '{}' must use Sheet!A1 notation with a single A1 cell reference",
            target
        ),
    }
}

fn read_target_cell(
    workbook: &WorkbookContext,
    sheet_name: &str,
    cell_ref: &str,
) -> Result<TargetCellSnapshot> {
    workbook.with_sheet(sheet_name, |sheet| {
        if let Some(cell) = sheet.get_cell(cell_ref) {
            let formula = non_empty_formula(cell.get_formula());
            TargetCellSnapshot {
                value: cell_to_value(cell),
                formula,
            }
        } else {
            TargetCellSnapshot {
                value: None,
                formula: None,
            }
        }
    })
}

fn collect_error_cells(
    workbook: &WorkbookContext,
    sheet_filter: Option<&str>,
) -> Result<BTreeMap<String, ErrorCellSnapshot>> {
    let mut out = BTreeMap::new();
    let sheet_names = if let Some(sheet_name) = sheet_filter {
        vec![sheet_name.to_string()]
    } else {
        workbook.sheet_names()
    };

    for sheet_name in sheet_names {
        let sheet_errors = workbook.with_sheet(&sheet_name, |sheet| {
            let mut items = Vec::new();
            for cell in sheet.get_cell_collection() {
                let raw = cell.get_value();
                if !is_error_text(&raw) {
                    continue;
                }
                let address = format!("{}!{}", sheet_name, cell.get_coordinate().get_coordinate());
                items.push((
                    address,
                    ErrorCellSnapshot {
                        error: raw.to_string(),
                        formula: non_empty_formula(cell.get_formula()),
                    },
                ));
            }
            items
        })?;
        for (address, snapshot) in sheet_errors {
            out.insert(address, snapshot);
        }
    }
    Ok(out)
}

fn compare_error_maps(
    baseline: &BTreeMap<String, ErrorCellSnapshot>,
    current: &BTreeMap<String, ErrorCellSnapshot>,
) -> (Vec<ErrorDelta>, Vec<ErrorDelta>, Vec<ErrorDelta>) {
    let mut new_errors = Vec::new();
    let mut resolved_errors = Vec::new();
    let mut preexisting_errors = Vec::new();

    for (address, after) in current {
        if let Some(before) = baseline.get(address) {
            preexisting_errors.push(ErrorDelta {
                address: address.clone(),
                before_error: Some(before.error.clone()),
                after_error: Some(after.error.clone()),
                before_formula: before.formula.clone(),
                after_formula: after.formula.clone(),
            });
        } else {
            new_errors.push(ErrorDelta {
                address: address.clone(),
                before_error: None,
                after_error: Some(after.error.clone()),
                before_formula: None,
                after_formula: after.formula.clone(),
            });
        }
    }

    for (address, before) in baseline {
        if !current.contains_key(address) {
            resolved_errors.push(ErrorDelta {
                address: address.clone(),
                before_error: Some(before.error.clone()),
                after_error: None,
                before_formula: before.formula.clone(),
                after_formula: None,
            });
        }
    }

    (new_errors, resolved_errors, preexisting_errors)
}

fn compare_named_ranges(
    baseline: &[NamedRangeDescriptor],
    current: &[NamedRangeDescriptor],
) -> Vec<NamedRangeDelta> {
    let base_map: BTreeMap<String, &NamedRangeDescriptor> = baseline
        .iter()
        .map(|item| (named_range_key(item), item))
        .collect();
    let current_map: BTreeMap<String, &NamedRangeDescriptor> = current
        .iter()
        .map(|item| (named_range_key(item), item))
        .collect();

    let keys: BTreeSet<String> = base_map
        .keys()
        .cloned()
        .chain(current_map.keys().cloned())
        .collect();

    let mut deltas = Vec::new();
    for key in keys {
        let before = base_map.get(&key).copied();
        let after = current_map.get(&key).copied();
        match (before, after) {
            (Some(b), Some(a)) if b.refers_to != a.refers_to || b.kind != a.kind => {
                deltas.push(NamedRangeDelta {
                    name: a.name.clone(),
                    scope_kind: a.scope_kind,
                    scope_sheet_name: a.scope_sheet_name.clone(),
                    change: "changed".to_string(),
                    before_refers_to: Some(b.refers_to.clone()),
                    after_refers_to: Some(a.refers_to.clone()),
                    before_kind: Some(b.kind.clone()),
                    after_kind: Some(a.kind.clone()),
                });
            }
            (Some(b), None) => {
                deltas.push(NamedRangeDelta {
                    name: b.name.clone(),
                    scope_kind: b.scope_kind,
                    scope_sheet_name: b.scope_sheet_name.clone(),
                    change: "removed".to_string(),
                    before_refers_to: Some(b.refers_to.clone()),
                    after_refers_to: None,
                    before_kind: Some(b.kind.clone()),
                    after_kind: None,
                });
            }
            (None, Some(a)) => {
                deltas.push(NamedRangeDelta {
                    name: a.name.clone(),
                    scope_kind: a.scope_kind,
                    scope_sheet_name: a.scope_sheet_name.clone(),
                    change: "added".to_string(),
                    before_refers_to: None,
                    after_refers_to: Some(a.refers_to.clone()),
                    before_kind: None,
                    after_kind: Some(a.kind.clone()),
                });
            }
            _ => {}
        }
    }

    deltas
}

fn named_range_key(item: &NamedRangeDescriptor) -> String {
    format!(
        "{}|{:?}|{}|{:?}",
        item.name,
        item.scope_kind,
        item.scope_sheet_name.as_deref().unwrap_or(""),
        item.kind
    )
}

fn classify_target_delta(
    before: &TargetCellSnapshot,
    after: &TargetCellSnapshot,
    changed: bool,
) -> &'static str {
    if !changed {
        return "unchanged";
    }
    if is_error_value(after.value.as_ref()) && !is_error_value(before.value.as_ref()) {
        return "new_error";
    }
    if before.formula != after.formula {
        return "formula_shift";
    }
    if before.formula.is_none() && after.formula.is_none() {
        return "direct_edit";
    }
    "recalc_result"
}

fn is_error_value(value: Option<&CellValue>) -> bool {
    match value {
        Some(CellValue::Error(_)) => true,
        Some(other) => serde_json::to_value(other)
            .ok()
            .and_then(|json| {
                json.get("value")
                    .and_then(|v| v.as_str())
                    .map(is_error_text)
            })
            .unwrap_or(false),
        None => false,
    }
}

fn cell_values_equal(left: Option<&CellValue>, right: Option<&CellValue>) -> bool {
    match (left, right) {
        (None, None) => true,
        (Some(l), Some(r)) => serde_json::to_value(l).ok() == serde_json::to_value(r).ok(),
        _ => false,
    }
}

fn non_empty_formula(raw: &str) -> Option<String> {
    let trimmed = raw.trim();
    (!trimmed.is_empty()).then(|| trimmed.to_string())
}

fn is_error_text(raw: &str) -> bool {
    let upper = raw.trim().to_ascii_uppercase();
    matches!(
        upper.as_str(),
        "#DIV/0!"
            | "#VALUE!"
            | "#NAME?"
            | "#REF!"
            | "#N/A"
            | "#NULL!"
            | "#NUM!"
            | "#SPILL!"
            | "#CALC!"
            | "#BUSY!"
            | "#FIELD!"
            | "#UNKNOWN!"
    )
}