USE teachcloud_db;

ALTER TABLE `exam_detail`
    ADD COLUMN `exam_id` BIGINT(20) NULL COMMENT '考试ID' AFTER `print_plan_id`;

ALTER TABLE `exam_task_sync`
    ADD COLUMN `exam_id` BIGINT(20) NOT NULL COMMENT '考试ID' AFTER `school_id`;

ALTER TABLE `exam_student`
    ADD COLUMN `exam_id` BIGINT(20) NULL COMMENT '考试ID' AFTER `student_code`;

ALTER TABLE `grade_batch`
    ADD COLUMN `exam_id` BIGINT NOT NULL COMMENT '知学知考考试id' AFTER `semester_id`;

ALTER TABLE `grade_paper`
    ADD COLUMN `exam_id` BIGINT NOT NULL COMMENT '知学知考考试id' AFTER `school_id`;

ALTER TABLE `grade_paper_dimension`
    ADD COLUMN `exam_id` BIGINT NOT NULL COMMENT '考试id' AFTER `school_id`;

ALTER TABLE `grade_paper_struct`
    ADD COLUMN `exam_id` BIGINT NOT NULL COMMENT '知学知考考试id' AFTER `school_id`;

ALTER TABLE `grade_batch_paper`
    ADD COLUMN `exam_id` BIGINT NOT NULL COMMENT '知学知考考试id' AFTER `org_id`;

ALTER TABLE `grade_module_define`
    ADD COLUMN `exam_id` BIGINT NOT NULL COMMENT '考试id' AFTER `school_id`;

ALTER TABLE `grade_module_evaluation`
    ADD COLUMN `exam_id` BIGINT NOT NULL COMMENT '考试id' AFTER `school_id`;

ALTER TABLE `grade_paper_struct`
    CHANGE COLUMN `exam_Id` `exam_id` BIGINT NOT NULL COMMENT '知学知考考试id';

ALTER TABLE `t_sync_exam_log`
    ADD COLUMN `third_relate_id` INT(6) NULL COMMENT '云阅卷考试ID' AFTER `exam_id`;

ALTER TABLE `t_sync_exam_student_score`
    ADD COLUMN `third_relate_id` INT(6) NULL COMMENT '云阅卷考试ID' AFTER `exam_id`;

ALTER TABLE `exam_task_sync`
    ADD COLUMN `paper_type` VARCHAR(10) NULL COMMENT '试卷类型' AFTER `paper_number`;

CREATE INDEX exam_detail_school_id_IDX USING BTREE ON exam_detail (school_id);

update exam_detail ed set ed.exam_id = (select epp.exam_id from exam_print_plan epp where ed.school_id = epp.school_id and ed.print_plan_id = epp.id);

update exam_task_sync ets set ets.exam_id = (select et.exam_id from exam_task et where ets.school_id = et.school_id and ets.paper_number = et.paper_number);

UPDATE exam_task_sync t
SET
    t.paper_type = (SELECT DISTINCT
                        edc.paper_type
                    FROM
                        exam_detail_course edc
                            LEFT JOIN
                        exam_detail ed ON edc.exam_detail_id = ed.id
                    WHERE
                            t.school_id = ed.school_id
                      AND t.exam_id = ed.exam_id
                      AND t.paper_number = edc.paper_number
                      AND edc.paper_type IS NOT NULL);

UPDATE exam_student es
SET es.exam_id = (
    SELECT
        epp.exam_id
    FROM
        exam_detail ed
            LEFT JOIN exam_detail_course edc ON ed.id = edc.exam_detail_id
            LEFT JOIN exam_print_plan epp ON ed.print_plan_id = epp.id
    WHERE
            edc.id = es.exam_detail_course_id);

update grade_batch_paper gb set exam_id = (select exam_id from exam_task et where gb.school_id = et.school_id  and gb.paper_number = et.paper_number) where id > 0;

update grade_module_define gb set exam_id = (select exam_id from exam_task et where gb.school_id = et.school_id  and gb.paper_number = et.paper_number) where id > 0;

update grade_module_evaluation gb set exam_id = (select exam_id from exam_task et where gb.school_id = et.school_id  and gb.paper_number = et.paper_number) where id > 0;

update grade_paper gb set exam_id = (select exam_id from exam_task et where gb.school_id = et.school_id  and gb.paper_number = et.paper_number) where id > 0;

update grade_paper_dimension gb set exam_id = (select exam_id from exam_task et where gb.school_id = et.school_id and gb.paper_number = et.paper_number) where id > 0;

update grade_paper_struct gb set exam_id = (select exam_id from exam_task et where gb.school_id = et.school_id and gb.paper_number = et.paper_number) where id > 0;

UPDATE `sys_privilege` SET `related` = '50,49,621' WHERE (`id` = '310');

update t_sync_exam_log set third_relate_id = exam_id where id >0;

update t_sync_exam_student_score set third_relate_id = exam_id where id >0;

UPDATE t_sync_exam_student_score t
SET
    t.exam_id = (SELECT
                     exam_id
                 FROM
                     exam_student es
                 WHERE
                         t.school_id = es.school_id
                   AND t.paper_number = es.paper_number
                   AND t.exam_number = es.ticket_number);

UPDATE t_sync_exam_log t
SET
    t.exam_id = (SELECT
                     a.exam_id
                 FROM
                     (SELECT DISTINCT
                          school_id, exam_id, third_relate_id
                      FROM
                          t_sync_exam_student_score) a
                 WHERE
                         t.school_id = a.school_id
                   AND t.third_relate_id = a.third_relate_id);