server/distributed-print/install/mysql/upgrade/3.4.4.sql

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);