"""
Ch4 配套代码 1 / 3 —— 6 大约束触发演示

依赖：先 init.sql 建好 ch4_* 系列表

演示：
  1. NOT NULL
  2. PRIMARY KEY 唯一
  3. UNIQUE（含 NULL 允许多个）
  4. CHECK（单列 + 跨列）
  5. FOREIGN KEY（含 ON DELETE RESTRICT / CASCADE）
  6. EXCLUDE USING gist（会议室时间段不重叠）
"""

import psycopg
from psycopg import errors as pe


DSN = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"


def section(title: str) -> None:
    print("\n" + "=" * 64)
    print(title)
    print("=" * 64)


def expect_error(conn: psycopg.Connection, sql: str, params=None,
                 expect: type[Exception] = Exception, hint: str = "") -> None:
    """执行一个 *预期会失败* 的 SQL，捕获约束错误并打印第一行。"""
    try:
        with conn.cursor() as cur:
            cur.execute(sql, params or ())
            cur.fetchall() if cur.description else None
        print(f"  ⚠️  预期失败但通过了！SQL: {sql[:80]}")
    except expect as e:
        conn.rollback()
        msg = str(e).splitlines()[0]
        print(f"  ✅ {hint:38} -> {msg}")
    except Exception as e:
        conn.rollback()
        print(f"  ❌ 未预期错误类型: {type(e).__name__}: {e}")


def demo_not_null(conn: psycopg.Connection) -> None:
    section("Demo 1: NOT NULL")

    expect_error(conn,
        "INSERT INTO ch4_rooms (name, capacity) VALUES (NULL, 10)",
        expect=pe.NotNullViolation,
        hint="name = NULL"
    )


def demo_primary_key(conn: psycopg.Connection) -> None:
    section("Demo 2: PRIMARY KEY 唯一")

    expect_error(conn,
        "INSERT INTO ch4_rooms (id, name, capacity) VALUES (1, 'dup', 10)",
        expect=pe.UniqueViolation,
        hint="id = 1 (已存在)"
    )


def demo_unique(conn: psycopg.Connection) -> None:
    section("Demo 3: UNIQUE 与 NULL")

    with conn.cursor() as cur:
        # name 列是 UNIQUE
        expect_error(conn,
            "INSERT INTO ch4_rooms (name, capacity) VALUES ('A101', 10)",
            expect=pe.UniqueViolation,
            hint="name = 'A101' (已存在)"
        )

        # phone UNIQUE 列允许多 NULL
        cur.execute("INSERT INTO ch4_customers (name, email, phone) VALUES ('U1', 'u1@x.com', NULL) RETURNING id")
        i1 = cur.fetchone()[0]
        cur.execute("INSERT INTO ch4_customers (name, email, phone) VALUES ('U2', 'u2@x.com', NULL) RETURNING id")
        i2 = cur.fetchone()[0]
        print(f"  ✅ phone=NULL 可以插多条: id={i1}, id={i2}")
        cur.execute("DELETE FROM ch4_customers WHERE id IN (%s, %s)", (i1, i2))
        conn.commit()


def demo_check(conn: psycopg.Connection) -> None:
    section("Demo 4: CHECK 约束 (单列 + 跨列)")

    expect_error(conn,
        "INSERT INTO ch4_rooms (name, capacity) VALUES ('TooBig', 9999)",
        expect=pe.CheckViolation,
        hint="capacity > 500"
    )

    expect_error(conn,
        "INSERT INTO ch4_orders (customer_id, list_price, sale_price) VALUES (1, 100, 200)",
        expect=pe.CheckViolation,
        hint="sale_price > list_price"
    )

    expect_error(conn,
        "INSERT INTO ch4_customers (name, email) VALUES ('X', 'invalid-email')",
        expect=pe.CheckViolation,
        hint="email 格式不对"
    )

    expect_error(conn,
        "INSERT INTO ch4_orders (customer_id, list_price, sale_price, status) VALUES (1, 10, 5, 'WHATEVER')",
        expect=pe.CheckViolation,
        hint="status 非法枚举值"
    )


