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

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

GRANT ALL PRIVILEGES  ON `ai_marking_stmms_db`.* TO 'ai_marking_stmms'@'localhost' IDENTIFIED BY 'ai_marking_stmms';

FLUSH  PRIVILEGES;

USE ai_marking_stmms_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,
  `title` 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,
  `score_grades` longtext COLLATE utf8mb4_bin DEFAULT NULL,
  `ext` longtext COLLATE utf8mb4_bin DEFAULT NULL,
  `answer_range_type` varchar(1000) COLLATE utf8mb4_bin NOT NULL,
  `image_slice` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL,
  `prompt_template` varchar(255) NOT NULL,
  `status` varchar(255) 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_score`;
CREATE TABLE `am_student_score` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `question_id` bigint NOT NULL,
   `student_id` bigint NOT NULL,
  `exam_number` varchar(255) COLLATE utf8mb4_bin 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,
  `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,
  `sheet` varchar(1000) DEFAULT NULL,
  `slice` varchar(500) DEFAULT NULL,
  `image_position` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL,
  `sheet_count` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_STUDENT_SCORE_01` (`question_id`,`exam_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;