query-forge 0.9.0

use std::fs;

use anyhow::{Result, bail};

use crate::cli::{HeaderCase, JsonMode, PivotAggregation, PivotCommand, XmlMode};
use crate::input_spec::parse_input_specs;
use crate::output::{parse_output_path_spec, write_query_result};

pub(crate) struct PivotExecution {
    pub(crate) result: query_forge::QueryResult,
    pub(crate) pivot_sql: String,
    pub(crate) distinct_column_values: usize,
}

// ---------------------------------------------------------------------------
// Pivot query language parser
// ---------------------------------------------------------------------------

/// Parameters extracted from a parsed pivot query.
#[derive(Debug)]
pub(crate) struct PivotQuerySpec {
    /// Column used as row labels.
    pub(crate) rows: String,
    /// Column whose distinct values become output columns (optional).
    pub(crate) cols: Option<String>,
    /// Column to aggregate (optional for COUNT, required for SUM/AVG/MIN/MAX).
    pub(crate) values: Option<String>,
    /// Aggregation function.
    pub(crate) agg: PivotAggregation,
    /// Source table name referenced in the FROM clause.
    pub(crate) table_name: String,
}

/// Minimal token type used by the pivot SQL parser.
#[derive(Debug, PartialEq)]
enum PToken {
    Word(String),
    Quoted(String),
    LParen,
    RParen,
    Star,
}

impl std::fmt::Display for PToken {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        match self {
            PToken::Word(w) => write!(f, "{w}"),
            PToken::Quoted(q) => write!(f, "\"{q}\""),
            PToken::LParen => write!(f, "("),
            PToken::RParen => write!(f, ")"),
            PToken::Star => write!(f, "*"),
        }
    }
}

fn tokenize_pivot_sql(input: &str) -> Vec<PToken> {
    let mut tokens = Vec::new();
    let mut chars = input.chars().peekable();
    while let Some(&c) = chars.peek() {
        match c {
            c if c.is_whitespace() => {
                chars.next();
            }
            '(' => {
                chars.next();
                tokens.push(PToken::LParen);
            }
            ')' => {
                chars.next();
                tokens.push(PToken::RParen);
            }
            '*' => {
                chars.next();
                tokens.push(PToken::Star);
            }
            '"' => {
                chars.next(); // consume opening quote
                let mut s = String::new();
                loop {
                    match chars.next() {
                        None => break,
                        Some('"') => {
                            if chars.peek() == Some(&'"') {
                                // escaped double-quote
                                chars.next();
                                s.push('"');
                            } else {
                                break;
                            }
                        }
                        Some(ch) => s.push(ch),
                    }
                }
                tokens.push(PToken::Quoted(s));
            }
            c if c.is_alphanumeric() || c == '_' => {
                let mut word = String::new();
                while let Some(&c2) = chars.peek() {
                    if c2.is_alphanumeric() || c2 == '_' {
                        word.push(c2);
                        chars.next();
                    } else {
                        break;
                    }
                }
                tokens.push(PToken::Word(word));
            }
            _ => {
                chars.next(); // skip unrecognised characters (e.g. `;` at end of file)
            }
        }
    }
    tokens
}

struct PivotParser {
    tokens: Vec<PToken>,
    pos: usize,
}

impl PivotParser {
    fn new(tokens: Vec<PToken>) -> Self {
        Self { tokens, pos: 0 }
    }

    fn peek(&self) -> Option<&PToken> {
        self.tokens.get(self.pos)
    }

    fn next_token(&mut self) -> Option<&PToken> {
        let t = self.tokens.get(self.pos);
        if t.is_some() {
            self.pos += 1;
        }
        t
    }

    fn expect_keyword(&mut self, expected: &str) -> Result<()> {
        match self.next_token() {
            Some(PToken::Word(w)) if w.eq_ignore_ascii_case(expected) => Ok(()),
            Some(other) => bail!(
                "pivot query syntax error: expected keyword '{expected}', got '{other}'"
            ),
            None => bail!("pivot query syntax error: expected keyword '{expected}', reached end of input"),
        }
    }

    fn is_keyword(&self, expected: &str) -> bool {
        matches!(self.peek(), Some(PToken::Word(w)) if w.eq_ignore_ascii_case(expected))
    }

