-- 1.3.14

USE `stmms_ft`;

ALTER TABLE eb_exam_student ADD COLUMN `inspected`	tinyint(1) NOT NULL COMMENT '当前轮次是否已审核';
ALTER TABLE eb_exam_student ADD COLUMN `score_verify_user`	int(11) DEFAULT NULL COMMENT '成绩校验人ID';
ALTER TABLE eb_exam_student ADD COLUMN `score_verify_time`	datetime DEFAULT NULL COMMENT '成绩校验时间';
ALTER TABLE eb_exam_student ADD COLUMN `score_verify_flagged`	tinyint(1) DEFAULT NULL COMMENT '成绩校验标记结果';
UPDATE eb_exam_student set inspected=1,subjective_status='MARKED' where subjective_status='INSPECTED';

ALTER TABLE eb_exam_subject ADD COLUMN `inspect_round` 	int(11) NOT NULL DEFAULT 1 COMMENT '复核轮数';
ALTER TABLE eb_inspect_history ADD COLUMN `inspect_round` 	int(11) NOT NULL DEFAULT 1 COMMENT '复核轮数';
ALTER TABLE eb_exam_subject ADD COLUMN `selective` 	tinyint(1) NOT NULL DEFAULT 0 COMMENT '选做题科目';

ALTER TABLE b_school ADD COLUMN `double_track`  tinyint(1)  NOT NULL DEFAULT 0 COMMENT '双评轨迹';
ALTER TABLE b_school ADD COLUMN `group_delete_check` tinyint(1)  NOT NULL COMMENT '开启删除分组授权码';

-- 更新选做题科目数据
UPDATE eb_exam_subject o,
 (
	SELECT
		s.exam_id,
		s. CODE
	FROM
		eb_exam_subject s
	INNER JOIN (
		SELECT DISTINCT
			exam_id,
			subject_code
		FROM
			eb_selective_group
	) aa ON s.exam_id = aa.exam_id
	AND s. CODE = aa.subject_code
) n
SET o.selective = 1
WHERE
	o.exam_id = n.exam_id
AND o. CODE = n. CODE;

-- 更新复核记录数据
-- 备份
DROP TABLE IF EXISTS `eb_inspect_history_bak`;
CREATE TABLE `eb_inspect_history_bak` 
(
	`id` 		INT (11) NOT NULL AUTO_INCREMENT COMMENT '主键',
	`exam_id`	INT (11) NOT NULL COMMENT '考试ID',
	`subject_code` 	VARCHAR (32) NOT NULL COMMENT '科目代码',
	`inspect_time` 	DATETIME NOT NULL COMMENT '复核时间',
	`inspector_id` 	INT (11) DEFAULT NULL COMMENT '复核人ID',
	`student_id` 	INT (11) DEFAULT NULL COMMENT '考生ID',
	PRIMARY KEY (`id`),
	KEY `index1` (`student_id`)
) ENGINE = INNODB 
	DEFAULT CHARSET = utf8mb4 COMMENT = '复核记录表';
	
INSERT INTO `eb_inspect_history_bak` (
	`id`,
	`exam_id`,
	`subject_code`,
	`inspect_time`,
	`inspector_id`,
	`student_id`
) SELECT
	t.id,
	t.exam_id,
	t.subject_code,
	t.inspect_time,
	t.inspector_id,
	t.student_id
FROM
	eb_inspect_history t;
-- 保留最后一条记录	
DELETE t1 FROM eb_inspect_history t1
    INNER JOIN eb_inspect_history t2
WHERE
    t1.id < t2.id AND t1.student_id = t2.student_id;

ALTER TABLE eb_inspect_history DROP INDEX index1;
ALTER TABLE eb_inspect_history ADD UNIQUE INDEX index1(`student_id`, `inspect_round`);

ALTER TABLE eb_exam ADD COLUMN `inspect_unrepeated`	tinyint(1) NOT NULL DEFAULT 0 COMMENT '全卷多次复核时不能为同一账号';
ALTER TABLE eb_exam ADD COLUMN `inspect_scroll_bottom`	tinyint(1) NOT NULL DEFAULT 0 COMMENT '全卷复核时强制试卷拉到底部';
ALTER TABLE eb_exam ADD COLUMN `remark_count`	int(11) DEFAULT NULL COMMENT '回评卷数';
ALTER TABLE eb_exam ADD COLUMN `show_objective_score`	tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否显示客观分';

DROP TABLE IF EXISTS `eb_score_verify`;
CREATE TABLE `eb_score_verify` 
(
	`id`           int(11)     NOT NULL AUTO_INCREMENT COMMENT '主键',
    `exam_id`			int(11)     NOT NULL COMMENT '考试ID',
  	`user_id`		int(11) 	NOT NULL COMMENT '操作人ID',
    `student_id`			int(11)     NOT NULL COMMENT '考生ID',
  	`flagged`		tinyint(1)  NOT NULL DEFAULT 0 COMMENT '是否被标记',
  	`viewed`		tinyint(1)  NOT NULL DEFAULT 0 COMMENT '是否被查看',
  	`create_time`  		datetime     NOT NULL COMMENT '创建时间',
  	`verify_time`  		datetime     DEFAULT NULL COMMENT '校验时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index1` (`exam_id`,`user_id`,`student_id`)
)  ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='成绩校验';
  
