scythe-codegen 0.16.0

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

#[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_array_param() {
    // From: testing_data/types/arrays/04_array_param.json
    // "Use an array parameter with ANY() for IN-style filtering"
    let schema_sql = &["CREATE TABLE scores (id SERIAL PRIMARY KEY, values INT[] NOT NULL);"];

    let query_sql = "-- @name GetScoresByIds\n-- @returns :many\nSELECT id, values FROM scores WHERE id = ANY($1);";

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

    assert_eq!(analyzed.name, "GetScoresByIds", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.params.len(), 1, "param count");
    assert_eq!(analyzed.params[0].name, "ids", "param name");
    assert_eq!(
        analyzed.params[0].neutral_type, "array<int32>",
        "param neutral_type for ids"
    );
    assert!(!analyzed.params[0].nullable, "param nullable for ids");
    assert_eq!(analyzed.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, "values", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "array<int32>",
        "column neutral_type for values"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for values");

    // 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, "array_param", 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, "array_param", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "array_param"
                        );
                    } 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,
                            "array_param",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "array_param"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "array_param"
                );
                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 GetScoresByIdsRow {\n    pub id: i32,\n    pub values: Vec<i32>,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "array_param", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetScoresByIdsRow {\n    pub id: i32,\n    pub values: Vec<i32>,\n}", actual
                    );
                    let actual = generated.query_fn.clone().unwrap_or_default();
                    assert!(
                        normalize_whitespace(&actual).contains(&normalize_whitespace("pub async fn get_scores_by_ids(pool: &sqlx::PgPool, ids: &[i32]) -> Result<Vec<GetScoresByIdsRow>, sqlx::Error> {\n    sqlx::query_as!(GetScoresByIdsRow, \"SELECT id, values FROM scores WHERE id = ANY($1)\", ids)\n        .fetch_all(pool)\n        .await\n}")),
                        "backend {} query_fn mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "array_param", "pub async fn get_scores_by_ids(pool: &sqlx::PgPool, ids: &[i32]) -> Result<Vec<GetScoresByIdsRow>, sqlx::Error> {\n    sqlx::query_as!(GetScoresByIdsRow, \"SELECT id, values FROM scores WHERE id = ANY($1)\", ids)\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,
                        "array_param",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "array_param", e
                ),
            },
        }
    }
}

#[test]
fn test_integer_array() {
    // From: testing_data/types/arrays/01_integer_array.json
    // "Select a non-nullable integer array column mapped to Vec<i32>"
    let schema_sql = &["CREATE TABLE scores (id SERIAL PRIMARY KEY, values INT[] NOT NULL);"];

    let query_sql = "-- @name GetScores\n-- @returns :many\nSELECT id, values FROM scores;";

    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, "GetScores", "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, "values", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "array<int32>",
        "column neutral_type for values"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for values");

    // 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, "integer_array", 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, "integer_array", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "integer_array"
                        );
                    } 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,
                            "integer_array",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "integer_array"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "integer_array"
                );
                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 GetScoresRow {\n    pub id: i32,\n    pub values: Vec<i32>,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "integer_array", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetScoresRow {\n    pub id: i32,\n    pub values: Vec<i32>,\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,
                        "integer_array",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "integer_array", e
                ),
            },
        }
    }
}

#[test]
fn test_nullable_array() {
    // From: testing_data/types/arrays/03_nullable_array.json
    // "Select a nullable integer array column mapped to Option<Vec<i32>>"
    let schema_sql = &["CREATE TABLE scores (id SERIAL PRIMARY KEY, values INT[]);"];

    let query_sql = "-- @name GetScoresNullable\n-- @returns :many\nSELECT id, values FROM scores;";

    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, "GetScoresNullable", "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, "values", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "array<int32>",
        "column neutral_type for values"
    );
    assert!(analyzed.columns[1].nullable, "column nullable for values");

    // 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, "nullable_array", 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, "nullable_array", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "nullable_array"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "nullable_array",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "nullable_array"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "nullable_array"
                );
                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 GetScoresNullableRow {\n    pub id: i32,\n    pub values: Option<Vec<i32>>,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "nullable_array", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetScoresNullableRow {\n    pub id: i32,\n    pub values: Option<Vec<i32>>,\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,
                        "nullable_array",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "nullable_array", e
                ),
            },
        }
    }
}

#[test]
fn test_text_array() {
    // From: testing_data/types/arrays/02_text_array.json
    // "Select a non-nullable text array column mapped to Vec<String>"
    let schema_sql = &["CREATE TABLE posts (id SERIAL PRIMARY KEY, title TEXT NOT NULL, tags TEXT[] NOT NULL);"];

    let query_sql = "-- @name GetPostTags\n-- @returns :many\nSELECT id, tags FROM posts;";

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

    // 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, "text_array", 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, "text_array", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "text_array"
                        );
                    } 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,
                            "text_array",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "text_array"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "text_array"
                );
                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 GetPostTagsRow {\n    pub id: i32,\n    pub tags: Vec<String>,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "text_array", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetPostTagsRow {\n    pub id: i32,\n    pub tags: Vec<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,
                        "text_array",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "text_array", e
                ),
            },
        }
    }
}

#[test]
fn test_bytea_column() {
    // From: testing_data/types/bytea/01_bytea_column.json
    // "Select a bytea column mapped to Vec<u8>"
    let schema_sql = &["CREATE TABLE files (id SERIAL PRIMARY KEY, name TEXT NOT NULL, content BYTEA NOT NULL);"];

    let query_sql = "-- @name GetFiles\n-- @returns :many\nSELECT id, name, content FROM files;";

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

    // 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, "bytea_column", 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, "bytea_column", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "bytea_column"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "bytea_column",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "bytea_column"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "bytea_column"
                );
                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 GetFilesRow {\n    pub id: i32,\n    pub name: String,\n    pub content: Vec<u8>,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "bytea_column", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetFilesRow {\n    pub id: i32,\n    pub name: String,\n    pub content: Vec<u8>,\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,
                        "bytea_column",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "bytea_column", e
                ),
            },
        }
    }
}

