fraiseql-core 2.3.2

#![allow(clippy::unwrap_used, clippy::panic)] // Reason: test code, panics acceptable
//! Tests end-to-end analytics functionality:
//! - Fact table metadata in schema
//! - Validator rejects invalid schemas
//! - Query routing dispatcher works correctly
//! - Aggregate query execution

#![allow(clippy::default_trait_access)] // Reason: test setup uses Default::default() for readability
#![allow(clippy::items_after_statements)] // Reason: test-local use statements after variable declarations
use std::{collections::HashMap, sync::Arc};

use async_trait::async_trait;
use fraiseql_core::{
    compiler::{
        fact_table::{DimensionColumn, FactTableMetadata, FilterColumn, MeasureColumn, SqlType},
        ir::{AuthoringIR, IRField, IRType},
        parser::SchemaParser,
        validator::SchemaValidator,
    },
    db::{
        traits::{DatabaseAdapter, SupportsMutations},
        types::{DatabaseType, JsonbValue, OrderByClause, PoolMetrics},
        where_clause::{WhereClause, WhereOperator},
    },
    error::Result,
    runtime::{Executor, aggregation::AggregationSqlGenerator},
    schema::{CompiledSchema, SqlProjectionHint},
};
use fraiseql_test_utils::schema_builder::{TestQueryBuilder, TestSchemaBuilder};
use serde_json::json;

/// Mock database adapter for testing
struct MockAdapter {
    mock_results: Vec<JsonbValue>,
}

impl MockAdapter {
    const fn new(mock_results: Vec<JsonbValue>) -> Self {
        Self { mock_results }
    }
}

// Reason: DatabaseAdapter is defined with #[async_trait]; all implementations must match
// its transformed method signatures to satisfy the trait contract
#[async_trait]
impl DatabaseAdapter for MockAdapter {
    async fn execute_with_projection(
        &self,
        view: &str,
        _projection: Option<&SqlProjectionHint>,
        where_clause: Option<&WhereClause>,
        limit: Option<u32>,
        _offset: Option<u32>,
        _order_by: Option<&[OrderByClause]>,
    ) -> Result<Vec<JsonbValue>> {
        // Fall back to standard query for tests
        self.execute_where_query(view, where_clause, limit, None, None).await
    }

    async fn execute_where_query(
        &self,
        _view: &str,
        _where_clause: Option<&WhereClause>,
        _limit: Option<u32>,
        _offset: Option<u32>,
        _order_by: Option<&[OrderByClause]>,
    ) -> Result<Vec<JsonbValue>> {
        Ok(self.mock_results.clone())
    }

    async fn health_check(&self) -> Result<()> {
        Ok(())
    }

    fn database_type(&self) -> DatabaseType {
        DatabaseType::PostgreSQL
    }

    fn pool_metrics(&self) -> PoolMetrics {
        PoolMetrics {
            total_connections:  1,
            active_connections: 0,
            idle_connections:   1,
            waiting_requests:   0,
        }
    }

    async fn execute_raw_query(
        &self,
        _sql: &str,
    ) -> Result<Vec<std::collections::HashMap<String, serde_json::Value>>> {
        // Mock implementation: return aggregate results
        let mut result = HashMap::new();
        result.insert("count".to_string(), json!(10));
        result.insert("revenue_sum".to_string(), json!(1500.50));
        Ok(vec![result])
    }

    async fn execute_parameterized_aggregate(
        &self,
        _sql: &str,
        _params: &[serde_json::Value],
    ) -> Result<Vec<std::collections::HashMap<String, serde_json::Value>>> {
        let mut result = HashMap::new();
        result.insert("count".to_string(), json!(10));
        result.insert("revenue_sum".to_string(), json!(1500.50));
        Ok(vec![result])
    }

    async fn execute_function_call(
        &self,
        _function_name: &str,
        _args: &[serde_json::Value],
    ) -> Result<Vec<std::collections::HashMap<String, serde_json::Value>>> {
        Ok(vec![])
    }
}

impl SupportsMutations for MockAdapter {}

