USE teachcloud_db;

ALTER TABLE `mark_question` ADD INDEX `index_1` (`exam_id` ASC, `paper_number` ASC, `main_number` ASC, `sub_number` ASC);

ALTER TABLE `basic_exam_student`
    ADD COLUMN `exam_date` VARCHAR(20) NULL COMMENT '考试日期' AFTER `exam_end_time`,
ADD COLUMN `exam_time` VARCHAR(20) NULL COMMENT '考试时间' AFTER `exam_date`;

update basic_exam_student set exam_date = DATE_FORMAT(FROM_UNIXTIME(exam_start_time/1000), '%Y-%m-%d') where exam_start_time is not null and exam_date is null;
update basic_exam_student set exam_time = concat(DATE_FORMAT(FROM_UNIXTIME(exam_start_time/1000), '%H:%i'),'-',DATE_FORMAT(FROM_UNIXTIME(exam_end_time/1000), '%H:%i')) where exam_start_time is not null and exam_time is null;

ALTER TABLE `exam_detail`
    ADD COLUMN `exam_date` VARCHAR(20) NULL COMMENT '考试日期' AFTER `exam_end_time`,
ADD COLUMN `exam_time` VARCHAR(20) NULL COMMENT '考试时间' AFTER `exam_date`;

update exam_detail set exam_date = DATE_FORMAT(FROM_UNIXTIME(exam_start_time/1000), '%Y-%m-%d') where exam_start_time is not null and exam_date is null;
update exam_detail set exam_time = concat(DATE_FORMAT(FROM_UNIXTIME(exam_start_time/1000), '%H:%i'),'-',DATE_FORMAT(FROM_UNIXTIME(exam_end_time/1000), '%H:%i')) where exam_start_time is not null and exam_time is null;

ALTER TABLE `sys_user_role` ADD INDEX `idx_1` (`role_id` ASC, `user_id` ASC);
ALTER TABLE `t_f_flow_log` DROP INDEX `flow_log_idx` , ADD INDEX `flow_log_idx` USING BTREE (`flow_id`);
ALTER TABLE `exam_task` ADD INDEX `idx_1` (`flow_id` ASC);

-- 2025-02-21
ALTER TABLE `mark_paper`
    ADD COLUMN `merge_marker` TINYINT(1) NULL DEFAULT '1' COMMENT '填空题是否合并设置评卷员' AFTER `double_mark`,
CHANGE COLUMN `open_double_marking` `double_mark` TINYINT(1) NULL DEFAULT '0' COMMENT '是否开启双评：true-开启，false-关闭' ;

ALTER TABLE `mark_question`
    ADD COLUMN `pic_list` MEDIUMTEXT NULL COMMENT '评卷区坐标' AFTER `update_time`,
    ADD COLUMN `double_rate` DOUBLE NULL COMMENT '双评比例' AFTER `pic_list`,
    ADD COLUMN `arbitrate_threshold` DOUBLE NULL COMMENT '仲裁阈值' AFTER `double_rate`,
    ADD COLUMN `score_policy` VARCHAR(10) NULL COMMENT '合分策略' AFTER `arbitrate_threshold`,
    ADD COLUMN `task_count` INT NULL COMMENT '任务总量' AFTER `score_policy`,
    ADD COLUMN `marked_count` INT NULL COMMENT '已评数量' AFTER `task_count`,
    ADD COLUMN `left_count` INT NULL COMMENT '剩余数量' AFTER `marked_count`;

ALTER TABLE `mark_user_group`
    ADD COLUMN `question_id` BIGINT(20) NULL COMMENT '题目ID' AFTER `paper_number`,
ADD COLUMN `enable` TINYINT(1) NULL DEFAULT 1 COMMENT '是否启用' AFTER `reject_count`, RENAME TO  `mark_user_question` ;

ALTER TABLE `mark_task`
    ADD COLUMN `question_id` BIGINT(1) NULL COMMENT '题目ID' AFTER `paper_number`,
ADD COLUMN `marker_track_list` MEDIUMTEXT NULL COMMENT '评卷分数轨迹' AFTER `marker_score_list`,
ADD COLUMN `marker_tag_list` MEDIUMTEXT NULL COMMENT '评卷特殊标记' AFTER `marker_track_list`,
ADD COLUMN `header_track_list` MEDIUMTEXT NULL COMMENT '复核轨迹' AFTER `header_score_list`,
ADD COLUMN `header_tag_list` MEDIUMTEXT NULL COMMENT '复核特殊标记' AFTER `header_track_list`;

