drizzle 0.1.11

//! PostgreSQL SELECT query tests
//!
//! Tests for SELECT statement generation and execution with PostgreSQL-specific syntax.

#![cfg(any(feature = "postgres-sync", feature = "tokio-postgres"))]

use crate::common::schema::postgres::*;
use drizzle::core::expr::*;
use drizzle::postgres::prelude::*;

#[derive(Debug, PostgresFromRow)]
struct PgCountResult {
    count: i64,
}

#[derive(Debug, PostgresFromRow)]
struct PgSumResult {
    total_age: Option<i32>,
}

#[derive(Debug, PostgresFromRow)]
struct PgAvgResult {
    avg_age: Option<f64>,
}

#[derive(Debug, PostgresFromRow)]
struct PgMinMaxResult {
    min_age: Option<i32>,
    max_age: Option<i32>,
}

#[derive(Debug, PostgresFromRow)]
struct PgAliasResult {
    user_name: String,
}

#[derive(Debug, PostgresFromRow)]
struct PgCoalesceResult {
    email: String,
}

#[allow(dead_code)]
#[cfg(feature = "uuid")]
#[derive(Debug, PostgresFromRow)]
struct PgComplexResult {
    id: uuid::Uuid,
    name: String,
    email: Option<String>,
    age: Option<i32>,
}

#[drizzle::test]
fn simple_select_with_conditions(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    // Insert test data
    let test_data = vec![
        InsertSimple::new("alpha"),
        InsertSimple::new("beta"),
        InsertSimple::new("gamma"),
        InsertSimple::new("delta"),
    ];

    let stmt = db.insert(simple).values(test_data);
    stmt.execute();

    // Test WHERE condition
    let stmt = db
        .select((simple.id, simple.name))
        .from(simple)
        .r#where(eq(simple.name, "beta"));

    let where_results: Vec<SelectSimple> = stmt.all();

    assert_eq!(where_results.len(), 1);
    assert_eq!(where_results[0].name, "beta");

    // Test ORDER BY with LIMIT
    let stmt = db
        .select((simple.id, simple.name))
        .from(simple)
        .order_by([asc(simple.name)])
        .limit(2);

    let ordered_results: Vec<SelectSimple> = stmt.all();

    assert_eq!(ordered_results.len(), 2);
    assert_eq!(ordered_results[0].name, "alpha");
    assert_eq!(ordered_results[1].name, "beta");

    // Test LIMIT with OFFSET
    let stmt = db
        .select((simple.id, simple.name))
        .from(simple)
        .order_by([asc(simple.name)])
        .limit(2)
        .offset(2);

    let offset_results: Vec<SelectSimple> = stmt.all();

    assert_eq!(offset_results.len(), 2);
    assert_eq!(offset_results[0].name, "delta");
    assert_eq!(offset_results[1].name, "gamma");
}

#[drizzle::test]
fn select_limit_offset_with_placeholders(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    db.insert(simple)
        .values([
            InsertSimple::new("alpha").with_id(1),
            InsertSimple::new("beta").with_id(2),
            InsertSimple::new("gamma").with_id(3),
            InsertSimple::new("delta").with_id(4),
        ])
        .execute();

    let literal = db
        .select((simple.id, simple.name))
        .from(simple)
        .order_by([asc(simple.name)])
        .limit(2)
        .offset(1);
    assert_eq!(
        literal.to_sql().sql(),
        r#"SELECT "simple"."id", "simple"."name" FROM "simple" ORDER BY "simple"."name" ASC LIMIT 2 OFFSET 1"#
    );

    let untyped_limit = drizzle::core::Placeholder::named("limit");
    let untyped_offset = drizzle::core::Placeholder::named("offset");
    let untyped_stmt = db
        .select((simple.id, simple.name))
        .from(simple)
        .order_by([asc(simple.name)])
        .limit(untyped_limit)
        .offset(untyped_offset);
    assert_eq!(
        untyped_stmt.to_sql().sql(),
        r#"SELECT "simple"."id", "simple"."name" FROM "simple" ORDER BY "simple"."name" ASC LIMIT $1 OFFSET $2"#
    );

    let limit = simple.id.placeholder("limit");
    let offset = simple.id.placeholder("offset");
    let prepared = db
        .select((simple.id, simple.name))
        .from(simple)
        .order_by([asc(simple.name)])
        .limit(limit)
        .offset(offset)
        .prepare()
        .into_owned();

    let rows: Vec<SelectSimple> = prepared.all(drizzle_client!(), [limit.bind(2), offset.bind(1)]);
    assert_eq!(rows.len(), 2);
    assert_eq!(rows[0].name, "beta");
    assert_eq!(rows[1].name, "delta");
}

