#!/usr/bin/env python3
"""
Fetch player transactions from ESPN API and store in PlayerTransaction / PlayerTeamHistory.

Covers NBA, NFL, NHL, MLB. Parses transaction descriptions to detect type
(trade, signing, waiver, release, assignment, retire) and updates team history.

Usage:
    python3 fetch_espn_transactions.py                     # all 4 leagues, last 30 days
    python3 fetch_espn_transactions.py --leagues nba --days 7
    python3 fetch_espn_transactions.py --leagues nba,nhl --days 14
"""

import argparse
import json
import re
import sys
import time
import requests
import psycopg2
import psycopg2.extras
from datetime import datetime, timedelta, timezone

# ESPN transaction endpoints by league
ESPN_SPORT_MAP = {
    'nba': ('basketball', 'nba'),
    'nfl': ('football', 'nfl'),
    'nhl': ('hockey', 'nhl'),
    'mlb': ('baseball', 'mlb'),
}

RATE_LIMIT_SECONDS = 0.5


def load_db_url():
    """Load database URL from .env file, matching the pattern used by other scripts."""
    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 get_db_connection():
    """Parse the database URL and return a psycopg2 connection."""
    db_url = load_db_url()
    if not db_url:
        print('ERROR: SPORTS_DATABASE_URL not found in .env')
        sys.exit(1)
    conn = psycopg2.connect(db_url)
    conn.autocommit = False
    return conn


def detect_transaction_type(description):
    """
    Parse the ESPN transaction description to determine the transaction type.
    Returns one of: trade, signing, waiver, release, assignment, retire
    """
    desc_lower = description.lower()

    if 'traded' in desc_lower or ' trade ' in desc_lower:
        return 'trade'
    if 'waived' in desc_lower or 'waiver' in desc_lower:
        return 'waiver'
    if 'released' in desc_lower:
        return 'release'
    if 'assigned' in desc_lower or 'recalled' in desc_lower:
        return 'assignment'
    if 'retired' in desc_lower:
        return 'retire'
    if 'signed' in desc_lower or 'signing' in desc_lower or 're-signed' in desc_lower:
        return 'signing'

    # Default: treat as signing if we can't classify
    return 'signing'


def extract_players_from_description(description):
    """
    Extract player names from ESPN transaction descriptions.
    ESPN format examples:
      "Signed G Killian Hayes to a 10-day contract."
      "Waived F-C Tamar Bates."
      "Signed F CJ Huntley to a rest-of-season contract."
      "Re-signed G Jamaree Bouyea to a rest-of-season contract."
      "Placed C Nikola Jokic on the injured list."
      "Recalled G Zyon Pullin from Iowa Wolves."
      "Traded F John Smith to Boston Celtics."

    Returns list of player name strings.
    """
    players = []

    # Multi-player descriptions: "Signed F CJ Huntley ... Signed G Jamaree Bouyea ..."
    # Split on sentence boundaries and process each
    sentences = re.split(r'(?<=[.])\s+', description)

    for sentence in sentences:
        # Primary pattern: action + position + name
        # Position codes: G, F, C, PG, SG, SF, PF, C, F-C, G-F, etc.
        m = re.search(
            r'(?:Signed|Traded|Waived|Released|Assigned|Recalled|Placed|Re-signed|Acquired)\s+'
            r'(?:[A-Z]{1,2}(?:-[A-Z]{1,2})?\s+)?'
            r'([A-Z][a-zA-Z\']+(?:\s+(?:Jr\.|Sr\.|III|II|IV|de|van|[A-Z][a-zA-Z\']+))+)',
            sentence
        )
        if m:
            name = m.group(1).strip()
            # Clean up trailing words that aren't part of the name
            name = re.sub(r'\s+(?:to|from|on|off|for|into)\b.*$', '', name)
            if len(name) > 3:  # Skip very short garbage
                players.append(name)

    return players if players else None


def parse_from_team(description):
    """
    Try to extract the 'from' team from trade descriptions.
    E.g., "Traded PG John Smith from Boston Celtics" or "Acquired ... in a trade with ..."
    """
    # "from <team>" pattern
    m = re.search(r'from\s+(?:the\s+)?([A-Z][a-zA-Z\s\.]+?)(?:\s+to|\s+for|\s*\.|\s*$)', description)
    if m:
        return m.group(1).strip()

    # "in a trade with <team>" pattern
    m = re.search(r'trade\s+with\s+(?:the\s+)?([A-Z][a-zA-Z\s\.]+?)(?:\s+for|\s*\.|\s*$)', description)
    if m:
        return m.group(1).strip()

    return None


