-- =====================================================
-- SISTEMA DE DYNAMIC PRICING CON IA - FASE 4
-- Optimización Automática de Precios con Machine Learning
-- =====================================================

USE travel_mvp;

-- Tabla de configuración de pricing dinámico
CREATE TABLE pricing_strategies (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tenant_id INT NULL, -- NULL para estrategias globales
    product_id INT NULL, -- NULL para estrategias por tenant
    
    -- Configuración de la estrategia
    strategy_name VARCHAR(100) NOT NULL,
    strategy_type ENUM('seasonal', 'demand_based', 'competitor', 'yield_management', 'ml_optimized') NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    
    -- Parámetros de la estrategia (JSON)
    strategy_params JSON NOT NULL,
    
    -- Límites de precios
    min_price_multiplier DECIMAL(5,4) DEFAULT 0.5000, -- Mínimo 50% del precio base
    max_price_multiplier DECIMAL(5,4) DEFAULT 3.0000, -- Máximo 300% del precio base
    
    -- Scheduling
    applies_from DATE,
    applies_to DATE,
    time_restrictions JSON, -- Horarios específicos, días de semana, etc.
    
    -- Performance tracking
    total_applications INT DEFAULT 0,
    total_revenue_impact DECIMAL(12,2) DEFAULT 0,
    avg_conversion_improvement DECIMAL(5,4) DEFAULT 0,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES productos(id) ON DELETE CASCADE,
    
    INDEX idx_tenant_strategy (tenant_id, is_active),
    INDEX idx_product_strategy (product_id, is_active),
    INDEX idx_strategy_type (strategy_type, is_active)
);

