"""
Direct fix for the ROUND error - uses simple queries only
"""
import psycopg2
import os
from dotenv import load_dotenv

load_dotenv()

# Try different connection methods
connection_configs = [
    # From .env DATABASE_URL
    lambda: psycopg2.connect(os.getenv("DATABASE_URL", "")),
    
    # Build from parts with common password
    lambda: psycopg2.connect(
        host="localhost",
        database="timesheet",
        user="timesheet_user", 
        password="asdf1234"
    ),
    
    # Try postgres user
    lambda: psycopg2.connect(
        host="localhost",
        database="timesheet",
        user="postgres",
        password="asdf1234"
    ),
    
    # From individual env vars
    lambda: psycopg2.connect(
        host=os.getenv("DB_HOST", "localhost"),
        database=os.getenv("DB_NAME", "timesheet"),
        user=os.getenv("DB_USER", "postgres"),
        password=os.getenv("DB_PASSWORD", "asdf1234")
    ),
]

print("🔧 Fixing Gmail/YouTube categories (Simple Version)")
print("=" * 50)

conn = None
for i, config in enumerate(connection_configs):
    try:
        print(f"\nTrying connection method {i+1}...")
        conn = config()
        print("✅ Connected successfully!")
        break
    except Exception as e:
        print(f"❌ Failed: {str(e)[:50]}...")
        continue

if not conn:
    print("\n❌ Could not connect to database!")
    print("\nPlease run this command manually:")
    print('psql -U postgres -d timesheet -h localhost -c "UPDATE activity_records SET category=\'browser\' WHERE category=\'productive\' AND (LOWER(window_title) LIKE \'%gmail%\' OR LOWER(window_title) LIKE \'%youtube%\')"')
    exit(1)

try:
    cur = conn.cursor()
    
    # Simple updates without any complex functions
    updates = [
        ("Gmail", "UPDATE activity_records SET category='browser' WHERE category='productive' AND (LOWER(window_title) LIKE '%gmail%' OR LOWER(url) LIKE '%mail.google.com%')"),
        ("YouTube", "UPDATE activity_records SET category='browser' WHERE category='productive' AND (LOWER(window_title) LIKE '%youtube%' OR LOWER(url) LIKE '%youtube.com%' OR LOWER(url) LIKE '%youtu.be%')"),
        ("Facebook", "UPDATE activity_records SET category='browser' WHERE category='productive' AND LOWER(window_title) LIKE '%facebook%'"),
        ("Instagram", "UPDATE activity_records SET category='browser' WHERE category='productive' AND LOWER(window_title) LIKE '%instagram%'"),
        ("Reddit", "UPDATE activity_records SET category='browser' WHERE category='productive' AND LOWER(window_title) LIKE '%reddit%'"),
        ("Netflix", "UPDATE activity_records SET category='browser' WHERE category='productive' AND LOWER(window_title) LIKE '%netflix%'"),
        ("Spotify", "UPDATE activity_records SET category='browser' WHERE category='productive' AND LOWER(window_title) LIKE '%spotify%'"),
    ]
    
    print("\n🚀 Running fixes...")
    total_fixed = 0
    
    for name, query in updates:
        cur.execute(query)
        count = cur.rowcount
        if count > 0:
            print(f"   ✅ {name}: {count} activities moved to browser")
            total_fixed += count
    
    conn.commit()
    
    if total_fixed == 0:
        print("\n✅ No fixes needed! Categories are already correct.")
    else:
        print(f"\n✨ Total fixed: {total_fixed} activities")
        
    # Simple count check
    print("\n📊 Current category counts:")
    cur.execute("SELECT category, COUNT(*) FROM activity_records WHERE timestamp > CURRENT_DATE - INTERVAL '7 days' GROUP BY category ORDER BY count DESC")
    for row in cur.fetchall():
        print(f"   {row[0]}: {row[1]} activities")
    
    print("\n🎉 Done! Restart your dashboard to see the changes.")
    
except Exception as e:
    print(f"\n❌ Error during update: {e}")
finally:
    if conn:
        conn.close()