scythe-codegen 0.16.1

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

#[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_deprecated_annotation() {
    // From: testing_data/annotations/deprecated/01_deprecated_annotation.json
    // "Verify @deprecated annotation generates #[deprecated] attribute on the query function"
    let schema_sql = &["CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL);"];

    let query_sql = "-- @name GetUserV1\n-- @returns :one\n-- @deprecated Use GetUserV2 instead\nSELECT id, name, email 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, "GetUserV1", "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.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, "deprecated_annotation", 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, "deprecated_annotation", 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,
                            "deprecated_annotation"
                        );
                    } 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,
                            "deprecated_annotation",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "deprecated_annotation"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "deprecated_annotation"
                );
                if *backend_name == "rust-sqlx" {
                    let actual = generated.row_struct.clone().unwrap_or_default();
                    assert!(
                        normalize_whitespace(&actual).contains(&normalize_whitespace("#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetUserV1Row {\n    pub id: i32,\n    pub name: String,\n    pub email: String,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "deprecated_annotation", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetUserV1Row {\n    pub id: i32,\n    pub name: String,\n    pub email: String,\n}", actual
                    );
                    let actual = generated.query_fn.clone().unwrap_or_default();
                    assert!(
                        normalize_whitespace(&actual).contains(&normalize_whitespace("#[deprecated(note = \"Use GetUserV2 instead\")]\npub async fn get_user_v1(pool: &sqlx::PgPool, id: i32) -> Result<GetUserV1Row, sqlx::Error> {\n    sqlx::query_as!(GetUserV1Row, \"SELECT id, name, email FROM users WHERE id = $1\", id)\n        .fetch_one(pool)\n        .await\n}")),
                        "backend {} query_fn mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "deprecated_annotation", "#[deprecated(note = \"Use GetUserV2 instead\")]\npub async fn get_user_v1(pool: &sqlx::PgPool, id: i32) -> Result<GetUserV1Row, sqlx::Error> {\n    sqlx::query_as!(GetUserV1Row, \"SELECT id, name, email FROM users WHERE id = $1\", id)\n        .fetch_one(pool)\n        .await\n}", actual
                    );
                }
            }
            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,
                        "deprecated_annotation",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "deprecated_annotation", e
                ),
            },
        }
    }
}

#[test]
fn test_left_join_grouped() {
    // From: testing_data/annotations/grouped/02_left_join_grouped.json
    // "Verify :grouped with LEFT JOIN makes child columns nullable"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL);",
        "CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), total NUMERIC);",
    ];

    let query_sql = "-- @name GetUsersWithOptionalOrders\n-- @returns :grouped\n-- @group_by users.id\nSELECT u.id, u.name, o.id as order_id, o.total\nFROM users u\nLEFT JOIN orders o ON o.user_id = u.id;";

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

    assert_eq!(analyzed.name, "GetUsersWithOptionalOrders", "query name");
    assert_eq!(analyzed.command.to_string(), "grouped", "query command");
    assert_eq!(analyzed.columns.len(), 4, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "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_id", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "int32",
        "column neutral_type for order_id"
    );
    assert!(analyzed.columns[2].nullable, "column nullable for order_id");
    assert_eq!(analyzed.columns[3].name, "total", "column name");
    assert_eq!(
        analyzed.columns[3].neutral_type, "decimal",
        "column neutral_type for total"
    );
    assert!(analyzed.columns[3].nullable, "column nullable for total");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "left_join_grouped", 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, "left_join_grouped", 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,
                            "left_join_grouped"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "left_join_grouped",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "left_join_grouped"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "left_join_grouped"
                );
            }
            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,
                        "left_join_grouped",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "left_join_grouped", e
                ),
            },
        }
    }
}

#[test]
fn test_simple_grouped() {
    // From: testing_data/annotations/grouped/01_simple_grouped.json
    // "Verify :grouped return annotation parses and populates group_by config"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL);",
        "CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), total NUMERIC);",
    ];

    let query_sql = "-- @name GetUsersWithOrders\n-- @returns :grouped\n-- @group_by users.id\nSELECT u.id, u.name, o.id as order_id, o.total\nFROM users u\nJOIN orders o ON o.user_id = u.id;";

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

    assert_eq!(analyzed.name, "GetUsersWithOrders", "query name");
    assert_eq!(analyzed.command.to_string(), "grouped", "query command");
    assert_eq!(analyzed.columns.len(), 4, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "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_id", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "int32",
        "column neutral_type for order_id"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for order_id");
    assert_eq!(analyzed.columns[3].name, "total", "column name");
    assert_eq!(
        analyzed.columns[3].neutral_type, "decimal",
        "column neutral_type for total"
    );
    assert!(analyzed.columns[3].nullable, "column nullable for total");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "simple_grouped", 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_grouped", 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_grouped"
                        );
                    } 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_grouped",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "simple_grouped"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "simple_grouped"
                );
            }
            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_grouped",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "simple_grouped", e
                ),
            },
        }
    }
}

