# temporary_diagnostic_endpoint.py
# Add this temporarily to your FastAPI app to run diagnostics

from fastapi import APIRouter, Depends
from sqlalchemy.orm import Session
from sqlalchemy import text
from database import get_db

router = APIRouter()

@router.get("/api/run-diagnostics/{developer_id}")
async def run_diagnostics(
    developer_id: str,
    db: Session = Depends(get_db)
):
    """Run diagnostic queries to understand the data"""
    
    diagnostics = {}
    
    # 1. Applications being tracked
    query1 = text("""
        SELECT 
            application_name,
            COUNT(*) as count,
            SUM(duration)/1000/60 as total_minutes
        FROM activity_records
        WHERE developer_id = :dev_id
        AND DATE(timestamp) = CURRENT_DATE
        GROUP BY application_name
        ORDER BY total_minutes DESC
        LIMIT 20
    """)
    
    result1 = db.execute(query1, {"dev_id": developer_id}).fetchall()
    diagnostics['applications'] = [
        {
            "application": row[0] or "Unknown",
            "count": row[1],
            "minutes": round(row[2] or 0, 2)
        } for row in result1
    ]
    
    # 2. Current project names
    query2 = text("""
        SELECT 
            project_name,
            COUNT(*) as count,
            SUM(duration)/1000/60 as total_minutes
        FROM activity_records
        WHERE developer_id = :dev_id
        AND DATE(timestamp) = CURRENT_DATE
        GROUP BY project_name
        ORDER BY count DESC
        LIMIT 20
    """)
    
    result2 = db.execute(query2, {"dev_id": developer_id}).fetchall()
    diagnostics['current_projects'] = [
        {
            "project": row[0] or "NULL",
            "count": row[1],
            "minutes": round(row[2] or 0, 2)
        } for row in result2
    ]
    
    # 3. Sample code editor activities
    query3 = text("""
        SELECT 
            window_title,
            application_name,
            project_name,
            duration/1000/60 as minutes
        FROM activity_records
        WHERE developer_id = :dev_id
        AND DATE(timestamp) = CURRENT_DATE
        AND LOWER(application_name) IN (
            'code.exe', 'code', 'visual studio code',
            'cursor.exe', 'cursor',
            'notepad++.exe', 'notepad++',
            'sublime_text.exe', 'sublime text'
        )
        ORDER BY duration DESC
        LIMIT 10
    """)
    
    result3 = db.execute(query3, {"dev_id": developer_id}).fetchall()
    diagnostics['code_editor_samples'] = [
        {
            "window_title": row[0] or "",
            "application": row[1] or "",
            "project": row[2] or "NULL",
            "minutes": round(row[3] or 0, 2)
        } for row in result3
    ]
    
    # 4. What's in "general" project
    query4 = text("""
        SELECT 
            window_title,
            application_name,
            category,
            COUNT(*) as count
        FROM activity_records
        WHERE developer_id = :dev_id
        AND DATE(timestamp) = CURRENT_DATE
        AND project_name = 'general'
        GROUP BY window_title, application_name, category
        ORDER BY count DESC
        LIMIT 10
    """)
    
    result4 = db.execute(query4, {"dev_id": developer_id}).fetchall()
    diagnostics['general_project_items'] = [
        {
            "window_title": row[0] or "",
            "application": row[1] or "",
            "category": row[2] or "",
            "count": row[3]
        } for row in result4
    ]
    
    # 5. Email/browser activities
    query5 = text("""
        SELECT 
            window_title,
            application_name,
            project_name,
            COUNT(*) as count
        FROM activity_records
        WHERE developer_id = :dev_id
        AND DATE(timestamp) = CURRENT_DATE
        AND (
            LOWER(window_title) LIKE '%inbox%' OR
            LOWER(window_title) LIKE '%gmail%' OR
            LOWER(window_title) LIKE '%mail%' OR
            LOWER(window_title) LIKE '%http%'
        )
        GROUP BY window_title, application_name, project_name
        ORDER BY count DESC
        LIMIT 10
    """)
    
    result5 = db.execute(query5, {"dev_id": developer_id}).fetchall()
    diagnostics['email_browser_activities'] = [
        {
            "window_title": row[0] or "",
            "application": row[1] or "",
            "project": row[2] or "NULL",
            "count": row[3]
        } for row in result5
    ]
    
    return diagnostics

# Add this to your main.py:
# from temporary_diagnostic_endpoint import router as diagnostic_router
# app.include_router(diagnostic_router)