drizzle 0.1.14

#![cfg(any(feature = "rusqlite", feature = "turso", feature = "libsql"))]

#[cfg(feature = "uuid")]
use crate::common::schema::sqlite::Role;
#[cfg(feature = "uuid")]
use crate::common::schema::sqlite::{ComplexSchema, InsertComplex};
use crate::common::schema::sqlite::{InsertSimple, SelectSimple, Simple, SimpleSchema};
use drizzle::core::expr::*;
use drizzle::sqlite::prelude::*;

#[derive(Debug, SQLiteFromRow)]
struct CountResult {
    count: i64,
}

#[derive(Debug, SQLiteFromRow)]
struct SumResult {
    sum: Option<i32>,
}

#[derive(Debug, SQLiteFromRow)]
struct MinResult {
    min: Option<i32>,
}

#[derive(Debug, SQLiteFromRow)]
struct MaxResult {
    max: Option<i32>,
}

#[derive(Debug, SQLiteFromRow)]
struct AvgResult {
    avg: Option<f64>,
}

#[cfg(feature = "uuid")]
#[derive(Debug, SQLiteFromRow)]
struct SumRealResult {
    sum: Option<f64>,
}

#[cfg(feature = "uuid")]
#[derive(Debug, SQLiteFromRow)]
struct MinRealResult {
    min: Option<f64>,
}

#[cfg(feature = "uuid")]
#[derive(Debug, SQLiteFromRow)]
struct MaxRealResult {
    max: Option<f64>,
}

#[derive(Debug, SQLiteFromRow)]
struct DistinctResult {
    name: String,
}

#[derive(Debug, SQLiteFromRow)]
struct CoalesceStringResult {
    coalesce: String,
}

#[derive(Debug, SQLiteFromRow)]
struct CoalesceManyStringResult {
    value: String,
}

#[cfg(feature = "uuid")]
#[derive(Debug, SQLiteFromRow)]
struct CoalesceIntResult {
    coalesce: i32,
}

#[derive(Debug, SQLiteFromRow)]
struct AliasResult {
    item_name: String,
}

#[derive(Debug, SQLiteFromRow)]
struct CountAliasResult {
    total_count: i64,
}

#[derive(Debug, SQLiteFromRow)]
struct SumAliasResult {
    id_sum: Option<i32>,
}

#[cfg(feature = "uuid")]
#[derive(Debug, SQLiteFromRow)]
struct ComplexAggregateResult {
    count: i64,
    avg: Option<f64>,
    max_age: i32,
}