#[test]
fn test_valid_name_pascal() {
    // From: testing_data/annotations/name/01_valid_name.json
    // "Verify @name annotation with PascalCase is parsed correctly"
    let schema_sql = &["CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL);"];

    let query_sql = "-- @name GetUser\n-- @returns :one\nSELECT id, name, email 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.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, "valid_name_pascal", 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, "valid_name_pascal", 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,
                            "valid_name_pascal"
                        );
                    } 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,
                            "valid_name_pascal",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "valid_name_pascal"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "valid_name_pascal"
                );
                if *backend_name == "rust-sqlx" {
                    let actual = generated.row_struct.clone().unwrap_or_default();
                    assert!(
                        normalize_whitespace(&actual).contains(&normalize_whitespace("#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetUserRow {\n    pub id: i32,\n    pub name: String,\n    pub email: String,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "valid_name_pascal", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetUserRow {\n    pub id: i32,\n    pub name: String,\n    pub email: String,\n}", actual
                    );
                }
            }
            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,
                        "valid_name_pascal",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "valid_name_pascal", e
                ),
            },
        }
    }
}

#[test]
fn test_valid_name_snake() {
    // From: testing_data/annotations/name/02_snake_case_name.json
    // "Verify @name annotation with snake_case is parsed correctly"
    let schema_sql = &["CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL);"];

    let query_sql = "-- @name get_user\n-- @returns :one\nSELECT id, name, email 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, "get_user", "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.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, "valid_name_snake", 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, "valid_name_snake", 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,
                            "valid_name_snake"
                        );
                    } 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,
                            "valid_name_snake",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "valid_name_snake"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "valid_name_snake"
                );
                if *backend_name == "rust-sqlx" {
                    let actual = generated.row_struct.clone().unwrap_or_default();
                    assert!(
                        normalize_whitespace(&actual).contains(&normalize_whitespace("#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetUserRow {\n    pub id: i32,\n    pub name: String,\n    pub email: String,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "valid_name_snake", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetUserRow {\n    pub id: i32,\n    pub name: String,\n    pub email: String,\n}", actual
                    );
                }
            }
            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,
                        "valid_name_snake",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "valid_name_snake", e
                ),
            },
        }
    }
}

#[test]
fn test_optional_multiple() {
    // From: testing_data/annotations/optional/02_optional_multiple.json
    // "Verify multiple @optional annotations make all specified parameters nullable"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active');",
        "CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER, total NUMERIC(10,2), status TEXT);",
    ];

    let query_sql = "-- @name SearchOrders\n-- @returns :many\n-- @optional status\n-- @optional user_id\nSELECT id, total FROM orders WHERE status = $1 AND user_id = $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, "SearchOrders", "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[1].name, "user_id", "param name");
    assert_eq!(
        analyzed.params[1].neutral_type, "int32",
        "param neutral_type for user_id"
    );
    assert!(analyzed.params[1].nullable, "param nullable for user_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, "total", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "decimal",
        "column neutral_type for total"
    );
    assert!(analyzed.columns[1].nullable, "column nullable for total");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "optional_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, "optional_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,
                            "optional_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,
                            "optional_multiple",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "optional_multiple"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "optional_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,
                        "optional_multiple",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "optional_multiple", e
                ),
            },
        }
    }
}

#[test]
fn test_optional_simple() {
    // From: testing_data/annotations/optional/01_optional_simple.json
    // "Verify @optional annotation makes a single parameter nullable"
    let schema_sql =
        &["CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active');"];

    let query_sql =
        "-- @name SearchUsers\n-- @returns :many\n-- @optional status\nSELECT id, name FROM users WHERE status = $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, "SearchUsers", "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, "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.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, "optional_simple", 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, "optional_simple", 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,
                            "optional_simple"
                        );
                    } 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,
                            "optional_simple",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "optional_simple"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "optional_simple"
                );
            }
            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,
                        "optional_simple",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "optional_simple", e
                ),
            },
        }
    }
}

