scythe-codegen 0.19.0

// 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;

#[allow(dead_code)]
fn normalize_whitespace(s: &str) -> String {
    s.split_whitespace().collect::<Vec<_>>().join(" ")
}

#[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: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "group_by_count", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "group_by_count", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        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"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "group_by_count",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "group_by_count", e
                ),
            },
        }
    }
}

#[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: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "group_by_having", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "group_by_having", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        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"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "group_by_having",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "group_by_having", e
                ),
            },
        }
    }
}

#[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: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "group_by_multiple_aggregates", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "group_by_multiple_aggregates", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        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"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "group_by_multiple_aggregates",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "group_by_multiple_aggregates", e
                ),
            },
        }
    }
}

#[test]
fn test_array_agg_row_constructor_is_rejected() {
    // From: testing_data/aggregates/nested_json/09_array_agg_row_constructor_is_rejected.json
    // "array_agg of a multi-field ROW() constructor must not silently collapse to the first field's type -- it has no single neutral type today, so it is rejected with an actionable error instead of reaching codegen as an unresolved element type (#223)"
    let schema_sql = &["CREATE TABLE orders (id SERIAL PRIMARY KEY, total NUMERIC NOT NULL);"];

    let query_sql =
        "-- @name GetOrderRows\n-- @returns :one\nSELECT array_agg(ROW(o.id, o.total)) AS rows FROM orders o;";

    let catalog_result = scythe_core::catalog::Catalog::from_ddl(schema_sql);
    if let Ok(catalog) = catalog_result {
        let query_result = scythe_core::parser::parse_query(query_sql);
        if let Ok(query) = query_result {
            let result = scythe_core::analyzer::analyze(&catalog, &query);
            assert!(result.is_err(), "expected analysis to fail");
            let err = result.unwrap_err();
            let err_msg = err.to_string();
            assert!(
                err_msg.contains("UNRESOLVED_TYPE"),
                "error should contain code {:?}, got: {}",
                "UNRESOLVED_TYPE",
                err_msg
            );
            assert!(
                err_msg.contains("ROW(...)"),
                "error should contain {:?}, got: {}",
                "ROW(...)",
                err_msg
            );
        } else {
            // Parse failed -- that counts as expected failure.
            let err = query_result.unwrap_err();
            let err_msg = err.to_string();
            assert!(
                err_msg.contains("UNRESOLVED_TYPE"),
                "error should contain code {:?}, got: {}",
                "UNRESOLVED_TYPE",
                err_msg
            );
            assert!(
                err_msg.contains("ROW(...)"),
                "error should contain {:?}, got: {}",
                "ROW(...)",
                err_msg
            );
        }
    } else {
        // DDL processing failed -- that counts as expected failure.
        let err = catalog_result.unwrap_err();
        let err_msg = err.to_string();
        assert!(
            err_msg.contains("UNRESOLVED_TYPE"),
            "error should contain code {:?}, got: {}",
            "UNRESOLVED_TYPE",
            err_msg
        );
        assert!(
            err_msg.contains("ROW(...)"),
            "error should contain {:?}, got: {}",
            "ROW(...)",
            err_msg
        );
    }
}

#[test]
fn test_array_agg_stays_scalar() {
    // From: testing_data/aggregates/nested_json/05_array_agg_stays_scalar.json
    // "Regression guard: array_agg shares json_agg's argument shape but aggregates into a SQL array and must never acquire a nested struct"
    let schema_sql = &["CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL, notes TEXT);"];

    let query_sql = "-- @name GetOrderIds\n-- @returns :one\nSELECT array_agg(o.id) AS ids FROM orders o;";

    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, "GetOrderIds", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "ids", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "array<int32>",
        "column neutral_type for ids"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for ids");
    assert_eq!(analyzed.nested_structs.len(), 0, "nested struct count");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "array_agg_stays_scalar", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "array_agg_stays_scalar", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "array_agg_stays_scalar"
                        );
                    } 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,
                            "array_agg_stays_scalar",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "array_agg_stays_scalar"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "array_agg_stays_scalar"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "array_agg_stays_scalar",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "array_agg_stays_scalar", e
                ),
            },
        }
    }
}

