"""
03_savepoint.py —— 保存点（SAVEPOINT）演示

依赖：
    pip install "psycopg[binary]>=3.1"

要点：
    - 在一个事务里依次插入多个购物车项
    - 故意让其中一条违反 CHECK (qty > 0)
    - 用 SAVEPOINT 局部回滚那条错的，事务整体仍可继续提交

注意：psycopg v3 的 conn.transaction() 支持嵌套，会自动产生 SAVEPOINT。
"""
from __future__ import annotations

import psycopg

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


def show_cart(conn: psycopg.Connection, tag: str) -> None:
    print(f"\n--- {tag} ---")
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, user_id, product_id, qty FROM ch7_cart_items "
            "ORDER BY id"
        )
        for row in cur.fetchall():
            print(f"  id={row[0]} user={row[1]} product={row[2]} qty={row[3]}")
        if cur.rowcount == 0:
            print("  (空)")


def main() -> None:
    with psycopg.connect(DSN) as conn:
        with conn.cursor() as cur:
            cur.execute("TRUNCATE ch7_cart_items RESTART IDENTITY")
        conn.commit()

        with conn.transaction():  # 主事务
            with conn.cursor() as cur:
                cur.execute(
                    "INSERT INTO ch7_cart_items (user_id, product_id, qty) "
                    "VALUES (10, 1, 1)"
                )
                print("[OK] 加入 product=1 qty=1")

            # 嵌套事务等价于 SAVEPOINT sp1; ... RELEASE sp1;
            try:
                with conn.transaction():
                    with conn.cursor() as cur:
                        cur.execute(
                            "INSERT INTO ch7_cart_items "
                            "(user_id, product_id, qty) VALUES (10, 2, -3)"
                        )
            except psycopg.errors.CheckViolation as e:
                # SAVEPOINT 自动 ROLLBACK TO，主事务还能继续
                print(f"[FAIL] 嵌套事务被 CHECK 拦下：{e.diag.message_primary}")
                print("       已 ROLLBACK TO SAVEPOINT，主事务不受影响")

            with conn.cursor() as cur:
                cur.execute(
                    "INSERT INTO ch7_cart_items (user_id, product_id, qty) "
                    "VALUES (10, 2, 2)"
                )
                print("[OK] 重新加入 product=2 qty=2")

                cur.execute(
                    "INSERT INTO ch7_cart_items (user_id, product_id, qty) "
                    "VALUES (10, 3, 1)"
                )
                print("[OK] 加入 product=3 qty=1")

        show_cart(conn, "最终购物车（应有 product 1/2/3，没有失败那条）")


if __name__ == "__main__":
    main()