-- ============================================
-- SPA管理系统 - 推广分佣系统SQL
-- ============================================
-- 版本: v1.0
-- 创建时间: 2025-12-30
-- 数据库: MySQL 8.0+
-- 字符集: utf8mb4
-- ============================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================
-- 用户推广分佣相关表
-- ============================================

-- 1. 用户推广收入记录表
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,

  `customer_id` bigint unsigned NOT NULL COMMENT '推广人ID',
  `referred_customer_id` bigint unsigned NOT NULL COMMENT '被推广用户ID',
  `order_id` bigint unsigned NOT NULL COMMENT '订单ID',
  `income_date` datetime NOT NULL COMMENT '收入时间',

  `order_amount` decimal(10,2) NOT NULL COMMENT '订单金额',
  `referral_level` tinyint NOT NULL COMMENT '推广层级 1一级 2二级 3三级',
  `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 '首单额外奖励',

  `settlement_status` tinyint DEFAULT 1 COMMENT '结算状态 1待结算 2已结算 3已提现',
  `settlement_month` varchar(7) DEFAULT NULL COMMENT '结算月份 YYYY-MM',

  PRIMARY KEY (`id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_order_id` (`order_id`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户推广收入记录表';

-- 2. 推广团队达标奖励表
DROP TABLE IF EXISTS `spa_referral_team_bonus`;
CREATE TABLE `spa_referral_team_bonus` (
  `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,

  `customer_id` bigint unsigned NOT NULL COMMENT '推广人ID',
  `bonus_month` varchar(7) NOT NULL COMMENT '奖励月份 YYYY-MM',
  
  `new_members_count` int NOT NULL COMMENT '新增团队人数',
  `bonus_type` tinyint NOT NULL COMMENT '奖励类型 1新增10人 2新增50人 3新增100人',
  `bonus_amount` decimal(10,2) NOT NULL COMMENT '奖励金额',
  
  `status` tinyint DEFAULT 1 COMMENT '状态 1待发放 2已发放',
  `grant_time` datetime DEFAULT NULL COMMENT '发放时间',

  PRIMARY KEY (`id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='推广团队达标奖励表';

-- 3. 推广统计表（用于快速查询）
DROP TABLE IF EXISTS `spa_referral_statistics`;
CREATE TABLE `spa_referral_statistics` (
  `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_team_count` int DEFAULT 0 COMMENT '团队总人数',

  `today_income` decimal(10,2) DEFAULT 0.00 COMMENT '今日收益',
  `month_income` decimal(10,2) DEFAULT 0.00 COMMENT '本月收益',
  `total_income` decimal(10,2) DEFAULT 0.00 COMMENT '累计收益',

  `today_orders` int DEFAULT 0 COMMENT '今日团队订单数',
  `month_orders` int DEFAULT 0 COMMENT '本月团队订单数',
  `total_orders` int DEFAULT 0 COMMENT '累计团队订单数',

  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_customer_date` (`customer_id`, `stat_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='推广统计表';

SET FOREIGN_KEY_CHECKS = 1;