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

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

#[test]
fn test_case_numeric_branches() {
    // From: testing_data/expressions/case/02_case_numeric_branches.json
    // "CASE expression with numeric column references preserves the column 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 GetAdjustedPrice\n-- @returns :many\nSELECT CASE WHEN price > 100 THEN price * 2 ELSE price END AS adjusted 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, "GetAdjustedPrice", "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, "adjusted", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for adjusted"
    );
    assert!(
        !analyzed.columns[0].nullable,
        "column nullable for adjusted"
    );

    // 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_numeric_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_numeric_branches",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "case_numeric_branches"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "case_numeric_branches"
            );
        }
    }
}

#[test]
fn test_case_type_from_branches() {
    // From: testing_data/expressions/case/01_case_type_from_branches.json
    // "CASE expression infers String type from string literal branches"
    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 GetProductTier\n-- @returns :many\nSELECT CASE WHEN price > 100 THEN 'expensive' ELSE 'cheap' END AS tier 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, "GetProductTier", "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, "tier", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for tier"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for tier");

    // 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_type_from_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_type_from_branches",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "case_type_from_branches"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "case_type_from_branches"
            );
        }
    }
}

#[test]
fn test_cast_preserves_nullability() {
    // From: testing_data/expressions/cast/03_cast_preserves_nullability.json
    // "Casting a nullable column preserves the nullable status 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 GetShortBio\n-- @returns :one\nSELECT bio::varchar(100) AS short_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, "GetShortBio", "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, "short_bio", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for short_bio"
    );
    assert!(
        analyzed.columns[0].nullable,
        "column nullable for short_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,
                        "cast_preserves_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,
                        "cast_preserves_nullability",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "cast_preserves_nullability"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "cast_preserves_nullability"
            );
        }
    }
}

#[test]
fn test_cast_text() {
    // From: testing_data/expressions/cast/02_cast_text.json
    // "Casting a nullable column to text preserves 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 GetAgeAsText\n-- @returns :one\nSELECT age::text AS age_str 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, "GetAgeAsText", "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, "age_str", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for age_str"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for age_str");

    // 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,
                        "cast_text"
                    );
                } 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,
                        "cast_text",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "cast_text"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "cast_text"
            );
        }
    }
}

#[test]
fn test_explicit_cast() {
    // From: testing_data/expressions/cast/01_explicit_cast.json
    // "Explicit cast changes the Rust type mapping while preserving 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 GetUserBigId\n-- @returns :one\nSELECT id::bigint AS big_id 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, "GetUserBigId", "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, "big_id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int64",
        "column neutral_type for big_id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for big_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,
                        "explicit_cast"
                    );
                } 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,
                        "explicit_cast",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "explicit_cast"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "explicit_cast"
            );
        }
    }
}

#[test]
fn test_cast_composite_field_access() {
    // From: testing_data/expressions/cast_advanced/03_composite_field_access.json
    // "Accessing fields from a composite type column — fields are nullable even if composite is NOT NULL"
    let schema_sql = &[
        "CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT);",
        "CREATE TABLE contacts (id SERIAL PRIMARY KEY, home_address address NOT NULL);",
    ];

    let query_sql = "-- @name GetContactAddressFields\n-- @returns :many\nSELECT id, (home_address).street, (home_address).city FROM contacts";

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

    // 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,
                        "cast_composite_field_access"
                    );
                } 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,
                        "cast_composite_field_access",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "cast_composite_field_access"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "cast_composite_field_access"
            );
        }
    }
}

#[test]
fn test_cast_pg_double_colon() {
    // From: testing_data/expressions/cast_advanced/01_pg_cast_syntax.json
    // "PostgreSQL :: cast syntax works alongside CAST() — confirms type resolution for double-colon casts"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active');",
    ];

    let query_sql = "-- @name ListUsersWithCasts\n-- @returns :many\nSELECT id, id::bigint AS big_id, name::varchar(10) AS short_name 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, "ListUsersWithCasts", "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, "big_id", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "int64",
        "column neutral_type for big_id"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for big_id");
    assert_eq!(analyzed.columns[2].name, "short_name", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "string",
        "column neutral_type for short_name"
    );
    assert!(
        !analyzed.columns[2].nullable,
        "column nullable for short_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,
                        "cast_pg_double_colon"
                    );
                } 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,
                        "cast_pg_double_colon",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "cast_pg_double_colon"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "cast_pg_double_colon"
            );
        }
    }
}

