fraiseql-core 2.3.2

#![allow(clippy::unwrap_used)] // Reason: test code, panics are acceptable

//! End-to-end aggregate query tests
//!
//! These tests exercise the full analytics pipeline from query parsing to SQL generation

#![allow(clippy::format_push_string)] // Reason: test query builders use push_str(&format!()) for readability
mod common;

use common::{assert_sql_contains, create_sales_metadata};
use fraiseql_core::{
    db::types::DatabaseType,
    runtime::{AggregateQueryParser, AggregationSqlGenerator, ParameterizedAggregationSql},
};
use serde_json::json;

// =============================================================================
// Helper Functions
// =============================================================================

fn parse_plan_generate_full(query: &serde_json::Value) -> ParameterizedAggregationSql {
    let metadata = create_sales_metadata();
    let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);

    let mut query_with_table = query.clone();
    if query_with_table.as_object_mut().unwrap().get("table").is_none() {
        query_with_table
            .as_object_mut()
            .unwrap()
            .insert("table".to_string(), json!("tf_sales"));
    }

    let parsed = AggregateQueryParser::parse(
        &query_with_table,
        &metadata,
        &std::collections::HashMap::new(),
    )
    .unwrap();
    let plan =
        fraiseql_core::compiler::aggregation::AggregationPlanner::plan(parsed, metadata).unwrap();
    generator.generate_parameterized(&plan).unwrap()
}

fn parse_plan_generate(query: &serde_json::Value) -> String {
    parse_plan_generate_full(query).sql
}

// =============================================================================
// Basic Aggregate Tests
// =============================================================================

#[test]
fn test_simple_count_all() {
    let query = json!({
        "aggregates": [{"count": {}}]
    });

    let sql = parse_plan_generate(&query);

    assert_sql_contains(&sql, &["SELECT", "COUNT(*)", "FROM tf_sales"]);
}

#[test]
fn test_count_with_sum() {
    let query = json!({
        "aggregates": [
            {"count": {}},
            {"revenue_sum": {}}
        ]
    });

    let sql = parse_plan_generate(&query);

    assert_sql_contains(&sql, &["COUNT(*)", "SUM(revenue)", "FROM tf_sales"]);
}

#[test]
fn test_all_aggregate_functions() {
    let query = json!({
        "aggregates": [
            {"count": {}},
            {"revenue_sum": {}},
            {"revenue_avg": {}},
            {"revenue_min": {}},
            {"revenue_max": {}},
            {"quantity_sum": {}},
            {"quantity_avg": {}}
        ]
    });

    let sql = parse_plan_generate(&query);

    assert_sql_contains(
        &sql,
        &[
            "COUNT(*)",
            "SUM(revenue)",
            "AVG(revenue)",
            "MIN(revenue)",
            "MAX(revenue)",
            "SUM(quantity)",
            "AVG(quantity)",
        ],
    );
}

// =============================================================================
// GROUP BY Tests
// =============================================================================

#[test]
fn test_group_by_single_dimension() {
    let query = json!({
        "groupBy": {"category": true},
        "aggregates": [
            {"count": {}},
            {"revenue_sum": {}}
        ]
    });

    let sql = parse_plan_generate(&query);

    assert_sql_contains(&sql, &["data->>'category'", "GROUP BY", "COUNT(*)", "SUM(revenue)"]);
}

#[test]
fn test_group_by_multiple_dimensions() {
    let query = json!({
        "groupBy": {
            "category": true,
            "region": true
        },
        "aggregates": [
            {"count": {}},
            {"revenue_sum": {}}
        ]
    });

    let sql = parse_plan_generate(&query);

    assert_sql_contains(&sql, &["data->>'category'", "data->>'region'", "GROUP BY"]);
}

// =============================================================================
// WHERE Clause Tests
// =============================================================================

#[test]
fn test_where_denormalized_filter() {
    let query = json!({
        "where": {
            "customer_id_eq": "cust-001"
        },
        "aggregates": [{"count": {}}]
    });

    let result = parse_plan_generate_full(&query);

    assert_sql_contains(&result.sql, &["WHERE", "customer_id"]);
    assert!(result.params.contains(&json!("cust-001")), "value must be a bind param");
}

#[test]
fn test_where_jsonb_dimension() {
    let query = json!({
        "where": {
            "category_eq": "Electronics"
        },
        "aggregates": [{"count": {}}]
    });

    let result = parse_plan_generate_full(&query);

    assert_sql_contains(&result.sql, &["WHERE", "data->>'category'"]);
    assert!(result.params.contains(&json!("Electronics")), "value must be a bind param");
}

