-- Sistema de Push Notifications para PWA Avanzada
-- Base de datos para notificaciones push, suscripciones y campañas

-- ==================== TABLA PRINCIPAL DE SUSCRIPCIONES ====================

CREATE TABLE IF NOT EXISTS push_subscriptions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NULL,                    -- NULL para usuarios anónimos
    user_email VARCHAR(255) NULL,       -- Email para identificación
    endpoint TEXT NOT NULL,             -- URL del endpoint push
    p256dh_key TEXT NOT NULL,           -- Clave pública para encriptación
    auth_token TEXT NOT NULL,           -- Token de autenticación
    device_type ENUM('desktop', 'mobile', 'tablet') DEFAULT 'desktop',
    browser VARCHAR(100) NULL,          -- Chrome, Firefox, Safari, etc.
    platform VARCHAR(100) NULL,        -- Windows, macOS, Android, iOS
    user_agent TEXT NULL,              -- Full user agent string
    language VARCHAR(10) DEFAULT 'es', -- Idioma preferido para notificaciones
    timezone VARCHAR(50) DEFAULT 'America/Guatemala',
    is_active BOOLEAN DEFAULT TRUE,     -- Suscripción activa
    permissions JSON NULL,              -- Permisos específicos otorgados
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_notification_at TIMESTAMP NULL, -- Última notificación enviada
    
    INDEX idx_user_id (user_id),
    INDEX idx_user_email (user_email),
    INDEX idx_active (is_active),
    INDEX idx_device_type (device_type),
    INDEX idx_created_at (created_at),
    
    FOREIGN KEY (user_id) REFERENCES usuarios(id) ON DELETE SET NULL
);

-- ==================== TABLA DE NOTIFICACIONES ENVIADAS ====================

CREATE TABLE IF NOT EXISTS push_notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    campaign_id INT NULL,               -- ID de campaña si es parte de una
    subscription_id INT NOT NULL,      -- Suscripción destino
    title VARCHAR(255) NOT NULL,       -- Título de la notificación
    body TEXT NOT NULL,                -- Contenido principal
    icon VARCHAR(255) DEFAULT '/assets/images/notification-icon.png',
    badge VARCHAR(255) DEFAULT '/assets/images/notification-badge.png',
    image VARCHAR(255) NULL,           -- Imagen opcional grande
    click_action VARCHAR(255) DEFAULT '/', -- URL de destino al hacer clic
    actions JSON NULL,                 -- Botones de acción personalizados
    data JSON NULL,                    -- Datos adicionales personalizados
    
    -- Configuración de la notificación
    require_interaction BOOLEAN DEFAULT FALSE,
    vibrate VARCHAR(100) DEFAULT '[200,100,200]', -- Patrón de vibración
    silent BOOLEAN DEFAULT FALSE,      -- Notificación silenciosa
    tag VARCHAR(100) NULL,             -- Tag para agrupar notificaciones
    
    -- Estado y tracking
    status ENUM('pending', 'sent', 'delivered', 'clicked', 'dismissed', 'failed') DEFAULT 'pending',
    sent_at TIMESTAMP NULL,            -- Cuándo se envió
    delivered_at TIMESTAMP NULL,       -- Cuándo se entregó
    clicked_at TIMESTAMP NULL,         -- Cuándo se hizo clic
    dismissed_at TIMESTAMP NULL,       -- Cuándo se descartó
    error_message TEXT NULL,           -- Mensaje de error si falló
    retry_count INT DEFAULT 0,         -- Número de reintentos
    
    -- Métricas y analytics
    delivery_time_ms INT NULL,         -- Tiempo de entrega en ms
    click_delay_seconds INT NULL,      -- Tiempo desde envío hasta clic
    device_online BOOLEAN DEFAULT TRUE, -- Dispositivo estaba online
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_campaign_id (campaign_id),
    INDEX idx_subscription_id (subscription_id),
    INDEX idx_status (status),
    INDEX idx_sent_at (sent_at),
    INDEX idx_clicked_at (clicked_at),
    INDEX idx_tag (tag),
    INDEX idx_created_at (created_at),
    
    FOREIGN KEY (subscription_id) REFERENCES push_subscriptions(id) ON DELETE CASCADE,
    FOREIGN KEY (campaign_id) REFERENCES push_campaigns(id) ON DELETE SET NULL
);

-- ==================== TABLA DE CAMPAÑAS DE MARKETING ====================