#[test]
fn test_composite_column() {
    // From: testing_data/types/composite/01_composite_column.json
    // "Select a composite type column mapped to a generated Rust struct"
    let schema_sql = &[
        "CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT);",
        "CREATE TABLE contacts (id SERIAL PRIMARY KEY, home_address address NOT NULL);",
    ];

    let query_sql = "-- @name GetContacts\n-- @returns :many\nSELECT id, home_address FROM contacts;";

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

    assert_eq!(analyzed.name, "GetContacts", "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, "home_address", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "composite::address",
        "column neutral_type for home_address"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for home_address");

    // 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, "composite_column", 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, "composite_column", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "composite_column"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "composite_column",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "composite_column"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "composite_column"
                );
                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 GetContactsRow {\n    pub id: i32,\n    pub home_address: Address,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "composite_column", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetContactsRow {\n    pub id: i32,\n    pub home_address: Address,\n}", actual
                    );
                    let actual = generated.model_struct.clone().unwrap_or_default();
                    assert!(
                        normalize_whitespace(&actual).contains(&normalize_whitespace("#[derive(Debug, Clone, sqlx::Type)]\n#[sqlx(type_name = \"address\")]\npub struct Address {\n    pub street: String,\n    pub city: String,\n    pub zip: String,\n}")),
                        "backend {} model_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "composite_column", "#[derive(Debug, Clone, sqlx::Type)]\n#[sqlx(type_name = \"address\")]\npub struct Address {\n    pub street: String,\n    pub city: String,\n    pub zip: 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,
                        "composite_column",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "composite_column", e
                ),
            },
        }
    }
}

#[test]
fn test_enum_param() {
    // From: testing_data/types/enums/03_enum_param.json
    // "Use an enum type as a query parameter in a WHERE clause"
    let schema_sql = &[
        "CREATE TYPE user_status AS ENUM ('active', 'inactive', 'banned');",
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, status user_status NOT NULL);",
    ];

    let query_sql = "-- @name GetUsersByStatus\n-- @returns :many\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, "GetUsersByStatus", "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, "enum::user_status",
        "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, "enum_param", 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, "enum_param", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "enum_param"
                        );
                    } 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,
                            "enum_param",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "enum_param"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "enum_param"
                );
                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 GetUsersByStatusRow {\n    pub id: i32,\n    pub name: String,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "enum_param", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetUsersByStatusRow {\n    pub id: i32,\n    pub name: String,\n}", actual
                    );
                    let actual = generated.query_fn.clone().unwrap_or_default();
                    assert!(
                        normalize_whitespace(&actual).contains(&normalize_whitespace("pub async fn get_users_by_status(pool: &sqlx::PgPool, status: &UserStatus) -> Result<Vec<GetUsersByStatusRow>, sqlx::Error> {\n    sqlx::query_as!(GetUsersByStatusRow, \"SELECT id, name FROM users WHERE status = $1\", status as &UserStatus)\n        .fetch_all(pool)\n        .await\n}")),
                        "backend {} query_fn mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "enum_param", "pub async fn get_users_by_status(pool: &sqlx::PgPool, status: &UserStatus) -> Result<Vec<GetUsersByStatusRow>, sqlx::Error> {\n    sqlx::query_as!(GetUsersByStatusRow, \"SELECT id, name FROM users WHERE status = $1\", status as &UserStatus)\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,
                        "enum_param",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "enum_param", e
                ),
            },
        }
    }
}

#[test]
fn test_insert_enum() {
    // From: testing_data/types/enums/04_insert_enum.json
    // "Insert a row with an enum-typed parameter"
    let schema_sql = &[
        "CREATE TYPE user_status AS ENUM ('active', 'inactive', 'banned');",
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, status user_status NOT NULL);",
    ];

    let query_sql = "-- @name InsertUser\n-- @returns :exec\nINSERT INTO users (name, status) VALUES ($1, $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, "InsertUser", "query name");
    assert_eq!(analyzed.command.to_string(), "exec", "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, "enum::user_status",
        "param neutral_type for status"
    );
    assert!(!analyzed.params[1].nullable, "param nullable for status");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "insert_enum", 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, "insert_enum", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "insert_enum"
                        );
                    } 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,
                            "insert_enum",
                            errors
                        );
                    }
                }
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "insert_enum"
                );
                if *backend_name == "rust-sqlx" {
                    let actual = generated.query_fn.clone().unwrap_or_default();
                    assert!(
                        normalize_whitespace(&actual).contains(&normalize_whitespace("pub async fn insert_user(pool: &sqlx::PgPool, name: &str, status: &UserStatus) -> Result<(), sqlx::Error> {\n    sqlx::query!(\"INSERT INTO users (name, status) VALUES ($1, $2)\", name, status as &UserStatus)\n        .execute(pool)\n        .await?;\n    Ok(())\n}")),
                        "backend {} query_fn mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "insert_enum", "pub async fn insert_user(pool: &sqlx::PgPool, name: &str, status: &UserStatus) -> Result<(), sqlx::Error> {\n    sqlx::query!(\"INSERT INTO users (name, status) VALUES ($1, $2)\", name, status as &UserStatus)\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,
                        "insert_enum",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "insert_enum", e
                ),
            },
        }
    }
}

#[test]
fn test_nullable_enum() {
    // From: testing_data/types/enums/02_nullable_enum.json
    // "Select a nullable enum column, verifying it maps to Option<UserStatus>"
    let schema_sql = &[
        "CREATE TYPE user_status AS ENUM ('active', 'inactive', 'banned');",
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, status user_status);",
    ];

    let query_sql = "-- @name GetUserStatusNullable\n-- @returns :many\nSELECT id, status FROM users;";

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

    assert_eq!(analyzed.name, "GetUserStatusNullable", "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, "status", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "enum::user_status",
        "column neutral_type for status"
    );
    assert!(analyzed.columns[1].nullable, "column nullable for status");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "nullable_enum", 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, "nullable_enum", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "nullable_enum"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "nullable_enum",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "nullable_enum"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "nullable_enum"
                );
                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 GetUserStatusNullableRow {\n    pub id: i32,\n    pub status: Option<UserStatus>,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "nullable_enum", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetUserStatusNullableRow {\n    pub id: i32,\n    pub status: Option<UserStatus>,\n}", actual
                    );
                    let actual = generated.query_fn.clone().unwrap_or_default();
                    assert!(
                        normalize_whitespace(&actual).contains(&normalize_whitespace("pub async fn get_user_status_nullable(pool: &sqlx::PgPool) -> Result<Vec<GetUserStatusNullableRow>, sqlx::Error> {\n    sqlx::query_as!(GetUserStatusNullableRow, \"SELECT id, status AS \\\"status: Option<UserStatus>\\\" FROM users\")\n        .fetch_all(pool)\n        .await\n}")),
                        "backend {} query_fn mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "nullable_enum", "pub async fn get_user_status_nullable(pool: &sqlx::PgPool) -> Result<Vec<GetUserStatusNullableRow>, sqlx::Error> {\n    sqlx::query_as!(GetUserStatusNullableRow, \"SELECT id, status AS \\\"status: Option<UserStatus>\\\" FROM users\")\n        .fetch_all(pool)\n        .await\n}", actual
                    );
                    let actual = generated.enum_def.clone().unwrap_or_default();
                    assert!(
                        normalize_whitespace(&actual).contains(&normalize_whitespace("#[derive(Debug, Clone, PartialEq, Eq, sqlx::Type)]\n#[sqlx(type_name = \"user_status\", rename_all = \"snake_case\")]\npub enum UserStatus {\n    Active,\n    Inactive,\n    Banned,\n}")),
                        "backend {} enum_def mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "nullable_enum", "#[derive(Debug, Clone, PartialEq, Eq, sqlx::Type)]\n#[sqlx(type_name = \"user_status\", rename_all = \"snake_case\")]\npub enum UserStatus {\n    Active,\n    Inactive,\n    Banned,\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,
                        "nullable_enum",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "nullable_enum", e
                ),
            },
        }
    }
}

