mollendorff-forge 10.0.0-beta.8

//! Formula translation from YAML to Excel syntax

use crate::error::{ForgeError, ForgeResult};
use regex::Regex;
use std::collections::HashMap;

/// Translates YAML row formulas to Excel cell formulas
pub struct FormulaTranslator {
    /// Maps column names to Excel column letters (revenue → A, cogs → B, etc.)
    column_map: HashMap<String, String>,
    /// Global mapping: `table_name` -> (`column_name` -> `column_letter`)
    table_column_maps: HashMap<String, HashMap<String, String>>,
    /// Global mapping: `table_name` -> `row_count`
    table_row_counts: HashMap<String, usize>,
}

impl FormulaTranslator {
    /// Create a new formula translator with column mappings (legacy, for backwards compat)
    #[must_use]
    pub fn new(column_map: HashMap<String, String>) -> Self {
        Self {
            column_map,
            table_column_maps: HashMap::new(),
            table_row_counts: HashMap::new(),
        }
    }

    /// Create a new formula translator with full table knowledge
    #[must_use]
    pub const fn new_with_tables(
        column_map: HashMap<String, String>,
        table_column_maps: HashMap<String, HashMap<String, String>>,
        table_row_counts: HashMap<String, usize>,
    ) -> Self {
        Self {
            column_map,
            table_column_maps,
            table_row_counts,
        }
    }

