-- =============================================
-- msj_whatsappwebhook — Bandeja cruda del webhook de WhatsApp (Meta Cloud API)
-- Generado: 2026-08-24
--
-- MOTIVO
--   Meta empuja a una sola URL TODO lo que pasa en la cuenta de WhatsApp
--   Business: mensajes entrantes del socio, acuses de entrega y lectura de las
--   plantillas que ya manda `WhatsAppChannel`, cambios de calidad del número,
--   aprobaciones y rechazos de plantillas, alertas de la cuenta... Hoy nada de
--   eso se guarda: se manda y no se sabe qué contestaron.
--
--   Esta tabla es la bandeja de entrada en crudo. Guarda **una fila por
--   notificación HTTP recibida**, con el cuerpo tal cual llegó y SIN filtrar por
--   tipo de evento. Las columnas sueltas son sólo un resumen para poder buscar;
--   la verdad completa siempre vive en `whatsappWebhookPayload`.
--
-- POR QUÉ UNA FILA POR PETICIÓN Y NO POR MENSAJE
--   Porque el requisito es no perder nada. Partir el sobre en eventos obliga a
--   decidir qué es un evento, y cualquier `field` nuevo que Meta invente —la
--   lista crece cada versión de la API— se quedaría fuera. Guardando el sobre
--   entero, un `field` desconocido igual queda registrado y se puede procesar
--   después. `whatsappWebhookEventos` dice cuántos traía el lote.
--
-- POR QUÉ NO HAY UNIQUE
--   Meta reintenta durante 36 horas cuando no recibe 200, así que un mismo
--   evento puede llegar varias veces. Un UNIQUE convertiría el reintento en un
--   error 1062 y perderíamos la evidencia de que Meta reintentó. En su lugar se
--   guarda `whatsappWebhookHuella` (SHA-256 del cuerpo crudo) indexada: quien
--   procese la bandeja deduplica por huella o por `whatsappWebhookEventoId`,
--   pero la recepción nunca rechaza.
--
-- POR QUÉ EL PREFIJO ES `msj_` Y NO `not_`
--   `msj_` (módulo Mensajería) es un prefijo NUEVO en el catálogo del proyecto,
--   acordado para esta tabla.
--
--   `not_` nombra lo que el sistema DECIDE EMITIR: una notificación, su canal y
--   su bitácora de envío. Esta bandeja trae también lo que el socio contesta y
--   lo que Meta avisa por su cuenta —plantillas rechazadas, calidad del número,
--   alertas de la cuenta—, que no son notificaciones de nadie.
--
--   La línea que separa los dos módulos:
--     · `not_` → lo que el sistema emite.
--     · `msj_` → el tráfico crudo del canal, en cualquier dirección.
--
--   El módulo queda abierto para lo que sigue (`msj_whatsappconversacion`,
--   `msj_whatsappplantilla`) sin volver a renombrar.
--
-- EJECUCIÓN: manual, en orden.
-- =============================================


-- =============================================
-- PASO 1 — La tabla no debe existir todavía.
--
-- Debe devolver 0 filas. Si devuelve alguna, DETENERSE y revisar por qué.
-- =============================================

SELECT `TABLE_NAME`
  FROM `information_schema`.`TABLES`
 WHERE `TABLE_SCHEMA` = DATABASE()
   AND `TABLE_NAME`   = 'msj_whatsappwebhook';


-- =============================================
-- PASO 2 — Creación
-- =============================================

