-- Migracion intranet-prod1.sql -> intranet-desa2.sql
-- Fecha de preparacion: 2026-05-04
--
-- Criterio aplicado:
--   - Desarrollo manda para el esquema general.
--   - En el modulo entregas_ manda produccion actual, por indicacion expresa.
--   - No se eliminan columnas/tablas que el codigo actual del repo sigue usando
--     aunque no aparezcan en el dump de desarrollo.
--
-- Requisitos: MariaDB 10.11.x.
-- Importante: muchas operaciones DDL hacen commit implicito. Ejecutar primero
-- sobre una copia o con backup verificado.

USE `intranet`;

DELIMITER $$

DROP PROCEDURE IF EXISTS `_mig_exec`$$
CREATE PROCEDURE `_mig_exec`(IN p_sql TEXT)
BEGIN
  SET @mig_sql = p_sql;
  PREPARE mig_stmt FROM @mig_sql;
  EXECUTE mig_stmt;
  DEALLOCATE PREPARE mig_stmt;
END$$

DROP PROCEDURE IF EXISTS `_mig_drop_fk_if_exists`$$
CREATE PROCEDURE `_mig_drop_fk_if_exists`(IN p_table VARCHAR(64), IN p_fk VARCHAR(64))
BEGIN
  IF EXISTS (
    SELECT 1
      FROM information_schema.TABLE_CONSTRAINTS
     WHERE CONSTRAINT_SCHEMA = DATABASE()
       AND TABLE_NAME = p_table
       AND CONSTRAINT_NAME = p_fk
       AND CONSTRAINT_TYPE = 'FOREIGN KEY'
  ) THEN
    CALL `_mig_exec`(
      CONCAT(
        'ALTER TABLE `', REPLACE(p_table, '`', '``'),
        '` DROP FOREIGN KEY `', REPLACE(p_fk, '`', '``'), '`'
      )
    );
  END IF;
END$$

DROP PROCEDURE IF EXISTS `_mig_drop_key_if_exists`$$
CREATE PROCEDURE `_mig_drop_key_if_exists`(IN p_table VARCHAR(64), IN p_key VARCHAR(64))
BEGIN
  IF p_key <> 'PRIMARY' AND EXISTS (
    SELECT 1
      FROM information_schema.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME = p_table
       AND INDEX_NAME = p_key
  ) THEN
    CALL `_mig_exec`(
      CONCAT(
        'ALTER TABLE `', REPLACE(p_table, '`', '``'),
        '` DROP KEY `', REPLACE(p_key, '`', '``'), '`'
      )
    );
  END IF;
END$$

DROP PROCEDURE IF EXISTS `_mig_add_key_if_missing`$$
CREATE PROCEDURE `_mig_add_key_if_missing`(
  IN p_table VARCHAR(64),
  IN p_key VARCHAR(64),
  IN p_columns_sql VARCHAR(255)
)
BEGIN
  IF NOT EXISTS (
    SELECT 1
      FROM information_schema.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME = p_table
       AND INDEX_NAME = p_key
  ) THEN
    CALL `_mig_exec`(
      CONCAT(
        'ALTER TABLE `', REPLACE(p_table, '`', '``'),
        '` ADD KEY `', REPLACE(p_key, '`', '``'), '` ', p_columns_sql
      )
    );
  END IF;
END$$

DROP PROCEDURE IF EXISTS `_mig_add_fk_if_missing`$$
CREATE PROCEDURE `_mig_add_fk_if_missing`(
  IN p_table VARCHAR(64),
  IN p_fk VARCHAR(64),
  IN p_fk_sql TEXT
)
BEGIN
  IF NOT EXISTS (
    SELECT 1
      FROM information_schema.TABLE_CONSTRAINTS
     WHERE CONSTRAINT_SCHEMA = DATABASE()
       AND TABLE_NAME = p_table
       AND CONSTRAINT_NAME = p_fk
       AND CONSTRAINT_TYPE = 'FOREIGN KEY'
  ) THEN
    CALL `_mig_exec`(
      CONCAT(
        'ALTER TABLE `', REPLACE(p_table, '`', '``'),
        '` ADD CONSTRAINT `', REPLACE(p_fk, '`', '``'), '` ', p_fk_sql
      )
    );
  END IF;
END$$

DROP PROCEDURE IF EXISTS `_mig_assert_icon_len`$$
CREATE PROCEDURE `_mig_assert_icon_len`()
BEGIN
  DECLARE v_count INT DEFAULT 0;

  SELECT COUNT(*)
    INTO v_count
    FROM `intranet_apps`
   WHERE `icon` IS NOT NULL
     AND CHAR_LENGTH(`icon`) > 1024;

  IF v_count > 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'intranet_apps.icon tiene valores de mas de 1024 caracteres; revisar antes de convertir a varchar(1024).';
  END IF;
