scythe-codegen 0.15.0

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

#[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_avg_nullable() {
    // From: testing_data/nullability/aggregate_nullability/04_avg_nullable.json
    // "AVG returns NULL on empty set and produces a numeric/decimal type"
    let schema_sql = &[
        "CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT NOT NULL, price INTEGER NOT NULL, description TEXT, category TEXT NOT NULL)",
    ];

    let query_sql = "-- @name AvgProductPrice\n-- @returns :one\nSELECT AVG(price) AS avg_price FROM products";

    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, "AvgProductPrice", "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, "avg_price", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "decimal",
        "column neutral_type for avg_price"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for avg_price");

    // 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_nullable", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "avg_nullable"
                    );
                } 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_nullable",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "avg_nullable"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "avg_nullable"
            );
        }
    }
}

#[test]
fn test_count_column_non_null() {
    // From: testing_data/nullability/aggregate_nullability/02_count_column_non_null.json
    // "COUNT(column) is always non-nullable regardless of column nullability"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name CountUsersWithBio\n-- @returns :one\nSELECT COUNT(bio) AS cnt FROM users";

    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, "CountUsersWithBio", "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, "cnt", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int64", "column neutral_type for cnt");
    assert!(!analyzed.columns[0].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, "count_column_non_null", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "count_column_non_null"
                    );
                } 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,
                        "count_column_non_null",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "count_column_non_null"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "count_column_non_null"
            );
        }
    }
}

#[test]
fn test_count_star_non_null() {
    // From: testing_data/nullability/aggregate_nullability/01_count_star_non_null.json
    // "COUNT(*) always returns a non-nullable i64, even on empty tables"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name CountUsers\n-- @returns :one\nSELECT COUNT(*) AS cnt FROM users";

    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, "CountUsers", "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, "cnt", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int64", "column neutral_type for cnt");
    assert!(!analyzed.columns[0].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, "count_star_non_null", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "count_star_non_null"
                    );
                } 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,
                        "count_star_non_null",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "count_star_non_null"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "count_star_non_null"
            );
        }
    }
}

#[test]
fn test_count_with_group_by() {
    // From: testing_data/nullability/aggregate_nullability/06_count_with_group_by.json
    // "COUNT with GROUP BY returns non-nullable count alongside the grouping column"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

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

    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, "CountUsersByStatus", "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, "status", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for status"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for status");
    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, "count_with_group_by", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "count_with_group_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,
                        "count_with_group_by",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "count_with_group_by"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "count_with_group_by"
            );
        }
    }
}

#[test]
fn test_min_max_nullable() {
    // From: testing_data/nullability/aggregate_nullability/05_min_max_nullable.json
    // "MIN and MAX return NULL on empty sets"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUserDateRange\n-- @returns :one\nSELECT MIN(created_at) AS earliest, MAX(created_at) AS latest FROM users";

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

    // 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, "min_max_nullable", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "min_max_nullable"
                    );
                } 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,
                        "min_max_nullable",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "min_max_nullable"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "min_max_nullable"
            );
        }
    }
}

#[test]
fn test_sum_nullable() {
    // From: testing_data/nullability/aggregate_nullability/03_sum_nullable.json
    // "SUM returns NULL on an empty set, so it is always nullable"
    let schema_sql = &[
        "CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL, total INTEGER NOT NULL, discount INTEGER, notes TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name SumOrderTotals\n-- @returns :one\nSELECT SUM(total) AS total_sum FROM orders";

    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, "SumOrderTotals", "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, "total_sum", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int64",
        "column neutral_type for total_sum"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for total_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_nullable", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "sum_nullable"
                    );
                } 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_nullable",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "sum_nullable"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "sum_nullable"
            );
        }
    }
}