#[cfg(feature = "uuid")]
#[derive(Debug, SQLiteFromRow)]
struct CoalesceAvgResult {
    coalesce: f64,
}

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

    let stmt = db
        .select(alias(
            cast(simple.id, drizzle::sqlite::types::Integer),
            "id_int",
        ))
        .from(simple);

    assert_eq!(
        stmt.to_sql().sql(),
        r#"SELECT CAST ("simple"."id" AS INTEGER) AS "id_int" FROM "simple""#
    );
}

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

    let test_data = vec![
        InsertSimple::new("Item A").with_id(10),
        InsertSimple::new("Item B").with_id(20),
        InsertSimple::new("Item C").with_id(30),
        InsertSimple::new("Item D").with_id(40),
    ];

    db.insert(simple).values(test_data).execute();

    // Test count all rows
    let stmt = db.select(alias(count(()), "count")).from(simple);
    assert_eq!(
        stmt.to_sql().sql(),
        r#"SELECT COUNT(*) AS "count" FROM "simple""#
    );
    let result: Vec<CountResult> = stmt.all();
    assert_eq!(result[0].count, 4);

    // Test count non-null values
    let result: Vec<CountResult> = db
        .select(alias(count(simple.id), "count"))
        .from(simple)
        .all();
    assert_eq!(result[0].count, 4);

    // Test sum function
    let result: Vec<SumResult> = db.select(alias(sum(simple.id), "sum")).from(simple).all();
    assert_eq!(result[0].sum, Some(100));

    // Test min function
    let result: Vec<MinResult> = db.select(alias(min(simple.id), "min")).from(simple).all();
    assert_eq!(result[0].min, Some(10));

    // Test max function
    let result: Vec<MaxResult> = db.select(alias(max(simple.id), "max")).from(simple).all();
    assert_eq!(result[0].max, Some(40));

    // Test avg function
    let result: Vec<AvgResult> = db.select(alias(avg(simple.id), "avg")).from(simple).all();
    assert_eq!(result[0].avg, Some(25.0));
}

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

    db.insert(simple)
        .values([
            InsertSimple::new("alice").with_id(1),
            InsertSimple::new("bob").with_id(2),
        ])
        .execute();

    let stmt = db
        .select((simple.id, simple.name))
        .from(simple)
        .r#where(ne(simple.id, 1));
    assert_eq!(
        stmt.to_sql().sql(),
        r#"SELECT "simple"."id", "simple"."name" FROM "simple" WHERE "simple"."id" <> ?"#
    );

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

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

    let test_data = vec![
        InsertComplex::new("User A", true, Role::User).with_score(85.5),
        InsertComplex::new("User B", false, Role::Admin).with_score(92.0),
        InsertComplex::new("User C", true, Role::User).with_score(78.3),
        InsertComplex::new("User D", false, Role::User).with_score(88.7),
    ];

    db.insert(complex).values(test_data).execute();

    // Test count with non-null values
    let result: Vec<CountResult> = db
        .select(alias(count(complex.score), "count"))
        .from(complex)
        .all();
    assert_eq!(result[0].count, 4);

    // Test sum with real numbers
    let result: Vec<SumRealResult> = db
        .select(alias(sum(complex.score), "sum"))
        .from(complex)
        .all();
    assert!((result[0].sum.expect("sum") - 344.5).abs() < 0.1);

    // Test avg with real numbers
    let result: Vec<AvgResult> = db
        .select(alias(avg(complex.score), "avg"))
        .from(complex)
        .all();
    assert!((result[0].avg.expect("avg") - 86.125).abs() < 0.1);

    // Test min with real numbers
    let result: Vec<MinRealResult> = db
        .select(alias(min(complex.score), "min"))
        .from(complex)
        .all();
    assert!((result[0].min.expect("min") - 78.3).abs() < 0.1);

    // Test max with real numbers
    let result: Vec<MaxRealResult> = db
        .select(alias(max(complex.score), "max"))
        .from(complex)
        .all();
    assert!((result[0].max.expect("max") - 92.0).abs() < 0.1);
}

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

    let test_data = vec![
        InsertSimple::new("Apple").with_id(1),
        InsertSimple::new("Apple").with_id(2),
        InsertSimple::new("Banana").with_id(3),
        InsertSimple::new("Apple").with_id(4),
        InsertSimple::new("Cherry").with_id(5),
    ];

    db.insert(simple).values(test_data).execute();

    // Test distinct function
    let result: Vec<DistinctResult> = db
        .select(alias(distinct(simple.name), "name"))
        .from(simple)
        .all();
    assert_eq!(result.len(), 3);
    let names: Vec<String> = result.iter().map(|r| r.name.clone()).collect();
    assert!(names.contains(&"Apple".to_string()));
    assert!(names.contains(&"Banana".to_string()));
    assert!(names.contains(&"Cherry".to_string()));

    // Test count with distinct
    let result: Vec<CountResult> = db
        .select(alias(count(distinct(simple.name)), "count"))
        .from(simple)
        .all();
    assert_eq!(result[0].count, 3);
}

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

    // Insert data with separate operations since each has different column patterns
    // Users A and C: have email set

    db.insert(complex)
        .values([
            InsertComplex::new("User A", true, Role::User)
                .with_email("user@example.com".to_string()),
            InsertComplex::new("User C", true, Role::User)
                .with_email("user3@example.com".to_string()),
        ])
        .execute();

    // User B: has no optional fields set

    db.insert(complex)
        .values([InsertComplex::new("User B", false, Role::Admin)])
        .execute();

    // Test coalesce with email field (some null, some not)
    let result: Vec<CoalesceStringResult> = db
        .select(alias(
            coalesce(complex.email, "no-email@example.com"),
            "coalesce",
        ))
        .from(complex)
        .all();
    assert_eq!(result.len(), 3);
    let emails: Vec<String> = result.iter().map(|r| r.coalesce.clone()).collect();
    assert!(emails.contains(&"user@example.com".to_string()));
    assert!(emails.contains(&"user3@example.com".to_string()));
    assert!(emails.contains(&"no-email@example.com".to_string()));

    // Test coalesce with age field
    let result: Vec<CoalesceIntResult> = db
        .select(alias(coalesce(complex.age, 0), "coalesce"))
        .from(complex)
        .all();
    assert_eq!(result.len(), 3);
    // All should be 0 since we didn't set any ages
    assert!(result.iter().all(|r| r.coalesce == 0));
}

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

    db.insert(complex)
        .values([InsertComplex::new("User A", true, Role::User)
            .with_email("user@example.com".to_string())])
        .execute();

    db.insert(complex)
        .values([InsertComplex::new("User B", false, Role::Admin)])
        .execute();

    let result: Vec<CoalesceManyStringResult> = db
        .select(alias(
            coalesce_many(complex.email, ["no-email@example.com"]),
            "value",
        ))
        .from(complex)
        .all();

    assert_eq!(result.len(), 2);
    let values: Vec<String> = result.iter().map(|r| r.value.clone()).collect();
    assert!(values.contains(&"user@example.com".to_string()));
    assert!(values.contains(&"no-email@example.com".to_string()));
}

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

    let test_data = vec![InsertSimple::new("Test Item").with_id(1)];

    db.insert(simple).values(test_data).execute();

    // Test alias with simple column
    let result: Vec<AliasResult> = db
        .select(alias(simple.name, "item_name"))
        .from(simple)
        .all();
    assert_eq!(result[0].item_name, "Test Item");

    // Test alias with aggregate function
    let result: Vec<CountAliasResult> = db
        .select(alias(count(simple.id), "total_count"))
        .from(simple)
        .all();
    assert_eq!(result[0].total_count, 1);

    // Test alias with expression
    let result: Vec<SumAliasResult> = db
        .select(alias(sum(simple.id), "id_sum"))
        .from(simple)
        .all();
    assert_eq!(result[0].id_sum, Some(1));
}

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

    // Insert data with separate operations since each has different column patterns
    // Users A and B: have both age and score set

    db.insert(complex)
        .values([
            InsertComplex::new("User A", true, Role::User)
                .with_age(25)
                .with_score(85.5),
            InsertComplex::new("User B", false, Role::Admin)
                .with_age(30)
                .with_score(92.0),
        ])
        .execute();

    // User C: has only score set

    db.insert(complex)
        .values([InsertComplex::new("User C", true, Role::User).with_score(78.3)])
        .execute();

    // Test multiple expressions in one query
    let result: Vec<ComplexAggregateResult> = db
        .select((
            alias(count(complex.id), "count"),
            alias(avg(complex.score), "avg"),
            alias(coalesce(max(complex.age), 0), "max_age"),
        ))
        .from(complex)
        .all();
    assert_eq!(result[0].count, 3); // count
    assert!((result[0].avg.expect("avg") - 85.266).abs() < 0.1); // avg score
    assert_eq!(result[0].max_age, 30); // max age (coalesced)

    // Test nested expressions
    let result: Vec<CoalesceAvgResult> = db
        .select(alias(coalesce(avg(complex.score), 0.0), "coalesce"))
        .from(complex)
        .r#where(is_not_null(complex.score))
        .all();
    assert!((result[0].coalesce - 85.266).abs() < 0.1);
}

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

    let test_data = [
        InsertComplex::new("Active User", true, Role::User).with_score(85.5),
        InsertComplex::new("Active Admin", true, Role::Admin).with_score(92.0),
        InsertComplex::new("Inactive User", false, Role::User).with_score(78.3),
        InsertComplex::new("Inactive Admin", false, Role::Admin).with_score(88.7),
    ];

    db.insert(complex).values(test_data).execute();

    // Test count with condition
    let result: Vec<CountResult> = db
        .select(alias(count(complex.id), "count"))
        .from(complex)
        .r#where(eq(complex.active, true))
        .all();
    assert_eq!(result[0].count, 2);

    // Test avg with condition
    let result: Vec<AvgResult> = db
        .select(alias(avg(complex.score), "avg"))
        .from(complex)
        .r#where(eq(complex.role, Role::Admin))
        .all();
    assert!((result[0].avg.expect("avg") - 90.35).abs() < 0.1);

    // Test max with condition
    let result: Vec<MaxRealResult> = db
        .select(alias(max(complex.score), "max"))
        .from(complex)
        .r#where(eq(complex.active, false))
        .all();
    assert!((result[0].max.expect("max") - 88.7).abs() < 0.1);
}

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

    // No data inserted, test aggregate functions on empty table

    // Count should return 0
    let result: Vec<CountResult> = db
        .select(alias(count(simple.id), "count"))
        .from(simple)
        .all();
    assert_eq!(result[0].count, 0);

    // Other aggregates on empty table should handle NULL appropriately
    // Note: Different databases handle this differently, but typically return NULL
    // which would be handled by the driver
}

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

    let test_data = [
        InsertSimple::new("").with_id(0), // Empty string and zero id
        InsertSimple::new("Test").with_id(1),
    ];

    db.insert(simple).values(test_data).execute();

    // Test count with all rows
    let result: Vec<CountResult> = db
        .select(alias(count(simple.id), "count"))
        .from(simple)
        .all();
    assert_eq!(result[0].count, 2);

    // Test distinct with empty string
    let result: Vec<DistinctResult> = db
        .select(alias(distinct(simple.name), "name"))
        .from(simple)
        .all();
    assert_eq!(result.len(), 2);
    let names: Vec<String> = result.iter().map(|r| r.name.clone()).collect();
    assert!(names.contains(&"".to_string()));
    assert!(names.contains(&"Test".to_string()));

    // Test sum with zero
    let result: Vec<SumResult> = db.select(alias(sum(simple.id), "sum")).from(simple).all();
    assert_eq!(result[0].sum, Some(1));

    // Test coalesce with empty string
    let result: Vec<CoalesceStringResult> = db
        .select(alias(coalesce(simple.name, "default"), "coalesce"))
        .from(simple)
        .r#where(eq(simple.name, ""))
        .all();
    assert_eq!(result[0].coalesce, ""); // Empty string is not NULL, so coalesce returns it
}

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

    let test_data = [
        InsertSimple::new("Item A").with_id(1),
        InsertSimple::new("Item B").with_id(2),
    ];

    db.insert(simple).values(test_data).execute();

    #[derive(SQLiteFromRow)]
    struct ResultRow {
        identifier: i32,
        item_name: String,
        total: i64,
    }
    // Test multiple aliases in same query (GROUP BY required when mixing scalar + aggregate)
    let result: Vec<ResultRow> = db
        .select((
            alias(simple.id, "identifier"),
            alias(simple.name, "item_name"),
            alias(count(simple.id), "total"),
        ))
        .from(simple)
        .group_by((simple.id, simple.name))
        .all();

    assert_eq!(result[0].identifier, 1);
    assert_eq!(result[0].item_name, "Item A");
    assert_eq!(result[0].total, 1);
}

