"""
db.py — SQLite schema and query helpers for Solitaire Hockey.

Schema
------
teams           — team metadata (name, city, tricode); one row per franchise
team_seasons    — per-season membership: which conference/division a team was in
schedule        — one row per scheduled game (may or may not be played yet)
games           — one row per completed simulated game
game_skaters    — one row per skater who appeared in a game (goals/assists/PIM/SOG)
game_goalies    — one row per goalie who appeared in a game (SA/GA/decision)
game_goals      — one row per goal (scorer + up to 2 assists)
game_penalties  — one row per penalty event

League structure notes
----------------------
Division/conference membership is stored per-season in team_seasons, since
franchises move, fold, and expand across eras. Populate this table before
generating a schedule so standings queries can group correctly.

No loser point. No shootout. Two points for a win, zero for a loss.
OT column records whether the game was decided in overtime (flavour only).
"""

import sqlite3
import json
from contextlib import contextmanager


# ---------------------------------------------------------------------------
# Connection helper
# ---------------------------------------------------------------------------

@contextmanager
def get_conn(db_path):
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA foreign_keys=ON")
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


# ---------------------------------------------------------------------------
# Schema
# ---------------------------------------------------------------------------

SCHEMA = """
-- -----------------------------------------------------------------------
-- Teams and league structure
-- -----------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS teams (
    tricode         TEXT PRIMARY KEY,          -- e.g. TOR, OTT, MTL
    city            TEXT NOT NULL,             -- e.g. Toronto
    name            TEXT NOT NULL,             -- e.g. Maple Leafs
    full_name       TEXT NOT NULL              -- e.g. Toronto Maple Leafs
);

CREATE TABLE IF NOT EXISTS team_seasons (
    tricode         TEXT NOT NULL REFERENCES teams(tricode),
    season          TEXT NOT NULL,             -- e.g. 20012002
    conference      TEXT NOT NULL,             -- e.g. Eastern, Western
    division        TEXT NOT NULL,             -- e.g. Northeast, Atlantic
    PRIMARY KEY (tricode, season)
);

-- -----------------------------------------------------------------------
-- Schedule
-- -----------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS schedule (
    schedule_id     INTEGER PRIMARY KEY AUTOINCREMENT,
    season          TEXT NOT NULL,
    game_number     INTEGER NOT NULL,          -- within-season sequence number
    game_date       TEXT NOT NULL,             -- YYYY-MM-DD (planned date)
    home_tricode    TEXT NOT NULL REFERENCES teams(tricode),
    away_tricode    TEXT NOT NULL REFERENCES teams(tricode),
    game_id         TEXT REFERENCES games(game_id),  -- NULL until played
    UNIQUE (season, game_number)
);

CREATE INDEX IF NOT EXISTS idx_schedule_season   ON schedule(season);
CREATE INDEX IF NOT EXISTS idx_schedule_date     ON schedule(game_date);
CREATE INDEX IF NOT EXISTS idx_schedule_home     ON schedule(home_tricode);
CREATE INDEX IF NOT EXISTS idx_schedule_away     ON schedule(away_tricode);
CREATE INDEX IF NOT EXISTS idx_team_seasons      ON team_seasons(season);

-- -----------------------------------------------------------------------
-- Games
-- -----------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS games (
    game_id         TEXT PRIMARY KEY,          -- YYYYMMDD-HOME-AWAY
    game_date       TEXT NOT NULL,             -- YYYY-MM-DD
    season          TEXT NOT NULL,             -- e.g. 20012002
    game_number     INTEGER,                   -- from schedule; NULL if unscheduled
    home_tricode    TEXT NOT NULL,
    away_tricode    TEXT NOT NULL,
    home_team_key   TEXT NOT NULL,             -- e.g. TOR_20012002
    away_team_key   TEXT NOT NULL,
    home_score      INTEGER NOT NULL,
    away_score      INTEGER NOT NULL,
    home_goalie_id  INTEGER,
    away_goalie_id  INTEGER,
    home_shots      INTEGER,
    away_shots      INTEGER,
    overtime        INTEGER NOT NULL DEFAULT 0, -- 1 if decided in OT (no loser point)
    playoff         INTEGER NOT NULL DEFAULT 0, -- 1 if playoff game
    ot_periods      INTEGER NOT NULL DEFAULT 0, -- number of OT periods played
    log             TEXT,                      -- JSON array of log entries
    created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS game_skaters (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    game_id         TEXT NOT NULL REFERENCES games(game_id),
    player_id       INTEGER NOT NULL,
    player_name     TEXT NOT NULL,
    team_tricode    TEXT NOT NULL,
    side            TEXT NOT NULL CHECK(side IN ('home','away')),
    goals           INTEGER NOT NULL DEFAULT 0,
    assists         INTEGER NOT NULL DEFAULT 0,
    shots           INTEGER NOT NULL DEFAULT 0,
    pim             INTEGER NOT NULL DEFAULT 0,
    pp_goals        INTEGER NOT NULL DEFAULT 0,
    sh_goals        INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS game_goalies (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    game_id         TEXT NOT NULL REFERENCES games(game_id),
    player_id       INTEGER NOT NULL,
    player_name     TEXT NOT NULL,
    team_tricode    TEXT NOT NULL,
    side            TEXT NOT NULL CHECK(side IN ('home','away')),
    shots_against   INTEGER NOT NULL DEFAULT 0,
    goals_against   INTEGER NOT NULL DEFAULT 0,
    decision        TEXT CHECK(decision IN ('W','L',NULL))  -- no OT loss distinction
);

CREATE TABLE IF NOT EXISTS game_goals (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    game_id         TEXT NOT NULL REFERENCES games(game_id),
    team_tricode    TEXT NOT NULL,
    side            TEXT NOT NULL CHECK(side IN ('home','away')),
    scorer_id       INTEGER NOT NULL,
    scorer_name     TEXT NOT NULL,
    assist1_id      INTEGER,
    assist1_name    TEXT,
    assist2_id      INTEGER,
    assist2_name    TEXT,
    goal_type       TEXT NOT NULL DEFAULT 'EV'  -- EV, PP, SH
);

CREATE TABLE IF NOT EXISTS game_penalties (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    game_id         TEXT NOT NULL REFERENCES games(game_id),
    team_tricode    TEXT NOT NULL,
    side            TEXT NOT NULL CHECK(side IN ('home','away')),
    player_id       INTEGER,
    player_name     TEXT NOT NULL,
    penalty_type    TEXT NOT NULL,             -- minor, major, misconduct, etc.
    coincidental    INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX IF NOT EXISTS idx_games_date        ON games(game_date);
CREATE INDEX IF NOT EXISTS idx_games_season      ON games(season);
CREATE INDEX IF NOT EXISTS idx_games_home        ON games(home_tricode);
CREATE INDEX IF NOT EXISTS idx_games_away        ON games(away_tricode);
CREATE INDEX IF NOT EXISTS idx_skaters_player    ON game_skaters(player_id);
CREATE INDEX IF NOT EXISTS idx_skaters_game      ON game_skaters(game_id);
CREATE INDEX IF NOT EXISTS idx_goalies_player    ON game_goalies(player_id);
CREATE INDEX IF NOT EXISTS idx_goals_scorer      ON game_goals(scorer_id);
CREATE INDEX IF NOT EXISTS idx_goals_game        ON game_goals(game_id);
CREATE INDEX IF NOT EXISTS idx_penalties_game    ON game_penalties(game_id);
"""


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