def demo_fk(conn: psycopg.Connection) -> None:
    section("Demo 5: 外键 FOREIGN KEY")

    expect_error(conn,
        "INSERT INTO ch4_orders (customer_id, list_price, sale_price) VALUES (99999, 10, 5)",
        expect=pe.ForeignKeyViolation,
        hint="customer_id 不存在"
    )

    # ON DELETE RESTRICT：删父表会失败
    expect_error(conn,
        "DELETE FROM ch4_customers WHERE id = 1",
        expect=pe.ForeignKeyViolation,
        hint="客户被订单引用，RESTRICT 拒绝删"
    )

    # 演示 ON DELETE CASCADE（rooms -> bookings）
    with conn.cursor() as cur:
        cur.execute("INSERT INTO ch4_rooms (name, capacity) VALUES ('TempRoom', 5) RETURNING id")
        rid = cur.fetchone()[0]
        cur.execute(
            "INSERT INTO ch4_bookings (room_id, booker, period) VALUES (%s, 'tmp', "
            "tstzrange('2026-05-01 09:00+08', '2026-05-01 11:00+08'))",
            (rid,)
        )
        conn.commit()
        cur.execute("SELECT count(*) FROM ch4_bookings WHERE room_id = %s", (rid,))
        before = cur.fetchone()[0]
        cur.execute("DELETE FROM ch4_rooms WHERE id = %s", (rid,))
        conn.commit()
        cur.execute("SELECT count(*) FROM ch4_bookings WHERE room_id = %s", (rid,))
        after = cur.fetchone()[0]
        print(f"  ✅ ON DELETE CASCADE: 删除房间前预订数={before}, 删除后={after}")


def demo_exclude(conn: psycopg.Connection) -> None:
    section("Demo 6: EXCLUDE 排他约束 (PG 独有！)")

    with conn.cursor() as cur:
        cur.execute("SELECT id FROM ch4_rooms WHERE name = 'A101'")
        room_id = cur.fetchone()[0]

        print("  现有 A101 预订：")
        cur.execute("""
            SELECT id, booker, period FROM ch4_bookings
            WHERE room_id = %s ORDER BY period
        """, (room_id,))
        for r in cur.fetchall():
            print(f"     id={r[0]}  booker={r[1]:8}  period={r[2]}")
        print()

    expect_error(conn,
        """INSERT INTO ch4_bookings (room_id, booker, period) VALUES
           (%s, 'Frank', tstzrange('2026-04-17 10:00+08', '2026-04-17 12:00+08'))""",
        params=(room_id,),
        expect=pe.ExclusionViolation,
        hint="A101 10:00~12:00 与 09:00~11:00 重叠"
    )

    expect_error(conn,
        """INSERT INTO ch4_bookings (room_id, booker, period) VALUES
           (%s, 'Frank', tstzrange('2026-04-17 14:00+08', '2026-04-17 16:00+08'))""",
        params=(room_id,),
        expect=pe.ExclusionViolation,
        hint="A101 14:00~16:00 与 13:00~15:00 重叠"
    )

    # 不冲突时间段应该成功
    with conn.cursor() as cur:
        cur.execute("""
            INSERT INTO ch4_bookings (room_id, booker, period) VALUES
            (%s, 'Frank', tstzrange('2026-04-17 19:00+08', '2026-04-17 21:00+08'))
            RETURNING id
        """, (room_id,))
        new_id = cur.fetchone()[0]
        conn.commit()
        print(f"  ✅ 不冲突时段(19:00~21:00) 写入成功 id={new_id}")
        cur.execute("DELETE FROM ch4_bookings WHERE id = %s", (new_id,))
        conn.commit()


def main() -> None:
    with psycopg.connect(DSN, autocommit=False) as conn:
        demo_not_null(conn)
        demo_primary_key(conn)
        demo_unique(conn)
        demo_check(conn)
        demo_fk(conn)
        demo_exclude(conn)


if __name__ == "__main__":
    try:
        main()
    except psycopg.OperationalError as e:
        print(f"❌ 连接 PostgreSQL 失败: {e}")