-- Sistema de Referidos y Programa de Recompensas Premium
-- Sistema gamificado con niveles, badges, challenges y recompensas múltiples

-- Tabla de configuración del programa de referidos
CREATE TABLE IF NOT EXISTS referral_config (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Configuración básica
    program_name VARCHAR(255) NOT NULL DEFAULT 'Programa Maya Explorers',
    program_description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    start_date DATE,
    end_date DATE NULL,
    
    -- Recompensas base
    referrer_reward_type ENUM('percentage', 'fixed_amount', 'points', 'credit') DEFAULT 'credit',
    referrer_reward_value DECIMAL(10,2) DEFAULT 25.00,
    referred_reward_type ENUM('percentage', 'fixed_amount', 'points', 'credit') DEFAULT 'credit',
    referred_reward_value DECIMAL(10,2) DEFAULT 15.00,
    
    -- Configuración de niveles
    enable_levels BOOLEAN DEFAULT TRUE,
    enable_badges BOOLEAN DEFAULT TRUE,
    enable_challenges BOOLEAN DEFAULT TRUE,
    enable_streaks BOOLEAN DEFAULT TRUE,
    
    -- Límites y restricciones
    max_referrals_per_user INT DEFAULT NULL, -- NULL = ilimitado
    max_rewards_per_month DECIMAL(10,2) DEFAULT NULL,
    min_booking_amount DECIMAL(10,2) DEFAULT 50.00, -- Mínimo para que el referido califique
    referral_validity_days INT DEFAULT 365, -- Validez del enlace de referido
    
    -- Configuración de pagos
    minimum_payout DECIMAL(10,2) DEFAULT 25.00,
    payout_frequency ENUM('immediate', 'weekly', 'monthly', 'quarterly') DEFAULT 'monthly',
    auto_payout BOOLEAN DEFAULT FALSE,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabla de usuarios del programa (extiende la tabla usuarios existente)
CREATE TABLE IF NOT EXISTS referral_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Referencia al usuario
    user_id INT NULL, -- Referencia a tabla usuarios
    user_email VARCHAR(255) NOT NULL,
    user_name VARCHAR(255) NOT NULL,
    user_phone VARCHAR(50) NULL,
    
    -- Código de referido único
    referral_code VARCHAR(20) UNIQUE NOT NULL,
    referral_link TEXT NOT NULL,
    qr_code_path VARCHAR(500) NULL,
    
    -- Estado del usuario en el programa
    status ENUM('active', 'suspended', 'banned', 'inactive') DEFAULT 'active',
    enrollment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Estadísticas generales
    total_referrals INT DEFAULT 0,
    successful_referrals INT DEFAULT 0, -- Referidos que completaron al menos una compra
    total_earnings DECIMAL(12,2) DEFAULT 0.00,
    available_balance DECIMAL(12,2) DEFAULT 0.00,
    lifetime_earnings DECIMAL(12,2) DEFAULT 0.00,
    
    -- Gamificación
    current_level INT DEFAULT 1,
    total_points INT DEFAULT 0,
    current_streak INT DEFAULT 0,
    best_streak INT DEFAULT 0,
    badges_count INT DEFAULT 0,
    
    -- Social sharing
    facebook_shares INT DEFAULT 0,
    whatsapp_shares INT DEFAULT 0,
    instagram_shares INT DEFAULT 0,
    email_invitations INT DEFAULT 0,
    
    -- Metadatos
    preferred_payout_method ENUM('bank_transfer', 'paypal', 'credit_account', 'gift_card') DEFAULT 'credit_account',
    payout_details JSON NULL, -- Información de pago
    marketing_consent BOOLEAN DEFAULT TRUE,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_referral_code (referral_code),
    INDEX idx_user_email (user_email),
    INDEX idx_status_level (status, current_level),
    
    FOREIGN KEY (user_id) REFERENCES usuarios(id) ON DELETE SET NULL
);