/// Test that schema with fact tables can be parsed and validated
#[test]
fn test_schema_with_fact_tables_validation() {
    let mut ir = AuthoringIR::new();

    // Add fact table metadata
    ir.fact_tables.insert(
        "tf_sales".to_string(),
        FactTableMetadata {
            table_name:               "tf_sales".to_string(),
            measures:                 vec![
                MeasureColumn {
                    name:     "revenue".to_string(),
                    sql_type: SqlType::Decimal,
                    nullable: false,
                },
                MeasureColumn {
                    name:     "quantity".to_string(),
                    sql_type: SqlType::Int,
                    nullable: false,
                },
            ],
            dimensions:               DimensionColumn {
                name:  "data".to_string(),
                paths: vec![],
            },
            denormalized_filters:     vec![],
            calendar_dimensions:      vec![],
            partial_period:           None,
            native_measures:          std::collections::HashMap::new(),
            native_dimension_mapping: std::collections::HashMap::new(),
        },
    );

    // Validate
    let validator = SchemaValidator::new();
    let result = validator.validate(ir);

    result.unwrap_or_else(|e| panic!("expected schema with fact tables to pass validation: {e}"));
}

/// Test that validator rejects fact table without tf_ prefix
#[test]
fn test_validator_rejects_invalid_fact_table_prefix() {
    let mut ir = AuthoringIR::new();

    ir.fact_tables.insert(
        "sales".to_string(),
        FactTableMetadata {
            table_name:               "sales".to_string(),
            measures:                 vec![MeasureColumn {
                name:     "revenue".to_string(),
                sql_type: SqlType::Decimal,
                nullable: false,
            }],
            dimensions:               DimensionColumn {
                name:  "data".to_string(),
                paths: vec![],
            },
            denormalized_filters:     vec![],
            calendar_dimensions:      vec![],
            partial_period:           None,
            native_measures:          std::collections::HashMap::new(),
            native_dimension_mapping: std::collections::HashMap::new(),
        },
    ); // Missing tf_ prefix

    let validator = SchemaValidator::new();
    let result = validator.validate(ir);

    assert!(
        result.is_err(),
        "expected Err for fact table without tf_ prefix, got: {result:?}"
    );
    if let Err(e) = result {
        let error_msg = format!("{}", e);
        assert!(error_msg.contains("must start with 'tf_' prefix"));
    }
}

/// Test that validator rejects fact table without measures
#[test]
fn test_validator_rejects_fact_table_without_measures() {
    let mut ir = AuthoringIR::new();

    ir.fact_tables.insert(
        "tf_sales".to_string(),
        FactTableMetadata {
            table_name:               "tf_sales".to_string(),
            measures:                 vec![],
            dimensions:               DimensionColumn {
                name:  "data".to_string(),
                paths: vec![],
            },
            denormalized_filters:     vec![],
            calendar_dimensions:      vec![],
            partial_period:           None,
            native_measures:          std::collections::HashMap::new(),
            native_dimension_mapping: std::collections::HashMap::new(),
        },
    );

    let validator = SchemaValidator::new();
    let result = validator.validate(ir);

    assert!(result.is_err(), "expected Err for fact table without measures, got: {result:?}");
    if let Err(e) = result {
        let error_msg = format!("{}", e);
        assert!(error_msg.contains("must have at least one measure"));
    }
}

/// Test that validator rejects aggregate type without count field
#[test]
fn test_validator_rejects_aggregate_type_without_count() {
    let mut ir = AuthoringIR::new();

    ir.types.push(IRType {
        name:        "SalesAggregate".to_string(),
        fields:      vec![IRField {
            name:        "revenue_sum".to_string(),
            field_type:  "Float".to_string(),
            nullable:    true,
            description: None,
            sql_column:  None,
        }],
        sql_source:  None,
        description: None,
    });

    let validator = SchemaValidator::new();
    let result = validator.validate(ir);

    assert!(
        result.is_err(),
        "expected Err for aggregate type without count field, got: {result:?}"
    );
    if let Err(e) = result {
        let error_msg = format!("{}", e);
        assert!(error_msg.contains("must have a 'count' field"));
    }
}

