# Check what projects are being filtered out
from sqlalchemy import create_engine, text
import os

DATABASE_URL = os.getenv("DATABASE_URL", "postgresql://postgres:asdf1234@localhost:5432/timesheet")
engine = create_engine(DATABASE_URL)

print("Checking projects filtered out by current rules...")
print("=" * 80)

with engine.connect() as conn:
    # Get projects from productive category with spaces (currently filtered out)
    result = conn.execute(text("""
        SELECT
            project_name,
            COUNT(*) as activity_count,
            SUM(duration) / 3600.0 as total_hours
        FROM activity_records
        WHERE category = 'productive'
        AND project_name IS NOT NULL
        AND project_name != ''
        AND project_name LIKE '% %'
        AND LENGTH(project_name) >= 4
        GROUP BY project_name
        ORDER BY total_hours DESC
        LIMIT 50
    """))

    projects_with_spaces = result.fetchall()

    print(f"\nProjects WITH SPACES (currently filtered out): {len(projects_with_spaces)}")
    print("-" * 80)
    print(f"{'Project Name':<60} {'Hours':<10}")
    print("-" * 80)

    for row in projects_with_spaces:
        project_name = row[0][:60]
        total_hours = row[2]
        print(f"{project_name:<60} {total_hours:.2f}")

    # Search for Mahindra and Sinoglobal projects
    print("\n" + "=" * 80)
    print("Searching for Mahindra projects...")
    print("-" * 80)

    result = conn.execute(text("""
        SELECT
            project_name,
            category,
            COUNT(*) as activity_count,
            SUM(duration) / 3600.0 as total_hours
        FROM activity_records
        WHERE LOWER(project_name) LIKE '%mahindra%'
        GROUP BY project_name, category
        ORDER BY total_hours DESC
    """))

    for row in result:
        print(f"{row[0]:<50} | Category: {row[1]:<12} | Hours: {row[3]:.2f}")

    print("\n" + "=" * 80)
    print("Searching for Sinoglobal projects...")
    print("-" * 80)

    result = conn.execute(text("""
        SELECT
            project_name,
            category,
            COUNT(*) as activity_count,
            SUM(duration) / 3600.0 as total_hours
        FROM activity_records
        WHERE LOWER(project_name) LIKE '%sinoglobal%' OR LOWER(project_name) LIKE '%sino%'
        GROUP BY project_name, category
        ORDER BY total_hours DESC
    """))

    sino_projects = result.fetchall()
    if sino_projects:
        for row in sino_projects:
            print(f"{row[0]:<50} | Category: {row[1]:<12} | Hours: {row[3]:.2f}")
    else:
        print("No Sinoglobal projects found in database")