#[test]
fn test_where_with_comparison_operators() {
    let query = json!({
        "where": {
            "revenue_gt": 100.0,
            "quantity_lte": 10
        },
        "aggregates": [{"count": {}}]
    });

    let sql = parse_plan_generate(&query);

    assert_sql_contains(&sql, &["WHERE", "revenue", ">", "quantity", "<="]);
}

#[test]
fn test_where_with_like_operator() {
    let query = json!({
        "where": {
            "category_contains": "electr"
        },
        "aggregates": [{"count": {}}]
    });

    let result = parse_plan_generate_full(&query);

    assert_sql_contains(&result.sql, &["WHERE", "data->>'category'", "LIKE"]);
    assert!(
        result.params.iter().any(|p| p.as_str() == Some("%electr%")),
        "LIKE pattern must be a bind param: {:?}",
        result.params
    );
}

// =============================================================================
// ORDER BY and LIMIT Tests
// =============================================================================

#[test]
fn test_order_by_aggregate_desc() {
    let query = json!({
        "groupBy": {"category": true},
        "aggregates": [
            {"count": {}},
            {"revenue_sum": {}}
        ],
        "orderBy": {
            "revenue_sum": "DESC"
        }
    });

    let sql = parse_plan_generate(&query);

    assert_sql_contains(&sql, &["ORDER BY", "DESC"]);
}

#[test]
fn test_order_by_dimension_asc() {
    let query = json!({
        "groupBy": {"category": true},
        "aggregates": [{"count": {}}],
        "orderBy": {
            "category": "ASC"
        }
    });

    let sql = parse_plan_generate(&query);

    assert_sql_contains(&sql, &["ORDER BY", "ASC"]);
}

#[test]
fn test_limit_only() {
    let query = json!({
        "groupBy": {"category": true},
        "aggregates": [{"count": {}}],
        "limit": 10
    });

    let sql = parse_plan_generate(&query);

    assert_sql_contains(&sql, &["LIMIT 10"]);
}

#[test]
fn test_limit_and_offset() {
    let query = json!({
        "groupBy": {"category": true},
        "aggregates": [{"count": {}}],
        "limit": 5,
        "offset": 10
    });

    let sql = parse_plan_generate(&query);

    assert_sql_contains(&sql, &["LIMIT 5", "OFFSET 10"]);
}

// =============================================================================
// Complex Query Tests
// =============================================================================

#[test]
fn test_complex_query_all_clauses() {
    let query = json!({
        "where": {
            "region_eq": "North",
            "revenue_gt": 100.0
        },
        "groupBy": {
            "category": true
        },
        "aggregates": [
            {"count": {}},
            {"revenue_sum": {}},
            {"revenue_avg": {}}
        ],
        "orderBy": {
            "revenue_sum": "DESC"
        },
        "limit": 10
    });

    let sql = parse_plan_generate(&query);

    // Verify all clauses present
    assert_sql_contains(&sql, &["SELECT", "WHERE", "GROUP BY", "ORDER BY", "LIMIT"]);

    // Verify specific content
    assert_sql_contains(
        &sql,
        &[
            "data->>'region'",
            "data->>'category'",
            "COUNT(*)",
            "SUM(revenue)",
            "AVG(revenue)",
            "DESC",
        ],
    );
}

#[test]
fn test_multiple_where_conditions() {
    let query = json!({
        "where": {
            "customer_id_eq": "cust-001",
            "category_eq": "Electronics",
            "revenue_gte": 50.0
        },
        "aggregates": [{"count": {}}]
    });

    let sql = parse_plan_generate(&query);

    assert_sql_contains(
        &sql,
        &[
            "WHERE",
            "customer_id",
            "data->>'category'",
            "revenue",
            "AND", // Multiple conditions should be AND-ed
        ],
    );
}

#[test]
fn test_group_by_with_multiple_aggregates() {
    let query = json!({
        "groupBy": {
            "category": true,
            "region": true
        },
        "aggregates": [
            {"count": {}},
            {"revenue_sum": {}},
            {"revenue_avg": {}},
            {"revenue_min": {}},
            {"revenue_max": {}},
            {"quantity_sum": {}}
        ]
    });

    let sql = parse_plan_generate(&query);

    // Verify GROUP BY with both dimensions
    assert_sql_contains(&sql, &["data->>'category'", "data->>'region'", "GROUP BY"]);

    // Verify all aggregates
    assert_sql_contains(
        &sql,
        &[
            "COUNT(*)",
            "SUM(revenue)",
            "AVG(revenue)",
            "MIN(revenue)",
            "MAX(revenue)",
            "SUM(quantity)",
        ],
    );
}

