"""SQLite schema + helpers. Single-file DB, zero-config.""" from __future__ import annotations import sqlite3 from contextlib import contextmanager from config import DB_PATH SCHEMA = """ CREATE TABLE IF NOT EXISTS teams ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE NOT NULL, confederation TEXT, fifa_rank INTEGER, elo REAL, attack REAL, defence REAL, group_label TEXT -- A..L for the 48 WC qualifiers, else NULL ); CREATE TABLE IF NOT EXISTS matches ( id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT NOT NULL, home_team TEXT NOT NULL, away_team TEXT NOT NULL, home_score INTEGER, away_score INTEGER, tournament TEXT, city TEXT, country TEXT, neutral INTEGER, stage TEXT -- 'group' / 'R32' ... only for WC 2026 fixtures ); CREATE INDEX IF NOT EXISTS idx_matches_date ON matches(date); CREATE INDEX IF NOT EXISTS idx_matches_tourn ON matches(tournament); CREATE TABLE IF NOT EXISTS predictions ( id INTEGER PRIMARY KEY AUTOINCREMENT, home_team TEXT NOT NULL, away_team TEXT NOT NULL, neutral INTEGER DEFAULT 1, p_home REAL, p_draw REAL, p_away REAL, exp_home REAL, exp_away REAL, top_scores TEXT, -- JSON [[h,a,prob],...] grid TEXT, -- JSON 2D matrix over25 REAL, btts REAL, model_run_id INTEGER, UNIQUE(home_team, away_team, neutral, model_run_id) ); CREATE TABLE IF NOT EXISTS simulations ( run_id INTEGER, team TEXT, group_label TEXT, p_group_win REAL, p_advance REAL, p_r16 REAL, p_qf REAL, p_sf REAL, p_final REAL, p_win REAL, exp_points REAL, PRIMARY KEY (run_id, team) ); CREATE TABLE IF NOT EXISTS model_runs ( id INTEGER PRIMARY KEY AUTOINCREMENT, model_name TEXT, xi REAL, trained_at TEXT, n_matches INTEGER, rps REAL, brier REAL, logloss REAL, n_sims INTEGER, notes TEXT ); CREATE TABLE IF NOT EXISTS calibration ( run_id INTEGER, bin_lo REAL, bin_hi REAL, predicted REAL, observed REAL, count INTEGER ); """ def connect() -> sqlite3.Connection: conn = sqlite3.connect(DB_PATH) conn.row_factory = sqlite3.Row conn.execute("PRAGMA journal_mode=WAL;") return conn def init_db() -> None: with connect() as conn: conn.executescript(SCHEMA) @contextmanager def cursor(): conn = connect() try: yield conn.cursor() conn.commit() finally: conn.close() if __name__ == "__main__": init_db() print(f"DB initialised at {DB_PATH}")