<?php
/**
 * @intranet-file base/stats/stats.php
 * @intranet-file-version 2026.03.13.1
 * @intranet-file-date 2026-03-13
 * @intranet-file-owner CSIF Intranet
 */
declare(strict_types=1); // 1. SIEMPRE LA PRIMERA LINEA

// 2. SEGURIDAD: Guardian Central
require_once dirname(__DIR__, 2) . '/common/app_guard.php';
require_once dirname(__DIR__, 2) . '/common/url.php';
checkAppAccess();

// 3. SESION Y CONFIGURACION
date_default_timezone_set('Europe/Madrid');

// ====== Helpers ======
if (!function_exists('h')) {
    function h($s): string {
        return htmlspecialchars((string)$s, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
    }
}

// ====== DB ======
require_once dirname(__DIR__, 2) . '/common/db.php';
require_once dirname(__DIR__, 2) . '/common/catalogo_db.php';
require_once dirname(__DIR__, 2) . '/common/user_preferences.php';
if (!function_exists('db')) {
    http_response_code(500);
    die("Falta la funcion db() en common/db.php");
}

$pdo = db();

// ====== Seguridad Admin ======
$adminRoles = ['administradores', 'app_administradores', 'admin', 'app_admin'];
$userRoles = $_SESSION['roles'] ?? [];
$userRolesLower = array_map('strtolower', (array)$userRoles);
$hasAdminRole = !empty(array_intersect($userRolesLower, $adminRoles));

if (!$hasAdminRole) {
    renderErrorPage('Se requieren permisos de administrador para acceder a <em>Estadisticas de Uso</em>.');
    exit();
}

$usernameSession = (string)($_SESSION['username'] ?? '');
$userName        = (string)($_SESSION['givenName'] ?? $usernameSession);

// ====== Cargar Mapa de Apps desde BD ======
$appCatalog = [];
try {
    foreach (catalogo_apps_fetch($pdo, false) as $row) {
        $id = (string)($row['app_id'] ?? '');
        if ($id === '') continue;
        $appCatalog[$id] = [
            'name' => (string)($row['name'] ?? $id),
            'group' => (string)($row['group_name'] ?? ''),
        ];
    }
} catch (Throwable $e) {
    $appCatalog = [];
}

function appName(string $appId, array $catalog): string {
    return $catalog[$appId]['name'] ?? $appId;
}

// ====== Cargar Mapa de Links desde BD ======
$linkCatalog = [];
try {
    $stL = $pdo->query("SELECT id, link_type, grupo, descripcion FROM intranet_links ORDER BY link_type ASC, grupo ASC, position ASC, id ASC");
    while ($r = $stL->fetch(PDO::FETCH_ASSOC)) {
        $id = (int)($r['id'] ?? 0);
        if ($id <= 0) continue;
        $linkCatalog[$id] = [
            'type' => (string)($r['link_type'] ?? ''),
            'group' => (string)($r['grupo'] ?? ''),
            'desc' => (string)($r['descripcion'] ?? ('Link #' . $id)),
        ];
    }
} catch (Throwable $e) {
    $linkCatalog = [];
}

function linkTitle(int $linkId, array $catalog): string {
    if (!isset($catalog[$linkId])) return 'Link #' . $linkId;
    $c = $catalog[$linkId];
    $t = $c['type'] ?? '';
    $g = $c['group'] ?? '';
    $d = $c['desc'] ?? ('Link #' . $linkId);
    $prefix = $t !== '' ? strtoupper($t) : 'LINK';
    return $prefix . '  -  ' . ($g !== '' ? ($g . '  -  ') : '') . $d;
}

// ====== Logica de Datos (KPIs y Filtros) ======
function scalar(PDO $pdo, string $sql, array $params = []) {
    $st = $pdo->prepare($sql);
    $st->execute($params);
    return $st->fetchColumn();
}

// --- Anos ---
try {
    $stY = $pdo->query("SELECT year FROM stats_year UNION SELECT year FROM stats_apps_year UNION SELECT year FROM stats_links_year ORDER BY year DESC");
    $years = $stY->fetchAll(PDO::FETCH_COLUMN);
} catch (Throwable $e) { $years = [date('Y')]; }

$year = (int)($_GET['year'] ?? (int)date('Y'));
$usernameFilter = trim((string)($_GET['username'] ?? ''));
$appIdFilter    = trim((string)($_GET['app_id'] ?? ''));
$linkIdFilter   = trim((string)($_GET['link_id'] ?? ''));
$uLike = $usernameFilter !== '' ? "%{$usernameFilter}%" : null;

// --- Vista (tablas / graficas) ---
$viewMode = (string)($_GET['view'] ?? 'tables');
if (!in_array($viewMode, ['tables','charts'], true)) {
    $viewMode = 'tables';
}
// Helper para mantener filtros al cambiar de vista
if (!function_exists('buildQuery')) {
    function buildQuery(array $overrides = []): string {
        $params = $_GET;
        foreach ($overrides as $k => $v) {
            if ($v === null) unset($params[$k]);
            else $params[$k] = $v;
        }
        return http_build_query($params);
    }
}


// --- Export CSV ---
if (($_GET['export'] ?? '') === 'csv') {
    // ... (Mantenemos tu logica de exportacion intacta como estaba en tu archivo original) ...
    // [Se omite aqui por brevedad, pero debe estar en tu archivo]
}

// --- Filtros de tiempo (Anio / Mes / Semana / Dia / Hora) ---
$monthFilterRaw = trim((string)($_GET['month'] ?? '')); // '' | 1..12
$monthFilter = ($monthFilterRaw !== '' && ctype_digit($monthFilterRaw)) ? (int)$monthFilterRaw : 0;
$weekFilterRaw = trim((string)($_GET['week'] ?? ''));  // '' | ISO week 1..53
$weekFilter = ($weekFilterRaw !== '' && ctype_digit($weekFilterRaw)) ? (int)$weekFilterRaw : 0;
$dayFilter  = trim((string)($_GET['day'] ?? ''));     // YYYY-MM-DD
$hourFilter = trim((string)($_GET['hour'] ?? ''));    // 00..23
$showCumulative = isset($_GET['cumulative']) && (string)$_GET['cumulative'] === '1';

// Default: si no hay filtro temporal explicito, usar la semana actual
if (!isset($_GET['month']) && !isset($_GET['week']) && !isset($_GET['day']) && !isset($_GET['hour'])) {
    $weekFilter = (int)date('W');
    $weekFilterRaw = (string)$weekFilter;
}

$hourInt = null;
if ($hourFilter !== '' && ctype_digit($hourFilter)) {
    $hi = (int)$hourFilter;
    if ($hi >= 0 && $hi <= 23) $hourInt = $hi;
}

$rangeStart = new DateTime(sprintf('%04d-01-01 00:00:00', $year));
$rangeEnd   = new DateTime(sprintf('%04d-12-31 23:59:59', $year));

// Si viene dia, manda sobre mes y semana
if ($dayFilter !== '') {
    try {
        $rangeStart = new DateTime($dayFilter . ' 00:00:00');
        $rangeEnd   = new DateTime($dayFilter . ' 23:59:59');
    } catch (Throwable $e) {
        // si no es fecha valida, se ignora
        $dayFilter = '';
    }
} elseif ($monthFilter >= 1 && $monthFilter <= 12) {
    try {
        $rangeStart = new DateTime(sprintf('%04d-%02d-01 00:00:00', $year, $monthFilter));
        $rangeEnd = (clone $rangeStart);
        $rangeEnd->modify('last day of this month')->setTime(23,59,59);
    } catch (Throwable $e) {
        $monthFilter = 0;
        $monthFilterRaw = '';
        $rangeStart = new DateTime(sprintf('%04d-01-01 00:00:00', $year));
        $rangeEnd   = new DateTime(sprintf('%04d-12-31 23:59:59', $year));
    }
} elseif ($weekFilter >= 1 && $weekFilter <= 53) {
    try {
        $rangeStart = new DateTime();
        $rangeStart->setISODate($year, $weekFilter);
        $rangeStart->setTime(0,0,0);
        $rangeEnd = (clone $rangeStart);
        $rangeEnd->modify('+6 days')->setTime(23,59,59);
    } catch (Throwable $e) {
        $weekFilter = 0;
        $rangeStart = new DateTime(sprintf('%04d-01-01 00:00:00', $year));
        $rangeEnd   = new DateTime(sprintf('%04d-12-31 23:59:59', $year));
    }
}

$timeWhere = "created_at BETWEEN :start AND :end";
$timeParams = [
    'start' => $rangeStart->format('Y-m-d H:i:s'),
    'end'   => $rangeEnd->format('Y-m-d H:i:s'),
];

$baseWhere = [$timeWhere];
$baseParams = $timeParams;

if ($uLike) {
    $baseWhere[] = "username LIKE :uLike";
    $baseParams['uLike'] = $uLike;
}
if ($hourInt !== null) {
    // Hora del dia dentro del rango
    $baseWhere[] = "HOUR(created_at) = :hour";
    $baseParams['hour'] = $hourInt;
}

$baseWhereSql = implode(" AND ", $baseWhere);

// --- KPIs (sobre stats_clicks, respetando filtros) ---
$kpi_intranet_total = (int)(scalar($pdo, "SELECT COUNT(*) FROM stats_clicks WHERE {$baseWhereSql}", $baseParams) ?? 0);
$kpi_intranet_users = (int)(scalar($pdo, "SELECT COUNT(DISTINCT username) FROM stats_clicks WHERE {$baseWhereSql}", $baseParams) ?? 0);

$kpi_apps_total  = (int)(scalar($pdo, "SELECT COUNT(*) FROM stats_clicks WHERE {$baseWhereSql} AND item_kind='app' " . ($appIdFilter !== '' ? "AND item_key = :app_id" : ""), $appIdFilter !== '' ? array_merge($baseParams, ['app_id'=>$appIdFilter]) : $baseParams) ?? 0);
$kpi_apps_unique = (int)(scalar($pdo, "SELECT COUNT(DISTINCT item_key) FROM stats_clicks WHERE {$baseWhereSql} AND item_kind='app' " . ($appIdFilter !== '' ? "AND item_key = :app_id" : ""), $appIdFilter !== '' ? array_merge($baseParams, ['app_id'=>$appIdFilter]) : $baseParams) ?? 0);
$kpi_apps_users  = (int)(scalar($pdo, "SELECT COUNT(DISTINCT username) FROM stats_clicks WHERE {$baseWhereSql} AND item_kind='app' " . ($appIdFilter !== '' ? "AND item_key = :app_id" : ""), $appIdFilter !== '' ? array_merge($baseParams, ['app_id'=>$appIdFilter]) : $baseParams) ?? 0);

$linkParams = $baseParams;
$linkWhereExtra = "";
if ($linkIdFilter !== '' && ctype_digit($linkIdFilter)) {
    $linkWhereExtra = " AND item_key = :link_id";
    $linkParams['link_id'] = (string)(int)$linkIdFilter;
}
$kpi_links_total  = (int)(scalar($pdo, "SELECT COUNT(*) FROM stats_clicks WHERE {$baseWhereSql} AND item_kind='link'{$linkWhereExtra}", $linkParams) ?? 0);
$kpi_links_unique = (int)(scalar($pdo, "SELECT COUNT(DISTINCT item_key) FROM stats_clicks WHERE {$baseWhereSql} AND item_kind='link'{$linkWhereExtra}", $linkParams) ?? 0);
$kpi_links_users  = (int)(scalar($pdo, "SELECT COUNT(DISTINCT username) FROM stats_clicks WHERE {$baseWhereSql} AND item_kind='link'{$linkWhereExtra}", $linkParams) ?? 0);

// --- Preferencias personales actuales ---
function stats_menu_preference_key(?string $layout): string {
    $layout = intranet_menu_layout_normalize($layout);
    if ($layout === 'dock') return 'menu_dock';
    if ($layout === 'sidebar') return 'menu_original';
    return 'menu_superior';
}

function stats_theme_preference_key(?string $mode): string {
    return 'tema_' . intranet_theme_mode_normalize($mode);
}

function stats_session_preference_key($minutes): string {
    return 'sesion_' . intranet_session_timeout_normalize($minutes) . '_min';
}

$preferenceStats = [
    'menu' => [],
    'theme' => [],
    'session' => [],
];
$preferenceUsersTotal = 0;

try {
    intranet_user_preferences_ensure_table($pdo);
    $preferenceUsersTotal = (int)(scalar($pdo, "SELECT COUNT(*) FROM intranet_user_preferences") ?? 0);

    $st = $pdo->query("SELECT menu_layout, COUNT(*) AS total FROM intranet_user_preferences GROUP BY menu_layout ORDER BY total DESC, menu_layout ASC");
    foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $row) {
        $layout = intranet_menu_layout_normalize((string)($row['menu_layout'] ?? ''));
        $preferenceStats['menu'][] = [
            'key' => stats_menu_preference_key($layout),
            'label' => intranet_menu_layout_label($layout),
            'total' => (int)($row['total'] ?? 0),
        ];
    }

    $st = $pdo->query("SELECT theme_mode, COUNT(*) AS total FROM intranet_user_preferences GROUP BY theme_mode ORDER BY total DESC, theme_mode ASC");
    foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $row) {
        $mode = intranet_theme_mode_normalize((string)($row['theme_mode'] ?? ''));
        $preferenceStats['theme'][] = [
            'key' => stats_theme_preference_key($mode),
            'label' => intranet_theme_mode_label($mode),
            'total' => (int)($row['total'] ?? 0),
        ];
    }

    $st = $pdo->query("SELECT session_timeout_minutes, COUNT(*) AS total FROM intranet_user_preferences GROUP BY session_timeout_minutes ORDER BY session_timeout_minutes ASC");
    foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $row) {
        $minutes = intranet_session_timeout_normalize($row['session_timeout_minutes'] ?? INTRANET_SESSION_TIMEOUT_DEFAULT_MINUTES);
        $preferenceStats['session'][] = [
            'key' => stats_session_preference_key($minutes),
            'label' => intranet_session_timeout_label($minutes),
            'total' => (int)($row['total'] ?? 0),
        ];
    }
} catch (Throwable $e) {
    $preferenceStats = ['menu' => [], 'theme' => [], 'session' => []];
    $preferenceUsersTotal = 0;
}