/// Test that validator accepts valid aggregate type
#[test]
fn test_validator_accepts_valid_aggregate_type() {
    let mut ir = AuthoringIR::new();

    ir.types.push(IRType {
        name:        "SalesAggregate".to_string(),
        fields:      vec![
            IRField {
                name:        "count".to_string(),
                field_type:  "Int!".to_string(),
                nullable:    false,
                description: None,
                sql_column:  None,
            },
            IRField {
                name:        "revenue_sum".to_string(),
                field_type:  "Float".to_string(),
                nullable:    true,
                description: None,
                sql_column:  None,
            },
        ],
        sql_source:  None,
        description: None,
    });

    let validator = SchemaValidator::new();
    let result = validator.validate(ir);

    result.unwrap_or_else(|e| panic!("expected valid aggregate type to pass validation: {e}"));
}

/// Test schema compilation with fact tables
#[test]
fn test_schema_compilation_with_fact_tables() {
    let schema_json = json!({
        "types": [],
        "queries": [],
        "mutations": [],
        "subscriptions": [],
        "fact_tables": {
            "tf_sales": {
                "table_name": "tf_sales",
                "measures": [
                    {"name": "revenue", "sql_type": "Decimal", "nullable": false}
                ],
                "dimensions": {
                    "name": "data",
                    "paths": []
                },
                "denormalized_filters": []
            }
        }
    });

    // Parse schema
    let parser = SchemaParser::new();
    let ir = parser.parse(&schema_json.to_string()).expect("Failed to parse schema");

    // Verify fact tables were parsed
    assert_eq!(ir.fact_tables.len(), 1);
    assert!(ir.fact_tables.contains_key("tf_sales"));

    // Validate
    let validator = SchemaValidator::new();
    let validated_ir = validator.validate(ir).expect("Validation failed");

    // Verify fact tables persist through validation
    assert_eq!(validated_ir.fact_tables.len(), 1);
}

/// Test executor query classification for regular queries
#[tokio::test]
async fn test_executor_classifies_regular_query() {
    let schema = create_test_schema();
    let adapter = Arc::new(MockAdapter::new(mock_user_results()));
    let executor = Executor::new(schema, adapter);

    let query = "{ users { id name } }";
    let result = executor.execute(query, None).await;

    // Should succeed as regular query
    result.unwrap_or_else(|e| panic!("expected regular query to succeed: {e}"));
}

/// Test executor query classification for aggregate queries
#[tokio::test]
async fn test_executor_classifies_aggregate_query() {
    let mut schema = CompiledSchema::new();

    // Add fact table metadata
    schema.add_fact_table(
        "tf_sales".to_string(),
        FactTableMetadata {
            table_name:               "tf_sales".to_string(),
            measures:                 vec![MeasureColumn {
                name:     "revenue".to_string(),
                sql_type: SqlType::Decimal,
                nullable: false,
            }],
            dimensions:               DimensionColumn {
                name:  "data".to_string(),
                paths: vec![],
            },
            denormalized_filters:     vec![],
            calendar_dimensions:      vec![],
            partial_period:           None,
            native_measures:          std::collections::HashMap::new(),
            native_dimension_mapping: std::collections::HashMap::new(),
        },
    );

    let adapter = Arc::new(MockAdapter::new(vec![]));
    let executor = Executor::new(schema, adapter);

    let query = "{ sales_aggregate { count } }";
    let variables = json!({});

    let result = executor.execute(query, Some(&variables)).await;

    // Without full fact table setup, this will produce an error, but the executor
    // should still attempt to process the query without panicking.
    // A panic here would indicate a routing failure.
    drop(result);
}