    /// Translate a row formula to an Excel cell formula for a specific row
    ///
    /// Example:
    /// - Input: `=revenue - cogs`, `row_idx` 0, `excel_row` 2
    /// - Output: `=A2-B2`
    ///
    /// # Errors
    ///
    /// Returns an error if a column reference cannot be resolved.
    ///
    /// # Panics
    ///
    /// Panics if a regex capture group 0 is missing (should never occur).
    #[allow(clippy::too_many_lines)] // splitting hurts readability: single coherent translation pipeline
    pub fn translate_row_formula(&self, formula: &str, excel_row: u32) -> ForgeResult<String> {
        // Remove leading = if present
        let formula_body = formula.strip_prefix('=').unwrap_or(formula);

        let mut result = formula_body.to_string();

        // FIRST: Handle table.column references in aggregation functions that need ranges
        // Pattern for simple aggregations: SUM(table.column) → SUM(table!A2:A4)
        let agg_pattern = Regex::new(r"(SUM|AVERAGE|MAX|MIN|COUNT|COUNTA|PRODUCT)\(([a-zA-Z_][a-zA-Z0-9_]*)\.([a-zA-Z_][a-zA-Z0-9_]*)\)")
            .map_err(|e| ForgeError::Export(format!("Regex error: {e}")))?;

        let agg_replacements: Vec<(std::ops::Range<usize>, String)> = agg_pattern
            .captures_iter(&result.clone())
            .map(|cap| {
                let full_match = cap.get(0).unwrap();
                let func_name = &cap[1];
                let table_name = &cap[2];
                let col_name = &cap[3];

                let col_letter = self
                    .table_column_maps
                    .get(table_name)
                    .and_then(|cols| cols.get(col_name))
                    .cloned()
                    .unwrap_or_else(|| col_name.to_string());

                let row_count = self.table_row_counts.get(table_name).copied().unwrap_or(1);
                let end_row = row_count + 1;
                let replacement =
                    format!("{func_name}('{table_name}'!{col_letter}2:{col_letter}{end_row})");
                (full_match.range(), replacement)
            })
            .collect();

        for (range, replacement) in agg_replacements.into_iter().rev() {
            result.replace_range(range, &replacement);
        }

        // SECOND: Handle general table.column references that need ranges (for functions like SUMIFS, PERCENTILE, etc.)
        let general_table_pattern =
            Regex::new(r"\b([a-zA-Z_][a-zA-Z0-9_]*)\.([a-zA-Z_][a-zA-Z0-9_]*)\b")
                .map_err(|e| ForgeError::Export(format!("Regex error: {e}")))?;

        let general_replacements: Vec<(std::ops::Range<usize>, String)> = general_table_pattern
            .captures_iter(&result.clone())
            .filter_map(|cap| {
                let full_match = cap.get(0).unwrap();
                let table_name = &cap[1];
                let col_name = &cap[2];

                // Skip if already processed (would contain !)
                if result[..full_match.start()].ends_with('\'')
                    || result[full_match.end()..].starts_with('!')
                {
                    return None;
                }

                // Only convert if this is a known table
                if !self.table_column_maps.contains_key(table_name) {
                    return None;
                }

                let col_letter = self
                    .table_column_maps
                    .get(table_name)
                    .and_then(|cols| cols.get(col_name))
                    .cloned()
                    .unwrap_or_else(|| col_name.to_string());

                let row_count = self.table_row_counts.get(table_name).copied().unwrap_or(1);
                let end_row = row_count + 1;
                let replacement = format!("'{table_name}'!{col_letter}2:{col_letter}{end_row}");
                Some((full_match.range(), replacement))
            })
            .collect();

        for (range, replacement) in general_replacements.into_iter().rev() {
            result.replace_range(range, &replacement);
        }

        // THIRD: Handle remaining table.column references (not in known tables, use fallback)
        let remaining_table_pattern =
            Regex::new(r"([a-zA-Z_][a-zA-Z0-9_]*)\.([a-zA-Z_][a-zA-Z0-9_]*)")
                .map_err(|e| ForgeError::Export(format!("Regex error: {e}")))?;

        let remaining_replacements: Vec<(std::ops::Range<usize>, String)> = remaining_table_pattern
            .captures_iter(&result.clone())
            .filter_map(|cap| {
                let full_match = cap.get(0).unwrap();
                let table_name = &cap[1];
                let col_name = &cap[2];

                // Skip if already processed
                if result[..full_match.start()].ends_with('\'')
                    || result[full_match.end()..].starts_with('!')
                {
                    return None;
                }

                // Fallback: convert to single cell reference with table name and column name
                let replacement = format!("'{table_name}'!{col_name}{excel_row}");
                Some((full_match.range(), replacement))
            })
            .collect();

        for (range, replacement) in remaining_replacements.into_iter().rev() {
            result.replace_range(range, &replacement);
        }

        // FOURTH: Handle simple column references in the current table
        let var_pattern = Regex::new(r"\b([a-zA-Z_][a-zA-Z0-9_]*)\b")
            .map_err(|e| ForgeError::Export(format!("Regex error: {e}")))?;

        let result_clone = result.clone();
        let matches: Vec<_> = var_pattern.find_iter(&result_clone).collect();

        for match_obj in matches.iter().rev() {
            let var_name = match_obj.as_str();

            // Skip Excel functions
            if Self::is_excel_function(var_name) {
                continue;
            }

            // Skip if already contains ! (already processed as table reference)
            let start = match_obj.start();
            let end = match_obj.end();
            if start > 0 && result[..start].ends_with('!') {
                continue;
            }

            // Skip if inside single quotes (already processed as sheet name)
            if start > 0 && result[..start].ends_with('\'') {
                continue;
            }
            if end < result.len() && result[end..].starts_with('!') {
                continue;
            }

            // Simple column reference in current table
            if let Some(col_letter) = self.column_map.get(var_name) {
                let excel_ref = format!("{col_letter}{excel_row}");
                result.replace_range(match_obj.range(), &excel_ref);
            } else {
                // Check if this looks like an Excel cell reference (e.g., A6, B2)
                let looks_like_cell_ref = var_name.len() >= 2
                    && var_name.chars().all(char::is_alphanumeric)
                    && var_name.chars().any(char::is_alphabetic)
                    && var_name.chars().any(char::is_numeric);

                // Check if it's a number
                let is_number = var_name.parse::<f64>().is_ok();

                if !looks_like_cell_ref && !is_number {
                    // Column not found and it's not a cell reference or number - this is an error
                    return Err(ForgeError::Export(format!(
                        "Column '{var_name}' not found in table"
                    )));
                }
                // If it's a cell reference or number, leave it as is
            }
        }

        Ok(format!("={result}"))
    }

