-- ============================================
-- 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;