#!/usr/bin/env python3
import requests
import psycopg2
import os
from datetime import datetime, timezone

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_cfb_rankings(year=2025):
    url = 'https://site.api.espn.com/apis/site/v2/sports/football/college-football/rankings'
    print(f'Fetching CFB rankings for {year}...')
    
    resp = requests.get(url, timeout=15)
    if resp.status_code != 200:
        print(f'Error: {resp.status_code}')
        return {'success': False}
    
    data = resp.json()
    rankings = data.get('rankings', [])
    
    db_url = load_db_url()
    conn = psycopg2.connect(db_url)
    cur = conn.cursor()
    stats_added = 0
    
    game_key = f'{year}_CFB_RANKINGS'
    game_date = datetime(year, 8, 15, tzinfo=timezone.utc)
    
    for ranking_type in rankings:
        type_name = ranking_type.get('name', 'Unknown')
        type_key = type_name.lower().replace(' ', '_').replace('-', '_')
        
        for team_rank in ranking_type.get('ranks', []):
            team = team_rank.get('team', {})
            team_name = team.get('nickname') or team.get('name') or 'Unknown'
            rank = team_rank.get('current')
            prev_rank = team_rank.get('previous')
            points = team_rank.get('points')
            record_summary = team_rank.get('recordSummary', '')
            
            if not team_name or rank is None:
                continue
            
            # Parse record
            wins, losses = 0, 0
            if record_summary and '-' in record_summary:
                parts = record_summary.split('-')
                try:
                    wins = int(parts[0])
                    losses = int(parts[1])
                except:
                    pass
            
            metrics = [
                (f'cfb_rank_{type_key}', rank),
                (f'cfb_prev_rank_{type_key}', prev_rank),
                (f'cfb_rank_points_{type_key}', points),
            ]
            
            # Only add wins/losses once per team
            if type_key == 'ap_top_25':
                metrics.extend([
                    ('cfb_wins', wins),
                    ('cfb_losses', losses),
                ])
            
            for stat_key, val in metrics:
                if val is not None:
                    try:
                        cur.execute('''
                            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
                        ''', ('ncaaf', year, game_key, game_date, team_name, stat_key, float(val)))
                        stats_added += 1
                    except Exception as e:
                        print(f'DB Error: {e}')
                        conn.rollback()
        
        print(f'  {type_name}: {len(ranking_type.get(ranks, []))} teams')
    
    conn.commit()
    cur.close()
    conn.close()
    
    print(f'')
    print(f'✅ CFB Rankings ingested: {stats_added} metrics')
    return {'success': True, 'stats_added': stats_added}

if __name__ == '__main__':
    import sys
    year = int(sys.argv[1]) if len(sys.argv) > 1 else 2025
    ingest_cfb_rankings(year)