#[test]
fn test_select_enum_column() {
    // From: testing_data/types/enums/01_select_enum_column.json
    // "Select a non-nullable enum column and verify it maps to a generated Rust enum"
    let schema_sql = &[
        "CREATE TYPE user_status AS ENUM ('active', 'inactive', 'banned');",
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, status user_status NOT NULL);",
    ];

    let query_sql = "-- @name GetUserStatus\n-- @returns :many\nSELECT id, status FROM users;";

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

    assert_eq!(analyzed.name, "GetUserStatus", "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, "status", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "enum::user_status",
        "column neutral_type for status"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for status");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "select_enum_column", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "select_enum_column", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "select_enum_column"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "select_enum_column",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "select_enum_column"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "select_enum_column"
                );
                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 GetUserStatusRow {\n    pub id: i32,\n    pub status: UserStatus,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "select_enum_column", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetUserStatusRow {\n    pub id: i32,\n    pub status: UserStatus,\n}", actual
                    );
                    let actual = generated.query_fn.clone().unwrap_or_default();
                    assert!(
                        normalize_whitespace(&actual).contains(&normalize_whitespace("pub async fn get_user_status(pool: &sqlx::PgPool) -> Result<Vec<GetUserStatusRow>, sqlx::Error> {\n    sqlx::query_as!(GetUserStatusRow, \"SELECT id, status AS \\\"status: UserStatus\\\" FROM users\")\n        .fetch_all(pool)\n        .await\n}")),
                        "backend {} query_fn mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "select_enum_column", "pub async fn get_user_status(pool: &sqlx::PgPool) -> Result<Vec<GetUserStatusRow>, sqlx::Error> {\n    sqlx::query_as!(GetUserStatusRow, \"SELECT id, status AS \\\"status: UserStatus\\\" FROM users\")\n        .fetch_all(pool)\n        .await\n}", actual
                    );
                    let actual = generated.enum_def.clone().unwrap_or_default();
                    assert!(
                        normalize_whitespace(&actual).contains(&normalize_whitespace("#[derive(Debug, Clone, PartialEq, Eq, sqlx::Type)]\n#[sqlx(type_name = \"user_status\", rename_all = \"snake_case\")]\npub enum UserStatus {\n    Active,\n    Inactive,\n    Banned,\n}")),
                        "backend {} enum_def mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "select_enum_column", "#[derive(Debug, Clone, PartialEq, Eq, sqlx::Type)]\n#[sqlx(type_name = \"user_status\", rename_all = \"snake_case\")]\npub enum UserStatus {\n    Active,\n    Inactive,\n    Banned,\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,
                        "select_enum_column",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "select_enum_column", e
                ),
            },
        }
    }
}

#[test]
fn test_json_column() {
    // From: testing_data/types/json_jsonb/01_json_column.json
    // "Select a non-nullable JSON column mapped to serde_json::Value"
    let schema_sql = &["CREATE TABLE events (id SERIAL PRIMARY KEY, data JSON NOT NULL);"];

    let query_sql = "-- @name GetEvents\n-- @returns :many\nSELECT id, data FROM events;";

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

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

    // 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, "json_column", 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, "json_column", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "json_column"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "json_column",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "json_column"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "json_column"
                );
                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 GetEventsRow {\n    pub id: i32,\n    pub data: serde_json::Value,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "json_column", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetEventsRow {\n    pub id: i32,\n    pub data: serde_json::Value,\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,
                        "json_column",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "json_column", e
                ),
            },
        }
    }
}

#[test]
fn test_json_typed_mapping() {
    // From: testing_data/types/json_jsonb/04_json_typed_mapping.json
    // "Map a JSON column to a strongly-typed Rust struct using the @json annotation"
    let schema_sql = &["CREATE TABLE events (id SERIAL PRIMARY KEY, data JSONB NOT NULL);"];

    let query_sql =
        "-- @name GetEventsTyped\n-- @returns :many\n-- @json data = EventData\nSELECT id, data FROM events;";

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

    assert_eq!(analyzed.name, "GetEventsTyped", "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, "data", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "json_typed<EventData>",
        "column neutral_type for data"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for data");

    // 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, "json_typed_mapping", 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, "json_typed_mapping", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "json_typed_mapping"
                        );
                    } 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,
                            "json_typed_mapping",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "json_typed_mapping"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "json_typed_mapping"
                );
                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 GetEventsTypedRow {\n    pub id: i32,\n    pub data: sqlx::types::Json<EventData>,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "json_typed_mapping", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetEventsTypedRow {\n    pub id: i32,\n    pub data: sqlx::types::Json<EventData>,\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,
                        "json_typed_mapping",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "json_typed_mapping", e
                ),
            },
        }
    }
}

#[test]
fn test_jsonb_arrow_json_extraction() {
    // From: testing_data/types/json_jsonb/06_jsonb_arrow_json.json
    // "Use -> operator to extract nested JSON from JSONB; result is nullable serde_json::Value"
    let schema_sql = &["CREATE TABLE events (id SERIAL PRIMARY KEY, data JSONB NOT NULL);"];

    let query_sql = "-- @name GetEventNested\n-- @returns :many\nSELECT id, data->'nested' AS nested FROM events;";

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

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

    // 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, "jsonb_arrow_json_extraction", 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, "jsonb_arrow_json_extraction", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "jsonb_arrow_json_extraction"
                        );
                    } 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,
                            "jsonb_arrow_json_extraction",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "jsonb_arrow_json_extraction"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "jsonb_arrow_json_extraction"
                );
                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 GetEventNestedRow {\n    pub id: i32,\n    pub nested: Option<serde_json::Value>,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "jsonb_arrow_json_extraction", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetEventNestedRow {\n    pub id: i32,\n    pub nested: Option<serde_json::Value>,\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,
                        "jsonb_arrow_json_extraction",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "jsonb_arrow_json_extraction", e
                ),
            },
        }
    }
}

