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

-- URS推广模块数据库创建脚本
-- 创建时间: 2025-06-14
-- 版本: v1.0.0

-- 1. URS用户推荐关系表
CREATE TABLE `kku_urs_promotion_user_referrals` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `urs_user_id` bigint unsigned NOT NULL COMMENT 'URS用户ID（核心）',
  `user_id` bigint unsigned NOT NULL COMMENT '农场用户ID（辅助）',
  `urs_referrer_id` bigint unsigned NOT NULL COMMENT 'URS推荐人ID（核心）',
  `referrer_id` bigint unsigned NOT NULL COMMENT '农场推荐人ID（辅助）',
  `referral_code` varchar(32) DEFAULT NULL COMMENT '使用的推荐码（历史记录）',
  `referral_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '推荐时间',
  `status` tinyint NOT NULL DEFAULT '1' COMMENT '状态:1有效,0无效',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_urs_user_id` (`urs_user_id`),
  UNIQUE KEY `uk_user_id` (`user_id`),
  KEY `idx_urs_referrer_id` (`urs_referrer_id`),
  KEY `idx_referrer_id` (`referrer_id`),
  KEY `idx_referral_code` (`referral_code`),
  KEY `idx_referral_time` (`referral_time`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='URS用户推荐关系表';

-- 2. URS达人等级表
CREATE TABLE `kku_urs_promotion_user_talents` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `urs_user_id` bigint unsigned NOT NULL COMMENT 'URS用户ID（核心）',
  `user_id` bigint unsigned NOT NULL COMMENT '农场用户ID（辅助）',
  `talent_level` tinyint NOT NULL DEFAULT '0' COMMENT 'URS达人等级:0无,1初级,2中级,3高级,4资深,5顶级',
  `direct_count` int NOT NULL DEFAULT '0' COMMENT '直推人数',
  `promotion_count` int NOT NULL DEFAULT '0' COMMENT '团队总人数',
  `last_level_update_time` timestamp NULL DEFAULT NULL COMMENT '最后等级更新时间',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_urs_user_id` (`urs_user_id`),
  UNIQUE KEY `uk_user_id` (`user_id`),
  KEY `idx_talent_level` (`talent_level`),
  KEY `idx_direct_count` (`direct_count`),
  KEY `idx_promotion_count` (`promotion_count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='URS达人等级表';

-- 3. URS团队收益记录表
CREATE TABLE `kku_urs_promotion_profits` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `urs_user_id` bigint unsigned NOT NULL COMMENT '获得收益的URS用户ID（核心）',
  `user_id` bigint unsigned NOT NULL COMMENT '获得收益的农场用户ID（辅助）',
  `urs_promotion_member_id` bigint unsigned NOT NULL COMMENT '团队成员URS用户ID（核心）',
  `promotion_member_id` bigint unsigned NOT NULL COMMENT '团队成员农场用户ID（辅助）',
  `source_id` bigint unsigned NOT NULL COMMENT '收益来源ID',
  `source_type` varchar(32) NOT NULL COMMENT '收益来源类型',
  `relation_type` varchar(16) NOT NULL COMMENT '关系类型:direct直推,indirect间推',
  `relation_level` tinyint NOT NULL DEFAULT '1' COMMENT '推荐层级:1直推,2-20间推',
  `original_amount` decimal(30,10) NOT NULL DEFAULT '0.0000000000' COMMENT '原始收益金额',
  `profit_amount` decimal(30,10) NOT NULL DEFAULT '0.0000000000' COMMENT '分成收益金额',
  `profit_rate` decimal(8,6) NOT NULL DEFAULT '0.000000' COMMENT '分成比例',
  `talent_level` tinyint NOT NULL DEFAULT '0' COMMENT '获得收益时的达人等级',
  `status` tinyint NOT NULL DEFAULT '1' COMMENT '状态:1正常,0取消',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_urs_user_id` (`urs_user_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_urs_promotion_member_id` (`urs_promotion_member_id`),
  KEY `idx_promotion_member_id` (`promotion_member_id`),
  KEY `idx_source` (`source_type`,`source_id`),
  KEY `idx_relation_type` (`relation_type`),
  KEY `idx_relation_level` (`relation_level`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='URS团队收益记录表';

-- 4. URS达人等级配置表
CREATE TABLE `kku_urs_promotion_talent_configs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `level` tinyint NOT NULL COMMENT '等级:0无,1初级,2中级,3高级,4资深,5顶级',
  `name` varchar(32) NOT NULL COMMENT '等级名称',
  `direct_count_required` int NOT NULL DEFAULT '0' COMMENT '所需直推人数',
  `promotion_count_required` int NOT NULL DEFAULT '0' COMMENT '所需团队总人数',
  `profit_rate` decimal(8,6) NOT NULL DEFAULT '0.000000' COMMENT '间推分成比例',
  `benefits` json DEFAULT NULL COMMENT '等级权益JSON',
  `icon` varchar(255) DEFAULT NULL COMMENT '等级图标',
  `description` text COMMENT '等级描述',
  `sort_order` int NOT NULL DEFAULT '0' COMMENT '排序权重',
  `status` tinyint NOT NULL DEFAULT '1' COMMENT '状态:1启用,0禁用',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_level` (`level`),
  KEY `idx_sort_order` (`sort_order`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='URS达人等级配置表';

-- 6. URS收益分成规则表
CREATE TABLE `kku_urs_promotion_profit_rules` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `source_type` varchar(32) NOT NULL COMMENT '收益来源类型',
  `name` varchar(64) NOT NULL COMMENT '规则名称',
  `direct_profit_rate` decimal(8,6) NOT NULL DEFAULT '0.050000' COMMENT '直推分成比例',
  `max_indirect_level` tinyint NOT NULL DEFAULT '20' COMMENT '最大间推层级',
  `min_amount` decimal(30,10) NOT NULL DEFAULT '0.0000000000' COMMENT '最小分成金额',
  `max_amount` decimal(30,10) NOT NULL DEFAULT '0.0000000000' COMMENT '最大分成金额,0为无限制',
  `rules` json DEFAULT NULL COMMENT '扩展规则JSON',
  `description` text COMMENT '规则描述',
  `sort_order` int NOT NULL DEFAULT '0' COMMENT '排序权重',
  `status` tinyint NOT NULL DEFAULT '1' COMMENT '状态:1启用,0禁用',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_source_type` (`source_type`),
  KEY `idx_status` (`status`),
  KEY `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='URS收益分成规则表';

-- 7. URS推荐关系修改记录表
CREATE TABLE `kku_urs_promotion_referral_changes` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint unsigned NOT NULL COMMENT '用户ID',
  `old_referrer_id` bigint unsigned DEFAULT NULL COMMENT '原推荐人ID',
  `new_referrer_id` bigint unsigned DEFAULT NULL COMMENT '新推荐人ID',
  `change_reason` varchar(64) NOT NULL COMMENT '修改原因',
  `change_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
  `operator_id` bigint unsigned DEFAULT NULL COMMENT '操作人ID',
  `remark` text COMMENT '备注',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_old_referrer_id` (`old_referrer_id`),
  KEY `idx_new_referrer_id` (`new_referrer_id`),
  KEY `idx_change_time` (`change_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='URS推荐关系修改记录表';

-- 8. URS用户关系缓存表
CREATE TABLE `kku_urs_promotion_user_relation_cache` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint unsigned NOT NULL COMMENT '用户ID',
  `related_user_id` bigint unsigned NOT NULL COMMENT '关联用户ID',
  `relation_type` varchar(16) NOT NULL COMMENT '关系类型:referrer上级,member下级',
  `level` tinyint NOT NULL DEFAULT '1' COMMENT '关系层级:1直接,2-20间接',
  `depth` tinyint NOT NULL DEFAULT '1' COMMENT '关系深度',
  `path` text COMMENT '关系路径',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_related` (`user_id`,`related_user_id`,`relation_type`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_related_user_id` (`related_user_id`),
  KEY `idx_relation_type` (`relation_type`),
  KEY `idx_level` (`level`),
  KEY `idx_depth` (`depth`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='URS用户关系缓存表';

-- 9. URS邀请奖励记录表
CREATE TABLE `kku_urs_promotion_invite_rewards` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint unsigned NOT NULL COMMENT '获得奖励的用户ID',
  `invited_user_id` bigint unsigned NOT NULL COMMENT '被邀请用户ID',
  `reward_type` varchar(32) NOT NULL COMMENT '奖励类型',
  `reward_source` varchar(32) NOT NULL COMMENT '奖励来源',
  `reward_amount` decimal(30,10) NOT NULL DEFAULT '0.0000000000' COMMENT '奖励数量',
  `reward_item_id` bigint unsigned DEFAULT NULL COMMENT '奖励物品ID',
  `status` tinyint NOT NULL DEFAULT '1' COMMENT '状态:1已发放,0待发放,2已取消',
  `granted_at` timestamp NULL DEFAULT NULL COMMENT '发放时间',
  `expire_at` timestamp NULL DEFAULT NULL COMMENT '过期时间',
  `remark` text COMMENT '备注',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT 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='URS邀请奖励记录表';