    /// Check if a word is an Excel function
    #[allow(clippy::too_many_lines)] // comprehensive function list is intentionally exhaustive
    fn is_excel_function(word: &str) -> bool {
        let upper = word.to_uppercase();
        matches!(
            upper.as_str(),
            // Aggregation functions
            "SUM"
                | "AVERAGE"
                | "MAX"
                | "MIN"
                | "COUNT"
                | "COUNTA"
                | "PRODUCT"
                | "MEDIAN"
                // Conditional aggregations
                | "SUMIF"
                | "SUMIFS"
                | "COUNTIF"
                | "COUNTIFS"
                | "AVERAGEIF"
                | "AVERAGEIFS"
                | "MAXIFS"
                | "MINIFS"
                // Logical functions
                | "IF"
                | "AND"
                | "OR"
                | "NOT"
                | "XOR"
                | "TRUE"
                | "FALSE"
                | "IFERROR"
                | "IFNA"
                | "CHOOSE"
                // Math functions
                | "ABS"
                | "ROUND"
                | "ROUNDUP"
                | "ROUNDDOWN"
                | "SQRT"
                | "POW"
                | "POWER"
                | "EXP"
                | "LN"
                | "LOG"
                | "LOG10"
                | "PI"
                | "E"
                | "MOD"
                | "CEILING"
                | "FLOOR"
                | "INT"
                | "TRUNC"
                | "SIGN"
                // Trigonometric functions
                | "SIN"
                | "COS"
                | "TAN"
                | "ASIN"
                | "ACOS"
                | "ATAN"
                | "SINH"
                | "COSH"
                | "TANH"
                | "RADIANS"
                | "DEGREES"
                // Text functions
                | "CONCATENATE"
                | "CONCAT"
                | "LEFT"
                | "RIGHT"
                | "MID"
                | "LEN"
                | "UPPER"
                | "LOWER"
                | "TRIM"
                | "TEXT"
                | "VALUE"
                | "FIND"
                | "SEARCH"
                | "REPLACE"
                | "SUBSTITUTE"
                // Date functions
                | "TODAY"
                | "NOW"
                | "DATE"
                | "YEAR"
                | "MONTH"
                | "DAY"
                | "WEEKDAY"
                | "HOUR"
                | "MINUTE"
                | "SECOND"
                | "DATEDIF"
                | "EDATE"
                | "EOMONTH"
                | "NETWORKDAYS"
                | "WORKDAY"
                | "YEARFRAC"
                | "DAYS"
                | "TIME"
                // Financial functions
                | "NPV"
                | "IRR"
                | "MIRR"
                | "XNPV"
                | "XIRR"
                | "PMT"
                | "FV"
                | "PV"
                | "RATE"
                | "NPER"
                | "SLN"
                | "DB"
                | "DDB"
                // Information functions
                | "ISEVEN"
                | "ISODD"
                | "ISBLANK"
                | "ISERROR"
                | "ISNA"
                | "ISNUMBER"
                | "ISTEXT"
                | "ISLOGICAL"
                | "ISREF"
                | "ISFORMULA"
                | "NA"
                | "TYPE"
                | "N"
                // Lookup functions
                | "VLOOKUP"
                | "HLOOKUP"
                | "XLOOKUP"
                | "INDEX"
                | "MATCH"
                | "OFFSET"
                | "INDIRECT"
                | "ADDRESS"
                | "ROW"
                | "COLUMN"
                | "ROWS"
                | "COLUMNS"
                // Array functions (v4.1.0)
                | "UNIQUE"
                | "COUNTUNIQUE"
                | "SORT"
                | "FILTER"
                | "SEQUENCE"
                | "RANDARRAY"
                // Statistical functions
                | "VAR"
                | "VARP"
                | "STDEV"
                | "STDEVP"
                | "CORREL"
                | "PERCENTILE"
                | "QUARTILE"
                | "LARGE"
                | "SMALL"
                | "RANK"
                // Advanced functions
                | "LET"
                | "LAMBDA"
                | "SWITCH"
                | "IFS"
        )
    }