#[test]
fn test_optional_with_required() {
    // From: testing_data/annotations/optional/03_optional_with_required.json
    // "Verify @optional only affects specified parameter while others remain required"
    let schema_sql =
        &["CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active');"];

    let query_sql = "-- @name FilterUsers\n-- @returns :many\n-- @optional status\nSELECT id, name FROM users WHERE name = $1 AND status = $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, "FilterUsers", "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, "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[1].name, "status", "param name");
    assert_eq!(
        analyzed.params[1].neutral_type, "string",
        "param neutral_type for status"
    );
    assert!(analyzed.params[1].nullable, "param nullable for status");
    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, "optional_with_required", 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, "optional_with_required", 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,
                            "optional_with_required"
                        );
                    } 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,
                            "optional_with_required",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "optional_with_required"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "optional_with_required"
                );
            }
            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,
                        "optional_with_required",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "optional_with_required", e
                ),
            },
        }
    }
}

#[test]
fn test_returns_exec() {
    // From: testing_data/annotations/returns/03_returns_exec.json
    // "Verify :exec return annotation maps to execute with no return value"
    let schema_sql = &["CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"];

    let query_sql = "-- @name DeleteAllUsers\n-- @returns :exec\nDELETE 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, "DeleteAllUsers", "query name");
    assert_eq!(analyzed.command.to_string(), "exec", "query command");

    // 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, "returns_exec", 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, "returns_exec", 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,
                            "returns_exec"
                        );
                    } 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,
                            "returns_exec",
                            errors
                        );
                    }
                }
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "returns_exec"
                );
                if *backend_name == "rust-sqlx" {
                    let actual = generated.query_fn.clone().unwrap_or_default();
                    assert!(
                        normalize_whitespace(&actual).contains(&normalize_whitespace("pub async fn delete_all_users(pool: &sqlx::PgPool) -> Result<(), sqlx::Error> {\n    sqlx::query!(\"DELETE FROM users\")\n        .execute(pool)\n        .await?;\n    Ok(())\n}")),
                        "backend {} query_fn mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "returns_exec", "pub async fn delete_all_users(pool: &sqlx::PgPool) -> Result<(), sqlx::Error> {\n    sqlx::query!(\"DELETE FROM users\")\n        .execute(pool)\n        .await?;\n    Ok(())\n}", actual
                    );
                }
            }
            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,
                        "returns_exec",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "returns_exec", e
                ),
            },
        }
    }
}

#[test]
fn test_returns_exec_result() {
    // From: testing_data/annotations/returns/04_returns_exec_result.json
    // "Verify :exec_result return annotation returns PgQueryResult"
    let schema_sql = &["CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"];

    let query_sql = "-- @name DeleteInactiveUsers\n-- @returns :exec_result\nDELETE 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, "DeleteInactiveUsers", "query name");
    assert_eq!(analyzed.command.to_string(), "exec_result", "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");

    // 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, "returns_exec_result", 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, "returns_exec_result", 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,
                            "returns_exec_result"
                        );
                    } 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,
                            "returns_exec_result",
                            errors
                        );
                    }
                }
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "returns_exec_result"
                );
                if *backend_name == "rust-sqlx" {
                    let actual = generated.query_fn.clone().unwrap_or_default();
                    assert!(
                        normalize_whitespace(&actual).contains(&normalize_whitespace("pub async fn delete_inactive_users(pool: &sqlx::PgPool, id: i32) -> Result<sqlx::postgres::PgQueryResult, sqlx::Error> {\n    sqlx::query!(\"DELETE FROM users WHERE id < $1\", id)\n        .execute(pool)\n        .await\n}")),
                        "backend {} query_fn mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "returns_exec_result", "pub async fn delete_inactive_users(pool: &sqlx::PgPool, id: i32) -> Result<sqlx::postgres::PgQueryResult, sqlx::Error> {\n    sqlx::query!(\"DELETE FROM users WHERE id < $1\", id)\n        .execute(pool)\n        .await\n}", actual
                    );
                }
            }
            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,
                        "returns_exec_result",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "returns_exec_result", e
                ),
            },
        }
    }
}