#[test]
fn test_case_guard_wrong_table_not_narrowed() {
    // From: testing_data/nullability/case_nullability/04_case_guard_wrong_table_not_narrowed.json
    // "A NOT NULL guard on one table's column must not narrow a different table's column of the same bare name"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, deleted_at TIMESTAMPTZ)",
        "CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL, deleted_at TIMESTAMPTZ)",
    ];

    let query_sql = "-- @name GetOrderDeletion\n-- @returns :many\nSELECT CASE WHEN u.deleted_at IS NOT NULL THEN o.deleted_at ELSE '1970-01-01' END AS d FROM users u JOIN orders o ON o.user_id = 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, "GetOrderDeletion", "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, "d", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "datetime_tz",
        "column neutral_type for d"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for d");

    // 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, "case_guard_wrong_table_not_narrowed", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "case_guard_wrong_table_not_narrowed"
                    );
                } 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,
                        "case_guard_wrong_table_not_narrowed",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "case_guard_wrong_table_not_narrowed"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "case_guard_wrong_table_not_narrowed"
            );
        }
    }
}

#[test]
fn test_case_mixed_branches() {
    // From: testing_data/nullability/case_nullability/03_case_mixed_branches.json
    // "CASE where all branches including ELSE resolve to non-nullable values is non-nullable"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUserDisplayName\n-- @returns :many\nSELECT CASE WHEN bio IS NOT NULL THEN bio ELSE name END AS display FROM users";

    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, "GetUserDisplayName", "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, "display", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for display"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for display");

    // 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, "case_mixed_branches", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "case_mixed_branches"
                    );
                } 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,
                        "case_mixed_branches",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "case_mixed_branches"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "case_mixed_branches"
            );
        }
    }
}

#[test]
fn test_case_nullable_branch() {
    // From: testing_data/nullability/case_nullability/04_case_nullable_branch.json
    // "CASE with a nullable branch makes the entire expression nullable even if ELSE is non-null"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUserInfo\n-- @returns :many\nSELECT CASE WHEN status = 'active' THEN bio ELSE 'none' END AS info FROM users";

    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, "GetUserInfo", "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, "info", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for info"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for info");

    // 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, "case_nullable_branch", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "case_nullable_branch"
                    );
                } 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,
                        "case_nullable_branch",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "case_nullable_branch"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "case_nullable_branch"
            );
        }
    }
}

#[test]
fn test_case_with_else() {
    // From: testing_data/nullability/case_nullability/01_case_with_else.json
    // "CASE with all non-null branches and an ELSE clause is non-nullable"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUserActiveFlag\n-- @returns :many\nSELECT CASE WHEN status = 'active' THEN 'yes' ELSE 'no' END AS active FROM users";

    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, "GetUserActiveFlag", "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, "active", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for active"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for active");

    // 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, "case_with_else", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "case_with_else"
                    );
                } 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,
                        "case_with_else",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "case_with_else"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "case_with_else"
            );
        }
    }
}

#[test]
fn test_case_without_else() {
    // From: testing_data/nullability/case_nullability/02_case_without_else.json
    // "CASE without ELSE is nullable because unmatched cases produce NULL implicitly"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUserActiveOnly\n-- @returns :many\nSELECT CASE WHEN status = 'active' THEN 'yes' END AS active FROM users";

    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, "GetUserActiveOnly", "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, "active", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for active"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for active");

    // 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, "case_without_else", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "case_without_else"
                    );
                } 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,
                        "case_without_else",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "case_without_else"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "case_without_else"
            );
        }
    }
}

#[test]
fn test_coalesce_in_where() {
    // From: testing_data/nullability/coalesce_non_null/04_coalesce_in_where.json
    // "COALESCE used in WHERE clause does not affect output column nullability"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql =
        "-- @name GetUsersWithBio\n-- @returns :many\nSELECT id, name FROM users WHERE COALESCE(bio, '') != ''";

    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, "GetUsersWithBio", "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, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name");

    // 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, "coalesce_in_where", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "coalesce_in_where"
                    );
                } 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,
                        "coalesce_in_where",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "coalesce_in_where"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "coalesce_in_where"
            );
        }
    }
}