    fn parse_ident(&mut self) -> Result<String> {
        match self.next_token() {
            Some(PToken::Word(w)) => Ok(w.clone()),
            Some(PToken::Quoted(q)) => Ok(q.clone()),
            Some(other) => bail!("pivot query syntax error: expected identifier, got '{other}'"),
            None => bail!("pivot query syntax error: expected identifier, reached end of input"),
        }
    }

    fn at_end(&self) -> bool {
        self.pos >= self.tokens.len()
    }
}

/// Parses a pivot-specific SQL-like statement and returns a [`PivotQuerySpec`].
///
/// ## Syntax
///
/// ```text
/// PIVOT AGG_FN( col | * ) [ FOR cols_col ] FROM table_name GROUP BY rows_col
/// ```
///
/// `AGG_FN` must be one of `COUNT`, `SUM`, `AVG`, `MIN`, `MAX` (case-insensitive).
/// `*` may only be used with `COUNT`; all other functions require a column name.
/// `FOR cols_col` is optional — when omitted a single aggregated column is produced.
///
/// ## Examples
///
/// ```text
/// PIVOT COUNT(*) FROM table GROUP BY category
/// PIVOT SUM(stock) FOR active FROM table GROUP BY category
/// PIVOT AVG(price) FOR region FROM inventory GROUP BY product
/// ```
pub(crate) fn parse_pivot_query(sql: &str) -> Result<PivotQuerySpec> {
    let tokens = tokenize_pivot_sql(sql);
    let mut p = PivotParser::new(tokens);

    // PIVOT keyword
    p.expect_keyword("PIVOT")?;

    // AGG_FN
    let agg_name = p.parse_ident()?;
    let agg = match agg_name.to_uppercase().as_str() {
        "COUNT" => PivotAggregation::Count,
        "SUM" => PivotAggregation::Sum,
        "AVG" => PivotAggregation::Avg,
        "MIN" => PivotAggregation::Min,
        "MAX" => PivotAggregation::Max,
        other => bail!(
            "unknown aggregation function '{other}' in pivot query; \
             expected COUNT, SUM, AVG, MIN, or MAX"
        ),
    };

    // (
    match p.next_token() {
        Some(PToken::LParen) => {}
        Some(other) => bail!(
            "pivot query syntax error: expected '(' after '{agg_name}', got '{other}'"
        ),
        None => bail!(
            "pivot query syntax error: expected '(' after '{agg_name}', reached end of input"
        ),
    }

    // col | *
    let values: Option<String> = match p.peek() {
        Some(PToken::Star) => {
            p.next_token();
            None
        }
        Some(PToken::Word(_)) | Some(PToken::Quoted(_)) => Some(p.parse_ident()?),
        Some(other) => {
            // Need to format before consuming
            let msg = format!(
                "pivot query syntax error: expected column name or '*' inside {agg_name}(), got '{other}'"
            );
            bail!(msg);
        }
        None => bail!(
            "pivot query syntax error: expected column name or '*' inside {agg_name}(), reached end of input"
        ),
    };

    // )
    match p.next_token() {
        Some(PToken::RParen) => {}
        Some(other) => bail!(
            "pivot query syntax error: expected ')' after aggregation argument, got '{other}'"
        ),
        None => bail!(
            "pivot query syntax error: expected ')' after aggregation argument, reached end of input"
        ),
    }

    // Validate: non-count agg must have a column, not *
    if matches!(
        agg,
        PivotAggregation::Sum | PivotAggregation::Avg | PivotAggregation::Min | PivotAggregation::Max
    ) && values.is_none()
    {
        bail!(
            "aggregation function '{agg_name}' requires a column name inside the parentheses, not '*'"
        );
    }

    // Optional FOR clause
    let cols: Option<String> = if p.is_keyword("FOR") {
        p.next_token(); // consume FOR
        Some(p.parse_ident()?)
    } else {
        None
    };

    // FROM
    p.expect_keyword("FROM")?;

    // table_name
    let table_name = p.parse_ident()?;

    // GROUP BY
    p.expect_keyword("GROUP")?;
    p.expect_keyword("BY")?;

    // rows col
    let rows = p.parse_ident()?;

    // Ensure no unexpected trailing tokens
    if !p.at_end() {
        bail!(
            "pivot query syntax error: unexpected tokens after GROUP BY clause"
        );
    }

    Ok(PivotQuerySpec {
        rows,
        cols,
        values,
        agg,
        table_name,
    })
}

