#!/usr/bin/env python3
"""
On-Demand Data Fetcher - All Sports
Pull data immediately from all available sources to minimize "no data" responses.

Usage:
    python fetch_ondemand.py odds <league>        - Current odds from DB
    python fetch_ondemand.py live <league>        - Live odds from The Odds API (real-time)
    python fetch_ondemand.py props <league>       - Player props from The Odds API
    python fetch_ondemand.py games <league>       - Upcoming games with odds
    python fetch_ondemand.py halflines <league>   - Half-time odds (OddsPortal)
    python fetch_ondemand.py refresh <league>     - Refresh all data for league
    python fetch_ondemand.py snapshot             - Capture odds snapshot

Supported: nba, nfl, nhl, mlb, ncaab, ncaaf, mma, epl, laliga, seriea, bundesliga, ligue1, ucl
"""
import sys
import os
import json
import re
import time
import random
import psycopg2
import requests
from datetime import datetime, timezone, timedelta
from dotenv import load_dotenv

VENV_PATH = '/var/www/html/eventheodds/.venv-scraper/lib/python3.12/site-packages'
if VENV_PATH not in sys.path:
    sys.path.insert(0, VENV_PATH)

load_dotenv('/var/www/html/eventheodds/.env')

def get_db():
    url = os.environ.get('SPORTS_DATABASE_URL', '').split('?')[0]
    return psycopg2.connect(url)

PROXY_REQUESTS = {
    'http': 'socks5h://goDRdyJf:1aIFz9LNOW@82.163.172.166:50101',
    'https': 'socks5h://goDRdyJf:1aIFz9LNOW@82.163.172.166:50101'
}
PROXY_PLAYWRIGHT = {'server': 'socks5://54.38.19.233:3028'}

# All supported leagues
LEAGUES = {
    'nba': {'name': 'NBA', 'oddsportal': '/basketball/usa/nba/', 'api': 'v1/games', 'type': 'basketball'},
    'nfl': {'name': 'NFL', 'oddsportal': '/american-football/usa/nfl/', 'api': 'nfl/v1/games', 'type': 'football'},
    'nhl': {'name': 'NHL', 'oddsportal': '/hockey/usa/nhl/', 'type': 'hockey'},
    'mlb': {'name': 'MLB', 'oddsportal': '/baseball/usa/mlb/', 'type': 'baseball'},
    'ncaab': {'name': 'NCAAB', 'oddsportal': '/basketball/usa/ncaa/', 'type': 'basketball'},
    'ncaaf': {'name': 'NCAAF', 'oddsportal': '/american-football/usa/ncaa/', 'type': 'football'},
    'mma': {'name': 'MMA/UFC', 'oddsportal': '/mma/ufc/', 'type': 'mma'},
    'epl': {'name': 'Premier League', 'oddsportal': '/football/england/premier-league/', 'fd': 'PL', 'type': 'soccer'},
    'laliga': {'name': 'La Liga', 'oddsportal': '/football/spain/laliga/', 'fd': 'PD', 'type': 'soccer'},
    'seriea': {'name': 'Serie A', 'oddsportal': '/football/italy/serie-a/', 'fd': 'SA', 'type': 'soccer'},
    'bundesliga': {'name': 'Bundesliga', 'oddsportal': '/football/germany/bundesliga/', 'fd': 'BL1', 'type': 'soccer'},
    'ligue1': {'name': 'Ligue 1', 'oddsportal': '/football/france/ligue-1/', 'fd': 'FL1', 'type': 'soccer'},
    'ucl': {'name': 'Champions League', 'oddsportal': '/football/europe/champions-league/', 'fd': 'CL', 'type': 'soccer'},
}

# The Odds API sport keys
THEODDS_SPORT_KEYS = {
    'nba': 'basketball_nba',
    'nfl': 'americanfootball_nfl',
    'nhl': 'icehockey_nhl',
    'mlb': 'baseball_mlb',
    'ncaab': 'basketball_ncaab',
    'ncaaf': 'americanfootball_ncaaf',
}

