-- =============================================================================
--  CAPA 2 (BD) DE LA CAPTURA DE CAMBIOS A+B  —  TRIGGERS DE REPLICACIÓN
--  Proyecto : CL032BACK (portal público)   Esquema: asturias_api
--  Archivo  : database/manual/2026_07_24_triggers_rsv_replicacion.sql
--  Fecha    : 2026-07-24
--  Autor    : Equipo Reservaciones en Línea
-- =============================================================================
--
--  BITÁCORA / PROPÓSITO
--  --------------------
--  Este script instala la RED DE SEGURIDAD a nivel de base de datos del esquema
--  de captura de cambios A+B descrito en PROPUESTA_ESCRITURA_TIEMPO_REAL.md.
--
--  Objetivo: que ABSOLUTAMENTE CUALQUIER insert/update/delete sobre las 4 tablas
--  de la reservación quede registrado en el outbox `rsv_replicacion`, para que el
--  worker del lado privado (Reservaciones V2) replique el ESTADO COMPLETO (snapshot) de la
--  reservación afectada. No se reproduce cada micro-cambio: se marca la
--  reservación como "sucia" y se re-replica su estado final (idempotente y
--  auto-reparable).
--
--  Tablas cubiertas (las 4 de la reservación):
--    1. rsv_reservacion                     (cabecera)     → idReservacion propio
--    2. rsv_reservacionhabitacion           (habitación)   → idReservacion propio
--    3. rsv_reservacionhabitacionpaquete    (precio)       → vía idReservacionHabitacion
--    4. rsv_reservacionhabitacionextra      (extras)       → vía idReservacionHabitacion
--
--  DOS CAPAS, UN MISMO OUTBOX
--  --------------------------
--    * Capa 1 (aplicación / opción B): ya implementada en el código
--      (App\Models\Modulos\Reservacion\Replicacion::marcar), corre DENTRO de la
--      transacción de cada operación del Service. Es el camino rápido y con la
--      etiqueta de operación semántica precisa (Cancelacion/Confirmacion/...).
--    * Capa 2 (BD / opción A): ESTE script. Triggers que atrapan cualquier
--      escritura que NO pase por la app (operaciones masivas, SQL manual,
--      migraciones, correcciones directas, otra app sobre la misma BD).
--
--  Ambas capas escriben con la MISMA lógica de dedup (procedimiento
--  sp_rsv_marcar_replicacion), por lo que conviven sin duplicar filas: como mucho
--  1 marca abierta (Pendiente/Error) por reservación. En el flujo normal, la
--  Capa 1 llama a marcar() AL FINAL de la operación, de modo que su etiqueta de
--  operación (p.ej. 'Confirmacion') es la que prevalece; los triggers solo
--  reactivan la misma marca. La réplica es por estado, así que la etiqueta es
--  meramente informativa/observabilidad.
--
--  IDEMPOTENCIA DEL SCRIPT
--  -----------------------
--  Todo objeto se recrea con DROP ... IF EXISTS antes de CREATE, por lo que el
--  script se puede ejecutar varias veces sin error. No borra datos.
--
--  REQUISITO PREVIO
--  ----------------
--  La tabla `rsv_replicacion` debe existir (migración
--  2026_07_24_130000_create_rsv_replicacion_table.php ya aplicada).
--
--  CÓMO EJECUTAR
--  ------------
--    * Cliente de línea de comandos:
--        mysql -u USUARIO -p asturias_api < 2026_07_24_triggers_rsv_replicacion.sql
--    * HeidiSQL / DBeaver / MySQL Workbench: abrir el archivo y ejecutar todo.
--    * phpMyAdmin: pestaña SQL; respeta las líneas DELIMITER $$ / DELIMITER ;
--      (phpMyAdmin las interpreta correctamente).
--
--  REVERSA (rollback)
--  ------------------
--  Al final del archivo hay un bloque comentado "REVERSA" que elimina todos los
--  triggers y el procedimiento. La tabla rsv_replicacion NO se toca ahí.
--
--  NOTA / LIMITACIÓN CONOCIDA
--  --------------------------
--  En los triggers de las tablas nieto (paquete/extra) se resuelve la reservación
--  padre con un SELECT sobre rsv_reservacionhabitacion. Si por un borrado en orden
--  atípico la habitación ya no existiera al dispararse el trigger del hijo, la
--  búsqueda devuelve NULL y esa fila hija no se marca; el procedimiento ignora los
--  NULL. En el flujo real esto no ocurre (los hijos se borran ANTES que su
--  habitación, que todavía existe), y el borrado de la habitación marca igualmente
--  la reservación. Los triggers de BD nunca abortan la operación de negocio.
-- =============================================================================


