<?php
include "../config/config.php";

session_start();

if (!isset($_SESSION['admin_id'])) {
    header("location: ../../");
    exit;
}

$calificaciones = [
    null => "",
    1 => "Excelente",
    2 => "Buena",
    3 => "Regular",
    4 => "Mala",
    5 => "Pesima",
    6 => "Sin respuesta",
];

function parseReporteDateRange(string $daterange): array
{
    $parts = explode(' - ', $daterange);
    if (count($parts) !== 2) {
        return [null, null];
    }

    $start = DateTime::createFromFormat('d/m/Y H:i:s', trim($parts[0]) . ' 00:00:00');
    $end = DateTime::createFromFormat('d/m/Y H:i:s', trim($parts[1]) . ' 23:59:59');

    if (!$start || !$end) {
        return [null, null];
    }

    return [$start->format('Y-m-d H:i:s'), $end->format('Y-m-d H:i:s')];
}

function formatFechaExcel($date): string
{
    $timestamp = strtotime((string) $date);
    return $timestamp ? date('d/m/Y', $timestamp) : '';
}

function formatHoraExcel($date): string
{
    $timestamp = strtotime((string) $date);
    return $timestamp ? date('H:i', $timestamp) : '';
}

function getRangoHoraSolicitud($date): string
{
    $timestamp = strtotime((string) $date);
    return $timestamp ? date('H:00', $timestamp) : '';
}

function getTiempo($a, $b): string
{
    if (empty($a) || empty($b)) {
        return '';
    }

    $datetime1 = strtotime((string) $a);
    $datetime2 = strtotime((string) $b);

    if (!$datetime1 || !$datetime2) {
        return '';
    }

    $total = (int) round(abs($datetime1 - $datetime2) / 60);
    $minutos = $total % 60;
    $horas = intdiv($total, 60);

    return sprintf('%d:%02d', $horas, $minutos);
}

function h($value): string
{
    return htmlspecialchars((string) $value, ENT_QUOTES, 'UTF-8');
}

$daterange = (string)($_REQUEST['daterange'] ?? '');
$category = (int)($_REQUEST['category'] ?? 0);
[$fecha_inicial, $fecha_final] = parseReporteDateRange($daterange);

if (!$fecha_inicial || !$fecha_final) {
    echo "<script>alert('Rango de fechas invalido.')</script>";
    echo "<script>window.close();</script>";
    exit;
}

$where = "tickets.created_at BETWEEN ? AND ? AND tickets.status_ticket = 1";
$params = [$fecha_inicial, $fecha_final];
$types = 'ss';

if ($category > 0) {
    $where .= " AND tickets.status_id = ?";
    $params[] = $category;
    $types .= 'i';
}

$sql = "SELECT
        tickets.*,
        assigned_user.name AS agent_name,
        assigned_user.lastname AS agent_lastname,
        client_user.fullname AS client_fullname,
        client_user.name AS client_name,
        client_user.lastname AS client_lastname,
        status.name AS status_name,
        tipos_requerimientos.name AS categoria_name,
        area.name AS area_name
    FROM tickets
    LEFT JOIN user AS assigned_user ON assigned_user.id = tickets.asigned_id
    LEFT JOIN user AS client_user ON client_user.id = tickets.client_id
    LEFT JOIN status ON status.id = tickets.status_id
    LEFT JOIN tipos_requerimientos ON tipos_requerimientos.id = tickets.tipo_requerimiento
    LEFT JOIN area ON area.id = tickets.area
    WHERE $where
    ORDER BY tickets.created_at DESC";

$stmt = mysqli_prepare($con, $sql);
if (!$stmt) {
    echo "<script>alert('No se pudo preparar el reporte.')</script>";
    echo "<script>window.close();</script>";
    exit;
}

mysqli_stmt_bind_param($stmt, $types, ...$params);
mysqli_stmt_execute($stmt);
$query = mysqli_stmt_get_result($stmt);

if (!$query || mysqli_num_rows($query) === 0) {
    echo "<script>alert('No hay Tickets agregados para el rango seleccionado!')</script>";
    echo "<script>window.close();</script>";
    exit;
}