#[test]
fn test_jsonb_arrow_text_extraction() {
    // From: testing_data/types/json_jsonb/05_jsonb_arrow_operator.json
    // "Use ->> operator to extract text from JSONB; result is nullable String since key may not exist"
    let schema_sql = &["CREATE TABLE events (id SERIAL PRIMARY KEY, data JSONB NOT NULL);"];

    let query_sql = "-- @name GetEventName\n-- @returns :many\nSELECT id, data->>'name' AS name FROM events;";

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

    assert_eq!(analyzed.name, "GetEventName", "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, "jsonb_arrow_text_extraction", 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, "jsonb_arrow_text_extraction", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "jsonb_arrow_text_extraction"
                        );
                    } 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,
                            "jsonb_arrow_text_extraction",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "jsonb_arrow_text_extraction"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "jsonb_arrow_text_extraction"
                );
                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 GetEventNameRow {\n    pub id: i32,\n    pub name: Option<String>,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "jsonb_arrow_text_extraction", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetEventNameRow {\n    pub id: i32,\n    pub name: Option<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,
                        "jsonb_arrow_text_extraction",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "jsonb_arrow_text_extraction", e
                ),
            },
        }
    }
}

#[test]
fn test_jsonb_column() {
    // From: testing_data/types/json_jsonb/02_jsonb_column.json
    // "Select a non-nullable JSONB column mapped to serde_json::Value"
    let schema_sql = &["CREATE TABLE events (id SERIAL PRIMARY KEY, data JSONB NOT NULL);"];

    let query_sql = "-- @name GetEventsJsonb\n-- @returns :many\nSELECT id, data FROM events;";

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

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

    // 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, "jsonb_column", 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, "jsonb_column", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "jsonb_column"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "jsonb_column",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "jsonb_column"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "jsonb_column"
                );
                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 GetEventsJsonbRow {\n    pub id: i32,\n    pub data: serde_json::Value,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "jsonb_column", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetEventsJsonbRow {\n    pub id: i32,\n    pub data: serde_json::Value,\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,
                        "jsonb_column",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "jsonb_column", e
                ),
            },
        }
    }
}

#[test]
fn test_nullable_json() {
    // From: testing_data/types/json_jsonb/03_nullable_json.json
    // "Select a nullable JSON column mapped to Option<serde_json::Value>"
    let schema_sql = &["CREATE TABLE events (id SERIAL PRIMARY KEY, data JSON);"];

    let query_sql = "-- @name GetEventsNullableJson\n-- @returns :many\nSELECT id, data FROM events;";

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

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

    // 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, "nullable_json", 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, "nullable_json", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "nullable_json"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "nullable_json",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "nullable_json"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "nullable_json"
                );
                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 GetEventsNullableJsonRow {\n    pub id: i32,\n    pub data: Option<serde_json::Value>,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "nullable_json", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetEventsNullableJsonRow {\n    pub id: i32,\n    pub data: Option<serde_json::Value>,\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,
                        "nullable_json",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "nullable_json", e
                ),
            },
        }
    }
}

#[test]
fn test_json_array_elements_select_list() {
    // From: testing_data/types/json_jsonb_advanced/10_json_array_elements_select_list.json
    // "Test the json_ (not jsonb_) variant of json_array_elements in select-list position, using a native json column"
    let schema_sql = &["CREATE TABLE payloads (id SERIAL PRIMARY KEY, payload JSON NOT NULL);"];

    let query_sql = "-- @name GetPayloadItemsSelectList\n-- @returns :many\nSELECT id, json_array_elements(payload->'items') AS item FROM payloads";

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

    // 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, "json_array_elements_select_list", 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, "json_array_elements_select_list", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "json_array_elements_select_list"
                        );
                    } 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,
                            "json_array_elements_select_list",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "json_array_elements_select_list"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "json_array_elements_select_list"
                );
            }
            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,
                        "json_array_elements_select_list",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "json_array_elements_select_list", e
                ),
            },
        }
    }
}

#[test]
fn test_jsonb_array_elements_from() {
    // From: testing_data/types/json_jsonb_advanced/03_jsonb_array_elements.json
    // "Test jsonb_array_elements set-returning function in FROM clause to expand a JSONB array into rows"
    let schema_sql = &["CREATE TABLE documents (id SERIAL PRIMARY KEY, data JSONB NOT NULL, metadata JSONB);"];

    let query_sql = "-- @name GetDocItems\n-- @returns :many\nSELECT id, elem.value AS item FROM documents, jsonb_array_elements(data->'items') AS elem";

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

    // 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, "jsonb_array_elements_from", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "jsonb_array_elements_from", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "jsonb_array_elements_from"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "jsonb_array_elements_from",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "jsonb_array_elements_from"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "jsonb_array_elements_from"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "jsonb_array_elements_from",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "jsonb_array_elements_from", e
                ),
            },
        }
    }
}

#[test]
fn test_jsonb_array_elements_select_list() {
    // From: testing_data/types/json_jsonb_advanced/09_jsonb_array_elements_select_list.json
    // "Test jsonb_array_elements in select-list position: unlike jsonb_each, this is SETOF jsonb (a scalar per row), so the column really is json"
    let schema_sql = &["CREATE TABLE documents (id SERIAL PRIMARY KEY, data JSONB NOT NULL, metadata JSONB);"];

    let query_sql = "-- @name GetDocItemsSelectList\n-- @returns :many\nSELECT id, jsonb_array_elements(data->'items') AS item FROM documents";

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

    // 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, "jsonb_array_elements_select_list", 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, "jsonb_array_elements_select_list", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "jsonb_array_elements_select_list"
                        );
                    } 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,
                            "jsonb_array_elements_select_list",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "jsonb_array_elements_select_list"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "jsonb_array_elements_select_list"
                );
            }
            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,
                        "jsonb_array_elements_select_list",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "jsonb_array_elements_select_list", e
                ),
            },
        }
    }
}

#[test]
fn test_jsonb_contains_operator() {
    // From: testing_data/types/json_jsonb_advanced/01_jsonb_contains.json
    // "Test @> containment operator in WHERE clause with JSONB"
    let schema_sql = &["CREATE TABLE documents (id SERIAL PRIMARY KEY, data JSONB NOT NULL, metadata JSONB);"];

    let query_sql = "-- @name FindDocsByContent\n-- @returns :many\nSELECT id, data FROM documents WHERE data @> '{\"type\": \"report\"}'";

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

    // 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, "jsonb_contains_operator", 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, "jsonb_contains_operator", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "jsonb_contains_operator"
                        );
                    } 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,
                            "jsonb_contains_operator",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "jsonb_contains_operator"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "jsonb_contains_operator"
                );
            }
            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,
                        "jsonb_contains_operator",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "jsonb_contains_operator", e
                ),
            },
        }
    }
}

