File size: 2,769 Bytes
1790bde
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
"""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}")