CREATE DATABASE eai CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `eai`.`users` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'User唯一标识（自增）',
  `name` varchar(50) NOT NULL COMMENT '真实姓名（必填）',
  `official_email` varchar(100) NOT NULL COMMENT '南大官邮，xxx@smail.nju.edu.cn（必填）',
  `official_number` varchar(20) NOT NULL COMMENT '南大学号、工号（必填）',
  `role` tinyint NOT NULL COMMENT '用户角色：0-ADMIN, 1-STUDENT, 2-TEACHER（对应枚举code）',
  `user_avatar` varchar(255) DEFAULT 'default_avatar_url' COMMENT '头像URL（有默认值、选填）',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间，系统自动设置',
  `last_login_time` datetime COMMENT '上一次登陆时间（可选）',
  `english_name` varchar(50) COMMENT '英文名（选填）',
  `gender` tinyint COMMENT '性别：0-MAN, 1-WOMAN（对应枚举code）',
  `birthday` date COMMENT '生日（选填）',
  `phone` varchar(20) COMMENT '联系方式（选填）',
  `content_email` varchar(100) COMMENT '联系邮箱（选填）',
  `pid` varchar(50) COMMENT '门户id',
  `password` varchar(100) NOT NULL COMMENT '密码（加密存储）（必填）',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

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

CREATE TABLE IF NOT EXISTS `eai`.`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 COMMENT '原始内容',
  `sentence` text NOT NULL COMMENT '待评估句子',
  `evaluation` text COMMENT '评估结果',
  `version` int DEFAULT 1 COMMENT '版本号',
  `type` varchar(50) COMMENT '句子类型',
  `category` varchar(50) COMMENT '句子类别',
  PRIMARY KEY (`id`),
  KEY `idx_student_assignment` (`student_id`, `assignment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='句子评估表';

CREATE TABLE IF NOT EXISTS `eai`.`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 COMMENT '作业内容',
  `version` int NOT NULL DEFAULT 1 COMMENT '批改版本（跟随作业版本）',
  `evaluation` text COMMENT '整体评估结果',
  PRIMARY KEY (`id`),
  KEY `idx_student_assignment` (`student_id`, `assignment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='作业整体评估表';

CREATE TABLE IF NOT EXISTS `eai`.`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) NOT NULL COMMENT '评价人',
  `overall` int COMMENT '整体评分',
  `sentence` int COMMENT '句子评分',
  PRIMARY KEY (`id`),
  KEY `idx_student_assignment` (`student_id`, `assignment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='作业评价表';

CREATE TABLE IF NOT EXISTS `eai`.`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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='学生选课关联表';

CREATE TABLE IF NOT EXISTS `eai`.`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) COMMENT '作业得分（百分制）',
  `remark` varchar(500) COMMENT '教师评语',
  `file_url` varchar(255) COMMENT '作业文件OSS链接',
  `version` int NOT NULL DEFAULT 1 COMMENT '文件版本（用于onlyoffice key构建）',
  `status` varchar(20) NOT NULL DEFAULT 'UNSUBMITTED' COMMENT '完成状态：UNSUBMITTED/SUBMITTED/GRADED',
  `quill_content` text COMMENT 'Quill富文本内容',
  `text_content` text COMMENT '纯文本内容',
  `html_content` text COMMENT 'HTML格式内容',
  PRIMARY KEY (`id`),
  KEY `idx_student_assignment` (`student_id`, `assignment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='学生作业关联表';


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

CREATE TABLE IF NOT EXISTS `eai`.`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) NOT NULL COMMENT '行为记录文件URL',
  `time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`id`),
  KEY `idx_student_assignment` (`student_id`, `assignment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='学生学习行为记录表';

CREATE TABLE IF NOT EXISTS `eai`.`assignment` (
  `assignment_id` bigint NOT NULL AUTO_INCREMENT COMMENT '作业ID（自增主键）',
  `course_id` bigint NOT NULL COMMENT '所属课程ID（外键）',
  `teacher_portal_id` varchar(50) COMMENT '教师门户ID',
  `assignment_name` varchar(100) NOT NULL COMMENT '作业名称',
  `description` text COMMENT '作业描述',
  `type` varchar(30) NOT NULL COMMENT '作业类型（如HOMEWORK/QUIZ/EXAM等）',
  `description_file` varchar(255) COMMENT '作业要求文件URL',
  `attachments` text 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 '创建时间',
  PRIMARY KEY (`assignment_id`),
  KEY `idx_time_range` (`start_time`, `end_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='课程作业表';

CREATE TABLE IF NOT EXISTS `eai`.`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 COMMENT '调查问卷链接（JSON格式）',
  `survey_duration` int COMMENT '问卷预计完成时间（秒）',
  PRIMARY KEY (`ai_speaking_assignment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='AI口语专项作业表';