END$$

DELIMITER ;

-- 1) Ajustes del catalogo de aplicaciones.
--    group_name nullable e icon como varchar(1024).
--    Se fija utf8mb4_spanish_ci para busquedas sin distincion de mayusculas
--    ni acentos. Se aborta si hubiera iconos demasiado largos.
CALL `_mig_assert_icon_len`();

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

-- intranet_catalog_groups ya esta en unicode_ci en los dumps, pero intranet_links
-- quedaba en general_ci. catalogo_links_fetch() compara ambas tablas por
-- link_type/grupo, asi que deben compartir collation.
ALTER TABLE `intranet_links`
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_spanish_ci;

ALTER TABLE `intranet_apps`
  MODIFY COLUMN `group_name` varchar(255) DEFAULT NULL,
  MODIFY COLUMN `icon` varchar(1024) DEFAULT NULL;

-- 2) Nuevas tablas de documentacion de locales.
CREATE TABLE IF NOT EXISTS `doc_locales` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(180) NOT NULL,
  `sector_type` varchar(40) NOT NULL,
  `address` varchar(255) NOT NULL,
  `city` varchar(120) NOT NULL DEFAULT '',
  `province` varchar(120) NOT NULL DEFAULT '',
  `postal_code` varchar(20) NOT NULL DEFAULT '',
  `reference_code` varchar(80) NOT NULL DEFAULT '',
  `contact_person` varchar(150) NOT NULL DEFAULT '',
  `notes` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_by` varchar(120) NOT NULL DEFAULT '',
  `updated_by` varchar(120) NOT NULL DEFAULT '',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_doc_locales_name` (`name`),
  KEY `idx_doc_locales_sector_active` (`sector_type`,`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

CREATE TABLE IF NOT EXISTS `doc_local_documents` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `local_id` int(10) UNSIGNED NOT NULL,
  `document_type` varchar(40) NOT NULL,
  `title` varchar(180) NOT NULL,
  `description` text DEFAULT NULL,
  `version_label` varchar(80) NOT NULL DEFAULT '',
  `tags_json` longtext DEFAULT NULL,
  `reference_number` varchar(120) NOT NULL DEFAULT '',
  `issued_on` date DEFAULT NULL,
  `expires_on` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `stored_name` varchar(120) NOT NULL,
  `original_name` varchar(255) NOT NULL,
  `mime_type` varchar(120) NOT NULL DEFAULT 'application/octet-stream',
  `file_size` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `sha256` char(64) DEFAULT NULL,
  `created_by` varchar(120) NOT NULL DEFAULT '',
  `updated_by` varchar(120) NOT NULL DEFAULT '',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_doc_local_documents_local` (`local_id`),
  KEY `idx_doc_local_documents_type` (`document_type`),
  KEY `idx_doc_local_documents_dates` (`issued_on`,`expires_on`),
  CONSTRAINT `fk_doc_local_documents_local`
    FOREIGN KEY (`local_id`) REFERENCES `doc_locales` (`id`)
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

-- 3) Nuevas tablas de inventario.
CREATE TABLE IF NOT EXISTS `inventario_equipos` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `equipo` varchar(128) NOT NULL,
  `dominio` varchar(255) DEFAULT NULL,
  `fabricante` varchar(255) DEFAULT NULL,
  `modelo` varchar(255) DEFAULT NULL,
  `serial_bios` varchar(255) DEFAULT NULL,
  `bios_version` varchar(255) DEFAULT NULL,
  `procesador` varchar(512) DEFAULT NULL,
  `ram_gb` decimal(10,2) DEFAULT NULL,
  `disco_total_gb` decimal(12,2) DEFAULT NULL,
  `disco_libre_gb` decimal(12,2) DEFAULT NULL,
  `windows_caption` varchar(255) DEFAULT NULL,
  `windows_version` varchar(120) DEFAULT NULL,
  `windows_build` varchar(80) DEFAULT NULL,
  `os_architecture` varchar(80) DEFAULT NULL,
  `ultimo_usuario` varchar(255) DEFAULT NULL,
  `ipv4_json` text DEFAULT NULL,
  `mac_json` text DEFAULT NULL,
  `red_json` longtext DEFAULT NULL,
  `wol_json` longtext DEFAULT NULL,
  `payload_json` longtext DEFAULT NULL,
  `inventory_version` varchar(40) DEFAULT NULL,
  `source_ip` varchar(64) DEFAULT NULL,
  `payload_hash` char(64) DEFAULT NULL,
  `first_seen_at` datetime NOT NULL DEFAULT current_timestamp(),
  `last_seen_at` datetime NOT NULL DEFAULT current_timestamp(),
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_inventario_equipo` (`equipo`),
  KEY `idx_inventario_last_seen` (`last_seen_at`),
  KEY `idx_inventario_modelo` (`fabricante`(80),`modelo`(120)),
  KEY `idx_inventario_windows` (`windows_caption`(160))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

CREATE TABLE IF NOT EXISTS `inventario_reportes` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `equipo_id` bigint(20) UNSIGNED NOT NULL,
  `payload_hash` char(64) DEFAULT NULL,
  `source_ip` varchar(64) DEFAULT NULL,
  `payload_json` longtext NOT NULL,
  `received_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_inventario_reportes_equipo_fecha` (`equipo_id`,`received_at`),
  KEY `idx_inventario_reportes_hash` (`payload_hash`),
  CONSTRAINT `fk_inventario_reportes_equipo`
    FOREIGN KEY (`equipo_id`) REFERENCES `inventario_equipos` (`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