    /// Translate a scalar formula to an Excel formula
    ///
    /// Examples:
    /// - `=SUM(table.column)` → `=SUM(table!A2:A4)`
    /// - `=table.column[0]` → `=table!A2`
    /// - `=scalar_name / 100` → `=B3 / 100`
    ///
    /// # Errors
    ///
    /// Returns an error if a regex compilation fails.
    ///
    /// # Panics
    ///
    /// Panics if a regex capture group 0 is missing (should never occur).
    #[allow(clippy::too_many_lines)] // splitting hurts readability: single coherent translation pipeline
    pub fn translate_scalar_formula(
        &self,
        formula: &str,
        scalar_row_map: &HashMap<String, u32>,
    ) -> ForgeResult<String> {
        // Remove leading = if present
        let formula_body = formula.strip_prefix('=').unwrap_or(formula);

        let mut result = formula_body.to_string();

        // Pattern for table.column[index] references (must be processed first)
        let indexed_pattern =
            Regex::new(r"([a-zA-Z_][a-zA-Z0-9_]*)\.([a-zA-Z_][a-zA-Z0-9_]*)\[(\d+)\]")
                .map_err(|e| ForgeError::Export(format!("Regex error: {e}")))?;

        // Replace indexed references: table.column[0] → table!A2
        let indexed_replacements: Vec<(std::ops::Range<usize>, String)> = indexed_pattern
            .captures_iter(&result.clone())
            .map(|cap| {
                let full_match = cap.get(0).unwrap();
                let table_name = &cap[1];
                let col_name = &cap[2];
                let index: usize = cap[3].parse().unwrap_or(0);

                let col_letter = self
                    .table_column_maps
                    .get(table_name)
                    .and_then(|cols| cols.get(col_name))
                    .cloned()
                    .unwrap_or_else(|| col_name.to_string());

                // Excel row = index + 2 (1 for header, 1 for 1-indexing)
                let excel_row = index + 2;
                let replacement = format!("'{table_name}'!{col_letter}{excel_row}");
                (full_match.range(), replacement)
            })
            .collect();

        // Apply indexed replacements in reverse order
        for (range, replacement) in indexed_replacements.into_iter().rev() {
            result.replace_range(range, &replacement);
        }

        // Pattern for table.column references inside aggregation functions
        // SUM(table.column) → SUM(table!A2:A4)
        let agg_pattern = Regex::new(r"(SUM|AVERAGE|MAX|MIN|COUNT|COUNTA|PRODUCT)\(([a-zA-Z_][a-zA-Z0-9_]*)\.([a-zA-Z_][a-zA-Z0-9_]*)\)")
            .map_err(|e| ForgeError::Export(format!("Regex error: {e}")))?;

        let agg_replacements: Vec<(std::ops::Range<usize>, String)> = agg_pattern
            .captures_iter(&result.clone())
            .map(|cap| {
                let full_match = cap.get(0).unwrap();
                let func_name = &cap[1];
                let table_name = &cap[2];
                let col_name = &cap[3];

                let col_letter = self
                    .table_column_maps
                    .get(table_name)
                    .and_then(|cols| cols.get(col_name))
                    .cloned()
                    .unwrap_or_else(|| col_name.to_string());

                let row_count = self.table_row_counts.get(table_name).copied().unwrap_or(1);
                // Range: row 2 to row (row_count + 1) - header is row 1
                let end_row = row_count + 1;
                let replacement =
                    format!("{func_name}('{table_name}'!{col_letter}2:{col_letter}{end_row})");
                (full_match.range(), replacement)
            })
            .collect();

        // Apply aggregation replacements in reverse order
        for (range, replacement) in agg_replacements.into_iter().rev() {
            result.replace_range(range, &replacement);
        }

        // Pattern for general table.column references that need range conversion
        // This handles functions like SUMIFS, AVERAGEIFS, PERCENTILE, QUARTILE, RANK, CORREL, etc.
        // that weren't caught by the simple aggregation pattern above
        let general_table_pattern =
            Regex::new(r"\b([a-zA-Z_][a-zA-Z0-9_]*)\.([a-zA-Z_][a-zA-Z0-9_]*)\b")
                .map_err(|e| ForgeError::Export(format!("Regex error: {e}")))?;

        // Collect all table.column references that should be converted to ranges
        let general_replacements: Vec<(std::ops::Range<usize>, String)> = general_table_pattern
            .captures_iter(&result.clone())
            .filter_map(|cap| {
                let full_match = cap.get(0).unwrap();
                let table_name = &cap[1];
                let col_name = &cap[2];

                // Skip if already processed (contains !)
                if result[..full_match.start()].ends_with('\'')
                    || result[full_match.end()..].starts_with('!')
                {
                    return None;
                }

                // Only convert if this is a known table (not a scalar reference)
                if !self.table_column_maps.contains_key(table_name) {
                    return None;
                }

                let col_letter = self
                    .table_column_maps
                    .get(table_name)
                    .and_then(|cols| cols.get(col_name))
                    .cloned()
                    .unwrap_or_else(|| col_name.to_string());

                let row_count = self.table_row_counts.get(table_name).copied().unwrap_or(1);
                // Range: row 2 to row (row_count + 1) - header is row 1
                let end_row = row_count + 1;
                let replacement = format!("'{table_name}'!{col_letter}2:{col_letter}{end_row}");
                Some((full_match.range(), replacement))
            })
            .collect();

        // Apply general replacements in reverse order
        for (range, replacement) in general_replacements.into_iter().rev() {
            result.replace_range(range, &replacement);
        }

        // Pattern for simple table.column references (not in aggregations, not indexed)
        // These become references to row 2 (first data row)
        let simple_table_pattern =
            Regex::new(r"([a-zA-Z_][a-zA-Z0-9_]*)\.([a-zA-Z_][a-zA-Z0-9_]*)")
                .map_err(|e| ForgeError::Export(format!("Regex error: {e}")))?;

        let simple_replacements: Vec<(std::ops::Range<usize>, String)> = simple_table_pattern
            .captures_iter(&result.clone())
            .filter_map(|cap| {
                let full_match = cap.get(0).unwrap();
                let table_name = &cap[1];
                let col_name = &cap[2];

                // Skip if this looks like it's already been processed (contains !)
                if result[full_match.range()].contains('!') {
                    return None;
                }

                // Check if this is actually a table reference
                if !self.table_column_maps.contains_key(table_name) {
                    // Could be a scalar like metrics.total_savings
                    // Check if it's a scalar reference
                    let scalar_name = format!("{table_name}.{col_name}");
                    if let Some(&row) = scalar_row_map.get(&scalar_name) {
                        return Some((full_match.range(), format!("B{row}")));
                    }
                    return None;
                }

                let col_letter = self
                    .table_column_maps
                    .get(table_name)
                    .and_then(|cols| cols.get(col_name))
                    .cloned()
                    .unwrap_or_else(|| col_name.to_string());

                // Default to row 2 (first data row)
                let replacement = format!("'{table_name}'!{col_letter}2");
                Some((full_match.range(), replacement))
            })
            .collect();

        // Apply simple replacements in reverse order
        for (range, replacement) in simple_replacements.into_iter().rev() {
            result.replace_range(range, &replacement);
        }

        // Handle scalar-to-scalar references (e.g., metrics.total_savings → B3)
        // Pattern for standalone scalar names
        let scalar_pattern = Regex::new(r"\b([a-zA-Z_][a-zA-Z0-9_]*\.[a-zA-Z_][a-zA-Z0-9_]*)\b")
            .map_err(|e| ForgeError::Export(format!("Regex error: {e}")))?;

        let scalar_replacements: Vec<(std::ops::Range<usize>, String)> = scalar_pattern
            .captures_iter(&result.clone())
            .filter_map(|cap| {
                let full_match = cap.get(0).unwrap();
                let scalar_name = &cap[1];

                // Skip if already processed (contains ! or is a number)
                if result[full_match.range()].contains('!') {
                    return None;
                }

                if let Some(&row) = scalar_row_map.get(scalar_name) {
                    return Some((full_match.range(), format!("B{row}")));
                }
                None
            })
            .collect();

        // Apply scalar replacements in reverse order
        for (range, replacement) in scalar_replacements.into_iter().rev() {
            result.replace_range(range, &replacement);
        }

        Ok(format!("={result}"))
    }

