USE teachcloud_db;

ALTER TABLE `exam_student` ADD COLUMN `basic_student_id` BIGINT(20) NULL COMMENT '考生ID（basic_exam_student表ID）' AFTER `exam_detail_course_id`;
ALTER TABLE `mark_student`
    ADD COLUMN `basic_student_id` BIGINT(20) NULL COMMENT '考生ID（basic_exam_student表ID）' AFTER `exam_id`,
ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `student_id`;
ALTER TABLE `basic_exam_student` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;
ALTER TABLE `exam_card` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `org_id`;
ALTER TABLE `exam_detail_course` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_detail_id`;
ALTER TABLE `exam_task` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `org_id`;
ALTER TABLE `mark_document` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;
ALTER TABLE `mark_group` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;
ALTER TABLE `mark_paper` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;
ALTER TABLE `mark_task` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;
ALTER TABLE `teach_course` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;
ALTER TABLE `t_p_course` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `professional_id`;
ALTER TABLE `t_b_sync_task` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;
ALTER TABLE `client_status` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `print_plan_id`;


ALTER TABLE `basic_course` CHANGE COLUMN `org_id` `org_id` BIGINT NULL COMMENT '机构id' ;
ALTER TABLE `basic_course` DROP INDEX `course_schoolId_code_idx` , ADD UNIQUE INDEX `uq_1` USING BTREE (`school_id`, `teaching_room_id`, `code`);
ALTER TABLE `teach_course` CHANGE COLUMN `course_code` `course_code` VARCHAR(20) NULL COMMENT '课程代码' ;

ALTER TABLE `mark_paper_package`
    ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`,
CHANGE COLUMN `course_code` `course_code` VARCHAR(32) CHARACTER SET 'utf8mb4' NULL COMMENT '课程代码' ,
CHANGE COLUMN `course_name` `course_name` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '科目名称' ;


ALTER TABLE `exam_task_temp`
    ADD COLUMN `teaching_room_name` VARCHAR(100) NULL COMMENT '开课学院' AFTER `course_name`;

ALTER TABLE `exam_task`
    CHANGE COLUMN `course_code` `course_code` VARCHAR(20) NULL COMMENT '课程代码' ,
    CHANGE COLUMN `course_name` `course_name` VARCHAR(50) NULL COMMENT '课程名称' ;

ALTER TABLE `basic_teach_clazz`
    ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;

ALTER TABLE `basic_teach_clazz`
    CHANGE COLUMN `course_code` `course_code` VARCHAR(20) CHARACTER SET 'utf8mb4' NULL COMMENT '课程编号' ;

ALTER TABLE `basic_exam_student`
    CHANGE COLUMN `course_code` `course_code` VARCHAR(20) CHARACTER SET 'utf8mb4' NULL COMMENT '课程编号' ,
    CHANGE COLUMN `clazz_id` `clazz_id` BIGINT NULL COMMENT '教学班id（basic_teach_clazz）' ;

ALTER TABLE `exam_detail_course`
    CHANGE COLUMN `course_code` `course_code` VARCHAR(20) CHARACTER SET 'utf8mb4' NULL COMMENT '课程编码' ,
    CHANGE COLUMN `course_name` `course_name` VARCHAR(50) CHARACTER SET 'utf8mb4' NULL COMMENT '课程名称' ;

ALTER TABLE `mark_student`
    CHANGE COLUMN `course_code` `course_code` VARCHAR(32) CHARACTER SET 'utf8mb4' NULL COMMENT '科目代码' ,
    CHANGE COLUMN `course_name` `course_name` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '科目名称',
    CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '试卷类型' ,
    CHANGE COLUMN `student_code` `student_code` VARCHAR(64) CHARACTER SET 'utf8mb4' NULL COMMENT '学号' ,
    CHANGE COLUMN `student_name` `student_name` VARCHAR(64) CHARACTER SET 'utf8mb4' NULL COMMENT '姓名' ;


ALTER TABLE `sys_org` DROP COLUMN `third_relate_id`;

ALTER TABLE `exam_student`
    CHANGE COLUMN `student_name` `student_name` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '考生姓名' ,
    CHANGE COLUMN `student_code` `student_code` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '考生代码' ;

ALTER TABLE `mark_paper`
    CHANGE COLUMN `course_code` `course_code` VARCHAR(32) CHARACTER SET 'utf8mb4' NULL COMMENT '课程代码' ,
    CHANGE COLUMN `course_name` `course_name` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '科目名称' ;

ALTER TABLE `mark_task`
    CHANGE COLUMN `course_code` `course_code` VARCHAR(32) CHARACTER SET 'utf8mb4' NULL COMMENT '课程代码' ,
    CHANGE COLUMN `course_name` `course_name` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '课程名称' ;