-- Tabla de referidos (usuarios que fueron referidos)
CREATE TABLE IF NOT EXISTS referrals (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Relación referidor-referido
    referrer_id INT NOT NULL, -- Usuario que refiere
    referred_user_id INT NULL, -- Usuario referido (si se registra)
    
    -- Información del referido
    referred_email VARCHAR(255) NOT NULL,
    referred_name VARCHAR(255) NULL,
    referred_phone VARCHAR(50) NULL,
    
    -- Origen del referido
    referral_source ENUM('direct_link', 'social_media', 'email', 'whatsapp', 'qr_code', 'word_of_mouth') NOT NULL,
    referral_medium VARCHAR(100) NULL, -- facebook, instagram, etc.
    referral_campaign VARCHAR(100) NULL, -- Campaña específica
    
    -- Estado del referido
    status ENUM('pending', 'registered', 'first_purchase', 'active_customer', 'churned') DEFAULT 'pending',
    registration_date TIMESTAMP NULL,
    first_purchase_date TIMESTAMP NULL,
    last_activity_date TIMESTAMP NULL,
    
    -- Información de conversión
    total_bookings INT DEFAULT 0,
    total_spent DECIMAL(12,2) DEFAULT 0.00,
    first_booking_amount DECIMAL(10,2) NULL,
    lifetime_value DECIMAL(12,2) DEFAULT 0.00,
    
    -- Recompensas generadas
    rewards_generated DECIMAL(10,2) DEFAULT 0.00,
    rewards_paid BOOLEAN DEFAULT FALSE,
    reward_payment_date TIMESTAMP NULL,
    
    -- Tracking y analytics
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    utm_source VARCHAR(100) NULL,
    utm_medium VARCHAR(100) NULL,
    utm_campaign VARCHAR(100) NULL,
    utm_content VARCHAR(100) NULL,
    
    -- Metadatos
    notes TEXT NULL,
    tags JSON NULL, -- ['vip', 'high_value', 'frequent_traveler']
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_referrer (referrer_id),
    INDEX idx_referred_email (referred_email),
    INDEX idx_status (status),
    INDEX idx_referral_source (referral_source, referral_medium),
    INDEX idx_conversion_date (first_purchase_date),
    
    FOREIGN KEY (referrer_id) REFERENCES referral_users(id) ON DELETE CASCADE,
    FOREIGN KEY (referred_user_id) REFERENCES usuarios(id) ON DELETE SET NULL
);

