# Find ALL Mahindra-related activities across all categories
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("\n" + "="*80)
print("SEARCHING FOR ALL MAHINDRA-RELATED ACTIVITIES")
print("="*80 + "\n")

with engine.connect() as conn:
    # Search in project_name
    print("1. Activities where project_name contains 'mahindra':")
    print("-" * 80)

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

    total_project_hours = 0
    for row in result:
        pname, cat, count, hours = row
        total_project_hours += hours or 0
        print(f"Project: {pname[:60]}")
        print(f"  Category: {cat} | Records: {count} | Hours: {hours:.2f}h\n")

    print(f"TOTAL from project_name: {total_project_hours:.2f}h\n")

    # Search in window_title
    print("2. Activities where window_title contains 'mahindra':")
    print("-" * 80)

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

    total_window_hours = 0
    for row in result:
        pname, cat, title, count, hours = row
        total_window_hours += hours or 0
        print(f"Window: {title[:60]}")
        print(f"  Project: {pname or 'NULL'} | Category: {cat} | Hours: {hours:.2f}h\n")

    print(f"TOTAL from window_title: {total_window_hours:.2f}h\n")

    # Search in application_name for VS Code
    print("3. VS Code activities (might contain Mahindra projects):")
    print("-" * 80)

    result = conn.execute(text("""
        SELECT
            project_name,
            category,
            COUNT(*) as count,
            SUM(duration) / 3600.0 as hours
        FROM activity_records
        WHERE application_name ILIKE '%code%'
        AND project_name IS NOT NULL
        AND project_name != 'general'
        GROUP BY project_name, category
        ORDER BY hours DESC
        LIMIT 15
    """))

    print("Top VS Code projects:\n")
    for row in result:
        pname, cat, count, hours = row
        print(f"{pname[:50]:50} | {cat:15} | {hours:6.2f}h")

    # Check 'general' category
    print("\n4. Activities in 'general' category:")
    print("-" * 80)

    result = conn.execute(text("""
        SELECT
            COUNT(*) as count,
            SUM(duration) / 3600.0 as hours,
            COUNT(DISTINCT developer_id) as developers
        FROM activity_records
        WHERE project_name = 'general'
    """))

    row = result.fetchone()
    if row:
        count, hours, devs = row
        print(f"Total 'general' records: {count}")
        print(f"Total 'general' hours: {hours:.2f}h")
        print(f"Developers: {devs}\n")

    # Sample general activities to see if Mahindra work is there
    print("5. Sample 'general' activities (checking for Mahindra):")
    print("-" * 80)

    result = conn.execute(text("""
        SELECT
            window_title,
            application_name,
            duration / 60.0 as minutes
        FROM activity_records
        WHERE project_name = 'general'
        AND (LOWER(window_title) LIKE '%mahindra%' OR LOWER(window_title) LIKE '%manulife%')
        ORDER BY duration DESC
        LIMIT 10
    """))

    for row in result:
        title, app, mins = row
        print(f"{app:20} | {mins:6.2f}m | {title[:60]}")

print("\n" + "="*80)
print("Search complete!")
print("="*80 + "\n")