ai_la/base4/server/sql/create_all_tables.sql

-- AI学习搭子项目 - MySQL数据库建表脚本
-- 创建所有9张表的完整SQL语句

-- 1. 创建用户信息表
CREATE TABLE user_info (
    user_id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    password VARCHAR(255) NOT NULL COMMENT '密码',
    grade VARCHAR(20) COMMENT '年级',
    avatar VARCHAR(255) COMMENT '头像路径',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';

-- 2. 创建学习目标表
CREATE TABLE goals (
    goal_id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '目标ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    type VARCHAR(50) NOT NULL COMMENT '目标类型',
    knowledge_point TEXT COMMENT '知识点',
    difficulty VARCHAR(20) COMMENT '难度',
    total_quantity INT DEFAULT 0 COMMENT '总题目数量',
    completed_quantity INT DEFAULT 0 COMMENT '已完成数量',
    estimated_time INT COMMENT '预估时间(分钟)',
    start_time TIMESTAMP COMMENT '开始时间',
    status TINYINT DEFAULT 0 COMMENT '状态(0:未开始,1:进行中,2:已完成)',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    complete_time TIMESTAMP COMMENT '完成时间',
    answer_duration INT DEFAULT 0 COMMENT '答题总时长(秒)',
    FOREIGN KEY (user_id) REFERENCES user_info(user_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学习目标表';

-- 3. 创建目标详情表
CREATE TABLE goal_of_detail (
    detail_id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '详情ID',
    goal_id BIGINT NOT NULL COMMENT '目标ID',
    type VARCHAR(20) COMMENT '题目类型(选择题、填空题、单词等)',
    question TEXT COMMENT '题目内容',
    options JSON COMMENT '选项(JSON格式)',
    answer TEXT COMMENT '正确答案',
    difficulty VARCHAR(20) COMMENT '难度',
    knowledge_point VARCHAR(100) COMMENT '知识点',
    explanation TEXT COMMENT '解析',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    FOREIGN KEY (goal_id) REFERENCES goals(goal_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='目标详情表';

-- 4. 创建用户答题记录表
CREATE TABLE user_answer (
    answer_id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '答题记录ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    goal_id BIGINT NOT NULL COMMENT '目标ID',
    detail_id BIGINT NOT NULL COMMENT '题目详情ID',
    user_answer TEXT COMMENT '用户答案',
    is_correct BOOLEAN DEFAULT FALSE COMMENT '是否正确',
    answer_time INT DEFAULT 0 COMMENT '答题用时(秒)',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '答题时间',
    FOREIGN KEY (user_id) REFERENCES user_info(user_id) ON DELETE CASCADE,
    FOREIGN KEY (goal_id) REFERENCES goals(goal_id) ON DELETE CASCADE,
    FOREIGN KEY (detail_id) REFERENCES goal_of_detail(detail_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户答题记录表';

-- 5. 创建学习报告表
CREATE TABLE study_report (
    report_id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '报告ID',
    goal_id BIGINT NOT NULL COMMENT '目标ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    accuracy_rate DECIMAL(5,2) DEFAULT 0.00 COMMENT '正确率',
    total_questions INT DEFAULT 0 COMMENT '总题数',
    correct_answers INT DEFAULT 0 COMMENT '正确题数',
    total_time INT DEFAULT 0 COMMENT '总用时(秒)',
    knowledge_analysis JSON COMMENT '知识点分析(JSON格式)',
    difficulty_analysis JSON COMMENT '难度分析(JSON格式)',
    improvement_suggestions TEXT COMMENT '改进建议',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    FOREIGN KEY (goal_id) REFERENCES goals(goal_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES user_info(user_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学习报告表';

-- 6. 创建AI对话记录表
CREATE TABLE ai_chat (
    chat_id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '聊天ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    content TEXT NOT NULL COMMENT '对话内容',
    type VARCHAR(20) NOT NULL COMMENT '消息类型(user/assistant)',
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '时间戳',
    FOREIGN KEY (user_id) REFERENCES user_info(user_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI对话记录表';

-- 7. 创建成就表
CREATE TABLE achievements (
    achievement_id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '成就ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    type VARCHAR(50) NOT NULL COMMENT '成就类型',
    name VARCHAR(100) NOT NULL COMMENT '成就名称',
    description TEXT COMMENT '成就描述',
    icon VARCHAR(255) COMMENT '成就图标',
    progress INT DEFAULT 0 COMMENT '当前进度',
    target INT DEFAULT 1 COMMENT '目标值',
    is_unlocked BOOLEAN DEFAULT FALSE COMMENT '是否已解锁',
    unlock_time TIMESTAMP COMMENT '解锁时间',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    FOREIGN KEY (user_id) REFERENCES user_info(user_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成就表';

-- 8. 创建学习统计表
CREATE TABLE study_stats (
    stats_id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '统计ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    daily_study_time JSON COMMENT '每日学习时长(JSON格式)',
    subject_study_time JSON COMMENT '各科目学习时长(JSON格式)',
    completed_goals_count INT DEFAULT 0 COMMENT '完成目标数',
    total_study_time INT DEFAULT 0 COMMENT '总学习时长(分钟)',
    average_accuracy DECIMAL(5,2) DEFAULT 0.00 COMMENT '平均正确率',
    streak_days INT DEFAULT 0 COMMENT '连续学习天数',
    last_study_date DATE COMMENT '最后学习日期',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (user_id) REFERENCES user_info(user_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学习统计表';

-- 9. 创建系统设置表
CREATE TABLE system_settings (
    setting_id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '设置ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    study_reminder_enabled BOOLEAN DEFAULT TRUE COMMENT '学习提醒开关',
    focus_mode_enabled BOOLEAN DEFAULT TRUE COMMENT '专注模式开关',
    data_sync_enabled BOOLEAN DEFAULT FALSE COMMENT '数据同步开关',
    privacy_protection_enabled BOOLEAN DEFAULT TRUE COMMENT '隐私保护开关',
    night_mode_enabled BOOLEAN DEFAULT FALSE COMMENT '夜间模式开关',
    reminder_time VARCHAR(10) DEFAULT '09:00' COMMENT '提醒时间',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (user_id) REFERENCES user_info(user_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统设置表';

-- 创建索引以提高查询性能
CREATE INDEX idx_goals_user_id ON goals(user_id);
CREATE INDEX idx_goals_status ON goals(status);
CREATE INDEX idx_goal_detail_goal_id ON goal_of_detail(goal_id);
CREATE INDEX idx_user_answer_user_id ON user_answer(user_id);
CREATE INDEX idx_user_answer_goal_id ON user_answer(goal_id);
CREATE INDEX idx_study_report_user_id ON study_report(user_id);
CREATE INDEX idx_ai_chat_user_id ON ai_chat(user_id);
CREATE INDEX idx_achievements_user_id ON achievements(user_id);
CREATE INDEX idx_study_stats_user_id ON study_stats(user_id);
CREATE INDEX idx_system_settings_user_id ON system_settings(user_id);

-- 插入初始数据（可选）
-- 创建默认管理员用户
INSERT INTO user_info (username, password, grade) VALUES 
('admin', '$2a$10$N.zmdr9k7uOCQb376NoUnuTJ8iAt6Z5EHsM8lE9lBOsl7iKTVEFDa', '管理员');

-- 显示创建结果
SELECT 'AI学习搭子数据库表创建完成！' AS message;
SHOW TABLES;