# The Odds API player prop markets
THEODDS_PROP_MARKETS = {
    'nba': ['player_points', 'player_rebounds', 'player_assists', 'player_threes', 'player_points_rebounds_assists'],
    'nfl': ['player_pass_tds', 'player_pass_yds', 'player_rush_yds', 'player_reception_yds', 'player_receptions', 'player_anytime_td'],
    'nhl': ['player_goals', 'player_shots_on_goal'],
}


def get_theoddsapi_key():
    """Get The Odds API key from environment"""
    return os.environ.get('THE_ODDS_API_KEY', '')


def fetch_odds(league):
    """Fetch current odds from database for upcoming games"""
    if league not in LEAGUES:
        print(f"Unknown league: {league}")
        print(f"Available: {', '.join(LEAGUES.keys())}")
        return

    config = LEAGUES[league]
    print(f"\n{'='*50}")
    print(f"{config['name']} - Current Odds")
    print(f"{'='*50}")

    conn = get_db()
    cur = conn.cursor()

    # Get upcoming games with odds
    cur.execute('''
        SELECT "homeTeam", "awayTeam", "gameDate",
               "spreadHome", "spreadAway", total,
               "moneylineHome", "moneylineAway"
        FROM "SportsGame"
        WHERE league = %s
          AND "gameDate" > NOW()
          AND "gameDate" < NOW() + INTERVAL '7 days'
        ORDER BY "gameDate"
        LIMIT 20
    ''', (league,))

    games = cur.fetchall()

    if games:
        print(f"\nFound {len(games)} upcoming games:\n")
        for g in games:
            home, away, date, spread_h, spread_a, total, ml_h, ml_a = g
            date_str = date.strftime('%a %m/%d %I:%M%p') if date else 'TBD'
            print(f"  {away[:18]:18} @ {home[:18]:18}  {date_str}")
            if spread_h or total or ml_h:
                odds_line = []
                if spread_h: odds_line.append(f"Spread: {spread_h:+.1f}")
                if total: odds_line.append(f"O/U: {total}")
                if ml_h: odds_line.append(f"ML: {int(ml_h):+d}/{int(ml_a):+d}" if ml_a else f"ML: {int(ml_h):+d}")
                print(f"    {' | '.join(odds_line)}")
            print()
    else:
        print(f"\nNo upcoming games found in database.")
        print("Checking OddsPortal for live data...")
        fetch_oddsportal_quick(league)

    # Also show half-line data if available
    cur.execute('''
        SELECT "homeTeam", "awayTeam", period, market, "lineValue", "bookOdds"
        FROM "GameHalfLine"
        WHERE league = %s
          AND "gameDate" > NOW()
          AND "gameDate" < NOW() + INTERVAL '3 days'
        ORDER BY "gameDate"
        LIMIT 10
    ''', (league,))

    halflines = cur.fetchall()
    if halflines:
        print(f"\n--- Half-Line Data ({len(halflines)} records) ---")
        for h in halflines:
            home, away, period, market, line, odds = h
            print(f"  {away[:15]} @ {home[:15]}: {period} {market} {line or ''} {odds or ''}")

    cur.close()
    conn.close()


def fetch_oddsportal_quick(league, max_games=5):
    """Quick fetch from OddsPortal without storing"""
    if league not in LEAGUES or 'oddsportal' not in LEAGUES[league]:
        return

    try:
        from playwright.sync_api import sync_playwright
    except:
        print("  Playwright not available")
        return

    config = LEAGUES[league]
    base_url = 'https://www.oddsportal.com' + config['oddsportal']
    pattern = config['oddsportal'].replace('/', r'/') + r'[a-z0-9-]+-[A-Za-z0-9]{8}/'

    print(f"\n  Fetching from OddsPortal...")

    try:
        with sync_playwright() as p:
            browser = p.chromium.launch(headless=True)
            ctx = browser.new_context(
                viewport={'width': 1920, 'height': 1080},
                user_agent='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 Chrome/120.0.0.0',
                proxy=PROXY_PLAYWRIGHT
            )
            ctx.add_init_script("Object.defineProperty(navigator, 'webdriver', {get: () => undefined})")
            page = ctx.new_page()

            page.goto(base_url, timeout=45000)
            time.sleep(2)

            content = page.content()
            game_urls = list(set(re.findall(pattern, content)))[:max_games]

            print(f"  Found {len(game_urls)} games on OddsPortal\n")

            for url in game_urls[:max_games]:
                try:
                    full_url = 'https://www.oddsportal.com' + url
                    page.goto(full_url, timeout=30000)
                    time.sleep(1.5)

                    title = page.title()
                    m = re.match(r'^([^-]+)\s*-\s*([^|]+)', title)
                    if m:
                        away = re.sub(r'\s+Odds.*$', '', m.group(1).strip(), flags=re.I)
                        home = re.sub(r'\s+Odds.*$', '', m.group(2).strip(), flags=re.I)

                        odds = re.findall(r'class="[^"]*odds[^"]*"[^>]*>([+-]?\d{3,4})<', page.content())
                        if odds and len(odds) >= 2:
                            print(f"  {away[:20]} @ {home[:20]}: ML {odds[0]}/{odds[1]}")
                except:
                    pass

            browser.close()

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