CREATE TABLE IF NOT EXISTS `inventario_software` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `equipo_id` bigint(20) UNSIGNED NOT NULL,
  `nombre` varchar(512) NOT NULL,
  `version` varchar(255) DEFAULT NULL,
  `fabricante` varchar(255) DEFAULT NULL,
  `install_date` varchar(40) DEFAULT NULL,
  `uninstall_key` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_inventario_sw_equipo_nombre` (`equipo_id`,`nombre`(160)),
  KEY `idx_inventario_sw_nombre` (`nombre`(190)),
  CONSTRAINT `fk_inventario_software_equipo`
    FOREIGN KEY (`equipo_id`) REFERENCES `inventario_equipos` (`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

-- 4) Normalizar claves duplicadas heredadas en incidencias y tareas.
CALL `_mig_add_key_if_missing`('inc_incidents', 'fk_inc_category', '(`category_id`)');
CALL `_mig_drop_fk_if_exists`('inc_incidents', 'fk_inc_category_new');
CALL `_mig_drop_key_if_exists`('inc_incidents', 'fk_inc_category_new');
CALL `_mig_add_fk_if_missing`(
  'inc_incidents',
  'fk_inc_category',
  'FOREIGN KEY (`category_id`) REFERENCES `inc_categories` (`id`) ON DELETE SET NULL'
);

CALL `_mig_add_key_if_missing`('tsk_tasks', 'fk_tsk_type', '(`type_id`)');
CALL `_mig_drop_fk_if_exists`('tsk_tasks', 'fk_tsk_type_new');
CALL `_mig_drop_key_if_exists`('tsk_tasks', 'fk_tsk_type_new');
CALL `_mig_add_fk_if_missing`(
  'tsk_tasks',
  'fk_tsk_type',
  'FOREIGN KEY (`type_id`) REFERENCES `tsk_types` (`id`) ON DELETE SET NULL'
);

-- 5) Ajuste de listin: el dump de desarrollo conserva indices, pero no estas FK.
CALL `_mig_drop_fk_if_exists`('listin_secciones', 'fk_listin_sector');
CALL `_mig_drop_fk_if_exists`('listin_subsecciones', 'fk_listin_sub_seccion');

-- 6) Limpieza de procedimientos auxiliares.
DROP PROCEDURE IF EXISTS `_mig_assert_icon_len`;
DROP PROCEDURE IF EXISTS `_mig_add_fk_if_missing`;
DROP PROCEDURE IF EXISTS `_mig_add_key_if_missing`;
DROP PROCEDURE IF EXISTS `_mig_drop_key_if_exists`;
DROP PROCEDURE IF EXISTS `_mig_drop_fk_if_exists`;
DROP PROCEDURE IF EXISTS `_mig_exec`;

-- Bloque opcional NO ejecutado por defecto.
-- Revisar manualmente antes de descomentar:
--
-- A) El dump de desarrollo no trae `compras_acquisitions`.`contabilizada`,
--    pero el codigo actual en apps/compras y plugins/compras la usa.
--    No ejecutar con el arbol actual:
-- ALTER TABLE `compras_acquisitions` DROP COLUMN `contabilizada`;
--
-- B) El dump de desarrollo no trae permisos_*, pero apps/permisos las usa.
--    No ejecutar salvo que la app de vacaciones se retire de verdad:
-- DROP TABLE `permisos_entries`;
-- DROP TABLE `permisos_holidays`;
-- DROP TABLE `permisos_sector_catalog`;
-- DROP TABLE `permisos_employees`;
--
-- C) El dump de desarrollo no marca `sectores`.`id` como AUTO_INCREMENT.
--    El codigo de base/sectores inserta IDs manuales, asi que no es urgente.
--    Puede fallar si hay FK activas segun el estado exacto de la base:
-- ALTER TABLE `sectores` MODIFY COLUMN `id` int(11) NOT NULL;
--
-- D) No hay cambios para entregas_: produccion actual manda en ese modulo.
