CREATE TABLE IF NOT EXISTS `t_distributor` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `code` varchar(20) NOT NULL COMMENT '分销编码，格式DS+6位数字',
  `name` varchar(50) NOT NULL COMMENT '姓名',
  `phone` varchar(20) NOT NULL COMMENT '手机号',
  `status` tinyint NOT NULL DEFAULT 0 COMMENT '状态：0-待激活 1-已激活 2-已停用',
  `qrcode_content` varchar(500) DEFAULT NULL COMMENT '地推二维码文本',
  `total_referrals` int NOT NULL DEFAULT 0 COMMENT '总推荐人数',
  `valid_referrals` int NOT NULL DEFAULT 0 COMMENT '有效推荐人数',
  `commission_balance` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '佣金可提现余额',
  `frozen_amount` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '冻结金额（提现审核中）',
  `total_commission` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '累计佣金总额',
  `total_withdrawn` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '累计已提现金额',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `creator_id` bigint DEFAULT NULL COMMENT '创建人ID',
  `creator_name` varchar(50) DEFAULT NULL COMMENT '创建人姓名',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` tinyint NOT NULL DEFAULT 0 COMMENT '删除标记：0-正常 1-已删除',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_code` (`code`),
  UNIQUE KEY `uk_phone` (`phone`),
  KEY `idx_status` (`status`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='分销员表';

CREATE TABLE IF NOT EXISTS `t_distributor_referral` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `distributor_id` bigint NOT NULL COMMENT '分销员ID',
  `user_id` bigint NOT NULL COMMENT '被推荐用户ID',
  `status` tinyint NOT NULL DEFAULT 0 COMMENT '状态：0-待生效 1-已生效 2-已失效',
  `first_order_id` bigint DEFAULT NULL COMMENT '首单ID',
  `first_order_amount` decimal(10,2) DEFAULT NULL COMMENT '首单金额',
  `first_order_time` datetime DEFAULT NULL COMMENT '首单时间',
  `effective_time` datetime DEFAULT NULL COMMENT '生效时间',
  `expire_time` datetime DEFAULT NULL COMMENT '过期时间（根据有效时间窗口计算）',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_distributor_user` (`distributor_id`, `user_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='分销推荐关系表';

CREATE TABLE IF NOT EXISTS `t_distributor_commission` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `distributor_id` bigint NOT NULL COMMENT '分销员ID',
  `referral_id` bigint DEFAULT NULL COMMENT '关联推荐记录ID',
  `user_id` bigint NOT NULL COMMENT '被推荐用户ID',
  `commission_type` tinyint NOT NULL COMMENT '佣金类型：0-拉新奖励（固定金额） 1-消费提成（首单比例）',
  `amount` decimal(10,2) NOT NULL COMMENT '佣金金额',
  `order_amount` decimal(10,2) DEFAULT NULL COMMENT '关联订单金额（比例佣金时使用）',
  `rate` decimal(5,2) DEFAULT NULL COMMENT '佣金比例（百分比，如5.00表示5%）',
  `status` tinyint NOT NULL DEFAULT 0 COMMENT '状态：0-待结算 1-已结算 2-已冻结',
  `settle_time` datetime DEFAULT NULL COMMENT '结算时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_distributor_id` (`distributor_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_commission_type` (`commission_type`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='佣金记录表';

CREATE TABLE IF NOT EXISTS `t_distributor_withdrawal` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `distributor_id` bigint NOT NULL COMMENT '分销员ID',
  `amount` decimal(10,2) NOT NULL COMMENT '提现金额',
  `status` tinyint NOT NULL DEFAULT 0 COMMENT '状态：0-待审核 1-已通过 2-已拒绝 3-已到账',
  `reviewer_id` bigint DEFAULT NULL COMMENT '审核人ID',
  `reviewer_name` varchar(50) DEFAULT NULL COMMENT '审核人姓名',
  `review_time` datetime DEFAULT NULL COMMENT '审核时间',
  `review_remark` varchar(500) DEFAULT NULL COMMENT '审核备注/拒绝原因',
  `transfer_time` datetime DEFAULT NULL COMMENT '到账时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_distributor_id` (`distributor_id`),
  KEY `idx_status` (`status`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='提现申请表';

CREATE TABLE IF NOT EXISTS `t_distributor_monthly_report` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `distributor_id` bigint NOT NULL COMMENT '分销员ID',
  `year_month` varchar(7) NOT NULL COMMENT '年月，格式yyyy-MM',
  `total_referrals` int NOT NULL DEFAULT 0 COMMENT '总推荐人数',
  `valid_referrals` int NOT NULL DEFAULT 0 COMMENT '有效推荐人数',
  `pending_referrals` int NOT NULL DEFAULT 0 COMMENT '待生效人数',
  `expired_referrals` int NOT NULL DEFAULT 0 COMMENT '已失效人数',
  `total_commission` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '佣金总额',
  `settled_commission` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '已结算佣金',
  `pending_commission` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '待结算佣金',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_distributor_month` (`distributor_id`, `year_month`),
  KEY `idx_year_month` (`year_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='月度统计报表表';

CREATE TABLE IF NOT EXISTS `t_distributor_config` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `config_key` varchar(100) NOT NULL COMMENT '配置键',
  `config_value` varchar(500) NOT NULL COMMENT '配置值',
  `config_desc` varchar(200) DEFAULT NULL COMMENT '配置描述',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_config_key` (`config_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='分销配置表';

INSERT INTO `t_distributor_config` (`config_key`, `config_value`, `config_desc`) VALUES
('fixed_commission_amount', '20.00', '固定佣金金额（元/人）'),
('first_order_commission_rate', '5.00', '首单佣金比例（百分比）'),
('valid_user_rule', 'FIRST_ORDER', '有效用户判定规则：FIRST_ORDER-首单完成 MIN_AMOUNT-最低消费金额 BOTH-首单且最低金额'),
('min_valid_order_amount', '0.00', '有效用户最低消费金额（元），0表示不限'),
('valid_time_window_days', '0', '有效时间窗口（天），0表示不限'),
('min_withdrawal_amount', '100.00', '最低提现金额（元）');
