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

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

#[test]
fn test_select_all_columns() {
    // From: testing_data/select/basic/01_select_all_columns.json
    // "SELECT specific columns from a single table, all non-nullable"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name ListUsers\n-- @returns :many\nSELECT id, name, email 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, "ListUsers", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 3, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name");
    assert_eq!(analyzed.columns[2].name, "email", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "string",
        "column neutral_type for email"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for email");

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

#[test]
fn test_select_count() {
    // From: testing_data/select/basic/05_select_count.json
    // "SELECT COUNT(*) aggregate returns i64 and is non-nullable"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

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

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

    assert_eq!(analyzed.name, "CountUsers", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.columns.len(), 1, "column count");
    assert_eq!(analyzed.columns[0].name, "count", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int64",
        "column neutral_type for count"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for count");

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

#[test]
fn test_select_one() {
    // From: testing_data/select/basic/03_select_one.json
    // "SELECT a single row by primary key with :one return type"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUser\n-- @returns :one\nSELECT id, name FROM users WHERE id = $1";

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

    assert_eq!(analyzed.name, "GetUser", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.params.len(), 1, "param count");
    assert_eq!(analyzed.params[0].name, "id", "param name");
    assert_eq!(
        analyzed.params[0].neutral_type, "int32",
        "param neutral_type for id"
    );
    assert!(!analyzed.params[0].nullable, "param nullable for id");
    assert_eq!(analyzed.params[0].position, 1, "param position for id");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name");

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

#[test]
fn test_select_star() {
    // From: testing_data/select/basic/02_select_star.json
    // "SELECT * expands to all columns with correct nullability (bio is nullable)"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetAllUsers\n-- @returns :many\nSELECT * 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, "GetAllUsers", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 6, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name");
    assert_eq!(analyzed.columns[2].name, "email", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "string",
        "column neutral_type for email"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for email");
    assert_eq!(analyzed.columns[3].name, "status", "column name");
    assert_eq!(
        analyzed.columns[3].neutral_type, "string",
        "column neutral_type for status"
    );
    assert!(!analyzed.columns[3].nullable, "column nullable for status");
    assert_eq!(analyzed.columns[4].name, "bio", "column name");
    assert_eq!(
        analyzed.columns[4].neutral_type, "string",
        "column neutral_type for bio"
    );
    assert!(analyzed.columns[4].nullable, "column nullable for bio");
    assert_eq!(analyzed.columns[5].name, "created_at", "column name");
    assert_eq!(
        analyzed.columns[5].neutral_type, "datetime_tz",
        "column neutral_type for created_at"
    );
    assert!(
        !analyzed.columns[5].nullable,
        "column nullable for created_at"
    );

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

#[test]
fn test_select_with_alias() {
    // From: testing_data/select/basic/06_select_with_alias.json
    // "SELECT with column alias renames the output column"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

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

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

#[test]
fn test_select_with_nullable() {
    // From: testing_data/select/basic/04_select_with_nullable.json
    // "SELECT includes a nullable column (bio), should map to Option<String>"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

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

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

#[test]
fn test_expression_alias() {
    // From: testing_data/select/column_alias/02_expression_alias.json
    // "Expression with alias infers correct type from the function return type"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name ListUpperNames\n-- @returns :many\nSELECT id, UPPER(name) AS upper_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, "ListUpperNames", "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, "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"
    );

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

#[test]
fn test_simple_alias() {
    // From: testing_data/select/column_alias/01_simple_alias.json
    // "Column alias renames output column in result set"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql =
        "-- @name ListUserFullNames\n-- @returns :many\nSELECT id, name AS full_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, "ListUserFullNames", "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, "full_name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for full_name"
    );
    assert!(
        !analyzed.columns[1].nullable,
        "column nullable for full_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,
                        "simple_alias"
                    );
                } 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,
                        "simple_alias",
                        errors
                    );
                }
            }
            assert!(
                generated.row_struct.is_some() || generated.model_struct.is_some(),
                "backend {} should produce a struct for {}",
                backend_name,
                "simple_alias"
            );
            assert!(
                generated.query_fn.is_some(),
                "backend {} should produce query_fn for {}",
                backend_name,
                "simple_alias"
            );
        }
    }
}

#[test]
fn test_select_distinct() {
    // From: testing_data/select/distinct/01_select_distinct.json
    // "SELECT DISTINCT returns unique values with correct types"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql =
        "-- @name ListDistinctStatuses\n-- @returns :many\nSELECT DISTINCT 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, "ListDistinctStatuses", "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, "status", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for status"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for status");

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

#[test]
fn test_select_distinct_on() {
    // From: testing_data/select/distinct/02_select_distinct_on.json
    // "PostgreSQL DISTINCT ON returns first row per group with correct column expansion"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
        "CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), total INTEGER NOT NULL, notes TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetLatestOrderPerUser\n-- @returns :many\nSELECT DISTINCT ON (user_id) * FROM orders ORDER BY user_id, created_at DESC";

    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, "GetLatestOrderPerUser", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 5, "column count");
    assert_eq!(analyzed.columns[0].name, "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, "user_id", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "int32",
        "column neutral_type for user_id"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for user_id");
    assert_eq!(analyzed.columns[2].name, "total", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "int32",
        "column neutral_type for total"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for total");
    assert_eq!(analyzed.columns[3].name, "notes", "column name");
    assert_eq!(
        analyzed.columns[3].neutral_type, "string",
        "column neutral_type for notes"
    );
    assert!(analyzed.columns[3].nullable, "column nullable for notes");
    assert_eq!(analyzed.columns[4].name, "created_at", "column name");
    assert_eq!(
        analyzed.columns[4].neutral_type, "datetime_tz",
        "column neutral_type for created_at"
    );
    assert!(
        !analyzed.columns[4].nullable,
        "column nullable for created_at"
    );

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

#[test]
fn test_join_table_with_view() {
    // From: testing_data/select/from_view/02_join_with_view.json
    // "LEFT JOIN a table with a view — right side columns become nullable"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active');",
        "CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), total INTEGER NOT NULL);",
        "CREATE VIEW user_order_summary AS SELECT u.id AS user_id, u.name, COUNT(*) AS order_count FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name;",
    ];

    let query_sql = "-- @name GetUsersWithOrderSummary\n-- @returns :many\nSELECT u.name, s.order_count FROM users u LEFT JOIN user_order_summary s ON u.id = s.user_id";

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

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

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

#[test]
fn test_select_from_view() {
    // From: testing_data/select/from_view/01_select_from_view.json
    // "SELECT from a VIEW should resolve column types from the underlying table"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active');",
        "CREATE VIEW active_users AS SELECT id, name, email FROM users WHERE status = 'active';",
    ];

    let query_sql =
        "-- @name ListActiveUsers\n-- @returns :many\nSELECT id, name, email FROM active_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, "ListActiveUsers", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 3, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name");
    assert_eq!(analyzed.columns[2].name, "email", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "string",
        "column neutral_type for email"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for email");

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

#[test]
fn test_select_for_share() {
    // From: testing_data/select/locking/02_for_share.json
    // "SELECT with FOR SHARE locking clause for shared read locks"
    let schema_sql =
        &["CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL);"];

    let query_sql = "-- @name LockUserForShare\n-- @returns :one\nSELECT id, name FROM users WHERE id = $1 FOR SHARE";

    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, "LockUserForShare", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.params.len(), 1, "param count");
    assert_eq!(analyzed.params[0].name, "id", "param name");
    assert_eq!(
        analyzed.params[0].neutral_type, "int32",
        "param neutral_type for id"
    );
    assert!(!analyzed.params[0].nullable, "param nullable for id");
    assert_eq!(analyzed.params[0].position, 1, "param position for id");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name");

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

#[test]
fn test_select_for_update() {
    // From: testing_data/select/locking/01_for_update.json
    // "SELECT with FOR UPDATE locking clause for pessimistic concurrency control"
    let schema_sql =
        &["CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL);"];

    let query_sql = "-- @name LockUserForUpdate\n-- @returns :one\nSELECT id, name, email FROM users WHERE id = $1 FOR UPDATE";

    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, "LockUserForUpdate", "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(), 3, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name");
    assert_eq!(analyzed.columns[2].name, "email", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "string",
        "column neutral_type for email"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for email");

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

#[test]
fn test_order_by_case_expression() {
    // From: testing_data/select/order_by/02_order_by_case.json
    // "ORDER BY with a CASE expression does not affect return types"
    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 ListUsersByStatusPriority\n-- @returns :many\nSELECT id, name, status FROM users ORDER BY CASE WHEN status = 'active' THEN 0 ELSE 1 END, name";

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

#[test]
fn test_order_by_function_expression() {
    // From: testing_data/select/order_by/01_order_by_expression.json
    // "ORDER BY with a function expression does not affect return types"
    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 ListUsersByLowerName\n-- @returns :many\nSELECT id, name FROM users ORDER BY LOWER(name)";

    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, "ListUsersByLowerName", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name");

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

#[test]
fn test_select_except() {
    // From: testing_data/select/set_operations/02_except.json
    // "EXCEPT returns rows from the first SELECT that are not present in the second SELECT"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL);",
        "CREATE TABLE contacts (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL);",
    ];

    let query_sql = "-- @name FindUsersNotInContacts\n-- @returns :many\nSELECT name, email FROM users EXCEPT SELECT name, email 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, "FindUsersNotInContacts", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "name", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for name");
    assert_eq!(analyzed.columns[1].name, "email", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for email"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for email");

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

#[test]
fn test_select_intersect() {
    // From: testing_data/select/set_operations/01_intersect.json
    // "INTERSECT of two SELECT statements returns only rows present in both result sets"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL);",
        "CREATE TABLE contacts (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL);",
    ];

    let query_sql = "-- @name FindCommonContacts\n-- @returns :many\nSELECT name, email FROM users INTERSECT SELECT name, email 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, "FindCommonContacts", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "name", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for name");
    assert_eq!(analyzed.columns[1].name, "email", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for email"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for email");

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

#[test]
fn test_star_from_cte() {
    // From: testing_data/select/star_expansion/03_star_from_cte.json
    // "SELECT * from a CTE expands the CTE's projected columns"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetActiveUsers\n-- @returns :many\nWITH active AS (SELECT id, name FROM users WHERE status = 'active') SELECT * FROM active";

    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, "GetActiveUsers", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name");

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

#[test]
fn test_star_single_table() {
    // From: testing_data/select/star_expansion/01_star_single_table.json
    // "SELECT * from a single table expands all columns with correct types and nullability"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUserById\n-- @returns :one\nSELECT * 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, "GetUserById", "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(), 6, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name");
    assert_eq!(analyzed.columns[2].name, "email", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "string",
        "column neutral_type for email"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for email");
    assert_eq!(analyzed.columns[3].name, "status", "column name");
    assert_eq!(
        analyzed.columns[3].neutral_type, "string",
        "column neutral_type for status"
    );
    assert!(!analyzed.columns[3].nullable, "column nullable for status");
    assert_eq!(analyzed.columns[4].name, "bio", "column name");
    assert_eq!(
        analyzed.columns[4].neutral_type, "string",
        "column neutral_type for bio"
    );
    assert!(analyzed.columns[4].nullable, "column nullable for bio");
    assert_eq!(analyzed.columns[5].name, "created_at", "column name");
    assert_eq!(
        analyzed.columns[5].neutral_type, "datetime_tz",
        "column neutral_type for created_at"
    );
    assert!(
        !analyzed.columns[5].nullable,
        "column nullable for created_at"
    );

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

#[test]
fn test_star_with_join() {
    // From: testing_data/select/star_expansion/02_star_with_join.json
    // "SELECT u.* with a JOIN expands only the aliased table's columns, plus explicitly selected columns from the other table"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
        "CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), total INTEGER NOT NULL, notes TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUsersWithOrderTotal\n-- @returns :many\nSELECT u.*, o.total FROM users u JOIN orders o ON u.id = o.user_id";

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

    assert_eq!(analyzed.name, "GetUsersWithOrderTotal", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 7, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name");
    assert_eq!(analyzed.columns[2].name, "email", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "string",
        "column neutral_type for email"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for email");
    assert_eq!(analyzed.columns[3].name, "status", "column name");
    assert_eq!(
        analyzed.columns[3].neutral_type, "string",
        "column neutral_type for status"
    );
    assert!(!analyzed.columns[3].nullable, "column nullable for status");
    assert_eq!(analyzed.columns[4].name, "bio", "column name");
    assert_eq!(
        analyzed.columns[4].neutral_type, "string",
        "column neutral_type for bio"
    );
    assert!(analyzed.columns[4].nullable, "column nullable for bio");
    assert_eq!(analyzed.columns[5].name, "created_at", "column name");
    assert_eq!(
        analyzed.columns[5].neutral_type, "datetime_tz",
        "column neutral_type for created_at"
    );
    assert!(
        !analyzed.columns[5].nullable,
        "column nullable for created_at"
    );
    assert_eq!(analyzed.columns[6].name, "total", "column name");
    assert_eq!(
        analyzed.columns[6].neutral_type, "int32",
        "column neutral_type for total"
    );
    assert!(!analyzed.columns[6].nullable, "column nullable for total");

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

#[test]
fn test_exists_subquery() {
    // From: testing_data/select/subquery/04_exists_subquery.json
    // "EXISTS subquery in WHERE clause with correlated subquery"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
        "CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), total INTEGER NOT NULL, notes TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUsersWithOrders\n-- @returns :many\nSELECT id, name FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = users.id)";

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

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

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

#[test]
fn test_scalar_subquery() {
    // From: testing_data/select/subquery/01_scalar_subquery.json
    // "Scalar subquery with COUNT(*) in SELECT list returns non-nullable i64"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
        "CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), total INTEGER NOT NULL, notes TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUsersWithOrderCount\n-- @returns :many\nSELECT id, name, (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count 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, "GetUsersWithOrderCount", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 3, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name");
    assert_eq!(analyzed.columns[2].name, "order_count", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "int64",
        "column neutral_type for order_count"
    );
    assert!(
        !analyzed.columns[2].nullable,
        "column nullable for order_count"
    );

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

#[test]
fn test_subquery_in_from() {
    // From: testing_data/select/subquery/02_subquery_in_from.json
    // "Subquery in FROM clause (derived table) projects columns from the inner query"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetActiveUserSubquery\n-- @returns :many\nSELECT sub.id, sub.name FROM (SELECT id, name FROM users WHERE status = 'active') sub";

    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, "GetActiveUserSubquery", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name");

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

#[test]
fn test_subquery_in_where() {
    // From: testing_data/select/subquery/03_subquery_in_where.json
    // "Subquery in WHERE clause with IN operator and parameterized condition"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
        "CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), total INTEGER NOT NULL, notes TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

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

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

#[test]
fn test_union() {
    // From: testing_data/select/union/01_union.json
    // "UNION of two SELECT statements with matching column types deduplicates rows"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
        "CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT NOT NULL, price INTEGER NOT NULL, category TEXT NOT NULL)",
    ];

    let query_sql = "-- @name ListAllNames\n-- @returns :many\nSELECT id, name FROM users UNION SELECT id, name 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, "ListAllNames", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name");

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

#[test]
fn test_union_all() {
    // From: testing_data/select/union/02_union_all.json
    // "UNION ALL of two SELECT statements preserves all rows including duplicates"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
        "CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT NOT NULL, price INTEGER NOT NULL, category TEXT NOT NULL)",
    ];

    let query_sql = "-- @name ListAllNamesWithDuplicates\n-- @returns :many\nSELECT id, name FROM users UNION ALL SELECT id, name 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, "ListAllNamesWithDuplicates", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name");

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

#[test]
fn test_where_between() {
    // From: testing_data/select/where_clause/03_where_between.json
    // "WHERE BETWEEN with two timestamptz parameters for range filtering"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUsersCreatedBetween\n-- @returns :many\nSELECT id, name, created_at FROM users WHERE created_at BETWEEN $1 AND $2";

    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, "GetUsersCreatedBetween", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.params.len(), 2, "param count");
    assert_eq!(analyzed.params[0].name, "start", "param name");
    assert_eq!(
        analyzed.params[0].neutral_type, "datetime_tz",
        "param neutral_type for start"
    );
    assert!(!analyzed.params[0].nullable, "param nullable for start");
    assert_eq!(analyzed.params[0].position, 1, "param position for start");
    assert_eq!(analyzed.params[1].name, "end", "param name");
    assert_eq!(
        analyzed.params[1].neutral_type, "datetime_tz",
        "param neutral_type for end"
    );
    assert!(!analyzed.params[1].nullable, "param nullable for end");
    assert_eq!(analyzed.params[1].position, 2, "param position for end");
    assert_eq!(analyzed.columns.len(), 3, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name");
    assert_eq!(analyzed.columns[2].name, "created_at", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "datetime_tz",
        "column neutral_type for created_at"
    );
    assert!(
        !analyzed.columns[2].nullable,
        "column nullable for created_at"
    );

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

#[test]
fn test_where_equals() {
    // From: testing_data/select/where_clause/01_where_equals.json
    // "WHERE clause with equality comparison and string parameter"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

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

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

#[test]
fn test_where_in() {
    // From: testing_data/select/where_clause/02_where_in.json
    // "WHERE clause with ANY($1) for array parameter matching"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

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

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

#[test]
fn test_where_is_not_null() {
    // From: testing_data/select/where_clause/06_where_is_not_null.json
    // "WHERE IS NOT NULL with no parameters"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUsersWithBio\n-- @returns :many\nSELECT id, name FROM users WHERE bio IS NOT NULL";

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

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

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

#[test]
fn test_where_is_null() {
    // From: testing_data/select/where_clause/05_where_is_null.json
    // "WHERE IS NULL with no parameters"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name GetUsersWithNoBio\n-- @returns :many\nSELECT id, name FROM users WHERE bio IS NULL";

    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, "GetUsersWithNoBio", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name");

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

#[test]
fn test_where_like() {
    // From: testing_data/select/where_clause/04_where_like.json
    // "WHERE LIKE with string parameter for pattern matching"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

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

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

#[test]
fn test_where_multiple() {
    // From: testing_data/select/where_clause/07_where_multiple.json
    // "WHERE clause with multiple conditions using AND, two parameters"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', bio TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now())",
    ];

    let query_sql = "-- @name SearchActiveUsers\n-- @returns :many\nSELECT id, name, email FROM users WHERE status = $1 AND name LIKE $2";

    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, "SearchActiveUsers", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.params.len(), 2, "param count");
    assert_eq!(analyzed.params[0].name, "status", "param name");
    assert_eq!(
        analyzed.params[0].neutral_type, "string",
        "param neutral_type for status"
    );
    assert!(!analyzed.params[0].nullable, "param nullable for status");
    assert_eq!(analyzed.params[0].position, 1, "param position for status");
    assert_eq!(analyzed.params[1].name, "name", "param name");
    assert_eq!(
        analyzed.params[1].neutral_type, "string",
        "param neutral_type for name"
    );
    assert!(!analyzed.params[1].nullable, "param nullable for name");
    assert_eq!(analyzed.params[1].position, 2, "param position for name");
    assert_eq!(analyzed.columns.len(), 3, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name");
    assert_eq!(analyzed.columns[2].name, "email", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "string",
        "column neutral_type for email"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for email");

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