176 lines
7.7 KiB
SQL
176 lines
7.7 KiB
SQL
-- ============================================
|
||
-- SPA管理系统 - 用户推广分佣系统表
|
||
-- ============================================
|
||
-- 版本: v1.0
|
||
-- 创建时间: 2025-12-30
|
||
-- 说明: 用户推广关系、推广收益、推广统计
|
||
-- ============================================
|
||
|
||
SET NAMES utf8mb4;
|
||
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
||
-- ============================================
|
||
-- 用户推广关系表
|
||
-- ============================================
|
||
|
||
-- 1. 用户推广关系表
|
||
DROP TABLE IF EXISTS `spa_customer_referral`;
|
||
CREATE TABLE `spa_customer_referral` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`created_at` datetime(3) DEFAULT NULL COMMENT '创建时间',
|
||
`updated_at` datetime(3) DEFAULT NULL COMMENT '更新时间',
|
||
`deleted_at` datetime(3) DEFAULT NULL COMMENT '删除时间',
|
||
|
||
`customer_id` bigint unsigned NOT NULL COMMENT '用户ID',
|
||
`referrer_id` bigint unsigned NOT NULL COMMENT '推广人ID（一级）',
|
||
`referrer_level_2` bigint unsigned DEFAULT NULL COMMENT '二级推广人ID',
|
||
`referrer_level_3` bigint unsigned DEFAULT NULL COMMENT '三级推广人ID',
|
||
|
||
`referral_code` varchar(20) NOT NULL COMMENT '使用的推广码',
|
||
`referral_channel` varchar(50) DEFAULT NULL COMMENT '推广渠道',
|
||
`bind_time` datetime NOT NULL COMMENT '绑定时间',
|
||
`bind_ip` varchar(50) DEFAULT NULL COMMENT '绑定IP',
|
||
|
||
`status` tinyint DEFAULT 1 COMMENT '状态 1生效 2失效',
|
||
`is_first_order` tinyint DEFAULT 0 COMMENT '是否已完成首单 0否 1是',
|
||
`first_order_time` datetime DEFAULT NULL COMMENT '首单时间',
|
||
`first_order_amount` decimal(10,2) DEFAULT 0.00 COMMENT '首单金额',
|
||
|
||
`total_orders` int DEFAULT 0 COMMENT '累计订单数',
|
||
`total_amount` decimal(12,2) DEFAULT 0.00 COMMENT '累计消费金额',
|
||
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `idx_customer_id` (`customer_id`),
|
||
KEY `idx_referrer_id` (`referrer_id`),
|
||
KEY `idx_referral_code` (`referral_code`),
|
||
KEY `idx_deleted_at` (`deleted_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户推广关系表';
|
||
|
||
-- 2. 用户推广收益记录表
|
||
DROP TABLE IF EXISTS `spa_customer_referral_income`;
|
||
CREATE TABLE `spa_customer_referral_income` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`created_at` datetime(3) DEFAULT NULL,
|
||
`updated_at` datetime(3) DEFAULT NULL,
|
||
`deleted_at` datetime(3) DEFAULT NULL,
|
||
|
||
`referrer_id` bigint unsigned NOT NULL COMMENT '推广人ID',
|
||
`customer_id` bigint unsigned NOT NULL COMMENT '被推广用户ID',
|
||
`order_id` bigint unsigned NOT NULL COMMENT '订单ID',
|
||
|
||
`referral_level` tinyint NOT NULL COMMENT '推广层级 1一级 2二级 3三级',
|
||
`order_amount` decimal(10,2) NOT NULL COMMENT '订单金额',
|
||
`commission_rate` decimal(5,2) NOT NULL COMMENT '分佣比例%',
|
||
`commission_amount` decimal(10,2) NOT NULL COMMENT '分佣金额',
|
||
|
||
`is_first_order` tinyint DEFAULT 0 COMMENT '是否首单 0否 1是',
|
||
`first_order_bonus` decimal(10,2) DEFAULT 0.00 COMMENT '首单额外奖励',
|
||
|
||
`income_date` datetime NOT NULL COMMENT '收益时间',
|
||
`settlement_status` tinyint DEFAULT 1 COMMENT '结算状态 1待结算 2已结算 3已提现',
|
||
`settlement_time` datetime DEFAULT NULL COMMENT '结算时间',
|
||
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_referrer_id` (`referrer_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_deleted_at` (`deleted_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户推广收益记录表';
|
||
|
||
-- 3. 用户推广统计表
|
||
DROP TABLE IF EXISTS `spa_customer_referral_stats`;
|
||
CREATE TABLE `spa_customer_referral_stats` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`created_at` datetime(3) DEFAULT NULL,
|
||
`updated_at` datetime(3) DEFAULT NULL,
|
||
|
||
`customer_id` bigint unsigned NOT NULL COMMENT '用户ID',
|
||
`stat_date` date NOT NULL COMMENT '统计日期',
|
||
|
||
`level_1_count` int DEFAULT 0 COMMENT '一级推广人数',
|
||
`level_2_count` int DEFAULT 0 COMMENT '二级推广人数',
|
||
`level_3_count` int DEFAULT 0 COMMENT '三级推广人数',
|
||
`total_referral_count` int DEFAULT 0 COMMENT '总推广人数',
|
||
|
||
`level_1_orders` int DEFAULT 0 COMMENT '一级订单数',
|
||
`level_2_orders` int DEFAULT 0 COMMENT '二级订单数',
|
||
`level_3_orders` int DEFAULT 0 COMMENT '三级订单数',
|
||
`total_orders` int DEFAULT 0 COMMENT '总订单数',
|
||
|
||
`level_1_amount` decimal(12,2) DEFAULT 0.00 COMMENT '一级订单金额',
|
||
`level_2_amount` decimal(12,2) DEFAULT 0.00 COMMENT '二级订单金额',
|
||
`level_3_amount` decimal(12,2) DEFAULT 0.00 COMMENT '三级订单金额',
|
||
`total_amount` decimal(12,2) DEFAULT 0.00 COMMENT '总订单金额',
|
||
|
||
`level_1_commission` decimal(12,2) DEFAULT 0.00 COMMENT '一级分佣',
|
||
`level_2_commission` decimal(12,2) DEFAULT 0.00 COMMENT '二级分佣',
|
||
`level_3_commission` decimal(12,2) DEFAULT 0.00 COMMENT '三级分佣',
|
||
`total_commission` decimal(12,2) DEFAULT 0.00 COMMENT '总分佣',
|
||
|
||
`active_referrals` int DEFAULT 0 COMMENT '活跃推广用户数',
|
||
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `idx_customer_date` (`customer_id`, `stat_date`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户推广统计表';
|
||
|
||
-- 4. 推广奖励规则表
|
||
DROP TABLE IF EXISTS `spa_referral_reward_rule`;
|
||
CREATE TABLE `spa_referral_reward_rule` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`created_at` datetime(3) DEFAULT NULL,
|
||
`updated_at` datetime(3) DEFAULT NULL,
|
||
`deleted_at` datetime(3) DEFAULT NULL,
|
||
|
||
`rule_name` varchar(100) NOT NULL COMMENT '规则名称',
|
||
`rule_type` tinyint NOT NULL COMMENT '规则类型 1首单奖励 2达标奖励 3排行奖励',
|
||
|
||
`trigger_condition` json DEFAULT NULL COMMENT '触发条件JSON',
|
||
`reward_type` tinyint NOT NULL COMMENT '奖励类型 1现金 2积分 3优惠券',
|
||
`reward_amount` decimal(10,2) DEFAULT 0.00 COMMENT '奖励金额',
|
||
|
||
`start_time` datetime DEFAULT NULL COMMENT '开始时间',
|
||
`end_time` datetime DEFAULT NULL COMMENT '结束时间',
|
||
|
||
`status` tinyint DEFAULT 1 COMMENT '状态 1启用 2停用',
|
||
`priority` int DEFAULT 0 COMMENT '优先级',
|
||
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_deleted_at` (`deleted_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='推广奖励规则表';
|
||
|
||
-- 5. 推广奖励发放记录表
|
||
DROP TABLE IF EXISTS `spa_referral_reward_record`;
|
||
CREATE TABLE `spa_referral_reward_record` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`created_at` datetime(3) DEFAULT NULL,
|
||
`updated_at` datetime(3) DEFAULT NULL,
|
||
|
||
`customer_id` bigint unsigned NOT NULL COMMENT '用户ID',
|
||
`rule_id` bigint unsigned NOT NULL COMMENT '规则ID',
|
||
`rule_name` varchar(100) NOT NULL COMMENT '规则名称',
|
||
|
||
`reward_type` tinyint NOT NULL COMMENT '奖励类型 1现金 2积分 3优惠券',
|
||
`reward_amount` decimal(10,2) DEFAULT 0.00 COMMENT '奖励金额',
|
||
|
||
`trigger_data` json DEFAULT NULL COMMENT '触发数据JSON',
|
||
`reward_time` datetime NOT NULL COMMENT '奖励时间',
|
||
|
||
`status` tinyint DEFAULT 1 COMMENT '状态 1待发放 2已发放 3已失效',
|
||
`grant_time` datetime DEFAULT NULL COMMENT '发放时间',
|
||
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_customer_id` (`customer_id`),
|
||
KEY `idx_rule_id` (`rule_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='推广奖励发放记录表';
|
||
|
||
-- ============================================
|
||
-- 初始化推广奖励规则
|
||
-- ============================================
|
||
|
||
INSERT INTO `spa_referral_reward_rule` (`rule_name`, `rule_type`, `trigger_condition`, `reward_type`, `reward_amount`, `status`, `priority`) VALUES
|
||
('新用户首单奖励', 1, '{"type":"first_order","min_amount":100}', 1, 5.00, 1, 100),
|
||
('月度推广10人奖励', 2, '{"type":"monthly_referral","count":10}', 1, 200.00, 1, 90),
|
||
('月度推广50人奖励', 2, '{"type":"monthly_referral","count":50}', 1, 1000.00, 1, 80),
|
||
('月度推广100人奖励', 2, '{"type":"monthly_referral","count":100}', 1, 3000.00, 1, 70);
|
||
|
||
SET FOREIGN_KEY_CHECKS = 1;
|