# ---------------------------------------------------------------------------
# Save game
# ---------------------------------------------------------------------------

def save_game(db_path, data):
    """
    Persist a completed game.

    Expected payload (from JS client):
    {
      game_id:      "20020112-TOR-OTT",
      game_date:    "2002-01-12",
      season:       "20012002",
      home_tricode: "TOR",
      away_tricode: "OTT",
      home_team_key: "TOR_20012002",
      away_team_key: "OTT_20012002",
      home_score:   3,
      away_score:   2,
      overtime:     false,
      game_number:  42,          -- optional; from schedule
      home_shots:   27,
      away_shots:   29,
      home_goalie:  { player_id: 123, name: "Curtis Joseph" },
      away_goalie:  { player_id: 456, name: "Patrick Lalime" },
      log:          [...],
      skaters: [
        { player_id, name, team_tricode, side, goals, assists, shots, pim, pp_goals, sh_goals },
        ...
      ],
      goals: [
        { team_tricode, side, scorer_id, scorer_name,
          assist1_id, assist1_name, assist2_id, assist2_name, goal_type },
        ...
      ],
      penalties: [
        { team_tricode, side, player_id, player_name, penalty_type, coincidental },
        ...
      ]
    }
    """
    game_id = data.get('game_id', '').strip()
    if not game_id:
        raise ValueError("game_id is required")

    with get_conn(db_path) as conn:
        # Check for duplicate
        existing = conn.execute(
            "SELECT game_id FROM games WHERE game_id = ?", (game_id,)
        ).fetchone()
        if existing:
            raise ValueError(f"Game {game_id} already exists")

        # Determine goalie decisions
        home_score = data['home_score']
        away_score = data['away_score']
        overtime   = 1 if data.get('overtime') else 0

        if home_score > away_score:
            home_decision = 'W'; away_decision = 'L'
        elif away_score > home_score:
            away_decision = 'W'; home_decision = 'L'
        else:
            home_decision = None; away_decision = None

        # games row
        conn.execute("""
            INSERT INTO games
                (game_id, game_date, season, game_number, home_tricode, away_tricode,
                 home_team_key, away_team_key, home_score, away_score,
                 home_goalie_id, away_goalie_id, home_shots, away_shots,
                 overtime, playoff, ot_periods, log)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
        """, (
            game_id,
            data.get('game_date'),
            data.get('season'),
            data.get('game_number'),
            data['home_tricode'],
            data['away_tricode'],
            data.get('home_team_key'),
            data.get('away_team_key'),
            home_score,
            away_score,
            data.get('home_goalie', {}).get('player_id'),
            data.get('away_goalie', {}).get('player_id'),
            data.get('home_shots'),
            data.get('away_shots'),
            overtime,
            1 if data.get('playoff') else 0,
            data.get('ot_periods', 0),
            json.dumps(data.get('log', [])),
        ))

        # goalies
        for side, key, decision in [
            ('home', 'home_goalie', home_decision),
            ('away', 'away_goalie', away_decision),
        ]:
            g = data.get(key)
            if g:
                sa = data.get(f'{side}_shots', 0)
                ga = away_score if side == 'home' else home_score
                conn.execute("""
                    INSERT INTO game_goalies
                        (game_id, player_id, player_name, team_tricode, side,
                         shots_against, goals_against, decision)
                    VALUES (?,?,?,?,?,?,?,?)
                """, (
                    game_id,
                    g.get('player_id'),
                    g.get('name'),
                    data[f'{side}_tricode'],
                    side,
                    sa,
                    ga,
                    decision,
                ))

        # skaters
        for s in data.get('skaters', []):
            conn.execute("""
                INSERT INTO game_skaters
                    (game_id, player_id, player_name, team_tricode, side,
                     goals, assists, shots, pim, pp_goals, sh_goals)
                VALUES (?,?,?,?,?,?,?,?,?,?,?)
            """, (
                game_id,
                s.get('player_id'),
                s.get('name'),
                s.get('team_tricode'),
                s.get('side'),
                s.get('goals', 0),
                s.get('assists', 0),
                s.get('shots', 0),
                s.get('pim', 0),
                s.get('pp_goals', 0),
                s.get('sh_goals', 0),
            ))

        # goals
        for g in data.get('goals', []):
            conn.execute("""
                INSERT INTO game_goals
                    (game_id, team_tricode, side, scorer_id, scorer_name,
                     assist1_id, assist1_name, assist2_id, assist2_name, goal_type)
                VALUES (?,?,?,?,?,?,?,?,?,?)
            """, (
                game_id,
                g.get('team_tricode'),
                g.get('side'),
                g.get('scorer_id'),
                g.get('scorer_name'),
                g.get('assist1_id'),
                g.get('assist1_name'),
                g.get('assist2_id'),
                g.get('assist2_name'),
                g.get('goal_type', 'EV'),
            ))

        # penalties
        for p in data.get('penalties', []):
            conn.execute("""
                INSERT INTO game_penalties
                    (game_id, team_tricode, side, player_id, player_name,
                     penalty_type, coincidental)
                VALUES (?,?,?,?,?,?,?)
            """, (
                game_id,
                p.get('team_tricode'),
                p.get('side'),
                p.get('player_id'),
                p.get('player_name'),
                p.get('penalty_type'),
                1 if p.get('coincidental') else 0,
            ))

        # Auto-link to schedule entry matching date + teams + season
        conn.execute("""
            UPDATE schedule SET game_id = ?
            WHERE game_id IS NULL
            AND season       = ?
            AND game_date    = ?
            AND home_tricode = ?
            AND away_tricode = ?
        """, (
            game_id,
            data.get('season'),
            data.get('game_date'),
            data['home_tricode'],
            data['away_tricode'],
        ))

    return game_id