#[test]
fn test_jsonb_deep_path_extraction() {
    // From: testing_data/types/json_jsonb_advanced/02_jsonb_path_deep.json
    // "Test #>> operator for deep path extraction from JSONB, returning text that may be null if path does not exist"
    let schema_sql = &["CREATE TABLE documents (id SERIAL PRIMARY KEY, data JSONB NOT NULL, metadata JSONB);"];

    let query_sql =
        "-- @name GetDeepValue\n-- @returns :many\nSELECT id, data #>> '{nested,deep,key}' AS deep_val FROM documents";

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

    // 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, "jsonb_deep_path_extraction", 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, "jsonb_deep_path_extraction", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "jsonb_deep_path_extraction"
                        );
                    } 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,
                            "jsonb_deep_path_extraction",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "jsonb_deep_path_extraction"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "jsonb_deep_path_extraction"
                );
            }
            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,
                        "jsonb_deep_path_extraction",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "jsonb_deep_path_extraction", e
                ),
            },
        }
    }
}

#[test]
fn test_jsonb_each_from() {
    // From: testing_data/types/json_jsonb_advanced/06_jsonb_each_from.json
    // "Test jsonb_each in FROM position, which expands into two real columns: key (text) and value (jsonb)"
    let schema_sql = &["CREATE TABLE documents (id SERIAL PRIMARY KEY, data JSONB NOT NULL, metadata JSONB);"];

    let query_sql =
        "-- @name GetDocEntries\n-- @returns :many\nSELECT id, kv.key, kv.value FROM documents, jsonb_each(data) AS kv";

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

    // 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, "jsonb_each_from", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "jsonb_each_from", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "jsonb_each_from"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "jsonb_each_from",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "jsonb_each_from"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "jsonb_each_from"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "jsonb_each_from",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "jsonb_each_from", e
                ),
            },
        }
    }
}

#[test]
fn test_jsonb_each_select_list() {
    // From: testing_data/types/json_jsonb_advanced/05_jsonb_each_select_list.json
    // "jsonb_each in select-list position (PostgreSQL SRF-in-target-list) yields an anonymous record scythe cannot name a type for -- rejected with an actionable error instead of reaching codegen as INTERNAL_ERROR (#223); see 06_jsonb_each_from.json for the FROM-clause form that resolves correctly"
    let schema_sql = &["CREATE TABLE documents (id SERIAL PRIMARY KEY, data JSONB NOT NULL, metadata JSONB);"];

    let query_sql = "-- @name GetDocEntries\n-- @returns :many\nSELECT id, jsonb_each(data) AS kv FROM documents";

    let catalog_result = scythe_core::catalog::Catalog::from_ddl(schema_sql);
    if let Ok(catalog) = catalog_result {
        let query_result = scythe_core::parser::parse_query(query_sql);
        if let Ok(query) = query_result {
            let result = scythe_core::analyzer::analyze(&catalog, &query);
            assert!(result.is_err(), "expected analysis to fail");
            let err = result.unwrap_err();
            let err_msg = err.to_string();
            assert!(
                err_msg.contains("UNRESOLVED_TYPE"),
                "error should contain code {:?}, got: {}",
                "UNRESOLVED_TYPE",
                err_msg
            );
            assert!(
                err_msg.contains("jsonb_each"),
                "error should contain {:?}, got: {}",
                "jsonb_each",
                err_msg
            );
        } else {
            // Parse failed -- that counts as expected failure.
            let err = query_result.unwrap_err();
            let err_msg = err.to_string();
            assert!(
                err_msg.contains("UNRESOLVED_TYPE"),
                "error should contain code {:?}, got: {}",
                "UNRESOLVED_TYPE",
                err_msg
            );
            assert!(
                err_msg.contains("jsonb_each"),
                "error should contain {:?}, got: {}",
                "jsonb_each",
                err_msg
            );
        }
    } else {
        // DDL processing failed -- that counts as expected failure.
        let err = catalog_result.unwrap_err();
        let err_msg = err.to_string();
        assert!(
            err_msg.contains("UNRESOLVED_TYPE"),
            "error should contain code {:?}, got: {}",
            "UNRESOLVED_TYPE",
            err_msg
        );
        assert!(
            err_msg.contains("jsonb_each"),
            "error should contain {:?}, got: {}",
            "jsonb_each",
            err_msg
        );
    }
}

#[test]
fn test_jsonb_each_text_from() {
    // From: testing_data/types/json_jsonb_advanced/08_jsonb_each_text_from.json
    // "Test jsonb_each_text in FROM position, which expands into two text columns: key and value"
    let schema_sql = &["CREATE TABLE documents (id SERIAL PRIMARY KEY, data JSONB NOT NULL, metadata JSONB);"];

    let query_sql = "-- @name GetDocEntryText\n-- @returns :many\nSELECT id, kv.key, kv.value FROM documents, jsonb_each_text(data) AS kv";

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

    // 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, "jsonb_each_text_from", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "jsonb_each_text_from", expected_message
                    );
                }
                let preamble = backend.file_preamble();
                let header = backend.file_header();
                let mut body = String::new();
                if header.is_empty() {
                    body.push_str("#![allow(dead_code, unused_imports)]\n");
                } else {
                    body.push_str(&header);
                    body.push('\n');
                }
                if let Some(ref s) = generated.enum_def {
                    body.push_str(s);
                    body.push('\n');
                }
                for def in &generated.nested_struct_defs {
                    body.push_str(&def.code);
                    body.push('\n');
                }
                if let Some(ref s) = generated.model_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.row_struct {
                    body.push_str(s);
                    body.push('\n');
                }
                if let Some(ref s) = generated.query_fn {
                    body.push_str(s);
                    body.push('\n');
                }
                let code = scythe_codegen::provenance::assemble_file(
                    &preamble,
                    &scythe_codegen::provenance::header_line(
                        &*backend,
                        env!("CARGO_PKG_VERSION"),
                        engine,
                        "sch1:0123456789abcdef",
                        "q1:fedcba9876543210",
                    ),
                    &body,
                );
                if body.lines().count() > 1 {
                    // Only validate Rust syntax with syn for Rust backends
                    if *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "jsonb_each_text_from"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "jsonb_each_text_from",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "jsonb_each_text_from"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "jsonb_each_text_from"
                );
            }
            Err(e) => match declared_codegen_failure {
                Some(expected_message) => {
                    let message = e.to_string();
                    assert!(
                        message.contains(expected_message),
                        "backend {} codegen error for fixture {} did not match the declared expected.codegen_errors message\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name,
                        "jsonb_each_text_from",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "jsonb_each_text_from", e
                ),
            },
        }
    }
}

