#!/usr/bin/env python3
"""
Check foreign key constraints that reference restaurant table
"""

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

def check_foreign_keys():
    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 foreign key constraints that reference restaurants table...")
        
        # Check foreign keys pointing to restaurants table
        result = db.execute(text("""
            SELECT 
                tc.table_name, 
                tc.constraint_name, 
                kcu.column_name, 
                ccu.table_name AS foreign_table_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
                  AND tc.table_schema = kcu.table_schema
                JOIN information_schema.constraint_column_usage AS ccu
                  ON ccu.constraint_name = tc.constraint_name
                  AND ccu.table_schema = tc.table_schema
            WHERE 
                tc.constraint_type = 'FOREIGN KEY' 
                AND ccu.table_name = 'restaurants'
            ORDER BY tc.table_name, tc.constraint_name;
        """)).fetchall()
        
        print("Foreign keys referencing restaurants table:")
        for table_name, constraint_name, column_name, foreign_table, foreign_column in result:
            print(f"  {table_name}.{column_name} -> {foreign_table}.{foreign_column} (constraint: {constraint_name})")
        
        # Check what columns exist in restaurants table
        print(f"\n📋 Restaurants table columns:")
        columns = 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 columns:
            print(f"  {col_name}: {data_type} (nullable: {is_nullable})")
        
        # Check restaurant_items table structure
        print(f"\n📋 Restaurant_items table columns:")
        items_columns = db.execute(text("""
            SELECT column_name, data_type, is_nullable
            FROM information_schema.columns 
            WHERE table_name = 'restaurant_items' 
            ORDER BY ordinal_position
        """)).fetchall()
        
        for col_name, data_type, is_nullable in items_columns:
            print(f"  {col_name}: {data_type} (nullable: {is_nullable})")
        
        # Check restaurant_categories table structure
        print(f"\n📋 Restaurant_categories table columns:")
        categories_columns = db.execute(text("""
            SELECT column_name, data_type, is_nullable
            FROM information_schema.columns 
            WHERE table_name = 'restaurant_categories' 
            ORDER BY ordinal_position
        """)).fetchall()
        
        for col_name, data_type, is_nullable in categories_columns:
            print(f"  {col_name}: {data_type} (nullable: {is_nullable})")

if __name__ == '__main__':
    check_foreign_keys()