# ---------------------------------------------------------------------------
# Query helpers
# ---------------------------------------------------------------------------

def _row_to_dict(row):
    return dict(row) if row else None

def _rows_to_list(rows):
    return [dict(r) for r in rows]


def get_games(db_path, season=None, team=None, limit=50):
    """List games with optional filters."""
    sql    = "SELECT * FROM games WHERE 1=1"
    params = []
    if season:
        sql += " AND season = ?"
        params.append(season)
    if team:
        sql += " AND (home_tricode = ? OR away_tricode = ?)"
        params += [team, team]
    sql += " ORDER BY game_date DESC, created_at DESC LIMIT ?"
    params.append(limit)

    with get_conn(db_path) as conn:
        rows = conn.execute(sql, params).fetchall()
    return _rows_to_list(rows)


def get_game(db_path, game_id):
    """Full game detail including goals, penalties, and skater lines."""
    with get_conn(db_path) as conn:
        game = _row_to_dict(
            conn.execute("SELECT * FROM games WHERE game_id = ?", (game_id,)).fetchone()
        )
        if not game:
            return None
        if game.get('log'):
            game['log'] = json.loads(game['log'])

        game['skaters']   = _rows_to_list(conn.execute(
            "SELECT * FROM game_skaters WHERE game_id = ? ORDER BY side, goals DESC, assists DESC",
            (game_id,)
        ).fetchall())

        game['goalies']   = _rows_to_list(conn.execute(
            "SELECT * FROM game_goalies WHERE game_id = ?", (game_id,)
        ).fetchall())

        game['goals']     = _rows_to_list(conn.execute(
            "SELECT * FROM game_goals WHERE game_id = ?", (game_id,)
        ).fetchall())

        game['penalties'] = _rows_to_list(conn.execute(
            "SELECT * FROM game_penalties WHERE game_id = ?", (game_id,)
        ).fetchall())

    return game


