467 lines
20 KiB
SQL
467 lines
20 KiB
SQL
-- ============================================
|
||
-- SPA管理系统 - 核心数据库表
|
||
-- ============================================
|
||
-- 版本: v1.0
|
||
-- 创建时间: 2025-12-30
|
||
-- 数据库: MySQL 8.0+
|
||
-- 字符集: utf8mb4
|
||
-- ============================================
|
||
|
||
SET NAMES utf8mb4;
|
||
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
||
-- ============================================
|
||
-- 第一部分：用户相关表
|
||
-- ============================================
|
||
|
||
-- 1. 客户信息表
|
||
DROP TABLE IF EXISTS `spa_customer`;
|
||
CREATE TABLE `spa_customer` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
||
`created_at` datetime(3) DEFAULT NULL COMMENT '创建时间',
|
||
`updated_at` datetime(3) DEFAULT NULL COMMENT '更新时间',
|
||
`deleted_at` datetime(3) DEFAULT NULL COMMENT '删除时间',
|
||
|
||
`user_id` bigint unsigned DEFAULT NULL COMMENT '关联系统用户ID',
|
||
`customer_no` varchar(50) DEFAULT NULL COMMENT '客户编号',
|
||
`name` varchar(100) DEFAULT NULL COMMENT '客户姓名',
|
||
`phone` varchar(20) NOT NULL COMMENT '手机号',
|
||
`gender` tinyint DEFAULT 0 COMMENT '性别 0未知 1男 2女',
|
||
`avatar` varchar(500) DEFAULT NULL COMMENT '头像',
|
||
`birthday` date DEFAULT NULL COMMENT '生日',
|
||
|
||
`referrer_id` bigint unsigned DEFAULT 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) DEFAULT NULL COMMENT '我的推广码',
|
||
`referral_count` int DEFAULT 0 COMMENT '累计推广人数',
|
||
|
||
`member_level` tinyint DEFAULT 0 COMMENT '会员等级 0普通 1银卡 2金卡 3钻石',
|
||
`balance` decimal(10,2) DEFAULT 0.00 COMMENT '账户余额',
|
||
`points` int DEFAULT 0 COMMENT '积分',
|
||
|
||
`total_orders` int DEFAULT 0 COMMENT '累计订单数',
|
||
`total_amount` decimal(12,2) DEFAULT 0.00 COMMENT '累计消费金额',
|
||
`total_referral_income` decimal(12,2) DEFAULT 0.00 COMMENT '累计推广收入',
|
||
|
||
`tags` json DEFAULT NULL COMMENT '客户标签JSON',
|
||
`remark` text COMMENT '备注',
|
||
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `idx_phone` (`phone`),
|
||
UNIQUE KEY `idx_referral_code` (`referral_code`),
|
||
KEY `idx_referrer_id` (`referrer_id`),
|
||
KEY `idx_deleted_at` (`deleted_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户信息表';
|
||
|
||
-- ============================================
|
||
-- 第二部分：代理相关表
|
||
-- ============================================
|
||
|
||
-- 2. 代理信息表
|
||
DROP TABLE IF EXISTS `spa_agent`;
|
||
CREATE TABLE `spa_agent` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
||
`created_at` datetime(3) DEFAULT NULL COMMENT '创建时间',
|
||
`updated_at` datetime(3) DEFAULT NULL COMMENT '更新时间',
|
||
`deleted_at` datetime(3) DEFAULT NULL COMMENT '删除时间',
|
||
|
||
`user_id` bigint unsigned DEFAULT NULL COMMENT '关联系统用户ID',
|
||
`agent_no` varchar(50) NOT NULL COMMENT '代理编号',
|
||
`name` varchar(100) NOT NULL COMMENT '代理姓名',
|
||
`phone` varchar(20) NOT NULL COMMENT '联系电话',
|
||
`avatar` varchar(500) DEFAULT NULL COMMENT '头像URL',
|
||
|
||
`level` tinyint DEFAULT 1 COMMENT '代理层级 1一级 2二级 3三级',
|
||
`parent_agent_id` bigint unsigned DEFAULT NULL COMMENT '上级代理ID',
|
||
`agent_path` varchar(500) DEFAULT NULL COMMENT '代理路径 如: 1/5/12',
|
||
|
||
`status` tinyint DEFAULT 1 COMMENT '状态 1正常 2冻结 3注销',
|
||
`join_date` date DEFAULT NULL COMMENT '加入日期',
|
||
|
||
`total_technicians` int DEFAULT 0 COMMENT '累计推广技师数',
|
||
`active_technicians` int DEFAULT 0 COMMENT '活跃技师数',
|
||
`total_commission` decimal(12,2) DEFAULT 0.00 COMMENT '累计分佣金额',
|
||
`balance` decimal(12,2) DEFAULT 0.00 COMMENT '账户余额',
|
||
|
||
`bank_name` varchar(100) DEFAULT NULL COMMENT '开户银行',
|
||
`bank_account` varchar(50) DEFAULT NULL COMMENT '银行账号',
|
||
`remark` text COMMENT '备注',
|
||
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `idx_agent_no` (`agent_no`),
|
||
UNIQUE KEY `idx_phone` (`phone`),
|
||
KEY `idx_parent_agent` (`parent_agent_id`),
|
||
KEY `idx_deleted_at` (`deleted_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='代理信息表';
|
||
|
||
-- 3. 技师代理绑定表
|
||
DROP TABLE IF EXISTS `spa_technician_agent_bind`;
|
||
CREATE TABLE `spa_technician_agent_bind` (
|
||
`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,
|
||
|
||
`technician_id` bigint unsigned NOT NULL COMMENT '技师ID',
|
||
`agent_id` bigint unsigned NOT NULL COMMENT '直属代理ID',
|
||
|
||
`agent_level_1` bigint unsigned DEFAULT NULL COMMENT '一级代理ID',
|
||
`agent_level_2` bigint unsigned DEFAULT NULL COMMENT '二级代理ID',
|
||
`agent_level_3` bigint unsigned DEFAULT NULL COMMENT '三级代理ID',
|
||
|
||
`bind_date` datetime NOT NULL COMMENT '绑定时间',
|
||
`status` tinyint DEFAULT 1 COMMENT '状态 1生效中 2已解绑',
|
||
|
||
`total_orders` int DEFAULT 0 COMMENT '累计订单数',
|
||
`total_amount` decimal(12,2) DEFAULT 0.00 COMMENT '累计订单金额',
|
||
`total_commission` decimal(12,2) DEFAULT 0.00 COMMENT '累计为代理产生的分佣',
|
||
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_technician_id` (`technician_id`),
|
||
KEY `idx_agent_id` (`agent_id`),
|
||
KEY `idx_deleted_at` (`deleted_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='技师代理绑定关系表';
|
||
|
||
-- 4. 代理收入记录表
|
||
DROP TABLE IF EXISTS `spa_agent_income`;
|
||
CREATE TABLE `spa_agent_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,
|
||
|
||
`agent_id` bigint unsigned NOT NULL COMMENT '代理ID',
|
||
`order_id` bigint unsigned NOT NULL COMMENT '订单ID',
|
||
`technician_id` bigint unsigned NOT NULL COMMENT '技师ID',
|
||
`income_date` datetime NOT NULL COMMENT '收入时间',
|
||
|
||
`order_amount` decimal(10,2) NOT NULL COMMENT '订单金额',
|
||
`agent_level` tinyint NOT NULL COMMENT '代理层级 1一级 2二级 3三级',
|
||
`commission_rate` decimal(5,2) NOT NULL COMMENT '分佣比例%',
|
||
`commission_amount` decimal(10,2) NOT NULL COMMENT '分佣金额',
|
||
|
||
`settlement_status` tinyint DEFAULT 1 COMMENT '结算状态 1待结算 2已结算 3已提现',
|
||
`settlement_month` varchar(7) DEFAULT NULL COMMENT '结算月份 YYYY-MM',
|
||
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_agent_id` (`agent_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_deleted_at` (`deleted_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='代理收入记录表';
|
||
|
||
-- ============================================
|
||
-- 第三部分：技师相关表
|
||
-- ============================================
|
||
|
||
-- 5. 技师信息表
|
||
DROP TABLE IF EXISTS `spa_technician`;
|
||
CREATE TABLE `spa_technician` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
||
`created_at` datetime(3) DEFAULT NULL COMMENT '创建时间',
|
||
`updated_at` datetime(3) DEFAULT NULL COMMENT '更新时间',
|
||
`deleted_at` datetime(3) DEFAULT NULL COMMENT '删除时间',
|
||
|
||
`user_id` bigint unsigned DEFAULT NULL COMMENT '关联系统用户ID',
|
||
`technician_no` varchar(50) NOT NULL COMMENT '技师工号',
|
||
`name` varchar(100) NOT NULL COMMENT '技师姓名',
|
||
`gender` tinyint DEFAULT 0 COMMENT '性别 0未知 1男 2女',
|
||
`phone` varchar(20) DEFAULT NULL COMMENT '联系电话',
|
||
`avatar` varchar(500) DEFAULT NULL COMMENT '头像URL',
|
||
|
||
`level` tinyint DEFAULT 1 COMMENT '技师等级 1初级 2中级 3高级 4首席',
|
||
`specialties` json DEFAULT NULL COMMENT '擅长项目JSON数组',
|
||
`service_years` int DEFAULT 0 COMMENT '服务年限（月）',
|
||
|
||
`status` tinyint DEFAULT 1 COMMENT '在职状态 1在职 2休假 3离职',
|
||
`entry_date` date DEFAULT NULL COMMENT '入职日期',
|
||
|
||
`current_agent_id` bigint unsigned DEFAULT NULL COMMENT '当前代理ID',
|
||
`bind_agent_time` datetime DEFAULT NULL COMMENT '绑定代理时间',
|
||
|
||
`commission_rate` decimal(5,2) DEFAULT 0.00 COMMENT '默认提成比例%',
|
||
`base_salary` decimal(10,2) DEFAULT 0.00 COMMENT '基本工资',
|
||
|
||
`rating` decimal(3,2) DEFAULT 5.00 COMMENT '综合评分',
|
||
`total_orders` int DEFAULT 0 COMMENT '累计服务单数',
|
||
`total_income` decimal(12,2) DEFAULT 0.00 COMMENT '累计收入',
|
||
|
||
`remark` text COMMENT '备注',
|
||
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `idx_technician_no` (`technician_no`),
|
||
KEY `idx_current_agent` (`current_agent_id`),
|
||
KEY `idx_deleted_at` (`deleted_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='技师信息表';
|
||
|
||
-- 6. 技师排班表
|
||
DROP TABLE IF EXISTS `spa_technician_schedule`;
|
||
CREATE TABLE `spa_technician_schedule` (
|
||
`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,
|
||
|
||
`technician_id` bigint unsigned NOT NULL COMMENT '技师ID',
|
||
`schedule_date` date NOT NULL COMMENT '排班日期',
|
||
`shift_type` tinyint NOT NULL COMMENT '班次类型 1早班 2中班 3晚班 4全天',
|
||
|
||
`start_time` time NOT NULL COMMENT '上班时间',
|
||
`end_time` time NOT NULL COMMENT '下班时间',
|
||
|
||
`status` tinyint DEFAULT 1 COMMENT '状态 1正常 2请假 3调班',
|
||
`is_available` tinyint DEFAULT 1 COMMENT '是否可预约 0否 1是',
|
||
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `idx_tech_date` (`technician_id`, `schedule_date`),
|
||
KEY `idx_deleted_at` (`deleted_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='技师排班表';
|
||
|
||
-- 7. 技师收入记录表
|
||
DROP TABLE IF EXISTS `spa_technician_income`;
|
||
CREATE TABLE `spa_technician_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,
|
||
|
||
`technician_id` bigint unsigned NOT NULL COMMENT '技师ID',
|
||
`order_id` bigint unsigned DEFAULT NULL COMMENT '订单ID',
|
||
`income_date` date NOT NULL COMMENT '收入日期',
|
||
|
||
`income_type` tinyint NOT NULL COMMENT '收入类型 1服务提成 2绩效奖金 3基本工资',
|
||
`amount` decimal(10,2) NOT NULL COMMENT '金额',
|
||
`commission_rate` decimal(5,2) DEFAULT NULL COMMENT '提成比例',
|
||
|
||
`settlement_status` tinyint DEFAULT 1 COMMENT '结算状态 1未结算 2已结算',
|
||
`settlement_month` varchar(7) DEFAULT NULL COMMENT '结算月份 YYYY-MM',
|
||
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_technician_id` (`technician_id`),
|
||
KEY `idx_deleted_at` (`deleted_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='技师收入记录表';
|
||
|
||
-- ============================================
|
||
-- 第四部分：服务项目相关表
|
||
-- ============================================
|
||
|
||
-- 8. 服务项目分类表
|
||
DROP TABLE IF EXISTS `spa_service_category`;
|
||
CREATE TABLE `spa_service_category` (
|
||
`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,
|
||
|
||
`parent_id` bigint unsigned DEFAULT 0 COMMENT '父级分类ID',
|
||
`name` varchar(100) NOT NULL COMMENT '分类名称',
|
||
`icon` varchar(500) DEFAULT NULL COMMENT '图标',
|
||
`sort` int DEFAULT 0 COMMENT '排序',
|
||
`status` tinyint DEFAULT 1 COMMENT '状态 1启用 2停用',
|
||
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_parent_id` (`parent_id`),
|
||
KEY `idx_deleted_at` (`deleted_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='服务项目分类表';
|
||
|
||
-- 9. 服务项目表
|
||
DROP TABLE IF EXISTS `spa_service_item`;
|
||
CREATE TABLE `spa_service_item` (
|
||
`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,
|
||
|
||
`category_id` bigint unsigned DEFAULT NULL COMMENT '服务分类ID',
|
||
`item_no` varchar(50) NOT NULL COMMENT '项目编号',
|
||
`name` varchar(100) NOT NULL COMMENT '项目名称',
|
||
`description` text COMMENT '项目描述',
|
||
`cover_image` varchar(500) DEFAULT NULL COMMENT '封面图',
|
||
|
||
`duration` int NOT NULL COMMENT '标准时长（分钟）',
|
||
`price` decimal(10,2) NOT NULL COMMENT '标准价格',
|
||
`vip_price` decimal(10,2) DEFAULT NULL COMMENT '会员价',
|
||
|
||
`required_level` tinyint DEFAULT 1 COMMENT '要求技师等级',
|
||
`default_commission_rate` decimal(5,2) DEFAULT 0.00 COMMENT '默认提成比例%',
|
||
|
||
`status` tinyint DEFAULT 1 COMMENT '状态 1上架 2下架',
|
||
`sort` int DEFAULT 0 COMMENT '排序',
|
||
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `idx_item_no` (`item_no`),
|
||
KEY `idx_category_id` (`category_id`),
|
||
KEY `idx_deleted_at` (`deleted_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='服务项目表';
|
||
|
||
-- ============================================
|
||
-- 第五部分：订单相关表
|
||
-- ============================================
|
||
|
||
-- 10. 订单主表
|
||
DROP TABLE IF EXISTS `spa_order`;
|
||
CREATE TABLE `spa_order` (
|
||
`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,
|
||
|
||
`order_no` varchar(50) NOT NULL COMMENT '订单号',
|
||
`customer_id` bigint unsigned NOT NULL COMMENT '客户ID',
|
||
|
||
`appointment_date` date NOT NULL COMMENT '预约日期',
|
||
`appointment_time` time NOT NULL COMMENT '预约时间',
|
||
|
||
`total_amount` decimal(10,2) NOT NULL COMMENT '订单总额',
|
||
`discount_amount` decimal(10,2) DEFAULT 0.00 COMMENT '优惠金额',
|
||
`pay_amount` decimal(10,2) NOT NULL COMMENT '实付金额',
|
||
|
||
`pay_status` tinyint DEFAULT 1 COMMENT '支付状态 1待支付 2已支付 3已退款',
|
||
`pay_type` tinyint DEFAULT NULL COMMENT '支付方式 1微信 2支付宝 3余额',
|
||
`pay_time` datetime DEFAULT NULL COMMENT '支付时间',
|
||
|
||
`status` tinyint DEFAULT 1 COMMENT '订单状态 1待确认 2已确认 3服务中 4已完成 5已取消',
|
||
`complete_time` datetime DEFAULT NULL COMMENT '完成时间',
|
||
|
||
`customer_name` varchar(100) DEFAULT NULL COMMENT '客户姓名',
|
||
`customer_phone` varchar(20) DEFAULT NULL COMMENT '客户电话',
|
||
`remark` text COMMENT '订单备注',
|
||
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `idx_order_no` (`order_no`),
|
||
KEY `idx_customer_id` (`customer_id`),
|
||
KEY `idx_deleted_at` (`deleted_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单主表';
|
||
|
||
-- 11. 订单项目表
|
||
DROP TABLE IF EXISTS `spa_order_item`;
|
||
CREATE TABLE `spa_order_item` (
|
||
`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,
|
||
|
||
`order_id` bigint unsigned NOT NULL COMMENT '订单ID',
|
||
`service_item_id` bigint unsigned NOT NULL COMMENT '服务项目ID',
|
||
`technician_id` bigint unsigned NOT NULL COMMENT '技师ID',
|
||
|
||
`service_name` varchar(100) NOT NULL COMMENT '服务名称',
|
||
`service_duration` int NOT NULL COMMENT '服务时长（分钟）',
|
||
`service_price` decimal(10,2) NOT NULL COMMENT '服务原价',
|
||
`actual_price` decimal(10,2) NOT NULL COMMENT '实际价格',
|
||
|
||
`appointment_time` datetime NOT NULL COMMENT '预约开始时间',
|
||
`start_time` datetime DEFAULT NULL COMMENT '实际开始时间',
|
||
`end_time` datetime DEFAULT NULL COMMENT '实际结束时间',
|
||
|
||
`status` tinyint DEFAULT 1 COMMENT '状态 1待确认 2已确认 3服务中 4已完成 5已取消',
|
||
|
||
`platform_fee` decimal(10,2) DEFAULT 0.00 COMMENT '平台服务费',
|
||
`technician_commission` decimal(10,2) DEFAULT 0.00 COMMENT '技师提成',
|
||
`agent_commission_total` decimal(10,2) DEFAULT 0.00 COMMENT '代理分佣总额',
|
||
|
||
`rating` tinyint DEFAULT NULL COMMENT '客户评分 1-5星',
|
||
`comment` text COMMENT '客户评价',
|
||
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_technician_id` (`technician_id`),
|
||
KEY `idx_deleted_at` (`deleted_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单项目明细表';
|
||
|
||
-- 12. 订单分佣记录表
|
||
DROP TABLE IF EXISTS `spa_order_commission`;
|
||
CREATE TABLE `spa_order_commission` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`created_at` datetime(3) DEFAULT NULL,
|
||
`updated_at` datetime(3) DEFAULT NULL,
|
||
|
||
`order_id` bigint unsigned NOT NULL COMMENT '订单ID',
|
||
`order_item_id` bigint unsigned NOT NULL COMMENT '订单项ID',
|
||
`order_amount` decimal(10,2) NOT NULL COMMENT '订单金额',
|
||
|
||
`technician_id` bigint unsigned NOT NULL COMMENT '技师ID',
|
||
`technician_commission_rate` decimal(5,2) DEFAULT 0.00 COMMENT '技师提成比例',
|
||
`technician_commission_amount` decimal(10,2) DEFAULT 0.00 COMMENT '技师提成金额',
|
||
|
||
`agent_level_1_id` bigint unsigned DEFAULT NULL COMMENT '一级代理ID',
|
||
`agent_level_1_rate` decimal(5,2) DEFAULT 0.00 COMMENT '一级代理分佣比例',
|
||
`agent_level_1_amount` decimal(10,2) DEFAULT 0.00 COMMENT '一级代理分佣金额',
|
||
|
||
`agent_level_2_id` bigint unsigned DEFAULT NULL COMMENT '二级代理ID',
|
||
`agent_level_2_rate` decimal(5,2) DEFAULT 0.00 COMMENT '二级代理分佣比例',
|
||
`agent_level_2_amount` decimal(10,2) DEFAULT 0.00 COMMENT '二级代理分佣金额',
|
||
|
||
`agent_level_3_id` bigint unsigned DEFAULT NULL COMMENT '三级代理ID',
|
||
`agent_level_3_rate` decimal(5,2) DEFAULT 0.00 COMMENT '三级代理分佣比例',
|
||
`agent_level_3_amount` decimal(10,2) DEFAULT 0.00 COMMENT '三级代理分佣金额',
|
||
|
||
`customer_referrer_1_id` bigint unsigned DEFAULT NULL COMMENT '一级推广人ID',
|
||
`customer_referrer_1_rate` decimal(5,2) DEFAULT 0.00 COMMENT '一级推广分佣比例',
|
||
`customer_referrer_1_amount` decimal(10,2) DEFAULT 0.00 COMMENT '一级推广分佣金额',
|
||
|
||
`customer_referrer_2_id` bigint unsigned DEFAULT NULL COMMENT '二级推广人ID',
|
||
`customer_referrer_2_rate` decimal(5,2) DEFAULT 0.00 COMMENT '二级推广分佣比例',
|
||
`customer_referrer_2_amount` decimal(10,2) DEFAULT 0.00 COMMENT '二级推广分佣金额',
|
||
|
||
`customer_referrer_3_id` bigint unsigned DEFAULT NULL COMMENT '三级推广人ID',
|
||
`customer_referrer_3_rate` decimal(5,2) DEFAULT 0.00 COMMENT '三级推广分佣比例',
|
||
`customer_referrer_3_amount` decimal(10,2) DEFAULT 0.00 COMMENT '三级推广分佣金额',
|
||
|
||
`platform_rate` decimal(5,2) DEFAULT 0.00 COMMENT '平台服务费比例',
|
||
`platform_amount` decimal(10,2) DEFAULT 0.00 COMMENT '平台服务费',
|
||
|
||
`calculate_time` datetime DEFAULT NULL COMMENT '计算时间',
|
||
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `idx_order_item` (`order_item_id`),
|
||
KEY `idx_order_id` (`order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单分佣记录表';
|
||
|
||
-- ============================================
|
||
-- 第六部分：分佣规则配置表
|
||
-- ============================================
|
||
|
||
-- 13. 分佣规则配置表
|
||
DROP TABLE IF EXISTS `spa_commission_rule`;
|
||
CREATE TABLE `spa_commission_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用户推广',
|
||
|
||
`technician_commission_rate` decimal(5,2) DEFAULT 0.00 COMMENT '技师提成比例%',
|
||
|
||
`agent_level_1_rate` decimal(5,2) DEFAULT 0.00 COMMENT '一级代理分佣比例%',
|
||
`agent_level_2_rate` decimal(5,2) DEFAULT 0.00 COMMENT '二级代理分佣比例%',
|
||
`agent_level_3_rate` decimal(5,2) DEFAULT 0.00 COMMENT '三级代理分佣比例%',
|
||
|
||
`customer_referrer_1_rate` decimal(5,2) DEFAULT 0.00 COMMENT '一级推广分佣比例%',
|
||
`customer_referrer_2_rate` decimal(5,2) DEFAULT 0.00 COMMENT '二级推广分佣比例%',
|
||
`customer_referrer_3_rate` decimal(5,2) DEFAULT 0.00 COMMENT '三级推广分佣比例%',
|
||
|
||
`platform_rate` decimal(5,2) DEFAULT 0.00 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='分佣规则配置表';
|
||
|
||
-- ============================================
|
||
-- 初始化默认分佣规则
|
||
-- ============================================
|
||
|
||
INSERT INTO `spa_commission_rule` (`rule_name`, `rule_type`, `technician_commission_rate`, `agent_level_1_rate`, `agent_level_2_rate`, `agent_level_3_rate`, `customer_referrer_1_rate`, `customer_referrer_2_rate`, `customer_referrer_3_rate`, `platform_rate`, `status`, `priority`) VALUES
|
||
('默认分佣规则', 1, 50.00, 10.00, 5.00, 2.00, 8.00, 3.00, 1.00, 10.00, 1, 100);
|
||
|
||
SET FOREIGN_KEY_CHECKS = 1;
|
||
|
||
-- ============================================
|
||
-- 数据库表创建完成
|
||
-- ============================================
|