#!/usr/bin/env python3 """ SEC Stats JSON Exporter Reads sec_stats.db and writes static JSON files for the frontend. Usage: python export_json.py # exports to ./json/ python export_json.py --out /path # exports to custom directory """ import sqlite3 import json import os import sys import argparse from collections import defaultdict DB_PATH = "sec_stats.db" OUT_DIR = "json" def _parse_date(date_str): """Parse M/D/YYYY or MM/DD/YYYY to a sortable tuple. Returns (9999,) on failure.""" try: parts = date_str.split('/') return (int(parts[2]), int(parts[0]), int(parts[1])) except (ValueError, IndexError): return (9999, 12, 31) def normalize_team_name(raw): """Map common SEC abbreviations/variants to canonical names.""" _MAP = { 'UA':'Alabama','BAMA':'Alabama','ALA':'Alabama', 'ARK':'Arkansas','AU':'Auburn','AUB':'Auburn','AUBURN':'Auburn', 'UF':'Florida','FLA':'Florida','UGA':'Georgia', 'UK':'Kentucky','MISS':'Ole Miss','OLEMISS':'Ole Miss','OLE MISS':'Ole Miss', 'MSU':'Mississippi State','MIZ':'Missouri','UM':'Missouri', 'OKLA':'Oklahoma','OU':'Oklahoma','SC':'South Carolina','SCAR':'South Carolina', 'TENN':'Tennessee','TEX':'Texas','TX':'Texas', 'TAMU':'Texas A&M','VANDY':'Vanderbilt','VU':'Vanderbilt', } n = raw.strip() return _MAP.get(n, _MAP.get(n.upper(), n)) # SEC team names — used to filter non-SEC teams from all stats tables. # We filter by name (not ID) because StatCrew uses inconsistent internal # abbreviations (UA, UT, UF etc.) that don't match our registry codes. SEC_TEAM_NAMES = { "Alabama", "Arkansas", "Auburn", "Florida", "Georgia", "Kentucky", "LSU", "Ole Miss", "Mississippi State", "Missouri", "Oklahoma", "South Carolina", "Tennessee", "Texas", "Texas A&M", "Vanderbilt", } # Keep SEC_TEAM_IDS as an alias for any code that references it # (will be replaced by name-based filtering below) SEC_TEAM_IDS = SEC_TEAM_NAMES # deprecated alias def get_season_game_ids(conn, season, conference_only=False): """ Return set of game_ids for the given season (or all if season is None). If conference_only=True, only returns games where game_type = 'Conference' as set by the Game Registry (the authoritative source). Falls back to team-name matching for games ingested before game_type was tracked. """ if not season: if not conference_only: return None # None means no filter rows = conn.execute( "SELECT game_id, vis_name, home_name, game_type FROM games" ).fetchall() else: rows = conn.execute( "SELECT game_id, vis_name, home_name, game_type FROM games WHERE season = ?", (season,) ).fetchall() if conference_only: result = set() for r in rows: gt = r["game_type"] or "" if gt == "Conference": # Registry says it's a conference game — trust it result.add(r["game_id"]) elif not gt: # game_type not set (older ingested game) — fall back to name check if r["vis_name"] in SEC_TEAM_NAMES and r["home_name"] in SEC_TEAM_NAMES: result.add(r["game_id"]) return result return {r["game_id"] for r in rows} def get_db(path=DB_PATH): conn = sqlite3.connect(path) conn.row_factory = sqlite3.Row return conn def thirds_to_ip_display(thirds): if thirds is None: return "0.0" t = int(thirds) return f"{t // 3}.{t % 3}" def safe_div(num, den, decimals=3): if not den: return ".000" val = num / den s = f"{val:.{decimals}f}" if s.startswith("0."): s = s[1:] elif s.startswith("-0."): s = "-" + s[2:] return s def calc_era(er, ip_thirds): if ip_thirds == 0: return "-.--" era = (er * 27) / ip_thirds # 9 innings * 3 thirds return f"{era:.2f}" def calc_whip(bb, h, ip_thirds): if ip_thirds == 0: return "-.--" ip = ip_thirds / 3 return f"{(bb + h) / ip:.2f}" def calc_obp(h, bb, hbp, ab, sf): denom = ab + bb + hbp + sf if denom == 0: return ".000" return safe_div(h + bb + hbp, denom) def calc_slg(h, double_, triple, hr, ab): if ab == 0: return ".000" tb = h + double_ + (triple * 2) + (hr * 3) return safe_div(tb, ab) def calc_ops(obp, slg): try: val = float(obp) + float(slg) s = f"{val:.3f}" return s.lstrip("0") or ".000" if val < 1 else s except (ValueError, TypeError): return "-.---" def write_json(data, path): os.makedirs(os.path.dirname(path), exist_ok=True) with open(path, "w", encoding="utf-8") as f: json.dump(data, f, ensure_ascii=False, separators=(",", ":")) print(f" Wrote: {path} ({len(json.dumps(data))} bytes)") # --------------------------------------------------------------------------- # Aggregators # --------------------------------------------------------------------------- def export_game_results(conn, out_dir, season=None, conference_only=False): gids_r = get_season_game_ids(conn, season, conference_only) gid_w = f"AND gr.game_id IN ({','.join(['?']*len(gids_r))})" if gids_r is not None else "" gid_p = list(gids_r) if gids_r is not None else [] rows = conn.execute(f""" SELECT gr.*, g.neutral, g.night_game, g.weather, g.start_time, g.game_type, g.vis_record, g.home_record, g.vis_rank, g.home_rank FROM game_results gr JOIN games g ON gr.game_id = g.game_id WHERE 1=1 {gid_w} ORDER BY gr.date DESC, gr.game_id """, gid_p).fetchall() results = [] for r in rows: results.append({ "game_id": r["game_id"], "date": r["date"], "vis_id": r["vis_id"], "vis_name": r["vis_name"], "vis_score": r["vis_score"], "vis_record": r["vis_record"] or "", "vis_rank": r["vis_rank"], "home_id": r["home_id"], "home_name": r["home_name"], "home_score": r["home_score"], "home_record": r["home_record"] or "", "home_rank": r["home_rank"], "winner_id": r["winner_id"], "winner_name":r["winner_name"], "location": r["location"], "stadium": r["stadium"], "duration": r["duration"], "attend": r["attend"], "neutral": bool(r["neutral"]), "night_game": bool(r["night_game"]), "game_type": r["game_type"] or "", }) write_json(results, os.path.join(out_dir, "results.json")) def export_linescore(conn, out_dir, season=None, conference_only=False): gids_l = get_season_game_ids(conn, season, conference_only) if gids_l is not None: placeholders = ','.join(['?']*len(gids_l)) games = conn.execute(f"SELECT game_id FROM games WHERE game_id IN ({placeholders}) ORDER BY date", list(gids_l)).fetchall() else: games = conn.execute("SELECT game_id FROM games ORDER BY date").fetchall() output = [] for g in games: gid = g["game_id"] teams = conn.execute( "SELECT * FROM linescore WHERE game_id=? ORDER BY vh DESC", (gid,) ).fetchall() innings = conn.execute( "SELECT * FROM linescore_innings WHERE game_id=? ORDER BY vh DESC, inning", (gid,) ).fetchall() inn_by_team = defaultdict(dict) for i in innings: inn_by_team[i["vh"]][i["inning"]] = i["score"] game_data = {"game_id": gid, "teams": []} for t in teams: game_data["teams"].append({ "vh": t["vh"], "team_id": t["team_id"], "team_name": t["team_name"], "runs": t["runs"], "hits": t["hits"], "errors": t["errors"], "lob": t["lob"], "innings": inn_by_team[t["vh"]], }) output.append(game_data) write_json(output, os.path.join(out_dir, "linescores.json")) def export_team_batting(conn, out_dir, season=None, conference_only=False): """Aggregate team batting across all games.""" gids_tb = get_season_game_ids(conn, season, conference_only) season_join = f"JOIN games g ON th.game_id = g.game_id WHERE th.game_id IN ({','.join(['?']*len(gids_tb))})" if gids_tb is not None else "" params = list(gids_tb) if gids_tb is not None else [] rows = conn.execute(f""" SELECT th.team_id, th.team_name, COUNT(DISTINCT th.game_id) AS g, SUM(th.ab) AS ab, SUM(th.r) AS r, SUM(th.h) AS h, SUM(th.rbi) AS rbi, SUM(th.double_) AS double_, SUM(th.triple) AS triple, SUM(th.hr) AS hr, SUM(th.bb) AS bb, SUM(th.so) AS so, SUM(th.kl) AS kl, SUM(th.hbp) AS hbp, SUM(th.sb) AS sb, SUM(th.cs) AS cs, SUM(th.sh) AS sh, SUM(th.sf) AS sf, SUM(th.gdp) AS gdp FROM team_hitting th {season_join} GROUP BY th.team_id ORDER BY CAST(SUM(th.h) AS FLOAT) / MAX(SUM(th.ab), 1) DESC """, params).fetchall() # Filter to SEC teams only rows = [r for r in rows if r["team_name"] in SEC_TEAM_NAMES] teams = [] for i, r in enumerate(rows): ab = r["ab"] or 0 h = r["h"] or 0 bb = r["bb"] or 0 hbp = r["hbp"] or 0 sf = r["sf"] or 0 d = r["double_"] or 0 t = r["triple"] or 0 hr = r["hr"] or 0 avg = safe_div(h, ab) obp = calc_obp(h, bb, hbp, ab, sf) slg = calc_slg(h, d, t, hr, ab) ops = calc_ops(obp, slg) tb = h + d + (t * 2) + (hr * 3) teams.append({ "rank": i + 1, "team_id": r["team_id"], "team": r["team_name"], "g": r["g"], "ab": ab, "r": r["r"], "h": h, "2b": d, "3b": t, "hr": hr, "rbi": r["rbi"], "tb": tb, "bb": bb, "hbp": hbp, "so": r["so"], "sb": r["sb"], "cs": r["cs"], "sh": r["sh"], "sf": sf, "gdp": r["gdp"], "avg": avg, "obp": obp, "slg": slg, "ops": ops, }) write_json(teams, os.path.join(out_dir, "team_batting.json")) def export_team_pitching(conn, out_dir, season=None, conference_only=False): gids_tp = get_season_game_ids(conn, season, conference_only) season_join = f"JOIN games g ON tp.game_id = g.game_id WHERE tp.game_id IN ({','.join(['?']*len(gids_tp))})" if gids_tp is not None else "" params = list(gids_tp) if gids_tp is not None else [] rows = conn.execute(f""" SELECT tp.team_id, tp.team_name, COUNT(DISTINCT tp.game_id) AS g, SUM(tp.ip_thirds) AS ip_thirds, SUM(tp.h) AS h, SUM(tp.r) AS r, SUM(tp.er) AS er, SUM(tp.bb) AS bb, SUM(tp.so) AS so, SUM(tp.hr) AS hr, SUM(tp.hbp) AS hbp, SUM(tp.wp) AS wp, SUM(tp.bf) AS bf, SUM(tp.double_) AS double_, SUM(tp.gdp) AS gdp FROM team_pitching tp {season_join} GROUP BY tp.team_id ORDER BY CAST(SUM(tp.er) AS FLOAT) / MAX(SUM(tp.ip_thirds), 1) ASC """, params).fetchall() rows = [r for r in rows if r["team_name"] in SEC_TEAM_NAMES] teams = [] for i, r in enumerate(rows): ip_t = r["ip_thirds"] or 0 er = r["er"] or 0 rows_data = { "rank": i + 1, "team_id": r["team_id"], "team": r["team_name"], "g": r["g"], "ip": thirds_to_ip_display(ip_t), "h": r["h"], "r": r["r"], "er": er, "bb": r["bb"], "so": r["so"], "hr": r["hr"], "hbp": r["hbp"], "wp": r["wp"], "bf": r["bf"], "2b": r["double_"], "gdp": r["gdp"], "era": calc_era(er, ip_t), "whip": calc_whip(r["bb"] or 0, r["h"] or 0, ip_t), } teams.append(rows_data) write_json(teams, os.path.join(out_dir, "team_pitching.json")) def export_team_fielding(conn, out_dir, season=None, conference_only=False): gids_tf = get_season_game_ids(conn, season, conference_only) season_join = f"JOIN games g ON tf.game_id = g.game_id WHERE tf.game_id IN ({','.join(['?']*len(gids_tf))})" if gids_tf is not None else "" params = list(gids_tf) if gids_tf is not None else [] rows = conn.execute(f""" SELECT tf.team_id, tf.team_name, COUNT(DISTINCT tf.game_id) AS g, SUM(tf.po) AS po, SUM(tf.a) AS a, SUM(tf.e) AS e, SUM(tf.dp) AS dp, SUM(tf.sba) AS sba, SUM(tf.csb) AS csb FROM team_fielding tf {season_join} GROUP BY tf.team_id ORDER BY CAST(SUM(tf.e) AS FLOAT) / MAX(SUM(tf.po)+SUM(tf.a)+SUM(tf.e), 1) ASC """, params).fetchall() rows = [r for r in rows if r["team_name"] in SEC_TEAM_NAMES] teams = [] for i, r in enumerate(rows): po = r["po"] or 0 a = r["a"] or 0 e = r["e"] or 0 total = po + a + e fld = safe_div(po + a, total) if total > 0 else "1.000" teams.append({ "rank": i + 1, "team_id": r["team_id"], "team": r["team_name"], "g": r["g"], "po": po, "a": a, "e": e, "dp": r["dp"], "sba": r["sba"], "csb": r["csb"], "fld_pct": fld, }) write_json(teams, os.path.join(out_dir, "team_fielding.json")) def export_individual_batting(conn, out_dir, season=None, conference_only=False): """Aggregate individual batting — only players who appeared (gp > 0).""" gids = get_season_game_ids(conn, season, conference_only) gid_clause = f"AND game_id IN ({','.join(['?']*len(gids))})" if gids is not None else "" gid_params = list(gids) if gids is not None else [] rows = conn.execute(f""" SELECT player_name, team_id, team_name, SUM(gp) AS gp, SUM(gs) AS gs, SUM(ab) AS ab, SUM(r) AS r, SUM(h) AS h, SUM(rbi) AS rbi, SUM(double_) AS double_, SUM(triple) AS triple, SUM(hr) AS hr, SUM(bb) AS bb, SUM(so) AS so, SUM(kl) AS kl, SUM(hbp) AS hbp, SUM(sb) AS sb, SUM(cs) AS cs, SUM(sh) AS sh, SUM(sf) AS sf, SUM(gdp) AS gdp, MAX(bats) AS bats, MAX(throws) AS throws, MAX(class_) AS class_, MAX(uni) AS uni FROM player_hitting WHERE gp > 0 {gid_clause} GROUP BY player_name, team_id ORDER BY CAST(SUM(h) AS FLOAT) / MAX(SUM(ab), 1) DESC """, gid_params).fetchall() rows = [r for r in rows if r["team_name"] in SEC_TEAM_NAMES] players = [] for i, r in enumerate(rows): ab = r["ab"] or 0 h = r["h"] or 0 bb = r["bb"] or 0 hbp = r["hbp"] or 0 sf = r["sf"] or 0 d = r["double_"] or 0 t = r["triple"] or 0 hr = r["hr"] or 0 tb = h + d + (t * 2) + (hr * 3) avg = safe_div(h, ab) obp = calc_obp(h, bb, hbp, ab, sf) slg = calc_slg(h, d, t, hr, ab) ops = calc_ops(obp, slg) sh = r["sh"] or 0 pa = ab + bb + hbp + sf + sh # Plate Appearances = AB + BB + HBP + SF + SH players.append({ "rank": i + 1, "player": r["player_name"], "team_id": r["team_id"], "team": r["team_name"], "uni": r["uni"], "class": r["class_"], "bats": r["bats"], "gp": r["gp"], "gs": r["gs"], "pa": pa, "ab": ab, "r": r["r"], "h": h, "2b": d, "3b": t, "hr": hr, "rbi": r["rbi"], "tb": tb, "bb": bb, "hbp": hbp, "so": r["so"], "sb": r["sb"], "cs": r["cs"], "sh": sh, "sf": sf, "gdp": r["gdp"], "avg": avg, "obp": obp, "slg": slg, "ops": ops, }) write_json(players, os.path.join(out_dir, "individual_batting.json")) def export_individual_pitching(conn, out_dir, season=None, conference_only=False): gids = get_season_game_ids(conn, season, conference_only) gid_clause = f"AND game_id IN ({','.join(['?']*len(gids))})" if gids is not None else "" gid_params = list(gids) if gids is not None else [] rows = conn.execute(f""" SELECT player_name, team_id, team_name, SUM(gp) AS gp, SUM(gs) AS gs, SUM(appear) AS appear, SUM(ip_thirds) AS ip_thirds, SUM(h) AS h, SUM(r) AS r, SUM(er) AS er, SUM(bb) AS bb, SUM(so) AS so, SUM(hr) AS hr, SUM(hbp) AS hbp, SUM(wp) AS wp, SUM(kl) AS kl, SUM(bf) AS bf, SUM(double_) AS double_, SUM(gdp) AS gdp, SUM(pitches) AS pitches, SUM(strikes) AS strikes, MAX(throws) AS throws, MAX(class_) AS class_, MAX(uni) AS uni, SUM(CASE WHEN win != '' THEN 1 ELSE 0 END) AS w, SUM(CASE WHEN loss != '' THEN 1 ELSE 0 END) AS l, SUM(CASE WHEN save_ != '' THEN 1 ELSE 0 END) AS sv FROM player_pitching WHERE gp > 0 {gid_clause} GROUP BY player_name, team_id ORDER BY CAST(SUM(er)*27 AS FLOAT) / MAX(SUM(ip_thirds), 1) ASC """, gid_params).fetchall() rows = [r for r in rows if r["team_name"] in SEC_TEAM_NAMES] players = [] for i, r in enumerate(rows): ip_t = r["ip_thirds"] or 0 er = r["er"] or 0 so = r["so"] or 0 bb = r["bb"] or 0 players.append({ "rank": i + 1, "player": r["player_name"], "team_id": r["team_id"], "team": r["team_name"], "uni": r["uni"], "class": r["class_"], "throws": r["throws"], "gp": r["gp"], "gs": r["gs"], "app": r["appear"], "w": r["w"], "l": r["l"], "sv": r["sv"], "ip": thirds_to_ip_display(ip_t), "h": r["h"], "r": r["r"], "er": er, "bb": bb, "so": so, "hr": r["hr"], "hbp": r["hbp"], "wp": r["wp"], "bf": r["bf"], "gdp": r["gdp"], "pitches": r["pitches"], "era": calc_era(er, ip_t), "whip": calc_whip(bb, r["h"] or 0, ip_t), "k_bb": f"{so/bb:.2f}" if bb > 0 else "∞", }) write_json(players, os.path.join(out_dir, "individual_pitching.json")) def export_individual_fielding(conn, out_dir, season=None, conference_only=False): gids = get_season_game_ids(conn, season, conference_only) gid_clause = f"AND pf.game_id IN ({','.join(['?']*len(gids))})" if gids is not None else "" gid_params = list(gids) if gids is not None else [] rows = conn.execute(f""" SELECT pf.player_name, pf.team_id, pf.team_name, SUM(pf.po) AS po, SUM(pf.a) AS a, SUM(pf.e) AS e, SUM(pf.dp) AS dp, SUM(pf.sba) AS sba, SUM(pf.csb) AS csb, MAX(pf.uni) AS uni, COUNT(DISTINCT pf.game_id) AS g, MAX(ph.class_) AS class_, MAX(pf.pos) AS pos FROM player_fielding pf LEFT JOIN player_hitting ph ON ph.game_id = pf.game_id AND ph.player_name = pf.player_name AND ph.team_name = pf.team_name WHERE 1=1 {gid_clause} GROUP BY pf.player_name, pf.team_id ORDER BY SUM(pf.e) ASC, (SUM(pf.po)+SUM(pf.a)) DESC """, gid_params).fetchall() rows = [r for r in rows if r["team_name"] in SEC_TEAM_NAMES] players = [] for i, r in enumerate(rows): po = r["po"] or 0 a = r["a"] or 0 e = r["e"] or 0 total = po + a + e fld = safe_div(po + a, total) if total > 0 else "1.000" players.append({ "rank": i + 1, "player": r["player_name"], "team_id": r["team_id"], "team": r["team_name"], "uni": r["uni"], "class_": r["class_"] or "", "pos": r["pos"] or "", "g": r["g"], "po": po, "a": a, "e": e, "dp": r["dp"], "sba": r["sba"], "csb": r["csb"], "fld_pct": fld, }) write_json(players, os.path.join(out_dir, "individual_fielding.json")) def export_stat_leaders(conn, out_dir, season=None, conference_only=False): gids = get_season_game_ids(conn, season, conference_only) _gid_w = f"AND game_id IN ({','.join(['?']*len(gids))})" if gids is not None else "" _gid_p = list(gids) if gids is not None else [] """Top N leaders per stat category for the Leaders tab.""" N = 10 leaders = {} # Build per-team game counts so we can apply NCAA minimums per team # NCAA batting minimum: 1 PA per team game # NCAA pitching minimum: 1 IP per team game if gids is not None: ph = ','.join(['?']*len(gids)); gp = list(gids) team_game_rows = conn.execute(f""" SELECT team_name, COUNT(*) AS g FROM ( SELECT vis_name AS team_name FROM game_results WHERE game_id IN ({ph}) UNION ALL SELECT home_name AS team_name FROM game_results WHERE game_id IN ({ph}) ) WHERE team_name IN ({','.join(['?']*len(SEC_TEAM_NAMES))}) GROUP BY team_name """, gp + gp + list(SEC_TEAM_NAMES)).fetchall() else: team_game_rows = conn.execute(f""" SELECT team_name, COUNT(*) AS g FROM ( SELECT vis_name AS team_name FROM game_results UNION ALL SELECT home_name AS team_name FROM game_results ) WHERE team_name IN ({','.join(['?']*len(SEC_TEAM_NAMES))}) GROUP BY team_name """, list(SEC_TEAM_NAMES)).fetchall() team_games = {r["team_name"]: r["g"] for r in team_game_rows} # Batting leaders for stat, col in [ ("avg", "CAST(SUM(h) AS FLOAT) / MAX(SUM(ab),1)"), ("hr", "SUM(hr)"), ("rbi", "SUM(rbi)"), ("r", "SUM(r)"), ("h", "SUM(h)"), ("sb", "SUM(sb)"), ("obp", "(SUM(h)+SUM(bb)+SUM(hbp)) * 1.0 / MAX(SUM(ab)+SUM(bb)+SUM(hbp)+SUM(sf),1)"), ("slg", "(SUM(h)+SUM(double_)+SUM(triple)*2+SUM(hr)*3) * 1.0 / MAX(SUM(ab),1)"), ]: is_rate = stat in ("avg", "obp", "slg") # Use a large LIMIT to ensure we don't cut off qualified players from # teams with realistic stats behind small-sample inflated averages rows = conn.execute(f""" SELECT player_name, team_id, team_name, SUM(ab) AS ab, SUM(ab)+SUM(bb)+SUM(hbp)+SUM(sf)+SUM(sh) AS pa, {col} AS val FROM player_hitting WHERE gp > 0 {_gid_w} GROUP BY player_name, team_id HAVING SUM(ab) >= 1 ORDER BY val DESC LIMIT 500 """, _gid_p).fetchall() rows = [r for r in rows if r["team_name"] in SEC_TEAM_NAMES] # Apply NCAA qualifier for rate stats: 1 PA per team game if is_rate: rows = [r for r in rows if (r["pa"] or 0) >= team_games.get(r["team_name"], 1)] leaders[f"batting_{stat}"] = [ { "rank": i + 1, "player": r["player_name"], "team": r["team_name"], "team_id": r["team_id"], "ab": r["ab"], "value": (f"{r['val']:.3f}".lstrip("0") or ".000") if is_rate else int(r["val"] or 0), } for i, r in enumerate(rows[:N]) ] # Pitching leaders for stat, col, order in [ ("era", "CAST(SUM(er)*27 AS FLOAT)/MAX(SUM(ip_thirds),1)", "ASC"), ("so", "SUM(so)", "DESC"), ("w", "SUM(CASE WHEN win != '' THEN 1 ELSE 0 END)", "DESC"), ("sv", "SUM(CASE WHEN save_ != '' THEN 1 ELSE 0 END)", "DESC"), ]: is_rate = stat in ("era",) rows = conn.execute(f""" SELECT player_name, team_id, team_name, SUM(ip_thirds) AS ip_thirds, {col} AS val FROM player_pitching WHERE gp > 0 {_gid_w} GROUP BY player_name, team_id HAVING SUM(ip_thirds) >= 1 ORDER BY val {order} LIMIT 500 """, _gid_p).fetchall() rows = [r for r in rows if r["team_name"] in SEC_TEAM_NAMES] # Apply NCAA qualifier for ERA: 1 IP per team game if is_rate: rows = [r for r in rows if (r["ip_thirds"] or 0) >= team_games.get(r["team_name"], 1) * 3] leaders[f"pitching_{stat}"] = [ { "rank": i + 1, "player": r["player_name"], "team": r["team_name"], "team_id": r["team_id"], "ip": thirds_to_ip_display(r["ip_thirds"]), "value": (f"{r['val']:.2f}" if stat == "era" else int(r["val"] or 0)), } for i, r in enumerate(rows) ] write_json(leaders, os.path.join(out_dir, "leaders.json")) def export_game_highs(conn, out_dir, season=None, conference_only=False): """Single-game individual highs.""" gids_h = get_season_game_ids(conn, season, conference_only) _w = f"AND ph.game_id IN ({','.join(['?']*len(gids_h))})" if gids_h is not None else "" _p = list(gids_h) if gids_h is not None else [] _pw = f"AND pp.game_id IN ({','.join(['?']*len(gids_h))})" if gids_h is not None else "" _fw = f"AND pf.game_id IN ({','.join(['?']*len(gids_h))})" if gids_h is not None else "" highs = {} def ind_high(key, table, alias, col, order, sec_only=True, fmt_fn=None): rows = conn.execute(f""" SELECT {alias}.player_name, {alias}.team_name, {alias}.{col} AS val, {alias}.game_id, gr.date, gr.vis_name, gr.home_name FROM {table} {alias} JOIN game_results gr ON {alias}.game_id = gr.game_id WHERE {alias}.gp > 0 {'AND '+alias+'.'+col+' > 0' if order=='DESC' else ''} {'AND '+alias+'.game_id IN ('+','.join(['?']*len(gids_h))+')' if gids_h is not None else ''} ORDER BY {alias}.{col} {order}, gr.date DESC LIMIT 10 """, _p).fetchall() if sec_only: rows = [r for r in rows if r["team_name"] in SEC_TEAM_NAMES] highs[key] = [ { "rank": i + 1, "player": r["player_name"], "team": r["team_name"], "game": f"{r['vis_name']} vs. {r['home_name']} ({r['date']})", "value": fmt_fn(r["val"]) if fmt_fn else r["val"], } for i, r in enumerate(rows) ] # ── Individual Hitting ──────────────────────────────────────────────────── hitting_highs = [ ("hitting_ab", "ab", "DESC"), ("hitting_r", "r", "DESC"), ("hitting_hits", "h", "DESC"), ("hitting_rbi", "rbi", "DESC"), ("hitting_2b", "double_","DESC"), ("hitting_3b", "triple", "DESC"), ("hitting_hr", "hr", "DESC"), ("hitting_tb", None, "DESC"), # calculated: ab+bb+hbp+sf+sh — use h+2b+3b*2+hr*3 approx ("hitting_bb", "bb", "DESC"), ("hitting_so", "so", "DESC"), ("hitting_sh", "sh", "DESC"), ("hitting_sf", "sf", "DESC"), ("hitting_sb", "sb", "DESC"), ("hitting_hbp", "hbp", "DESC"), ("hitting_cs", "cs", "DESC"), ("hitting_gdp", "gdp", "DESC"), ] for key, col, order in hitting_highs: if col is None: # Total bases: h + double_ + 2*triple + 3*hr rows = conn.execute(f""" SELECT ph.player_name, ph.team_name, (ph.h + ph.double_ + ph.triple*2 + ph.hr*3) AS val, ph.game_id, gr.date, gr.vis_name, gr.home_name FROM player_hitting ph JOIN game_results gr ON ph.game_id = gr.game_id WHERE ph.gp > 0 AND (ph.h + ph.double_ + ph.triple*2 + ph.hr*3) > 0 {'AND ph.game_id IN ('+",".join(["?"]*len(gids_h))+')' if gids_h is not None else ''} ORDER BY val DESC, gr.date DESC LIMIT 10 """, _p).fetchall() rows = [r for r in rows if r["team_name"] in SEC_TEAM_NAMES] highs[key] = [{"rank":i+1,"player":r["player_name"],"team":r["team_name"], "game":f"{r['vis_name']} vs. {r['home_name']} ({r['date']})","value":r["val"]} for i,r in enumerate(rows)] else: ind_high(key, "player_hitting", "ph", col, order) # ── Individual Pitching ─────────────────────────────────────────────────── pitching_highs = [ ("pitching_ip", "ip_thirds", "DESC"), ("pitching_r", "r", "DESC"), ("pitching_bb", "bb", "DESC"), ("pitching_so", "so", "DESC"), ("pitching_h", "h", "DESC"), ("pitching_2b", "double_", "DESC"), ("pitching_3b", "triple", "DESC"), ("pitching_hr", "hr", "DESC"), ("pitching_hbp", "hbp", "DESC"), ("pitching_wp", "wp", "DESC"), ] for key, col, order in pitching_highs: fmt = thirds_to_ip_display if col == "ip_thirds" else None rows = conn.execute(f""" SELECT pp.player_name, pp.team_name, pp.{col} AS val, pp.game_id, gr.date, gr.vis_name, gr.home_name FROM player_pitching pp JOIN game_results gr ON pp.game_id = gr.game_id WHERE pp.gp > 0 AND pp.{col} > 0 {'AND pp.game_id IN ('+",".join(["?"]*len(gids_h))+')' if gids_h is not None else ''} ORDER BY pp.{col} {order}, gr.date DESC LIMIT 10 """, _p).fetchall() rows = [r for r in rows if r["team_name"] in SEC_TEAM_NAMES] highs[key] = [{"rank":i+1,"player":r["player_name"],"team":r["team_name"], "game":f"{r['vis_name']} vs. {r['home_name']} ({r['date']})","value":fmt(r["val"]) if fmt else r["val"]} for i,r in enumerate(rows)] # ── Individual Fielding ─────────────────────────────────────────────────── fielding_highs = [ ("fielding_po", "po", "DESC"), ("fielding_a", "a", "DESC"), ("fielding_e", "e", "DESC"), ("fielding_dp", "dp", "DESC"), ] for key, col, order in fielding_highs: rows = conn.execute(f""" SELECT pf.player_name, pf.team_name, pf.{col} AS val, pf.game_id, gr.date, gr.vis_name, gr.home_name FROM player_fielding pf JOIN game_results gr ON pf.game_id = gr.game_id WHERE pf.{col} > 0 {'AND pf.game_id IN ('+",".join(["?"]*len(gids_h))+')' if gids_h is not None else ''} ORDER BY pf.{col} {order}, gr.date DESC LIMIT 10 """, _p).fetchall() rows = [r for r in rows if r["team_name"] in SEC_TEAM_NAMES] highs[key] = [{"rank":i+1,"player":r["player_name"],"team":r["team_name"], "game":f"{r['vis_name']} vs. {r['home_name']} ({r['date']})","value":r["val"]} for i,r in enumerate(rows)] write_json(highs, os.path.join(out_dir, "game_highs.json")) def export_team_game_highs(conn, out_dir, season=None, conference_only=False): """Single-game team highs.""" gids_h = get_season_game_ids(conn, season, conference_only) _w = f"AND gr.game_id IN ({','.join(['?']*len(gids_h))})" if gids_h is not None else "" _p = list(gids_h) if gids_h is not None else [] highs = {} def team_high(key, table, alias, col, order, fmt_fn=None): rows = conn.execute(f""" SELECT {alias}.team_name, {col} AS val, gr.game_id, gr.date, gr.vis_name, gr.home_name FROM {table} {alias} JOIN game_results gr ON {alias}.game_id = gr.game_id WHERE 1=1 {_w} ORDER BY {col} {order}, gr.date DESC LIMIT 10 """, _p).fetchall() rows = [r for r in rows if r["team_name"] in SEC_TEAM_NAMES] highs[key] = [{"rank":i+1,"team":r["team_name"], "game":f"{r['vis_name']} vs. {r['home_name']} ({r['date']})","value":fmt_fn(r["val"]) if fmt_fn else r["val"]} for i,r in enumerate(rows)] # ── Team Hitting ────────────────────────────────────────────────────────── team_bat = [ ("team_bat_ab", "th.ab", "DESC"), ("team_bat_r", "th.r", "DESC"), ("team_bat_h", "th.h", "DESC"), ("team_bat_rbi", "th.rbi", "DESC"), ("team_bat_2b", "th.double_", "DESC"), ("team_bat_3b", "th.triple", "DESC"), ("team_bat_hr", "th.hr", "DESC"), ("team_bat_bb", "th.bb", "DESC"), ("team_bat_so", "th.so", "DESC"), ("team_bat_sh", "th.sh", "DESC"), ("team_bat_sf", "th.sf", "DESC"), ("team_bat_sb", "th.sb", "DESC"), ("team_bat_hbp", "th.hbp", "DESC"), ("team_bat_cs", "th.cs", "DESC"), ("team_bat_gdp", "th.gdp", "DESC"), ] for key, col, order in team_bat: team_high(key, "team_hitting", "th", col, order) # Team total bases tb_rows = conn.execute(f""" SELECT th.team_name, (th.h + th.double_ + th.triple*2 + th.hr*3) AS val, gr.date, gr.vis_name, gr.home_name FROM team_hitting th JOIN game_results gr ON th.game_id = gr.game_id WHERE 1=1 {_w} ORDER BY val DESC, gr.date DESC LIMIT 10 """, _p).fetchall() highs["team_bat_tb"] = [{"rank":i+1,"team":r["team_name"], "game":f"{r['vis_name']} vs. {r['home_name']} ({r['date']})","value":r["val"]} for i,r in enumerate(tb_rows) if r["team_name"] in SEC_TEAM_NAMES] # LOB from linescore lob_rows = conn.execute(f""" SELECT ls.team_name, ls.lob AS val, gr.date, gr.vis_name, gr.home_name FROM linescore ls JOIN game_results gr ON ls.game_id = gr.game_id WHERE ls.lob > 0 {_w} ORDER BY ls.lob DESC, gr.date DESC LIMIT 10 """, _p).fetchall() highs["team_bat_lob"] = [{"rank":i+1,"team":r["team_name"], "game":f"{r['vis_name']} vs. {r['home_name']} ({r['date']})","value":r["val"]} for i,r in enumerate(lob_rows) if r["team_name"] in SEC_TEAM_NAMES] # ── Team Pitching ───────────────────────────────────────────────────────── team_pit = [ ("team_pit_ip", "tp.ip_thirds", "DESC"), ("team_pit_r", "tp.r", "DESC"), ("team_pit_bb", "tp.bb", "DESC"), ("team_pit_so", "tp.so", "DESC"), ("team_pit_h", "tp.h", "DESC"), ("team_pit_2b", "tp.double_", "DESC"), ("team_pit_3b", "tp.triple", "DESC"), ("team_pit_hr", "tp.hr", "DESC"), ("team_pit_hbp", "tp.hbp", "DESC"), ("team_pit_wp", "tp.wp", "DESC"), ] for key, col, order in team_pit: fmt = thirds_to_ip_display if col == "tp.ip_thirds" else None team_high(key, "team_pitching", "tp", col, order, fmt_fn=fmt) # ── Team Fielding ───────────────────────────────────────────────────────── team_fld = [ ("team_fld_po", "tf.po", "DESC"), ("team_fld_a", "tf.a", "DESC"), ("team_fld_e", "tf.e", "DESC"), ("team_fld_dp", "tf.dp", "DESC"), ] for key, col, order in team_fld: team_high(key, "team_fielding", "tf", col, order) # Passed balls — counted from play_by_play narratives (defensive team) # vh in play_by_play is the batting team, so defensive = opposite vh pb_rows = conn.execute(f""" SELECT CASE WHEN pbp.vh = 'V' THEN gr.home_name ELSE gr.vis_name END AS team_name, pbp.game_id, COUNT(*) AS val, gr.date, gr.vis_name, gr.home_name FROM play_by_play pbp JOIN game_results gr ON pbp.game_id = gr.game_id WHERE lower(pbp.narrative) LIKE '%passed ball%' {'AND pbp.game_id IN ('+",".join(["?"]*len(gids_h))+')' if gids_h is not None else ''} GROUP BY pbp.game_id, pbp.vh ORDER BY val DESC, gr.date DESC LIMIT 10 """, _p).fetchall() highs["team_fld_pb"] = [ {"rank":i+1,"team":r["team_name"], "game":f"{r['vis_name']} vs. {r['home_name']} ({r['date']})","value":r["val"]} for i,r in enumerate(pb_rows) if r["team_name"] in SEC_TEAM_NAMES ] # LOB — from linescore (team stat only) lob_rows = conn.execute(f""" SELECT ls.team_name, ls.lob AS val, gr.date, gr.vis_name, gr.home_name FROM linescore ls JOIN game_results gr ON ls.game_id = gr.game_id WHERE ls.lob > 0 {'AND ls.game_id IN ('+",".join(["?"]*len(gids_h))+')' if gids_h is not None else ''} ORDER BY ls.lob DESC, gr.date DESC LIMIT 10 """, _p).fetchall() highs["team_bat_lob"] = [ {"rank":i+1,"team":r["team_name"], "game":f"{r['vis_name']} vs. {r['home_name']} ({r['date']})","value":r["val"]} for i,r in enumerate(lob_rows) if r["team_name"] in SEC_TEAM_NAMES ] write_json(highs, os.path.join(out_dir, "team_game_highs.json")) def export_player_game_logs(conn, out_dir, season=None, conference_only=False): """Per-player game logs — hitting, pitching, fielding per game.""" gids = get_season_game_ids(conn, season, conference_only) _w = f"AND ph.game_id IN ({','.join(['?']*len(gids))})" if gids is not None else "" _p = list(gids) if gids is not None else [] logs = {} # { "Player Name|TeamName": { hitting: [...], pitching: [...], fielding: [...] } } # ── Hitting game log ────────────────────────────────────────────────────── rows = conn.execute(f""" SELECT ph.player_name, ph.team_name, ph.uni, ph.pos, ph.class_, ph.game_id, ph.vh, ph.gp, ph.ab, ph.r, ph.h, ph.double_, ph.triple, ph.hr, ph.rbi, ph.bb, ph.so, ph.hbp, ph.sb, ph.cs, ph.sh, ph.sf, ph.gdp, gr.date, gr.vis_name, gr.home_name, gr.vis_score, gr.home_score FROM player_hitting ph JOIN game_results gr ON ph.game_id = gr.game_id WHERE ph.gp > 0 {_w} ORDER BY ph.player_name, ph.team_name, gr.date """, _p).fetchall() for r in rows: if r["team_name"] not in SEC_TEAM_NAMES: continue key = f"{r['player_name']}|{r['team_name']}" if key not in logs: logs[key] = { "player": r["player_name"], "team": r["team_name"], "uni": r["uni"], "pos": r["pos"], "class_": r["class_"], "hitting": [], "pitching": [], "fielding": [] } opp = r["vis_name"] if r["vh"] == "H" else r["home_name"] loc = "@" if r["vh"] == "V" else "vs" result_score = f"{r['vis_score']}-{r['home_score']}" ab = r["ab"] or 0 h = r["h"] or 0 avg = f"{h/ab:.3f}".lstrip("0") if ab > 0 else ".000" logs[key]["hitting"].append({ "game_id": r["game_id"], "date": r["date"], "opp": f"{loc} {opp}", "result": result_score, "ab": ab, "r": r["r"] or 0, "h": h, "2b": r["double_"] or 0, "3b": r["triple"] or 0, "hr": r["hr"] or 0, "rbi":r["rbi"] or 0, "bb": r["bb"] or 0, "so": r["so"] or 0, "hbp": r["hbp"] or 0, "sb": r["sb"] or 0, "cs": r["cs"] or 0, "sh": r["sh"] or 0, "sf": r["sf"] or 0, "gdp":r["gdp"] or 0, "avg": avg, }) # ── Pitching game log ───────────────────────────────────────────────────── rows = conn.execute(f""" SELECT pp.player_name, pp.team_name, pp.uni, pp.pos, pp.class_, pp.game_id, pp.vh, pp.gp, pp.ip_thirds, pp.h, pp.r, pp.er, pp.bb, pp.so, pp.hr, pp.hbp, pp.wp, pp.double_, pp.triple, pp.win, pp.loss, pp.save_, gr.date, gr.vis_name, gr.home_name, gr.vis_score, gr.home_score FROM player_pitching pp JOIN game_results gr ON pp.game_id = gr.game_id WHERE pp.gp > 0 {_w.replace('ph.','pp.')} ORDER BY pp.player_name, pp.team_name, gr.date """, _p).fetchall() for r in rows: if r["team_name"] not in SEC_TEAM_NAMES: continue key = f"{r['player_name']}|{r['team_name']}" if key not in logs: logs[key] = { "player": r["player_name"], "team": r["team_name"], "uni": r["uni"], "pos": r["pos"], "class_": r["class_"], "hitting": [], "pitching": [], "fielding": [] } opp = r["vis_name"] if r["vh"] == "H" else r["home_name"] loc = "@" if r["vh"] == "V" else "vs" dec = r["win"] and "W" or r["loss"] and "L" or r["save_"] and "S" or "" logs[key]["pitching"].append({ "game_id": r["game_id"], "date": r["date"], "opp": f"{loc} {opp}", "result": f"{r['vis_score']}-{r['home_score']}", "dec": dec, "ip": thirds_to_ip_display(r["ip_thirds"] or 0), "h": r["h"] or 0, "r": r["r"] or 0, "er": r["er"] or 0, "bb": r["bb"] or 0, "so": r["so"] or 0, "hr": r["hr"] or 0, "hbp": r["hbp"]or 0, "wp": r["wp"] or 0, "2b": r["double_"] or 0, "3b": r["triple"] or 0, }) # ── Fielding game log ───────────────────────────────────────────────────── rows = conn.execute(f""" SELECT pf.player_name, pf.team_name, pf.uni, pf.pos, pf.game_id, pf.po, pf.a, pf.e, pf.dp, gr.date, gr.vis_name, gr.home_name, gr.vis_score, gr.home_score, ph.vh FROM player_fielding pf JOIN game_results gr ON pf.game_id = gr.game_id LEFT JOIN player_hitting ph ON ph.game_id = pf.game_id AND ph.player_name = pf.player_name AND ph.team_name = pf.team_name WHERE (pf.po > 0 OR pf.a > 0 OR pf.e > 0 OR pf.dp > 0) {_w.replace('ph.game_id','pf.game_id').replace('ph.','pf.')} ORDER BY pf.player_name, pf.team_name, gr.date """, _p).fetchall() for r in rows: if r["team_name"] not in SEC_TEAM_NAMES: continue key = f"{r['player_name']}|{r['team_name']}" if key not in logs: logs[key] = { "player": r["player_name"], "team": r["team_name"], "uni": r["uni"] if hasattr(r, "uni") else "", "pos": r["pos"], "class_": "", "hitting": [], "pitching": [], "fielding": [] } opp = r["vis_name"] if r["vh"] == "H" else r["home_name"] loc = "@" if r["vh"] == "V" else "vs" logs[key]["fielding"].append({ "game_id": r["game_id"], "date": r["date"], "opp": f"{loc} {opp}", "result": f"{r['vis_score']}-{r['home_score']}", "po": r["po"] or 0, "a": r["a"] or 0, "e": r["e"] or 0, "dp": r["dp"] or 0, }) write_json(list(logs.values()), os.path.join(out_dir, "player_game_logs.json")) def export_boxscores(conn, out_dir, season=None, conference_only=False): """Per-game box score: linescore + batting + pitching + fielding + play-by-play.""" gids_b = get_season_game_ids(conn, season, conference_only) if gids_b is not None: placeholders = ','.join(['?']*len(gids_b)) games = conn.execute(f"SELECT * FROM games WHERE game_id IN ({placeholders}) ORDER BY date", list(gids_b)).fetchall() else: games = conn.execute("SELECT * FROM games ORDER BY date").fetchall() boxscores = [] for g in games: gid = g["game_id"] # Linescore ls_rows = conn.execute( "SELECT * FROM linescore WHERE game_id=? ORDER BY vh DESC", (gid,) ).fetchall() inn_rows = conn.execute( "SELECT * FROM linescore_innings WHERE game_id=? ORDER BY vh DESC, inning", (gid,) ).fetchall() inn_by_vh = {} for i in inn_rows: inn_by_vh.setdefault(i["vh"], {})[i["inning"]] = i["score"] max_inn = max((int(k) for t in inn_by_vh.values() for k in t.keys()), default=9) linescore = [] for t in ls_rows: linescore.append({ "vh": t["vh"], "team_id": t["team_id"], "team_name": t["team_name"], "runs": t["runs"], "hits": t["hits"], "errors": t["errors"], "lob": t["lob"], "innings": {str(i): inn_by_vh.get(t["vh"], {}).get(i, 0) for i in range(1, max_inn + 1)}, }) # Per-team batting box batting_box = {} pit_box = {} field_box = {} for vh in ("V", "H"): # Batting hitters = conn.execute(""" SELECT player_name, short_name, uni, pos, spot, bats, ab, r, h, rbi, double_, triple, hr, bb, so, hbp, sb, sf, sh, gdp FROM player_hitting WHERE game_id=? AND vh=? AND gp>0 ORDER BY spot, id """, (gid, vh)).fetchall() batting_box[vh] = [] for p in hitters: avg = safe_div(p["h"], p["ab"]) batting_box[vh].append({ "name": p["player_name"], "short": p["short_name"], "uni": p["uni"], "pos": p["pos"].upper(), "ab": p["ab"], "r": p["r"], "h": p["h"], "rbi": p["rbi"], "2b": p["double_"], "3b": p["triple"], "hr": p["hr"], "bb": p["bb"], "so": p["so"], "hbp": p["hbp"], "sb": p["sb"], "sf": p["sf"], "sh": p["sh"], "avg": avg, }) # Pitching pitchers = conn.execute(""" SELECT player_name, short_name, uni, throws, ip_thirds, h, r, er, bb, so, hr, hbp, wp, bf, pitches, strikes, win, loss, save_ FROM player_pitching WHERE game_id=? AND vh=? AND gp>0 ORDER BY appear, id """, (gid, vh)).fetchall() pit_box[vh] = [] for p in pitchers: ip_t = p["ip_thirds"] or 0 era = calc_era(p["er"] or 0, ip_t) decision = "" if p["win"]: decision = f"W ({p['win']})" elif p["loss"]: decision = f"L ({p['loss']})" elif p["save_"]: decision = f"SV ({p['save_']})" pit_box[vh].append({ "name": p["player_name"], "short": p["short_name"], "uni": p["uni"], "throws": p["throws"], "ip": thirds_to_ip_display(ip_t), "h": p["h"], "r": p["r"], "er": p["er"], "bb": p["bb"], "so": p["so"], "hr": p["hr"], "hbp": p["hbp"], "wp": p["wp"], "bf": p["bf"], "pitches": p["pitches"], "strikes": p["strikes"], "era": era, "decision": decision, }) # Fielding (just errors for box score display) fielders = conn.execute(""" SELECT player_name, pos, po, a, e, dp FROM player_fielding WHERE game_id=? AND vh=? AND e>0 ORDER BY id """, (gid, vh)).fetchall() field_box[vh] = [dict(f) for f in fielders] # Play-by-play pbp_rows = conn.execute(""" SELECT inning, vh, seq, narrative FROM play_by_play WHERE game_id=? ORDER BY seq """, (gid,)).fetchall() pbp = [] for p in pbp_rows: pbp.append({ "inning": p["inning"], "vh": p["vh"], "seq": p["seq"], "text": p["narrative"], }) # Game meta gr = conn.execute( "SELECT * FROM game_results WHERE game_id=?", (gid,) ).fetchone() gm = conn.execute( "SELECT * FROM games WHERE game_id=?", (gid,) ).fetchone() boxscores.append({ "game_id": gid, "date": gm["date"], "location": gm["location"], "stadium": gm["stadium"], "duration": gm["duration"], "attend": gm["attend"], "weather": gm["weather"], "start": gm["start_time"], "vis_id": gm["vis_id"], "vis_name": gm["vis_name"], "vis_record": gm["vis_record"], "vis_rank": gm["vis_rank"], "home_id": gm["home_id"], "home_name": gm["home_name"], "home_record": gm["home_record"], "home_rank": gm["home_rank"], "vis_score": gr["vis_score"] if gr else 0, "home_score": gr["home_score"] if gr else 0, "linescore": linescore, "batting": batting_box, "pitching": pit_box, "fielding_errors": field_box, "plays": pbp, }) write_json(boxscores, os.path.join(out_dir, "boxscores.json")) def export_team_game_logs(conn, out_dir, season=None, conference_only=False): """Per-team game-by-game stats: batting, pitching, fielding + opponent totals.""" gids = get_season_game_ids(conn, season, conference_only) _w = f"AND gr.game_id IN ({','.join(['?']*len(gids))})" if gids is not None else "" _p = list(gids) if gids is not None else [] logs = {} # team_name → { hitting: [...], pitching: [...], opp_hitting: [...], opp_pitching: [...] } # ── Team batting per game ───────────────────────────────────────────────── rows = conn.execute(f""" SELECT th.team_name, th.vh, th.game_id, gr.date, gr.vis_name, gr.home_name, gr.vis_score, gr.home_score, th.ab, th.r, th.h, th.double_ AS "2b", th.triple AS "3b", th.hr, th.rbi, th.bb, th.hbp, th.so, th.sb, th.cs, th.sh, th.sf, th.gdp, (th.h + th.double_ + th.triple*2 + th.hr*3) AS tb, CAST(th.h AS FLOAT)/MAX(th.ab,1) AS avg FROM team_hitting th JOIN game_results gr ON th.game_id = gr.game_id WHERE 1=1 {_w} ORDER BY th.team_name, gr.date """, _p).fetchall() for r in rows: if r["team_name"] not in SEC_TEAM_NAMES: continue tn = r["team_name"] if tn not in logs: logs[tn] = {"hitting":[],"pitching":[],"opp_hitting":[],"opp_pitching":[]} opp = r["vis_name"] if r["vh"]=="H" else r["home_name"] loc = "vs" if r["vh"]=="H" else "@" ts, os_ = (r["home_score"], r["vis_score"]) if r["vh"]=="H" else (r["vis_score"], r["home_score"]) result = ("W" if ts>os_ else "L") + f" {ts}-{os_}" logs[tn]["hitting"].append({ "game_id": r["game_id"], "date": r["date"], "opp": f"{loc} {opp}", "result": result, "ab":r["ab"],"r":r["r"],"h":r["h"],"2b":r["2b"],"3b":r["3b"],"hr":r["hr"], "rbi":r["rbi"],"bb":r["bb"],"hbp":r["hbp"],"so":r["so"], "sb":r["sb"],"cs":r["cs"],"sh":r["sh"],"sf":r["sf"],"gdp":r["gdp"],"tb":r["tb"], "avg": f"{r['avg']:.3f}".lstrip("0") or ".000", }) # ── Team pitching per game ──────────────────────────────────────────────── rows = conn.execute(f""" SELECT tp.team_name, tp.vh, tp.game_id, gr.date, gr.vis_name, gr.home_name, gr.vis_score, gr.home_score, tp.ip_thirds, tp.h, tp.r, tp.er, tp.bb, tp.so, tp.hr, tp.hbp, tp.wp, tp.double_ AS "2b", tp.triple AS "3b", CAST(tp.er * 27 AS FLOAT) / MAX(tp.ip_thirds, 1) AS era FROM team_pitching tp JOIN game_results gr ON tp.game_id = gr.game_id WHERE 1=1 {_w} ORDER BY tp.team_name, gr.date """, _p).fetchall() for r in rows: if r["team_name"] not in SEC_TEAM_NAMES: continue tn = r["team_name"] if tn not in logs: logs[tn] = {"hitting":[],"pitching":[],"opp_hitting":[],"opp_pitching":[]} opp = r["vis_name"] if r["vh"]=="H" else r["home_name"] loc = "vs" if r["vh"]=="H" else "@" ts, os_ = (r["home_score"], r["vis_score"]) if r["vh"]=="H" else (r["vis_score"], r["home_score"]) result = ("W" if ts>os_ else "L") + f" {ts}-{os_}" logs[tn]["pitching"].append({ "game_id": r["game_id"], "date": r["date"], "opp": f"{loc} {opp}", "result": result, "ip": thirds_to_ip_display(r["ip_thirds"]), "h":r["h"],"r":r["r"],"er":r["er"],"bb":r["bb"],"so":r["so"], "hr":r["hr"],"hbp":r["hbp"],"wp":r["wp"],"2b":r["2b"],"3b":r["3b"], "era": f"{r['era']:.2f}", }) # ── Opponent batting per game (vs each SEC team) ────────────────────────── # Use ALL game IDs in season (not team-scoped) so SEC vs SEC games are included all_gids = get_season_game_ids(conn, season, conference_only) _aw = f"AND gr.game_id IN ({','.join(['?']*len(all_gids))})" if all_gids is not None else "" _ap = list(all_gids) if all_gids is not None else [] rows = conn.execute(f""" SELECT th.team_name AS opp_name, CASE th.vh WHEN 'V' THEN gr.home_name ELSE gr.vis_name END AS sec_team, th.game_id, gr.date, gr.vis_name, gr.home_name, gr.vis_score, gr.home_score, th.vh, th.ab, th.r, th.h, th.double_ AS "2b", th.triple AS "3b", th.hr, th.rbi, th.bb, th.hbp, th.so, th.sb, th.cs, th.sh, th.sf, th.gdp, (th.h + th.double_ + th.triple*2 + th.hr*3) AS tb, CAST(th.h AS FLOAT)/MAX(th.ab,1) AS avg FROM team_hitting th JOIN game_results gr ON th.game_id = gr.game_id WHERE ( (th.vh = 'V' AND gr.home_name IN ({','.join(['?']*len(SEC_TEAM_NAMES))})) OR (th.vh = 'H' AND gr.vis_name IN ({','.join(['?']*len(SEC_TEAM_NAMES))})) ) -- Exclude the SEC team's own row: the team_name must NOT equal the sec_team AND th.team_name != CASE th.vh WHEN 'V' THEN gr.home_name ELSE gr.vis_name END {_aw} ORDER BY sec_team, gr.date """, list(SEC_TEAM_NAMES) + list(SEC_TEAM_NAMES) + _ap).fetchall() for r in rows: tn = r["sec_team"] if not tn or tn not in SEC_TEAM_NAMES: continue if tn not in logs: logs[tn] = {"hitting":[],"pitching":[],"opp_hitting":[],"opp_pitching":[]} sec_vh = "H" if r["vh"]=="V" else "V" opp = r["opp_name"] loc = "vs" if sec_vh=="H" else "@" ts = r["home_score"] if sec_vh=="H" else r["vis_score"] os_ = r["vis_score"] if sec_vh=="H" else r["home_score"] result = ("W" if ts>os_ else "L") + f" {ts}-{os_}" logs[tn]["opp_hitting"].append({ "game_id": r["game_id"], "date": r["date"], "opp": f"{loc} {opp}", "result": result, "ab":r["ab"],"r":r["r"],"h":r["h"],"2b":r["2b"],"3b":r["3b"],"hr":r["hr"], "rbi":r["rbi"],"bb":r["bb"],"hbp":r["hbp"],"so":r["so"], "sb":r["sb"],"cs":r["cs"],"sh":r["sh"],"sf":r["sf"],"gdp":r["gdp"],"tb":r["tb"], "avg": f"{r['avg']:.3f}".lstrip("0") or ".000", }) # ── Opponent pitching per game ──────────────────────────────────────────── rows = conn.execute(f""" SELECT tp.team_name AS opp_name, CASE tp.vh WHEN 'V' THEN gr.home_name ELSE gr.vis_name END AS sec_team, tp.game_id, gr.date, gr.vis_name, gr.home_name, gr.vis_score, gr.home_score, tp.vh, tp.ip_thirds, tp.h, tp.r, tp.er, tp.bb, tp.so, tp.hr, tp.hbp, tp.wp, tp.double_ AS "2b", tp.triple AS "3b", CAST(tp.er * 27 AS FLOAT) / MAX(tp.ip_thirds, 1) AS era FROM team_pitching tp JOIN game_results gr ON tp.game_id = gr.game_id WHERE ( (tp.vh = 'V' AND gr.home_name IN ({','.join(['?']*len(SEC_TEAM_NAMES))})) OR (tp.vh = 'H' AND gr.vis_name IN ({','.join(['?']*len(SEC_TEAM_NAMES))})) ) AND tp.team_name != CASE tp.vh WHEN 'V' THEN gr.home_name ELSE gr.vis_name END {_aw} ORDER BY sec_team, gr.date """, list(SEC_TEAM_NAMES) + list(SEC_TEAM_NAMES) + _ap).fetchall() for r in rows: tn = r["sec_team"] if not tn or tn not in SEC_TEAM_NAMES: continue if tn not in logs: logs[tn] = {"hitting":[],"pitching":[],"opp_hitting":[],"opp_pitching":[]} sec_vh = "H" if r["vh"]=="V" else "V" opp = r["opp_name"] loc = "vs" if sec_vh=="H" else "@" ts = r["home_score"] if sec_vh=="H" else r["vis_score"] os_ = r["vis_score"] if sec_vh=="H" else r["home_score"] result = ("W" if ts>os_ else "L") + f" {ts}-{os_}" logs[tn]["opp_pitching"].append({ "game_id": r["game_id"], "date": r["date"], "opp": f"{loc} {opp}", "result": result, "ip": thirds_to_ip_display(r["ip_thirds"]), "h":r["h"],"r":r["r"],"er":r["er"],"bb":r["bb"],"so":r["so"], "hr":r["hr"],"hbp":r["hbp"],"wp":r["wp"],"2b":r["2b"],"3b":r["3b"], "era": f"{r['era']:.2f}", }) # ── Opponent fielding per game ─────────────────────────────────────────── rows = conn.execute(f""" SELECT tf.team_name AS opp_name, CASE tf.vh WHEN 'V' THEN gr.home_name ELSE gr.vis_name END AS sec_team, tf.game_id, gr.date, gr.vis_name, gr.home_name, gr.vis_score, gr.home_score, tf.vh, tf.po, tf.a, tf.e, tf.dp, tf.sba, tf.csb FROM team_fielding tf JOIN game_results gr ON tf.game_id = gr.game_id WHERE ( (tf.vh = 'V' AND gr.home_name IN ({','.join(['?']*len(SEC_TEAM_NAMES))})) OR (tf.vh = 'H' AND gr.vis_name IN ({','.join(['?']*len(SEC_TEAM_NAMES))})) ) AND tf.team_name != CASE tf.vh WHEN 'V' THEN gr.home_name ELSE gr.vis_name END {_aw} ORDER BY sec_team, gr.date """, list(SEC_TEAM_NAMES) + list(SEC_TEAM_NAMES) + _ap).fetchall() for r in rows: tn = r["sec_team"] if not tn or tn not in SEC_TEAM_NAMES: continue if tn not in logs: logs[tn] = {"hitting":[],"pitching":[],"opp_hitting":[],"opp_pitching":[],"opp_fielding":[]} if "opp_fielding" not in logs[tn]: logs[tn]["opp_fielding"] = [] sec_vh = "H" if r["vh"]=="V" else "V" opp = r["opp_name"] loc = "vs" if sec_vh=="H" else "@" ts = r["home_score"] if sec_vh=="H" else r["vis_score"] os_ = r["vis_score"] if sec_vh=="H" else r["home_score"] result = ("W" if ts>os_ else "L") + f" {ts}-{os_}" po, a, e = r["po"] or 0, r["a"] or 0, r["e"] or 0 tc = po + a + e logs[tn]["opp_fielding"].append({ "game_id": r["game_id"], "date": r["date"], "opp": f"{loc} {opp}", "result": result, "po":po,"a":a,"e":e,"dp":r["dp"] or 0, "sba":r["sba"] or 0,"csb":r["csb"] or 0, "fld_pct": f"{(po+a)/tc:.3f}".lstrip("0") if tc > 0 else ".000", }) write_json(logs, os.path.join(out_dir, "team_game_logs.json")) def export_standings(conn, out_dir, season=None, schedule_path=None): """ Full SEC standings with tiebreaker logic and schedule data. Always uses conference games regardless of conference_only flag. If schedule_path is provided, merges upcoming games from Game Registry. """ SEC_TEAMS = list(SEC_TEAM_NAMES) # ── Get all completed games ─────────────────────────────────────────────── if season: all_results = conn.execute(""" SELECT gr.game_id, gr.date, gr.vis_name, gr.home_name, gr.vis_score, gr.home_score, g.game_type, g.neutral FROM game_results gr JOIN games g ON gr.game_id = g.game_id WHERE g.season = ? ORDER BY gr.date """, [season]).fetchall() else: all_results = conn.execute(""" SELECT gr.game_id, gr.date, gr.vis_name, gr.home_name, gr.vis_score, gr.home_score, g.game_type, g.neutral FROM game_results gr JOIN games g ON gr.game_id = g.game_id ORDER BY gr.date """).fetchall() # ── Get full schedule (completed + future) ──────────────────────────────── if season: full_sched = conn.execute(""" SELECT g.game_id, g.date, g.vis_name, g.home_name, g.game_type, g.neutral, g.stadium, g.location, g.start_time, g.vis_rank, g.home_rank, gr.vis_score, gr.home_score, CASE WHEN gr.game_id IS NOT NULL THEN 1 ELSE 0 END AS completed FROM games g LEFT JOIN game_results gr ON g.game_id = gr.game_id WHERE g.season = ? ORDER BY g.date """, [season]).fetchall() else: full_sched = conn.execute(""" SELECT g.game_id, g.date, g.vis_name, g.home_name, g.game_type, g.neutral, g.stadium, g.location, g.start_time, g.vis_rank, g.home_rank, gr.vis_score, gr.home_score, CASE WHEN gr.game_id IS NOT NULL THEN 1 ELSE 0 END AS completed FROM games g LEFT JOIN game_results gr ON g.game_id = gr.game_id ORDER BY g.date """).fetchall() # ── Build per-team stats ────────────────────────────────────────────────── stats = {t: { 'team': t, 'conf_w': 0, 'conf_l': 0, 'overall_w': 0, 'overall_l': 0, 'home_w': 0, 'home_l': 0, 'road_w': 0, 'road_l': 0, 'neutral_w': 0, 'neutral_l': 0, 'streak': 0, 'streak_type': '', # positive=W, negative=L 'last_result': None, 'last_date': None, 'next_game': None, 'conf_results': [], # list of (opp, w/l) for tiebreaker } for t in SEC_TEAMS} SEC_SET = set(SEC_TEAMS) for g in all_results: vis, home = g['vis_name'], g['home_name'] vs, hs = g['vis_score'], g['home_score'] gtype = g['game_type'] or '' is_neutral = (g['game_type'] or '') == 'Neutral Site' for team in [vis, home]: if team not in stats: continue is_vis = (team == vis) won = (vs > hs) if is_vis else (hs > vs) opp = home if is_vis else vis # Overall if won: stats[team]['overall_w'] += 1 else: stats[team]['overall_l'] += 1 # Location if is_neutral: if won: stats[team]['neutral_w'] += 1 else: stats[team]['neutral_l'] += 1 elif is_vis: if won: stats[team]['road_w'] += 1 else: stats[team]['road_l'] += 1 else: if won: stats[team]['home_w'] += 1 else: stats[team]['home_l'] += 1 # Conference if gtype == 'Conference' and opp in SEC_SET: if won: stats[team]['conf_w'] += 1 else: stats[team]['conf_l'] += 1 stats[team]['conf_results'].append({'opp': opp, 'won': won}) # Streak (running — last game will be most recent) if stats[team]['streak_type'] == '' or (stats[team]['streak_type'] == 'W') == won: stats[team]['streak_type'] = 'W' if won else 'L' stats[team]['streak'] = abs(stats[team]['streak']) + 1 else: stats[team]['streak_type'] = 'W' if won else 'L' stats[team]['streak'] = 1 # Last result score = f"{vs}-{hs}" if is_vis else f"{hs}-{vs}" stats[team]['last_result'] = ('W' if won else 'L') + ' ' + score stats[team]['last_date'] = g['date'] stats[team]['last_opp'] = opp # ── Next game from full schedule ────────────────────────────────────────── for g in full_sched: if g['completed']: continue for team in [g['vis_name'], g['home_name']]: if team not in stats: continue if stats[team]['next_game'] is None: opp = g['home_name'] if team == g['vis_name'] else g['vis_name'] loc = 'vs' if team == g['home_name'] else '@' if g['neutral'] and str(g['neutral']).strip() not in ('0',''): loc = 'vs' stats[team]['next_game'] = { 'game_id': g['game_id'], 'date': g['date'], 'opp': f"{loc} {opp}", 'opp_name': opp, 'game_type':g['game_type'] or '', 'stadium': g['stadium'] or '', 'start_time': str(g['start_time'] or ''), } # ── Tiebreaker sort ─────────────────────────────────────────────────────── def conf_pct(t): g = stats[t]['conf_w'] + stats[t]['conf_l'] return stats[t]['conf_w'] / g if g else 0 def h2h_pct(t1, t2): """Won-lost pct of t1 vs t2 in conference play.""" w = sum(1 for r in stats[t1]['conf_results'] if r['opp']==t2 and r['won']) l = sum(1 for r in stats[t1]['conf_results'] if r['opp']==t2 and not r['won']) return w/(w+l) if (w+l) else 0.5 def common_opp_pct(teams, target_team): """Won-lost pct of target_team against opponents common to all tied teams.""" common = None for t in teams: opps = set(r['opp'] for r in stats[t]['conf_results']) common = opps if common is None else common & opps if not common: return 0.5 w = sum(1 for r in stats[target_team]['conf_results'] if r['opp'] in common and r['won']) l = sum(1 for r in stats[target_team]['conf_results'] if r['opp'] in common and not r['won']) return w/(w+l) if (w+l) else 0.5 def all_conf_opp_pct(t): """Best cumulative W% vs all conference opponents.""" w = sum(1 for r in stats[t]['conf_results'] if r['won']) total = len(stats[t]['conf_results']) return w/total if total else 0 def break_tie(tied_teams): """Returns tied_teams sorted by tiebreaker rules.""" if len(tied_teams) == 1: return tied_teams if len(tied_teams) == 2: t1, t2 = tied_teams # A. Head-to-head p1, p2 = h2h_pct(t1,t2), h2h_pct(t2,t1) if p1 != p2: return sorted(tied_teams, key=lambda t: h2h_pct(t, [x for x in tied_teams if x!=t][0]), reverse=True) # B. Common opponents p1, p2 = common_opp_pct(tied_teams,t1), common_opp_pct(tied_teams,t2) if p1 != p2: return sorted(tied_teams, key=lambda t: common_opp_pct(tied_teams,t), reverse=True) # D. All conference opponents return sorted(tied_teams, key=all_conf_opp_pct, reverse=True) else: # 3+ team tie # A. Total W% among tied teams def among_pct(t): w = sum(1 for r in stats[t]['conf_results'] if r['opp'] in tied_teams and r['won']) l = sum(1 for r in stats[t]['conf_results'] if r['opp'] in tied_teams and not r['won']) return w/(w+l) if (w+l) else 0.5 pcts = {t: among_pct(t) for t in tied_teams} if len(set(pcts.values())) > 1: return sorted(tied_teams, key=lambda t: pcts[t], reverse=True) # B. Common opponents common_pcts = {t: common_opp_pct(tied_teams,t) for t in tied_teams} if len(set(common_pcts.values())) > 1: return sorted(tied_teams, key=lambda t: common_pcts[t], reverse=True) # D. All conference opponents return sorted(tied_teams, key=all_conf_opp_pct, reverse=True) # Initial sort by conf_pct ordered = sorted(SEC_TEAMS, key=conf_pct, reverse=True) # Break ties group by group final_order = [] i = 0 while i < len(ordered): j = i + 1 while j < len(ordered) and conf_pct(ordered[j]) == conf_pct(ordered[i]): j += 1 tied = ordered[i:j] final_order.extend(break_tie(tied)) i = j # ── Calculate games behind ──────────────────────────────────────────────── leader = final_order[0] leader_w = stats[leader]['conf_w'] leader_l = stats[leader]['conf_l'] # ── Build full schedule per team ────────────────────────────────────────── team_schedules = {t: [] for t in SEC_TEAMS} for g in full_sched: for team in [g['vis_name'], g['home_name']]: if team not in team_schedules: continue opp = g['home_name'] if team == g['vis_name'] else g['vis_name'] is_home = (team == g['home_name']) is_neutral = (g['game_type'] or '') == 'Neutral Site' loc = 'vs' if (is_home or is_neutral) else '@' result = None if g['completed']: ts = g['home_score'] if is_home else g['vis_score'] os_ = g['vis_score'] if is_home else g['home_score'] result = ('W' if ts > os_ else 'L') + f" {ts}-{os_}" team_schedules[team].append({ 'game_id': g['game_id'], 'date': g['date'], 'opp': f"{loc} {opp}", 'opp_name': opp, 'game_type': g['game_type'] or '', 'stadium': g['stadium'] or '', 'completed': bool(g['completed']), 'result': result, 'start_time': str(g['start_time'] or ''), 'opp_rank': g['vis_rank'] if is_home else g['home_rank'], }) # ── Merge upcoming games from Game Registry schedule ──────────────────── if schedule_path and os.path.exists(schedule_path): try: with open(schedule_path, encoding="utf-8") as sf: reg_games = json.load(sf) # Normalize date to M/D/YYYY (strip leading zeros) for consistent matching def norm_date(d): try: parts = d.split('/') return f"{int(parts[0])}/{int(parts[1])}/{parts[2]}" except (ValueError, IndexError): return d # Count (date, team, opp_name) occurrences from DB for doubleheader-aware dedup from collections import Counter known_counts = Counter() for team, sched in team_schedules.items(): for g in sched: known_counts[(norm_date(g['date']), team, g['opp_name'])] += 1 # Track how many schedule games we've already skipped per matchup skip_used = Counter() added = 0 for rg in reg_games: vis = normalize_team_name(rg.get('vis_name', '')) home = normalize_team_name(rg.get('home_name', '')) date = rg.get('date', '') game_type = rg.get('game_type', '') stadium = rg.get('stadium', '') start_time = rg.get('start_time', '') game_id = rg.get('game_id', '') for team in [vis, home]: if team not in team_schedules: continue opp = home if team == vis else vis # Skip if this game already exists from DB (match by date + teams) key = (norm_date(date), team, opp) if skip_used[key] < known_counts[key]: skip_used[key] += 1 continue is_home = (team == home) is_neutral = game_type == 'Neutral Site' loc = 'vs' if (is_home or is_neutral) else '@' team_schedules[team].append({ 'game_id': game_id, 'date': date, 'opp': f"{loc} {opp}", 'opp_name': opp, 'game_type': game_type, 'stadium': stadium, 'completed': False, 'result': None, 'start_time': start_time, 'opp_rank': None, }) added += 1 # Sort schedules by date after merging for team in team_schedules: team_schedules[team].sort(key=lambda g: _parse_date(g['date'])) # Re-derive next_game for teams that don't have one for team in SEC_TEAMS: if stats[team]['next_game'] is None: for g in team_schedules.get(team, []): if not g['completed']: stats[team]['next_game'] = { 'date': g['date'], 'opp': g['opp'], 'opp_name': g['opp_name'], 'game_type': g['game_type'], 'stadium': g['stadium'], 'start_time': g['start_time'], } break if added: print(f" Merged {added} upcoming game slots from schedule") except Exception as e: print(f" Warning: Could not read schedule file: {e}") # ── Assemble output ─────────────────────────────────────────────────────── standings = [] for rank, team in enumerate(final_order, 1): s = stats[team] cw, cl = s['conf_w'], s['conf_l'] ow, ol = s['overall_w'], s['overall_l'] gb = ((leader_w - cw) + (cl - leader_l)) / 2 gb_str = '—' if gb == 0 else f"{gb:.1f}".rstrip('0').rstrip('.') standings.append({ 'rank': rank, 'team': team, 'conf': f"{cw}-{cl}", 'conf_w': cw, 'conf_l': cl, 'conf_pct': f"{cw/(cw+cl):.3f}".lstrip('0') if (cw+cl) else '.000', 'overall': f"{ow}-{ol}", 'overall_w': ow, 'overall_l': ol, 'overall_pct':f"{ow/(ow+ol):.3f}".lstrip('0') if (ow+ol) else '.000', 'home': f"{s['home_w']}-{s['home_l']}", 'road': f"{s['road_w']}-{s['road_l']}", 'neutral': f"{s['neutral_w']}-{s['neutral_l']}", 'streak': f"{s['streak_type']}{s['streak']}" if s['streak_type'] else '—', 'games_back': gb_str, 'last_result':s['last_result'], 'last_opp': s.get('last_opp',''), 'last_date': s['last_date'], 'next_game': s['next_game'], 'schedule': team_schedules[team], }) write_json(standings, os.path.join(out_dir, "standings.json")) def export_manifest(conn, out_dir, season=None, conference_only=False): """Small manifest with metadata about the database.""" gids_m = get_season_game_ids(conn, season, conference_only) if gids_m is not None: ph = ','.join(['?']*len(gids_m)); gp = list(gids_m) games = conn.execute(f"SELECT COUNT(*) AS n FROM games WHERE game_id IN ({ph})", gp).fetchone()["n"] last = conn.execute(f"SELECT MAX(date) AS d FROM games WHERE game_id IN ({ph})", gp).fetchone()["d"] else: games = conn.execute("SELECT COUNT(*) AS n FROM games").fetchone()["n"] last = conn.execute("SELECT MAX(date) AS d FROM games").fetchone()["d"] # Detect seasons available in DB for the year switcher all_seasons = [r["season"] for r in conn.execute("SELECT DISTINCT season FROM games ORDER BY season DESC").fetchall()] # Per-team game counts for NCAA qualifier filter in the frontend if gids_m is not None: ph = ','.join(['?']*len(gids_m)); gp = list(gids_m) team_rows = conn.execute(f""" SELECT team_name, COUNT(*) AS g FROM ( SELECT vis_name AS team_name FROM game_results WHERE game_id IN ({ph}) UNION ALL SELECT home_name AS team_name FROM game_results WHERE game_id IN ({ph}) ) WHERE team_name IN ({','.join(['?']*len(SEC_TEAM_NAMES))}) GROUP BY team_name """, gp + gp + list(SEC_TEAM_NAMES)).fetchall() else: team_rows = conn.execute(f""" SELECT team_name, COUNT(*) AS g FROM ( SELECT vis_name AS team_name FROM game_results UNION ALL SELECT home_name AS team_name FROM game_results ) WHERE team_name IN ({','.join(['?']*len(SEC_TEAM_NAMES))}) GROUP BY team_name """, list(SEC_TEAM_NAMES)).fetchall() team_games = {r["team_name"]: r["g"] for r in team_rows} manifest = { "season": season, "games_count": games, "last_game_date": last, "seasons": all_seasons, "team_games": team_games, "exported_at": __import__("datetime").datetime.utcnow().isoformat(), } write_json(manifest, os.path.join(out_dir, "manifest.json")) # --------------------------------------------------------------------------- # Main # --------------------------------------------------------------------------- def main(): parser = argparse.ArgumentParser(description="Export SEC stats DB to JSON") parser.add_argument("--db", default=DB_PATH, help="Path to SQLite database") parser.add_argument("--out", default=OUT_DIR, help="Output directory") parser.add_argument("--season", default=None, help="Filter to a specific season (e.g. 2026). " "Defaults to the most recent season in the database.") parser.add_argument("--schedule", default=None, help="Path to sec_stats_schedule.json from Game Registry. " "Merges upcoming games into standings. " "Auto-detects from Google Drive if not specified.") args = parser.parse_args() if not os.path.exists(args.db): print(f"ERROR: Database not found: {args.db}") print("Run parse_games.py first to build the database.") sys.exit(1) conn = get_db(args.db) out = args.out os.makedirs(out, exist_ok=True) # Resolve season — use provided value or latest in DB if args.season: season = str(args.season) else: row = conn.execute("SELECT MAX(season) AS s FROM games").fetchone() season = row["s"] if row and row["s"] else None if not season: print("ERROR: No games in database.") sys.exit(1) # Resolve schedule file schedule_path = args.schedule if not schedule_path: # Auto-detect from Google Drive gdrive_path = os.path.expanduser( "~/Library/CloudStorage/GoogleDrive-tbrasher@sec.org" "/My Drive/SEC Stats/sec_stats_schedule.json" ) if os.path.exists(gdrive_path): schedule_path = gdrive_path print(f"Exporting to: {os.path.abspath(out)}/") print(f"Season: {season}") if schedule_path: print(f"Schedule: {schedule_path}") for scope, conf_only in [("overall", False), ("conference", True)]: scope_dir = os.path.join(out, scope) os.makedirs(scope_dir, exist_ok=True) print(f"\n [{scope.upper()}]") export_game_results(conn, scope_dir, season, conf_only) export_linescore(conn, scope_dir, season, conf_only) export_boxscores(conn, scope_dir, season, conf_only) export_team_batting(conn, scope_dir, season, conf_only) export_team_pitching(conn, scope_dir, season, conf_only) export_team_fielding(conn, scope_dir, season, conf_only) export_individual_batting(conn, scope_dir, season, conf_only) export_individual_pitching(conn, scope_dir, season, conf_only) export_individual_fielding(conn, scope_dir, season, conf_only) export_stat_leaders(conn, scope_dir, season, conf_only) export_game_highs(conn, scope_dir, season, conf_only) export_team_game_highs(conn, scope_dir, season, conf_only) export_player_game_logs(conn, scope_dir, season, conf_only) export_team_game_logs(conn, scope_dir, season, conf_only) export_manifest(conn, scope_dir, season, conf_only) if not conf_only: export_standings(conn, scope_dir, season, schedule_path) conn.close() print("\nAll JSON files exported successfully.") if __name__ == "__main__": main()