

-- Tabla de items de wishlist
CREATE TABLE IF NOT EXISTS wishlist_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    wishlist_id INT NOT NULL,
    producto_id INT NOT NULL,
    
    -- Metadata del item
    fecha_agregado TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    precio_cuando_agregado DECIMAL(10,2) NULL,
    notas_personales TEXT NULL,
    prioridad ENUM('baja', 'media', 'alta') DEFAULT 'media',
    
    -- Seguimiento de cambios
    ultimo_precio_visto DECIMAL(10,2) NULL,
    fecha_ultimo_cambio_precio TIMESTAMP NULL,
    notificado_cambio_precio BOOLEAN DEFAULT FALSE,
    
    -- Control de disponibilidad
    estaba_disponible BOOLEAN DEFAULT TRUE,
    fecha_ultimo_check_disponibilidad TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Índices y restricciones
    INDEX idx_wishlist_producto (wishlist_id, producto_id),
    INDEX idx_producto (producto_id),
    INDEX idx_precio_cambios (ultimo_precio_visto, fecha_ultimo_cambio_precio),
    
    UNIQUE KEY unique_wishlist_product (wishlist_id, producto_id),
    FOREIGN KEY (wishlist_id) REFERENCES wishlists(id) ON DELETE CASCADE,
    FOREIGN KEY (producto_id) REFERENCES productos(id) ON DELETE CASCADE
);

-- Tabla de notificaciones de wishlist
CREATE TABLE IF NOT EXISTS wishlist_notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    wishlist_item_id INT NOT NULL,
    
    -- Tipo y contenido de notificación
    tipo ENUM('precio_bajo', 'precio_subio', 'disponible', 'no_disponible', 'oferta_especial', 'ultimo_cupo') NOT NULL,
    titulo VARCHAR(255) NOT NULL,
    mensaje TEXT NOT NULL,
    
    -- Datos contextuales
    precio_anterior DECIMAL(10,2) NULL,
    precio_nuevo DECIMAL(10,2) NULL,
    porcentaje_cambio DECIMAL(5,2) NULL,
    
    -- Estado de la notificación
    estado ENUM('pendiente', 'enviada', 'leida', 'descartada') DEFAULT 'pendiente',
    canal ENUM('email', 'web', 'push') DEFAULT 'email',
    
    -- Fechas
    fecha_creada TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_enviada TIMESTAMP NULL,
    fecha_leida TIMESTAMP NULL,
    
    -- Índices
    INDEX idx_item_tipo (wishlist_item_id, tipo),
    INDEX idx_estado_canal (estado, canal),
    INDEX idx_fecha_creada (fecha_creada DESC),
    
    FOREIGN KEY (wishlist_item_id) REFERENCES wishlist_items(id) ON DELETE CASCADE
);

-- Tabla de configuración de alertas de precio
CREATE TABLE IF NOT EXISTS price_alerts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    wishlist_item_id INT NOT NULL,
    
    -- Configuración de alerta
    precio_objetivo DECIMAL(10,2) NOT NULL,
    tipo_alerta ENUM('precio_igual', 'precio_menor', 'descuento_porcentaje') NOT NULL,
    valor_descuento DECIMAL(5,2) NULL, -- Para alertas de descuento porcentual
    
    -- Estado
    activa BOOLEAN DEFAULT TRUE,
    veces_disparada INT DEFAULT 0,
    fecha_ultima_activacion TIMESTAMP NULL,
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL,
    
    INDEX idx_item_activa (wishlist_item_id, activa),
    INDEX idx_precio (precio_objetivo),
    
    FOREIGN KEY (wishlist_item_id) REFERENCES wishlist_items(id) ON DELETE CASCADE
);