// =============================================================================
// SQL Correctness Tests
// =============================================================================

#[test]
fn test_sql_structure_validity() {
    let query = json!({
        "groupBy": {"category": true},
        "aggregates": [
            {"count": {}},
            {"revenue_sum": {}}
        ]
    });

    let sql = parse_plan_generate(&query);

    // Verify SQL structure follows: SELECT ... FROM ... GROUP BY ...
    let select_pos = sql.find("SELECT").expect("Missing SELECT");
    let from_pos = sql.find("FROM").expect("Missing FROM");
    let group_by_pos = sql.find("GROUP BY").expect("Missing GROUP BY");

    assert!(select_pos < from_pos, "SELECT should come before FROM");
    assert!(from_pos < group_by_pos, "FROM should come before GROUP BY");
}

#[test]
fn test_sql_no_unnecessary_clauses() {
    let query = json!({
        "aggregates": [{"count": {}}]
    });

    let sql = parse_plan_generate(&query);

    // Simple count should not have GROUP BY, WHERE, HAVING, ORDER BY
    assert!(!sql.contains("GROUP BY"));
    assert!(!sql.contains("WHERE"));
    assert!(!sql.contains("HAVING"));
    assert!(!sql.contains("ORDER BY"));
    assert!(!sql.contains("LIMIT"));
}

// =============================================================================
// HAVING Clause Tests
// =============================================================================

#[test]
fn test_having_simple_count() {
    let query = json!({
        "groupBy": {"category": true},
        "aggregates": [{"count": {}}],
        "having": {
            "count_gt": 5
        }
    });

    let result = parse_plan_generate_full(&query);

    assert_sql_contains(&result.sql, &["GROUP BY", "HAVING", "COUNT(*) >"]);
    assert!(result.params.contains(&json!(5)), "HAVING value must be a bind param");
}

#[test]
fn test_having_aggregate_sum() {
    let query = json!({
        "groupBy": {"category": true},
        "aggregates": [
            {"count": {}},
            {"revenue_sum": {}}
        ],
        "having": {
            "revenue_sum_gte": 1000.0
        }
    });

    let result = parse_plan_generate_full(&query);

    assert_sql_contains(&result.sql, &["GROUP BY", "HAVING", "SUM(revenue) >="]);
    assert!(result.params.contains(&json!(1000.0)), "HAVING value must be a bind param");
}

#[test]
fn test_having_multiple_conditions() {
    let query = json!({
        "groupBy": {"category": true},
        "aggregates": [
            {"count": {}},
            {"revenue_sum": {}},
            {"revenue_avg": {}}
        ],
        "having": {
            "count_gt": 10,
            "revenue_avg_gte": 50.0
        }
    });

    let result = parse_plan_generate_full(&query);

    assert_sql_contains(&result.sql, &["HAVING", "COUNT(*) >", "AVG(revenue) >=", "AND"]);
    assert!(result.params.contains(&json!(10)), "count threshold must be a bind param");
    assert!(result.params.contains(&json!(50.0)), "avg threshold must be a bind param");
}

#[test]
fn test_having_with_where() {
    // WHERE filters rows before aggregation
    // HAVING filters aggregated results
    let query = json!({
        "where": {
            "region_eq": "North"
        },
        "groupBy": {"category": true},
        "aggregates": [{"revenue_sum": {}}],
        "having": {
            "revenue_sum_gt": 5000.0
        }
    });

    let result = parse_plan_generate_full(&query);
    let sql = &result.sql;

    // Verify SQL clause order: WHERE → GROUP BY → HAVING
    let where_pos = sql.find("WHERE").expect("Missing WHERE");
    let group_pos = sql.find("GROUP BY").expect("Missing GROUP BY");
    let having_pos = sql.find("HAVING").expect("Missing HAVING");

    assert!(where_pos < group_pos, "WHERE should come before GROUP BY");
    assert!(group_pos < having_pos, "GROUP BY should come before HAVING");

    assert_sql_contains(sql, &["data->>'region'", "SUM(revenue) >"]);
    assert!(result.params.contains(&json!("North")), "WHERE value must be a bind param");
    assert!(result.params.contains(&json!(5000.0)), "HAVING value must be a bind param");
}