def get_player_stats(db_path, player_id, season=None):
    """Aggregated simulated stats for a skater across saved games."""
    season_join = "JOIN games g ON g.game_id = s.game_id AND g.season = ?" if season else \
                  "JOIN games g ON g.game_id = s.game_id"
    params = [player_id]
    if season:
        params = [season, player_id]

    with get_conn(db_path) as conn:
        row = conn.execute(f"""
            SELECT
                s.player_id,
                s.player_name,
                s.team_tricode,
                COUNT(*)            AS gp,
                SUM(s.goals)        AS goals,
                SUM(s.assists)      AS assists,
                SUM(s.goals + s.assists) AS points,
                SUM(s.shots)        AS shots,
                SUM(s.pim)          AS pim,
                SUM(s.pp_goals)     AS pp_goals,
                SUM(s.sh_goals)     AS sh_goals
            FROM game_skaters s
            {season_join}
            WHERE s.player_id = ?
            GROUP BY s.player_id, s.player_name, s.team_tricode
        """, params).fetchone()
    return _row_to_dict(row)


def get_team_stats(db_path, tricode, season=None):
    """
    Win/loss record and goal totals for a team.
    Points: 2 for a win, 0 for a loss. No loser point. No shootout.
    Tiebreaker order: points -> wins -> GF.
    """
    season_clause = "AND season = :season" if season else ""
    params = {'t': tricode}
    if season:
        params['season'] = season

    with get_conn(db_path) as conn:
        row = conn.execute(f"""
            SELECT
                COUNT(*)  AS gp,
                SUM(CASE WHEN home_tricode = :t AND home_score > away_score THEN 1
                         WHEN away_tricode = :t AND away_score > home_score THEN 1
                         ELSE 0 END)                                          AS wins,
                SUM(CASE WHEN home_tricode = :t AND home_score < away_score THEN 1
                         WHEN away_tricode = :t AND away_score < home_score THEN 1
                         ELSE 0 END)                                          AS losses,
                SUM(CASE WHEN home_tricode = :t AND home_score > away_score THEN 2
                         WHEN away_tricode = :t AND away_score > home_score THEN 2
                         WHEN home_score = away_score THEN 1
                         ELSE 0 END)                                          AS points,
                SUM(CASE WHEN home_score = away_score THEN 1
                         ELSE 0 END)                                          AS ties,
                SUM(CASE WHEN home_tricode = :t THEN home_score
                         ELSE away_score END)                                 AS gf,
                SUM(CASE WHEN home_tricode = :t THEN away_score
                         ELSE home_score END)                                 AS ga,
                SUM(CASE WHEN home_tricode = :t THEN home_score - away_score
                         ELSE away_score - home_score END)                    AS goal_diff
            FROM games
            WHERE (home_tricode = :t OR away_tricode = :t)
            {season_clause}
        """, params).fetchone()
    return _row_to_dict(row)


def get_standings(db_path, season, conference=None, division=None):
    """
    Full standings for a season, optionally filtered to a conference or division.
    Sorted by: points DESC, wins DESC, gf DESC.
    Requires team_seasons to be populated.
    """
    filters = ["g.season = :season"]
    params  = {"season": season}
    if conference:
        filters.append("ts.conference = :conference")
        params["conference"] = conference
    if division:
        filters.append("ts.division = :division")
        params["division"] = division

    where = " AND ".join(filters)

    with get_conn(db_path) as conn:
        rows = conn.execute(f"""
            SELECT
                t.tricode,
                t.full_name,
                ts.conference,
                ts.division,
                COUNT(DISTINCT g.game_id)                                     AS gp,
                SUM(CASE WHEN g.home_tricode = t.tricode AND g.home_score > g.away_score THEN 1
                         WHEN g.away_tricode = t.tricode AND g.away_score > g.home_score THEN 1
                         ELSE 0 END)                                          AS wins,
                SUM(CASE WHEN g.home_tricode = t.tricode AND g.home_score < g.away_score THEN 1
                         WHEN g.away_tricode = t.tricode AND g.away_score < g.home_score THEN 1
                         ELSE 0 END)                                          AS losses,
                SUM(CASE WHEN g.home_score = g.away_score THEN 1
                         ELSE 0 END)                                          AS ties,
                SUM(CASE WHEN g.home_tricode = t.tricode AND g.home_score > g.away_score THEN 2
                         WHEN g.away_tricode = t.tricode AND g.away_score > g.home_score THEN 2
                         WHEN g.home_score = g.away_score THEN 1
                         ELSE 0 END)                                          AS points,
                SUM(CASE WHEN g.home_tricode = t.tricode THEN g.home_score
                         ELSE g.away_score END)                               AS gf,
                SUM(CASE WHEN g.home_tricode = t.tricode THEN g.away_score
                         ELSE g.home_score END)                               AS ga
            FROM teams t
            JOIN team_seasons ts ON ts.tricode = t.tricode AND ts.season = :season
            LEFT JOIN games g ON (g.home_tricode = t.tricode OR g.away_tricode = t.tricode)
                              AND g.season = :season
            WHERE {where}
            GROUP BY t.tricode
            ORDER BY points DESC, wins DESC, gf DESC
        """, params).fetchall()
    return _rows_to_list(rows)