// CTE tests have moved to use .into_cte() API - see test_cte_integration_* tests below

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

    struct FilteredUsersTag;
    impl drizzle::core::Tag for FilteredUsersTag {
        const NAME: &'static str = "filtered_users";
    }

    #[derive(SQLiteFromRow)]
    struct CteSimpleRow {
        id: i32,
        name: String,
    }

    // Insert test data
    let test_data = [
        InsertSimple::new("Alice").with_id(1),
        InsertSimple::new("Bob").with_id(2),
        InsertSimple::new("Charlie").with_id(3),
    ];
    db.insert(simple).values(test_data).execute();

    // Create a CTE with typed field access using .into_cte()
    let filtered_users = db
        .select((simple.id, simple.name))
        .from(simple)
        .r#where(gt(simple.id, 1))
        .into_cte::<FilteredUsersTag>();

    // Use the CTE with typed column access via Deref
    let result: Vec<CteSimpleRow> = db
        .with(&filtered_users)
        .select(CteSimpleRow::Select)
        .from(&filtered_users)
        .all();

    assert_eq!(result.len(), 2);
    assert_eq!(result[0].id, 2);
    assert_eq!(result[0].name, "Bob");
    assert_eq!(result[1].id, 3);
    assert_eq!(result[1].name, "Charlie");
}

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

    struct UserCountTag;
    impl drizzle::core::Tag for UserCountTag {
        const NAME: &'static str = "user_count";
    }

    // Insert test data
    let test_data = [
        InsertSimple::new("Test1").with_id(1),
        InsertSimple::new("Test2").with_id(2),
        InsertSimple::new("Test3").with_id(3),
    ];
    db.insert(simple).values(test_data).execute();

    // Create a CTE with count using .into_cte()
    // Note: For aggregations, select the computed column using sql!() or SELECT *
    let user_count = db
        .select(count(simple.id).alias("count"))
        .from(simple)
        .into_cte::<UserCountTag>();

    #[derive(SQLiteFromRow)]
    struct CountResult {
        count: i64,
    }

    // Use the CTE with inferred FromRow selector columns
    let result: Vec<CountResult> = db
        .with(&user_count)
        .select(CountResult::Select)
        .from(&user_count)
        .all();

    assert_eq!(result.len(), 1);
    assert_eq!(result[0].count, 3);
}

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

    struct FilteredUsersTag;
    impl drizzle::core::Tag for FilteredUsersTag {
        const NAME: &'static str = "filtered_users";
    }

    // Insert test data
    let test_data = [
        InsertSimple::new("Alice").with_id(1),
        InsertSimple::new("Bob").with_id(2),
        InsertSimple::new("Charlie").with_id(3),
        InsertSimple::new("David").with_id(4),
        InsertSimple::new("Eve").with_id(5),
    ];
    db.insert(simple).values(test_data).execute();

    // Level 1 CTE: Filter users with id > 2 using .into_cte() for typed field access
    let filtered_users = db
        .select((simple.id, simple.name))
        .from(simple)
        .r#where(gt(simple.id, 2))
        .into_cte::<FilteredUsersTag>();

    #[derive(SQLiteFromRow)]
    struct StatsResult {
        count: i64,
        category: Option<String>,
    }

    // Final query: Use the CTE with aggregation
    // Note: For computed columns, we use sql!() since they're not table fields
    let result: Vec<StatsResult> = db
        .with(&filtered_users)
        .select((
            count(filtered_users.id).alias("count"),
            min(filtered_users.name).alias("category"),
        ))
        .from(&filtered_users)
        .all();

    assert_eq!(result.len(), 1);
    assert_eq!(result[0].count, 3); // Should have 3 users with id > 2 (Charlie, David, Eve)
    assert_eq!(result[0].category, Some("Charlie".to_string()));
}

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

    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 test_data = [
        InsertSimple::new("Alpha").with_id(1),
        InsertSimple::new("Beta").with_id(2),
        InsertSimple::new("Gamma").with_id(3),
    ];
    db.insert(simple).values(test_data).execute();

    let simple_alias = Simple::alias::<SimpleTag>();
    let joined_simple = db
        .select((simple.id, simple.name))
        .from(simple)
        .join((simple_alias, eq(simple.id, simple_alias.id)))
        .into_cte::<JoinedSimpleTag>();

    let results: Vec<SelectSimple> = db
        .with(&joined_simple)
        .select((joined_simple.id, joined_simple.name))
        .from(&joined_simple)
        .order_by([asc(joined_simple.id)])
        .all();

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

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

    struct PagedSimpleTag;
    impl drizzle::core::Tag for PagedSimpleTag {
        const NAME: &'static str = "paged_simple";
    }

    let test_data = [
        InsertSimple::new("One").with_id(1),
        InsertSimple::new("Two").with_id(2),
        InsertSimple::new("Three").with_id(3),
        InsertSimple::new("Four").with_id(4),
    ];
    db.insert(simple).values(test_data).execute();

    let paged_simple = db
        .select((simple.id, simple.name))
        .from(simple)
        .order_by([asc(simple.id)])
        .limit(2)
        .offset(1)
        .into_cte::<PagedSimpleTag>();

    let results: Vec<SelectSimple> = db
        .with(&paged_simple)
        .select((paged_simple.id, paged_simple.name))
        .from(&paged_simple)
        .order_by([asc(paged_simple.id)])
        .all();

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

