# Check database for Mahindra Manulife CMS project
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)

project_name = "Mahindra Manulife CMS"

print(f"\n{'='*80}")
print(f"DATABASE ANALYSIS FOR: {project_name}")
print(f"{'='*80}\n")

with engine.connect() as conn:
    # Check if project exists in database
    print("1. Checking if project exists in activity_records...")
    print("-" * 80)

    result = conn.execute(text("""
        SELECT COUNT(*) as total_records
        FROM activity_records
        WHERE project_name = :project_name
    """), {"project_name": project_name})

    count = result.fetchone()[0]
    print(f"Total records found: {count}\n")

    if count == 0:
        print(f"[X] No records found for '{project_name}'")
        print("\nSearching for similar project names...")
        print("-" * 80)

        result = conn.execute(text("""
            SELECT DISTINCT project_name, COUNT(*) as count
            FROM activity_records
            WHERE LOWER(project_name) LIKE LOWER(:pattern)
            GROUP BY project_name
            ORDER BY count DESC
        """), {"pattern": "%mahindra%manulife%"})

        similar = result.fetchall()
        if similar:
            print(f"Found {len(similar)} similar project names:\n")
            for row in similar:
                print(f"  - '{row[0]}' ({row[1]} records)")
        else:
            print("No similar project names found")

    else:
        print(f"[OK] Found {count} records for '{project_name}'\n")

        # Get detailed breakdown
        print("2. Category breakdown:")
        print("-" * 80)

        result = conn.execute(text("""
            SELECT
                category,
                COUNT(*) as record_count,
                SUM(duration) / 3600.0 as total_hours,
                MIN(timestamp) as first_activity,
                MAX(timestamp) as last_activity
            FROM activity_records
            WHERE project_name = :project_name
            GROUP BY category
            ORDER BY total_hours DESC
        """), {"project_name": project_name})

        for row in result:
            cat, count, hours, first, last = row
            print(f"\nCategory: {cat or 'NULL'}")
            print(f"  Records: {count}")
            print(f"  Total Hours: {hours:.2f}h")
            print(f"  First Activity: {first}")
            print(f"  Last Activity: {last}")

        # Get sample activities
        print("\n3. Sample activities (last 10):")
        print("-" * 80)

        result = conn.execute(text("""
            SELECT
                timestamp,
                category,
                application_name,
                window_title,
                duration / 60.0 as minutes
            FROM activity_records
            WHERE project_name = :project_name
            ORDER BY timestamp DESC
            LIMIT 10
        """), {"project_name": project_name})

        for row in result:
            ts, cat, app, title, mins = row
            print(f"{ts} | {cat or 'NULL':<12} | {app or 'N/A':<20} | {mins:.2f}m")
            print(f"  Title: {title[:100]}")

        # Check developer breakdown
        print("\n4. Developer breakdown:")
        print("-" * 80)

        result = conn.execute(text("""
            SELECT
                ar.developer_id,
                d.name as developer_name,
                COUNT(*) as activity_count,
                SUM(ar.duration) / 3600.0 as total_hours
            FROM activity_records ar
            LEFT JOIN developers d ON ar.developer_id = d.developer_id
            WHERE ar.project_name = :project_name
            GROUP BY ar.developer_id, d.name
            ORDER BY total_hours DESC
        """), {"project_name": project_name})

        for row in result:
            dev_id, dev_name, count, hours = row
            print(f"Developer: {dev_name or 'Unknown'} (ID: {dev_id})")
            print(f"  Activities: {count}")
            print(f"  Hours: {hours:.2f}h\n")

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