package aceboot::orm.tests

import aceboot::orm.common.*

import aceboot::orm.sqlite.*
import aceboot::orm.mysql.*
import aceboot::orm.postgres.*

import std.unittest.*
import std.unittest.testmacro.*

// 回归:having 多次调用应 AND 连接、占位与参数个数一致(修复前覆盖片段但累加参数→错位)。
@Test
func test_having_multiple_and_count_with_groupby() {
    let ds = DataSource(SqliteDriver(":memory:"))
    let repo = Repository<_T>(ds, _TMapper())
    repo.createTable()
    // name 分三组:a(n=1,2)、b(n=5)、c(n=9)
    for (p in [("a", 1), ("a", 2), ("b", 5), ("c", 9)]) {
        let e = _T()
        e.name = p[0]
        e.n = p[1]
        repo.insert(e)
    }
    // 按 name 分组,having SUM(n) >= 3 AND COUNT(*) >= 1(两次 having 调用 → AND)
    let qb = repo.createQueryBuilder("t").select("t.name AS name, SUM(t.n) AS s").groupBy("t.name").having(
        "SUM(t.n) >= ?", [DbInt(3)]).having("COUNT(*) >= ?", [DbInt(1)])
    let rows = qb.getRawMany("t.name AS name, SUM(t.n) AS s")
    // a 组 SUM=3、b 组 SUM=5、c 组 SUM=9 都 >=3 → 3 组
    @Expect(rows.size, 3)
    // getCount 在 groupBy 下应返回【组数】=3,而非某组的行数
    @Expect(repo.createQueryBuilder("t").groupBy("t.name").having("SUM(t.n) >= ?", [DbInt(3)]).getCount(), 3)
    // getManyAndCount 的 total 同为组数
    let (_, total) = repo.createQueryBuilder("t").groupBy("t.name").having("SUM(t.n) >= ?", [DbInt(3)]).getManyAndCount()
    @Expect(total, 3)
    ds.driver.close()
}

@Test
func test_render_placeholders_per_dialect() {
    @Expect(renderPlaceholders("a = ? AND b = ?", SqliteDialect()), "a = ? AND b = ?")
    @Expect(renderPlaceholders("a = ? AND b = ?", MySqlDialect()), "a = ? AND b = ?")
    @Expect(renderPlaceholders("a = ? AND b = ?", PostgresDialect()), "a = $1 AND b = $2") // PG 顺序编号
}

@Test
func test_querybuilder_sql_join_where_order_limit() {
    let ds = DataSource(SqliteDriver(":memory:"))
    let repo = Repository<_T>(ds, _TMapper())
    let qb = repo.createQueryBuilder("t").innerJoin("other", "o", "o.tid = t.id").andWhere("o.name", "=",
        DbText("x")).andWhere("t.n", ">", DbInt(5)).orderBy("t.id", "DESC").limit(10)
    @Expect(
        qb.toSql(),
        "SELECT t.id, t.name, t.n FROM t t INNER JOIN other o ON o.tid = t.id WHERE o.name = ? AND t.n > ? ORDER BY t.id DESC LIMIT 10"
    )
    ds.driver.close()
}

@Test
func test_querybuilder_join_execute() {
    // 真跑一次联表:t JOIN other,按 other.tag 过滤,返回 t 实体。
    let ds = DataSource(SqliteDriver(":memory:"))
    let repo = Repository<_T>(ds, _TMapper())
    repo.createTable()
    ds.driver.exec("CREATE TABLE other(id INTEGER PRIMARY KEY, tid INTEGER, tag TEXT)", [])
    let a = _T()
    a.name = "alice"
    a.n = 1
    let ida = repo.insert(a)
    let b = _T()
    b.name = "bob"
    b.n = 2
    repo.insert(b)
    ds.driver.exec("INSERT INTO other(tid, tag) VALUES(?, ?)", [DbInt(ida), DbText("vip")])
    // 查 tag=vip 关联的 t 行 → 应只命中 alice
    let hit = repo.createQueryBuilder("t").innerJoin("other", "o", "o.tid = t.id").andWhere("o.tag", "=",
        DbText("vip")).getMany()
    @Expect(hit.size, 1)
    @Expect(hit[0].name, "alice")
    ds.driver.close()
}

