package aceboot::orm.postgres

import aceboot::orm.common.*

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

// 手写实体 + 映射器(不依赖宏包),验证 PostgresDriver + 方言 + Repository 真连 CRUD。
class _PgItem {
    var id: Int64 = 0
    var name: String = ""
    var n: Int64 = 0
}

class _PgMapper <: EntityMapper<_PgItem> {
    public func table(): String {
        "ace_pg_test"
    }
    public func idColumn(): String {
        "id"
    }
    public func columns(): Array<ColumnSpec> {
        [ColumnSpec("id", DbType.TInt, true, ""), ColumnSpec("name", DbType.TText, false, ""),
            ColumnSpec("n", DbType.TInt, false, "")]
    }
    public func fromRow(r: Row): _PgItem {
        let e = _PgItem()
        e.id = r.getInt("id")
        e.name = r.getText("name")
        e.n = r.getInt("n")
        return e
    }
    public func insertParams(e: _PgItem): Array<DbValue> {
        [DbText(e.name), DbInt(e.n)]
    }
    public func idOf(e: _PgItem): DbValue {
        DbInt(e.id)
    }
    public func setId(e: _PgItem, id: Int64): Unit {
        e.id = id
    }
}

let PG_CONNINFO = "host=127.0.0.1 port=15455 user=postgres password=ace dbname=acetest connect_timeout=2"

@Test
func test_postgres_crud() {
    // 连不上(无 PG 环境)则跳过,保证 cjpm test 在无库环境仍绿。
    let driver = try {
        PostgresDriver(PG_CONNINFO)
    } catch (e: Exception) {
        println("[pg-test] skip: ${e.message}")
        return
    }
    let ds = DataSource(driver)
    let repo = Repository<_PgItem>(ds, _PgMapper())
    driver.exec("DROP TABLE IF EXISTS ace_pg_test", [])
    repo.createTable() // SERIAL 主键(PostgresDialect)

    let e = _PgItem()
    e.name = "alice"
    e.n = 30
    let id = repo.insert(e) // RETURNING id
    @Expect(id, 1)
    @Expect(e.id, 1) // 回填
    @Expect(repo.count(), 1)

    let got = repo.findById(DbInt(id)).getOrThrow()
    @Expect(got.name, "alice") // 文本经 $1 占位绑定 + 取回
    @Expect(got.n, 30)

    got.n = 99
    repo.update(got)
    @Expect(repo.findById(DbInt(id)).getOrThrow().n, 99)
    @Expect(repo.findBy("name", DbText("alice")).size, 1)

    repo.deleteById(DbInt(id))
    @Expect(repo.count(), 0)

    driver.exec("DROP TABLE IF EXISTS ace_pg_test", [])
    driver.close()
    println("[pg-test] PostgreSQL CRUD passed against real server")
}

// 数组字面量序列化(纯函数,无需 PG 环境)。
@Test
func test_pg_array_literal_serialize() {
    @Expect(PostgresDriver.pgArrayLiteral([DbInt(1), DbInt(2), DbNull]), "{1,2,NULL}")
    @Expect(PostgresDriver.pgArrayLiteral(Array<DbValue>()), "{}")
    // 文本恒加引号,内部 " 与 \ 转义
    @Expect(PostgresDriver.pgArrayLiteral([DbText("a"), DbText("b,c"), DbText("q\"x\\y")]),
        "{\"a\",\"b,c\",\"q\\\"x\\\\y\"}")
    // 布尔裸写 t/f,嵌套数组递归
    @Expect(PostgresDriver.pgArrayLiteral([DbBool(true), DbArray([DbInt(7), DbInt(8)])]), "{t,{7,8}}")
    // toText 入口:DbArray 不再返回空串
    @Expect(PostgresDriver.toText(DbArray([DbInt(3), DbInt(5)])), "{3,5}")
}

// 序列化 -> parsePgArray 往返(含引号内逗号与反斜杠转义)。
@Test
func test_pg_array_roundtrip_parse() {
    let lit = PostgresDriver.pgArrayLiteral([DbText("b,c"), DbText("q\"x\\y"), DbNull])
    let back = PostgresDriver.parsePgArray(lit)
    @Expect(back.size, 3)
    match (back[0]) {
        case DbText(s) => @Expect(s, "b,c")
        case _ => @Expect(false, true)
    }
    match (back[1]) {
        case DbText(s) => @Expect(s, "q\"x\\y")
        case _ => @Expect(false, true)
    }
    match (back[2]) {
        case DbNull => ()
        case _ => @Expect(false, true)
    }
}

// 数组列 OID 判定(纯函数):数组类型 OID 为真,JSONB/JSON/TEXT 等标量为假。
@Test
func test_pg_array_oid_detect() {
    @Expect(PostgresDriver.isPgArrayOid(1016), true) // _int8
    @Expect(PostgresDriver.isPgArrayOid(1009), true) // _text
    @Expect(PostgresDriver.isPgArrayOid(3807), true) // _jsonb[]
    @Expect(PostgresDriver.isPgArrayOid(3802), false) // jsonb(标量)
    @Expect(PostgresDriver.isPgArrayOid(114), false) // json(标量)
    @Expect(PostgresDriver.isPgArrayOid(25), false) // text
}