-- 卡格式相关
ALTER TABLE eb_answer_card ADD COLUMN `dpi` 	int(11) DEFAULT NULL COMMENT 'dpi';
ALTER TABLE eb_answer_card ADD COLUMN `code` 	varchar(64) DEFAULT NULL COMMENT '混扫代码';
CREATE TABLE `eb_answer_card_subject` 
(
	`id`           		int(11)     	NOT NULL AUTO_INCREMENT COMMENT '主键',
    `exam_id`			int(11)     	NOT NULL COMMENT '考试ID',
    `card_number`		int(11)     	NOT NULL COMMENT '卡格式号',
  	`subject_code`		varchar(128)	NOT NULL COMMENT '科目代码',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index1` (`exam_id`,`card_number`,`subject_code`)
)  ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='卡格式科目关联关系';

-- 卡格式科目关联关系数据订正
INSERT INTO `eb_answer_card_subject` (
	`exam_id`,
	`subject_code`,
	`card_number`
) SELECT
	b.exam_id,
	b.subject_code,
	b.number
FROM
	eb_answer_card b
WHERE
	b.subject_code is not  NULL;
	
-- 仲裁轨迹
ALTER TABLE m_arbitrate_history ADD COLUMN	`unanswered_count`  int(11)	 DEFAULT NULL COMMENT '未作答的步骤数量';
UPDATE m_arbitrate_history set unanswered_count=0;

# Dump of table m_header_tag
# ------------------------------------------------------------

DROP TABLE IF EXISTS `m_header_tag`;

CREATE TABLE `m_header_tag`
(
    `id`           int(11)     NOT NULL AUTO_INCREMENT COMMENT '主键',
    `student_id`   int(11)     NOT NULL COMMENT '考生ID',
	`group_number` int(11)     NOT NULL COMMENT '大题题号',
	`user_id`      int(11)     NOT NULL COMMENT '用户ID',
    `tag_name`     varchar(64) NOT NULL COMMENT '标记内容',
    `position_x`   double      NOT NULL COMMENT 'X轴位置',
    `position_y`   double      NOT NULL COMMENT 'Y轴位置',
    `offset_index` int(11)     NOT NULL COMMENT '裁切图序号',
    `offset_x`     int(11)     NOT NULL COMMENT '裁切图X轴坐标',
    `offset_y`     int(11)     NOT NULL COMMENT '裁切图Y轴坐标',
    PRIMARY KEY (`id`),
    KEY `index1` (`student_id`,`group_number`),
	KEY `index2` (`user_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='组长特殊标记表';


# Dump of table m_header_track
# ------------------------------------------------------------

DROP TABLE IF EXISTS `m_header_track`;

CREATE TABLE `m_header_track`
(
	`student_id`      int(11)      NOT NULL COMMENT '考生ID',
    `question_number` varchar(128) NOT NULL COMMENT '完整题号',
    `number`          int(11)      NOT NULL COMMENT '序号',
    `exam_id`         int(11)      NOT NULL COMMENT '考试ID',
    `subject_code`    varchar(32)  NOT NULL COMMENT '科目代码',
    `group_number`    int(11)      NOT NULL COMMENT '大题题号',
    `user_id`         int(11)      NOT NULL COMMENT '用户ID',
    `score`           double       NOT NULL COMMENT '给分',
    `position_x`      double       NOT NULL COMMENT 'X轴位置',
    `position_y`      double       NOT NULL COMMENT 'Y轴位置',
    `offset_index`    int(11)      NOT NULL COMMENT '裁切图序号',
    `offset_x`        int(11)      NOT NULL COMMENT '裁切图X轴坐标',
    `offset_y`        int(11)      NOT NULL COMMENT '裁切图Y轴坐标',
    `unanswered`      tinyint(1)   NOT NULL COMMENT '未作答',
    PRIMARY KEY (`student_id`, `question_number`, `number`),
    KEY `index1` (`student_id`, `group_number`),
    KEY `index2` (`user_id`),
    KEY `index3` (`exam_id`, `subject_code`, `group_number`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='组长轨迹给分表';

-- 试评 
ALTER TABLE eb_exam_student ADD COLUMN  `is_trial`      tinyint(1)  NOT NULL DEFAULT 0 COMMENT '是否试评';
-- 更新试评数据
UPDATE eb_exam_student set is_trial=1 where subjective_status='TRIAL';
UPDATE eb_exam_student set subjective_status='UNMARK' where subjective_status='TRIAL';
UPDATE eb_exam_student o,(SELECT DISTINCT s.student_id FROM m_trial_library s) n
SET o.is_trial = 1 WHERE o.id = n.student_id;
	
DROP TABLE IF EXISTS `eb_user_student`;
CREATE TABLE `eb_user_student`
(
    `id`           int(11)     NOT NULL AUTO_INCREMENT COMMENT '主键',
    `user_id`      int(11)     NOT NULL COMMENT '用户ID',
    `exam_number` 	varchar(64)	   NOT NULL COMMENT '考生ID',
    PRIMARY KEY (`id`),
    UNIQUE KEY `index1` (`user_id`, `exam_number`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='用户考生关联表';

-- 菜单权限  
DROP TABLE IF EXISTS `b_privilege`;
CREATE TABLE `b_privilege`
(
    `id`             int(11)		NOT NULL AUTO_INCREMENT COMMENT '主键',
    `code`           varchar(64)	NOT NULL COMMENT '权限CODE',
    `name`           varchar(64)	NOT NULL COMMENT '名称',
    `parent_code`    varchar(64)	NOT NULL COMMENT '父权限CODE',
    `privilege_type` varchar(64)	NOT NULL COMMENT '类型',
    `privilege_uri`  varchar(64)	DEFAULT NULL COMMENT 'URI',
    `seq`            int(11)		NOT NULL COMMENT '排序',
    `level`          int(11)		NOT NULL COMMENT '树结构层级',
    `icon` 			 varchar(64)	DEFAULT NULL COMMENT '图表',
    `i18n` 			 varchar(64)	DEFAULT NULL COMMENT '国际化',
    PRIMARY KEY (`id`),
    UNIQUE KEY `IDX_PRIVILEGE_01` (`code`),
    KEY `IDX_PRIVILEGE_02` (`parent_code`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='权限表';


DROP TABLE IF EXISTS `b_role_privilege`;
CREATE TABLE `b_role_privilege`
(
	`id`				int(11)     NOT NULL AUTO_INCREMENT COMMENT '主键',
	`school_id` 		int(11) 	NOT NULL COMMENT '学校ID',
    `role_code`			varchar(64) NOT NULL COMMENT '角色CODE',
    `privilege_code`	varchar(64)	NOT NULL COMMENT '权限CODE',
    `enable`       		tinyint(1)	NOT NULL COMMENT '启用禁用',
    PRIMARY KEY (`id`),
    UNIQUE KEY `IDX_ROLE_PRIVILEGE_01`(`school_id`,`role_code`, `privilege_code`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='角色权限关联表';
  
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`,`icon`,`i18n`)
VALUES ('user_list', '用户管理', 'root_code', 'MENU', '/admin/user/list', 10,1,'icon-user','index.user');
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`,`icon`,`i18n`)
VALUES ('exam_list', '考试管理', 'root_code', 'MENU', '/admin/exam/list', 20,1,'icon-th-list','index.exam');
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`,`icon`,`i18n`)
VALUES ('exam_student', '考生管理', 'root_code', 'MENU', '/admin/exam/student', 30,1,'icon-user','index.student');
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`,`icon`,`i18n`)
VALUES ('exam_paper', '科目管理', 'root_code', 'MENU', '/admin/exam/paper', 40,1,'icon-book','index.paper');
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`,`icon`,`i18n`)
VALUES ('exam_scan', '扫描进度', 'root_code', 'MENU', '/admin/exam/scan', 50,1,'icon-print','index.scan');
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`,`icon`,`i18n`)
VALUES ('exam_mark', '评卷管理', 'root_code', 'MENU', '/admin/basic/role/info/sub/page', 60,1,'icon-pencil','index.mark');
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`,`privilege_uri`,`level`, `seq`)
VALUES ('exam_mark-mark', '评卷进度', 'exam_mark', 'PAGE','/admin/exam/mark', 2,1);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`,`privilege_uri`,`level`, `seq`)
VALUES ('exam_mark-group', '分组管理', 'exam_mark', 'PAGE','/admin/exam/group', 2,2);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`,`level`, `seq`)
VALUES ('exam_mark-group-add', '新增', 'exam_mark-group', 'BUTTON', 3,1);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`,`level`, `seq`)
VALUES ('exam_mark-group-edit', '修改', 'exam_mark-group', 'BUTTON', 3,2);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`,`level`, `seq`)
VALUES ('exam_mark-group-reset_edit', '重置修改', 'exam_mark-group', 'BUTTON', 3,3);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`,`level`, `seq`)
VALUES ('exam_mark-group-delete', '删除', 'exam_mark-group', 'BUTTON', 3,4);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`,`privilege_uri`,`level`, `seq`)
VALUES ('exam_mark-marker', '评卷员管理', 'exam_mark', 'PAGE','/admin/exam/marker', 2,3);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`,`level`, `seq`)
VALUES ('exam_mark-marker-reset', '重置', 'exam_mark-marker', 'BUTTON', 3,1);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`,`privilege_uri`,`level`, `seq`)
VALUES ('exam_mark-trial', '试评管理', 'exam_mark', 'PAGE','/admin/exam/trial', 2,4);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`,`privilege_uri`,`level`, `seq`)
VALUES ('exam_mark-library', '任务管理', 'exam_mark', 'PAGE','/admin/exam/library', 2,5);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`,`level`, `seq`)
VALUES ('exam_mark-library-inspect', '复核', 'exam_mark-library', 'BUTTON', 3,1);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`,`level`, `seq`)
VALUES ('exam_mark-library-reject', '打回', 'exam_mark-library', 'BUTTON', 3,2);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`,`privilege_uri`,`level`, `seq`)
VALUES ('exam_mark-arbitrate', '仲裁管理', 'exam_mark', 'PAGE','/admin/exam/arbitrate', 2,6);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`,`privilege_uri`,`level`, `seq`)
VALUES ('exam_mark-quality', '质量监控', 'exam_mark', 'PAGE','/admin/exam/quality', 2,7);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`,`icon`,`i18n`)
VALUES ('exam_reject_list', '打回试卷', 'root_code', 'MENU', '/admin/basic/role/info/sub/page', 70,1,'icon-bookmark','index.reject');
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`,`privilege_uri`,`level`, `seq`)
VALUES ('exam_reject_list-paper', '打回卷', 'exam_reject_list', 'PAGE','/admin/exam/reject/list', 2,1);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`,`privilege_uri`,`level`, `seq`)
VALUES ('exam_reject_list-history', '打回记录', 'exam_reject_list', 'PAGE','/admin/exam/reject/history', 2,2);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`,`icon`,`i18n`)
VALUES ('exam_problem_history', '问题试卷', 'root_code', 'MENU', '/admin/exam/problem/history', 80,1,'icon-tag','index.problem');
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`,`level`, `seq`)
VALUES ('exam_problem_history-reset', '重置', 'exam_problem_history', 'BUTTON', 2,1);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`,`level`, `seq`)
VALUES ('exam_problem_history-batch_reset', '批量重置', 'exam_problem_history', 'BUTTON', 2,2);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`,`icon`,`i18n`)
VALUES ('exam_inspected_info', '成绩复核', 'root_code', 'MENU', '/admin/basic/role/info/sub/page', 90,1,'icon-flag','index.inspected');
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`,`privilege_uri`,`level`, `seq`)
VALUES ('exam_inspected_info-info', '成绩复核进度', 'exam_inspected_info', 'PAGE','/admin/exam/inspected/info', 2,1);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`,`privilege_uri`,`level`, `seq`)
VALUES ('exam_inspected_info-list', '全卷复核', 'exam_inspected_info', 'PAGE','/admin/exam/inspected/list', 2,2);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`,`level`, `seq`)
VALUES ('exam_inspected_info-next_round', '再次复核', 'exam_inspected_info-list', 'BUTTON', 3,1);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`,`privilege_uri`,`level`, `seq`)
VALUES ('exam_inspected_info-score_verify', '成绩校验', 'exam_inspected_info', 'PAGE','/admin/exam/score/verify/init', 2,3);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`,`icon`,`i18n`)
VALUES ('exam_score', '成绩查询', 'root_code', 'MENU', '/admin/exam/score', 100,1,'icon-search','index.score');
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`,`level`, `seq`)
VALUES ('exam_score-export', '导出', 'exam_score', 'BUTTON', 2,1);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`,`icon`,`i18n`)
VALUES ('exam_report_subject', '总量分析', 'root_code', 'MENU', '/admin/exam/reportSubject', 110,1,'icon-signal','index.report.total');
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`,`icon`,`i18n`)
VALUES ('exam_report_subject_range', '科目分析', 'root_code', 'MENU', '/admin/exam/reportSubjectRange', 120,1,'icon-asterisk','index.report.subject');
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`,`icon`,`i18n`)
VALUES ('exam_check_answer', '数据检查', 'root_code', 'MENU', '/admin/exam/check/answer', 130,1,'icon-check','index.check');
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`,`icon`,`i18n`)
VALUES ('operation_log', '操作日志', 'root_code', 'MENU', '/admin/operation/log', 140,1,'icon-tasks','index.log');

  
INSERT INTO b_role_privilege (`school_id`, `role_code`, `privilege_code`,`enable`)
SELECT s.id,'SCHOOL_ADMIN',p.`code`,1 from b_privilege p join b_school s;

INSERT INTO b_role_privilege (`school_id`, `role_code`, `privilege_code`,`enable`)
SELECT s.id,'SUBJECT_HEADER',p.`code`,1 from b_privilege p join b_school s   
where p.`code` in('exam_mark','exam_mark-mark','exam_mark-group','exam_mark-marker','exam_mark-trial','exam_mark-library'
,'exam_mark-library-inspect','exam_mark-library-reject','exam_mark-arbitrate','exam_mark-quality','exam_reject_list'
,'exam_reject_list-paper','exam_reject_list-history','exam_problem_history','exam_problem_history-reset'
,'exam_problem_history-batch_reset'
,'exam_inspected_info','exam_inspected_info-info','exam_inspected_info-list','exam_inspected_info-next_round'
,'exam_score','exam_score-export','exam_report_subject','exam_report_subject_range');

INSERT INTO b_role_privilege (`school_id`, `role_code`, `privilege_code`,`enable`)
SELECT s.id,'COLLEGE_ADMIN',p.`code`,1 from b_privilege p join b_school s   
where p.`code` in('exam_mark','exam_mark-mark','exam_mark-group','exam_mark-marker','exam_mark-trial','exam_mark-library'
,'exam_mark-library-inspect','exam_mark-library-reject','exam_mark-arbitrate','exam_mark-quality','exam_reject_list'
,'exam_reject_list-paper','exam_reject_list-history','exam_problem_history','exam_problem_history-reset'
,'exam_problem_history-batch_reset'
,'exam_inspected_info','exam_inspected_info-info','exam_inspected_info-list','exam_inspected_info-next_round'
,'exam_score','exam_score-export','exam_report_subject','exam_report_subject_range');

INSERT INTO b_role_privilege (`school_id`, `role_code`, `privilege_code`,`enable`)
SELECT s.id,'INSPECTOR',p.`code`,1 from b_privilege p join b_school s   
where p.`code` in('exam_inspected_info','exam_inspected_info-info','exam_inspected_info-list','exam_inspected_info-next_round');

INSERT INTO b_role_privilege (`school_id`, `role_code`, `privilege_code`,`enable`)
SELECT s.id,'SCHOOL_VIEWER',p.`code`,1 from b_privilege p join b_school s   
where p.`code` in('exam_score','exam_report_subject','operation_log');

DROP TABLE IF EXISTS `b_role_info`;
CREATE TABLE `b_role_info`
(
    `id`             int(11)     NOT NULL AUTO_INCREMENT COMMENT '主键',
    `school_id` 	 int(11)	 NOT NULL COMMENT '学校ID',
    `code`           varchar(64) NOT NULL COMMENT '角色CODE',
    `name`           varchar(64) NOT NULL COMMENT '名称',
    `seq`            int(11)     NOT NULL COMMENT '排序',
    `updater_id` 	 int(11)	 DEFAULT NULL COMMENT '更新人ID',
    `update_time`	 datetime    DEFAULT NULL COMMENT '更新时间', 
    PRIMARY KEY (`id`),
    UNIQUE KEY `IDX_ROLE_INFO_01` (`school_id`,`code`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='角色表';

DROP TABLE IF EXISTS `b_role_temp`;
CREATE TABLE `b_role_temp`
(
    `id`             int(11)      NOT NULL AUTO_INCREMENT,
    `code`           varchar(50) NOT NULL,
    `name`           varchar(50) NOT NULL,
    `seq`            int(11)         NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `IDX_ROLE_TEMP_01` (`code`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_bin;
INSERT INTO `b_role_temp` (`code`, `name`, `seq`) VALUES ('SYS_ADMIN', '系统管理员', 1);
INSERT INTO `b_role_temp` (`code`, `name`, `seq`) VALUES ('SCHOOL_ADMIN', '学校管理员', 2);
INSERT INTO `b_role_temp` (`code`, `name`, `seq`) VALUES ('SCANNER', '扫描员', 3);
INSERT INTO `b_role_temp` (`code`, `name`, `seq`) VALUES ('SUBJECT_HEADER', '科组长', 4);
INSERT INTO `b_role_temp` (`code`, `name`, `seq`) VALUES ('MARKER', '评卷员', 5);
INSERT INTO `b_role_temp` (`code`, `name`, `seq`) VALUES ('SCHOOL_VIEWER', '学校查询员', 6);
INSERT INTO `b_role_temp` (`code`, `name`, `seq`) VALUES ('SCHOOL_DEV', '学校接口调用', 7);
INSERT INTO `b_role_temp` (`code`, `name`, `seq`) VALUES ('INSPECTOR', '复核员', 8);
INSERT INTO `b_role_temp` (`code`, `name`, `seq`) VALUES ('SCAN_ADMIN', '扫描管理员', 9);
INSERT INTO `b_role_temp` (`code`, `name`, `seq`) VALUES ('COLLEGE_ADMIN', '学院管理员', 10);

  
INSERT INTO `b_role_info` (`school_id`,`code`, `name`, `seq`)
SELECT s.id,p.`code`,p.name,p.seq from b_role_temp p join b_school s;

DROP TABLE IF EXISTS `b_role_temp`;













-- 1.3.6 college_scan_multi

use college_scan_multi;

TRUNCATE TABLE college_scan_multi.absent;
TRUNCATE TABLE college_scan_multi.append_scan;
TRUNCATE TABLE college_scan_multi.baseinfo;
TRUNCATE TABLE college_scan_multi.baseinfolog;
TRUNCATE TABLE college_scan_multi.campus;
TRUNCATE TABLE college_scan_multi.card_defcount;
--  TRUNCATE TABLE import_sign_page;
TRUNCATE TABLE college_scan_multi.kslb;
TRUNCATE TABLE college_scan_multi.config;
TRUNCATE TABLE college_scan_multi.registqty;
TRUNCATE TABLE college_scan_multi.subject_code_kgt;
TRUNCATE TABLE college_scan_multi.task_master;

-- DROP table college_scan_multi.check_omr;
-- drop table college_scan_multi.check_student;
-- drop table college_scan_multi.cliprect;
-- drop table college_scan_multi.objective;
-- drop table college_scan_multi.ocrdata;
-- drop table college_scan_multi.omrresult;
-- drop table college_scan_multi.registno;
-- drop table college_scan_multi.sign_page;
-- drop table college_scan_multi.task_child;
-- drop table college_scan_multi.upload_file_list;
-- drop table college_scan_multi.verify_check_info;
-- drop table college_scan_multi.import_sign_page;

-- ----------------------------
-- Table structure for upload_file_list
-- ----------------------------
DROP TABLE IF EXISTS college_scan_multi.`upload_file_list`;
CREATE TABLE college_scan_multi.`upload_file_list` (
  `exam_id` int(11) NOT NULL,
  `subject_code` varchar(30) NOT NULL,
  `subject_name` varchar(50) DEFAULT NULL,
  `is_used` int(1) DEFAULT NULL,
  `file_description` varchar(100) NOT NULL,
  `file_type` int(1) NOT NULL,
  `down_url` varchar(200) DEFAULT NULL,
  `DiskSerNo` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`exam_id`,`file_description`,`file_type`,`subject_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for task_child