#[test]
fn test_cast_union_type_widening() {
    // From: testing_data/expressions/cast_advanced/02_implicit_union_widening.json
    // "Type widening in UNION — result uses the wider type"
    let schema_sql = &[
        "CREATE TABLE small_nums (val SMALLINT NOT NULL);",
        "CREATE TABLE big_nums (val BIGINT NOT NULL);",
    ];

    let query_sql = "-- @name GetAllNums\n-- @returns :many\nSELECT val FROM small_nums UNION ALL SELECT val FROM big_nums";

    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, "GetAllNums", "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, "int64",
        "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,
                        "cast_union_type_widening"
                    );
                } 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,
                        "cast_union_type_widening",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "cast_union_type_widening"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "cast_union_type_widening"
            );
        }
    }
}

#[test]
fn test_coalesce_preserves_type() {
    // From: testing_data/expressions/coalesce/02_coalesce_preserves_type.json
    // "COALESCE preserves the SQL type of the first argument while adjusting 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 GetUserBioOrDefault\n-- @returns :one\nSELECT COALESCE(bio, 'default') 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, "GetUserBioOrDefault", "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, "bio", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for bio"
    );
    assert!(!analyzed.columns[0].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_preserves_type"
                    );
                } 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_preserves_type",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "coalesce_preserves_type"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "coalesce_preserves_type"
            );
        }
    }
}

#[test]
fn test_coalesce_type_inference() {
    // From: testing_data/expressions/coalesce/01_coalesce_type_inference.json
    // "COALESCE infers the type from the first argument and literal default makes it 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 GetUserAge\n-- @returns :one\nSELECT COALESCE(age, 0) AS 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, "GetUserAge", "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, "age", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for age"
    );
    assert!(!analyzed.columns[0].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,
                        "coalesce_type_inference"
                    );
                } 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_type_inference",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "coalesce_type_inference"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "coalesce_type_inference"
            );
        }
    }
}

#[test]
fn test_date_age_interval() {
    // From: testing_data/expressions/date_functions/04_age_interval.json
    // "AGE function returns PgInterval, non-nullable when both arguments are non-null"
    let schema_sql = &[
        "CREATE TABLE events (id SERIAL PRIMARY KEY, name TEXT NOT NULL, occurred_at TIMESTAMPTZ NOT NULL);",
    ];

    let query_sql = "-- @name AgeInterval\n-- @returns :many\nSELECT id, AGE(NOW(), occurred_at) AS elapsed FROM events";

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

    // 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,
                        "date_age_interval"
                    );
                } 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,
                        "date_age_interval",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "date_age_interval"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "date_age_interval"
            );
        }
    }
}

#[test]
fn test_date_current_functions() {
    // From: testing_data/expressions/date_functions/01_current_date_timestamp.json
    // "CURRENT_DATE, CURRENT_TIMESTAMP, and NOW() return non-nullable date/time values"
    let schema_sql = &[
        "CREATE TABLE events (id SERIAL PRIMARY KEY, name TEXT NOT NULL, occurred_at TIMESTAMPTZ NOT NULL);",
    ];

    let query_sql = "-- @name CurrentDateTimestamp\n-- @returns :one\nSELECT CURRENT_DATE AS today, CURRENT_TIMESTAMP AS now_ts, NOW() AS now_fn";

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

    // 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,
                        "date_current_functions"
                    );
                } 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,
                        "date_current_functions",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "date_current_functions"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "date_current_functions"
            );
        }
    }
}

