-- =====================================================================
-- Migración: service_endpoints + service_connection_info
-- Fecha: 2026-08
-- Repo: nube_services_dao
--
-- Contexto:
--   El modal "Ver información" del grid de servicios (frontend PHP) obtiene
--   hoy la información de red de cada release ejecutando `kubectl` por shell
--   en cada apertura: una decena de invocaciones síncronas contra el
--   apiserver de producción para pintar un cuadro de diálogo.
--
--   Se elimina esa dependencia. k8s_monitor_services —que ya tiene informers
--   de client-go y ya escribe requests_queue a través del DAO— observará
--   también los objetos Service e Ingress y persistirá sus datos; el frontend
--   pasa a hacer un SELECT.
--
--   Esta migración cubre únicamente la base. No incluye cambios en el monitor
--   ni en el frontend, que dependen de que estas tablas existan.
--
--   Orden de ejecución: este archivo primero; después
--   2026-08_service_connection_info_catalog.sql, que carga las 22 filas del
--   catálogo y depende de que la tabla exista.
--
--   Todo el bloque es re-ejecutable de forma segura (CREATE TABLE IF NOT
--   EXISTS, columnas con verificación previa, semillas con WHERE NOT EXISTS).
--
-- ---------------------------------------------------------------------
-- VERIFICADO CONTRA LA BASE ANTES DE ESCRIBIR ESTA MIGRACIÓN
-- ---------------------------------------------------------------------
--   * requests_queue.RequestQueueID es `bigint(20) NOT NULL PRI
--     auto_increment`. Esa tabla no tiene DDL versionado en ningún
--     repositorio —su definición sólo vive en la base— así que el tipo de la
--     FK se confirmó a mano:
--         SHOW COLUMNS FROM requests_queue LIKE 'RequestQueueID';
--
--   * Los anchos de columna NO son arbitrarios: se tomaron de lo que ya usa
--     el esquema para cada concepto, no de un máximo teórico.
--         k8s_context           varchar(100)  <- backup_schedule, backup_tracking,
--                                                backup_restore, backup_operations,
--                                                cluster.k8scontext
--         release_identificator varchar(100)  <- requests_queue.identificator
--         resource_name         varchar(253)  <- velero_backup_name,
--                                                velero_schedule_name (nombre de
--                                                objeto K8s, RFC 1123)
--         namespace             varchar(63)   <- included_namespace,
--                                                velero_namespace (máximo real de
--                                                un namespace de Kubernetes)
--
--     Esto NO es cosmético. Con varchar(255) en esas columnas, la UNIQUE KEY
--     de cuatro campos suma 3260 bytes en utf8mb4 y excede el límite de 3072
--     de InnoDB. MariaDB no falla: la degrada en silencio a un índice
--     `USING HASH`, que el optimizador NO usa para leer. Comprobado sobre una
--     copia de la base en MariaDB 11.4.7:
--
--         varchar(255) -> INDEX_TYPE = HASH
--                         SELECT del Upsert: type=ALL, key=NULL (escaneo completo)
--         anchos reales -> INDEX_TYPE = BTREE  (1892 bytes)
--                         SELECT del Upsert: type=const, key_len=1892, 1 fila
--
--     La unicidad se cumple en ambos casos (el INSERT duplicado falla con
--     ERROR 1062), pero con HASH cada Upsert del monitor escanearía la tabla
--     entera, y el monitor hace uno por cada Service observado.
--
--   * COLLATE=utf8mb4_general_ci es explícito a propósito. Sin declararlo,
--     MariaDB 11.4 aplica utf8mb4_uca1400_ai_ci, mientras que las 34 tablas
--     utf8mb4 del esquema usan general_ci. Dejarlo implícito hace que el
--     resultado del DDL dependa de la versión del servidor donde se corra.
-- =====================================================================