-- Tabla de niveles del programa
CREATE TABLE IF NOT EXISTS referral_levels (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Información del nivel
    level_number INT NOT NULL UNIQUE,
    level_name VARCHAR(100) NOT NULL, -- Explorer, Adventurer, Maya Master, etc.
    level_description TEXT,
    level_color VARCHAR(20) DEFAULT '#007bff', -- Color hex para UI
    level_icon VARCHAR(100) NULL, -- URL del icono
    
    -- Requisitos para alcanzar el nivel
    min_referrals INT DEFAULT 0,
    min_successful_referrals INT DEFAULT 0,
    min_points INT DEFAULT 0,
    min_earnings DECIMAL(10,2) DEFAULT 0.00,
    min_streak INT DEFAULT 0,
    
    -- Beneficios del nivel
    referrer_bonus_multiplier DECIMAL(3,2) DEFAULT 1.00, -- 1.5x = 150% del reward base
    referred_bonus_multiplier DECIMAL(3,2) DEFAULT 1.00,
    extra_points_per_referral INT DEFAULT 0,
    
    -- Beneficios exclusivos
    exclusive_rewards JSON NULL, -- Recompensas especiales del nivel
    perks JSON NULL, -- Beneficios adicionales: {'early_access': true, 'vip_support': true}
    
    -- Estado
    is_active BOOLEAN DEFAULT TRUE,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabla de badges/insignias
CREATE TABLE IF NOT EXISTS referral_badges (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Información del badge
    badge_name VARCHAR(100) NOT NULL,
    badge_slug VARCHAR(100) NOT NULL UNIQUE,
    badge_description TEXT,
    badge_icon VARCHAR(500) NULL,
    badge_color VARCHAR(20) DEFAULT '#28a745',
    
    -- Tipo de badge
    badge_category ENUM('milestone', 'achievement', 'special', 'seasonal', 'streak', 'social') DEFAULT 'milestone',
    badge_rarity ENUM('common', 'rare', 'epic', 'legendary') DEFAULT 'common',
    
    -- Criterios para obtener el badge
    criteria_type ENUM('referrals_count', 'earnings_amount', 'streak_days', 'social_shares', 'custom') NOT NULL,
    criteria_value INT NOT NULL,
    criteria_description TEXT,
    
    -- Recompensas del badge
    points_reward INT DEFAULT 0,
    monetary_reward DECIMAL(10,2) DEFAULT 0.00,
    
    -- Configuración
    is_active BOOLEAN DEFAULT TRUE,
    is_limited_time BOOLEAN DEFAULT FALSE,
    available_from DATE NULL,
    available_until DATE NULL,
    max_recipients INT NULL, -- NULL = ilimitado
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabla de badges obtenidos por usuarios
CREATE TABLE IF NOT EXISTS user_badges (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    user_id INT NOT NULL,
    badge_id INT NOT NULL,
    
    earned_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    points_earned INT DEFAULT 0,
    monetary_reward_earned DECIMAL(10,2) DEFAULT 0.00,
    
    -- Metadatos
    achievement_context TEXT NULL, -- Contexto específico de cómo se obtuvo
    is_featured BOOLEAN DEFAULT FALSE, -- Si se muestra prominentemente
    
    UNIQUE KEY unique_user_badge (user_id, badge_id),
    
    FOREIGN KEY (user_id) REFERENCES referral_users(id) ON DELETE CASCADE,
    FOREIGN KEY (badge_id) REFERENCES referral_badges(id) ON DELETE CASCADE
);

-- Tabla de challenges/retos
CREATE TABLE IF NOT EXISTS referral_challenges (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Información del challenge
    challenge_name VARCHAR(200) NOT NULL,
    challenge_description TEXT,
    challenge_icon VARCHAR(500) NULL,
    
    -- Tipo y duración
    challenge_type ENUM('daily', 'weekly', 'monthly', 'seasonal', 'one_time') NOT NULL,
    difficulty ENUM('easy', 'medium', 'hard', 'expert') DEFAULT 'medium',
    
    -- Objetivos
    target_type ENUM('referrals', 'earnings', 'social_shares', 'streak', 'bookings') NOT NULL,
    target_value INT NOT NULL,
    progress_description VARCHAR(255), -- "Refiere 5 amigos esta semana"
    
    -- Recompensas
    completion_points INT DEFAULT 0,
    completion_reward DECIMAL(10,2) DEFAULT 0.00,
    bonus_badge_id INT NULL, -- Badge especial por completar
    
    -- Período de validez
    start_date DATETIME NOT NULL,
    end_date DATETIME NOT NULL,
    
    -- Estado
    is_active BOOLEAN DEFAULT TRUE,
    max_participants INT NULL,
    current_participants INT DEFAULT 0,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (bonus_badge_id) REFERENCES referral_badges(id) ON DELETE SET NULL
);

-- Tabla de participación en challenges
CREATE TABLE IF NOT EXISTS user_challenges (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    user_id INT NOT NULL,
    challenge_id INT NOT NULL,
    
    -- Progreso
    start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    current_progress INT DEFAULT 0,
    is_completed BOOLEAN DEFAULT FALSE,
    completion_date TIMESTAMP NULL,
    
    -- Recompensas obtenidas
    points_earned INT DEFAULT 0,
    monetary_reward_earned DECIMAL(10,2) DEFAULT 0.00,
    badge_earned BOOLEAN DEFAULT FALSE,
    
    UNIQUE KEY unique_user_challenge (user_id, challenge_id),
    
    FOREIGN KEY (user_id) REFERENCES referral_users(id) ON DELETE CASCADE,
    FOREIGN KEY (challenge_id) REFERENCES referral_challenges(id) ON DELETE CASCADE
);

-- Tabla de recompensas y transacciones
CREATE TABLE IF NOT EXISTS referral_rewards (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Usuario que recibe la recompensa
    user_id INT NOT NULL,
    
    -- Origen de la recompensa
    reward_type ENUM('referral_commission', 'level_bonus', 'badge_reward', 'challenge_completion', 'streak_bonus', 'special_promotion') NOT NULL,
    reward_source_id INT NULL, -- ID del referido, badge, challenge, etc.
    
    -- Detalles de la recompensa
    reward_category ENUM('monetary', 'points', 'credit', 'discount', 'gift') NOT NULL,
    reward_amount DECIMAL(10,2) NOT NULL,
    points_awarded INT DEFAULT 0,
    
    -- Información del referido (si aplica)
    related_referral_id INT NULL,
    referral_booking_id INT NULL, -- Reserva que generó la recompensa
    
    -- Estado de la recompensa
    status ENUM('pending', 'approved', 'paid', 'expired', 'cancelled') DEFAULT 'pending',
    approval_date TIMESTAMP NULL,
    payment_date TIMESTAMP NULL,
    expiry_date TIMESTAMP NULL,
    
    -- Información de pago
    payment_method VARCHAR(50) NULL,
    payment_reference VARCHAR(100) NULL,
    payment_notes TEXT NULL,
    
    -- Metadatos
    description TEXT,
    admin_notes TEXT NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_user_rewards (user_id, status),
    INDEX idx_reward_type (reward_type, created_at),
    INDEX idx_payment_status (status, payment_date),
    
    FOREIGN KEY (user_id) REFERENCES referral_users(id) ON DELETE CASCADE,
    FOREIGN KEY (related_referral_id) REFERENCES referrals(id) ON DELETE SET NULL,
    FOREIGN KEY (referral_booking_id) REFERENCES reservas(id) ON DELETE SET NULL
);

-- Tabla de códigos promocionales especiales
CREATE TABLE IF NOT EXISTS referral_promo_codes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Usuario propietario
    user_id INT NOT NULL,
    
    -- Información del código
    promo_code VARCHAR(50) NOT NULL UNIQUE,
    code_type ENUM('referral_exclusive', 'level_reward', 'special_occasion', 'challenge_prize') DEFAULT 'referral_exclusive',
    
    -- Configuración del descuento
    discount_type ENUM('percentage', 'fixed_amount') NOT NULL,
    discount_value DECIMAL(10,2) NOT NULL,
    min_purchase_amount DECIMAL(10,2) DEFAULT 0.00,
    max_discount_amount DECIMAL(10,2) NULL,
    
    -- Límites de uso
    usage_limit INT DEFAULT 1,
    current_usage INT DEFAULT 0,
    usage_limit_per_customer INT DEFAULT 1,
    
    -- Validez
    valid_from DATETIME DEFAULT CURRENT_TIMESTAMP,
    valid_until DATETIME NOT NULL,
    
    -- Aplicabilidad
    applicable_products JSON NULL, -- Lista de IDs de productos
    applicable_categories JSON NULL, -- Lista de categorías
    
    -- Estado
    is_active BOOLEAN DEFAULT TRUE,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_promo_code (promo_code),
    INDEX idx_user_codes (user_id, is_active),
    
    FOREIGN KEY (user_id) REFERENCES referral_users(id) ON DELETE CASCADE
);

-- Tabla de estadísticas diarias del programa
CREATE TABLE IF NOT EXISTS referral_stats (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    date DATE NOT NULL UNIQUE,
    
    -- Estadísticas de referidos
    new_referrals INT DEFAULT 0,
    successful_conversions INT DEFAULT 0,
    total_active_referrers INT DEFAULT 0,
    
    -- Estadísticas de recompensas
    rewards_distributed DECIMAL(12,2) DEFAULT 0.00,
    points_awarded INT DEFAULT 0,
    badges_earned INT DEFAULT 0,
    challenges_completed INT DEFAULT 0,
    
    -- Estadísticas de ingresos
    referred_bookings_count INT DEFAULT 0,
    referred_bookings_value DECIMAL(12,2) DEFAULT 0.00,
    program_roi DECIMAL(5,2) DEFAULT 0.00, -- Return on Investment del programa
    
    -- Engagement
    social_shares INT DEFAULT 0,
    email_invitations INT DEFAULT 0,
    link_clicks INT DEFAULT 0,
    
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabla de actividades/log de eventos
CREATE TABLE IF NOT EXISTS referral_activities (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    user_id INT NOT NULL,
    activity_type ENUM(
        'referral_sent', 'referral_registered', 'referral_converted', 
        'reward_earned', 'level_upgraded', 'badge_earned', 'challenge_completed',
        'points_earned', 'payout_requested', 'payout_completed',
        'social_share', 'code_used', 'streak_milestone'
    ) NOT NULL,
    
    -- Detalles de la actividad
    activity_description TEXT NOT NULL,
    activity_data JSON NULL, -- Datos adicionales de la actividad
    
    -- Referencias
    related_referral_id INT NULL,
    related_reward_id INT NULL,
    related_badge_id INT NULL,
    related_challenge_id INT NULL,
    
    -- Metadatos
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_activity (user_id, activity_type, created_at),
    INDEX idx_activity_date (created_at DESC),
    
    FOREIGN KEY (user_id) REFERENCES referral_users(id) ON DELETE CASCADE,
    FOREIGN KEY (related_referral_id) REFERENCES referrals(id) ON DELETE SET NULL,
    FOREIGN KEY (related_reward_id) REFERENCES referral_rewards(id) ON DELETE SET NULL,
    FOREIGN KEY (related_badge_id) REFERENCES referral_badges(id) ON DELETE SET NULL,
    FOREIGN KEY (related_challenge_id) REFERENCES referral_challenges(id) ON DELETE SET NULL
);

-- Vista para dashboard de referidos
CREATE OR REPLACE VIEW referral_dashboard AS
SELECT 
    ru.id,
    ru.user_email,
    ru.user_name,
    ru.referral_code,
    ru.status,
    ru.total_referrals,
    ru.successful_referrals,
    ru.total_earnings,
    ru.available_balance,
    ru.current_level,
    ru.total_points,
    ru.current_streak,
    ru.badges_count,
    
    -- Información del nivel
    rl.level_name,
    rl.level_color,
    rl.level_icon,
    
    -- Próximo nivel
    (SELECT MIN(level_number) FROM referral_levels WHERE level_number > ru.current_level AND is_active = 1) as next_level,
    (SELECT level_name FROM referral_levels WHERE level_number = (SELECT MIN(level_number) FROM referral_levels WHERE level_number > ru.current_level AND is_active = 1)) as next_level_name,
    (SELECT min_successful_referrals FROM referral_levels WHERE level_number = (SELECT MIN(level_number) FROM referral_levels WHERE level_number > ru.current_level AND is_active = 1)) as next_level_required_referrals,
    
    -- Estadísticas del mes actual
    (SELECT COUNT(*) FROM referrals WHERE referrer_id = ru.id AND MONTH(created_at) = MONTH(NOW()) AND YEAR(created_at) = YEAR(NOW())) as referrals_this_month,
    (SELECT COUNT(*) FROM referrals WHERE referrer_id = ru.id AND status = 'first_purchase' AND MONTH(first_purchase_date) = MONTH(NOW()) AND YEAR(first_purchase_date) = YEAR(NOW())) as conversions_this_month,
    (SELECT COALESCE(SUM(reward_amount), 0) FROM referral_rewards WHERE user_id = ru.id AND MONTH(created_at) = MONTH(NOW()) AND YEAR(created_at) = YEAR(NOW()) AND status = 'approved') as earnings_this_month,
    
    -- Rankings
    (SELECT COUNT(*) + 1 FROM referral_users WHERE successful_referrals > ru.successful_referrals AND status = 'active') as referrals_ranking,
    (SELECT COUNT(*) + 1 FROM referral_users WHERE total_earnings > ru.total_earnings AND status = 'active') as earnings_ranking
    
FROM referral_users ru
LEFT JOIN referral_levels rl ON ru.current_level = rl.level_number
WHERE ru.status = 'active';

-- Procedimientos almacenados para gamificación

DELIMITER //

-- Procedimiento para procesar una nueva conversión de referido
CREATE PROCEDURE ProcessReferralConversion(
    IN p_referral_id INT,
    IN p_booking_amount DECIMAL(10,2),
    IN p_booking_id INT
)
BEGIN
    DECLARE v_referrer_id INT;
    DECLARE v_config_referrer_reward DECIMAL(10,2);
    DECLARE v_config_referred_reward DECIMAL(10,2);
    DECLARE v_level_multiplier DECIMAL(3,2);
    DECLARE v_final_reward DECIMAL(10,2);
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Obtener información del referido
    SELECT referrer_id INTO v_referrer_id
    FROM referrals 
    WHERE id = p_referral_id;
    
    -- Obtener configuración de recompensas
    SELECT referrer_reward_value, referred_reward_value INTO v_config_referrer_reward, v_config_referred_reward
    FROM referral_config 
    WHERE is_active = 1 
    LIMIT 1;
    
    -- Obtener multiplicador por nivel
    SELECT rl.referrer_bonus_multiplier INTO v_level_multiplier
    FROM referral_users ru
    JOIN referral_levels rl ON ru.current_level = rl.level_number
    WHERE ru.id = v_referrer_id;
    
    -- Calcular recompensa final
    SET v_final_reward = v_config_referrer_reward * COALESCE(v_level_multiplier, 1.00);
    
    -- Actualizar estado del referido
    UPDATE referrals 
    SET status = 'first_purchase',
        first_purchase_date = NOW(),
        first_booking_amount = p_booking_amount,
        total_spent = p_booking_amount,
        total_bookings = 1,
        rewards_generated = v_final_reward
    WHERE id = p_referral_id;
    
    -- Crear recompensa para el referidor
    INSERT INTO referral_rewards (
        user_id, reward_type, reward_source_id, reward_category, 
        reward_amount, related_referral_id, referral_booking_id, 
        description, status
    ) VALUES (
        v_referrer_id, 'referral_commission', p_referral_id, 'monetary',
        v_final_reward, p_referral_id, p_booking_id,
        CONCAT('Comisión por referido - Reserva #', p_booking_id), 'approved'
    );
    
    -- Actualizar estadísticas del referidor
    UPDATE referral_users 
    SET successful_referrals = successful_referrals + 1,
        total_earnings = total_earnings + v_final_reward,
        available_balance = available_balance + v_final_reward,
        lifetime_earnings = lifetime_earnings + v_final_reward,
        current_streak = current_streak + 1,
        total_points = total_points + 100 -- 100 puntos por conversión
    WHERE id = v_referrer_id;
    
    -- Verificar si debe subir de nivel
    CALL CheckLevelUpgrade(v_referrer_id);
    
    -- Verificar badges
    CALL CheckBadgeAchievements(v_referrer_id);
    
    -- Registrar actividad
    INSERT INTO referral_activities (
        user_id, activity_type, activity_description, 
        related_referral_id, activity_data
    ) VALUES (
        v_referrer_id, 'referral_converted', 
        CONCAT('Referido convertido - Ganaste $', v_final_reward),
        p_referral_id, JSON_OBJECT('booking_amount', p_booking_amount, 'reward', v_final_reward)
    );
    
    COMMIT;
END//

-- Procedimiento para verificar subida de nivel
CREATE PROCEDURE CheckLevelUpgrade(IN p_user_id INT)
BEGIN
    DECLARE v_current_level INT;
    DECLARE v_successful_referrals INT;
    DECLARE v_total_earnings DECIMAL(12,2);
    DECLARE v_total_points INT;
    DECLARE v_new_level INT;
    DECLARE v_level_name VARCHAR(100);
    
    -- Obtener estadísticas actuales del usuario
    SELECT current_level, successful_referrals, total_earnings, total_points
    INTO v_current_level, v_successful_referrals, v_total_earnings, v_total_points
    FROM referral_users 
    WHERE id = p_user_id;
    
    -- Encontrar el nivel más alto que califique
    SELECT MAX(level_number), level_name INTO v_new_level, v_level_name
    FROM referral_levels 
    WHERE level_number > v_current_level 
    AND is_active = 1
    AND v_successful_referrals >= min_successful_referrals
    AND v_total_earnings >= min_earnings
    AND v_total_points >= min_points;
    
    -- Si hay un nivel superior disponible
    IF v_new_level IS NOT NULL AND v_new_level > v_current_level THEN
        -- Actualizar nivel del usuario
        UPDATE referral_users 
        SET current_level = v_new_level 
        WHERE id = p_user_id;
        
        -- Otorgar recompensa por subir de nivel
        INSERT INTO referral_rewards (
            user_id, reward_type, reward_category, reward_amount, 
            description, status
        ) VALUES (
            p_user_id, 'level_bonus', 'monetary', 50.00, 
            CONCAT('Bonus por alcanzar nivel: ', v_level_name), 'approved'
        );
        
        -- Registrar actividad
        INSERT INTO referral_activities (
            user_id, activity_type, activity_description, activity_data
        ) VALUES (
            p_user_id, 'level_upgraded', 
            CONCAT('¡Subiste al nivel ', v_level_name, '!'),
            JSON_OBJECT('old_level', v_current_level, 'new_level', v_new_level, 'level_name', v_level_name)
        );
    END IF;
END//

-- Procedimiento para verificar logro de badges
CREATE PROCEDURE CheckBadgeAchievements(IN p_user_id INT)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE v_badge_id INT;
    DECLARE v_badge_name VARCHAR(100);
    DECLARE v_criteria_type VARCHAR(50);
    DECLARE v_criteria_value INT;
    DECLARE v_points_reward INT;
    DECLARE v_monetary_reward DECIMAL(10,2);
    DECLARE v_user_value INT;
    
    DECLARE badge_cursor CURSOR FOR
        SELECT id, badge_name, criteria_type, criteria_value, points_reward, monetary_reward
        FROM referral_badges 
        WHERE is_active = 1
        AND id NOT IN (SELECT badge_id FROM user_badges WHERE user_id = p_user_id);
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN badge_cursor;
    
    badge_loop: LOOP
        FETCH badge_cursor INTO v_badge_id, v_badge_name, v_criteria_type, v_criteria_value, v_points_reward, v_monetary_reward;
        
        IF done THEN
            LEAVE badge_loop;
        END IF;
        
        -- Obtener valor actual del usuario según el criterio
        CASE v_criteria_type
            WHEN 'referrals_count' THEN
                SELECT total_referrals INTO v_user_value FROM referral_users WHERE id = p_user_id;
            WHEN 'earnings_amount' THEN
                SELECT FLOOR(total_earnings) INTO v_user_value FROM referral_users WHERE id = p_user_id;
            WHEN 'streak_days' THEN
                SELECT best_streak INTO v_user_value FROM referral_users WHERE id = p_user_id;
            WHEN 'social_shares' THEN
                SELECT (facebook_shares + whatsapp_shares + instagram_shares) INTO v_user_value FROM referral_users WHERE id = p_user_id;
        END CASE;
        
        -- Verificar si cumple el criterio
        IF v_user_value >= v_criteria_value THEN
            -- Otorgar badge
            INSERT INTO user_badges (user_id, badge_id, points_earned, monetary_reward_earned)
            VALUES (p_user_id, v_badge_id, v_points_reward, v_monetary_reward);
            
            -- Actualizar contador de badges
            UPDATE referral_users 
            SET badges_count = badges_count + 1,
                total_points = total_points + v_points_reward
            WHERE id = p_user_id;
            
            -- Crear recompensa monetaria si aplica
            IF v_monetary_reward > 0 THEN
                INSERT INTO referral_rewards (
                    user_id, reward_type, reward_source_id, reward_category, 
                    reward_amount, description, status
                ) VALUES (
                    p_user_id, 'badge_reward', v_badge_id, 'monetary',
                    v_monetary_reward, CONCAT('Recompensa por badge: ', v_badge_name), 'approved'
                );
            END IF;
            
            -- Registrar actividad
            INSERT INTO referral_activities (
                user_id, activity_type, activity_description, 
                related_badge_id, activity_data
            ) VALUES (
                p_user_id, 'badge_earned', 
                CONCAT('¡Ganaste el badge: ', v_badge_name, '!'),
                v_badge_id, JSON_OBJECT('points', v_points_reward, 'monetary_reward', v_monetary_reward)
            );
        END IF;
        
    END LOOP;
    
    CLOSE badge_cursor;
END//

DELIMITER ;

-- Insertar configuración inicial
INSERT INTO referral_config (
    program_name, program_description, is_active,
    referrer_reward_value, referred_reward_value,
    min_booking_amount, minimum_payout
) VALUES (
    'Programa Maya Explorers', 
    'Comparte la magia del mundo Maya con tus amigos y gana recompensas increíbles',
    TRUE, 25.00, 15.00, 50.00, 25.00
) ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;

-- Insertar niveles iniciales
INSERT INTO referral_levels (level_number, level_name, level_description, level_color, min_successful_referrals, min_earnings, referrer_bonus_multiplier) VALUES
(1, 'Maya Novato', 'Comenzando tu aventura como explorador Maya', '#6c757d', 0, 0.00, 1.00),
(2, 'Explorador', 'Ya conoces los secretos básicos del mundo Maya', '#17a2b8', 3, 75.00, 1.10),
(3, 'Aventurero', 'Un verdadero conocedor de la cultura Maya', '#28a745', 8, 200.00, 1.25),
(4, 'Guía Maya', 'Compartes la sabiduría ancestral con otros', '#ffc107', 15, 400.00, 1.50),
(5, 'Maestro Maya', 'El nivel más alto de sabiduría y experiencia', '#dc3545', 30, 1000.00, 2.00)
ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;

-- Insertar badges iniciales
INSERT INTO referral_badges (badge_name, badge_slug, badge_description, badge_category, criteria_type, criteria_value, points_reward) VALUES
('Primer Referido', 'first_referral', 'Refirió a su primer amigo al mundo Maya', 'milestone', 'referrals_count', 1, 50),
('Embajador Maya', 'maya_ambassador', 'Refirió a 5 exploradores al mundo Maya', 'milestone', 'referrals_count', 5, 200),
('Influencer Maya', 'maya_influencer', 'Refirió a 20 aventureros', 'achievement', 'referrals_count', 20, 500),
('Primer Dólar', 'first_dollar', 'Ganó sus primeras recompensas', 'milestone', 'earnings_amount', 1, 25),
('Cazador de Tesoros', 'treasure_hunter', 'Acumuló $500 en recompensas', 'achievement', 'earnings_amount', 500, 300),
('Racha de Fuego', 'fire_streak', 'Mantuvo una racha de 7 días', 'streak', 'streak_days', 7, 100),
('Social Maya', 'social_maya', 'Compartió en redes sociales 10 veces', 'social', 'social_shares', 10, 75)
ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;

-- Insertar challenge inicial
INSERT INTO referral_challenges (
    challenge_name, challenge_description, challenge_type, target_type, target_value,
    completion_points, completion_reward, start_date, end_date
) VALUES (
    'Desafío del Explorador', 
    'Refiere a 3 amigos este mes y gana recompensas extra',
    'monthly', 'referrals', 3, 500, 25.00,
    DATE_FORMAT(NOW(), '%Y-%m-01'), LAST_DAY(NOW())
) ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;

-- Crear índices adicionales para optimización
CREATE INDEX idx_referrals_conversion ON referrals(referrer_id, status, first_purchase_date);
CREATE INDEX idx_rewards_payout ON referral_rewards(user_id, status, payment_date);
CREATE INDEX idx_activities_timeline ON referral_activities(user_id, created_at DESC);
CREATE INDEX idx_stats_date ON referral_stats(date DESC);