import sqlite3
from pathlib import Path

DATA_DIR = Path.home() / ".betlab"
DB_PATH = DATA_DIR / "betlab.db"

SCHEMA = """
CREATE TABLE IF NOT EXISTS bets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    placed_at TEXT NOT NULL,
    sport TEXT NOT NULL DEFAULT 'nba',
    game_date TEXT NOT NULL,
    game_id TEXT,
    event_desc TEXT NOT NULL,
    team_home TEXT,
    team_away TEXT,
    market TEXT NOT NULL,
    player_name TEXT,
    stat_type TEXT,
    line REAL,
    direction TEXT CHECK(direction IN ('over','under',NULL)),
    selection TEXT NOT NULL,
    odds_taken REAL NOT NULL,
    odds_open REAL,
    odds_close REAL,
    stake REAL NOT NULL,
    book TEXT NOT NULL DEFAULT 'superbet_ro',
    model_edge_pct REAL,
    rationale TEXT,
    status TEXT NOT NULL DEFAULT 'open' CHECK(status IN ('open','won','lost','push','void')),
    payout REAL,
    notes TEXT
);

CREATE TABLE IF NOT EXISTS player_stats (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    game_id TEXT NOT NULL,
    game_date TEXT NOT NULL,
    player_name TEXT NOT NULL,
    team TEXT NOT NULL,
    pts INTEGER DEFAULT 0,
    reb INTEGER DEFAULT 0,
    ast INTEGER DEFAULT 0,
    stl INTEGER DEFAULT 0,
    blk INTEGER DEFAULT 0,
    tov INTEGER DEFAULT 0,
    fg3m INTEGER DEFAULT 0,
    minutes REAL DEFAULT 0,
    dnp INTEGER DEFAULT 0,
    UNIQUE(game_id, player_name)
);

CREATE TABLE IF NOT EXISTS game_results (
    game_id TEXT PRIMARY KEY,
    game_date TEXT NOT NULL,
    team_home TEXT NOT NULL,
    team_away TEXT NOT NULL,
    pts_home INTEGER,
    pts_away INTEGER,
    status TEXT DEFAULT 'scheduled'
);
"""


def get_conn() -> sqlite3.Connection:
    DATA_DIR.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(str(DB_PATH))
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    return conn


def init_db():
    with get_conn() as conn:
        conn.executescript(SCHEMA)