#!/usr/bin/env python3
"""
NCAA Travel & Road Performance Analysis
Tracks home/away splits and road performance for smaller programs.
"""
import psycopg2
from datetime import datetime, timezone
from collections import defaultdict
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 ingest_travel(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

    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
    '''

    for league in ['ncaaf', 'ncaab']:
        game_key = f'{season}_{league.upper()}_TRAVEL'
        game_date_base = datetime(season, 9, 1, tzinfo=timezone.utc) if league == 'ncaaf' else datetime(season, 11, 1, tzinfo=timezone.utc)
        
        print(f'Analyzing {league.upper()} travel for {season}...')
        
        cur.execute('''
            SELECT "homeTeam", "awayTeam", "homeScore", "awayScore", "spreadHome"
            FROM "SportsGame"
            WHERE league = %s AND season = %s AND status = 'Final'
        ''', (league, season))
        
        games = cur.fetchall()
        print(f'  Games: {len(games)}')
        
        if len(games) == 0:
            continue

        team_stats = defaultdict(lambda: {
            'home_games': 0, 'home_wins': 0, 'home_covers': 0,
            'away_games': 0, 'away_wins': 0, 'away_covers': 0
        })

        for home, away, hscore, ascore, spread_home in games:
            if hscore is None or ascore is None:
                continue
            
            margin = hscore - ascore
            home_won = margin > 0
            home_covered = margin > -spread_home if spread_home else None
            
            team_stats[home]['home_games'] += 1
            if home_won:
                team_stats[home]['home_wins'] += 1
            if home_covered:
                team_stats[home]['home_covers'] += 1
            
            team_stats[away]['away_games'] += 1
            if not home_won:
                team_stats[away]['away_wins'] += 1
            if home_covered is not None and not home_covered:
                team_stats[away]['away_covers'] += 1

        for team, data in team_stats.items():
            metrics = {}
            
            if data['home_games'] >= 3:
                metrics[f'{league}_home_win_rate'] = data['home_wins'] / data['home_games']
                if data['home_covers'] > 0:
                    metrics[f'{league}_home_cover_rate'] = data['home_covers'] / data['home_games']
            
            if data['away_games'] >= 3:
                metrics[f'{league}_away_win_rate'] = data['away_wins'] / data['away_games']
                if data['away_covers'] > 0:
                    metrics[f'{league}_away_cover_rate'] = data['away_covers'] / data['away_games']
            
            for stat_key, val in metrics.items():
                cur.execute(sql, (league, season, game_key, game_date_base, team, stat_key, float(val)))
                stats_added += 1

    conn.commit()
    cur.close()
    conn.close()
    print(f'✅ NCAA travel 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_travel(season)