LWX/ai_teaching_system/schema.sql

-- 创建数据库（如果不存在）
CREATE DATABASE IF NOT EXISTS ai_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 使用该数据库
USE ai_system;

-- 创建用户表（与后端模型 User 对应，包含基础登录信息 + 个人资料）
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(80) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) DEFAULT 'student',
    full_name VARCHAR(50) NULL,
    student_id VARCHAR(50) NULL,
    class_name VARCHAR(100) NULL,
    grade VARCHAR(20) NULL,
    email VARCHAR(120) NULL,
    phone VARCHAR(20) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 表：questions（与 Flask 模型 Question 对应，化学每日训练题库）
CREATE TABLE IF NOT EXISTS questions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content TEXT NOT NULL,
    option_a VARCHAR(255) NOT NULL,
    option_b VARCHAR(255) NOT NULL,
    option_c VARCHAR(255) NOT NULL,
    option_d VARCHAR(255) NOT NULL,
    correct_option VARCHAR(1) NOT NULL,
    knowledge_point VARCHAR(100),
    difficulty INT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 表：answer_records（与 Flask 模型 AnswerRecord 对应，答题记录）
CREATE TABLE IF NOT EXISTS answer_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    question_id INT NOT NULL,
    selected_option VARCHAR(1) NOT NULL,
    is_correct TINYINT(1) DEFAULT 0,
    answered_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_answer_records_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_answer_records_question
        FOREIGN KEY (question_id) REFERENCES questions(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 表：knowledge_points（与 Flask 模型 KnowledgePoint 对应，知识点实体）
CREATE TABLE IF NOT EXISTS knowledge_points (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    chapter VARCHAR(80),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 表：knowledge_relations（与 Flask 模型 KnowledgeRelation 对应，知识图谱三元组）
CREATE TABLE IF NOT EXISTS knowledge_relations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    subject VARCHAR(120) NOT NULL,
    predicate VARCHAR(80) NOT NULL,
    obj VARCHAR(120) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 表：curriculum_topics（与 Flask 模型 CurriculumTopic 对应，课程主题）
CREATE TABLE IF NOT EXISTS curriculum_topics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    phase VARCHAR(20) DEFAULT '初中',
    chapter VARCHAR(80),
    description TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 表：chemistry_question_bank（与 Flask 模型 ChemistryQuestionBank 对应，多题型题库）
CREATE TABLE IF NOT EXISTS chemistry_question_bank (
    question_id INT AUTO_INCREMENT PRIMARY KEY,
    type ENUM('choice', 'equation', 'experiment', 'calculation', 'inference', 'diagram') NOT NULL,
    difficulty ENUM('easy', 'medium', 'hard') NOT NULL DEFAULT 'easy',
    topic VARCHAR(50),
    unit_no TINYINT NULL COMMENT '教材单元编号，如 1-12',
    knowledge_tags VARCHAR(255) NULL COMMENT '知识点标签，逗号分隔',
    content TEXT NOT NULL,
    answer_schema JSON NOT NULL COMMENT '题型结构化字段：choice 含 options{A..D} 与 correct；填空/实验等用 reference_answer 等，见 seed_data 与抽题脚本',
    analysis TEXT NULL COMMENT '题目解析（冗余列，便于直接检索/展示）',
    hint TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_cqb_topic
        FOREIGN KEY (topic) REFERENCES curriculum_topics(name)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 表：chemistry_question_bank_test（测试题库，结构与 chemistry_question_bank 保持一致）
CREATE TABLE IF NOT EXISTS chemistry_question_bank_test (
    question_id INT AUTO_INCREMENT PRIMARY KEY,
    type ENUM('choice', 'equation', 'experiment', 'calculation', 'inference', 'diagram') NOT NULL,
    difficulty ENUM('easy', 'medium', 'hard') NOT NULL DEFAULT 'easy',
    topic VARCHAR(50),
    unit_no TINYINT NULL COMMENT '教材单元编号，如 1-12',
    knowledge_tags VARCHAR(255) NULL COMMENT '知识点标签，逗号分隔',
    content TEXT NOT NULL,
    answer_schema JSON NOT NULL COMMENT '同 chemistry_question_bank.answer_schema',
    analysis TEXT NULL COMMENT '题目解析（冗余列，便于直接检索/展示）',
    hint TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_cqb_test_topic
        FOREIGN KEY (topic) REFERENCES curriculum_topics(name)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 表：chemistry_question_knowledge_points（化学题库题目-知识点映射，结构参考 question_knowledge_points）
CREATE TABLE IF NOT EXISTS chemistry_question_knowledge_points (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question_id INT NOT NULL,
    knowledge_point_id INT NOT NULL,
    weight INT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_cqkp_question
        FOREIGN KEY (question_id) REFERENCES chemistry_question_bank(question_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_cqkp_knowledge_point
        FOREIGN KEY (knowledge_point_id) REFERENCES knowledge_points(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 表：chemistry_question_knowledge_points_test（测试题库题目-知识点映射，结构与 chemistry_question_knowledge_points 一致）
CREATE TABLE IF NOT EXISTS chemistry_question_knowledge_points_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question_id INT NOT NULL,
    knowledge_point_id INT NOT NULL,
    weight INT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_cqkp_test_question
        FOREIGN KEY (question_id) REFERENCES chemistry_question_bank_test(question_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_cqkp_test_knowledge_point
        FOREIGN KEY (knowledge_point_id) REFERENCES knowledge_points(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 表：qa_sessions（与 backend 模型 QASession 对应，答疑会话记录）
CREATE TABLE IF NOT EXISTS qa_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    question TEXT NOT NULL,
    answer TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_qa_sessions_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 表：student_profiles（与 backend 模型 StudentProfile 对应，学生学习档案）
CREATE TABLE IF NOT EXISTS student_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    current_topic VARCHAR(80) NULL,
    learning_stage VARCHAR(50) NULL,
    total_study_time INT DEFAULT 0,
    total_questions_answered INT DEFAULT 0,
    last_active_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_student_profiles_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 表：student_knowledge_mastery（与 backend 模型 StudentKnowledgeMastery 对应，学生-知识点掌握度）
CREATE TABLE IF NOT EXISTS student_knowledge_mastery (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    knowledge_point_id INT NOT NULL,
    mastery_score INT NOT NULL DEFAULT 0,
    correct_count INT DEFAULT 0,
    wrong_count INT DEFAULT 0,
    weakness_weight INT DEFAULT 0,
    last_practiced_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_user_kp (user_id, knowledge_point_id),
    CONSTRAINT fk_skm_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_skm_knowledge_point
        FOREIGN KEY (knowledge_point_id) REFERENCES knowledge_points(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 表：question_knowledge_points（与 backend 模型 QuestionKnowledgePoint 对应，题目-知识点映射）
CREATE TABLE IF NOT EXISTS question_knowledge_points (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question_id INT NOT NULL,
    knowledge_point_id INT NOT NULL,
    weight INT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_qkp_question
        FOREIGN KEY (question_id) REFERENCES questions(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_qkp_knowledge_point
        FOREIGN KEY (knowledge_point_id) REFERENCES knowledge_points(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 表：daily_recommendations（与 backend 模型 DailyRecommendation 对应，每日一练推荐记录）
CREATE TABLE IF NOT EXISTS daily_recommendations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    question_id INT NOT NULL,
    recommend_date DATE NOT NULL,
    strategy VARCHAR(50) NULL,
    is_done INT DEFAULT 0,
    answer_record_id INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_dr_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_dr_question
        FOREIGN KEY (question_id) REFERENCES questions(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_dr_answer_record
        FOREIGN KEY (answer_record_id) REFERENCES answer_records(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 表：wrong_questions（错题集：用户在某题答错的聚合记录）
CREATE TABLE IF NOT EXISTS wrong_questions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    question_id INT NOT NULL,
    wrong_count INT DEFAULT 0,
    last_wrong_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_user_wrong (user_id, question_id),
    CONSTRAINT fk_wq_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_wq_question
        FOREIGN KEY (question_id) REFERENCES questions(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 表：question_variants（错题变式题映射：base_question -> variant_question）
CREATE TABLE IF NOT EXISTS question_variants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    base_question_id INT NOT NULL,
    variant_question_id INT NOT NULL,
    knowledge_point_id INT NULL,
    weight INT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_base_variant (base_question_id, variant_question_id),
    CONSTRAINT fk_qv_base_question
        FOREIGN KEY (base_question_id) REFERENCES questions(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_qv_variant_question
        FOREIGN KEY (variant_question_id) REFERENCES questions(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_qv_kp
        FOREIGN KEY (knowledge_point_id) REFERENCES knowledge_points(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ---------------------------------------------------------------------------
-- 增量补丁：已有库若缺列则补齐（CREATE TABLE IF NOT EXISTS 不会改旧表结构）
-- 可随 schema.sql 一并执行，重复执行安全
-- ---------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS _patch_ai_system_schema;
DELIMITER $$
CREATE PROCEDURE _patch_ai_system_schema()
BEGIN
    DECLARE n INT DEFAULT 0;

    SELECT COUNT(*) INTO n FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = 'ai_system' AND TABLE_NAME = 'chemistry_question_bank' AND COLUMN_NAME = 'knowledge_tags';
    IF n = 0 THEN
        ALTER TABLE chemistry_question_bank
            ADD COLUMN knowledge_tags VARCHAR(255) NULL COMMENT '知识点标签，逗号分隔' AFTER unit_no;
    END IF;

    SELECT COUNT(*) INTO n FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = 'ai_system' AND TABLE_NAME = 'chemistry_question_bank_test' AND COLUMN_NAME = 'knowledge_tags';
    IF n = 0 THEN
        ALTER TABLE chemistry_question_bank_test
            ADD COLUMN knowledge_tags VARCHAR(255) NULL COMMENT '知识点标签，逗号分隔' AFTER unit_no;
    END IF;

    SELECT COUNT(*) INTO n FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = 'ai_system' AND TABLE_NAME = 'chemistry_question_bank' AND COLUMN_NAME = 'analysis';
    IF n = 0 THEN
        ALTER TABLE chemistry_question_bank
            ADD COLUMN analysis TEXT NULL COMMENT '题目解析（冗余列，便于直接检索/展示）' AFTER answer_schema;
    END IF;

    SELECT COUNT(*) INTO n FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = 'ai_system' AND TABLE_NAME = 'chemistry_question_bank_test' AND COLUMN_NAME = 'analysis';
    IF n = 0 THEN
        ALTER TABLE chemistry_question_bank_test
            ADD COLUMN analysis TEXT NULL COMMENT '题目解析（冗余列，便于直接检索/展示）' AFTER answer_schema;
    END IF;
END$$
DELIMITER ;
CALL _patch_ai_system_schema();
DROP PROCEDURE IF EXISTS _patch_ai_system_schema;


-- 初始化数据请运行（在项目根目录）:
--   python scripts/seed_data.py