#[test]
fn test_jsonb_each_text_select_list() {
    // From: testing_data/types/json_jsonb_advanced/07_jsonb_each_text_select_list.json
    // "jsonb_each_text in select-list position (PostgreSQL SRF-in-target-list) yields an anonymous record scythe cannot name a type for -- rejected with an actionable error instead of reaching codegen as INTERNAL_ERROR (#223); see 08_jsonb_each_text_from.json for the FROM-clause form that resolves correctly"
    let schema_sql = &["CREATE TABLE documents (id SERIAL PRIMARY KEY, data JSONB NOT NULL, metadata JSONB);"];

    let query_sql =
        "-- @name GetDocEntryText\n-- @returns :many\nSELECT id, jsonb_each_text(data) AS kv FROM documents";

    let catalog_result = scythe_core::catalog::Catalog::from_ddl(schema_sql);
    if let Ok(catalog) = catalog_result {
        let query_result = scythe_core::parser::parse_query(query_sql);
        if let Ok(query) = query_result {
            let result = scythe_core::analyzer::analyze(&catalog, &query);
            assert!(result.is_err(), "expected analysis to fail");
            let err = result.unwrap_err();
            let err_msg = err.to_string();
            assert!(
                err_msg.contains("UNRESOLVED_TYPE"),
                "error should contain code {:?}, got: {}",
                "UNRESOLVED_TYPE",
                err_msg
            );
            assert!(
                err_msg.contains("jsonb_each_text"),
                "error should contain {:?}, got: {}",
                "jsonb_each_text",
                err_msg
            );
        } else {
            // Parse failed -- that counts as expected failure.
            let err = query_result.unwrap_err();
            let err_msg = err.to_string();
            assert!(
                err_msg.contains("UNRESOLVED_TYPE"),
                "error should contain code {:?}, got: {}",
                "UNRESOLVED_TYPE",
                err_msg
            );
            assert!(
                err_msg.contains("jsonb_each_text"),
                "error should contain {:?}, got: {}",
                "jsonb_each_text",
                err_msg
            );
        }
    } else {
        // DDL processing failed -- that counts as expected failure.
        let err = catalog_result.unwrap_err();
        let err_msg = err.to_string();
        assert!(
            err_msg.contains("UNRESOLVED_TYPE"),
            "error should contain code {:?}, got: {}",
            "UNRESOLVED_TYPE",
            err_msg
        );
        assert!(
            err_msg.contains("jsonb_each_text"),
            "error should contain {:?}, got: {}",
            "jsonb_each_text",
            err_msg
        );
    }
}

#[test]
fn test_jsonb_object_keys_func() {
    // From: testing_data/types/json_jsonb_advanced/04_jsonb_object_keys.json
    // "Test jsonb_object_keys function which returns text keys from a JSONB object as a set"
    let schema_sql = &["CREATE TABLE documents (id SERIAL PRIMARY KEY, data JSONB NOT NULL, metadata JSONB);"];

    let query_sql =
        "-- @name GetDocKeys\n-- @returns :many\nSELECT id, jsonb_object_keys(data) AS key_name FROM documents";

    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, "GetDocKeys", "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, "key_name", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "string",
        "column neutral_type for key_name"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for key_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, "jsonb_object_keys_func", 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, "jsonb_object_keys_func", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "jsonb_object_keys_func"
                        );
                    } 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,
                            "jsonb_object_keys_func",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "jsonb_object_keys_func"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "jsonb_object_keys_func"
                );
            }
            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,
                        "jsonb_object_keys_func",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "jsonb_object_keys_func", e
                ),
            },
        }
    }
}

#[test]
fn test_select_network_types() {
    // From: testing_data/types/network/01_network_types.json
    // "Select inet and cidr network columns mapped to ipnetwork::IpNetwork"
    let schema_sql = &["CREATE TABLE hosts (id SERIAL PRIMARY KEY, ip_addr INET NOT NULL, subnet CIDR NOT NULL);"];

    let query_sql = "-- @name GetHosts\n-- @returns :many\nSELECT id, ip_addr, subnet FROM hosts;";

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

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "select_network_types", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "select_network_types", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "select_network_types"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "select_network_types",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "select_network_types"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "select_network_types"
                );
                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 GetHostsRow {\n    pub id: i32,\n    pub ip_addr: ipnetwork::IpNetwork,\n    pub subnet: ipnetwork::IpNetwork,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "select_network_types", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetHostsRow {\n    pub id: i32,\n    pub ip_addr: ipnetwork::IpNetwork,\n    pub subnet: ipnetwork::IpNetwork,\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,
                        "select_network_types",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "select_network_types", e
                ),
            },
        }
    }
}

#[test]
fn test_select_all_numeric_types() {
    // From: testing_data/types/numeric/01_all_numeric.json
    // "Select all numeric types and verify correct Rust type mappings"
    let schema_sql = &[
        "CREATE TABLE numeric_data (id SERIAL PRIMARY KEY, small SMALLINT NOT NULL, med INTEGER NOT NULL, big BIGINT NOT NULL, r REAL NOT NULL, d DOUBLE PRECISION NOT NULL, n NUMERIC(10,2) NOT NULL);",
    ];

    let query_sql =
        "-- @name GetNumericData\n-- @returns :many\nSELECT id, small, med, big, r, d, n FROM numeric_data;";

    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, "GetNumericData", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 7, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "small", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "int16",
        "column neutral_type for small"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for small");
    assert_eq!(analyzed.columns[2].name, "med", "column name");
    assert_eq!(analyzed.columns[2].neutral_type, "int32", "column neutral_type for med");
    assert!(!analyzed.columns[2].nullable, "column nullable for med");
    assert_eq!(analyzed.columns[3].name, "big", "column name");
    assert_eq!(analyzed.columns[3].neutral_type, "int64", "column neutral_type for big");
    assert!(!analyzed.columns[3].nullable, "column nullable for big");
    assert_eq!(analyzed.columns[4].name, "r", "column name");
    assert_eq!(analyzed.columns[4].neutral_type, "float32", "column neutral_type for r");
    assert!(!analyzed.columns[4].nullable, "column nullable for r");
    assert_eq!(analyzed.columns[5].name, "d", "column name");
    assert_eq!(analyzed.columns[5].neutral_type, "float64", "column neutral_type for d");
    assert!(!analyzed.columns[5].nullable, "column nullable for d");
    assert_eq!(analyzed.columns[6].name, "n", "column name");
    assert_eq!(analyzed.columns[6].neutral_type, "decimal", "column neutral_type for n");
    assert!(!analyzed.columns[6].nullable, "column nullable for n");

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "select_all_numeric_types", e
            ),
        };
        let declared_codegen_failure: Option<&str> = None;
        match scythe_codegen::generate_with_backend(&analyzed, &*backend) {
            Ok(generated) => {
                if let Some(expected_message) = declared_codegen_failure {
                    panic!(
                        "backend {} was declared under expected.codegen_errors to fail codegen for fixture {} (declared message {:?}), but codegen succeeded -- delete the stale entry",
                        backend_name, "select_all_numeric_types", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "select_all_numeric_types"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "select_all_numeric_types",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "select_all_numeric_types"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "select_all_numeric_types"
                );
                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 GetNumericDataRow {\n    pub id: i32,\n    pub small: i16,\n    pub med: i32,\n    pub big: i64,\n    pub r: f32,\n    pub d: f64,\n    pub n: rust_decimal::Decimal,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "select_all_numeric_types", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetNumericDataRow {\n    pub id: i32,\n    pub small: i16,\n    pub med: i32,\n    pub big: i64,\n    pub r: f32,\n    pub d: f64,\n    pub n: rust_decimal::Decimal,\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,
                        "select_all_numeric_types",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "select_all_numeric_types", e
                ),
            },
        }
    }
}

