<?php
class ReporteController {

    /**
     * GET /reportes/resumen — KPIs principales
     */
    public static function resumen(): void {
        $payload = AuthMiddleware::verificar();
        RoleMiddleware::requireTecnico($payload);
        $db = getDB();

        $desde = $_GET['desde'] ?? date('Y-m-01');
        $hasta = $_GET['hasta'] ?? date('Y-m-d');

        $r = [];

        // Tickets en período
        $stmt = $db->prepare("SELECT COUNT(*) FROM tickets WHERE DATE(created_at) BETWEEN ? AND ?");
        $stmt->execute([$desde, $hasta]);
        $r['tickets_creados'] = (int)$stmt->fetchColumn();

        $stmt = $db->prepare("SELECT COUNT(*) FROM tickets WHERE DATE(resuelto_at) BETWEEN ? AND ?");
        $stmt->execute([$desde, $hasta]);
        $r['tickets_resueltos'] = (int)$stmt->fetchColumn();

        $stmt = $db->prepare("SELECT COUNT(*) FROM tickets WHERE estado IN ('abierto','en_progreso','pendiente')");
        $stmt->execute();
        $r['tickets_pendientes'] = (int)$stmt->fetchColumn();

        // Promedio resolución (horas)
        $stmt = $db->prepare("SELECT AVG(TIMESTAMPDIFF(HOUR, created_at, resuelto_at)) FROM tickets WHERE resuelto_at IS NOT NULL AND DATE(resuelto_at) BETWEEN ? AND ?");
        $stmt->execute([$desde, $hasta]);
        $r['promedio_resolucion_h'] = round((float)$stmt->fetchColumn(), 1);

        // SLA compliance
        $stmt = $db->prepare("SELECT 
            COUNT(*) as total,
            SUM(CASE WHEN resuelto_at IS NOT NULL AND 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 DATE(resuelto_at) BETWEEN ? AND ?");
        $stmt->execute([$desde, $hasta]);
        $sla = $stmt->fetch();
        $r['sla_cumplimiento_pct'] = $sla['total'] > 0 ? round(($sla['dentro_sla'] / $sla['total']) * 100, 1) : 100;

        // Primera respuesta promedio (horas)
        $stmt = $db->prepare("SELECT AVG(TIMESTAMPDIFF(HOUR, created_at, primera_respuesta_at)) FROM tickets WHERE primera_respuesta_at IS NOT NULL AND DATE(created_at) BETWEEN ? AND ?");
        $stmt->execute([$desde, $hasta]);
        $r['promedio_primera_respuesta_h'] = round((float)$stmt->fetchColumn(), 1);

        // Satisfacción promedio
        $stmt = $db->prepare("SELECT AVG(calificacion), COUNT(calificacion) FROM tickets WHERE calificacion IS NOT NULL AND DATE(resuelto_at) BETWEEN ? AND ?");
        $stmt->execute([$desde, $hasta]);
        $row = $stmt->fetch(PDO::FETCH_NUM);
        $r['satisfaccion_promedio'] = $row[0] ? round((float)$row[0], 1) : null;
        $r['total_calificaciones'] = (int)$row[1];

        // Activos
        $r['total_activos'] = (int)$db->query("SELECT COUNT(*) FROM activos")->fetchColumn();
        $r['activos_activos'] = (int)$db->query("SELECT COUNT(*) FROM activos WHERE estado = 'activo'")->fetchColumn();
        $r['alertas_activas'] = (int)$db->query("SELECT COUNT(*) FROM alertas WHERE resuelta = 0")->fetchColumn();
        $r['mantenimientos_pendientes'] = (int)$db->query("SELECT COUNT(*) FROM mantenimientos WHERE estado = 'programado'")->fetchColumn();

        Response::success($r);
    }

    /**
     * GET /reportes/tickets-por-dia — Tendencia diaria
     */
    public static function ticketsPorDia(): void {
        $payload = AuthMiddleware::verificar();
        RoleMiddleware::requireTecnico($payload);
        $db = getDB();
        $dias = min(90, max(7, (int)($_GET['dias'] ?? 30)));

        $stmt = $db->prepare("SELECT DATE(created_at) as fecha,
            COUNT(*) as creados,
            SUM(CASE WHEN resuelto_at IS NOT NULL AND DATE(resuelto_at) = DATE(created_at) THEN 1 ELSE 0 END) as resueltos_mismo_dia
            FROM tickets WHERE created_at > DATE_SUB(NOW(), INTERVAL ? DAY)
            GROUP BY DATE(created_at) ORDER BY fecha");
        $stmt->execute([$dias]);
        Response::success($stmt->fetchAll());
    }

    /**
     * GET /reportes/tickets-por-categoria — Distribución por categoría
     */
    public static function ticketsPorCategoria(): void {
        $payload = AuthMiddleware::verificar();
        RoleMiddleware::requireTecnico($payload);
        $db = getDB();
        $stmt = $db->prepare("SELECT c.nombre, c.color, COUNT(t.id) as total,
            AVG(TIMESTAMPDIFF(HOUR, t.created_at, t.resuelto_at)) as promedio_h
            FROM tickets t LEFT JOIN categorias c ON t.categoria_id = c.id
            WHERE t.created_at > DATE_SUB(NOW(), INTERVAL 90 DAY)
            GROUP BY t.categoria_id, c.nombre, c.color ORDER BY total DESC");
        $stmt->execute();
        Response::success($stmt->fetchAll());
    }

    /**
     * GET /reportes/rendimiento-tecnicos — Performance por técnico
     */
    public static function rendimientoTecnicos(): void {
        $payload = AuthMiddleware::verificar();
        RoleMiddleware::requireTecnico($payload);
        $db = getDB();
        $stmt = $db->prepare("SELECT u.id, u.nombre_completo,
            COUNT(t.id) as total_asignados,
            SUM(CASE WHEN t.estado IN ('resuelto','cerrado') THEN 1 ELSE 0 END) as resueltos,
            SUM(CASE WHEN t.estado IN ('abierto','en_progreso','pendiente') THEN 1 ELSE 0 END) as pendientes,
            AVG(CASE WHEN t.resuelto_at IS NOT NULL THEN TIMESTAMPDIFF(HOUR, t.created_at, t.resuelto_at) END) as promedio_horas,
            AVG(t.calificacion) as satisfaccion
            FROM usuarios u
            LEFT JOIN tickets t ON u.id = t.tecnico_asignado_id
            WHERE u.rol IN ('admin','tecnico') AND u.activo = 1
            GROUP BY u.id, u.nombre_completo ORDER BY resueltos DESC");
        $stmt->execute();
        Response::success($stmt->fetchAll());
    }

    /**
     * GET /reportes/sla — Cumplimiento SLA por categoría
     */
    public static function sla(): void {
        $payload = AuthMiddleware::verificar();
        RoleMiddleware::requireTecnico($payload);
        $db = getDB();
        $stmt = $db->prepare("SELECT c.nombre as categoria, c.sla_horas,
            COUNT(t.id) as total,
            SUM(CASE WHEN t.resuelto_at IS NOT NULL AND t.resuelto_at <= t.fecha_vencimiento THEN 1 ELSE 0 END) as dentro_sla,
            SUM(CASE WHEN t.resuelto_at IS NOT NULL AND t.resuelto_at > t.fecha_vencimiento THEN 1 ELSE 0 END) as fuera_sla,
            SUM(CASE WHEN t.estado IN ('abierto','en_progreso','pendiente') AND t.fecha_vencimiento < NOW() THEN 1 ELSE 0 END) as vencidos_abiertos
            FROM tickets t LEFT JOIN categorias c ON t.categoria_id = c.id
            WHERE t.created_at > DATE_SUB(NOW(), INTERVAL 90 DAY)
            GROUP BY t.categoria_id, c.nombre, c.sla_horas ORDER BY total DESC");
        $stmt->execute();
        Response::success($stmt->fetchAll());
    }

    /**
     * GET /reportes/activos-resumen — Estado del inventario
     */
    public static function activosResumen(): void {
        $payload = AuthMiddleware::verificar();
        RoleMiddleware::requireTecnico($payload);
        $db = getDB();
        $r = [];
        $r['por_tipo'] = $db->query("SELECT tipo, COUNT(*) as total FROM activos GROUP BY tipo ORDER BY total DESC")->fetchAll();
        $r['por_estado'] = $db->query("SELECT estado, COUNT(*) as total FROM activos GROUP BY estado")->fetchAll();
        $r['por_departamento'] = $db->query("SELECT COALESCE(u.departamento, 'Sin asignar') as departamento, COUNT(a.id) as total FROM activos a LEFT JOIN usuarios u ON a.usuario_asignado_id = u.id GROUP BY u.departamento ORDER BY total DESC")->fetchAll();
        $r['valor_total'] = (float)$db->query("SELECT COALESCE(SUM(costo_adquisicion), 0) FROM activos")->fetchColumn();
        $r['garantias_vencidas'] = (int)$db->query("SELECT COUNT(*) FROM activos WHERE garantia_hasta IS NOT NULL AND garantia_hasta < CURDATE()")->fetchColumn();
        $r['garantias_por_vencer'] = (int)$db->query("SELECT COUNT(*) FROM activos WHERE garantia_hasta IS NOT NULL AND garantia_hasta BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 90 DAY)")->fetchColumn();
        Response::success($r);
    }

    /**
     * GET /reportes/tickets-por-usuario — Quién creó más tickets, tiempos promedio por solicitante
     */
    public static function ticketsPorUsuario(): void {
        $payload = AuthMiddleware::verificar();
        RoleMiddleware::requireSupervisor($payload);
        $db = getDB();

        $desde = $_GET['desde'] ?? date('Y-m-01');
        $hasta = $_GET['hasta'] ?? date('Y-m-d');

        $stmt = $db->prepare("
            SELECT 
                u.id as usuario_id,
                u.nombre_completo,
                u.email,
                u.departamento,
                COUNT(t.id) as total_tickets,
                SUM(CASE WHEN t.estado IN ('abierto','en_progreso','pendiente') THEN 1 ELSE 0 END) as tickets_abiertos,
                SUM(CASE WHEN t.estado IN ('resuelto','cerrado') THEN 1 ELSE 0 END) as tickets_resueltos,
                AVG(CASE WHEN t.resuelto_at IS NOT NULL 
                    THEN TIMESTAMPDIFF(HOUR, t.created_at, t.resuelto_at) 
                    ELSE NULL END) as tiempo_promedio_h,
                AVG(CASE WHEN t.calificacion IS NOT NULL AND t.calificacion > 0 
                    THEN t.calificacion ELSE NULL END) as satisfaccion_promedio,
                MAX(t.created_at) as ultimo_ticket
            FROM usuarios u
            INNER JOIN tickets t ON u.id = t.solicitante_id
            WHERE DATE(t.created_at) BETWEEN ? AND ?
            GROUP BY u.id, u.nombre_completo, u.email, u.departamento
            ORDER BY total_tickets DESC
        ");
        $stmt->execute([$desde, $hasta]);
        $usuarios = $stmt->fetchAll();

        $result = array_map(function($u) {
            return [
                'usuario_id' => (int)$u['usuario_id'],
                'nombre_completo' => $u['nombre_completo'],
                'email' => $u['email'],
                'departamento' => $u['departamento'] ?: 'Sin asignar',
                'total_tickets' => (int)$u['total_tickets'],
                'tickets_abiertos' => (int)$u['tickets_abiertos'],
                'tickets_resueltos' => (int)$u['tickets_resueltos'],
                'tiempo_promedio_h' => $u['tiempo_promedio_h'] ? round((float)$u['tiempo_promedio_h'], 1) : null,
                'satisfaccion_promedio' => $u['satisfaccion_promedio'] ? round((float)$u['satisfaccion_promedio'], 1) : null,
                'ultimo_ticket' => $u['ultimo_ticket'],
            ];
        }, $usuarios);

        Response::success($result);
    }

    /**
     * GET /reportes/tiempos-atencion — Tiempos de primera respuesta y resolución
     */
    public static function tiemposAtencion(): void {
        $payload = AuthMiddleware::verificar();
        RoleMiddleware::requireTecnico($payload);
        $db = getDB();

        $desde = $_GET['desde'] ?? date('Y-m-01');
        $hasta = $_GET['hasta'] ?? date('Y-m-d');

        // Primera respuesta promedio
        $stmt = $db->prepare("
            SELECT AVG(TIMESTAMPDIFF(HOUR, created_at, primera_respuesta_at)) 
            FROM tickets 
            WHERE primera_respuesta_at IS NOT NULL 
            AND DATE(created_at) BETWEEN ? AND ?
        ");
        $stmt->execute([$desde, $hasta]);
        $primeraResp = $stmt->fetchColumn();

        // Resolución promedio
        $stmt = $db->prepare("
            SELECT AVG(TIMESTAMPDIFF(HOUR, created_at, resuelto_at)) 
            FROM tickets 
            WHERE resuelto_at IS NOT NULL 
            AND DATE(created_at) BETWEEN ? AND ?
        ");
        $stmt->execute([$desde, $hasta]);
        $resolucion = $stmt->fetchColumn();

        // Tickets resueltos en <24h
        $stmt = $db->prepare("
            SELECT COUNT(*) FROM tickets 
            WHERE resuelto_at IS NOT NULL 
            AND TIMESTAMPDIFF(HOUR, created_at, resuelto_at) < 24
            AND DATE(created_at) BETWEEN ? AND ?
        ");
        $stmt->execute([$desde, $hasta]);
        $menos24 = (int)$stmt->fetchColumn();

        // Tickets resueltos en >48h
        $stmt = $db->prepare("
            SELECT COUNT(*) FROM tickets 
            WHERE resuelto_at IS NOT NULL 
            AND TIMESTAMPDIFF(HOUR, created_at, resuelto_at) > 48
            AND DATE(created_at) BETWEEN ? AND ?
        ");
        $stmt->execute([$desde, $hasta]);
        $mas48 = (int)$stmt->fetchColumn();

        // Total resueltos
        $stmt = $db->prepare("
            SELECT COUNT(*) FROM tickets 
            WHERE resuelto_at IS NOT NULL 
            AND DATE(created_at) BETWEEN ? AND ?
        ");
        $stmt->execute([$desde, $hasta]);
        $totalResueltos = (int)$stmt->fetchColumn();

        // Detalle top 50 tickets más lentos
        $stmt = $db->prepare("
            SELECT 
                t.id, t.numero_ticket, t.titulo, t.prioridad, t.estado,
                tec.nombre_completo as tecnico_nombre,
                TIMESTAMPDIFF(HOUR, t.created_at, t.resuelto_at) as tiempo_total_horas,
                TIMESTAMPDIFF(HOUR, t.created_at, t.primera_respuesta_at) as primera_respuesta_horas
            FROM tickets t
            LEFT JOIN usuarios tec ON t.tecnico_asignado_id = tec.id
            WHERE t.resuelto_at IS NOT NULL
            AND DATE(t.created_at) BETWEEN ? AND ?
            ORDER BY tiempo_total_horas DESC
            LIMIT 50
        ");
        $stmt->execute([$desde, $hasta]);
        $detalle = $stmt->fetchAll();

        Response::success([
            'primera_respuesta_promedio' => $primeraResp ? round((float)$primeraResp, 1) : null,
            'tiempo_resolucion_promedio' => $resolucion ? round((float)$resolucion, 1) : null,
            'tickets_menos_24h' => $menos24,
            'tickets_mas_48h' => $mas48,
            'total_resueltos' => $totalResueltos,
            'detalle' => array_map(function($t) {
                return [
                    'id' => (int)$t['id'],
                    'numero_ticket' => $t['numero_ticket'],
                    'titulo' => $t['titulo'],
                    'prioridad' => $t['prioridad'],
                    'estado' => $t['estado'],
                    'tecnico_nombre' => $t['tecnico_nombre'],
                    'tiempo_total_horas' => $t['tiempo_total_horas'] !== null ? round((float)$t['tiempo_total_horas'], 1) : null,
                    'primera_respuesta_horas' => $t['primera_respuesta_horas'] !== null ? round((float)$t['primera_respuesta_horas'], 1) : null,
                ];
            }, $detalle),
        ]);
    }

    /**
     * GET /reportes/exportar-tickets — Export tickets as CSV
     */
    public static function exportarTickets(): void {
        $payload = AuthMiddleware::verificar();
        RoleMiddleware::requireTecnico($payload);
        $db = getDB();

        $desde = $_GET['desde'] ?? date('Y-m-01');
        $hasta = $_GET['hasta'] ?? date('Y-m-d');

        $stmt = $db->prepare("SELECT t.numero_ticket, t.titulo, t.prioridad, t.estado, t.canal_origen,
            s.nombre_completo as solicitante, tec.nombre_completo as tecnico,
            c.nombre as categoria, t.created_at, t.resuelto_at, t.cerrado_at,
            t.calificacion, t.fecha_vencimiento,
            CASE WHEN t.resuelto_at IS NOT NULL THEN TIMESTAMPDIFF(HOUR, t.created_at, t.resuelto_at) ELSE NULL END as horas_resolucion,
            CASE WHEN t.resuelto_at IS NOT NULL AND t.fecha_vencimiento IS NOT NULL AND t.resuelto_at <= t.fecha_vencimiento THEN 'Si' ELSE 'No' END as dentro_sla
            FROM tickets t
            LEFT JOIN usuarios s ON t.solicitante_id = s.id
            LEFT JOIN usuarios tec ON t.tecnico_asignado_id = tec.id
            LEFT JOIN categorias c ON t.categoria_id = c.id
            WHERE DATE(t.created_at) BETWEEN ? AND ?
            ORDER BY t.created_at DESC");
        $stmt->execute([$desde, $hasta]);
        $rows = $stmt->fetchAll();

        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="tickets_' . $desde . '_' . $hasta . '.csv"');
        $out = fopen('php://output', 'w');
        fprintf($out, chr(0xEF).chr(0xBB).chr(0xBF)); // BOM UTF-8
        fputcsv($out, ['Ticket','Título','Prioridad','Estado','Canal','Solicitante','Técnico','Categoría','Creado','Resuelto','Cerrado','Calificación','Vencimiento','Horas Resolución','Dentro SLA']);
        foreach ($rows as $r) fputcsv($out, array_values($r));
        fclose($out);
        exit;
    }
}