#!/usr/bin/env python3
"""
Properly remove restaurant_uuid column by fixing foreign key dependencies
"""

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

def fix_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 and fixing foreign key dependencies...")
        
        try:
            # First, let's check which constraints depend on the restaurant_uuid index
            dependent_constraints = db.execute(text("""
                SELECT 
                    conname,
                    conrelid::regclass AS table_name,
                    confrelid::regclass AS foreign_table,
                    pg_get_constraintdef(oid) AS definition
                FROM pg_constraint 
                WHERE confrelid = 'restaurants'::regclass
                  AND contype = 'f'
            """)).fetchall()
            
            print("Foreign key constraints referencing restaurants:")
            for conname, table_name, foreign_table, definition in dependent_constraints:
                print(f"  {conname}: {table_name} -> {foreign_table}")
                print(f"    Definition: {definition}")
            
            # Let's try a different approach: drop the column with CASCADE
            print(f"\n🗑️ Attempting to drop restaurant_uuid column with CASCADE...")
            
            # First check if restaurant_uuid exists
            uuid_exists = db.execute(text("""
                SELECT 1 FROM information_schema.columns 
                WHERE table_name = 'restaurants' AND column_name = 'restaurant_uuid'
            """)).fetchone()
            
            if not uuid_exists:
                print("✅ restaurant_uuid column does not exist. Nothing to remove.")
                return
            
            # Drop the column with CASCADE (this will drop dependent constraints)
            db.execute(text("""
                ALTER TABLE restaurants DROP COLUMN restaurant_uuid CASCADE
            """))
            
            print("✅ Successfully dropped restaurant_uuid column and its dependencies")
            
            # Now recreate any necessary foreign key constraints that were dropped
            # Check if the foreign keys still exist
            print(f"\n🔧 Checking and recreating necessary foreign key constraints...")
            
            # Check restaurant_items foreign key
            items_fk_exists = db.execute(text("""
                SELECT 1 FROM pg_constraint 
                WHERE conname = 'restaurant_items_restaurant_id_fkey'
                  AND conrelid = 'restaurant_items'::regclass
            """)).fetchone()
            
            if not items_fk_exists:
                print("Recreating restaurant_items.restaurant_id foreign key...")
                db.execute(text("""
                    ALTER TABLE restaurant_items 
                    ADD CONSTRAINT restaurant_items_restaurant_id_fkey 
                    FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id)
                """))
                print("✅ Created restaurant_items_restaurant_id_fkey")
            
            # Check restaurant_categories foreign key
            categories_fk_exists = db.execute(text("""
                SELECT 1 FROM pg_constraint 
                WHERE conname LIKE '%restaurant_categories%restaurant%'
                  AND conrelid = 'restaurant_categories'::regclass
            """)).fetchone()
            
            if not categories_fk_exists:
                print("Recreating restaurant_categories.restaurant_id foreign key...")
                db.execute(text("""
                    ALTER TABLE restaurant_categories 
                    ADD CONSTRAINT restaurant_categories_restaurant_id_fkey 
                    FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id)
                """))
                print("✅ Created restaurant_categories_restaurant_id_fkey")
            
            db.commit()
            
            # 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 final constraints
            print(f"\n🔗 Final constraints on restaurants table:")
            constraints = db.execute(text("""
                SELECT conname, contype 
                FROM pg_constraint 
                WHERE conrelid = 'restaurants'::regclass
            """)).fetchall()
            
            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__':
    fix_remove_restaurant_uuid()