/// Loads the pivot query string from either `--query` (inline) or `--query-file`.
pub(crate) fn load_pivot_query(cmd: &PivotCommand) -> Result<String> {
    if let Some(sql) = &cmd.query {
        return Ok(sql.clone());
    }
    if let Some(path) = &cmd.query_file {
        return fs::read_to_string(path)
            .map_err(Into::into)
            .map(|content| content.trim().to_owned());
    }
    bail!("provide either --query or --query-file")
}

/// Wraps a SQL identifier in double-quotes, escaping any embedded double-quotes.
fn quote_identifier(name: &str) -> String {
    format!("\"{}\"", name.replace('"', "\"\""))
}

/// Builds a crosstab (pivot) SQL query.
///
/// * When `cols_col` is `None` a simple GROUP BY aggregation over `rows_col` is generated.
/// * When `cols_col` is `Some`, a CASE WHEN crosstab with one output column per entry in
///   `distinct_col_values` is generated.
///
/// The `values_col` is required for `Sum`, `Avg`, `Min`, `Max` aggregations and optional
/// for `Count` (defaults to `COUNT(*)`).
pub(crate) fn build_pivot_sql(
    table_name: &str,
    rows_col: &str,
    cols_col: Option<&str>,
    values_col: Option<&str>,
    agg: PivotAggregation,
    distinct_col_values: &[String],
) -> String {
    let table = quote_identifier(table_name);
    let rows = quote_identifier(rows_col);
    let agg_fn = match agg {
        PivotAggregation::Count => "COUNT",
        PivotAggregation::Sum => "SUM",
        PivotAggregation::Avg => "AVG",
        PivotAggregation::Min => "MIN",
        PivotAggregation::Max => "MAX",
    };

    if let Some(cols) = cols_col {
        let cols_q = quote_identifier(cols);

        let case_exprs = distinct_col_values
            .iter()
            .map(|val| {
                let escaped_val = val.replace('\'', "''");
                let col_alias = quote_identifier(val);
                if matches!(agg, PivotAggregation::Count) && values_col.is_none() {
                    format!(
                        "COUNT(CASE WHEN CAST({cols_q} AS TEXT) = '{escaped_val}' THEN 1 END) AS {col_alias}"
                    )
                } else {
                    let vals_q = quote_identifier(values_col.unwrap_or("*"));
                    format!(
                        "{agg_fn}(CASE WHEN CAST({cols_q} AS TEXT) = '{escaped_val}' THEN {vals_q} END) AS {col_alias}"
                    )
                }
            })
            .collect::<Vec<_>>()
            .join(",\n    ");

        if case_exprs.is_empty() {
            // No distinct values found; emit a query that returns just the rows column.
            format!("SELECT {rows}\nFROM {table}\nGROUP BY {rows}\nORDER BY {rows}")
        } else {
            format!(
                "SELECT {rows},\n    {case_exprs}\nFROM {table}\nGROUP BY {rows}\nORDER BY {rows}"
            )
        }
    } else {
        let agg_expr = if let Some(vals) = values_col {
            let vals_q = quote_identifier(vals);
            format!("{agg_fn}({vals_q}) AS {}", agg_fn.to_lowercase())
        } else {
            "COUNT(*) AS count".to_owned()
        };
        format!(
            "SELECT {rows},\n    {agg_expr}\nFROM {table}\nGROUP BY {rows}\nORDER BY {rows}"
        )
    }
}

pub(crate) fn run_pivot_command(cmd: PivotCommand) -> Result<i32> {
    let execution = execute_pivot_command(&cmd)?;
    let (output_path, output_format_hint) = parse_output_path_spec(cmd.output.as_deref());
    write_query_result(
        &execution.result,
        output_path.as_deref(),
        cmd.format,
        output_format_hint.as_deref(),
    )?;

    Ok(0)
}

