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 debug_companies():
    try:
        # Database connection
        DATABASE_URL = ADMIN_CONFIG['DATABASE_URL']
        engine = create_engine(DATABASE_URL)
        SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
        
        db = SessionLocal()
        
        # Check what columns exist in the companies table
        print("Checking companies table structure...")
        columns_result = db.execute(text("""
            SELECT column_name, data_type 
            FROM information_schema.columns 
            WHERE table_name = 'companies' 
            ORDER BY ordinal_position
        """)).fetchall()
        
        print("Columns in companies table:")
        for column in columns_result:
            print(f"  - {column[0]} ({column[1]})")
        
        # Check the actual query being used in the route
        print("\nTesting the companies query from the route...")
        columns_to_select = ["company_id", "company_name"]
        
        # Check each optional column
        optional_columns = [
            ("description", "description"),
            ("website", "website"),
            ("contact_email", "contact_email"),
            ("contact_phone", "contact_phone"),
            ("logo_path", "logo_path"),
            ("created_at", "created_at"),
            ("updated_at", "updated_at")
        ]
        
        for column_name, column_alias in optional_columns:
            column_check = db.execute(text(f"""
                SELECT EXISTS (
                    SELECT column_name FROM information_schema.columns 
                    WHERE table_name = 'companies' AND column_name = '{column_name}'
                )
            """)).fetchone()
            
            if column_check and column_check[0]:
                columns_to_select.append(f"{column_name} AS {column_alias}")
                print(f"  Including column: {column_name} AS {column_alias}")
            else:
                print(f"  Skipping column (not found): {column_name}")
        
        # Build the query with only existing columns
        columns_str = ", ".join(columns_to_select)
        query = f"""
            SELECT {columns_str}
            FROM companies
            ORDER BY company_name
            LIMIT 5
        """
        
        print(f"\nExecuting query:\n{query}")
        
        companies = db.execute(text(query)).fetchall()
        print(f"\nFound {len(companies)} companies with this query:")
        for company in companies:
            print(f"  - {company.company_id}: {company.company_name}")
        
        db.close()
        
    except Exception as e:
        print(f"Error debugging companies: {str(e)}")
        import traceback
        traceback.print_exc()

if __name__ == "__main__":
    debug_companies()