"""
01_acid_demo.py —— ACID 演示：转账场景

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

用法：
    1. 先执行 init.sql 初始化（表名带 ch7_ 前缀）
       psql -h 127.0.0.1 -U postgres -d learn_pg -f ../init.sql
    2. python 01_acid_demo.py

要点：
    - 演示原子性（A）：人为制造异常，整个事务回滚，账户余额不变
    - 演示一致性（C）：CHECK 约束阻止余额变成负数
    - 演示持久性（D）：COMMIT 后立刻查询，数据已落盘
"""
from __future__ import annotations

import psycopg
from decimal import Decimal

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


def show_balances(conn: psycopg.Connection, tag: str) -> None:
    print(f"\n--- {tag} ---")
    with conn.cursor() as cur:
        cur.execute("SELECT id, name, balance FROM ch7_accounts ORDER BY id")
        for row in cur.fetchall():
            print(f"  id={row[0]}  name={row[1]:<6}  balance={row[2]}")


def transfer(conn: psycopg.Connection, src: int, dst: int,
             amount: Decimal, fail: bool = False) -> bool:
    """以 atomic 块演示原子性。fail=True 时人为抛异常验证回滚。"""
    try:
        with conn.transaction():  # psycopg v3：BEGIN ... COMMIT/ROLLBACK
            with conn.cursor() as cur:
                cur.execute(
                    "UPDATE ch7_accounts SET balance = balance - %s, "
                    "updated_at = now() WHERE id = %s",
                    (amount, src),
                )
                if fail:
                    raise RuntimeError("人为抛出异常，验证原子性")
                cur.execute(
                    "UPDATE ch7_accounts SET balance = balance + %s, "
                    "updated_at = now() WHERE id = %s",
                    (amount, dst),
                )
                cur.execute(
                    "INSERT INTO ch7_transfer_log (from_id, to_id, amount) "
                    "VALUES (%s, %s, %s)",
                    (src, dst, amount),
                )
        return True
    except Exception as e:
        print(f"  转账失败：{e}")
        return False


def demo_consistency(conn: psycopg.Connection) -> None:
    """利用 CHECK (balance >= 0) 约束演示一致性。"""
    print("\n=== Consistency 演示：尝试让 Alice 余额变负 ===")
    ok = transfer(conn, src=1, dst=2, amount=Decimal("99999.00"))
    print(f"  转账结果：{'成功' if ok else '被约束阻止 ✓'}")


def demo_atomicity(conn: psycopg.Connection) -> None:
    """中途抛异常验证「全有 / 全无」。"""
    print("\n=== Atomicity 演示：转账中途抛异常 ===")
    show_balances(conn, "事务前")
    transfer(conn, src=1, dst=2, amount=Decimal("100.00"), fail=True)
    show_balances(conn, "异常回滚后（应与事务前完全一致）")


def demo_durability(conn: psycopg.Connection) -> None:
    """COMMIT 后再查，数据已经持久化。"""
    print("\n=== Durability 演示：成功转账并立刻查询 ===")
    transfer(conn, src=1, dst=2, amount=Decimal("100.00"))
    show_balances(conn, "成功 COMMIT 后")


def main() -> None:
    with psycopg.connect(DSN) as conn:
        # 重置数据，便于反复演示
        with conn.cursor() as cur:
            cur.execute("UPDATE ch7_accounts SET balance = 1000.00")
            cur.execute("TRUNCATE ch7_transfer_log RESTART IDENTITY")
        conn.commit()

        show_balances(conn, "初始余额")
        demo_atomicity(conn)
        demo_consistency(conn)
        demo_durability(conn)

        print("\n=== 流水日志 ===")
        with conn.cursor() as cur:
            cur.execute(
                "SELECT id, from_id, to_id, amount, created_at "
                "FROM ch7_transfer_log ORDER BY id"
            )
            for row in cur.fetchall():
                print(f"  {row}")


if __name__ == "__main__":
    main()