#[drizzle::test]
fn select_all_columns(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    db.insert(simple)
        .values(vec![InsertSimple::new("test")])
        .execute();

    let sql = db.select(()).from(simple).to_sql().sql();
    assert_eq!(
        sql,
        r#"SELECT "simple"."id", "simple"."name" FROM "simple""#
    );

    // Also verify via DB execution
    let results: Vec<SelectSimple> = db.select(()).from(simple).all();
    assert_eq!(results.len(), 1);
    assert_eq!(results[0].name, "test");
}

#[drizzle::test]
fn select_with_where(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    db.insert(simple)
        .values(vec![InsertSimple::new("test"), InsertSimple::new("other")])
        .execute();

    let stmt = db
        .select((simple.id, simple.name))
        .from(simple)
        .r#where(eq(simple.name, "test"));

    assert_eq!(
        stmt.to_sql().sql(),
        r#"SELECT "simple"."id", "simple"."name" FROM "simple" WHERE "simple"."name" = $1"#
    );

    let results: Vec<SelectSimple> = stmt.all();
    assert_eq!(results.len(), 1);
    assert_eq!(results[0].name, "test");
}

#[drizzle::test]
fn select_with_order_by(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    db.insert(simple)
        .values(vec![
            InsertSimple::new("zebra"),
            InsertSimple::new("alpha"),
            InsertSimple::new("beta"),
        ])
        .execute();

    let stmt = db
        .select((simple.id, simple.name))
        .from(simple)
        .order_by([asc(simple.name)])
        .limit(2);

    assert_eq!(
        stmt.to_sql().sql(),
        r#"SELECT "simple"."id", "simple"."name" FROM "simple" ORDER BY "simple"."name" ASC LIMIT 2"#
    );

    let results: Vec<SelectSimple> = stmt.all();
    assert_eq!(results.len(), 2);
    assert_eq!(results[0].name, "alpha");
    assert_eq!(results[1].name, "beta");
}

#[drizzle::test]
fn select_with_limit(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    db.insert(simple)
        .values(vec![
            InsertSimple::new("one"),
            InsertSimple::new("two"),
            InsertSimple::new("three"),
        ])
        .execute();

    let stmt = db.select((simple.id, simple.name)).from(simple).limit(2);

    assert_eq!(
        stmt.to_sql().sql(),
        r#"SELECT "simple"."id", "simple"."name" FROM "simple" LIMIT 2"#
    );

    let results: Vec<SelectSimple> = stmt.all();
    assert_eq!(results.len(), 2);
}

#[drizzle::test]
fn select_with_offset(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    db.insert(simple)
        .values([
            InsertSimple::new("one"),
            InsertSimple::new("two"),
            InsertSimple::new("three"),
            InsertSimple::new("four"),
        ])
        .execute();

    let stmt = db
        .select((simple.id, simple.name))
        .from(simple)
        .order_by([asc(simple.name)])
        .limit(2)
        .offset(1);

    assert_eq!(
        stmt.to_sql().sql(),
        r#"SELECT "simple"."id", "simple"."name" FROM "simple" ORDER BY "simple"."name" ASC LIMIT 2 OFFSET 1"#
    );

    let results: Vec<SelectSimple> = stmt.all();
    assert_eq!(results.len(), 2);
    // After ordering: four, one, three, two - offset 1 skips "four"
    assert_eq!(results[0].name, "one");
    assert_eq!(results[1].name, "three");
}

#[drizzle::test]
fn cte_after_join(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    let test_data = [
        InsertSimple::new("alpha"),
        InsertSimple::new("beta"),
        InsertSimple::new("gamma"),
    ];
    db.insert(simple).values(test_data).execute();

    let results: Vec<SelectSimple> = {
        struct SimpleTag;
        impl drizzle::core::Tag for SimpleTag {
            const NAME: &'static str = "simple_alias";
        }
        struct JoinedSimpleTag;
        impl drizzle::core::Tag for JoinedSimpleTag {
            const NAME: &'static str = "joined_simple";
        }

        let simple_alias = Simple::alias::<SimpleTag>();
        let builder = drizzle::postgres::builder::QueryBuilder::new::<SimpleSchema>();
        let join_cond = eq(simple.id, simple_alias.id);
        let joined_simple: drizzle_postgres::builder::CTEView<'static, _, _> = builder
            .select((simple.id, simple.name))
            .from(simple)
            .join((simple_alias, join_cond))
            .into_cte::<JoinedSimpleTag>();
        let joined_alias = joined_simple.table;

        db.with(&joined_simple)
            .select((joined_alias.id, joined_alias.name))
            .from(&joined_simple)
            .order_by([asc(joined_alias.id)])
            .all()
    };

    assert_eq!(results.len(), 3);
    assert_eq!(results[0].name, "alpha");
    assert_eq!(results[2].name, "gamma");
}