def fetch_espn_transactions(league, date_str):
    """
    Fetch transactions from ESPN API for a given league and date (YYYYMMDD).
    Returns list of parsed transaction dicts.
    """
    if league not in ESPN_SPORT_MAP:
        print(f'  Unknown league: {league}')
        return []

    sport, espn_league = ESPN_SPORT_MAP[league]
    url = f'https://site.api.espn.com/apis/site/v2/sports/{sport}/{espn_league}/transactions?dates={date_str}'

    try:
        resp = requests.get(url, timeout=30, headers={
            'User-Agent': 'Mozilla/5.0 (compatible; EventheOdds/1.0)'
        })
        if resp.status_code == 404:
            return []
        if resp.status_code != 200:
            print(f'  ESPN {league} {date_str} returned HTTP {resp.status_code}')
            return []

        data = resp.json()
    except requests.RequestException as e:
        print(f'  ESPN {league} {date_str} request error: {e}')
        return []
    except json.JSONDecodeError as e:
        print(f'  ESPN {league} {date_str} JSON error: {e}')
        return []

    transactions = []
    items = data.get('transactions', data.get('items', []))

    for txn in items:
        description = txn.get('description', '')
        if not description:
            continue

        # Transaction date
        txn_date_str = txn.get('date', '')
        if txn_date_str:
            try:
                txn_date = datetime.fromisoformat(txn_date_str.replace('Z', '+00:00'))
            except (ValueError, TypeError):
                txn_date = datetime.strptime(date_str, '%Y%m%d').replace(tzinfo=timezone.utc)
        else:
            txn_date = datetime.strptime(date_str, '%Y%m%d').replace(tzinfo=timezone.utc)

        # Team info from the transaction object
        team_info = txn.get('team', {})
        team_abbr = team_info.get('abbreviation', '')
        team_name = team_info.get('displayName', '')

        # Transaction type
        txn_type = detect_transaction_type(description)

        # Try to get athletes from the API response (some endpoints include them)
        athletes = txn.get('athletes', [])

        if athletes:
            for athlete in athletes:
                player_name = athlete.get('displayName', '')
                player_id = str(athlete.get('id', '')) if athlete.get('id') else None
                if not player_name:
                    continue

                from_team = None
                to_team = team_abbr or team_name

                if txn_type == 'trade':
                    from_team = parse_from_team(description)
                if txn_type in ('waiver', 'release', 'retire'):
                    from_team = team_abbr or team_name
                    to_team = None

                transactions.append({
                    'league': league,
                    'playerExternalId': player_id,
                    'playerName': player_name,
                    'fromTeam': from_team,
                    'toTeam': to_team,
                    'transactionType': txn_type,
                    'transactionDate': txn_date,
                    'raw': txn,
                })
        else:
            # No athlete array — extract player name(s) from description
            players = extract_players_from_description(description)

            if not players:
                # Can't parse any player name — store with raw description snippet
                players = [description[:80]]

            for player_name in players:
                from_team = None
                to_team = team_abbr or team_name

                if txn_type == 'trade':
                    from_team = parse_from_team(description)
                if txn_type in ('waiver', 'release', 'retire'):
                    from_team = team_abbr or team_name
                    to_team = None

                transactions.append({
                    'league': league,
                    'playerExternalId': None,  # No ESPN athlete ID available
                    'playerName': player_name,
                    'fromTeam': from_team,
                    'toTeam': to_team,
                    'transactionType': txn_type,
                    'transactionDate': txn_date,
                    'raw': txn,
                })

    return transactions


def try_link_canonical_player(cur, league, player_name, player_external_id):
    """
    Try to find a matching CanonicalPlayer by espnId or normalized name.
    Returns (canonical_player_id, espnId_from_canonical) or (None, None).
    """
    if player_external_id:
        cur.execute(
            'SELECT id FROM "CanonicalPlayer" WHERE "espnId" = %s AND league = %s LIMIT 1',
            (player_external_id, league)
        )
        row = cur.fetchone()
        if row:
            return row[0], player_external_id

    if player_name:
        normalized = re.sub(r'[^a-z]', '', player_name.lower())
        cur.execute(
            'SELECT id, "espnId" FROM "CanonicalPlayer" WHERE "normalizedName" = %s AND league = %s LIMIT 1',
            (normalized, league)
        )
        row = cur.fetchone()
        if row:
            return row[0], row[1]

    return None, None


def player_key(txn):
    """
    Return the stable identifier for dedup: playerExternalId if available, else playerName.
    This matches the COALESCE in the unique index.
    """
    return txn['playerExternalId'] or txn['playerName']


def upsert_transaction(cur, txn):
    """
    Insert a transaction into PlayerTransaction, skipping duplicates via ON CONFLICT.
    The unique index uses COALESCE(playerExternalId, playerName).
    Returns True if a new row was inserted.
    """
    cur.execute('''
        INSERT INTO "PlayerTransaction"
            (league, "playerExternalId", "playerName", "canonicalPlayerId",
             "fromTeam", "toTeam", "transactionType", "transactionDate", source, raw)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, 'espn', %s)
        ON CONFLICT (league, COALESCE("playerExternalId", "playerName"), "transactionType",
                     "transactionDate", COALESCE("toTeam", ''), COALESCE("fromTeam", ''))
        DO NOTHING
        RETURNING id
    ''', (
        txn['league'],
        txn['playerExternalId'],
        txn['playerName'],
        txn.get('canonicalPlayerId'),
        txn['fromTeam'],
        txn['toTeam'],
        txn['transactionType'],
        txn['transactionDate'],
        json.dumps(txn['raw']),
    ))
    return cur.fetchone() is not None


