USE teachcloud_db;

ALTER TABLE obe_culture_program_requirement ADD UNIQUE INDEX obe_culture_program_requirement_unique (culture_program_id ASC, name ASC);

CREATE TABLE obe_course_outline_audit_report (
         id BIGINT NOT NULL,
         course_outline_id BIGINT NOT NULL,
         weight_setting_sign BIGINT NULL COMMENT '课程权重设置标识',
         course_basic_info MEDIUMTEXT NULL COMMENT '课程基础信息',
         course_target_info MEDIUMTEXT NULL COMMENT '课程目标情况',
         rationality_evaluation MEDIUMTEXT NULL COMMENT '合理性评价',
         PRIMARY KEY (id)
)  ENGINE=INNODB DEFAULT CHARACTER SET=UTF8MB4 COLLATE=utf8mb4_general_ci COMMENT='课程目标达成情况评价依据合理性审核表';

INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, enable, default_auth, front_display) VALUES ('3060', '课程大纲-审核', '/api/admin/obe/course_outline/audit_report', 'URL', '3000', '7', 'AUTH', '1', '1', '1');
INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, enable, default_auth, front_display) VALUES ('3061', '课程大纲-审核及合理性', '/api/admin/obe/course_outline/audit_rationality_report', 'URL', '3000', '8', 'AUTH', '1', '1', '1');
INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, enable, default_auth, front_display) VALUES ('3062', '课程大纲-审核报告保存', '/api/admin/obe/course_outline/audit/save', 'URL', '3000', '9', 'AUTH', '1', '1', '1');
INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, related, enable, default_auth, front_display) VALUES ('3063', '审核', 'Audit', 'LINK', '3000', '8', 'AUTH', '3060,3062', '1', '0', '1');
INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, related, enable, default_auth, front_display) VALUES ('3064', '审核&合理性', 'AuditAndRationality', 'LINK', '3000', '9', 'AUTH', '3061,3062', '1', '0', '1');

ALTER TABLE sys_config CHANGE COLUMN config_value config_value MEDIUMTEXT NULL COMMENT '参数键值' ;

INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, enable, default_auth, front_display) VALUES ('3070', '学号配置保存', '/api/admin/set/student_number_config/save', 'URL', '508', '17', 'AUTH', '1', '0', '1');
INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, enable, default_auth, front_display) VALUES ('3071', '学号配置查询', '/api/admin/set/student_number_config/select', 'URL', '508', '18', 'AUTH', '1', '0', '1');

INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, enable, default_auth, front_display) VALUES ('3072', '查询学号字母映射关系', '/api/scan/config/student_number_letter_relation', 'URL', '970', '40', 'AUTH', '1', '1', '1');

ALTER TABLE scan_paper_page ADD COLUMN paper_type VARCHAR(10) NULL COMMENT '卷型' AFTER recog_data;

INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, enable, default_auth, front_display) VALUES ('3073', '卷型检查任务-状态数量', '/api/scan/student/paper_type_check/status/count', 'URL', '970', '41', 'AUTH', '1', '1', '1');
INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, enable, default_auth, front_display) VALUES ('3074', '卷型检查任务-任务确认', '/api/scan/student/paper_type_check/confirm', 'URL', '970', '42', '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,1148,3072,3073,3074' WHERE (id = '970');
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,1148,3072,3073,3074' WHERE (id = '1006');

ALTER TABLE scan_paper_page CHANGE COLUMN paper_type paper_type TEXT NULL COMMENT '卷型';

UPDATE sys_privilege SET property = 'SYS', default_auth = '1' WHERE (id = '3071');

INSERT INTO sys_privilege (id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display) VALUES(2111, '平时成绩-获取作业集合', '/api/admin/course/degree/usual_score/template_download/list', 'URL', 2006, 1, 'AUTH', NULL, 1, 1, 1);
UPDATE sys_privilege SET name='管理成绩', url='Score', `type`='LINK', parent_id=2001, `sequence`=1, property='AUTH', related='2008,2009,2010,2011,2012,2013,2014,2015,2016,2017,2018,2019,2020,2029,2030,2031,2032,2033,2034,3018,2111', enable=1, default_auth=0, front_display=1 WHERE id=2006;

ALTER TABLE t_c_usual_score ADD source varchar(50) NULL COMMENT '来源，EXCEL_IMPORT：excel导入，SYNC：数据同步';
ALTER TABLE t_c_usual_score CHANGE source source varchar(50) NULL COMMENT '来源，EXCEL_IMPORT：excel导入，SYNC：数据同步' AFTER enable;
ALTER TABLE t_c_final_score MODIFY COLUMN score_detail mediumtext NULL COMMENT '成绩明细';
ALTER TABLE t_c_final_score MODIFY COLUMN score double NULL COMMENT '分数';
ALTER TABLE t_c_usual_score MODIFY COLUMN score mediumtext NULL COMMENT '平常作业分数';
ALTER TABLE t_c_usual_score ADD teach_class_name varchar(100) NOT NULL COMMENT '教学班';
ALTER TABLE t_c_usual_score CHANGE teach_class_name teach_class_name varchar(100) NOT NULL COMMENT '教学班' AFTER source;
ALTER TABLE t_c_final_score ADD teach_class_name varchar(100) NOT NULL COMMENT '教学班';
ALTER TABLE t_c_final_score CHANGE teach_class_name teach_class_name varchar(100) NOT NULL COMMENT '教学班' AFTER source;
ALTER TABLE t_r_basic_info ADD teach_class_name varchar(100) NOT NULL COMMENT '教学班';
ALTER TABLE t_r_basic_info CHANGE teach_class_name teach_class_name varchar(100) NOT NULL COMMENT '教学班' AFTER enable;
ALTER TABLE t_r_basic_info DROP KEY t_r_basic_info_unique;
ALTER TABLE t_r_basic_info ADD CONSTRAINT t_r_basic_info_unique UNIQUE KEY (culture_program_id,course_id,exam_id,paper_number,teach_class_name);

UPDATE sys_privilege SET name='成绩管理', url='TargetScoreManage', `type`='MENU', parent_id=2100, `sequence`=1, property=NULL, related='2003,2004,2005,3053', enable=1, default_auth=0, front_display=1 WHERE id=2001;

ALTER TABLE `basic_print_config` ADD COLUMN `open_ab` TINYINT(1) NULL DEFAULT 0 COMMENT '是否开启AB卷' AFTER `card_rule_id`;
ALTER TABLE `mark_student` ADD COLUMN `omr_breach` TINYINT(1) NULL DEFAULT 0 COMMENT '识别违纪' AFTER `omr_absent_checked`;
ALTER TABLE mark_student ADD COLUMN paper_type_check_status VARCHAR(32) NOT NULL DEFAULT 'NORMAL' COMMENT '卷型检查状态' AFTER omr_breach;
UPDATE mark_student SET paper_type_check_status = 'NORMAL' WHERE paper_type_check_status = '' or paper_type_check_status is null;

ALTER TABLE `exam_task_detail`
ADD COLUMN `serial_number` int(2) NULL COMMENT '备用卷序号（1,2,3,4…）' AFTER `exam_task_id`,
ADD COLUMN `exposed` TINYINT(1) NULL DEFAULT 0 COMMENT '是否曝光' AFTER `remark`;
ALTER TABLE `exam_task_detail` ADD COLUMN `exposed_exam_id` BIGINT(20) NULL COMMENT '曝光的考试ID' AFTER `exposed`;

ALTER TABLE `exam_task` ADD COLUMN `open_ab` TINYINT(1) NULL DEFAULT 0 COMMENT '是否开启AB卷' AFTER `paper_number`;
update exam_task et set open_ab = (select case when draw_count > 1 then 1 else 0 end from exam_task_detail etd where et.id = etd.exam_task_id) ;

ALTER TABLE `basic_card_rule`
    ADD COLUMN `contains_letter` TINYINT(1) NULL DEFAULT 0 COMMENT '学号是否包含字母' AFTER `default_option_number`,
ADD COLUMN `relation_list` VARCHAR(500) NULL COMMENT '学号字母所在位置坐标' AFTER `contains_letter`;

ALTER TABLE `exam_task`
    ADD COLUMN `paper_confirm_attachment_ids` TEXT NULL AFTER `exam_task_content`,
ADD COLUMN `remark` VARCHAR(200) NULL AFTER `paper_confirm_attachment_ids`;

update exam_task et set et.paper_confirm_attachment_ids = (select paper_confirm_attachment_ids from exam_task_detail etd where et.id = etd.exam_task_id);
update exam_task et set et.remark = (select remark from exam_task_detail etd where et.id = etd.exam_task_id);

ALTER TABLE `exam_detail_course` ADD COLUMN `serial_number` INT(2) NULL COMMENT '备用卷' AFTER `update_time`;
ALTER TABLE `mark_paper` ADD COLUMN `serial_number` INT(1) NULL COMMENT '备用卷' AFTER `user_id`;
ALTER TABLE `mark_paper_package` ADD COLUMN `serial_number` INT(1) NULL COMMENT '备用卷序号' AFTER `course_paper_id`;
ALTER TABLE `mark_student` ADD COLUMN `serial_number` INT(2) NULL COMMENT '备用卷序号' AFTER `course_paper_id`;
ALTER TABLE `scan_answer_card` ADD COLUMN `serial_number` INT(2) NULL COMMENT '备用卷序号' AFTER `course_paper_id`;

CREATE TABLE `mark_question_answer` (
        `id` BIGINT(20) NOT NULL,
        `exam_id` BIGINT(20) NULL COMMENT '考试ID',
        `paper_number` VARCHAR(50) NULL COMMENT '试卷编号',
        `paper_type` VARCHAR(10) NULL COMMENT '卷型',
        `main_number` INT NULL COMMENT '大题号',
        `sub_number` INT NULL COMMENT '小题号',
        `answer` VARCHAR(16) NULL COMMENT '客观题标答',
        `objective_policy` VARCHAR(16) NULL COMMENT '判分策略',
        `objective_policy_score` DOUBLE NULL COMMENT '客观题判分策略计分（漏选给分）',
        `create_id` BIGINT(20) NULL,
        `create_time` BIGINT(20) NULL,
        `update_id` BIGINT(20) NULL,
        `update_time` BIGINT(20) NULL,
        PRIMARY KEY (`id`))
    COMMENT = '客观题信息';

ALTER TABLE `exam_task` ADD COLUMN `exam_task_detail_enable` TINYINT(1) NULL DEFAULT 1 COMMENT '卷库启用禁用状态。是否启用，false：停用，true：启用' AFTER `enable`;

-- 2024-10-24
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(2114, '试卷清除信息', '/api/admin/course/degree/final_score/paper_struct/clear', 'URL', 2006, 1, 'AUTH', NULL, 1, 1, 1);

UPDATE sys_privilege
SET name='管理成绩', url='Score', `type`='LINK', parent_id=2001, `sequence`=1, property='AUTH', related='2008,2009,2010,2011,2012,2013,2014,2015,2016,2017,2018,2019,2020,2029,2030,2031,2032,2033,2034,3018,2111,2114', enable=1, default_auth=0, front_display=1
WHERE id=2006;

INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('882', '是否开启AB卷', 'OpenAb', 'BUTTON', '40', '13', 'AUTH', '1', '0', '1');

ALTER TABLE `basic_exam_student` CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) NULL DEFAULT NULL COMMENT '试卷编号' ;
ALTER TABLE `basic_message` CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) NULL DEFAULT NULL COMMENT '试卷编号' ;
ALTER TABLE `client_status` CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) NOT NULL COMMENT '试卷编号' ;
ALTER TABLE `exam_detail_course` CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) NOT NULL COMMENT '页数' ;
ALTER TABLE `exam_student` CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) NOT NULL COMMENT '试卷编号' ;
ALTER TABLE `exam_task` CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) NOT NULL COMMENT '试卷编号' ;
ALTER TABLE `exam_task_print` CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) NULL DEFAULT NULL COMMENT '试卷编号' ;
ALTER TABLE `exam_task_sync` CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) NOT NULL COMMENT '试着编号' ;
ALTER TABLE `exam_task_temp` CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) NULL DEFAULT NULL COMMENT '试卷编号' ;
ALTER TABLE `grade_batch_student` CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) NULL DEFAULT NULL COMMENT '试卷编号' ;
ALTER TABLE `grade_batch_student_clazz` CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) NULL DEFAULT NULL COMMENT '试卷编号' ;
ALTER TABLE `mark_document` CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) NULL DEFAULT NULL COMMENT '试卷编号' ;
ALTER TABLE `mark_group_student` CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) NOT NULL COMMENT '科目代码' ;
ALTER TABLE `mark_header_tag` CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) NOT NULL COMMENT '试卷编号' ;
ALTER TABLE `mark_paper` CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) NULL DEFAULT NULL COMMENT '试卷编号' ;
ALTER TABLE `mark_paper_package` CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) NULL DEFAULT NULL COMMENT '试卷编号' ;
ALTER TABLE `mark_problem_history` CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) NOT NULL COMMENT '试卷编号' ;
ALTER TABLE `mark_question_answer` CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) NULL DEFAULT NULL COMMENT '试卷编号' ;
ALTER TABLE `mark_reject_history` CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) NULL DEFAULT NULL COMMENT '试卷编号' ;
ALTER TABLE `t_b_sync_task` CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) NULL DEFAULT NULL COMMENT '试卷编号' ;
ALTER TABLE `t_b_task` CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) NULL DEFAULT NULL COMMENT '试卷编号' ;
ALTER TABLE `t_sync_exam_student_score` CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) NULL DEFAULT NULL COMMENT '试卷编号' ;

