"""
Fix Gmail/YouTube categorization - Works with any database configuration
Handles both DATABASE_URL and individual DB parameters from .env
"""

import os
import sys
from sqlalchemy import create_engine, text
from dotenv import load_dotenv

# Load environment variables
load_dotenv()

def get_database_url():
    """Get database URL from various possible configurations"""
    
    # Try DATABASE_URL first
    database_url = os.getenv("DATABASE_URL")
    if database_url:
        return database_url
    
    # Build from individual parameters
    db_host = os.getenv("DB_HOST", "localhost")
    db_port = os.getenv("DB_PORT", "5432")
    db_name = os.getenv("DB_NAME", "timesheet_db")
    db_user = os.getenv("DB_USER", "postgres")
    db_password = os.getenv("DB_PASSWORD", "")
    
    # If no password in env, try common defaults
    if not db_password:
        print("⚠️  No DB_PASSWORD found in .env file.")
        print("   Trying common defaults...")
        db_password = "asdf1234"  # Common password from your setup
    
    return f"postgresql://{db_user}:{db_password}@{db_host}:{db_port}/{db_name}"

def main():
    print("🔧 Gmail/YouTube Category Fixer")
    print("=" * 50)
    
    database_url = get_database_url()
    
    # Hide password in output
    display_url = database_url.replace(
        database_url.split('@')[0].split('://')[-1], 
        "****:****"
    ) if '@' in database_url else database_url
    
    print(f"\n📊 Connecting to: {display_url}")
    
    try:
        engine = create_engine(database_url)
        
        with engine.connect() as conn:
            # Test connection
            conn.execute(text("SELECT 1"))
            print("✅ Database connected successfully!")
            
            # Check for problems
            print("\n🔍 Checking for misplaced activities...")
            
            problems = []
            
            # Check Gmail
            result = conn.execute(text("""
                SELECT COUNT(*) as cnt, COALESCE(SUM(duration)/3600.0, 0) as hrs
                FROM activity_records
                WHERE category = 'productive'
                AND (LOWER(window_title) LIKE '%gmail%' OR LOWER(url) LIKE '%mail.google.com%')
            """)).fetchone()
            if result.cnt > 0:
                problems.append(f"Gmail: {result.cnt} activities ({result.hrs:.1f} hours)")
            
            # Check YouTube
            result = conn.execute(text("""
                SELECT COUNT(*) as cnt, COALESCE(SUM(duration)/3600.0, 0) as hrs
                FROM activity_records
                WHERE category = 'productive'
                AND (LOWER(window_title) LIKE '%youtube%' OR LOWER(url) LIKE '%youtube.com%')
            """)).fetchone()
            if result.cnt > 0:
                problems.append(f"YouTube: {result.cnt} activities ({result.hrs:.1f} hours)")
            
            # Check Social Media
            result = conn.execute(text("""
                SELECT COUNT(*) as cnt, COALESCE(SUM(duration)/3600.0, 0) as hrs
                FROM activity_records
                WHERE category = 'productive'
                AND (
                    LOWER(window_title) LIKE '%facebook%'
                    OR LOWER(window_title) LIKE '%instagram%'
                    OR LOWER(window_title) LIKE '%reddit%'
                )
            """)).fetchone()
            if result.cnt > 0:
                problems.append(f"Social Media: {result.cnt} activities ({result.hrs:.1f} hours)")
            
            if not problems:
                print("✅ No problems found! Your categories are correct.")
                return
            
            print("\n❌ Found misplaced activities:")
            for problem in problems:
                print(f"   - {problem}")
            
            # Ask for confirmation
            response = input("\n🤔 Fix these? (y/n): ").lower()
            if response != 'y':
                print("Cancelled.")
                return
            
            print("\n🚀 Fixing categories...")
            
            # Run all fixes
            fixes = [
                ("Gmail → Browser", """
                    UPDATE activity_records
                    SET category = 'browser', subcategory = 'email'
                    WHERE category = 'productive'
                    AND (LOWER(window_title) LIKE '%gmail%' OR LOWER(url) LIKE '%mail.google.com%')
                """),
                ("YouTube → Browser", """
                    UPDATE activity_records
                    SET category = 'browser', subcategory = 'entertainment'
                    WHERE category = 'productive'
                    AND (LOWER(window_title) LIKE '%youtube%' OR LOWER(url) LIKE '%youtube.com%' OR LOWER(url) LIKE '%youtu.be%')
                """),
                ("Social Media → Browser", """
                    UPDATE activity_records
                    SET category = 'browser', subcategory = 'social-media'
                    WHERE category = 'productive'
                    AND (
                        LOWER(window_title) LIKE '%facebook%'
                        OR LOWER(window_title) LIKE '%instagram%'
                        OR LOWER(window_title) LIKE '%twitter%'
                        OR LOWER(window_title) LIKE '%reddit%'
                    )
                """),
                ("Entertainment → Browser", """
                    UPDATE activity_records
                    SET category = 'browser', subcategory = 'entertainment'
                    WHERE category = 'productive'
                    AND (
                        LOWER(window_title) LIKE '%netflix%'
                        OR LOWER(window_title) LIKE '%spotify%'
                        OR LOWER(window_title) LIKE '%twitch%'
                    )
                """),
            ]
            
            total_fixed = 0
            for name, query in fixes:
                result = conn.execute(text(query))
                if result.rowcount > 0:
                    print(f"   ✅ {name}: {result.rowcount} activities")
                    total_fixed += result.rowcount
            
            conn.commit()
            print(f"\n✨ Fixed {total_fixed} activities total!")
            
            # Show results
            print("\n📊 New distribution:")
            result = conn.execute(text("""
                SELECT 
                    category,
                    COUNT(*) as cnt,
                    ROUND(SUM(duration)/3600.0, 1) as hrs
                FROM activity_records
                WHERE timestamp >= CURRENT_DATE - INTERVAL '7 days'
                GROUP BY category
                ORDER BY hrs DESC
            """))
            
            for row in result:
                emoji = {'productive': '💻', 'browser': '🌐', 'server': '☁️', 'non-work': '🎮'}.get(row.category, '❓')
                print(f"   {emoji} {row.category}: {row.hrs}h ({row.cnt} activities)")
            
            print("\n🎉 Success! Restart your dashboard to see the changes.")
            
    except Exception as e:
        print(f"\n❌ Connection failed: {str(e)}")
        print("\n🔧 Troubleshooting tips:")
        print("1. Check PostgreSQL is running: sudo service postgresql status")
        print("2. Check your .env file has correct DB settings")
        print("3. Try running: psql -U postgres -d timesheet_db")
        print("4. Common passwords: asdf1234, your_password, postgres")
        print("\n📝 Your .env should contain either:")
        print("   DATABASE_URL=postgresql://user:password@localhost:5432/timesheet")
        print("   OR individual settings:")
        print("   DB_USER=postgres")
        print("   DB_PASSWORD=your_password")
        print("   DB_NAME=timesheet_db")

if __name__ == "__main__":
    main()