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

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

#[test]
fn test_delete_basic() {
    // From: testing_data/dml/delete/01_delete_basic.json
    // "Basic DELETE with WHERE parameter"
    let schema_sql =
        &["CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL);"];

    let query_sql = "-- @name DeleteUser\n-- @returns :exec\nDELETE FROM users WHERE id = $1;";

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

    assert_eq!(analyzed.name, "DeleteUser", "query name");
    assert_eq!(analyzed.command.to_string(), "exec", "query command");
    assert_eq!(analyzed.params.len(), 1, "param count");
    assert_eq!(analyzed.params[0].name, "id", "param name");
    assert_eq!(
        analyzed.params[0].neutral_type, "int32",
        "param neutral_type for id"
    );
    assert!(!analyzed.params[0].nullable, "param nullable for id");

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

#[test]
fn test_delete_returning() {
    // From: testing_data/dml/delete/02_delete_returning.json
    // "DELETE with RETURNING * to get the deleted row"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW());",
    ];

    let query_sql = "-- @name DeleteUserReturning\n-- @returns :one\nDELETE FROM users WHERE id = $1 RETURNING *;";

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

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

#[test]
fn test_insert_exec() {
    // From: testing_data/dml/insert/01_insert_exec.json
    // "Simple INSERT with :exec return type (no returning clause)"
    let schema_sql =
        &["CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL);"];

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

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

#[test]
fn test_insert_returning() {
    // From: testing_data/dml/insert/02_insert_returning.json
    // "INSERT with RETURNING specific columns"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW());",
    ];

    let query_sql = "-- @name CreateUserReturning\n-- @returns :one\nINSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, name, created_at;";

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

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

#[test]
fn test_insert_returning_star() {
    // From: testing_data/dml/insert/03_insert_returning_star.json
    // "INSERT with RETURNING * to get all columns"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW());",
    ];

    let query_sql = "-- @name CreateUserReturningStar\n-- @returns :one\nINSERT INTO users (name, email) VALUES ($1, $2) RETURNING *;";

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

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

#[test]
fn test_upsert() {
    // From: testing_data/dml/on_conflict/01_upsert.json
    // "INSERT ON CONFLICT DO UPDATE (upsert) with RETURNING *"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW());",
    ];

    let query_sql = "-- @name UpsertUser\n-- @returns :one\nINSERT INTO users (name, email) VALUES ($1, $2) ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name RETURNING *;";

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

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

#[test]
fn test_returning_specific_columns() {
    // From: testing_data/dml/returning/01_returning_specific_columns.json
    // "INSERT with RETURNING only specific columns (not *)"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW());",
    ];

    let query_sql = "-- @name CreateUserReturnIdAndDate\n-- @returns :one\nINSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, created_at;";

    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, "CreateUserReturnIdAndDate", "query name");
    assert_eq!(analyzed.command.to_string(), "one", "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, "email", "param name");
    assert_eq!(
        analyzed.params[1].neutral_type, "string",
        "param neutral_type for email"
    );
    assert!(!analyzed.params[1].nullable, "param nullable for email");
    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, "created_at", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "datetime_tz",
        "column neutral_type for created_at"
    );
    assert!(
        !analyzed.columns[1].nullable,
        "column nullable for created_at"
    );

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

#[test]
fn test_insert_returning_now() {
    // From: testing_data/dml/returning_expressions/01_returning_now.json
    // "INSERT with RETURNING NOW() as a computed server timestamp"
    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 CreateEventWithServerTime\n-- @returns :one\nINSERT INTO events (name) VALUES ($1) RETURNING id, NOW() AS server_time";

    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, "CreateEventWithServerTime", "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, "name", "param name");
    assert_eq!(
        analyzed.params[0].neutral_type, "string",
        "param neutral_type for name"
    );
    assert!(!analyzed.params[0].nullable, "param nullable for name");
    assert_eq!(analyzed.params[0].position, 1, "param position for name");
    assert_eq!(analyzed.columns.len(), 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, "server_time", "column name");
    assert_eq!(
        analyzed.columns[1].neutral_type, "datetime_tz",
        "column neutral_type for server_time"
    );
    assert!(
        !analyzed.columns[1].nullable,
        "column nullable for server_time"
    );

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

#[test]
fn test_update_returning_computed() {
    // From: testing_data/dml/returning_expressions/02_returning_computed.json
    // "UPDATE with RETURNING a computed expression (total * 2)"
    let schema_sql = &["CREATE TABLE orders (id SERIAL PRIMARY KEY, total INTEGER NOT NULL);"];

    let query_sql = "-- @name UpdateOrderReturnDoubled\n-- @returns :one\nUPDATE orders SET total = $1 WHERE id = $2 RETURNING id, total * 2 AS doubled";

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

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

#[test]
fn test_update_basic() {
    // From: testing_data/dml/update/01_update_basic.json
    // "Basic UPDATE with SET and WHERE parameters"
    let schema_sql =
        &["CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL);"];

    let query_sql =
        "-- @name UpdateUserName\n-- @returns :exec\nUPDATE users SET name = $1 WHERE id = $2;";

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

    assert_eq!(analyzed.name, "UpdateUserName", "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, "id", "param name");
    assert_eq!(
        analyzed.params[1].neutral_type, "int32",
        "param neutral_type for id"
    );
    assert!(!analyzed.params[1].nullable, "param nullable for id");

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

#[test]
fn test_update_returning() {
    // From: testing_data/dml/update/02_update_returning.json
    // "UPDATE with RETURNING * to get the updated row"
    let schema_sql = &[
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW());",
    ];

    let query_sql = "-- @name UpdateUserReturning\n-- @returns :one\nUPDATE users SET name = $1, email = $2 WHERE id = $3 RETURNING *;";

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

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