/*
 * test_query_ext.cj - 扩展查询构建器测试
 */

package tybb2026::tycj_orm

import std.collection.*
import std.unittest.*

@TestClass
class TestUnionBuilder {

    @TestCase
    func testUnionBasic(): Unit {
        let builder1 = SelectBuilder("users")
        builder1.column("id").column("name")

        let builder2 = SelectBuilder("customers")
        builder2.column("id").column("name")

        let unionBuilder = UnionBuilder()
        unionBuilder.addSelect(builder1).addSelect(builder2)

        let sql = unionBuilder.build()

        @Assert(sql.contains("SELECT"))
        @Assert(sql.contains("FROM users"))
        @Assert(sql.contains("FROM customers"))
        @Assert(sql.contains("UNION"))
    }

    @TestCase
    func testUnionAll(): Unit {
        let builder1 = SelectBuilder("users")
        builder1.column("id")

        let builder2 = SelectBuilder("admins")
        builder2.column("id")

        let unionBuilder = UnionBuilder()
        unionBuilder.addSelect(builder1).addSelect(builder2).unionAll()

        let sql = unionBuilder.build()

        @Assert(sql.contains("UNION ALL"))
    }

    @TestCase
    func testUnionWithOrderBy(): Unit {
        let builder1 = SelectBuilder("users")
        builder1.column("id").column("name")

        let builder2 = SelectBuilder("customers")
        builder2.column("id").column("name")

        let unionBuilder = UnionBuilder()
        unionBuilder.addSelect(builder1).addSelect(builder2)
        unionBuilder.orderBy("name", Direction.Asc)

        let sql = unionBuilder.build()

        @Assert(sql.contains("ORDER BY"))
        @Assert(sql.contains("ASC"))
    }

    @TestCase
    func testUnionWithLimit(): Unit {
        let builder1 = SelectBuilder("users")
        builder1.column("id")

        let builder2 = SelectBuilder("admins")
        builder2.column("id")

        let unionBuilder = UnionBuilder()
        unionBuilder.addSelect(builder1).addSelect(builder2)
        unionBuilder.limit(10)

        let sql = unionBuilder.build()

        @Assert(sql.contains("LIMIT"))
        @Assert(sql.contains("10"))
    }
}

@TestClass
class TestBatchInsertBuilder {

    @TestCase
    func testBatchInsertBasic(): Unit {
        let builder = BatchInsertBuilder("users")

        let columns = ArrayList<String>()
        columns.add("name")
        columns.add("age")
        builder.columns(columns)

        let row1 = ArrayList<String>()
        row1.add("张三")
        row1.add("25")
        builder.addRow(row1)

        let row2 = ArrayList<String>()
        row2.add("李四")
        row2.add("30")
        builder.addRow(row2)

        let sql = builder.build()

        @Assert(sql.contains("INSERT INTO"))
        @Assert(sql.contains("users"))
        @Assert(sql.contains("VALUES"))
        @Assert(sql.contains("(?, ?)"))
        @Assert(sql.contains(", (?, ?)"))
    }

    @TestCase
    func testBatchInsertParameters(): Unit {
        let builder = BatchInsertBuilder("users")

        let columns = ArrayList<String>()
        columns.add("name")
        builder.columns(columns)

        let row1 = ArrayList<String>()
        row1.add("张三")
        builder.addRow(row1)

        let row2 = ArrayList<String>()
        row2.add("李四")
        builder.addRow(row2)

        let params = builder.getParameters()

        @Assert(params.size == 2)
        @Assert(params[0] == "张三")
        @Assert(params[1] == "李四")
    }

    @TestCase
    func testBatchInsertRowCount(): Unit {
        let builder = BatchInsertBuilder("users")

        let columns = ArrayList<String>()
        columns.add("name")
        builder.columns(columns)

        for (i in 0..5) {
            let row = ArrayList<String>()
            row.add("user_${i}")
            builder.addRow(row)
        }

        @Assert(builder.getRowCount() == 5)
    }

    @TestCase
    func testBatchInsertAddRows(): Unit {
        let builder = BatchInsertBuilder("users")

        let columns = ArrayList<String>()
        columns.add("name")
        builder.columns(columns)

        let rows = ArrayList<ArrayList<String>>()
        for (i in 0..3) {
            let row = ArrayList<String>()
            row.add("user_${i}")
            rows.add(row)
        }
        builder.addRows(rows)

        @Assert(builder.getRowCount() == 3)
    }
}