#[test]
fn test_json_agg_left_join_nullable_elements() {
    // From: testing_data/aggregates/nested_json/07_json_agg_left_join_nullable_elements.json
    // "json_agg over a LEFT JOIN yields [null] when nothing matched, so the array element is nullable while each field keeps its schema nullability"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL);",
        "CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL, notes TEXT);",
    ];

    let query_sql = "-- @name GetUserOrdersOuter\n-- @returns :many\nSELECT u.id, json_agg(o.*) AS orders FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id;";

    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, "GetUserOrdersOuter", "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, "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, "orders", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "json_nested<array<nullable<GetUserOrdersOuterRowOrders>>>",
        "column neutral_type for orders"
    );
    assert!(analyzed.columns[1].nullable, "column nullable for orders");
    assert_eq!(analyzed.nested_structs.len(), 1, "nested struct count");
    assert_eq!(
        analyzed.nested_structs[0].name, "get_user_orders_outer_row_orders",
        "nested struct name"
    );
    assert_eq!(
        analyzed.nested_structs[0].fields.len(),
        3,
        "nested field count for get_user_orders_outer_row_orders"
    );
    assert_eq!(analyzed.nested_structs[0].fields[0].name, "id", "nested field name");
    assert_eq!(
        analyzed.nested_structs[0].fields[0].neutral_type, "int32",
        "nested field neutral_type for id"
    );
    assert!(
        !analyzed.nested_structs[0].fields[0].nullable,
        "nested field nullable for id"
    );
    assert_eq!(
        analyzed.nested_structs[0].fields[1].name, "user_id",
        "nested field name"
    );
    assert_eq!(
        analyzed.nested_structs[0].fields[1].neutral_type, "int32",
        "nested field neutral_type for user_id"
    );
    assert!(
        !analyzed.nested_structs[0].fields[1].nullable,
        "nested field nullable for user_id"
    );
    assert_eq!(analyzed.nested_structs[0].fields[2].name, "notes", "nested field name");
    assert_eq!(
        analyzed.nested_structs[0].fields[2].neutral_type, "string",
        "nested field neutral_type for notes"
    );
    assert!(
        analyzed.nested_structs[0].fields[2].nullable,
        "nested field nullable for notes"
    );

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "json_agg_left_join_nullable_elements", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "json_agg_left_join_nullable_elements", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "json_agg_left_join_nullable_elements"
                        );
                    } 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,
                            "json_agg_left_join_nullable_elements",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "json_agg_left_join_nullable_elements"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "json_agg_left_join_nullable_elements"
                );
                if *backend_name == "elixir-postgrex" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> =
                        vec![scythe_codegen::NestedStructDegradation {
                            column: "orders".to_string(),
                            struct_name: "GetUserOrdersOuterRowOrders".to_string(),
                            fallback_type: "json_array",
                            backend: (*backend_name).to_string(),
                        }];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_left_join_nullable_elements",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "go-pgx" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_left_join_nullable_elements",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "java-jdbc" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> =
                        vec![scythe_codegen::NestedStructDegradation {
                            column: "orders".to_string(),
                            struct_name: "GetUserOrdersOuterRowOrders".to_string(),
                            fallback_type: "json",
                            backend: (*backend_name).to_string(),
                        }];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_left_join_nullable_elements",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "php-amphp" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_left_join_nullable_elements",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "php-pdo" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_left_join_nullable_elements",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "python-asyncpg" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> =
                        vec![scythe_codegen::NestedStructDegradation {
                            column: "orders".to_string(),
                            struct_name: "GetUserOrdersOuterRowOrders".to_string(),
                            fallback_type: "json_array",
                            backend: (*backend_name).to_string(),
                        }];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_left_join_nullable_elements",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "python-psycopg3" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_left_join_nullable_elements",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "rust-sqlx" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_left_join_nullable_elements",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "rust-tokio-postgres" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_left_join_nullable_elements",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "typescript-kysely" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_left_join_nullable_elements",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "typescript-pg" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_left_join_nullable_elements",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "typescript-postgres" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_left_join_nullable_elements",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "json_agg_left_join_nullable_elements",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "json_agg_left_join_nullable_elements", e
                ),
            },
        }
    }
}

