#!/usr/bin/env python3
"""
Execute restaurant_photos table creation
"""

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

def create_restaurant_photos_table():
    DATABASE_URL = f"postgresql://postgres:{quote_plus('F@f@k0s!!')}@localhost:5432/bookbeach"
    engine = create_engine(DATABASE_URL)
    
    with engine.connect() as db:
        print("🔧 Creating restaurant_photos table...")
        
        try:
            # Read and execute the SQL file
            with open('create_restaurant_photos_table.sql', 'r') as f:
                sql_content = f.read()
            
            # Execute the SQL
            db.execute(text(sql_content))
            db.commit()
            
            print("✅ Successfully created restaurant_photos table")
            
            # Verify table creation
            result = db.execute(text("""
                SELECT column_name, data_type, is_nullable
                FROM information_schema.columns 
                WHERE table_name = 'restaurant_photos' 
                ORDER BY ordinal_position
            """)).fetchall()
            
            print("\n📋 Restaurant_photos table structure:")
            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 = 'restaurant_photos'::regclass
            """)).fetchall()
            
            print("\n🔗 Constraints:")
            for name, type_code in constraints:
                constraint_type = {'p': 'PRIMARY KEY', 'f': 'FOREIGN KEY', 'u': 'UNIQUE', 'c': 'CHECK', 'x': 'EXCLUDE'}.get(type_code, type_code)
                print(f"  {name}: {constraint_type}")
            
        except Exception as e:
            db.rollback()
            print(f"❌ Error creating table: {e}")
            raise

if __name__ == '__main__':
    create_restaurant_photos_table()