期末大作业01

今天进行了期末大作业开发，是一个心理咨询app，
以下是我的数据库设计,只是当前阶段的，后续可能还需要添加

-- 创建数据库
CREATE DATABASE IF NOT EXISTS mental_health DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE mental_health;

-- 用户表
CREATE TABLE IF NOT EXISTS user (
                                    id INT PRIMARY KEY AUTO_INCREMENT,
                                    username VARCHAR(50) NOT NULL UNIQUE,
                                    password VARCHAR(255) NOT NULL,
                                    nickname VARCHAR(50) NOT NULL,
                                    phone VARCHAR(20),
                                    email VARCHAR(100),
                                    avatar VARCHAR(255),
                                    gender TINYINT COMMENT '0:未知,1:男,2:女',
                                    role TINYINT NOT NULL DEFAULT 0 COMMENT '0:普通用户,1:咨询师',
                                    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                                    status TINYINT NOT NULL DEFAULT 1 COMMENT '0:禁用,1:启用'
);

-- 聊天会话表
CREATE TABLE IF NOT EXISTS chat_session (
                                            id INT PRIMARY KEY AUTO_INCREMENT,
                                            user_id INT NOT NULL,
                                            counselor_id INT NOT NULL,
                                            status TINYINT NOT NULL DEFAULT 1 COMMENT '1:进行中,0:已结束',
                                            create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                            update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                                            FOREIGN KEY (user_id) REFERENCES user(id),
                                            FOREIGN KEY (counselor_id) REFERENCES user(id),
                                            UNIQUE KEY uk_user_counselor (user_id, counselor_id)
);

-- 聊天消息表
CREATE TABLE IF NOT EXISTS chat_message (
                                            id INT PRIMARY KEY AUTO_INCREMENT,
                                            session_id INT NOT NULL,
                                            sender_id INT NOT NULL,
                                            sender_role TINYINT NOT NULL COMMENT '0:用户,1:咨询师',
                                            content TEXT NOT NULL,
                                            send_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                            is_read TINYINT NOT NULL DEFAULT 0 COMMENT '0:未读,1:已读',
                                            FOREIGN KEY (session_id) REFERENCES chat_session(id),
                                            FOREIGN KEY (sender_id) REFERENCES user(id)
);

-- 咨询师表
CREATE TABLE IF NOT EXISTS counselor (
                                         id INT PRIMARY KEY AUTO_INCREMENT,
                                         name VARCHAR(50) NOT NULL,
                                         gender TINYINT NOT NULL COMMENT '1:男,2:女',
                                         avatar VARCHAR(255),
                                         phone VARCHAR(20),
                                         email VARCHAR(100),
                                         introduction TEXT COMMENT '个人简介',
                                         specialties VARCHAR(255) COMMENT '擅长领域，用逗号分隔',
                                         education VARCHAR(255) COMMENT '教育背景',
                                         certifications TEXT COMMENT '资质证书',
                                         experience_years INT COMMENT '从业年限',
                                         rating DOUBLE DEFAULT 5.0 COMMENT '评分',
                                         review_count INT DEFAULT 0 COMMENT '评价数量',
                                         consultation_methods VARCHAR(100) COMMENT '咨询方式，如：语音,视频,文字',
                                         therapy_schools VARCHAR(255) COMMENT '治疗流派',
                                         price_per_hour DOUBLE NOT NULL COMMENT '每小时价格',
                                         status TINYINT NOT NULL DEFAULT 1 COMMENT '0:禁用,1:启用',
                                         create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                         update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 快速咨询申请表
CREATE TABLE IF NOT EXISTS quick_consultation_request (
                                                          id INT PRIMARY KEY AUTO_INCREMENT,
                                                          user_id INT NOT NULL,
                                                          problem_description TEXT NOT NULL COMMENT '核心心理问题描述',
                                                          duration VARCHAR(50) COMMENT '问题持续时间',
                                                          preferred_method VARCHAR(50) COMMENT '偏好咨询方式',
                                                          image_urls TEXT COMMENT '图片URL，用逗号分隔',
                                                          status TINYINT NOT NULL DEFAULT 0 COMMENT '0:待匹配,1:已匹配,-1:已取消',
                                                          matched_counselor_id INT,
                                                          create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                                          update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                                                          FOREIGN KEY (user_id) REFERENCES user(id),
                                                          FOREIGN KEY (matched_counselor_id) REFERENCES counselor(id)
);

-- 咨询预约表
CREATE TABLE IF NOT EXISTS consultation_appointment (
                                                        id INT PRIMARY KEY AUTO_INCREMENT,
                                                        user_id INT NOT NULL,
                                                        counselor_id INT NOT NULL,
                                                        consultation_type VARCHAR(50) NOT NULL COMMENT '咨询类型：电话/图文/视频',
                                                        duration INT NOT NULL COMMENT '时长（分钟）',
                                                        appointment_time DATETIME NOT NULL COMMENT '预约时间',
                                                        amount DOUBLE NOT NULL COMMENT '金额',
                                                        payment_status TINYINT NOT NULL DEFAULT 0 COMMENT '0:未支付,1:已支付,-1:支付失败',
                                                        appointment_status TINYINT NOT NULL DEFAULT 0 COMMENT '0:待确认,1:已确认,2:已完成,-1:已取消',
                                                        create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                                        update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                                                        FOREIGN KEY (user_id) REFERENCES user(id),
                                                        FOREIGN KEY (counselor_id) REFERENCES counselor(id)
);

-- 咨询记录表
CREATE TABLE IF NOT EXISTS consultation_record (
                                                   id INT PRIMARY KEY AUTO_INCREMENT,
                                                   appointment_id INT NOT NULL,
                                                   user_id INT NOT NULL,
                                                   counselor_id INT NOT NULL,
                                                   summary TEXT COMMENT '咨询摘要',
                                                   feedback TEXT COMMENT '咨询师反馈',
                                                   rating TINYINT COMMENT '评分(1-5星)',
                                                   review_content TEXT COMMENT '评价内容',
                                                   consultation_time DATETIME NOT NULL COMMENT '咨询时间',
                                                   create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                                   update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                                                   FOREIGN KEY (appointment_id) REFERENCES consultation_appointment(id),
                                                   FOREIGN KEY (user_id) REFERENCES user(id),
                                                   FOREIGN KEY (counselor_id) REFERENCES counselor(id)
);

-- 创建索引
CREATE INDEX idx_appointment_user ON consultation_appointment(user_id);
CREATE INDEX idx_appointment_counselor ON consultation_appointment(counselor_id);
CREATE INDEX idx_appointment_time ON consultation_appointment(appointment_time);
CREATE INDEX idx_record_user ON consultation_record(user_id);
CREATE INDEX idx_record_counselor ON consultation_record(counselor_id);
CREATE INDEX idx_quick_request_user ON quick_consultation_request(user_id);