kknongchang_2506/app/Module/Promotionurs/Docs/create.sql

-- 团队模块数据库创建脚本
-- 包含所有团队模块相关表的创建语句

-- 1. 用户推荐关系表 (promotion_user_referrals)
-- 存储用户与其直接推荐人（直接上级）的关系
CREATE TABLE IF NOT EXISTS `kku_promotion_user_referrals` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `referrer_id` bigint(20) NOT NULL COMMENT '直接推荐人ID',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_id` (`user_id`),
  KEY `idx_referrer_id` (`referrer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户直接推荐关系表';

-- 2. 达人等级表 (promotion_user_talents)
-- 存储用户的达人等级信息，包括直推人数和团队总人数
CREATE TABLE IF NOT EXISTS `kku_promotion_user_talents` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `talent_level` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '达人等级:0无,1初级,2中级,3高级,4资深,5顶级',
  `direct_count` int(11) NOT NULL DEFAULT '0' COMMENT '直推人数',
  `promotion_count` int(11) NOT NULL DEFAULT '0' COMMENT '团队总人数',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_id` (`user_id`),
  KEY `idx_talent_level` (`talent_level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='达人等级表';

-- 3. 团队收益记录表 (promotion_profits)
-- 记录团队成员产生的分成收益
CREATE TABLE IF NOT EXISTS `kku_promotion_profits` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint(20) NOT NULL COMMENT '获得收益的用户ID',
  `promotion_member_id` bigint(20) NOT NULL COMMENT '团队成员ID',
  `source_id` bigint(20) NOT NULL COMMENT '收益来源ID',
  `source_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '收益来源类型',
  `item_id` bigint(20) NOT NULL COMMENT '物品ID',
  `profit_amount` int(11) NOT NULL COMMENT '分成收益数量',
  `profit_rate` decimal(5,4) NOT NULL COMMENT '分成比例',
  `relation_type` tinyint(1) NOT NULL DEFAULT '1' COMMENT '关系类型:1直推,2间推',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_promotion_member_id` (`promotion_member_id`),
  KEY `idx_source` (`source_type`,`source_id`),
  KEY `idx_relation_type` (`relation_type`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='团队收益记录表';

-- 4. 推荐码表 (promotion_referral_codes)
-- 存储用户的推荐码信息
CREATE TABLE IF NOT EXISTS `kku_promotion_referral_codes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `code` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '推荐码',
  `usage_count` int(11) NOT NULL DEFAULT '0' COMMENT '使用次数',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '状态:1有效,0无效',
  `expire_time` timestamp NULL DEFAULT NULL COMMENT '过期时间,NULL表示永不过期',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_code` (`code`),
  UNIQUE KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='推荐码表';

-- 5. 达人等级配置表 (promotion_talent_configs)
-- 存储不同达人等级的配置信息
CREATE TABLE IF NOT EXISTS `kku_promotion_talent_configs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `level` tinyint(3) unsigned NOT NULL COMMENT '等级',
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '等级名称',
  `direct_count_required` int(11) NOT NULL COMMENT '所需直推人数',
  `promotion_count_required` int(11) NOT NULL COMMENT '所需团队总人数',
  `profit_rate` decimal(5,4) NOT NULL COMMENT '间推分成比例',
  `benefits` json DEFAULT NULL COMMENT '等级权益',
  `icon` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '等级图标',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_level` (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='达人等级配置表';

-- 6. 收益分成规则表 (promotion_profit_rules)
-- 存储不同来源的收益分成规则
CREATE TABLE IF NOT EXISTS `kku_promotion_profit_rules` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `source_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '来源类型',
  `direct_profit_rate` decimal(5,4) NOT NULL COMMENT '直推分成比例',
  `max_indirect_level` int(11) NOT NULL DEFAULT '20' COMMENT '最大间推层级',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '状态:1有效,0无效',
  `rules` json DEFAULT NULL COMMENT '特殊规则',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_source_type` (`source_type`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='收益分成规则表';

-- 7. 推荐关系修改记录表 (promotion_referral_changes)
-- 记录用户推荐关系的修改历史
CREATE TABLE IF NOT EXISTS `kku_promotion_referral_changes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `old_referrer_id` bigint(20) DEFAULT NULL COMMENT '旧推荐人ID',
  `new_referrer_id` bigint(20) NOT NULL COMMENT '新推荐人ID',
  `change_time` timestamp NOT NULL COMMENT '修改时间',
  `change_reason` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '修改原因',
  `changed_by` bigint(20) NOT NULL COMMENT '操作人ID',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_change_time` (`change_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='推荐关系修改记录表';

-- 8. 用户关系缓存表 (promotion_user_relation_cache)
-- 用于存储用户之间的所有推荐关系（包括上下级关系）
CREATE TABLE IF NOT EXISTS `kku_promotion_user_relation_cache` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `related_user_id` bigint(20) NOT NULL COMMENT '关联用户ID（上级）',
  `level` tinyint(3) unsigned NOT NULL COMMENT '关系层级:1直接,2间接',
  `path` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '关系路径,格式:1,2,3',
  `depth` tinyint(3) unsigned NOT NULL COMMENT '层级深度,从1开始',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_relation` (`user_id`,`related_user_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_related_user_id` (`related_user_id`),
  KEY `idx_level` (`level`),
  KEY `idx_depth` (`depth`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户关系缓存表';

-- 9. 邀请奖励记录表 (promotion_invite_rewards)
-- 用于记录用户邀请他人注册时获得的奖励
CREATE TABLE IF NOT EXISTS `kku_promotion_invite_rewards` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint(20) NOT NULL COMMENT '获得奖励的用户ID',
  `invited_user_id` bigint(20) NOT NULL COMMENT '被邀请的用户ID',
  `reward_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '奖励类型:item=物品,coin=货币,exp=经验',
  `reward_id` bigint(20) NOT NULL COMMENT '奖励ID(物品ID或货币类型ID)',
  `reward_amount` int(11) NOT NULL COMMENT '奖励数量',
  `reward_source` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'register' COMMENT '奖励来源:register=注册,upgrade=升级,task=任务',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '状态:0=未发放,1=已发放,2=已过期',
  `remark` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_invited_user_id` (`invited_user_id`),
  KEY `idx_reward_type` (`reward_type`),
  KEY `idx_reward_source` (`reward_source`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='邀请奖励记录表';

-- 10. 邀请码使用记录表 (promotion_referral_code_usages)
-- 用于详细记录每次邀请码的使用情况
CREATE TABLE IF NOT EXISTS `kku_promotion_referral_code_usages` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `code` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '使用的邀请码',
  `code_owner_id` bigint(20) NOT NULL COMMENT '邀请码所有者用户ID',
  `user_id` bigint(20) NOT NULL COMMENT '使用邀请码的用户ID',
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '使用时的IP地址',
  `user_agent` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '使用时的用户代理',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '状态:0=失败,1=成功,2=已撤销',
  `result` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '使用结果描述',
  `remark` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_code` (`code`),
  KEY `idx_code_owner_id` (`code_owner_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='邀请码使用记录表';

-- 初始化数据

-- 1. 达人等级配置数据
INSERT INTO `kku_promotion_talent_configs` (`level`, `name`, `direct_count_required`, `promotion_count_required`, `profit_rate`, `benefits`) VALUES
(1, '初级达人', 5, 10, 0.0100, '{"farm_output_bonus": 0.01}'),
(2, '中级达人', 10, 30, 0.0150, '{"farm_output_bonus": 0.02}'),
(3, '高级达人', 20, 50, 0.0200, '{"farm_output_bonus": 0.03, "daily_gift": {"item_id": 1001, "amount": 1}}'),
(4, '资深达人', 30, 100, 0.0250, '{"farm_output_bonus": 0.04, "daily_gift": {"item_id": 1001, "amount": 2}}'),
(5, '顶级达人', 50, 200, 0.0300, '{"farm_output_bonus": 0.05, "daily_gift": {"item_id": 1001, "amount": 3}, "special_privileges": ["vip_customer_service", "exclusive_avatar_frame"]}');

-- 2. 收益分成规则数据
INSERT INTO `kku_promotion_profit_rules` (`source_type`, `direct_profit_rate`, `max_indirect_level`, `status`, `rules`) VALUES
('farm_harvest', 0.0500, 20, 1, '{"min_amount": 10, "max_amount": 1000}'),
('task_complete', 0.0300, 10, 1, '{"min_amount": 5, "max_amount": 500}'),
('item_sell', 0.0200, 5, 1, '{"min_amount": 1, "max_amount": 100, "excluded_items": [1001, 1002]}');