// --- Listados Principales (respetan filtros) ---
// Top usuarios
$sqlUsers = "SELECT username, COUNT(*) AS total, MAX(created_at) AS last_visit_at
            FROM stats_clicks
            WHERE {$baseWhereSql}
            GROUP BY username
            ORDER BY total DESC
            LIMIT 50";
$stUsers = $pdo->prepare($sqlUsers);
$stUsers->execute($baseParams);
$rowsUsers = $stUsers->fetchAll();

// Top apps
$sqlApps = "SELECT item_key AS app_id, COUNT(*) AS total, MAX(created_at) AS updated_at
           FROM stats_clicks
           WHERE {$baseWhereSql} AND item_kind='app' " . ($appIdFilter !== '' ? "AND item_key = :app_id " : "") . "
           GROUP BY item_key
           ORDER BY total DESC
           LIMIT 50";
$paramsApps = $baseParams;
if ($appIdFilter !== '') $paramsApps['app_id'] = $appIdFilter;
$stApps = $pdo->prepare($sqlApps);
$stApps->execute($paramsApps);
$rowsApps = $stApps->fetchAll();

// Top links
$rowsLinks = [];
try {
    $sqlLinks = "SELECT COALESCE(link_type,'links') AS link_type, CAST(item_key AS UNSIGNED) AS link_id, COUNT(*) AS total, MAX(created_at) AS updated_at
                FROM stats_clicks
                WHERE {$baseWhereSql} AND item_kind='link'{$linkWhereExtra}
                GROUP BY COALESCE(link_type,'links'), CAST(item_key AS UNSIGNED)
                ORDER BY total DESC
                LIMIT 50";
    $st = $pdo->prepare($sqlLinks);
    $st->execute($linkParams);
    $rowsLinks = $st->fetchAll();
} catch (Throwable $e) {
    $rowsLinks = [];
}