ALTER TABLE `mark_subjective_score`
    ADD COLUMN `question_id` BIGINT(20) NULL COMMENT '题目ID' AFTER `paper_number`;

CREATE TABLE `mark_user_paper` (
      `id` BIGINT(20) NOT NULL,
      `exam_id` BIGINT(20) NULL,
      `paper_number` VARCHAR(100) NULL,
      `user_id` BIGINT(20) NULL COMMENT '用户ID',
      `question_model` VARCHAR(10) NULL COMMENT '评卷模式（SINGLE：单题阅，MULTI：多题阅）',
      PRIMARY KEY (`id`))
    COMMENT = '试卷评卷员设置';

ALTER TABLE `mark_problem_history`
    ADD COLUMN `question_id` BIGINT(20) NULL COMMENT '题目ID' AFTER `paper_number`;

ALTER TABLE `mark_arbitrate_history`
    ADD COLUMN `question_id` BIGINT(20) NULL COMMENT '题目ID' AFTER `paper_number`;

ALTER TABLE `mark_reject_history`
    ADD COLUMN `question_id` BIGINT(20) NULL COMMENT '题目ID' AFTER `paper_number`;

CREATE TABLE `mark_header_history` (
          `id` BIGINT(20) NOT NULL,
          `exam_id` BIGINT(20) NULL COMMENT '考试ID',
          `paper_number` VARCHAR(100) NULL COMMENT '试卷编号',
          `student_id` BIGINT(20) NULL COMMENT '考生ID',
          `question_id` BIGINT(20) NULL COMMENT '题目ID',
          `main_number` INT NULL COMMENT '大题号',
          `sub_number` INT NULL COMMENT '小题号',
          `user_id` BIGINT(20) NULL COMMENT '用户ID',
          `score` DOUBLE NULL COMMENT '分数',
          `track_list` MEDIUMTEXT NULL COMMENT '轨迹',
          `original_score` DOUBLE NULL COMMENT '原分数',
          `original_track_list` MEDIUMTEXT NULL COMMENT '原轨迹',
          `create_time` BIGINT(20) NULL COMMENT '创建时间',
          PRIMARY KEY (`id`))
    COMMENT = '复核历史表';

ALTER TABLE `mark_user_paper`
    ADD COLUMN `mode` VARCHAR(45) NULL COMMENT '阅卷模式' AFTER `question_model`;

ALTER TABLE `mark_task`
    ADD COLUMN `main_number` INT NULL COMMENT '大题号' AFTER `question_id`,
ADD COLUMN `sub_number` INT NULL COMMENT '小题号' AFTER `main_number`;

ALTER TABLE `mark_paper`
    CHANGE COLUMN `open_mark_class` `class_mark` TINYINT NULL DEFAULT '0' COMMENT '是否开启分班阅' AFTER `paper_type`;

-- 2025/2/27
drop table if exists t_s_job_log;

UPDATE `sys_privilege` SET `url` = '/api/admin/mark/question/subjective/list' WHERE (`id` = '904');
UPDATE `sys_privilege` SET `related` = '672,902,903,904,905,906,907,908,909,910,911,912,913,962,963,965,1132,1133' WHERE (`id` = '901');
UPDATE `sys_privilege` SET `name` = '单双评设置', `url` = '/api/admin/mark/question/subjective/double/mark/update' WHERE (`id` = '905');
UPDATE `sys_privilege` SET `url` = '/api/admin/mark/question/subjective/picture/config/update' WHERE (`id` = '962');
UPDATE `sys_privilege` SET `url` = '/api/admin/mark/class/update' WHERE (`id` = '963');

ALTER TABLE `mark_question` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID' AFTER `exam_id`;

UPDATE `sys_privilege` SET `url` = '/api/admin/mark/question/subjective/summary' WHERE (`id` = '928');
UPDATE `sys_privilege` SET `url` = '/api/admin/mark/question/subjective/class/summary' WHERE (`id` = '964');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('1012', '评阅题目列表', '/api/admin/mark/question/subjective/number/list', 'URL', '917', '28', 'AUTH', '1', '1', '1');
UPDATE `sys_privilege` SET `related` = '928,929,932,933,934,935,936,937,938,939,940,941,942,943,964,965,966,967,968,1008,1012,1183,1186' WHERE (`id` = '944');

INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1189, '版本管理列表', '/api/admin/version/list', 'URL', 1, 18, 'SYS', NULL, 1, 1, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1190, '版本管理新增/修改', '/api/admin/version/save', 'URL', 1, 18, 'SYS', NULL, 1, 1, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1191, '版本管理编辑', '/api/admin/version/edit', 'URL', 1, 18, 'SYS', NULL, 1, 1, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1192, '版本管理删除', '/api/admin/version/delete', 'URL', 1, 18, 'SYS', NULL, 1, 1, 1);