def update_team_history(cur, txn):
    """
    When a player moves to a new team (trade, signing, assignment),
    close any existing open history row and open a new one.
    For waiver/release/retire, just close the current row.

    Uses COALESCE(playerExternalId, playerName) as the stable player key,
    matching the unique index on PlayerTeamHistory.
    """
    league = txn['league']
    player_ext_id = txn['playerExternalId']
    player_name = txn['playerName']
    canonical_id = txn.get('canonicalPlayerId')
    txn_date = txn['transactionDate']
    to_team = txn['toTeam']
    txn_type = txn['transactionType']

    pk = player_ext_id or player_name
    if not pk:
        return

    # Close any existing open row for this player (match on the COALESCE key)
    cur.execute('''
        UPDATE "PlayerTeamHistory"
        SET "endDate" = %s
        WHERE league = %s
          AND COALESCE("playerExternalId", "playerName") = %s
          AND "endDate" IS NULL
    ''', (txn_date, league, pk))

    # Open a new row if the player is joining a team
    if txn_type in ('trade', 'signing', 'assignment') and to_team:
        cur.execute('''
            INSERT INTO "PlayerTeamHistory"
                (league, "playerExternalId", "playerName", "canonicalPlayerId",
                 team, "startDate", source)
            VALUES (%s, %s, %s, %s, %s, %s, 'espn')
            ON CONFLICT (league, COALESCE("playerExternalId", "playerName"))
                WHERE "endDate" IS NULL
            DO UPDATE SET
                team = EXCLUDED.team,
                "startDate" = EXCLUDED."startDate",
                "playerName" = EXCLUDED."playerName",
                "canonicalPlayerId" = COALESCE(EXCLUDED."canonicalPlayerId", "PlayerTeamHistory"."canonicalPlayerId")
        ''', (
            league, player_ext_id, player_name, canonical_id,
            to_team, txn_date,
        ))


def run(leagues, days, dry_run=False):
    """Main entry point: fetch ESPN transactions and upsert into DB."""
    conn = get_db_connection()
    cur = conn.cursor()

    today = datetime.now(timezone.utc).date()
    dates = [(today - timedelta(days=d)).strftime('%Y%m%d') for d in range(days)]

    total_inserted = 0
    total_skipped = 0
    total_history_updated = 0

    for league in leagues:
        print(f'\n=== {league.upper()} ===')
        league_inserted = 0
        league_skipped = 0

        for date_str in dates:
            txns = fetch_espn_transactions(league, date_str)
            if txns:
                print(f'  {date_str}: {len(txns)} transaction(s)')

            for txn in txns:
                # Try to link to canonical player (also retrieves espnId if found by name)
                canonical_id, espn_id = try_link_canonical_player(
                    cur, league, txn['playerName'], txn['playerExternalId']
                )
                txn['canonicalPlayerId'] = canonical_id
                # If we found a canonical player with an espnId, use it
                if espn_id and not txn['playerExternalId']:
                    txn['playerExternalId'] = espn_id

                if dry_run:
                    linked = f' [canonical:{canonical_id}]' if canonical_id else ''
                    print(f'    [{txn["transactionType"]}] {txn["playerName"]} '
                          f'({txn["fromTeam"]} -> {txn["toTeam"]}){linked}')
                    league_inserted += 1
                    continue

                was_inserted = upsert_transaction(cur, txn)
                if was_inserted:
                    league_inserted += 1
                    update_team_history(cur, txn)
                    total_history_updated += 1
                else:
                    league_skipped += 1

            # Rate limit between ESPN requests
            time.sleep(RATE_LIMIT_SECONDS)

        if not dry_run:
            conn.commit()

        print(f'  {league.upper()} total: {league_inserted} inserted, {league_skipped} skipped (dupes)')
        total_inserted += league_inserted
        total_skipped += league_skipped

    cur.close()
    conn.close()

    print(f'\n--- Summary ---')
    print(f'Total inserted:  {total_inserted}')
    print(f'Total skipped:   {total_skipped}')
    print(f'History updated: {total_history_updated}')
    print('Done.')


def main():
    parser = argparse.ArgumentParser(description='Fetch ESPN player transactions')
    parser.add_argument('--leagues', type=str, default='nba,nfl,nhl,mlb',
                        help='Comma-separated leagues (default: nba,nfl,nhl,mlb)')
    parser.add_argument('--days', type=int, default=30,
                        help='Number of days to look back (default: 30)')
    parser.add_argument('--dry-run', action='store_true',
                        help='Print transactions without writing to DB')
    args = parser.parse_args()

    leagues = [l.strip().lower() for l in args.leagues.split(',')]
    invalid = [l for l in leagues if l not in ESPN_SPORT_MAP]
    if invalid:
        print(f'ERROR: Unknown league(s): {", ".join(invalid)}')
        print(f'Valid leagues: {", ".join(ESPN_SPORT_MAP.keys())}')
        sys.exit(1)

    print(f'Fetching ESPN transactions: leagues={leagues}, days={args.days}, dry_run={args.dry_run}')
    run(leagues, args.days, dry_run=args.dry_run)


if __name__ == '__main__':
    main()