CREATE TABLE IF NOT EXISTS push_campaigns (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,        -- Nombre de la campaña
    description TEXT NULL,             -- Descripción interna
    type ENUM('marketing', 'transactional', 'reminder', 'emergency', 'referral', 'booking') DEFAULT 'marketing',
    
    -- Configuración de la campaña
    title VARCHAR(255) NOT NULL,       -- Título de la notificación
    body TEXT NOT NULL,               -- Contenido del mensaje
    icon VARCHAR(255) DEFAULT '/assets/images/notification-icon.png',
    badge VARCHAR(255) DEFAULT '/assets/images/notification-badge.png',
    image VARCHAR(255) NULL,          -- Imagen opcional
    click_action VARCHAR(255) DEFAULT '/',
    actions JSON NULL,                -- Botones de acción
    
    -- Targeting y segmentación
    target_audience JSON NULL,        -- Criterios de segmentación
    device_types JSON DEFAULT '["desktop","mobile","tablet"]',
    languages JSON DEFAULT '["es","en"]',
    user_segments JSON NULL,          -- Segmentos de usuarios objetivo
    
    -- Programación
    status ENUM('draft', 'scheduled', 'sending', 'sent', 'paused', 'cancelled') DEFAULT 'draft',
    scheduled_at TIMESTAMP NULL,      -- Cuándo enviar (NULL = inmediato)
    timezone VARCHAR(50) DEFAULT 'America/Guatemala',
    send_immediately BOOLEAN DEFAULT FALSE,
    
    -- Límites y control de frecuencia
    max_recipients INT NULL,          -- Límite máximo de destinatarios
    daily_cap INT NULL,               -- Límite diario de envíos
    frequency_cap_hours INT DEFAULT 24, -- Horas mínimas entre notificaciones al mismo usuario
    
    -- Métricas de la campaña
    recipients_count INT DEFAULT 0,    -- Total de destinatarios
    sent_count INT DEFAULT 0,         -- Total enviadas
    delivered_count INT DEFAULT 0,    -- Total entregadas
    clicked_count INT DEFAULT 0,      -- Total con clic
    failed_count INT DEFAULT 0,       -- Total fallidas
    
    -- Análisis y conversión
    conversion_goal VARCHAR(100) NULL, -- Objetivo de conversión (booking, signup, etc.)
    conversion_count INT DEFAULT 0,    -- Conversiones logradas
    estimated_revenue DECIMAL(10,2) DEFAULT 0.00, -- Revenue estimado generado
    
    created_by INT NULL,              -- Usuario que creó la campaña
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    started_at TIMESTAMP NULL,        -- Cuándo inició el envío
    completed_at TIMESTAMP NULL,      -- Cuándo terminó el envío
    
    INDEX idx_type (type),
    INDEX idx_status (status),
    INDEX idx_scheduled_at (scheduled_at),
    INDEX idx_created_at (created_at),
    
    FOREIGN KEY (created_by) REFERENCES usuarios(id) ON DELETE SET NULL
);

-- ==================== TABLA DE TEMPLATES DE NOTIFICACIONES ====================

CREATE TABLE IF NOT EXISTS push_templates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,       -- Nombre del template
    category ENUM('booking', 'payment', 'reminder', 'marketing', 'referral', 'wishlist', 'chat') NOT NULL,
    
    -- Contenido del template
    title VARCHAR(255) NOT NULL,      -- Título con variables {{var}}
    body TEXT NOT NULL,               -- Cuerpo con variables
    icon VARCHAR(255) DEFAULT '/assets/images/notification-icon.png',
    image VARCHAR(255) NULL,
    click_action VARCHAR(255) DEFAULT '/',
    actions JSON NULL,
    
    -- Variables disponibles en el template
    available_variables JSON NULL,    -- Lista de variables disponibles
    required_variables JSON NULL,     -- Variables obligatorias
    
    -- Configuración del template
    require_interaction BOOLEAN DEFAULT FALSE,
    vibrate VARCHAR(100) DEFAULT '[200,100,200]',
    silent BOOLEAN DEFAULT FALSE,
    
    -- Estado y estadísticas
    is_active BOOLEAN DEFAULT TRUE,
    usage_count INT DEFAULT 0,        -- Cuántas veces se ha usado
    success_rate DECIMAL(5,2) DEFAULT 0.00, -- Tasa de éxito promedio
    click_rate DECIMAL(5,2) DEFAULT 0.00,   -- Tasa de clic promedio
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_category (category),
    INDEX idx_is_active (is_active),
    INDEX idx_usage_count (usage_count)
);

