-- Engine de Recordatorios Automáticos
-- Sistema para enviar recordatorios de pagos RNPL y invitaciones de reviews

-- Tabla de tareas de recordatorios programados
CREATE TABLE IF NOT EXISTS scheduled_reminders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Identificación del recordatorio
    tipo ENUM('rnpl_payment', 'review_invitation', 'payment_overdue', 'booking_confirmation') NOT NULL,
    referencia_id INT NOT NULL, -- ID de la reserva, invitación, etc.
    referencia_tipo ENUM('reserva', 'review_invitation', 'payment_transaction') NOT NULL,
    
    -- Información del destinatario
    destinatario_email VARCHAR(255) NOT NULL,
    destinatario_nombre VARCHAR(255) NOT NULL,
    
    -- Datos contextuales
    contexto JSON, -- Información adicional para personalizar el mensaje
    
    -- Programación
    fecha_programada DATETIME NOT NULL,
    fecha_limite DATETIME NULL, -- Fecha límite para el recordatorio (opcional)
    
    -- Estado del recordatorio
    estado ENUM('programado', 'enviado', 'fallido', 'cancelado', 'expirado') DEFAULT 'programado',
    intentos_envio TINYINT DEFAULT 0,
    max_intentos TINYINT DEFAULT 3,
    
    -- Resultado del envío
    fecha_enviado DATETIME NULL,
    fecha_ultimo_intento DATETIME NULL,
    mensaje_error TEXT NULL,
    
    -- Prioridad
    prioridad ENUM('baja', 'media', 'alta', 'urgente') DEFAULT 'media',
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Índices
    INDEX idx_fecha_programada (fecha_programada, estado),
    INDEX idx_tipo_referencia (tipo, referencia_id),
    INDEX idx_destinatario (destinatario_email),
    INDEX idx_estado_prioridad (estado, prioridad),
    INDEX idx_limite (fecha_limite)
);

-- Tabla de configuración del engine de recordatorios
CREATE TABLE IF NOT EXISTS reminder_configuracion (
    id INT PRIMARY KEY AUTO_INCREMENT,
    clave VARCHAR(100) NOT NULL UNIQUE,
    valor TEXT NOT NULL,
    descripcion TEXT NOT NULL,
    categoria ENUM('rnpl', 'reviews', 'general', 'email') DEFAULT 'general',
    tipo ENUM('string', 'int', 'boolean', 'json', 'datetime') DEFAULT 'string',
    
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Configuraciones iniciales
INSERT INTO reminder_configuracion (clave, valor, descripcion, categoria, tipo) VALUES
-- Configuraciones RNPL
('rnpl_reminder_days_before', '7,3,1', 'Días antes del tour para enviar recordatorios de pago RNPL', 'rnpl', 'string'),
('rnpl_grace_period_hours', '48', 'Horas de gracia después del vencimiento para pagos RNPL', 'rnpl', 'int'),
('rnpl_overdue_reminder_hours', '6,24,48', 'Horas después del vencimiento para recordatorios', 'rnpl', 'string'),

-- Configuraciones de Reviews
('review_invitation_days_after', '3', 'Días después del tour para enviar invitación de review', 'reviews', 'int'),
('review_reminder_days', '7,14', 'Días adicionales para recordatorios de review', 'reviews', 'string'),
('review_invitation_expiry_days', '30', 'Días de validez de invitaciones de review', 'reviews', 'int'),

-- Configuraciones de Email
('email_max_retries', '3', 'Máximo número de reintentos para envío de emails', 'email', 'int'),
('email_retry_delay_minutes', '30,120,360', 'Minutos de espera entre reintentos (por intento)', 'email', 'string'),
('email_batch_size', '50', 'Número máximo de emails a procesar por lote', 'email', 'int'),

-- Configuraciones Generales
('engine_enabled', 'true', 'Activar o desactivar el engine de recordatorios', 'general', 'boolean'),
('max_processing_time_minutes', '10', 'Tiempo máximo de procesamiento por ejecución', 'general', 'int'),
('debug_mode', 'false', 'Modo debug para logging detallado', 'general', 'boolean');

-- Tabla de logs del engine de recordatorios
CREATE TABLE IF NOT EXISTS reminder_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    reminder_id INT NULL,
    
    -- Información del evento
    evento ENUM('programado', 'enviado', 'fallido', 'cancelado', 'procesado', 'error') NOT NULL,
    mensaje TEXT NOT NULL,
    
    -- Contexto técnico
    detalles JSON NULL,
    
    -- Timestamp
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_reminder (reminder_id),
    INDEX idx_evento_fecha (evento, created_at),
    
    FOREIGN KEY (reminder_id) REFERENCES scheduled_reminders(id) ON DELETE SET NULL
);