// =============================================================================
// New Expression DX Tests
// =============================================================================

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

    let test_data = vec![
        InsertSimple::new("Item A").with_id(10),
        InsertSimple::new("Item B").with_id(15),
        InsertSimple::new("Item C").with_id(23),
    ];

    db.insert(simple).values(test_data).execute();

    // Test modulo operator - find items where id % 5 == 0
    // Numeric columns now have arithmetic operators directly!
    let result: Vec<SelectSimple> = db
        .select((simple.id, simple.name))
        .from(simple)
        .r#where(eq(simple.id % 5, 0))
        .all();
    assert_eq!(result.len(), 2);
    assert_eq!(result[0].id, 10);
    assert_eq!(result[1].id, 15);

    // Test modulo with different values
    let result: Vec<SelectSimple> = db
        .select((simple.id, simple.name))
        .from(simple)
        .r#where(eq(simple.id % 10, 3))
        .all();
    assert_eq!(result.len(), 1);
    assert_eq!(result[0].id, 23);
}

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

    let test_data = vec![
        InsertSimple::new("Item A").with_id(5),
        InsertSimple::new("Item B").with_id(10),
        InsertSimple::new("Item C").with_id(15),
        InsertSimple::new("Item D").with_id(20),
        InsertSimple::new("Item E").with_id(25),
    ];

    db.insert(simple).values(test_data).execute();

    // Test between method - find items with id between 10 and 20 (inclusive)
    let result: Vec<SelectSimple> = db
        .select((simple.id, simple.name))
        .from(simple)
        .r#where(simple.id.between(10, 20))
        .all();
    assert_eq!(result.len(), 3);
    assert_eq!(result[0].id, 10);
    assert_eq!(result[1].id, 15);
    assert_eq!(result[2].id, 20);

    // Test not_between method
    let result: Vec<SelectSimple> = db
        .select((simple.id, simple.name))
        .from(simple)
        .r#where(simple.id.not_between(10, 20))
        .all();
    assert_eq!(result.len(), 2);
    assert_eq!(result[0].id, 5);
    assert_eq!(result[1].id, 25);
}

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

    let test_data = vec![
        InsertSimple::new("Alice").with_id(1),
        InsertSimple::new("Bob").with_id(2),
        InsertSimple::new("Charlie").with_id(3),
        InsertSimple::new("David").with_id(4),
        InsertSimple::new("Eve").with_id(5),
    ];

    db.insert(simple).values(test_data).execute();

    // Test in_array method with integers
    let result: Vec<SelectSimple> = db
        .select((simple.id, simple.name))
        .from(simple)
        .r#where(simple.id.in_array([1, 3, 5]))
        .all();
    assert_eq!(result.len(), 3);
    assert_eq!(result[0].name, "Alice");
    assert_eq!(result[1].name, "Charlie");
    assert_eq!(result[2].name, "Eve");

    // Test not_in_array method
    let result: Vec<SelectSimple> = db
        .select((simple.id, simple.name))
        .from(simple)
        .r#where(simple.id.not_in_array([1, 3, 5]))
        .all();
    assert_eq!(result.len(), 2);
    assert_eq!(result[0].name, "Bob");
    assert_eq!(result[1].name, "David");

    // Test in_array with strings
    let result: Vec<SelectSimple> = db
        .select((simple.id, simple.name))
        .from(simple)
        .r#where(simple.name.in_array(["Alice", "Eve"]))
        .all();
    assert_eq!(result.len(), 2);
    assert_eq!(result[0].name, "Alice");
    assert_eq!(result[1].name, "Eve");
}

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

    let test_data = vec![
        InsertSimple::new("Item A").with_id(10),
        InsertSimple::new("Item B").with_id(20),
        InsertSimple::new("Item C").with_id(30),
    ];

    db.insert(simple).values(test_data).execute();

    #[derive(Debug, SQLiteFromRow)]
    struct ComputedResult {
        computed: i32,
    }

    // Test direct column arithmetic: simple.id * 2
    let result: Vec<ComputedResult> = db
        .select(alias(simple.id * 2, "computed"))
        .from(simple)
        .all();
    assert_eq!(result.len(), 3);
    assert_eq!(result[0].computed, 20); // 10 * 2
    assert_eq!(result[1].computed, 40); // 20 * 2
    assert_eq!(result[2].computed, 60); // 30 * 2

    // Test column arithmetic in comparison
    let result: Vec<SelectSimple> = db
        .select((simple.id, simple.name))
        .from(simple)
        .r#where(lt(simple.id, 25))
        .all();
    assert_eq!(result.len(), 2);
    assert_eq!(result[0].id, 10);
    assert_eq!(result[1].id, 20);
}

// =============================================================================
// String Function Tests
// =============================================================================

#[derive(Debug, SQLiteFromRow)]
struct StringResult {
    result: String,
}

#[derive(Debug, SQLiteFromRow)]
struct LengthResult {
    length: i64,
}