pub(crate) fn execute_pivot_command(cmd: &PivotCommand) -> Result<PivotExecution> {
    // Load and parse the pivot query.
    let raw_query = load_pivot_query(&cmd)?;
    let spec = parse_pivot_query(&raw_query)?;

    let input_specs = parse_input_specs(&cmd.input, None)?;
    let workbook_inputs: Vec<query_forge::WorkbookInput> = input_specs
        .iter()
        .map(|spec| query_forge::WorkbookInput {
            path: spec.path.as_path(),
            sheet_name: spec.sheet_name.as_deref(),
            table_name: spec.table_name.as_deref(),
            explicit_format: spec.explicit_format.as_deref(),
        })
        .collect();

    let type_inference_options = resolve_type_inference_options(&cmd);
    let input_normalization_options = resolve_input_normalization_options(&cmd);
    let extraction_options = resolve_extraction_options(&cmd);
    let has_headers = !cmd.no_headers;

    // Pass 1 (only when a FOR clause is present): query distinct values in the cols column.
    let distinct_col_values: Vec<String> = if let Some(cols) = &spec.cols {
        let cols_q = quote_identifier(cols);
        let distinct_sql = format!(
            "SELECT DISTINCT {cols_q} FROM {} WHERE {cols_q} IS NOT NULL ORDER BY 1",
            quote_identifier(&spec.table_name)
        );
        let distinct_result =
            query_forge::run_query_with_params_multi_inputs_and_options_and_normalization(
                &workbook_inputs,
                &distinct_sql,
                &[],
                &type_inference_options,
                &input_normalization_options,
                &extraction_options,
                has_headers,
            )?;
        distinct_result
            .rows
            .into_iter()
            .filter_map(|row| match row.into_iter().next() {
                Some(query_forge::QueryValue::Text(s)) => Some(s),
                Some(query_forge::QueryValue::Integer(n)) => Some(n.to_string()),
                Some(query_forge::QueryValue::Real(r)) => Some(r.to_string()),
                _ => None,
            })
            .collect()
    } else {
        vec![]
    };

    // Build the pivot SQL from the parsed spec.
    let pivot_sql = build_pivot_sql(
        &spec.table_name,
        &spec.rows,
        spec.cols.as_deref(),
        spec.values.as_deref(),
        spec.agg,
        &distinct_col_values,
    );

    // Print the SQL to stderr when requested.
    if cmd.show_sql {
        eprintln!("-- generated pivot SQL:\n{pivot_sql}");
    }

    // Pass 2: run the pivot query and write the result.
    let result = query_forge::run_query_with_params_multi_inputs_and_options_and_normalization(
        &workbook_inputs,
        &pivot_sql,
        &[],
        &type_inference_options,
        &input_normalization_options,
        &extraction_options,
        has_headers,
    )?;

    Ok(PivotExecution {
        result,
        pivot_sql,
        distinct_column_values: distinct_col_values.len(),
    })
}

fn resolve_type_inference_options(cmd: &PivotCommand) -> query_forge::TypeInferenceOptions {
    let defaults = query_forge::TypeInferenceOptions::default();
    query_forge::TypeInferenceOptions {
        infer_types: !cmd.all_text || cmd.infer_types,
        decimal_comma: cmd.decimal_comma,
        date_format: cmd.date_format.clone(),
        null_values: if cmd.null_values.is_empty() {
            defaults.null_values
        } else {
            cmd.null_values.iter().map(|v| v.trim().to_owned()).collect()
        },
        true_values: if cmd.true_values.is_empty() {
            defaults.true_values
        } else {
            cmd.true_values.iter().map(|v| v.trim().to_owned()).collect()
        },
        false_values: if cmd.false_values.is_empty() {
            defaults.false_values
        } else {
            cmd.false_values.iter().map(|v| v.trim().to_owned()).collect()
        },
    }
}