#[test]
fn test_sql_clause_order_with_having() {
    let query = json!({
        "where": {"customer_id_eq": "cust-001"},
        "groupBy": {"category": true},
        "aggregates": [{"count": {}}, {"revenue_sum": {}}],
        "having": {"count_gt": 5},
        "orderBy": {"revenue_sum": "DESC"},
        "limit": 10
    });

    let sql = parse_plan_generate(&query);

    // Verify correct SQL clause order:
    // SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
    let select_pos = sql.find("SELECT").expect("Missing SELECT");
    let from_pos = sql.find("FROM").expect("Missing FROM");
    let where_pos = sql.find("WHERE").expect("Missing WHERE");
    let group_pos = sql.find("GROUP BY").expect("Missing GROUP BY");
    let having_pos = sql.find("HAVING").expect("Missing HAVING");
    let order_pos = sql.find("ORDER BY").expect("Missing ORDER BY");
    let limit_pos = sql.find("LIMIT").expect("Missing LIMIT");

    assert!(select_pos < from_pos);
    assert!(from_pos < where_pos);
    assert!(where_pos < group_pos);
    assert!(group_pos < having_pos);
    assert!(having_pos < order_pos);
    assert!(order_pos < limit_pos);
}

#[test]
fn test_having_different_operators() {
    // Test various HAVING operators: gt, gte, lt, lte, eq, neq
    let query = json!({
        "groupBy": {"category": true},
        "aggregates": [
            {"count": {}},
            {"revenue_sum": {}},
            {"revenue_avg": {}},
            {"revenue_min": {}},
            {"revenue_max": {}}
        ],
        "having": {
            "count_gt": 10,
            "revenue_sum_gte": 1000.0,
            "revenue_avg_lt": 200.0,
            "revenue_min_lte": 50.0,
            "revenue_max_eq": 500.0
        }
    });

    let result = parse_plan_generate_full(&query);

    assert_sql_contains(
        &result.sql,
        &[
            "HAVING",
            "COUNT(*) >",
            "SUM(revenue) >=",
            "AVG(revenue) <",
            "MIN(revenue) <=",
            "MAX(revenue) =",
        ],
    );
    assert!(result.params.contains(&json!(10)), "count threshold must be a bind param");
    assert!(result.params.contains(&json!(1000.0)), "sum threshold must be a bind param");
    assert!(result.params.contains(&json!(200.0)), "avg threshold must be a bind param");
    assert!(result.params.contains(&json!(50.0)), "min threshold must be a bind param");
    assert!(result.params.contains(&json!(500.0)), "max threshold must be a bind param");
}

// =============================================================================
// Temporal Bucketing Tests
// =============================================================================

#[test]
fn test_temporal_bucket_day() {
    let query = json!({
        "groupBy": {
            "occurred_at": "day"
        },
        "aggregates": [
            {"count": {}},
            {"revenue_sum": {}}
        ]
    });

    let sql = parse_plan_generate(&query);

    assert_sql_contains(
        &sql,
        &[
            "DATE_TRUNC('day', occurred_at)", // PostgreSQL
            "GROUP BY",
        ],
    );
}

#[test]
fn test_temporal_bucket_month() {
    let query = json!({
        "groupBy": {
            "occurred_at": "month"
        },
        "aggregates": [{"count": {}}]
    });

    let sql = parse_plan_generate(&query);

    assert_sql_contains(&sql, &["DATE_TRUNC('month', occurred_at)", "GROUP BY"]);
}

#[test]
fn test_temporal_bucket_with_dimension() {
    // Mix temporal bucketing with regular dimension grouping
    let query = json!({
        "groupBy": {
            "category": true,
            "occurred_at": "day"
        },
        "aggregates": [{"count": {}}, {"revenue_sum": {}}]
    });

    let sql = parse_plan_generate(&query);

    assert_sql_contains(
        &sql,
        &[
            "data->>'category'",
            "DATE_TRUNC('day', occurred_at)",
            "GROUP BY",
        ],
    );
}

#[test]
fn test_temporal_bucket_all_types() {
    for bucket in &[
        "second", "minute", "hour", "day", "week", "month", "quarter", "year",
    ] {
        let query = json!({
            "groupBy": {
                "occurred_at": bucket
            },
            "aggregates": [{"count": {}}]
        });

        let sql = parse_plan_generate(&query);

        // Verify SQL generation doesn't panic for any bucket type
        assert!(!sql.is_empty());
        assert!(sql.contains("GROUP BY"));
    }
}

