<?php
/**
 * @intranet-file common/catalogo_db.php
 * @intranet-file-version 2026.03.13.1
 * @intranet-file-date 2026-03-13
 * @intranet-file-owner CSIF Intranet
 */
declare(strict_types=1);

/**
 * Catálogo DB (Apps + Links)
 *
 * Sustituye el modelo antiguo basado en ficheros JSON.
 *
 * Tablas esperadas:
 *  - intranet_apps
 *  - intranet_links
 */

require_once __DIR__ . '/db.php';

function catalogo_app_group_key(string $groupName): string
{
    $name = trim($groupName);
    if ($name === '') {
        return '';
    }

    $name = function_exists('mb_strtolower')
        ? mb_strtolower($name, 'UTF-8')
        : strtolower($name);
    $name = strtr($name, [
        'á' => 'a', 'à' => 'a', 'ä' => 'a', 'â' => 'a',
        'é' => 'e', 'è' => 'e', 'ë' => 'e', 'ê' => 'e',
        'í' => 'i', 'ì' => 'i', 'ï' => 'i', 'î' => 'i',
        'ó' => 'o', 'ò' => 'o', 'ö' => 'o', 'ô' => 'o',
        'ú' => 'u', 'ù' => 'u', 'ü' => 'u', 'û' => 'u',
        'ñ' => 'n',
        'Ã¡' => 'a', 'Ã©' => 'e', 'Ã­' => 'i', 'Ã³' => 'o', 'Ãº' => 'u', 'Ã¼' => 'u', 'Ã±' => 'n',
        'ã¡' => 'a', 'ã©' => 'e', 'ã­' => 'i', 'ã³' => 'o', 'ãº' => 'u', 'ã¼' => 'u', 'ã±' => 'n',
    ]);
    $name = preg_replace('/\s+/u', ' ', $name) ?? $name;
    return trim($name);
}

function catalogo_normalize_app_group_name($groupName): string
{
    $name = trim((string)$groupName);
    if ($name === '') {
        return 'Mis apps';
    }

    $key = catalogo_app_group_key($name);
    $known = [
        'administracion' => 'Administración',
        'general' => 'Generales',
        'generales' => 'Generales',
        'mis aplicaciones' => 'Mis apps',
        'mis apps' => 'Mis apps',
    ];

    return $known[$key] ?? $name;
}

function catalogo_group_types(): array
{
    return ['links', 'novedades', 'nntt', 'apps'];
}

function catalogo_normalize_group_type(string $groupType): string
{
    $groupType = trim($groupType);
    if ($groupType === 'aplicaciones') {
        $groupType = 'apps';
    }

    if (!in_array($groupType, catalogo_group_types(), true)) {
        throw new InvalidArgumentException('Tipo de grupo invalido.');
    }

    return $groupType;
}

function catalogo_default_group_name(string $groupType): string
{
    $groupType = catalogo_normalize_group_type($groupType);
    return $groupType === 'apps' ? 'Mis apps' : 'General';
}

function catalogo_normalize_group_name($groupName, string $groupType): string
{
    $groupType = catalogo_normalize_group_type($groupType);
    if ($groupType === 'apps') {
        return catalogo_normalize_app_group_name($groupName);
    }

    $name = trim((string)$groupName);
    $name = preg_replace('/\s+/u', ' ', $name) ?? $name;
    return trim($name) !== '' ? trim($name) : 'General';
}

function catalogo_ensure_legacy_app_groups_table(PDO $pdo): void
{
    $pdo->exec(
        "CREATE TABLE IF NOT EXISTS intranet_app_groups (
            id int unsigned NOT NULL AUTO_INCREMENT,
            group_name varchar(100) NOT NULL,
            icon_svg mediumtext NULL,
            position int unsigned NOT NULL DEFAULT 0,
            created_at timestamp NULL DEFAULT current_timestamp(),
            updated_at timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
            PRIMARY KEY (id),
            UNIQUE KEY uq_group_name (group_name)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci"
    );

    $groupIconColumnCheck = $pdo->query("SHOW COLUMNS FROM intranet_app_groups LIKE 'icon_svg'");
    $hasGroupIconSvg = $groupIconColumnCheck !== false && $groupIconColumnCheck->fetch(PDO::FETCH_ASSOC);
    if (!$hasGroupIconSvg) {
        $pdo->exec("ALTER TABLE intranet_app_groups ADD COLUMN icon_svg mediumtext NULL AFTER group_name");
    }
}