#[derive(Debug, SQLiteFromRow)]
struct InstrResult {
    position: i64,
}

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

    let test_data = vec![
        InsertSimple::new("Hello World").with_id(1),
        InsertSimple::new("Test String").with_id(2),
    ];

    db.insert(simple).values(test_data).execute();

    // Test UPPER function
    let result: Vec<StringResult> = db
        .select(alias(upper(simple.name), "result"))
        .from(simple)
        .r#where(eq(simple.id, 1))
        .all();
    assert_eq!(result[0].result, "HELLO WORLD");

    // Test LOWER function
    let result: Vec<StringResult> = db
        .select(alias(lower(simple.name), "result"))
        .from(simple)
        .r#where(eq(simple.id, 1))
        .all();
    assert_eq!(result[0].result, "hello world");
}

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

    let test_data = vec![
        InsertSimple::new("  trimmed  ").with_id(1),
        InsertSimple::new("  left").with_id(2),
        InsertSimple::new("right  ").with_id(3),
    ];

    db.insert(simple).values(test_data).execute();

    // Test TRIM function
    let result: Vec<StringResult> = db
        .select(alias(trim(simple.name), "result"))
        .from(simple)
        .r#where(eq(simple.id, 1))
        .all();
    assert_eq!(result[0].result, "trimmed");

    // Test LTRIM function
    let result: Vec<StringResult> = db
        .select(alias(ltrim(simple.name), "result"))
        .from(simple)
        .r#where(eq(simple.id, 2))
        .all();
    assert_eq!(result[0].result, "left");

    // Test RTRIM function
    let result: Vec<StringResult> = db
        .select(alias(rtrim(simple.name), "result"))
        .from(simple)
        .r#where(eq(simple.id, 3))
        .all();
    assert_eq!(result[0].result, "right");
}

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

    let test_data = vec![
        InsertSimple::new("hello").with_id(1),
        InsertSimple::new("").with_id(2),
        InsertSimple::new("test string").with_id(3),
    ];

    db.insert(simple).values(test_data).execute();

    // Test LENGTH function
    let result: Vec<LengthResult> = db
        .select(alias(length(simple.name), "length"))
        .from(simple)
        .r#where(eq(simple.id, 1))
        .all();
    assert_eq!(result[0].length, 5);

    // Test LENGTH with empty string
    let result: Vec<LengthResult> = db
        .select(alias(length(simple.name), "length"))
        .from(simple)
        .r#where(eq(simple.id, 2))
        .all();
    assert_eq!(result[0].length, 0);
}

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

    let test_data = vec![InsertSimple::new("Hello World").with_id(1)];

    db.insert(simple).values(test_data).execute();

    // Test SUBSTR function - extract "Hello"
    let result: Vec<StringResult> = db
        .select(alias(substr(simple.name, 1, 5), "result"))
        .from(simple)
        .all();
    assert_eq!(result[0].result, "Hello");

    // Test SUBSTR function - extract "World"
    let result: Vec<StringResult> = db
        .select(alias(substr(simple.name, 7, 5), "result"))
        .from(simple)
        .all();
    assert_eq!(result[0].result, "World");
}

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

    let test_data = vec![InsertSimple::new("Hello World").with_id(1)];

    db.insert(simple).values(test_data).execute();

    // Test REPLACE function
    let result: Vec<StringResult> = db
        .select(alias(replace(simple.name, "World", "Rust"), "result"))
        .from(simple)
        .all();
    assert_eq!(result[0].result, "Hello Rust");

    // Test REPLACE with non-existent pattern (should return original)
    let result: Vec<StringResult> = db
        .select(alias(replace(simple.name, "xyz", "abc"), "result"))
        .from(simple)
        .all();
    assert_eq!(result[0].result, "Hello World");
}

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

    let test_data = vec![InsertSimple::new("Hello World").with_id(1)];

    db.insert(simple).values(test_data).execute();

    // Test INSTR function - find position of "World"
    let result: Vec<InstrResult> = db
        .select(alias(instr(simple.name, "World"), "position"))
        .from(simple)
        .all();
    assert_eq!(result[0].position, 7);

    // Test INSTR with non-existent pattern (should return 0)
    let result: Vec<InstrResult> = db
        .select(alias(instr(simple.name, "xyz"), "position"))
        .from(simple)
        .all();
    assert_eq!(result[0].position, 0);
}

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

    let test_data = vec![
        InsertSimple::new("Hello").with_id(1),
        InsertSimple::new("World").with_id(2),
    ];

    db.insert(simple).values(test_data).execute();

    // Test concat function with literal
    let result: Vec<StringResult> = db
        .select(alias(concat(simple.name, "!"), "result"))
        .from(simple)
        .r#where(eq(simple.id, 1))
        .all();
    assert_eq!(result[0].result, "Hello!");

    // Test chained concat
    let result: Vec<StringResult> = db
        .select(alias(concat(concat(simple.name, " "), "there"), "result"))
        .from(simple)
        .r#where(eq(simple.id, 1))
        .all();
    assert_eq!(result[0].result, "Hello there");
}

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

    let test_data = vec![InsertSimple::new("  Hello World  ").with_id(1)];

    db.insert(simple).values(test_data).execute();

    // Test combined: UPPER(TRIM(name))
    let result: Vec<StringResult> = db
        .select(alias(upper(trim(simple.name)), "result"))
        .from(simple)
        .all();
    assert_eq!(result[0].result, "HELLO WORLD");

    // Test combined: LOWER(TRIM(name))
    let result: Vec<StringResult> = db
        .select(alias(lower(trim(simple.name)), "result"))
        .from(simple)
        .all();
    assert_eq!(result[0].result, "hello world");

    // Test combined: LENGTH(TRIM(name))
    let result: Vec<LengthResult> = db
        .select(alias(length(trim(simple.name)), "length"))
        .from(simple)
        .all();
    assert_eq!(result[0].length, 11); // "Hello World" without leading/trailing spaces
}

// =============================================================================
// Math Function Tests
// =============================================================================

#[derive(Debug, SQLiteFromRow)]
struct MathIntResult {
    result: i32,
}