    /// Convert a column name to an Excel column letter
    ///
    /// Examples:
    /// - 0 → A
    /// - 1 → B
    /// - 25 → Z
    /// - 26 → AA
    #[must_use]
    pub fn column_index_to_letter(index: usize) -> String {
        let mut result = String::new();
        let mut idx = index;

        loop {
            let remainder = idx % 26;
            // Remainder is always 0..25, fits in u8
            #[allow(clippy::cast_possible_truncation)] // remainder is 0..25, always fits in u8
            let ch = (b'A' + remainder as u8) as char;
            result.insert(0, ch);
            if idx < 26 {
                break;
            }
            idx = idx / 26 - 1;
        }

        result
    }
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn test_column_index_to_letter() {
        assert_eq!(FormulaTranslator::column_index_to_letter(0), "A");
        assert_eq!(FormulaTranslator::column_index_to_letter(1), "B");
        assert_eq!(FormulaTranslator::column_index_to_letter(25), "Z");
        assert_eq!(FormulaTranslator::column_index_to_letter(26), "AA");
        assert_eq!(FormulaTranslator::column_index_to_letter(27), "AB");
        assert_eq!(FormulaTranslator::column_index_to_letter(701), "ZZ");
    }

    #[test]
    fn test_simple_formula_translation() {
        let mut column_map = HashMap::new();
        column_map.insert("revenue".to_string(), "A".to_string());
        column_map.insert("cogs".to_string(), "B".to_string());

        let translator = FormulaTranslator::new(column_map);

        // Test simple subtraction (row 2 in Excel)
        let result = translator
            .translate_row_formula("=revenue - cogs", 2)
            .unwrap();
        assert_eq!(result, "=A2 - B2");

        // Test division (row 3 in Excel)
        let result = translator
            .translate_row_formula("=revenue / cogs", 3)
            .unwrap();
        assert_eq!(result, "=A3 / B3");
    }

