use pretty_assertions::assert_eq;
use sea_query::{tests_cfg::*, *};
use sea_query_clickhouse::*;

#[test]
fn select_1() {
    assert_eq!(
        Query::select()
            .columns([Char::Character, Char::SizeW, Char::SizeH])
            .from(Char::Table)
            .limit(10)
            .offset(100)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT `character`, `size_w`, `size_h` FROM `character` LIMIT 10 OFFSET 100"#
    );
}

#[test]
fn select_2() {
    assert_eq!(
        Query::select()
            .columns([Char::Character, Char::SizeW, Char::SizeH])
            .from(Char::Table)
            .and_where(Expr::col(Char::SizeW).eq(3))
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT `character`, `size_w`, `size_h` FROM `character` WHERE `size_w` = 3"#
    );
}

#[test]
fn select_3() {
    assert_eq!(
        Query::select()
            .columns([Char::Character, Char::SizeW, Char::SizeH])
            .from(Char::Table)
            .and_where(Expr::col(Char::SizeW).eq(3))
            .and_where(Expr::col(Char::SizeH).eq(4))
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT `character`, `size_w`, `size_h` FROM `character` WHERE `size_w` = 3 AND `size_h` = 4"#
    );
}

#[test]
fn select_join() {
    assert_eq!(
        Query::select()
            .column(Char::Character)
            .column((Font::Table, Font::Name))
            .from(Char::Table)
            .left_join(
                Font::Table,
                Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id))
            )
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT `character`, `font`.`name` FROM `character` LEFT JOIN `font` ON `character`.`font_id` = `font`.`id`"#
    );
}

#[test]
fn select_group_by() {
    assert_eq!(
        Query::select()
            .column(Char::Character)
            .from(Char::Table)
            .group_by_col(Char::Character)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT `character` FROM `character` GROUP BY `character`"#
    );
}

#[test]
fn select_having() {
    assert_eq!(
        Query::select()
            .column(Glyph::Aspect)
            .expr(Expr::col(Glyph::Image).max())
            .from(Glyph::Table)
            .group_by_columns([Glyph::Aspect])
            .and_having(Expr::col(Glyph::Aspect).gt(2))
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT `aspect`, MAX(`image`) FROM `glyph` GROUP BY `aspect` HAVING `aspect` > 2"#
    );
}

#[test]
fn select_order_by() {
    assert_eq!(
        Query::select()
            .column(Glyph::Aspect)
            .from(Glyph::Table)
            .order_by(Glyph::Aspect, Order::Asc)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT `aspect` FROM `glyph` ORDER BY `aspect` ASC"#
    );
}

#[test]
fn select_order_by_nulls() {
    // NOTE: NULLS FIRST/LAST is not supported via the trait system because
    // OrderExpr fields are pub(crate) in sea-query. Use Expr::cust() as a
    // workaround for null ordering, e.g.:
    //   Expr::cust("`aspect` ASC NULLS LAST")
    assert_eq!(
        Query::select()
            .column(Glyph::Aspect)
            .from(Glyph::Table)
            .order_by_with_nulls(Glyph::Aspect, Order::Asc, NullOrdering::Last)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT `aspect` FROM `glyph` ORDER BY `aspect` ASC"#
    );
}

#[test]
fn select_subquery() {
    assert_eq!(
        Query::select()
            .columns([Glyph::Aspect])
            .from_subquery(
                Query::select()
                    .columns([Glyph::Image, Glyph::Aspect])
                    .from(Glyph::Table)
                    .take(),
                "subglyph",
            )
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT `aspect` FROM (SELECT `image`, `aspect` FROM `glyph`) AS `subglyph`"#
    );
}

#[test]
fn select_distinct() {
    assert_eq!(
        Query::select()
            .distinct()
            .columns([Char::Character])
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT DISTINCT `character` FROM `character`"#
    );
}