def fetch_games(league, date=None):
    """Fetch games from multiple sources"""
    if league not in LEAGUES:
        print(f"Unknown league: {league}")
        return

    config = LEAGUES[league]
    print(f"\n{'='*50}")
    print(f"{config['name']} - Games")
    print(f"{'='*50}")

    # Try database first
    conn = get_db()
    cur = conn.cursor()

    date_filter = date if date else datetime.now().strftime('%Y-%m-%d')
    next_week = (datetime.strptime(date_filter, '%Y-%m-%d') + timedelta(days=7)).strftime('%Y-%m-%d')

    cur.execute('''
        SELECT "homeTeam", "awayTeam", "gameDate", status,
               "homeScore", "awayScore"
        FROM "SportsGame"
        WHERE league = %s
          AND "gameDate"::date >= %s
          AND "gameDate"::date <= %s
        ORDER BY "gameDate"
        LIMIT 30
    ''', (league, date_filter, next_week))

    games = cur.fetchall()
    cur.close()
    conn.close()

    if games:
        print(f"\nGames from {date_filter}:\n")
        for g in games:
            home, away, gdate, status, h_score, a_score = g
            date_str = gdate.strftime('%a %m/%d %I:%M%p') if gdate else 'TBD'
            score = f"{a_score or 0}-{h_score or 0}" if status and status.lower() in ['final', 'finished'] else ''
            status_str = f"({status})" if status else ''
            print(f"  {away[:20]:20} @ {home[:20]:20}  {date_str} {score} {status_str}")
    else:
        print(f"\nNo games found in database for {date_filter}")

        # Try BallDontLie API for NBA/NFL
        if 'api' in config:
            print("\nTrying BallDontLie API...")
            api_key = os.environ.get('BALLDONTLIE_API_KEY', '')
            url = f"https://api.balldontlie.io/{config['api']}?dates[]={date_filter}"
            try:
                resp = requests.get(url, headers={'Authorization': api_key}, timeout=15)
                data = resp.json().get('data', [])
                if data:
                    print(f"\nFrom API ({len(data)} games):\n")
                    for g in data:
                        home = g.get('home_team', {}).get('full_name', str(g.get('home_team', '')))
                        away = g.get('visitor_team', {}).get('full_name', str(g.get('away_team', '')))
                        print(f"  {away[:20]} @ {home[:20]}")
            except Exception as e:
                print(f"  API error: {str(e)[:40]}")

        # Try Football-Data.org for soccer
        if 'fd' in config:
            print("\nTrying Football-Data.org...")
            fd_key = os.environ.get('FOOTBALL_DATA_API_KEY', '')
            url = f"https://api.football-data.org/v4/competitions/{config['fd']}/matches?status=SCHEDULED"
            try:
                resp = requests.get(url, headers={'X-Auth-Token': fd_key}, timeout=15)
                matches = resp.json().get('matches', [])[:10]
                if matches:
                    print(f"\nFrom Football-Data ({len(matches)} matches):\n")
                    for m in matches:
                        home = m.get('homeTeam', {}).get('name', '')
                        away = m.get('awayTeam', {}).get('name', '')
                        mdate = m.get('utcDate', '')[:10]
                        print(f"  {away[:20]} @ {home[:20]}  {mdate}")
            except Exception as e:
                print(f"  API error: {str(e)[:40]}")


