#!/var/www/html/eventheodds/.venv-scraper/bin/python
"""
Enhanced Player Props Aggregator
Combines player stats from multiple free sources to generate prop projections
and enhance the PlayerPropLine table for dynamic user questions.

NOTE: External APIs (PrizePicks, NumberFire) are often blocked or rate-limited.
The script will still generate projections from local player stats data.
For real-time props, use SportsGameOdds integration (scripts/sync-sgo-props.js).
"""
import requests
import psycopg2
import json
import os
import time
from datetime import datetime, timezone, timedelta
from bs4 import BeautifulSoup

HEADERS = {
    'User-Agent': 'Mozilla/5.0 (compatible; EvenTheOdds/1.0)',
    'Accept': 'application/json, text/html',
}

def load_db_url():
    """Load database URL from environment"""
    env_paths = [
        '/var/www/html/eventheodds/.env',
        os.path.join(os.path.dirname(__file__), '..', '.env'),
    ]
    for env_path in env_paths:
        try:
            with open(env_path, 'r') as f:
                for line in f:
                    if line.startswith('SPORTS_DATABASE_URL='):
                        return line.split('=', 1)[1].strip().split('?')[0]
        except FileNotFoundError:
            continue
    return os.environ.get('SPORTS_DATABASE_URL', '').split('?')[0]


def fetch_prizepicks_public_props():
    """
    Attempt to fetch props from PrizePicks public data
    Note: This may be rate-limited or blocked
    """
    print("\n[PrizePicks] Attempting to fetch public props...")

    # PrizePicks has a public projections endpoint
    url = "https://api.prizepicks.com/projections"

    try:
        resp = requests.get(url, headers=HEADERS, timeout=30)
        if resp.status_code != 200:
            print(f"  Status: {resp.status_code}")
            return []

        data = resp.json()
        props = []

        for projection in data.get('data', []):
            attrs = projection.get('attributes', {})
            props.append({
                'playerId': projection.get('relationships', {}).get('new_player', {}).get('data', {}).get('id'),
                'statType': attrs.get('stat_type'),
                'line': attrs.get('line_score'),
                'description': attrs.get('description'),
                'gameTime': attrs.get('start_time'),
                'source': 'prizepicks',
            })

        print(f"  Found {len(props)} props")
        return props

    except Exception as e:
        print(f"  Error: {e}")
        return []


def fetch_espn_player_projections(sport='nba'):
    """
    Fetch player projections/fantasy points from ESPN
    """
    print(f"\n[ESPN] Fetching {sport.upper()} player projections...")

    # ESPN fantasy projections endpoint
    sport_ids = {
        'nba': 'basketball/nba',
        'nfl': 'football/nfl',
        'mlb': 'baseball/mlb',
        'nhl': 'hockey/nhl',
    }

    sport_path = sport_ids.get(sport, sport)
    url = f"https://site.api.espn.com/apis/site/v2/sports/{sport_path}/teams"

    try:
        resp = requests.get(url, headers=HEADERS, timeout=30)
        if resp.status_code != 200:
            print(f"  Status: {resp.status_code}")
            return []

        data = resp.json()
        teams = []

        for team in data.get('sports', [{}])[0].get('leagues', [{}])[0].get('teams', []):
            team_info = team.get('team', {})
            teams.append({
                'teamId': team_info.get('id'),
                'teamName': team_info.get('displayName'),
                'abbreviation': team_info.get('abbreviation'),
                'sport': sport,
            })

        print(f"  Found {len(teams)} teams")
        return teams

    except Exception as e:
        print(f"  Error: {e}")
        return []


def fetch_numberfire_projections(sport='nba'):
    """
    Scrape player projections from NumberFire (free DFS projections)
    """
    print(f"\n[NumberFire] Fetching {sport.upper()} projections...")

    urls = {
        'nba': 'https://www.numberfire.com/nba/daily-fantasy/daily-basketball-projections',
        'nfl': 'https://www.numberfire.com/nfl/daily-fantasy/daily-football-projections',
        'mlb': 'https://www.numberfire.com/mlb/daily-fantasy/daily-baseball-projections',
    }

    url = urls.get(sport)
    if not url:
        return []

    try:
        time.sleep(2)
        resp = requests.get(url, headers=HEADERS, timeout=30)
        if resp.status_code != 200:
            print(f"  Status: {resp.status_code}")
            return []

        soup = BeautifulSoup(resp.text, 'html.parser')

        # Find projection table
        table = soup.find('table', {'class': 'projection-table'})
        if not table:
            print("  Could not find projection table")
            return []

        projections = []
        tbody = table.find('tbody')
        if tbody:
            for row in tbody.find_all('tr'):
                cells = row.find_all('td')
                if len(cells) < 5:
                    continue

                try:
                    player_cell = cells[0]
                    player_name = player_cell.text.strip()

                    projections.append({
                        'playerName': player_name,
                        'sport': sport,
                        'projectedPoints': cells[2].text.strip() if len(cells) > 2 else None,
                        'salary': cells[1].text.strip() if len(cells) > 1 else None,
                        'source': 'numberfire',
                    })
                except:
                    continue

        print(f"  Found {len(projections)} projections")
        return projections

    except Exception as e:
        print(f"  Error: {e}")
        return []