#[test]
fn test_temporal_bucket_multi_database() {
    use fraiseql_core::{
        compiler::aggregation::AggregationPlanner,
        db::DatabaseType,
        runtime::{AggregateQueryParser, AggregationSqlGenerator},
    };

    let query = json!({
        "table": "tf_sales",
        "groupBy": {"occurred_at": "day"},
        "aggregates": [{"count": {}}]
    });

    let metadata = create_sales_metadata();

    // PostgreSQL
    let pg_gen = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
    let pg_parsed =
        AggregateQueryParser::parse(&query, &metadata, &std::collections::HashMap::new()).unwrap();
    let pg_plan = AggregationPlanner::plan(pg_parsed, metadata.clone()).unwrap();
    let pg_sql = pg_gen.generate_parameterized(&pg_plan).unwrap();
    assert!(pg_sql.sql.contains("DATE_TRUNC('day', occurred_at)"));

    // MySQL
    let mysql_gen = AggregationSqlGenerator::new(DatabaseType::MySQL);
    let mysql_parsed =
        AggregateQueryParser::parse(&query, &metadata, &std::collections::HashMap::new()).unwrap();
    let mysql_plan = AggregationPlanner::plan(mysql_parsed, metadata.clone()).unwrap();
    let mysql_sql = mysql_gen.generate_parameterized(&mysql_plan).unwrap();
    assert!(mysql_sql.sql.contains("DATE_FORMAT(occurred_at,"));

    // SQLite
    let sqlite_gen = AggregationSqlGenerator::new(DatabaseType::SQLite);
    let sqlite_parsed =
        AggregateQueryParser::parse(&query, &metadata, &std::collections::HashMap::new()).unwrap();
    let sqlite_plan = AggregationPlanner::plan(sqlite_parsed, metadata.clone()).unwrap();
    let sqlite_sql = sqlite_gen.generate_parameterized(&sqlite_plan).unwrap();
    assert!(sqlite_sql.sql.contains("strftime("));

    // SQL Server
    let mssql_gen = AggregationSqlGenerator::new(DatabaseType::SQLServer);
    let mssql_parsed =
        AggregateQueryParser::parse(&query, &metadata, &std::collections::HashMap::new()).unwrap();
    let mssql_plan = AggregationPlanner::plan(mssql_parsed, metadata).unwrap();
    let mssql_sql = mssql_gen.generate_parameterized(&mssql_plan).unwrap();
    assert!(mssql_sql.sql.contains("CAST(occurred_at AS DATE)"));
}

#[test]
fn test_temporal_bucket_with_where_having() {
    let query = json!({
        "where": {
            "customer_id_eq": "cust-001"
        },
        "groupBy": {
            "occurred_at": "month"
        },
        "aggregates": [{"revenue_sum": {}}],
        "having": {
            "revenue_sum_gt": 1000.0
        },
        "orderBy": {"revenue_sum": "DESC"},
        "limit": 5
    });

    let result = parse_plan_generate_full(&query);

    // Verify all clauses present with temporal bucketing
    assert_sql_contains(
        &result.sql,
        &[
            "WHERE",
            "customer_id",
            "DATE_TRUNC('month', occurred_at)",
            "GROUP BY",
            "HAVING",
            "SUM(revenue) >",
            "ORDER BY",
            "DESC",
            "LIMIT 5",
        ],
    );
    assert!(result.params.contains(&json!("cust-001")), "WHERE value must be a bind param");
    assert!(result.params.contains(&json!(1000.0)), "HAVING value must be a bind param");
}

#[test]
fn test_temporal_bucket_week_quarter_year() {
    // Test less common bucket types
    for bucket in &["week", "quarter", "year"] {
        let query = json!({
            "groupBy": {
                "occurred_at": bucket
            },
            "aggregates": [{"count": {}}, {"revenue_sum": {}}]
        });

        let sql = parse_plan_generate(&query);

        // PostgreSQL generates DATE_TRUNC for all types
        assert!(sql.contains(&format!("DATE_TRUNC('{}', occurred_at)", bucket)));
        assert!(sql.contains("GROUP BY"));
        assert!(sql.contains("COUNT(*)"));
        assert!(sql.contains("SUM(revenue)"));
    }
}

