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

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