#[drizzle::test]
fn cte_after_order_limit_offset(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    let test_data = [
        InsertSimple::new("one"),
        InsertSimple::new("two"),
        InsertSimple::new("three"),
        InsertSimple::new("four"),
    ];
    db.insert(simple).values(test_data).execute();

    let results: Vec<SelectSimple> = {
        struct PagedSimpleTag;
        impl drizzle::core::Tag for PagedSimpleTag {
            const NAME: &'static str = "paged_simple";
        }

        let builder = drizzle::postgres::builder::QueryBuilder::new::<SimpleSchema>();
        let paged_simple: drizzle_postgres::builder::CTEView<'static, _, _> = builder
            .select((simple.id, simple.name))
            .from(simple)
            .order_by([asc(simple.id)])
            .limit(2)
            .offset(1)
            .into_cte::<PagedSimpleTag>();
        let paged_alias = paged_simple.table;

        db.with(&paged_simple)
            .select((paged_alias.id, paged_alias.name))
            .from(&paged_simple)
            .order_by([asc(paged_alias.id)])
            .all()
    };

    assert_eq!(results.len(), 2);
    assert_eq!(results[0].id, 2);
    assert_eq!(results[1].id, 3);
}

// Validate that the generated Select model can be used directly
#[drizzle::test]
fn select_with_generated_model(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    let stmt = db
        .insert(simple)
        .values(vec![InsertSimple::new("sel_a"), InsertSimple::new("sel_b")]);
    stmt.execute();

    let stmt = db.select(()).from(simple).order_by([asc(simple.id)]);

    let results: Vec<SelectSimple> = stmt.all();

    assert_eq!(results.len(), 2);
    assert_eq!(results[0].name, "sel_a");
    assert_eq!(results[1].name, "sel_b");
}

#[cfg(feature = "uuid")]
#[drizzle::test]
fn select_with_multiple_order_by(db: &mut TestDb<ComplexSchema>) {
    let ComplexSchema { complex, .. } = schema;

    db.insert(complex)
        .values(vec![
            InsertComplex::new("Alice", true, Role::User)
                .with_email("alice@example.com")
                .with_age(30),
            InsertComplex::new("Bob", true, Role::User)
                .with_email("bob@example.com")
                .with_age(25),
            InsertComplex::new("Charlie", true, Role::User)
                .with_email("charlie@example.com")
                .with_age(30),
        ])
        .execute();

    let stmt = db
        .select((complex.id, complex.name, complex.email, complex.age))
        .from(complex)
        .order_by([desc(complex.age), asc(complex.name)]);

    let results: Vec<PgComplexResult> = stmt.all();
    assert_eq!(results.len(), 3);
    // age DESC, name ASC: Alice(30), Charlie(30), Bob(25)
    assert_eq!(results[0].name, "Alice");
    assert_eq!(results[1].name, "Charlie");
    assert_eq!(results[2].name, "Bob");
}

#[drizzle::test]
fn select_with_in_array(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    let stmt = db.insert(simple).values(vec![
        InsertSimple::new("Alice"),
        InsertSimple::new("Bob"),
        InsertSimple::new("Charlie"),
        InsertSimple::new("David"),
    ]);
    stmt.execute();

    let stmt = db
        .select(())
        .from(simple)
        .r#where(in_array(simple.name, ["Alice", "Bob", "Charlie"]));

    let sql = stmt.to_sql().sql();

    assert!(sql.contains("IN"));
    // Should have PostgreSQL numbered placeholders
    assert!(sql.contains("$1"));

    let results: Vec<SelectSimple> = stmt.all();
    assert_eq!(results.len(), 3);
}

