"""Small SQLite implementation under test; no Proof runtime is involved."""

import sqlite3


def connect(database_path):
    # Pin legacy mode on Python >=3.12 so a future default change cannot silently
    # change isolation_level=None semantics. Python 3.10/3.11 have no such option.
    options = {"isolation_level": None}
    if hasattr(sqlite3, "LEGACY_TRANSACTION_CONTROL"):
        options["autocommit"] = sqlite3.LEGACY_TRANSACTION_CONTROL
    db = sqlite3.connect(str(database_path), **options)
    db.execute("PRAGMA foreign_keys = ON")
    db.execute("PRAGMA journal_mode = DELETE")
    db.execute("PRAGMA synchronous = FULL")
    return db


def initialize(db):
    db.executescript("""
        CREATE TABLE accounts (
            account_id TEXT PRIMARY KEY,
            balance_cents INTEGER NOT NULL
                CHECK (typeof(balance_cents) = 'integer' AND balance_cents >= 0)
        );
        CREATE TABLE transfers (
            transfer_id INTEGER PRIMARY KEY,
            source_id TEXT NOT NULL REFERENCES accounts(account_id),
            destination_id TEXT NOT NULL REFERENCES accounts(account_id),
            amount_cents INTEGER NOT NULL
                CHECK (typeof(amount_cents) = 'integer' AND amount_cents > 0)
        );
        INSERT INTO accounts VALUES ('alice', 10000), ('bob', 2500);
    """)


def snapshot(db):
    return {
        "accounts": [list(row) for row in db.execute(
            "SELECT account_id, balance_cents FROM accounts ORDER BY account_id")],
        "transfers": [list(row) for row in db.execute(
            "SELECT transfer_id, source_id, destination_id, amount_cents "
            "FROM transfers ORDER BY transfer_id")],
    }


def _trace(db, events, event, **details):
    events.append({"event": event, "in_transaction": db.in_transaction,
                   "state": snapshot(db), **details})


def _rejected(reason):
    return {"status": "rejected", "reason": reason}


def _amount_invalid(amount_cents):
    return type(amount_cents) is not int or not 1 <= amount_cents <= 1000000


def _inject_record_failure(db, source_id, destination_id, events):
    # Test-only fault seam, never a request field. This executes a real failing
    # SQLite statement AFTER the real debit, before credit/normal record writes.
    _trace(db, events, "inject_invalid_record")
    db.execute(
        "INSERT INTO transfers (source_id, destination_id, amount_cents) "
        "VALUES (?, ?, 0)", (source_id, destination_id))


def transfer_baseline(db, source_id, destination_id, amount_cents,
                      *, fail_after_debit=False, events=None):
    """Bug: debit is autocommitted before destination check/later SQL failure."""
    events = events if events is not None else []
    if _amount_invalid(amount_cents):
        return _rejected("invalid_amount")
    source = db.execute("SELECT balance_cents FROM accounts WHERE account_id = ?",
                        (source_id,)).fetchone()
    if source is None:
        return _rejected("unknown_source")
    if source[0] < amount_cents:
        return _rejected("insufficient_balance")
    try:
        db.execute("UPDATE accounts SET balance_cents = balance_cents - ? "
                   "WHERE account_id = ?", (amount_cents, source_id))
        _trace(db, events, "debit_executed")
        destination = db.execute(
            "SELECT account_id FROM accounts WHERE account_id = ?",
            (destination_id,)).fetchone()
        if destination is None:
            _trace(db, events, "destination_missing_after_debit")
            return _rejected("unknown_destination")
        if fail_after_debit:
            _inject_record_failure(db, source_id, destination_id, events)
        db.execute("UPDATE accounts SET balance_cents = balance_cents + ? "
                   "WHERE account_id = ?", (amount_cents, destination_id))
        db.execute("INSERT INTO transfers (source_id, destination_id, amount_cents) "
                   "VALUES (?, ?, ?)", (source_id, destination_id, amount_cents))
        _trace(db, events, "success_returned")
        return {"status": "accepted", "transfer_id": 1}
    except sqlite3.Error as error:
        _trace(db, events, "database_error_caught", error_type=type(error).__name__,
               error_message=str(error))
        return _rejected("processing_failed")


def transfer_corrected(db, source_id, destination_id, amount_cents,
                       *, fail_after_debit=False, events=None):
    """Validate inside one explicit transaction; rollback caught SQL failures."""
    events = events if events is not None else []
    if _amount_invalid(amount_cents):
        return _rejected("invalid_amount")
    # Contract: caller supplies a connection with no active transaction.
    db.execute("BEGIN IMMEDIATE")
    try:
        source = db.execute("SELECT balance_cents FROM accounts WHERE account_id = ?",
                            (source_id,)).fetchone()
        if source is None:
            db.execute("ROLLBACK")
            return _rejected("unknown_source")
        destination = db.execute(
            "SELECT account_id FROM accounts WHERE account_id = ?",
            (destination_id,)).fetchone()
        if destination is None:
            db.execute("ROLLBACK")
            _trace(db, events, "destination_rejected_before_debit")
            return _rejected("unknown_destination")
        if source[0] < amount_cents:
            db.execute("ROLLBACK")
            return _rejected("insufficient_balance")
        db.execute("UPDATE accounts SET balance_cents = balance_cents - ? "
                   "WHERE account_id = ?", (amount_cents, source_id))
        _trace(db, events, "debit_executed")
        if fail_after_debit:
            _inject_record_failure(db, source_id, destination_id, events)
        db.execute("UPDATE accounts SET balance_cents = balance_cents + ? "
                   "WHERE account_id = ?", (amount_cents, destination_id))
        db.execute("INSERT INTO transfers (source_id, destination_id, amount_cents) "
                   "VALUES (?, ?, ?)", (source_id, destination_id, amount_cents))
        db.execute("COMMIT")
        _trace(db, events, "success_returned")
        return {"status": "accepted", "transfer_id": 1}
    except sqlite3.Error as error:
        _trace(db, events, "database_error_before_rollback",
               error_type=type(error).__name__, error_message=str(error))
        if db.in_transaction:
            db.execute("ROLLBACK")
        _trace(db, events, "database_error_after_rollback")
        return _rejected("processing_failed")


def transfer_reject_all(db, source_id, destination_id, amount_cents,
                        *, fail_after_debit=False, events=None):
    """Deliberately useless implementation for the positive-control check."""
    return _rejected("disabled")