#[test]
fn select_union() {
    assert_eq!(
        Query::select()
            .column(Char::Character)
            .from(Char::Table)
            .and_where(Expr::col(Char::FontId).eq(5))
            .union(
                UnionType::All,
                Query::select()
                    .column(Char::Character)
                    .from(Char::Table)
                    .and_where(Expr::col(Char::FontId).eq(4))
                    .to_owned()
            )
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT `character` FROM `character` WHERE `font_id` = 5 UNION ALL (SELECT `character` FROM `character` WHERE `font_id` = 4)"#
    );
}

// ClickHouse-specific tests (via ClickhouseSelect wrapper)

#[test]
fn select_prewhere() {
    assert_eq!(
        ClickhouseSelect::new(
            Query::select()
                .columns([Char::Character, Char::SizeW])
                .from(Char::Table)
                .and_where(Expr::col(Char::SizeH).lt(50))
                .to_owned()
        )
        .prewhere(Expr::col(Char::SizeW).gt(100))
        .to_string(),
        r#"SELECT `character`, `size_w` FROM `character` PREWHERE `size_w` > 100 WHERE `size_h` < 50"#
    );
}

#[test]
fn select_prewhere_only() {
    assert_eq!(
        ClickhouseSelect::new(
            Query::select()
                .columns([Char::Character])
                .from(Char::Table)
                .to_owned()
        )
        .prewhere(Expr::col(Char::FontId).eq(5))
        .to_string(),
        r#"SELECT `character` FROM `character` PREWHERE `font_id` = 5"#
    );
}

#[test]
fn select_final() {
    assert_eq!(
        ClickhouseSelect::new(
            Query::select()
                .columns([Char::Character])
                .from(Char::Table)
                .to_owned()
        )
        .r#final()
        .to_string(),
        r#"SELECT `character` FROM `character` FINAL"#
    );
}

#[test]
fn select_final_with_where() {
    assert_eq!(
        ClickhouseSelect::new(
            Query::select()
                .columns([Char::Character])
                .from(Char::Table)
                .and_where(Expr::col(Char::SizeW).eq(3))
                .to_owned()
        )
        .r#final()
        .to_string(),
        r#"SELECT `character` FROM `character` FINAL WHERE `size_w` = 3"#
    );
}

#[test]
fn select_sample() {
    assert_eq!(
        ClickhouseSelect::new(
            Query::select()
                .columns([Char::Character])
                .from(Char::Table)
                .to_owned()
        )
        .sample(0.1)
        .to_string(),
        r#"SELECT `character` FROM `character` SAMPLE 0.1"#
    );
}

#[test]
fn select_sample_with_offset() {
    assert_eq!(
        ClickhouseSelect::new(
            Query::select()
                .columns([Char::Character])
                .from(Char::Table)
                .to_owned()
        )
        .sample_with_offset(0.1, 0.5)
        .to_string(),
        r#"SELECT `character` FROM `character` SAMPLE 0.1 OFFSET 0.5"#
    );
}

#[test]
fn select_settings() {
    assert_eq!(
        ClickhouseSelect::new(
            Query::select()
                .columns([Char::Character])
                .from(Char::Table)
                .to_owned()
        )
        .setting("max_threads", "2")
        .setting("max_block_size", "65505")
        .to_string(),
        r#"SELECT `character` FROM `character` SETTINGS max_threads = 2, max_block_size = 65505"#
    );
}

#[test]
fn select_final_sample_prewhere_settings() {
    assert_eq!(
        ClickhouseSelect::new(
            Query::select()
                .columns([Char::Character, Char::SizeW])
                .from(Char::Table)
                .and_where(Expr::col(Char::SizeH).lt(100))
                .order_by(Char::Character, Order::Asc)
                .limit(10)
                .to_owned()
        )
        .r#final()
        .sample(0.5)
        .prewhere(Expr::col(Char::SizeW).gt(10))
        .setting("max_threads", "4")
        .to_string(),
        [
            r#"SELECT `character`, `size_w` FROM `character` FINAL SAMPLE 0.5"#,
            r#"PREWHERE `size_w` > 10"#,
            r#"WHERE `size_h` < 100"#,
            r#"ORDER BY `character` ASC"#,
            r#"LIMIT 10"#,
            r#"SETTINGS max_threads = 4"#,
        ]
        .join(" ")
    );
}

