CREATE DATABASE IF NOT EXISTS ai_marking_db default character set utf8mb4 collate utf8mb4_general_ci;

CREATE USER IF NOT EXISTS 'ai_marking'@'%' IDENTIFIED BY 'ai_marking';

GRANT ALL PRIVILEGES  ON `ai_marking_db`.* TO 'ai_marking'@'localhost' IDENTIFIED BY 'ai_marking';

FLUSH  PRIVILEGES;

USE ai_marking_db;

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,
  `exam_id` bigint NOT NULL,
  `subject_code` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `subject_name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `main_number` int NOT NULL,
  `sub_number` int NOT NULL,
  `full_score` double NOT NULL,
  `answer` longtext COLLATE utf8mb4_bin DEFAULT NULL,
  `content` longtext COLLATE utf8mb4_bin NOT NULL,
  `image_slice` varchar(1000) 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,
  `exam_id` bigint NOT NULL,
  `subject_code` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `student_code` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `data_status` varchar(255) NOT NULL,
  `err_msg` varchar(2000) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_STUDENT_01` (`exam_id`, `subject_code`, `student_code`),
  KEY `IDX_STUDENT_02` (`student_code`),
  KEY `IDX_STUDENT_03` (`data_status`)
) 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,
  `student_id` bigint NOT NULL,
  `question_id` bigint NOT NULL,
  `exam_id` bigint NOT NULL,
  `subject_code` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `student_code` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `main_number` int NOT NULL,
  `sub_number` 	 varchar(255) NOT NULL,
  `answer_status` varchar(255) NOT NULL,
  `score_status` varchar(255) NOT NULL,
  `answer` longtext COLLATE utf8mb4_bin DEFAULT NULL,
  `ai_score` double DEFAULT NULL,
  `score_ratio` double DEFAULT NULL,
  `marking_score` double DEFAULT NULL,
  `err_msg` varchar(2000) COLLATE utf8mb4_bin DEFAULT NULL,
  `score_none` bit(1) DEFAULT NULL,
  `step_score` varchar(500) COLLATE utf8mb4_bin DEFAULT 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;