sj-miniprogram/online-service/backend-service/docs/数据库设计.sql

-- =====================================================
-- 审计之家 · 在线客服系统 · 数据库设计
-- 数据库: MySQL 8.0
-- 字符集: utf8mb4
-- 时间: 2026-03-17
-- =====================================================

CREATE DATABASE IF NOT EXISTS `sjzj_chat` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `sjzj_chat`;

-- =====================================================
-- 1. 坐席配置表
-- 对应前端: SeatConfig.vue
-- =====================================================
DROP TABLE IF EXISTS `cs_seat_config`;
CREATE TABLE `cs_seat_config` (
  `id`          BIGINT       NOT NULL AUTO_INCREMENT COMMENT '坐席ID',
  `seat_name`   VARCHAR(50)  NOT NULL COMMENT '坐席名称（如：客服小A）',
  `avatar`      VARCHAR(500) DEFAULT NULL COMMENT '坐席头像URL',
  `module`      VARCHAR(20)  NOT NULL COMMENT '负责模块: mini(小程序), merchant(商家), all(全部)',
  `status`      TINYINT(1)   NOT NULL DEFAULT 1 COMMENT '状态: 0=停用, 1=启用',
  `create_by`   VARCHAR(64)  DEFAULT '' COMMENT '创建者',
  `create_time` DATETIME     DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by`   VARCHAR(64)  DEFAULT '' COMMENT '更新者',
  `update_time` DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `del_flag`    CHAR(1)      DEFAULT '0' COMMENT '删除标志: 0=存在, 2=已删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='坐席配置表';

-- 坐席与客服人员关联表
DROP TABLE IF EXISTS `cs_seat_waiter`;
CREATE TABLE `cs_seat_waiter` (
  `id`          BIGINT NOT NULL AUTO_INCREMENT,
  `seat_id`     BIGINT NOT NULL COMMENT '坐席ID',
  `user_id`     BIGINT NOT NULL COMMENT '客服用户ID（关联若依sys_user）',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_seat_id` (`seat_id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='坐席-客服关联表';

-- 初始数据
INSERT INTO `cs_seat_config` (`seat_name`, `avatar`, `module`, `status`) VALUES
('客服小A', 'https://api.dicebear.com/7.x/avataaars/svg?seed=A', '小程序', 1),
('客服小B', 'https://api.dicebear.com/7.x/avataaars/svg?seed=B', '官网',   1),
('客服小C', 'https://api.dicebear.com/7.x/avataaars/svg?seed=C', '小程序', 0);


-- =====================================================
-- 2. 会话表（一个 User/商家 与客服建立一个会话）
-- =====================================================
DROP TABLE IF EXISTS `cs_session`;
CREATE TABLE `cs_session` (
  `id`              BIGINT        NOT NULL AUTO_INCREMENT COMMENT '会话ID',
  `session_no`      VARCHAR(32)   NOT NULL UNIQUE COMMENT '会话编号，唯一，用于WS频道路由',
  `session_type`    TINYINT(1)    NOT NULL COMMENT '会话类型: 1=小程序用户, 2=PC商家',
  `from_user_id`    BIGINT        NOT NULL COMMENT '发起用户ID（小程序用户或商家用户ID）',
  `from_user_name`  VARCHAR(100)  DEFAULT '' COMMENT '发起方昵称',
  `from_user_avatar`VARCHAR(500)  DEFAULT '' COMMENT '发起方头像',
  `seat_id`         BIGINT        DEFAULT NULL COMMENT '当前分配坐席ID',
  `waiter_id`       BIGINT        DEFAULT NULL COMMENT '当前接待客服ID',
  `status`          TINYINT(1)    NOT NULL DEFAULT 1 COMMENT '状态: 1=进行中, 2=已结束',
  `last_msg`        VARCHAR(500)  DEFAULT '' COMMENT '最后一条消息摘要（用于列表展示）',
  `last_msg_time`   DATETIME      DEFAULT NULL COMMENT '最后消息时间',
  `unread_count`    INT           NOT NULL DEFAULT 0 COMMENT '客服侧未读消息数',
  `create_time`     DATETIME      DEFAULT CURRENT_TIMESTAMP COMMENT '会话建立时间',
  `update_time`     DATETIME      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `end_time`        DATETIME      DEFAULT NULL COMMENT '会话结束时间',
  PRIMARY KEY (`id`),
  KEY `idx_from_user` (`from_user_id`),
  KEY `idx_waiter` (`waiter_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='客服会话表';


-- =====================================================
-- 3. 消息表（核心表，存储所有聊天消息）
-- =====================================================
DROP TABLE IF EXISTS `cs_message`;
CREATE TABLE `cs_message` (
  `id`           BIGINT        NOT NULL AUTO_INCREMENT COMMENT '消息ID',
  `session_id`   BIGINT        NOT NULL COMMENT '归属会话ID',
  `msg_no`       VARCHAR(32)   NOT NULL UNIQUE COMMENT '消息唯一编号（客户端生成，用于幂等）',
  `sender_type`  TINYINT(1)    NOT NULL COMMENT '发送方类型: 1=用户/商家, 2=客服, 3=系统',
  `sender_id`    BIGINT        NOT NULL COMMENT '发送者ID',
  `msg_type`     VARCHAR(20)   NOT NULL COMMENT '消息类型: text/image/file/job_card/order_card/emoji',
  `content`      TEXT          DEFAULT NULL COMMENT '文本消息内容',
  `file_url`     VARCHAR(500)  DEFAULT NULL COMMENT '图片/文件URL',
  `file_name`    VARCHAR(255)  DEFAULT NULL COMMENT '文件原始名称',
  `file_size`    BIGINT        DEFAULT NULL COMMENT '文件大小（字节）',
  `file_type`    VARCHAR(100)  DEFAULT NULL COMMENT '文件MIME类型',
  `payload`      JSON          DEFAULT NULL COMMENT '卡片类消息的结构化数据（job_card/order_card的JSON）',
  `status`       TINYINT(1)    NOT NULL DEFAULT 1 COMMENT '状态: 1=正常, 2=已撤回',
  `is_read`      TINYINT(1)    NOT NULL DEFAULT 0 COMMENT '是否已读: 0=未读, 1=已读',
  `send_time`    DATETIME      NOT NULL COMMENT '发送时间',
  `create_time`  DATETIME      DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_session` (`session_id`),
  KEY `idx_send_time` (`send_time`),
  KEY `idx_msg_type` (`msg_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='聊天消息表';


-- =====================================================
-- 4. 结算单（order_card）扩展表
-- 配合消息表中 msg_type='order_card' 的消息使用
-- 实现: 60秒倒计时自动失效
-- =====================================================
DROP TABLE IF EXISTS `cs_order_card`;
CREATE TABLE `cs_order_card` (
  `id`              BIGINT         NOT NULL AUTO_INCREMENT COMMENT '结算单ID',
  `msg_id`          BIGINT         NOT NULL COMMENT '关联消息ID',
  `session_id`      BIGINT         NOT NULL COMMENT '关联会话ID',
  `order_name`      VARCHAR(200)   NOT NULL COMMENT '项目名称',
  `order_price`     DECIMAL(10, 2) NOT NULL COMMENT '支付金额',
  `order_type`      VARCHAR(50)    DEFAULT NULL COMMENT '订单类型说明',
  `original_order_id` BIGINT       DEFAULT NULL COMMENT '关联平台真实订单ID',
  `status`          VARCHAR(20)    NOT NULL DEFAULT 'pending'
                    COMMENT '状态: pending=待支付, paid=已支付, cancelled=已取消, expired=已失效',
  `expire_time`     DATETIME       NOT NULL COMMENT '结算单过期时间（发送时间+60s）',
  `pay_time`        DATETIME       DEFAULT NULL COMMENT '实际支付时间',
  `create_time`     DATETIME       DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_msg_id` (`msg_id`),
  KEY `idx_session` (`session_id`),
  KEY `idx_status` (`status`),
  KEY `idx_expire` (`expire_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='结算单表（order_card）';


-- =====================================================
-- 5. 工单表（客服工单管理）
-- 对应前端: SeatConfig.vue 的工单子系统
-- =====================================================
DROP TABLE IF EXISTS `cs_ticket`;
CREATE TABLE `cs_ticket` (
  `id`            VARCHAR(32)   NOT NULL COMMENT '工单编号（如: 20991216194）',
  `session_id`    BIGINT        DEFAULT NULL COMMENT '关联会话ID（若来自在线沟通）',
  `user_id`       VARCHAR(50)   NOT NULL COMMENT '反馈用户ID',
  `user_name`     VARCHAR(100)  NOT NULL COMMENT '反馈用户昵称',
  `content`       TEXT          NOT NULL COMMENT '反馈内容',
  `source`        VARCHAR(20)   NOT NULL COMMENT '反馈渠道: 小程序/商家',
  `category`      VARCHAR(50)   NOT NULL COMMENT '问题分类',
  `status`        VARCHAR(20)   NOT NULL DEFAULT 'pending'
                  COMMENT '状态: pending=待处理, processing=处理中, completed=已完成, abandoned=已废弃',
  `handler_id`    BIGINT        DEFAULT NULL COMMENT '处理客服ID',
  `handler_reply` TEXT          DEFAULT NULL COMMENT '客服处理回复',
  `create_time`   DATETIME      DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `finish_time`   DATETIME      DEFAULT NULL COMMENT '完结时间',
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_source` (`source`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客服工单表';


-- =====================================================
-- 6. 文件资源表（统一管理上传的图片/附件）
-- =====================================================
DROP TABLE IF EXISTS `cs_file_resource`;
CREATE TABLE `cs_file_resource` (
  `id`           BIGINT        NOT NULL AUTO_INCREMENT,
  `session_id`   BIGINT        DEFAULT NULL COMMENT '关联会话',
  `msg_id`       BIGINT        DEFAULT NULL COMMENT '关联消息',
  `origin_name`  VARCHAR(255)  NOT NULL COMMENT '原始文件名',
  `storage_name` VARCHAR(255)  NOT NULL COMMENT '存储文件名（UUID格式）',
  `file_url`     VARCHAR(500)  NOT NULL COMMENT '文件访问URL',
  `file_type`    VARCHAR(100)  DEFAULT NULL COMMENT 'MIME类型',
  `file_size`    BIGINT        DEFAULT 0 COMMENT '文件大小（字节）',
  `upload_by`    BIGINT        DEFAULT NULL COMMENT '上传者ID',
  `create_time`  DATETIME      DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='聊天文件资源表';


-- =====================================================
-- 7. 消息已读记录表（精确追踪已读状态）
-- =====================================================
DROP TABLE IF EXISTS `cs_read_record`;
CREATE TABLE `cs_read_record` (
  `id`          BIGINT   NOT NULL AUTO_INCREMENT,
  `session_id`  BIGINT   NOT NULL COMMENT '会话ID',
  `user_id`     BIGINT   NOT NULL COMMENT '用户ID',
  `last_read_msg_id` BIGINT DEFAULT 0 COMMENT '已读到的消息ID',
  `read_time`   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_session_user` (`session_id`, `user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='消息已读记录表';


-- =====================================================
-- 关联视图: 会话列表聚合视图（供坐席端展示）
-- =====================================================
CREATE OR REPLACE VIEW `v_session_list` AS
SELECT
    s.id,
    s.session_no,
    s.session_type,
    s.from_user_id,
    s.from_user_name,
    s.from_user_avatar,
    s.waiter_id,
    s.status,
    s.last_msg,
    s.last_msg_time,
    s.unread_count,
    s.create_time,
    sc.seat_name
FROM cs_session s
LEFT JOIN cs_seat_config sc ON s.seat_id = sc.id;