"""
02_trigger_audit.py
-------------------
触发器自动写审计表演示：
  应用代码只 INSERT/UPDATE/DELETE ch12_orders，
  但每次操作都会被 ch12_trg_orders_audit 自动捕获并写入 ch12_orders_audit。

依赖:  pip install psycopg[binary]>=3.1
运行:  python 02_trigger_audit.py
"""
import psycopg

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


def show_audit(conn, last_n: int = 10):
    print(f"\n--- ch12_orders_audit 最近 {last_n} 行 ---")
    with conn.cursor() as cur:
        cur.execute(
            "SELECT audit_id, op, order_id, "
            "       (new_data->>'status')  AS new_status, "
            "       (old_data->>'status')  AS old_status, "
            "       changed_at "
            "  FROM ch12_orders_audit "
            " ORDER BY audit_id DESC LIMIT %s",
            (last_n,),
        )
        for r in cur.fetchall():
            print(r)


def main():
    with psycopg.connect(DSN, autocommit=True) as conn:
        show_audit(conn, 5)

        with conn.cursor() as cur:
            print("\n>>> INSERT 一条订单")
            cur.execute(
                "INSERT INTO ch12_orders(user_id, sku, amount) "
                "VALUES (99, 'TestSKU', 199.99) RETURNING id"
            )
            new_id = cur.fetchone()[0]
            print(f"新增订单 id={new_id}")

            print("\n>>> UPDATE 状态为 paid")
            cur.execute(
                "UPDATE ch12_orders SET status='paid' WHERE id = %s", (new_id,)
            )

            print("\n>>> UPDATE 状态为 done")
            cur.execute(
                "UPDATE ch12_orders SET status='done' WHERE id = %s", (new_id,)
            )

            print("\n>>> DELETE")
            cur.execute("DELETE FROM ch12_orders WHERE id = %s", (new_id,))

        show_audit(conn, 6)
        print("\n应用代码没写一行审计逻辑，触发器全程帮我们记录！")


if __name__ == "__main__":
    main()