DROP TABLE IF EXISTS `t_b_version`;
CREATE TABLE `t_b_version` (
                               `id` bigint NOT NULL COMMENT '主键',
                               `soft_version` varchar(200) NOT NULL COMMENT '服务端版本',
                               `client_version` varchar(100) NOT NULL COMMENT '客户端版本',
                               `create_id` bigint DEFAULT NULL COMMENT '创建人id',
                               `create_time` bigint DEFAULT NULL COMMENT '创建时间',
                               `update_id` bigint DEFAULT NULL COMMENT '更新人id',
                               `update_time` bigint DEFAULT NULL COMMENT '更新时间',
                               PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='版本管理表';

-- 2025-02-28
UPDATE `sys_privilege` SET `name` = '评卷参数设置步骤状态', `url` = '/api/admin/mark/question/subjective/step/status' WHERE (`id` = '906');

INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1193, '课程任课老师导入', '/api/admin/basic/course/import', 'URL', 13, 1, 'AUTH', NULL, 1, 1, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1194, '课程任课老师导入', 'AssginCourseUserImport', 'BUTTON', 576, 7, 'AUTH', '1193', 1, 0, 1);

-- 2025-03-03
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('1013', '新增考生', '/api/scan/student/save', 'URL', '970', '44', 'AUTH', '1', '1', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('1014', '复制卡格式', '/api/scan/answer/card/copy', 'URL', '970', '45', 'AUTH', '1', '1', '1');
UPDATE `sys_privilege` SET `related` = '971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000,1001,1002,1003,1004,1005,1007,1009,1010,1013,1014,1148,3072,3073,3074,3075' WHERE (`id` = '970');

-- 2025-03-04
UPDATE sys_config
SET school_id=NULL, org_id=NULL, config_key='attachment.type', config_name='附件类型', config_value='[.xlsx,.xls,.doc,.docx,.pdf,.jpg,.jpeg,.png,.html,.zip,.mp3,.wav,.dll,.exe,.ftl,.bpmn,.xml,.ttf,.otf,.pfm,.woff,.svg,.eot]', remark=NULL, enable=1, sort=1, create_id=1, create_time=NULL, update_id=NULL, update_time=NULL
WHERE id=10;

ALTER TABLE `basic_print_config` ADD COLUMN `default_serial_number` INT(2) NULL DEFAULT 0 COMMENT '默认抽取卷型（0-随机，1-卷1，2-卷2）' AFTER `draw_rule`;

INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1195, '字体查询', '/api/admin/set/font/select', 'URL', 508, 21, 'AUTH', NULL, 1, 0, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1196, '字体保存', '/api/admin/set/font/save', 'URL', 508, 21, 'AUTH', NULL, 1, 0, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1197, '字体修改', '/api/admin/set/font/update', 'URL', 508, 21, 'AUTH', NULL, 1, 0, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1198, '字体删除', '/api/admin/set/font/delete', 'URL', 508, 21, 'AUTH', NULL, 1, 0, 1);

-- 2025-03-05
ALTER TABLE mark_student ADD version INTEGER DEFAULT 1
    COMMENT '版本号' NOT NULL after miss_scan;

INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('1015', '获取识别对照任务', '/api/scan/task/omr/getTask', 'URL', '970', '44', 'AUTH', '1', '1', '1');
UPDATE `sys_privilege` SET `related` = '971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000,1001,1002,1003,1004,1005,1007,1009,1010,1013,1014,1015,1148,3072,3073,3074,3075' WHERE (`id` = '970');

INSERT INTO sys_config
(id, school_id, org_id, config_key, config_name, config_value, remark, enable, sort, create_id, create_time, update_id, update_time)
VALUES(51, NULL, NULL, 'mark.score.calculate.job.db.limit', '统分查询考生条数', 'limit 0,500', NULL, 1, 20, 1, NULL, NULL, NULL);

