from sqlalchemy import create_engine, text
from urllib.parse import quote_plus
import os
from dotenv import load_dotenv

load_dotenv()

def get_decrypted_env_var(var_name, default=''):
    return os.getenv(var_name, default)

DATABASE_URL = f"postgresql://{os.getenv('DATABASE_USER', 'postgres')}:{quote_plus(get_decrypted_env_var('DATABASE_PASSWORD'))}@{os.getenv('DATABASE_HOST', 'localhost')}:{os.getenv('DATABASE_PORT', '5432')}/{os.getenv('DATABASE_NAME', 'bookbeach')}"

engine = create_engine(DATABASE_URL)

with engine.connect() as conn:
    # Check if restaurants table exists
    table_exists_result = conn.execute(text("""
        SELECT EXISTS (
            SELECT FROM information_schema.tables 
            WHERE table_schema = 'public' 
            AND table_name = 'restaurants'
        )
    """)).fetchone()
    
    table_exists = table_exists_result[0] if table_exists_result else False
    
    print(f"Restaurants table exists: {table_exists}")
    
    if table_exists:
        result = conn.execute(text('SELECT COUNT(*) FROM restaurants')).fetchone()
        restaurant_count = result[0] if result else 0
        print(f'Total restaurants: {restaurant_count}')
        
        if restaurant_count > 0:
            sample = conn.execute(text('SELECT restaurant_id, name, city, country_id FROM restaurants LIMIT 3')).fetchall()
            print('Sample restaurants:')
            for row in sample:
                print(f'  ID: {row[0]}, Name: {row[1]}, City: {row[2]}, Country ID: {row[3]}')
                
            # Test the exact query from the API
            print("\nTesting API query...")
            api_query = """
                SELECT r.restaurant_id, r.name, r.address, r.city, r.country_id,
                       co.country_name, r.latitude, r.longitude, r.cuisine_type,
                       r.phone, r.email, r.website, r.description,
                       COALESCE(booking_counts.booking_count, 0) as popularity,
                       rp.photo_path, r.created_at, r.updated_at
                FROM restaurants r
                LEFT JOIN countries co ON r.country_id = co.country_id
                LEFT JOIN (
                    SELECT restaurant_id, COUNT(*) as booking_count
                    FROM bookings 
                    WHERE status IN ('confirmed', 'completed')
                    GROUP BY restaurant_id
                ) booking_counts ON r.restaurant_id = booking_counts.restaurant_id
                LEFT JOIN (
                    SELECT DISTINCT ON (restaurant_id) 
                           restaurant_id, photo_path
                    FROM restaurant_photos 
                    WHERE is_primary = true
                    ORDER BY restaurant_id, sort_order
                ) rp ON r.restaurant_id = rp.restaurant_id
                WHERE 1=1
                ORDER BY popularity DESC, r.name
                LIMIT 5
            """
            
            results = conn.execute(text(api_query)).fetchall()
            print(f"API query returned {len(results)} restaurants")
            for row in results:
                print(f"  - {row.name} in {row.city or 'Unknown'}, {row.country_name or 'Unknown'}")