-- Normalizacion de charset/collation para DESARROLLO.
-- Fecha: 2026-05-04
--
-- Uso:
--   Ejecutar este script en el servidor de desarrollo.
--
-- La intranet usa obligatoriamente la base de datos `intranet`.

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('Base objetivo: ', COALESCE(DATABASE(), '(ninguna)')) AS database_info,
       CONCAT('Collation objetivo: ', @target_collation) AS collation_info;

DELIMITER $$

DROP PROCEDURE IF EXISTS `_coll_dev_exec`$$
CREATE PROCEDURE `_coll_dev_exec`(IN p_sql TEXT)
BEGIN
  SET @coll_dev_sql = p_sql;
  PREPARE coll_dev_stmt FROM @coll_dev_sql;
  EXECUTE coll_dev_stmt;
  DEALLOCATE PREPARE coll_dev_stmt;
END$$

DROP PROCEDURE IF EXISTS `_coll_dev_normalize_database`$$
CREATE PROCEDURE `_coll_dev_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;

  IF DATABASE() IS NULL THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Selecciona primero la base de datos de desarrollo con USE.';
  END IF;

  CALL `_coll_dev_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_dev_exec`(
      CONCAT(
        'ALTER TABLE `', REPLACE(v_table, '`', '``'),
        '` CONVERT TO CHARACTER SET utf8mb4 COLLATE ', @target_collation
      )
    );
  END LOOP;
  CLOSE cur;
END$$

DELIMITER ;

CALL `_coll_dev_normalize_database`();

DROP PROCEDURE IF EXISTS `_coll_dev_normalize_database`;
DROP PROCEDURE IF EXISTS `_coll_dev_exec`;

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;

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;
