USE teachcloud_db;

-- 更新试卷和标答文件存储路径格式
update mark_document md join mark_paper mp on md.exam_id = mp.exam_id and md.paper_number = mp.paper_number set md.file_path = mp.paper_file_path where md.type = 'PAPER';
update mark_document md join mark_paper mp on md.exam_id = mp.exam_id and md.paper_number = mp.paper_number set md.file_path = mp.answer_file_path where md.type = 'ANSWER';

-- 未归档试卷结构插入到原表中
insert into mark_question(id, exam_id, course_id, paper_number, paper_type, objective, main_number,sub_number,main_title, answer, total_score,option_count,interval_score,objective_policy, question_type,name,paper_index,page_index,objective_policy_score, create_id, create_time,update_id, update_time)
select mq.id, mq.exam_id, mp.course_id, mq.paper_number, mq.paper_type, mq.objective, mq.main_number,mq.sub_number,mq.main_title, mq.answer, mq.total_score,mq.option_count,mq.interval_score,mq.objective_policy, mq.question_type,mq.name,mq.paper_index,mq.page_index,mq.objective_policy_score, mq.create_id, mq.create_time,mq.update_id, mq.update_time from `mark_question_3.4.3` mq
     left join mark_paper mp on mq.exam_id = mp.exam_id and mq.paper_number = mp.paper_number where mp.archive = 0;

-- 扫描客观题结构插入新表中，3.4.2新加的表，将旧数据插入，用于识别对照任务
ALTER TABLE `scan_answer_card_question` CHANGE COLUMN `id` `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键' ;

insert into scan_answer_card_question(exam_id,
                                      paper_number,
                                      course_paper_id,
                                      serial_number,
                                      card_number,
                                      main_number,
                                      sub_number,
                                      option_count,
                                      question_type,
                                      paper_index,
                                      page_index,
                                      create_time)
SELECT
    mq.exam_id,
    mq.paper_number,
    mp.course_paper_id,
    mp.serial_number,
    sac.number,
    mq.main_number,
    mq.sub_number,
    mq.option_count,
    mq.question_type,
    mq.paper_index,
    mq.page_index,
    1747022400000
FROM
    `mark_question_3.4.3` mq
        JOIN
    mark_question_answer mqa ON mq.exam_id = mqa.exam_id
        AND mq.paper_number = mqa.paper_number and mq.main_number = mqa.main_number
        and mq.sub_number = mqa.sub_number
        join (select * from mark_paper where archive = 0) mp on mq.exam_id = mp.exam_id and mq.paper_number = mp.paper_number
        join (select * from scan_answer_card ) sac on mq.exam_id = sac.exam_id and mq.paper_number = sac.paper_number
WHERE
        mq.objective = 1 and not exists (select 1 from scan_answer_card_question sacq where mq.exam_id = sacq.exam_id and mq.paper_number = sacq.paper_number)
order by mq.exam_id, mq.paper_number, mq.main_number, mq.sub_number;

-- 更新是否开启AB卷的状态为默认0
update exam_task set open_ab = 0 where open_ab is null;

-- 更新卷型方式为FILL
update basic_card_rule set paper_type = 'FILL' where exam_number_style = 'FILL' and paper_type = 'PRINT';