# Check actual time for Laravel Download Routes Cleanup 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 = "Laravel Download Routes Cleanup"

print(f"\n=== TIME ANALYSIS FOR: {project_name} ===\n")

with engine.connect() as conn:
    # Check time by category
    print("Time breakdown by category:")
    print("-" * 80)

    result = conn.execute(text("""
        SELECT
            category,
            COUNT(*) as activity_count,
            SUM(duration) as total_duration_seconds,
            SUM(duration) / 3600.0 as total_hours,
            SUM(duration) / 60.0 as total_minutes
        FROM activity_records
        WHERE project_name = :project_name
        GROUP BY category
        ORDER BY total_hours DESC
    """), {"project_name": project_name})

    total_seconds = 0
    for row in result:
        category, count, duration_sec, hours, minutes = row
        total_seconds += duration_sec if duration_sec else 0
        print(f"Category: {category or 'NULL':<15} | Activities: {count:<5} | Time: {hours:.4f}h ({minutes:.2f}m)")

    # Check ALL time for this project
    print("\n" + "=" * 80)
    print("TOTAL TIME (all categories):")
    print("-" * 80)

    result = conn.execute(text("""
        SELECT
            COUNT(*) as total_activities,
            SUM(duration) as total_duration_seconds,
            SUM(duration) / 3600.0 as total_hours,
            SUM(duration) / 60.0 as total_minutes,
            MIN(timestamp) as first_activity,
            MAX(timestamp) as last_activity
        FROM activity_records
        WHERE project_name = :project_name
    """), {"project_name": project_name})

    row = result.fetchone()
    if row:
        count, duration_sec, hours, minutes, first, last = row
        print(f"Total Activities: {count}")
        print(f"Total Duration: {duration_sec} seconds")
        print(f"Total Hours: {hours:.4f}h")
        print(f"Total Minutes: {minutes:.2f}m")
        print(f"First Activity: {first}")
        print(f"Last Activity: {last}")

    # Show sample activities
    print("\n" + "=" * 80)
    print("Sample activities (last 10):")
    print("-" * 80)

    result = conn.execute(text("""
        SELECT
            timestamp,
            category,
            application_name,
            window_title,
            duration,
            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, dur, mins = row
        print(f"{ts} | {cat or 'NULL':<12} | {app:<20} | {mins:.2f}m | {title[:50]}")