-- ==================== TABLA DE EVENTOS PERSONALIZADOS ====================

CREATE TABLE IF NOT EXISTS push_events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    subscription_id INT NOT NULL,     -- Suscripción que generó el evento
    event_type VARCHAR(100) NOT NULL, -- notification_shown, notification_clicked, etc.
    event_data JSON NULL,             -- Datos adicionales del evento
    notification_id INT NULL,         -- ID de notificación relacionada
    page_url VARCHAR(500) NULL,       -- URL donde ocurrió el evento
    referrer_url VARCHAR(500) NULL,   -- URL de referencia
    user_agent TEXT NULL,            -- User agent del navegador
    ip_address VARCHAR(45) NULL,      -- IP del usuario
    session_id VARCHAR(100) NULL,     -- ID de sesión del navegador
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_subscription_id (subscription_id),
    INDEX idx_event_type (event_type),
    INDEX idx_notification_id (notification_id),
    INDEX idx_created_at (created_at),
    
    FOREIGN KEY (subscription_id) REFERENCES push_subscriptions(id) ON DELETE CASCADE,
    FOREIGN KEY (notification_id) REFERENCES push_notifications(id) ON DELETE CASCADE
);

-- ==================== TABLA DE CONFIGURACIÓN DE NOTIFICACIONES POR USUARIO ====================

