-- Normalizacion global de charset/collation para la base intranet.
-- Fecha: 2026-05-04
--
-- Objetivo:
--   - Charset utf8mb4.
--   - Collation espanola, case-insensitive y accent-insensitive.
--   - Misma collation en todas las tablas para evitar errores en JOIN/WHERE.
--
-- MariaDB 10.11 suele disponer de utf8mb4_es_0900_ai_ci, alias moderno
-- explicito para espanol + accent-insensitive + case-insensitive.
-- Si no existe, se usa utf8mb4_spanish_ci como fallback compatible.

USE `intranet`;

SET @target_charset = 'utf8mb4';

SELECT CASE
         WHEN EXISTS (
           SELECT 1
             FROM information_schema.COLLATIONS
            WHERE COLLATION_NAME = 'utf8mb4_es_0900_ai_ci'
         )
         THEN 'utf8mb4_es_0900_ai_ci'
         ELSE 'utf8mb4_spanish_ci'
       END
  INTO @target_collation;

SELECT CONCAT('Collation objetivo: ', @target_collation) AS info;

DELIMITER $$

DROP PROCEDURE IF EXISTS `_coll_exec`$$
CREATE PROCEDURE `_coll_exec`(IN p_sql TEXT)
BEGIN
  SET @coll_sql = p_sql;
  PREPARE coll_stmt FROM @coll_sql;
  EXECUTE coll_stmt;
  DEALLOCATE PREPARE coll_stmt;
END$$

DROP PROCEDURE IF EXISTS `_coll_normalize_database`$$
CREATE PROCEDURE `_coll_normalize_database`()
BEGIN
  DECLARE v_done TINYINT DEFAULT 0;
  DECLARE v_table VARCHAR(128);
  DECLARE cur CURSOR FOR
    SELECT TABLE_NAME
      FROM information_schema.TABLES
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_TYPE = 'BASE TABLE'
     ORDER BY TABLE_NAME;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

  CALL `_coll_exec`(
    CONCAT(
      'ALTER DATABASE `', REPLACE(DATABASE(), '`', '``'),
      '` CHARACTER SET utf8mb4 COLLATE ', @target_collation
    )
  );

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_table;
    IF v_done = 1 THEN
      LEAVE read_loop;
    END IF;

    CALL `_coll_exec`(
      CONCAT(
        'ALTER TABLE `', REPLACE(v_table, '`', '``'),
        '` CONVERT TO CHARACTER SET utf8mb4 COLLATE ', @target_collation
      )
    );
  END LOOP;
  CLOSE cur;
END$$

DELIMITER ;

CALL `_coll_normalize_database`();

DROP PROCEDURE IF EXISTS `_coll_normalize_database`;
DROP PROCEDURE IF EXISTS `_coll_exec`;

-- Informe final: si devuelve filas, aun quedan columnas de texto con otra collation.
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
  FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA = DATABASE()
   AND CHARACTER_SET_NAME IS NOT NULL
   AND (
        CHARACTER_SET_NAME <> @target_charset
        OR COLLATION_NAME <> @target_collation
   )
 ORDER BY TABLE_NAME, ORDINAL_POSITION;

-- Prueba rapida de comportamiento esperado.
SET @probe_sql = CONCAT(
  'SELECT ',
  '_utf8mb4''A'' COLLATE ', @target_collation,
  ' = _utf8mb4''a'' COLLATE ', @target_collation,
  ' AS case_insensitive, ',
  'CONVERT(0xC3A1 USING utf8mb4) COLLATE ', @target_collation,
  ' = _utf8mb4''a'' COLLATE ', @target_collation,
  ' AS accent_insensitive'
);
PREPARE probe_stmt FROM @probe_sql;
EXECUTE probe_stmt;
DEALLOCATE PREPARE probe_stmt;