/// Test end-to-end: parse schema with fact tables, validate, and verify metadata
#[test]
fn test_end_to_end_fact_table_flow() {
    // 1. Create schema with fact table
    let schema_json = json!({
        "types": [
            {
                "name": "SalesAggregate",
                "fields": [
                    {"name": "count", "type": "Int!", "nullable": false},
                    {"name": "revenue_sum", "type": "Float", "nullable": true}
                ],
                "sql_source": null,
                "description": null
            }
        ],
        "queries": [],
        "mutations": [],
        "subscriptions": [],
        "fact_tables": {
            "tf_sales": {
                "table_name": "tf_sales",
                "measures": [
                    {"name": "revenue", "sql_type": "Decimal", "nullable": false},
                    {"name": "quantity", "sql_type": "Int", "nullable": false}
                ],
                "dimensions": {
                    "name": "data",
                    "paths": [
                        {"name": "category", "json_path": "data->>'category'", "data_type": "String"}
                    ]
                },
                "denormalized_filters": [
                    {"name": "customer_id", "sql_type": "Uuid", "indexed": true}
                ]
            }
        }
    });

    // 2. Parse
    let parser = SchemaParser::new();
    let ir = parser.parse(&schema_json.to_string()).expect("Failed to parse");

    assert_eq!(ir.fact_tables.len(), 1);
    assert_eq!(ir.types.len(), 1);

    // 3. Validate
    let validator = SchemaValidator::new();
    let validated_ir = validator.validate(ir).expect("Validation failed");

    // 4. Verify structure
    assert_eq!(validated_ir.fact_tables.len(), 1);
    assert_eq!(validated_ir.types.len(), 1);

    let fact_table = validated_ir.fact_tables.get("tf_sales").unwrap();
    assert_eq!(fact_table.measures.len(), 2);

    let aggregate_type = &validated_ir.types[0];
    assert_eq!(aggregate_type.name, "SalesAggregate");
    assert!(aggregate_type.fields.iter().any(|f| f.name == "count"));
}

// Helper functions

// Migration 1: create_test_schema
fn create_test_schema() -> CompiledSchema {
    TestSchemaBuilder::new()
        .with_query(
            TestQueryBuilder::new("users", "User")
                .returns_list(true)
                .with_sql_source("v_user")
                .build(),
        )
        .build()
}

fn mock_user_results() -> Vec<JsonbValue> {
    vec![
        JsonbValue::new(json!({"id": "1", "name": "Alice"})),
        JsonbValue::new(json!({"id": "2", "name": "Bob"})),
    ]
}

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

/// Create test fact table metadata for WHERE clause tests
fn create_test_fact_table_metadata() -> FactTableMetadata {
    FactTableMetadata {
        table_name:               "tf_sales".to_string(),
        measures:                 vec![MeasureColumn {
            name:     "revenue".to_string(),
            sql_type: SqlType::Decimal,
            nullable: false,
        }],
        dimensions:               DimensionColumn {
            name:  "data".to_string(),
            paths: vec![],
        },
        denormalized_filters:     vec![
            FilterColumn {
                name:     "customer_id".to_string(),
                sql_type: SqlType::Uuid,
                indexed:  true,
            },
            FilterColumn {
                name:     "occurred_at".to_string(),
                sql_type: SqlType::Timestamp,
                indexed:  true,
            },
        ],
        calendar_dimensions:      vec![],
        partial_period:           None,
        native_measures:          std::collections::HashMap::new(),
        native_dimension_mapping: std::collections::HashMap::new(),
    }
}

/// Test WHERE clause with denormalized filter (direct column)
#[test]
fn test_where_denormalized_filter() {
    let metadata = create_test_fact_table_metadata();
    let where_clause = WhereClause::Field {
        path:     vec!["customer_id".to_string()],
        operator: WhereOperator::Eq,
        value:    json!("550e8400-e29b-41d4-a716-446655440000"),
    };

    let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
    let mut params: Vec<serde_json::Value> = vec![];
    let sql = generator
        .build_where_clause_parameterized(&where_clause, &metadata, &mut params)
        .unwrap();

    assert!(sql.contains("WHERE"));
    assert!(sql.contains("customer_id"));
    assert!(sql.contains('='));
    assert!(params.contains(&json!("550e8400-e29b-41d4-a716-446655440000")));
    // Should be direct column, not JSONB
    assert!(!sql.contains("->"));
}

