#!/usr/bin/env python3
"""
Execute the removal of restaurant_uuid column
"""

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

def remove_restaurant_uuid():
    DATABASE_URL = f"postgresql://postgres:{quote_plus('F@f@k0s!!')}@localhost:5432/bookbeach"
    engine = create_engine(DATABASE_URL)
    
    with engine.connect() as db:
        print("🔍 Checking current restaurants table structure...")
        
        # Check current structure
        result = db.execute(text("""
            SELECT column_name, data_type, is_nullable
            FROM information_schema.columns 
            WHERE table_name = 'restaurants' 
            ORDER BY ordinal_position
        """)).fetchall()
        
        print("Current columns:")
        restaurant_uuid_exists = False
        for col_name, data_type, is_nullable in result:
            print(f"  {col_name}: {data_type} (nullable: {is_nullable})")
            if col_name == 'restaurant_uuid':
                restaurant_uuid_exists = True
        
        if not restaurant_uuid_exists:
            print("✅ restaurant_uuid column does not exist. Nothing to remove.")
            return
        
        print(f"\n🗑️ Removing redundant restaurant_uuid column...")
        
        try:
            # Step 1: Drop constraint if exists
            constraint_check = db.execute(text("""
                SELECT 1 FROM pg_constraint WHERE conname = 'restaurants_restaurant_uuid_key'
            """)).fetchone()
            
            if constraint_check:
                db.execute(text("""
                    ALTER TABLE restaurants DROP CONSTRAINT restaurants_restaurant_uuid_key
                """))
                print("✅ Dropped constraint: restaurants_restaurant_uuid_key")
            else:
                print("ℹ️ Constraint restaurants_restaurant_uuid_key does not exist")
            
            # Step 2: Drop column
            db.execute(text("""
                ALTER TABLE restaurants DROP COLUMN restaurant_uuid
            """))
            print("✅ Dropped column: restaurant_uuid")
            
            db.commit()
            
            # Step 3: Verify final structure
            print(f"\n📋 Final restaurants table structure:")
            result = db.execute(text("""
                SELECT column_name, data_type, is_nullable
                FROM information_schema.columns 
                WHERE table_name = 'restaurants' 
                ORDER BY ordinal_position
            """)).fetchall()
            
            for col_name, data_type, is_nullable in result:
                print(f"  {col_name}: {data_type} (nullable: {is_nullable})")
            
            # Check constraints
            constraints = db.execute(text("""
                SELECT conname, contype 
                FROM pg_constraint 
                WHERE conrelid = 'restaurants'::regclass
            """)).fetchall()
            
            print(f"\nConstraints:")
            for name, type_code in constraints:
                constraint_type = {'p': 'PRIMARY KEY', 'f': 'FOREIGN KEY', 'u': 'UNIQUE', 'c': 'CHECK'}.get(type_code, type_code)
                print(f"  {name}: {constraint_type}")
            
            print(f"\n🎉 Successfully removed redundant restaurant_uuid column!")
            
        except Exception as e:
            db.rollback()
            print(f"❌ Error: {e}")
            raise

if __name__ == '__main__':
    remove_restaurant_uuid()