#!/usr/bin/env python3
"""
NCAA Football & Basketball Totals Analysis
Analyzes tempo and over/under patterns.
"""
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_totals(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()}_TOTALS'
        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()} totals for {season}...')
        
        cur.execute('''
            SELECT "homeTeam", "awayTeam", "homeScore", "awayScore", total
            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: {
            'games': 0, 'total_points': 0, 'overs': 0, 
            'high_totals': 0, 'high_overs': 0
        })

        for home, away, hscore, ascore, total in games:
            if hscore is None or ascore is None:
                continue
            
            game_total = hscore + ascore
            went_over = game_total > total if total else None
            
            # High total threshold: 55 for NCAAF, 145 for NCAAB
            high_thresh = 55 if league == 'ncaaf' else 145
            is_high = total and total > high_thresh
            
            for team in [home, away]:
                team_stats[team]['games'] += 1
                team_stats[team]['total_points'] += game_total
                if went_over:
                    team_stats[team]['overs'] += 1
                if is_high:
                    team_stats[team]['high_totals'] += 1
                    if went_over:
                        team_stats[team]['high_overs'] += 1

        for team, data in team_stats.items():
            if data['games'] < 5:
                continue
            
            metrics = {
                f'{league}_avg_total': data['total_points'] / data['games'],
                f'{league}_over_rate': data['overs'] / data['games'],
            }
            
            if data['high_totals'] >= 3:
                metrics[f'{league}_high_total_over_rate'] = data['high_overs'] / data['high_totals']
            
            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 totals 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_totals(season)