-- Tabla de estadísticas del engine
CREATE TABLE IF NOT EXISTS reminder_estadisticas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    fecha_proceso DATE NOT NULL UNIQUE,
    
    -- Contadores por tipo
    rnpl_enviados INT DEFAULT 0,
    reviews_enviados INT DEFAULT 0,
    confirmaciones_enviadas INT DEFAULT 0,
    
    -- Contadores por resultado
    exitosos INT DEFAULT 0,
    fallidos INT DEFAULT 0,
    cancelados INT DEFAULT 0,
    
    -- Tiempos de procesamiento
    tiempo_procesamiento_segundos INT DEFAULT 0,
    recordatorios_procesados INT DEFAULT 0,
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Vista para recordatorios pendientes (próximos a enviar)
CREATE OR REPLACE VIEW recordatorios_pendientes AS
SELECT 
    sr.*,
    TIMESTAMPDIFF(MINUTE, NOW(), sr.fecha_programada) as minutos_hasta_envio,
    CASE 
        WHEN sr.fecha_limite IS NOT NULL AND NOW() > sr.fecha_limite THEN 'expirado'
        WHEN sr.fecha_programada <= NOW() THEN 'listo_para_envio'
        ELSE 'programado'
    END as estado_calculado,
    JSON_EXTRACT(sr.contexto, '$.producto_nombre') as producto_nombre,
    JSON_EXTRACT(sr.contexto, '$.fecha_tour') as fecha_tour,
    JSON_EXTRACT(sr.contexto, '$.monto_pendiente') as monto_pendiente
FROM scheduled_reminders sr
WHERE sr.estado IN ('programado')
ORDER BY sr.prioridad DESC, sr.fecha_programada ASC;

-- Procedimiento para programar recordatorios RNPL
DELIMITER //
CREATE PROCEDURE ProgramarRecordatoriosRNPL(
    IN reserva_id INT,
    IN email VARCHAR(255),
    IN nombre VARCHAR(255),
    IN fecha_tour DATETIME,
    IN monto_pendiente DECIMAL(10,2),
    IN producto_nombre VARCHAR(255)
)
BEGIN
    DECLARE dias_recordatorio VARCHAR(50);
    DECLARE dia INT;
    DECLARE pos INT;
    DECLARE len INT;
    DECLARE contexto_json JSON;
    
    -- Obtener configuración de días de recordatorio
    SELECT valor INTO dias_recordatorio 
    FROM reminder_configuracion 
    WHERE clave = 'rnpl_reminder_days_before';
    
    -- Preparar contexto
    SET contexto_json = JSON_OBJECT(
        'reserva_id', reserva_id,
        'producto_nombre', producto_nombre,
        'fecha_tour', fecha_tour,
        'monto_pendiente', monto_pendiente,
        'moneda', 'USD'
    );
    
    -- Procesar cada día de recordatorio
    SET len = CHAR_LENGTH(dias_recordatorio);
    SET pos = 1;
    
    WHILE pos <= len DO
        SET dia = CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(dias_recordatorio, ',', pos), ',', -1) AS UNSIGNED);
        
        -- Programar recordatorio
        INSERT INTO scheduled_reminders (
            tipo, referencia_id, referencia_tipo,
            destinatario_email, destinatario_nombre,
            contexto, fecha_programada, fecha_limite,
            prioridad
        ) VALUES (
            'rnpl_payment', reserva_id, 'reserva',
            email, nombre,
            contexto_json,
            DATE_SUB(fecha_tour, INTERVAL dia DAY),
            fecha_tour,
            CASE 
                WHEN dia = 1 THEN 'urgente'
                WHEN dia <= 3 THEN 'alta'
                ELSE 'media'
            END
        );
        
        SET pos = pos + 1;
    END WHILE;
END//

