"""
Check what's in the productive category that shouldn't be
"""
import psycopg2
import os
from dotenv import load_dotenv

load_dotenv()

def get_connection():
    attempts = [
        lambda: psycopg2.connect(os.getenv("DATABASE_URL", "")),
        lambda: psycopg2.connect(host="localhost", database="timesheet", user="postgres", password="asdf1234"),
        lambda: psycopg2.connect(host="localhost", database="timesheet", user="timesheet_user", password="asdf1234"),
    ]
    
    for attempt in attempts:
        try:
            return attempt()
        except:
            continue
    return None

print("🔍 Checking What's in Productive Category")
print("=" * 50)

conn = get_connection()
if not conn:
    print("❌ Could not connect to database!")
    exit(1)

try:
    cur = conn.cursor()
    
    # Check productive category activities from today
    print("\n📊 Activities in PRODUCTIVE category (grouped by window_title):")
    print("Looking for items that should be in BROWSER category...\n")
    
    cur.execute("""
        SELECT 
            window_title,
            application_name,
            COUNT(*) as activity_count,
            SUM(duration)/3600.0 as hours
        FROM activity_records
        WHERE category = 'productive'
        AND timestamp >= CURRENT_DATE - INTERVAL '7 days'
        GROUP BY window_title, application_name
        ORDER BY hours DESC
        LIMIT 30
    """)
    
    suspicious_items = []
    
    print("Top activities in PRODUCTIVE category:")
    print("-" * 80)
    
    for row in cur.fetchall():
        title = row[0] or "Unknown"
        app = row[1] or "Unknown"
        count = row[2]
        hours = row[3] or 0
        
        # Check if this should be in browser
        title_lower = title.lower()
        suspicious = False
        
        if any(word in title_lower for word in [
            'mail', 'gmail', 'outlook', 'inbox', 'email',
            'youtube', 'facebook', 'instagram', 'twitter', 'reddit',
            'netflix', 'spotify', 'amazon', 'shopping',
            'news', 'weather', 'search'
        ]):
            suspicious = True
            suspicious_items.append((title, app, count, hours))
            print(f"❌ {title[:60]:<60} | {app:<15} | {hours:>6.1f}h")
        else:
            print(f"✅ {title[:60]:<60} | {app:<15} | {hours:>6.1f}h")
    
    if suspicious_items:
        print(f"\n⚠️  Found {len(suspicious_items)} items that should probably be in BROWSER category:")
        for title, app, count, hours in suspicious_items:
            print(f"   - {title} ({count} activities, {hours:.1f} hours)")
        
        print("\n💡 To fix these, run: python fix_all_mail_activities.py")
    else:
        print("\n✅ All items in productive category look correct!")
    
    # Specifically check for First Economy Mail
    print("\n🔍 Checking 'First Economy Mail' specifically:")
    cur.execute("""
        SELECT category, COUNT(*), SUM(duration)/3600.0 as hours
        FROM activity_records
        WHERE LOWER(window_title) LIKE '%first economy mail%'
        GROUP BY category
    """)
    
    results = cur.fetchall()
    if results:
        for cat, count, hours in results:
            print(f"   Category: {cat}, Activities: {count}, Hours: {hours:.1f}")
    else:
        print("   Not found in database")

except Exception as e:
    print(f"\n❌ Error: {e}")
finally:
    if conn:
        conn.close()