from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.future import select
from sqlalchemy import func, or_
from datetime import datetime, timezone
from typing import Optional

from database import get_db
from models import User, Project, Task, Ticket, KanbanColumn
from auth import get_current_active_user, require_metrics_access

router = APIRouter(prefix="/api/v1/dashboard", tags=["dashboard"])

@router.get("/metrics")
async def get_dashboard_metrics(
    project_id: Optional[int] = None,
    db: AsyncSession = Depends(get_db),
    current_user: User = Depends(require_metrics_access)
):
    is_superadmin = any(role.name == "Super Administrador" for role in current_user.roles)
    company_id = current_user.company_id

    # 1. Tasks by Status
    tasks_query = select(KanbanColumn.name, func.count(Task.id)).select_from(Task).join(Project, Task.project_id == Project.id).outerjoin(KanbanColumn, Task.column_id == KanbanColumn.id)
    if project_id is not None:
        tasks_query = tasks_query.where(Task.project_id == project_id)
    if not is_superadmin:
        tasks_query = tasks_query.where(Project.company_id == company_id)
    tasks_query = tasks_query.group_by(KanbanColumn.name)
    
    tasks_result = await db.execute(tasks_query)
    tasks_by_status = {status or 'Sin Estado': count for status, count in tasks_result.all()}

    # 2. Overdue Tasks
    today = datetime.now(timezone.utc).replace(tzinfo=None)
    overdue_query = select(func.count(Task.id)).select_from(Task).join(Project, Task.project_id == Project.id).where(
        Task.due_date < today,
        Task.completed_at.is_(None)
    )
    if project_id is not None:
        overdue_query = overdue_query.where(Task.project_id == project_id)
    if not is_superadmin:
        overdue_query = overdue_query.where(Project.company_id == company_id)
        
    overdue_result = await db.execute(overdue_query)
    overdue_tasks = overdue_result.scalar() or 0

    # 3. Tickets by Status
    tickets_query = select(Ticket.status, func.count(Ticket.id)).select_from(Ticket)
    if project_id is not None:
        tickets_query = tickets_query.outerjoin(Task, Ticket.linked_task_id == Task.id).where(
            or_(
                Ticket.linked_project_id == project_id,
                Task.project_id == project_id
            )
        )
    if not is_superadmin:
        tickets_query = tickets_query.where(Ticket.company_id == company_id)
    tickets_query = tickets_query.group_by(Ticket.status)
    
    tickets_result = await db.execute(tickets_query)
    tickets_by_status = {status: count for status, count in tickets_result.all()}

    # 4. Total Projects
    if project_id is not None:
        projects_query = select(func.count(Project.id)).where(Project.id == project_id)
    else:
        projects_query = select(func.count(Project.id))
    if not is_superadmin:
        projects_query = projects_query.where(Project.company_id == company_id)
        
    projects_result = await db.execute(projects_query)
    total_projects = projects_result.scalar() or 0

    return {
        "tasks_by_status": tasks_by_status,
        "overdue_tasks": overdue_tasks,
        "tickets_by_status": tickets_by_status,
        "total_projects": total_projects
    }