#[test]
fn test_json_agg_mysql_dialect_gate() {
    // From: testing_data/aggregates/nested_json/02_json_agg_mysql_dialect_gate.json
    // "The dialect gate: json_agg on MySQL must keep the pre-existing plain json type and infer no nested struct"
    let schema_sql = &["CREATE TABLE orders (id INT NOT NULL, user_id INT NOT NULL, notes VARCHAR(255));"];

    let query_sql = "-- @name GetOrdersJsonMysql\n-- @returns :many\nSELECT json_agg(o.*) AS orders FROM orders o;";

    let catalog =
        scythe_core::catalog::Catalog::from_ddl_with_dialect(schema_sql, &scythe_core::dialect::SqlDialect::MySQL)
            .unwrap()
            .with_engine("mysql");
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetOrdersJsonMysql", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "orders", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "json",
        "column neutral_type for orders"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for orders");
    assert_eq!(analyzed.nested_structs.len(), 0, "nested struct count");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "mysql";
    let all_backends = [
        "rust-sqlx",
        "typescript-kysely",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "ruby-trilogy",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "json_agg_mysql_dialect_gate", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "json_agg_mysql_dialect_gate", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "json_agg_mysql_dialect_gate"
                        );
                    } 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,
                            "json_agg_mysql_dialect_gate",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "json_agg_mysql_dialect_gate"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "json_agg_mysql_dialect_gate"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "json_agg_mysql_dialect_gate",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "json_agg_mysql_dialect_gate", e
                ),
            },
        }
    }
}

#[test]
fn test_json_agg_redshift_engine_gate() {
    // From: testing_data/aggregates/nested_json/04_json_agg_redshift_engine_gate.json
    // "The engine gate: Redshift catalogs map to the PostgreSQL dialect but Redshift has no json_agg, so no nested struct may be inferred"
    let schema_sql = &["CREATE TABLE orders (id INTEGER NOT NULL, user_id INTEGER NOT NULL, notes TEXT);"];

    let query_sql = "-- @name GetOrdersJsonRedshift\n-- @returns :many\nSELECT json_agg(o.*) AS orders FROM orders o;";

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

    assert_eq!(analyzed.name, "GetOrdersJsonRedshift", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "orders", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "json",
        "column neutral_type for orders"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for orders");
    assert_eq!(analyzed.nested_structs.len(), 0, "nested struct count");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "redshift";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "kotlin-jdbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "ruby-pg",
        "php-pdo",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "json_agg_redshift_engine_gate", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "json_agg_redshift_engine_gate", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "json_agg_redshift_engine_gate"
                        );
                    } 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,
                            "json_agg_redshift_engine_gate",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "json_agg_redshift_engine_gate"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "json_agg_redshift_engine_gate"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "json_agg_redshift_engine_gate",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "json_agg_redshift_engine_gate", e
                ),
            },
        }
    }
}

#[test]
fn test_json_agg_sqlite_dialect_gate() {
    // From: testing_data/aggregates/nested_json/03_json_agg_sqlite_dialect_gate.json
    // "The dialect gate: json_agg on SQLite must keep the pre-existing plain json type and infer no nested struct"
    let schema_sql = &["CREATE TABLE orders (id INTEGER NOT NULL, user_id INTEGER NOT NULL, notes TEXT);"];

    let query_sql = "-- @name GetOrdersJsonSqlite\n-- @returns :many\nSELECT json_agg(o.*) AS orders FROM orders o;";

    let catalog =
        scythe_core::catalog::Catalog::from_ddl_with_dialect(schema_sql, &scythe_core::dialect::SqlDialect::SQLite)
            .unwrap()
            .with_engine("sqlite");
    let query = scythe_core::parser::parse_query(query_sql).unwrap();
    let analyzed = scythe_core::analyzer::analyze(&catalog, &query).unwrap();

    assert_eq!(analyzed.name, "GetOrdersJsonSqlite", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "orders", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "json",
        "column neutral_type for orders"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for orders");
    assert_eq!(analyzed.nested_structs.len(), 0, "nested struct count");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "sqlite";
    let all_backends = [
        "rust-sqlx",
        "typescript-kysely",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "php-pdo",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "json_agg_sqlite_dialect_gate", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "json_agg_sqlite_dialect_gate", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "json_agg_sqlite_dialect_gate"
                        );
                    } 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,
                            "json_agg_sqlite_dialect_gate",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "json_agg_sqlite_dialect_gate"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "json_agg_sqlite_dialect_gate"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "json_agg_sqlite_dialect_gate",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "json_agg_sqlite_dialect_gate", e
                ),
            },
        }
    }
}

