-- =====================================================
-- SISTEMA DE ANALYTICS Y PERSONALIZACIÓN CON IA
-- Mayan World Travel MVP - Fase 3
-- =====================================================

USE travel_mvp;

-- Tabla de eventos de usuario (tracking)
CREATE TABLE user_events (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NULL,
    session_id VARCHAR(128) NOT NULL,
    event_type VARCHAR(50) NOT NULL, -- page_view, product_view, booking_start, booking_complete, etc.
    event_category VARCHAR(50) NOT NULL, -- navigation, engagement, conversion, etc.
    event_action VARCHAR(100) NOT NULL,
    event_label VARCHAR(200) NULL,
    event_value DECIMAL(10,2) NULL,
    
    -- Context data
    product_id INT NULL,
    category_id INT NULL,
    booking_id INT NULL,
    
    -- Technical data
    url TEXT NOT NULL,
    referrer TEXT NULL,
    user_agent TEXT NULL,
    ip_address VARCHAR(45) NULL,
    device_type ENUM('desktop', 'mobile', 'tablet') NULL,
    browser VARCHAR(50) NULL,
    os VARCHAR(50) NULL,
    
    -- Geographic data
    country_code VARCHAR(2) NULL,
    city VARCHAR(100) NULL,
    
    -- Timestamps
    event_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES usuarios(id) ON DELETE SET NULL,
    FOREIGN KEY (product_id) REFERENCES productos(id) ON DELETE SET NULL,
    FOREIGN KEY (category_id) REFERENCES categorias(id) ON DELETE SET NULL,
    FOREIGN KEY (booking_id) REFERENCES reservas(id) ON DELETE SET NULL,
    
    INDEX idx_user_events (user_id, event_timestamp),
    INDEX idx_session_events (session_id, event_timestamp),
    INDEX idx_event_type (event_type, event_timestamp),
    INDEX idx_product_events (product_id, event_timestamp),
    INDEX idx_event_timestamp (event_timestamp)
) PARTITION BY RANGE (YEAR(event_timestamp)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Tabla de perfiles de usuario (ML features)
CREATE TABLE user_profiles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE,
    session_id VARCHAR(128) UNIQUE NULL,
    
    -- Demographic data
    age_range VARCHAR(20) NULL, -- 18-25, 26-35, etc.
    gender ENUM('M', 'F', 'O') NULL,
    location_country VARCHAR(2) NULL,
    location_city VARCHAR(100) NULL,
    
    -- Behavioral data
    total_sessions INT DEFAULT 0,
    total_page_views INT DEFAULT 0,
    total_products_viewed INT DEFAULT 0,
    total_bookings INT DEFAULT 0,
    total_spent DECIMAL(12,2) DEFAULT 0,
    avg_session_duration INT DEFAULT 0, -- seconds
    
    -- Preferences (inferred)
    preferred_category_id INT NULL,
    preferred_price_range VARCHAR(20) NULL, -- 'budget', 'mid-range', 'luxury'
    preferred_duration VARCHAR(20) NULL, -- 'short', 'medium', 'long'
    preferred_difficulty VARCHAR(20) NULL, -- 'easy', 'moderate', 'hard'
    
    -- Engagement metrics
    engagement_score DECIMAL(5,2) DEFAULT 0, -- 0-100
    conversion_probability DECIMAL(3,2) DEFAULT 0, -- 0-1
    churn_risk DECIMAL(3,2) DEFAULT 0, -- 0-1
    
    -- ML model scores
    recommendation_vector JSON NULL, -- For similarity calculations
    
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (preferred_category_id) REFERENCES categorias(id) ON DELETE SET NULL,
    
    INDEX idx_engagement_score (engagement_score DESC),
    INDEX idx_conversion_prob (conversion_probability DESC),
    INDEX idx_churn_risk (churn_risk DESC),
    INDEX idx_last_activity (last_activity)
);

-- Tabla de productos con features para ML
CREATE TABLE product_features (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL UNIQUE,
    
    -- Popularity metrics
    total_views INT DEFAULT 0,
    total_bookings INT DEFAULT 0,
    total_revenue DECIMAL(12,2) DEFAULT 0,
    conversion_rate DECIMAL(5,4) DEFAULT 0, -- views to bookings
    
    -- Rating metrics
    avg_rating DECIMAL(3,2) DEFAULT 0,
    total_reviews INT DEFAULT 0,
    
    -- Seasonal patterns (JSON)
    seasonal_popularity JSON NULL, -- {month: popularity_score}
    
    -- Feature vector for ML (extracted features)
    feature_vector JSON NULL,
    
    -- Calculated categories
    popularity_tier ENUM('low', 'medium', 'high', 'viral') DEFAULT 'medium',
    revenue_tier ENUM('budget', 'mid-range', 'premium', 'luxury') DEFAULT 'mid-range',
    
    last_calculated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (product_id) REFERENCES productos(id) ON DELETE CASCADE,
    
    INDEX idx_popularity_tier (popularity_tier),
    INDEX idx_revenue_tier (revenue_tier),
    INDEX idx_conversion_rate (conversion_rate DESC),
    INDEX idx_avg_rating (avg_rating DESC)
);