# ---------------------------------------------------------------------------
# Team / season metadata helpers
# ---------------------------------------------------------------------------

def upsert_team(db_path, tricode, city, name, full_name):
    """Insert or replace a team metadata row."""
    with get_conn(db_path) as conn:
        conn.execute("""
            INSERT INTO teams (tricode, city, name, full_name)
            VALUES (?, ?, ?, ?)
            ON CONFLICT(tricode) DO UPDATE SET
                city=excluded.city, name=excluded.name, full_name=excluded.full_name
        """, (tricode, city, name, full_name))


def upsert_team_season(db_path, tricode, season, conference, division):
    """Insert or replace a team's season membership."""
    with get_conn(db_path) as conn:
        conn.execute("""
            INSERT INTO team_seasons (tricode, season, conference, division)
            VALUES (?, ?, ?, ?)
            ON CONFLICT(tricode, season) DO UPDATE SET
                conference=excluded.conference, division=excluded.division
        """, (tricode, season, conference, division))


def get_team(db_path, tricode):
    """Fetch team metadata."""
    with get_conn(db_path) as conn:
        return _row_to_dict(
            conn.execute("SELECT * FROM teams WHERE tricode = ?", (tricode,)).fetchone()
        )


def get_teams_for_season(db_path, season, conference=None, division=None):
    """List all teams in a season, optionally filtered."""
    sql    = "SELECT t.*, ts.conference, ts.division FROM teams t JOIN team_seasons ts ON ts.tricode = t.tricode WHERE ts.season = ?"
    params = [season]
    if conference:
        sql += " AND ts.conference = ?"
        params.append(conference)
    if division:
        sql += " AND ts.division = ?"
        params.append(division)
    sql += " ORDER BY ts.conference, ts.division, t.full_name"
    with get_conn(db_path) as conn:
        return _rows_to_list(conn.execute(sql, params).fetchall())


# ---------------------------------------------------------------------------
# Schedule helpers
# ---------------------------------------------------------------------------

def add_schedule_game(db_path, season, game_number, game_date, home_tricode, away_tricode):
    """Add a single game to the schedule."""
    with get_conn(db_path) as conn:
        conn.execute("""
            INSERT OR IGNORE INTO schedule
                (season, game_number, game_date, home_tricode, away_tricode)
            VALUES (?, ?, ?, ?, ?)
        """, (season, game_number, game_date, home_tricode, away_tricode))


def get_schedule(db_path, season, team=None, unplayed_only=False):
    """Fetch schedule for a season, optionally filtered."""
    sql    = "SELECT * FROM schedule WHERE season = ?"
    params = [season]
    if team:
        sql += " AND (home_tricode = ? OR away_tricode = ?)"
        params += [team, team]
    if unplayed_only:
        sql += " AND game_id IS NULL"
    sql += " ORDER BY game_number"
    with get_conn(db_path) as conn:
        return _rows_to_list(conn.execute(sql, params).fetchall())


def mark_schedule_played(db_path, season, game_number, game_id):
    """Link a completed game back to its schedule entry."""
    with get_conn(db_path) as conn:
        conn.execute("""
            UPDATE schedule SET game_id = ?
            WHERE season = ? AND game_number = ?
        """, (game_id, season, game_number))

# ---------------------------------------------------------------------------
# Stats page queries
# ---------------------------------------------------------------------------

def get_seasons(db_path):
    """Return all seasons that have at least one saved game, most recent first."""
    with get_conn(db_path) as conn:
        rows = conn.execute(
            "SELECT DISTINCT season FROM games ORDER BY season DESC"
        ).fetchall()
    return [r['season'] for r in rows]


def get_results(db_path, season=None, team=None, limit=100):
    """Games list for the scores page, with team names from TEAMS if available."""
    sql    = "SELECT * FROM games WHERE 1=1"
    params = []
    if season:
        sql += " AND season = ?"
        params.append(season)
    if team:
        sql += " AND (home_tricode = ? OR away_tricode = ?)"
        params += [team, team]
    sql += " ORDER BY game_date DESC, created_at DESC LIMIT ?"
    params.append(limit)
    with get_conn(db_path) as conn:
        rows = conn.execute(sql, params).fetchall()
    return _rows_to_list(rows)


