#!/usr/bin/env python3
"""
Add latitude and longitude columns to restaurants table
"""

import os
from sqlalchemy import create_engine, text
from urllib.parse import quote_plus

def add_restaurant_coordinates_columns():
    """Add latitude and longitude columns to restaurants table"""
    try:
        # Database connection
        password = quote_plus('F@f@k0s!!')
        DATABASE_URL = f'postgresql://postgres:{password}@localhost:5432/bookbeach'
        engine = create_engine(DATABASE_URL)
        
        # Read the SQL migration script
        with open('add_restaurant_coordinates.sql', 'r') as f:
            sql_script = f.read()
        
        with engine.connect() as db:
            print("🌍 Adding latitude and longitude columns to restaurants table...")
            
            # Execute the migration
            db.execute(text(sql_script))
            db.commit()
            
            print("✅ Coordinate columns added successfully!")
            
            # Verify the changes
            result = db.execute(text("""
                SELECT column_name, data_type, is_nullable, numeric_precision, numeric_scale
                FROM information_schema.columns 
                WHERE table_name = 'restaurants' 
                AND column_name IN ('latitude', 'longitude')
                ORDER BY column_name
            """)).fetchall()
            
            print("\n📋 New coordinate columns added:")
            for row in result:
                nullable = 'NULL' if row.is_nullable == 'YES' else 'NOT NULL'
                precision_info = f"({row.numeric_precision},{row.numeric_scale})" if row.numeric_precision else ""
                print(f"   {row.column_name}: {row.data_type}{precision_info} {nullable}")
            
    except Exception as e:
        print(f"❌ Error: {e}")
        import traceback
        traceback.print_exc()

if __name__ == "__main__":
    add_restaurant_coordinates_columns()