    #[test]
    fn test_formula_with_multiple_columns() {
        let mut column_map = HashMap::new();
        column_map.insert("sales_marketing".to_string(), "A".to_string());
        column_map.insert("rd".to_string(), "B".to_string());
        column_map.insert("ga".to_string(), "C".to_string());

        let translator = FormulaTranslator::new(column_map);

        let result = translator
            .translate_row_formula("=sales_marketing + rd + ga", 2)
            .unwrap();
        assert_eq!(result, "=A2 + B2 + C2");
    }

    #[test]
    fn test_formula_with_parentheses() {
        let mut column_map = HashMap::new();
        column_map.insert("gross_profit".to_string(), "A".to_string());
        column_map.insert("revenue".to_string(), "B".to_string());

        let translator = FormulaTranslator::new(column_map);

        let result = translator
            .translate_row_formula("=(gross_profit / revenue) * 100", 2)
            .unwrap();
        assert_eq!(result, "=(A2 / B2) * 100");
    }

    #[test]
    fn test_cross_table_reference() {
        let column_map = HashMap::new(); // Empty for this test

        let translator = FormulaTranslator::new(column_map);

        let result = translator
            .translate_row_formula("=pl_2025.revenue", 2)
            .unwrap();
        assert_eq!(result, "='pl_2025'!revenue2");
    }

    #[test]
    fn test_formula_without_leading_equals() {
        let mut column_map = HashMap::new();
        column_map.insert("revenue".to_string(), "A".to_string());
        column_map.insert("cogs".to_string(), "B".to_string());

        let translator = FormulaTranslator::new(column_map);

        // Test formula without leading =
        let result = translator
            .translate_row_formula("revenue - cogs", 2)
            .unwrap();
        assert_eq!(result, "=A2 - B2");
    }

    #[test]
    fn test_financial_functions_preserved() {
        let mut column_map = HashMap::new();
        column_map.insert("cashflow".to_string(), "A".to_string());

        let translator = FormulaTranslator::new(column_map);

        // Test that NPV, IRR, XNPV, XIRR are preserved as functions (use literals for other args)
        let result = translator
            .translate_row_formula("=NPV(0.1, cashflow)", 2)
            .unwrap();
        assert!(result.contains("NPV"));
        assert!(result.contains("A2")); // cashflow translated

        // Test XNPV with literals
        let result = translator
            .translate_row_formula("=XNPV(0.1, cashflow, 45000)", 2)
            .unwrap();
        assert!(result.contains("XNPV"));

        // Test PMT with literals
        let result = translator
            .translate_row_formula("=PMT(0.05, 12, 1000)", 2)
            .unwrap();
        assert!(result.contains("PMT"));

        // Test IRR, PV, FV, RATE, NPER
        let result = translator
            .translate_row_formula("=IRR(cashflow)", 2)
            .unwrap();
        assert!(result.contains("IRR"));

        let result = translator
            .translate_row_formula("=PV(0.1, 10, 100)", 2)
            .unwrap();
        assert!(result.contains("PV"));

        let result = translator
            .translate_row_formula("=FV(0.1, 10, 100)", 2)
            .unwrap();
        assert!(result.contains("FV"));
    }

    #[test]
    fn test_date_functions_preserved() {
        let column_map = HashMap::new();
        let translator = FormulaTranslator::new(column_map);

        // Test DATEDIF, EDATE, EOMONTH are preserved (use numeric literals only)
        let result = translator
            .translate_row_formula("=DATEDIF(45000, 45365, 1)", 2)
            .unwrap();
        assert!(result.contains("DATEDIF"));

        let result = translator
            .translate_row_formula("=EDATE(45000, 3)", 2)
            .unwrap();
        assert!(result.contains("EDATE"));

        let result = translator
            .translate_row_formula("=EOMONTH(45000, 1)", 2)
            .unwrap();
        assert!(result.contains("EOMONTH"));
    }

    #[test]
    fn test_other_new_functions_preserved() {
        let column_map = HashMap::new();
        let translator = FormulaTranslator::new(column_map);

        // Test CHOOSE, MAXIFS, MINIFS, POWER, CONCAT (use numeric literals only)
        let result = translator
            .translate_row_formula("=CHOOSE(1, 10, 20, 30)", 2)
            .unwrap();
        assert!(result.contains("CHOOSE"));

        let result = translator.translate_row_formula("=POWER(2, 8)", 2).unwrap();
        assert!(result.contains("POWER"));

        // CONCAT with numbers to avoid string parsing issues
        let result = translator
            .translate_row_formula("=CONCAT(1, 2)", 2)
            .unwrap();
        assert!(result.contains("CONCAT"));

        let result = translator
            .translate_row_formula("=MAXIFS(1, 2, 3)", 2)
            .unwrap();
        assert!(result.contains("MAXIFS"));

        let result = translator
            .translate_row_formula("=MINIFS(1, 2, 3)", 2)
            .unwrap();
        assert!(result.contains("MINIFS"));
    }