def get_team_roster_stats(db_path, team_key):
    """
    Aggregated skater stats for a team-season (e.g. 'TOR_20012002').
    Returns one row per player sorted by points desc.
    """
    # team_key is like TOR_20012002 — split to tricode + season
    parts   = team_key.split('_', 1)
    tricode = parts[0]
    season  = parts[1] if len(parts) > 1 else None

    params = [tricode]
    season_clause = ""
    if season:
        season_clause = "AND g.season = ?"
        params.append(season)

    with get_conn(db_path) as conn:
        rows = conn.execute(f"""
            SELECT
                s.player_id,
                s.player_name,
                COUNT(DISTINCT s.game_id)        AS gp,
                SUM(s.goals)                     AS goals,
                SUM(s.assists)                   AS assists,
                SUM(s.goals + s.assists)         AS points,
                SUM(s.shots)                     AS shots,
                SUM(s.pim)                       AS pim,
                SUM(s.pp_goals)                  AS pp_goals,
                SUM(s.sh_goals)                  AS sh_goals,
                CASE WHEN SUM(s.shots) > 0
                     THEN ROUND(CAST(SUM(s.goals) AS FLOAT) / SUM(s.shots) * 100, 1)
                     ELSE 0.0 END                AS shoot_pct
            FROM game_skaters s
            JOIN games g ON g.game_id = s.game_id
            WHERE s.team_tricode = ?
            {season_clause}
            GROUP BY s.player_id, s.player_name
            ORDER BY points DESC, goals DESC, assists DESC
        """, params).fetchall()

        # Goalies for this team-season
        goalie_rows = conn.execute(f"""
            SELECT
                g2.player_id,
                g2.player_name,
                COUNT(DISTINCT g2.game_id)       AS gp,
                SUM(g2.shots_against)            AS sa,
                SUM(g2.goals_against)            AS ga,
                SUM(g2.shots_against - g2.goals_against) AS saves,
                CASE WHEN SUM(g2.shots_against) > 0
                     THEN ROUND(CAST(SUM(g2.shots_against - g2.goals_against) AS FLOAT)
                          / SUM(g2.shots_against), 4)
                     ELSE NULL END               AS sv_pct,
                ROUND(
                    CAST(SUM(g2.goals_against) AS FLOAT) * 60.0
                    / SUM(CASE WHEN g.home_score = g.away_score THEN 65.0 ELSE 60.0 END),
                2)                               AS gaa,
                SUM(CASE WHEN g2.decision = 'W' THEN 1 ELSE 0 END) AS wins,
                SUM(CASE WHEN g2.decision = 'L' THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN g2.decision = 'T' THEN 1 ELSE 0 END) AS ties
            FROM game_goalies g2
            JOIN games g ON g.game_id = g2.game_id
            WHERE g2.team_tricode = ?
            {season_clause}
            GROUP BY g2.player_id, g2.player_name
            ORDER BY wins DESC, sv_pct DESC
        """, params).fetchall()

    return {
        'skaters': _rows_to_list(rows),
        'goalies':  _rows_to_list(goalie_rows),
    }