ALTER TABLE `exam_card` ADD COLUMN `open_ab` TINYINT(1) NULL DEFAULT 0 COMMENT '是否AB卷（AB卷题卡）' AFTER `page_size`;

update mark_paper set paper_file_path = concat('[',paper_file_path, ']') where paper_file_path is not null;
update mark_document set file_path = concat('[',file_path, ']') where type = 'PAPER' and file_path is not null;

-- 2024-11-11
ALTER TABLE obe_course_outline MODIFY COLUMN course_type varchar(50) NOT NULL COMMENT '课程类别';

ALTER TABLE `t_b_session` CHANGE COLUMN `type` `type` VARCHAR(200) NOT NULL COMMENT '用户类型' ;

ALTER TABLE `mark_student` ADD COLUMN `is_manual_breach` TINYINT(1) NULL DEFAULT 0 COMMENT '人工指定缺考' AFTER `omr_breach`;

CREATE TABLE `scan_answer_card_question`  (
      `id` bigint NOT NULL COMMENT '主键',
      `exam_id` bigint NOT NULL COMMENT '考试ID',
      `paper_number` varchar(100) NOT NULL COMMENT '试卷类型',
      `course_paper_id` varchar(32) NULL DEFAULT NULL COMMENT '试卷编号（交互）',
      `serial_number` int NULL DEFAULT NULL COMMENT '备用卷',
      `card_number` int NULL DEFAULT NULL COMMENT '卡格式序号',
      `main_number` int NOT NULL COMMENT '大题号',
      `sub_number` int NOT NULL COMMENT '小题号',
      `option_count` int NOT NULL COMMENT '选项个数',
      `question_type` varchar(2) NULL DEFAULT NULL COMMENT '题型',
      `paper_index` int NULL DEFAULT NULL,
      `page_index` int NULL DEFAULT NULL,
      `create_id` bigint NULL DEFAULT NULL,
      `create_time` bigint NULL DEFAULT NULL,
      `update_id` bigint NULL DEFAULT NULL,
      `update_time` bigint NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '卡格式题型';

-- 2024-11-15
ALTER TABLE t_c_paper_struct DROP KEY t_c_paper_struct_unique;
ALTER TABLE t_c_paper_struct ADD CONSTRAINT t_c_paper_struct_unique UNIQUE KEY (culture_program_id,exam_id,course_id,paper_number);
ALTER TABLE t_c_final_score DROP KEY t_c_final_score_unique;
ALTER TABLE t_c_final_score ADD CONSTRAINT t_c_final_score_unique UNIQUE KEY (culture_program_id,exam_id,course_id,paper_number,student_code);
ALTER TABLE t_c_usual_score DROP KEY t_c_usual_score_unique;
ALTER TABLE t_c_usual_score ADD CONSTRAINT t_c_usual_score_unique UNIQUE KEY (culture_program_id,exam_id,course_id,paper_number,student_code);

update mark_student set is_manual_absent = 1, scan_status = 'UNEXIST' where scan_status = 'MANUAL_ABSENT';

ALTER TABLE `mark_student`
    CHANGE COLUMN `is_manual_absent` `is_manual_absent` TINYINT(4) NULL DEFAULT NULL COMMENT '是否人工指定缺考' ,
    CHANGE COLUMN `is_manual_breach` `is_manual_breach` TINYINT(1) NULL DEFAULT NULL COMMENT '人工指定缺考' ;

INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('3075', '修改违纪状态', '/api/scan/student/breach/update', 'URL', '970', '43', '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,1148,3072,3073,3074,3075' WHERE (`id` = '970');

-- 2024-11-26
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(2115, '期末成绩-同步状态', '/api/admin/course/degree/final_score/sync/status', 'URL', 2006, 1, 'AUTH', NULL, 1, 1, 1);

UPDATE sys_privilege
SET name='管理成绩', url='Score', `type`='LINK', parent_id=2001, `sequence`=1, property='AUTH', related='2008,2009,2010,2011,2012,2013,2014,2015,2016,2017,2018,2019,2020,2029,2030,2031,2032,2033,2034,3018,2111,2114,2115', enable=1, default_auth=0, front_display=1
WHERE id=2006;

-- 2024-11-27
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1188, '共用接口-OBE专业', '/api/admin/basic/condition/list_professional_all', 'URL', 149, 18, 'SYS', NULL, 1, 1, 1);

INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('854', '指定用户添加课程', 'AssginCourseUser', 'BUTTON', '576', '6', 'AUTH', '1', '0', '1');
UPDATE `sys_privilege` SET `related` = '157,1103' WHERE (`id` = '1111');

-- 2024-12-3
CREATE INDEX t_c_final_score_culture_program_id_IDX USING BTREE ON t_c_final_score (culture_program_id,exam_id,course_id,paper_number);

CREATE INDEX t_c_usual_score_culture_program_id_IDX USING BTREE ON t_c_usual_score (culture_program_id,exam_id,course_id,paper_number);

CREATE INDEX t_r_basic_info_program_id_IDX USING BTREE ON t_r_basic_info (culture_program_id,exam_id,course_id,paper_number);

-- 2025-03-13
INSERT INTO sys_privilege
(id, name, url, parent_id, `type`, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1300, '获取评卷区', '/api/admin/mark/inspected/get_mark_area', 946, 'URL', 9, 'AUTH', NULL, 1, 1, 1);
UPDATE sys_privilege
SET name='成绩检查', url='ScoreCheck', `type`='MENU', parent_id=486, `sequence`=4, property='AUTH', related='1300', enable=1, default_auth=0, front_display=1
WHERE id=946;