#[derive(Debug, SQLiteFromRow)]
struct MathFloatResult {
    result: f64,
}

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

    let test_data = vec![
        InsertSimple::new("Negative").with_id(-10),
        InsertSimple::new("Zero").with_id(0),
        InsertSimple::new("Positive").with_id(10),
    ];

    db.insert(simple).values(test_data).execute();

    // Test ABS function
    let result: Vec<MathIntResult> = db
        .select(alias(abs(simple.id), "result"))
        .from(simple)
        .r#where(eq(simple.name, "Negative"))
        .all();
    assert_eq!(result[0].result, 10);

    // Test ABS with zero
    let result: Vec<MathIntResult> = db
        .select(alias(abs(simple.id), "result"))
        .from(simple)
        .r#where(eq(simple.name, "Zero"))
        .all();
    assert_eq!(result[0].result, 0);
}

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

    // Use a table with float values - we'll compute from integer for simplicity
    let test_data = vec![InsertSimple::new("Test").with_id(37)];

    db.insert(simple).values(test_data).execute();

    // Test ROUND function with computed expression (id / 10.0)
    // 37 / 10.0 = 3.7, ROUND(3.7) = 4.0
    let result: Vec<MathFloatResult> = db
        .select(alias(round(simple.id / 10), "result"))
        .from(simple)
        .all();
    // Integer division: 37 / 10 = 3, ROUND(3) = 3.0
    assert_eq!(result[0].result, 3.0);
}

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

    let test_data = vec![
        InsertSimple::new("Negative").with_id(-5),
        InsertSimple::new("Zero").with_id(0),
        InsertSimple::new("Positive").with_id(5),
    ];

    db.insert(simple).values(test_data).execute();

    // Test SIGN function with negative (SIGN returns a float type)
    let result: Vec<MathFloatResult> = db
        .select(alias(sign(simple.id), "result"))
        .from(simple)
        .r#where(eq(simple.name, "Negative"))
        .all();
    assert_eq!(result[0].result, -1.0);

    // Test SIGN with zero
    let result: Vec<MathFloatResult> = db
        .select(alias(sign(simple.id), "result"))
        .from(simple)
        .r#where(eq(simple.name, "Zero"))
        .all();
    assert_eq!(result[0].result, 0.0);

    // Test SIGN with positive
    let result: Vec<MathFloatResult> = db
        .select(alias(sign(simple.id), "result"))
        .from(simple)
        .r#where(eq(simple.name, "Positive"))
        .all();
    assert_eq!(result[0].result, 1.0);
}

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

    let test_data = vec![
        InsertSimple::new("Ten").with_id(10),
        InsertSimple::new("Seven").with_id(7),
        InsertSimple::new("Fifteen").with_id(15),
    ];

    db.insert(simple).values(test_data).execute();

    // Test MOD function (10 % 3 = 1)
    let result: Vec<MathIntResult> = db
        .select(alias(mod_(simple.id, 3), "result"))
        .from(simple)
        .r#where(eq(simple.name, "Ten"))
        .all();
    assert_eq!(result[0].result, 1);

    // Test MOD function (15 % 4 = 3)
    let result: Vec<MathIntResult> = db
        .select(alias(mod_(simple.id, 4), "result"))
        .from(simple)
        .r#where(eq(simple.name, "Fifteen"))
        .all();
    assert_eq!(result[0].result, 3);
}

// =============================================================================
// DateTime Function Tests
// =============================================================================

#[derive(Debug, SQLiteFromRow)]
struct DateResult {
    result: String,
}

#[derive(Debug, SQLiteFromRow)]
struct CurrentDateResult {
    today: String,
}

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

    let test_data = vec![InsertSimple::new("Test").with_id(1)];
    db.insert(simple).values(test_data).execute();

    // Test CURRENT_DATE - returns format YYYY-MM-DD
    let result: Vec<CurrentDateResult> = db
        .select(alias(
            cast(current_date(), drizzle::sqlite::types::Text),
            "today",
        ))
        .from(simple)
        .all();
    // Just verify it's in the expected format (YYYY-MM-DD)
    assert!(result[0].today.len() == 10);
    assert!(result[0].today.contains('-'));
}

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

    let test_data = vec![InsertSimple::new("Test").with_id(1)];
    db.insert(simple).values(test_data).execute();

    // Test STRFTIME to format current date
    let result: Vec<DateResult> = db
        .select(alias(strftime("%Y", current_date()), "result"))
        .from(simple)
        .all();
    // Should return 4-digit year
    assert!(result[0].result.len() == 4);
    assert!(result[0].result.starts_with("20")); // Years 2000-2099
}

// =============================================================================
// SQLTypeToRust Inference Tests
// =============================================================================
// These tests verify that expression functions (current_date, count, etc.)
// produce types that can be deserialized correctly via the String fallback
// when chrono/uuid/serde features are not enabled.

#[derive(Debug, SQLiteFromRow)]
struct InferredDateResult {
    today: String,
}

// Tests that current_date() infers String (or chrono::NaiveDate with chrono)
// and deserializes correctly from SQLite's TEXT representation.
#[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<InferredDateResult> = db
        .select(alias(
            cast(current_date(), drizzle::sqlite::types::Text),
            "today",
        ))
        .from(simple)
        .all();
    assert_eq!(result.len(), 1);
    // SQLite returns YYYY-MM-DD for CURRENT_DATE
    assert_eq!(result[0].today.len(), 10);
    assert!(result[0].today.contains('-'));
}

#[derive(Debug, SQLiteFromRow)]
struct InferredTimestampResult {
    now: String,
}

// Tests that current_timestamp() infers correctly and deserializes from SQLite.
#[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<InferredTimestampResult> = db
        .select(alias(
            cast(current_timestamp(), drizzle::sqlite::types::Text),
            "now",
        ))
        .from(simple)
        .all();
    assert_eq!(result.len(), 1);
    // SQLite returns YYYY-MM-DD HH:MM:SS for CURRENT_TIMESTAMP
    assert!(result[0].now.contains(' '));
    assert!(result[0].now.contains(':'));
}

// =============================================================================
// CASE / WHEN expressions
// =============================================================================

#[derive(Debug, SQLiteFromRow)]
struct CaseNonNullResult {
    label: String,
}

