"""SQLite 并发幂等、提交后丢响应、deadline 不撤销事务。"""
import asyncio
from contextlib import closing
from concurrent.futures import ThreadPoolExecutor
import json
from pathlib import Path
import sqlite3
import tempfile
import threading
import time


def transfer(path, operation_id, amount):
    if not isinstance(amount, int) or isinstance(amount, bool) or amount <= 0:
        raise ValueError("positive integer required")
    payload = json.dumps({"amount": amount}, sort_keys=True)
    connection = sqlite3.connect(path, timeout=2, autocommit=True)
    try:
        # SQLite 写者串行化；不把它称为 PostgreSQL 行锁实测。
        connection.execute("BEGIN IMMEDIATE")
        prior = connection.execute(
            "SELECT payload,result FROM operations WHERE id=?", (operation_id,)
        ).fetchone()
        if prior is not None:
            if prior[0] != payload:
                raise ValueError("operation id payload mismatch")
            result = json.loads(prior[1])
        else:
            changed = connection.execute(
                "UPDATE accounts SET balance=balance-? WHERE id='a' AND balance>=?",
                (amount, amount),
            ).rowcount
            if changed != 1:
                raise ValueError("insufficient funds")
            connection.execute("UPDATE accounts SET balance=balance+? WHERE id='b'", (amount,))
            result = dict(connection.execute("SELECT id,balance FROM accounts"))
            connection.execute(
                "INSERT INTO operations VALUES(?,?,?)",
                (operation_id, payload, json.dumps(result, sort_keys=True)),
            )
            # 外部发送由后续投递器执行，不放在事务里面。
            connection.execute("INSERT INTO outbox VALUES(?,?)", (operation_id, payload))
        connection.execute("COMMIT")
        return result
    except BaseException:
        if connection.in_transaction:
            connection.execute("ROLLBACK")
        raise
    finally:
        connection.close()


async def deadline_experiment(path):
    committed = threading.Event()
    release_response = threading.Event()

    def slow_response():
        result = transfer(path, "deadline-op", 10)
        committed.set()
        if not release_response.wait(2):
            raise TimeoutError("test response gate")
        return result

    operation = asyncio.create_task(asyncio.to_thread(slow_response))
    deadline = time.monotonic() + 0.2
    try:
        # 先确认真实提交发生，再制造响应等待耗尽预算。
        async with asyncio.timeout(1):
            while not committed.is_set():
                await asyncio.sleep(0)
        remaining = max(0, deadline - time.monotonic())
        try:
            await asyncio.wait_for(asyncio.shield(operation), timeout=remaining)
        except TimeoutError:
            print("response deadline exceeded; commit already happened")
    finally:
        release_response.set()
        result = await operation  # 保留任务引用并收割，避免遗留后台任务。
    assert result == {"a": 60, "b": 40}
    assert transfer(path, "deadline-op", 10) == result


def main():
    with tempfile.TemporaryDirectory() as directory:
        path = Path(directory) / "bank.sqlite"
        with closing(sqlite3.connect(path, autocommit=True)) as connection:
            connection.executescript("""
                CREATE TABLE accounts(id TEXT PRIMARY KEY,balance INTEGER NOT NULL);
                INSERT INTO accounts VALUES('a',100),('b',0);
                CREATE TABLE operations(id TEXT PRIMARY KEY,payload TEXT,result TEXT);
                CREATE TABLE outbox(id TEXT PRIMARY KEY,payload TEXT);
            """)
        start = threading.Barrier(4)

        def concurrent_retry():
            start.wait(timeout=2)
            return transfer(path, "same-op", 30)

        with ThreadPoolExecutor(max_workers=4) as executor:
            futures = [executor.submit(concurrent_retry) for _ in range(4)]
            results = [future.result(timeout=5) for future in futures]
        assert all(result == {"a": 70, "b": 30} for result in results)
        try:
            transfer(path, "same-op", 20)
        except ValueError as error:
            assert "mismatch" in str(error)
        else:
            raise AssertionError("mismatched payload accepted")
        asyncio.run(deadline_experiment(path))
        with closing(sqlite3.connect(path)) as connection:
            assert connection.execute("SELECT COUNT(*) FROM operations").fetchone()[0] == 2
            assert connection.execute("SELECT COUNT(*) FROM outbox").fetchone()[0] == 2
            print("sqlite:", sqlite3.sqlite_version, "balances:", dict(connection.execute("SELECT * FROM accounts")))


if __name__ == "__main__":
    main()