/// Test WHERE clause with JSONB dimension
#[test]
fn test_where_jsonb_dimension() {
    let metadata = create_test_fact_table_metadata();
    let where_clause = WhereClause::Field {
        path:     vec!["category".to_string()],
        operator: WhereOperator::Eq,
        value:    json!("electronics"),
    };

    let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
    let mut params: Vec<serde_json::Value> = vec![];
    let sql = generator
        .build_where_clause_parameterized(&where_clause, &metadata, &mut params)
        .unwrap();

    assert!(sql.contains("WHERE"));
    assert!(sql.contains("data->>'category'"));
    assert!(sql.contains('='));
    assert!(params.contains(&json!("electronics")));
}

/// Test WHERE clause with AND operator (denormalized + JSONB)
#[test]
fn test_where_and_operator() {
    let metadata = create_test_fact_table_metadata();
    let where_clause = WhereClause::And(vec![
        WhereClause::Field {
            path:     vec!["customer_id".to_string()],
            operator: WhereOperator::Eq,
            value:    json!("550e8400-e29b-41d4-a716-446655440000"),
        },
        WhereClause::Field {
            path:     vec!["category".to_string()],
            operator: WhereOperator::Eq,
            value:    json!("electronics"),
        },
    ]);

    let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
    let mut params: Vec<serde_json::Value> = vec![];
    let sql = generator
        .build_where_clause_parameterized(&where_clause, &metadata, &mut params)
        .unwrap();

    assert!(sql.contains("customer_id"));
    assert!(sql.contains("data->>'category'"));
    assert!(sql.contains(" AND "));
}

/// Test WHERE clause with OR operator
#[test]
fn test_where_or_operator() {
    let metadata = create_test_fact_table_metadata();
    let where_clause = WhereClause::Or(vec![
        WhereClause::Field {
            path:     vec!["category".to_string()],
            operator: WhereOperator::Eq,
            value:    json!("electronics"),
        },
        WhereClause::Field {
            path:     vec!["category".to_string()],
            operator: WhereOperator::Eq,
            value:    json!("furniture"),
        },
    ]);

    let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
    let mut params: Vec<serde_json::Value> = vec![];
    let sql = generator
        .build_where_clause_parameterized(&where_clause, &metadata, &mut params)
        .unwrap();

    assert!(sql.contains("data->>'category'"));
    assert!(sql.contains(" OR "));
    assert!(params.contains(&json!("electronics")));
    assert!(params.contains(&json!("furniture")));
}

/// Test WHERE clause with NOT operator
#[test]
fn test_where_not_operator() {
    let metadata = create_test_fact_table_metadata();
    let where_clause = WhereClause::Not(Box::new(WhereClause::Field {
        path:     vec!["category".to_string()],
        operator: WhereOperator::Eq,
        value:    json!("electronics"),
    }));

    let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
    let mut params: Vec<serde_json::Value> = vec![];
    let sql = generator
        .build_where_clause_parameterized(&where_clause, &metadata, &mut params)
        .unwrap();

    assert!(sql.contains("NOT"));
    assert!(sql.contains("data->>'category'"));
}

/// Test WHERE clause with comparison operators
#[test]
fn test_where_comparison_operators() {
    let metadata = create_test_fact_table_metadata();

    // Test Gt
    let where_clause = WhereClause::Field {
        path:     vec!["occurred_at".to_string()],
        operator: WhereOperator::Gt,
        value:    json!("2024-01-01"),
    };
    let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
    let mut params: Vec<serde_json::Value> = vec![];
    let sql = generator
        .build_where_clause_parameterized(&where_clause, &metadata, &mut params)
        .unwrap();
    assert!(sql.contains('>'));

    // Test Lte
    let where_clause = WhereClause::Field {
        path:     vec!["occurred_at".to_string()],
        operator: WhereOperator::Lte,
        value:    json!("2024-12-31"),
    };
    let mut params: Vec<serde_json::Value> = vec![];
    let sql = generator
        .build_where_clause_parameterized(&where_clause, &metadata, &mut params)
        .unwrap();
    assert!(sql.contains("<="));
}

