EAI-Backend/eai.sql

/*
 Navicat Premium Data Transfer

 Source Server         : online_eai
 Source Server Type    : MySQL
 Source Server Version : 80027
 Source Host           : 8.130.28.19:3306
 Source Schema         : eai

 Target Server Type    : MySQL
 Target Server Version : 80027
 File Encoding         : 65001

 Date: 18/08/2025 15:30:09
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for ai_speaking_assignment
-- ----------------------------
DROP TABLE IF EXISTS `ai_speaking_assignment`;
CREATE TABLE `ai_speaking_assignment`  (
  `ai_speaking_assignment_id` bigint NOT NULL AUTO_INCREMENT COMMENT 'AI口语作业ID（自增主键）',
  `assignment_id` bigint NOT NULL COMMENT '关联的基础作业ID',
  `min_duration` int NOT NULL DEFAULT 60 COMMENT '最小持续时间（秒）',
  `min_interaction` int NOT NULL DEFAULT 5 COMMENT '最小交互次数',
  `survey_links` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '调查问卷链接（JSON格式）',
  `survey_duration` int NULL DEFAULT NULL COMMENT '问卷预计完成时间（秒）',
  PRIMARY KEY (`ai_speaking_assignment_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 35 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'AI口语专项作业表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for assignment
-- ----------------------------
DROP TABLE IF EXISTS `assignment`;
CREATE TABLE `assignment`  (
  `assignment_id` bigint NOT NULL AUTO_INCREMENT COMMENT '作业ID（自增主键）',
  `course_id` bigint NOT NULL COMMENT '所属课程ID（外键）',
  `assignment_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '作业名称',
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '作业描述',
  `type` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '作业类型（如HOMEWORK/QUIZ/EXAM等）',
  `description_file` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '作业要求文件URL',
  `attachments` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '附件URL列表（JSON格式）',
  `teacher_id` bigint NOT NULL COMMENT '发布教师ID',
  `start_time` datetime NOT NULL COMMENT '开始时间',
  `end_time` datetime NOT NULL COMMENT '截止时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `teacher_portal_id` bigint NULL DEFAULT NULL,
  `correct_number` int NULL DEFAULT 0,
  `engage_number` int NULL DEFAULT 0,
  `exam_mode` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否为考试模式：0-普通作业，1-考试模式',
  `exam_duration` int NULL DEFAULT NULL COMMENT '考试时长（分钟），仅考试模式有效',
  PRIMARY KEY (`assignment_id`) USING BTREE,
  INDEX `idx_time_range`(`start_time`, `end_time`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 79 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '课程作业表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for behavior_record
-- ----------------------------
DROP TABLE IF EXISTS `behavior_record`;
CREATE TABLE `behavior_record`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '行为记录ID（自增主键）',
  `student_id` bigint NOT NULL COMMENT '学生ID',
  `assignment_id` bigint NOT NULL COMMENT '作业ID',
  `record_file_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '行为记录文件URL',
  `time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_student_assignment`(`student_id`, `assignment_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 76 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '学生学习行为记录表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class`  (
  `class_id` bigint NOT NULL AUTO_INCREMENT,
  `class_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `class_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `course_id` bigint NOT NULL,
  `stu_number` int NULL DEFAULT NULL,
  `teacher_id` bigint NULL DEFAULT NULL,
  `class_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '班级码',
  PRIMARY KEY (`class_id`) USING BTREE,
  INDEX `idx_class_course`(`course_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 37 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for class_student
-- ----------------------------
DROP TABLE IF EXISTS `class_student`;
CREATE TABLE `class_student`  (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `class_id` bigint NOT NULL,
  `official_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `state` int NULL DEFAULT NULL,
  `stu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_class_student_class`(`class_id`) USING BTREE,
  INDEX `idx_class_student_official`(`official_number`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 38 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `course_id` bigint NOT NULL AUTO_INCREMENT COMMENT '课程ID（自增主键）',
  `course_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '课程名称',
  `teacher_ids` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '教师ID列表（JSON序列化字符串）',
  `teacher_portal_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '教师门户ID',
  `enroll_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '选课码（唯一）',
  `course_imgs` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '课程图片列表（JSON序列化字符串）',
  `target_grade` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '目标年级',
  `start_time` datetime NOT NULL COMMENT '课程开始时间',
  `end_time` datetime NOT NULL COMMENT '课程结束时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '课程描述',
  `course_images` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  PRIMARY KEY (`course_id`) USING BTREE,
  INDEX `idx_time_range`(`start_time`, `end_time`) USING BTREE,
  INDEX `idx_course_teacher_pid`(`teacher_portal_id`) USING BTREE,
  INDEX `idx_course_time_range`(`start_time`, `end_time`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 40 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '课程信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for course_student
-- ----------------------------
DROP TABLE IF EXISTS `course_student`;
CREATE TABLE `course_student`  (
  `enrollment_id` bigint NOT NULL AUTO_INCREMENT COMMENT '选课记录ID（自增主键）',
  `course_id` bigint NOT NULL COMMENT '课程ID',
  `student_id` bigint NOT NULL COMMENT '学生ID',
  PRIMARY KEY (`enrollment_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 57 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '学生选课关联表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for evaluation
-- ----------------------------
DROP TABLE IF EXISTS `evaluation`;
CREATE TABLE `evaluation`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '评价ID（自增主键）',
  `student_id` bigint NOT NULL COMMENT '学生ID',
  `assignment_id` bigint NOT NULL COMMENT '作业ID',
  `version` int NOT NULL DEFAULT 1 COMMENT '评价版本号',
  `time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '评价时间',
  `author` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '评价人',
  `overall` int NULL DEFAULT NULL COMMENT '整体评分',
  `sentence` int NULL DEFAULT NULL COMMENT '句子评分',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_student_assignment`(`student_id`, `assignment_id`) USING BTREE,
  UNIQUE INDEX `uk_evaluation_assignment_student_version`(`assignment_id`, `student_id`, `version`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '作业评价表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for general_log
-- ----------------------------
DROP TABLE IF EXISTS `general_log`;
CREATE TABLE `general_log`  (
  `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `user_host` mediumtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `thread_id` bigint UNSIGNED NOT NULL,
  `server_id` int UNSIGNED NOT NULL,
  `command_type` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `argument` mediumblob NOT NULL
) ENGINE = CSV CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'General log' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for overall_evaluation
-- ----------------------------
DROP TABLE IF EXISTS `overall_evaluation`;
CREATE TABLE `overall_evaluation`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID（自增）',
  `student_id` bigint NOT NULL COMMENT '学生ID',
  `assignment_id` bigint NOT NULL COMMENT '作业ID',
  `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '作业内容',
  `version` int NOT NULL DEFAULT 1 COMMENT '批改版本（跟随作业版本）',
  `evaluation` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '整体评估结果',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_student_assignment`(`student_id`, `assignment_id`) USING BTREE,
  UNIQUE INDEX `uk_overall_eval_assignment_student_version`(`assignment_id`, `student_id`, `version`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 20 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '作业整体评估表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for phone_verification
-- ----------------------------
DROP TABLE IF EXISTS `phone_verification`;
CREATE TABLE `phone_verification`  (
  `id` bigint NOT NULL,
  `code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `code_time` datetime(6) NULL DEFAULT NULL,
  `phone` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `UK_1ebndp9bn7nuiyqujcmeifojg`(`phone`) USING BTREE,
  INDEX `verification_phone_index`(`phone`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for phone_verification_seq
-- ----------------------------
DROP TABLE IF EXISTS `phone_verification_seq`;
CREATE TABLE `phone_verification_seq`  (
  `next_val` bigint NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for sentence_evaluation
-- ----------------------------
DROP TABLE IF EXISTS `sentence_evaluation`;
CREATE TABLE `sentence_evaluation`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID（自增）',
  `student_id` bigint NOT NULL COMMENT '学生ID',
  `assignment_id` bigint NOT NULL COMMENT '作业ID',
  `no` int NOT NULL COMMENT '句子编号',
  `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '原始内容',
  `sentence` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '待评估句子',
  `evaluation` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '评估结果',
  `version` int NULL DEFAULT 1 COMMENT '版本号',
  `type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '句子类型',
  `category` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '句子类别',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_student_assignment`(`student_id`, `assignment_id`) USING BTREE,
  UNIQUE INDEX `uk_sentence_eval_assignment_student_version_no`(`assignment_id`, `student_id`, `version`, `no`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 74 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '句子评估表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Migration notes (for existing databases)
-- ----------------------------
-- If your production DB already contains duplicate rows, the UNIQUE indexes above will fail to create.
-- Recommended dedup strategy before adding UNIQUE indexes:
--
-- 1) evaluation: keep latest by time
--   DELETE e1 FROM evaluation e1
--   JOIN evaluation e2
--     ON e1.assignment_id=e2.assignment_id AND e1.student_id=e2.student_id AND e1.version=e2.version
--    AND e1.id < e2.id;
--
-- 2) overall_evaluation: keep latest row per (assignment_id, student_id, version)
--   DELETE o1 FROM overall_evaluation o1
--   JOIN overall_evaluation o2
--     ON o1.assignment_id=o2.assignment_id AND o1.student_id=o2.student_id AND o1.version=o2.version
--    AND o1.id < o2.id;
--
-- 3) sentence_evaluation: keep latest row per (assignment_id, student_id, version, no)
--   DELETE s1 FROM sentence_evaluation s1
--   JOIN sentence_evaluation s2
--     ON s1.assignment_id=s2.assignment_id AND s1.student_id=s2.student_id AND s1.version=s2.version AND s1.no=s2.no
--    AND s1.id < s2.id;

-- ----------------------------
-- Table structure for sign_records
-- ----------------------------
DROP TABLE IF EXISTS `sign_records`;
CREATE TABLE `sign_records`  (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `official_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `sign_in_time` datetime(6) NULL DEFAULT NULL,
  `sign_status` enum('SIGNED','NOT_SIGNED','FINISHED') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `sign_task_id` bigint NOT NULL,
  `student_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_sign_record_task`(`sign_task_id`) USING BTREE,
  INDEX `idx_sign_record_official_task`(`official_number`, `sign_task_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for sign_tasks
-- ----------------------------
DROP TABLE IF EXISTS `sign_tasks`;
CREATE TABLE `sign_tasks`  (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `class_id` bigint NOT NULL,
  `course_id` bigint NOT NULL,
  `create_time` timestamp NULL DEFAULT NULL,
  `end_time` timestamp NULL DEFAULT NULL,
  `sign_code` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `sign_type` enum('NUMBER','QR') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `signed_number` int NULL DEFAULT NULL,
  `status` enum('PROCESSING','FINISHED') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `teacher_id` bigint NOT NULL,
  `total_number` int NULL DEFAULT NULL,
  `qr_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `time` int NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_sign_task_teacher`(`teacher_id`) USING BTREE,
  INDEX `idx_sign_task_class`(`class_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for slow_log
-- ----------------------------
DROP TABLE IF EXISTS `slow_log`;
CREATE TABLE `slow_log`  (
  `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `user_host` mediumtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `query_time` time(6) NOT NULL,
  `lock_time` time(6) NOT NULL,
  `rows_sent` int NOT NULL,
  `rows_examined` int NOT NULL,
  `db` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `last_insert_id` int NOT NULL,
  `insert_id` int NOT NULL,
  `server_id` int UNSIGNED NOT NULL,
  `sql_text` mediumblob NOT NULL,
  `thread_id` bigint UNSIGNED NOT NULL
) ENGINE = CSV CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'Slow log' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for student_assignment
-- ----------------------------
DROP TABLE IF EXISTS `student_assignment`;
CREATE TABLE `student_assignment`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID（自增）',
  `assignment_id` bigint NOT NULL COMMENT '作业ID',
  `student_id` bigint NOT NULL COMMENT '学生ID',
  `score` decimal(5, 2) NULL DEFAULT NULL COMMENT '作业得分（百分制）',
  `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '教师评语',
  `file_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '作业文件OSS链接',
  `version` int NOT NULL DEFAULT 1 COMMENT '文件版本（用于onlyoffice key构建）',
  `status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'UNSUBMITTED' COMMENT '完成状态：UNSUBMITTED/SUBMITTED/GRADED',
  `quill_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT 'Quill富文本内容',
  `text_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '纯文本内容',
  `html_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT 'HTML格式内容',
  `submitted` tinyint(1) NULL DEFAULT 0 COMMENT '是否已交卷：0-未交卷，1-已交卷',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_assignment_student_latest`(`assignment_id`, `student_id`) USING BTREE,
  INDEX `idx_student_assignment`(`student_id`, `assignment_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 95 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '学生作业关联表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for student_assignment_history
-- ----------------------------
DROP TABLE IF EXISTS `student_assignment_history`;
CREATE TABLE `student_assignment_history`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID（自增）',
  `assignment_id` bigint NOT NULL COMMENT '作业ID',
  `student_id` bigint NOT NULL COMMENT '学生ID',
  `version` int NOT NULL COMMENT '提交版本号',
  `status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '提交状态',
  `quill_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT 'Quill富文本内容',
  `text_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '纯文本内容',
  `html_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT 'HTML格式内容',
  `submit_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '提交时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_assignment_student_version`(`assignment_id`, `student_id`, `version`) USING BTREE,
  INDEX `idx_assignment_student_submit_time`(`assignment_id`, `student_id`, `submit_time`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '学生作业历史版本表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for translations
-- ----------------------------
DROP TABLE IF EXISTS `translations`;
CREATE TABLE `translations`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '翻译记录唯一标识（自增）',
  `user_id` bigint NOT NULL COMMENT '关联用户ID',
  `assignment_id` bigint NULL DEFAULT NULL COMMENT '关联任务ID（可选）',
  `query_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '待翻译原文',
  `query_result` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '翻译结果',
  `target_language` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '目标语言类型（如zh/en等）',
  `query_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '翻译请求时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_user_id`(`user_id`) USING BTREE,
  INDEX `idx_assignment_id`(`assignment_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '翻译记录表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'User唯一标识（自增）',
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '真实姓名（必填）',
  `official_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '南大官邮，xxx@smail.nju.edu.cn（必填）',
  `official_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '南大学号、工号（必填）',
  `role` smallint NULL DEFAULT NULL,
  `user_avatar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT 'default_avatar_url' COMMENT '头像URL（有默认值、选填）',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间，系统自动设置',
  `last_login_time` datetime NULL DEFAULT NULL COMMENT '上一次登陆时间（可选）',
  `english_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '英文名（选填）',
  `gender` smallint NULL DEFAULT NULL,
  `birthday` date NULL DEFAULT NULL COMMENT '生日（选填）',
  `phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '联系方式（选填）',
  `content_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '联系邮箱（选填）',
  `pid` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '门户id',
  `password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码（加密存储）（必填）',
  `contact_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 77 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '用户表' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;