def fetch_rotowire_projections(sport='nba'):
    """
    Scrape daily projections from Rotowire
    """
    print(f"\n[Rotowire] Fetching {sport.upper()} projections...")

    urls = {
        'nba': 'https://www.rotowire.com/basketball/daily-projections.php',
        'nfl': 'https://www.rotowire.com/football/daily-projections.php',
        'mlb': 'https://www.rotowire.com/baseball/daily-projections.php',
        'nhl': 'https://www.rotowire.com/hockey/daily-projections.php',
    }

    url = urls.get(sport)
    if not url:
        return []

    try:
        time.sleep(2)
        resp = requests.get(url, headers=HEADERS, timeout=30)
        if resp.status_code != 200:
            print(f"  Status: {resp.status_code}")
            return []

        soup = BeautifulSoup(resp.text, 'html.parser')

        # Find projection table
        table = soup.find('table', {'class': lambda x: x and 'projected' in str(x).lower()})
        if not table:
            table = soup.find('table')

        projections = []
        if table:
            tbody = table.find('tbody')
            if tbody:
                for row in tbody.find_all('tr')[:50]:  # Limit to top 50
                    cells = row.find_all('td')
                    if len(cells) < 3:
                        continue

                    try:
                        player_cell = cells[0]
                        player_link = player_cell.find('a')
                        player_name = player_link.text.strip() if player_link else player_cell.text.strip()

                        projections.append({
                            'playerName': player_name,
                            'sport': sport,
                            'source': 'rotowire',
                        })
                    except:
                        continue

        print(f"  Found {len(projections)} projections")
        return projections

    except Exception as e:
        print(f"  Error: {e}")
        return []


def generate_stat_projections(player_stats):
    """
    Generate prop projections based on player season stats
    Returns list of prop lines for each player
    """
    projections = []

    for player in player_stats:
        player_name = player.get('playerName', '')
        league = player.get('league', '')

        if league == 'nba':
            # NBA props: points, rebounds, assists, threes, steals+blocks
            ppg = player.get('pointsPerGame')
            rpg = player.get('reboundsPerGame')
            apg = player.get('assistsPerGame')

            if ppg:
                projections.append({
                    'playerName': player_name,
                    'league': league,
                    'propType': 'points',
                    'projectedLine': round(ppg, 1),
                    'confidence': 0.7,
                    'source': 'stats_projection',
                })

            if rpg:
                projections.append({
                    'playerName': player_name,
                    'league': league,
                    'propType': 'rebounds',
                    'projectedLine': round(rpg, 1),
                    'confidence': 0.7,
                    'source': 'stats_projection',
                })

            if apg:
                projections.append({
                    'playerName': player_name,
                    'league': league,
                    'propType': 'assists',
                    'projectedLine': round(apg, 1),
                    'confidence': 0.7,
                    'source': 'stats_projection',
                })

            # Combo props
            if ppg and rpg and apg:
                pra = ppg + rpg + apg
                projections.append({
                    'playerName': player_name,
                    'league': league,
                    'propType': 'pts+reb+ast',
                    'projectedLine': round(pra, 1),
                    'confidence': 0.6,
                    'source': 'stats_projection',
                })

        elif league == 'nhl':
            # NHL props: goals, assists, points, shots
            goals = player.get('goals')
            assists = player.get('assists')
            gp = player.get('gamesPlayed') or 1

            if goals:
                gpg = goals / gp
                projections.append({
                    'playerName': player_name,
                    'league': league,
                    'propType': 'goals',
                    'projectedLine': round(gpg, 2),
                    'confidence': 0.6,
                    'source': 'stats_projection',
                })

            if assists:
                apg = assists / gp
                projections.append({
                    'playerName': player_name,
                    'league': league,
                    'propType': 'assists',
                    'projectedLine': round(apg, 2),
                    'confidence': 0.6,
                    'source': 'stats_projection',
                })

        elif league == 'nfl':
            # NFL props: passing yards, rushing yards, receiving yards, TDs
            pass_yards = player.get('passYards')
            pass_tds = player.get('passTouchdowns')
            rush_yards = player.get('rushYards')
            gp = player.get('gamesPlayed') or 1

            if pass_yards:
                ypg = pass_yards / gp
                projections.append({
                    'playerName': player_name,
                    'league': league,
                    'propType': 'passing_yards',
                    'projectedLine': round(ypg, 0),
                    'confidence': 0.65,
                    'source': 'stats_projection',
                })

            if pass_tds:
                tdpg = pass_tds / gp
                projections.append({
                    'playerName': player_name,
                    'league': league,
                    'propType': 'passing_tds',
                    'projectedLine': round(tdpg, 1),
                    'confidence': 0.6,
                    'source': 'stats_projection',
                })

            if rush_yards:
                rypg = rush_yards / gp
                projections.append({
                    'playerName': player_name,
                    'league': league,
                    'propType': 'rushing_yards',
                    'projectedLine': round(rypg, 0),
                    'confidence': 0.65,
                    'source': 'stats_projection',
                })

        elif league == 'mlb':
            # MLB props: hits, runs, RBIs, HRs
            hr = player.get('homeRuns')
            hits = player.get('hits')
            gp = player.get('gamesPlayed') or 1

            if hr:
                hrpg = hr / gp
                projections.append({
                    'playerName': player_name,
                    'league': league,
                    'propType': 'home_runs',
                    'projectedLine': round(hrpg, 2),
                    'confidence': 0.5,
                    'source': 'stats_projection',
                })

    return projections


