-- =============================================
-- EduNexus Pro - MySQL数据库建表脚本
-- 基于Model.ts生成
-- 版本: 1.0
-- 日期: 2025-11-25
-- =============================================

-- 设置字符集和时区
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET time_zone = '+08:00';

-- =============================================
-- 1. 身份与权限模块
-- =============================================

-- 用户表
DROP TABLE IF EXISTS `application_users`;
CREATE TABLE `application_users` (
    `id` VARCHAR(36) PRIMARY KEY COMMENT '用户ID (UUID)',
    `real_name` VARCHAR(50) NOT NULL COMMENT '真实姓名',
    `student_id` VARCHAR(20) NULL COMMENT '学号（学生）',
    `avatar_url` VARCHAR(500) NULL COMMENT '头像URL',
    `gender` ENUM('Male', 'Female') NOT NULL DEFAULT 'Male' COMMENT '性别',
    `current_school_id` VARCHAR(36) NULL COMMENT '当前所属学校ID',
    `account_status` ENUM('Active', 'Suspended', 'Graduated') NOT NULL DEFAULT 'Active' COMMENT '账号状态',
    `email` VARCHAR(100) NULL COMMENT '邮箱',
    `phone` VARCHAR(20) NULL COMMENT '手机号',
    `bio` TEXT NULL COMMENT '个人简介',
    `password_hash` VARCHAR(255) NOT NULL COMMENT '密码Hash',
    
    -- 审计字段
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `created_by` VARCHAR(36) NOT NULL COMMENT '创建人ID',
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `updated_by` VARCHAR(36) NOT NULL COMMENT '更新人ID',
    `is_deleted` BOOLEAN NOT NULL DEFAULT FALSE COMMENT '软删除标记',
    
    INDEX `idx_student_id` (`student_id`),
    INDEX `idx_email` (`email`),
    INDEX `idx_phone` (`phone`),
    INDEX `idx_school` (`current_school_id`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

-- =============================================
-- 2. 组织架构模块
-- =============================================

-- 学校表
DROP TABLE IF EXISTS `schools`;
CREATE TABLE `schools` (
    `id` VARCHAR(36) PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL COMMENT '学校名称',
    `region_code` VARCHAR(20) NOT NULL COMMENT '地区编码',
    `address` VARCHAR(200) NULL COMMENT '学校地址',
    
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` VARCHAR(36) NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updated_by` VARCHAR(36) NOT NULL,
    `is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
    
    INDEX `idx_region` (`region_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='学校表';

-- 年级表
DROP TABLE IF EXISTS `grades`;
CREATE TABLE `grades` (
    `id` VARCHAR(36) PRIMARY KEY,
    `school_id` VARCHAR(36) NOT NULL COMMENT '所属学校ID',
    `name` VARCHAR(50) NOT NULL COMMENT '年级名称',
    `sort_order` INT NOT NULL COMMENT '排序序号',
    `enrollment_year` INT NOT NULL COMMENT '入学年份',
    
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` VARCHAR(36) NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updated_by` VARCHAR(36) NOT NULL,
    `is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
    
    FOREIGN KEY (`school_id`) REFERENCES `schools`(`id`) ON DELETE RESTRICT,
    INDEX `idx_school_id` (`school_id`),
    INDEX `idx_enrollment_year` (`enrollment_year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='年级表';

-- 班级表
DROP TABLE IF EXISTS `classes`;
CREATE TABLE `classes` (
    `id` VARCHAR(36) PRIMARY KEY,
    `grade_id` VARCHAR(36) NOT NULL COMMENT '所属年级ID',
    `name` VARCHAR(50) NOT NULL COMMENT '班级名称',
    `invite_code` VARCHAR(10) NOT NULL UNIQUE COMMENT '邀请码',
    `head_teacher_id` VARCHAR(36) NULL COMMENT '班主任ID',
    
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` VARCHAR(36) NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updated_by` VARCHAR(36) NOT NULL,
    `is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
    
    FOREIGN KEY (`grade_id`) REFERENCES `grades`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`head_teacher_id`) REFERENCES `application_users`(`id`) ON DELETE SET NULL,
    UNIQUE INDEX `idx_invite_code` (`invite_code`),
    INDEX `idx_grade_id` (`grade_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='班级表';

-- 班级成员表
DROP TABLE IF EXISTS `class_members`;
CREATE TABLE `class_members` (
    `id` VARCHAR(36) PRIMARY KEY,
    `class_id` VARCHAR(36) NOT NULL COMMENT '班级ID',
    `user_id` VARCHAR(36) NOT NULL COMMENT '用户ID',
    `role_in_class` ENUM('Student', 'Monitor', 'Committee', 'Teacher') NOT NULL DEFAULT 'Student' COMMENT '班级角色',
    
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` VARCHAR(36) NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updated_by` VARCHAR(36) NOT NULL,
    `is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
    
    FOREIGN KEY (`class_id`) REFERENCES `classes`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `application_users`(`id`) ON DELETE CASCADE,
    UNIQUE INDEX `idx_class_user` (`class_id`, `user_id`),
    INDEX `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='班级成员表';

-- =============================================
-- 3. 教材与知识图谱模块
-- =============================================

-- 学科表
DROP TABLE IF EXISTS `subjects`;
CREATE TABLE `subjects` (
    `id` VARCHAR(36) PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL COMMENT '学科名称',
    `code` VARCHAR(20) NOT NULL UNIQUE COMMENT '学科代码',
    `icon` VARCHAR(50) NULL COMMENT '图标',
    
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` VARCHAR(36) NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updated_by` VARCHAR(36) NOT NULL,
    `is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
    
    INDEX `idx_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='学科表';

-- 教材表
DROP TABLE IF EXISTS `textbooks`;
CREATE TABLE `textbooks` (
    `id` VARCHAR(36) PRIMARY KEY,
    `subject_id` VARCHAR(36) NOT NULL COMMENT '所属学科ID',
    `name` VARCHAR(100) NOT NULL COMMENT '教材名称',
    `publisher` VARCHAR(100) NOT NULL COMMENT '出版社',
    `version_year` VARCHAR(20) NOT NULL COMMENT '版本年份',
    `cover_url` VARCHAR(500) NULL COMMENT '封面URL',
    
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` VARCHAR(36) NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updated_by` VARCHAR(36) NOT NULL,
    `is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
    
    FOREIGN KEY (`subject_id`) REFERENCES `subjects`(`id`) ON DELETE RESTRICT,
    INDEX `idx_subject_id` (`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='教材表';

-- 教材单元表
DROP TABLE IF EXISTS `textbook_units`;
CREATE TABLE `textbook_units` (
    `id` VARCHAR(36) PRIMARY KEY,
    `textbook_id` VARCHAR(36) NOT NULL COMMENT '所属教材ID',
    `name` VARCHAR(100) NOT NULL COMMENT '单元名称',
    `sort_order` INT NOT NULL COMMENT '排序序号',
    
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` VARCHAR(36) NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updated_by` VARCHAR(36) NOT NULL,
    `is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
    
    FOREIGN KEY (`textbook_id`) REFERENCES `textbooks`(`id`) ON DELETE CASCADE,
    INDEX `idx_textbook_id` (`textbook_id`),
    INDEX `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='教材单元表';

-- 教材课程表
DROP TABLE IF EXISTS `textbook_lessons`;
CREATE TABLE `textbook_lessons` (
    `id` VARCHAR(36) PRIMARY KEY,
    `unit_id` VARCHAR(36) NOT NULL COMMENT '所属单元ID',
    `name` VARCHAR(100) NOT NULL COMMENT '课名称',
    `sort_order` INT NOT NULL COMMENT '排序序号',
    
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` VARCHAR(36) NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updated_by` VARCHAR(36) NOT NULL,
    `is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
    
    FOREIGN KEY (`unit_id`) REFERENCES `textbook_units`(`id`) ON DELETE CASCADE,
    INDEX `idx_unit_id` (`unit_id`),
    INDEX `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='教材课程表';

-- 知识点表
DROP TABLE IF EXISTS `knowledge_points`;
CREATE TABLE `knowledge_points` (
    `id` VARCHAR(36) PRIMARY KEY,
    `lesson_id` VARCHAR(36) NOT NULL COMMENT '挂载课节ID',
    `parent_knowledge_point_id` VARCHAR(36) NULL COMMENT '父知识点ID',
    `name` VARCHAR(200) NOT NULL COMMENT '知识点名称',
    `difficulty` INT NOT NULL COMMENT '难度系数(1-5)',
    `description` TEXT NULL COMMENT '描述/口诀',
    
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` VARCHAR(36) NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updated_by` VARCHAR(36) NOT NULL,
    `is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
    
    FOREIGN KEY (`lesson_id`) REFERENCES `textbook_lessons`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`parent_knowledge_point_id`) REFERENCES `knowledge_points`(`id`) ON DELETE CASCADE,
    INDEX `idx_lesson_id` (`lesson_id`),
    INDEX `idx_parent_id` (`parent_knowledge_point_id`),
    INDEX `idx_difficulty` (`difficulty`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='知识点表';

-- =============================================
-- 4. 题库资源模块
-- =============================================

-- 题目表
DROP TABLE IF EXISTS `questions`;
CREATE TABLE `questions` (
    `id` VARCHAR(36) PRIMARY KEY,
    `subject_id` VARCHAR(36) NOT NULL COMMENT '所属学科ID',
    `content` TEXT NOT NULL COMMENT '题干内容(HTML)',
    `options_config` JSON NULL COMMENT '选项配置(JSON)',
    `question_type` ENUM('SingleChoice', 'MultipleChoice', 'TrueFalse', 'FillBlank', 'Subjective') NOT NULL COMMENT '题目类型',
    `answer` TEXT NOT NULL COMMENT '参考答案',
    `explanation` TEXT NULL COMMENT '答案解析',
    `difficulty` INT NOT NULL DEFAULT 3 COMMENT '难度(1-5)',
    
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` VARCHAR(36) NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updated_by` VARCHAR(36) NOT NULL,
    `is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
    
    FOREIGN KEY (`subject_id`) REFERENCES `subjects`(`id`) ON DELETE RESTRICT,
    INDEX `idx_subject_id` (`subject_id`),
    INDEX `idx_question_type` (`question_type`),
    INDEX `idx_difficulty` (`difficulty`),
    FULLTEXT INDEX `ft_content` (`content`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='题目表';

-- 题目-知识点关联表
DROP TABLE IF EXISTS `question_knowledge`;
CREATE TABLE `question_knowledge` (
    `id` VARCHAR(36) PRIMARY KEY,
    `question_id` VARCHAR(36) NOT NULL COMMENT '题目ID',
    `knowledge_point_id` VARCHAR(36) NOT NULL COMMENT '知识点ID',
    `weight` INT NOT NULL DEFAULT 100 COMMENT '考察权重(0-100)',
    
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` VARCHAR(36) NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updated_by` VARCHAR(36) NOT NULL,
    `is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
    
    FOREIGN KEY (`question_id`) REFERENCES `questions`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`knowledge_point_id`) REFERENCES `knowledge_points`(`id`) ON DELETE CASCADE,
    UNIQUE INDEX `idx_question_knowledge` (`question_id`, `knowledge_point_id`),
    INDEX `idx_knowledge_id` (`knowledge_point_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='题目知识点关联表';

-- =============================================
-- 5. 试卷工程模块
-- =============================================

-- 试卷表
DROP TABLE IF EXISTS `exams`;
CREATE TABLE `exams` (
    `id` VARCHAR(36) PRIMARY KEY,
    `subject_id` VARCHAR(36) NOT NULL COMMENT '所属学科ID',
    `title` VARCHAR(200) NOT NULL COMMENT '试卷标题',
    `total_score` DECIMAL(5,1) NOT NULL DEFAULT 0 COMMENT '总分',
    `suggested_duration` INT NOT NULL COMMENT '建议时长(分钟)',
    `status` ENUM('Draft', 'Published') NOT NULL DEFAULT 'Draft' COMMENT '状态',
    
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` VARCHAR(36) NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updated_by` VARCHAR(36) NOT NULL,
    `is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
    
    FOREIGN KEY (`subject_id`) REFERENCES `subjects`(`id`) ON DELETE RESTRICT,
    INDEX `idx_subject_id` (`subject_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='试卷表';

-- 试卷节点表(树形结构)
DROP TABLE IF EXISTS `exam_nodes`;
CREATE TABLE `exam_nodes` (
    `id` VARCHAR(36) PRIMARY KEY,
    `exam_id` VARCHAR(36) NOT NULL COMMENT '所属试卷ID',
    `parent_node_id` VARCHAR(36) NULL COMMENT '父节点ID',
    `node_type` ENUM('Group', 'Question') NOT NULL COMMENT '节点类型',
    `question_id` VARCHAR(36) NULL COMMENT '题目ID(Question节点)',
    `title` VARCHAR(200) NULL COMMENT '标题(Group节点)',
    `description` TEXT NULL COMMENT '描述(Group节点)',
    `score` DECIMAL(5,1) NOT NULL COMMENT '分数',
    `sort_order` INT NOT NULL COMMENT '排序序号',
    
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` VARCHAR(36) NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updated_by` VARCHAR(36) NOT NULL,
    `is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
    
    FOREIGN KEY (`exam_id`) REFERENCES `exams`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`parent_node_id`) REFERENCES `exam_nodes`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`question_id`) REFERENCES `questions`(`id`) ON DELETE RESTRICT,
    INDEX `idx_exam_id` (`exam_id`),
    INDEX `idx_parent_node_id` (`parent_node_id`),
    INDEX `idx_sort_order` (`sort_order`),
    INDEX `idx_question_id` (`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='试卷节点表';

-- =============================================
-- 6. 教学执行模块
-- =============================================

-- 作业表
DROP TABLE IF EXISTS `assignments`;
CREATE TABLE `assignments` (
    `id` VARCHAR(36) PRIMARY KEY,
    `exam_id` VARCHAR(36) NOT NULL COMMENT '关联试卷ID',
    `class_id` VARCHAR(36) NOT NULL COMMENT '目标班级ID',
    `title` VARCHAR(200) NOT NULL COMMENT '作业标题',
    `start_time` DATETIME NOT NULL COMMENT '开始时间',
    `end_time` DATETIME NOT NULL COMMENT '截止时间',
    `allow_late_submission` BOOLEAN NOT NULL DEFAULT FALSE COMMENT '允许迟交',
    `auto_score_enabled` BOOLEAN NOT NULL DEFAULT TRUE COMMENT '自动判分',
    
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` VARCHAR(36) NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updated_by` VARCHAR(36) NOT NULL,
    `is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
    
    FOREIGN KEY (`exam_id`) REFERENCES `exams`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`class_id`) REFERENCES `classes`(`id`) ON DELETE RESTRICT,
    INDEX `idx_exam_id` (`exam_id`),
    INDEX `idx_class_id` (`class_id`),
    INDEX `idx_start_time` (`start_time`),
    INDEX `idx_end_time` (`end_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='作业表';

-- 学生提交表
DROP TABLE IF EXISTS `student_submissions`;
CREATE TABLE `student_submissions` (
    `id` VARCHAR(36) PRIMARY KEY,
    `assignment_id` VARCHAR(36) NOT NULL COMMENT '作业ID',
    `student_id` VARCHAR(36) NOT NULL COMMENT '学生ID',
    `submission_status` ENUM('Pending', 'Submitted', 'Grading', 'Graded') NOT NULL DEFAULT 'Pending' COMMENT '提交状态',
    `submit_time` DATETIME NULL COMMENT '提交时间',
    `time_spent_seconds` INT NULL COMMENT '耗时(秒)',
    `total_score` DECIMAL(5,1) NULL COMMENT '总得分',
    
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` VARCHAR(36) NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updated_by` VARCHAR(36) NOT NULL,
    `is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
    
    FOREIGN KEY (`assignment_id`) REFERENCES `assignments`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`student_id`) REFERENCES `application_users`(`id`) ON DELETE CASCADE,
    UNIQUE INDEX `idx_assignment_student` (`assignment_id`, `student_id`),
    INDEX `idx_student_id` (`student_id`),
    INDEX `idx_submit_time` (`submit_time`),
    INDEX `idx_status` (`submission_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='学生提交表';

-- 答题详情表
DROP TABLE IF EXISTS `submission_details`;
CREATE TABLE `submission_details` (
    `id` VARCHAR(36) PRIMARY KEY,
    `submission_id` VARCHAR(36) NOT NULL COMMENT '提交ID',
    `exam_node_id` VARCHAR(36) NOT NULL COMMENT '试卷节点ID',
    `student_answer` TEXT NULL COMMENT '学生答案',
    `grading_data` JSON NULL COMMENT '批改数据(Canvas JSON)',
    `score` DECIMAL(5,1) NULL COMMENT '得分',
    `judgement` ENUM('Correct', 'Incorrect', 'Partial') NULL COMMENT '判题结果',
    `teacher_comment` TEXT NULL COMMENT '老师评语',
    
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` VARCHAR(36) NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updated_by` VARCHAR(36) NOT NULL,
    `is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
    
    FOREIGN KEY (`submission_id`) REFERENCES `student_submissions`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`exam_node_id`) REFERENCES `exam_nodes`(`id`) ON DELETE RESTRICT,
    UNIQUE INDEX `idx_submission_node` (`submission_id`, `exam_node_id`),
    INDEX `idx_node_id` (`exam_node_id`),
    INDEX `idx_judgement` (`judgement`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='答题详情表';

-- =============================================
-- 完成
-- =============================================

SET FOREIGN_KEY_CHECKS = 1;

-- 显示创建的所有表
SHOW TABLES;