#[drizzle::test]
fn select_with_like_pattern(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    let stmt = db.insert(simple).values(vec![
        InsertSimple::new("test_one"),
        InsertSimple::new("test_two"),
        InsertSimple::new("other"),
    ]);
    stmt.execute();

    let stmt = db
        .select(())
        .from(simple)
        .r#where(like(simple.name, "%test%"));

    let sql = stmt.to_sql().sql();

    assert!(sql.contains("LIKE"));
    assert!(sql.contains("$1"));

    let results: Vec<SelectSimple> = stmt.all();
    assert_eq!(results.len(), 2);
}

#[cfg(feature = "uuid")]
#[drizzle::test]
fn select_with_null_check(db: &mut TestDb<ComplexSchema>) {
    let ComplexSchema { complex, .. } = schema;

    let data1 = InsertComplex::new("Alice", true, Role::User)
        .with_email("alice@example.com")
        .with_age(30);

    let stmt = db.insert(complex).values(vec![data1]);
    stmt.execute();

    let data2 = InsertComplex::new("Bob", true, Role::User).with_age(25);
    let stmt = db.insert(complex).values(vec![data2]);
    stmt.execute();

    let stmt = db.select(()).from(complex).r#where(is_null(complex.email));

    let sql = stmt.to_sql().sql();

    assert!(sql.contains("IS NULL"));

    let results: Vec<PgComplexResult> = stmt.all();
    assert_eq!(results.len(), 1);
    assert_eq!(results[0].name, "Bob");
}

#[cfg(feature = "uuid")]
#[drizzle::test]
fn select_with_between(db: &mut TestDb<ComplexSchema>) {
    let ComplexSchema { complex, .. } = schema;

    let stmt = db.insert(complex).values(vec![
        InsertComplex::new("Young", true, Role::User)
            .with_email("young@example.com")
            .with_age(15),
        InsertComplex::new("Adult", true, Role::User)
            .with_email("adult@example.com")
            .with_age(30),
        InsertComplex::new("Senior", true, Role::User)
            .with_email("senior@example.com")
            .with_age(70),
    ]);
    stmt.execute();

    let stmt = db
        .select(())
        .from(complex)
        .r#where(between(complex.age, 18, 65));

    let sql = stmt.to_sql().sql();

    assert!(sql.contains("BETWEEN"));
    assert!(sql.contains("$1"));
    assert!(sql.contains("$2"));

    let results: Vec<PgComplexResult> = stmt.all();
    assert_eq!(results.len(), 1);
    assert_eq!(results[0].name, "Adult");
}

#[cfg(feature = "uuid")]
#[drizzle::test]
fn select_with_enum_condition(db: &mut TestDb<ComplexSchema>) {
    let ComplexSchema { complex, .. } = schema;

    let data1 = InsertComplex::new("Alice", true, Role::Admin)
        .with_email("alice@example.com")
        .with_age(30);
    let data2 = InsertComplex::new("Bob", true, Role::User)
        .with_email("bob@example.com")
        .with_age(25);

    let stmt = db.insert(complex).values(vec![data1, data2]);
    stmt.execute();

    let stmt = db
        .select(())
        .from(complex)
        .r#where(eq(complex.role, Role::Admin));

    let sql = stmt.to_sql().sql();

    assert!(sql.contains(r#""complex"."role""#));
    assert!(sql.contains("$1"));

    let results: Vec<PgComplexResult> = stmt.all();
    assert_eq!(results.len(), 1);
    assert_eq!(results[0].name, "Alice");
}

// Verify full SelectComplex round-trip including native PG enum columns.
// Previous tests use PgComplexResult (partial) which skips the role column.
// This test ensures row.get::<_, Role>(idx) works for native PG enum types.
#[cfg(feature = "uuid")]
#[drizzle::test]
fn select_full_model_with_enum(db: &mut TestDb<ComplexSchema>) {
    let ComplexSchema { complex, .. } = schema;

    let data1 = InsertComplex::new("Alice", true, Role::Admin);
    let data2 = InsertComplex::new("Bob", true, Role::User);
    let data3 = InsertComplex::new("Charlie", false, Role::Moderator);

    db.insert(complex)
        .values(vec![data1, data2, data3])
        .execute();

    // Full select returning SelectComplex (includes role enum column)
    let results: Vec<SelectComplex> = db.select(()).from(complex).all();
    assert_eq!(results.len(), 3);
    assert_eq!(results[0].role, Role::Admin);
    assert_eq!(results[1].role, Role::User);
    assert_eq!(results[2].role, Role::Moderator);
}

// Verify full SelectTask round-trip with TWO native PG enum columns (priority, status).
#[drizzle::test]
fn select_full_model_with_multiple_enums(db: &mut TestDb<TaskSchema>) {
    let TaskSchema { task, .. } = schema;

    let t1 = InsertTask::new("Urgent", Priority::High, PostStatus::Published);
    let t2 = InsertTask::new("Normal", Priority::Medium, PostStatus::Draft);
    let t3 = InsertTask::new("Low-pri", Priority::Low, PostStatus::Archived);

    db.insert(task).values(vec![t1, t2, t3]).execute();

    let results: Vec<SelectTask> = db.select(()).from(task).all();
    assert_eq!(results.len(), 3);
    assert_eq!(results[0].priority, Priority::High);
    assert_eq!(results[0].status, PostStatus::Published);
    assert_eq!(results[1].priority, Priority::Medium);
    assert_eq!(results[1].status, PostStatus::Draft);
    assert_eq!(results[2].priority, Priority::Low);
    assert_eq!(results[2].status, PostStatus::Archived);
}

#[cfg(feature = "uuid")]
#[drizzle::test]
fn select_complex_where(db: &mut TestDb<ComplexSchema>) {
    let ComplexSchema { complex, .. } = schema;

    let data1 = InsertComplex::new("Alice", true, Role::Admin)
        .with_email("alice@example.com")
        .with_age(30);

    let data2 = InsertComplex::new("Bob", true, Role::User)
        .with_email("bob@example.com")
        .with_age(25);

    let data3 = InsertComplex::new("Charlie", false, Role::User)
        .with_email("charlie@example.com")
        .with_age(20);

    let stmt = db.insert(complex).values(vec![data1, data2, data3]);
    stmt.execute();

    let stmt = db.select(()).from(complex).r#where(and(
        eq(complex.active, true),
        or(eq(complex.role, Role::Admin), gt(complex.age, 21)),
    ));

    let sql = stmt.to_sql().sql();

    assert!(sql.contains("AND"));
    assert!(sql.contains("OR"));

    let results: Vec<PgComplexResult> = stmt.all();
    // Should match Alice (active=true, role=Admin) and Bob (active=true, age>21)
    assert_eq!(results.len(), 2);
}