// --- Detalle ---
$rowsUserApps = []; $rowsAppUsers = []; $rowUserApp = null;
$rowsUserLinks = []; $rowsLinkUsers = []; $rowUserLink = null;

// Detalle por usuario: sus apps y sus links (solo cuando no se ha fijado una app o link)
if ($usernameFilter !== '' && $appIdFilter === '' && $linkIdFilter === '') {
    // Apps del usuario
    $sql = "SELECT item_key AS app_id, COUNT(*) AS total, MAX(created_at) AS last_click_at
            FROM stats_clicks
            WHERE {$baseWhereSql} AND item_kind='app' AND username = :u
            GROUP BY item_key
            ORDER BY total DESC
            LIMIT 200";
    $params = array_merge($baseParams, ['u' => $usernameFilter]);
    $st = $pdo->prepare($sql);
    $st->execute($params);
    $rowsUserApps = $st->fetchAll();

    // Links del usuario
    $sql = "SELECT COALESCE(link_type,'links') AS link_type, CAST(item_key AS UNSIGNED) AS link_id, COUNT(*) AS total, MAX(created_at) AS last_click_at
            FROM stats_clicks
            WHERE {$baseWhereSql} AND item_kind='link' AND username = :u
            GROUP BY COALESCE(link_type,'links'), CAST(item_key AS UNSIGNED)
            ORDER BY total DESC
            LIMIT 200";
    $st = $pdo->prepare($sql);
    $st->execute($params);
    $rowsUserLinks = $st->fetchAll();
}

// Detalle por app: usuarios
if ($appIdFilter !== '' && $usernameFilter === '') {
    $sql = "SELECT username, COUNT(*) AS total, MAX(created_at) AS last_click_at
            FROM stats_clicks
            WHERE {$baseWhereSql} AND item_kind='app' AND item_key = :a " . ($uLike ? "AND username LIKE :uLike " : "") . "
            GROUP BY username
            ORDER BY total DESC
            LIMIT 200";
    $params = $baseParams;
    $params['a'] = $appIdFilter;
    if ($uLike) $params['uLike'] = $uLike;
    $st = $pdo->prepare($sql);
    $st->execute($params);
    $rowsAppUsers = $st->fetchAll();
}

// Detalle por enlace: usuarios
if ($linkIdFilter !== '' && ctype_digit($linkIdFilter) && $usernameFilter === '') {
    $sql = "SELECT username, COUNT(*) AS total, MAX(created_at) AS last_click_at
            FROM stats_clicks
            WHERE {$baseWhereSql} AND item_kind='link' AND item_key = :id
            GROUP BY username
            ORDER BY total DESC
            LIMIT 200";
    $params = $baseParams;
    $params['id'] = (string)(int)$linkIdFilter;
    $st = $pdo->prepare($sql);
    $st->execute($params);
    $rowsLinkUsers = $st->fetchAll();
}

// Detalle usuario+app
if ($appIdFilter !== '' && $usernameFilter !== '') {
    $sql = "SELECT COUNT(*) AS total, MAX(created_at) AS last_click_at
            FROM stats_clicks
            WHERE {$baseWhereSql} AND item_kind='app' AND item_key = :a AND username = :u
            LIMIT 1";
    $params = $baseParams;
    $params['a'] = $appIdFilter;
    $params['u'] = $usernameFilter;
    $st = $pdo->prepare($sql);
    $st->execute($params);
    $rowUserApp = $st->fetch();
}

// Detalle usuario+link
if ($linkIdFilter !== '' && ctype_digit($linkIdFilter) && $usernameFilter !== '') {
    $sql = "SELECT COUNT(*) AS total, MAX(created_at) AS last_click_at
            FROM stats_clicks
            WHERE {$baseWhereSql} AND item_kind='link' AND item_key = :id AND username = :u
            LIMIT 1";
    $params = $baseParams;
    $params['id'] = (string)(int)$linkIdFilter;
    $params['u'] = $usernameFilter;
    $st = $pdo->prepare($sql);
    $st->execute($params);
    $rowUserLink = $st->fetch();
}

// --- Datos adicionales para graficas de barras --- 
$appsBarLabels = [];
$appsBarData = [];
foreach (array_slice($rowsApps, 0, 12) as $r) {
    $appId = (string)($r['app_id'] ?? '');
    if ($appId === '') continue;
    $label = appName($appId, $appCatalog);
    $group = trim((string)($appCatalog[$appId]['group'] ?? ''));
    if ($group !== '') {
        $label .= ' [' . $group . ']';
    }
    $appsBarLabels[] = $label;
    $appsBarData[] = (int)($r['total'] ?? 0);
}

$linkTypeBarLabels = [];
$linkTypeBarData = [];
$linkGroupBarLabels = [];
$linkGroupBarData = [];

try {
    $sqlLinkTypes = "SELECT COALESCE(NULLIF(link_type, ''), 'links') AS bucket, COUNT(*) AS total
                     FROM stats_clicks
                     WHERE {$baseWhereSql} AND item_kind='link'{$linkWhereExtra}
                     GROUP BY COALESCE(NULLIF(link_type, ''), 'links')
                     ORDER BY total DESC, bucket ASC
                     LIMIT 12";
    $st = $pdo->prepare($sqlLinkTypes);
    $st->execute($linkParams);
    foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $r) {
        $linkTypeBarLabels[] = strtoupper((string)($r['bucket'] ?? 'LINKS'));
        $linkTypeBarData[] = (int)($r['total'] ?? 0);
    }
} catch (Throwable $e) {
    $linkTypeBarLabels = [];
    $linkTypeBarData = [];
}

try {
    $linkChartWhere = ["sc.created_at BETWEEN :start AND :end", "sc.item_kind='link'"];
    $linkChartParams = $baseParams;
    if ($linkIdFilter !== '' && ctype_digit($linkIdFilter)) {
        $linkChartWhere[] = "sc.item_key = :link_id";
        $linkChartParams['link_id'] = (string)(int)$linkIdFilter;
    }

    $sqlLinkGroups = "SELECT COALESCE(NULLIF(l.grupo, ''), 'Sin grupo') AS bucket, COUNT(*) AS total
                      FROM stats_clicks sc
                      LEFT JOIN intranet_links l ON l.id = CAST(sc.item_key AS UNSIGNED)
                      WHERE " . implode(' AND ', $linkChartWhere) . "
                      GROUP BY COALESCE(NULLIF(l.grupo, ''), 'Sin grupo')
                      ORDER BY total DESC, bucket ASC
                      LIMIT 12";
    $st = $pdo->prepare($sqlLinkGroups);
    $st->execute($linkChartParams);
    foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $r) {
        $linkGroupBarLabels[] = (string)($r['bucket'] ?? 'Sin grupo');
        $linkGroupBarData[] = (int)($r['total'] ?? 0);
    }
} catch (Throwable $e) {
    $linkGroupBarLabels = [];
    $linkGroupBarData = [];
}