#[test]
fn test_json_agg_wildcard_nested_struct() {
    // From: testing_data/aggregates/nested_json/01_json_agg_wildcard_nested_struct.json
    // "json_agg over a relation wildcard infers a named nested struct, including an enum field and a quoted mixed-case column"
    let schema_sql = &[
        "CREATE TYPE order_status AS ENUM ('pending', 'shipped');",
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL);",
        "CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL, status order_status NOT NULL, notes TEXT, \"createdAt\" TIMESTAMPTZ NOT NULL);",
    ];

    let query_sql = "-- @name GetUserOrders\n-- @returns :many\nSELECT u.id, json_agg(o.*) AS orders FROM users u JOIN orders o ON o.user_id = u.id GROUP BY u.id;";

    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, "GetUserOrders", "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, "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, "orders", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "json_nested<array<GetUserOrdersRowOrders>>",
        "column neutral_type for orders"
    );
    assert!(analyzed.columns[1].nullable, "column nullable for orders");
    assert_eq!(analyzed.nested_structs.len(), 1, "nested struct count");
    assert_eq!(
        analyzed.nested_structs[0].name, "get_user_orders_row_orders",
        "nested struct name"
    );
    assert_eq!(
        analyzed.nested_structs[0].fields.len(),
        5,
        "nested field count for get_user_orders_row_orders"
    );
    assert_eq!(analyzed.nested_structs[0].fields[0].name, "id", "nested field name");
    assert_eq!(
        analyzed.nested_structs[0].fields[0].neutral_type, "int32",
        "nested field neutral_type for id"
    );
    assert!(
        !analyzed.nested_structs[0].fields[0].nullable,
        "nested field nullable for id"
    );
    assert_eq!(
        analyzed.nested_structs[0].fields[1].name, "user_id",
        "nested field name"
    );
    assert_eq!(
        analyzed.nested_structs[0].fields[1].neutral_type, "int32",
        "nested field neutral_type for user_id"
    );
    assert!(
        !analyzed.nested_structs[0].fields[1].nullable,
        "nested field nullable for user_id"
    );
    assert_eq!(analyzed.nested_structs[0].fields[2].name, "status", "nested field name");
    assert_eq!(
        analyzed.nested_structs[0].fields[2].neutral_type, "enum::order_status",
        "nested field neutral_type for status"
    );
    assert!(
        !analyzed.nested_structs[0].fields[2].nullable,
        "nested field nullable for status"
    );
    assert_eq!(analyzed.nested_structs[0].fields[3].name, "notes", "nested field name");
    assert_eq!(
        analyzed.nested_structs[0].fields[3].neutral_type, "string",
        "nested field neutral_type for notes"
    );
    assert!(
        analyzed.nested_structs[0].fields[3].nullable,
        "nested field nullable for notes"
    );
    assert_eq!(
        analyzed.nested_structs[0].fields[4].name, "createdAt",
        "nested field name"
    );
    assert_eq!(
        analyzed.nested_structs[0].fields[4].neutral_type, "datetime_tz",
        "nested field neutral_type for createdAt"
    );
    assert!(
        !analyzed.nested_structs[0].fields[4].nullable,
        "nested field nullable for createdAt"
    );

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "json_agg_wildcard_nested_struct", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "json_agg_wildcard_nested_struct", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "json_agg_wildcard_nested_struct"
                        );
                    } 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,
                            "json_agg_wildcard_nested_struct",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "json_agg_wildcard_nested_struct"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "json_agg_wildcard_nested_struct"
                );
                if *backend_name == "elixir-postgrex" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> =
                        vec![scythe_codegen::NestedStructDegradation {
                            column: "orders".to_string(),
                            struct_name: "GetUserOrdersRowOrders".to_string(),
                            fallback_type: "json_array",
                            backend: (*backend_name).to_string(),
                        }];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "go-pgx" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "java-jdbc" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> =
                        vec![scythe_codegen::NestedStructDegradation {
                            column: "orders".to_string(),
                            struct_name: "GetUserOrdersRowOrders".to_string(),
                            fallback_type: "json",
                            backend: (*backend_name).to_string(),
                        }];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "php-amphp" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "php-pdo" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "python-asyncpg" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> =
                        vec![scythe_codegen::NestedStructDegradation {
                            column: "orders".to_string(),
                            struct_name: "GetUserOrdersRowOrders".to_string(),
                            fallback_type: "json_array",
                            backend: (*backend_name).to_string(),
                        }];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "python-psycopg3" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "rust-sqlx" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "rust-tokio-postgres" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "typescript-kysely" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "typescript-pg" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "typescript-postgres" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "json_agg_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "json_agg_wildcard_nested_struct",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "json_agg_wildcard_nested_struct", e
                ),
            },
        }
    }
}