    // ═══════════════════════════════════════════════════════════════════════════
    // Additional coverage tests
    // ═══════════════════════════════════════════════════════════════════════════

    #[test]
    fn test_new_with_tables() {
        let column_map = HashMap::new();
        let mut table_column_maps = HashMap::new();
        let mut sales_cols = HashMap::new();
        sales_cols.insert("revenue".to_string(), "A".to_string());
        sales_cols.insert("cost".to_string(), "B".to_string());
        table_column_maps.insert("sales".to_string(), sales_cols);

        let mut table_row_counts = HashMap::new();
        table_row_counts.insert("sales".to_string(), 5);

        let translator =
            FormulaTranslator::new_with_tables(column_map, table_column_maps, table_row_counts);

        // Test cross-table reference with full knowledge
        let result = translator
            .translate_row_formula("=sales.revenue", 2)
            .unwrap();
        assert!(result.contains("sales"));
    }

    #[test]
    fn test_translate_scalar_formula_basic() {
        let mut table_column_maps = HashMap::new();
        let mut sales_cols = HashMap::new();
        sales_cols.insert("amount".to_string(), "A".to_string());
        table_column_maps.insert("sales".to_string(), sales_cols);

        let mut table_row_counts = HashMap::new();
        table_row_counts.insert("sales".to_string(), 3);

        let translator =
            FormulaTranslator::new_with_tables(HashMap::new(), table_column_maps, table_row_counts);

        let scalar_row_map = HashMap::new();

        // Test SUM aggregation
        let result = translator
            .translate_scalar_formula("=SUM(sales.amount)", &scalar_row_map)
            .unwrap();
        assert!(result.contains("SUM"));
        assert!(result.contains("sales"));
    }

    #[test]
    fn test_translate_scalar_formula_with_index() {
        let mut table_column_maps = HashMap::new();
        let mut data_cols = HashMap::new();
        data_cols.insert("value".to_string(), "C".to_string());
        table_column_maps.insert("data".to_string(), data_cols);

        let mut table_row_counts = HashMap::new();
        table_row_counts.insert("data".to_string(), 10);

        let translator =
            FormulaTranslator::new_with_tables(HashMap::new(), table_column_maps, table_row_counts);

        let scalar_row_map = HashMap::new();

        // Test indexed reference: data.value[0]
        let result = translator
            .translate_scalar_formula("=data.value[0]", &scalar_row_map)
            .unwrap();
        assert!(result.contains("data"));
        assert!(result.contains("C2")); // index 0 = row 2
    }

    #[test]
    fn test_translate_scalar_formula_with_scalar_ref() {
        let translator = FormulaTranslator::new(HashMap::new());

        let mut scalar_row_map = HashMap::new();
        scalar_row_map.insert("metrics.total".to_string(), 5);

        // Test scalar-to-scalar reference
        let result = translator
            .translate_scalar_formula("=metrics.total * 2", &scalar_row_map)
            .unwrap();
        assert!(result.contains("B5")); // scalar at row 5
    }

    #[test]
    fn test_column_not_found_error() {
        let column_map = HashMap::new(); // Empty
        let translator = FormulaTranslator::new(column_map);

        // Should error on unknown column
        let result = translator.translate_row_formula("=unknown_column + 1", 2);
        assert!(result.is_err());
        let err = result.unwrap_err().to_string();
        assert!(err.contains("not found"));
    }

    #[test]
    fn test_more_excel_functions_preserved() {
        let column_map = HashMap::new();
        let translator = FormulaTranslator::new(column_map);

        // Test more functions
        let functions = [
            "ABS", "ROUND", "CEILING", "FLOOR", "MOD", "SQRT", "EXP", "LN", "LOG", "LOG10",
        ];

        for func in functions {
            let formula = format!("={func}(10)");
            let result = translator.translate_row_formula(&formula, 2).unwrap();
            assert!(
                result.to_uppercase().contains(func),
                "Function {func} should be preserved"
            );
        }
    }

    #[test]
    fn test_text_functions_preserved() {
        let column_map = HashMap::new();
        let translator = FormulaTranslator::new(column_map);

        let functions = ["UPPER", "LOWER", "TRIM", "LEN", "LEFT", "RIGHT", "MID"];

        for func in functions {
            let formula = format!("={func}(1)"); // Using number to avoid string parsing
            let result = translator.translate_row_formula(&formula, 2).unwrap();
            assert!(
                result.to_uppercase().contains(func),
                "Function {func} should be preserved"
            );
        }
    }