// =============================================================================
// Advanced Aggregates Tests
// =============================================================================

#[test]
fn test_string_agg_simple() {
    let query = json!({
        "groupBy": {"category": true},
        "aggregates": [
            {"count": {}},
            {"customer_id_string_agg": {}}
        ]
    });

    let sql = parse_plan_generate(&query);

    // PostgreSQL uses STRING_AGG
    assert_sql_contains(&sql, &["GROUP BY", "STRING_AGG(customer_id"]);
}

#[test]
fn test_array_agg_simple() {
    let query = json!({
        "groupBy": {"category": true},
        "aggregates": [
            {"count": {}},
            {"customer_id_array_agg": {}}
        ]
    });

    let sql = parse_plan_generate(&query);

    // PostgreSQL uses ARRAY_AGG
    assert_sql_contains(&sql, &["GROUP BY", "ARRAY_AGG(customer_id"]);
}

#[test]
fn test_advanced_aggregates_multi_database() {
    use fraiseql_core::{
        compiler::aggregation::AggregationPlanner,
        db::DatabaseType,
        runtime::{AggregateQueryParser, AggregationSqlGenerator},
    };

    let query = json!({
        "table": "tf_sales",
        "groupBy": {"category": true},
        "aggregates": [
            {"customer_id_string_agg": {}}
        ]
    });

    let metadata = create_sales_metadata();

    // PostgreSQL
    let pg_gen = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
    let pg_parsed =
        AggregateQueryParser::parse(&query, &metadata, &std::collections::HashMap::new()).unwrap();
    let pg_plan = AggregationPlanner::plan(pg_parsed, metadata.clone()).unwrap();
    let pg_sql = pg_gen.generate_parameterized(&pg_plan).unwrap();
    assert!(pg_sql.sql.contains("STRING_AGG(customer_id"));

    // MySQL
    let mysql_gen = AggregationSqlGenerator::new(DatabaseType::MySQL);
    let mysql_parsed =
        AggregateQueryParser::parse(&query, &metadata, &std::collections::HashMap::new()).unwrap();
    let mysql_plan = AggregationPlanner::plan(mysql_parsed, metadata.clone()).unwrap();
    let mysql_sql = mysql_gen.generate_parameterized(&mysql_plan).unwrap();
    assert!(mysql_sql.sql.contains("GROUP_CONCAT(customer_id"));

    // SQLite
    let sqlite_gen = AggregationSqlGenerator::new(DatabaseType::SQLite);
    let sqlite_parsed =
        AggregateQueryParser::parse(&query, &metadata, &std::collections::HashMap::new()).unwrap();
    let sqlite_plan = AggregationPlanner::plan(sqlite_parsed, metadata.clone()).unwrap();
    let sqlite_sql = sqlite_gen.generate_parameterized(&sqlite_plan).unwrap();
    assert!(sqlite_sql.sql.contains("GROUP_CONCAT(customer_id"));

    // SQL Server
    let mssql_gen = AggregationSqlGenerator::new(DatabaseType::SQLServer);
    let mssql_parsed =
        AggregateQueryParser::parse(&query, &metadata, &std::collections::HashMap::new()).unwrap();
    let mssql_plan = AggregationPlanner::plan(mssql_parsed, metadata).unwrap();
    let mssql_sql = mssql_gen.generate_parameterized(&mssql_plan).unwrap();
    assert!(mssql_sql.sql.contains("STRING_AGG"));
}

// =============================================================================
// Statistical Functions Tests
// =============================================================================

