心理咨询系统

-- 创建数据库XinLiZiXun
CREATE DATABASE IF NOT EXISTS XinLiZiXun;

-- 使用该数据库
USE XinLiZiXun;

-- 创建用户表user
CREATE TABLE IF NOT EXISTS `user` (
  `id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID，自增主键',
  `username` VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名，唯一',
  `password` VARCHAR(100) NOT NULL COMMENT '密码',
  `role` ENUM('用户', '咨询师', '系统管理员') NOT NULL DEFAULT '用户' COMMENT '角色，限定为用户、咨询师或系统管理员'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';

-- 创建咨询信息表
CREATE TABLE IF NOT EXISTS consultation_info (
  id INT AUTO_INCREMENT PRIMARY KEY COMMENT '信息单ID，自增主键',
  username VARCHAR(50) NOT NULL COMMENT '用户名，关联user表的username',
  problem_description TEXT NOT NULL COMMENT '心理问题描述',
  duration VARCHAR(50) NOT NULL COMMENT '问题持续时间（如：1个月、半年）',
  consultation_method ENUM('语音', '视频', '文字') NOT NULL COMMENT '偏好咨询方式',
  related_images VARCHAR(255) COMMENT '本地上传图片路径（如：/uploads/20231014/user1/report.png），多个图片用逗号分隔',
  consultant_name VARCHAR(50) COMMENT '咨询师名，接单时存入',
  create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  FOREIGN KEY (username) REFERENCES user(username) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='心理咨询信息单表（支持本地图片提交）';

-- 创建咨询师个人信息表
CREATE TABLE IF NOT EXISTS consultant_info (
  consultant_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '咨询师id，自增主键',
  username VARCHAR(50) NOT NULL COMMENT '用户名，关联user表的username（角色为咨询师）',
  real_name VARCHAR(50) NOT NULL COMMENT '咨询师真实姓名',
  gender ENUM('男', '女', '不透露') NOT NULL COMMENT '性别',
  work_years INT NOT NULL COMMENT '从业时长（单位：年）',
  expertise_fields VARCHAR(255) NOT NULL COMMENT '擅长领域（多个领域用逗号分隔，如：焦虑,婚姻家庭,青少年心理）',
  support_methods VARCHAR(100) NOT NULL COMMENT '支持的咨询方式（多个用逗号分隔，如：语音,视频,文字）',
  qualification_certs VARCHAR(512) COMMENT '资质证书路径（多个证书用逗号分隔，如：/certs/cert1.jpg,/certs/cert2.jpg）',
  consultation_cases TEXT COMMENT '咨询案例（多个案例用||分隔，如：案例1：焦虑症缓解||案例2：婚姻关系修复）',
  user_evaluations TEXT COMMENT '用户评价（多个评价用||分隔，如：评价1：很专业||评价2：有帮助）',
  service_prices VARCHAR(255) NOT NULL COMMENT '服务价格（格式：方式-时长-价格，多个用逗号分隔，如：视频-60分钟-500元,文字-30分钟-200元）',
  create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '信息创建时间',
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '信息更新时间',
  -- 关联用户表，确保该用户存在且为咨询师角色（业务层需额外校验角色）
  FOREIGN KEY (username) REFERENCES user(username) ON DELETE CASCADE,
  -- 确保用户名唯一，避免重复数据
  UNIQUE KEY uk_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='咨询师个人信息表';

-- 为咨询信息表添加预约时间和预约状态字段
ALTER TABLE consultation_info
ADD COLUMN appointment_time DATETIME COMMENT '预约时间（年月日时分）',
ADD COLUMN appointment_status ENUM('预约中', '预约成功') DEFAULT '预约中' COMMENT '预约状态';
-- 修改预约状态字段，增加"预约失败"选项
ALTER TABLE consultation_info
MODIFY COLUMN appointment_status ENUM('预约中', '预约成功', '预约失败') DEFAULT '预约中' COMMENT '预约状态';



-- 聊天记录表
CREATE TABLE chat_message (
    id INT AUTO_INCREMENT PRIMARY KEY,
    consultation_id INT NOT NULL COMMENT '咨询ID，关联consultation_info表',
    sender VARCHAR(50) NOT NULL COMMENT '发送者用户名',
    receiver VARCHAR(50) NOT NULL COMMENT '接收者用户名',
    content TEXT NOT NULL COMMENT '消息内容',
    send_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发送时间',
    is_read BOOLEAN DEFAULT FALSE COMMENT '是否已读',
    FOREIGN KEY (consultation_id) REFERENCES consultation_info(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='聊天记录表';

-- 创建索引以优化查询性能
CREATE INDEX idx_consultation_id ON chat_message(consultation_id);
CREATE INDEX idx_sender ON chat_message(sender);
CREATE INDEX idx_receiver ON chat_message(receiver);
CREATE INDEX idx_send_time ON chat_message(send_time);


ALTER TABLE user ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT '启用';

ALTER TABLE consultation_info ADD COLUMN consultant_feedback TEXT NULL COMMENT '咨询师反馈';