#!/usr/bin/env python3
"""
MLB Team Pitching via ESPN API
Gets team-level pitching metrics for matchup analysis.
"""
import requests
import psycopg2
from datetime import datetime, timezone
import sys


def load_db_url():
    with open('/var/www/html/eventheodds/.env', 'r') as f:
        for line in f:
            if line.startswith('SPORTS_DATABASE_URL='):
                return line.split('=', 1)[1].strip().split('?')[0]
    return ''


def get_espn_stats(team_id):
    url = f'https://site.api.espn.com/apis/site/v2/sports/baseball/mlb/teams/{team_id}/statistics'
    try:
        resp = requests.get(url, timeout=10)
        if resp.ok:
            return resp.json()
    except:
        pass
    return None


def parse_stats(data):
    if not data or 'results' not in data:
        return {}
    stats = {}
    try:
        categories = data['results']['stats'].get('categories', [])
        for cat in categories:
            for stat in cat.get('stats', []):
                stats[stat.get('name', '')] = stat.get('value')
    except:
        pass
    return stats


def ingest_pitching(season=2024):
    db_url = load_db_url()
    if not db_url:
        raise RuntimeError('SPORTS_DATABASE_URL not set')

    conn = psycopg2.connect(db_url)
    cur = conn.cursor()
    stats_added = 0

    game_key = f'{season}_MLB_PITCHING'
    game_date = datetime(season, 4, 1, tzinfo=timezone.utc)

    sql = '''
        INSERT INTO "TeamGameMetric"
        (league, season, "gameKey", "gameDate", team, "statKey", value)
        VALUES (%s, %s, %s, %s, %s, %s, %s)
        ON CONFLICT (league, season, "gameKey", team, "statKey")
        DO UPDATE SET value = EXCLUDED.value
    '''

    print(f'Fetching MLB pitching via ESPN...')
    
    teams_resp = requests.get('https://site.api.espn.com/apis/site/v2/sports/baseball/mlb/teams', timeout=10)
    if not teams_resp.ok:
        return {'success': False}
    
    teams = teams_resp.json()['sports'][0]['leagues'][0]['teams']
    print(f'  Teams: {len(teams)}')

    for team_data in teams:
        team = team_data['team']
        abbr = team['abbreviation']
        team_id = team['id']
        
        data = get_espn_stats(team_id)
        stats = parse_stats(data)
        if not stats:
            continue

        metrics = {
            'mlb_team_era': stats.get('ERA'),
            'mlb_team_whip': stats.get('WHIP'),
            'mlb_team_k9': stats.get('strikeoutsPerNineInnings'),
            'mlb_team_bb9': stats.get('walksPerNineInnings'),
            'mlb_team_hr9': stats.get('homeRunsPerNineInnings'),
            'mlb_team_avg_against': stats.get('opponentBattingAverage'),
            'mlb_team_saves': stats.get('saves'),
            'mlb_team_qstarts': stats.get('qualityStarts'),
        }
        
        for stat_key, val in metrics.items():
            if val is not None:
                cur.execute(sql, ('mlb', season, game_key, game_date, abbr, stat_key, float(val)))
                stats_added += 1

    conn.commit()
    cur.close()
    conn.close()
    print(f'✅ MLB pitching ingested: {stats_added}')
    return {'success': True, 'stats_added': stats_added}


if __name__ == '__main__':
    season = int(sys.argv[1]) if len(sys.argv) > 1 else 2024
    ingest_pitching(season)