#[test]
fn test_coalesce_nullable_with_default() {
    // From: testing_data/nullability/coalesce_non_null/01_coalesce_nullable_with_default.json
    // "COALESCE with a literal default makes the result non-nullable since the literal guarantees a non-NULL value"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUserWithDefaultBio\n-- @returns :one\nSELECT id, COALESCE(bio, 'N/A') AS bio FROM users WHERE id = $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, "GetUserWithDefaultBio", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.params.len(), 1, "param count");
    assert_eq!(analyzed.params[0].name, "id", "param name");
    assert_eq!(analyzed.params[0].neutral_type, "int32", "param neutral_type for id");
    assert!(!analyzed.params[0].nullable, "param nullable for id");
    assert_eq!(analyzed.params[0].position, 1, "param position for id");
    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, "bio", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for bio"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for bio");

    // 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, "coalesce_nullable_with_default", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "coalesce_nullable_with_default"
                    );
                } 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,
                        "coalesce_nullable_with_default",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "coalesce_nullable_with_default"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "coalesce_nullable_with_default"
            );
        }
    }
}

#[test]
fn test_coalesce_two_nullable() {
    // From: testing_data/nullability/coalesce_non_null/02_coalesce_two_nullable.json
    // "COALESCE with only nullable arguments and no literal default is still nullable"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
        "CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), total INTEGER NOT NULL, discount INTEGER, notes TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetCoalescedText\n-- @returns :many\nSELECT COALESCE(u.bio, o.notes) AS text FROM users u LEFT JOIN orders o ON u.id = o.user_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, "GetCoalescedText", "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, "text", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for text"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for text");

    // 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, "coalesce_two_nullable", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "coalesce_two_nullable"
                    );
                } 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,
                        "coalesce_two_nullable",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "coalesce_two_nullable"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "coalesce_two_nullable"
            );
        }
    }
}

#[test]
fn test_coalesce_with_literal_last() {
    // From: testing_data/nullability/coalesce_non_null/03_coalesce_with_literal_last.json
    // "COALESCE with multiple nullable args but a literal as the last argument is non-nullable"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUserDisplayText\n-- @returns :one\nSELECT COALESCE(bio, age::text, 'unknown') AS result FROM users WHERE id = $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, "GetUserDisplayText", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.params.len(), 1, "param count");
    assert_eq!(analyzed.params[0].name, "id", "param name");
    assert_eq!(analyzed.params[0].neutral_type, "int32", "param neutral_type for id");
    assert!(!analyzed.params[0].nullable, "param nullable for id");
    assert_eq!(analyzed.params[0].position, 1, "param position for id");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "result", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for result"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for result");

    // 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, "coalesce_with_literal_last", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "coalesce_with_literal_last"
                    );
                } 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,
                        "coalesce_with_literal_last",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "coalesce_with_literal_last"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "coalesce_with_literal_last"
            );
        }
    }
}

#[test]
fn test_nested_coalesce() {
    // From: testing_data/nullability/coalesce_non_null/05_nested_coalesce.json
    // "Nested COALESCE where the outer call has a literal default is non-nullable"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
        "CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), total INTEGER NOT NULL, discount INTEGER, notes TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetNestedCoalesce\n-- @returns :many\nSELECT COALESCE(COALESCE(u.bio, o.notes), 'default') AS val FROM users u LEFT JOIN orders o ON u.id = o.user_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, "GetNestedCoalesce", "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, "val", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for val"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for val");

    // 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, "nested_coalesce", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "nested_coalesce"
                    );
                } 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,
                        "nested_coalesce",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "nested_coalesce"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "nested_coalesce"
            );
        }
    }
}