-- Tabla de estadísticas de wishlist
CREATE TABLE IF NOT EXISTS wishlist_stats (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Identificación
    fecha DATE NOT NULL UNIQUE,
    
    -- Estadísticas generales
    total_wishlists INT DEFAULT 0,
    wishlists_activas INT DEFAULT 0,
    total_items INT DEFAULT 0,
    items_agregados_hoy INT DEFAULT 0,
    items_removidos_hoy INT DEFAULT 0,
    
    -- Notificaciones
    notificaciones_enviadas INT DEFAULT 0,
    notificaciones_clickeadas INT DEFAULT 0,
    tasa_conversion_notificaciones DECIMAL(5,2) DEFAULT 0.00,
    
    -- Compartir
    listas_compartidas INT DEFAULT 0,
    clicks_listas_compartidas INT DEFAULT 0,
    
    -- Conversiones
    reservas_desde_wishlist INT DEFAULT 0,
    ingresos_desde_wishlist DECIMAL(12,2) DEFAULT 0.00,
    
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Procedimiento para detectar cambios de precio
DELIMITER //
CREATE PROCEDURE DetectarCambiosPrecios()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE item_id INT;
    DECLARE producto_id INT;
    DECLARE precio_anterior DECIMAL(10,2);
    DECLARE precio_actual DECIMAL(10,2);
    DECLARE porcentaje_cambio DECIMAL(5,2);
    
    DECLARE precio_cursor CURSOR FOR
        SELECT wi.id, wi.producto_id, wi.ultimo_precio_visto, p.precio
        FROM wishlist_items wi
        JOIN productos p ON wi.producto_id = p.id
        WHERE wi.ultimo_precio_visto IS NOT NULL 
        AND ABS(wi.ultimo_precio_visto - p.precio) > 0.01; -- Cambio mínimo de $0.01
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN precio_cursor;
    
    read_loop: LOOP
        FETCH precio_cursor INTO item_id, producto_id, precio_anterior, precio_actual;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Calcular porcentaje de cambio
        SET porcentaje_cambio = ROUND(((precio_actual - precio_anterior) / precio_anterior) * 100, 2);
        
        -- Actualizar precio en wishlist item
        UPDATE wishlist_items 
        SET ultimo_precio_visto = precio_actual,
            fecha_ultimo_cambio_precio = NOW()
        WHERE id = item_id;
        
        -- Crear notificación de cambio de precio
        INSERT INTO wishlist_notifications (
            wishlist_item_id, tipo, titulo, mensaje,
            precio_anterior, precio_nuevo, porcentaje_cambio
        ) VALUES (
            item_id,
            CASE 
                WHEN precio_actual < precio_anterior THEN 'precio_bajo'
                ELSE 'precio_subio'
            END,
            CASE 
                WHEN precio_actual < precio_anterior THEN 
                    CONCAT('¡Precio reducido! ', ABS(porcentaje_cambio), '% menos')
                ELSE 
                    CONCAT('Precio aumentó ', porcentaje_cambio, '%')
            END,
            CASE 
                WHEN precio_actual < precio_anterior THEN 
                    CONCAT('El precio bajó de $', precio_anterior, ' a $', precio_actual, '. ¡No dejes pasar esta oportunidad!')
                ELSE 
                    CONCAT('El precio subió de $', precio_anterior, ' a $', precio_actual, '. Considera reservar pronto.')
            END
        );
    END LOOP;
    
    CLOSE precio_cursor;
END//

-- Procedimiento para verificar alertas de precio
CREATE PROCEDURE VerificarAlertasPrecios()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE alert_id INT;
    DECLARE item_id INT;
    DECLARE precio_objetivo DECIMAL(10,2);
    DECLARE precio_actual DECIMAL(10,2);
    DECLARE tipo_alerta VARCHAR(50);
    DECLARE valor_descuento DECIMAL(5,2);
    
    DECLARE alert_cursor CURSOR FOR
        SELECT pa.id, pa.wishlist_item_id, pa.precio_objetivo, pa.tipo_alerta, pa.valor_descuento, p.precio
        FROM price_alerts pa
        JOIN wishlist_items wi ON pa.wishlist_item_id = wi.id
        JOIN productos p ON wi.producto_id = p.id
        WHERE pa.activa = TRUE
        AND (pa.expires_at IS NULL OR pa.expires_at > NOW());
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN alert_cursor;
    
    alert_loop: LOOP
        FETCH alert_cursor INTO alert_id, item_id, precio_objetivo, tipo_alerta, valor_descuento, precio_actual;
        IF done THEN
            LEAVE alert_loop;
        END IF;
        
        -- Verificar si se cumple la condición de la alerta
        IF (tipo_alerta = 'precio_igual' AND ABS(precio_actual - precio_objetivo) < 0.01) OR
           (tipo_alerta = 'precio_menor' AND precio_actual <= precio_objetivo) OR
           (tipo_alerta = 'descuento_porcentaje' AND precio_actual <= (precio_objetivo * (1 - valor_descuento/100))) THEN
            
            -- Crear notificación de alerta
            INSERT INTO wishlist_notifications (
                wishlist_item_id, tipo, titulo, mensaje, precio_nuevo
            ) VALUES (
                item_id,
                'precio_bajo',
                '🎯 ¡Alerta de Precio Activada!',
                CONCAT('El precio alcanzó tu objetivo de $', precio_objetivo, '. Precio actual: $', precio_actual),
                precio_actual
            );
            
            -- Actualizar alerta
            UPDATE price_alerts 
            SET veces_disparada = veces_disparada + 1,
                fecha_ultima_activacion = NOW()
            WHERE id = alert_id;
        END IF;
    END LOOP;
    
    CLOSE alert_cursor;
END//
DELIMITER ;

-- Índices adicionales para optimización
CREATE INDEX idx_wishlist_items_precio_check ON wishlist_items(ultimo_precio_visto, fecha_ultimo_check_disponibilidad);
CREATE INDEX idx_wishlist_notifications_user_pending ON wishlist_notifications(wishlist_item_id, estado, fecha_creada);
CREATE INDEX idx_price_alerts_active ON price_alerts(activa, expires_at);

-- Triggers para actualizar estadísticas automáticamente
DELIMITER //
CREATE TRIGGER update_wishlist_stats_after_item_insert
AFTER INSERT ON wishlist_items
FOR EACH ROW
BEGIN
    INSERT INTO wishlist_stats (fecha, items_agregados_hoy)
    VALUES (CURDATE(), 1)
    ON DUPLICATE KEY UPDATE 
        items_agregados_hoy = items_agregados_hoy + 1,
        total_items = (SELECT COUNT(*) FROM wishlist_items),
        updated_at = CURRENT_TIMESTAMP;
END//

CREATE TRIGGER update_wishlist_stats_after_item_delete
AFTER DELETE ON wishlist_items
FOR EACH ROW
BEGIN
    INSERT INTO wishlist_stats (fecha, items_removidos_hoy)
    VALUES (CURDATE(), 1)
    ON DUPLICATE KEY UPDATE 
        items_removidos_hoy = items_removidos_hoy + 1,
        total_items = (SELECT COUNT(*) FROM wishlist_items),
        updated_at = CURRENT_TIMESTAMP;
END//
DELIMITER ;