function catalogo_normalize_existing_app_groups(PDO $pdo): void
{
    $names = [];
    foreach (['intranet_apps', 'intranet_app_groups'] as $table) {
        $st = $pdo->query("SELECT DISTINCT group_name FROM $table WHERE group_name IS NOT NULL AND TRIM(group_name) <> ''");
        if ($st === false) {
            continue;
        }
        while ($row = $st->fetch(PDO::FETCH_ASSOC)) {
            $name = trim((string)($row['group_name'] ?? ''));
            if ($name !== '') {
                $names[$name] = true;
            }
        }
    }

    foreach (array_keys($names) as $name) {
        $canonical = catalogo_normalize_app_group_name($name);
        if ($canonical === $name) {
            continue;
        }

        $stApps = $pdo->prepare('UPDATE intranet_apps SET group_name = :canonical WHERE group_name = :old');
        $stApps->execute(['canonical' => $canonical, 'old' => $name]);

        $stCanonical = $pdo->prepare('SELECT id, icon_svg FROM intranet_app_groups WHERE group_name = :g LIMIT 1');
        $stCanonical->execute(['g' => $canonical]);
        $canonicalRow = $stCanonical->fetch(PDO::FETCH_ASSOC) ?: null;

        $stOld = $pdo->prepare('SELECT id, icon_svg FROM intranet_app_groups WHERE group_name = :g LIMIT 1');
        $stOld->execute(['g' => $name]);
        $oldRow = $stOld->fetch(PDO::FETCH_ASSOC) ?: null;
        if (!is_array($oldRow)) {
            continue;
        }

        if (is_array($canonicalRow) && (int)$canonicalRow['id'] === (int)$oldRow['id']) {
            $stRename = $pdo->prepare('UPDATE intranet_app_groups SET group_name = :canonical WHERE id = :id');
            $stRename->execute(['canonical' => $canonical, 'id' => (int)$oldRow['id']]);
            continue;
        }

        if (!is_array($canonicalRow)) {
            $stRename = $pdo->prepare('UPDATE intranet_app_groups SET group_name = :canonical WHERE id = :id');
            $stRename->execute(['canonical' => $canonical, 'id' => (int)$oldRow['id']]);
            continue;
        }

        $oldIcon = trim((string)($oldRow['icon_svg'] ?? ''));
        $canonicalIcon = trim((string)($canonicalRow['icon_svg'] ?? ''));
        if ($canonicalIcon === '' && $oldIcon !== '') {
            $stIcon = $pdo->prepare('UPDATE intranet_app_groups SET icon_svg = :icon_svg WHERE id = :id');
            $stIcon->execute(['icon_svg' => $oldIcon, 'id' => (int)$canonicalRow['id']]);
        }

        $stDelete = $pdo->prepare('DELETE FROM intranet_app_groups WHERE id = :id');
        $stDelete->execute(['id' => (int)$oldRow['id']]);
    }
}