#[test]
fn test_mixed_nullability() {
    // From: testing_data/nullability/column_not_null/03_mixed_nullability.json
    // "Selecting a mix of NOT NULL and nullable columns returns correct nullability for each"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUserProfile\n-- @returns :one\nSELECT id, name, bio, age FROM users WHERE id = $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, "GetUserProfile", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.params.len(), 1, "param count");
    assert_eq!(analyzed.params[0].name, "id", "param name");
    assert_eq!(analyzed.params[0].neutral_type, "int32", "param neutral_type for id");
    assert!(!analyzed.params[0].nullable, "param nullable for id");
    assert_eq!(analyzed.params[0].position, 1, "param position for id");
    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, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name");
    assert_eq!(analyzed.columns[2].name, "bio", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "string",
        "column neutral_type for bio"
    );
    assert!(analyzed.columns[2].nullable, "column nullable for bio");
    assert_eq!(analyzed.columns[3].name, "age", "column name");
    assert_eq!(analyzed.columns[3].neutral_type, "int32", "column neutral_type for age");
    assert!(analyzed.columns[3].nullable, "column nullable for age");

    // 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, "mixed_nullability", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "mixed_nullability"
                    );
                } 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,
                        "mixed_nullability",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "mixed_nullability"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "mixed_nullability"
            );
        }
    }
}

#[test]
fn test_not_null_columns() {
    // From: testing_data/nullability/column_not_null/01_not_null_columns.json
    // "Selecting only NOT NULL columns returns non-nullable types"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUserIdAndName\n-- @returns :one\nSELECT id, name FROM users WHERE id = $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, "GetUserIdAndName", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.params.len(), 1, "param count");
    assert_eq!(analyzed.params[0].name, "id", "param name");
    assert_eq!(analyzed.params[0].neutral_type, "int32", "param neutral_type for id");
    assert!(!analyzed.params[0].nullable, "param nullable for id");
    assert_eq!(analyzed.params[0].position, 1, "param position for id");
    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, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name");

    // 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, "not_null_columns", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "not_null_columns"
                    );
                } 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,
                        "not_null_columns",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "not_null_columns"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "not_null_columns"
            );
        }
    }
}

#[test]
fn test_nullable_column() {
    // From: testing_data/nullability/column_not_null/02_nullable_column.json
    // "Selecting a nullable column returns Option type"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUserIdAndBio\n-- @returns :one\nSELECT id, bio FROM users WHERE id = $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, "GetUserIdAndBio", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.params.len(), 1, "param count");
    assert_eq!(analyzed.params[0].name, "id", "param name");
    assert_eq!(analyzed.params[0].neutral_type, "int32", "param neutral_type for id");
    assert!(!analyzed.params[0].nullable, "param nullable for id");
    assert_eq!(analyzed.params[0].position, 1, "param position for id");
    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, "bio", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for bio"
    );
    assert!(analyzed.columns[1].nullable, "column nullable for bio");

    // 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, "nullable_column", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "nullable_column"
                    );
                } 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,
                        "nullable_column",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "nullable_column"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "nullable_column"
            );
        }
    }
}

#[test]
fn test_lag_lead_default_narrows_non_null() {
    // From: testing_data/nullability/lag_lead_default_narrowing/01_lag_lead_default_narrows_non_null.json
    // "LAG/LEAD with a non-null default (third argument) cannot return NULL when the tracked column is NOT NULL, because the default is returned instead of NULL at partition boundaries"
    let schema_sql = &[
        "CREATE TABLE sales (id SERIAL PRIMARY KEY, employee_id INTEGER NOT NULL, department TEXT NOT NULL, amount INTEGER NOT NULL, discount INTEGER, sale_date DATE NOT NULL)",
    ];

    let query_sql = "-- @name LagLeadWithDefault\n-- @returns :many\nSELECT id, amount, LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS prev_amount, LEAD(amount, 1, 0) 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, "LagLeadWithDefault", "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, "lag_lead_default_narrows_non_null", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "lag_lead_default_narrows_non_null"
                    );
                } 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,
                        "lag_lead_default_narrows_non_null",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "lag_lead_default_narrows_non_null"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "lag_lead_default_narrows_non_null"
            );
        }
    }
}

