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 simple_companies_test():
    try:
        # Database connection
        DATABASE_URL = ADMIN_CONFIG['DATABASE_URL']
        engine = create_engine(DATABASE_URL)
        SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
        
        db = SessionLocal()
        
        # Simple test - just get a count of companies
        count_result = db.execute(text("SELECT COUNT(*) FROM companies")).fetchone()
        total_count = count_result[0] if count_result else 0
        print(f"Total companies in database: {total_count}")
        
        # Test the full query without pagination
        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 10
        """
        
        companies = db.execute(text(query)).fetchall()
        print(f"Companies returned by query: {len(companies)}")
        
        for i, company in enumerate(companies):
            print(f"  {i+1}. {company.company_name} (ID: {company.company_id})")
            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")
        
        db.close()
        
    except Exception as e:
        print(f"Error in simple companies test: {str(e)}")
        import traceback
        traceback.print_exc()

if __name__ == "__main__":
    simple_companies_test()