#[test]
fn test_date_extract() {
    // From: testing_data/expressions/date_functions/02_extract.json
    // "EXTRACT returns double precision (f64) for year and month parts"
    let schema_sql = &[
        "CREATE TABLE events (id SERIAL PRIMARY KEY, name TEXT NOT NULL, occurred_at TIMESTAMPTZ NOT NULL);",
    ];

    let query_sql = "-- @name ExtractDateParts\n-- @returns :many\nSELECT id, EXTRACT(YEAR FROM occurred_at) AS yr, EXTRACT(MONTH FROM occurred_at) AS mo FROM events";

    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, "ExtractDateParts", "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, "yr", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "float64",
        "column neutral_type for yr"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for yr");
    assert_eq!(analyzed.columns[2].name, "mo", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "float64",
        "column neutral_type for mo"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for mo");

    // 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,
                        "date_extract"
                    );
                } 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,
                        "date_extract",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "date_extract"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "date_extract"
            );
        }
    }
}

#[test]
fn test_date_trunc() {
    // From: testing_data/expressions/date_functions/03_date_trunc.json
    // "DATE_TRUNC preserves the timezone type of the input timestamp"
    let schema_sql = &[
        "CREATE TABLE events (id SERIAL PRIMARY KEY, name TEXT NOT NULL, occurred_at TIMESTAMPTZ NOT NULL);",
    ];

    let query_sql = "-- @name DateTruncMonth\n-- @returns :many\nSELECT id, DATE_TRUNC('month', occurred_at) AS month_start FROM events";

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

    // 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,
                        "date_trunc"
                    );
                } 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,
                        "date_trunc",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "date_trunc"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "date_trunc"
            );
        }
    }
}

#[test]
fn test_math_abs_ceil_floor() {
    // From: testing_data/expressions/math_functions/01_abs_ceil_floor.json
    // "ABS, CEIL, and FLOOR math functions preserve non-nullability"
    let schema_sql = &[
        "CREATE TABLE measurements (id SERIAL PRIMARY KEY, value DOUBLE PRECISION NOT NULL, quantity INTEGER NOT NULL);",
    ];

    let query_sql = "-- @name AbsCeilFloor\n-- @returns :many\nSELECT id, ABS(quantity) AS abs_qty, CEIL(value) AS ceil_val, FLOOR(value) AS floor_val FROM measurements";

    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, "AbsCeilFloor", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 4, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "abs_qty", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "int32",
        "column neutral_type for abs_qty"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for abs_qty");
    assert_eq!(analyzed.columns[2].name, "ceil_val", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "float64",
        "column neutral_type for ceil_val"
    );
    assert!(
        !analyzed.columns[2].nullable,
        "column nullable for ceil_val"
    );
    assert_eq!(analyzed.columns[3].name, "floor_val", "column name");
    assert_eq!(
        analyzed.columns[3].neutral_type, "float64",
        "column neutral_type for floor_val"
    );
    assert!(
        !analyzed.columns[3].nullable,
        "column nullable for floor_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,
                        "math_abs_ceil_floor"
                    );
                } 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,
                        "math_abs_ceil_floor",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "math_abs_ceil_floor"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "math_abs_ceil_floor"
            );
        }
    }
}

#[test]
fn test_math_power_sqrt() {
    // From: testing_data/expressions/math_functions/03_power_sqrt.json
    // "POWER and SQRT math functions return f64 non-nullable on non-null input"
    let schema_sql = &[
        "CREATE TABLE measurements (id SERIAL PRIMARY KEY, value DOUBLE PRECISION NOT NULL, quantity INTEGER NOT NULL);",
    ];

    let query_sql = "-- @name PowerSqrt\n-- @returns :many\nSELECT id, POWER(value, 2) AS squared, SQRT(value) AS root FROM measurements";

    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, "PowerSqrt", "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, "squared", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "float64",
        "column neutral_type for squared"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for squared");
    assert_eq!(analyzed.columns[2].name, "root", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "float64",
        "column neutral_type for root"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for root");

    // 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,
                        "math_power_sqrt"
                    );
                } 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,
                        "math_power_sqrt",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "math_power_sqrt"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "math_power_sqrt"
            );
        }
    }
}

#[test]
fn test_math_round() {
    // From: testing_data/expressions/math_functions/02_round.json
    // "ROUND with cast to numeric returns rust_decimal::Decimal non-nullable"
    let schema_sql = &[
        "CREATE TABLE measurements (id SERIAL PRIMARY KEY, value DOUBLE PRECISION NOT NULL, quantity INTEGER NOT NULL);",
    ];

    let query_sql = "-- @name RoundValue\n-- @returns :many\nSELECT id, ROUND(value::numeric, 2) AS rounded FROM measurements";

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

    // 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,
                        "math_round"
                    );
                } 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,
                        "math_round",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "math_round"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "math_round"
            );
        }
    }
}

