-- Hotfix: alinear collations del catalogo de apps/enlaces
-- Fecha: 2026-05-04
--
-- Soluciona errores MariaDB como:
--   Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT)
--   and (utf8mb4_general_ci,IMPLICIT) for operation '='
--
-- Causa detectada:
--   common/catalogo_db.php compara intranet_catalog_groups con intranet_links:
--     g.group_type = l.link_type AND g.group_name = l.grupo
--   intranet_catalog_groups esta en utf8mb4_unicode_ci y intranet_links estaba
--   en utf8mb4_general_ci.
--
-- Nota: para normalizar toda la base, usar normalizar_collation_es.sql.

SET NAMES utf8mb4;
USE `intranet`;

ALTER TABLE `intranet`.`intranet_apps`
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_spanish_ci;

ALTER TABLE `intranet`.`intranet_app_groups`
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_spanish_ci;

ALTER TABLE `intranet`.`intranet_catalog_groups`
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_spanish_ci;

ALTER TABLE `intranet`.`intranet_links`
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_spanish_ci;

-- Comprobacion opcional: estas columnas deben quedar en utf8mb4_spanish_ci.
SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
  FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA = 'intranet'
   AND TABLE_NAME IN (
     'intranet_apps',
     'intranet_app_groups',
     'intranet_catalog_groups',
     'intranet_links'
   )
   AND COLUMN_NAME IN (
     'app_id',
     'group_name',
     'group_type',
     'grupo',
     'link_type'
   )
 ORDER BY TABLE_NAME, COLUMN_NAME;

-- Comprobacion opcional: esta consulta debe ejecutarse sin error.
SELECT COUNT(*) AS nntt_links_ok
  FROM `intranet`.`intranet_links` l
  LEFT JOIN `intranet`.`intranet_catalog_groups` g
    ON g.`group_type` = l.`link_type`
   AND g.`group_name` = l.`grupo`
 WHERE l.`link_type` = 'nntt';
