#!/usr/bin/env python3
"""
Script to fetch educational POIs for all properties in the database
and integrate them with the house database.
"""

import asyncio
import httpx
import json
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
from db.db_connection import DATABASE_URL
import time

# Database connection
engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

async def fetch_education_pois_for_property(lat: float, lon: float, property_id: int, property_title: str):
    """Fetch educational POIs for a single property"""
    try:
        async with httpx.AsyncClient() as client:
            response = await client.get(
                f"http://localhost:8000/api/location/education-pois",
                params={
                    "lat": lat,
                    "lon": lon,
                    "radius": 15000,
                    "limit": 50
                },
                timeout=30.0
            )
            
            if response.status_code == 200:
                pois = response.json()
                print(f"✅ Property {property_id} ({property_title}): Found {len(pois)} educational POIs")
                
                # Log sample POIs
                for poi in pois[:3]:
                    print(f"   - {poi['name']} ({poi['education_type']}) - {poi['distance']}km")
                
                return pois
            else:
                print(f"❌ Property {property_id}: API error {response.status_code}")
                return []
                
    except Exception as e:
        print(f"❌ Property {property_id}: Error fetching POIs - {e}")
        return []

async def fetch_all_education_pois():
    """Fetch educational POIs for all properties in the database"""
    print("🚀 Starting educational POI fetch for all properties...")
    
    # Get all properties with coordinates
    db = SessionLocal()
    try:
        result = db.execute(text("""
            SELECT id, title, latitude, longitude 
            FROM houses 
            WHERE latitude IS NOT NULL 
            AND longitude IS NOT NULL
            ORDER BY id
        """))
        
        properties = result.fetchall()
        print(f"📊 Found {len(properties)} properties with coordinates")
        
        if not properties:
            print("❌ No properties found with coordinates")
            return
        
        # Process properties in batches to avoid overwhelming the API
        batch_size = 5
        all_results = []
        
        for i in range(0, len(properties), batch_size):
            batch = properties[i:i + batch_size]
            print(f"\n🔄 Processing batch {i//batch_size + 1}/{(len(properties) + batch_size - 1)//batch_size}")
            
            # Process batch concurrently
            tasks = []
            for prop in batch:
                task = fetch_education_pois_for_property(
                    float(prop.latitude), 
                    float(prop.longitude), 
                    prop.id, 
                    prop.title
                )
                tasks.append(task)
            
            # Wait for batch to complete
            batch_results = await asyncio.gather(*tasks, return_exceptions=True)
            
            # Process results
            for j, result in enumerate(batch_results):
                if isinstance(result, Exception):
                    print(f"❌ Property {batch[j].id}: Exception - {result}")
                    continue
                
                all_results.append({
                    "property_id": batch[j].id,
                    "property_title": batch[j].title,
                    "latitude": float(batch[j].latitude),
                    "longitude": float(batch[j].longitude),
                    "educational_pois": result,
                    "poi_count": len(result)
                })
            
            # Add delay between batches to be respectful to the API
            if i + batch_size < len(properties):
                print("⏳ Waiting 2 seconds before next batch...")
                await asyncio.sleep(2)
        
        # Save results to file
        output_file = "educational_pois_all_properties.json"
        with open(output_file, 'w', encoding='utf-8') as f:
            json.dump(all_results, f, indent=2, ensure_ascii=False)
        
        # Summary statistics
        total_pois = sum(result["poi_count"] for result in all_results)
        properties_with_pois = len([r for r in all_results if r["poi_count"] > 0])
        
        print(f"\n📈 SUMMARY:")
        print(f"   Properties processed: {len(all_results)}")
        print(f"   Properties with educational POIs: {properties_with_pois}")
        print(f"   Total educational POIs found: {total_pois}")
        print(f"   Results saved to: {output_file}")
        
        # Show top properties by POI count
        sorted_results = sorted(all_results, key=lambda x: x["poi_count"], reverse=True)
        print(f"\n🏆 TOP 10 PROPERTIES BY EDUCATIONAL POI COUNT:")
        for i, result in enumerate(sorted_results[:10]):
            print(f"   {i+1}. {result['property_title']} - {result['poi_count']} POIs")
        
    finally:
        db.close()

