scythe-codegen 0.16.1

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

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

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

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

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

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

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

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

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

#[test]
fn test_join_duplicate_column_names_disambiguated() {
    // From: testing_data/select/column_alias/03_join_duplicate_column_names_disambiguated.json
    // "Two same-named columns from different joined tables are auto-disambiguated instead of rejected"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL)",
        "CREATE TABLE posts (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL, title TEXT NOT NULL)",
    ];

    let query_sql =
        "-- @name ListUserPostIds\n-- @returns :many\nSELECT u.id, p.id FROM users u JOIN posts p ON p.user_id = u.id";

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

    assert_eq!(analyzed.name, "ListUserPostIds", "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_1", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "int32",
        "column neutral_type for id_1"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for id_1");
    assert_eq!(analyzed.columns[1].name, "id_2", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "int32",
        "column neutral_type for id_2"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for id_2");

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

#[test]
fn test_repeated_explicit_alias_disambiguated() {
    // From: testing_data/select/column_alias/04_repeated_explicit_alias_disambiguated.json
    // "The same explicit alias used twice is auto-disambiguated rather than reported as DUPLICATE_ALIAS"
    let schema_sql = &["CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL);"];

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

#[test]
fn test_scalar_subquery_non_aggregate_nullable() {
    // From: testing_data/select/subquery/05_scalar_subquery_non_aggregate_nullable.json
    // "Non-aggregate scalar subquery over a NOT NULL column is nullable, because zero matching rows makes the subquery evaluate to SQL NULL regardless of the projected column's own 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())",
        "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 GetFirstOrderTotal\n-- @returns :many\nSELECT id, (SELECT total FROM orders WHERE orders.user_id = users.id LIMIT 1) AS first_order_total 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, "GetFirstOrderTotal", "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, "first_order_total", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "int32",
        "column neutral_type for first_order_total"
    );
    assert!(analyzed.columns[1].nullable, "column nullable for first_order_total");

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

#[test]
fn test_scalar_subquery_sum_nullable() {
    // From: testing_data/select/subquery/06_scalar_subquery_sum_nullable.json
    // "SUM(...) as a scalar subquery is nullable, because SUM (unlike COUNT) returns SQL NULL, not zero, over an empty 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())",
        "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 GetUsersWithTotalSpent\n-- @returns :many\nSELECT id, name, (SELECT SUM(total) FROM orders WHERE user_id = users.id) AS total_spent 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, "GetUsersWithTotalSpent", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 3, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for name");
    assert_eq!(analyzed.columns[2].name, "total_spent", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "int64",
        "column neutral_type for total_spent"
    );
    assert!(analyzed.columns[2].nullable, "column nullable for total_spent");

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

#[test]
fn test_scalar_subquery_windowed_aggregate_nullable() {
    // From: testing_data/select/subquery/07_scalar_subquery_windowed_aggregate_nullable.json
    // "Scalar subquery projecting a windowed aggregate is nullable: a window function produces one row per input row, so zero matching rows yields zero result rows and the subquery evaluates to SQL NULL"
    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 GetRunningTotal\n-- @returns :many\nSELECT id, (SELECT SUM(total) OVER () FROM orders WHERE orders.user_id = users.id LIMIT 1) AS running_total 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, "GetRunningTotal", "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, "running_total", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "int64",
        "column neutral_type for running_total"
    );
    assert!(analyzed.columns[1].nullable, "column nullable for running_total");

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

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

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

#[test]
fn test_generate_series_bigint() {
    // From: testing_data/select/table_functions/01_generate_series_bigint.json
    // "generate_series is polymorphic: bigint arguments produce a bigint column, not a hardcoded int32"
    let schema_sql = &["CREATE TABLE dummy (id SERIAL PRIMARY KEY)"];

    let query_sql = "-- @name ListBigSeries\n-- @returns :many\nSELECT * FROM generate_series(1::bigint, 10::bigint)";

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

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

#[test]
fn test_generate_series_timestamptz() {
    // From: testing_data/select/table_functions/02_generate_series_timestamptz.json
    // "generate_series over timestamptz arguments produces a timestamptz column, not a hardcoded int32"
    let schema_sql = &["CREATE TABLE dummy (id SERIAL PRIMARY KEY)"];

    let query_sql = "-- @name ListDaySeries\n-- @returns :many\nSELECT * FROM generate_series('2024-01-01'::timestamptz, '2024-02-01'::timestamptz, INTERVAL '1 day')";

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

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

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

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

#[test]
fn test_values_multi_row_widening() {
    // From: testing_data/select/values/01_values_multi_row_widening.json
    // "A multi-row VALUES source widens its column types and nullability across every row, not just the first one"
    let schema_sql = &["CREATE TABLE dummy (id SERIAL PRIMARY KEY)"];

    let query_sql =
        "-- @name GetValueRows\n-- @returns :many\nSELECT * FROM (VALUES (1, 'a', 10), (2.5, 'b', NULL)) AS t";

    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, "GetValueRows", "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, "column1", "column name");
    assert_eq!(
        analyzed.columns[0].neutral_type, "decimal",
        "column neutral_type for column1"
    );
    assert!(!analyzed.columns[0].nullable, "column nullable for column1");
    assert_eq!(analyzed.columns[1].name, "column2", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for column2"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for column2");
    assert_eq!(analyzed.columns[2].name, "column3", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "int32",
        "column neutral_type for column3"
    );
    assert!(analyzed.columns[2].nullable, "column nullable for column3");

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

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

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

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

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

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

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

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