#!/usr/bin/env python3
"""Retrying an "atomic" transfer double-charges; a request id makes it exactly-once, DDIA 2e Ch. 13.

A reader reasons: "the transfer is a single ACID transaction, so retrying it
after a timed-out COMMIT is safe." It is not. Atomicity guarantees each
transaction is all-or-nothing; it says NOTHING about running that transaction
twice. When the client never hears back from the first COMMIT (the ack was lost)
and retries, the second, equally-atomic transaction moves the money AGAIN.

Scenario 1 (the bug): transfer($11) does UPDATE payer -11, UPDATE payee +11,
COMMIT. Called TWICE -- simulating a client retry after a lost COMMIT ack -- it
moves $22: payer 100 -> 78, payee 0 -> 22. Each run was atomic; two clean runs
are still two transfers.

Scenario 2 (the fix, DDIA Example 13-2): add a requests(request_id) table with a
uniqueness constraint and pass a client-generated id end-to-end. transfer first
INSERTs the request_id INSIDE the same transaction. The retry carries the SAME
id, its INSERT hits the UNIQUE constraint (sqlite3.IntegrityError), the whole
transaction rolls back, and the transfer happens exactly once: payer 100 -> 89,
payee 0 -> 11. Correctness is enforced at the endpoint that owns the operation,
not by the transport -- the end-to-end argument made concrete.

Balances are in whole dollars (integers). Pure standard library (sqlite3),
deterministic (fixed request ids) -> reproducible.
"""
import sqlite3

AMOUNT = 11          # dollars to transfer
PAYER, PAYEE = "alice", "bob"

# A client-generated id, chosen once and reused across the retry so both attempts
# carry the SAME id. Fixed here (not random) so the run is deterministic.
REQUEST_ID = "req-6f1a2b3c-0001"


def seed_accounts(conn):
    """Fresh ledger: payer starts with $100, payee with $0."""
    conn.execute("CREATE TABLE accounts (id TEXT PRIMARY KEY, balance INTEGER)")
    conn.execute("INSERT INTO accounts VALUES (?, ?)", (PAYER, 100))
    conn.execute("INSERT INTO accounts VALUES (?, ?)", (PAYEE, 0))
    conn.commit()


def balances(conn):
    rows = dict(conn.execute("SELECT id, balance FROM accounts").fetchall())
    return rows[PAYER], rows[PAYEE]


# --- Scenario 1: the "atomic" transfer, retried ---------------------------------

def transfer(conn, amount):
    """Move `amount` from payer to payee as ONE atomic transaction.

    All-or-nothing: both UPDATEs commit together or neither does. But nothing
    here makes running it twice a no-op -- a second call is a second transfer.
    """
    conn.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (amount, PAYER))
    conn.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (amount, PAYEE))
    conn.commit()   # the client waits for this ack -- and may never hear it


# --- Scenario 2: the same transfer, guarded by a unique request id ---------------

def transfer_once(conn, request_id, amount):
    """Move `amount` exactly once, keyed by a client-generated request_id.

    The INSERT into `requests` and the two UPDATEs are ONE transaction. A retry
    with the same request_id fails the UNIQUE constraint before any money moves;
    the transaction rolls back and the call is a no-op. Returns True if the
    transfer was applied, False if it was recognized as a duplicate and skipped.
    """
    try:
        # Uniqueness check and the money move are in the same atomic unit.
        conn.execute("INSERT INTO requests (request_id) VALUES (?)", (request_id,))
        conn.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (amount, PAYER))
        conn.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (amount, PAYEE))
        conn.commit()
        return True
    except sqlite3.IntegrityError as exc:
        conn.rollback()   # duplicate request id: undo everything, move no money
        print(f"    caught sqlite3.IntegrityError: {exc}")
        return False


def main():
    line = "-" * 68

    # ===== Scenario 1: atomicity does NOT imply idempotence =====
    print(line)
    print("Scenario 1 -- one 'atomic' transfer, retried after a lost COMMIT ack")
    print(line)
    conn = sqlite3.connect(":memory:", isolation_level=None)
    seed_accounts(conn)
    p, q = balances(conn)
    print(f"start:                 payer({PAYER})={p:>3}   payee({PAYEE})={q:>3}")

    transfer(conn, AMOUNT)   # first attempt -- succeeds on the server
    p, q = balances(conn)
    print(f"after 1st transfer:    payer({PAYER})={p:>3}   payee({PAYEE})={q:>3}")

    # The client's COMMIT ack was lost; it cannot tell success from failure, so
    # it retries the identical transaction.
    transfer(conn, AMOUNT)   # retry -- succeeds AGAIN
    p, q = balances(conn)
    print(f"after retry:           payer({PAYER})={p:>3}   payee({PAYEE})={q:>3}")
    print(f"=> moved ${2 * AMOUNT} instead of ${AMOUNT}: the retry DOUBLE-CHARGED.")
    conn.close()

    # ===== Scenario 2: a unique request id closes it =====
    print()
    print(line)
    print("Scenario 2 -- same transfer keyed by a client-generated request id")
    print(line)
    conn = sqlite3.connect(":memory:", isolation_level=None)
    seed_accounts(conn)
    conn.execute("CREATE TABLE requests (request_id TEXT UNIQUE)")
    conn.commit()
    p, q = balances(conn)
    print(f"start:                 payer({PAYER})={p:>3}   payee({PAYEE})={q:>3}")
    print(f"request id:            {REQUEST_ID}")

    applied = transfer_once(conn, REQUEST_ID, AMOUNT)   # first attempt
    p, q = balances(conn)
    print(f"1st attempt applied={applied}:  payer({PAYER})={p:>3}   payee({PAYEE})={q:>3}")

    # Retry carries the SAME request id.
    applied = transfer_once(conn, REQUEST_ID, AMOUNT)   # retry -- deduplicated
    p, q = balances(conn)
    print(f"retry applied={applied}:       payer({PAYER})={p:>3}   payee({PAYEE})={q:>3}")
    print(f"=> moved ${AMOUNT} exactly once: the retry was recognized and skipped.")
    conn.close()


if __name__ == "__main__":
    main()