def get_player_career(db_path, player_id):
    """
    Full career stats for a player across all seasons.
    Returns per-season aggregates plus a game log.
    """
    with get_conn(db_path) as conn:
        # Per-season totals
        season_rows = conn.execute("""
            SELECT
                g.season,
                s.team_tricode,
                s.player_name,
                COUNT(DISTINCT s.game_id)        AS gp,
                SUM(s.goals)                     AS goals,
                SUM(s.assists)                   AS assists,
                SUM(s.goals + s.assists)         AS points,
                SUM(s.shots)                     AS shots,
                SUM(s.pim)                       AS pim,
                SUM(s.pp_goals)                  AS pp_goals,
                SUM(s.sh_goals)                  AS sh_goals,
                CASE WHEN SUM(s.shots) > 0
                     THEN ROUND(CAST(SUM(s.goals) AS FLOAT) / SUM(s.shots) * 100, 1)
                     ELSE 0.0 END                AS shoot_pct
            FROM game_skaters s
            JOIN games g ON g.game_id = s.game_id
            WHERE s.player_id = ?
            GROUP BY g.season, s.team_tricode
            ORDER BY g.season DESC
        """, (player_id,)).fetchall()

        # Career totals
        career_row = conn.execute("""
            SELECT
                s.player_id,
                s.player_name,
                COUNT(DISTINCT s.game_id)        AS gp,
                SUM(s.goals)                     AS goals,
                SUM(s.assists)                   AS assists,
                SUM(s.goals + s.assists)         AS points,
                SUM(s.shots)                     AS shots,
                SUM(s.pim)                       AS pim,
                SUM(s.pp_goals)                  AS pp_goals,
                SUM(s.sh_goals)                  AS sh_goals
            FROM game_skaters s
            WHERE s.player_id = ?
            GROUP BY s.player_id, s.player_name
        """, (player_id,)).fetchone()

        # Game log (most recent first)
        game_log = conn.execute("""
            SELECT
                g.game_id,
                g.game_date,
                g.season,
                s.team_tricode,
                CASE WHEN s.team_tricode = g.home_tricode
                     THEN g.away_tricode ELSE g.home_tricode END AS opponent,
                CASE WHEN s.team_tricode = g.home_tricode THEN 'H' ELSE 'A' END AS ha,
                CASE WHEN s.team_tricode = g.home_tricode
                     THEN g.home_score || '-' || g.away_score
                     ELSE g.away_score || '-' || g.home_score END AS score,
                CASE WHEN (s.team_tricode = g.home_tricode AND g.home_score > g.away_score)
                       OR (s.team_tricode = g.away_tricode AND g.away_score > g.home_score)
                     THEN 'W'
                     WHEN g.home_score = g.away_score THEN 'T'
                     ELSE 'L' END AS result,
                s.goals, s.assists, s.goals + s.assists AS points,
                s.shots, s.pim, s.pp_goals, s.sh_goals
            FROM game_skaters s
            JOIN games g ON g.game_id = s.game_id
            WHERE s.player_id = ?
            ORDER BY g.game_date DESC, g.created_at DESC
        """, (player_id,)).fetchall()

        # Check if this is a goalie instead
        goalie_seasons = conn.execute("""
            SELECT
                g.season,
                g2.team_tricode,
                g2.player_name,
                COUNT(DISTINCT g2.game_id)       AS gp,
                SUM(g2.shots_against)            AS sa,
                SUM(g2.goals_against)            AS ga,
                CASE WHEN SUM(g2.shots_against) > 0
                     THEN ROUND(CAST(SUM(g2.shots_against - g2.goals_against) AS FLOAT)
                          / SUM(g2.shots_against), 4)
                     ELSE NULL END               AS sv_pct,
                CASE WHEN SUM(g2.shots_against) > 0
                     THEN ROUND(
                         CAST(SUM(g2.goals_against) AS FLOAT) * 60.0
                         / SUM(CASE WHEN g.home_score = g.away_score THEN 65.0 ELSE 60.0 END),
                     2)
                     ELSE NULL END               AS gaa,
                SUM(CASE WHEN g2.decision = 'W' THEN 1 ELSE 0 END) AS wins,
                SUM(CASE WHEN g2.decision = 'L' THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN g2.decision = 'T' THEN 1 ELSE 0 END) AS ties
            FROM game_goalies g2
            JOIN games g ON g.game_id = g2.game_id
            WHERE g2.player_id = ?
            GROUP BY g.season, g2.team_tricode
            ORDER BY g.season DESC
        """, (player_id,)).fetchall()

        goalie_log = conn.execute("""
            SELECT
                g.game_id,
                g.game_date,
                g.season,
                g2.team_tricode,
                CASE WHEN g2.team_tricode = g.home_tricode
                     THEN g.away_tricode ELSE g.home_tricode END AS opponent,
                CASE WHEN g2.team_tricode = g.home_tricode THEN 'H' ELSE 'A' END AS ha,
                CASE WHEN g2.team_tricode = g.home_tricode
                     THEN g.home_score || '-' || g.away_score
                     ELSE g.away_score || '-' || g.home_score END AS score,
                g2.decision,
                g2.shots_against AS sa,
                g2.goals_against AS ga,
                g2.shots_against - g2.goals_against AS saves,
                CASE WHEN g2.shots_against > 0
                     THEN ROUND(CAST(g2.shots_against - g2.goals_against AS FLOAT)
                          / g2.shots_against, 4)
                     ELSE NULL END AS sv_pct,
                ROUND(
                    CAST(g2.goals_against AS FLOAT) * 60.0
                    / CASE WHEN g.home_score = g.away_score THEN 65.0 ELSE 60.0 END,
                2) AS gaa
            FROM game_goalies g2
            JOIN games g ON g.game_id = g2.game_id
            WHERE g2.player_id = ?
            ORDER BY g.game_date DESC, g.created_at DESC
        """, (player_id,)).fetchall()

    return {
        'career':         _row_to_dict(career_row),
        'seasons':        _rows_to_list(season_rows),
        'game_log':       _rows_to_list(game_log),
        'goalie_seasons': _rows_to_list(goalie_seasons),
        'goalie_log':     _rows_to_list(goalie_log),
    }