function catalogo_schema_ensure(PDO $pdo): void
{
    static $done = false;
    if ($done) {
        return;
    }

    catalogo_ensure_legacy_app_groups_table($pdo);

    $pdo->exec(
        "CREATE TABLE IF NOT EXISTS intranet_catalog_groups (
            id int unsigned NOT NULL AUTO_INCREMENT,
            group_type varchar(32) NOT NULL,
            group_name varchar(255) NOT NULL,
            icon_svg mediumtext NULL,
            position int unsigned NOT NULL DEFAULT 0,
            created_at timestamp NULL DEFAULT current_timestamp(),
            updated_at timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
            PRIMARY KEY (id),
            UNIQUE KEY uq_group_type_name (group_type, group_name),
            KEY idx_group_type_position (group_type, position)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci"
    );

    $columnCheck = $pdo->query("SHOW COLUMNS FROM intranet_links LIKE 'icon_svg'");
    $hasIconSvg = $columnCheck !== false && $columnCheck->fetch(PDO::FETCH_ASSOC);
    if (!$hasIconSvg) {
        $pdo->exec("ALTER TABLE intranet_links ADD COLUMN icon_svg mediumtext NULL AFTER imagen");
    }

    catalogo_normalize_existing_app_groups($pdo);

    $nextPositions = [];
    $nextPosition = static function (string $groupType) use ($pdo, &$nextPositions): int {
        if (!isset($nextPositions[$groupType])) {
            $stMax = $pdo->prepare("SELECT COALESCE(MAX(position), 0) FROM intranet_catalog_groups WHERE group_type = :t");
            $stMax->execute(['t' => $groupType]);
            $nextPositions[$groupType] = (int)($stMax->fetchColumn() ?: 0) + 1;
        }
        return $nextPositions[$groupType]++;
    };

    $seedGroup = static function (string $groupType, string $groupName, ?string $iconSvg = null, ?int $position = null) use ($pdo, $nextPosition): void {
        $groupType = catalogo_normalize_group_type($groupType);
        $groupName = catalogo_normalize_group_name($groupName, $groupType);
        $iconSvg = catalogo_normalize_svg_markup($iconSvg);
        $position = $position !== null && $position > 0 ? $position : $nextPosition($groupType);

        $ins = $pdo->prepare("INSERT IGNORE INTO intranet_catalog_groups (group_type, group_name, icon_svg, position) VALUES (:t, :g, :icon_svg, :p)");
        $ins->execute([
            't' => $groupType,
            'g' => $groupName,
            'icon_svg' => $iconSvg,
            'p' => $position,
        ]);

        if ($iconSvg !== null && trim($iconSvg) !== '') {
            $up = $pdo->prepare("UPDATE intranet_catalog_groups SET icon_svg = :icon_svg WHERE group_type = :t AND group_name = :g AND (icon_svg IS NULL OR TRIM(icon_svg) = '')");
            $up->execute(['icon_svg' => $iconSvg, 't' => $groupType, 'g' => $groupName]);
        }
    };

    $stLegacyGroups = $pdo->query("SELECT group_name, icon_svg, position FROM intranet_app_groups WHERE group_name IS NOT NULL AND TRIM(group_name) <> '' ORDER BY position ASC, group_name ASC");
    if ($stLegacyGroups !== false) {
        while ($row = $stLegacyGroups->fetch(PDO::FETCH_ASSOC)) {
            $seedGroup('apps', (string)($row['group_name'] ?? 'Mis apps'), $row['icon_svg'] ?? null, (int)($row['position'] ?? 0));
        }
    }

    $stAppGroups = $pdo->query("SELECT DISTINCT COALESCE(NULLIF(TRIM(group_name), ''), 'Mis apps') AS group_name FROM intranet_apps ORDER BY group_name ASC");
    if ($stAppGroups !== false) {
        while ($row = $stAppGroups->fetch(PDO::FETCH_ASSOC)) {
            $seedGroup('apps', (string)($row['group_name'] ?? 'Mis apps'));
        }
    }

    $stLinkGroups = $pdo->query("SELECT link_type, COALESCE(NULLIF(TRIM(grupo), ''), 'General') AS group_name FROM intranet_links GROUP BY link_type, group_name ORDER BY link_type ASC, group_name ASC");
    if ($stLinkGroups !== false) {
        while ($row = $stLinkGroups->fetch(PDO::FETCH_ASSOC)) {
            $linkType = (string)($row['link_type'] ?? '');
            if (in_array($linkType, ['links', 'novedades', 'nntt'], true)) {
                $seedGroup($linkType, (string)($row['group_name'] ?? 'General'));
            }
        }
    }

    foreach (catalogo_group_types() as $groupType) {
        $stCount = $pdo->prepare("SELECT COUNT(*) FROM intranet_catalog_groups WHERE group_type = :t");
        $stCount->execute(['t' => $groupType]);
        if ((int)($stCount->fetchColumn() ?: 0) === 0) {
            $seedGroup($groupType, catalogo_default_group_name($groupType), null, 1);
        }
    }

    $stMirror = $pdo->query("SELECT group_name, icon_svg, position FROM intranet_catalog_groups WHERE group_type = 'apps' ORDER BY position ASC, group_name ASC");
    if ($stMirror !== false) {
        $mirror = $pdo->prepare("INSERT IGNORE INTO intranet_app_groups (group_name, icon_svg, position) VALUES (:g, :icon_svg, :p)");
        while ($row = $stMirror->fetch(PDO::FETCH_ASSOC)) {
            $mirror->execute([
                'g' => catalogo_normalize_app_group_name($row['group_name'] ?? 'Mis apps'),
                'icon_svg' => catalogo_normalize_svg_markup($row['icon_svg'] ?? null),
                'p' => (int)($row['position'] ?? 0),
            ]);
        }
    }

    $done = true;
}