-- 2025-03-06
CREATE TABLE IF NOT EXISTS `exam_card_model_four` (
  `id` bigint NOT NULL COMMENT '主键',
  `school_id` bigint NOT NULL COMMENT '学校id',
  `org_id` bigint DEFAULT NULL COMMENT '机构id',
  `exam_id` bigint DEFAULT NULL COMMENT '考试id',
  `course_id` bigint DEFAULT NULL COMMENT '课程ID（basic_course表ID）',
  `title` varchar(200) NOT NULL COMMENT '标题',
  `status` varchar(45) DEFAULT NULL COMMENT 'STAGE-暂存，SUBMIT-提交',
  `content` mediumtext COMMENT '题卡工具制作题卡内容',
  `stage_content` mediumtext COMMENT '暂存内容（提交后置空）',
  `html_content` mediumtext COMMENT 'html格式内容',
  `enable` tinyint(1) DEFAULT '1' COMMENT '1正常，0禁用',
  `card_rule_id` bigint DEFAULT NULL COMMENT '题卡规则Id',
  `jpg_attachment` text COMMENT '题卡转换成的jpg文件信息',
  `create_id` bigint DEFAULT NULL COMMENT '创建人',
  `create_time` bigint DEFAULT NULL COMMENT '创建时间',
  `update_id` bigint DEFAULT NULL COMMENT '更新人',
  `update_time` bigint DEFAULT NULL COMMENT '更新时间',
  `page_size` varchar(10) DEFAULT NULL COMMENT '题卡纸张大小（A3，8K）',
  `remark` mediumtext COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='模式4题卡';

INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1199, '答题卡管理', 'CardModel4Manage', 'MENU', 3, 4, 'AUTH', NULL, 1, 0, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1200, '查询', 'Select', 'BUTTON', 1199, 1, 'AUTH', '1209', 1, 0, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1201, '新增题卡', 'Add', 'BUTTON', 1199, 2, 'AUTH', '1210', 1, 0, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1202, '批量下载', 'BatchDownload', 'BUTTON', 1199, 3, 'AUTH', '1214', 1, 0, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1203, '查询条件', 'Condition', 'CONDITION', 1199, 4, 'AUTH', NULL, 1, 0, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1204, '查看', 'Preview', 'LINK', 1199, 1, 'AUTH', NULL, 1, 0, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1205, '生成图片/预览图片', 'ConvertImage', 'LINK', 1199, 2, 'AUTH', '1212', 1, 0, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1206, '编辑题卡/编辑信息', 'Edit', 'LINK', 1199, 3, 'AUTH', '1210', 1, 0, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1207, '下载', 'Download', 'LINK', 1199, 4, 'AUTH', '1213', 1, 0, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1208, '删除', 'Delete', 'LINK', 1199, 5, 'AUTH', '1211', 1, 0, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1209, '查询', '/api/admin/exam/model_4/card/page', 'URL', 1199, 1, 'AUTH', NULL, 1, 1, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1210, '新增/修改', '/api/admin/exam/model_4/card/save_generic', 'URL', 1199, 2, 'AUTH', NULL, 1, 1, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1211, '删除', '/api/admin/exam/model_4/card/delete_generic', 'URL', 1199, 3, 'AUTH', NULL, 1, 1, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1212, '生成图片', '/api/admin/exam/model_4/card/convert_image', 'URL', 1199, 4, 'AUTH', NULL, 1, 1, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1213, '下载', '/api/admin/exam/model_4/card/download_card', 'URL', 1199, 5, 'AUTH', NULL, 1, 1, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1214, '批量下载', '/api/admin/exam/model_4/card/batch_download_card', 'URL', 1199, 6, 'AUTH', NULL, 1, 1, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1215, '列表', 'List', 'LIST', 1199, 5, 'AUTH', '1209', 1, 0, 1);

ALTER TABLE `mark_arbitrate_history` CHANGE COLUMN `group_number` `group_number` INT NULL COMMENT '大题号' ;
ALTER TABLE `mark_problem_history` CHANGE COLUMN `group_number` `group_number` INT NULL COMMENT '大题号' ;
ALTER TABLE `mark_subjective_score` CHANGE COLUMN `group_number` `group_number` INT NULL COMMENT '分组序号' ;
ALTER TABLE `mark_subjective_score` CHANGE COLUMN `group_score` `group_score` DOUBLE NULL COMMENT '分组得分' ;
ALTER TABLE `mark_subjective_score` CHANGE COLUMN `uncalculate` `uncalculate` TINYINT(1) NULL COMMENT '是否合分' ;

-- 2025-03-07
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1216, '查看', '/api/admin/exam/model_4/card/get_one', 'URL', 1199, 7, 'AUTH', NULL, 1, 1, 1);
UPDATE sys_privilege
SET name='查看', url='Preview', `type`='LINK', parent_id=1199, `sequence`=1, property='AUTH', related='1216', enable=1, default_auth=0, front_display=1
WHERE id=1204;