database.py

# ============================================================
# database.py — Local SQLite storage (zero setup required)
# ============================================================

import sqlite3
import json
from datetime import datetime
from pathlib import Path

DB_PATH = Path(__file__).parent / "data" / "edges.db"


def get_connection() -> sqlite3.Connection:
    DB_PATH.parent.mkdir(exist_ok=True)
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn


def init_db():
    """Create tables if they don't exist."""
    with get_connection() as conn:
        conn.executescript("""
            CREATE TABLE IF NOT EXISTS edges (
                id          INTEGER PRIMARY KEY AUTOINCREMENT,
                found_at    TEXT NOT NULL,
                game        TEXT NOT NULL,
                sport       TEXT NOT NULL,
                game_time   TEXT NOT NULL,
                market      TEXT NOT NULL,
                side        TEXT NOT NULL,
                soft_book   TEXT NOT NULL,
                soft_odds   INTEGER NOT NULL,
                pinnacle_odds INTEGER NOT NULL,
                true_prob   REAL NOT NULL,
                ev_percent  REAL NOT NULL,
                kelly_pct   REAL NOT NULL
            );

            CREATE TABLE IF NOT EXISTS bet_log (
                id          INTEGER PRIMARY KEY AUTOINCREMENT,
                logged_at   TEXT NOT NULL,
                game        TEXT NOT NULL,
                sport       TEXT NOT NULL,
                market      TEXT NOT NULL,
                side        TEXT NOT NULL,
                soft_book   TEXT NOT NULL,
                odds        INTEGER NOT NULL,
                stake       REAL NOT NULL,
                result      TEXT DEFAULT NULL,  -- 'win', 'loss', 'push', 'pending'
                profit_loss REAL DEFAULT NULL,
                notes       TEXT DEFAULT NULL
            );

            CREATE TABLE IF NOT EXISTS scan_log (
                id          INTEGER PRIMARY KEY AUTOINCREMENT,
                scanned_at  TEXT NOT NULL,
                sports      TEXT NOT NULL,
                events_fetched INTEGER NOT NULL,
                edges_found INTEGER NOT NULL
            );
        """)


def save_edges(edges: list):
    """Save a batch of edges to the database."""
    now = datetime.now().isoformat()
    with get_connection() as conn:
        conn.executemany("""
            INSERT INTO edges
            (found_at, game, sport, game_time, market, side, soft_book,
             soft_odds, pinnacle_odds, true_prob, ev_percent, kelly_pct)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, [
            (now, e.game, e.sport, e.game_time, e.market, e.side,
             e.soft_book, e.soft_odds, e.pinnacle_odds, e.true_prob,
             e.ev_percent, e.kelly_pct)
            for e in edges
        ])


def log_scan(sports: list, events_fetched: int, edges_found: int):
    with get_connection() as conn:
        conn.execute("""
            INSERT INTO scan_log (scanned_at, sports, events_fetched, edges_found)
            VALUES (?, ?, ?, ?)
        """, (datetime.now().isoformat(), json.dumps(sports), events_fetched, edges_found))


def log_bet(game: str, sport: str, market: str, side: str, soft_book: str,
            odds: int, stake: float, notes: str = ""):
    """Record a bet you placed."""
    with get_connection() as conn:
        conn.execute("""
            INSERT INTO bet_log
            (logged_at, game, sport, market, side, soft_book, odds, stake, notes)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (datetime.now().isoformat(), game, sport, market, side,
              soft_book, odds, stake, notes))
    print(f"✓ Bet logged: {side} @ {odds} for ${stake}")


def settle_bet(bet_id: int, result: str, profit_loss: float):
    """Update a bet with its result. result = 'win', 'loss', 'push'"""
    with get_connection() as conn:
        conn.execute("""
            UPDATE bet_log SET result=?, profit_loss=? WHERE id=?
        """, (result, profit_loss, bet_id))
    print(f"✓ Bet #{bet_id} settled: {result} ${profit_loss:+.2f}")


def get_roi_summary() -> dict:
    """Calculate overall ROI from settled bets."""
    with get_connection() as conn:
        row = conn.execute("""
            SELECT
                COUNT(*) as total_bets,
                SUM(CASE WHEN result != 'pending' AND result IS NOT NULL THEN 1 ELSE 0 END) as settled,
                SUM(CASE WHEN result = 'win' THEN 1 ELSE 0 END) as wins,
                SUM(CASE WHEN result = 'loss' THEN 1 ELSE 0 END) as losses,
                SUM(stake) as total_staked,
                SUM(COALESCE(profit_loss, 0)) as total_profit
            FROM bet_log
        """).fetchone()

        if row and row["total_staked"]:
            roi = (row["total_profit"] / row["total_staked"]) * 100
        else:
            roi = 0.0

        return {
            "total_bets": row["total_bets"] or 0,
            "settled": row["settled"] or 0,
            "wins": row["wins"] or 0,
            "losses": row["losses"] or 0,
            "total_staked": row["total_staked"] or 0,
            "total_profit": row["total_profit"] or 0,
            "roi_pct": round(roi, 2),
        }


def get_pending_bets() -> list:
    """Get all unsettled bets."""
    with get_connection() as conn:
        rows = conn.execute("""
            SELECT * FROM bet_log WHERE result IS NULL OR result = 'pending'
            ORDER BY logged_at DESC
        """).fetchall()
        return [dict(r) for r in rows]


def get_recent_edges(limit: int = 50) -> list:
    """Get the most recently found edges."""
    with get_connection() as conn:
        rows = conn.execute("""
            SELECT * FROM edges ORDER BY found_at DESC, ev_percent DESC LIMIT ?
        """, (limit,)).fetchall()
        return [dict(r) for r in rows]