#[test]
fn test_lag_lead_default_with_nullable_source_stays_nullable() {
    // From: testing_data/nullability/lag_lead_default_narrowing/03_lag_lead_default_with_nullable_source_stays_nullable.json
    // "LAG/LEAD with a non-null default still returns nullable when the tracked column itself is nullable, since the tracked column's own NULL values flow through unchanged"
    let schema_sql = &[
        "CREATE TABLE sales (id SERIAL PRIMARY KEY, employee_id INTEGER NOT NULL, department TEXT NOT NULL, amount INTEGER NOT NULL, discount INTEGER, sale_date DATE NOT NULL)",
    ];

    let query_sql = "-- @name LagLeadDefaultNullableSource\n-- @returns :many\nSELECT id, discount, LAG(discount, 1, 0) OVER (ORDER BY sale_date) AS prev_discount, LEAD(discount, 1, 0) OVER (ORDER BY sale_date) AS next_discount 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, "LagLeadDefaultNullableSource", "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, "discount", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "int32",
        "column neutral_type for discount"
    );
    assert!(analyzed.columns[1].nullable, "column nullable for discount");
    assert_eq!(analyzed.columns[2].name, "prev_discount", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "int32",
        "column neutral_type for prev_discount"
    );
    assert!(analyzed.columns[2].nullable, "column nullable for prev_discount");
    assert_eq!(analyzed.columns[3].name, "next_discount", "column name");
    assert_eq!(
        analyzed.columns[3].neutral_type, "int32",
        "column neutral_type for next_discount"
    );
    assert!(analyzed.columns[3].nullable, "column nullable for next_discount");

    // 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, "lag_lead_default_with_nullable_source_stays_nullable", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "lag_lead_default_with_nullable_source_stays_nullable"
                    );
                } 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,
                        "lag_lead_default_with_nullable_source_stays_nullable",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "lag_lead_default_with_nullable_source_stays_nullable"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "lag_lead_default_with_nullable_source_stays_nullable"
            );
        }
    }
}

#[test]
fn test_lag_lead_ignore_nulls_bails_out_to_nullable() {
    // From: testing_data/nullability/lag_lead_default_narrowing/05_lag_lead_ignore_nulls_bails_out_to_nullable.json
    // "IGNORE NULLS changes which rows LAG/LEAD counts as the offset, so it can exhaust the partition and fall back to the default (or NULL) even when both the tracked column and the default are proven non-null; scythe bails out to nullable whenever a null-treatment clause is present"
    let schema_sql = &[
        "CREATE TABLE sales (id SERIAL PRIMARY KEY, employee_id INTEGER NOT NULL, department TEXT NOT NULL, amount INTEGER NOT NULL, discount INTEGER, sale_date DATE NOT NULL)",
    ];

    let query_sql = "-- @name LagLeadIgnoreNulls\n-- @returns :many\nSELECT id, amount, LAG(amount, 1, 0) IGNORE NULLS OVER (ORDER BY sale_date) AS prev_amount, LEAD(amount, 1, 0) IGNORE NULLS 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, "LagLeadIgnoreNulls", "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, "lag_lead_ignore_nulls_bails_out_to_nullable", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "lag_lead_ignore_nulls_bails_out_to_nullable"
                    );
                } 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,
                        "lag_lead_ignore_nulls_bails_out_to_nullable",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "lag_lead_ignore_nulls_bails_out_to_nullable"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "lag_lead_ignore_nulls_bails_out_to_nullable"
            );
        }
    }
}