def get_league_leaders(db_path, season=None, limit=20):
    """
    Top players by goals, assists, points, and top goalies by SV% and GAA.
    """
    season_clause     = "AND g.season = :season" if season else ""
    g_season_clause   = "AND g.season = :season" if season else ""
    params            = {'season': season, 'limit': limit} if season else {'limit': limit}

    with get_conn(db_path) as conn:
        goals = conn.execute(f"""
            SELECT s.player_id, s.player_name, s.team_tricode,
                   SUM(s.goals) AS goals,
                   SUM(s.assists) AS assists,
                   SUM(s.goals + s.assists) AS points,
                   COUNT(DISTINCT s.game_id) AS gp
            FROM game_skaters s JOIN games g ON g.game_id = s.game_id
            WHERE 1=1 {season_clause}
            GROUP BY s.player_id, s.player_name, s.team_tricode
            HAVING goals > 0
            ORDER BY SUM(s.goals) DESC, SUM(s.goals + s.assists) DESC LIMIT :limit
        """, params).fetchall()

        assists = conn.execute(f"""
            SELECT s.player_id, s.player_name, s.team_tricode,
                   SUM(s.goals) AS goals,
                   SUM(s.assists) AS assists,
                   SUM(s.goals + s.assists) AS points,
                   COUNT(DISTINCT s.game_id) AS gp
            FROM game_skaters s JOIN games g ON g.game_id = s.game_id
            WHERE 1=1 {season_clause}
            GROUP BY s.player_id, s.player_name, s.team_tricode
            HAVING assists > 0
            ORDER BY SUM(s.assists) DESC, SUM(s.goals + s.assists) DESC LIMIT :limit
        """, params).fetchall()

        points = conn.execute(f"""
            SELECT s.player_id, s.player_name, s.team_tricode,
                   SUM(s.goals) AS goals,
                   SUM(s.assists) AS assists,
                   SUM(s.goals + s.assists) AS points,
                   COUNT(DISTINCT s.game_id) AS gp
            FROM game_skaters s JOIN games g ON g.game_id = s.game_id
            WHERE 1=1 {season_clause}
            GROUP BY s.player_id, s.player_name, s.team_tricode
            HAVING points > 0
            ORDER BY SUM(s.goals + s.assists) DESC, SUM(s.goals) DESC LIMIT :limit
        """, params).fetchall()

        # Min 5 games for goalie leaders
        # GAA: goals against per 60 min. Ties counted as 65 min, all others 60 min.
        sv_pct = conn.execute(f"""
            SELECT g2.player_id, g2.player_name, g2.team_tricode,
                   COUNT(DISTINCT g2.game_id) AS gp,
                   SUM(g2.shots_against) AS sa,
                   SUM(g2.goals_against) AS ga,
                   ROUND(CAST(SUM(g2.shots_against - g2.goals_against) AS FLOAT)
                         / SUM(g2.shots_against), 4) AS sv_pct
            FROM game_goalies g2 JOIN games g ON g.game_id = g2.game_id
            WHERE g2.shots_against > 0 {g_season_clause}
            GROUP BY g2.player_id, g2.player_name, g2.team_tricode
            HAVING gp >= 5
            ORDER BY sv_pct DESC LIMIT :limit
        """, params).fetchall()

        gaa = conn.execute(f"""
            SELECT g2.player_id, g2.player_name, g2.team_tricode,
                   COUNT(DISTINCT g2.game_id) AS gp,
                   SUM(g2.goals_against) AS ga,
                   ROUND(
                       CAST(SUM(g2.goals_against) AS FLOAT) * 60.0
                       / SUM(CASE WHEN g.home_score = g.away_score THEN 65.0 ELSE 60.0 END),
                   2) AS gaa
            FROM game_goalies g2 JOIN games g ON g.game_id = g2.game_id
            WHERE g2.shots_against > 0 {g_season_clause}
            GROUP BY g2.player_id, g2.player_name, g2.team_tricode
            HAVING gp >= 5
            ORDER BY gaa ASC LIMIT :limit
        """, params).fetchall()

    return {
        'goals':   _rows_to_list(goals),
        'assists': _rows_to_list(assists),
        'points':  _rows_to_list(points),
        'sv_pct':  _rows_to_list(sv_pct),
        'gaa':     _rows_to_list(gaa),
    }

def get_next_unplayed(db_path, season=None):
    """Return the next unplayed scheduled game, optionally filtered by season."""
    sql = """
        SELECT * FROM schedule
        WHERE game_id IS NULL
    """
    params = []
    if season:
        sql += " AND season = ?"
        params.append(season)
    sql += " ORDER BY season, game_number LIMIT 1"
    with get_conn(db_path) as conn:
        row = conn.execute(sql, params).fetchone()
    return _row_to_dict(row)


def import_schedule_csv(db_path, rows):
    """
    Bulk-insert schedule rows from a list of dicts with keys:
        season, game_number, date, away, home
    Skips rows that already exist (same season + game_number).
    Uses INSERT OR IGNORE so FK violations (unknown tricodes) are skipped silently.
    Returns (inserted, skipped) counts.
    """
    inserted = skipped = 0
    # Disable FK enforcement during bulk import so unknown tricodes
    # don't block the whole file — they'll show as skipped.
    with get_conn(db_path) as conn:
        conn.execute("PRAGMA foreign_keys = OFF")
        for row in rows:
            try:
                conn.execute("""
                    INSERT OR IGNORE INTO schedule
                        (season, game_number, game_date, home_tricode, away_tricode)
                    VALUES (?, ?, ?, ?, ?)
                """, (
                    str(row['season']).strip(),
                    int(row['game_number']),
                    str(row['date']).strip(),
                    str(row['home']).strip().upper(),
                    str(row['away']).strip().upper(),
                ))
                if conn.execute("SELECT changes()").fetchone()[0]:
                    inserted += 1
                else:
                    skipped += 1
            except Exception:
                skipped += 1
        conn.execute("PRAGMA foreign_keys = ON")
    return inserted, skipped