import os
import sys
import json
from sqlalchemy import create_engine, text
from dotenv import load_dotenv

# Add parent directory to path to import models
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from db_connection import DATABASE_URL
from models.house_model import House

def import_sabah_properties():
    """Import Sabah properties from JSON to PostgreSQL"""
    
    # Load environment variables from backend/.env.local
    env_path = os.path.join(os.path.dirname(os.path.dirname(__file__)), '.env.local')
    load_dotenv(dotenv_path=env_path)
    
    try:
        # Get database URL and create engine
        engine = create_engine(DATABASE_URL)
        
        print("🔗 Connecting to database...")
        
        # Check if properties table exists
        with engine.connect() as connection:
            result = connection.execute(text("""
                SELECT EXISTS (
                    SELECT FROM information_schema.tables 
                    WHERE table_schema = 'public' 
                    AND table_name = 'houses'
                );
            """))
            
            table_exists = result.fetchone()[0]
            
            if not table_exists:
                print("❌ Houses table does not exist!")
                print("Please run the database migrations first.")
                return False
        
        # Load properties data from house_listing.json
        json_file_path = os.path.join(
            os.path.dirname(os.path.dirname(os.path.dirname(__file__))),
            'house_listing,json'
        )
        
        if not os.path.exists(json_file_path):
            print(f"❌ Properties JSON file not found: {json_file_path}")
            print("Please ensure the JSON file exists in the project root.")
            return False
        
        print("📖 Loading properties from JSON file...")
        with open(json_file_path, 'r', encoding='utf-8') as f:
            properties_data = json.load(f)
        
        print(f"📊 Found {len(properties_data)} properties to import")
        

        # Delete all existing records from houses table before import
        imported_count = 0
        with engine.connect() as connection:
            print("⚠️  Deleting all existing records from houses table...")
            connection.execute(text("DELETE FROM houses;"))
            connection.commit()
            print("✅ All records deleted. Proceeding with import...")

            for property_data in properties_data:
                try:
                    # Map JSON fields to database columns (house_listing.json format)
                    title = property_data.get('title', '')
                    price = property_data.get('price', 0) or 0
                    address = property_data.get('address', '')
                    property_type = property_data.get('property_type', '')
                    furnishing_status = property_data.get('furnishing_status', 'Unknown')
                    bedroom = property_data.get('bedroom', 0) or 0
                    bathroom = property_data.get('bathroom', 0) or 0
                    size_sqft = property_data.get('size_sqft', 0) or 0
                    broadband_services = property_data.get('broadband_services', [])
                    agent = property_data.get('agent', {})
                    amenities = property_data.get('amenities', [])
                    facilities = property_data.get('facilities', [])
                    availability_on = property_data.get('availability_on', None)
                    latitude = property_data.get('latitude', 0.0) or 0.0
                    longitude = property_data.get('longitude', 0.0) or 0.0
                    images = property_data.get('images', [])
                    crime_level = property_data.get('crime_level', None)
                    
                    # Insert new property
                    connection.execute(text("""
                        INSERT INTO houses (
                            title, price, address, property_type, 
                            furnishing_status, bedroom, bathroom, size_sqft,
                            broadband_services, agent, amenities, facilities,
                            availability_on, latitude, longitude, images, crime_level
                        ) VALUES (
                            :title, :price, :address, :property_type,
                            :furnishing_status, :bedroom, :bathroom, :size_sqft,
                            :broadband_services, :agent, :amenities, :facilities,
                            :availability_on, :latitude, :longitude, :images, :crime_level
                        )
                    """), {
                        'title': title,
                        'price': price,
                        'address': address,
                        'property_type': property_type,
                        'furnishing_status': furnishing_status,
                        'bedroom': bedroom,
                        'bathroom': bathroom,
                        'size_sqft': size_sqft,
                        'broadband_services': json.dumps(broadband_services),
                        'agent': json.dumps(agent),
                        'amenities': json.dumps(amenities),
                        'facilities': json.dumps(facilities),
                        'availability_on': availability_on,
                        'latitude': latitude,
                        'longitude': longitude,
                        'images': json.dumps(images),
                        'crime_level': crime_level
                    })

                    imported_count += 1
                    if imported_count % 10 == 0:
                        print(f"📈 Imported {imported_count} properties...")

                except Exception as e:
                    print(f"⚠️  Error importing property {property_data.get('title', 'Unknown')}: {e}")
                    continue

            # Commit all changes
            connection.commit()

        print(f"✅ Successfully imported {imported_count} properties!")
        return True
        
    except Exception as e:
        print(f"❌ Import failed: {e}")
        return False

if __name__ == "__main__":
    print("🏠 Importing House Listings to PostgreSQL...")
    print("=" * 60)
    
    success = import_sabah_properties()
    
    print("=" * 60)
    if success:
        print("🎉 House listings import completed successfully!")
    else:
        print("💥 House listings import failed!")
        sys.exit(1)
