qail-core 3.0.1

use crate::ast::*;
use crate::parser::parse;

#[test]
fn test_v2_simple_get() {
    let cmd = parse("get users").unwrap();
    assert_eq!(cmd.action, Action::Get);
    assert_eq!(cmd.table, "users");
    // Default is Star when no fields specified
    assert_eq!(cmd.columns, vec![Expr::Star]);
}

#[test]
fn test_v2_get_with_star() {
    let cmd = parse("get users fields *").unwrap();
    assert_eq!(cmd.action, Action::Get);
    assert_eq!(cmd.table, "users");
    assert_eq!(cmd.columns, vec![Expr::Star]);
}

#[test]
fn test_v2_get_with_columns() {
    let cmd = parse("get users fields id, email").unwrap();
    assert_eq!(cmd.action, Action::Get);
    assert_eq!(cmd.table, "users");
    assert_eq!(
        cmd.columns,
        vec![
            Expr::Named("id".to_string()),
            Expr::Named("email".to_string()),
        ]
    );
}

#[test]
fn test_v2_get_expression_literals_stay_structured() {
    let cmd = parse(
        "get users fields COALESCE(name, 'fallback'), CASE WHEN active = true THEN 1 ELSE 0 END",
    )
    .unwrap();

    match &cmd.columns[0] {
        Expr::FunctionCall { args, .. } => {
            assert_eq!(
                args[1],
                Expr::Literal(Value::String("fallback".to_string()))
            );
        }
        other => panic!("expected function call, got {other:?}"),
    }

    match &cmd.columns[1] {
        Expr::Case {
            when_clauses,
            else_value,
            ..
        } => {
            assert_eq!(*when_clauses[0].1, Expr::Literal(Value::Int(1)));
            assert_eq!(else_value.as_deref(), Some(&Expr::Literal(Value::Int(0))));
        }
        other => panic!("expected case expression, got {other:?}"),
    }
}

#[test]
fn test_v2_get_with_filter() {
    let cmd = parse("get users fields * where active = true").unwrap();
    assert_eq!(cmd.cages.len(), 1);
    assert_eq!(cmd.cages[0].kind, CageKind::Filter);
    assert_eq!(cmd.cages[0].conditions.len(), 1);
    assert_eq!(
        cmd.cages[0].conditions[0].left,
        Expr::Named("active".to_string())
    );
    assert_eq!(cmd.cages[0].conditions[0].op, Operator::Eq);
    assert_eq!(cmd.cages[0].conditions[0].value, Value::Bool(true));
}

#[test]
fn test_v2_get_with_limit() {
    let cmd = parse("get users fields * limit 10").unwrap();
    let limit_cage = cmd
        .cages
        .iter()
        .find(|c| matches!(c.kind, CageKind::Limit(_)));
    assert!(limit_cage.is_some());
    assert_eq!(limit_cage.unwrap().kind, CageKind::Limit(10));
}

#[test]
fn test_v2_get_with_offset() {
    let cmd = parse("get users fields * offset 20").unwrap();
    let offset_cage = cmd
        .cages
        .iter()
        .find(|c| matches!(c.kind, CageKind::Offset(_)));
    assert!(offset_cage.is_some());
    assert_eq!(offset_cage.unwrap().kind, CageKind::Offset(20));
}

#[test]
fn test_v2_get_with_limit_offset() {
    let cmd = parse("get users fields * limit 10 offset 20").unwrap();
    let limit_cage = cmd
        .cages
        .iter()
        .find(|c| matches!(c.kind, CageKind::Limit(_)));
    let offset_cage = cmd
        .cages
        .iter()
        .find(|c| matches!(c.kind, CageKind::Offset(_)));
    assert_eq!(limit_cage.unwrap().kind, CageKind::Limit(10));
    assert_eq!(offset_cage.unwrap().kind, CageKind::Offset(20));
}

#[test]
fn test_v2_get_with_sort_desc() {
    let cmd = parse("get users fields * order by created_at desc").unwrap();
    let sort_cage = cmd
        .cages
        .iter()
        .find(|c| matches!(c.kind, CageKind::Sort(_)));
    assert!(sort_cage.is_some());
    assert_eq!(sort_cage.unwrap().kind, CageKind::Sort(SortOrder::Desc));
    assert_eq!(
        sort_cage.unwrap().conditions[0].left,
        Expr::Named("created_at".to_string())
    );
}

#[test]
fn test_v2_get_with_sort_asc() {
    let cmd = parse("get users fields * order by id asc").unwrap();
    let sort_cage = cmd
        .cages
        .iter()
        .find(|c| matches!(c.kind, CageKind::Sort(_)));
    assert!(sort_cage.is_some());
    assert_eq!(sort_cage.unwrap().kind, CageKind::Sort(SortOrder::Asc));
    assert_eq!(
        sort_cage.unwrap().conditions[0].left,
        Expr::Named("id".to_string())
    );
}