#[test]
fn test_returns_exec_rows() {
    // From: testing_data/annotations/returns/05_returns_exec_rows.json
    // "Verify :exec_rows return annotation returns the number of affected rows"
    let schema_sql =
        &["CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, active BOOLEAN NOT NULL DEFAULT true);"];

    let query_sql =
        "-- @name DeactivateUsers\n-- @returns :exec_rows\nUPDATE users SET active = false WHERE active = true;";

    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, "DeactivateUsers", "query name");
    assert_eq!(analyzed.command.to_string(), "exec_rows", "query command");

    // 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, "returns_exec_rows", 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, "returns_exec_rows", 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,
                            "returns_exec_rows"
                        );
                    } 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,
                            "returns_exec_rows",
                            errors
                        );
                    }
                }
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "returns_exec_rows"
                );
                if *backend_name == "rust-sqlx" {
                    let actual = generated.query_fn.clone().unwrap_or_default();
                    assert!(
                        normalize_whitespace(&actual).contains(&normalize_whitespace("pub async fn deactivate_users(pool: &sqlx::PgPool) -> Result<u64, sqlx::Error> {\n    let result = sqlx::query!(\"UPDATE users SET active = false WHERE active = true\")\n        .execute(pool)\n        .await?;\n    Ok(result.rows_affected())\n}")),
                        "backend {} query_fn mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "returns_exec_rows", "pub async fn deactivate_users(pool: &sqlx::PgPool) -> Result<u64, sqlx::Error> {\n    let result = sqlx::query!(\"UPDATE users SET active = false WHERE active = true\")\n        .execute(pool)\n        .await?;\n    Ok(result.rows_affected())\n}", actual
                    );
                }
            }
            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,
                        "returns_exec_rows",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "returns_exec_rows", e
                ),
            },
        }
    }
}

#[test]
fn test_returns_many() {
    // From: testing_data/annotations/returns/02_returns_many.json
    // "Verify :many return annotation maps to fetch_all"
    let schema_sql = &["CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"];

    let query_sql = "-- @name GetAllUsers\n-- @returns :many\nSELECT id, 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, "GetAllUsers", "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, "returns_many", 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, "returns_many", 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,
                            "returns_many"
                        );
                    } 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,
                            "returns_many",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "returns_many"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "returns_many"
                );
                if *backend_name == "rust-sqlx" {
                    let actual = generated.query_fn.clone().unwrap_or_default();
                    assert!(
                        normalize_whitespace(&actual).contains(&normalize_whitespace("pub async fn get_all_users(pool: &sqlx::PgPool) -> Result<Vec<GetAllUsersRow>, sqlx::Error> {\n    sqlx::query_as!(GetAllUsersRow, \"SELECT id, name FROM users\")\n        .fetch_all(pool)\n        .await\n}")),
                        "backend {} query_fn mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "returns_many", "pub async fn get_all_users(pool: &sqlx::PgPool) -> Result<Vec<GetAllUsersRow>, sqlx::Error> {\n    sqlx::query_as!(GetAllUsersRow, \"SELECT id, name FROM users\")\n        .fetch_all(pool)\n        .await\n}", actual
                    );
                }
            }
            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,
                        "returns_many",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "returns_many", e
                ),
            },
        }
    }
}

#[test]
fn test_returns_one() {
    // From: testing_data/annotations/returns/01_returns_one.json
    // "Verify :one return annotation maps to fetch_one"
    let schema_sql = &["CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"];

    let query_sql = "-- @name GetOneUser\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, "GetOneUser", "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.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, "returns_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, "returns_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,
                            "returns_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,
                            "returns_one",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "returns_one"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "returns_one"
                );
                if *backend_name == "rust-sqlx" {
                    let actual = generated.query_fn.clone().unwrap_or_default();
                    assert!(
                        normalize_whitespace(&actual).contains(&normalize_whitespace("pub async fn get_one_user(pool: &sqlx::PgPool, id: i32) -> Result<GetOneUserRow, sqlx::Error> {\n    sqlx::query_as!(GetOneUserRow, \"SELECT id, name FROM users WHERE id = $1\", id)\n        .fetch_one(pool)\n        .await\n}")),
                        "backend {} query_fn mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "returns_one", "pub async fn get_one_user(pool: &sqlx::PgPool, id: i32) -> Result<GetOneUserRow, sqlx::Error> {\n    sqlx::query_as!(GetOneUserRow, \"SELECT id, name FROM users WHERE id = $1\", id)\n        .fetch_one(pool)\n        .await\n}", actual
                    );
                }
            }
            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,
                        "returns_one",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "returns_one", e
                ),
            },
        }
    }
}