#!/usr/bin/env python3
"""
College Basketball Advanced Stats - CollegeBasketballData.com API
Fetches efficiency ratings, KenPom-style metrics, team stats, rankings
Uses same API key as CollegeFootballData.com
"""
import requests
import psycopg2
import os
from datetime import datetime, timezone

CBB_BASE = 'https://api.collegebasketballdata.com'

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 load_api_key():
    """Same key works for both CFBD and CBB"""
    with open('/var/www/html/eventheodds/.env', 'r') as f:
        for line in f:
            if line.startswith('CFBD_API_KEY='):
                return line.split('=', 1)[1].strip()
    return None

def fetch_team_stats(season=2025):
    """Fetch team season stats with nested teamStats/opponentStats"""
    key = load_api_key()
    if not key:
        return []
    
    headers = {'Authorization': f'Bearer {key}'}
    url = f'{CBB_BASE}/stats/team/season'
    params = {'season': season}
    
    print(f'Fetching CBB team stats for {season}...')
    try:
        resp = requests.get(url, params=params, headers=headers, timeout=60)
        if resp.status_code != 200:
            print(f'Team stats: {resp.status_code}')
            return []
        data = resp.json()
        print(f'  Found {len(data)} teams')
        return data
    except Exception as e:
        print(f'Error: {e}')
        return []

def fetch_rankings(season=2025):
    """Fetch AP rankings - returns list of team-week entries"""
    key = load_api_key()
    headers = {'Authorization': f'Bearer {key}'} if key else {}
    
    url = f'{CBB_BASE}/rankings'
    params = {'season': season}
    
    print(f'Fetching CBB rankings for {season}...')
    try:
        resp = requests.get(url, params=params, headers=headers, timeout=60)
        if resp.status_code != 200:
            print(f'Rankings: {resp.status_code}')
            return []
        data = resp.json()
        print(f'  Found {len(data)} ranking entries')
        return data
    except Exception as e:
        print(f'Error: {e}')
        return []