// --- Datos para graficas (Chart.js) ---
$chartTitle = 'Actividad';
$chartLabels = [];
$chartData = [];
$chartCumulativeData = [];

try {
    if ($dayFilter !== '' && $hourInt !== null) {
        // Dia + hora => por minuto
        $chartTitle = "Clicks por minuto ({$dayFilter} {$hourInt}:00)";
        $sql = "SELECT MINUTE(created_at) AS bucket, COUNT(*) AS total
                FROM stats_clicks
                WHERE {$timeWhere} AND HOUR(created_at)=:hour" . ($uLike ? " AND username LIKE :uLike" : "") .
                ($appIdFilter !== '' ? " AND item_kind='app' AND item_key = :app_id" : "") .
                ($linkIdFilter !== '' && ctype_digit($linkIdFilter) ? " AND item_kind='link' AND item_key = :link_id" : "") .
                "
                GROUP BY MINUTE(created_at)
                ORDER BY bucket ASC";
        $p = $timeParams;
        $p['hour'] = $hourInt;
        if ($uLike) $p['uLike'] = $uLike;
        if ($appIdFilter !== '') $p['app_id'] = $appIdFilter;
        if ($linkIdFilter !== '' && ctype_digit($linkIdFilter)) $p['link_id'] = (string)(int)$linkIdFilter;
        $st = $pdo->prepare($sql);
        $st->execute($p);
        $rows = $st->fetchAll(PDO::FETCH_ASSOC);
        $map = [];
        foreach ($rows as $r) $map[(int)$r['bucket']] = (int)$r['total'];
        for ($i=0;$i<=59;$i++){
            $chartLabels[] = str_pad((string)$i,2,'0',STR_PAD_LEFT);
            $chartData[] = (int)($map[$i] ?? 0);
        }
    } elseif ($dayFilter !== '') {
        // Dia => por hora
        $chartTitle = "Clicks por hora ({$dayFilter})";
        $sql = "SELECT HOUR(created_at) AS bucket, COUNT(*) AS total
                FROM stats_clicks
                WHERE {$timeWhere}" . ($uLike ? " AND username LIKE :uLike" : "") .
                ($appIdFilter !== '' ? " AND item_kind='app' AND item_key = :app_id" : "") .
                ($linkIdFilter !== '' && ctype_digit($linkIdFilter) ? " AND item_kind='link' AND item_key = :link_id" : "") .
                "
                GROUP BY HOUR(created_at)
                ORDER BY bucket ASC";
        $p = $timeParams;
        if ($uLike) $p['uLike'] = $uLike;
        if ($appIdFilter !== '') $p['app_id'] = $appIdFilter;
        if ($linkIdFilter !== '' && ctype_digit($linkIdFilter)) $p['link_id'] = (string)(int)$linkIdFilter;
        $st = $pdo->prepare($sql);
        $st->execute($p);
        $rows = $st->fetchAll(PDO::FETCH_ASSOC);
        $map = [];
        foreach ($rows as $r) $map[(int)$r['bucket']] = (int)$r['total'];
        for ($i=0;$i<=23;$i++){
            $chartLabels[] = str_pad((string)$i,2,'0',STR_PAD_LEFT).":00";
            $chartData[] = (int)($map[$i] ?? 0);
        }
    } elseif ($monthFilter >= 1 && $monthFilter <= 12) {
        // Mes => por dia
        $monthNames = [1 => 'Ene', 2 => 'Feb', 3 => 'Mar', 4 => 'Abr', 5 => 'May', 6 => 'Jun', 7 => 'Jul', 8 => 'Ago', 9 => 'Sep', 10 => 'Oct', 11 => 'Nov', 12 => 'Dic'];
        $chartTitle = "Clicks por dia (" . ($monthNames[$monthFilter] ?? ('Mes ' . $monthFilter)) . " {$year})";
        $sql = "SELECT DATE(created_at) AS bucket, COUNT(*) AS total
                FROM stats_clicks
                WHERE {$timeWhere}" . ($hourInt !== null ? " AND HOUR(created_at)=:hour" : "") .
                ($uLike ? " AND username LIKE :uLike" : "") .
                ($appIdFilter !== '' ? " AND item_kind='app' AND item_key = :app_id" : "") .
                ($linkIdFilter !== '' && ctype_digit($linkIdFilter) ? " AND item_kind='link' AND item_key = :link_id" : "") .
                "
                GROUP BY DATE(created_at)
                ORDER BY bucket ASC";
        $p = $timeParams;
        if ($hourInt !== null) $p['hour'] = $hourInt;
        if ($uLike) $p['uLike'] = $uLike;
        if ($appIdFilter !== '') $p['app_id'] = $appIdFilter;
        if ($linkIdFilter !== '' && ctype_digit($linkIdFilter)) $p['link_id'] = (string)(int)$linkIdFilter;
        $st = $pdo->prepare($sql);
        $st->execute($p);
        $rows = $st->fetchAll(PDO::FETCH_ASSOC);
        $map = [];
        foreach ($rows as $r) $map[(string)$r['bucket']] = (int)$r['total'];
        $d = clone $rangeStart;
        while ($d <= $rangeEnd) {
            $key = $d->format('Y-m-d');
            $chartLabels[] = $d->format('d/m');
            $chartData[] = (int)($map[$key] ?? 0);
            $d->modify('+1 day');
        }
    } elseif ($weekFilter >= 1 && $weekFilter <= 53) {
        // Semana => por dia
        $chartTitle = "Clicks por dia (Semana {$weekFilter})";
        $sql = "SELECT DATE(created_at) AS bucket, COUNT(*) AS total
                FROM stats_clicks
                WHERE {$timeWhere}" . ($hourInt !== null ? " AND HOUR(created_at)=:hour" : "") .
                ($uLike ? " AND username LIKE :uLike" : "") .
                ($appIdFilter !== '' ? " AND item_kind='app' AND item_key = :app_id" : "") .
                ($linkIdFilter !== '' && ctype_digit($linkIdFilter) ? " AND item_kind='link' AND item_key = :link_id" : "") .
                "
                GROUP BY DATE(created_at)
                ORDER BY bucket ASC";
        $p = $timeParams;
        if ($hourInt !== null) $p['hour'] = $hourInt;
        if ($uLike) $p['uLike'] = $uLike;
        if ($appIdFilter !== '') $p['app_id'] = $appIdFilter;
        if ($linkIdFilter !== '' && ctype_digit($linkIdFilter)) $p['link_id'] = (string)(int)$linkIdFilter;
        $st = $pdo->prepare($sql);
        $st->execute($p);
        $rows = $st->fetchAll(PDO::FETCH_ASSOC);
        $map = [];
        foreach ($rows as $r) $map[(string)$r['bucket']] = (int)$r['total'];
        $d = clone $rangeStart;
        for ($i=0;$i<7;$i++){
            $key = $d->format('Y-m-d');
            $chartLabels[] = $d->format('d/m');
            $chartData[] = (int)($map[$key] ?? 0);
            $d->modify('+1 day');
        }
    } else {
        // Ano => por mes
        $chartTitle = "Clicks por mes ({$year})";
        $sql = "SELECT MONTH(created_at) AS bucket, COUNT(*) AS total
                FROM stats_clicks
                WHERE {$timeWhere}" . ($hourInt !== null ? " AND HOUR(created_at)=:hour" : "") .
                ($uLike ? " AND username LIKE :uLike" : "") .
                ($appIdFilter !== '' ? " AND item_kind='app' AND item_key = :app_id" : "") .
                ($linkIdFilter !== '' && ctype_digit($linkIdFilter) ? " AND item_kind='link' AND item_key = :link_id" : "") .
                "
                GROUP BY MONTH(created_at)
                ORDER BY bucket ASC";
        $p = $timeParams;
        if ($hourInt !== null) $p['hour'] = $hourInt;
        if ($uLike) $p['uLike'] = $uLike;
        if ($appIdFilter !== '') $p['app_id'] = $appIdFilter;
        if ($linkIdFilter !== '' && ctype_digit($linkIdFilter)) $p['link_id'] = (string)(int)$linkIdFilter;
        $st = $pdo->prepare($sql);
        $st->execute($p);
        $rows = $st->fetchAll(PDO::FETCH_ASSOC);
        $map = [];
        foreach ($rows as $r) $map[(int)$r['bucket']] = (int)$r['total'];

        $months = ['Ene','Feb','Mar','Abr','May','Jun','Jul','Ago','Sep','Oct','Nov','Dic'];
        for ($i=1;$i<=12;$i++){
            $chartLabels[] = $months[$i-1];
            $chartData[] = (int)($map[$i] ?? 0);
        }
    }
} catch (Throwable $e) {
    $chartLabels = [];
    $chartData = [];
}

