// Auto-generated by test-generator. Do not edit.
// Category: aggregates

#[allow(unused_imports)]
use scythe_codegen as _codegen;
#[allow(unused_imports)]
use syn as _syn;

#[test]
fn test_group_by_count() {
    // From: testing_data/aggregates/group_by/01_group_by_count.json
    // "GROUP BY with COUNT(*) aggregate returns i64 non-nullable"
    let schema_sql = &[
        "CREATE TABLE sales (id SERIAL PRIMARY KEY, employee_id INTEGER NOT NULL, department TEXT NOT NULL, amount INTEGER NOT NULL, sale_date DATE NOT NULL);",
    ];

    let query_sql = "-- @name CountByDepartment\n-- @returns :many\nSELECT department, COUNT(*) AS cnt FROM sales GROUP BY department";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "CountByDepartment", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "department", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for department"
    );
    assert!(
        !analyzed.columns[0].nullable,
        "column nullable for department"
    );
    assert_eq!(analyzed.columns[1].name, "cnt", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "int64",
        "column neutral_type for cnt"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for cnt");

    // Codegen verification: all backends should produce valid output
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "go-pgx",
        "java-jdbc",
        "kotlin-jdbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "ruby-trilogy",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, "postgresql") {
            Ok(b) => b,
            Err(_) => continue, // skip unregistered backends
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            let header = backend.file_header();
            let mut code = if header.is_empty() {
                String::from("#![allow(dead_code, unused_imports)]\n")
            } else {
                let mut h = header;
                h.push('\n');
                h
            };
            if let Some(ref s) = generated.enum_def {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.model_struct {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.row_struct {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.query_fn {
                code.push_str(s);
                code.push('\n');
            }
            if code.lines().count() > 1 {
                // Only validate Rust syntax with syn for Rust backends
                if *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "group_by_count"
                    );
                } else {
                    // Structural validation for non-Rust backends
                    let errors =
                        scythe_codegen::validation::validate_structural(&code, backend_name);
                    assert!(
                        errors.is_empty(),
                        "backend {} structural validation failed for {}: {:?}",
                        backend_name,
                        "group_by_count",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "group_by_count"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "group_by_count"
            );
        }
    }
}

#[test]
fn test_group_by_having() {
    // From: testing_data/aggregates/group_by/02_group_by_having.json
    // "GROUP BY with HAVING clause and parameter"
    let schema_sql = &[
        "CREATE TABLE sales (id SERIAL PRIMARY KEY, employee_id INTEGER NOT NULL, department TEXT NOT NULL, amount INTEGER NOT NULL, sale_date DATE NOT NULL);",
    ];

    let query_sql = "-- @name CountByDepartmentHaving\n-- @returns :many\nSELECT department, COUNT(*) AS cnt FROM sales GROUP BY department HAVING COUNT(*) > $1";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "CountByDepartmentHaving", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.params.len(), 1, "param count");
    assert_eq!(analyzed.params[0].name, "min_count", "param name");
    assert_eq!(
        analyzed.params[0].neutral_type, "int64",
        "param neutral_type for min_count"
    );
    assert!(!analyzed.params[0].nullable, "param nullable for min_count");
    assert_eq!(
        analyzed.params[0].position, 1,
        "param position for min_count"
    );
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "department", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for department"
    );
    assert!(
        !analyzed.columns[0].nullable,
        "column nullable for department"
    );
    assert_eq!(analyzed.columns[1].name, "cnt", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "int64",
        "column neutral_type for cnt"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for cnt");

    // Codegen verification: all backends should produce valid output
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "go-pgx",
        "java-jdbc",
        "kotlin-jdbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "ruby-trilogy",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, "postgresql") {
            Ok(b) => b,
            Err(_) => continue, // skip unregistered backends
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            let header = backend.file_header();
            let mut code = if header.is_empty() {
                String::from("#![allow(dead_code, unused_imports)]\n")
            } else {
                let mut h = header;
                h.push('\n');
                h
            };
            if let Some(ref s) = generated.enum_def {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.model_struct {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.row_struct {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.query_fn {
                code.push_str(s);
                code.push('\n');
            }
            if code.lines().count() > 1 {
                // Only validate Rust syntax with syn for Rust backends
                if *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "group_by_having"
                    );
                } else {
                    // Structural validation for non-Rust backends
                    let errors =
                        scythe_codegen::validation::validate_structural(&code, backend_name);
                    assert!(
                        errors.is_empty(),
                        "backend {} structural validation failed for {}: {:?}",
                        backend_name,
                        "group_by_having",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "group_by_having"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "group_by_having"
            );
        }
    }
}

#[test]
fn test_group_by_multiple_aggregates() {
    // From: testing_data/aggregates/group_by/03_group_by_multiple.json
    // "GROUP BY with multiple aggregate functions: COUNT, SUM, AVG"
    let schema_sql = &[
        "CREATE TABLE sales (id SERIAL PRIMARY KEY, employee_id INTEGER NOT NULL, department TEXT NOT NULL, amount INTEGER NOT NULL, sale_date DATE NOT NULL);",
    ];

    let query_sql = "-- @name MultiAggByDepartment\n-- @returns :many\nSELECT department, COUNT(*) AS cnt, SUM(amount) AS total, AVG(amount) AS avg_amount FROM sales GROUP BY department";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "MultiAggByDepartment", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 4, "column count");
    assert_eq!(analyzed.columns[0].name, "department", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for department"
    );
    assert!(
        !analyzed.columns[0].nullable,
        "column nullable for department"
    );
    assert_eq!(analyzed.columns[1].name, "cnt", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "int64",
        "column neutral_type for cnt"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for cnt");
    assert_eq!(analyzed.columns[2].name, "total", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "int64",
        "column neutral_type for total"
    );
    assert!(analyzed.columns[2].nullable, "column nullable for total");
    assert_eq!(analyzed.columns[3].name, "avg_amount", "column name");
    assert_eq!(
        analyzed.columns[3].neutral_type, "decimal",
        "column neutral_type for avg_amount"
    );
    assert!(
        analyzed.columns[3].nullable,
        "column nullable for avg_amount"
    );

    // Codegen verification: all backends should produce valid output
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "go-pgx",
        "java-jdbc",
        "kotlin-jdbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "ruby-trilogy",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, "postgresql") {
            Ok(b) => b,
            Err(_) => continue, // skip unregistered backends
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            let header = backend.file_header();
            let mut code = if header.is_empty() {
                String::from("#![allow(dead_code, unused_imports)]\n")
            } else {
                let mut h = header;
                h.push('\n');
                h
            };
            if let Some(ref s) = generated.enum_def {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.model_struct {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.row_struct {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.query_fn {
                code.push_str(s);
                code.push('\n');
            }
            if code.lines().count() > 1 {
                // Only validate Rust syntax with syn for Rust backends
                if *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "group_by_multiple_aggregates"
                    );
                } else {
                    // Structural validation for non-Rust backends
                    let errors =
                        scythe_codegen::validation::validate_structural(&code, backend_name);
                    assert!(
                        errors.is_empty(),
                        "backend {} structural validation failed for {}: {:?}",
                        backend_name,
                        "group_by_multiple_aggregates",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "group_by_multiple_aggregates"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "group_by_multiple_aggregates"
            );
        }
    }
}

#[test]
fn test_window_lag_lead() {
    // From: testing_data/aggregates/window_functions/04_lag_lead.json
    // "LAG and LEAD window functions return nullable values when there is no row at the offset"
    let schema_sql = &[
        "CREATE TABLE sales (id SERIAL PRIMARY KEY, employee_id INTEGER NOT NULL, department TEXT NOT NULL, amount INTEGER NOT NULL, sale_date DATE NOT NULL);",
    ];

    let query_sql = "-- @name LagLeadSales\n-- @returns :many\nSELECT id, amount, LAG(amount, 1) OVER (ORDER BY sale_date) AS prev_amount, LEAD(amount, 1) OVER (ORDER BY sale_date) AS next_amount FROM sales";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "LagLeadSales", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 4, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "amount", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "int32",
        "column neutral_type for amount"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for amount");
    assert_eq!(analyzed.columns[2].name, "prev_amount", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "int32",
        "column neutral_type for prev_amount"
    );
    assert!(
        analyzed.columns[2].nullable,
        "column nullable for prev_amount"
    );
    assert_eq!(analyzed.columns[3].name, "next_amount", "column name");
    assert_eq!(
        analyzed.columns[3].neutral_type, "int32",
        "column neutral_type for next_amount"
    );
    assert!(
        analyzed.columns[3].nullable,
        "column nullable for next_amount"
    );

    // Codegen verification: all backends should produce valid output
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "go-pgx",
        "java-jdbc",
        "kotlin-jdbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "ruby-trilogy",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, "postgresql") {
            Ok(b) => b,
            Err(_) => continue, // skip unregistered backends
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            let header = backend.file_header();
            let mut code = if header.is_empty() {
                String::from("#![allow(dead_code, unused_imports)]\n")
            } else {
                let mut h = header;
                h.push('\n');
                h
            };
            if let Some(ref s) = generated.enum_def {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.model_struct {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.row_struct {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.query_fn {
                code.push_str(s);
                code.push('\n');
            }
            if code.lines().count() > 1 {
                // Only validate Rust syntax with syn for Rust backends
                if *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "window_lag_lead"
                    );
                } else {
                    // Structural validation for non-Rust backends
                    let errors =
                        scythe_codegen::validation::validate_structural(&code, backend_name);
                    assert!(
                        errors.is_empty(),
                        "backend {} structural validation failed for {}: {:?}",
                        backend_name,
                        "window_lag_lead",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "window_lag_lead"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "window_lag_lead"
            );
        }
    }
}

#[test]
fn test_window_ntile() {
    // From: testing_data/aggregates/window_functions/06_ntile.json
    // "NTILE() window function returns i64 non-nullable bucket number"
    let schema_sql = &[
        "CREATE TABLE sales (id SERIAL PRIMARY KEY, employee_id INTEGER NOT NULL, department TEXT NOT NULL, amount INTEGER NOT NULL, sale_date DATE NOT NULL);",
    ];

    let query_sql = "-- @name NtileSales\n-- @returns :many\nSELECT id, amount, NTILE(4) OVER (ORDER BY amount) AS quartile FROM sales";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "NtileSales", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 3, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "amount", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "int32",
        "column neutral_type for amount"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for amount");
    assert_eq!(analyzed.columns[2].name, "quartile", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "int64",
        "column neutral_type for quartile"
    );
    assert!(
        !analyzed.columns[2].nullable,
        "column nullable for quartile"
    );

    // Codegen verification: all backends should produce valid output
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "go-pgx",
        "java-jdbc",
        "kotlin-jdbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "ruby-trilogy",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, "postgresql") {
            Ok(b) => b,
            Err(_) => continue, // skip unregistered backends
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            let header = backend.file_header();
            let mut code = if header.is_empty() {
                String::from("#![allow(dead_code, unused_imports)]\n")
            } else {
                let mut h = header;
                h.push('\n');
                h
            };
            if let Some(ref s) = generated.enum_def {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.model_struct {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.row_struct {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.query_fn {
                code.push_str(s);
                code.push('\n');
            }
            if code.lines().count() > 1 {
                // Only validate Rust syntax with syn for Rust backends
                if *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "window_ntile"
                    );
                } else {
                    // Structural validation for non-Rust backends
                    let errors =
                        scythe_codegen::validation::validate_structural(&code, backend_name);
                    assert!(
                        errors.is_empty(),
                        "backend {} structural validation failed for {}: {:?}",
                        backend_name,
                        "window_ntile",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "window_ntile"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "window_ntile"
            );
        }
    }
}

#[test]
fn test_window_partition_by() {
    // From: testing_data/aggregates/window_functions/03_partition_by.json
    // "ROW_NUMBER() with PARTITION BY clause returns i64 non-nullable"
    let schema_sql = &[
        "CREATE TABLE sales (id SERIAL PRIMARY KEY, employee_id INTEGER NOT NULL, department TEXT NOT NULL, amount INTEGER NOT NULL, sale_date DATE NOT NULL);",
    ];

    let query_sql = "-- @name PartitionedSales\n-- @returns :many\nSELECT id, department, amount, ROW_NUMBER() OVER (PARTITION BY department ORDER BY amount DESC) AS dept_rank FROM sales";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "PartitionedSales", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 4, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "department", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for department"
    );
    assert!(
        !analyzed.columns[1].nullable,
        "column nullable for department"
    );
    assert_eq!(analyzed.columns[2].name, "amount", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "int32",
        "column neutral_type for amount"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for amount");
    assert_eq!(analyzed.columns[3].name, "dept_rank", "column name");
    assert_eq!(
        analyzed.columns[3].neutral_type, "int64",
        "column neutral_type for dept_rank"
    );
    assert!(
        !analyzed.columns[3].nullable,
        "column nullable for dept_rank"
    );

    // Codegen verification: all backends should produce valid output
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "go-pgx",
        "java-jdbc",
        "kotlin-jdbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "ruby-trilogy",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, "postgresql") {
            Ok(b) => b,
            Err(_) => continue, // skip unregistered backends
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            let header = backend.file_header();
            let mut code = if header.is_empty() {
                String::from("#![allow(dead_code, unused_imports)]\n")
            } else {
                let mut h = header;
                h.push('\n');
                h
            };
            if let Some(ref s) = generated.enum_def {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.model_struct {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.row_struct {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.query_fn {
                code.push_str(s);
                code.push('\n');
            }
            if code.lines().count() > 1 {
                // Only validate Rust syntax with syn for Rust backends
                if *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "window_partition_by"
                    );
                } else {
                    // Structural validation for non-Rust backends
                    let errors =
                        scythe_codegen::validation::validate_structural(&code, backend_name);
                    assert!(
                        errors.is_empty(),
                        "backend {} structural validation failed for {}: {:?}",
                        backend_name,
                        "window_partition_by",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "window_partition_by"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "window_partition_by"
            );
        }
    }
}

#[test]
fn test_window_rank_dense_rank() {
    // From: testing_data/aggregates/window_functions/02_rank_dense_rank.json
    // "RANK() and DENSE_RANK() window functions return i64 non-nullable"
    let schema_sql = &[
        "CREATE TABLE sales (id SERIAL PRIMARY KEY, employee_id INTEGER NOT NULL, department TEXT NOT NULL, amount INTEGER NOT NULL, sale_date DATE NOT NULL);",
    ];

    let query_sql = "-- @name RankDenseRankSales\n-- @returns :many\nSELECT id, amount, RANK() OVER (ORDER BY amount DESC) AS rnk, DENSE_RANK() OVER (ORDER BY amount DESC) AS drnk FROM sales";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "RankDenseRankSales", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 4, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "amount", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "int32",
        "column neutral_type for amount"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for amount");
    assert_eq!(analyzed.columns[2].name, "rnk", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "int64",
        "column neutral_type for rnk"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for rnk");
    assert_eq!(analyzed.columns[3].name, "drnk", "column name");
    assert_eq!(
        analyzed.columns[3].neutral_type, "int64",
        "column neutral_type for drnk"
    );
    assert!(!analyzed.columns[3].nullable, "column nullable for drnk");

    // Codegen verification: all backends should produce valid output
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "go-pgx",
        "java-jdbc",
        "kotlin-jdbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "ruby-trilogy",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, "postgresql") {
            Ok(b) => b,
            Err(_) => continue, // skip unregistered backends
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            let header = backend.file_header();
            let mut code = if header.is_empty() {
                String::from("#![allow(dead_code, unused_imports)]\n")
            } else {
                let mut h = header;
                h.push('\n');
                h
            };
            if let Some(ref s) = generated.enum_def {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.model_struct {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.row_struct {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.query_fn {
                code.push_str(s);
                code.push('\n');
            }
            if code.lines().count() > 1 {
                // Only validate Rust syntax with syn for Rust backends
                if *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "window_rank_dense_rank"
                    );
                } else {
                    // Structural validation for non-Rust backends
                    let errors =
                        scythe_codegen::validation::validate_structural(&code, backend_name);
                    assert!(
                        errors.is_empty(),
                        "backend {} structural validation failed for {}: {:?}",
                        backend_name,
                        "window_rank_dense_rank",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "window_rank_dense_rank"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "window_rank_dense_rank"
            );
        }
    }
}

#[test]
fn test_window_row_number() {
    // From: testing_data/aggregates/window_functions/01_row_number.json
    // "ROW_NUMBER() window function returns i64 non-nullable"
    let schema_sql = &[
        "CREATE TABLE sales (id SERIAL PRIMARY KEY, employee_id INTEGER NOT NULL, department TEXT NOT NULL, amount INTEGER NOT NULL, sale_date DATE NOT NULL);",
    ];

    let query_sql = "-- @name RankedSales\n-- @returns :many\nSELECT id, amount, ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn FROM sales";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "RankedSales", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 3, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "amount", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "int32",
        "column neutral_type for amount"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for amount");
    assert_eq!(analyzed.columns[2].name, "rn", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "int64",
        "column neutral_type for rn"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for rn");

    // Codegen verification: all backends should produce valid output
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "go-pgx",
        "java-jdbc",
        "kotlin-jdbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "ruby-trilogy",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, "postgresql") {
            Ok(b) => b,
            Err(_) => continue, // skip unregistered backends
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            let header = backend.file_header();
            let mut code = if header.is_empty() {
                String::from("#![allow(dead_code, unused_imports)]\n")
            } else {
                let mut h = header;
                h.push('\n');
                h
            };
            if let Some(ref s) = generated.enum_def {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.model_struct {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.row_struct {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.query_fn {
                code.push_str(s);
                code.push('\n');
            }
            if code.lines().count() > 1 {
                // Only validate Rust syntax with syn for Rust backends
                if *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "window_row_number"
                    );
                } else {
                    // Structural validation for non-Rust backends
                    let errors =
                        scythe_codegen::validation::validate_structural(&code, backend_name);
                    assert!(
                        errors.is_empty(),
                        "backend {} structural validation failed for {}: {:?}",
                        backend_name,
                        "window_row_number",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "window_row_number"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "window_row_number"
            );
        }
    }
}

#[test]
fn test_window_sum_over_partition() {
    // From: testing_data/aggregates/window_functions/05_sum_over.json
    // "Window SUM over partition operates on existing rows and is non-nullable unlike aggregate SUM"
    let schema_sql = &[
        "CREATE TABLE sales (id SERIAL PRIMARY KEY, employee_id INTEGER NOT NULL, department TEXT NOT NULL, amount INTEGER NOT NULL, sale_date DATE NOT NULL);",
    ];

    let query_sql = "-- @name SumOverPartition\n-- @returns :many\nSELECT id, department, amount, SUM(amount) OVER (PARTITION BY department) AS dept_total FROM sales";

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "SumOverPartition", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 4, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "department", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for department"
    );
    assert!(
        !analyzed.columns[1].nullable,
        "column nullable for department"
    );
    assert_eq!(analyzed.columns[2].name, "amount", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "int32",
        "column neutral_type for amount"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for amount");
    assert_eq!(analyzed.columns[3].name, "dept_total", "column name");
    assert_eq!(
        analyzed.columns[3].neutral_type, "int64",
        "column neutral_type for dept_total"
    );
    assert!(
        !analyzed.columns[3].nullable,
        "column nullable for dept_total"
    );

    // Codegen verification: all backends should produce valid output
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "go-pgx",
        "java-jdbc",
        "kotlin-jdbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "ruby-trilogy",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, "postgresql") {
            Ok(b) => b,
            Err(_) => continue, // skip unregistered backends
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            let header = backend.file_header();
            let mut code = if header.is_empty() {
                String::from("#![allow(dead_code, unused_imports)]\n")
            } else {
                let mut h = header;
                h.push('\n');
                h
            };
            if let Some(ref s) = generated.enum_def {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.model_struct {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.row_struct {
                code.push_str(s);
                code.push('\n');
            }
            if let Some(ref s) = generated.query_fn {
                code.push_str(s);
                code.push('\n');
            }
            if code.lines().count() > 1 {
                // Only validate Rust syntax with syn for Rust backends
                if *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "window_sum_over_partition"
                    );
                } else {
                    // Structural validation for non-Rust backends
                    let errors =
                        scythe_codegen::validation::validate_structural(&code, backend_name);
                    assert!(
                        errors.is_empty(),
                        "backend {} structural validation failed for {}: {:?}",
                        backend_name,
                        "window_sum_over_partition",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "window_sum_over_partition"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "window_sum_over_partition"
            );
        }
    }
}