#[test]
fn insert_1() {
    assert_eq!(
        Query::insert()
            .into_table(Glyph::Table)
            .columns([Glyph::Aspect, Glyph::Image])
            .values_panic([5.15.into(), "12A".into()])
            .values_panic([4.21.into(), "123".into()])
            .to_string(ClickhouseQueryBuilder),
        r#"INSERT INTO `glyph` (`aspect`, `image`) VALUES (5.15, '12A'), (4.21, '123')"#
    );
}

#[test]
fn update_1() {
    assert_eq!(
        Query::update()
            .table(Glyph::Table)
            .values([(Glyph::Aspect, 1.23.into()), (Glyph::Image, "abc".into())])
            .and_where(Expr::col(Glyph::Id).eq(1))
            .to_string(ClickhouseQueryBuilder),
        r#"ALTER TABLE `glyph` UPDATE `aspect` = 1.23, `image` = 'abc' WHERE `id` = 1"#
    );
}

#[test]
fn delete_1() {
    assert_eq!(
        Query::delete()
            .from_table(Glyph::Table)
            .and_where(Expr::col(Glyph::Id).eq(1))
            .to_string(ClickhouseQueryBuilder),
        r#"ALTER TABLE `glyph` DELETE WHERE `id` = 1"#
    );
}

#[test]
fn select_expr_alias() {
    assert_eq!(
        Query::select()
            .from(Char::Table)
            .expr_as(Expr::col(Char::Character), "C")
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT `character` AS `C` FROM `character`"#
    );
}

#[test]
fn select_bin_oper_json() {
    assert_eq!(
        Query::select()
            .from(Char::Table)
            .expr(Expr::col(Char::Character).binary(ChBinOper::GetJsonField, "key"))
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT `character` -> 'key' FROM `character`"#
    );
}

// ClickHouse function tests

#[test]
fn func_to_date_time() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::to_date_time(Expr::val("2024-01-15 10:30:00")))
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT toDateTime('2024-01-15 10:30:00')"#
    );
}

#[test]
fn func_to_date() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::to_date(Expr::col(Char::Character)))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT toDate(`character`) FROM `character`"#
    );
}

#[test]
fn func_to_year() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::to_year(Expr::col(Char::Character)))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT toYear(`character`) FROM `character`"#
    );
}

#[test]
fn func_to_start_of_month() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::to_start_of_month(Expr::col(Char::Character)))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT toStartOfMonth(`character`) FROM `character`"#
    );
}

#[test]
fn func_date_diff() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::date_diff(
                Expr::val("day"),
                Expr::col(Char::SizeW),
                Expr::col(Char::SizeH)
            ))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT dateDiff('day', `size_w`, `size_h`) FROM `character`"#
    );
}

#[test]
fn func_format_date_time() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::format_date_time(
                Expr::col(Char::Character),
                Expr::val("%Y-%m-%d")
            ))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT formatDateTime(`character`, '%Y-%m-%d') FROM `character`"#
    );
}

#[test]
fn func_now() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::now())
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT now()"#
    );
}

#[test]
fn func_today() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::today())
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT today()"#
    );
}

#[test]
fn func_to_yyyymm() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::to_yyyymm(ChFunc::now()))
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT toYYYYMM(now())"#
    );
}

#[test]
fn func_array_join() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::array_join(Expr::col(Char::Character)))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT arrayJoin(`character`) FROM `character`"#
    );
}

#[test]
fn func_group_array() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::group_array(Expr::col(Char::Character)))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT groupArray(`character`) FROM `character`"#
    );
}

#[test]
fn func_has() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::has(Expr::col(Char::Character), Expr::val("a")))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT has(`character`, 'a') FROM `character`"#
    );
}