$extension = '.xls';
header("Content-type: application/vnd.ms-excel; charset=UTF-8");
header("Content-Disposition: attachment; filename=reporte_ticket$extension");
header("Pragma: no-cache");
header("Expires: 0");
echo "\xEF\xBB\xBF";

$scheme = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off') ? 'https://' : 'http://';
$baseUrl = $scheme . $_SERVER['HTTP_HOST'] . '/pro_php82';
?>
<table cellspacing="0" style="width: 100%; border: solid 1px #16a085; background: #16a085;color:white; text-align: center; font-size: 10pt;padding:1mm;">
    <tr>
        <th style="width: 100px;">FECHA DE SOLICITUD</th>
        <th style="width: 100px;">FECHA DE RESPUESTA</th>
        <th style="width: 100px;">FECHA DE CONCLUSI&Oacute;N</th>
        <th style="width: 100px;">CODIGO</th>
        <th style="width: 100px;">EMPLEADO</th>
        <th style="width: 100px;">AREA</th>
        <th style="width: 100px;">CATEGORIA</th>
        <th style="width: 100px;">STATUS</th>
        <th style="width: 100px;">HORA DE SOLICITUD</th>
        <th style="width: 100px;">HORA DE RESPUESTA</th>
        <th style="width: 100px;">HORA DE CONCLUSI&Oacute;N</th>
        <th style="width: 100px;">TIEMPO DE RESPUESTA</th>
        <th style="width: 100px;">TIEMPO DE SOLUCI&Oacute;N</th>
        <th style="width: 100px;">NOMBRE DEL AGENTE</th>
        <th style="width: 100px;">CALIFICACI&Oacute;N</th>
        <th style="width: 100px;">RANGO HORA SOLICITUD</th>
        <th style="width: 100px;">SOLUCI&Oacute;N</th>
        <th style="width: 100px;">REPORTE</th>
    </tr>
</table>

<table border="1" cellspacing="0" style="width: 100%; border: solid 1px #16a085; text-align: center; font-size: 9.5pt;padding:1mm;">
<?php while ($row = mysqli_fetch_assoc($query)):
    $fullname = trim((string)($row['client_fullname'] ?: trim((string)$row['client_name'] . ' ' . (string)$row['client_lastname'])));
    $agentName = trim((string)$row['agent_name'] . ' ' . (string)$row['agent_lastname']);
    $reportUrl = $baseUrl . '/admin/?view=pdf_reporte&id=' . (int)$row['id'];
?>
    <tr>
        <td style="min-width: 100px; text-align: left"><?= h(formatFechaExcel($row['created_at'])) ?></td>
        <td><?= h(formatFechaExcel($row['asignado'])) ?></td>
        <td><?= h(formatFechaExcel($row['final'])) ?></td>
        <td style="min-width: 100px; text-align: right;"><?= h($row['number_ticket']) ?></td>
        <td style="min-width: 100px; text-align: center;"><?= h($fullname) ?></td>
        <td style="min-width: 100px; text-align: center;"><?= h($row['area_name']) ?></td>
        <td style="min-width: 100px; text-align: center"><?= h($row['categoria_name']) ?></td>
        <td style="min-width: 100px; text-align: center"><?= h($row['status_name']) ?></td>
        <td><?= h(formatHoraExcel($row['created_at'])) ?></td>
        <td><?= h(formatHoraExcel($row['asignado'])) ?></td>
        <td><?= h(formatHoraExcel($row['final'])) ?></td>
        <td><?= h(getTiempo($row['created_at'], $row['asignado'])) ?></td>
        <td><?= h(getTiempo($row['created_at'], $row['final'])) ?></td>
        <td><?= h($agentName) ?></td>
        <td><?= h($calificaciones[$row['calificacion']] ?? '') ?></td>
        <td><?= h(getRangoHoraSolicitud($row['created_at'])) ?></td>
        <td><?= h($row['finalizado']) ?></td>
        <td><a href="<?= h($reportUrl) ?>"><?= h($reportUrl) ?></a></td>
    </tr>
<?php endwhile; ?>
</table>
<?php
mysqli_stmt_close($stmt);