-- Tabla de recomendaciones generadas
CREATE TABLE recommendations (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NULL,
    session_id VARCHAR(128) NULL,
    
    -- Recommendation context
    context_type VARCHAR(50) NOT NULL, -- 'homepage', 'product_detail', 'checkout', etc.
    context_product_id INT NULL, -- For related products
    
    -- Recommended products (JSON array of product IDs with scores)
    recommended_products JSON NOT NULL,
    algorithm_used VARCHAR(50) NOT NULL, -- 'collaborative', 'content_based', 'hybrid', etc.
    confidence_score DECIMAL(3,2) DEFAULT 0, -- 0-1
    
    -- Performance tracking
    products_shown INT DEFAULT 0,
    products_clicked INT DEFAULT 0,
    products_booked INT DEFAULT 0,
    click_through_rate DECIMAL(5,4) DEFAULT 0,
    conversion_rate DECIMAL(5,4) DEFAULT 0,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP DEFAULT (CURRENT_TIMESTAMP + INTERVAL 24 HOUR),
    
    FOREIGN KEY (user_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (context_product_id) REFERENCES productos(id) ON DELETE SET NULL,
    
    INDEX idx_user_recommendations (user_id, created_at DESC),
    INDEX idx_session_recommendations (session_id, created_at DESC),
    INDEX idx_context_type (context_type),
    INDEX idx_expires_at (expires_at)
);

-- Tabla de segmentos de usuarios
CREATE TABLE user_segments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    
    -- Segment criteria (JSON)
    criteria JSON NOT NULL,
    
    -- Segment stats
    total_users INT DEFAULT 0,
    avg_conversion_rate DECIMAL(5,4) DEFAULT 0,
    avg_order_value DECIMAL(10,2) DEFAULT 0,
    
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_active_segments (is_active, name)
);

-- Tabla de asignación de usuarios a segmentos
CREATE TABLE user_segment_assignments (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    segment_id INT NOT NULL,
    confidence_score DECIMAL(3,2) DEFAULT 1.00,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (segment_id) REFERENCES user_segments(id) ON DELETE CASCADE,
    
    UNIQUE KEY unique_user_segment (user_id, segment_id),
    INDEX idx_segment_users (segment_id, assigned_at)
);

-- Tabla de experimentos A/B
CREATE TABLE ab_experiments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    
    -- Experiment configuration
    start_date DATE NOT NULL,
    end_date DATE NULL,
    traffic_allocation DECIMAL(3,2) DEFAULT 0.50, -- 0-1, percentage of traffic
    
    -- Variants (JSON)
    variants JSON NOT NULL, -- [{"name": "control", "allocation": 0.5}, {"name": "variant_a", "allocation": 0.5}]
    
    -- Success metrics
    primary_metric VARCHAR(50) NOT NULL, -- 'conversion_rate', 'revenue', 'engagement'
    secondary_metrics JSON NULL,
    
    -- Results
    total_participants INT DEFAULT 0,
    confidence_level DECIMAL(5,2) DEFAULT 0,
    winner_variant VARCHAR(50) NULL,
    
    status ENUM('draft', 'running', 'completed', 'stopped') DEFAULT 'draft',
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (created_by) REFERENCES usuarios(id),
    
    INDEX idx_status_dates (status, start_date, end_date),
    INDEX idx_active_experiments (status, start_date, end_date)
);

