133 lines
5.8 KiB
SQL
133 lines
5.8 KiB
SQL
-- ============================================
|
|
-- SPA管理系统 - 评价客服系统SQL
|
|
-- ============================================
|
|
|
|
SET NAMES utf8mb4;
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
|
|
-- 1. 订单评价表
|
|
DROP TABLE IF EXISTS `spa_order_review`;
|
|
CREATE TABLE `spa_order_review` (
|
|
`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',
|
|
`order_item_id` bigint unsigned NOT NULL COMMENT '订单项ID',
|
|
`customer_id` bigint unsigned NOT NULL COMMENT '客户ID',
|
|
`technician_id` bigint unsigned NOT NULL COMMENT '技师ID',
|
|
`service_rating` tinyint NOT NULL COMMENT '服务评分 1-5星',
|
|
`environment_rating` tinyint DEFAULT NULL COMMENT '环境评分 1-5星',
|
|
`attitude_rating` tinyint DEFAULT NULL COMMENT '态度评分 1-5星',
|
|
`overall_rating` decimal(3,2) DEFAULT NULL COMMENT '综合评分',
|
|
`content` text COMMENT '评价内容',
|
|
`images` json DEFAULT NULL COMMENT '评价图片JSON',
|
|
`is_anonymous` tinyint DEFAULT 0 COMMENT '是否匿名 0否 1是',
|
|
`status` tinyint DEFAULT 1 COMMENT '状态 1正常 2隐藏',
|
|
`reply_content` text COMMENT '回复内容',
|
|
`reply_time` datetime DEFAULT NULL COMMENT '回复时间',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_order_id` (`order_id`),
|
|
KEY `idx_customer_id` (`customer_id`),
|
|
KEY `idx_technician_id` (`technician_id`),
|
|
KEY `idx_deleted_at` (`deleted_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单评价表';
|
|
|
|
-- 2. 评价标签表
|
|
DROP TABLE IF EXISTS `spa_review_tag`;
|
|
CREATE TABLE `spa_review_tag` (
|
|
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
`created_at` datetime(3) DEFAULT NULL,
|
|
`updated_at` datetime(3) DEFAULT NULL,
|
|
`tag_name` varchar(50) NOT NULL COMMENT '标签名称',
|
|
`tag_type` tinyint NOT NULL COMMENT '标签类型 1好评 2中评 3差评',
|
|
`sort` int DEFAULT 0 COMMENT '排序',
|
|
`status` tinyint DEFAULT 1 COMMENT '状态 1启用 2停用',
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='评价标签表';
|
|
|
|
-- 3. 评价点赞表
|
|
DROP TABLE IF EXISTS `spa_review_like`;
|
|
CREATE TABLE `spa_review_like` (
|
|
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
`created_at` datetime(3) DEFAULT NULL,
|
|
`review_id` bigint unsigned NOT NULL COMMENT '评价ID',
|
|
`customer_id` bigint unsigned NOT NULL COMMENT '客户ID',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `idx_review_customer` (`review_id`, `customer_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='评价点赞表';
|
|
|
|
-- 4. 客服信息表
|
|
DROP TABLE IF EXISTS `spa_customer_service`;
|
|
CREATE TABLE `spa_customer_service` (
|
|
`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,
|
|
`user_id` bigint unsigned DEFAULT NULL COMMENT '关联用户ID',
|
|
`service_no` varchar(50) NOT NULL COMMENT '客服工号',
|
|
`name` varchar(100) NOT NULL COMMENT '客服姓名',
|
|
`avatar` varchar(500) DEFAULT NULL COMMENT '头像',
|
|
`phone` varchar(20) DEFAULT NULL COMMENT '电话',
|
|
`max_sessions` int DEFAULT 10 COMMENT '最大接待数',
|
|
`current_sessions` int DEFAULT 0 COMMENT '当前接待数',
|
|
`status` tinyint DEFAULT 1 COMMENT '状态 1在线 2忙碌 3离线',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `idx_service_no` (`service_no`),
|
|
KEY `idx_deleted_at` (`deleted_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客服信息表';
|
|
|
|
-- 5. 客服会话表
|
|
DROP TABLE IF EXISTS `spa_service_session`;
|
|
CREATE TABLE `spa_service_session` (
|
|
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
`created_at` datetime(3) DEFAULT NULL,
|
|
`updated_at` datetime(3) DEFAULT NULL,
|
|
`session_no` varchar(50) NOT NULL COMMENT '会话编号',
|
|
`customer_id` bigint unsigned NOT NULL COMMENT '客户ID',
|
|
`service_id` bigint unsigned DEFAULT NULL COMMENT '客服ID',
|
|
`start_time` datetime NOT NULL COMMENT '开始时间',
|
|
`end_time` datetime DEFAULT NULL COMMENT '结束时间',
|
|
`status` tinyint DEFAULT 1 COMMENT '状态 1进行中 2已结束',
|
|
`satisfaction` tinyint DEFAULT NULL COMMENT '满意度 1-5星',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `idx_session_no` (`session_no`),
|
|
KEY `idx_customer_id` (`customer_id`),
|
|
KEY `idx_service_id` (`service_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客服会话表';
|
|
|
|
-- 6. 客服消息表
|
|
DROP TABLE IF EXISTS `spa_service_message`;
|
|
CREATE TABLE `spa_service_message` (
|
|
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
`created_at` datetime(3) DEFAULT NULL,
|
|
`session_id` bigint unsigned NOT NULL COMMENT '会话ID',
|
|
`sender_type` tinyint NOT NULL COMMENT '发送者类型 1客户 2客服',
|
|
`sender_id` bigint unsigned NOT NULL COMMENT '发送者ID',
|
|
`message_type` tinyint NOT NULL COMMENT '消息类型 1文本 2图片 3语音 4视频',
|
|
`content` text COMMENT '消息内容',
|
|
`media_url` varchar(500) DEFAULT NULL COMMENT '媒体URL',
|
|
`is_read` tinyint DEFAULT 0 COMMENT '是否已读 0否 1是',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_session_id` (`session_id`),
|
|
KEY `idx_created_at` (`created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客服消息表';
|
|
|
|
-- 7. 快捷回复表
|
|
DROP TABLE IF EXISTS `spa_quick_reply`;
|
|
CREATE TABLE `spa_quick_reply` (
|
|
`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` varchar(50) DEFAULT NULL COMMENT '分类',
|
|
`title` varchar(100) NOT NULL COMMENT '标题',
|
|
`content` text NOT NULL COMMENT '内容',
|
|
`sort` int DEFAULT 0 COMMENT '排序',
|
|
`status` tinyint DEFAULT 1 COMMENT '状态 1启用 2停用',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_deleted_at` (`deleted_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='快捷回复表';
|
|
|
|
SET FOREIGN_KEY_CHECKS = 1;
|