ai_english/serve/migrations/create_test_tables.sql

-- 创建测试相关表
-- Create test-related tables

-- 测试模板表
CREATE TABLE IF NOT EXISTS test_templates (
    id VARCHAR(36) PRIMARY KEY,
    title VARCHAR(255) NOT NULL COMMENT '模板标题',
    description TEXT COMMENT '模板描述',
    type VARCHAR(50) NOT NULL COMMENT '测试类型: quick, comprehensive, daily, custom',
    difficulty VARCHAR(50) COMMENT '难度: beginner, intermediate, advanced',
    duration INT COMMENT '测试时长(秒)',
    total_questions INT COMMENT '总题目数',
    passing_score INT COMMENT '及格分数',
    max_score INT COMMENT '最高分数',
    question_config JSON COMMENT '题目配置',
    skill_distribution JSON COMMENT '技能分布',
    is_active BOOLEAN DEFAULT TRUE COMMENT '是否启用',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_type (type),
    INDEX idx_difficulty (difficulty),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='测试模板表';

-- 测试题目表
CREATE TABLE IF NOT EXISTS test_questions (
    id VARCHAR(36) PRIMARY KEY,
    template_id VARCHAR(36) NOT NULL COMMENT '模板ID',
    question_type VARCHAR(50) NOT NULL COMMENT '题目类型: single_choice, multiple_choice, true_false, fill_blank, short_answer',
    skill_type VARCHAR(50) NOT NULL COMMENT '技能类型: vocabulary, grammar, reading, listening, speaking, writing',
    difficulty VARCHAR(50) COMMENT '难度',
    content TEXT NOT NULL COMMENT '题目内容',
    options JSON COMMENT '选项(JSON数组)',
    correct_answer TEXT COMMENT '正确答案',
    explanation TEXT COMMENT '答案解析',
    points INT DEFAULT 1 COMMENT '分值',
    order_index INT COMMENT '题目顺序',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_template_id (template_id),
    INDEX idx_question_type (question_type),
    INDEX idx_skill_type (skill_type),
    INDEX idx_difficulty (difficulty),
    FOREIGN KEY (template_id) REFERENCES test_templates(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='测试题目表';

-- 测试会话表
CREATE TABLE IF NOT EXISTS test_sessions (
    id VARCHAR(36) PRIMARY KEY,
    template_id VARCHAR(36) NOT NULL COMMENT '模板ID',
    user_id VARCHAR(36) NOT NULL COMMENT '用户ID',
    status VARCHAR(50) NOT NULL DEFAULT 'pending' COMMENT '状态: pending, in_progress, paused, completed, abandoned',
    start_time TIMESTAMP NULL COMMENT '开始时间',
    end_time TIMESTAMP NULL COMMENT '结束时间',
    paused_at TIMESTAMP NULL COMMENT '暂停时间',
    time_remaining INT COMMENT '剩余时间(秒)',
    current_question_index INT DEFAULT 0 COMMENT '当前题目索引',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_template_id (template_id),
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (template_id) REFERENCES test_templates(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='测试会话表';

-- 测试会话题目关联表
CREATE TABLE IF NOT EXISTS test_session_questions (
    session_id VARCHAR(36) NOT NULL COMMENT '会话ID',
    question_id VARCHAR(36) NOT NULL COMMENT '题目ID',
    order_index INT COMMENT '题目顺序',
    PRIMARY KEY (session_id, question_id),
    INDEX idx_session_id (session_id),
    INDEX idx_question_id (question_id),
    FOREIGN KEY (session_id) REFERENCES test_sessions(id) ON DELETE CASCADE,
    FOREIGN KEY (question_id) REFERENCES test_questions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='测试会话题目关联表';

-- 测试答案表
CREATE TABLE IF NOT EXISTS test_answers (
    id VARCHAR(36) PRIMARY KEY,
    session_id VARCHAR(36) NOT NULL COMMENT '会话ID',
    question_id VARCHAR(36) NOT NULL COMMENT '题目ID',
    answer TEXT COMMENT '用户答案',
    is_correct BOOLEAN COMMENT '是否正确',
    score INT DEFAULT 0 COMMENT '得分',
    time_spent INT COMMENT '答题用时(秒)',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_session_id (session_id),
    INDEX idx_question_id (question_id),
    UNIQUE KEY uk_session_question (session_id, question_id),
    FOREIGN KEY (session_id) REFERENCES test_sessions(id) ON DELETE CASCADE,
    FOREIGN KEY (question_id) REFERENCES test_questions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='测试答案表';

-- 测试结果表
CREATE TABLE IF NOT EXISTS test_results (
    id VARCHAR(36) PRIMARY KEY,
    session_id VARCHAR(36) NOT NULL UNIQUE COMMENT '会话ID',
    user_id VARCHAR(36) NOT NULL COMMENT '用户ID',
    template_id VARCHAR(36) NOT NULL COMMENT '模板ID',
    total_score INT COMMENT '总得分',
    max_score INT COMMENT '最高分',
    percentage DECIMAL(5,2) COMMENT '得分百分比',
    correct_count INT COMMENT '正确题数',
    wrong_count INT COMMENT '错误题数',
    skipped_count INT COMMENT '跳过题数',
    time_spent INT COMMENT '总用时(秒)',
    skill_scores JSON COMMENT '各技能得分',
    passed BOOLEAN COMMENT '是否通过',
    completed_at TIMESTAMP NOT NULL COMMENT '完成时间',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_session_id (session_id),
    INDEX idx_user_id (user_id),
    INDEX idx_template_id (template_id),
    INDEX idx_completed_at (completed_at),
    FOREIGN KEY (session_id) REFERENCES test_sessions(id) ON DELETE CASCADE,
    FOREIGN KEY (template_id) REFERENCES test_templates(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='测试结果表';