function catalogo_is_admin(): bool
{
    $roles = $_SESSION['roles'] ?? [];
    $rolesLower = array_map('strtolower', is_array($roles) ? $roles : []);
    return in_array('administradores', $rolesLower, true);
}

/**
 * CSRF
 */
function catalogo_csrf_token(string $key = 'catalogo_csrf'): string
{
    if (empty($_SESSION[$key]) || !is_string($_SESSION[$key])) {
        $_SESSION[$key] = bin2hex(random_bytes(16));
    }
    return (string)$_SESSION[$key];
}

function catalogo_csrf_validate(?string $token, string $key = 'catalogo_csrf'): void
{
    $expected = $_SESSION[$key] ?? '';
    if (!is_string($token) || $token === '' || !is_string($expected) || $expected === '' || !hash_equals($expected, $token)) {
        throw new RuntimeException('Token CSRF inválido. Recarga la página e inténtalo de nuevo.');
    }
}

/**
 * Devuelve lista de imágenes disponibles en /styles/images para selector.
 */
function catalogo_list_images(string $stylesImagesDir): array
{
    $files = [];
    if (!is_dir($stylesImagesDir)) {
        return $files;
    }
    foreach (scandir($stylesImagesDir) as $f) {
        if ($f === '.' || $f === '..') continue;
        $path = $stylesImagesDir . DIRECTORY_SEPARATOR . $f;
        if (!is_file($path)) continue;
        if (!preg_match('/\.(png|jpg|jpeg|webp|svg)$/i', $f)) continue;
        $files[] = '/images/' . $f;
    }
    sort($files, SORT_NATURAL | SORT_FLAG_CASE);
    return $files;
}

function catalogo_is_svg_markup(?string $svg): bool
{
    $svg = trim((string)$svg);
    return $svg !== '' && stripos($svg, '<svg') !== false;
}

function catalogo_normalize_svg_markup(?string $svg): ?string
{
    $svg = trim((string)$svg);
    if ($svg === '') {
        return null;
    }
    return catalogo_is_svg_markup($svg) ? $svg : null;
}

function catalogo_image_src(?string $image): string
{
    $image = trim((string)$image);
    if ($image === '') {
        return '/styles/images/default.png';
    }
    if (preg_match('~^(?:https?:)?//~i', $image)) {
        return $image;
    }
    if (strpos($image, '/styles/') === 0) {
        return $image;
    }
    if (strpos($image, '/images/') === 0) {
        return '/styles' . $image;
    }
    if (strpos($image, 'styles/') === 0) {
        return '/' . ltrim($image, '/');
    }
    return '/styles/' . ltrim($image, '/');
}