-- -----------------------------------------------------------------------------
-- 0. Contexto de sesión
-- -----------------------------------------------------------------------------
-- Descomenta y ajusta si tu cliente no está posicionado en el esquema:
-- USE `asturias_api`;


-- -----------------------------------------------------------------------------
-- 1. Procedimiento de marca (dedup)  — espejo de Replicacion::marcar()
--    Reactiva la marca abierta de la reservación o inserta una nueva.
--    Ignora p_idReservacion NULL (hijo sin padre resoluble).
-- -----------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS `sp_rsv_marcar_replicacion`;

DELIMITER $$
CREATE PROCEDURE `sp_rsv_marcar_replicacion`(
    IN p_idReservacion INT,
    IN p_operacion     VARCHAR(20)
)
BEGIN
    IF p_idReservacion IS NOT NULL THEN
        IF EXISTS (
            SELECT 1 FROM `rsv_replicacion`
             WHERE `idReservacion` = p_idReservacion
               AND `replicacionEstado` IN ('Pendiente', 'Error')
        ) THEN
            UPDATE `rsv_replicacion`
               SET `replicacionOperacion` = p_operacion,
                   `replicacionEstado`    = 'Pendiente',
                   `replicacionFecha`     = NOW(),
                   `replicacionError`     = NULL
             WHERE `idReservacion` = p_idReservacion
               AND `replicacionEstado` IN ('Pendiente', 'Error');
        ELSE
            INSERT INTO `rsv_replicacion`
                (`idReservacion`, `replicacionOperacion`, `replicacionEstado`,
                 `replicacionIntentos`, `replicacionFecha`)
            VALUES
                (p_idReservacion, p_operacion, 'Pendiente', 0, NOW());
        END IF;
    END IF;
END$$
DELIMITER ;


-- -----------------------------------------------------------------------------
-- 2. Triggers sobre rsv_reservacion (cabecera) — idReservacion propio
--    INSERT/UPDATE → 'Actualizacion'; DELETE → 'Eliminacion'.
-- -----------------------------------------------------------------------------
DROP TRIGGER IF EXISTS `trg_rsv_reservacion_ai`;
DROP TRIGGER IF EXISTS `trg_rsv_reservacion_au`;
DROP TRIGGER IF EXISTS `trg_rsv_reservacion_ad`;

DELIMITER $$

CREATE TRIGGER `trg_rsv_reservacion_ai` AFTER INSERT ON `rsv_reservacion`
FOR EACH ROW
BEGIN
    CALL sp_rsv_marcar_replicacion(NEW.`idReservacion`, 'Actualizacion');
END$$

CREATE TRIGGER `trg_rsv_reservacion_au` AFTER UPDATE ON `rsv_reservacion`
FOR EACH ROW
BEGIN
    CALL sp_rsv_marcar_replicacion(NEW.`idReservacion`, 'Actualizacion');
END$$

CREATE TRIGGER `trg_rsv_reservacion_ad` AFTER DELETE ON `rsv_reservacion`
FOR EACH ROW
BEGIN
    CALL sp_rsv_marcar_replicacion(OLD.`idReservacion`, 'Eliminacion');
END$$

DELIMITER ;


-- -----------------------------------------------------------------------------
-- 3. Triggers sobre rsv_reservacionhabitacion (habitación) — idReservacion propio
-- -----------------------------------------------------------------------------
DROP TRIGGER IF EXISTS `trg_rsv_rhabitacion_ai`;
DROP TRIGGER IF EXISTS `trg_rsv_rhabitacion_au`;
DROP TRIGGER IF EXISTS `trg_rsv_rhabitacion_ad`;