#[derive(Debug, SQLiteFromRow)]
struct CaseNullableResult {
    label: Option<String>,
}

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

    let test_data = vec![
        InsertSimple::new("alice").with_id(10),
        InsertSimple::new("bob").with_id(25),
        InsertSimple::new("charlie").with_id(70),
    ];

    db.insert(simple).values(test_data).execute();

    // CASE with ELSE where all branches are NonNull → result is NonNull
    let results: Vec<CaseNonNullResult> = db
        .select(alias(
            case()
                .when(gt(simple.id, 65), "Senior")
                .when(gt(simple.id, 18), "Adult")
                .r#else("Minor"),
            "label",
        ))
        .from(simple)
        .order_by(asc(simple.id))
        .all();

    assert_eq!(results.len(), 3);
    assert_eq!(results[0].label, "Minor"); // id=10
    assert_eq!(results[1].label, "Adult"); // id=25
    assert_eq!(results[2].label, "Senior"); // id=70
}

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

    let test_data = vec![
        InsertSimple::new("alice").with_id(10),
        InsertSimple::new("bob").with_id(25),
    ];

    db.insert(simple).values(test_data).execute();

    // Without ELSE, unmatched rows produce NULL → result is always Null
    let results: Vec<CaseNullableResult> = db
        .select(alias(case().when(gt(simple.id, 20), "Big").end(), "label"))
        .from(simple)
        .order_by(asc(simple.id))
        .all();

    assert_eq!(results.len(), 2);
    assert_eq!(results[0].label, None); // id=10, no match
    assert_eq!(results[1].label.as_deref(), Some("Big")); // id=25
}

// =============================================================================
// Window functions
// =============================================================================

#[derive(Debug, SQLiteFromRow)]
struct RowNumberResult {
    name: String,
    rn: i64,
}

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

    let test_data = vec![
        InsertSimple::new("alice").with_id(1),
        InsertSimple::new("bob").with_id(2),
        InsertSimple::new("charlie").with_id(3),
    ];

    db.insert(simple).values(test_data).execute();

    let results: Vec<RowNumberResult> = db
        .select((
            simple.name,
            alias(row_number().over(window().order_by(asc(simple.id))), "rn"),
        ))
        .from(simple)
        .all();

    assert_eq!(results.len(), 3);
    assert_eq!(results[0].name, "alice");
    assert_eq!(results[0].rn, 1);
    assert_eq!(results[1].name, "bob");
    assert_eq!(results[1].rn, 2);
    assert_eq!(results[2].name, "charlie");
    assert_eq!(results[2].rn, 3);
}

#[derive(Debug, SQLiteFromRow)]
struct RunningSumResult {
    name: String,
    running_total: Option<i32>,
}

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

    let test_data = vec![
        InsertSimple::new("alice").with_id(10),
        InsertSimple::new("bob").with_id(20),
        InsertSimple::new("charlie").with_id(30),
    ];

    db.insert(simple).values(test_data).execute();

    // Running sum of id ordered by id
    let results: Vec<RunningSumResult> = db
        .select((
            simple.name,
            alias(
                sum(simple.id).over(
                    window()
                        .order_by(asc(simple.id))
                        .rows_between(FrameBound::UnboundedPreceding, FrameBound::CurrentRow),
                ),
                "running_total",
            ),
        ))
        .from(simple)
        .all();

    assert_eq!(results.len(), 3);
    assert_eq!(results[0].name, "alice");
    assert_eq!(results[0].running_total, Some(10)); // 10
    assert_eq!(results[1].name, "bob");
    assert_eq!(results[1].running_total, Some(30)); // 10+20
    assert_eq!(results[2].name, "charlie");
    assert_eq!(results[2].running_total, Some(60)); // 10+20+30
}

#[derive(Debug, SQLiteFromRow)]
struct RankResult {
    name: String,
    rnk: i64,
}

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

    let test_data = vec![
        InsertSimple::new("alice").with_id(1),
        InsertSimple::new("bob").with_id(2),
        InsertSimple::new("charlie").with_id(3),
    ];

    db.insert(simple).values(test_data).execute();

    let results: Vec<RankResult> = db
        .select((
            simple.name,
            alias(dense_rank().over(window().order_by(asc(simple.id))), "rnk"),
        ))
        .from(simple)
        .order_by(asc(simple.id))
        .all();

    assert_eq!(results.len(), 3);
    assert_eq!(results[0].name, "alice");
    assert_eq!(results[0].rnk, 1);
    assert_eq!(results[1].name, "bob");
    assert_eq!(results[1].rnk, 2);
    assert_eq!(results[2].name, "charlie");
    assert_eq!(results[2].rnk, 3);
}

#[derive(Debug, SQLiteFromRow)]
struct PercentRankResult {
    name: String,
    pct: f64,
}

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

    let test_data = vec![
        InsertSimple::new("alice").with_id(1),
        InsertSimple::new("bob").with_id(2),
        InsertSimple::new("charlie").with_id(3),
    ];

    db.insert(simple).values(test_data).execute();

    let results: Vec<PercentRankResult> = db
        .select((
            simple.name,
            alias(
                percent_rank().over(window().order_by(asc(simple.id))),
                "pct",
            ),
        ))
        .from(simple)
        .all();

    assert_eq!(results.len(), 3);
    assert_eq!(results[0].name, "alice");
    assert!((results[0].pct - 0.0).abs() < f64::EPSILON); // first row → 0.0
    assert!((results[1].pct - 0.5).abs() < f64::EPSILON); // (2-1)/(3-1) = 0.5
    assert!((results[2].pct - 1.0).abs() < f64::EPSILON); // (3-1)/(3-1) = 1.0
}

#[derive(Debug, SQLiteFromRow)]
struct CumeDistResult {
    name: String,
    cd: f64,
}

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

    let test_data = vec![
        InsertSimple::new("alice").with_id(1),
        InsertSimple::new("bob").with_id(2),
        InsertSimple::new("charlie").with_id(3),
    ];

    db.insert(simple).values(test_data).execute();

    let results: Vec<CumeDistResult> = db
        .select((
            simple.name,
            alias(cume_dist().over(window().order_by(asc(simple.id))), "cd"),
        ))
        .from(simple)
        .all();

    assert_eq!(results.len(), 3);
    assert_eq!(results[0].name, "alice");
    // CUME_DIST = rows_with_value_leq / total_rows
    assert!((results[0].cd - 1.0 / 3.0).abs() < f64::EPSILON); // 1/3
    assert!((results[1].cd - 2.0 / 3.0).abs() < f64::EPSILON); // 2/3
    assert!((results[2].cd - 1.0).abs() < f64::EPSILON); // 3/3
}

// =============================================================================
// CONCAT_WS Test
// =============================================================================

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

    let test_data = vec![InsertSimple::new("World").with_id(1)];
    db.insert(simple).values(test_data).execute();

    // Test CONCAT_WS with literal separator and mixed values
    let result: Vec<StringResult> = db
        .select(alias(concat_ws(", ", ["Hello", "Beautiful"]), "result"))
        .from(simple)
        .all();
    assert_eq!(result[0].result, "Hello, Beautiful");
}