if ($showCumulative && !empty($chartData)) {
    $runningTotal = 0;
    foreach ($chartData as $value) {
        $runningTotal += (int)$value;
        $chartCumulativeData[] = $runningTotal;
    }
}

$refreshQuery = buildQuery(['refresh' => (string)time()]);

?>
<!doctype html>
<html lang="es" class="app-html" <?= intranet_theme_html_attr(); ?>>
<head>
    <meta charset="utf-8"/>
    <meta name="viewport" content="width=device-width, initial-scale=1"/>
    <title>Estadisticas - Intranet</title>
    <?= intranet_theme_bootstrap_script_tag(); ?>
    <link rel="stylesheet" href="<?= h(asset_url('styles/css/style.css')) ?>"/>
    <link rel="stylesheet" href="<?= h(asset_url('styles/css/theme-vars.css')) ?>"/>
    <style>
        .stats-filter-actions {
            display: inline-flex;
            align-items: stretch;
            gap: 12px;
        }

        .stats-filter-actions > .btn-primary,
        .stats-filter-actions > .btn-secondary,
        .stats-filter-actions > .stats-action-link {
            display: inline-flex;
            align-items: center;
            justify-content: center;
            height: 45px;
            min-height: 45px;
            padding: 0 18px;
            line-height: 1;
            box-sizing: border-box;
            text-decoration: none !important;
            white-space: nowrap;
            appearance: none;
            -webkit-appearance: none;
        }
    </style>
    <script src="<?= h(asset_url('styles/js/table_sort.js')) ?>" defer></script>
    <script src="https://cdn.jsdelivr.net/npm/chart.js@3.9.1/dist/chart.min.js"></script>
</head>
<body class="app-body">

<div class="app-container">
    <!-- CABECERA -->
    <header class="header-top">
        <div class="header-info">
            <h2>Estadisticas de Uso</h2>
            <p>Conectado como: <strong><?php echo h($userName); ?></strong></p>
        </div>
        <div class="header-actions">
            <nav class="header-nav">
                <ul>
                    <li><a href="?<?php echo h(buildQuery(['view' => 'tables'])); ?>" class="<?php echo $viewMode==='tables'?'active':''; ?>">Ver tablas</a></li>
                    <li><a href="?<?php echo h(buildQuery(['view' => 'charts'])); ?>" class="<?php echo $viewMode==='charts'?'active':''; ?>">Ver graficas</a></li>
                </ul>
            </nav>

            <a href="../../dashboard.php" target="_top" class="btn-back-dashboard">Volver al inicio</a>
        </div>
    </header>

    <!-- FILTROS -->
    <section class="card-filters">
        <form method="GET" class="filter-grid">
            
            <input type="hidden" name="view" value="<?php echo h($viewMode); ?>"/>