-- Tabla de participantes en experimentos A/B
CREATE TABLE ab_experiment_participants (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    experiment_id INT NOT NULL,
    user_id INT NULL,
    session_id VARCHAR(128) NOT NULL,
    
    variant_assigned VARCHAR(50) NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Conversion tracking
    converted BOOLEAN DEFAULT FALSE,
    conversion_value DECIMAL(10,2) DEFAULT 0,
    converted_at TIMESTAMP NULL,
    
    FOREIGN KEY (experiment_id) REFERENCES ab_experiments(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES usuarios(id) ON DELETE SET NULL,
    
    UNIQUE KEY unique_experiment_session (experiment_id, session_id),
    INDEX idx_experiment_variant (experiment_id, variant_assigned),
    INDEX idx_conversion_tracking (experiment_id, converted, converted_at)
);

-- Tabla de reportes de analytics automatizados
CREATE TABLE analytics_reports (
    id INT PRIMARY KEY AUTO_INCREMENT,
    report_type VARCHAR(50) NOT NULL, -- 'daily', 'weekly', 'monthly', 'custom'
    report_name VARCHAR(100) NOT NULL,
    
    -- Report configuration
    metrics JSON NOT NULL, -- List of metrics to include
    dimensions JSON NOT NULL, -- Grouping dimensions
    date_range_type VARCHAR(20) NOT NULL, -- 'last_7_days', 'last_30_days', 'custom'
    custom_date_range JSON NULL, -- {start: date, end: date}
    
    -- Report data (cached)
    report_data JSON NULL,
    last_generated TIMESTAMP NULL,
    
    -- Delivery settings
    email_recipients JSON NULL,
    auto_send BOOLEAN DEFAULT FALSE,
    send_frequency ENUM('daily', 'weekly', 'monthly') NULL,
    
    is_active BOOLEAN DEFAULT TRUE,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (created_by) REFERENCES usuarios(id),
    
    INDEX idx_active_reports (is_active, auto_send),
    INDEX idx_report_type (report_type, last_generated)
);

-- =====================================================
-- STORED PROCEDURES PARA ANALYTICS
-- =====================================================

DELIMITER //

-- Procedure para calcular métricas de usuario
CREATE PROCEDURE CalculateUserMetrics(IN user_id_param INT)
BEGIN
    DECLARE total_sessions INT DEFAULT 0;
    DECLARE total_page_views INT DEFAULT 0;
    DECLARE total_products_viewed INT DEFAULT 0;
    DECLARE total_bookings INT DEFAULT 0;
    DECLARE total_spent DECIMAL(12,2) DEFAULT 0;
    DECLARE avg_session_duration INT DEFAULT 0;
    DECLARE engagement_score DECIMAL(5,2) DEFAULT 0;
    
    -- Calculate metrics
    SELECT 
        COUNT(DISTINCT session_id),
        COUNT(CASE WHEN event_type = 'page_view' THEN 1 END),
        COUNT(CASE WHEN event_type = 'product_view' THEN 1 END)
    INTO total_sessions, total_page_views, total_products_viewed
    FROM user_events 
    WHERE user_id = user_id_param;
    
    SELECT COUNT(*), COALESCE(SUM(precio_final), 0)
    INTO total_bookings, total_spent
    FROM reservas 
    WHERE cliente_email = (SELECT email FROM usuarios WHERE id = user_id_param);
    
    -- Calculate engagement score (simplified algorithm)
    SET engagement_score = LEAST(100, 
        (total_page_views * 0.1) + 
        (total_products_viewed * 0.5) + 
        (total_bookings * 10)
    );
    
    -- Update user profile
    INSERT INTO user_profiles (user_id, total_sessions, total_page_views, total_products_viewed, 
                             total_bookings, total_spent, engagement_score)
    VALUES (user_id_param, total_sessions, total_page_views, total_products_viewed, 
            total_bookings, total_spent, engagement_score)
    ON DUPLICATE KEY UPDATE
        total_sessions = VALUES(total_sessions),
        total_page_views = VALUES(total_page_views),
        total_products_viewed = VALUES(total_products_viewed),
        total_bookings = VALUES(total_bookings),
        total_spent = VALUES(total_spent),
        engagement_score = VALUES(engagement_score),
        updated_at = CURRENT_TIMESTAMP;
END//

-- Procedure para calcular métricas de producto
CREATE PROCEDURE CalculateProductMetrics(IN product_id_param INT)
BEGIN
    DECLARE total_views INT DEFAULT 0;
    DECLARE total_bookings INT DEFAULT 0;
    DECLARE total_revenue DECIMAL(12,2) DEFAULT 0;
    DECLARE conversion_rate DECIMAL(5,4) DEFAULT 0;
    DECLARE avg_rating DECIMAL(3,2) DEFAULT 0;
    DECLARE total_reviews INT DEFAULT 0;
    
    -- Calculate view metrics
    SELECT COUNT(*)
    INTO total_views
    FROM user_events 
    WHERE event_type = 'product_view' AND product_id = product_id_param;
    
    -- Calculate booking metrics
    SELECT COUNT(*), COALESCE(SUM(precio_final), 0)
    INTO total_bookings, total_revenue
    FROM reservas 
    WHERE producto_id = product_id_param AND estado IN ('confirmada', 'pagada');
    
    -- Calculate conversion rate
    IF total_views > 0 THEN
        SET conversion_rate = total_bookings / total_views;
    END IF;
    
    -- Calculate rating metrics (if reviews table exists)
    -- SELECT AVG(rating), COUNT(*)
    -- INTO avg_rating, total_reviews
    -- FROM reviews WHERE product_id = product_id_param AND status = 'approved';
    
    -- Update product features
    INSERT INTO product_features (product_id, total_views, total_bookings, total_revenue, 
                                conversion_rate, avg_rating, total_reviews)
    VALUES (product_id_param, total_views, total_bookings, total_revenue, 
            conversion_rate, avg_rating, total_reviews)
    ON DUPLICATE KEY UPDATE
        total_views = VALUES(total_views),
        total_bookings = VALUES(total_bookings),
        total_revenue = VALUES(total_revenue),
        conversion_rate = VALUES(conversion_rate),
        avg_rating = VALUES(avg_rating),
        total_reviews = VALUES(total_reviews),
        last_calculated = CURRENT_TIMESTAMP,
        updated_at = CURRENT_TIMESTAMP;
END//

DELIMITER ;

-- =====================================================
-- TRIGGERS PARA ANALYTICS AUTOMÁTICOS
-- =====================================================

DELIMITER //

-- Trigger para actualizar métricas cuando se crea una nueva reserva
CREATE TRIGGER update_analytics_on_booking
AFTER INSERT ON reservas
FOR EACH ROW
BEGIN
    -- Update product metrics
    CALL CalculateProductMetrics(NEW.producto_id);
    
    -- Update user metrics if user exists
    IF NEW.cliente_email IS NOT NULL THEN
        SET @user_id = (SELECT id FROM usuarios WHERE email = NEW.cliente_email LIMIT 1);
        IF @user_id IS NOT NULL THEN
            CALL CalculateUserMetrics(@user_id);
        END IF;
    END IF;
END//

DELIMITER ;

-- =====================================================
-- VISTAS PARA REPORTING
-- =====================================================

-- Vista de métricas diarias
CREATE VIEW daily_metrics AS
SELECT 
    DATE(event_timestamp) as metric_date,
    COUNT(DISTINCT session_id) as unique_sessions,
    COUNT(CASE WHEN event_type = 'page_view' THEN 1 END) as page_views,
    COUNT(CASE WHEN event_type = 'product_view' THEN 1 END) as product_views,
    COUNT(DISTINCT CASE WHEN event_type = 'product_view' THEN session_id END) as sessions_with_product_view,
    COUNT(DISTINCT user_id) as unique_users
FROM user_events
WHERE event_timestamp >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
GROUP BY DATE(event_timestamp)
ORDER BY metric_date DESC;

-- Vista de productos más populares
CREATE VIEW popular_products AS
SELECT 
    p.id,
    p.nombre,
    pf.total_views,
    pf.total_bookings,
    pf.conversion_rate,
    pf.total_revenue,
    pf.popularity_tier
FROM productos p
JOIN product_features pf ON p.id = pf.product_id
WHERE p.activo = TRUE
ORDER BY pf.total_views DESC;

-- Vista de segmentación de usuarios
CREATE VIEW user_segments_overview AS
SELECT 
    us.name as segment_name,
    us.total_users,
    us.avg_conversion_rate,
    us.avg_order_value,
    COUNT(usa.user_id) as current_users
FROM user_segments us
LEFT JOIN user_segment_assignments usa ON us.id = usa.segment_id
WHERE us.is_active = TRUE
GROUP BY us.id, us.name, us.total_users, us.avg_conversion_rate, us.avg_order_value
ORDER BY us.avg_conversion_rate DESC;

-- =====================================================
-- DATOS INICIALES
-- =====================================================

-- Segmentos predefinidos
INSERT INTO user_segments (name, description, criteria) VALUES
('High Value Customers', 'Customers with multiple bookings and high spend', 
 '{"total_bookings": {"min": 3}, "total_spent": {"min": 1000}}'),
 
('Frequent Browsers', 'Users who browse frequently but haven\'t booked', 
 '{"total_page_views": {"min": 20}, "total_bookings": {"max": 0}}'),
 
('Recent Customers', 'Customers who booked in the last 30 days', 
 '{"days_since_last_booking": {"max": 30}}'),
 
('Adventure Seekers', 'Users interested in high-difficulty tours', 
 '{"preferred_difficulty": "dificil"}'),
 
('Budget Travelers', 'Users who prefer budget-friendly options', 
 '{"preferred_price_range": "budget"}');

-- Experimento A/B de ejemplo
INSERT INTO ab_experiments (name, description, start_date, end_date, variants, primary_metric, created_by) VALUES
('Homepage Hero Banner Test', 'Testing different hero banner designs for conversion', 
 CURRENT_DATE, DATE_ADD(CURRENT_DATE, INTERVAL 30 DAY),
 '[{"name": "control", "allocation": 0.5}, {"name": "new_design", "allocation": 0.5}]',
 'conversion_rate', 1);