#[test]
fn test_lag_lead_null_default_stays_nullable() {
    // From: testing_data/nullability/lag_lead_default_narrowing/04_lag_lead_null_default_stays_nullable.json
    // "LAG/LEAD with an explicit NULL default cannot be narrowed, even when the tracked column is NOT NULL, because the default itself is the value returned at the boundary"
    let schema_sql = &[
        "CREATE TABLE sales (id SERIAL PRIMARY KEY, employee_id INTEGER NOT NULL, department TEXT NOT NULL, amount INTEGER NOT NULL, discount INTEGER, sale_date DATE NOT NULL)",
    ];

    let query_sql = "-- @name LagLeadNullDefault\n-- @returns :many\nSELECT id, amount, LAG(amount, 1, NULL) OVER (ORDER BY sale_date) AS prev_amount, LEAD(amount, 1, NULL) 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, "LagLeadNullDefault", "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, "lag_lead_null_default_stays_nullable", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "lag_lead_null_default_stays_nullable"
                    );
                } 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,
                        "lag_lead_null_default_stays_nullable",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "lag_lead_null_default_stays_nullable"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "lag_lead_null_default_stays_nullable"
            );
        }
    }
}

#[test]
fn test_lag_lead_without_default_stays_nullable() {
    // From: testing_data/nullability/lag_lead_default_narrowing/02_lag_lead_without_default_stays_nullable.json
    // "LAG/LEAD without a default (one or two arguments) genuinely returns NULL at partition boundaries, so the result stays nullable even when the tracked column is NOT NULL"
    let schema_sql = &[
        "CREATE TABLE sales (id SERIAL PRIMARY KEY, employee_id INTEGER NOT NULL, department TEXT NOT NULL, amount INTEGER NOT NULL, discount INTEGER, sale_date DATE NOT NULL)",
    ];

    let query_sql = "-- @name LagLeadWithoutDefault\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, "LagLeadWithoutDefault", "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, "lag_lead_without_default_stays_nullable", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "lag_lead_without_default_stays_nullable"
                    );
                } 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,
                        "lag_lead_without_default_stays_nullable",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "lag_lead_without_default_stays_nullable"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "lag_lead_without_default_stays_nullable"
            );
        }
    }
}

#[test]
fn test_chained_left_joins() {
    // From: testing_data/nullability/left_join_nullable/04_chained_left_joins.json
    // "Chained LEFT JOINs make all right-side columns from every join nullable"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
        "CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), total INTEGER NOT NULL, discount INTEGER, notes TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
        "CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT NOT NULL, price INTEGER NOT NULL, description TEXT, category TEXT NOT NULL)",
        "CREATE TABLE order_items (id SERIAL PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id), product_id INTEGER NOT NULL REFERENCES products(id), quantity INTEGER NOT NULL)",
    ];

    let query_sql = "-- @name GetUserOrderProducts\n-- @returns :many\nSELECT u.name, o.total, oi.quantity, p.name AS product_name, p.price FROM users u LEFT JOIN orders o ON u.id = o.user_id LEFT JOIN order_items oi ON o.id = oi.order_id LEFT JOIN products p ON oi.product_id = p.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, "GetUserOrderProducts", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 5, "column count");
    assert_eq!(analyzed.columns[0].name, "name", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for name");
    assert_eq!(analyzed.columns[1].name, "total", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "int32",
        "column neutral_type for total"
    );
    assert!(analyzed.columns[1].nullable, "column nullable for total");
    assert_eq!(analyzed.columns[2].name, "quantity", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "int32",
        "column neutral_type for quantity"
    );
    assert!(analyzed.columns[2].nullable, "column nullable for quantity");
    assert_eq!(analyzed.columns[3].name, "product_name", "column name");
    assert_eq!(
        analyzed.columns[3].neutral_type, "string",
        "column neutral_type for product_name"
    );
    assert!(analyzed.columns[3].nullable, "column nullable for product_name");
    assert_eq!(analyzed.columns[4].name, "price", "column name");
    assert_eq!(
        analyzed.columns[4].neutral_type, "int32",
        "column neutral_type for price"
    );
    assert!(analyzed.columns[4].nullable, "column nullable for price");

    // 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, "chained_left_joins", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "chained_left_joins"
                    );
                } 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,
                        "chained_left_joins",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "chained_left_joins"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "chained_left_joins"
            );
        }
    }
}

