#!/usr/bin/env python3
"""
Verify that backend code is consistent with database schema changes
"""

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

def verify_database_schema():
    """Check the database schema is correct"""
    DATABASE_URL = f"postgresql://postgres:{quote_plus('F@f@k0s!!')}@localhost:5432/bookbeach"
    engine = create_engine(DATABASE_URL)
    
    with engine.connect() as db:
        print("🔍 Verifying database schema...")
        
        # Check restaurants table
        result = db.execute(text("""
            SELECT column_name, data_type, is_nullable
            FROM information_schema.columns 
            WHERE table_name = 'restaurants' 
            ORDER BY ordinal_position
        """)).fetchall()
        
        columns = [col[0] for col in result]
        
        if 'restaurant_uuid' in columns:
            print("❌ ERROR: restaurant_uuid column still exists!")
            return False
        
        if 'restaurant_id' not in columns:
            print("❌ ERROR: restaurant_id column missing!")
            return False
        
        print("✅ Database schema is correct")
        
        # Check primary key
        pk_result = db.execute(text("""
            SELECT column_name FROM information_schema.table_constraints tc
            JOIN information_schema.key_column_usage kcu 
                ON tc.constraint_name = kcu.constraint_name
            WHERE tc.table_name = 'restaurants' 
                AND tc.constraint_type = 'PRIMARY KEY'
        """)).fetchall()
        
        if pk_result and pk_result[0][0] == 'restaurant_id':
            print("✅ Primary key is restaurant_id")
        else:
            print(f"❌ ERROR: Primary key issue - found: {pk_result}")
            return False
        
        return True

def check_backend_code():
    """Check backend code for any restaurant_uuid references"""
    print("\n🔍 Checking backend code for restaurant_uuid references...")
    
    backend_dir = "backend"
    if not os.path.exists(backend_dir):
        print("❌ Backend directory not found")
        return False
    
    restaurant_uuid_found = False
    
    for root, dirs, files in os.walk(backend_dir):
        for file in files:
            if file.endswith('.py'):
                file_path = os.path.join(root, file)
                try:
                    with open(file_path, 'r', encoding='utf-8') as f:
                        content = f.read()
                        if 'restaurant_uuid' in content:
                            print(f"⚠️  Found restaurant_uuid in: {file_path}")
                            # Show the lines containing restaurant_uuid
                            lines = content.split('\n')
                            for i, line in enumerate(lines, 1):
                                if 'restaurant_uuid' in line:
                                    print(f"   Line {i}: {line.strip()}")
                            restaurant_uuid_found = True
                except:
                    pass
    
    if not restaurant_uuid_found:
        print("✅ No restaurant_uuid references found in backend code")
    
    return not restaurant_uuid_found

def test_database_operations():
    """Test basic database operations"""
    print("\n🧪 Testing database operations...")
    
    DATABASE_URL = f"postgresql://postgres:{quote_plus('F@f@k0s!!')}@localhost:5432/bookbeach"
    engine = create_engine(DATABASE_URL)
    
    try:
        with engine.connect() as db:
            # Test querying restaurants
            result = db.execute(text("""
                SELECT restaurant_id, restaurant_name 
                FROM restaurants 
                LIMIT 1
            """)).fetchall()
            
            if result:
                print(f"✅ Can query restaurants: {result[0][1]} (ID: {result[0][0]})")
            else:
                print("ℹ️  No restaurants found in database")
            
            # Test querying restaurant_items with join
            result = db.execute(text("""
                SELECT ri.item_name, r.restaurant_name 
                FROM restaurant_items ri
                JOIN restaurants r ON ri.restaurant_id = r.restaurant_id
                LIMIT 1
            """)).fetchall()
            
            if result:
                print(f"✅ Can join restaurant_items: {result[0][0]} from {result[0][1]}")
            else:
                print("ℹ️  No restaurant items found")
            
            # Test querying restaurant_categories with join
            result = db.execute(text("""
                SELECT rc.category_name, r.restaurant_name 
                FROM restaurant_categories rc
                JOIN restaurants r ON rc.restaurant_id = r.restaurant_id
                LIMIT 1
            """)).fetchall()
            
            if result:
                print(f"✅ Can join restaurant_categories: {result[0][0]} from {result[0][1]}")
            else:
                print("ℹ️  No restaurant categories found")
        
        return True
        
    except Exception as e:
        print(f"❌ Database operation error: {e}")
        return False

def main():
    """Main verification function"""
    print("🚀 Backend Consistency Verification")
    print("=" * 50)
    
    all_good = True
    
    # Check database schema
    if not verify_database_schema():
        all_good = False
    
    # Check backend code
    if not check_backend_code():
        all_good = False
    
    # Test database operations
    if not test_database_operations():
        all_good = False
    
    print("\n" + "=" * 50)
    if all_good:
        print("🎉 ALL CHECKS PASSED!")
        print("✅ restaurant_uuid successfully removed")
        print("✅ Backend code uses restaurant_id correctly")
        print("✅ Database operations work properly")
        print("✅ System is ready for use")
    else:
        print("❌ SOME CHECKS FAILED!")
        print("Please review the issues above")
    
    return all_good

if __name__ == '__main__':
    main()