DELIMITER $$

CREATE TRIGGER `trg_rsv_rhabitacion_ai` AFTER INSERT ON `rsv_reservacionhabitacion`
FOR EACH ROW
BEGIN
    CALL sp_rsv_marcar_replicacion(NEW.`idReservacion`, 'Actualizacion');
END$$

CREATE TRIGGER `trg_rsv_rhabitacion_au` AFTER UPDATE ON `rsv_reservacionhabitacion`
FOR EACH ROW
BEGIN
    CALL sp_rsv_marcar_replicacion(NEW.`idReservacion`, 'Actualizacion');
END$$

CREATE TRIGGER `trg_rsv_rhabitacion_ad` AFTER DELETE ON `rsv_reservacionhabitacion`
FOR EACH ROW
BEGIN
    CALL sp_rsv_marcar_replicacion(OLD.`idReservacion`, 'Actualizacion');
END$$

DELIMITER ;


-- -----------------------------------------------------------------------------
-- 4. Triggers sobre rsv_reservacionhabitacionpaquete (precio)
--    Resuelve la reservación padre vía idReservacionHabitacion.
-- -----------------------------------------------------------------------------
DROP TRIGGER IF EXISTS `trg_rsv_rhpaquete_ai`;
DROP TRIGGER IF EXISTS `trg_rsv_rhpaquete_au`;
DROP TRIGGER IF EXISTS `trg_rsv_rhpaquete_ad`;

DELIMITER $$

CREATE TRIGGER `trg_rsv_rhpaquete_ai` AFTER INSERT ON `rsv_reservacionhabitacionpaquete`
FOR EACH ROW
BEGIN
    DECLARE v_idReservacion INT DEFAULT NULL;
    SELECT `idReservacion` INTO v_idReservacion
      FROM `rsv_reservacionhabitacion`
     WHERE `idReservacionHabitacion` = NEW.`idReservacionHabitacion`
     LIMIT 1;
    CALL sp_rsv_marcar_replicacion(v_idReservacion, 'Actualizacion');
END$$

CREATE TRIGGER `trg_rsv_rhpaquete_au` AFTER UPDATE ON `rsv_reservacionhabitacionpaquete`
FOR EACH ROW
BEGIN
    DECLARE v_idReservacion INT DEFAULT NULL;
    SELECT `idReservacion` INTO v_idReservacion
      FROM `rsv_reservacionhabitacion`
     WHERE `idReservacionHabitacion` = NEW.`idReservacionHabitacion`
     LIMIT 1;
    CALL sp_rsv_marcar_replicacion(v_idReservacion, 'Actualizacion');
END$$

CREATE TRIGGER `trg_rsv_rhpaquete_ad` AFTER DELETE ON `rsv_reservacionhabitacionpaquete`
FOR EACH ROW
BEGIN
    DECLARE v_idReservacion INT DEFAULT NULL;
    SELECT `idReservacion` INTO v_idReservacion
      FROM `rsv_reservacionhabitacion`
     WHERE `idReservacionHabitacion` = OLD.`idReservacionHabitacion`
     LIMIT 1;
    CALL sp_rsv_marcar_replicacion(v_idReservacion, 'Actualizacion');
END$$

DELIMITER ;


-- -----------------------------------------------------------------------------
-- 5. Triggers sobre rsv_reservacionhabitacionextra (extras)
--    Resuelve la reservación padre vía idReservacionHabitacion.
-- -----------------------------------------------------------------------------
DROP TRIGGER IF EXISTS `trg_rsv_rhextra_ai`;
DROP TRIGGER IF EXISTS `trg_rsv_rhextra_au`;
DROP TRIGGER IF EXISTS `trg_rsv_rhextra_ad`;

DELIMITER $$

CREATE TRIGGER `trg_rsv_rhextra_ai` AFTER INSERT ON `rsv_reservacionhabitacionextra`
FOR EACH ROW
BEGIN
    DECLARE v_idReservacion INT DEFAULT NULL;
    SELECT `idReservacion` INTO v_idReservacion
      FROM `rsv_reservacionhabitacion`
     WHERE `idReservacionHabitacion` = NEW.`idReservacionHabitacion`
     LIMIT 1;
    CALL sp_rsv_marcar_replicacion(v_idReservacion, 'Actualizacion');
