#!/usr/bin/env python3
"""
Sync MMA events and fights from BallDontLie API to SportsDB.
This supplements the Kaggle data with more recent events.
"""

import os
import json
import time
import urllib.request
import urllib.parse
import subprocess
from datetime import datetime
from pathlib import Path


def load_api_key():
    """Load BallDontLie API key from .env file."""
    env_path = Path('/var/www/html/eventheodds/.env')
    if env_path.exists():
        with open(env_path) as f:
            for line in f:
                if line.startswith('BALLDONTLIE_API_KEY='):
                    return line.split('=', 1)[1].strip().strip('"')
    return os.environ.get('BALLDONTLIE_API_KEY')


def load_db_url():
    """Load database URL from .env file."""
    env_path = Path('/var/www/html/eventheodds/.env')
    if env_path.exists():
        with open(env_path) as f:
            for line in f:
                if line.startswith('SPORTS_DATABASE_URL='):
                    url = line.split('=', 1)[1].strip().strip('"')
                    return url.split('?schema=')[0] if '?schema=' in url else url
    return os.environ.get('SPORTS_DATABASE_URL', '').split('?schema=')[0]


API_KEY = load_api_key()
BASE_URL = 'https://api.balldontlie.io/mma/v1'


def make_request(endpoint, params=None):
    """Make authenticated API request."""
    url = f'{BASE_URL}/{endpoint}'
    if params:
        url += '?' + urllib.parse.urlencode(params, doseq=True)
    req = urllib.request.Request(url, headers={'Authorization': API_KEY})
    try:
        with urllib.request.urlopen(req, timeout=30) as resp:
            return json.loads(resp.read().decode())
    except Exception as e:
        print(f'API Error: {e}')
        return None


def sql_value(val):
    """Format value for SQL."""
    if val is None or val == '':
        return 'NULL'
    if isinstance(val, (int, float)):
        return str(val)
    s = str(val).replace("'", "''")
    return f"'{s}'"


def run_sql(sql):
    """Execute SQL against SportsDB."""
    db_url = load_db_url()
    if not db_url:
        raise RuntimeError('SPORTS_DATABASE_URL not set')

    tmp_path = '/tmp/mma_bdl_batch.sql'
    with open(tmp_path, 'w', encoding='utf-8') as f:
        f.write(sql)

    result = subprocess.run(
        ['psql', db_url, '-f', tmp_path],
        stdout=subprocess.PIPE,
        stderr=subprocess.PIPE,
        text=True,
        timeout=300,
    )
    if result.returncode != 0:
        err = (result.stderr or '').strip()
        if 'already exists' not in err and 'duplicate key' not in err.lower():
            print(f'SQL Warning: {err[:200]}')


def sync_events():
    """Fetch and sync MMA events from BallDontLie."""
    print('[mma] Fetching events from BallDontLie...')

    all_events = []
    cursor = None

    while True:
        params = {'per_page': 100}
        if cursor:
            params['cursor'] = cursor

        resp = make_request('events', params)
        if not resp or not resp.get('data'):
            break

        all_events.extend(resp['data'])
        cursor = resp.get('meta', {}).get('next_cursor')

        if not cursor or len(all_events) >= 500:
            break

        time.sleep(0.3)  # Rate limit

    print(f'[mma] Found {len(all_events)} events from BallDontLie')

    # Filter for recent/future events (2024+)
    recent_events = [
        e for e in all_events
        if e.get('date') and e['date'][:4] >= '2024'
    ]
    print(f'[mma] Processing {len(recent_events)} events from 2024+')

    # Insert events
    for event in recent_events:
        event_id = f"bdl_{event['id']}"
        name = event.get('name', 'Unknown Event')
        date = event.get('date', '')[:10] if event.get('date') else None
        location = event.get('location') or event.get('venue') or 'TBD'

        sql = f'''
INSERT INTO "UfcEvent" ("eventId", name, date, location, raw)
VALUES ({sql_value(event_id)}, {sql_value(name)}, {sql_value(date)}, {sql_value(location)}, {sql_value(json.dumps(event))})
ON CONFLICT ("eventId") DO UPDATE SET
    name = EXCLUDED.name,
    date = EXCLUDED.date,
    location = EXCLUDED.location,
    raw = EXCLUDED.raw;
'''
        run_sql(sql)

    print(f'[mma] Synced {len(recent_events)} events to database')
    return recent_events


