"""Constructed Chapter 11 cases, joins and dispatch batches; Python 3 stdlib only."""
from fractions import Fraction
from pathlib import Path
import csv
import sqlite3

HERE = Path(__file__).resolve().parent
db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")
db.executescript("""
CREATE TABLE cases(case_id TEXT PRIMARY KEY, resolved INTEGER CHECK(resolved IN (0,1) OR resolved IS NULL));
CREATE TABLE messages(message_id TEXT PRIMARY KEY, case_id TEXT NOT NULL REFERENCES cases(case_id));
INSERT INTO cases VALUES ('A',1),('B',0),('C',1),('D',NULL);
INSERT INTO messages VALUES ('A1','A'),('A2','A'),('A3','A'),('B1','B'),('C1','C'),('C2','C');
""")
def one(query): return db.execute(query).fetchone()
case_rate = one("SELECT SUM(resolved)*1.0/COUNT(resolved) FROM cases WHERE case_id IN ('A','B','C')")[0]
joined_rate = one("SELECT SUM(c.resolved)*1.0/COUNT(c.resolved) FROM cases c JOIN messages m USING(case_id)")[0]
assert case_rate == 2/3 and joined_rate == 5/6
assert one("SELECT COUNT(*),COUNT(resolved) FROM cases") == (4,3)
assert one("SELECT COUNT(*) FROM cases c LEFT JOIN messages m USING(case_id)")[0] == 7
assert one("SELECT COUNT(*) FROM cases c LEFT JOIN messages m ON c.case_id=m.case_id WHERE m.message_id IS NOT NULL")[0] == 6
assert one("SELECT COUNT(*) FROM cases c LEFT JOIN messages m ON c.case_id=m.case_id AND m.message_id='A1'")[0] == 4
assert one("SELECT COUNT(*) FROM cases c LEFT JOIN messages m ON c.case_id=m.case_id WHERE m.message_id='A1'")[0] == 1

batches = [
    ('B01','2026-01-05','North',10,1),('B02','2026-01-12','North',20,4),
    ('B03','2026-01-20','South',5,0),('B04','2026-02-03','North',15,3),
    ('B05','2026-02-10','South',10,1),('B06','2026-02-24','South',40,12)]
db.execute("CREATE TABLE dispatches(batch TEXT PRIMARY KEY, date TEXT, region TEXT, orders INTEGER CHECK(orders>0), late INTEGER CHECK(late BETWEEN 0 AND orders))")
db.executemany("INSERT INTO dispatches VALUES (?,?,?,?,?)", batches)
pooled = Fraction(sum(row[4] for row in batches),sum(row[3] for row in batches))
batch_mean = sum((Fraction(row[4],row[3]) for row in batches),Fraction())/len(batches)
assert pooled == Fraction(21,100) and batch_mean == Fraction(3,20)
regions = db.execute("SELECT region,SUM(orders),SUM(late),COUNT(*) FROM dispatches GROUP BY region ORDER BY region").fetchall()
assert regions == [('North',45,8,3),('South',55,13,3)]
with (HERE/'dispatches.csv').open('w',newline='') as file:
    writer=csv.writer(file);writer.writerow(['Batch','Date','Region','Orders','Late']);writer.writerows(batches)
print('CASE RATE 2/3 =',format(case_rate,'.6f'))
print('JOINED MESSAGE-WEIGHTED RATE 5/6 =',format(joined_rate,'.6f'))
print('CASE COUNT / OBSERVED OUTCOME COUNT =',one('SELECT COUNT(*),COUNT(resolved) FROM cases'))
print('LEFT JOIN ROWS / INNER MATCHES =',7,6)
print('FILTER IN ON / FILTER IN WHERE ROWS =',4,1)
print('DISPATCH POOLED RATE =',f'{float(pooled):.2%}')
print('UNWEIGHTED BATCH MEAN =',f'{float(batch_mean):.2%}')
print('REGION orders, late, batches =',regions)
