#!/usr/bin/env python3
"""
Ingest stadium data from stadium_data.json into SportsDB Stadium table
"""
import os
import json
import psycopg2
from pathlib import Path

# Load environment
def load_env():
    env = {}
    env_path = Path('/var/www/html/eventheodds/.env')
    if env_path.exists():
        with open(env_path, 'r') as f:
            for line in f:
                if '=' in line and not line.startswith('#'):
                    key, val = line.strip().split('=', 1)
                    env[key] = val
    return env

ENV = load_env()
DB_URL = ENV.get('SPORTS_DATABASE_URL', '').split('?')[0]

def ingest_stadiums():
    """Ingest stadium data into database"""

    # Load stadium data
    script_dir = Path(__file__).parent
    stadium_file = script_dir / 'stadium_data.json'

    if not stadium_file.exists():
        print(f"❌ Stadium data file not found: {stadium_file}")
        return

    with open(stadium_file, 'r') as f:
        stadium_data = json.load(f)

    # Connect to database
    conn = psycopg2.connect(DB_URL)
    cur = conn.cursor()

    stadiums_added = 0
    stadiums_skipped = 0

    # Process each league
    for league, stadiums in stadium_data.items():
        print(f"\n📍 Processing {league.upper()} ({len(stadiums)} stadiums)...")

        for stadium in stadiums:
            try:
                # Handle different field names between leagues
                name = stadium['name']
                city = stadium['city']
                state = stadium.get('state')
                country = stadium.get('country', 'USA')
                lat = stadium['lat'] if 'lat' in stadium else stadium.get('latitude')
                lon = stadium['lon'] if 'lon' in stadium else stadium.get('longitude')
                teams = stadium['teams']
                is_indoor = stadium.get('indoor', False)

                if not lat or not lon:
                    print(f"  ⚠️  Skipping {name} - missing coordinates")
                    stadiums_skipped += 1
                    continue

                # Insert or update stadium
                cur.execute('''
                    INSERT INTO "Stadium" ("name", "city", "state", "country", "latitude", "longitude", "league", "teamIds", "isIndoor")
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                    ON CONFLICT ("name", "city") DO UPDATE SET
                        "state" = EXCLUDED."state",
                        "country" = EXCLUDED."country",
                        "latitude" = EXCLUDED."latitude",
                        "longitude" = EXCLUDED."longitude",
                        "league" = EXCLUDED."league",
                        "teamIds" = EXCLUDED."teamIds",
                        "isIndoor" = EXCLUDED."isIndoor"
                ''', (name, city, state, country, lat, lon, league, teams, is_indoor))

                stadiums_added += 1
                print(f"  ✅ {name} ({city})")

            except Exception as e:
                print(f"  ❌ Error with {stadium.get('name', 'unknown')}: {e}")
                stadiums_skipped += 1
                continue

    conn.commit()

    # Summary
    cur.execute('SELECT league, COUNT(*) FROM "Stadium" GROUP BY league ORDER BY league')
    print("\n📊 Stadium Database Summary:")
    for row in cur.fetchall():
        print(f"  {row[0]}: {row[1]} stadiums")

    cur.close()
    conn.close()

    print(f"\n🎯 Ingestion complete: {stadiums_added} added, {stadiums_skipped} skipped")

if __name__ == '__main__':
    ingest_stadiums()