#[test]
fn test_range_int4range() {
    // From: testing_data/types/range/01_int4range.json
    // "Select an INT4RANGE column mapped to PgRange<i32>"
    let schema_sql =
        &["CREATE TABLE reservations (id SERIAL PRIMARY KEY, room_id INTEGER NOT NULL, during INT4RANGE NOT NULL);"];

    let query_sql =
        "-- @name GetReservation\n-- @returns :one\nSELECT id, room_id, during FROM reservations 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, "GetReservation", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.params.len(), 1, "param count");
    assert_eq!(analyzed.params[0].name, "id", "param name");
    assert_eq!(analyzed.params[0].neutral_type, "int32", "param neutral_type for id");
    assert!(!analyzed.params[0].nullable, "param nullable for id");
    assert_eq!(analyzed.params[0].position, 1, "param position for id");
    assert_eq!(analyzed.columns.len(), 3, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "room_id", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "int32",
        "column neutral_type for room_id"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for room_id");
    assert_eq!(analyzed.columns[2].name, "during", "column name");
    assert_eq!(
        analyzed.columns[2].neutral_type, "range<int32>",
        "column neutral_type for during"
    );
    assert!(!analyzed.columns[2].nullable, "column nullable for during");

    // 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, "range_int4range", 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, "range_int4range", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "range_int4range"
                        );
                    } 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,
                            "range_int4range",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "range_int4range"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "range_int4range"
                );
            }
            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,
                        "range_int4range",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "range_int4range", e
                ),
            },
        }
    }
}

#[test]
fn test_range_tstzrange() {
    // From: testing_data/types/range/02_tstzrange.json
    // "Select a TSTZRANGE column mapped to PgRange<DateTime<Utc>>"
    let schema_sql = &["CREATE TABLE bookings (id SERIAL PRIMARY KEY, time_slot TSTZRANGE NOT NULL);"];

    let query_sql = "-- @name GetBooking\n-- @returns :one\nSELECT id, time_slot FROM bookings 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, "GetBooking", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.params.len(), 1, "param count");
    assert_eq!(analyzed.params[0].name, "id", "param name");
    assert_eq!(analyzed.params[0].neutral_type, "int32", "param neutral_type for id");
    assert!(!analyzed.params[0].nullable, "param nullable for id");
    assert_eq!(analyzed.params[0].position, 1, "param position for id");
    assert_eq!(analyzed.columns.len(), 2, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "time_slot", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "range<datetime_tz>",
        "column neutral_type for time_slot"
    );
    assert!(!analyzed.columns[1].nullable, "column nullable for time_slot");

    // 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, "range_tstzrange", 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, "range_tstzrange", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "range_tstzrange"
                        );
                    } 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,
                            "range_tstzrange",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "range_tstzrange"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "range_tstzrange"
                );
            }
            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,
                        "range_tstzrange",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "range_tstzrange", e
                ),
            },
        }
    }
}

#[test]
fn test_all_temporal_types() {
    // From: testing_data/types/temporal/01_all_temporal.json
    // "Select all temporal types and verify correct Rust type mappings"
    let schema_sql = &[
        "CREATE TABLE temporal_data (id SERIAL PRIMARY KEY, d DATE NOT NULL, t TIME NOT NULL, tz TIMETZ NOT NULL, ts TIMESTAMP NOT NULL, tstz TIMESTAMPTZ NOT NULL, dur INTERVAL NOT NULL);",
    ];

    let query_sql =
        "-- @name GetTemporalData\n-- @returns :many\nSELECT id, d, t, tz, ts, tstz, dur FROM temporal_data;";

    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, "GetTemporalData", "query name");
    assert_eq!(analyzed.command.to_string(), "many", "query command");
    assert_eq!(analyzed.columns.len(), 7, "column count");
    assert_eq!(analyzed.columns[0].name, "id", "column name");
    assert_eq!(analyzed.columns[0].neutral_type, "int32", "column neutral_type for id");
    assert!(!analyzed.columns[0].nullable, "column nullable for id");
    assert_eq!(analyzed.columns[1].name, "d", "column name");
    assert_eq!(analyzed.columns[1].neutral_type, "date", "column neutral_type for d");
    assert!(!analyzed.columns[1].nullable, "column nullable for d");
    assert_eq!(analyzed.columns[2].name, "t", "column name");
    assert_eq!(analyzed.columns[2].neutral_type, "time", "column neutral_type for t");
    assert!(!analyzed.columns[2].nullable, "column nullable for t");
    assert_eq!(analyzed.columns[3].name, "tz", "column name");
    assert_eq!(
        analyzed.columns[3].neutral_type, "time_tz",
        "column neutral_type for tz"
    );
    assert!(!analyzed.columns[3].nullable, "column nullable for tz");
    assert_eq!(analyzed.columns[4].name, "ts", "column name");
    assert_eq!(
        analyzed.columns[4].neutral_type, "datetime",
        "column neutral_type for ts"
    );
    assert!(!analyzed.columns[4].nullable, "column nullable for ts");
    assert_eq!(analyzed.columns[5].name, "tstz", "column name");
    assert_eq!(
        analyzed.columns[5].neutral_type, "datetime_tz",
        "column neutral_type for tstz"
    );
    assert!(!analyzed.columns[5].nullable, "column nullable for tstz");
    assert_eq!(analyzed.columns[6].name, "dur", "column name");
    assert_eq!(
        analyzed.columns[6].neutral_type, "interval",
        "column neutral_type for dur"
    );
    assert!(!analyzed.columns[6].nullable, "column nullable for dur");

    // 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, "all_temporal_types", 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, "all_temporal_types", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "all_temporal_types"
                        );
                    } 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,
                            "all_temporal_types",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "all_temporal_types"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "all_temporal_types"
                );
                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 GetTemporalDataRow {\n    pub id: i32,\n    pub d: chrono::NaiveDate,\n    pub t: chrono::NaiveTime,\n    pub tz: sqlx::postgres::types::PgTimeTz,\n    pub ts: chrono::NaiveDateTime,\n    pub tstz: chrono::DateTime<chrono::Utc>,\n    pub dur: sqlx::postgres::types::PgInterval,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "all_temporal_types", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetTemporalDataRow {\n    pub id: i32,\n    pub d: chrono::NaiveDate,\n    pub t: chrono::NaiveTime,\n    pub tz: sqlx::postgres::types::PgTimeTz,\n    pub ts: chrono::NaiveDateTime,\n    pub tstz: chrono::DateTime<chrono::Utc>,\n    pub dur: sqlx::postgres::types::PgInterval,\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,
                        "all_temporal_types",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "all_temporal_types", e
                ),
            },
        }
    }
}