def create_education_pois_table():
    """Create a table to store educational POIs for properties"""
    db = SessionLocal()
    try:
        # Create educational_pois table
        db.execute(text("""
            CREATE TABLE IF NOT EXISTS educational_pois (
                id SERIAL PRIMARY KEY,
                property_id INTEGER REFERENCES houses(id) ON DELETE CASCADE,
                poi_id VARCHAR(255) NOT NULL,
                name VARCHAR(500) NOT NULL,
                amenity VARCHAR(100),
                education_type VARCHAR(50),
                latitude DECIMAL(9, 6) NOT NULL,
                longitude DECIMAL(9, 6) NOT NULL,
                distance_km DECIMAL(8, 2),
                tags JSONB,
                source VARCHAR(50) DEFAULT 'real_osm_education',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(property_id, poi_id)
            )
        """))
        
        # Create indexes for better performance
        db.execute(text("""
            CREATE INDEX IF NOT EXISTS idx_educational_pois_property_id 
            ON educational_pois(property_id)
        """))
        
        db.execute(text("""
            CREATE INDEX IF NOT EXISTS idx_educational_pois_education_type 
            ON educational_pois(education_type)
        """))
        
        db.execute(text("""
            CREATE INDEX IF NOT EXISTS idx_educational_pois_distance 
            ON educational_pois(distance_km)
        """))
        
        db.commit()
        print("✅ Educational POIs table created successfully")
        
    except Exception as e:
        print(f"❌ Error creating table: {e}")
        db.rollback()
    finally:
        db.close()

def import_education_pois_to_database(json_file: str):
    """Import educational POIs from JSON file to database"""
    print(f"📥 Importing educational POIs from {json_file}...")
    
    try:
        with open(json_file, 'r', encoding='utf-8') as f:
            data = json.load(f)
        
        db = SessionLocal()
        try:
            # Clear existing data
            db.execute(text("DELETE FROM educational_pois"))
            
            imported_count = 0
            for property_data in data:
                property_id = property_data["property_id"]
                
                for poi in property_data["educational_pois"]:
                    try:
                        db.execute(text("""
                            INSERT INTO educational_pois (
                                property_id, poi_id, name, amenity, education_type,
                                latitude, longitude, distance_km, tags, source
                            ) VALUES (
                                :property_id, :poi_id, :name, :amenity, :education_type,
                                :latitude, :longitude, :distance_km, :tags, :source
                            )
                        """), {
                            'property_id': property_id,
                            'poi_id': poi['id'],
                            'name': poi['name'],
                            'amenity': poi['amenity'],
                            'education_type': poi['education_type'],
                            'latitude': poi['lat'],
                            'longitude': poi['lon'],
                            'distance_km': poi['distance'],
                            'tags': json.dumps(poi['tags']),
                            'source': poi['source']
                        })
                        imported_count += 1
                        
                    except Exception as e:
                        print(f"❌ Error importing POI {poi['id']}: {e}")
                        continue
            
            db.commit()
            print(f"✅ Successfully imported {imported_count} educational POIs to database")
            
        finally:
            db.close()
            
    except Exception as e:
        print(f"❌ Error importing data: {e}")

async def main():
    """Main function"""
    print("🎓 Educational POI Integration Script")
    print("=" * 50)
    
    # Step 1: Create database table
    print("\n1️⃣ Creating database table...")
    create_education_pois_table()
    
    # Step 2: Fetch educational POIs for all properties
    print("\n2️⃣ Fetching educational POIs...")
    await fetch_all_education_pois()
    
    # Step 3: Import to database
    print("\n3️⃣ Importing to database...")
    import_education_pois_to_database("educational_pois_all_properties.json")
    
    print("\n🎉 Educational POI integration completed!")

if __name__ == "__main__":
    asyncio.run(main())