fn resolve_input_normalization_options(
    cmd: &PivotCommand,
) -> query_forge::InputNormalizationOptions {
    query_forge::InputNormalizationOptions {
        trim: cmd.trim,
        skip_empty_rows: cmd.skip_empty_rows,
        normalize_headers: cmd.normalize_headers,
        header_case: match cmd.header_case {
            Some(HeaderCase::Snake) => Some(query_forge::HeaderCase::Snake),
            Some(HeaderCase::Camel) => Some(query_forge::HeaderCase::Camel),
            Some(HeaderCase::Pascal) => Some(query_forge::HeaderCase::Pascal),
            Some(HeaderCase::ScreamingSnake) => Some(query_forge::HeaderCase::ScreamingSnake),
            None => None,
        },
        dedupe_headers: cmd.dedupe_headers,
    }
}

fn resolve_extraction_options(cmd: &PivotCommand) -> query_forge::ExtractionOptions {
    query_forge::ExtractionOptions {
        json_mode: match cmd.json_mode {
            Some(JsonMode::Array) | None => query_forge::JsonMode::Array,
            Some(JsonMode::Object) => query_forge::JsonMode::Object,
            Some(JsonMode::Flatten) => query_forge::JsonMode::Flatten,
        },
        xml_mode: match cmd.xml_mode {
            Some(XmlMode::Rows) | None => query_forge::XmlMode::Rows,
            Some(XmlMode::Descendants) => query_forge::XmlMode::Descendants,
            Some(XmlMode::Attributes) => query_forge::XmlMode::Attributes,
        },
    }
}

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

    #[test]
    fn simple_count_no_cols() {
        let sql = build_pivot_sql("table", "category", None, None, PivotAggregation::Count, &[]);
        assert!(sql.contains("COUNT(*) AS count"), "expected COUNT(*): {sql}");
        assert!(sql.contains("GROUP BY \"category\""), "expected GROUP BY: {sql}");
        assert!(sql.contains("ORDER BY \"category\""), "expected ORDER BY: {sql}");
    }

    #[test]
    fn simple_sum_no_cols() {
        let sql =
            build_pivot_sql("table", "category", None, Some("stock"), PivotAggregation::Sum, &[]);
        assert!(sql.contains("SUM(\"stock\") AS sum"), "expected SUM: {sql}");
        assert!(sql.contains("GROUP BY \"category\""), "expected GROUP BY: {sql}");
    }

    #[test]
    fn simple_avg_no_cols() {
        let sql =
            build_pivot_sql("table", "region", None, Some("price"), PivotAggregation::Avg, &[]);
        assert!(sql.contains("AVG(\"price\") AS avg"), "expected AVG: {sql}");
    }

    #[test]
    fn crosstab_sum_with_cols() {
        let vals = vec!["true".to_owned(), "false".to_owned()];
        let sql = build_pivot_sql(
            "table",
            "category",
            Some("active"),
            Some("stock"),
            PivotAggregation::Sum,
            &vals,
        );
        assert!(
            sql.contains("SUM(CASE WHEN CAST(\"active\" AS TEXT) = 'true' THEN \"stock\" END)"),
            "expected true branch: {sql}"
        );
        assert!(
            sql.contains("SUM(CASE WHEN CAST(\"active\" AS TEXT) = 'false' THEN \"stock\" END)"),
            "expected false branch: {sql}"
        );
        assert!(sql.contains("GROUP BY \"category\""), "expected GROUP BY: {sql}");
    }

    #[test]
    fn crosstab_count_with_cols_no_values() {
        let vals = vec!["A".to_owned(), "B".to_owned()];
        let sql = build_pivot_sql(
            "table",
            "region",
            Some("type"),
            None,
            PivotAggregation::Count,
            &vals,
        );
        assert!(
            sql.contains("COUNT(CASE WHEN CAST(\"type\" AS TEXT) = 'A' THEN 1 END)"),
            "expected COUNT A: {sql}"
        );
        assert!(
            sql.contains("COUNT(CASE WHEN CAST(\"type\" AS TEXT) = 'B' THEN 1 END)"),
            "expected COUNT B: {sql}"
        );
    }

    #[test]
    fn crosstab_escapes_single_quotes_in_col_values() {
        let vals = vec!["it's".to_owned()];
        let sql =
            build_pivot_sql("t", "cat", Some("col"), None, PivotAggregation::Count, &vals);
        assert!(sql.contains("= 'it''s'"), "expected escaped quote: {sql}");
    }

    #[test]
    fn crosstab_with_no_distinct_values_returns_only_rows_col() {
        let sql =
            build_pivot_sql("table", "category", Some("type"), None, PivotAggregation::Count, &[]);
        // No case expressions — should produce a simple GROUP BY with just the rows column.
        assert!(!sql.contains("CASE WHEN"), "no CASE WHEN expected: {sql}");
        assert!(sql.contains("GROUP BY \"category\""), "expected GROUP BY: {sql}");
    }

    #[test]
    fn quote_identifier_escapes_double_quotes() {
        let q = quote_identifier("col\"name");
        assert_eq!(q, "\"col\"\"name\"");
    }

    #[test]
    fn integration_count_pivot_on_csv() {
        use std::io::Write;

        let csv = b"category,active\nelectronics,true\nelectronics,false\noffice,true\n";
        let dir = std::env::temp_dir();
        let path = dir.join("pivot_test_count.csv");
        std::fs::File::create(&path)
            .unwrap()
            .write_all(csv)
            .unwrap();

        let input = query_forge::WorkbookInput {
            path: &path,
            sheet_name: None,
            table_name: None,
            explicit_format: None,
        };
        let sql = build_pivot_sql(
            "table",
            "category",
            None,
            None,
            PivotAggregation::Count,
            &[],
        );
        let result = query_forge::run_query_with_params_multi_inputs_and_options_and_normalization(
            &[input],
            &sql,
            &[],
            &query_forge::TypeInferenceOptions::default(),
            &query_forge::InputNormalizationOptions::default(),
            &query_forge::ExtractionOptions::default(),
            true,
        )
        .expect("pivot query should succeed");

        assert_eq!(result.columns, vec!["category", "count"]);
        assert_eq!(result.rows.len(), 2);
        let _ = std::fs::remove_file(path);
    }

    #[test]
    fn integration_sum_crosstab_on_csv() {
        use std::io::Write;

        let csv =
            b"category,active,stock\nelectronics,true,8\nelectronics,false,2\noffice,true,80\n";
        let dir = std::env::temp_dir();
        let path = dir.join("pivot_test_sum.csv");
        std::fs::File::create(&path)
            .unwrap()
            .write_all(csv)
            .unwrap();

        let input = query_forge::WorkbookInput {
            path: &path,
            sheet_name: None,
            table_name: None,
            explicit_format: None,
        };

        // First pass: collect distinct "active" values.
        let distinct_sql = "SELECT DISTINCT \"active\" FROM \"table\" WHERE \"active\" IS NOT NULL ORDER BY 1";
        let distinct_result =
            query_forge::run_query_with_params_multi_inputs_and_options_and_normalization(
                &[input.clone()],
                distinct_sql,
                &[],
                &query_forge::TypeInferenceOptions::default(),
                &query_forge::InputNormalizationOptions::default(),
                &query_forge::ExtractionOptions::default(),
                true,
            )
            .expect("distinct query should succeed");
        let distinct_vals: Vec<String> = distinct_result
            .rows
            .into_iter()
            .filter_map(|row| match row.into_iter().next() {
                Some(query_forge::QueryValue::Text(s)) => Some(s),
                Some(query_forge::QueryValue::Integer(n)) => Some(n.to_string()),
                _ => None,
            })
            .collect();

        // Second pass: run the pivot.
        let pivot_sql = build_pivot_sql(
            "table",
            "category",
            Some("active"),
            Some("stock"),
            PivotAggregation::Sum,
            &distinct_vals,
        );
        let result = query_forge::run_query_with_params_multi_inputs_and_options_and_normalization(
            &[input],
            &pivot_sql,
            &[],
            &query_forge::TypeInferenceOptions::default(),
            &query_forge::InputNormalizationOptions::default(),
            &query_forge::ExtractionOptions::default(),
            true,
        )
        .expect("pivot query should succeed");

        assert_eq!(result.columns[0], "category");
        assert!(result.columns.len() >= 2, "expected pivot columns: {:?}", result.columns);
        assert_eq!(result.rows.len(), 2, "expected 2 category rows");
        let _ = std::fs::remove_file(path);
    }

    // --- parse_pivot_query tests ---

    #[test]
    fn parse_count_star_no_cols() {
        let spec = parse_pivot_query("PIVOT COUNT(*) FROM table GROUP BY category").unwrap();
        assert_eq!(spec.table_name, "table");
        assert_eq!(spec.rows, "category");
        assert!(spec.cols.is_none());
        assert!(spec.values.is_none());
        assert!(matches!(spec.agg, PivotAggregation::Count));
    }

    #[test]
    fn parse_sum_with_for_clause() {
        let spec =
            parse_pivot_query("PIVOT SUM(stock) FOR active FROM table GROUP BY category").unwrap();
        assert_eq!(spec.table_name, "table");
        assert_eq!(spec.rows, "category");
        assert_eq!(spec.cols.as_deref(), Some("active"));
        assert_eq!(spec.values.as_deref(), Some("stock"));
        assert!(matches!(spec.agg, PivotAggregation::Sum));
    }

    #[test]
    fn parse_avg_no_for() {
        let spec =
            parse_pivot_query("PIVOT AVG(price) FROM inventory GROUP BY product").unwrap();
        assert_eq!(spec.table_name, "inventory");
        assert_eq!(spec.rows, "product");
        assert!(spec.cols.is_none());
        assert_eq!(spec.values.as_deref(), Some("price"));
        assert!(matches!(spec.agg, PivotAggregation::Avg));
    }

    #[test]
    fn parse_min_and_max() {
        let spec = parse_pivot_query("PIVOT MIN(val) FROM t GROUP BY k").unwrap();
        assert!(matches!(spec.agg, PivotAggregation::Min));
        let spec2 = parse_pivot_query("PIVOT MAX(val) FROM t GROUP BY k").unwrap();
        assert!(matches!(spec2.agg, PivotAggregation::Max));
    }

    #[test]
    fn parse_case_insensitive_keywords() {
        let spec =
            parse_pivot_query("pivot count(*) for region from sales group by product").unwrap();
        assert!(matches!(spec.agg, PivotAggregation::Count));
        assert_eq!(spec.cols.as_deref(), Some("region"));
        assert_eq!(spec.table_name, "sales");
        assert_eq!(spec.rows, "product");
    }

    #[test]
    fn parse_quoted_identifiers() {
        let spec =
            parse_pivot_query("PIVOT SUM(\"my stock\") FOR \"active flag\" FROM \"my table\" GROUP BY \"cat col\"")
                .unwrap();
        assert_eq!(spec.values.as_deref(), Some("my stock"));
        assert_eq!(spec.cols.as_deref(), Some("active flag"));
        assert_eq!(spec.table_name, "my table");
        assert_eq!(spec.rows, "cat col");
    }

    #[test]
    fn parse_trailing_semicolon_ignored() {
        // Files may end with a semicolon; the tokenizer skips it.
        let spec =
            parse_pivot_query("PIVOT COUNT(*) FROM table GROUP BY category;").unwrap();
        assert_eq!(spec.rows, "category");
    }

    #[test]
    fn parse_error_sum_with_star() {
        let result = parse_pivot_query("PIVOT SUM(*) FROM table GROUP BY category");
        assert!(result.is_err(), "SUM(*) should fail: {result:?}");
    }

    #[test]
    fn parse_error_unknown_agg() {
        let result = parse_pivot_query("PIVOT MEDIAN(price) FROM table GROUP BY category");
        assert!(result.is_err(), "unknown agg should fail: {result:?}");
    }

    #[test]
    fn parse_error_missing_pivot_keyword() {
        let result = parse_pivot_query("COUNT(*) FROM table GROUP BY category");
        assert!(result.is_err(), "missing PIVOT keyword should fail");
    }

    #[test]
    fn parse_error_missing_from() {
        let result = parse_pivot_query("PIVOT COUNT(*) GROUP BY category");
        assert!(result.is_err(), "missing FROM should fail");
    }

    #[test]
    fn parse_error_missing_group_by() {
        let result = parse_pivot_query("PIVOT COUNT(*) FROM table");
        assert!(result.is_err(), "missing GROUP BY should fail");
    }

    #[test]
    fn parse_error_trailing_tokens() {
        let result = parse_pivot_query("PIVOT COUNT(*) FROM table GROUP BY category extra");
        assert!(result.is_err(), "trailing tokens should fail");
    }
}