import sys
import os
sys.path.append(os.path.join(os.path.dirname(__file__), 'backend'))

from sqlalchemy import create_engine, text
from app.core.config import settings

# Create database engine
engine = create_engine(str(settings.DATABASE_URL))

# Create a connection
with engine.connect() as conn:
    # Check restaurants
    result = conn.execute(text('SELECT COUNT(*) FROM restaurants'))
    total_restaurants = result.fetchone()[0]
    print(f'Total restaurants: {total_restaurants}')
    
    # Check markets
    result = conn.execute(text('SELECT COUNT(*) FROM markets'))
    total_markets = result.fetchone()[0]
    print(f'Total markets: {total_markets}')
    
    # Check adventures
    result = conn.execute(text('SELECT COUNT(*) FROM adventures'))
    total_adventures = result.fetchone()[0]
    print(f'Total adventures: {total_adventures}')
    
    # Check reviews
    result = conn.execute(text('SELECT COUNT(*) FROM reviews'))
    total_reviews = result.fetchone()[0]
    print(f'Total reviews: {total_reviews}')
    
    result = conn.execute(text('SELECT COUNT(*) FROM reviews WHERE is_approved=true'))
    approved_reviews = result.fetchone()[0]
    print(f'Approved reviews: {approved_reviews}')
    
    # Check if there are any restaurants with bookings/reviews
    result = conn.execute(text('''
        SELECT r.restaurant_id, r.restaurant_name, COUNT(b.booking_id) as booking_count
        FROM restaurants r
        LEFT JOIN bookings b ON r.restaurant_id = b.restaurant_id
        GROUP BY r.restaurant_id, r.restaurant_name
        ORDER BY booking_count DESC
        LIMIT 5
    '''))
    print("\nTop 5 restaurants by bookings:")
    for row in result.fetchall():
        print(f"  {row.restaurant_name}: {row.booking_count} bookings")
    
    # Check if there are any restaurants with approved reviews
    result = conn.execute(text('''
        SELECT r.restaurant_id, r.restaurant_name, COUNT(rev.review_id) as review_count
        FROM restaurants r
        LEFT JOIN bookings b ON r.restaurant_id = b.restaurant_id
        LEFT JOIN reviews rev ON b.booking_id = rev.booking_id AND rev.is_approved = true
        GROUP BY r.restaurant_id, r.restaurant_name
        HAVING COUNT(rev.review_id) > 0
        ORDER BY review_count DESC
        LIMIT 5
    '''))
    print("\nRestaurants with approved reviews:")
    rows = result.fetchall()
    if rows:
        for row in rows:
            print(f"  {row.restaurant_name}: {row.review_count} approved reviews")
    else:
        print("  No restaurants with approved reviews found")