#[test]
fn test_jsonb_agg_wildcard_nested_struct() {
    // From: testing_data/aggregates/nested_json/10_jsonb_agg_wildcard_nested_struct.json
    // "jsonb_agg over a relation wildcard preserves JSON runtime scalar and composite shapes"
    let schema_sql = &[
        "CREATE TYPE shipping_address AS (street TEXT, zip_code INTEGER);",
        "CREATE TABLE orders (id SERIAL PRIMARY KEY, amount NUMERIC NOT NULL, created_at TIMESTAMPTZ NOT NULL, address shipping_address);",
    ];

    let query_sql = "-- @name GetOrdersJsonb\n-- @returns :many\nSELECT jsonb_agg(o.*) AS orders FROM orders o;";

    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, "GetOrdersJsonb", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "orders", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "json_nested<array<GetOrdersJsonbRowOrders>>",
        "column neutral_type for orders"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for orders");
    assert_eq!(analyzed.nested_structs.len(), 1, "nested struct count");
    assert_eq!(
        analyzed.nested_structs[0].name, "get_orders_jsonb_row_orders",
        "nested struct name"
    );
    assert_eq!(
        analyzed.nested_structs[0].fields.len(),
        4,
        "nested field count for get_orders_jsonb_row_orders"
    );
    assert_eq!(analyzed.nested_structs[0].fields[0].name, "id", "nested field name");
    assert_eq!(
        analyzed.nested_structs[0].fields[0].neutral_type, "int32",
        "nested field neutral_type for id"
    );
    assert!(
        !analyzed.nested_structs[0].fields[0].nullable,
        "nested field nullable for id"
    );
    assert_eq!(analyzed.nested_structs[0].fields[1].name, "amount", "nested field name");
    assert_eq!(
        analyzed.nested_structs[0].fields[1].neutral_type, "decimal",
        "nested field neutral_type for amount"
    );
    assert!(
        !analyzed.nested_structs[0].fields[1].nullable,
        "nested field nullable for amount"
    );
    assert_eq!(
        analyzed.nested_structs[0].fields[2].name, "created_at",
        "nested field name"
    );
    assert_eq!(
        analyzed.nested_structs[0].fields[2].neutral_type, "datetime_tz",
        "nested field neutral_type for created_at"
    );
    assert!(
        !analyzed.nested_structs[0].fields[2].nullable,
        "nested field nullable for created_at"
    );
    assert_eq!(
        analyzed.nested_structs[0].fields[3].name, "address",
        "nested field name"
    );
    assert_eq!(
        analyzed.nested_structs[0].fields[3].neutral_type, "composite::shipping_address",
        "nested field neutral_type for address"
    );
    assert!(
        analyzed.nested_structs[0].fields[3].nullable,
        "nested field nullable for address"
    );

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "jsonb_agg_wildcard_nested_struct", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "jsonb_agg_wildcard_nested_struct", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "jsonb_agg_wildcard_nested_struct"
                        );
                    } 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,
                            "jsonb_agg_wildcard_nested_struct",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "jsonb_agg_wildcard_nested_struct"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "jsonb_agg_wildcard_nested_struct"
                );
                if *backend_name == "typescript-kysely" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "jsonb_agg_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "typescript-pg" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "jsonb_agg_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "typescript-postgres" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "jsonb_agg_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "jsonb_agg_wildcard_nested_struct",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "jsonb_agg_wildcard_nested_struct", e
                ),
            },
        }
    }
}

