kknongchang_2506/app/Module/UrsPromotion/Databases/createsql/upgrade_to_v3_completed.sql

-- URS推广模块数据库升级完成记录
-- 升级时间: 2025-06-15 18:01
-- 升级版本: v3.0.0 (分离映射关系版本)
-- 升级说明: 将字段名从user_id/referrer_id/promotion_member_id统一改为urs_user_id/urs_referrer_id/urs_promotion_member_id

-- ========================================
-- 升级内容概述
-- ========================================

-- 1. kku_urs_promotion_profits表升级
--    - 添加字段: urs_user_id, urs_promotion_member_id, farm_user_id
--    - 删除字段: user_id, promotion_member_id
--    - 数据迁移: 将旧字段数据复制到新字段
--    - 索引重建: 删除旧索引，添加新索引

-- 2. kku_urs_promotion_user_referrals表升级
--    - 添加字段: urs_user_id, urs_referrer_id
--    - 删除字段: user_id, referrer_id
--    - 数据迁移: 将旧字段数据复制到新字段
--    - 索引重建: 删除旧索引，添加新索引

-- 3. kku_urs_promotion_user_talents表升级
--    - 添加字段: urs_user_id
--    - 删除字段: user_id
--    - 数据迁移: 将旧字段数据复制到新字段
--    - 索引重建: 删除旧索引，添加新索引

-- 4. 模型关系修复
--    - UrsProfit模型: 修复关系定义，使用新的字段名
--    - UrsUserTalent模型: 修复关系定义，使用新的字段名
--    - 添加新的关系方法: userMapping(), promotionMemberMapping(), farmUser()

-- ========================================
-- 升级后的表结构验证
-- ========================================

-- 验证kku_urs_promotion_user_mappings表 (无变化)
-- 字段: id, urs_user_id, user_id, mapping_time, status, created_at, updated_at

-- 验证kku_urs_promotion_user_referrals表 (已升级)
-- 字段: id, urs_user_id, urs_referrer_id, referral_code, referral_time, status, created_at, updated_at

-- 验证kku_urs_promotion_user_talents表 (已升级)
-- 字段: id, urs_user_id, talent_level, direct_count, indirect_count, third_count, promotion_count, last_level_update_time, created_at, updated_at

-- 验证kku_urs_promotion_profits表 (已升级)
-- 字段: id, urs_user_id, urs_promotion_member_id, source_id, source_type, profit_type, relation_level, original_amount, profit_amount, profit_rate, reward_group_id, talent_level, farm_user_id, status, created_at, updated_at

-- 验证kku_urs_promotion_talent_configs表 (无变化)
-- 字段: id, level, name, direct_count_required, promotion_count_required, icon, description, sort_order, status, created_at, updated_at, promotion_direct_group, promotion_indirect_group, promotion_third_group, planting_direct_rate, planting_indirect_rate, planting_third_rate

-- ========================================
-- 升级验证查询
-- ========================================

-- 验证所有表是否存在
SELECT 'Tables Check' as check_type;
SHOW TABLES LIKE 'kku_urs_promotion_%';

-- 验证关键字段是否存在
SELECT 'Fields Check' as check_type;
SELECT 
    'user_mappings' as table_name,
    COUNT(*) as field_count
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'kku_urs_promotion_user_mappings'
  AND COLUMN_NAME IN ('urs_user_id', 'user_id');

SELECT 
    'user_referrals' as table_name,
    COUNT(*) as field_count
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'kku_urs_promotion_user_referrals'
  AND COLUMN_NAME IN ('urs_user_id', 'urs_referrer_id');

SELECT 
    'user_talents' as table_name,
    COUNT(*) as field_count
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'kku_urs_promotion_user_talents'
  AND COLUMN_NAME = 'urs_user_id';

SELECT 
    'profits' as table_name,
    COUNT(*) as field_count
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'kku_urs_promotion_profits'
  AND COLUMN_NAME IN ('urs_user_id', 'urs_promotion_member_id', 'farm_user_id');

-- ========================================
-- 升级状态
-- ========================================
-- 状态: 已完成
-- 数据完整性: 已验证
-- 模型关系: 已修复
-- 索引优化: 已完成
-- 备份状态: 用户已备份数据库

-- 注意事项:
-- 1. 本次升级已完成数据迁移，旧字段已删除
-- 2. 模型关系已更新，使用新的字段名
-- 3. 所有索引已重建，查询性能不受影响
-- 4. 升级过程中保持了数据完整性
-- 5. 建议在生产环境使用前进行充分测试