-- ---------------------------------------------------------------------
-- 1. service_endpoints — estado observado
--
-- Una fila por objeto de Kubernetes (Service o Ingress) de cada release.
-- La escribe k8s_monitor_services por observación continua; nadie más la
-- toca.
--
-- Sobre request_queue_id NOT NULL: es deliberado. Un endpoint cuyo release
-- no empate con una fila de requests_queue no es un despliegue válido
-- originado en la interfaz, y no debe registrarse.
--
-- Sobre status_id en lugar de borrar filas o de un flag `active`: sigue el
-- patrón de backup_schedule / backup_tracking. Conservar las filas da
-- histórico de qué IP tuvo cada release, que es información valiosa porque
-- las IPs externas están limitadas por licenciamiento con VMware/Broadcom.
--
-- La FK va a requests_queue y no a contracted_services porque la relación no
-- es 1:1 — hoy hay 436 filas en requests_queue contra 432 en
-- contracted_services: una contratación puede abarcar varios despliegues.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `service_endpoints` (
  `ServiceEndpointsID`    bigint(20)   NOT NULL AUTO_INCREMENT,
  `request_queue_id`      bigint(20)   NOT NULL,
  `k8s_context`           varchar(100) NOT NULL,
  `namespace`             varchar(63)  NOT NULL,
  `release_identificator` varchar(100) NOT NULL,
  `resource_kind`         varchar(20)  NOT NULL,   -- 'Service' | 'Ingress'
  `resource_name`         varchar(253) NOT NULL,
  `resource_role`         varchar(50)  DEFAULT NULL,  -- app.kubernetes.io/component
  `service_type`          varchar(20)  DEFAULT NULL,  -- LoadBalancer | ClusterIP | NodePort
  `external_ip`           varchar(45)  DEFAULT NULL,
  `cluster_ip`            varchar(45)  DEFAULT NULL,  -- incluye el literal 'None' en headless
  `hostname`              varchar(255) DEFAULT NULL,
  `ports`                 longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
                            CHECK (json_valid(`ports`)),
  `status_id`             int(11)      DEFAULT NULL,
  `synced_at`             datetime     DEFAULT NULL,
  `created_at`            datetime     NOT NULL DEFAULT current_timestamp(),
  `updated_at`            datetime     DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`ServiceEndpointsID`),
  -- Indispensable: es lo único que evita duplicados en la ventana que tiene
  -- el Upsert del DAO entre su SELECT y el guardado. Y un release expone
  -- varios Services (MongoDB uno por nodo, Kafka hasta seis, Hadoop+Spark
  -- once), así que la clave no puede ser sólo el release.
  UNIQUE KEY `uk_service_endpoints_release`
    (`release_identificator`, `k8s_context`, `resource_kind`, `resource_name`),
  -- Permite acotar la reconciliación de arranque a un clúster y namespace
  -- concretos, para marcar `Removed` lo que desapareció mientras el monitor
  -- estuvo caído sin arriesgarse a tocar filas de otro namespace.
  KEY `ix_service_endpoints_scope` (`k8s_context`, `namespace`),
  KEY `fk_service_endpoints_request_idx` (`request_queue_id`),
  KEY `fk_service_endpoints_status_idx`  (`status_id`),
  CONSTRAINT `fk_service_endpoints_request`
    FOREIGN KEY (`request_queue_id`) REFERENCES `requests_queue` (`RequestQueueID`) ON UPDATE CASCADE,
  CONSTRAINT `fk_service_endpoints_status`
    FOREIGN KEY (`status_id`)        REFERENCES `status_catalog` (`StatusCatalogID`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Formato esperado de `ports` (array de los spec.ports del objeto):
--   [{"name":"restAPI","port":9200,"targetPort":9200,"protocol":"TCP"}]
--
-- OJO al escribirlo por el CRUD dinámico: debe llegar como CADENA ya
-- serializada, nunca como estructura anidada. ConvertValue del DAO intenta
-- Convert.ChangeType(value, string) sobre el Dictionary/List que produce
-- ProtoValueToObject, eso lanza InvalidCastException, y el método la captura
-- devolviendo null. La columna quedaría en NULL y el Upsert reportaría éxito.


-- ---------------------------------------------------------------------
-- 2. service_connection_info — catálogo estático
--
-- Cómo se consume cada tecnología. Una o varias filas por servicio; no la
-- escribe ningún proceso automático, es catálogo y se administra.
--
-- No necesita índice único: puede haber varias filas por servicio (una por
-- componente), y `sort_order` define el orden de presentación.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `service_connection_info` (
  `ServiceConnectionInfoID` int(11)      NOT NULL AUTO_INCREMENT,
  `services_id`             int(11)      NOT NULL,
  `component`               varchar(50)  DEFAULT NULL,  -- empata con service_endpoints.resource_role; NULL = aplica a todos
  `kind`                    varchar(20)  NOT NULL,      -- 'cli' | 'web' | 'gui'
  `endpoint_strategy`       varchar(20)  NOT NULL DEFAULT 'first',  -- 'first' | 'join' | 'each'
  `default_port`            int(11)      DEFAULT NULL,
  `default_user`            varchar(64)  DEFAULT NULL,  -- NULL = usar el que capturó el cliente
  `template`                text         DEFAULT NULL,  -- texto plano con {MARCADORES}, nunca HTML
  `notes`                   text         DEFAULT NULL,
  `sort_order`              int(11)      NOT NULL DEFAULT 0,
  `active`                  bit(1)       NOT NULL DEFAULT b'1',
  PRIMARY KEY (`ServiceConnectionInfoID`),
  KEY `fk_service_connection_service_idx` (`services_id`),
  CONSTRAINT `fk_service_connection_service`
    FOREIGN KEY (`services_id`) REFERENCES `services` (`ServicesID`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Notas de diseño:
--   * `default_port` significa "el puerto que se le ofrece al cliente", no
--     "el puerto del Service". Un mismo Service puede publicar varios y sólo
--     uno ser el correcto: el de Elasticsearch declara 9200 (API REST) y
--     9300 (transporte interno), y sólo el 9200 es para el cliente.
--   * `template` guarda texto plano con marcadores, NUNCA HTML. Su contenido
--     termina insertado en el DOM del navegador; permitir HTML ahí
--     convertiría el catálogo en un vector de inyección hacia la sesión de
--     todos los clientes. El marcado lo pone la vista.
--   * La contraseña no se sustituye en lo que se muestra en pantalla: el
--     marcador {CONTRASEÑA} queda literal y sólo se resuelve al copiar.


-- ---------------------------------------------------------------------
-- 3. cluster — DNS por clúster (multirregión)
--
-- Hoy estos dos valores están escritos a mano en el JavaScript del frontend,
-- lo que impide operar más de una región. `cluster` ya tiene `idregion`, así
-- que queda resuelto por construcción.
--
-- Con verificación previa para que el archivo sea re-ejecutable.
-- ---------------------------------------------------------------------
SET @ddl := (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `cluster` ADD COLUMN `dns_zone` varchar(255) DEFAULT NULL',
    'DO 0')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'cluster' AND COLUMN_NAME = 'dns_zone');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @ddl := (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `cluster` ADD COLUMN `dns_resolver_ip` varchar(45) DEFAULT NULL',
    'DO 0')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'cluster' AND COLUMN_NAME = 'dns_resolver_ip');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Valores de ejemplo (los que hoy están hardcodeados en el frontend):
--   dns_zone        = 'qr.triara.com'
--   dns_resolver_ip = '10.252.8.20'


-- ---------------------------------------------------------------------
-- 4. Catálogo de estados — status_type / status_catalog
--
-- Mismo catálogo compartido que ya usan supply_status, service_status y las
-- fases de backup/restore/schedule. Idempotente por WHERE NOT EXISTS.
-- ---------------------------------------------------------------------
INSERT INTO `status_type` (`name`, `active`)
SELECT 'service_endpoint', b'1'
WHERE NOT EXISTS (SELECT 1 FROM `status_type` WHERE `name` = 'service_endpoint');

INSERT INTO `status_catalog` (`status`, `status_dsc`, `id_type`, `active`)
SELECT s.status, s.status_dsc, st.StatusTypeID, b'1'
FROM (
            SELECT 'Active'  AS status, 'El objeto existe y su información está vigente' AS status_dsc
  UNION ALL SELECT 'Pending',           'Es LoadBalancer pero todavía no tiene IP externa asignada'
  UNION ALL SELECT 'Stale',             'No se pudo reconciliar; la información puede estar vencida'
  UNION ALL SELECT 'Removed',           'El objeto ya no existe en el clúster'
) s
JOIN `status_type` st ON st.name = 'service_endpoint'
WHERE NOT EXISTS (
  SELECT 1 FROM `status_catalog` sc WHERE sc.status = s.status AND sc.id_type = st.StatusTypeID
);

-- 'Pending' no es decorativa: las IPs externas están limitadas por el
-- licenciamiento con VMware/Broadcom, así que un Service puede quedarse sin
-- dirección indefinidamente si el conjunto disponible se agota. Hoy esa
-- situación es invisible.


-- =====================================================================
-- Verificación
-- =====================================================================
-- El índice único debe salir BTREE, no HASH. Si sale HASH, alguna columna
-- quedó más ancha de lo que declara esta migración:
--
--   SELECT INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS cols, INDEX_TYPE
--   FROM information_schema.STATISTICS
--   WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'service_endpoints'
--   GROUP BY INDEX_NAME, INDEX_TYPE;
--
--   SHOW COLUMNS FROM cluster LIKE 'dns_%';        -- deben aparecer las dos nuevas
--
--   SELECT sc.status FROM status_catalog sc
--     JOIN status_type st ON st.StatusTypeID = sc.id_type
--     WHERE st.name = 'service_endpoint';          -- deben ser 4 filas
--
-- Después del alta en el DAO (modelo EF + ModelRegistry + DbSet):
--   GetFields(model: "service_endpoints")          -- 17 columnas
--   GetFields(model: "service_connection_info")    -- 11 columnas


-- =====================================================================
-- ROLLBACK (ejecutar en orden inverso; comentado — descomentar a mano)
-- =====================================================================
-- DELETE sc FROM `status_catalog` sc
--   JOIN `status_type` st ON st.StatusTypeID = sc.id_type
--   WHERE st.name = 'service_endpoint';
-- DELETE FROM `status_type` WHERE `name` = 'service_endpoint';
--
-- ALTER TABLE `cluster` DROP COLUMN `dns_zone`, DROP COLUMN `dns_resolver_ip`;
--
-- DROP TABLE IF EXISTS `service_connection_info`;
-- DROP TABLE IF EXISTS `service_endpoints`;