#[drizzle::test]
fn select_with_aggregate_count(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    db.insert(simple)
        .values(vec![
            InsertSimple::new("one"),
            InsertSimple::new("two"),
            InsertSimple::new("three"),
        ])
        .execute();

    let stmt = db.select(alias(count(()), "count")).from(simple);
    assert_eq!(
        stmt.to_sql().sql(),
        r#"SELECT COUNT(*) AS "count" FROM "simple""#
    );

    let result: PgCountResult = stmt.get();
    assert_eq!(result.count, 3);
}

#[cfg(feature = "uuid")]
#[drizzle::test]
fn select_with_aggregate_sum(db: &mut TestDb<ComplexSchema>) {
    let ComplexSchema { complex, .. } = schema;

    db.insert(complex)
        .values(vec![
            InsertComplex::new("Alice", true, Role::User)
                .with_email("alice@example.com")
                .with_age(30),
            InsertComplex::new("Bob", true, Role::User)
                .with_email("bob@example.com")
                .with_age(25),
        ])
        .execute();

    let stmt = db
        .select(alias(
            cast(sum(complex.age), drizzle::postgres::types::Int4),
            "total_age",
        ))
        .from(complex);

    let result: PgSumResult = stmt.get();
    assert_eq!(result.total_age, Some(55));
}

#[cfg(feature = "uuid")]
#[drizzle::test]
fn select_with_aggregate_avg(db: &mut TestDb<ComplexSchema>) {
    let ComplexSchema { complex, .. } = schema;

    db.insert(complex)
        .values(vec![
            InsertComplex::new("Alice", true, Role::User)
                .with_email("alice@example.com")
                .with_age(30),
            InsertComplex::new("Bob", true, Role::User)
                .with_email("bob@example.com")
                .with_age(20),
        ])
        .execute();

    let stmt = db
        .select(alias(
            cast(avg(complex.age), drizzle::postgres::types::Float8),
            "avg_age",
        ))
        .from(complex);

    let result: PgAvgResult = stmt.get();
    assert!(result.avg_age.is_some_and(|avg| (avg - 25.0).abs() < 0.01));
}