END$$

CREATE TRIGGER `trg_rsv_rhextra_au` AFTER UPDATE ON `rsv_reservacionhabitacionextra`
FOR EACH ROW
BEGIN
    DECLARE v_idReservacion INT DEFAULT NULL;
    SELECT `idReservacion` INTO v_idReservacion
      FROM `rsv_reservacionhabitacion`
     WHERE `idReservacionHabitacion` = NEW.`idReservacionHabitacion`
     LIMIT 1;
    CALL sp_rsv_marcar_replicacion(v_idReservacion, 'Actualizacion');
END$$

CREATE TRIGGER `trg_rsv_rhextra_ad` AFTER DELETE ON `rsv_reservacionhabitacionextra`
FOR EACH ROW
BEGIN
    DECLARE v_idReservacion INT DEFAULT NULL;
    SELECT `idReservacion` INTO v_idReservacion
      FROM `rsv_reservacionhabitacion`
     WHERE `idReservacionHabitacion` = OLD.`idReservacionHabitacion`
     LIMIT 1;
    CALL sp_rsv_marcar_replicacion(v_idReservacion, 'Actualizacion');
END$$

DELIMITER ;


-- =============================================================================
--  6. VERIFICACIÓN (ejecutar tras instalar)
-- =============================================================================
--  6.1 Los 12 triggers deben aparecer:
--      SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING
--        FROM information_schema.TRIGGERS
--       WHERE TRIGGER_SCHEMA = DATABASE()
--         AND EVENT_OBJECT_TABLE LIKE 'rsv_reservacion%'
--       ORDER BY EVENT_OBJECT_TABLE, EVENT_MANIPULATION;
--
--  6.2 El procedimiento debe existir:
--      SHOW PROCEDURE STATUS WHERE Db = DATABASE() AND Name = 'sp_rsv_marcar_replicacion';
--
--  6.3 Prueba funcional (usa un idReservacion Temporal REAL de rsv_reservacion):
--      -- SET @idr := (SELECT idReservacion FROM rsv_reservacion
--      --              WHERE reservacionEstado = 'Temporal' ORDER BY idReservacion DESC LIMIT 1);
--      -- UPDATE rsv_reservacion SET reservacionNotas = reservacionNotas WHERE idReservacion = @idr;
--      -- SELECT * FROM rsv_replicacion WHERE idReservacion = @idr;   -- debe haber 1 marca Pendiente
-- =============================================================================


-- =============================================================================
--  7. REVERSA (rollback)  —  descomentar y ejecutar para desinstalar la Capa 2
-- =============================================================================
-- DROP TRIGGER IF EXISTS `trg_rsv_reservacion_ai`;
-- DROP TRIGGER IF EXISTS `trg_rsv_reservacion_au`;
-- DROP TRIGGER IF EXISTS `trg_rsv_reservacion_ad`;
-- DROP TRIGGER IF EXISTS `trg_rsv_rhabitacion_ai`;
-- DROP TRIGGER IF EXISTS `trg_rsv_rhabitacion_au`;
-- DROP TRIGGER IF EXISTS `trg_rsv_rhabitacion_ad`;
-- DROP TRIGGER IF EXISTS `trg_rsv_rhpaquete_ai`;
-- DROP TRIGGER IF EXISTS `trg_rsv_rhpaquete_au`;
-- DROP TRIGGER IF EXISTS `trg_rsv_rhpaquete_ad`;
-- DROP TRIGGER IF EXISTS `trg_rsv_rhextra_ai`;
-- DROP TRIGGER IF EXISTS `trg_rsv_rhextra_au`;
-- DROP TRIGGER IF EXISTS `trg_rsv_rhextra_ad`;
-- DROP PROCEDURE IF EXISTS `sp_rsv_marcar_replicacion`;
-- =============================================================================
--  FIN DEL SCRIPT
-- =============================================================================