def ingest_cbb_advanced(season=2025):
    """Main ingest function"""
    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}_CBB_SEASON'
    game_date = datetime(season, 11, 1, tzinfo=timezone.utc)  # CBB starts in November
    
    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
    '''
    
    # 1. Team Stats (includes KenPom-style efficiency)
    team_stats = fetch_team_stats(season)
    team_count = 0
    for team in team_stats:
        team_name = team.get('team', '')
        if not team_name:
            continue
        
        # Basic metrics
        metrics = [
            ('cbb_games', team.get('games')),
            ('cbb_wins', team.get('wins')),
            ('cbb_losses', team.get('losses')),
            ('cbb_pace', team.get('pace')),  # Tempo/possessions per game
        ]
        
        # Team offensive stats
        ts = team.get('teamStats', {}) or {}
        metrics.extend([
            ('cbb_off_rating', ts.get('rating')),  # Offensive efficiency
            ('cbb_true_shooting', ts.get('trueShooting')),
            ('cbb_possessions', ts.get('possessions')),
            ('cbb_assists', ts.get('assists')),
            ('cbb_blocks', ts.get('blocks')),
            ('cbb_steals', ts.get('steals')),
        ])
        
        # Field goals
        fg = ts.get('fieldGoals', {}) or {}
        metrics.extend([
            ('cbb_fg_pct', fg.get('pct')),
            ('cbb_fg_made', fg.get('made')),
            ('cbb_fg_attempted', fg.get('attempted')),
        ])
        
        # Three pointers
        three = ts.get('threePointFieldGoals', {}) or {}
        metrics.extend([
            ('cbb_three_pct', three.get('pct')),
            ('cbb_three_made', three.get('made')),
        ])
        
        # Free throws
        ft = ts.get('freeThrows', {}) or {}
        metrics.extend([
            ('cbb_ft_pct', ft.get('pct')),
        ])
        
        # Rebounds
        reb = ts.get('rebounds', {}) or {}
        metrics.extend([
            ('cbb_off_rebounds', reb.get('offensive')),
            ('cbb_def_rebounds', reb.get('defensive')),
            ('cbb_total_rebounds', reb.get('total')),
        ])
        
        # Turnovers
        to = ts.get('turnovers', {}) or {}
        metrics.extend([
            ('cbb_turnovers', to.get('total')),
        ])
        
        # Opponent/defensive stats
        opp = team.get('opponentStats', {}) or {}
        metrics.extend([
            ('cbb_def_rating', opp.get('rating')),  # Defensive efficiency
            ('cbb_opp_true_shooting', opp.get('trueShooting')),
        ])
        
        opp_fg = opp.get('fieldGoals', {}) or {}
        metrics.extend([
            ('cbb_opp_fg_pct', opp_fg.get('pct')),
        ])
        
        opp_three = opp.get('threePointFieldGoals', {}) or {}
        metrics.extend([
            ('cbb_opp_three_pct', opp_three.get('pct')),
        ])
        
        # Calculate net rating (offensive - defensive)
        off_rating = ts.get('rating')
        def_rating = opp.get('rating')
        if off_rating and def_rating:
            metrics.append(('cbb_net_rating', off_rating - def_rating))
        
        for stat_key, val in metrics:
            if val is not None:
                try:
                    cur.execute(sql, ('ncaab', season, game_key, game_date, team_name, stat_key, float(val)))
                    stats_added += 1
                    team_count += 1
                except Exception as e:
                    conn.rollback()
    
    conn.commit()
    print(f'  Team stats/efficiency added: {team_count}')
    
    # 2. Rankings (AP Top 25)
    rankings = fetch_rankings(season)
    
    # Group by week to find preseason (week 1) and latest
    by_week = {}
    for entry in rankings:
        if entry.get('pollType') != 'AP Top 25':
            continue
        week = entry.get('week', 0)
        if week not in by_week:
            by_week[week] = []
        by_week[week].append(entry)
    
    weeks_sorted = sorted(by_week.keys())
    preseason_week = weeks_sorted[0] if weeks_sorted else None
    latest_week = weeks_sorted[-1] if weeks_sorted else None
    
    rankings_count = 0
    
    # Preseason rankings
    if preseason_week is not None and preseason_week in by_week:
        print(f'  Preseason week: {preseason_week}')
        for entry in by_week[preseason_week]:
            team_name = entry.get('team', '')
            rank = entry.get('ranking')
            if team_name and rank:
                try:
                    cur.execute(sql, ('ncaab', season, game_key, game_date, team_name, 'cbb_preseason_rank_ap', float(rank)))
                    stats_added += 1
                    rankings_count += 1
                except:
                    conn.rollback()
    
    conn.commit()
    
    # Current/latest rankings
    if latest_week is not None and latest_week in by_week:
        print(f'  Latest week: {latest_week}')
        for entry in by_week[latest_week]:
            team_name = entry.get('team', '')
            rank = entry.get('ranking')
            points = entry.get('points')
            first_place = entry.get('firstPlaceVotes')
            if team_name and rank:
                try:
                    cur.execute(sql, ('ncaab', season, game_key, game_date, team_name, 'cbb_current_rank_ap', float(rank)))
                    stats_added += 1
                    rankings_count += 1
                    if points:
                        cur.execute(sql, ('ncaab', season, game_key, game_date, team_name, 'cbb_current_points_ap', float(points)))
                        stats_added += 1
                        rankings_count += 1
                    if first_place:
                        cur.execute(sql, ('ncaab', season, game_key, game_date, team_name, 'cbb_first_place_votes', float(first_place)))
                        stats_added += 1
                        rankings_count += 1
                except:
                    conn.rollback()
    
    conn.commit()
    print(f'  Rankings added: {rankings_count}')
    
    # 3. Calculate rank changes (preseason vs current)
    print('Calculating CBB rank changes...')
    cur.execute('''
        SELECT t1.team, 
               t1.value as preseason,
               t2.value as current,
               (t1.value - t2.value) as change
        FROM "TeamGameMetric" t1
        JOIN "TeamGameMetric" t2 ON t1.team = t2.team 
            AND t1.league = t2.league 
            AND t1.season = t2.season
        WHERE t1.league = 'ncaab' 
            AND t1.season = %s
            AND t1."statKey" = 'cbb_preseason_rank_ap'
            AND t2."statKey" = 'cbb_current_rank_ap'
    ''', (season,))
    
    change_count = 0
    for row in cur.fetchall():
        team_name, preseason, current, change = row
        if change is not None:
            try:
                cur.execute(sql, ('ncaab', season, game_key, game_date, team_name, 'cbb_rank_change_ap', float(change)))
                stats_added += 1
                change_count += 1
            except:
                conn.rollback()
    
    conn.commit()
    print(f'  Rank changes added: {change_count}')
    
    # 4. Identify top efficiency teams
    print('Identifying top efficiency teams...')
    cur.execute('''
        SELECT team, value as net_rating
        FROM "TeamGameMetric"
        WHERE league = 'ncaab' AND season = %s AND "statKey" = 'cbb_net_rating'
        ORDER BY value DESC
        LIMIT 25
    ''', (season,))
    
    for i, row in enumerate(cur.fetchall(), 1):
        team_name, net_rating = row
        try:
            cur.execute(sql, ('ncaab', season, game_key, game_date, team_name, 'cbb_efficiency_rank', float(i)))
            stats_added += 1
        except:
            conn.rollback()
    
    conn.commit()
    print(f'  Efficiency ranks added: 25')
    
    cur.close()
    conn.close()
    
    print(f'')
    print(f'✅ CBB Advanced Stats ingested: {stats_added} total metrics')
    return {'success': True, 'stats_added': stats_added}

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