#[test]
fn test_nullif_always_nullable() {
    // From: testing_data/expressions/nullif/01_nullif_always_nullable.json
    // "NULLIF always produces a nullable result because it can return NULL when arguments match"
    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 GetActiveStatus\n-- @returns :many\nSELECT NULLIF(status, 'inactive') AS active_status 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, "GetActiveStatus", "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_status", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for active_status"
    );
    assert!(
        analyzed.columns[0].nullable,
        "column nullable for active_status"
    );

    // 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,
                        "nullif_always_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,
                        "nullif_always_nullable",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "nullif_always_nullable"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "nullif_always_nullable"
            );
        }
    }
}

#[test]
fn test_nullif_on_non_null() {
    // From: testing_data/expressions/nullif/02_nullif_on_non_null.json
    // "NULLIF on a NOT NULL column still produces nullable result"
    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 GetNonEmptyName\n-- @returns :many\nSELECT NULLIF(name, '') AS name 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, "GetNonEmptyName", "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, "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");

    // 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,
                        "nullif_on_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,
                        "nullif_on_non_null",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "nullif_on_non_null"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "nullif_on_non_null"
            );
        }
    }
}

#[test]
fn test_arithmetic() {
    // From: testing_data/expressions/operators/01_arithmetic.json
    // "Arithmetic on two NOT NULL columns produces a non-nullable result"
    let schema_sql = &[
        "CREATE TABLE order_items (id SERIAL PRIMARY KEY, order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL, price INTEGER NOT NULL)",
    ];

    let query_sql = "-- @name GetLineTotal\n-- @returns :many\nSELECT price * quantity AS line_total FROM order_items";

    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, "GetLineTotal", "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, "line_total", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for line_total"
    );
    assert!(
        !analyzed.columns[0].nullable,
        "column nullable for line_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,
                        "arithmetic"
                    );
                } 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,
                        "arithmetic",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "arithmetic"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "arithmetic"
            );
        }
    }
}

#[test]
fn test_arithmetic_with_nullable() {
    // From: testing_data/expressions/operators/03_arithmetic_with_nullable.json
    // "Arithmetic involving a nullable column produces a nullable result"
    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 GetNextAge\n-- @returns :many\nSELECT age + 1 AS next_age 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, "GetNextAge", "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, "next_age", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for next_age"
    );
    assert!(analyzed.columns[0].nullable, "column nullable for next_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,
                        "arithmetic_with_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,
                        "arithmetic_with_nullable",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "arithmetic_with_nullable"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "arithmetic_with_nullable"
            );
        }
    }
}

#[test]
fn test_string_concat() {
    // From: testing_data/expressions/operators/02_string_concat.json
    // "String concatenation of NOT NULL columns produces a non-nullable String"
    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 GetUserDisplay\n-- @returns :many\nSELECT name || ' <' || email || '>' 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, "GetUserDisplay", "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,
                        "string_concat"
                    );
                } else {
                    // Structural validation for non-Rust backends
                    let errors =
                        scythe_codegen::validation::validate_structural(&code, backend_name);
                    assert!(
                        errors.is_empty(),
                        "backend {} structural validation failed for {}: {:?}",
                        backend_name,
                        "string_concat",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "string_concat"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "string_concat"
            );
        }
    }
}

#[test]
fn test_like_returns_bool() {
    // From: testing_data/expressions/pattern_matching/01_like_returns_bool.json
    // "LIKE expression in SELECT returns a non-nullable bool when the input column is NOT 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 GetUsersStartingWithA\n-- @returns :many\nSELECT id, name LIKE 'A%' AS starts_with_a 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, "GetUsersStartingWithA", "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, "starts_with_a", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "bool",
        "column neutral_type for starts_with_a"
    );
    assert!(
        !analyzed.columns[1].nullable,
        "column nullable for starts_with_a"
    );

    // 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,
                        "like_returns_bool"
                    );
                } 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,
                        "like_returns_bool",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "like_returns_bool"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "like_returns_bool"
            );
        }
    }
}

