<?php
class SupervisorController {

    // GET /supervisor/resumen — Dashboard ejecutivo
    public static function resumen(): void {
        $payload = AuthMiddleware::verificar();
        RoleMiddleware::requireSupervisor($payload);
        $db = getDB();
        $r = [];

        // KPIs generales
        $r['tickets_abiertos'] = (int)$db->query("SELECT COUNT(*) FROM tickets WHERE estado IN ('abierto','en_progreso','pendiente')")->fetchColumn();
        $r['tickets_hoy'] = (int)$db->query("SELECT COUNT(*) FROM tickets WHERE DATE(created_at) = CURDATE()")->fetchColumn();
        $r['tickets_semana'] = (int)$db->query("SELECT COUNT(*) FROM tickets WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)")->fetchColumn();
        $r['tickets_mes'] = (int)$db->query("SELECT COUNT(*) FROM tickets WHERE YEAR(created_at)=YEAR(NOW()) AND MONTH(created_at)=MONTH(NOW())")->fetchColumn();

        // SLA
        $sla = $db->query("SELECT 
            COUNT(*) as total,
            SUM(CASE WHEN resuelto_at <= fecha_vencimiento THEN 1 ELSE 0 END) as dentro_sla
            FROM tickets WHERE resuelto_at IS NOT NULL AND fecha_vencimiento IS NOT NULL 
            AND resuelto_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)")->fetch();
        $r['sla_cumplimiento'] = $sla['total'] > 0 ? round(($sla['dentro_sla'] / $sla['total']) * 100, 1) : 100;

        // Vencidos ahora
        $r['tickets_vencidos'] = (int)$db->query("SELECT COUNT(*) FROM tickets WHERE estado IN ('abierto','en_progreso','pendiente') AND fecha_vencimiento < NOW()")->fetchColumn();

        // Por vencer en 2 horas
        $r['por_vencer_2h'] = (int)$db->query("SELECT COUNT(*) FROM tickets WHERE estado IN ('abierto','en_progreso','pendiente') AND fecha_vencimiento BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 2 HOUR)")->fetchColumn();

        // CSAT
        $csat = $db->query("SELECT AVG(calificacion) as promedio, COUNT(*) as total 
            FROM tickets WHERE calificacion IS NOT NULL AND resuelto_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)")->fetch();
        $r['csat_promedio'] = $csat['promedio'] ? round((float)$csat['promedio'], 1) : null;
        $r['csat_total'] = (int)$csat['total'];

        // Resolución promedio
        $r['resolucion_promedio_h'] = round((float)$db->query("SELECT AVG(TIMESTAMPDIFF(HOUR, created_at, resuelto_at)) FROM tickets WHERE resuelto_at IS NOT NULL AND resuelto_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)")->fetchColumn(), 1);

        // Reaperturas
        $r['tickets_reabiertos'] = (int)$db->query("SELECT COUNT(*) FROM tickets WHERE reaberturas > 0 AND updated_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)")->fetchColumn();

        Response::success($r);
    }

    // GET /supervisor/carga-tecnicos — carga de trabajo por técnico
    public static function cargaTecnicos(): void {
        $payload = AuthMiddleware::verificar();
        RoleMiddleware::requireSupervisor($payload);
        $db = getDB();

        $stmt = $db->query("SELECT u.id, u.nombre_completo, u.rol, u.email,
            COUNT(CASE WHEN t.estado IN ('abierto','en_progreso','pendiente') THEN 1 END) as tickets_abiertos,
            COUNT(CASE WHEN t.estado IN ('resuelto','cerrado') AND t.resuelto_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as resueltos_mes,
            COUNT(CASE WHEN t.estado IN ('abierto','en_progreso','pendiente') AND t.fecha_vencimiento < NOW() THEN 1 END) as vencidos,
            AVG(CASE WHEN t.resuelto_at IS NOT NULL THEN TIMESTAMPDIFF(HOUR, t.created_at, t.resuelto_at) END) as promedio_resolucion_h,
            AVG(t.calificacion) as csat
            FROM usuarios u 
            LEFT JOIN tickets t ON u.id = t.tecnico_asignado_id
            WHERE u.rol IN ('tecnico','tecnico_n2','admin') AND u.activo = 1
            GROUP BY u.id ORDER BY tickets_abiertos DESC");
        Response::success($stmt->fetchAll());
    }

    // GET /supervisor/usuarios-departamento — actividad por departamento/usuario
    public static function usuariosDepartamento(): void {
        $payload = AuthMiddleware::verificar();
        RoleMiddleware::requireSupervisor($payload);
        $db = getDB();

        $stmt = $db->query("SELECT u.id, u.nombre_completo, u.email, u.departamento, u.ultimo_login,
            COUNT(t.id) as total_tickets,
            COUNT(CASE WHEN t.estado IN ('abierto','en_progreso','pendiente') THEN 1 END) as tickets_abiertos,
            COUNT(CASE WHEN t.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as tickets_mes,
            (SELECT COUNT(*) FROM activos a WHERE a.usuario_asignado_id = u.id) as equipos_asignados
            FROM usuarios u 
            LEFT JOIN tickets t ON u.id = t.solicitante_id
            WHERE u.rol = 'usuario' AND u.activo = 1
            GROUP BY u.id ORDER BY total_tickets DESC");
        Response::success($stmt->fetchAll());
    }

    // GET /supervisor/sla-tendencia — tendencia SLA mensual
    public static function slaTendencia(): void {
        $payload = AuthMiddleware::verificar();
        RoleMiddleware::requireSupervisor($payload);
        $db = getDB();

        $stmt = $db->query("SELECT 
            DATE_FORMAT(resuelto_at, '%Y-%m') as mes,
            COUNT(*) as total,
            SUM(CASE WHEN resuelto_at <= fecha_vencimiento THEN 1 ELSE 0 END) as dentro_sla,
            ROUND(AVG(TIMESTAMPDIFF(HOUR, created_at, resuelto_at)), 1) as promedio_h,
            ROUND(AVG(calificacion), 1) as csat
            FROM tickets 
            WHERE resuelto_at IS NOT NULL AND fecha_vencimiento IS NOT NULL
            AND resuelto_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
            GROUP BY DATE_FORMAT(resuelto_at, '%Y-%m') ORDER BY mes");
        Response::success($stmt->fetchAll());
    }

    // GET /supervisor/top-solicitantes — quién pide más soporte
    public static function topSolicitantes(): void {
        $payload = AuthMiddleware::verificar();
        RoleMiddleware::requireSupervisor($payload);
        $db = getDB();

        $stmt = $db->query("SELECT u.nombre_completo, u.email, u.departamento,
            COUNT(t.id) as total_tickets,
            COUNT(CASE WHEN t.prioridad IN ('urgente','critica') THEN 1 END) as tickets_urgentes,
            COUNT(CASE WHEN t.reaberturas > 0 THEN 1 END) as tickets_reabiertos
            FROM tickets t JOIN usuarios u ON t.solicitante_id = u.id
            WHERE t.created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY)
            GROUP BY u.id ORDER BY total_tickets DESC LIMIT 20");
        Response::success($stmt->fetchAll());
    }
}
