# Diagnostic script to check actual category values in database
from sqlalchemy import create_engine, text
import os
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

def check_categories():
    """Check what categories exist in the database and show sample projects"""
    try:
        DATABASE_URL = os.getenv("DATABASE_URL", "postgresql://postgres:asdf1234@localhost:5432/timesheet")
        engine = create_engine(DATABASE_URL)

        with engine.connect() as conn:
            # Check all unique categories
            print("\n=== ALL UNIQUE CATEGORIES IN DATABASE ===")
            result = conn.execute(text("""
                SELECT DISTINCT category, COUNT(*) as count
                FROM activity_records
                WHERE category IS NOT NULL
                GROUP BY category
                ORDER BY count DESC
            """))

            for row in result:
                print(f"Category: '{row[0]}' | Count: {row[1]}")

            # Check projects by category
            print("\n=== PROJECTS BY CATEGORY (Top 10 each) ===")
            categories = ['productive', 'browser', 'server', 'non-work']

            for cat in categories:
                print(f"\n--- Category: {cat} ---")
                result = conn.execute(text("""
                    SELECT
                        project_name,
                        COUNT(*) as activity_count,
                        SUM(duration) / 3600.0 as total_hours
                    FROM activity_records
                    WHERE category = :category
                    AND project_name IS NOT NULL
                    AND project_name != ''
                    GROUP BY project_name
                    ORDER BY total_hours DESC
                    LIMIT 10
                """), {"category": cat})

                for row in result:
                    print(f"  {row[0][:60]:60} | Activities: {row[1]:5} | Hours: {row[2]:.2f}")

            # Check for file extensions in project names
            print("\n=== PROJECTS WITH FILE EXTENSIONS ===")
            extensions = ['.js', '.ts', '.py', '.css', '.html', '.json', '.jsx',
                         '.tsx', '.php', '.java', '.xml', '.md', '.txt', '.vue']

            for ext in extensions:
                result = conn.execute(text("""
                    SELECT COUNT(DISTINCT project_name) as count
                    FROM activity_records
                    WHERE project_name LIKE :pattern
                    AND project_name IS NOT NULL
                """), {"pattern": f"%{ext}"})

                count = result.fetchone()[0]
                if count > 0:
                    print(f"  Projects ending with {ext}: {count}")

                    # Show sample
                    result = conn.execute(text("""
                        SELECT DISTINCT project_name
                        FROM activity_records
                        WHERE project_name LIKE :pattern
                        AND project_name IS NOT NULL
                        LIMIT 5
                    """), {"pattern": f"%{ext}"})

                    for row in result:
                        print(f"    - {row[0]}")

    except Exception as e:
        logger.error(f"Error checking categories: {e}")
        raise

if __name__ == "__main__":
    check_categories()