def save_projections_to_db(conn, projections):
    """Save prop projections to database"""
    if not projections:
        return 0

    cur = conn.cursor()
    saved = 0

    for proj in projections:
        try:
            # Generate a unique ID for the prop line
            player_id = proj.get('playerName', '').lower().replace(' ', '_')
            prop_type = proj.get('propType', '')
            league = proj.get('league', '')

            cur.execute('''
                INSERT INTO "PlayerPropLine" (
                    "playerId", "playerName", "propType", "line",
                    "league", "gameDate", "source", "raw", "createdAt"
                )
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, NOW())
                ON CONFLICT DO NOTHING
            ''', (
                player_id,
                proj.get('playerName'),
                prop_type,
                proj.get('projectedLine'),
                league,
                datetime.now(timezone.utc).date(),
                proj.get('source', 'projection'),
                json.dumps(proj),
            ))
            saved += 1

        except Exception as e:
            conn.rollback()
            continue

    conn.commit()
    cur.close()
    return saved


def load_player_stats_from_file():
    """Load previously scraped player stats"""
    stats_file = '/var/www/html/eventheodds/data/sportsreference_stats.json'
    try:
        with open(stats_file, 'r') as f:
            data = json.load(f)
            return data.get('players', [])
    except:
        return []


def main():
    print("=" * 60)
    print("ENHANCED PLAYER PROPS AGGREGATOR")
    print(f"Time: {datetime.now(timezone.utc).isoformat()}")
    print("=" * 60)

    db_url = load_db_url()
    all_projections = []

    # 1. Load player stats from Sports-Reference scrape
    player_stats = load_player_stats_from_file()
    print(f"\nLoaded {len(player_stats)} players from stats file")

    # 2. Generate projections from stats
    if player_stats:
        stats_projections = generate_stat_projections(player_stats)
        all_projections.extend(stats_projections)
        print(f"Generated {len(stats_projections)} stat-based projections")

    # 3. Fetch from external sources (may be rate-limited)
    # PrizePicks
    pp_props = fetch_prizepicks_public_props()
    all_projections.extend(pp_props)

    # NumberFire (for each sport)
    for sport in ['nba', 'nfl']:
        nf_projections = fetch_numberfire_projections(sport)
        all_projections.extend(nf_projections)

    # Summary
    print(f"\n{'='*60}")
    print(f"TOTAL PROJECTIONS: {len(all_projections)}")

    # Save to JSON
    output_dir = '/var/www/html/eventheodds/data'
    os.makedirs(output_dir, exist_ok=True)
    output_path = os.path.join(output_dir, 'player_props_enhanced.json')

    with open(output_path, 'w') as f:
        json.dump({
            'projections': all_projections,
            'count': len(all_projections),
            'timestamp': datetime.now(timezone.utc).isoformat(),
        }, f, indent=2, default=str)

    print(f"Saved to {output_path}")

    # Save to database if available
    if db_url and all_projections:
        try:
            conn = psycopg2.connect(db_url)
            saved = save_projections_to_db(conn, all_projections)
            conn.close()
            print(f"Saved {saved} projections to database")
        except Exception as e:
            print(f"Database error: {e}")

    print("=" * 60)


if __name__ == '__main__':
    main()