function catalogo_link_media_markup(?string $image, ?string $iconSvg, string $alt = 'Icono'): string
{
    $svg = catalogo_normalize_svg_markup($iconSvg);
    if ($svg !== null) {
        return '<span class="catalogo-svg-icon" aria-hidden="true" style="display:inline-flex; width:72px; height:72px; align-items:center; justify-content:center;">' . $svg . '</span>';
    }

    $src = htmlspecialchars(catalogo_image_src($image), ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
    $alt = htmlspecialchars($alt, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
    return '<img src="' . $src . '" alt="' . $alt . '">';
}

/**
 * Roles helpers
 */
function catalogo_roles_from_json(?string $rolesJson): array
{
    $rolesJson = is_string($rolesJson) ? trim($rolesJson) : '';
    if ($rolesJson === '') return [];

    if (function_exists('intranet_roles_normalize')) {
        return intranet_roles_normalize($rolesJson);
    }

    $decoded = json_decode($rolesJson, true);
    if (is_array($decoded)) {
        $roles = [];
        foreach ($decoded as $r) {
            if (!is_string($r)) continue;
            $r = trim($r);
            if ($r !== '') $roles[] = $r;
        }
        return array_values(array_unique($roles));
    }

    // Fallback: CSV/espacios
    return catalogo_roles_parse_csv($rolesJson);
}

function catalogo_roles_parse_csv(string $csv): array
{
    if (function_exists('intranet_roles_normalize')) {
        return intranet_roles_normalize($csv);
    }

    $parts = preg_split('/[\s,;]+/', $csv, -1, PREG_SPLIT_NO_EMPTY);
    $roles = [];
    foreach ($parts as $p) {
        $p = trim((string)$p);
        if ($p !== '') $roles[] = $p;
    }
    return array_values(array_unique($roles));
}

function catalogo_roles_to_json(array $roles): string
{
    if (function_exists('intranet_roles_normalize')) {
        $roles = intranet_roles_normalize($roles);
    } else {
        $roles = array_values(array_unique(array_filter(array_map(function ($r) {
            return is_string($r) ? trim($r) : '';
        }, $roles), function ($r) {
            return $r !== '';
        })));
    }

    $json = json_encode($roles, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    if ($json === false) {
        throw new RuntimeException('No se pudieron serializar los roles a JSON.');
    }
    return $json;
}

function catalogo_roles_allow(array $userRolesLower, array $requiredRoles): bool
{
    $reqLower = array_map('strtolower', $requiredRoles);

    // '*' o 'all' => visible para cualquiera autenticado
    if (in_array('*', $reqLower, true) || in_array('all', $reqLower, true)) {
        return true;
    }

    $intersection = array_intersect($userRolesLower, $reqLower);
    return !empty($intersection);
}

/**
 * Apps
 */
function catalogo_apps_fetch(PDO $pdo, bool $onlyEnabled = true): array
{
    catalogo_schema_ensure($pdo);

    $sql = "SELECT a.id, a.app_id, a.name, a.url, a.group_name, a.description, a.icon, a.image, a.new_tab, a.roles_json, a.position, a.is_enabled, a.created_at, a.updated_at,
                   g.id AS group_id, g.position AS group_position, g.icon_svg AS group_icon_svg
            FROM intranet_apps a
            LEFT JOIN intranet_catalog_groups g
              ON CONVERT(g.group_type USING utf8mb4) COLLATE utf8mb4_spanish_ci = _utf8mb4'apps' COLLATE utf8mb4_spanish_ci
             AND CONVERT(g.group_name USING utf8mb4) COLLATE utf8mb4_spanish_ci = CONVERT(a.group_name USING utf8mb4) COLLATE utf8mb4_spanish_ci";
    if ($onlyEnabled) {
        $sql .= " WHERE a.is_enabled = 1";
    }
    $sql .= " ORDER BY COALESCE(g.position, 999999) ASC, a.group_name ASC, a.position ASC, a.name ASC";

    $st = $pdo->query($sql);
    return $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
}

function catalogo_app_get(PDO $pdo, string $appId): ?array
{
    $st = $pdo->prepare("SELECT id, app_id, name, url, group_name, description, icon, image, new_tab, roles_json, position, is_enabled
                         FROM intranet_apps
                         WHERE app_id = :app_id
                         LIMIT 1");
    $st->execute(['app_id' => $appId]);
    $row = $st->fetch(PDO::FETCH_ASSOC);
    return is_array($row) ? $row : null;
}

function catalogo_app_find_by_script(PDO $pdo, string $currentScript): ?array
{
    $currentScript = trim(str_replace('\\', '/', $currentScript), '/');
    if ($currentScript === '') {
        return null;
    }

    $st = $pdo->query("SELECT app_id, name, url, roles_json FROM intranet_apps WHERE is_enabled = 1 ORDER BY CHAR_LENGTH(url) DESC, id ASC");
    $rows = $st ? ($st->fetchAll(PDO::FETCH_ASSOC) ?: []) : [];

    foreach ($rows as $row) {
        $url = trim(str_replace('\\', '/', (string)($row['url'] ?? '')), '/');
        if ($url === '') {
            continue;
        }

        $cleanUrl = trim(str_replace('index.php', '', $url), '/');
        if ($cleanUrl !== '' && strpos($currentScript, $cleanUrl) !== false) {
            return $row;
        }

        if ($url !== '' && $currentScript === $url) {
            return $row;
        }
    }

    return null;
}

function catalogo_next_app_position(PDO $pdo, string $groupName): int
{
    catalogo_schema_ensure($pdo);
    $groupName = catalogo_normalize_app_group_name($groupName);
    $st = $pdo->prepare("SELECT COALESCE(MAX(position), 0) + 1 FROM intranet_apps WHERE group_name = :g");
    $st->execute(['g' => $groupName]);
    return (int)($st->fetchColumn() ?: 1);
}

function catalogo_groups_fetch(PDO $pdo, string $groupType): array
{
    catalogo_schema_ensure($pdo);
    $groupType = catalogo_normalize_group_type($groupType);
    $st = $pdo->prepare("SELECT id, group_type, group_name, icon_svg, position, created_at, updated_at FROM intranet_catalog_groups WHERE group_type = :t ORDER BY position ASC, group_name ASC");
    $st->execute(['t' => $groupType]);
    return $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
}

function catalogo_group_exists(PDO $pdo, string $groupType, string $groupName): bool
{
    catalogo_schema_ensure($pdo);
    $groupType = catalogo_normalize_group_type($groupType);
    $groupName = catalogo_normalize_group_name($groupName, $groupType);
    $st = $pdo->prepare("SELECT id FROM intranet_catalog_groups WHERE group_type = :t AND group_name = :g LIMIT 1");
    $st->execute(['t' => $groupType, 'g' => $groupName]);
    return (bool)$st->fetch(PDO::FETCH_ASSOC);
}

function catalogo_next_group_position(PDO $pdo, string $groupType): int
{
    catalogo_schema_ensure($pdo);
    $groupType = catalogo_normalize_group_type($groupType);
    $st = $pdo->prepare("SELECT COALESCE(MAX(position), 0) + 1 FROM intranet_catalog_groups WHERE group_type = :t");
    $st->execute(['t' => $groupType]);
    return (int)($st->fetchColumn() ?: 1);
}

function catalogo_ensure_group(PDO $pdo, string $groupType, $groupName, ?string $iconSvg = null): string
{
    catalogo_schema_ensure($pdo);
    $groupType = catalogo_normalize_group_type($groupType);
    $groupName = catalogo_normalize_group_name($groupName, $groupType);
    $iconSvg = catalogo_normalize_svg_markup($iconSvg);
    $position = catalogo_next_group_position($pdo, $groupType);

    $st = $pdo->prepare("INSERT IGNORE INTO intranet_catalog_groups (group_type, group_name, icon_svg, position) VALUES (:t, :g, :icon_svg, :p)");
    $st->execute([
        't' => $groupType,
        'g' => $groupName,
        'icon_svg' => $iconSvg,
        'p' => $position,
    ]);

    if ($groupType === 'apps') {
        $legacy = $pdo->prepare("INSERT IGNORE INTO intranet_app_groups (group_name, icon_svg, position) VALUES (:g, :icon_svg, :p)");
        $legacy->execute(['g' => $groupName, 'icon_svg' => $iconSvg, 'p' => $position]);
    }

    return $groupName;
}

function catalogo_group_usage_count(PDO $pdo, string $groupType, string $groupName): int
{
    catalogo_schema_ensure($pdo);
    $groupType = catalogo_normalize_group_type($groupType);
    $groupName = catalogo_normalize_group_name($groupName, $groupType);

    if ($groupType === 'apps') {
        $st = $pdo->prepare("SELECT COUNT(*) FROM intranet_apps WHERE group_name = :g");
        $st->execute(['g' => $groupName]);
        return (int)($st->fetchColumn() ?: 0);
    }

    $st = $pdo->prepare("SELECT COUNT(*) FROM intranet_links WHERE link_type = :t AND grupo = :g");
    $st->execute(['t' => $groupType, 'g' => $groupName]);
    return (int)($st->fetchColumn() ?: 0);
}

function catalogo_rename_group_items(PDO $pdo, string $groupType, string $oldName, string $newName): void
{
    catalogo_schema_ensure($pdo);
    $groupType = catalogo_normalize_group_type($groupType);
    $oldName = catalogo_normalize_group_name($oldName, $groupType);
    $newName = catalogo_normalize_group_name($newName, $groupType);

    if ($oldName === $newName) {
        return;
    }

    if ($groupType === 'apps') {
        $st = $pdo->prepare("UPDATE intranet_apps SET group_name = :new_name WHERE group_name = :old_name");
        $st->execute(['new_name' => $newName, 'old_name' => $oldName]);

        $legacy = $pdo->prepare("UPDATE intranet_app_groups SET group_name = :new_name WHERE group_name = :old_name");
        $legacy->execute(['new_name' => $newName, 'old_name' => $oldName]);
        return;
    }

    $st = $pdo->prepare("UPDATE intranet_links SET grupo = :new_name WHERE link_type = :t AND grupo = :old_name");
    $st->execute(['new_name' => $newName, 't' => $groupType, 'old_name' => $oldName]);
}

function catalogo_app_groups_fetch(PDO $pdo): array
{
    return catalogo_groups_fetch($pdo, 'apps');
}

function catalogo_next_app_group_position(PDO $pdo): int
{
    return catalogo_next_group_position($pdo, 'apps');
}

/**
 * Links
 */
function catalogo_links_fetch(PDO $pdo, string $type, bool $onlyEnabled = true): array
{
    catalogo_schema_ensure($pdo);

    $sql = "SELECT l.id, l.link_type, l.grupo, l.descripcion, l.destino, l.imagen, l.icon_svg, l.aviso, l.position, l.is_enabled, l.created_at, l.updated_at,
                   g.id AS group_id, g.position AS group_position, g.icon_svg AS group_icon_svg
            FROM intranet_links l
            LEFT JOIN intranet_catalog_groups g
              ON CONVERT(g.group_type USING utf8mb4) COLLATE utf8mb4_spanish_ci = CONVERT(l.link_type USING utf8mb4) COLLATE utf8mb4_spanish_ci
             AND CONVERT(g.group_name USING utf8mb4) COLLATE utf8mb4_spanish_ci = CONVERT(l.grupo USING utf8mb4) COLLATE utf8mb4_spanish_ci
            WHERE l.link_type = :t";
    if ($onlyEnabled) {
        $sql .= " AND l.is_enabled = 1";
    }
    $sql .= " ORDER BY COALESCE(g.position, 999999) ASC, l.position ASC, l.id ASC";

    $st = $pdo->prepare($sql);
    $st->execute(['t' => $type]);
    return $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
}

function catalogo_next_link_position(PDO $pdo, string $type): int
{
    $st = $pdo->prepare("SELECT COALESCE(MAX(position), 0) + 1 FROM intranet_links WHERE link_type = :t");
    $st->execute(['t' => $type]);
    return (int)($st->fetchColumn() ?: 1);
}

/**
 * Agrupa enlaces por 'grupo' manteniendo el orden de primera aparición en el array.
 * Devuelve: [ ['name' => 'Grupo', 'links' => [...]], ... ]
 */
function catalogo_links_grouped(array $links): array
{
    $groups = [];
    $icons = [];
    $order = [];
    $i = 0;

    foreach ($links as $row) {
        if (!is_array($row)) continue;
        $g = trim((string)($row['grupo'] ?? 'General'));
        if ($g === '') $g = 'General';
        if (!array_key_exists($g, $groups)) {
            $groups[$g] = [];
            $order[$g] = $i++;
        }
        if (!isset($icons[$g])) {
            $icons[$g] = catalogo_normalize_svg_markup($row['group_icon_svg'] ?? null) ?? '';
        }
        $groups[$g][] = $row;
    }

    $names = array_keys($groups);
    usort($names, function ($a, $b) use ($order) {
        if ($a === 'General' && $b !== 'General') return -1;
        if ($b === 'General' && $a !== 'General') return 1;
        return ($order[$a] ?? 0) <=> ($order[$b] ?? 0);
    });

    $out = [];
    foreach ($names as $name) {
        $out[] = ['name' => $name, 'icon_svg' => $icons[$name] ?? '', 'links' => $groups[$name]];
    }
    return $out;
}
