vantage-sql 0.10.0

//! Test 2p: SQL primitives and SqliteOperation — verifies that typed columns,
//! fx! macro, ternary, Case, Concat, and chaining work as documented.

use vantage_expressions::Expressive;
use vantage_sql::condition::SqliteCondition;
use vantage_sql::fx;
use vantage_sql::primitives::*;
use vantage_sql::sqlite::operation::SqliteOperation;
use vantage_sql::sqlite::sqlite_ident as ident;
use vantage_sql::sqlite::types::AnySqliteType;
use vantage_sql::sqlite_expr;
use vantage_table::column::core::Column;

// ── SqliteOperation on typed columns ────────────────────────────────

#[test]
fn test_typed_column_eq() {
    let is_deleted = Column::<bool>::new("is_deleted");
    let cond: SqliteCondition = is_deleted.eq(false);
    assert_eq!(cond.into_expr().preview(), "is_deleted = 0");
}

#[test]
fn test_typed_column_gt() {
    let price = Column::<i64>::new("price");
    let cond: SqliteCondition = price.gt(150i64);
    assert_eq!(cond.into_expr().preview(), "price > 150");
}

#[test]
fn test_typed_column_lte() {
    let calories = Column::<i64>::new("calories");
    let cond: SqliteCondition = calories.lte(250i64);
    assert_eq!(cond.into_expr().preview(), "calories <= 250");
}

#[test]
fn test_typed_column_ne() {
    let status = Column::<AnySqliteType>::new("status");
    let cond: SqliteCondition = status.ne(AnySqliteType::from("cancelled".to_string()));
    assert_eq!(cond.into_expr().preview(), "status != 'cancelled'");
}

#[test]
fn test_typed_column_lt_and_gte() {
    let price = Column::<i64>::new("price");
    let lt: SqliteCondition = price.lt(150i64);
    assert_eq!(lt.into_expr().preview(), "price < 150");
    let gte: SqliteCondition = price.gte(150i64);
    assert_eq!(gte.into_expr().preview(), "price >= 150");
}

#[test]
fn test_in_and_not_in_take_a_values_expression() {
    let vip = || sqlite_expr!("SELECT id FROM vip");
    assert_eq!(
        ident("id").in_(vip()).into_expr().preview(),
        "\"id\" IN (SELECT id FROM vip)"
    );
    assert_eq!(
        ident("id").not_in(vip()).into_expr().preview(),
        "\"id\" NOT IN (SELECT id FROM vip)"
    );
}

#[test]
fn test_in_list_and_not_in_list_take_scalars() {
    assert_eq!(
        ident("status")
            .in_list(&["new", "open"])
            .into_expr()
            .preview(),
        "\"status\" IN ('new', 'open')"
    );
    assert_eq!(
        ident("status")
            .not_in_list(&["new", "open"])
            .into_expr()
            .preview(),
        "\"status\" NOT IN ('new', 'open')"
    );
}

#[test]
fn test_cast_wraps_the_expression() {
    assert_eq!(
        ident("price").cast("TEXT").into_expr().preview(),
        "CAST(\"price\" AS TEXT)"
    );
}

#[test]
fn test_and_groups_two_conditions() {
    let price = Column::<i64>::new("price");
    assert_eq!(
        price.gt(10i64).and_(price.lt(99i64)).preview(),
        "(price > 10 AND price < 99)"
    );
}

#[test]
fn test_typed_column_is_null() {
    let note = Column::<i64>::new("note");
    let cond: SqliteCondition = note.is_null();
    assert_eq!(cond.into_expr().preview(), "note IS NULL");
}

#[test]
fn test_typed_column_is_not_null() {
    let note = Column::<i64>::new("note");
    let cond: SqliteCondition = note.is_not_null();
    assert_eq!(cond.into_expr().preview(), "note IS NOT NULL");
}

// ── Chaining across type boundaries ─────────────────────────────────

