#!/usr/bin/env python3
"""
Final cleanup of restaurant_uuid issues - completely rebuild foreign key constraints
"""

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

def final_cleanup():
    DATABASE_URL = f"postgresql://postgres:{quote_plus('F@f@k0s!!')}@localhost:5432/bookbeach"
    engine = create_engine(DATABASE_URL)
    
    with engine.connect() as db:
        print("🔧 Final cleanup of restaurant_uuid dependencies...")
        
        try:
            # Step 1: Drop all foreign key constraints that depend on restaurants table
            print("Step 1: Dropping all foreign keys that reference restaurants...")
            
            fk_constraints = [
                ("restaurant_items", "restaurant_items_restaurant_id_fkey"),
                ("restaurant_items", "fk_restaurant_items_restaurant"),
                ("restaurant_categories", "fk_restaurant_categories_restaurant")
            ]
            
            for table_name, constraint_name in fk_constraints:
                try:
                    # Check if constraint exists
                    exists = db.execute(text(f"""
                        SELECT 1 FROM pg_constraint 
                        WHERE conname = '{constraint_name}' 
                          AND conrelid = '{table_name}'::regclass
                    """)).fetchone()
                    
                    if exists:
                        db.execute(text(f"""
                            ALTER TABLE {table_name} DROP CONSTRAINT {constraint_name}
                        """))
                        print(f"  ✅ Dropped {table_name}.{constraint_name}")
                    else:
                        print(f"  ℹ️ {table_name}.{constraint_name} doesn't exist")
                except Exception as e:
                    print(f"  ⚠️ Error dropping {table_name}.{constraint_name}: {e}")
            
            # Step 2: Now drop the restaurant_uuid constraint
            print("\nStep 2: Dropping restaurant_uuid constraint...")
            try:
                db.execute(text("""
                    ALTER TABLE restaurants DROP CONSTRAINT restaurants_restaurant_uuid_key CASCADE
                """))
                print("  ✅ Dropped restaurants_restaurant_uuid_key")
            except Exception as e:
                print(f"  ⚠️ Error dropping constraint: {e}")
            
            # Step 3: Recreate foreign key constraints properly
            print("\nStep 3: Recreating foreign key constraints...")
            
            # Check that restaurant_id column exists and is the primary key
            pk_check = db.execute(text("""
                SELECT column_name FROM information_schema.columns 
                WHERE table_name = 'restaurants' AND column_name = 'restaurant_id'
            """)).fetchone()
            
            if not pk_check:
                print("❌ ERROR: restaurant_id column doesn't exist!")
                return
            
            # Recreate restaurant_items foreign key
            try:
                db.execute(text("""
                    ALTER TABLE restaurant_items 
                    ADD CONSTRAINT restaurant_items_restaurant_id_fkey 
                    FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id)
                    ON DELETE CASCADE
                """))
                print("  ✅ Created restaurant_items.restaurant_id foreign key")
            except Exception as e:
                print(f"  ⚠️ Error creating restaurant_items FK: {e}")
            
            # Recreate restaurant_categories foreign key
            try:
                db.execute(text("""
                    ALTER TABLE restaurant_categories 
                    ADD CONSTRAINT restaurant_categories_restaurant_id_fkey 
                    FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id)
                    ON DELETE CASCADE
                """))
                print("  ✅ Created restaurant_categories.restaurant_id foreign key")
            except Exception as e:
                print(f"  ⚠️ Error creating restaurant_categories FK: {e}")
            
            db.commit()
            
            # Step 4: Final verification
            print("\n📋 Final verification:")
            
            # Check 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()
            
            print("  Restaurants table columns:")
            restaurant_uuid_found = 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_found = True
            
            if restaurant_uuid_found:
                print("  ❌ WARNING: restaurant_uuid column still exists!")
            else:
                print("  ✅ restaurant_uuid column successfully removed")
            
            # Check constraints
            constraints = db.execute(text("""
                SELECT conname, contype 
                FROM pg_constraint 
                WHERE conrelid = 'restaurants'::regclass
            """)).fetchall()
            
            print("  Restaurants table constraints:")
            uuid_constraint_found = False
            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}")
                if 'restaurant_uuid' in name:
                    uuid_constraint_found = True
            
            if uuid_constraint_found:
                print("  ❌ WARNING: restaurant_uuid constraint still exists!")
            else:
                print("  ✅ restaurant_uuid constraint successfully removed")
            
            # Check foreign keys pointing to restaurants
            fk_result = db.execute(text("""
                SELECT 
                    tc.table_name, 
                    tc.constraint_name, 
                    kcu.column_name, 
                    ccu.column_name AS foreign_column_name
                FROM 
                    information_schema.table_constraints AS tc 
                    JOIN information_schema.key_column_usage AS kcu
                      ON tc.constraint_name = kcu.constraint_name
                    JOIN information_schema.constraint_column_usage AS ccu
                      ON ccu.constraint_name = tc.constraint_name
                WHERE 
                    tc.constraint_type = 'FOREIGN KEY' 
                    AND ccu.table_name = 'restaurants'
                ORDER BY tc.table_name;
            """)).fetchall()
            
            print("  Foreign keys referencing restaurants:")
            for table_name, constraint_name, column_name, foreign_column in fk_result:
                print(f"    {table_name}.{column_name} -> restaurants.{foreign_column} ({constraint_name})")
            
            print(f"\n🎉 Cleanup completed!")
            
        except Exception as e:
            db.rollback()
            print(f"❌ Error during cleanup: {e}")
            raise

if __name__ == '__main__':
    final_cleanup()