#[test]
fn test_stddev_postgres_mysql() {
    use fraiseql_core::{
        compiler::aggregation::AggregationPlanner,
        db::DatabaseType,
        runtime::{AggregateQueryParser, AggregationSqlGenerator},
    };

    let query = json!({
        "table": "tf_sales",
        "groupBy": {"category": true},
        "aggregates": [
            {"count": {}},
            {"revenue_stddev": {}}
        ]
    });

    let metadata = create_sales_metadata();

    // PostgreSQL
    let pg_gen = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
    let pg_parsed =
        AggregateQueryParser::parse(&query, &metadata, &std::collections::HashMap::new()).unwrap();
    let pg_plan = AggregationPlanner::plan(pg_parsed, metadata.clone()).unwrap();
    let pg_sql = pg_gen.generate_parameterized(&pg_plan).unwrap();
    assert!(pg_sql.sql.contains("STDDEV_SAMP(revenue)"));

    // MySQL
    let mysql_gen = AggregationSqlGenerator::new(DatabaseType::MySQL);
    let mysql_parsed =
        AggregateQueryParser::parse(&query, &metadata, &std::collections::HashMap::new()).unwrap();
    let mysql_plan = AggregationPlanner::plan(mysql_parsed, metadata.clone()).unwrap();
    let mysql_sql = mysql_gen.generate_parameterized(&mysql_plan).unwrap();
    assert!(mysql_sql.sql.contains("STDDEV_SAMP(revenue)"));

    // SQL Server
    let mssql_gen = AggregationSqlGenerator::new(DatabaseType::SQLServer);
    let mssql_parsed =
        AggregateQueryParser::parse(&query, &metadata, &std::collections::HashMap::new()).unwrap();
    let mssql_plan = AggregationPlanner::plan(mssql_parsed, metadata).unwrap();
    let mssql_sql = mssql_gen.generate_parameterized(&mssql_plan).unwrap();
    assert!(mssql_sql.sql.contains("STDEV(revenue)"));
}

#[test]
fn test_variance_postgres_mysql() {
    use fraiseql_core::{
        compiler::aggregation::AggregationPlanner,
        db::DatabaseType,
        runtime::{AggregateQueryParser, AggregationSqlGenerator},
    };

    let query = json!({
        "table": "tf_sales",
        "groupBy": {"category": true},
        "aggregates": [
            {"count": {}},
            {"revenue_variance": {}}
        ]
    });

    let metadata = create_sales_metadata();

    // PostgreSQL
    let pg_gen = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
    let pg_parsed =
        AggregateQueryParser::parse(&query, &metadata, &std::collections::HashMap::new()).unwrap();
    let pg_plan = AggregationPlanner::plan(pg_parsed, metadata.clone()).unwrap();
    let pg_sql = pg_gen.generate_parameterized(&pg_plan).unwrap();
    assert!(pg_sql.sql.contains("VAR_SAMP(revenue)"));

    // MySQL
    let mysql_gen = AggregationSqlGenerator::new(DatabaseType::MySQL);
    let mysql_parsed =
        AggregateQueryParser::parse(&query, &metadata, &std::collections::HashMap::new()).unwrap();
    let mysql_plan = AggregationPlanner::plan(mysql_parsed, metadata.clone()).unwrap();
    let mysql_sql = mysql_gen.generate_parameterized(&mysql_plan).unwrap();
    assert!(mysql_sql.sql.contains("VAR_SAMP(revenue)"));

    // SQL Server
    let mssql_gen = AggregationSqlGenerator::new(DatabaseType::SQLServer);
    let mssql_parsed =
        AggregateQueryParser::parse(&query, &metadata, &std::collections::HashMap::new()).unwrap();
    let mssql_plan = AggregationPlanner::plan(mssql_parsed, metadata).unwrap();
    let mssql_sql = mssql_gen.generate_parameterized(&mssql_plan).unwrap();
    assert!(mssql_sql.sql.contains("VAR(revenue)"));
}

#[test]
fn test_statistical_functions_sqlite_unsupported() {
    use fraiseql_core::{
        compiler::aggregation::AggregationPlanner,
        db::DatabaseType,
        runtime::{AggregateQueryParser, AggregationSqlGenerator},
    };

    let query = json!({
        "table": "tf_sales",
        "groupBy": {"category": true},
        "aggregates": [
            {"count": {}},
            {"revenue_stddev": {}},
            {"revenue_variance": {}}
        ]
    });

    let metadata = create_sales_metadata();

    // SQLite - should return NULL placeholders
    let sqlite_gen = AggregationSqlGenerator::new(DatabaseType::SQLite);
    let sqlite_parsed =
        AggregateQueryParser::parse(&query, &metadata, &std::collections::HashMap::new()).unwrap();
    let sqlite_plan = AggregationPlanner::plan(sqlite_parsed, metadata).unwrap();
    let sqlite_sql = sqlite_gen.generate_parameterized(&sqlite_plan).unwrap();

    // SQLite doesn't support STDDEV/VARIANCE natively
    assert!(sqlite_sql.sql.contains("NULL /* STDDEV not supported"));
    assert!(sqlite_sql.sql.contains("NULL /* VARIANCE not supported"));
}

// =============================================================================
// Cross-Feature Integration Tests
// =============================================================================

