"""Fixed-slot reservation interleaving; disposable SQLite database, no UI or load test."""
import json,sqlite3,tempfile
from pathlib import Path
report=[]
with tempfile.TemporaryDirectory() as folder:
 path=Path(folder)/'bookings.db';a=sqlite3.connect(path);b=sqlite3.connect(path)
 a.executescript("""CREATE TABLE reservations(id TEXT PRIMARY KEY,equipment TEXT NOT NULL,slot TEXT NOT NULL,
 status TEXT NOT NULL CHECK(status IN ('active','cancelled')));
 CREATE UNIQUE INDEX one_active_slot ON reservations(equipment,slot) WHERE status='active';""")
 def rows():return a.execute('SELECT * FROM reservations ORDER BY id').fetchall()
 seen_a=a.execute("SELECT id FROM reservations WHERE equipment='P1' AND slot='S1' AND status='active'").fetchall()
 seen_b=b.execute("SELECT id FROM reservations WHERE equipment='P1' AND slot='S1' AND status='active'").fetchall()
 assert seen_a==seen_b==[]
 a.execute("INSERT INTO reservations VALUES('R1','P1','S1','active')");a.commit()
 try:b.execute("INSERT INTO reservations VALUES('R2','P1','S1','active')");raise AssertionError('must conflict')
 except sqlite3.IntegrityError:b.rollback()
 report.append({'case':'Both read empty; A commits then B attempts','aSaw':seen_a,'bSaw':seen_b,'rows':rows()})
 a.execute("UPDATE reservations SET status='cancelled' WHERE id='R1'");a.commit()
 b.execute("INSERT INTO reservations VALUES('R2','P1','S1','active')");b.commit()
 report.append({'case':'Cancel then another reservation','rows':rows()})
 a.execute("INSERT INTO reservations VALUES('R3','P1','S2','active')");a.commit()
 try:
  a.execute('BEGIN IMMEDIATE');a.execute("UPDATE reservations SET status='cancelled' WHERE id='R2'")
  a.execute("INSERT INTO reservations VALUES('R4','P1','S2','active')");raise AssertionError('must conflict')
 except sqlite3.IntegrityError:a.rollback()
 assert a.execute("SELECT status FROM reservations WHERE id='R2'").fetchone()[0]=='active'
 report.append({'case':'Move R2 into occupied S2; rollback preserves original','rows':rows()})
 a.close();b.close()
if __name__=='__main__':print(json.dumps({'sqliteVersion':sqlite3.sqlite_version,'report':report},indent=2))