#[test]
fn test_v2_get_with_sort_default_asc() {
    let cmd = parse("get users fields * order by name").unwrap();
    let sort_cage = cmd
        .cages
        .iter()
        .find(|c| matches!(c.kind, CageKind::Sort(_)));
    assert!(sort_cage.is_some());
    // Default is ASC
    assert_eq!(sort_cage.unwrap().kind, CageKind::Sort(SortOrder::Asc));
}

#[test]
fn test_v2_fuzzy_match() {
    let cmd = parse("get users fields id where name ~ \"john\"").unwrap();
    assert_eq!(cmd.cages[0].conditions[0].op, Operator::Fuzzy);
    assert_eq!(
        cmd.cages[0].conditions[0].value,
        Value::String("john".to_string())
    );
}

#[test]
fn test_v2_param_in_filter() {
    let cmd = parse("get users fields id where email = $1").unwrap();
    assert_eq!(cmd.cages.len(), 1);
    assert_eq!(cmd.cages[0].conditions[0].value, Value::Param(1));
}

#[test]
fn test_v2_in_accepts_named_parameter_array() {
    let cmd = parse("get users fields id where id in :ids").unwrap();
    assert_eq!(cmd.cages.len(), 1);
    assert_eq!(
        cmd.cages[0].conditions[0].value,
        Value::NamedParam("ids".to_string())
    );

    let cmd = parse("get users fields id where id not in :blocked_ids").unwrap();
    assert_eq!(
        cmd.cages[0].conditions[0].value,
        Value::NamedParam("blocked_ids".to_string())
    );
}

#[test]
fn test_v2_rejects_empty_in_lists() {
    for query in [
        "get users fields id where id in ()",
        "get users fields id where id not in ()",
        "get users fields id where id in ( )",
        "get users fields count(id) filter (where id in ())",
        "get users fields count(id) filter (where id not in ())",
    ] {
        assert!(parse(query).is_err(), "empty IN list parsed: {query}");
    }
}

#[test]
fn test_v2_numeric_overflow_is_rejected() {
    let huge = "999999999999999999999999999999999999999999999999";

    assert!(parse(&format!("get users fields * limit {huge}")).is_err());
    assert!(parse(&format!("get users fields * offset {huge}")).is_err());
    assert!(parse(&format!("get users fields id where email = ${huge}")).is_err());
    assert!(parse(&format!("get users fields id where age = {huge}")).is_err());
    assert!(parse(&format!("get users fields id where age = {huge}d")).is_err());
}

#[test]
fn test_v2_rejects_malformed_identifiers_across_query_clauses() {
    for query in [
        "get 1users",
        "get üsers",
        "get .users",
        "get users.",
        "get users..archive",
        "get users fields .id",
        "get users fields id.",
        "get users fields naïve",
        "get users fields id as 1alias",
        "get users fields id as alias.",
        "get users fields id where .active = true",
        "get users fields id where active. = true",
        "get users fields id where active = .other",
        "get users fields id where active = other.",
        "get users fields id where active = :1active",
        "get users fields id order by .created_at",
        "get users fields id order by created_at.",
        "get distinct on (.id) users fields id",
        "get distinct on (id.) users fields id",
    ] {
        assert!(
            parse(query).is_err(),
            "malformed identifier parsed: {query}"
        );
    }
}

#[test]
fn test_v2_multiple_conditions() {
    let cmd = parse("get users fields * where active = true and role = \"admin\"").unwrap();
    assert_eq!(cmd.cages[0].conditions.len(), 2);
    assert_eq!(
        cmd.cages[0].conditions[0].left,
        Expr::Named("active".to_string())
    );
    assert_eq!(
        cmd.cages[0].conditions[1].left,
        Expr::Named("role".to_string())
    );
}

#[test]
fn test_v2_get_with_or_conditions() {
    let cmd = parse("get users fields * where active = true or role = \"admin\"").unwrap();
    assert_eq!(cmd.cages.len(), 1);
    assert_eq!(cmd.cages[0].kind, CageKind::Filter);
    assert_eq!(cmd.cages[0].logical_op, LogicalOp::Or);
    assert_eq!(cmd.cages[0].conditions.len(), 2);
}

#[test]
fn test_v2_rejects_case_without_when_and_empty_window_clauses() {
    for query in [
        "get users fields CASE ELSE 1 END",
        "get users fields CASE END",
        "get users fields row_number() over (partition by) as rn",
        "get users fields row_number() over (partition by ) as rn",
        "get users fields row_number() over (order by) as rn",
        "get users fields row_number() over (order by ) as rn",
        "get users fields row_number() over (partition by, order by id) as rn",
        "get users fields row_number() over (partition by tenant_id order by) as rn",
    ] {
        assert!(
            parse(query).is_err(),
            "invalid CASE/window expression parsed: {query}"
        );
    }
}