// =============================================================================
// IS DISTINCT FROM / IS NOT DISTINCT FROM Tests
// =============================================================================

#[derive(Debug, SQLiteFromRow)]
struct BoolIntResult {
    result: i64,
}

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

    let test_data = vec![
        InsertSimple::new("alice").with_id(10),
        InsertSimple::new("bob").with_id(20),
    ];
    db.insert(simple).values(test_data).execute();

    // 10 IS DISTINCT FROM 10 → false (0)
    let result: Vec<BoolIntResult> = db
        .select(alias(
            cast(
                is_distinct_from(simple.id, 10),
                drizzle::sqlite::types::Integer,
            ),
            "result",
        ))
        .from(simple)
        .r#where(eq(simple.id, 10))
        .all();
    assert_eq!(result[0].result, 0);

    // 10 IS DISTINCT FROM 20 → true (1)
    let result: Vec<BoolIntResult> = db
        .select(alias(
            cast(
                is_distinct_from(simple.id, 20),
                drizzle::sqlite::types::Integer,
            ),
            "result",
        ))
        .from(simple)
        .r#where(eq(simple.id, 10))
        .all();
    assert_eq!(result[0].result, 1);

    // 10 IS NOT DISTINCT FROM 10 → true (1)
    let result: Vec<BoolIntResult> = db
        .select(alias(
            cast(
                is_not_distinct_from(simple.id, 10),
                drizzle::sqlite::types::Integer,
            ),
            "result",
        ))
        .from(simple)
        .r#where(eq(simple.id, 10))
        .all();
    assert_eq!(result[0].result, 1);
}

// =============================================================================
// IS TRUE / IS FALSE Tests
// =============================================================================

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

    let test_data = vec![
        InsertSimple::new("truthy").with_id(1),
        InsertSimple::new("falsy").with_id(0),
    ];
    db.insert(simple).values(test_data).execute();

    // (1 > 0) IS TRUE → true (1)
    let result: Vec<BoolIntResult> = db
        .select(alias(
            cast(is_true(gt(simple.id, 0)), drizzle::sqlite::types::Integer),
            "result",
        ))
        .from(simple)
        .r#where(eq(simple.name, "truthy"))
        .all();
    assert_eq!(result[0].result, 1);

    // (0 > 0) IS TRUE → false (0)
    let result: Vec<BoolIntResult> = db
        .select(alias(
            cast(is_true(gt(simple.id, 0)), drizzle::sqlite::types::Integer),
            "result",
        ))
        .from(simple)
        .r#where(eq(simple.name, "falsy"))
        .all();
    assert_eq!(result[0].result, 0);

    // (0 > 0) IS FALSE → true (1)
    let result: Vec<BoolIntResult> = db
        .select(alias(
            cast(is_false(gt(simple.id, 0)), drizzle::sqlite::types::Integer),
            "result",
        ))
        .from(simple)
        .r#where(eq(simple.name, "falsy"))
        .all();
    assert_eq!(result[0].result, 1);
}

// =============================================================================
// TOTAL (SQLite-specific) Test
// =============================================================================

#[derive(Debug, SQLiteFromRow)]
struct TotalResult {
    total: f64,
}

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

    let test_data = vec![
        InsertSimple::new("a").with_id(10),
        InsertSimple::new("b").with_id(20),
        InsertSimple::new("c").with_id(30),
    ];
    db.insert(simple).values(test_data).execute();

    // TOTAL returns 0.0 for empty sets (unlike SUM which returns NULL)
    let result: Vec<TotalResult> = db
        .select(alias(total(simple.id), "total"))
        .from(simple)
        .r#where(eq(simple.id, 999))
        .all();
    assert_eq!(result[0].total, 0.0);

    // TOTAL with actual data
    let result: Vec<TotalResult> = db
        .select(alias(total(simple.id), "total"))
        .from(simple)
        .all();
    assert_eq!(result[0].total, 60.0);
}

// =============================================================================
// CHAR_LENGTH / OCTET_LENGTH Tests
// =============================================================================

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

    let test_data = vec![
        InsertSimple::new("hello").with_id(1),
        InsertSimple::new("").with_id(2),
    ];
    db.insert(simple).values(test_data).execute();

    let result: Vec<LengthResult> = db
        .select(alias(char_length(simple.name), "length"))
        .from(simple)
        .r#where(eq(simple.id, 1))
        .all();
    assert_eq!(result[0].length, 5);

    let result: Vec<LengthResult> = db
        .select(alias(char_length(simple.name), "length"))
        .from(simple)
        .r#where(eq(simple.id, 2))
        .all();
    assert_eq!(result[0].length, 0);
}

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

    let test_data = vec![InsertSimple::new("hello").with_id(1)];
    db.insert(simple).values(test_data).execute();

    let result: Vec<LengthResult> = db
        .select(alias(octet_length(simple.name), "length"))
        .from(simple)
        .all();
    // ASCII "hello" = 5 bytes
    assert_eq!(result[0].length, 5);
}

// =============================================================================
// PI / RANDOM / LOG2 Tests
// =============================================================================

// pi() is PostgreSQL-only — tested in PG tests

#[derive(Debug, SQLiteFromRow)]
struct RandomIntResult {
    result: i64,
}

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

    let test_data = vec![InsertSimple::new("seed").with_id(1)];
    db.insert(simple).values(test_data).execute();

    // SQLite RANDOM() returns an integer — just verify it doesn't error
    let result: Vec<RandomIntResult> = db.select(alias(random(), "result")).from(simple).all();
    assert_eq!(result.len(), 1);
    // We can't assert a specific value, but we can verify it's a number
    let _ = result[0].result;
}

// log2() requires SQLITE_ENABLE_MATH_FUNCTIONS — tested in PG tests

// =============================================================================
// TIMEDIFF Test (SQLite 3.43+)
// =============================================================================

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

    let test_data = vec![InsertSimple::new("seed").with_id(1)];
    db.insert(simple).values(test_data).execute();

    // TIMEDIFF('2024-01-02', '2024-01-01') = '+1 00:00:00.000'
    let result: Vec<StringResult> = db
        .select(alias(
            timediff(
                cast("2024-01-02", drizzle::sqlite::types::Text),
                cast("2024-01-01", drizzle::sqlite::types::Text),
            ),
            "result",
        ))
        .from(simple)
        .all();
    assert!(result[0].result.contains("1"));
}