import sqlite3
from pathlib import Path
from tempfile import TemporaryDirectory


def reserve(connection, key, amount):
    if not isinstance(amount, int) or isinstance(amount, bool) or amount <= 0:
        raise ValueError("amount must be a positive integer")
    with connection:
        connection.execute(
            "INSERT INTO reservations (request_key, amount_cents) VALUES (?, ?) "
            "ON CONFLICT(request_key) DO NOTHING", (key, amount)
        )
        saved = connection.execute(
            "SELECT amount_cents FROM reservations WHERE request_key = ?", (key,)
        ).fetchone()[0]
        if saved != amount:
            raise ValueError("idempotency key reused with a different amount")
        connection.execute(
            "INSERT INTO outbox (request_key, event_type) VALUES (?, ?) "
            "ON CONFLICT(request_key) DO NOTHING", (key, "reservation.created")
        )
    return saved


with TemporaryDirectory() as directory:
    path = Path(directory) / "proposal.sqlite"
    with sqlite3.connect(path) as connection:
        connection.executescript("""
            CREATE TABLE reservations (
                request_key TEXT PRIMARY KEY, amount_cents INTEGER NOT NULL
            );
            CREATE TABLE outbox (
                request_key TEXT PRIMARY KEY, event_type TEXT NOT NULL,
                FOREIGN KEY(request_key) REFERENCES reservations(request_key)
            );
        """)
        connection.execute("PRAGMA foreign_keys = ON")
        assert reserve(connection, "request-1", 1200) == 1200
        assert reserve(connection, "request-1", 1200) == 1200
        try:
            reserve(connection, "request-1", 9999)
            raise AssertionError("conflicting request accepted")
        except ValueError:
            pass
        connection.executescript("""
            CREATE TRIGGER reject_demo BEFORE INSERT ON outbox
            WHEN NEW.request_key = 'failed-request'
            BEGIN SELECT RAISE(ABORT, 'injected failure'); END;
        """)
        try:
            reserve(connection, "failed-request", 800)
            raise AssertionError("injected failure did not abort")
        except sqlite3.IntegrityError:
            pass
        assert connection.execute(
            "SELECT count(*) FROM reservations WHERE request_key = ?", ("failed-request",)
        ).fetchone()[0] == 0
    connection.close()
    # Reopen to verify that deduplication is persisted, not held in a process cache.
    with sqlite3.connect(path) as reopened:
        assert reserve(reopened, "request-1", 1200) == 1200
        assert reopened.execute("SELECT count(*) FROM reservations").fetchone()[0] == 1
        assert reopened.execute("SELECT count(*) FROM outbox").fetchone()[0] == 1
    reopened.close()
print("OK: replay persisted once; conflicting request rejected")