CREATE TABLE IF NOT EXISTS user_notification_preferences (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NULL,                 -- NULL para usuarios anónimos
    subscription_id INT NOT NULL,     -- Suscripción específica
    
    -- Preferencias por categoría
    booking_notifications BOOLEAN DEFAULT TRUE,     -- Confirmaciones, recordatorios
    payment_notifications BOOLEAN DEFAULT TRUE,     -- Pagos, RNPL
    marketing_notifications BOOLEAN DEFAULT TRUE,   -- Ofertas, descuentos
    referral_notifications BOOLEAN DEFAULT TRUE,    -- Programa de referidos
    wishlist_notifications BOOLEAN DEFAULT TRUE,    -- Alertas de wishlist
    chat_notifications BOOLEAN DEFAULT TRUE,        -- Mensajes de chat
    
    -- Configuración de horarios
    quiet_hours_start TIME DEFAULT '22:00:00',      -- Inicio de horas silenciosas
    quiet_hours_end TIME DEFAULT '08:00:00',        -- Fin de horas silenciosas
    timezone VARCHAR(50) DEFAULT 'America/Guatemala',
    
    -- Límites de frecuencia
    max_daily_notifications INT DEFAULT 5,          -- Máximo por día
    min_hours_between INT DEFAULT 2,               -- Horas mínimas entre notificaciones
    
    -- Días permitidos (JSON array con días de la semana)
    allowed_days JSON DEFAULT '["monday","tuesday","wednesday","thursday","friday","saturday","sunday"]',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_subscription_id (subscription_id),
    
    FOREIGN KEY (user_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (subscription_id) REFERENCES push_subscriptions(id) ON DELETE CASCADE,
    
    UNIQUE KEY unique_user_subscription (user_id, subscription_id)
);

-- ==================== INSERTAR TEMPLATES PREDEFINIDOS ====================

INSERT INTO push_templates (name, category, title, body, click_action, available_variables, required_variables) VALUES
('Booking Confirmation', 'booking', 
 '¡Reserva Confirmada! {{tour_name}}', 
 'Tu reserva para {{tour_name}} el {{date}} está confirmada. ¡Prepárate para una aventura increíble!',
 '/?route=booking/confirmation/{{booking_id}}',
 '["tour_name", "date", "booking_id", "user_name", "total_amount"]',
 '["tour_name", "booking_id"]'),

('Payment Reminder', 'payment',
 '⏰ Recordatorio de Pago - {{tour_name}}',
 'Tu reserva vence en {{hours_remaining}} horas. Completa tu pago para asegurar tu lugar.',
 '/?route=booking/payment/{{booking_id}}',
 '["tour_name", "hours_remaining", "booking_id", "amount"]',
 '["tour_name", "booking_id", "hours_remaining"]'),

('Special Offer', 'marketing',
 '🔥 Oferta Especial: {{discount}}% OFF',
 '¡Ahorra {{discount}}% en tours mayas! Oferta válida hasta {{expiry_date}}. ¡Reserva ahora!',
 '/?route=products?promo={{promo_code}}',
 '["discount", "expiry_date", "promo_code", "tour_names"]',
 '["discount", "expiry_date"]'),

('Referral Reward', 'referral',
 '🎉 ¡Nuevo Referido! Ganaste ${{amount}}',
 '{{friend_name}} completó su reserva. ¡Has ganado ${{amount}} en comisiones!',
 '/?route=referral/dashboard',
 '["friend_name", "amount", "referral_code"]',
 '["friend_name", "amount"]'),

('Wishlist Alert', 'wishlist',
 '❤️ ¡Tour en Descuento! {{tour_name}}',
 'El tour {{tour_name}} de tu wishlist ahora tiene {{discount}}% de descuento. ¡No te lo pierdas!',
 '/?route=product/{{product_id}}',
 '["tour_name", "discount", "product_id", "original_price", "new_price"]',
 '["tour_name", "product_id"]'),

('New Chat Message', 'chat',
 '💬 Nuevo Mensaje de {{sender_name}}',
 'Tienes un nuevo mensaje: "{{message_preview}}"',
 '/?route=chat',
 '["sender_name", "message_preview", "conversation_id"]',
 '["sender_name"]');

-- ==================== VISTAS ÚTILES ====================

CREATE OR REPLACE VIEW push_analytics_summary AS
SELECT 
    DATE(pn.created_at) as date,
    COUNT(*) as total_sent,
    SUM(CASE WHEN pn.status = 'delivered' THEN 1 ELSE 0 END) as delivered,
    SUM(CASE WHEN pn.status = 'clicked' THEN 1 ELSE 0 END) as clicked,
    SUM(CASE WHEN pn.status = 'failed' THEN 1 ELSE 0 END) as failed,
    ROUND(AVG(CASE WHEN pn.status = 'delivered' THEN 1 ELSE 0 END) * 100, 2) as delivery_rate,
    ROUND(AVG(CASE WHEN pn.status = 'clicked' THEN 1 ELSE 0 END) * 100, 2) as click_rate,
    ROUND(AVG(pn.delivery_time_ms), 2) as avg_delivery_time_ms,
    ROUND(AVG(pn.click_delay_seconds), 2) as avg_click_delay_seconds
FROM push_notifications pn
WHERE pn.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(pn.created_at)
ORDER BY date DESC;

CREATE OR REPLACE VIEW active_subscriptions_summary AS
SELECT 
    ps.device_type,
    ps.browser,
    ps.language,
    COUNT(*) as subscription_count,
    COUNT(CASE WHEN ps.last_notification_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) as active_last_week,
    COUNT(CASE WHEN ps.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as new_this_month
FROM push_subscriptions ps
WHERE ps.is_active = TRUE
GROUP BY ps.device_type, ps.browser, ps.language
ORDER BY subscription_count DESC;

-- ==================== STORED PROCEDURES ====================

DELIMITER $$

CREATE PROCEDURE GetOptimalSendTime(
    IN target_timezone VARCHAR(50),
    OUT optimal_hour INT,
    OUT optimal_minute INT
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE hour_performance DECIMAL(5,2);
    DECLARE current_hour INT DEFAULT 6;
    DECLARE best_hour INT DEFAULT 10;
    DECLARE best_performance DECIMAL(5,2) DEFAULT 0;
    
    -- Buscar la hora con mejor performance en los últimos 30 días
    WHILE current_hour <= 22 DO
        SELECT AVG(CASE WHEN status = 'clicked' THEN 1 ELSE 0 END) * 100
        INTO hour_performance
        FROM push_notifications pn
        JOIN push_subscriptions ps ON pn.subscription_id = ps.id
        WHERE ps.timezone = target_timezone
          AND HOUR(pn.sent_at) = current_hour
          AND pn.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);
        
        IF hour_performance > best_performance THEN
            SET best_performance = hour_performance;
            SET best_hour = current_hour;
        END IF;
        
        SET current_hour = current_hour + 1;
    END WHILE;
    
    SET optimal_hour = best_hour;
    SET optimal_minute = 0; -- Por simplicidad, siempre en punto
END$$

CREATE PROCEDURE CleanupOldNotifications()
BEGIN
    DECLARE deleted_count INT DEFAULT 0;
    
    -- Eliminar notificaciones antiguas (>90 días) excepto las que tuvieron clic
    DELETE FROM push_notifications 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
      AND status NOT IN ('clicked', 'delivered');
    
    SET deleted_count = ROW_COUNT();
    
    -- Eliminar eventos antiguos (>60 días)
    DELETE FROM push_events 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 60 DAY);
    
    -- Log de limpieza
    INSERT INTO push_events (subscription_id, event_type, event_data)
    SELECT 1, 'cleanup_completed', JSON_OBJECT('deleted_notifications', deleted_count, 'cleanup_date', NOW())
    LIMIT 1;
END$$

DELIMITER ;

-- ==================== TRIGGERS ====================

DELIMITER $$

CREATE TRIGGER update_campaign_stats_after_notification_update
    AFTER UPDATE ON push_notifications
    FOR EACH ROW
BEGIN
    IF NEW.campaign_id IS NOT NULL AND OLD.status != NEW.status THEN
        UPDATE push_campaigns 
        SET 
            sent_count = (SELECT COUNT(*) FROM push_notifications WHERE campaign_id = NEW.campaign_id AND status IN ('sent', 'delivered', 'clicked')),
            delivered_count = (SELECT COUNT(*) FROM push_notifications WHERE campaign_id = NEW.campaign_id AND status IN ('delivered', 'clicked')),
            clicked_count = (SELECT COUNT(*) FROM push_notifications WHERE campaign_id = NEW.campaign_id AND status = 'clicked'),
            failed_count = (SELECT COUNT(*) FROM push_notifications WHERE campaign_id = NEW.campaign_id AND status = 'failed'),
            updated_at = NOW()
        WHERE id = NEW.campaign_id;
    END IF;
END$$

CREATE TRIGGER update_subscription_last_notification
    AFTER UPDATE ON push_notifications
    FOR EACH ROW
BEGIN
    IF NEW.status = 'sent' AND OLD.status != 'sent' THEN
        UPDATE push_subscriptions 
        SET last_notification_at = NEW.sent_at
        WHERE id = NEW.subscription_id;
    END IF;
END$$

DELIMITER ;

-- ==================== ÍNDICES ADICIONALES PARA PERFORMANCE ====================

CREATE INDEX idx_push_notifications_campaign_status ON push_notifications(campaign_id, status);
CREATE INDEX idx_push_notifications_sent_at_status ON push_notifications(sent_at, status);
CREATE INDEX idx_push_subscriptions_active_device ON push_subscriptions(is_active, device_type);
CREATE INDEX idx_push_events_created_type ON push_events(created_at, event_type);

-- ==================== DATOS DE PRUEBA ====================

-- Insertar configuraciones por defecto para usuarios existentes
INSERT INTO user_notification_preferences (user_id, subscription_id, created_at)
SELECT u.id, ps.id, NOW()
FROM usuarios u
JOIN push_subscriptions ps ON (ps.user_id = u.id OR ps.user_email = u.email)
WHERE NOT EXISTS (
    SELECT 1 FROM user_notification_preferences unp 
    WHERE unp.user_id = u.id AND unp.subscription_id = ps.id
);

-- ==================== COMENTARIOS FINALES ====================

/*
Este sistema de push notifications incluye:

1. SUSCRIPCIONES AVANZADAS:
   - Soporte para múltiples dispositivos por usuario
   - Detección automática de plataforma y navegador
   - Gestión de permisos granular

2. CAMPAÑAS INTELIGENTES:
   - Segmentación avanzada de audiencia
   - Programación inteligente basada en timezone
   - Control de frecuencia y límites diarios

3. TEMPLATES DINÁMICOS:
   - Templates reutilizables con variables
   - Categorización automática
   - Métricas de performance por template

4. ANALYTICS COMPLETOS:
   - Tracking de entrega, clics y conversiones
   - Análisis de performance por dispositivo/browser
   - Métricas de tiempo real

5. PERSONALIZACIÓN TOTAL:
   - Preferencias individuales por usuario
   - Horarios silenciosos configurables
   - Límites personalizados de frecuencia

6. OPTIMIZACIÓN AUTOMÁTICA:
   - Limpieza automática de datos antiguos
   - Cálculo de horarios óptimos de envío
   - Updates automáticos de estadísticas

El sistema está diseñado para escalar y manejar millones de notificaciones
con performance óptima y insights profundos para marketing inteligente.
*/