#[test]
fn test_v2_generic_aggregate_keeps_distinct_and_filter() {
    use crate::transpiler::ToSql;

    for (query, expected) in [
        (
            "get orders fields array_agg(distinct status)",
            "SELECT ARRAY_AGG(DISTINCT status) FROM orders",
        ),
        (
            "get orders fields jsonb_agg(payload) filter (where active = true)",
            "SELECT JSONB_AGG(payload) FILTER (WHERE active = true) FROM orders",
        ),
        (
            "get orders fields array_agg(distinct status) filter (where active = true) as statuses",
            "SELECT ARRAY_AGG(DISTINCT status) FILTER (WHERE active = true) AS statuses FROM orders",
        ),
    ] {
        let cmd = parse(query).unwrap();
        assert_eq!(cmd.to_sql(), expected, "{query}");
    }
}

#[test]
fn test_v2_window_keeps_aggregate_filter() {
    use crate::transpiler::ToSql;

    let cmd = parse("get orders fields sum(amount) filter (where active = true) over ()").unwrap();
    assert_eq!(
        cmd.to_sql(),
        "SELECT SUM(amount) FILTER (WHERE active = true) OVER () AS sum FROM orders"
    );
}

#[test]
fn test_v2_aggregate_modifiers_round_trip_through_display() {
    for query in [
        "get orders fields array_agg(distinct status)",
        "get orders fields jsonb_agg(payload) filter (where active = true)",
        "get orders fields sum(amount) filter (where active = true) over ()",
        "get orders fields sum(amount) filter (where active = true) over (partition by region order by created_at desc rows between unbounded preceding and current row) as running",
        "get orders fields sum(amount) filter (where status = 'paid' and deleted_at is null) over (order by id range between 2 preceding and 1 following) as paid",
        "get orders fields sum(amount) over (order by id groups between 1 preceding and current row exclude ties) as grouped",
        "get orders fields avg(amount) over (order by created_at range between 7d preceding and current row exclude current row) as weekly",
        "get orders fields sum(amount) filter (where active = true) over (order by created_at range between 6mo preceding and 1y following exclude group) as spread",
        "get orders fields count(distinct status) filter (where region = 'bali') as n",
        "get orders fields count(*) filter (where amount >= 10 and deleted_at is not null) as n",
    ] {
        let cmd = parse(query).unwrap();
        let rendered = cmd.to_string();
        let reparsed = parse(&rendered)
            .unwrap_or_else(|err| panic!("{query} rendered as {rendered:?}: {err}"));
        assert_eq!(reparsed, cmd, "{query} rendered as {rendered:?}");
    }
}

#[test]
fn test_v2_rejects_aggregate_modifiers_it_cannot_keep() {
    for query in [
        // PostgreSQL has no DISTINCT for window functions.
        "get orders fields count(distinct status) over ()",
        "get orders fields array_agg(distinct status) over ()",
        // Argument counts the aggregate does not take.
        "get orders fields array_agg(distinct status, region)",
        "get orders fields count(distinct status, region)",
        "get orders fields max(amount, fee)",
        // Functions outside the aggregate set have no node for the modifiers.
        "get orders fields bit_or(flags) filter (where active = true)",
        "get orders fields coalesce(distinct status)",
    ] {
        assert!(
            parse(query).is_err(),
            "aggregate modifiers would be dropped: {query}"
        );
    }
}

#[test]
fn test_v2_keeps_aggregate_modifiers_on_argument_lists() {
    use crate::transpiler::ToSql;

    // Expression arguments and STRING_AGG's delimiter now have a slot, so
    // DISTINCT and FILTER are kept rather than refused.
    for (query, modifier) in [
        (
            "get orders fields string_agg(distinct status, ',')",
            "DISTINCT",
        ),
        (
            "get orders fields string_agg(status, ',') filter (where active = true)",
            "FILTER (WHERE",
        ),
        // The formatter writes function names upper-case.
        (
            "get orders fields array_agg(distinct LOWER(status))",
            "DISTINCT",
        ),
    ] {
        let cmd = parse(query).unwrap_or_else(|err| panic!("{query}: {err}"));
        let rendered = cmd.to_string();
        let reparsed = parse(&rendered)
            .unwrap_or_else(|err| panic!("{query} rendered as {rendered:?}: {err}"));
        assert_eq!(reparsed, cmd, "{query} rendered as {rendered:?}");
        let sql = cmd.to_sql();
        assert!(sql.contains(modifier), "{query} lost {modifier}: {sql}");
    }
}

#[test]
fn test_v2_get_mixed_and_or_rejected() {
    let result = parse("get users fields * where active = true and role = \"admin\" or age > 18");
    assert!(result.is_err());
}

#[test]
fn test_v2_full_query() {
    let cmd = parse(
        "get users fields id, name, email where active = true order by created_at desc limit 10",
    )
    .unwrap();
    assert_eq!(cmd.table, "users");
    assert_eq!(cmd.columns.len(), 3);
    assert!(!cmd.cages.is_empty());
}