#[cfg(feature = "uuid")]
#[drizzle::test]
fn select_with_aggregate_min_max(db: &mut TestDb<ComplexSchema>) {
    let ComplexSchema { complex, .. } = schema;

    db.insert(complex)
        .values(vec![
            InsertComplex::new("Alice", true, Role::User)
                .with_email("alice@example.com")
                .with_age(30),
            InsertComplex::new("Bob", true, Role::User)
                .with_email("bob@example.com")
                .with_age(25),
            InsertComplex::new("Charlie", true, Role::User)
                .with_email("charlie@example.com")
                .with_age(35),
        ])
        .execute();

    let stmt = db
        .select((
            alias(min(complex.age), "min_age"),
            alias(max(complex.age), "max_age"),
        ))
        .from(complex);

    let result: PgMinMaxResult = stmt.get();
    assert_eq!(result.min_age, Some(25));
    assert_eq!(result.max_age, Some(35));
}

#[cfg(feature = "uuid")]
#[drizzle::test]
fn select_distinct(db: &mut TestDb<ComplexSchema>) {
    let ComplexSchema { complex, .. } = schema;

    db.insert(complex)
        .values(vec![
            InsertComplex::new("Alice", true, Role::User)
                .with_email("alice@example.com")
                .with_age(30),
            InsertComplex::new("Bob", true, Role::Admin)
                .with_email("bob@example.com")
                .with_age(25),
            InsertComplex::new("Charlie", true, Role::User)
                .with_email("charlie@example.com")
                .with_age(35),
        ])
        .execute();

    #[allow(dead_code)]
    #[derive(Debug, PostgresFromRow)]
    struct PgDistinctRoleResult {
        role: String,
    }
    let results: Vec<PgDistinctRoleResult> = db
        .select(alias(
            distinct(cast(complex.role, drizzle::postgres::types::Varchar)),
            "role",
        ))
        .from(complex)
        .all();
    assert_eq!(results.len(), 2);
}

#[drizzle::test]
fn select_with_alias(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    db.insert(simple)
        .values(vec![InsertSimple::new("test")])
        .execute();

    let stmt = db.select(alias(simple.name, "user_name")).from(simple);

    let result: PgAliasResult = stmt.get();
    assert_eq!(result.user_name, "test");
}

#[cfg(feature = "uuid")]
#[drizzle::test]
fn select_with_coalesce(db: &mut TestDb<ComplexSchema>) {
    let ComplexSchema { complex, .. } = schema;

    db.insert(complex)
        .values(vec![
            InsertComplex::new("Alice", true, Role::User).with_age(30),
        ])
        .execute();

    let stmt = db
        .select(alias(
            coalesce(complex.email, "unknown@example.com"),
            "email",
        ))
        .from(complex);

    let result: PgCoalesceResult = stmt.get();
    assert_eq!(result.email, "unknown@example.com");
}

// =============================================================================
// SQLTypeToRust Inference Tests
// =============================================================================
// These tests verify that expression functions (current_date, current_timestamp)
// can be selected and deserialized on PostgreSQL. PostgreSQL returns native
// binary values for temporal types, so the chrono feature is required.

#[cfg(feature = "chrono")]
#[derive(Debug, PostgresFromRow)]
struct PgInferredDateResult {
    today: chrono::NaiveDate,
}

// Tests that current_date() deserializes as NaiveDate on PostgreSQL (requires chrono).
#[cfg(feature = "chrono")]
#[drizzle::test]
fn test_inferred_current_date(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;
    db.insert(simple)
        .values([InsertSimple::new("seed")])
        .execute();

    let result: Vec<PgInferredDateResult> =
        db.select(alias(current_date(), "today")).from(simple).all();
    assert_eq!(result.len(), 1);
    let today = result[0].today;
    assert!(chrono::Datelike::year(&today) >= 2024);
}

/// Tests that current_timestamp() deserializes as DateTime<Utc> on PostgreSQL (requires chrono).
/// PostgreSQL's CURRENT_TIMESTAMP returns `timestamptz`, which maps to TimestampTz → DateTime<Utc>.
#[cfg(feature = "chrono")]
#[derive(Debug, PostgresFromRow)]
struct PgInferredTimestampResult {
    now: chrono::DateTime<chrono::Utc>,
}

#[cfg(feature = "chrono")]
#[drizzle::test]
fn test_inferred_current_timestamp(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;
    db.insert(simple)
        .values([InsertSimple::new("seed")])
        .execute();

    let result: Vec<PgInferredTimestampResult> = db
        .select(alias(current_timestamp(), "now"))
        .from(simple)
        .all();
    assert_eq!(result.len(), 1);
    assert!(result[0].now.timestamp() > 0);
}