def fetch_halflines(league, max_games=8):
    """Fetch and store half-time lines from OddsPortal"""
    if league not in LEAGUES:
        print(f"Unknown league: {league}")
        return

    try:
        from playwright.sync_api import sync_playwright
    except:
        print("Playwright not available")
        return

    config = LEAGUES[league]
    base_url = 'https://www.oddsportal.com' + config['oddsportal']
    pattern = config['oddsportal'].replace('/', r'/') + r'[a-z0-9-]+-[A-Za-z0-9]{8}/'

    print(f"\n{'='*50}")
    print(f"{config['name']} - Fetching Half-Lines from OddsPortal")
    print(f"{'='*50}\n")

    conn = get_db()
    cur = conn.cursor()
    stored = 0

    with sync_playwright() as p:
        browser = p.chromium.launch(headless=True)
        ctx = browser.new_context(
            viewport={'width': 1920, 'height': 1080},
            user_agent='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 Chrome/120.0.0.0',
            proxy=PROXY_PLAYWRIGHT
        )
        ctx.add_init_script("Object.defineProperty(navigator, 'webdriver', {get: () => undefined})")
        page = ctx.new_page()

        page.goto(base_url, timeout=60000)
        time.sleep(3)

        content = page.content()
        game_urls = list(set(re.findall(pattern, content)))[:max_games]
        print(f"Found {len(game_urls)} games\n")

        for url in game_urls:
            try:
                full_url = 'https://www.oddsportal.com' + url
                page.goto(full_url, timeout=45000)
                time.sleep(2)

                title = page.title()
                m = re.match(r'^([^-]+)\s*-\s*([^|]+)', title)
                if not m:
                    continue

                away = re.sub(r'\s+Odds.*$', '', m.group(1).strip(), flags=re.I)
                home = re.sub(r'\s+Odds.*$', '', m.group(2).strip(), flags=re.I)

                pcontent = page.content()
                full_odds = re.findall(r'class="[^"]*odds[^"]*"[^>]*>([+-]?\d{3,4})<', pcontent)

                # Store full game odds
                if full_odds and len(full_odds) >= 2:
                    game_date = datetime.now(timezone.utc) + timedelta(days=1)
                    cur.execute('''
                        INSERT INTO "GameHalfLine"
                        (league, "gameDate", "homeTeam", "awayTeam", period, market, "bookOdds", bookmaker, "createdAt", "updatedAt")
                        VALUES (%s, %s, %s, %s, 'full', 'moneyline', %s, 'oddsportal', NOW(), NOW())
                        ON CONFLICT DO NOTHING
                    ''', (league, game_date, home, away, full_odds[0]))

                # Try 1H tab
                if '1st half' in pcontent.lower() or '1st Half' in pcontent:
                    try:
                        page.locator('text=1st Half').first.click()
                        time.sleep(1.5)
                        hc = page.content()
                        half_odds = re.findall(r'class="[^"]*odds[^"]*"[^>]*>([+-]?\d{3,4})<', hc)

                        if half_odds and len(half_odds) >= 2:
                            game_date = datetime.now(timezone.utc) + timedelta(days=1)
                            cur.execute('''
                                INSERT INTO "GameHalfLine"
                                (league, "gameDate", "homeTeam", "awayTeam", period, market, "bookOdds", bookmaker, "createdAt", "updatedAt")
                                VALUES (%s, %s, %s, %s, '1h', 'moneyline', %s, 'oddsportal', NOW(), NOW())
                                ON CONFLICT DO NOTHING
                            ''', (league, game_date, home, away, half_odds[0]))
                            stored += 1
                            print(f"  ✓ {home[:20]} @ {away[:20]}: Full {full_odds[0]}/{full_odds[1]}, 1H {half_odds[0]}/{half_odds[1]}")
                    except:
                        print(f"  ~ {home[:20]} @ {away[:20]}: Full only {full_odds[0]}/{full_odds[1]}")
                else:
                    if full_odds:
                        print(f"  ~ {home[:20]} @ {away[:20]}: Full only {full_odds[0]}/{full_odds[1]}")

            except Exception as e:
                print(f"  ✗ Error: {str(e)[:40]}")

            time.sleep(1)

        browser.close()

    conn.commit()
    cur.close()
    conn.close()
    print(f"\nStored {stored} half-line odds")