#[test]
fn func_length() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::length(Expr::col(Char::Character)))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT length(`character`) FROM `character`"#
    );
}

#[test]
fn func_to_string() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::to_string(Expr::col(Char::SizeW)))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT toString(`size_w`) FROM `character`"#
    );
}

#[test]
fn func_to_uint32() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::to_uint32(Expr::col(Char::Character)))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT toUInt32(`character`) FROM `character`"#
    );
}

#[test]
fn func_to_type_name() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::to_type_name(Expr::col(Char::Character)))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT toTypeName(`character`) FROM `character`"#
    );
}

#[test]
fn func_replace_all() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::replace_all(
                Expr::col(Char::Character),
                Expr::val("old"),
                Expr::val("new")
            ))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT replaceAll(`character`, 'old', 'new') FROM `character`"#
    );
}

#[test]
fn func_split_by_char() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::split_by_char(
                Expr::val(","),
                Expr::col(Char::Character)
            ))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT splitByChar(',', `character`) FROM `character`"#
    );
}

#[test]
fn func_sip_hash64() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::sip_hash64(Expr::col(Char::Character)))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT sipHash64(`character`) FROM `character`"#
    );
}

#[test]
fn func_uniq() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::uniq(Expr::col(Char::Character)))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT uniq(`character`) FROM `character`"#
    );
}

#[test]
fn func_uniq_exact() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::uniq_exact(Expr::col(Char::Character)))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT uniqExact(`character`) FROM `character`"#
    );
}

#[test]
fn func_arg_min() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::arg_min(
                Expr::col(Char::Character),
                Expr::col(Char::SizeW)
            ))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT argMin(`character`, `size_w`) FROM `character`"#
    );
}

#[test]
fn func_sum_if() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::sum_if(
                Expr::col(Char::SizeW),
                Expr::col(Char::SizeH).gt(10)
            ))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT sumIf(`size_w`, `size_h` > 10) FROM `character`"#
    );
}

#[test]
fn func_count_if() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::count_if(Expr::col(Char::SizeW).gt(5)))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT countIf(`size_w` > 5) FROM `character`"#
    );
}

#[test]
fn func_if() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::r#if(
                Expr::col(Char::SizeW).gt(10),
                Expr::val("big"),
                Expr::val("small")
            ))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT if(`size_w` > 10, 'big', 'small') FROM `character`"#
    );
}

#[test]
fn func_is_null() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::is_null(Expr::col(Char::Character)))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT isNull(`character`) FROM `character`"#
    );
}

#[test]
fn func_assume_not_null() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::assume_not_null(Expr::col(Char::Character)))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT assumeNotNull(`character`) FROM `character`"#
    );
}

#[test]
fn func_generate_uuid_v4() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::generate_uuid_v4())
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT generateUUIDv4()"#
    );
}

#[test]
fn func_domain() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::domain(Expr::val("https://example.com/path")))
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT domain('https://example.com/path')"#
    );
}

#[test]
fn func_json_extract_string() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::json_extract_string([
                Expr::col(Char::Character),
                Expr::val("key"),
            ]))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT JSONExtractString(`character`, 'key') FROM `character`"#
    );
}

#[test]
fn func_running_difference() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::running_difference(Expr::col(Char::SizeW)))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT runningDifference(`size_w`) FROM `character`"#
    );
}

#[test]
fn func_nested_functions() {
    assert_eq!(
        Query::select()
            .expr(ChFunc::to_start_of_month(ChFunc::to_date(Expr::col(
                Char::Character
            ))))
            .from(Char::Table)
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT toStartOfMonth(toDate(`character`)) FROM `character`"#
    );
}

#[test]
fn func_in_where_clause() {
    assert_eq!(
        Query::select()
            .columns([Char::Character])
            .from(Char::Table)
            .and_where(Expr::expr(ChFunc::to_year(Expr::col(Char::Character))).eq(2024))
            .to_string(ClickhouseQueryBuilder),
        r#"SELECT `character` FROM `character` WHERE toYear(`character`) = 2024"#
    );
}