CREATE TABLE `msj_whatsappwebhook` (

    `idWhatsappWebhook` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

    -- ---- Resumen del sobre (para buscar; el detalle va en el payload) ----

    -- Campo `object` de Meta. Hoy siempre 'whatsapp_business_account', pero se
    -- guarda tal cual por si algún día llega otro producto a la misma URL.
    `whatsappWebhookObjeto` VARCHAR(60) NULL
        COMMENT 'object del payload (whatsapp_business_account)',

    -- `entry[].id`: el WhatsApp Business Account ID (WABA).
    `whatsappWebhookCuentaId` VARCHAR(64) NULL
        COMMENT 'entry[].id — WhatsApp Business Account ID',

    -- `entry[].changes[].field`: messages, message_template_status_update,
    -- phone_number_quality_update, account_alerts, security, history, ...
    -- Es texto libre a propósito: la lista de campos suscribibles de Meta crece
    -- y un ENUM obligaría a un ALTER cada vez.
    `whatsappWebhookCampo` VARCHAR(64) NULL
        COMMENT 'entry[].changes[].field suscrito en Meta',

    -- Clasificación propia, derivada del contenido de `value`, para no tener que
    -- abrir el JSON en las consultas del día a día.
    `whatsappWebhookTipo` ENUM('Mensaje','Estado','Error','Otro') NULL
        COMMENT 'Mensaje entrante / acuse de estado / error / otro evento',

    -- `value.metadata.phone_number_id`: el número de la empresa que recibió.
    `whatsappWebhookNumeroId` VARCHAR(64) NULL
        COMMENT 'value.metadata.phone_number_id',

    -- `messages[].from` o `statuses[].recipient_id`: el teléfono del socio.
    `whatsappWebhookTelefono` VARCHAR(32) NULL
        COMMENT 'Teléfono del contacto (from / recipient_id)',

    -- `messages[].id` o `statuses[].id` — el wamid. Es la llave con la que se
    -- amarra un acuse con el envío que hizo WhatsAppChannel.
    `whatsappWebhookEventoId` VARCHAR(191) NULL
        COMMENT 'wamid del primer mensaje/estado del lote',

    -- Cuántos mensajes + estados + errores traía el sobre. Sirve para detectar
    -- lotes grandes sin abrir el JSON.
    `whatsappWebhookEventos` SMALLINT UNSIGNED NOT NULL DEFAULT 0
        COMMENT 'Total de eventos contenidos en el payload',

    -- ---- La verdad completa ----

    `whatsappWebhookPayload` JSON NOT NULL
        COMMENT 'Cuerpo completo recibido, sin filtrar ni recortar',

    -- SHA-256 del cuerpo crudo. Deduplicación de reintentos SIN rechazar.
    `whatsappWebhookHuella` CHAR(64) NOT NULL
        COMMENT 'SHA-256 del cuerpo crudo, para detectar reintentos de Meta',

    -- ---- Procedencia ----

    -- Header X-Hub-Signature-256 tal cual llegó ('sha256=...').
    `whatsappWebhookFirma` VARCHAR(191) NULL
        COMMENT 'Header X-Hub-Signature-256 recibido',

    -- 'NoVerificada' cuando no hay App Secret configurado: la fila se guarda
    -- igual, pero queda marcada para que nadie la dé por auténtica.
    `whatsappWebhookFirmaValida` ENUM('Si','No','NoVerificada') NOT NULL DEFAULT 'NoVerificada'
        COMMENT 'Resultado de validar la firma HMAC contra el App Secret',

    `whatsappWebhookIP` VARCHAR(45) NULL
        COMMENT 'IP de origen de la petición',

    `whatsappWebhookFecha` DATETIME NOT NULL
        COMMENT 'Momento en que se recibió la notificación',

    -- 'Activo' = recibido y sin procesar. Quien consuma la bandeja lo pasa a
    -- 'Procesado'. 'Eliminado' es el borrado lógico de la casa.
    `whatsappWebhookEstado` ENUM('Activo','Procesado','Eliminado') NOT NULL DEFAULT 'Activo'
        COMMENT 'Estado del registro dentro de la bandeja',

    PRIMARY KEY (`idWhatsappWebhook`),

    -- Recorrido natural de la bandeja: lo pendiente, lo más reciente primero.
    KEY `ix_whatsappwebhook_estado_fecha` (`whatsappWebhookEstado`, `whatsappWebhookFecha`),

    -- Amarrar un acuse con el envío que lo originó.
    KEY `ix_whatsappwebhook_eventoid` (`whatsappWebhookEventoId`),

    -- Detectar reintentos de Meta.
    KEY `ix_whatsappwebhook_huella` (`whatsappWebhookHuella`),

    -- Filtrar por tipo de suscripción (sólo mensajes, sólo estados...).
    KEY `ix_whatsappwebhook_campo_fecha` (`whatsappWebhookCampo`, `whatsappWebhookFecha`),

    -- Toda la conversación de un socio.
    KEY `ix_whatsappwebhook_telefono_fecha` (`whatsappWebhookTelefono`, `whatsappWebhookFecha`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Bandeja cruda de notificaciones del webhook de WhatsApp (Meta Cloud API)';


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

SHOW CREATE TABLE `msj_whatsappwebhook`;

SHOW INDEX FROM `msj_whatsappwebhook`;

-- Debe estar vacía hasta que Meta mande la primera notificación.
SELECT COUNT(*) AS recibidas FROM `msj_whatsappwebhook`;


-- =============================================
-- ROLLBACK
-- =============================================
-- DROP TABLE `msj_whatsappwebhook`;