"""独立示例：SQLite 显式事务、约束失败回滚与参数化 SQL。"""
import sqlite3
from contextlib import closing
import json


def transfer(connection, amount):
    connection.execute("BEGIN IMMEDIATE")
    try:
        connection.execute("UPDATE accounts SET balance = balance - ? WHERE name = 'a'", (amount,))
        connection.execute("UPDATE accounts SET balance = balance + ? WHERE name = 'b'", (amount,))
        connection.execute("COMMIT")
    except BaseException:
        if connection.in_transaction:
            connection.execute("ROLLBACK")
        raise


def idempotent_transfer(connection, operation_id, amount):
    payload = json.dumps({"amount": amount}, sort_keys=True)
    connection.execute("BEGIN IMMEDIATE")
    try:
        previous = connection.execute(
            "SELECT payload, result FROM operations WHERE operation_id = ?", (operation_id,)
        ).fetchone()
        if previous is not None:
            if previous[0] != payload:
                raise ValueError("same operation ID with different payload")
            result = json.loads(previous[1])
        else:
            connection.execute("UPDATE accounts SET balance = balance - ? WHERE name = 'a'", (amount,))
            connection.execute("UPDATE accounts SET balance = balance + ? WHERE name = 'b'", (amount,))
            result = connection.execute("SELECT name, balance FROM accounts ORDER BY name").fetchall()
            result = [list(row) for row in result]
            connection.execute("INSERT INTO operations VALUES (?, ?, ?)",
                               (operation_id, payload, json.dumps(result)))
        connection.execute("COMMIT")
        return result
    except BaseException:
        if connection.in_transaction:
            connection.execute("ROLLBACK")
        raise


if __name__ == "__main__":
    # autocommit=True 保证示例只使用自己显式开启的 SQL 事务。
    with closing(sqlite3.connect(":memory:", autocommit=True)) as connection:
        connection.execute("CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER CHECK(balance >= 0))")
        connection.executemany("INSERT INTO accounts VALUES (?, ?)", [("a", 100), ("b", 0)])
        # 反例：两条语句各自提交，第二条失败后，第一条已经生效。
        connection.execute("UPDATE accounts SET balance = balance - ? WHERE name = ?", (30, "a"))
        try:
            connection.execute("UPDATE accounts SET balance = -1 WHERE name = ?", ("b",))
        except sqlite3.IntegrityError:
            pass
        print("bad:", connection.execute("SELECT name, balance FROM accounts ORDER BY name").fetchall())
        connection.execute("UPDATE accounts SET balance = 100 WHERE name = ?", ("a",))
        connection.execute("BEGIN")
        try:
            connection.execute("UPDATE accounts SET balance = balance - ? WHERE name = ?", (30, "a"))
            connection.execute("UPDATE accounts SET balance = -1 WHERE name = ?", ("b",))
            connection.execute("COMMIT")
        except BaseException as exc:
            connection.execute("ROLLBACK")  # 所有失败都清理，只消费本例预期的约束错误。
            if not isinstance(exc, sqlite3.IntegrityError):
                raise
            print("rolled back:", True)
        rows = connection.execute("SELECT name, balance FROM accounts ORDER BY name").fetchall()
        assert rows == [("a", 100), ("b", 0)]
        print("good:", rows)
        suspicious_name = "a' OR 1=1 --"
        rows = connection.execute("SELECT name FROM accounts WHERE name = ?", (suspicious_name,)).fetchall()
        assert rows == []
        print("parameterized:", rows)
        # 反例：每次转移都是原子的，响应丢失后的重试仍重复副作用。
        for attempt in range(2):
            transfer(connection, 30)
            if attempt == 0:
                try:
                    raise ConnectionError("response lost after commit")
                except ConnectionError:
                    pass  # 客户端只知道没有成功响应，接着发第二次尝试。
        assert connection.execute("SELECT balance FROM accounts WHERE name='a'").fetchone()[0] == 40
        print("unsafe retry:", connection.execute("SELECT name, balance FROM accounts ORDER BY name").fetchall())
        connection.execute("UPDATE accounts SET balance = CASE name WHEN 'a' THEN 100 ELSE 0 END")
        connection.execute("CREATE TABLE operations(operation_id TEXT PRIMARY KEY, payload TEXT NOT NULL, result TEXT NOT NULL)")
        result = idempotent_transfer(connection, "operation-1", 30)
        try:
            raise ConnectionError("response lost after committed idempotent operation")
        except ConnectionError:
            retried = idempotent_transfer(connection, "operation-1", 30)
        assert result == retried == [["a", 70], ["b", 30]]
        assert connection.execute("SELECT count(*) FROM operations").fetchone()[0] == 1
        try:
            idempotent_transfer(connection, "operation-1", 20)
        except ValueError:
            print("same ID different payload:", "rejected")
        else:
            raise AssertionError("conflicting payload accepted")
        assert not connection.in_transaction
        print("safe retry:", retried, "operations:", 1, "transaction open:", connection.in_transaction)