def sync_fights(events):
    """Fetch fights for events from BallDontLie."""
    print(f'[mma] Fetching fights for {len(events)} events...')

    total_fights = 0

    for event in events:
        event_id = event['id']
        bdl_event_id = f"bdl_{event_id}"

        # Get fights for this event
        resp = make_request('fights', {'event_id': event_id, 'per_page': 50})
        if not resp or not resp.get('data'):
            continue

        fights = resp['data']

        for fight in fights:
            fight_id = f"bdl_{fight['id']}"
            fighter1 = fight.get('fighter1', {})
            fighter2 = fight.get('fighter2', {})

            sql = f'''
INSERT INTO "UfcFight" (
    "fightId", "eventId",
    "fighter1Name", "fighter2Name",
    "weightClass", method, round, "fightTime",
    result1, result2, raw
) VALUES (
    {sql_value(fight_id)}, {sql_value(bdl_event_id)},
    {sql_value(fighter1.get('name'))}, {sql_value(fighter2.get('name'))},
    {sql_value(fight.get('weight_class'))}, {sql_value(fight.get('method'))},
    {sql_value(fight.get('round'))}, {sql_value(fight.get('time'))},
    {sql_value(fight.get('result1'))}, {sql_value(fight.get('result2'))},
    {sql_value(json.dumps(fight))}
) ON CONFLICT ("fightId") DO UPDATE SET
    "eventId" = EXCLUDED."eventId",
    "fighter1Name" = EXCLUDED."fighter1Name",
    "fighter2Name" = EXCLUDED."fighter2Name",
    "weightClass" = EXCLUDED."weightClass",
    method = EXCLUDED.method,
    round = EXCLUDED.round,
    "fightTime" = EXCLUDED."fightTime",
    result1 = EXCLUDED.result1,
    result2 = EXCLUDED.result2,
    raw = EXCLUDED.raw;
'''
            run_sql(sql)
            total_fights += 1

        time.sleep(0.3)  # Rate limit

    print(f'[mma] Synced {total_fights} fights to database')


def sync_odds(events):
    """Fetch odds for events from BallDontLie."""
    print(f'[mma] Fetching odds for {len(events)} events...')

    total_odds = 0

    for event in events[:30]:  # Limit to recent events for odds
        event_id = event['id']
        event_date = event.get('date', '')[:10] if event.get('date') else None

        if not event_date:
            continue

        # Get odds for this event
        resp = make_request('odds', {'event_id': event_id, 'per_page': 100})
        if not resp or not resp.get('data'):
            continue

        odds_data = resp['data']

        for odd in odds_data:
            fighter1 = odd.get('fighter1', {})
            fighter2 = odd.get('fighter2', {})

            f1_name = fighter1.get('name')
            f2_name = fighter2.get('name')

            if not f1_name or not f2_name:
                continue

            sql = f'''
INSERT INTO "UfcOdds" (
    fighter1, fighter2, odds1, odds2,
    "eventDate", "addingDate", source, region, raw
) VALUES (
    {sql_value(f1_name)}, {sql_value(f2_name)},
    {sql_value(odd.get('fighter1_odds'))}, {sql_value(odd.get('fighter2_odds'))},
    {sql_value(event_date)}, {sql_value(datetime.utcnow().isoformat())},
    {sql_value(odd.get('vendor', 'balldontlie'))}, {sql_value('us')},
    {sql_value(json.dumps(odd))}
) ON CONFLICT (fighter1, fighter2, "eventDate", source, region) DO UPDATE SET
    odds1 = EXCLUDED.odds1,
    odds2 = EXCLUDED.odds2,
    "addingDate" = EXCLUDED."addingDate",
    raw = EXCLUDED.raw;
'''
            run_sql(sql)
            total_odds += 1

        time.sleep(0.3)  # Rate limit

    print(f'[mma] Synced {total_odds} odds to database')


def main():
    if not API_KEY:
        print('[mma] Error: BALLDONTLIE_API_KEY not set')
        return

    print('[mma] Starting BallDontLie MMA sync...')
    print(f'[mma] API Key: {API_KEY[:10]}...')

    # Sync events
    events = sync_events()

    if events:
        # Sync fights for these events
        sync_fights(events)

        # Sync odds for these events
        sync_odds(events)

    print('[mma] BallDontLie MMA sync complete!')

    # Show summary
    db_url = load_db_url()
    if db_url:
        result = subprocess.run(
            ['psql', db_url, '-c',
             '''SELECT 'Events' as type, COUNT(*), MAX(date)::date as max_date FROM "UfcEvent"
                UNION ALL
                SELECT 'Fights', COUNT(*), NULL FROM "UfcFight"
                UNION ALL
                SELECT 'Odds', COUNT(*), MAX("eventDate")::date FROM "UfcOdds";'''],
            capture_output=True, text=True
        )
        print('\n[mma] Database Summary:')
        print(result.stdout)


if __name__ == '__main__':
    main()