def fetch_refresh(league):
    """Refresh all data for a league"""
    if league not in LEAGUES:
        print(f"Unknown league: {league}")
        return

    config = LEAGUES[league]
    print(f"\n{'='*50}")
    print(f"REFRESHING ALL DATA FOR {config['name']}")
    print(f"{'='*50}\n")

    # 1. Capture snapshot
    print("1. Capturing odds snapshot...")
    os.system('cd /var/www/html/eventheodds && python3 scripts/capture_odds_snapshots.py 2>&1 | grep -E "Snapshot|created"')

    # 2. Fetch half-lines
    print(f"\n2. Fetching half-lines for {league}...")
    fetch_halflines(league, max_games=5)

    # 3. Show current odds
    print(f"\n3. Current odds summary:")
    fetch_odds(league)


def fetch_snapshot():
    """Quick odds snapshot capture"""
    print("\nCapturing odds snapshot...")
    os.system('cd /var/www/html/eventheodds && python3 scripts/capture_odds_snapshots.py 2>&1 | tail -15')


def fetch_live_theoddsapi(league, store=True):
    """Fetch live odds from The Odds API and optionally store in DB"""
    if league not in THEODDS_SPORT_KEYS:
        print(f"League '{league}' not supported by The Odds API")
        print(f"Supported: {', '.join(THEODDS_SPORT_KEYS.keys())}")
        return

    api_key = get_theoddsapi_key()
    if not api_key:
        print("ERROR: THE_ODDS_API_KEY not set in environment")
        return

    sport_key = THEODDS_SPORT_KEYS[league]
    config = LEAGUES.get(league, {'name': league.upper()})

    print(f"\n{'='*60}")
    print(f"{config['name']} - LIVE ODDS (The Odds API)")
    print(f"{'='*60}")

    conn = None
    stored_count = 0

    try:
        url = f"https://api.the-odds-api.com/v4/sports/{sport_key}/odds"
        params = {
            'apiKey': api_key,
            'regions': 'us',
            'markets': 'h2h,spreads,totals',
            'oddsFormat': 'american'
        }

        resp = requests.get(url, params=params, timeout=30)

        # Check quota
        quota_used = resp.headers.get('x-requests-used', '?')
        quota_remaining = resp.headers.get('x-requests-remaining', '?')
        print(f"API Quota: {quota_used} used, {quota_remaining} remaining\n")

        if resp.status_code != 200:
            print(f"ERROR: API returned {resp.status_code}")
            print(resp.text[:200])
            return

        games = resp.json()

        if not games:
            print("No upcoming games found")
            return

        print(f"Found {len(games)} upcoming games:\n")

        # Open DB connection if storing
        if store:
            conn = get_db()
            cur = conn.cursor()

        for game in games:
            event_id = game.get('id', '')
            home = game.get('home_team', '')
            away = game.get('away_team', '')
            commence = game.get('commence_time', '')
            commence_display = commence[:16].replace('T', ' ') if commence else 'TBD'

            # Parse commence time for DB
            commence_dt = None
            if commence:
                try:
                    commence_dt = datetime.fromisoformat(commence.replace('Z', '+00:00'))
                except:
                    pass

            print(f"  {away[:20]:20} @ {home[:20]:20}  {commence_display}")

            # Get odds from all books and store
            books = game.get('bookmakers', [])
            for book in books:
                book_name = book.get('key', 'unknown')
                book_title = book.get('title', 'Unknown')

                # Collect all market data
                ml_home = ml_away = None
                spread_home = spread_away = None
                spread_home_odds = spread_away_odds = None
                total_line = over_odds = under_odds = None

                for market in book.get('markets', []):
                    mkey = market.get('key')
                    outcomes = market.get('outcomes', [])

                    if mkey == 'h2h':
                        for o in outcomes:
                            if o.get('name') == home:
                                ml_home = o.get('price')
                            elif o.get('name') == away:
                                ml_away = o.get('price')

                    elif mkey == 'spreads':
                        for o in outcomes:
                            if o.get('name') == home:
                                spread_home = o.get('point')
                                spread_home_odds = o.get('price')
                            elif o.get('name') == away:
                                spread_away = o.get('point')
                                spread_away_odds = o.get('price')

                    elif mkey == 'totals':
                        for o in outcomes:
                            if o.get('name') == 'Over':
                                total_line = o.get('point')
                                over_odds = o.get('price')
                            elif o.get('name') == 'Under':
                                under_odds = o.get('price')

                # Store in OddsSnapshot if we have data
                if store and conn and (ml_home or spread_home or total_line):
                    try:
                        cur.execute('''
                            INSERT INTO "OddsSnapshot" (
                                league, "theoddsapiEventId", "externalGameId", "gameDate",
                                "homeTeam", "awayTeam", bookmaker, "snapshotType", "snapshotAt",
                                "moneylineHome", "moneylineAway",
                                "spreadHome", "spreadAway", "spreadHomeOdds", "spreadAwayOdds",
                                total, "totalOverOdds", "totalUnderOdds", source
                            ) VALUES (
                                %s, %s, %s, %s, %s, %s, %s, 'live', NOW(),
                                %s, %s, %s, %s, %s, %s, %s, %s, %s, 'theoddsapi'
                            )
                            ON CONFLICT (league, "externalGameId", "snapshotAt", source, bookmaker)
                            DO UPDATE SET
                                "moneylineHome" = EXCLUDED."moneylineHome",
                                "moneylineAway" = EXCLUDED."moneylineAway",
                                "spreadHome" = EXCLUDED."spreadHome",
                                "spreadAway" = EXCLUDED."spreadAway",
                                total = EXCLUDED.total
                        ''', (
                            league, event_id, event_id, commence_dt,
                            home, away, book_name,
                            ml_home, ml_away,
                            spread_home, spread_away, spread_home_odds, spread_away_odds,
                            total_line, over_odds, under_odds
                        ))
                        stored_count += 1
                    except Exception as db_err:
                        pass  # Continue on DB errors

                # Display first book only
                if book == books[0]:
                    if ml_home and ml_away:
                        print(f"    ML: {book_title}: {int(ml_away):+d} / {int(ml_home):+d}")
                    if spread_home is not None:
                        print(f"    Spread: {book_title}: {spread_home:+.1f}")
                    if total_line:
                        print(f"    Total: {book_title}: {total_line}")

            print()

        if store and conn:
            conn.commit()
            print(f"✓ Stored {stored_count} odds records in OddsSnapshot")

    except Exception as e:
        print(f"ERROR: {str(e)[:100]}")
    finally:
        if conn:
            conn.close()