/// Test WHERE clause with IN operator
#[test]
fn test_where_in_operator() {
    let metadata = create_test_fact_table_metadata();
    let where_clause = WhereClause::Field {
        path:     vec!["category".to_string()],
        operator: WhereOperator::In,
        value:    json!(["electronics", "furniture", "clothing"]),
    };

    let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
    let mut params: Vec<serde_json::Value> = vec![];
    let sql = generator
        .build_where_clause_parameterized(&where_clause, &metadata, &mut params)
        .unwrap();

    assert!(sql.contains("IN"));
    assert!(params.contains(&json!("electronics")));
    assert!(params.contains(&json!("furniture")));
    assert!(params.contains(&json!("clothing")));
}

/// Test WHERE clause with LIKE operator
#[test]
fn test_where_like_operators() {
    let metadata = create_test_fact_table_metadata();

    // Test Contains (LIKE '%value%')
    let where_clause = WhereClause::Field {
        path:     vec!["category".to_string()],
        operator: WhereOperator::Contains,
        value:    json!("electr"),
    };
    let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
    let mut params: Vec<serde_json::Value> = vec![];
    let sql = generator
        .build_where_clause_parameterized(&where_clause, &metadata, &mut params)
        .unwrap();
    assert!(sql.contains("LIKE"));
    assert!(params.iter().any(|p| p.as_str() == Some("%electr%")));

    // Test Startswith (LIKE 'value%')
    let where_clause = WhereClause::Field {
        path:     vec!["category".to_string()],
        operator: WhereOperator::Startswith,
        value:    json!("electr"),
    };
    let mut params: Vec<serde_json::Value> = vec![];
    let sql = generator
        .build_where_clause_parameterized(&where_clause, &metadata, &mut params)
        .unwrap();
    assert!(sql.contains("LIKE"));
    assert!(params.iter().any(|p| p.as_str() == Some("electr%")));

    // Test Endswith (LIKE '%value')
    let where_clause = WhereClause::Field {
        path:     vec!["category".to_string()],
        operator: WhereOperator::Endswith,
        value:    json!("onics"),
    };
    let mut params: Vec<serde_json::Value> = vec![];
    let sql = generator
        .build_where_clause_parameterized(&where_clause, &metadata, &mut params)
        .unwrap();
    assert!(sql.contains("LIKE"));
    assert!(params.iter().any(|p| p.as_str() == Some("%onics")));
}

/// Test WHERE clause with case-insensitive operators (PostgreSQL)
#[test]
fn test_where_case_insensitive_postgresql() {
    let metadata = create_test_fact_table_metadata();
    let where_clause = WhereClause::Field {
        path:     vec!["category".to_string()],
        operator: WhereOperator::Icontains,
        value:    json!("ELECTR"),
    };

    let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
    let mut params: Vec<serde_json::Value> = vec![];
    let sql = generator
        .build_where_clause_parameterized(&where_clause, &metadata, &mut params)
        .unwrap();

    // PostgreSQL should use ILIKE
    assert!(sql.contains("ILIKE"));
    assert!(params.iter().any(|p| p.as_str() == Some("%ELECTR%")));
}

/// Test WHERE clause with case-insensitive operators (MySQL - uses UPPER)
#[test]
fn test_where_case_insensitive_mysql() {
    let metadata = create_test_fact_table_metadata();
    let where_clause = WhereClause::Field {
        path:     vec!["category".to_string()],
        operator: WhereOperator::Icontains,
        value:    json!("electr"),
    };

    let generator = AggregationSqlGenerator::new(DatabaseType::MySQL);
    let mut params: Vec<serde_json::Value> = vec![];
    let sql = generator
        .build_where_clause_parameterized(&where_clause, &metadata, &mut params)
        .unwrap();

    // MySQL should use UPPER() for case-insensitive
    assert!(sql.contains("UPPER"));
    assert!(sql.contains("LIKE"));
}

