import sys
import os

# Add the backend directory to the path
sys.path.append(os.path.dirname(os.path.abspath(__file__)))

from config import ADMIN_CONFIG
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker

def test_companies_query():
    try:
        # Database connection
        DATABASE_URL = ADMIN_CONFIG['DATABASE_URL']
        engine = create_engine(DATABASE_URL)
        SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
        
        db = SessionLocal()
        
        # Test the exact query we're using
        query = """
            SELECT 
                c.company_id,
                c.company_name,
                c.website,
                c.email AS contact_email,
                c.phone AS contact_phone,
                c.company_logo_path AS logo_path,
                c.create_date AS created_at,
                c.update_date AS updated_at,
                c.vat_number,
                c.city,
                c.province,
                c.country_id,
                c.company_status,
                co.country_name,
                COALESCE(beach_counts.count, 0) AS beaches_count,
                COALESCE(restaurant_counts.count, 0) AS restaurants_count,
                COALESCE(market_counts.count, 0) AS markets_count,
                COALESCE(adventure_counts.count, 0) AS adventures_count
            FROM companies c
            LEFT JOIN countries co ON c.country_id = co.country_id
            LEFT JOIN (
                SELECT company_id, COUNT(*) AS count
                FROM beach_places
                GROUP BY company_id
            ) beach_counts ON c.company_id = beach_counts.company_id
            LEFT JOIN (
                SELECT company_id, COUNT(*) AS count
                FROM restaurants
                GROUP BY company_id
            ) restaurant_counts ON c.company_id = restaurant_counts.company_id
            LEFT JOIN (
                SELECT company_id, COUNT(*) AS count
                FROM markets
                GROUP BY company_id
            ) market_counts ON c.company_id = market_counts.company_id
            LEFT JOIN (
                SELECT company_id, COUNT(*) AS count
                FROM adventures
                GROUP BY company_id
            ) adventure_counts ON c.company_id = adventure_counts.company_id
            ORDER BY c.company_name
            LIMIT 5
        """
        
        print("Executing companies query...")
        companies = db.execute(text(query)).fetchall()
        print(f"Found {len(companies)} companies")
        
        for company in companies:
            print(f"  - {company.company_id}: {company.company_name}")
            print(f"    Status: {company.company_status}")
            print(f"    Country: {company.country_name}")
            print(f"    Businesses: {company.beaches_count} beaches, {company.restaurants_count} restaurants, {company.markets_count} markets, {company.adventures_count} adventures")
            print()
        
        db.close()
        
    except Exception as e:
        print(f"Error testing companies query: {str(e)}")
        import traceback
        traceback.print_exc()

if __name__ == "__main__":
    test_companies_query()