// 真连 PG 回归:DbNull 须绑成 SQL NULL 而非空串(空串对 timestamp/bigint 列报
// invalid input syntax);无 PG 环境则跳过。
@Test
func test_postgres_null_binding() {
    let driver = try {
        PostgresDriver(PG_CONNINFO)
    } catch (e: Exception) {
        println("[pg-test] skip: ${e.message}")
        return
    }
    driver.exec("DROP TABLE IF EXISTS ace_pg_null_test", [])
    driver.exec("CREATE TABLE ace_pg_null_test (id SERIAL PRIMARY KEY, ts TIMESTAMP, n BIGINT)", [])
    // 此前 DbNull 被绑成 "",严格类型列直接报错
    driver.exec("INSERT INTO ace_pg_null_test (ts, n) VALUES ($1, $2)", [DbNull, DbNull])
    let rows = driver.query("SELECT ts, n FROM ace_pg_null_test", [])
    @Expect(rows.size, 1)
    match (rows[0].get("ts").getOrThrow()) {
        case DbNull => ()
        case _ => @Expect(false, true)
    }
    match (rows[0].get("n").getOrThrow()) {
        case DbNull => ()
        case _ => @Expect(false, true)
    }
    // NULL 与非 NULL 参数混绑同一语句
    driver.exec("INSERT INTO ace_pg_null_test (ts, n) VALUES ($1, $2)", [DbNull, DbInt(7)])
    let r2 = driver.query("SELECT n FROM ace_pg_null_test WHERE ts IS NULL AND n IS NOT NULL", [])
    @Expect(r2.size, 1)
    @Expect(r2[0].getInt("n"), 7)
    driver.exec("DROP TABLE IF EXISTS ace_pg_null_test", [])
    driver.close()
    println("[pg-test] PostgreSQL NULL binding passed against real server")
}

// 真连 PG 回归:JSONB 与「内容形似数组的 TEXT」不得被误解析成 DbArray;无 PG 环境则跳过。
@Test
func test_postgres_jsonb_not_parsed_as_array() {
    let driver = try {
        PostgresDriver(PG_CONNINFO)
    } catch (e: Exception) {
        println("[pg-test] skip: ${e.message}")
        return
    }
    driver.exec("DROP TABLE IF EXISTS ace_pg_jsonb_test", [])
    driver.exec("CREATE TABLE ace_pg_jsonb_test (id SERIAL, meta JSONB, note TEXT)", [])
    driver.exec("INSERT INTO ace_pg_jsonb_test (meta, note) VALUES ($1::jsonb, $2)",
        [DbText("{\"k\":\"v\",\"n\":42}"), DbText("{not,an,array}")])
    let rows = driver.query("SELECT meta, note FROM ace_pg_jsonb_test", [])
    @Expect(rows.size, 1)
    // JSONB 以 { 开头,此前被误判为 PG 数组导致 getText/getJson 返回空串
    @Expect(rows[0].getJson("meta"), "{\"k\": \"v\", \"n\": 42}") // jsonb 规范化输出(冒号后带空格)
    // 存了形似数组内容的普通 TEXT 列也须原样读回
    @Expect(rows[0].getText("note"), "{not,an,array}")
    driver.exec("DROP TABLE IF EXISTS ace_pg_jsonb_test", [])
    driver.close()
    println("[pg-test] PostgreSQL JSONB read passed against real server")
}

// 真连 PG 验证数组参数绑定(ANY($1::bigint[]) / $1::text[]);无 PG 环境则跳过。
@Test
func test_postgres_array_binding() {
    let driver = try {
        PostgresDriver(PG_CONNINFO)
    } catch (e: Exception) {
        println("[pg-test] skip: ${e.message}")
        return
    }
    // ANY($1::bigint[]):此前被绑成空串导致查询失败/结果错误
    let rows = driver.query(
        "SELECT x FROM unnest(ARRAY[1,2,3,4]::bigint[]) AS t(x) WHERE x = ANY($1::bigint[]) ORDER BY x",
        [DbArray([DbInt(2), DbInt(4)])])
    @Expect(rows.size, 2)
    @Expect(rows[0].getInt("x"), 2)
    @Expect(rows[1].getInt("x"), 4)
    // text[] 含逗号/引号元素经服务端往返
    let r2 = driver.query("SELECT $1::text[] AS a", [DbArray([DbText("b,c"), DbText("q\"x")])])
    @Expect(r2.size, 1)
    let arr = r2[0].getArray("a")
    @Expect(arr.size, 2)
    match (arr[0]) {
        case DbText(s) => @Expect(s, "b,c")
        case _ => @Expect(false, true)
    }
    match (arr[1]) {
        case DbText(s) => @Expect(s, "q\"x")
        case _ => @Expect(false, true)
    }
    driver.close()
    println("[pg-test] PostgreSQL array binding passed against real server")
}