/// Test WHERE clause with IS NULL operator
#[test]
fn test_where_is_null_operator() {
    let metadata = create_test_fact_table_metadata();
    let where_clause = WhereClause::Field {
        path:     vec!["category".to_string()],
        operator: WhereOperator::IsNull,
        value:    json!(null),
    };

    let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
    let mut params: Vec<serde_json::Value> = vec![];
    let sql = generator
        .build_where_clause_parameterized(&where_clause, &metadata, &mut params)
        .unwrap();

    assert!(sql.contains("IS NULL"));
    assert!(sql.contains("data->>'category'"));
}

/// Test WHERE clause SQL generation across all databases
#[test]
fn test_where_multi_database_compatibility() {
    let metadata = create_test_fact_table_metadata();
    let where_clause = WhereClause::Field {
        path:     vec!["category".to_string()],
        operator: WhereOperator::Eq,
        value:    json!("electronics"),
    };

    // PostgreSQL: data->>'category'
    let pg = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
    let mut params: Vec<serde_json::Value> = vec![];
    let sql = pg
        .build_where_clause_parameterized(&where_clause, &metadata, &mut params)
        .unwrap();
    assert!(sql.contains("data->>'category'"));

    // MySQL: JSON_UNQUOTE(JSON_EXTRACT(...))
    let mysql = AggregationSqlGenerator::new(DatabaseType::MySQL);
    let mut params: Vec<serde_json::Value> = vec![];
    let sql = mysql
        .build_where_clause_parameterized(&where_clause, &metadata, &mut params)
        .unwrap();
    assert!(sql.contains("JSON_EXTRACT") || sql.contains("JSON_UNQUOTE"));

    // SQLite: json_extract(...)
    let sqlite = AggregationSqlGenerator::new(DatabaseType::SQLite);
    let mut params: Vec<serde_json::Value> = vec![];
    let sql = sqlite
        .build_where_clause_parameterized(&where_clause, &metadata, &mut params)
        .unwrap();
    assert!(sql.contains("json_extract"));

    // SQL Server: JSON_VALUE(...)
    let mssql = AggregationSqlGenerator::new(DatabaseType::SQLServer);
    let mut params: Vec<serde_json::Value> = vec![];
    let sql = mssql
        .build_where_clause_parameterized(&where_clause, &metadata, &mut params)
        .unwrap();
    assert!(sql.contains("JSON_VALUE"));
}

/// Test empty WHERE clause
#[test]
fn test_where_empty_clause() {
    let metadata = create_test_fact_table_metadata();
    let where_clause = WhereClause::And(vec![]);

    let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
    let mut params: Vec<serde_json::Value> = vec![];
    let sql = generator
        .build_where_clause_parameterized(&where_clause, &metadata, &mut params)
        .unwrap();

    // Empty WHERE should return empty string (no WHERE keyword)
    assert_eq!(sql, "");
}

/// Test complex nested WHERE clause
#[test]
fn test_where_complex_nested() {
    let metadata = create_test_fact_table_metadata();

    // (customer_id = 'uuid' AND (category = 'electronics' OR category = 'furniture'))
    let where_clause = WhereClause::And(vec![
        WhereClause::Field {
            path:     vec!["customer_id".to_string()],
            operator: WhereOperator::Eq,
            value:    json!("550e8400-e29b-41d4-a716-446655440000"),
        },
        WhereClause::Or(vec![
            WhereClause::Field {
                path:     vec!["category".to_string()],
                operator: WhereOperator::Eq,
                value:    json!("electronics"),
            },
            WhereClause::Field {
                path:     vec!["category".to_string()],
                operator: WhereOperator::Eq,
                value:    json!("furniture"),
            },
        ]),
    ]);

    let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
    let mut params: Vec<serde_json::Value> = vec![];
    let sql = generator
        .build_where_clause_parameterized(&where_clause, &metadata, &mut params)
        .unwrap();

    assert!(sql.contains("customer_id"));
    assert!(sql.contains("data->>'category'"));
    assert!(sql.contains(" AND "));
    assert!(sql.contains(" OR "));
    assert!(params.contains(&json!("electronics")));
    assert!(params.contains(&json!("furniture")));
}