    #[test]
    fn test_logical_functions_preserved() {
        let column_map = HashMap::new();
        let translator = FormulaTranslator::new(column_map);

        let functions = ["IF", "AND", "OR", "NOT", "TRUE", "FALSE", "IFERROR"];

        for func in functions {
            let formula = format!("={func}(1, 2, 3)");
            let result = translator.translate_row_formula(&formula, 2).unwrap();
            assert!(
                result.to_uppercase().contains(func),
                "Function {func} should be preserved"
            );
        }
    }

    #[test]
    fn test_column_index_to_letter_extended() {
        // Test more column letters
        assert_eq!(FormulaTranslator::column_index_to_letter(702), "AAA");
        assert_eq!(FormulaTranslator::column_index_to_letter(52), "BA");
    }

    #[test]
    fn test_is_excel_function() {
        // Test various function names
        assert!(FormulaTranslator::is_excel_function("SUM"));
        assert!(FormulaTranslator::is_excel_function("sum")); // lowercase
        assert!(FormulaTranslator::is_excel_function("AVERAGE"));
        assert!(FormulaTranslator::is_excel_function("NPV"));
        assert!(FormulaTranslator::is_excel_function("XLOOKUP"));
        assert!(!FormulaTranslator::is_excel_function("revenue")); // not a function
        assert!(!FormulaTranslator::is_excel_function("my_column")); // not a function
    }

    #[test]
    fn test_translate_sumifs_formula() {
        let mut table_column_maps = HashMap::new();
        let mut sales_cols = HashMap::new();
        sales_cols.insert("amount".to_string(), "A".to_string());
        sales_cols.insert("region".to_string(), "B".to_string());
        sales_cols.insert("product".to_string(), "C".to_string());
        table_column_maps.insert("sales_data".to_string(), sales_cols);

        let mut table_row_counts = HashMap::new();
        table_row_counts.insert("sales_data".to_string(), 6);

        let translator =
            FormulaTranslator::new_with_tables(HashMap::new(), table_column_maps, table_row_counts);

        let scalar_row_map = HashMap::new();

        // Test SUMIFS with multiple criteria
        let result = translator
            .translate_scalar_formula(
                "=SUMIFS(sales_data.amount, sales_data.region, 1, sales_data.product, 1)",
                &scalar_row_map,
            )
            .unwrap();

        println!("SUMIFS result: {result}");
        assert!(result.contains("'sales_data'!A2:A7"));
        assert!(result.contains("'sales_data'!B2:B7"));
        assert!(result.contains("'sales_data'!C2:C7"));
    }

    #[test]
    fn test_translate_percentile_formula() {
        let mut table_column_maps = HashMap::new();
        let mut dataset_cols = HashMap::new();
        dataset_cols.insert("values".to_string(), "A".to_string());
        table_column_maps.insert("dataset".to_string(), dataset_cols);

        let mut table_row_counts = HashMap::new();
        table_row_counts.insert("dataset".to_string(), 10);

        let translator =
            FormulaTranslator::new_with_tables(HashMap::new(), table_column_maps, table_row_counts);

        let scalar_row_map = HashMap::new();

        // Test PERCENTILE
        let result = translator
            .translate_scalar_formula("=PERCENTILE(dataset.values, 0.5)", &scalar_row_map)
            .unwrap();

        println!("PERCENTILE result: {result}");
        assert!(result.contains("'dataset'!A2:A11"));
    }

    #[test]
    fn test_translate_correl_formula() {
        let mut table_column_maps = HashMap::new();
        let mut data_cols = HashMap::new();
        data_cols.insert("advertising".to_string(), "A".to_string());
        data_cols.insert("sales".to_string(), "B".to_string());
        table_column_maps.insert("data_series".to_string(), data_cols);

        let mut table_row_counts = HashMap::new();
        table_row_counts.insert("data_series".to_string(), 5);

        let translator =
            FormulaTranslator::new_with_tables(HashMap::new(), table_column_maps, table_row_counts);

        let scalar_row_map = HashMap::new();

        // Test CORREL
        let result = translator
            .translate_scalar_formula(
                "=CORREL(data_series.advertising, data_series.sales)",
                &scalar_row_map,
            )
            .unwrap();

        println!("CORREL result: {result}");
        assert!(result.contains("'data_series'!A2:A6"));
        assert!(result.contains("'data_series'!B2:B6"));
    }
}