#[test]
fn test_row_to_json_wildcard_nested_struct() {
    // From: testing_data/aggregates/nested_json/08_row_to_json_wildcard_nested_struct.json
    // "row_to_json over a relation wildcard infers a nested struct without the array wrapper json_agg adds"
    let schema_sql = &["CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL, notes TEXT);"];

    let query_sql = "-- @name GetOrderPayload\n-- @returns :many\nSELECT row_to_json(o.*) AS payload FROM orders o;";

    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, "GetOrderPayload", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "payload", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "json_nested<GetOrderPayloadRowPayload>",
        "column neutral_type for payload"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for payload");
    assert_eq!(analyzed.nested_structs.len(), 1, "nested struct count");
    assert_eq!(
        analyzed.nested_structs[0].name, "get_order_payload_row_payload",
        "nested struct name"
    );
    assert_eq!(
        analyzed.nested_structs[0].fields.len(),
        3,
        "nested field count for get_order_payload_row_payload"
    );
    assert_eq!(analyzed.nested_structs[0].fields[0].name, "id", "nested field name");
    assert_eq!(
        analyzed.nested_structs[0].fields[0].neutral_type, "int32",
        "nested field neutral_type for id"
    );
    assert!(
        !analyzed.nested_structs[0].fields[0].nullable,
        "nested field nullable for id"
    );
    assert_eq!(
        analyzed.nested_structs[0].fields[1].name, "user_id",
        "nested field name"
    );
    assert_eq!(
        analyzed.nested_structs[0].fields[1].neutral_type, "int32",
        "nested field neutral_type for user_id"
    );
    assert!(
        !analyzed.nested_structs[0].fields[1].nullable,
        "nested field nullable for user_id"
    );
    assert_eq!(analyzed.nested_structs[0].fields[2].name, "notes", "nested field name");
    assert_eq!(
        analyzed.nested_structs[0].fields[2].neutral_type, "string",
        "nested field neutral_type for notes"
    );
    assert!(
        analyzed.nested_structs[0].fields[2].nullable,
        "nested field nullable for notes"
    );

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "row_to_json_wildcard_nested_struct", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "row_to_json_wildcard_nested_struct", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "row_to_json_wildcard_nested_struct"
                        );
                    } 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,
                            "row_to_json_wildcard_nested_struct",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "row_to_json_wildcard_nested_struct"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "row_to_json_wildcard_nested_struct"
                );
                if *backend_name == "go-pgx" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "row_to_json_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "java-jdbc" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> =
                        vec![scythe_codegen::NestedStructDegradation {
                            column: "payload".to_string(),
                            struct_name: "GetOrderPayloadRowPayload".to_string(),
                            fallback_type: "json",
                            backend: (*backend_name).to_string(),
                        }];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "row_to_json_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "php-amphp" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "row_to_json_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "php-pdo" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "row_to_json_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "python-asyncpg" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> =
                        vec![scythe_codegen::NestedStructDegradation {
                            column: "payload".to_string(),
                            struct_name: "GetOrderPayloadRowPayload".to_string(),
                            fallback_type: "json",
                            backend: (*backend_name).to_string(),
                        }];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "row_to_json_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "python-psycopg3" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "row_to_json_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "rust-sqlx" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "row_to_json_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "rust-tokio-postgres" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "row_to_json_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "typescript-kysely" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "row_to_json_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "typescript-pg" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "row_to_json_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
                if *backend_name == "typescript-postgres" {
                    let expected_degradations: Vec<scythe_codegen::NestedStructDegradation> = vec![];
                    assert_eq!(
                        generated.degraded_nested_structs,
                        expected_degradations,
                        "backend {} degraded_nested_structs mismatch for {}\n--- expected ---\n{:?}\n--- actual ---\n{:?}",
                        backend_name,
                        "row_to_json_wildcard_nested_struct",
                        expected_degradations,
                        generated.degraded_nested_structs
                    );
                }
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "row_to_json_wildcard_nested_struct",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "row_to_json_wildcard_nested_struct", e
                ),
            },
        }
    }
}

