-- =============================================================================
-- OC y Clasificación — 3/3: parametrización en Central
-- Base: asercolg_central (Central, proyecto `cia`) · MariaDB 10.6
-- Ver specs/Especificacion_CTC_OC_Clasificacion.md Módulo 8, RN-4.1 y §A.5
-- =============================================================================
--
-- Central administra estas tablas desde `cia`; el CTC solo las lee (conexión
-- `central`). Lo aplica el DBA a mano; NO se crea migración.
-- Es idempotente: CREATE TABLE IF NOT EXISTS e INSERT ... WHERE NOT EXISTS.
--
-- Lo que NO está aquí, a propósito (decisiones del 21/10):
--   · El In House de cada cliente sale de asercolg_bitacora.inhouse_cliente,
--     que ya se administra en cia (/inhouse_cliente).
--   · El Analista de Servicio al Cliente de un cliente sin CTC es el
--     ID_EMPLEADO_OP del DO en Websimex; antes del DO, quien cargó la OC.
--   · La Dirección de Servicio al Cliente es un solo grupo de correos para
--     todos los clientes: va en alerta_destinatario, no por cliente.
--
-- Orden: este es el 3º de 3 (ver oc_clasificacion_01_ctc.sql).
--
-- =============================================================================
-- ANTES DE CORRER: correo del grupo de la Dirección de Servicio al Cliente
-- =============================================================================

SET @correo_direccion_sac = NULL;   -- <- poner aquí el correo del grupo, ej. 'direccion.sac@asercol.com'

-- =============================================================================
-- PASO 0 — Estado antes (ejecutar y guardar la salida)
-- =============================================================================

SELECT TABLE_NAME, TABLE_ROWS
  FROM information_schema.TABLES
 WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME IN ('clasificacion_param_cliente', 'clasificacion_param_auditoria',
                      'comprador_interno', 'alerta_destinatario');
-- Esperado antes de la primera ejecución: ninguna fila.

-- =============================================================================
-- PASO 1 — Tablas
-- =============================================================================