#[test]
fn test_chain_gt_eq_false() {
    let price = Column::<i64>::new("price");
    let cond = price.gt(10i64).eq(false);
    assert_eq!(cond.into_expr().preview(), "price > 10 = 0");
}

#[test]
fn test_chain_gt_eq_true() {
    let price = Column::<i64>::new("price");
    let cond = price.gt(10i64).eq(true);
    assert_eq!(cond.into_expr().preview(), "price > 10 = 1");
}

#[test]
fn test_chain_gt_eq_string() {
    // After first op, any AnySqliteType-compatible value is accepted
    let price = Column::<i64>::new("price");
    let cond = price.gt(10i64).eq("foobar");
    assert_eq!(cond.into_expr().preview(), "price > 10 = 'foobar'");
}

// ── Same-type column comparison ─────────────────────────────────────

#[test]
fn test_column_eq_column() {
    let price = Column::<i64>::new("price");
    let cond = price.eq(price.clone());
    assert_eq!(cond.into_expr().preview(), "price = price");
}

// ── Cross-type column rejected at compile time ──────────────────────
// price.eq(is_deleted) won't compile: Column<bool> isn't Expressive<i64>
// (verified by negative compile test, not runtime)

// ── fx! macro ───────────────────────────────────────────────────────

#[test]
fn test_fx_count_star() {
    let f: vantage_sql::primitives::Fx<AnySqliteType> = fx!("count", sqlite_expr!("*"));
    assert_eq!(f.expr().preview(), "COUNT(*)");
}

#[test]
fn test_fx_avg_ident() {
    let f: vantage_sql::primitives::Fx<AnySqliteType> = fx!("avg", ident("price"));
    assert_eq!(f.expr().preview(), "AVG(\"price\")");
}

#[test]
fn test_fx_coalesce_multiple_args() {
    let f: vantage_sql::primitives::Fx<AnySqliteType> =
        fx!("coalesce", ident("nickname"), "anonymous");
    assert_eq!(f.expr().preview(), "COALESCE(\"nickname\", 'anonymous')");
}

#[test]
fn test_fx_nested() {
    let f: vantage_sql::primitives::Fx<AnySqliteType> =
        fx!("round", fx!("avg", ident("price")), 2i64);
    assert_eq!(f.expr().preview(), "ROUND(AVG(\"price\"), 2)");
}

// ── Interval: SQLite has no interval type, so it renders days ──────────

#[test]
fn test_interval_renders_as_days() {
    let days = |amount: i64, unit: &str| -> String {
        let e: vantage_expressions::Expression<AnySqliteType> = Interval::new(amount, unit).expr();
        e.preview()
    };

    assert_eq!(days(30, "day"), "30");
    assert_eq!(days(2, "weeks"), "14");
    assert_eq!(days(1, "week"), "7");
    assert_eq!(days(1, "month"), "30");
    assert_eq!(days(1, "year"), "365");
    assert_eq!(days(48, "hours"), "2");
}

// ── ternary ─────────────────────────────────────────────────────────

#[test]
fn test_ternary_bare_strings() {
    let expr = ternary(ident("stock").gt(0i64), "in stock", "sold out");
    assert_eq!(
        expr.expr().preview(),
        "IIF(\"stock\" > 0, 'in stock', 'sold out')"
    );
}

// ── Case ────────────────────────────────────────────────────────────

#[test]
fn test_case_bare_strings() {
    let expr = Case::new()
        .when(ident("status").eq("active"), "yes")
        .when(ident("status").eq("banned"), "no")
        .else_("unknown");
    assert_eq!(
        expr.expr().preview(),
        "CASE WHEN \"status\" = 'active' THEN 'yes' WHEN \"status\" = 'banned' THEN 'no' ELSE 'unknown' END"
    );
}

// ── Concat ──────────────────────────────────────────────────────────