#[test]
fn test_string_agg_stays_scalar() {
    // From: testing_data/aggregates/nested_json/06_string_agg_stays_scalar.json
    // "Guardrail: string_agg stays a scalar string aggregate and must never be reinterpreted as a nested aggregate"
    let schema_sql = &["CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL, notes TEXT);"];

    let query_sql =
        "-- @name GetOrderNotes\n-- @returns :one\nSELECT string_agg(o.notes, ',') AS all_notes FROM orders o;";

    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, "GetOrderNotes", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "all_notes", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for all_notes"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for all_notes");
    assert_eq!(analyzed.nested_structs.len(), 0, "nested struct count");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "string_agg_stays_scalar", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "string_agg_stays_scalar", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "string_agg_stays_scalar"
                        );
                    } 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,
                            "string_agg_stays_scalar",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "string_agg_stays_scalar"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "string_agg_stays_scalar"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "string_agg_stays_scalar",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "string_agg_stays_scalar", e
                ),
            },
        }
    }
}

#[test]
fn test_avg_float64_stays_float64() {
    // From: testing_data/aggregates/numeric_widening/05_avg_float64_stays_float64.json
    // "AVG(double precision) preserves float64, matching PostgreSQL's avg(double precision) -> double precision"
    let schema_sql = &[
        "CREATE TABLE numeric_data (id SERIAL PRIMARY KEY, small SMALLINT NOT NULL, med INTEGER NOT NULL, big BIGINT NOT NULL, r REAL NOT NULL, d DOUBLE PRECISION NOT NULL, n NUMERIC(10,2) NOT NULL);",
    ];

    let query_sql = "-- @name AvgDouble\n-- @returns :one\nSELECT AVG(d) AS d_avg FROM numeric_data;";

    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, "AvgDouble", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "d_avg", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "float64",
        "column neutral_type for d_avg"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for d_avg");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "avg_float64_stays_float64", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "avg_float64_stays_float64", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "avg_float64_stays_float64"
                        );
                    } 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,
                            "avg_float64_stays_float64",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "avg_float64_stays_float64"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "avg_float64_stays_float64"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "avg_float64_stays_float64",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "avg_float64_stays_float64", e
                ),
            },
        }
    }
}

#[test]
fn test_sum_float32_stays_float32() {
    // From: testing_data/aggregates/numeric_widening/04_sum_float32_stays_float32.json
    // "SUM(real) preserves float32, matching PostgreSQL's sum(real) -> real"
    let schema_sql = &[
        "CREATE TABLE numeric_data (id SERIAL PRIMARY KEY, small SMALLINT NOT NULL, med INTEGER NOT NULL, big BIGINT NOT NULL, r REAL NOT NULL, d DOUBLE PRECISION NOT NULL, n NUMERIC(10,2) NOT NULL);",
    ];

    let query_sql = "-- @name SumReal\n-- @returns :one\nSELECT SUM(r) AS r_sum FROM numeric_data;";

    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, "SumReal", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "r_sum", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "float32",
        "column neutral_type for r_sum"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for r_sum");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "sum_float32_stays_float32", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "sum_float32_stays_float32", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "sum_float32_stays_float32"
                        );
                    } 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,
                            "sum_float32_stays_float32",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "sum_float32_stays_float32"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "sum_float32_stays_float32"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "sum_float32_stays_float32",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "sum_float32_stays_float32", e
                ),
            },
        }
    }
}

#[test]
fn test_sum_float64_stays_float64() {
    // From: testing_data/aggregates/numeric_widening/03_sum_float64_stays_float64.json
    // "SUM(double precision) preserves float64, matching PostgreSQL's sum(double precision) -> double precision. Regression coverage: this previously (incorrectly) inferred decimal for every non-integer argument."
    let schema_sql = &[
        "CREATE TABLE numeric_data (id SERIAL PRIMARY KEY, small SMALLINT NOT NULL, med INTEGER NOT NULL, big BIGINT NOT NULL, r REAL NOT NULL, d DOUBLE PRECISION NOT NULL, n NUMERIC(10,2) NOT NULL);",
    ];

    let query_sql = "-- @name SumDouble\n-- @returns :one\nSELECT SUM(d) AS d_sum FROM numeric_data;";

    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, "SumDouble", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "d_sum", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "float64",
        "column neutral_type for d_sum"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for d_sum");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "sum_float64_stays_float64", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "sum_float64_stays_float64", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "sum_float64_stays_float64"
                        );
                    } 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,
                            "sum_float64_stays_float64",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "sum_float64_stays_float64"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "sum_float64_stays_float64"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "sum_float64_stays_float64",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "sum_float64_stays_float64", e
                ),
            },
        }
    }
}