def fetch_props_theoddsapi(league, store=True):
    """Fetch player props from The Odds API and optionally store in DB"""
    if league not in THEODDS_SPORT_KEYS:
        print(f"League '{league}' not supported by The Odds API")
        return

    if league not in THEODDS_PROP_MARKETS:
        print(f"No prop markets defined for {league}")
        return

    api_key = get_theoddsapi_key()
    if not api_key:
        print("ERROR: THE_ODDS_API_KEY not set in environment")
        return

    sport_key = THEODDS_SPORT_KEYS[league]
    prop_markets = THEODDS_PROP_MARKETS[league]
    config = LEAGUES.get(league, {'name': league.upper()})

    print(f"\n{'='*60}")
    print(f"{config['name']} - PLAYER PROPS (The Odds API)")
    print(f"{'='*60}")

    conn = None
    stored_count = 0

    try:
        # First get events
        events_url = f"https://api.the-odds-api.com/v4/sports/{sport_key}/events"
        resp = requests.get(events_url, params={'apiKey': api_key}, timeout=30)

        if resp.status_code != 200:
            print(f"ERROR: Could not fetch events: {resp.status_code}")
            return

        events = resp.json()

        if not events:
            print("No upcoming events found")
            return

        # Limit to first 3 events to save API credits
        events = events[:3]
        print(f"Fetching props for {len(events)} upcoming games...\n")

        # Open DB connection if storing
        if store:
            conn = get_db()
            cur = conn.cursor()
            # Ensure PropSnapshot table exists
            cur.execute('''
                CREATE TABLE IF NOT EXISTS "PropSnapshot" (
                    id SERIAL PRIMARY KEY,
                    league VARCHAR(20),
                    "theoddsapiEventId" VARCHAR(100),
                    "gameDate" TIMESTAMP,
                    "homeTeam" VARCHAR(100),
                    "awayTeam" VARCHAR(100),
                    bookmaker VARCHAR(50),
                    "marketType" VARCHAR(50),
                    "playerName" VARCHAR(100),
                    "propLine" DOUBLE PRECISION,
                    "overPrice" INTEGER,
                    "underPrice" INTEGER,
                    "capturedAt" TIMESTAMP DEFAULT NOW(),
                    source VARCHAR(50) DEFAULT 'theoddsapi',
                    "createdAt" TIMESTAMP DEFAULT NOW()
                )
            ''')
            conn.commit()

        for event in events:
            event_id = event.get('id')
            home = event.get('home_team', '')
            away = event.get('away_team', '')
            commence = event.get('commence_time', '')
            commence_display = commence[:16].replace('T', ' ') if commence else 'TBD'

            # Parse commence time for DB
            commence_dt = None
            if commence:
                try:
                    commence_dt = datetime.fromisoformat(commence.replace('Z', '+00:00'))
                except:
                    pass

            print(f"\n  {away[:20]} @ {home[:20]}  {commence_display}")
            print(f"  {'-'*50}")

            # Fetch props for this event
            props_url = f"https://api.the-odds-api.com/v4/sports/{sport_key}/events/{event_id}/odds"
            props_params = {
                'apiKey': api_key,
                'regions': 'us',
                'markets': ','.join(prop_markets),
                'oddsFormat': 'american'
            }

            props_resp = requests.get(props_url, params=props_params, timeout=30)

            if props_resp.status_code != 200:
                print(f"    Could not fetch props: {props_resp.status_code}")
                continue

            props_data = props_resp.json()

            # Organize props by player for display
            player_props = {}

            # Process all bookmakers for storage, but only first for display
            for book_idx, book in enumerate(props_data.get('bookmakers', [])):
                book_name = book.get('key', 'unknown')
                book_title = book.get('title', '')

                for market in book.get('markets', []):
                    market_key = market.get('key', '')

                    if not market_key.startswith('player_'):
                        continue

                    # Track over/under pairs by player
                    player_lines = {}

                    for outcome in market.get('outcomes', []):
                        player = outcome.get('description', outcome.get('name', ''))
                        line = outcome.get('point')
                        price = outcome.get('price')
                        side = outcome.get('name', '').lower()

                        if player and line is not None:
                            key = (player, line)
                            if key not in player_lines:
                                player_lines[key] = {'over': None, 'under': None}

                            if side == 'over':
                                player_lines[key]['over'] = price
                            elif side == 'under':
                                player_lines[key]['under'] = price

                            # Track for display (first book only)
                            if book_idx == 0:
                                if player not in player_props:
                                    player_props[player] = {}
                                if market_key not in player_props[player]:
                                    player_props[player][market_key] = {'line': line, 'over': None, 'under': None}
                                if side == 'over':
                                    player_props[player][market_key]['over'] = price
                                elif side == 'under':
                                    player_props[player][market_key]['under'] = price

                    # Store in PropSnapshot
                    if store and conn:
                        for (player, line), prices in player_lines.items():
                            try:
                                cur.execute('''
                                    INSERT INTO "PropSnapshot" (
                                        league, "theoddsapiEventId", "gameDate", "homeTeam", "awayTeam",
                                        bookmaker, "marketType", "playerName", "propLine",
                                        "overPrice", "underPrice", "capturedAt"
                                    ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, date_trunc('minute', NOW()))
                                    ON CONFLICT DO NOTHING
                                ''', (
                                    league, event_id, commence_dt, home, away,
                                    book_name, market_key, player, line,
                                    prices['over'], prices['under']
                                ))
                                stored_count += 1
                            except Exception as db_err:
                                pass  # Continue on DB errors

            # Display props by player (first book only)
            if player_props:
                for player, props in list(player_props.items())[:10]:  # Top 10 players
                    prop_strs = []
                    for mkt, data in props.items():
                        mkt_short = mkt.replace('player_', '').replace('_', ' ').title()[:8]
                        over_str = f"+{data['over']}" if data['over'] and data['over'] > 0 else str(data['over'] or '?')
                        prop_strs.append(f"{mkt_short} {data['line']} ({over_str})")
                    print(f"    {player[:25]:25} {' | '.join(prop_strs[:3])}")
            else:
                print(f"    No props available")

        if store and conn:
            conn.commit()
            print(f"\n✓ Stored {stored_count} prop records in PropSnapshot")

        # Show quota
        quota_used = props_resp.headers.get('x-requests-used', '?')
        quota_remaining = props_resp.headers.get('x-requests-remaining', '?')
        print(f"API Quota: {quota_used} used, {quota_remaining} remaining")

    except Exception as e:
        print(f"ERROR: {str(e)[:100]}")
    finally:
        if conn:
            conn.close()