-- ----------------------------
DROP TABLE IF EXISTS college_scan_multi.`task_child`;
CREATE TABLE college_scan_multi.`task_child` (
  `exam_id` int(11) NOT NULL,
  `exam_number` varchar(30) NOT NULL,
  `task_id` varchar(30) NOT NULL,
  `check_times1` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `check_ip1` varchar(30) DEFAULT NULL,
  `check_status1` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`exam_id`,`exam_number`,`task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for sign_page
-- ----------------------------
DROP TABLE IF EXISTS college_scan_multi.`sign_page`;
CREATE TABLE college_scan_multi.`sign_page` (
  `exam_id` int(11) NOT NULL,
  `exam_number` varchar(30) NOT NULL,
  `exam_site` varchar(50) DEFAULT NULL,
  `college` varchar(64) DEFAULT NULL,
  `page_no` varchar(11) NOT NULL,
  `xh` int(10) NOT NULL,
  `Card_DefCount` int(3) DEFAULT NULL,
  `subject_code` varchar(30) DEFAULT NULL,
  `sign_page_src` longtext,
  `scan_remark` longtext,
  `Card_type` varchar(50) DEFAULT NULL,
  `img_name` varchar(50) NOT NULL,
  `is_upload` tinyint(1) unsigned zerofill NOT NULL DEFAULT '0',
  `DiskSerNo` varchar(30) DEFAULT NULL,
  `check_status` int(2) DEFAULT NULL,
  PRIMARY KEY (`img_name`,`xh`,`exam_id`),
  KEY `exam_number` (`exam_number`,`exam_site`,`page_no`,`subject_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for registno
-- ----------------------------
DROP TABLE IF EXISTS college_scan_multi.`registno`;
CREATE TABLE college_scan_multi.`registno` (
  `DiskSerNo` varchar(30) DEFAULT NULL,
  `ExamId` int(11) NOT NULL,
  `RegistNo` varchar(30) NOT NULL,
  `OCRTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `PicSrc` longtext,
  `RegistCount` int(3) DEFAULT NULL,
  `isUpload` int(1) NOT NULL DEFAULT '0',
  `UpError` varchar(255) DEFAULT NULL,
  `UploadTime` varchar(30) DEFAULT NULL,
  `ServerPath` longtext,
  `ServerAnswer` longtext,
  `LoginName` varchar(30) DEFAULT NULL,
  `loginPwd` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`ExamId`,`RegistNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for omrresult
-- ----------------------------
DROP TABLE IF EXISTS college_scan_multi.`omrresult`;
CREATE TABLE college_scan_multi.`omrresult` (
  `examid` int(11) DEFAULT '0',
  `subjectCode` varchar(30) DEFAULT NULL,
  `examNumber` varchar(30) DEFAULT '',
  `FirstResult` longtext,
  `SecondResult` longtext,
  `newFirstResult` longtext,
  `newSecondResult` longtext,
  `newFirst` longtext,
  `newSecond` longtext,
  `Status` int(1) DEFAULT NULL,
  KEY `FirstResult` (`FirstResult`(250)),
  KEY `SecondResult` (`SecondResult`(250))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for ocrdata
-- ----------------------------
DROP TABLE IF EXISTS college_scan_multi.`ocrdata`;
CREATE TABLE college_scan_multi.`ocrdata` (
  `DiskSerNo` varchar(50) DEFAULT NULL,
  `DiskSerNo_KGT` varchar(50) DEFAULT NULL,
  `Batch` varchar(10) DEFAULT NULL,
  `DeviceId` int(5) DEFAULT NULL,
  `ExamId` int(5) NOT NULL,
  `CampusCode` varchar(5) DEFAULT NULL,
  `CampusName` varchar(30) DEFAULT NULL,
  `exam_site` varchar(50) DEFAULT NULL,
  `college` varchar(64) DEFAULT NULL,
  `SubjectCode` varchar(30) DEFAULT NULL,
  `SubjectName` varchar(50) DEFAULT NULL,
  `OCRTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `OCRUser` varchar(30) DEFAULT NULL,
  `student_Code` varchar(50) DEFAULT NULL,
  `ScanNumber` varchar(30) DEFAULT NULL,
  `examNumber` varchar(30) NOT NULL,
  `examName` varchar(50) DEFAULT NULL,
  `StudentID` varchar(30) DEFAULT NULL,
  `sheetSrc` longtext,
  `sheetCount` int(3) DEFAULT NULL,
  `sliceSrc` longtext,
  `slicePic` longtext,
  `sliceCount` int(3) DEFAULT NULL,
  `absent` int(1) DEFAULT NULL,
  `wj` int(1) DEFAULT NULL,
  `check_absent_status` int(1) DEFAULT NULL,
  `paper_type` varchar(5) DEFAULT NULL,
  `card_type` varchar(50) DEFAULT NULL,
  `card_name` varchar(50) DEFAULT NULL,
  `omrUp` int(1) DEFAULT NULL,
  `omrResult` longtext,
  `FirstResult` longtext,
  `SecondResult` longtext,
  `FirstResultBack` longtext,
  `SecondResultback` longtext,
  `ScanRemark` longtext,
  `kgtpoint` longtext,
  `slicePoint` longtext,
  `ans_checkIp1` varchar(30) DEFAULT NULL,
  `ans_checkIp2` varchar(30) DEFAULT NULL,
  `ans_checkIp3` varchar(30) DEFAULT NULL,
  `misspage` varchar(30) DEFAULT NULL,
  `misspageStatusCheck` varchar(10) DEFAULT NULL,
  `ScanPagelist` varchar(30) DEFAULT NULL,
  `scanPageCount` int(5) DEFAULT NULL,
  `isUpload` int(3) NOT NULL DEFAULT '0',
  `isUpload_kgt` int(3) NOT NULL DEFAULT '0',
  `UpError` longtext,
  `UploadTime` varchar(30) DEFAULT NULL,
  `ServerPath` longtext,
  `ServerAnswer` longtext,
  `LoginName` varchar(30) DEFAULT '',
  `loginPwd` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`ExamId`,`examNumber`),
  KEY `fx_ExamId` (`ExamId`),
  KEY `fx_CampusCode` (`CampusCode`),
  KEY `fx_SubjectCode` (`SubjectCode`),
  KEY `fx_examNumber` (`examNumber`),
  KEY `fx_StudentID` (`StudentID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT;

-- ----------------------------
-- Table structure for objective
-- ----------------------------
DROP TABLE IF EXISTS college_scan_multi.`objective`;
CREATE TABLE college_scan_multi.`objective` (
  `DiskSerNo` varchar(50) DEFAULT NULL,
  `ExamID` int(11) NOT NULL,
  `SubjectCode` varchar(30) NOT NULL,
  `jData` longtext,
  `ChoStart` int(10) DEFAULT '0',
  `ChoEnd` int(10) DEFAULT '0',
  `MulitChoStart` int(10) DEFAULT '0',
  `MulitChoEnd` int(10) DEFAULT '0',
  `JudgeStart` int(10) DEFAULT '0',
  `JudgeEnd` int(10) DEFAULT '0',
  `mark` int(10) DEFAULT '0',
  `JudegMark` int(10) DEFAULT '0',
  `logtime` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Table structure for cliprect
-- ----------------------------
DROP TABLE IF EXISTS college_scan_multi.`cliprect`;
CREATE TABLE college_scan_multi.`cliprect` (
  `ExamID` int(11) DEFAULT NULL,
  `DiskSerNo` varchar(30) DEFAULT NULL,
  `FormID` int(3) DEFAULT NULL,
  `ClipCount` int(3) DEFAULT NULL,
  `ClitRect` longtext
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for check_student
-- ----------------------------
DROP TABLE IF EXISTS college_scan_multi.`check_student`;
CREATE TABLE college_scan_multi.`check_student` (
  `exam_id` int(11) NOT NULL,
  `exam_site` varchar(50) DEFAULT NULL,
  `student_code` varchar(50) DEFAULT NULL,
  `exam_number` varchar(30) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `subject_code` varchar(30) DEFAULT NULL,
  `subject_name` varchar(50) DEFAULT NULL,
  `is_upload` tinyint(1) NOT NULL DEFAULT '0',
  `scan_misspage_page` varchar(128) DEFAULT NULL,
  `ocrdata_scan_count` int(3) DEFAULT NULL,
  `ocrdata_scan_page` varchar(128) DEFAULT NULL,
  `sign_scan_count` int(3) DEFAULT NULL,
  `sign_scan_page` varchar(128) DEFAULT NULL,
  `card_def_count` int(3) DEFAULT NULL,
  `misspage` varchar(200) DEFAULT NULL,
  `remark` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`exam_id`,`exam_number`),
  KEY `exam_site` (`exam_site`),
  KEY `ocrdata_scan_page` (`ocrdata_scan_page`),
  KEY `sign_scan_page` (`sign_scan_page`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ----------------------------
-- Table structure for check_omr
-- ----------------------------
DROP TABLE IF EXISTS college_scan_multi.`check_omr`;
CREATE TABLE college_scan_multi.`check_omr` (
  `exam_id` int(11) NOT NULL,
  `student_id` varchar(30) NOT NULL,
  `subject_code` varchar(30) NOT NULL,
  `exam_number` varchar(30) NOT NULL,
  `sheetSrc` longtext,
  `check_times` varchar(30) DEFAULT NULL,
  `check_status` int(5) DEFAULT NULL,
  `check_ip` varchar(30) DEFAULT NULL,
  `newAnswer` longtext,
  `mark_dth` longtext,
  `admin_check_Ip` varchar(30) DEFAULT NULL,
  `admin_check_status` int(5) DEFAULT NULL,
  PRIMARY KEY (`exam_id`,`exam_number`),
  KEY `check_ip` (`check_ip`) USING BTREE,
  KEY `check_status` (`check_status`) USING BTREE,
  KEY `exam_number` (`exam_number`),
  KEY `exam_id` (`exam_id`),
  KEY `mark_dth` (`mark_dth`(768)),
  KEY `admin_check_Ip` (`admin_check_Ip`),
  KEY `admin_check_status` (`admin_check_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS college_scan_multi.`eb_exam_student`;
CREATE TABLE college_scan_multi.`eb_exam_student` (
  `auid` int(11) NOT NULL AUTO_INCREMENT,
  `exam_id` int(11) NOT NULL COMMENT '考试ID',
  `school_id` int(11) NOT NULL COMMENT '学校ID',
  `campus_code` int(11) NOT NULL DEFAULT '0',
  `campus_name` varchar(64) DEFAULT NULL COMMENT '学习中心名称',
  `subject_code` varchar(32) NOT NULL COMMENT '科目代码',
  `subject_name` varchar(128) DEFAULT NULL,
  `student_id` int(11) DEFAULT NULL,
  `secret_number` varchar(64) DEFAULT NULL,
  `exam_number` varchar(64) NOT NULL COMMENT '准考证号',
  `student_code` varchar(64) NOT NULL COMMENT '学号',
  `name` varchar(64) NOT NULL COMMENT '姓名',
  `package_code` varchar(64) DEFAULT NULL COMMENT '试卷袋编号',
  `exam_site` varchar(32) DEFAULT NULL COMMENT '考点',
  `exam_room` varchar(32) DEFAULT NULL COMMENT '考场',
  `remark` varchar(128) DEFAULT NULL COMMENT '备注',
  `subject_level` varchar(64) DEFAULT NULL COMMENT '层次',
  `subject_category` varchar(64) DEFAULT NULL COMMENT '专业类型',
  `college` varchar(64) NOT NULL COMMENT '学院',
  `class_name` varchar(64) NOT NULL COMMENT '班级',
  `teacher` varchar(64) NOT NULL COMMENT '任课老师',
  `is_upload` tinyint(1) unsigned zerofill NOT NULL DEFAULT '0',
  PRIMARY KEY (`exam_id`,`exam_number`),
  UNIQUE KEY `auid` (`auid`),
  KEY `subject_code` (`subject_code`),
  KEY `exam_number` (`exam_number`),
  KEY `student_code` (`student_code`),
  KEY `package_code` (`package_code`),
  KEY `exam_site` (`exam_site`),
  KEY `exam_room` (`exam_room`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS college_scan_multi.`ocrdata_history`;
CREATE TABLE college_scan_multi.`ocrdata_history` (
  `remark` varchar(100) DEFAULT NULL,
  `DiskSerNo` varchar(50) DEFAULT NULL,
  `DiskSerNo_KGT` varchar(50) DEFAULT NULL,
  `Batch` varchar(10) DEFAULT NULL,
  `DeviceId` int(5) DEFAULT NULL,
  `ExamId` int(5) NOT NULL,
  `CampusCode` varchar(5) DEFAULT NULL,
  `CampusName` varchar(30) DEFAULT NULL,
  `exam_site` varchar(50) DEFAULT NULL,
  `college` varchar(64) DEFAULT NULL,
  `SubjectCode` varchar(30) DEFAULT NULL,
  `SubjectName` varchar(50) DEFAULT NULL,
  `OCRTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `OCRUser` varchar(30) DEFAULT NULL,
  `student_Code` varchar(50) DEFAULT NULL,
  `ScanNumber` varchar(30) DEFAULT NULL,
  `examNumber` varchar(30) NOT NULL,
  `examName` varchar(50) DEFAULT NULL,
  `StudentID` varchar(30) DEFAULT NULL,
  `sheetSrc` longtext,
  `sheetCount` int(3) DEFAULT NULL,
  `sliceSrc` longtext,
  `slicePic` longtext,
  `sliceCount` int(3) DEFAULT NULL,
  `absent` int(1) DEFAULT NULL,
  `wj` int(1) DEFAULT NULL,
  `check_absent_status` int(1) DEFAULT NULL,
  `paper_type` varchar(5) DEFAULT NULL,
  `card_type` varchar(50) DEFAULT NULL,
  `card_name` varchar(50) DEFAULT NULL,
  `omrUp` int(1) DEFAULT NULL,
  `omrResult` longtext,
  `FirstResult` longtext,
  `SecondResult` longtext,
  `ScanRemark` longtext,
  `kgtpoint` longtext,
  `slicePoint` longtext,
  `ScanPagelist` varchar(30) DEFAULT NULL,
  `scanPageCount` int(5) DEFAULT NULL,
  `isUpload` int(3) DEFAULT '0',
  `isUpload_kgt` int(3) DEFAULT '0',
  KEY `ExamId` (`ExamId`),
  KEY `SubjectCode` (`SubjectCode`),
  KEY `examNumber` (`examNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS college_scan_multi.`import_sign_page`;
CREATE TABLE college_scan_multi.`import_sign_page` (
  `exam_id` int(5) NOT NULL,
  `exam_site` varchar(50) DEFAULT NULL,
  `exam_room` varchar(100) DEFAULT NULL,
  `student_code` varchar(50) NOT NULL,
  `student_name` varchar(100) DEFAULT NULL,
  `subject_code` varchar(30) NOT NULL,
  `subject_name` varchar(50) DEFAULT NULL,
  `exam_number` varchar(20) DEFAULT NULL,
  `pagelist` varchar(50) DEFAULT NULL,
  `is_absent` int(2) DEFAULT NULL,
  `edit_time` varchar(30) DEFAULT NULL,
  `edit_user` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`exam_id`,`student_code`,`subject_code`),
  KEY `student_code_2` (`student_code`),
  KEY `exam_number` (`exam_number`),
  KEY `subject_code` (`subject_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS college_scan_multi.`verify_check_info`;
CREATE TABLE college_scan_multi.`verify_check_info` (
  `exam_id` int(11) NOT NULL,
  `campus_code` int(30) DEFAULT NULL,
  `exam_number` varchar(30) NOT NULL,
  `subject_code` varchar(30) DEFAULT NULL,
  `exam_college` varchar(64) DEFAULT NULL,
  `exam_site` varchar(50) DEFAULT NULL,
  `slicePoint` longtext,
  `misspage` varchar(30) DEFAULT NULL,
  `sheet_src` longtext,
  `check_times` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `check_ip1` varchar(30) DEFAULT NULL,
  `check_status1` varchar(50) DEFAULT NULL,
  `check_msg_history1` varchar(300) DEFAULT NULL,
  `status_examine1` varchar(10) DEFAULT NULL,
  `check_ip2` varchar(30) DEFAULT NULL,
  `check_status2` varchar(50) DEFAULT NULL,
  `check_msg_history2` varchar(300) DEFAULT NULL,
  `status_examine2` varchar(10) DEFAULT NULL,
  `check_ip3` varchar(30) DEFAULT NULL,
  `check_status3` varchar(50) DEFAULT NULL,
  `check_msg_history3` varchar(300) DEFAULT NULL,
  `status_examine3` varchar(10) DEFAULT NULL,
  `Remark` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`exam_id`,`exam_number`),
  KEY `check_ip1` (`check_ip1`,`check_status1`,`check_msg_history1`),
  KEY `check_ip2` (`check_ip2`,`check_status2`,`check_msg_history2`),
  KEY `check_ip3` (`check_ip3`,`check_status3`,`check_msg_history3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;