#[test]
fn test_sum_int32_widens_to_int64() {
    // From: testing_data/aggregates/numeric_widening/02_sum_int32_widens_to_int64.json
    // "SUM(integer) widens to int64 (bigint), matching PostgreSQL's sum(integer) -> bigint"
    let schema_sql = &[
        "CREATE TABLE numeric_data (id SERIAL PRIMARY KEY, small SMALLINT NOT NULL, med INTEGER NOT NULL, big BIGINT NOT NULL, r REAL NOT NULL, d DOUBLE PRECISION NOT NULL, n NUMERIC(10,2) NOT NULL);",
    ];

    let query_sql = "-- @name SumMed\n-- @returns :one\nSELECT SUM(med) AS med_sum FROM numeric_data;";

    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, "SumMed", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "med_sum", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int64",
        "column neutral_type for med_sum"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for med_sum");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "sum_int32_widens_to_int64", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "sum_int32_widens_to_int64", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "sum_int32_widens_to_int64"
                        );
                    } 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,
                            "sum_int32_widens_to_int64",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "sum_int32_widens_to_int64"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "sum_int32_widens_to_int64"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "sum_int32_widens_to_int64",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "sum_int32_widens_to_int64", e
                ),
            },
        }
    }
}

#[test]
fn test_sum_int64_widens_to_decimal() {
    // From: testing_data/aggregates/numeric_widening/01_sum_int64_widens_to_decimal.json
    // "SUM(bigint) widens to decimal (numeric), matching PostgreSQL's sum(bigint) -> numeric"
    let schema_sql = &[
        "CREATE TABLE numeric_data (id SERIAL PRIMARY KEY, small SMALLINT NOT NULL, med INTEGER NOT NULL, big BIGINT NOT NULL, r REAL NOT NULL, d DOUBLE PRECISION NOT NULL, n NUMERIC(10,2) NOT NULL);",
    ];

    let query_sql = "-- @name SumBig\n-- @returns :one\nSELECT SUM(big) AS big_sum FROM numeric_data;";

    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, "SumBig", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "big_sum", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "decimal",
        "column neutral_type for big_sum"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for big_sum");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "sum_int64_widens_to_decimal", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "sum_int64_widens_to_decimal", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "sum_int64_widens_to_decimal"
                        );
                    } 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,
                            "sum_int64_widens_to_decimal",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "sum_int64_widens_to_decimal"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "sum_int64_widens_to_decimal"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "sum_int64_widens_to_decimal",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "sum_int64_widens_to_decimal", e
                ),
            },
        }
    }
}

#[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: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "window_lag_lead", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "window_lag_lead", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        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"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "window_lag_lead",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "window_lag_lead", e
                ),
            },
        }
    }
}

#[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: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "window_ntile", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "window_ntile", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        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"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "window_ntile",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "window_ntile", e
                ),
            },
        }
    }
}

#[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: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "window_partition_by", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "window_partition_by", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        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"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "window_partition_by",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "window_partition_by", e
                ),
            },
        }
    }
}

#[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: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "window_rank_dense_rank", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "window_rank_dense_rank", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        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"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "window_rank_dense_rank",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "window_rank_dense_rank", e
                ),
            },
        }
    }
}

#[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: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "window_row_number", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "window_row_number", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        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"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "window_row_number",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "window_row_number", e
                ),
            },
        }
    }
}

#[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: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "window_sum_over_partition", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "window_sum_over_partition", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if matches!(
                        *backend_name,
                        "rust-sqlx" | "rust-tokio-postgres" | "rust-tiberius" | "rust-sibyl"
                    ) {
                        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"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "window_sum_over_partition",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "window_sum_over_partition", e
                ),
            },
        }
    }
}