kknongchang_2506/app/Module/UrsPromotion/Database/add_active_user_fields.sql

-- URS推广模块活跃用户功能数据库扩展脚本
-- 创建时间: 2025-06-16
-- 功能: 为URS推广模块添加活跃用户概念支持

-- =====================================================
-- 1. 扩展用户映射表，添加活跃用户相关字段
-- =====================================================

-- 添加活跃状态字段
ALTER TABLE `kku_urs_promotion_user_mappings` 
ADD COLUMN `is_active` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否活跃:1活跃,0不活跃' AFTER `status`;

-- 添加最后活跃检查时间字段
ALTER TABLE `kku_urs_promotion_user_mappings` 
ADD COLUMN `last_activity_check` TIMESTAMP NULL COMMENT '最后活跃检查时间' AFTER `is_active`;

-- 添加活跃天数统计字段
ALTER TABLE `kku_urs_promotion_user_mappings` 
ADD COLUMN `active_days_count` INT NOT NULL DEFAULT 0 COMMENT '活跃天数统计' AFTER `last_activity_check`;

-- 添加活跃状态索引
ALTER TABLE `kku_urs_promotion_user_mappings` 
ADD INDEX `idx_is_active` (`is_active`);

-- 添加活跃检查时间索引
ALTER TABLE `kku_urs_promotion_user_mappings` 
ADD INDEX `idx_last_activity_check` (`last_activity_check`);

-- =====================================================
-- 2. 扩展达人等级配置表，添加活跃人数条件
-- =====================================================

-- 添加所需活跃人数字段
ALTER TABLE `kku_urs_promotion_talent_configs` 
ADD COLUMN `active_count_required` INT NOT NULL DEFAULT 0 COMMENT '所需活跃人数' AFTER `promotion_count_required`;

-- 添加所需直推活跃人数字段
ALTER TABLE `kku_urs_promotion_talent_configs` 
ADD COLUMN `active_direct_required` INT NOT NULL DEFAULT 0 COMMENT '所需直推活跃人数' AFTER `active_count_required`;

-- =====================================================
-- 3. 更新达人等级配置数据，设置活跃人数要求
-- =====================================================

-- 更新各等级的活跃人数要求
UPDATE `kku_urs_promotion_talent_configs` SET 
    `active_count_required` = 0,
    `active_direct_required` = 0
WHERE `level` = 0; -- 非达人

UPDATE `kku_urs_promotion_talent_configs` SET 
    `active_count_required` = 3,
    `active_direct_required` = 2
WHERE `level` = 1; -- 初级达人

UPDATE `kku_urs_promotion_talent_configs` SET 
    `active_count_required` = 8,
    `active_direct_required` = 5
WHERE `level` = 2; -- 中级达人

UPDATE `kku_urs_promotion_talent_configs` SET 
    `active_count_required` = 15,
    `active_direct_required` = 8
WHERE `level` = 3; -- 高级达人

UPDATE `kku_urs_promotion_talent_configs` SET 
    `active_count_required` = 30,
    `active_direct_required` = 15
WHERE `level` = 4; -- 资深达人

UPDATE `kku_urs_promotion_talent_configs` SET 
    `active_count_required` = 50,
    `active_direct_required` = 25
WHERE `level` = 5; -- 顶级达人

-- =====================================================
-- 4. 验证数据库结构
-- =====================================================

-- 验证用户映射表结构
DESCRIBE `kku_urs_promotion_user_mappings`;

-- 验证达人等级配置表结构
DESCRIBE `kku_urs_promotion_talent_configs`;

-- 验证索引创建
SHOW INDEX FROM `kku_urs_promotion_user_mappings` WHERE Key_name IN ('idx_is_active', 'idx_last_activity_check');

-- 验证配置数据更新
SELECT `level`, `name`, `direct_count_required`, `promotion_count_required`, `active_count_required`, `active_direct_required` 
FROM `kku_urs_promotion_talent_configs` 
ORDER BY `level`;

-- =====================================================
-- 5. 初始化活跃状态数据
-- =====================================================

-- 为现有用户初始化活跃状态（基于最近15天的活动）
UPDATE `kku_urs_promotion_user_mappings` m
INNER JOIN `kku_users` u ON m.user_id = u.id
SET 
    m.is_active = CASE 
        WHEN u.last_activity_time >= DATE_SUB(NOW(), INTERVAL 15 DAY) THEN 1 
        ELSE 0 
    END,
    m.last_activity_check = NOW(),
    m.active_days_count = CASE 
        WHEN u.last_activity_time >= DATE_SUB(NOW(), INTERVAL 15 DAY) THEN 1 
        ELSE 0 
    END
WHERE m.status = 1; -- 只处理有效的映射关系

-- 统计初始化结果
SELECT 
    COUNT(*) as total_mappings,
    SUM(is_active) as active_users,
    SUM(CASE WHEN is_active = 0 THEN 1 ELSE 0 END) as inactive_users,
    ROUND(SUM(is_active) * 100.0 / COUNT(*), 2) as active_percentage
FROM `kku_urs_promotion_user_mappings` 
WHERE status = 1;

-- =====================================================
-- 脚本执行完成
-- =====================================================

SELECT 'URS推广模块活跃用户功能数据库扩展完成' as message;