#[test]
fn test_left_side_stays_non_null() {
    // From: testing_data/nullability/left_join_nullable/03_left_side_stays_non_null.json
    // "LEFT JOIN preserves non-nullability of left-side columns while making right-side columns nullable"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
        "CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), total INTEGER NOT NULL, discount INTEGER, notes TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUsersWithOptionalOrders\n-- @returns :many\nSELECT u.id, u.name, o.total FROM users u LEFT JOIN orders o ON u.id = o.user_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, "GetUsersWithOptionalOrders", "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, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name");
    assert_eq!(analyzed.columns[2].name, "total", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "int32",
        "column neutral_type for total"
    );
    assert!(analyzed.columns[2].nullable, "column nullable for 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, "left_side_stays_non_null", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "left_side_stays_non_null"
                    );
                } 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,
                        "left_side_stays_non_null",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "left_side_stays_non_null"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "left_side_stays_non_null"
            );
        }
    }
}

#[test]
fn test_right_side_multiple_cols() {
    // From: testing_data/nullability/left_join_nullable/02_right_side_multiple_cols.json
    // "LEFT JOIN makes all right-side columns nullable, including those already nullable in schema"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
        "CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), total INTEGER NOT NULL, discount INTEGER, notes TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUserWithOrderDetails\n-- @returns :many\nSELECT u.name, o.total, o.notes, o.created_at FROM users u LEFT JOIN orders o ON u.id = o.user_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, "GetUserWithOrderDetails", "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, "name", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for name");
    assert_eq!(analyzed.columns[1].name, "total", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "int32",
        "column neutral_type for total"
    );
    assert!(analyzed.columns[1].nullable, "column nullable for total");
    assert_eq!(analyzed.columns[2].name, "notes", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "string",
        "column neutral_type for notes"
    );
    assert!(analyzed.columns[2].nullable, "column nullable for notes");
    assert_eq!(analyzed.columns[3].name, "created_at", "column name");
    assert_eq!(
        analyzed.columns[3].neutral_type, "datetime_tz",
        "column neutral_type for created_at"
    );
    assert!(analyzed.columns[3].nullable, "column nullable for created_at");

    // 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, "right_side_multiple_cols", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "right_side_multiple_cols"
                    );
                } 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,
                        "right_side_multiple_cols",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "right_side_multiple_cols"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "right_side_multiple_cols"
            );
        }
    }
}

#[test]
fn test_right_side_not_null_becomes_nullable() {
    // From: testing_data/nullability/left_join_nullable/01_right_side_not_null_becomes_nullable.json
    // "LEFT JOIN makes right-side NOT NULL columns nullable because unmatched rows produce NULLs"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
        "CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), total INTEGER NOT NULL, discount INTEGER, notes TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUserWithOrderTotal\n-- @returns :many\nSELECT u.id, o.total FROM users u LEFT JOIN orders o ON u.id = o.user_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, "GetUserWithOrderTotal", "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, "total", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "int32",
        "column neutral_type for total"
    );
    assert!(analyzed.columns[1].nullable, "column nullable for 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, "right_side_not_null_becomes_nullable", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "right_side_not_null_becomes_nullable"
                    );
                } 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,
                        "right_side_not_null_becomes_nullable",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "right_side_not_null_becomes_nullable"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "right_side_not_null_becomes_nullable"
            );
        }
    }
}

#[test]
fn test_force_nonnull() {
    // From: testing_data/nullability/override_annotations/02_force_nonnull.json
    // "The @nonnull annotation overrides a nullable column to become non-nullable in the output"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql =
        "-- @name GetUserForceNonnullBio\n-- @returns :one\n-- @nonnull bio\nSELECT id, bio FROM users WHERE id = $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, "GetUserForceNonnullBio", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.params.len(), 1, "param count");
    assert_eq!(analyzed.params[0].name, "id", "param name");
    assert_eq!(analyzed.params[0].neutral_type, "int32", "param neutral_type for id");
    assert!(!analyzed.params[0].nullable, "param nullable for id");
    assert_eq!(analyzed.params[0].position, 1, "param position for id");
    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, "bio", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for bio"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for bio");

    // 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, "force_nonnull", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "force_nonnull"
                    );
                } 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,
                        "force_nonnull",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "force_nonnull"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "force_nonnull"
            );
        }
    }
}