#[test]
fn test_string_concat_function() {
    // From: testing_data/expressions/string_functions/02_concat.json
    // "CONCAT function never returns null, coerces null arguments to empty string"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT);",
    ];

    let query_sql = "-- @name ConcatDisplay\n-- @returns :many\nSELECT id, CONCAT(name, ' <', email, '>') 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, "ConcatDisplay", "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, "display", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for display"
    );
    assert!(!analyzed.columns[1].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,
                        "string_concat_function"
                    );
                } else {
                    // Structural validation for non-Rust backends
                    let errors =
                        scythe_codegen::validation::validate_structural(&code, backend_name);
                    assert!(
                        errors.is_empty(),
                        "backend {} structural validation failed for {}: {:?}",
                        backend_name,
                        "string_concat_function",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "string_concat_function"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "string_concat_function"
            );
        }
    }
}

#[test]
fn test_string_length_trim() {
    // From: testing_data/expressions/string_functions/04_length_trim.json
    // "LENGTH returns i32 and TRIM returns String, both non-nullable on NOT NULL input"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT);",
    ];

    let query_sql = "-- @name LengthTrimNames\n-- @returns :many\nSELECT id, LENGTH(name) AS name_len, TRIM(name) AS trimmed 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, "LengthTrimNames", "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_len", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "int32",
        "column neutral_type for name_len"
    );
    assert!(
        !analyzed.columns[1].nullable,
        "column nullable for name_len"
    );
    assert_eq!(analyzed.columns[2].name, "trimmed", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "string",
        "column neutral_type for trimmed"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for trimmed");

    // 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,
                        "string_length_trim"
                    );
                } else {
                    // Structural validation for non-Rust backends
                    let errors =
                        scythe_codegen::validation::validate_structural(&code, backend_name);
                    assert!(
                        errors.is_empty(),
                        "backend {} structural validation failed for {}: {:?}",
                        backend_name,
                        "string_length_trim",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "string_length_trim"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "string_length_trim"
            );
        }
    }
}

#[test]
fn test_string_substring() {
    // From: testing_data/expressions/string_functions/03_substring.json
    // "SUBSTRING function on a NOT NULL column returns non-nullable String"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT);",
    ];

    let query_sql = "-- @name SubstringPrefix\n-- @returns :many\nSELECT id, SUBSTRING(name FROM 1 FOR 3) AS prefix 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, "SubstringPrefix", "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, "prefix", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for prefix"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for prefix");

    // 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,
                        "string_substring"
                    );
                } else {
                    // Structural validation for non-Rust backends
                    let errors =
                        scythe_codegen::validation::validate_structural(&code, backend_name);
                    assert!(
                        errors.is_empty(),
                        "backend {} structural validation failed for {}: {:?}",
                        backend_name,
                        "string_substring",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "string_substring"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "string_substring"
            );
        }
    }
}

#[test]
fn test_string_upper_lower() {
    // From: testing_data/expressions/string_functions/01_upper_lower.json
    // "UPPER and LOWER string functions preserve non-nullability of input"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, bio TEXT);",
    ];

    let query_sql = "-- @name UpperLowerNames\n-- @returns :many\nSELECT id, UPPER(name) AS upper_name, LOWER(name) AS lower_name 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, "UpperLowerNames", "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, "upper_name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for upper_name"
    );
    assert!(
        !analyzed.columns[1].nullable,
        "column nullable for upper_name"
    );
    assert_eq!(analyzed.columns[2].name, "lower_name", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "string",
        "column neutral_type for lower_name"
    );
    assert!(
        !analyzed.columns[2].nullable,
        "column nullable for lower_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,
                        "string_upper_lower"
                    );
                } else {
                    // Structural validation for non-Rust backends
                    let errors =
                        scythe_codegen::validation::validate_structural(&code, backend_name);
                    assert!(
                        errors.is_empty(),
                        "backend {} structural validation failed for {}: {:?}",
                        backend_name,
                        "string_upper_lower",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "string_upper_lower"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "string_upper_lower"
            );
        }
    }
}
