"""A check that runs before the write lock, against the same check inside it.

WRITERS threads each try to take one place on a slot that holds LIMIT.
Standard library only. Uses a throwaway SQLite file beside this script.
"""
import os
import sqlite3
import sys
import tempfile
import threading
import time

LIMIT = 3
WRITERS = 8
ROUNDS = 100
HERE = os.path.dirname(os.path.abspath(__file__))


def connect(path):
    db = sqlite3.connect(path, timeout=30, isolation_level=None)
    db.execute("PRAGMA journal_mode=WAL")
    return db


def count(db):
    return db.execute("SELECT COUNT(*) FROM booking WHERE slot = 1").fetchone()[0]


def count_then_lock(db, user, window):
    """Wrong order: the count is finished before the lock is taken."""
    taken = count(db)
    time.sleep(window)
    db.execute("BEGIN IMMEDIATE")
    if taken >= LIMIT:
        db.execute("ROLLBACK")
        return False
    db.execute("INSERT INTO booking (slot, user) VALUES (1, ?)", (user,))
    db.execute("COMMIT")
    return True


def lock_then_count(db, user, window):
    """Right order: the count happens while holding the lock."""
    db.execute("BEGIN IMMEDIATE")
    taken = count(db)
    time.sleep(window)
    if taken >= LIMIT:
        db.execute("ROLLBACK")
        return False
    db.execute("INSERT INTO booking (slot, user) VALUES (1, ?)", (user,))
    db.execute("COMMIT")
    return True


def one_round(path, book, window):
    db = connect(path)
    db.execute("DELETE FROM booking")
    gate = threading.Barrier(WRITERS)
    errors = []

    def writer(user):
        conn = connect(path)
        gate.wait()
        try:
            book(conn, user, window)
        except sqlite3.Error as exc:
            errors.append(str(exc))
        finally:
            conn.close()

    threads = [threading.Thread(target=writer, args=(u,)) for u in range(WRITERS)]
    for t in threads:
        t.start()
    for t in threads:
        t.join()
    booked = count(db)
    db.close()
    return booked, errors


def run(path, book, window):
    results = [one_round(path, book, window) for _ in range(ROUNDS)]
    booked = [b for b, _ in results]
    over = sum(b > LIMIT for b in booked)
    errors = sum(len(e) for _, e in results)
    print(f"{book.__name__:16} window={window:<5} overbooked rounds: {over:>3} of {ROUNDS}"
          f"   worst: {max(booked)} on a limit of {LIMIT}   lock errors: {errors}")
    return over, errors


def run_one_at_a_time(path, book):
    """The same writers, one after another: the case a solo test exercises."""
    db = connect(path)
    over = 0
    for _ in range(ROUNDS):
        db.execute("DELETE FROM booking")
        for user in range(WRITERS):
            book(db, user, 0)
        over += count(db) > LIMIT
    db.close()
    print(f"{book.__name__:16} one at a time  overbooked rounds: {over:>3} of {ROUNDS}")
    return over


def main():
    print(f"python {sys.version.split()[0]}, sqlite {sqlite3.sqlite_version}, "
          f"{WRITERS} writers, limit {LIMIT}, {ROUNDS} rounds")
    with tempfile.TemporaryDirectory(dir=HERE) as tmp:
        path = os.path.join(tmp, "demo.sqlite")
        db = connect(path)
        db.execute("CREATE TABLE booking (id INTEGER PRIMARY KEY, slot INT, user INT)")
        db.close()
        assert run_one_at_a_time(path, count_then_lock) == 0, "alone, even the wrong order holds"
        for window in (0, 0.02):
            run(path, count_then_lock, window)
            over, errors = run(path, lock_then_count, window)
            assert over == 0 and errors == 0, "the lock-first order must never overbook"


if __name__ == "__main__":
    main()