-- Tabla de variables de pricing (datos para ML)
CREATE TABLE pricing_variables (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    
    -- Context
    product_id INT NOT NULL,
    calculation_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    date_for_pricing DATE NOT NULL, -- Fecha para la cual se calcula el precio
    
    -- Demand variables
    current_bookings_today INT DEFAULT 0,
    bookings_same_day_last_week INT DEFAULT 0,
    bookings_same_day_last_month INT DEFAULT 0,
    bookings_same_day_last_year INT DEFAULT 0,
    
    -- Capacity variables
    total_capacity INT DEFAULT 0,
    available_capacity INT DEFAULT 0,
    capacity_utilization DECIMAL(5,4) DEFAULT 0, -- 0-1
    days_until_departure INT DEFAULT 0,
    
    -- Seasonal variables
    is_high_season BOOLEAN DEFAULT FALSE,
    is_weekend BOOLEAN DEFAULT FALSE,
    is_holiday BOOLEAN DEFAULT FALSE,
    weather_forecast_score DECIMAL(3,2) DEFAULT 0, -- 0-10 scale
    
    -- Market variables
    competitor_avg_price DECIMAL(10,2) DEFAULT 0,
    our_price_vs_market DECIMAL(5,4) DEFAULT 1, -- Our price / market average
    market_demand_index DECIMAL(5,4) DEFAULT 1, -- Google Trends, etc.
    
    -- Historical performance
    historical_conversion_rate DECIMAL(5,4) DEFAULT 0,
    avg_rating DECIMAL(3,2) DEFAULT 0,
    reviews_count INT DEFAULT 0,
    
    -- Economic indicators
    usd_to_local_rate DECIMAL(10,6) DEFAULT 1,
    tourism_index DECIMAL(5,4) DEFAULT 1, -- Country-specific tourism index
    gas_price_index DECIMAL(5,4) DEFAULT 1, -- For transportation costs
    
    -- Event-based variables
    special_events JSON, -- Local events, festivals, etc.
    travel_alerts JSON, -- Safety, weather alerts, etc.
    
    FOREIGN KEY (product_id) REFERENCES productos(id) ON DELETE CASCADE,
    
    INDEX idx_product_date (product_id, date_for_pricing),
    INDEX idx_timestamp (calculation_timestamp)
) PARTITION BY RANGE (YEAR(calculation_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 precios calculados dinámicamente
CREATE TABLE dynamic_prices (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    
    -- Context
    product_id INT NOT NULL,
    tenant_id INT NULL,
    calculation_date DATE NOT NULL,
    valid_from TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    valid_to TIMESTAMP DEFAULT (CURRENT_TIMESTAMP + INTERVAL 24 HOUR),
    
    -- Pricing
    base_price DECIMAL(10,2) NOT NULL,
    calculated_price DECIMAL(10,2) NOT NULL,
    price_multiplier DECIMAL(5,4) NOT NULL,
    
    -- Algorithm details
    algorithm_used VARCHAR(50) NOT NULL, -- 'seasonal', 'ml_optimized', 'demand_based'
    confidence_score DECIMAL(3,2) DEFAULT 0, -- 0-1
    
    -- Input variables (JSON snapshot)
    input_variables JSON,
    
    -- Breakdown of price adjustments
    adjustments JSON, -- [{'type': 'seasonal', 'amount': 10.50, 'reason': 'High season'}]
    
    -- Performance tracking
    views_count INT DEFAULT 0,
    bookings_count INT DEFAULT 0,
    revenue_generated DECIMAL(12,2) DEFAULT 0,
    conversion_rate DECIMAL(5,4) DEFAULT 0,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (product_id) REFERENCES productos(id) ON DELETE CASCADE,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE SET NULL,
    
    UNIQUE KEY unique_product_date (product_id, tenant_id, calculation_date),
    INDEX idx_product_valid (product_id, valid_from, valid_to),
    INDEX idx_algorithm (algorithm_used, created_at)
) PARTITION BY RANGE (YEAR(calculation_date)) (
    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 experimentos de pricing A/B
CREATE TABLE pricing_experiments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Experiment details
    experiment_name VARCHAR(200) NOT NULL,
    product_id INT NULL, -- NULL para experimentos globales
    tenant_id INT NULL,
    
    -- Experiment configuration
    control_strategy VARCHAR(50) NOT NULL, -- 'static', 'seasonal'
    test_strategy VARCHAR(50) NOT NULL, -- 'ml_optimized', 'demand_based'
    traffic_split DECIMAL(3,2) DEFAULT 0.50, -- 50% split
    
    -- Date range
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    
    -- Success metrics
    primary_metric ENUM('revenue', 'bookings', 'conversion_rate') DEFAULT 'revenue',
    
    -- Results
    control_group_size INT DEFAULT 0,
    test_group_size INT DEFAULT 0,
    control_group_revenue DECIMAL(12,2) DEFAULT 0,
    test_group_revenue DECIMAL(12,2) DEFAULT 0,
    control_group_bookings INT DEFAULT 0,
    test_group_bookings INT DEFAULT 0,
    
    -- Statistical significance
    p_value DECIMAL(10,8) DEFAULT 1,
    confidence_level DECIMAL(5,2) DEFAULT 0,
    is_significant BOOLEAN DEFAULT FALSE,
    winner ENUM('control', 'test', 'inconclusive') 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 (product_id) REFERENCES productos(id) ON DELETE CASCADE,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES usuarios(id),
    
    INDEX idx_experiment_dates (start_date, end_date, status),
    INDEX idx_tenant_experiments (tenant_id, status)
);

-- Tabla de participantes en experimentos de pricing
CREATE TABLE pricing_experiment_participants (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    experiment_id INT NOT NULL,
    session_id VARCHAR(128) NOT NULL,
    user_id INT NULL,
    
    -- Assignment
    group_assigned ENUM('control', 'test') NOT NULL,
    price_shown DECIMAL(10,2) NOT NULL,
    product_id INT NOT NULL,
    
    -- Outcome
    viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    booked BOOLEAN DEFAULT FALSE,
    booked_at TIMESTAMP NULL,
    booking_amount DECIMAL(10,2) DEFAULT 0,
    
    FOREIGN KEY (experiment_id) REFERENCES pricing_experiments(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES usuarios(id) ON DELETE SET NULL,
    FOREIGN KEY (product_id) REFERENCES productos(id) ON DELETE CASCADE,
    
    UNIQUE KEY unique_experiment_session (experiment_id, session_id),
    INDEX idx_experiment_group (experiment_id, group_assigned),
    INDEX idx_booking_tracking (experiment_id, booked, booked_at)
);

-- Tabla de modelos de ML entrenados
CREATE TABLE ml_pricing_models (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Model identification
    model_name VARCHAR(100) NOT NULL,
    model_version VARCHAR(20) NOT NULL,
    algorithm_type VARCHAR(50) NOT NULL, -- 'random_forest', 'neural_network', 'linear_regression'
    
    -- Training data
    training_data_from DATE NOT NULL,
    training_data_to DATE NOT NULL,
    training_samples_count INT NOT NULL,
    
    -- Model performance
    training_accuracy DECIMAL(5,4) DEFAULT 0,
    validation_accuracy DECIMAL(5,4) DEFAULT 0,
    test_accuracy DECIMAL(5,4) DEFAULT 0,
    
    -- Feature importance (JSON)
    feature_importance JSON,
    
    -- Model parameters (JSON)
    model_parameters JSON,
    
    -- Deployment
    is_active BOOLEAN DEFAULT FALSE,
    deployed_at TIMESTAMP NULL,
    performance_score DECIMAL(5,4) DEFAULT 0, -- Real-world performance
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_model_version (model_name, model_version),
    INDEX idx_active_models (is_active, performance_score DESC)
);

-- Tabla de logs de pricing (para auditoría)
CREATE TABLE pricing_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    
    -- Context
    product_id INT NOT NULL,
    tenant_id INT NULL,
    user_session_id VARCHAR(128),
    
    -- Price calculation
    original_price DECIMAL(10,2) NOT NULL,
    calculated_price DECIMAL(10,2) NOT NULL,
    algorithm_used VARCHAR(50) NOT NULL,
    
    -- Input data snapshot
    input_snapshot JSON,
    calculation_details JSON,
    
    -- Result
    price_accepted BOOLEAN NULL, -- NULL if not decided yet
    booking_created BOOLEAN DEFAULT FALSE,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (product_id) REFERENCES productos(id) ON DELETE CASCADE,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE SET NULL,
    
    INDEX idx_product_pricing (product_id, created_at DESC),
    INDEX idx_algorithm_performance (algorithm_used, price_accepted, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
    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
);

-- =====================================================
-- STORED PROCEDURES PARA DYNAMIC PRICING
-- =====================================================

DELIMITER //

-- Procedure para calcular precio dinámico
CREATE PROCEDURE CalculateDynamicPrice(
    IN p_product_id INT,
    IN p_tenant_id INT,
    IN p_target_date DATE
)
BEGIN
    DECLARE v_base_price DECIMAL(10,2);
    DECLARE v_calculated_price DECIMAL(10,2);
    DECLARE v_multiplier DECIMAL(5,4) DEFAULT 1.0000;
    DECLARE v_algorithm VARCHAR(50) DEFAULT 'static';
    
    -- Obtener precio base
    SELECT precio INTO v_base_price 
    FROM productos 
    WHERE id = p_product_id;
    
    -- Calcular variables de pricing
    CALL CollectPricingVariables(p_product_id, p_target_date);
    
    -- Aplicar algoritmo de pricing dinámico
    CALL ApplyPricingStrategy(p_product_id, p_tenant_id, p_target_date, v_multiplier, v_algorithm);
    
    -- Calcular precio final
    SET v_calculated_price = v_base_price * v_multiplier;
    
    -- Insertar/actualizar precio dinámico
    INSERT INTO dynamic_prices (
        product_id, tenant_id, calculation_date, base_price, 
        calculated_price, price_multiplier, algorithm_used
    ) VALUES (
        p_product_id, p_tenant_id, p_target_date, v_base_price,
        v_calculated_price, v_multiplier, v_algorithm
    ) ON DUPLICATE KEY UPDATE
        calculated_price = v_calculated_price,
        price_multiplier = v_multiplier,
        algorithm_used = v_algorithm,
        valid_from = CURRENT_TIMESTAMP,
        valid_to = CURRENT_TIMESTAMP + INTERVAL 24 HOUR;
    
    -- Devolver resultado
    SELECT 
        v_base_price as base_price,
        v_calculated_price as calculated_price,
        v_multiplier as multiplier,
        v_algorithm as algorithm;
END//

-- Procedure para recopilar variables de pricing
CREATE PROCEDURE CollectPricingVariables(
    IN p_product_id INT,
    IN p_target_date DATE
)
BEGIN
    DECLARE v_current_bookings INT DEFAULT 0;
    DECLARE v_total_capacity INT DEFAULT 0;
    DECLARE v_available_capacity INT DEFAULT 0;
    DECLARE v_days_until INT DEFAULT 0;
    DECLARE v_is_weekend BOOLEAN DEFAULT FALSE;
    DECLARE v_is_holiday BOOLEAN DEFAULT FALSE;
    
    -- Calcular días hasta el tour
    SET v_days_until = DATEDIFF(p_target_date, CURDATE());
    
    -- Verificar si es fin de semana
    SET v_is_weekend = (DAYOFWEEK(p_target_date) IN (1, 7));
    
    -- Obtener capacidad del producto
    SELECT capacidad_maxima INTO v_total_capacity
    FROM productos WHERE id = p_product_id;
    
    -- Calcular reservas actuales para esa fecha
    SELECT COUNT(*) INTO v_current_bookings
    FROM reservas 
    WHERE producto_id = p_product_id 
    AND fecha_salida = p_target_date
    AND estado IN ('confirmada', 'pagada');
    
    SET v_available_capacity = v_total_capacity - v_current_bookings;
    
    -- Insertar variables calculadas
    INSERT INTO pricing_variables (
        product_id, date_for_pricing, current_bookings_today,
        total_capacity, available_capacity, days_until_departure,
        is_weekend, is_holiday
    ) VALUES (
        p_product_id, p_target_date, v_current_bookings,
        v_total_capacity, v_available_capacity, v_days_until,
        v_is_weekend, v_is_holiday
    ) ON DUPLICATE KEY UPDATE
        current_bookings_today = v_current_bookings,
        available_capacity = v_available_capacity,
        calculation_timestamp = CURRENT_TIMESTAMP;
END//

-- Procedure para aplicar estrategia de pricing
CREATE PROCEDURE ApplyPricingStrategy(
    IN p_product_id INT,
    IN p_tenant_id INT,
    IN p_target_date DATE,
    OUT p_multiplier DECIMAL(5,4),
    OUT p_algorithm VARCHAR(50)
)
BEGIN
    DECLARE v_capacity_util DECIMAL(5,4);
    DECLARE v_days_until INT;
    DECLARE v_is_weekend BOOLEAN;
    DECLARE v_base_multiplier DECIMAL(5,4) DEFAULT 1.0000;
    
    SET p_algorithm = 'dynamic_basic';
    
    -- Obtener variables calculadas
    SELECT 
        (current_bookings_today / NULLIF(total_capacity, 0)),
        days_until_departure,
        is_weekend
    INTO v_capacity_util, v_days_until, v_is_weekend
    FROM pricing_variables
    WHERE product_id = p_product_id AND date_for_pricing = p_target_date
    ORDER BY calculation_timestamp DESC
    LIMIT 1;
    
    -- Algoritmo básico de pricing dinámico
    SET p_multiplier = v_base_multiplier;
    
    -- Ajuste por capacidad utilizada
    IF v_capacity_util > 0.8 THEN
        SET p_multiplier = p_multiplier * 1.3; -- +30% si está 80%+ ocupado
    ELSEIF v_capacity_util > 0.6 THEN
        SET p_multiplier = p_multiplier * 1.15; -- +15% si está 60%+ ocupado
    ELSEIF v_capacity_util < 0.2 AND v_days_until <= 7 THEN
        SET p_multiplier = p_multiplier * 0.85; -- -15% descuento last minute
    END IF;
    
    -- Ajuste por proximidad de fecha
    IF v_days_until <= 2 THEN
        SET p_multiplier = p_multiplier * 1.2; -- +20% last minute premium
    ELSEIF v_days_until >= 30 THEN
        SET p_multiplier = p_multiplier * 0.9; -- -10% early bird
    END IF;
    
    -- Ajuste por fin de semana
    IF v_is_weekend THEN
        SET p_multiplier = p_multiplier * 1.1; -- +10% weekend premium
    END IF;
    
    -- Limitar multiplicadores extremos
    SET p_multiplier = GREATEST(0.5, LEAST(3.0, p_multiplier));
END//

DELIMITER ;

-- =====================================================
-- TRIGGERS PARA DYNAMIC PRICING
-- =====================================================

DELIMITER //

-- Trigger para recalcular precios cuando cambia la demanda
CREATE TRIGGER recalculate_prices_on_booking
AFTER INSERT ON reservas
FOR EACH ROW
BEGIN
    -- Recalcular precio para el producto y fecha de la nueva reserva
    CALL CalculateDynamicPrice(NEW.producto_id, NULL, NEW.fecha_salida);
END//

DELIMITER ;

-- =====================================================
-- VISTAS PARA PRICING ANALYTICS
-- =====================================================

-- Vista de performance de pricing por producto
CREATE VIEW pricing_performance AS
SELECT 
    p.id as product_id,
    p.nombre as product_name,
    COUNT(dp.id) as price_calculations,
    AVG(dp.price_multiplier) as avg_multiplier,
    SUM(dp.views_count) as total_views,
    SUM(dp.bookings_count) as total_bookings,
    AVG(dp.conversion_rate) as avg_conversion_rate,
    SUM(dp.revenue_generated) as total_revenue
FROM productos p
LEFT JOIN dynamic_prices dp ON p.id = dp.product_id
WHERE dp.created_at >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY p.id, p.nombre
ORDER BY total_revenue DESC;

-- Vista de experimentos activos
CREATE VIEW active_pricing_experiments AS
SELECT 
    pe.*,
    p.nombre as product_name,
    DATEDIFF(pe.end_date, CURDATE()) as days_remaining,
    (pe.control_group_size + pe.test_group_size) as total_participants
FROM pricing_experiments pe
LEFT JOIN productos p ON pe.product_id = p.id
WHERE pe.status = 'running'
AND pe.end_date >= CURDATE();

-- =====================================================
-- CONFIGURACIONES INICIALES
-- =====================================================

-- Estrategias de pricing por defecto
INSERT INTO pricing_strategies (strategy_name, strategy_type, strategy_params) VALUES
(
    'Basic Dynamic Pricing',
    'demand_based',
    '{
        "capacity_thresholds": [0.2, 0.6, 0.8],
        "capacity_multipliers": [0.85, 1.0, 1.15, 1.3],
        "time_multipliers": {"last_minute": 1.2, "early_bird": 0.9},
        "weekend_premium": 1.1
    }'
),
(
    'Seasonal Pricing',
    'seasonal',
    '{
        "high_season_months": [12, 1, 2, 7, 8],
        "high_season_multiplier": 1.25,
        "low_season_multiplier": 0.9,
        "holiday_multiplier": 1.4
    }'
),
(
    'Yield Management',
    'yield_management',
    '{
        "advance_booking_curve": {
            "60_days": 0.85,
            "30_days": 0.95,
            "14_days": 1.05,
            "7_days": 1.15,
            "2_days": 1.25
        }
    }'
);

-- Modelo ML básico (placeholder)
INSERT INTO ml_pricing_models (
    model_name, model_version, algorithm_type,
    training_data_from, training_data_to, training_samples_count,
    feature_importance, is_active
) VALUES (
    'Basic Random Forest',
    '1.0.0',
    'random_forest',
    DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR),
    CURRENT_DATE,
    1000,
    '{
        "capacity_utilization": 0.25,
        "days_until_departure": 0.20,
        "seasonal_factor": 0.15,
        "historical_conversion": 0.15,
        "competitor_price_ratio": 0.10,
        "weather_score": 0.08,
        "market_demand": 0.07
    }',
    TRUE
);