心理健康系统开发1，数据库

-- MySQL数据库表结构
-- 心理健康平台数据库

-- 创建用户表
CREATE TABLE sys_user (
id bigint NOT NULL AUTO_INCREMENT COMMENT '用户ID',
username varchar(50) NOT NULL COMMENT '用户名',
password varchar(100) NOT NULL COMMENT '密码',
nickname varchar(50) DEFAULT NULL COMMENT '昵称',
phone varchar(20) DEFAULT NULL COMMENT '手机号',
email varchar(100) DEFAULT NULL COMMENT '邮箱',
avatar varchar(200) DEFAULT NULL COMMENT '头像',
gender tinyint DEFAULT 0 COMMENT '性别(1男 2女 0未知)',
birthday datetime DEFAULT NULL COMMENT '生日',
region varchar(100) DEFAULT NULL COMMENT '地区',
status tinyint DEFAULT 1 COMMENT '状态(0禁用 1启用)',
create_time datetime NOT NULL COMMENT '创建时间',
update_time datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (id),
UNIQUE KEY uk_username (username),
UNIQUE KEY uk_phone (phone),
UNIQUE KEY uk_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

-- 创建咨询师表
CREATE TABLE sys_consultant (
id bigint NOT NULL AUTO_INCREMENT COMMENT '咨询师ID',
username varchar(50) NOT NULL COMMENT '用户名',
password varchar(100) NOT NULL COMMENT '密码',
real_name varchar(50) DEFAULT NULL COMMENT '真实姓名',
phone varchar(20) DEFAULT NULL COMMENT '手机号',
email varchar(100) DEFAULT NULL COMMENT '邮箱',
avatar varchar(200) DEFAULT NULL COMMENT '头像',
introduction varchar(500) DEFAULT NULL COMMENT '自我介绍',
qualifications varchar(500) DEFAULT NULL COMMENT '资质证书',
specialties varchar(200) DEFAULT NULL COMMENT '专长领域',
experience varchar(500) DEFAULT NULL COMMENT '从业经验',
gender tinyint DEFAULT 0 COMMENT '性别(1男 2女 0未知)',
price_per_hour int DEFAULT NULL COMMENT '每小时价格',
status tinyint DEFAULT 0 COMMENT '状态(0待审核 1审核通过 2禁用 3下线)',
create_time datetime NOT NULL COMMENT '创建时间',
update_time datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (id),
UNIQUE KEY uk_consultant_username (username),
UNIQUE KEY uk_consultant_phone (phone),
UNIQUE KEY uk_consultant_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='咨询师表';

-- 创建学习包表
CREATE TABLE sys_study_package (
id bigint NOT NULL AUTO_INCREMENT COMMENT '学习包ID',
name varchar(100) NOT NULL COMMENT '学习包名称',
description varchar(500) DEFAULT NULL COMMENT '学习包描述',
cover_image varchar(200) DEFAULT NULL COMMENT '封面图片',
tags varchar(200) DEFAULT NULL COMMENT '关联的困扰标签，用于匹配推荐',
order_num int DEFAULT 0 COMMENT '排序号',
status tinyint DEFAULT 1 COMMENT '状态(0禁用 1启用)',
create_time datetime NOT NULL COMMENT '创建时间',
update_time datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='学习包表';

-- 创建咨询记录表
CREATE TABLE sys_consultation (
id bigint NOT NULL AUTO_INCREMENT COMMENT '咨询记录ID',
user_id bigint NOT NULL COMMENT '用户ID',
consultant_id bigint NOT NULL COMMENT '咨询师ID',
core_issue varchar(200) DEFAULT NULL COMMENT '核心问题',
description text COMMENT '详细描述',
duration int DEFAULT NULL COMMENT '咨询时长（分钟）',
type tinyint DEFAULT NULL COMMENT '咨询类型(1文字 2语音 3视频)',
image_urls varchar(500) DEFAULT NULL COMMENT '图片URL，多个用逗号分隔',
status tinyint DEFAULT 0 COMMENT '状态(0待匹配 1待咨询 2进行中 3已完成 4已取消)',
appointment_time datetime DEFAULT NULL COMMENT '预约时间',
actual_start_time datetime DEFAULT NULL COMMENT '实际开始时间',
actual_end_time datetime DEFAULT NULL COMMENT '实际结束时间',
room_id varchar(50) DEFAULT NULL COMMENT '咨询室ID',
payment_status tinyint DEFAULT 0 COMMENT '支付状态(0未支付 1已支付 2已退款)',
total_amount int DEFAULT 0 COMMENT '总金额',
problem_tags varchar(200) DEFAULT NULL COMMENT '核心困扰标签，多个用逗号分隔',
create_time datetime NOT NULL COMMENT '创建时间',
update_time datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (id),
KEY idx_user_id (user_id),
KEY idx_consultant_id (consultant_id),
KEY idx_status (status),
CONSTRAINT fk_consultation_user FOREIGN KEY (user_id) REFERENCES sys_user (id) ON DELETE CASCADE,
CONSTRAINT fk_consultation_consultant FOREIGN KEY (consultant_id) REFERENCES sys_consultant (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='咨询记录表';

-- 创建消息表
CREATE TABLE sys_message (
id bigint NOT NULL AUTO_INCREMENT COMMENT '消息ID',
sender_id bigint DEFAULT NULL COMMENT '发送者ID（用户）',
receiver_id bigint DEFAULT NULL COMMENT '接收者ID（用户）',
consultant_id bigint DEFAULT NULL COMMENT '咨询师ID',
type tinyint DEFAULT 1 COMMENT '消息类型(1文字 2图片 3语音 4系统通知)',
content text COMMENT '消息内容或URL',
status tinyint DEFAULT 0 COMMENT '状态(0未读 1已读)',
room_id varchar(50) DEFAULT NULL COMMENT '咨询室ID',
send_time datetime DEFAULT NULL COMMENT '发送时间',
create_time datetime NOT NULL COMMENT '创建时间',
update_time datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (id),
KEY idx_sender_id (sender_id),
KEY idx_receiver_id (receiver_id),
KEY idx_consultant_id (consultant_id),
KEY idx_room_id (room_id),
CONSTRAINT fk_message_sender FOREIGN KEY (sender_id) REFERENCES sys_user (id) ON DELETE CASCADE,
CONSTRAINT fk_message_receiver FOREIGN KEY (receiver_id) REFERENCES sys_user (id) ON DELETE CASCADE,
CONSTRAINT fk_message_consultant FOREIGN KEY (consultant_id) REFERENCES sys_consultant (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='消息表';

-- 创建咨询评价表
CREATE TABLE sys_consultation_evaluation (
id bigint NOT NULL AUTO_INCREMENT COMMENT '评价ID',
user_id bigint NOT NULL COMMENT '用户ID',
consultant_id bigint NOT NULL COMMENT '咨询师ID',
consultation_id bigint NOT NULL COMMENT '咨询记录ID',
score tinyint NOT NULL COMMENT '评分1-5',
comment varchar(500) DEFAULT NULL COMMENT '评价内容',
create_time datetime NOT NULL COMMENT '创建时间',
update_time datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (id),
UNIQUE KEY uk_consultation_id (consultation_id),
KEY idx_user_id (user_id),
KEY idx_consultant_id (consultant_id),
CONSTRAINT fk_evaluation_user FOREIGN KEY (user_id) REFERENCES sys_user (id) ON DELETE CASCADE,
CONSTRAINT fk_evaluation_consultant FOREIGN KEY (consultant_id) REFERENCES sys_consultant (id) ON DELETE CASCADE,
CONSTRAINT fk_evaluation_consultation FOREIGN KEY (consultation_id) REFERENCES sys_consultation (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='咨询评价表';

-- 创建视频表
CREATE TABLE sys_video (
id bigint NOT NULL AUTO_INCREMENT COMMENT '视频ID',
study_package_id bigint NOT NULL COMMENT '学习包ID',
title varchar(200) NOT NULL COMMENT '视频标题',
description varchar(500) DEFAULT NULL COMMENT '视频描述',
video_url varchar(500) NOT NULL COMMENT '视频URL',
duration int DEFAULT 0 COMMENT '视频时长（秒）',
order_num int DEFAULT 0 COMMENT '排序号',
status tinyint DEFAULT 1 COMMENT '状态(0禁用 1启用)',
verification_question varchar(200) DEFAULT NULL COMMENT '验证题目',
verification_options varchar(200) DEFAULT NULL COMMENT '验证选项，多个用逗号分隔',
verification_answer varchar(100) DEFAULT NULL COMMENT '验证答案',
create_time datetime NOT NULL COMMENT '创建时间',
update_time datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (id),
KEY idx_study_package_id (study_package_id),
KEY idx_status (status),
CONSTRAINT fk_video_study_package FOREIGN KEY (study_package_id) REFERENCES sys_study_package (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='视频表';

-- 创建视频进度表
CREATE TABLE sys_video_progress (
id bigint NOT NULL AUTO_INCREMENT COMMENT '进度ID',
user_id bigint NOT NULL COMMENT '用户ID',
video_id bigint NOT NULL COMMENT '视频ID',
progress int DEFAULT 0 COMMENT '观看进度（秒）',
is_completed tinyint DEFAULT 0 COMMENT '是否完成(0未完成 1已完成)',
is_verified tinyint DEFAULT 0 COMMENT '是否验证(0未验证 1已验证)',
last_watch_time datetime DEFAULT NULL COMMENT '最后观看时间',
create_time datetime NOT NULL COMMENT '创建时间',
update_time datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (id),
UNIQUE KEY uk_user_video (user_id,video_id),
KEY idx_video_id (video_id),
CONSTRAINT fk_progress_user FOREIGN KEY (user_id) REFERENCES sys_user (id) ON DELETE CASCADE,
CONSTRAINT fk_progress_video FOREIGN KEY (video_id) REFERENCES sys_video (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='视频进度表';

-- 创建测试表
CREATE TABLE sys_test (
id bigint NOT NULL AUTO_INCREMENT COMMENT '测试ID',
study_package_id bigint NOT NULL COMMENT '学习包ID',
title varchar(200) NOT NULL COMMENT '测试标题',
description varchar(500) DEFAULT NULL COMMENT '测试描述',
time_limit int DEFAULT 0 COMMENT '时间限制（分钟）',
status tinyint DEFAULT 1 COMMENT '状态(0禁用 1启用)',
create_time datetime NOT NULL COMMENT '创建时间',
update_time datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (id),
KEY idx_study_package_id (study_package_id),
KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='测试表';

-- 创建测试题目表
CREATE TABLE sys_test_question (
id bigint NOT NULL AUTO_INCREMENT COMMENT '题目ID',
test_id bigint NOT NULL COMMENT '测试ID',
content text NOT NULL COMMENT '题目内容',
type tinyint DEFAULT 1 COMMENT '题目类型(1单选 2多选 3判断)',
options text COMMENT '选项内容，JSON格式',
answer varchar(100) NOT NULL COMMENT '正确答案',
analysis text COMMENT '解析说明',
score int DEFAULT 10 COMMENT '分值',
order_num int DEFAULT 0 COMMENT '排序号',
create_time datetime NOT NULL COMMENT '创建时间',
update_time datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (id),
KEY idx_test_id (test_id),
CONSTRAINT fk_question_test FOREIGN KEY (test_id) REFERENCES sys_test (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='测试题目表';

-- 创建测试答案表
CREATE TABLE sys_test_answer (
id bigint NOT NULL AUTO_INCREMENT COMMENT '答案ID',
user_id bigint NOT NULL COMMENT '用户ID',
test_id bigint NOT NULL COMMENT '测试ID',
answers text COMMENT '用户答案，JSON格式',
score int DEFAULT 0 COMMENT '得分',
total_score int DEFAULT 0 COMMENT '总分',
correct_rate double DEFAULT 0 COMMENT '正确率',
wrong_questions varchar(500) DEFAULT NULL COMMENT '错题ID列表，多个用逗号分隔',
start_time datetime DEFAULT NULL COMMENT '开始答题时间',
end_time datetime DEFAULT NULL COMMENT '结束答题时间',
create_time datetime NOT NULL COMMENT '创建时间',
update_time datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (id),
KEY idx_user_id (user_id),
KEY idx_test_id (test_id),
CONSTRAINT fk_test_answer_user FOREIGN KEY (user_id) REFERENCES sys_user (id) ON DELETE CASCADE,
CONSTRAINT fk_test_answer_test FOREIGN KEY (test_id) REFERENCES sys_test (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='测试答案表';

-- 创建评估报告表
CREATE TABLE sys_assessment_report (
id bigint NOT NULL AUTO_INCREMENT COMMENT '报告ID',
user_id bigint NOT NULL COMMENT '用户ID',
test_answer_id bigint NOT NULL COMMENT '测试答案ID',
learning_score double DEFAULT 0 COMMENT '学习效果得分',
weak_points varchar(500) DEFAULT NULL COMMENT '薄弱环节',
stress_level tinyint DEFAULT 0 COMMENT '压力水平 1-10',
mood_status tinyint DEFAULT 0 COMMENT '情绪状态 1-10',
suggestions text COMMENT '后续建议',
report_content text COMMENT '完整报告内容',
create_time datetime NOT NULL COMMENT '创建时间',
update_time datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (id),
UNIQUE KEY uk_test_answer_id (test_answer_id),
KEY idx_user_id (user_id),
CONSTRAINT fk_report_user FOREIGN KEY (user_id) REFERENCES sys_user (id) ON DELETE CASCADE,
CONSTRAINT fk_report_test_answer FOREIGN KEY (test_answer_id) REFERENCES sys_test_answer (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='评估报告表';

-- 创建索引优化查询性能
CREATE INDEX idx_consultant_status ON sys_consultant (status);
CREATE INDEX idx_study_package_tags ON sys_study_package (tags);
CREATE INDEX idx_consultation_appointment ON sys_consultation (appointment_time);
CREATE INDEX idx_video_progress_completed ON sys_video_progress (user_id,is_completed);
CREATE INDEX idx_message_status ON sys_message (status);
CREATE INDEX idx_evaluation_score ON sys_consultation_evaluation (score);