-- 心理咨询系统数据库初始化脚本
CREATE DATABASE IF NOT EXISTS psychology_system DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE psychology_system;

-- 用户表
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    password VARCHAR(100) NOT NULL COMMENT '密码',
    email VARCHAR(100) UNIQUE COMMENT '邮箱',
    phone VARCHAR(20) UNIQUE COMMENT '手机号',
    real_name VARCHAR(50) COMMENT '真实姓名',
    avatar_url VARCHAR(255) COMMENT '头像URL',
    gender TINYINT COMMENT '性别 0-未知 1-男 2-女',
    birth_date DATE COMMENT '出生日期',
    role ENUM('USER', 'COUNSELOR', 'ADMIN') DEFAULT 'USER' COMMENT '用户角色',
    status TINYINT DEFAULT 1 COMMENT '状态 0-禁用 1-正常',
    last_login_time DATETIME COMMENT '最后登录时间',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
);

-- 咨询师信息表
CREATE TABLE counselors (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '咨询师ID',
    user_id BIGINT NOT NULL COMMENT '关联用户ID',
    title VARCHAR(100) COMMENT '职称',
    experience_years INT DEFAULT 0 COMMENT '从业年限',
    education TEXT COMMENT '教育背景',
    specializations TEXT COMMENT '擅长领域（JSON格式存储）',
    consultation_types ENUM('TEXT', 'VOICE', 'VIDEO') DEFAULT 'TEXT' COMMENT '咨询方式',
    price_per_hour DECIMAL(8,2) DEFAULT 0 COMMENT '价格(元/小时)',
    rating DECIMAL(3,2) DEFAULT 5.0 COMMENT '评分',
    total_consultations INT DEFAULT 0 COMMENT '总咨询次数',
    bio TEXT COMMENT '个人简介',
    certificates TEXT COMMENT '资质证书（JSON格式）',
    status ENUM('PENDING', 'APPROVED', 'REJECTED', 'SUSPENDED') DEFAULT 'PENDING' COMMENT '审核状态',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 快速咨询申请表
CREATE TABLE consultation_applications (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '申请ID',
    user_id BIGINT NOT NULL COMMENT '申请用户ID',
    problem_description TEXT NOT NULL COMMENT '核心心理问题描述',
    problem_duration VARCHAR(100) COMMENT '问题持续时间',
    preferred_method ENUM('TEXT', 'VOICE', 'VIDEO') COMMENT '偏好咨询方式',
    image_urls TEXT COMMENT '上传图片URLs（逗号分隔）',
    status ENUM('PENDING', 'MATCHED', 'COMPLETED') DEFAULT 'PENDING' COMMENT '申请状态',
    matched_counselor_id BIGINT COMMENT '匹配咨询师ID',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (matched_counselor_id) REFERENCES counselors(id)
);

-- 咨询预约表
CREATE TABLE consultation_bookings (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '预约ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    counselor_id BIGINT NOT NULL COMMENT '咨询师ID',
    consultation_type ENUM('TEXT', 'VOICE', 'VIDEO') NOT NULL COMMENT '咨询方式',
    duration INT NOT NULL COMMENT '咨询时长（分钟）',
    scheduled_time DATETIME NOT NULL COMMENT '预约时间',
    status ENUM('PENDING', 'CONFIRMED', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED') DEFAULT 'PENDING' COMMENT '预约状态',
    price DECIMAL(8,2) NOT NULL COMMENT '咨询费用',
    payment_status ENUM('UNPAID', 'PAID', 'REFUNDED') DEFAULT 'UNPAID' COMMENT '支付状态',
    payment_id VARCHAR(100) COMMENT '支付订单ID',
    notes TEXT COMMENT '备注',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (counselor_id) REFERENCES counselors(id)
);

-- 咨询记录表
CREATE TABLE consultation_records (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '记录ID',
    booking_id BIGINT NOT NULL COMMENT '预约ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    counselor_id BIGINT NOT NULL COMMENT '咨询师ID',
    start_time DATETIME COMMENT '实际开始时间',
    end_time DATETIME COMMENT '实际结束时间',
    consultation_summary TEXT COMMENT '咨询摘要',
    counselor_feedback TEXT COMMENT '咨询师反馈',
    user_rating TINYINT COMMENT '用户评分 1-5',
    counselor_rating TINYINT COMMENT '咨询师评分 1-5',
    core_issues JSON COMMENT '核心困扰标签（JSON格式）',
    follow_up_required BOOLEAN DEFAULT FALSE COMMENT '是否需要后续跟进',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (booking_id) REFERENCES consultation_bookings(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (counselor_id) REFERENCES counselors(id)
);

-- 学习包表
CREATE TABLE learning_packages (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '学习包ID',
    name VARCHAR(200) NOT NULL COMMENT '学习包名称',
    description TEXT COMMENT '学习包描述',
    category VARCHAR(100) COMMENT '分类',
    target_issues JSON COMMENT '目标困扰类型（JSON格式）',
    difficulty_level ENUM('BEGINNER', 'INTERMEDIATE', 'ADVANCED') DEFAULT 'BEGINNER' COMMENT '难度等级',
    video_count INT DEFAULT 0 COMMENT '视频数量',
    estimated_duration INT COMMENT '预计学习时长（分钟）',
    cover_image_url VARCHAR(255) COMMENT '封面图片URL',
    status ENUM('DRAFT', 'PUBLISHED', 'ARCHIVED') DEFAULT 'DRAFT' COMMENT '状态',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
);

-- 视频表
CREATE TABLE videos (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '视频ID',
    learning_package_id BIGINT NOT NULL COMMENT '学习包ID',
    title VARCHAR(200) NOT NULL COMMENT '视频标题',
    description TEXT COMMENT '视频描述',
    video_url VARCHAR(500) NOT NULL COMMENT '视频URL',
    duration INT COMMENT '视频时长（秒）',
    order_index INT DEFAULT 0 COMMENT '排序索引',
    verification_question TEXT COMMENT '验证题目',
    verification_answer VARCHAR(100) COMMENT '验证答案',
    view_count INT DEFAULT 0 COMMENT '观看次数',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (learning_package_id) REFERENCES learning_packages(id)
);

-- 测试题表
CREATE TABLE test_questions (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '题目ID',
    learning_package_id BIGINT NOT NULL COMMENT '学习包ID',
    question_text TEXT NOT NULL COMMENT '题目内容',
    question_type ENUM('SINGLE_CHOICE', 'MULTIPLE_CHOICE', 'TRUE_FALSE') NOT NULL COMMENT '题目类型',
    options JSON COMMENT '选项（JSON格式）',
    correct_answer TEXT NOT NULL COMMENT '正确答案',
    explanation TEXT COMMENT '答案解析',
    score INT DEFAULT 1 COMMENT '分值',
    order_index INT DEFAULT 0 COMMENT '排序索引',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (learning_package_id) REFERENCES learning_packages(id)
);

-- 用户学习记录表
CREATE TABLE user_learning_records (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '记录ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    video_id BIGINT NOT NULL COMMENT '视频ID',
    learning_package_id BIGINT NOT NULL COMMENT '学习包ID',
    watch_progress DECIMAL(5,2) DEFAULT 0 COMMENT '观看进度（百分比）',
    last_watch_position INT DEFAULT 0 COMMENT '最后观看位置（秒）',
    completed BOOLEAN DEFAULT FALSE COMMENT '是否完成',
    verification_passed BOOLEAN DEFAULT FALSE COMMENT '验证是否通过',
    watch_count INT DEFAULT 0 COMMENT '观看次数',
    total_watch_time INT DEFAULT 0 COMMENT '总观看时长（秒）',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (video_id) REFERENCES videos(id),
    FOREIGN KEY (learning_package_id) REFERENCES learning_packages(id),
    UNIQUE KEY uk_user_video (user_id, video_id)
);

-- 测试记录表
CREATE TABLE test_records (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '测试记录ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    learning_package_id BIGINT NOT NULL COMMENT '学习包ID',
    total_questions INT NOT NULL COMMENT '总题数',
    correct_answers INT DEFAULT 0 COMMENT '正确题数',
    total_score INT DEFAULT 0 COMMENT '总分',
    obtained_score INT DEFAULT 0 COMMENT '获得分数',
    duration INT COMMENT '答题时长（秒）',
    answers JSON COMMENT '用户答案（JSON格式）',
    status ENUM('IN_PROGRESS', 'COMPLETED') DEFAULT 'IN_PROGRESS' COMMENT '状态',
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '开始时间',
    completed_at TIMESTAMP COMMENT '完成时间',
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (learning_package_id) REFERENCES learning_packages(id)
);

-- 评估报告表
CREATE TABLE evaluation_reports (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '报告ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    test_record_id BIGINT NOT NULL COMMENT '测试记录ID',
    learning_package_id BIGINT NOT NULL COMMENT '学习包ID',
    learning_effectiveness JSON COMMENT '学习效果（JSON格式）',
    psychological_state JSON COMMENT '心理状态评估（JSON格式）',
    recommendations TEXT COMMENT '建议内容',
    report_data JSON COMMENT '完整报告数据（JSON格式）',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (test_record_id) REFERENCES test_records(id),
    FOREIGN KEY (learning_package_id) REFERENCES learning_packages(id)
);

-- 消息表
CREATE TABLE messages (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '消息ID',
    booking_id BIGINT COMMENT '关联预约ID',
    sender_id BIGINT NOT NULL COMMENT '发送者ID',
    receiver_id BIGINT NOT NULL COMMENT '接收者ID',
    content TEXT NOT NULL COMMENT '消息内容',
    message_type ENUM('TEXT', 'IMAGE', 'VOICE', 'VIDEO') DEFAULT 'TEXT' COMMENT '消息类型',
    file_url VARCHAR(500) COMMENT '文件URL',
    is_read BOOLEAN DEFAULT FALSE COMMENT '是否已读',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    FOREIGN KEY (booking_id) REFERENCES consultation_bookings(id),
    FOREIGN KEY (sender_id) REFERENCES users(id),
    FOREIGN KEY (receiver_id) REFERENCES users(id)
);

-- 系统配置表
CREATE TABLE system_configs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '配置ID',
    config_key VARCHAR(100) NOT NULL UNIQUE COMMENT '配置键',
    config_value TEXT COMMENT '配置值',
    description VARCHAR(200) COMMENT '描述',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
);

-- 插入初始配置数据
INSERT INTO system_configs (config_key, config_value, description) VALUES
('consultation_price_text', '100', '图文咨询价格(元/小时)'),
('consultation_price_voice', '200', '语音咨询价格(元/小时)'),
('consultation_price_video', '300', '视频咨询价格(元/小时)'),
('test_time_limit', '900', '测试题时间限制(秒)'),
('video_verification_required', 'true', '视频验证是否必填'),
('default_rating', '5', '默认评分');

-- 创建索引
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_counselors_user_id ON counselors(user_id);
CREATE INDEX idx_counselors_status ON counselors(status);
CREATE INDEX idx_consultation_applications_user_id ON consultation_applications(user_id);
CREATE INDEX idx_consultation_bookings_user_id ON consultation_bookings(user_id);
CREATE INDEX idx_consultation_bookings_counselor_id ON consultation_bookings(counselor_id);
CREATE INDEX idx_consultation_records_user_id ON consultation_records(user_id);
CREATE INDEX idx_learning_packages_category ON learning_packages(category);
CREATE INDEX idx_videos_package_id ON videos(learning_package_id);
CREATE INDEX idx_user_learning_records_user_id ON user_learning_records(user_id);
CREATE INDEX idx_test_records_user_id ON test_records(user_id);