ALTER TABLE `mark_document`
    CHANGE COLUMN `course_code` `course_code` VARCHAR(32) CHARACTER SET 'utf8mb4' NULL COMMENT '课程代码' ,
    CHANGE COLUMN `course_name` `course_name` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '科目名称' ;

ALTER TABLE `t_c_final_score`
    ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`,
CHANGE COLUMN `course_code` `course_code` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '课程编码' ;
ALTER TABLE `t_r_basic_info`
    ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`,
CHANGE COLUMN `course_code` `course_code` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '课程编码' ;
ALTER TABLE `t_c_paper_struct`
    ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`,
CHANGE COLUMN `course_code` `course_code` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '课程编码' ;
ALTER TABLE `t_c_usual_score`
    ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`,
CHANGE COLUMN `course_code` `course_code` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '课程编码' ;


update exam_task a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update exam_card a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update exam_detail_course a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update teach_course a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update t_p_course a left join t_p_professional tpp on a.professional_id = tpp.id left join basic_course bc on tpp.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;
update t_b_sync_task a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update basic_exam_student a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update basic_teach_clazz a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update mark_student a left join basic_exam be on a.exam_id = be.id left join basic_course bc on be.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;
update mark_document a left join basic_exam be on a.exam_id = be.id left join basic_course bc on be.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;
update mark_group a left join basic_exam be on a.exam_id = be.id left join basic_course bc on be.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;
update mark_paper a left join basic_exam be on a.exam_id = be.id left join basic_course bc on be.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;
update mark_task a left join basic_exam be on a.exam_id = be.id left join basic_course bc on be.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;
update mark_paper_package a left join basic_exam be on a.exam_id = be.id left join basic_course bc on be.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;
update client_status a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update t_c_final_score a left join basic_exam be on a.exam_id = be.id left join basic_course bc on be.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;
update t_r_basic_info a left join basic_exam be on a.exam_id = be.id left join basic_course bc on be.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;
update t_c_paper_struct a left join basic_exam be on a.exam_id = be.id left join basic_course bc on be.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;
update t_c_usual_score a left join basic_exam be on a.exam_id = be.id left join basic_course bc on be.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;

ALTER TABLE `exam_print_plan` DROP COLUMN `third_relate_name`, DROP COLUMN `third_relate_id`;
ALTER TABLE `exam_student` DROP COLUMN `can_delete`;
ALTER TABLE `basic_exam_student` ADD COLUMN `teach_class_name` VARCHAR(100) NULL AFTER `major`, ADD COLUMN `class_name` VARCHAR(100) NULL AFTER `teach_class_name`;
ALTER TABLE `basic_exam_student` ADD COLUMN `site_number` VARCHAR(10) NULL COMMENT '座位号' AFTER `student_code`, ADD COLUMN `extend_fields` MEDIUMTEXT NULL COMMENT '扩展字段' AFTER `exam_room`;
ALTER TABLE `basic_exam_student`
    CHANGE COLUMN `college` `college_name` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL DEFAULT NULL COMMENT '学院' ,
    CHANGE COLUMN `major` `major_name` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL DEFAULT NULL COMMENT '专业' ;

update basic_exam_student bes set bes.teach_class_name = (select btc.clazz_name from basic_teach_clazz btc where bes.clazz_id = btc.id) where bes.teach_class_name is null;

ALTER TABLE `mark_paper` ADD COLUMN `question_status` TINYINT(1) NULL DEFAULT '0' COMMENT '试卷结构是否提交' AFTER `show_object_score`;
update mark_paper set question_status = group_status;

ALTER TABLE `basic_exam_student`
    ADD COLUMN `status` VARCHAR(1) NULL DEFAULT 'N' COMMENT '状态，默认N-正常，D-缓考，F-免考，M-缺考，B-违纪' AFTER `class_name`,
ADD COLUMN `required_fields` MEDIUMTEXT NULL COMMENT '基础字段' AFTER `exam_room`;

ALTER TABLE `exam_card` ADD COLUMN `copy_card_id` BIGINT(20) NULL COMMENT '复制源题卡ID' AFTER `paper_id`;

INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES ('870', '启用/禁用', 'Enable', 'LINK', '624', '10', 'AUTH', '871', '1', '0', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('871', '启用/禁用', '/api/admin/exam/card/enable', 'URL', '624', '7', 'AUTH', '1', '1', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES ('1140', '状态', 'Status', 'LINK', '1100', '10', 'AUTH', '1141', '1', '0', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('1141', '状态', '/api/admin/basic/exam_student/status', 'URL', '1100', '6', 'AUTH', '1', '1', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES ('1142', '任课老师导入', 'ImportTeacher', 'BUTTON', '1100', '11', 'AUTH', '1143', '1', '0', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('1143', '任课老师导入', '/api/admin/basic/exam_student/import_teacher', 'URL', '1100', '7', 'AUTH', '1', '1', '1');

CREATE TABLE `exam_task_whole` (
      `id` BIGINT(20) NOT NULL,
      `semester_id` BIGINT(20) NULL COMMENT '学期ID',
      `exam_id` BIGINT(20) NULL COMMENT '考试ID',
      `course_id` BIGINT(20) NULL,
      `paper_number` VARCHAR(100) NULL,
      `attachment_path` VARCHAR(200) NULL COMMENT '文件地址',
      `status` VARCHAR(45) NULL COMMENT '状态',
      `create_time` BIGINT(20) NULL COMMENT '创建时间',
      PRIMARY KEY (`id`));

INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('1145', '合并PDF查看', '/api/admin/exam/print/get_whole_pdf', 'URL', '47', '5', 'AUTH', '1', '1', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES ('1146', '合并PDF生成', 'CreateWholePdf', 'BUTTON', '47', '5', 'AUTH', '1145,1147', '1', '0', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('1147', '合并PDF生成', '/api/admin/exam/print/create_whole_pdf', 'URL', '47', '6', 'AUTH', '1', '1', '1');


ALTER TABLE `mark_student` ADD COLUMN `miss_scan` TINYINT(1) NULL DEFAULT 0 COMMENT '是否漏扫' AFTER `invalid`;
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('1148', '更新漏扫状态', '/api/scan/student/missScan/update', 'URL', '970', '39', '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' WHERE (`id` = '1006');
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' WHERE (`id` = '970');

UPDATE `basic_school` SET has_paper_number = '0' where has_paper_number is null;
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`) VALUES ('242', '查询条件-课程', '/api/admin/client/course/list', 'URL', '199', '18', 'AUTH');
UPDATE `sys_privilege` SET `related` = '200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,241,242,973,974,975' WHERE (`id` = '199');

ALTER TABLE `teachcloud-3.4.0`.`mark_student`
DROP COLUMN `teacher`,
DROP COLUMN `teacher_id`,
DROP COLUMN `class_name`,
DROP COLUMN `major_name`,
DROP COLUMN `college`,
DROP COLUMN `student1_name`,
DROP COLUMN `course_name`,
DROP COLUMN `course_code`,
DROP INDEX `index3` ,
ADD INDEX `index3` USING BTREE (`exam_id`, `course_id`, `upload_time`);

UPDATE `sys_privilege` SET `related` = '184,414' WHERE (`id` = '408');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES ('1149', '评卷设置（批量）', 'BatchMarkConfig', 'BUTTON', '897', '8', 'AUTH', '1150', '1', '0', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('1150', '评卷设置保存', '/api/admin/mark/setting/paper/batch_save', 'URL', '897', '19', 'AUTH', '1', '1', '1');

ALTER TABLE `t_c_final_score`
DROP INDEX `t_c_score_end_exam_unique` ,
ADD UNIQUE INDEX `t_c_score_end_exam_unique` (`exam_id` ASC, `course_id` ASC, `paper_number` ASC, `student_code` ASC, `name` ASC);

ALTER TABLE `t_c_final_score`
DROP INDEX `t_c_final_score_unique` ,
ADD UNIQUE INDEX `t_c_final_score_unique` (`exam_id` ASC, `course_id` ASC, `teach_course_id` ASC, `student_code` ASC, `name` ASC);

ALTER TABLE `t_c_usual_score`
DROP INDEX `t_c_usual_score_unique` ,
ADD UNIQUE INDEX `t_c_usual_score_unique` (`exam_id` ASC, `course_id` ASC, `teach_course_id` ASC, `student_code` ASC, `name` ASC),
DROP INDEX `t_c_score_normal_unique` ,
ADD UNIQUE INDEX `t_c_score_normal_unique` (`exam_id` ASC, `course_id` ASC, `paper_number` ASC, `student_code` ASC, `name` ASC);

UPDATE `sys_privilege` SET `related` = '490,543,955,959,1139' WHERE (`id` = '488');


-- drop table if exists exam_detail_course_paper_type;
-- drop table if exists basic_template_org;
-- drop table if exists cloud_user_push_status;
-- drop table if exists exam_card_detail;
-- drop table if exists exam_paper_class_marker;
-- drop table if exists exam_paper_group;
-- drop table if exists exam_paper_group_marker;
-- drop table if exists exam_paper_structure;
-- drop table if exists exam_task_sync;
-- drop table if exists t_sync_exam_log;
-- drop table if exists t_sync_stmms_exam;
-- drop table if exists basic_college;
-- drop table if exists basic_campus;

-- delete脚本需要加上新增的表

-- 升级说明
-- 1. 将通用规则中，教学班改为teachClassName,班级改为className

--2024.06.24update*******************3.3.4->3.4.0升级脚本