-- Parametrización por cliente (Módulo 8). Un registro por importador.
CREATE TABLE IF NOT EXISTS `clasificacion_param_cliente` (
  `id`                 INT(11)      NOT NULL AUTO_INCREMENT,
  `id_importador`      INT(11)      NOT NULL     COMMENT 'comex.importadores.ID_IMPORTADOR',
  `usa_ctc`            TINYINT(1)   NOT NULL     COMMENT '1: la OC la carga el cliente en CTC · 0: la carga ASERCOL en Central',
  `copias_adicionales` VARCHAR(1000) DEFAULT NULL COMMENT 'Correos adicionales en copia, separados por coma',
  `estado`             TINYINT(4)   NOT NULL DEFAULT 1,
  `created_at`         TIMESTAMP    NULL DEFAULT NULL,
  `updated_at`         TIMESTAMP    NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_clasificacion_param_cliente_importador` (`id_importador`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Catálogo maestro de compradores internos (RN-8.6 a RN-8.11).
-- El correo admite NULL solo para los migrados (RN-8.11): el In House lo
-- completa al aprobarlos. Un comprador activo debe tener correo; lo valida cia.
CREATE TABLE IF NOT EXISTS `comprador_interno` (
  `id`            INT(11)      NOT NULL AUTO_INCREMENT,
  `id_importador` INT(11)      NOT NULL,
  `nombre`        VARCHAR(150) NOT NULL,
  `correo`        VARCHAR(150) DEFAULT NULL,
  `area`          VARCHAR(100) DEFAULT NULL,
  `estado`        VARCHAR(12)  NOT NULL DEFAULT 'POR_APROBAR' COMMENT 'ACTIVO | INACTIVO | POR_APROBAR',
  `origen`        VARCHAR(20)  NOT NULL DEFAULT 'MANUAL'      COMMENT 'MANUAL | PLANTILLA | IA | MIGRACION',
  `id_user_aprueba` INT(11)    DEFAULT NULL,
  `fecha_aprueba` DATETIME     DEFAULT NULL,
  `created_at`    TIMESTAMP    NULL DEFAULT NULL,
  `updated_at`    TIMESTAMP    NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_comprador_interno` (`id_importador`, `nombre`),
  KEY `ix_comprador_interno_estado` (`id_importador`, `estado`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Destinatarios por tipo de alerta (RN-4.1). Se carga con la configuración
-- inicial del Módulo 4 y se edita en cia sin cambios de código.
CREATE TABLE IF NOT EXISTS `alerta_destinatario` (
  `id`          INT(11)      NOT NULL AUTO_INCREMENT,
  `tipo_alerta` VARCHAR(30)  NOT NULL COMMENT 'PRODUCTO_NUEVO | DOCUMENTOS_INCOMPLETOS | ANS_RIESGO | PRODUCTO_POR_CREAR | PROVEEDOR_POR_CREAR | REQUIERE_RIM | RIM_SIN_APROBAR | ADICION_PRODUCTOS',
  `tipo_destinatario` VARCHAR(25) NOT NULL COMMENT 'DESTINATARIO_PRINCIPAL | IN_HOUSE | ANALISTA | ANALISTA_SAC | ROL_CTI | USUARIO | CORREO',
  `valor`       VARCHAR(150) DEFAULT NULL COMMENT 'id de rol de CTI, id de usuario o correo; NULL en los tipos lógicos',
  `canal`       VARCHAR(10)  NOT NULL DEFAULT 'AMBOS' COMMENT 'PANTALLA | CORREO | AMBOS',
  `copia`       TINYINT(1)   NOT NULL DEFAULT 0 COMMENT '1 = va en copia',
  `estado`      TINYINT(4)   NOT NULL DEFAULT 1,
  `created_at`  TIMESTAMP    NULL DEFAULT NULL,
  `updated_at`  TIMESTAMP    NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_alerta_destinatario` (`tipo_alerta`, `tipo_destinatario`, `valor`),
  KEY `ix_alerta_destinatario_tipo` (`tipo_alerta`, `estado`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Auditoría de la parametrización (RN-8.5): usuario, fecha, valor anterior y
-- nuevo. Solo inserción.
CREATE TABLE IF NOT EXISTS `clasificacion_param_auditoria` (
  `id`             INT(11)      NOT NULL AUTO_INCREMENT,
  `tabla`          VARCHAR(40)  NOT NULL COMMENT 'clasificacion_param_cliente | comprador_interno | alerta_destinatario',
  `id_registro`    INT(11)      NOT NULL,
  `accion`         VARCHAR(10)  NOT NULL COMMENT 'CREAR | EDITAR | INACTIVAR | APROBAR | RECHAZAR',
  `valor_anterior` LONGTEXT     DEFAULT NULL COMMENT 'JSON',
  `valor_nuevo`    LONGTEXT     DEFAULT NULL COMMENT 'JSON',
  `id_user`        INT(11)      NOT NULL,
  `usuario`        VARCHAR(150) DEFAULT NULL,
  `fecha`          DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_clasificacion_param_auditoria_registro` (`tabla`, `id_registro`, `fecha`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- PASO 2 — Configuración inicial de destinatarios (tabla de alertas del M4)
-- =============================================================================
--
-- Tipos lógicos que resuelve la aplicación al enviar:
--   DESTINATARIO_PRINCIPAL  In House si la OC entró por CTC; si entró por Central,
--                           el analista que la cargó (ID_EMPLEADO_OP una vez hay DO)
--   IN_HOUSE                In House del cliente (inhouse_cliente)
--   ANALISTA                Analista de clasificación que tomó la solicitud
--   ANALISTA_SAC            Analista de Servicio al Cliente (ID_EMPLEADO_OP del DO,
--                           o quien cargó la OC si aún no hay DO)
--
-- La Torre de Control no recibe correos: muestra las alertas como tarjetas
-- (RN-6.6), por eso no aparece aquí.

INSERT INTO `alerta_destinatario` (`tipo_alerta`, `tipo_destinatario`, `valor`, `canal`, `copia`, `estado`, `created_at`)
SELECT v.alerta, v.destinatario, v.valor, v.canal, v.copia, 1, NOW()
  FROM (SELECT 'PRODUCTO_NUEVO'         AS alerta, 'DESTINATARIO_PRINCIPAL' AS destinatario, NULL AS valor, 'AMBOS'    AS canal, 0 AS copia UNION ALL
        SELECT 'DOCUMENTOS_INCOMPLETOS',           'IN_HOUSE',               NULL,          'AMBOS',              0 UNION ALL
        SELECT 'ANS_RIESGO',                       'ANALISTA',               NULL,          'AMBOS',              0 UNION ALL
        SELECT 'PRODUCTO_POR_CREAR',               'ANALISTA_SAC',           NULL,          'AMBOS',              0 UNION ALL
        SELECT 'PROVEEDOR_POR_CREAR',              'ANALISTA_SAC',           NULL,          'AMBOS',              0 UNION ALL
        SELECT 'REQUIERE_RIM',                     'ROL_CTI',                '47',          'AMBOS',              0 UNION ALL
        SELECT 'REQUIERE_RIM',                     'IN_HOUSE',               NULL,          'AMBOS',              0 UNION ALL
        SELECT 'RIM_SIN_APROBAR',                  'ROL_CTI',                '47',          'AMBOS',              0 UNION ALL
        SELECT 'ADICION_PRODUCTOS',                'IN_HOUSE',               NULL,          'PANTALLA',           0) v
 WHERE NOT EXISTS (SELECT 1 FROM `alerta_destinatario` a
                    WHERE a.tipo_alerta = v.alerta
                      AND a.tipo_destinatario = v.destinatario
                      AND (a.valor <=> v.valor));

-- Dirección de Servicio al Cliente en copia: producto nuevo y ANS en riesgo.
-- Solo si se definió @correo_direccion_sac arriba.
INSERT INTO `alerta_destinatario` (`tipo_alerta`, `tipo_destinatario`, `valor`, `canal`, `copia`, `estado`, `created_at`)
SELECT v.alerta, 'CORREO', @correo_direccion_sac, 'CORREO', 1, 1, NOW()
  FROM (SELECT 'PRODUCTO_NUEVO' AS alerta UNION ALL
        SELECT 'ANS_RIESGO') v
 WHERE @correo_direccion_sac IS NOT NULL
   AND NOT EXISTS (SELECT 1 FROM `alerta_destinatario` a
                    WHERE a.tipo_alerta = v.alerta
                      AND a.tipo_destinatario = 'CORREO'
                      AND a.valor = @correo_direccion_sac);

-- =============================================================================
-- PASO 3 — Verificación
-- =============================================================================

SELECT tipo_alerta, tipo_destinatario, valor, canal, copia
  FROM `alerta_destinatario`
 WHERE estado = 1
 ORDER BY tipo_alerta, copia, tipo_destinatario;
-- Esperado: 9 filas de destinatarios + 2 de la Dirección de Servicio al Cliente
-- (CORREO, copia = 1) si se definió @correo_direccion_sac.

-- =============================================================================
-- Carga inicial de compradores (RN-8.11) — NO se corre aquí
-- =============================================================================
--
-- Los compradores históricos están en asercolg_po (definicion_campos_po /
-- adicionales_po: grupo 27 campo 363, grupo 2 campos 366 y 380) y su importador
-- sale de compania.id_websimex. Como son bases distintas, la carga la hace la
-- tarea 4.3 desde cia, en estado POR_APROBAR, sin correo y con origen MIGRACION.
--
-- =============================================================================
-- Para revertir (solo si cia no ha escrito datos todavía)
-- =============================================================================
--
--   DROP TABLE IF EXISTS `clasificacion_param_auditoria`;
--   DROP TABLE IF EXISTS `alerta_destinatario`;
--   DROP TABLE IF EXISTS `comprador_interno`;
--   DROP TABLE IF EXISTS `clasificacion_param_cliente`;