// 造若干行,n 为 1..5,name 形如 "u1".."u5"。
func seedTen(repo: Repository<_T>): Unit {
    var i = 1
    while (i <= 5) {
        let e = _T()
        e.name = "u" + i.toString()
        e.n = i
        repo.insert(e)
        i += 1
    }
}

@Test
func test_querybuilder_skip_take_paging() {
    let ds = DataSource(SqliteDriver(":memory:"))
    let repo = Repository<_T>(ds, _TMapper())
    repo.createTable()
    seedTen(repo)
    // skip/take 是 offset/limit 别名:按 n 升序,跳过 1 取 2 → n=2,3
    let page = repo.createQueryBuilder("t").orderBy("t.n", "ASC").skip(1).take(2).getMany()
    @Expect(page.size, 2)
    @Expect(page[0].n, 2)
    @Expect(page[1].n, 3)
    // SQL 反映 LIMIT/OFFSET
    @Expect(
        repo.createQueryBuilder("t").orderBy("t.n", "ASC").skip(1).take(2).toSql(),
        "SELECT t.id, t.name, t.n FROM t t ORDER BY t.n ASC LIMIT 2 OFFSET 1"
    )
    ds.driver.close()
}

@Test
func test_querybuilder_distinct() {
    let ds = DataSource(SqliteDriver(":memory:"))
    let repo = Repository<_T>(ds, _TMapper())
    repo.createTable()
    // 两行同 n=7,DISTINCT 投影 n 应只剩一行
    let a = _T()
    a.name = "a"
    a.n = 7
    repo.insert(a)
    let b = _T()
    b.name = "b"
    b.n = 7
    repo.insert(b)
    let rows = repo.createQueryBuilder("t").distinct().getRawMany("t.n AS n")
    @Expect(rows.size, 1)
    @Expect(rows[0].getInt("n"), 7)
    // SQL 含 DISTINCT
    @Expect(
        repo.createQueryBuilder("t").distinct().toSql(),
        "SELECT DISTINCT t.id, t.name, t.n FROM t t"
    )
    ds.driver.close()
}

@Test
func test_querybuilder_groupby_having() {
    let ds = DataSource(SqliteDriver(":memory:"))
    let repo = Repository<_T>(ds, _TMapper())
    repo.createTable()
    // name=g 三行,name=h 一行;按 name 分组,HAVING count > 1 → 只剩 g
    var i = 0
    while (i < 3) {
        let e = _T()
        e.name = "g"
        e.n = 1
        repo.insert(e)
        i += 1
    }
    let h = _T()
    h.name = "h"
    h.n = 1
    repo.insert(h)
    let rows = repo.createQueryBuilder("t").groupBy("t.name").having("COUNT(*) > ?", [DbInt(1)]).getRawMany(
        "t.name AS name, COUNT(*) AS c")
    @Expect(rows.size, 1)
    @Expect(rows[0].getText("name"), "g")
    @Expect(rows[0].getInt("c"), 3)
    // SQL 顺序:WHERE 之后 GROUP BY ... HAVING ...
    @Expect(
        repo.createQueryBuilder("t").andWhere("t.n", "=", DbInt(1)).groupBy("t.name").having("COUNT(*) > ?",
            [DbInt(1)]).toSql(),
        "SELECT t.id, t.name, t.n FROM t t WHERE t.n = ? GROUP BY t.name HAVING COUNT(*) > ?"
    )
    ds.driver.close()
}

@Test
func test_querybuilder_get_many_and_count() {
    let ds = DataSource(SqliteDriver(":memory:"))
    let repo = Repository<_T>(ds, _TMapper())
    repo.createTable()
    seedTen(repo) // 5 行,n=1..5
    // n >= 2 的有 4 行;分页只取 2 行,但 count 应是 4(忽略 limit/offset)
    let (rows, total) = repo.createQueryBuilder("t").andWhere("t.n", ">=", DbInt(2)).orderBy("t.n", "ASC").take(
        2).getManyAndCount()
    @Expect(rows.size, 2)
    @Expect(rows[0].n, 2)
    @Expect(rows[1].n, 3)
    @Expect(total, 4)
    ds.driver.close()
}