#!/usr/bin/env python3
"""
Enrich recent TheSportsDB events with lineups, stats, timeline, and highlights.
Fetches detail endpoints for events from the last 3 days that lack enrichment.

Stores everything in ExternalFeedRecord with kind = lineup|stats|timeline|highlights.

Cron: 0 */4 * * * (every 4 hours)
"""
import json
import hashlib
import requests
import psycopg2
from datetime import datetime, timezone, timedelta

API_KEY = "428892"
BASE = "https://www.thesportsdb.com/api/v2/json"
HEADERS = {"X-API-KEY": API_KEY}

DETAIL_ENDPOINTS = {
    "lineup": "lookup/event_lineup",
    "stats": "lookup/event_stats",
    "timeline": "lookup/event_timeline",
    "highlights": "lookup/event_highlights",
}


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().strip('"').split('?')[0]
    return ''


def sha256(data):
    return hashlib.sha256(json.dumps(data, sort_keys=True).encode()).hexdigest()


def get_recent_event_ids(conn, days=3):
    """Get TheSportsDB event IDs from recent ExternalFeedRecords."""
    cur = conn.cursor()
    cur.execute("""
        SELECT DISTINCT raw->>'idEvent'
        FROM "ExternalFeedRecord"
        WHERE source = 'thesportsdb'
          AND kind = 'livescore'
          AND "fetchedAt" > NOW() - INTERVAL '%s days'
          AND raw->>'strStatus' IN ('FT', 'AET', 'PEN')
    """, (days,))
    return [row[0] for row in cur.fetchall() if row[0]]


def get_already_enriched(conn, event_ids, kind):
    """Check which events already have this kind of enrichment."""
    if not event_ids:
        return set()
    cur = conn.cursor()
    # Check by looking at contentHash prefix pattern
    placeholders = ','.join(['%s'] * len(event_ids))
    cur.execute(f"""
        SELECT DISTINCT raw->>'idEvent'
        FROM "ExternalFeedRecord"
        WHERE source = 'thesportsdb'
          AND kind = %s
          AND raw->>'idEvent' IN ({placeholders})
    """, [kind] + event_ids)
    return set(row[0] for row in cur.fetchall())


def fetch_detail(endpoint, event_id):
    url = f"{BASE}/{endpoint}/{event_id}"
    try:
        resp = requests.get(url, headers=HEADERS, timeout=15)
        if resp.status_code != 200:
            return None
        data = resp.json()
        # Different endpoints return different keys
        for key in data:
            if data[key]:
                return data[key] if isinstance(data[key], list) else [data[key]]
        return None
    except Exception as e:
        print(f"  Error {endpoint}/{event_id}: {e}")
        return None


def store_details(conn, event_id, kind, records):
    if not records:
        return 0
    now = datetime.now(timezone.utc)
    cur = conn.cursor()
    inserted = 0

    for rec in records:
        if not isinstance(rec, dict):
            continue
        rec["idEvent"] = event_id
        content_hash = sha256(rec)
        try:
            cur.execute("""
                INSERT INTO "ExternalFeedRecord"
                    (source, kind, league, season, "contentHash", "fetchedAt", raw, "createdAt")
                VALUES ('thesportsdb', %s, NULL, NULL, %s, %s, %s, %s)
                ON CONFLICT (source, "contentHash") DO NOTHING
            """, (kind, content_hash, now, json.dumps(rec), now))
            inserted += cur.rowcount
        except Exception:
            conn.rollback()
            continue

    conn.commit()
    return inserted


def main():
    db_url = load_db_url()
    if not db_url:
        print("ERROR: No SPORTS_DATABASE_URL")
        return

    conn = psycopg2.connect(db_url)
    event_ids = get_recent_event_ids(conn, days=3)
    print(f"Found {len(event_ids)} recent finished events to enrich")

    total = 0
    for kind, endpoint in DETAIL_ENDPOINTS.items():
        already = get_already_enriched(conn, event_ids, kind)
        to_fetch = [eid for eid in event_ids if eid not in already]
        print(f"  {kind}: {len(to_fetch)} to fetch ({len(already)} already done)")

        for eid in to_fetch:
            records = fetch_detail(endpoint, eid)
            if records:
                n = store_details(conn, eid, kind, records)
                total += n

    conn.close()
    print(f"TOTAL: {total} detail records stored")


if __name__ == "__main__":
    main()