<div class="filter-group">
                <label>Anio de analisis</label>
                <select name="year" class="form-control">
                    <?php foreach ($years as $yy): ?>
                        <option value="<?php echo h((string)$yy); ?>" <?php echo ((int)$yy === $year) ? 'selected' : ''; ?>><?php echo h((string)$yy); ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="filter-group">
                <label>Mes</label>
                <select name="month" class="form-control">
                    <option value="" <?php echo $monthFilterRaw === '' ? 'selected' : ''; ?>>(Todos)</option>
                    <?php $monthLabels = [1 => 'Enero', 2 => 'Febrero', 3 => 'Marzo', 4 => 'Abril', 5 => 'Mayo', 6 => 'Junio', 7 => 'Julio', 8 => 'Agosto', 9 => 'Septiembre', 10 => 'Octubre', 11 => 'Noviembre', 12 => 'Diciembre']; ?>
                    <?php foreach ($monthLabels as $monthValue => $monthLabel): ?>
                        <option value="<?php echo h((string)$monthValue); ?>" <?php echo $monthFilter === $monthValue ? 'selected' : ''; ?>>
                            <?php echo h($monthLabel); ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="filter-group">
                <label>Semana (ISO)</label>
                <select name="week" class="form-control">
                    <option value="" <?php echo $weekFilterRaw === '' ? 'selected' : ''; ?>>(Todas las semanas)</option>
                    <?php for ($ww = 1; $ww <= 53; $ww++): $weekValue = str_pad((string)$ww, 2, '0', STR_PAD_LEFT); ?>
                        <option value="<?php echo h($weekValue); ?>" <?php echo $weekFilter === $ww ? 'selected' : ''; ?>>
                            <?php echo 'Semana ' . h($weekValue); ?>
                        </option>
                    <?php endfor; ?>
                </select>
            </div>
            <div class="filter-group">
                <label>Dia</label>
                <input type="date" name="day" class="form-control" value="<?php echo h((string)$dayFilter); ?>">
            </div>
            <div class="filter-group">
                <label>Hora</label>
                <select name="hour" class="form-control">
                    <option value="">(Todas)</option>
                    <?php for ($hh = 0; $hh <= 23; $hh++): $h2 = str_pad((string)$hh, 2, '0', STR_PAD_LEFT); ?>
                        <option value="<?php echo $h2; ?>" <?php echo ($hourFilter !== '' && (int)$hourFilter === $hh) ? 'selected' : ''; ?>><?php echo $h2 . ':00'; ?></option>
                    <?php endfor; ?>
                </select>
            </div>

            <div class="filter-group">
                <label>Filtrar Usuario (Contiene)</label>
                <input type="text" name="username" class="form-control" value="<?php echo h($usernameFilter); ?>" placeholder="ej: jgarcia">
            </div>
            <div class="filter-group">
                <label>Aplicacion Especifica</label>
                <select name="app_id" class="form-control">
                    <option value="">(Todas las aplicaciones)</option>
                    <?php foreach ($appCatalog as $id => $info): ?>
                        <option value="<?php echo h((string)$id); ?>" <?php echo ((string)$id === $appIdFilter) ? 'selected' : ''; ?>>
                            <?php echo h($info['name']); ?> (<?php echo h((string)$id); ?>)
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="filter-group">
                <label>Enlace especifico</label>
                <select name="link_id" class="form-control">
                    <option value="">(Todos los enlaces)</option>
                    <?php foreach ($linkCatalog as $lid => $info): ?>
                        <option value="<?php echo h((string)$lid); ?>" <?php echo ((string)$lid === $linkIdFilter) ? 'selected' : ''; ?>>
                            <?php echo h(linkTitle((int)$lid, $linkCatalog)); ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="filter-group">
                <label>Grafica acumulada</label>
                <select name="cumulative" class="form-control">
                    <option value="0" <?php echo !$showCumulative ? 'selected' : ''; ?>>No</option>
                    <option value="1" <?php echo $showCumulative ? 'selected' : ''; ?>>Si</option>
                </select>
            </div>
            <div class="stats-filter-actions">
                <button type="submit" class="btn-primary">Aplicar Filtros</button>
                <a href="?<?php echo h($refreshQuery); ?>" class="btn-secondary stats-action-link">Actualizar datos</a>
                <a href="stats.php" class="btn-secondary stats-action-link">Limpiar</a>
            </div>
        </form>
    </section>
    <?php if ($viewMode === 'charts'): ?>

    <script>
        function statsHexToRgba(hex, alpha) {
            const value = String(hex || '').trim().replace('#', '');
            if (!/^[0-9a-f]{6}$/i.test(value)) {
                return 'rgba(6, 123, 59, ' + alpha + ')';
            }
            const r = parseInt(value.slice(0, 2), 16);
            const g = parseInt(value.slice(2, 4), 16);
            const b = parseInt(value.slice(4, 6), 16);
            return 'rgba(' + r + ', ' + g + ', ' + b + ', ' + alpha + ')';
        }

        function statsChartTheme() {
            const rootStyle = getComputedStyle(document.documentElement);
            return {
                primary: rootStyle.getPropertyValue('--primary').trim() || '#067b3b',
                primaryDark: rootStyle.getPropertyValue('--primary-dark').trim() || '#045227',
                accent: rootStyle.getPropertyValue('--accent').trim() || '#ef4444',
                text: rootStyle.getPropertyValue('--text-main').trim() || '#334155',
                muted: rootStyle.getPropertyValue('--text-muted').trim() || '#64748b',
                border: rootStyle.getPropertyValue('--border-light').trim() || '#e2e8f0',
                card: rootStyle.getPropertyValue('--bg-card').trim() || '#ffffff'
            };
        }

        function renderStatsBarChart(canvasId, labels, datasets, options) {
            const canvas = document.getElementById(canvasId);
            if (!canvas || !Array.isArray(labels) || !labels.length) return;

            const theme = statsChartTheme();
            const grid = statsHexToRgba(theme.border, 0.65);
            const palette = [
                {bg: statsHexToRgba(theme.primary, 0.82), border: theme.primaryDark},
                {bg: statsHexToRgba(theme.accent, 0.82), border: theme.accent},
                {bg: statsHexToRgba(theme.primaryDark, 0.82), border: theme.primaryDark}
            ];

            const normalizedDatasets = datasets.map((dataset, index) => ({
                borderWidth: 1,
                borderRadius: 10,
                maxBarThickness: 32,
                backgroundColor: dataset.backgroundColor || palette[index % palette.length].bg,
                borderColor: dataset.borderColor || palette[index % palette.length].border,
                ...dataset
            }));

            const horizontal = !!(options && options.horizontal);

            new Chart(canvas, {
                type: 'bar',
                data: {
                    labels: labels,
                    datasets: normalizedDatasets
                },
                options: {
                    indexAxis: horizontal ? 'y' : 'x',
                    responsive: true,
                    maintainAspectRatio: false,
                    plugins: {
                        legend: { display: normalizedDatasets.length > 1 },
                        tooltip: {
                            backgroundColor: theme.card,
                            titleColor: theme.text,
                            bodyColor: theme.text,
                            borderColor: grid,
                            borderWidth: 1,
                            displayColors: normalizedDatasets.length > 1
                        }
                    },
                    scales: {
                        x: {
                            beginAtZero: horizontal,
                            ticks: { color: theme.muted, precision: 0 },
                            grid: { color: grid, drawBorder: false }
                        },
                        y: {
                            beginAtZero: !horizontal,
                            ticks: { color: theme.muted, precision: 0 },
                            grid: { color: grid, drawBorder: false }
                        }
                    }
                }
            });
        }
    </script>

    <!-- GRAFICAS -->
    <section class="card-table">
        <div class="card-title">
            Grafica de actividad
            <span class="pill pill-link"><?php echo h($chartTitle); ?></span>
            <?php if ($monthFilter > 0): ?>
                <span class="pill pill-link">Mes filtrado</span>
            <?php elseif ($weekFilter === 0 && $dayFilter === ''): ?>
                <span class="pill pill-link">Todas las semanas</span>
            <?php endif; ?>
            <?php if ($showCumulative): ?>
                <span class="pill pill-link">Acumulada</span>
            <?php endif; ?>
        </div>
        <?php if (!empty($chartLabels)): ?>
            <div class="chart-canvas-wrap">
                <canvas id="chartActivity" height="90"></canvas>
            </div>
            <script>
                const activityLabels = <?php echo json_encode($chartLabels, JSON_UNESCAPED_UNICODE); ?>;
                const activityData = <?php echo json_encode($chartData, JSON_UNESCAPED_UNICODE); ?>;
                const activityCumulativeData = <?php echo json_encode($chartCumulativeData, JSON_UNESCAPED_UNICODE); ?>;
                const activityShowCumulative = <?php echo $showCumulative ? 'true' : 'false'; ?>;
                const activityRootStyle = getComputedStyle(document.documentElement);
                const activityPrimary = activityRootStyle.getPropertyValue('--primary').trim() || '#067b3b';
                const activityPrimaryDark = activityRootStyle.getPropertyValue('--primary-dark').trim() || '#045227';
                const activityAccent = activityRootStyle.getPropertyValue('--accent').trim() || '#ef4444';
                const activityText = activityRootStyle.getPropertyValue('--text-main').trim() || '#334155';
                const activityMuted = activityRootStyle.getPropertyValue('--text-muted').trim() || '#64748b';
                const activityBorder = activityRootStyle.getPropertyValue('--border-light').trim() || '#e2e8f0';
                const activityCard = activityRootStyle.getPropertyValue('--bg-card').trim() || '#ffffff';

                const activityGrid = statsHexToRgba(activityBorder, 0.65);
                const activityBar = statsHexToRgba(activityPrimary, 0.82);
                const activityBarHover = statsHexToRgba(activityPrimaryDark, 0.92);
                const activityLine = statsHexToRgba(activityAccent, 0.95);

                const activityDatasets = [{
                    label: 'Clicks',
                    data: activityData,
                    backgroundColor: activityBar,
                    hoverBackgroundColor: activityBarHover,
                    borderColor: activityPrimaryDark,
                    borderWidth: 1,
                    borderRadius: 10,
                    maxBarThickness: 42
                }];

                if (activityShowCumulative && activityCumulativeData.length) {
                    activityDatasets.push({
                        type: 'line',
                        label: 'Acumulado',
                        data: activityCumulativeData,
                        borderColor: activityLine,
                        backgroundColor: activityLine,
                        pointBackgroundColor: activityLine,
                        pointBorderColor: activityCard,
                        pointRadius: 3,
                        pointHoverRadius: 4,
                        borderWidth: 2,
                        tension: 0.24,
                        fill: false
                    });
                }

                new Chart(document.getElementById('chartActivity'), {
                    type: 'bar',
                    data: {
                        labels: activityLabels,
                        datasets: activityDatasets
                    },
                    options: {
                        responsive: true,
                        plugins: {
                            legend: { display: activityShowCumulative },
                            tooltip: {
                                backgroundColor: activityCard,
                                titleColor: activityText,
                                bodyColor: activityText,
                                borderColor: activityGrid,
                                borderWidth: 1,
                                displayColors: false
                            }
                        },
                        scales: {
                            x: {
                                ticks: { color: activityMuted },
                                grid: { color: activityGrid, drawBorder: false }
                            },
                            y: {
                                beginAtZero: true,
                                ticks: {
                                    color: activityMuted,
                                    precision: 0
                                },
                                grid: { color: activityGrid, drawBorder: false }
                            }
                        }
                    }
                });
            </script>
        <?php else: ?>
            <div class="muted-help">No hay datos para representar con los filtros seleccionados.</div>
        <?php endif; ?>
    </section>

    <section class="card-table">
        <div class="card-title">
            Apps y plugins mas visitados
            <span class="pill pill-link">Top 12</span>
        </div>
        <?php if (!empty($appsBarLabels)): ?>
            <div class="chart-canvas-wrap" style="height: 420px;">
                <canvas id="chartAppsBars"></canvas>
            </div>
            <script>
                renderStatsBarChart(
                    'chartAppsBars',
                    <?php echo json_encode($appsBarLabels, JSON_UNESCAPED_UNICODE); ?>,
                    [{
                        label: 'Clicks',
                        data: <?php echo json_encode($appsBarData, JSON_UNESCAPED_UNICODE); ?>
                    }],
                    { horizontal: true }
                );
            </script>
        <?php else: ?>
            <div class="muted-help">No hay visitas a apps/plugins con los filtros seleccionados.</div>
        <?php endif; ?>
    </section>

    <section class="card-table">
        <div class="card-title">
            Enlaces visitados por tipo y grupo
            <span class="pill pill-link">Barras</span>
        </div>
        <div style="display:grid; grid-template-columns:repeat(auto-fit, minmax(320px, 1fr)); gap:20px;">
            <div>
                <div class="muted-help" style="margin-bottom:10px;">Por tipo de enlace</div>
                <?php if (!empty($linkTypeBarLabels)): ?>
                    <div class="chart-canvas-wrap" style="height: 320px;">
                        <canvas id="chartLinkTypes"></canvas>
                    </div>
                    <script>
                        renderStatsBarChart(
                            'chartLinkTypes',
                            <?php echo json_encode($linkTypeBarLabels, JSON_UNESCAPED_UNICODE); ?>,
                            [{
                                label: 'Clicks',
                                data: <?php echo json_encode($linkTypeBarData, JSON_UNESCAPED_UNICODE); ?>
                            }],
                            { horizontal: false }
                        );
                    </script>
                <?php else: ?>
                    <div class="muted-help">No hay visitas a enlaces para agrupar por tipo.</div>
                <?php endif; ?>
            </div>
            <div>
                <div class="muted-help" style="margin-bottom:10px;">Por grupo de enlace</div>
                <?php if (!empty($linkGroupBarLabels)): ?>
                    <div class="chart-canvas-wrap" style="height: 420px;">
                        <canvas id="chartLinkGroups"></canvas>
                    </div>
                    <script>
                        renderStatsBarChart(
                            'chartLinkGroups',
                            <?php echo json_encode($linkGroupBarLabels, JSON_UNESCAPED_UNICODE); ?>,
                            [{
                                label: 'Clicks',
                                data: <?php echo json_encode($linkGroupBarData, JSON_UNESCAPED_UNICODE); ?>
                            }],
                            { horizontal: true }
                        );
                    </script>
                <?php else: ?>
                    <div class="muted-help">No hay visitas a enlaces para agrupar por grupo.</div>
                <?php endif; ?>
            </div>
        </div>
    </section>
    <?php endif; ?>
    <?php if ($viewMode === 'tables'): ?>

    <!-- KPIs -->
    <section class="kpi-grid">
        <div class="kpi-card"><div class="label">Visitas Intranet</div><div class="value"><?php echo number_format($kpi_intranet_total); ?></div></div>
        <div class="kpi-card"><div class="label">Usuarios Activos</div><div class="value"><?php echo number_format($kpi_intranet_users); ?></div></div>
        <div class="kpi-card"><div class="label">Clics en Apps</div><div class="value"><?php echo number_format($kpi_apps_total); ?></div></div>
        <div class="kpi-card is-accent"><div class="label">Apps Utilizadas</div><div class="value"><?php echo number_format($kpi_apps_unique); ?></div></div>
        <div class="kpi-card"><div class="label">Clics en Enlaces</div><div class="value"><?php echo number_format($kpi_links_total); ?></div></div>
        <div class="kpi-card is-accent"><div class="label">Enlaces Utilizados</div><div class="value"><?php echo number_format($kpi_links_unique); ?></div></div>
    </section>

    <section class="card-table" style="margin-bottom:20px;">
        <div class="card-title">
            Preferencias personales
            <span class="pill pill-link"><?php echo number_format($preferenceUsersTotal); ?> usuario(s)</span>
        </div>
        <div class="content-grid-wide">
            <div>
                <h3 style="margin:0 0 10px 0;">Menu principal</h3>
                <table class="table-sortable">
                    <thead><tr><th data-sort-type="text">Preferencia</th><th data-sort-type="text">Descripcion</th><th data-sort-type="num">Usuarios</th></tr></thead>
                    <tbody>
                        <?php foreach ($preferenceStats['menu'] as $row): ?>
                            <tr>
                                <td><code><?php echo h((string)$row['key']); ?></code></td>
                                <td><?php echo h((string)$row['label']); ?></td>
                                <td><?php echo number_format((int)$row['total']); ?></td>
                            </tr>
                        <?php endforeach; ?>
                        <?php if (empty($preferenceStats['menu'])): ?>
                            <tr><td colspan="3" style="color:var(--text-muted);">No hay preferencias de menu guardadas.</td></tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>

            <div>
                <h3 style="margin:0 0 10px 0;">Tema visual</h3>
                <table class="table-sortable">
                    <thead><tr><th data-sort-type="text">Preferencia</th><th data-sort-type="text">Descripcion</th><th data-sort-type="num">Usuarios</th></tr></thead>
                    <tbody>
                        <?php foreach ($preferenceStats['theme'] as $row): ?>
                            <tr>
                                <td><code><?php echo h((string)$row['key']); ?></code></td>
                                <td><?php echo h((string)$row['label']); ?></td>
                                <td><?php echo number_format((int)$row['total']); ?></td>
                            </tr>
                        <?php endforeach; ?>
                        <?php if (empty($preferenceStats['theme'])): ?>
                            <tr><td colspan="3" style="color:var(--text-muted);">No hay preferencias de tema guardadas.</td></tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>

            <div>
                <h3 style="margin:0 0 10px 0;">Sesion</h3>
                <table class="table-sortable">
                    <thead><tr><th data-sort-type="text">Preferencia</th><th data-sort-type="text">Descripcion</th><th data-sort-type="num">Usuarios</th></tr></thead>
                    <tbody>
                        <?php foreach ($preferenceStats['session'] as $row): ?>
                            <tr>
                                <td><code><?php echo h((string)$row['key']); ?></code></td>
                                <td><?php echo h((string)$row['label']); ?></td>
                                <td><?php echo number_format((int)$row['total']); ?></td>
                            </tr>
                        <?php endforeach; ?>
                        <?php if (empty($preferenceStats['session'])): ?>
                            <tr><td colspan="3" style="color:var(--text-muted);">No hay preferencias de sesion guardadas.</td></tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </section>

    <!-- RANKINGS -->
    <div class="content-grid-wide">
        <!-- Ranking Usuarios -->
        <div class="card-table">
            <div class="card-title">
                Top Usuarios Intranet
                <a class="pill pill-link" href="stats.php?<?php echo http_build_query(array_merge($_GET, ['export'=>'csv', 'report'=>'intranet_users'])); ?>">CSV</a>
            </div>
            <table class="table-sortable">
                <thead><tr><th data-sort-type="text">Usuario</th><th data-sort-type="num">Total</th><th data-sort-type="date">Ultimo Acceso</th><th data-sort-type="text">Accion</th></tr></thead>
                <tbody>
                    <?php foreach ($rowsUsers as $r): ?>
                        <tr>
                            <td><strong><?php echo h((string)$r['username']); ?></strong></td>
                            <td><?php echo h((string)$r['total']); ?></td>
                            <td><small><?php echo h((string)$r['last_visit_at']); ?></small></td>
                            <td><a class="link-detail" href="stats.php?year=<?php echo $year; ?>&username=<?php echo h((string)$r['username']); ?>">Ver detalle</a></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>

        <!-- Ranking Apps -->
        <div class="card-table">
            <div class="card-title">
                Top Aplicaciones
                <a class="pill pill-link" href="stats.php?<?php echo http_build_query(array_merge($_GET, ['export'=>'csv', 'report'=>'apps_year'])); ?>">CSV</a>
            </div>
            <table class="table-sortable">
                <thead><tr><th data-sort-type="text">Aplicacion</th><th data-sort-type="num">Total</th><th data-sort-type="text">Detalle</th></tr></thead>
                <tbody>
                    <?php foreach ($rowsApps as $r): ?>
                        <tr>
                            <td>
                                <strong><?php echo h(appName((string)$r['app_id'], $appCatalog)); ?></strong><br>
                                <span class="pill"><?php echo h((string)$r['app_id']); ?></span>
                            </td>
                            <td><?php echo h((string)$r['total']); ?></td>
                            <td><a class="link-detail" href="stats.php?year=<?php echo $year; ?>&app_id=<?php echo h((string)$r['app_id']); ?>">Ver Usuarios</a></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>

        <!-- Ranking Links -->
        <div class="card-table">
            <div class="card-title">
                Top Enlaces (Links/NNTT/Novedades)
                <a class="pill pill-link" href="stats.php?<?php echo http_build_query(array_merge($_GET, ['export'=>'csv', 'report'=>'links_year'])); ?>">CSV</a>
            </div>
            <table class="table-sortable">
                <thead><tr><th data-sort-type="text">Enlace</th><th data-sort-type="num">Total</th><th data-sort-type="text">Detalle</th></tr></thead>
                <tbody>
                    <?php foreach ($rowsLinks as $r): ?>
                        <?php $lid = (int)($r['link_id'] ?? 0); ?>
                        <tr>
                            <td>
                                <strong><?php echo h(linkTitle($lid, $linkCatalog)); ?></strong><br>
                                <span class="pill"><?php echo h((string)($r['link_type'] ?? '')); ?></span>
                                <span class="pill">#<?php echo h((string)$lid); ?></span>
                            </td>
                            <td><?php echo h((string)($r['total'] ?? 0)); ?></td>
                            <td><a class="link-detail" href="stats.php?year=<?php echo $year; ?>&link_id=<?php echo h((string)$lid); ?>">Ver Usuarios</a></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>

    <!-- SECCION DE DETALLE -->
    <?php if (!empty($rowsUserApps) || !empty($rowsAppUsers) || !empty($rowsUserLinks) || !empty($rowsLinkUsers) || $rowUserApp || $rowUserLink): ?>
    <section class="card-table stack-20">
        <div class="card-title">
            Detalle de Seleccion: 
            <?php 
                if ($usernameFilter) echo " Usuario [".h($usernameFilter)."]";
                if ($appIdFilter) echo " App [".h(appName($appIdFilter, $appCatalog))."]";
                if ($linkIdFilter && ctype_digit($linkIdFilter)) echo " Enlace [".h(linkTitle((int)$linkIdFilter, $linkCatalog))."]";
            ?>
        </div>
        
        <?php if ($rowUserApp): ?>
            <div class="metric-highlight">
                <p class="label">Clics totales en este anio</p>
                <div class="value"><?php echo h((string)$rowUserApp['total']); ?></div>
                <p><small>Ultima interaccion: <?php echo h((string)$rowUserApp['last_click_at']); ?></small></p>
            </div>
        <?php endif; ?>

        <?php if ($rowUserLink): ?>
            <div class="metric-highlight">
                <p class="label">Clics totales en este anio</p>
                <div class="value"><?php echo h((string)$rowUserLink['total']); ?></div>
                <p><small>Ultima interaccion: <?php echo h((string)$rowUserLink['last_click_at']); ?></small></p>
            </div>
        <?php endif; ?>

        <?php if (!empty($rowsUserApps)): ?>
            <table class="table-sortable">
                <thead><tr><th data-sort-type="text">Aplicacion</th><th data-sort-type="num">Total Clics</th><th data-sort-type="date">Ultimo Clic</th></tr></thead>
                <tbody>
                    <?php foreach ($rowsUserApps as $r): ?>
                        <tr>
                            <td><strong><?php echo h(appName((string)$r['app_id'], $appCatalog)); ?></strong> <span class="pill"><?php echo h((string)$r['app_id']); ?></span></td>
                            <td><?php echo h((string)$r['total']); ?></td>
                            <td><?php echo h((string)$r['last_click_at']); ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        <?php endif; ?>

        <?php if (!empty($rowsUserLinks)): ?>
            <table class="table-sortable">
                <thead><tr><th data-sort-type="text">Enlace</th><th data-sort-type="num">Total Clics</th><th data-sort-type="date">Ultimo Clic</th></tr></thead>
                <tbody>
                    <?php foreach ($rowsUserLinks as $r): ?>
                        <?php $lid = (int)($r['link_id'] ?? 0); ?>
                        <tr>
                            <td><strong><?php echo h(linkTitle($lid, $linkCatalog)); ?></strong> <span class="pill"><?php echo h((string)($r['link_type'] ?? '')); ?></span> <span class="pill">#<?php echo h((string)$lid); ?></span></td>
                            <td><?php echo h((string)($r['total'] ?? 0)); ?></td>
                            <td><?php echo h((string)($r['last_click_at'] ?? '')); ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        <?php endif; ?>

        <?php if (!empty($rowsAppUsers)): ?>
            <table class="table-sortable">
                <thead><tr><th data-sort-type="text">Usuario</th><th data-sort-type="num">Total Clics</th><th data-sort-type="date">Ultimo Clic</th></tr></thead>
                <tbody>
                    <?php foreach ($rowsAppUsers as $r): ?>
                        <tr>
                            <td><strong><?php echo h((string)$r['username']); ?></strong></td>
                            <td><?php echo h((string)$r['total']); ?></td>
                            <td><?php echo h((string)$r['last_click_at']); ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        <?php endif; ?>

        <?php if (!empty($rowsLinkUsers)): ?>
            <table class="table-sortable">
                <thead><tr><th data-sort-type="text">Usuario</th><th data-sort-type="num">Total Clics</th><th data-sort-type="date">Ultimo Clic</th></tr></thead>
                <tbody>
                    <?php foreach ($rowsLinkUsers as $r): ?>
                        <tr>
                            <td><strong><?php echo h((string)$r['username']); ?></strong></td>
                            <td><?php echo h((string)($r['total'] ?? 0)); ?></td>
                            <td><?php echo h((string)($r['last_click_at'] ?? '')); ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        <?php endif; ?>
    </section>
    <?php endif; ?>
    <?php endif; ?>
</div>

<footer>
    <?= h(function_exists('intranet_brand_name_upper') ? intranet_brand_name_upper() : 'INTRANET') ?> | Sistema de Auditoria y Estadisticas | Sesion: <strong><?php echo h($usernameSession); ?></strong>
</footer>

</body>
</html>