#[test]
fn test_concat_basic() {
    use vantage_sql::concat_;
    let c: Concat<AnySqliteType> = concat_!(ident("first_name"), ident("last_name"));
    assert_eq!(c.expr().preview(), "\"first_name\" || \"last_name\"");
}

#[test]
fn test_concat_ws_string_separator() {
    use vantage_sql::concat_;
    let c: Concat<AnySqliteType> = concat_!(ident("first_name"), ident("last_name")).ws(", ");
    assert_eq!(
        c.expr().preview(),
        "\"first_name\" || ', ' || \"last_name\""
    );
}

#[test]
fn test_concat_with_literal() {
    use vantage_sql::concat_;
    let c: Concat<AnySqliteType> = concat_!(ident("first_name"), " ", ident("last_name"));
    assert_eq!(c.expr().preview(), "\"first_name\" || ' ' || \"last_name\"");
}

// ── ident ───────────────────────────────────────────────────────────

#[test]
fn test_ident_simple() {
    let e: vantage_expressions::Expression<AnySqliteType> = ident("price").expr();
    assert_eq!(e.preview(), "\"price\"");
}

#[test]
fn test_ident_qualified() {
    let e: vantage_expressions::Expression<AnySqliteType> = ident("name").dot_of("u").expr();
    assert_eq!(e.preview(), "\"u\".\"name\"");
}

#[test]
fn test_ident_alias() {
    let e: vantage_expressions::Expression<AnySqliteType> = ident("total").with_alias("t").expr();
    assert_eq!(e.preview(), "\"total\" AS \"t\"");
}

// ── Union ───────────────────────────────────────────────────────────

#[test]
fn test_union_intersect_joins_selects_with_intersect() {
    use vantage_expressions::Selectable;
    use vantage_sql::primitives::union::Union;
    use vantage_sql::sqlite::statements::SqliteSelect;

    let first = SqliteSelect::new().with_source("t1").with_field("id");
    let second = SqliteSelect::new().with_source("t2").with_field("id");
    let compound = Union::new(first).intersect(second);
    assert_eq!(
        compound.expr().preview(),
        "SELECT \"id\" FROM \"t1\" INTERSECT SELECT \"id\" FROM \"t2\""
    );
}

// ── or_ / and_ ─────────────────────────────────────────────────────

/// How conditions make groups. `AND` binds more tightly than `OR`. Thus
/// alternatives without brackets change the query: `role = 'admin' AND a
/// OR b` means `(role = 'admin' AND a) OR b`, and each row that matches
/// only `b` ignores the role condition. `or_` and `and_` write the
/// brackets. The statement renderer does not.
#[test]
fn test_condition_grouping() {
    let price = Column::<i64>::new("price");
    let in_stock = Column::<bool>::new("in_stock");
    let featured = Column::<bool>::new("featured");

    // A chain is one flat group. It has one set of brackets around the
    // full chain, and no brackets around each operand.
    assert_eq!(
        price
            .gt(100i64)
            .or_(in_stock.eq(true))
            .or_(featured.eq(true))
            .preview(),
        "(price > 100 OR in_stock = 1 OR featured = 1)"
    );

    // Nest the calls, and the inner group writes its own brackets.
    assert_eq!(
        price
            .gt(100i64)
            .or_(in_stock.eq(true).or_(featured.eq(true)))
            .preview(),
        "(price > 100 OR (in_stock = 1 OR featured = 1))"
    );

    // The operator changes. The chain to this point becomes one operand.
    assert_eq!(
        or_(and_(price.gt(100i64), in_stock.eq(true)), featured.eq(true)).preview(),
        "((price > 100 AND in_stock = 1) OR featured = 1)"
    );

    // The function and the method give the same result.
    assert_eq!(
        or_(ident("role").eq("admin"), ident("role").eq("superuser")).preview(),
        "(\"role\" = 'admin' OR \"role\" = 'superuser')"
    );
}