-- Procedimiento para programar invitación de review
CREATE PROCEDURE ProgramarInvitacionReview(
    IN reserva_id INT,
    IN producto_id INT,
    IN email VARCHAR(255),
    IN nombre VARCHAR(255),
    IN fecha_tour DATETIME,
    IN producto_nombre VARCHAR(255)
)
BEGIN
    DECLARE dias_espera INT;
    DECLARE contexto_json JSON;
    
    -- Obtener configuración
    SELECT CAST(valor AS UNSIGNED) INTO dias_espera
    FROM reminder_configuracion 
    WHERE clave = 'review_invitation_days_after';
    
    -- Preparar contexto
    SET contexto_json = JSON_OBJECT(
        'reserva_id', reserva_id,
        'producto_id', producto_id,
        'producto_nombre', producto_nombre,
        'fecha_tour', fecha_tour
    );
    
    -- Programar invitación inicial
    INSERT INTO scheduled_reminders (
        tipo, referencia_id, referencia_tipo,
        destinatario_email, destinatario_nombre,
        contexto, fecha_programada, fecha_limite,
        prioridad
    ) VALUES (
        'review_invitation', reserva_id, 'reserva',
        email, nombre,
        contexto_json,
        DATE_ADD(fecha_tour, INTERVAL dias_espera DAY),
        DATE_ADD(fecha_tour, INTERVAL 45 DAY), -- 45 días límite
        'media'
    );
END//
DELIMITER ;

-- Índices adicionales para optimización
CREATE INDEX idx_scheduled_reminders_processing ON scheduled_reminders(estado, fecha_programada, prioridad);
CREATE INDEX idx_scheduled_reminders_cleanup ON scheduled_reminders(estado, created_at);
CREATE INDEX idx_reminder_logs_fecha ON reminder_logs(created_at DESC);

-- Trigger para actualizar estadísticas automáticamente
DELIMITER //
CREATE TRIGGER update_reminder_stats_after_update
AFTER UPDATE ON scheduled_reminders
FOR EACH ROW
BEGIN
    DECLARE fecha_hoy DATE DEFAULT CURDATE();
    
    -- Solo procesar si cambió el estado a enviado, fallido o cancelado
    IF NEW.estado IN ('enviado', 'fallido', 'cancelado') AND OLD.estado != NEW.estado THEN
        
        -- Crear o actualizar estadística del día
        INSERT INTO reminder_estadisticas (
            fecha_proceso,
            rnpl_enviados,
            reviews_enviados,
            confirmaciones_enviadas,
            exitosos,
            fallidos,
            cancelados,
            recordatorios_procesados
        ) VALUES (
            fecha_hoy,
            CASE WHEN NEW.tipo = 'rnpl_payment' AND NEW.estado = 'enviado' THEN 1 ELSE 0 END,
            CASE WHEN NEW.tipo = 'review_invitation' AND NEW.estado = 'enviado' THEN 1 ELSE 0 END,
            CASE WHEN NEW.tipo = 'booking_confirmation' AND NEW.estado = 'enviado' THEN 1 ELSE 0 END,
            CASE WHEN NEW.estado = 'enviado' THEN 1 ELSE 0 END,
            CASE WHEN NEW.estado = 'fallido' THEN 1 ELSE 0 END,
            CASE WHEN NEW.estado = 'cancelado' THEN 1 ELSE 0 END,
            1
        ) ON DUPLICATE KEY UPDATE
            rnpl_enviados = rnpl_enviados + CASE WHEN NEW.tipo = 'rnpl_payment' AND NEW.estado = 'enviado' THEN 1 ELSE 0 END,
            reviews_enviados = reviews_enviados + CASE WHEN NEW.tipo = 'review_invitation' AND NEW.estado = 'enviado' THEN 1 ELSE 0 END,
            confirmaciones_enviadas = confirmaciones_enviadas + CASE WHEN NEW.tipo = 'booking_confirmation' AND NEW.estado = 'enviado' THEN 1 ELSE 0 END,
            exitosos = exitosos + CASE WHEN NEW.estado = 'enviado' THEN 1 ELSE 0 END,
            fallidos = fallidos + CASE WHEN NEW.estado = 'fallido' THEN 1 ELSE 0 END,
            cancelados = cancelados + CASE WHEN NEW.estado = 'cancelado' THEN 1 ELSE 0 END,
            recordatorios_procesados = recordatorios_procesados + 1,
            updated_at = CURRENT_TIMESTAMP;
    END IF;
END//
DELIMITER ;