#[test]
fn test_temporal_params() {
    // From: testing_data/types/temporal/02_temporal_params.json
    // "Use a timestamptz column in a WHERE clause parameter"
    let schema_sql = &[
        "CREATE TABLE events (id SERIAL PRIMARY KEY, name TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW());",
    ];

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

    // Codegen verification: every backend that supports this fixture's engine
    // should produce valid output. The list below is derived at generation
    // time from the fixture's engine via the real `get_backend` registry, so
    // every entry here is expected to construct -- a construction failure
    // below is a real regression, not an expected engine mismatch. See #156.
    let engine = "postgresql";
    let all_backends = [
        "rust-sqlx",
        "rust-tokio-postgres",
        "python-psycopg3",
        "python-asyncpg",
        "typescript-postgres",
        "typescript-pg",
        "typescript-kysely",
        "go-pgx",
        "java-jdbc",
        "java-r2dbc",
        "kotlin-exposed",
        "kotlin-jdbc",
        "kotlin-r2dbc",
        "csharp-npgsql",
        "elixir-postgrex",
        "elixir-ecto",
        "ruby-pg",
        "php-pdo",
        "php-amphp",
    ];
    for backend_name in &all_backends {
        let backend = match scythe_codegen::get_backend(backend_name, engine) {
            Ok(b) => b,
            Err(e) => panic!(
                "backend {} failed to construct for engine {} in fixture {}: {}",
                backend_name, engine, "temporal_params", 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, "temporal_params", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "temporal_params"
                        );
                    } 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,
                            "temporal_params",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "temporal_params"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "temporal_params"
                );
                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 GetEventsAfterRow {\n    pub id: i32,\n    pub name: String,\n    pub created_at: chrono::DateTime<chrono::Utc>,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "temporal_params", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetEventsAfterRow {\n    pub id: i32,\n    pub name: String,\n    pub created_at: chrono::DateTime<chrono::Utc>,\n}", actual
                    );
                    let actual = generated.query_fn.clone().unwrap_or_default();
                    assert!(
                        normalize_whitespace(&actual).contains(&normalize_whitespace("pub async fn get_events_after(pool: &sqlx::PgPool, created_at: &chrono::DateTime<chrono::Utc>) -> Result<Vec<GetEventsAfterRow>, sqlx::Error> {\n    sqlx::query_as!(GetEventsAfterRow, \"SELECT id, name, created_at FROM events WHERE created_at > $1\", created_at)\n        .fetch_all(pool)\n        .await\n}")),
                        "backend {} query_fn mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "temporal_params", "pub async fn get_events_after(pool: &sqlx::PgPool, created_at: &chrono::DateTime<chrono::Utc>) -> Result<Vec<GetEventsAfterRow>, sqlx::Error> {\n    sqlx::query_as!(GetEventsAfterRow, \"SELECT id, name, created_at FROM events WHERE created_at > $1\", created_at)\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,
                        "temporal_params",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "temporal_params", e
                ),
            },
        }
    }
}

#[test]
fn test_uuid_column() {
    // From: testing_data/types/uuid/01_uuid_column.json
    // "Select a UUID column mapped to uuid::Uuid"
    let schema_sql = &["CREATE TABLE users (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name TEXT NOT NULL);"];

    let query_sql = "-- @name GetUsers\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, "GetUsers", "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, "uuid", "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, "uuid_column", 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, "uuid_column", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "uuid_column"
                        );
                    } else {
                        // Structural validation for non-Rust backends
                        let errors = scythe_codegen::validation::validate_structural(&code, backend_name);
                        assert!(
                            errors.is_empty(),
                            "backend {} structural validation failed for {}: {:?}",
                            backend_name,
                            "uuid_column",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "uuid_column"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "uuid_column"
                );
                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 GetUsersRow {\n    pub id: uuid::Uuid,\n    pub name: String,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "uuid_column", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetUsersRow {\n    pub id: uuid::Uuid,\n    pub name: 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,
                        "uuid_column",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "uuid_column", e
                ),
            },
        }
    }
}

#[test]
fn test_uuid_param() {
    // From: testing_data/types/uuid/02_uuid_param.json
    // "Use a UUID primary key as a query parameter"
    let schema_sql = &["CREATE TABLE users (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name TEXT NOT NULL);"];

    let query_sql = "-- @name GetUserById\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, "GetUserById", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "query command");
    assert_eq!(analyzed.params.len(), 1, "param count");
    assert_eq!(analyzed.params[0].name, "id", "param name");
    assert_eq!(analyzed.params[0].neutral_type, "uuid", "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, "uuid", "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, "uuid_param", 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, "uuid_param", 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 *backend_name == "rust-sqlx" || *backend_name == "rust-tokio-postgres" {
                        assert!(
                            syn::parse_file(&code).is_ok(),
                            "backend {} generated invalid Rust for {}",
                            backend_name,
                            "uuid_param"
                        );
                    } 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,
                            "uuid_param",
                            errors
                        );
                    }
                }
                assert!(
                    generated.row_struct.is_some() || generated.model_struct.is_some(),
                    "backend {} should produce a struct for {}",
                    backend_name,
                    "uuid_param"
                );
                assert!(
                    generated.query_fn.is_some(),
                    "backend {} should produce query_fn for {}",
                    backend_name,
                    "uuid_param"
                );
                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 GetUserByIdRow {\n    pub id: uuid::Uuid,\n    pub name: String,\n}")),
                        "backend {} row_struct mismatch for {}\n--- expected (substring) ---\n{}\n--- actual ---\n{}",
                        backend_name, "uuid_param", "#[derive(Debug, Clone, sqlx::FromRow)]\npub struct GetUserByIdRow {\n    pub id: uuid::Uuid,\n    pub name: String,\n}", actual
                    );
                    let actual = generated.query_fn.clone().unwrap_or_default();
                    assert!(
                        normalize_whitespace(&actual).contains(&normalize_whitespace("pub async fn get_user_by_id(pool: &sqlx::PgPool, id: &uuid::Uuid) -> Result<GetUserByIdRow, sqlx::Error> {\n    sqlx::query_as!(GetUserByIdRow, \"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, "uuid_param", "pub async fn get_user_by_id(pool: &sqlx::PgPool, id: &uuid::Uuid) -> Result<GetUserByIdRow, sqlx::Error> {\n    sqlx::query_as!(GetUserByIdRow, \"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,
                        "uuid_param",
                        expected_message,
                        message
                    );
                }
                None => panic!(
                    "backend {} failed to generate code for engine {} in fixture {}: {}",
                    backend_name, engine, "uuid_param", e
                ),
            },
        }
    }
}