#[test]
fn test_force_nullable() {
    // From: testing_data/nullability/override_annotations/01_force_nullable.json
    // "The @nullable annotation overrides a NOT NULL column to become nullable in the output"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql =
        "-- @name GetUserForceNullableId\n-- @returns :one\n-- @nullable id\nSELECT id, name FROM users WHERE id = $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, "GetUserForceNullableId", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.params.len(), 1, "param count");
    assert_eq!(analyzed.params[0].name, "id", "param name");
    assert_eq!(analyzed.params[0].neutral_type, "int32", "param neutral_type for id");
    assert!(!analyzed.params[0].nullable, "param nullable for id");
    assert_eq!(analyzed.params[0].position, 1, "param position for id");
    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, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name");

    // 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, "force_nullable", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "force_nullable"
                    );
                } 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,
                        "force_nullable",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "force_nullable"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "force_nullable"
            );
        }
    }
}

#[test]
fn test_left_side_becomes_nullable() {
    // From: testing_data/nullability/right_join_nullable/01_left_side_becomes_nullable.json
    // "RIGHT JOIN makes left-side NOT NULL columns nullable because unmatched rows produce NULLs"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
        "CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), total INTEGER NOT NULL, discount INTEGER, notes TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetOrdersWithOptionalUser\n-- @returns :many\nSELECT u.name, o.id FROM users u RIGHT JOIN orders o ON u.id = o.user_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, "GetOrdersWithOptionalUser", "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, "name", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for name");
    assert_eq!(analyzed.columns[1].name, "id", "column name");
    assert_eq!(analyzed.columns[1].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[1].nullable, "column nullable for id");

    // 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, "left_side_becomes_nullable", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "left_side_becomes_nullable"
                    );
                } 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,
                        "left_side_becomes_nullable",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "left_side_becomes_nullable"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "left_side_becomes_nullable"
            );
        }
    }
}

#[test]
fn test_left_side_multiple_cols() {
    // From: testing_data/nullability/right_join_nullable/02_left_side_multiple_cols.json
    // "RIGHT JOIN makes all left-side columns nullable while right-side columns preserve their schema nullability"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT, age INTEGER, score NUMERIC, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
        "CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), total INTEGER NOT NULL, discount INTEGER, notes TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetOrdersWithUserDetails\n-- @returns :many\nSELECT u.id AS user_id, u.name, u.email, o.total FROM users u RIGHT JOIN orders o ON u.id = o.user_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, "GetOrdersWithUserDetails", "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, "user_id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for user_id"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for user_id");
    assert_eq!(analyzed.columns[1].name, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(analyzed.columns[1].nullable, "column nullable for name");
    assert_eq!(analyzed.columns[2].name, "email", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "string",
        "column neutral_type for email"
    );
    assert!(analyzed.columns[2].nullable, "column nullable for email");
    assert_eq!(analyzed.columns[3].name, "total", "column name");
    assert_eq!(
        analyzed.columns[3].neutral_type, "int32",
        "column neutral_type for total"
    );
    assert!(!analyzed.columns[3].nullable, "column nullable for 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, "left_side_multiple_cols", e
            ),
        };
        if let Ok(generated) = scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                    assert!(
                        syn::parse_file(&code).is_ok(),
                        "backend {} generated invalid Rust for {}",
                        backend_name,
                        "left_side_multiple_cols"
                    );
                } 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,
                        "left_side_multiple_cols",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "left_side_multiple_cols"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "left_side_multiple_cols"
            );
        }
    }
}