-- Sistema de Reviews Verificadas Post-Tour
-- Este sistema garantiza que solo clientes que completaron el tour puedan dejar reviews

-- Tabla principal de reviews
CREATE TABLE IF NOT EXISTS reviews (
    id INT PRIMARY KEY AUTO_INCREMENT,
    reserva_id INT NOT NULL,
    producto_id INT NOT NULL,
    usuario_email VARCHAR(255) NOT NULL,
    usuario_nombre VARCHAR(255) NOT NULL,
    
    -- Calificaciones específicas (1-5 estrellas)
    calificacion_general TINYINT NOT NULL CHECK (calificacion_general BETWEEN 1 AND 5),
    calificacion_guia TINYINT DEFAULT NULL CHECK (calificacion_guia BETWEEN 1 AND 5),
    calificacion_transporte TINYINT DEFAULT NULL CHECK (calificacion_transporte BETWEEN 1 AND 5),
    calificacion_organizacion TINYINT DEFAULT NULL CHECK (calificacion_organizacion BETWEEN 1 AND 5),
    calificacion_valor TINYINT DEFAULT NULL CHECK (calificacion_valor BETWEEN 1 AND 5),
    
    -- Contenido del review
    titulo VARCHAR(200) NOT NULL,
    comentario TEXT NOT NULL,
    
    -- Metadata del review
    fecha_tour DATE NOT NULL,
    verificado BOOLEAN DEFAULT FALSE,
    verificado_fecha TIMESTAMP NULL,
    moderado BOOLEAN DEFAULT FALSE,
    aprobado BOOLEAN DEFAULT FALSE,
    motivo_rechazo TEXT NULL,
    
    -- Información adicional
    experiencia_previa ENUM('primera_vez', 'ocasional', 'frecuente', 'experto') DEFAULT 'primera_vez',
    tipo_viajero ENUM('solo', 'pareja', 'familia', 'amigos', 'business') DEFAULT 'solo',
    grupo_edad ENUM('18-25', '26-35', '36-45', '46-55', '56-65', '65+') DEFAULT '26-35',
    
    -- Utilidad del review
    votos_utiles INT DEFAULT 0,
    votos_totales INT DEFAULT 0,
    
    -- Control
    estado ENUM('borrador', 'enviado', 'verificado', 'publicado', 'rechazado') DEFAULT 'enviado',
    ip_address VARCHAR(45),
    user_agent TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Índices y restricciones
    INDEX idx_producto_fecha (producto_id, created_at DESC),
    INDEX idx_estado_verificado (estado, verificado),
    INDEX idx_reserva (reserva_id),
    INDEX idx_calificacion (calificacion_general),
    INDEX idx_fecha_tour (fecha_tour DESC),
    
    -- Un usuario puede hacer solo un review por reserva
    UNIQUE KEY unique_review_per_booking (reserva_id, usuario_email),
    
    FOREIGN KEY (reserva_id) REFERENCES reservas(id) ON DELETE CASCADE,
    FOREIGN KEY (producto_id) REFERENCES productos(id) ON DELETE CASCADE
);

-- Tabla de fotos de reviews (opcional para reviews premium)
CREATE TABLE IF NOT EXISTS review_fotos (
    id INT PRIMARY KEY AUTO_INCREMENT,
    review_id INT NOT NULL,
    ruta_archivo VARCHAR(500) NOT NULL,
    nombre_original VARCHAR(255) NOT NULL,
    tamaño_archivo INT NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    orden TINYINT DEFAULT 0,
    aprobada BOOLEAN DEFAULT FALSE,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_review_orden (review_id, orden),
    FOREIGN KEY (review_id) REFERENCES reviews(id) ON DELETE CASCADE
);

-- Tabla de invitaciones a reviews
CREATE TABLE IF NOT EXISTS review_invitaciones (
    id INT PRIMARY KEY AUTO_INCREMENT,
    reserva_id INT NOT NULL,
    producto_id INT NOT NULL,
    usuario_email VARCHAR(255) NOT NULL,
    usuario_nombre VARCHAR(255) NOT NULL,
    
    -- Tokens de seguridad
    token_verificacion VARCHAR(255) NOT NULL UNIQUE,
    token_expira TIMESTAMP NOT NULL,
    
    -- Control de envíos
    invitaciones_enviadas TINYINT DEFAULT 0,
    fecha_primer_envio TIMESTAMP NULL,
    fecha_ultimo_envio TIMESTAMP NULL,
    
    -- Estado
    estado ENUM('pendiente', 'enviada', 'recordatorio_1', 'recordatorio_2', 'completada', 'expirada') DEFAULT 'pendiente',
    review_completado BOOLEAN DEFAULT FALSE,
    review_id INT NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_token (token_verificacion),
    INDEX idx_estado_fecha (estado, fecha_ultimo_envio),
    INDEX idx_reserva (reserva_id),
    INDEX idx_expira (token_expira),
    
    FOREIGN KEY (reserva_id) REFERENCES reservas(id) ON DELETE CASCADE,
    FOREIGN KEY (producto_id) REFERENCES productos(id) ON DELETE CASCADE,
    FOREIGN KEY (review_id) REFERENCES reviews(id) ON DELETE SET NULL
);

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