/// Create metadata with `native_measures` and `native_dimension_mapping` for cross-feature tests
fn create_native_metadata() -> fraiseql_core::compiler::fact_table::FactTableMetadata {
    use fraiseql_core::compiler::fact_table::*;

    FactTableMetadata {
        table_name:               "tf_metrics".to_string(),
        measures:                 vec![MeasureColumn {
            name:     "raw_value".to_string(),
            sql_type: SqlType::Decimal,
            nullable: false,
        }],
        dimensions:               DimensionColumn {
            name:  "data".to_string(),
            paths: vec![DimensionPath {
                name:      "source".to_string(),
                json_path: "data->>'source'".to_string(),
                data_type: "text".to_string(),
            }],
        },
        denormalized_filters:     vec![FilterColumn {
            name:     "device_id".to_string(),
            sql_type: SqlType::Text,
            indexed:  true,
        }],
        calendar_dimensions:      vec![],
        partial_period:           None,
        native_measures:          std::collections::HashMap::from([
            ("measures.volume".to_string(), "volume".to_string()),
            ("measures.latency".to_string(), "latency_ms".to_string()),
        ]),
        native_dimension_mapping: std::collections::HashMap::from([
            ("dimensions.category.id".to_string(), "category_id".to_string()),
            ("dimensions.region.code".to_string(), "region_code".to_string()),
        ]),
    }
}

#[test]
fn test_native_measures_and_dimension_mapping_combined() {
    // Exercises both native_measures (Phase 3) and native_dimension_mapping (Phase 4) together
    let metadata = create_native_metadata();
    let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);

    let query = json!({
        "table": "tf_metrics",
        "aggregates": [
            {"measures.volume_sum": {}},
            {"measures.latency_avg": {}}
        ],
        "groupBy": {"dimensions.category.id": true}
    });

    let parsed =
        AggregateQueryParser::parse(&query, &metadata, &std::collections::HashMap::new()).unwrap();
    let plan =
        fraiseql_core::compiler::aggregation::AggregationPlanner::plan(parsed, metadata).unwrap();
    let result = generator.generate_parameterized(&plan).unwrap();

    // Native measures should produce quoted identifiers, not JSONB extraction
    assert_sql_contains(&result.sql, &["SUM(\"volume\")", "AVG(\"latency_ms\")"]);
    // Native dimension mapping should produce quoted GROUP BY column
    assert_sql_contains(&result.sql, &["GROUP BY", "\"category_id\""]);
    // Should NOT contain JSONB extraction for the native columns
    assert!(
        !result.sql.contains("data->>"),
        "Native columns should not use JSONB extraction: {}",
        result.sql
    );
}

#[test]
fn test_native_and_jsonb_dimensions_coexist() {
    // A query that groups by both a native-mapped dimension and a regular JSONB dimension
    let metadata = create_native_metadata();
    let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);

    let query = json!({
        "table": "tf_metrics",
        "aggregates": [{"measures.volume_sum": {}}],
        "groupBy": {"dimensions.category.id": true, "source": true}
    });

    let parsed =
        AggregateQueryParser::parse(&query, &metadata, &std::collections::HashMap::new()).unwrap();
    let plan =
        fraiseql_core::compiler::aggregation::AggregationPlanner::plan(parsed, metadata).unwrap();
    let result = generator.generate_parameterized(&plan).unwrap();

    // Native dimension → quoted identifier
    assert_sql_contains(&result.sql, &["\"category_id\""]);
    // JSONB dimension → extraction expression
    assert_sql_contains(&result.sql, &["data->>'source'"]);
}

#[test]
fn test_native_measures_with_where_filter() {
    // Native measures combined with WHERE clause on denormalized filter
    let metadata = create_native_metadata();
    let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);

    let query = json!({
        "table": "tf_metrics",
        "where": { "device_id_eq": "sensor-42" },
        "aggregates": [{"measures.volume_sum": {}}],
        "groupBy": {"dimensions.region.code": true}
    });

    let parsed =
        AggregateQueryParser::parse(&query, &metadata, &std::collections::HashMap::new()).unwrap();
    let plan =
        fraiseql_core::compiler::aggregation::AggregationPlanner::plan(parsed, metadata).unwrap();
    let result = generator.generate_parameterized(&plan).unwrap();

    assert_sql_contains(
        &result.sql,
        &[
            "SUM(\"volume\")",
            "WHERE",
            "device_id",
            "GROUP BY",
            "\"region_code\"",
        ],
    );
    assert!(result.params.contains(&json!("sensor-42")));
}