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

USE teachcloud_db;

ALTER TABLE `exam_student` ADD COLUMN `student_id` BIGINT(20) NULL COMMENT '考生ID（basic_exam_student表ID）' AFTER `exam_detail_course_id`;
ALTER TABLE `mark_student`
    ADD COLUMN `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 `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 `course_dimension`
    ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`,
CHANGE COLUMN `course_code` `course_code` VARCHAR(20) CHARACTER SET 'utf8mb4' NULL COMMENT '课程编号' ;

ALTER TABLE `course_evaluation`
    ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`,
CHANGE COLUMN `course_code` `course_code` VARCHAR(20) CHARACTER SET 'utf8mb4' NULL COMMENT '课程编号' ;

ALTER TABLE `course_target`
    ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`,
CHANGE COLUMN `course_code` `course_code` VARCHAR(20) CHARACTER SET 'utf8mb4' NULL COMMENT '课程编号' ;

ALTER TABLE `course_weight`
    ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`,
CHANGE COLUMN `course_code` `course_code` VARCHAR(20) 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 `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 '考生代码' ;


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 course_dimension 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 course_evaluation 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 course_target 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 course_weight 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;


ALTER TABLE `exam_print_plan` DROP COLUMN `third_relate_name`, DROP COLUMN `third_relate_id`;
ALTER TABLE `exam_student` DROP COLUMN `can_delete`;

-- 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脚本需要加上新增的表