-- Configuraciones iniciales del sistema
INSERT INTO review_configuracion (clave, valor, descripcion, tipo) VALUES
('dias_espera_inicial', '3', 'Días después del tour para enviar primera invitación', 'int'),
('dias_recordatorio_1', '7', 'Días para enviar primer recordatorio', 'int'),
('dias_recordatorio_2', '14', 'Días para enviar segundo recordatorio', 'int'),
('dias_expiracion_token', '30', 'Días de validez del token de invitación', 'int'),
('moderacion_automatica', 'false', 'Aprobar reviews automáticamente si cumplen criterios', 'boolean'),
('minimo_caracteres', '50', 'Mínimo de caracteres requeridos en comentario', 'int'),
('maximo_caracteres', '2000', 'Máximo de caracteres permitidos en comentario', 'int'),
('permitir_fotos', 'true', 'Permitir subida de fotos en reviews', 'boolean'),
('max_fotos_review', '5', 'Máximo número de fotos por review', 'int'),
('calificacion_minima_auto', '4', 'Calificación mínima para aprobación automática', 'int');

-- Tabla de estadísticas de reviews por producto
CREATE TABLE IF NOT EXISTS review_estadisticas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    producto_id INT NOT NULL UNIQUE,
    
    -- Contadores de reviews
    total_reviews INT DEFAULT 0,
    reviews_verificadas INT DEFAULT 0,
    reviews_publicadas INT DEFAULT 0,
    
    -- Promedios de calificaciones
    promedio_general DECIMAL(3,2) DEFAULT 0.00,
    promedio_guia DECIMAL(3,2) DEFAULT 0.00,
    promedio_transporte DECIMAL(3,2) DEFAULT 0.00,
    promedio_organizacion DECIMAL(3,2) DEFAULT 0.00,
    promedio_valor DECIMAL(3,2) DEFAULT 0.00,
    
    -- Distribución de estrellas (para gráficos)
    estrellas_5 INT DEFAULT 0,
    estrellas_4 INT DEFAULT 0,
    estrellas_3 INT DEFAULT 0,
    estrellas_2 INT DEFAULT 0,
    estrellas_1 INT DEFAULT 0,
    
    -- Metadata
    fecha_ultimo_review TIMESTAMP NULL,
    necesita_recalculo BOOLEAN DEFAULT TRUE,
    
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (producto_id) REFERENCES productos(id) ON DELETE CASCADE
);

-- Función para recalcular estadísticas de un producto
DELIMITER //
CREATE TRIGGER update_review_stats_after_insert
AFTER INSERT ON reviews
FOR EACH ROW
BEGIN
    INSERT INTO review_estadisticas (producto_id, necesita_recalculo)
    VALUES (NEW.producto_id, TRUE)
    ON DUPLICATE KEY UPDATE necesita_recalculo = TRUE;
END//

CREATE TRIGGER update_review_stats_after_update
AFTER UPDATE ON reviews
FOR EACH ROW
BEGIN
    UPDATE review_estadisticas 
    SET necesita_recalculo = TRUE 
    WHERE producto_id = NEW.producto_id;
END//

CREATE TRIGGER update_review_stats_after_delete
AFTER DELETE ON reviews
FOR EACH ROW
BEGIN
    UPDATE review_estadisticas 
    SET necesita_recalculo = TRUE 
    WHERE producto_id = OLD.producto_id;
END//
DELIMITER ;

-- Vista para reviews públicos (solo aprobados y verificados)
CREATE OR REPLACE VIEW reviews_publicos AS
SELECT 
    r.*,
    p.nombre as producto_nombre,
    p.categoria_id,
    CASE 
        WHEN r.verificado = 1 THEN 'Compra verificada'
        ELSE 'Review estándar'
    END as badge_verificacion,
    DATEDIFF(CURRENT_DATE, r.fecha_tour) as dias_desde_tour,
    CASE 
        WHEN r.votos_totales > 0 THEN ROUND((r.votos_utiles / r.votos_totales) * 100, 1)
        ELSE NULL
    END as porcentaje_utilidad
FROM reviews r
JOIN productos p ON r.producto_id = p.id
WHERE r.estado = 'publicado' 
    AND r.aprobado = TRUE
ORDER BY r.verificado DESC, r.created_at DESC;

-- Índices adicionales para optimización
CREATE INDEX idx_reviews_producto_estado ON reviews(producto_id, estado, aprobado);
CREATE INDEX idx_reviews_fecha_calificacion ON reviews(created_at DESC, calificacion_general);
CREATE INDEX idx_invitaciones_estado_fecha ON review_invitaciones(estado, token_expira);