@TestClass
class TestSubQueryBuilder {

    @TestCase
    func testSubQueryBasic(): Unit {
        let subBuilder = SubQueryBuilder(alias: "sub")
        let selectBuilder = subBuilder.getSelectBuilder()
        selectBuilder.from("orders").column("user_id")

        let sql = subBuilder.build()

        @Assert(sql.contains("SELECT"))
        @Assert(sql.contains("FROM orders"))
        @Assert(sql.contains("AS sub"))
        @Assert(sql.startsWith("("))
        @Assert(sql.endsWith(") AS sub"))
    }

    @TestCase
    func testSubQueryWithoutAlias(): Unit {
        let subBuilder = SubQueryBuilder()
        let selectBuilder = subBuilder.getSelectBuilder()
        selectBuilder.from("orders").column("user_id")

        let sql = subBuilder.buildWithoutAlias()

        @Assert(sql.startsWith("("))
        @Assert(sql.endsWith(")"))
        @Assert(!sql.contains("AS"))
    }
}

@TestClass
class TestSelectBuilderExt {

    @TestCase
    func testFromSubQuery(): Unit {
        let subSelect = SelectBuilder("orders")
        subSelect.column("user_id").column("COUNT(*) AS order_count")

        let sql = SelectBuilderExt.fromSubQuery(subSelect, "sub")

        @Assert(sql.startsWith("("))
        @Assert(sql.contains("AS sub"))
    }

    @TestCase
    func testColumnSubQuery(): Unit {
        let subSelect = SelectBuilder("orders")
        subSelect.column("COUNT(*)")

        let sql = SelectBuilderExt.columnSubQuery(subSelect, "total_orders")

        @Assert(sql.startsWith("("))
        @Assert(sql.contains("AS total_orders"))
    }

    @TestCase
    func testInSubQuery(): Unit {
        let subSelect = SelectBuilder("orders")
        subSelect.column("user_id").where_("status = 'active'")

        let sql = SelectBuilderExt.inSubQuery("user_id", subSelect)

        @Assert(sql.contains("user_id IN"))
        @Assert(sql.contains("SELECT"))
        @Assert(sql.contains("FROM orders"))
    }

    @TestCase
    func testExistsSubQuery(): Unit {
        let subSelect = SelectBuilder("orders")
        subSelect.column("1").where_("orders.user_id = users.id")

        let sql = SelectBuilderExt.existsSubQuery(subSelect)

        @Assert(sql.contains("EXISTS"))
        @Assert(sql.contains("SELECT"))
    }

    @TestCase
    func testNotExistsSubQuery(): Unit {
        let subSelect = SelectBuilder("orders")
        subSelect.column("1").where_("orders.user_id = users.id")

        let sql = SelectBuilderExt.notExistsSubQuery(subSelect)

        @Assert(sql.contains("NOT EXISTS"))
    }
}

@TestClass
class TestConditionBuilderExt {

    @TestCase
    func testInSubQuery(): Unit {
        let subSelect = SelectBuilder("orders")
        subSelect.column("user_id")

        let sql = ConditionBuilderExt.inSubQuery("id", subSelect)

        @Assert(sql.contains("id IN"))
        @Assert(sql.contains("SELECT"))
    }

    @TestCase
    func testNotInSubQuery(): Unit {
        let subSelect = SelectBuilder("blacklist")
        subSelect.column("user_id")

        let sql = ConditionBuilderExt.notInSubQuery("id", subSelect)

        @Assert(sql.contains("id NOT IN"))
    }

    @TestCase
    func testExistsSubQuery(): Unit {
        let subSelect = SelectBuilder("orders")
        subSelect.column("1")

        let sql = ConditionBuilderExt.existsSubQuery(subSelect)

        @Assert(sql.contains("EXISTS"))
    }

    @TestCase
    func testNotExistsSubQuery(): Unit {
        let subSelect = SelectBuilder("orders")
        subSelect.column("1")

        let sql = ConditionBuilderExt.notExistsSubQuery(subSelect)

        @Assert(sql.contains("NOT EXISTS"))
    }

    @TestCase
    func testCompareSubQuery(): Unit {
        let subSelect = SelectBuilder("orders")
        subSelect.column("MAX(amount)")

        let sql = ConditionBuilderExt.compareSubQuery("amount", ">", subSelect)

        @Assert(sql.contains("amount >"))
        @Assert(sql.contains("SELECT"))
        @Assert(sql.contains("MAX"))
    }
}