DROP TABLE IF EXISTS `am_question`;
CREATE TABLE `am_question` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `answer` longtext COLLATE utf8mb4_bin NOT NULL,
  `content` longtext COLLATE utf8mb4_bin NOT NULL,
  `exam_id` bigint NOT NULL,
  `full_score` double NOT NULL,
  `image_slice` varchar(1000) COLLATE utf8mb4_bin NOT NULL,
  `main_number` int NOT NULL,
  `sub_number` int NOT NULL,
  `subject_code` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `subject_name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_QUESTION_01` (`exam_id`, `subject_code`, `main_number`, `sub_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

DROP TABLE IF EXISTS `am_student`;
CREATE TABLE `am_student` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `data_status` varchar(255) NOT NULL,
  `exam_id` bigint NOT NULL,
  `student_code` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `subject_code` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `err_msg` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_STUDENT_01` (`exam_id`, `subject_code`, `student_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

DROP TABLE IF EXISTS `am_student_score`;
CREATE TABLE `am_student_score` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `ai_score` double DEFAULT NULL,
  `answer_status` varchar(255) NOT NULL,
  `score_status` varchar(255) NOT NULL,
  `answer` longtext COLLATE utf8mb4_bin DEFAULT NULL,
  `err_msg` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL,
  `main_number` int NOT NULL,
  `marking_score` double DEFAULT NULL,
  `score_ratio` double DEFAULT NULL,
  `student_id` bigint NOT NULL,
  `question_id` bigint NOT NULL,
  `exam_id` bigint NOT NULL,
  `student_code` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `subject_code` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `sub_number` 	 varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_STUDENT_SCORE_01` (`student_id`, `question_id`),
  UNIQUE KEY `IDX_STUDENT_SCORE_02` (`exam_id`, `subject_code`, `student_code`,`main_number`, `sub_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