def list_leagues():
    """List all supported leagues"""
    print("\nSupported Leagues:")
    print("-" * 40)

    by_type = {}
    for code, cfg in LEAGUES.items():
        t = cfg.get('type', 'other')
        if t not in by_type:
            by_type[t] = []
        by_type[t].append((code, cfg['name']))

    for sport, leagues in by_type.items():
        print(f"\n  {sport.upper()}:")
        for code, name in leagues:
            print(f"    {code:12} - {name}")


def run_sportsgameodds(command: str, league: str):
    """Run SportsGameOdds fetcher command"""
    import subprocess
    script_path = '/var/www/html/eventheodds/scripts/sportsgameodds_fetcher.py'
    result = subprocess.run(
        ['python3', script_path, command, '-l', league],
        capture_output=False,
        cwd='/var/www/html/eventheodds'
    )
    return result.returncode


def main():
    if len(sys.argv) < 2:
        print("""
On-Demand Data Fetcher - All Sports
===================================
Usage:
    python fetch_ondemand.py odds <league>      - Current odds from DB
    python fetch_ondemand.py live <league>      - Live odds from The Odds API
    python fetch_ondemand.py props <league>     - Player props from The Odds API
    python fetch_ondemand.py games <league>     - Upcoming games
    python fetch_ondemand.py halflines <league> - Fetch half-time odds
    python fetch_ondemand.py refresh <league>   - Refresh all data
    python fetch_ondemand.py snapshot           - Capture odds snapshot
    python fetch_ondemand.py leagues            - List all leagues

SportsGameOdds API (80+ bookmakers):
    python fetch_ondemand.py sgo-half <league>  - Half/quarter lines (1H, 1Q, etc)
    python fetch_ondemand.py sgo-live <league>  - Live in-game odds
    python fetch_ondemand.py sgo-books <league> - Multi-bookmaker comparison
    python fetch_ondemand.py sgo-props <league> - Player props snapshot
    python fetch_ondemand.py sgo-all <league>   - Fetch all SportsGameOdds data
""")
        list_leagues()
        return

    cmd = sys.argv[1].lower()
    league = sys.argv[2].lower() if len(sys.argv) > 2 else None

    if cmd == 'odds':
        fetch_odds(league or 'nba')
    elif cmd == 'live':
        fetch_live_theoddsapi(league or 'nba')
    elif cmd == 'props':
        fetch_props_theoddsapi(league or 'nba')
    elif cmd == 'games':
        date = sys.argv[3] if len(sys.argv) > 3 else None
        fetch_games(league or 'nba', date)
    elif cmd == 'halflines':
        fetch_halflines(league or 'nba')
    elif cmd == 'refresh':
        fetch_refresh(league or 'nba')
    elif cmd == 'snapshot':
        fetch_snapshot()
    elif cmd == 'leagues':
        list_leagues()
    # SportsGameOdds API commands
    elif cmd == 'sgo-half':
        run_sportsgameodds('half', league or 'nba')
    elif cmd == 'sgo-live':
        run_sportsgameodds('live', league or 'nba')
    elif cmd == 'sgo-books':
        run_sportsgameodds('books', league or 'nba')
    elif cmd == 'sgo-props':
        run_sportsgameodds('props', league or 'nba')
    elif cmd == 'sgo-all':
        run_sportsgameodds('all', league or 'nba')
    elif cmd == 'sgo-quota':
        run_sportsgameodds('quota', league or 'nba')
    else:
        print(f"Unknown command: {cmd}")


if __name__ == '__main__':
    main()