shequ/addons/exam/install.sql

CREATE TABLE IF NOT EXISTS `__PREFIX__exam_cate` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `kind` enum('QUESTION','PAPER','ROOM','COURSE') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'QUESTION' COMMENT '种类',
    `level` enum('1','2','3') COLLATE utf8mb4_general_ci NOT NULL DEFAULT '1' COMMENT '类型',
    `name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT '名称',
    `parent_id` int(11) NOT NULL DEFAULT '0' COMMENT '父级',
    `sort` int(11) NOT NULL DEFAULT '1' COMMENT '排序',
    `remark` mediumtext COLLATE utf8mb4_general_ci COMMENT '简介',
    `deletetime` bigint(16) DEFAULT NULL COMMENT '删除时间',
    `status` ENUM('0','1') NOT NULL DEFAULT '1' COMMENT '状态:0=禁用,1=启用' COLLATE 'utf8_general_ci',
    PRIMARY KEY (`id`),
    KEY `parent_id` (`parent_id`),
    KEY `kind` (`kind`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='试题分类';


CREATE TABLE IF NOT EXISTS `__PREFIX__exam_config_info` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `ad_config` mediumtext COLLATE utf8mb4_general_ci COMMENT '广告位配置',
    `system_config` mediumtext COLLATE utf8mb4_general_ci COMMENT '系统配置',
    `wx_config` mediumtext COLLATE utf8mb4_general_ci COMMENT '微信配置',
    `page_config` mediumtext COLLATE utf8mb4_general_ci COMMENT '页面配置',
    PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='参数配置';


CREATE TABLE IF NOT EXISTS `__PREFIX__exam_grade` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `cate_id` int(11) unsigned NOT NULL COMMENT '所属分类',
    `user_id` int(11) unsigned NOT NULL COMMENT '考试用户',
    `paper_id` int(11) unsigned NOT NULL COMMENT '所属试卷',
    `mode` ENUM('RANDOM','FIX') NOT NULL DEFAULT 'RANDOM' COMMENT '选题模式' COLLATE 'utf8mb4_general_ci',
    `score` int(11) unsigned NOT NULL COMMENT '考试分数',
    `system_score` int(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '系统得分',
    `manual_score` int(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '人工判分',
    `is_pass` int(11) unsigned NOT NULL COMMENT '是否及格',
    `pass_score` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '及格线',
    `total_score` int(11) unsigned NOT NULL COMMENT '总分数',
    `total_count` int(11) unsigned NOT NULL COMMENT '总题数',
    `right_count` int(11) unsigned NOT NULL COMMENT '答对数',
    `error_count` int(11) unsigned NOT NULL COMMENT '答错数',
    `grade_time` bigint(16) unsigned NOT NULL COMMENT '考试用时',
    `date` CHAR(10) NOT NULL DEFAULT '' COMMENT '考试日期',
    `question_ids` VARCHAR(2000) NULL DEFAULT '' COMMENT '试卷ID集合' COLLATE 'utf8mb4_general_ci',
    `error_ids` VARCHAR(2000) NULL DEFAULT '' COMMENT '错题ID集合' COLLATE 'utf8mb4_general_ci',
    `user_answers` TEXT NULL DEFAULT NULL COMMENT '用户答案集合' COLLATE 'utf8mb4_general_ci',
    `configs` TEXT NULL DEFAULT NULL COMMENT '试卷选题配置' COLLATE 'utf8mb4_general_ci',
    `createtime` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
    `updatetime` bigint(16) unsigned DEFAULT NULL COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `user_id` (`user_id`),
    KEY `paper_id` (`paper_id`),
    KEY `work_type_id` (`cate_id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='考试成绩';


CREATE TABLE IF NOT EXISTS `__PREFIX__exam_notice` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '标题',
    `contents` varchar(2000) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '内容',
    `weigh` int(11) NOT NULL DEFAULT '1' COMMENT '排序',
    `status` enum('NORMAL','HIDDEN') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'NORMAL' COMMENT '状态',
    `createtime` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
    `updatetime` bigint(16) unsigned DEFAULT NULL COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='系统公告';


CREATE TABLE IF NOT EXISTS `__PREFIX__exam_paper` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `cate_id` int(11) unsigned NOT NULL COMMENT '试卷分类',
    `mode` enum('RANDOM','FIX') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'RANDOM' COMMENT '选题模式',
    `title` varchar(3000) COLLATE utf8mb4_general_ci NOT NULL COMMENT '试卷名称',
    `configs` varchar(3000) COLLATE utf8mb4_general_ci NOT NULL COMMENT '选题配置',
    `quantity` int(10) unsigned NOT NULL COMMENT '题目数量',
    `total_score` tinyint(3) unsigned NOT NULL COMMENT '试卷总分',
    `pass_score` tinyint(3) unsigned NOT NULL COMMENT '及格线',
    `limit_time` bigint(16) unsigned NOT NULL COMMENT '考试限时',
    `join_count` int(10) NOT NULL DEFAULT '0' COMMENT '参与人次',
    `day_limit_count` INT(10) NOT NULL DEFAULT '0' COMMENT '每日限制考试次数',
    `start_time` BIGINT(16) DEFAULT NULL COMMENT '开始时间',
    `end_time` BIGINT(16) UNSIGNED DEFAULT NULL COMMENT '过期时间',
    `is_only_room` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '仅用于考场',
    `status` enum('NORMAL','HIDDEN') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'NORMAL' COMMENT '状态',
    `createtime` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
    `updatetime` bigint(16) unsigned DEFAULT NULL COMMENT '修改时间',
    `deletetime` bigint(16) DEFAULT NULL COMMENT '删除时间',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `cate_id` (`cate_id`,`status`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='试卷';


CREATE TABLE IF NOT EXISTS `__PREFIX__exam_paper_question` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `paper_id` int(11) unsigned NOT NULL COMMENT '所属试卷',
    `question_id` int(11) unsigned NOT NULL COMMENT '试题',
    `score` int(10) unsigned NOT NULL COMMENT '分数',
    `sort` int(10) unsigned NOT NULL DEFAULT '1' COMMENT '排序',
    `answer_config` TEXT NULL DEFAULT NULL COMMENT '正确答案配置' COLLATE 'utf8mb4_general_ci',
    `createtime` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
    `updatetime` bigint(16) unsigned DEFAULT NULL COMMENT '修改时间',
    `deletetime` bigint(16) DEFAULT NULL COMMENT '删除时间',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `paper_id` (`paper_id`),
    KEY `question_id` (`question_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='试卷试题';


CREATE TABLE IF NOT EXISTS `__PREFIX__exam_question` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `cate_id` int(11) unsigned NOT NULL COMMENT '分类',
    `kind` enum('JUDGE','SINGLE','MULTI','FILL','SHORT','MATERIAL') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'JUDGE' COMMENT '试题类型',
    `title` varchar(1000) COLLATE utf8mb4_general_ci NOT NULL COMMENT '题目',
    `explain` varchar(2000) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '解析',
    `difficulty` enum('EASY','GENERAL','HARD') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'GENERAL' COMMENT '难度',
    `options_json` text COLLATE utf8mb4_general_ci NOT NULL COMMENT '选项',
    `options_img` varchar(1000) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '选项图片',
    `options_extend` TEXT NULL DEFAULT NULL COMMENT '选项扩展' COLLATE 'utf8mb4_general_ci',
    `answer` TEXT NOT NULL COMMENT '正确答案' COLLATE 'utf8mb4_general_ci',
    `status` enum('NORMAL','HIDDEN') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'NORMAL' COMMENT '状态',
    `is_material_child` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT '属于材料题子题：0=否，1=是',
    `material_question_id` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '所属材料题',
    `material_score` BIGINT(16) UNSIGNED DEFAULT NULL COMMENT '材料子题分数',
    `createtime` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
    `updatetime` bigint(16) unsigned DEFAULT NULL COMMENT '修改时间',
    `deletetime` bigint(16) DEFAULT NULL COMMENT '删除时间',
    PRIMARY KEY (`id`),
    KEY `kind` (`kind`,`status`) USING BTREE,
    KEY `cate_id` (`cate_id`,`kind`,`status`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='试题';


CREATE TABLE IF NOT EXISTS `__PREFIX__exam_question_collect` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` int(11) unsigned NOT NULL COMMENT '用户',
    `question_id` int(11) unsigned NOT NULL COMMENT '试题',
    `createtime` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
    `updatetime` bigint(16) unsigned DEFAULT NULL COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `question_id` (`question_id`) USING BTREE,
    KEY `user_id` (`user_id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='题目收藏';


CREATE TABLE IF NOT EXISTS `__PREFIX__exam_question_wrong` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) UNSIGNED NOT NULL COMMENT '用户',
    `question_id` INT(11) UNSIGNED NOT NULL COMMENT '试题',
    `user_answer` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '用户答案' COLLATE 'utf8mb4_general_ci',
    `kind` ENUM('PAPER','ROOM','TRAINING') NULL DEFAULT 'PAPER' COMMENT '来源：PAPER=试卷，ROOM=考场，TRAINING=练题' COLLATE 'utf8mb4_general_ci',
    `cate_id` INT(11) UNSIGNED NULL DEFAULT '0' COMMENT '所属题库',
    `paper_id` INT(11) UNSIGNED NULL DEFAULT '0' COMMENT '来源试卷',
    `room_id` INT(11) UNSIGNED NULL DEFAULT '0' COMMENT '来源考场',
    `createtime` BIGINT(16) UNSIGNED DEFAULT NULL COMMENT '创建时间',
    `updatetime` BIGINT(16) UNSIGNED NULL DEFAULT NULL COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `question_id` (`question_id`) USING BTREE,
    INDEX `kind` (`kind`) USING BTREE,
    INDEX `user_id` (`user_id`, `kind`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='错题记录';


CREATE TABLE IF NOT EXISTS `__PREFIX__exam_room` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '考场标题',
    `contents` varchar(1000) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '考场说明',
    `cate_id` int(11) NOT NULL COMMENT '考场分类',
    `paper_id` int(11) NOT NULL COMMENT '考试试卷',
    `people_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '限制考场人数',
    `start_time` bigint(16) DEFAULT NULL COMMENT '考试开始时间',
    `end_time` bigint(16) DEFAULT NULL COMMENT '考试结束时间',
    `weigh` int(11) NOT NULL DEFAULT '1' COMMENT '排序',
    `status` enum('NORMAL','HIDDEN') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'NORMAL' COMMENT '状态',
    `signup_mode` enum('NORMAL','PASSWORD','AUDIT') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'NORMAL' COMMENT '报名方式:NORMAL=普通模式，PASSWORD=密码模式，AUDIT=审核模式',
    `password` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '考场密码',
    `is_makeup` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否允许补考:0=关闭，1=开启',
    `makeup_count` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '补考次数',
    `is_rank` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否已排名',
    `signup_count` int(11) NOT NULL DEFAULT '0' COMMENT '报考人数',
    `grade_count` int(11) NOT NULL DEFAULT '0' COMMENT '考试人数',
    `pass_count` int(11) NOT NULL DEFAULT '0' COMMENT '及格人数',
    `pass_rate` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '及格率',
    `createtime` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
    `updatetime` bigint(16) unsigned DEFAULT NULL COMMENT '修改时间',
    `deletetime` bigint(16) DEFAULT NULL COMMENT '删除时间',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `paper_id` (`paper_id`),
    KEY `status` (`status`) USING BTREE,
    KEY `cate_id` (`status`,`cate_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='考试考场';


CREATE TABLE IF NOT EXISTS `__PREFIX__exam_room_grade` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` int(11) NOT NULL COMMENT '考试用户',
    `cate_id` int(11) unsigned NOT NULL COMMENT '所属分类',
    `room_id` int(11) unsigned NOT NULL COMMENT '所属考场',
    `paper_id` int(11) unsigned NOT NULL COMMENT '所属试卷',
    `mode` ENUM('RANDOM','FIX') NOT NULL DEFAULT 'RANDOM' COMMENT '选题模式' COLLATE 'utf8mb4_general_ci',
    `score` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '考试分数',
    `system_score` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT '系统得分',
    `manual_score` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT '人工判分',
    `is_pass` tinyint(3) NOT NULL DEFAULT '0' COMMENT '是否及格:0=不及格，1=及格',
    `is_makeup` tinyint(3) NOT NULL DEFAULT '0' COMMENT '是否是补考:0=否，1=是',
    `pass_score` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '及格线',
    `total_score` tinyint(3) NOT NULL DEFAULT '0' COMMENT '总分数',
    `total_count` tinyint(3) NOT NULL DEFAULT '0' COMMENT '总题数',
    `right_count` tinyint(3) NOT NULL DEFAULT '0' COMMENT '答对数',
    `error_count` tinyint(3) NOT NULL DEFAULT '0' COMMENT '答错数',
    `rank` tinyint(3) NOT NULL DEFAULT '0' COMMENT '本次考试排名',
    `is_pre` tinyint(3) NOT NULL DEFAULT '0' COMMENT '是否为预载入数据',
    `grade_time` bigint(16) unsigned NOT NULL COMMENT '考试用时',
    `question_ids` VARCHAR(2000) NULL DEFAULT '' COMMENT '试卷ID集合' COLLATE 'utf8mb4_general_ci',
    `error_ids` VARCHAR(2000) NULL DEFAULT '' COMMENT '错题ID集合' COLLATE 'utf8mb4_general_ci',
    `user_answers` TEXT NULL DEFAULT NULL COMMENT '用户答案集合' COLLATE 'utf8mb4_general_ci',
    `configs` TEXT NULL DEFAULT NULL COMMENT '试卷选题配置' COLLATE 'utf8mb4_general_ci',
    `createtime` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
    `updatetime` bigint(16) unsigned DEFAULT NULL COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `user_id` (`user_id`) USING BTREE,
    KEY `paper_id` (`paper_id`) USING BTREE,
    KEY `cate_id` (`cate_id`) USING BTREE,
    KEY `FK2_exam_room_grade_with_exam_room` (`room_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='考场考试成绩';


CREATE TABLE IF NOT EXISTS `__PREFIX__exam_room_signup` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `room_id` int(11) unsigned NOT NULL COMMENT '所属考场',
    `user_id` int(11) unsigned NOT NULL COMMENT '报名用户',
    `real_name` varchar(20) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '真实姓名',
    `phone` varchar(20) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机号码',
    `message` varchar(200) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '审核说明',
    `status` enum('0','1','2') COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '状态:0=未审核，1=报名成功，2=报名被拒绝',
    `createtime` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
    `updatetime` bigint(16) unsigned DEFAULT NULL COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `room_id` (`room_id`,`status`),
    KEY `user_id` (`user_id`,`status`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='考场报名';

-- 1.0.2版本，新增考卷每日限制考试次数
ALTER TABLE `__PREFIX__exam_paper` ADD COLUMN `day_limit_count` INT(10) NOT NULL DEFAULT '0' COMMENT '每日限制考试次数' AFTER `join_count`;
ALTER TABLE `__PREFIX__exam_grade` ADD COLUMN `date` CHAR(10) NOT NULL DEFAULT '' COMMENT '考试日期' AFTER `grade_time`;

-- 1.0.4版本，新增系统配置 - 页面配置
ALTER TABLE `__PREFIX__exam_config_info` ADD COLUMN `page_config` MEDIUMTEXT NULL DEFAULT NULL COMMENT '页面配置' COLLATE 'utf8mb4_general_ci' AFTER `wx_config`;


-- 1.0.6版本
-- 加大选项图片字段长度
ALTER TABLE `__PREFIX__exam_question` CHANGE COLUMN `options_img` `options_img` VARCHAR(1000) NULL DEFAULT NULL COMMENT '选项图片' COLLATE 'utf8mb4_general_ci' AFTER `options_json`;


-- 1.0.9版本
-- 题库新增图标
-- ALTER TABLE `__PREFIX__exam_cate` ADD COLUMN `icon` VARCHAR(200) NULL DEFAULT '' COMMENT '图标' AFTER `name`;

-- 用户信息
CREATE TABLE IF NOT EXISTS `__PREFIX__exam_user_info` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `type` ENUM('NORMAL','VIP_MONTH','VIP_YEAR','VIP_LIFE') NOT NULL DEFAULT 'NORMAL' COMMENT '用户类型:NORMAL=普通用户,VIP_MONTH=月卡会员,VIP_YEAR=年卡会员,VIP_LIFE=终身会员' COLLATE 'utf8mb4_general_ci',
    `member_config_id` INT(10) NOT NULL COMMENT '开通会员类型',
    `user_id` INT(11) NOT NULL DEFAULT '0' COMMENT '用户ID',
    `score` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '积分',
    `score_inc` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '累计获得积分',
    `score_dec` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '累计支出积分',
    `expire_time` BIGINT(16) UNSIGNED DEFAULT NULL COMMENT '过期时间',
    `createtime` BIGINT(16) NULL DEFAULT NULL COMMENT '创建时间',
    `updatetime` BIGINT(16) NULL DEFAULT NULL COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `user_id` (`user_id`) USING BTREE,
    INDEX `type` (`type`) USING BTREE,
    INDEX `member_config_id` (`member_config_id`, `expire_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户信息';

-- 1.0.11
-- 试卷加开始、过期时间，仅用于考场字段
ALTER TABLE `__PREFIX__exam_paper` ADD COLUMN `end_time` BIGINT(16) UNSIGNED DEFAULT NULL COMMENT '过期时间' AFTER `member_price`;
ALTER TABLE `__PREFIX__exam_paper`
    ADD COLUMN `start_time` BIGINT(16) DEFAULT NULL COMMENT '开始时间' AFTER `member_price`,
	ADD COLUMN `is_only_room` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '仅用于考场' AFTER `end_time`,
	ADD INDEX `is_only_room` (`is_only_room`),
	ADD INDEX `start_time` (`start_time`);

-- 试题新增【填空题】类型
ALTER TABLE `__PREFIX__exam_question` ADD COLUMN `options_extend` VARCHAR(1000) NULL DEFAULT NULL COMMENT '选项扩展' COLLATE 'utf8mb4_general_ci' AFTER `options_img`;
ALTER TABLE `__PREFIX__exam_question` CHANGE COLUMN `kind` `kind` ENUM('JUDGE','SINGLE','MULTI','FILL') NOT NULL DEFAULT 'JUDGE' COMMENT '试题类型' COLLATE 'utf8mb4_general_ci';
ALTER TABLE `__PREFIX__exam_question` CHANGE COLUMN `answer` `answer` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '正确答案' COLLATE 'utf8mb4_general_ci';

-- 1.0.12
-- 考卷成绩新增答题信息
ALTER TABLE `__PREFIX__exam_grade`
    ADD COLUMN `question_ids` VARCHAR(2000) NULL DEFAULT '' COMMENT '试卷ID集合' COLLATE 'utf8mb4_general_ci' AFTER `date`,
	ADD COLUMN `error_ids` VARCHAR(2000) NULL DEFAULT '' COMMENT '错题ID集合' COLLATE 'utf8mb4_general_ci' AFTER `question_ids`,
	ADD COLUMN `user_answers` TEXT NULL DEFAULT NULL COMMENT '用户答案集合' COLLATE 'utf8mb4_general_ci' AFTER `error_ids`;

-- 考场成绩新增答题信息
ALTER TABLE `__PREFIX__exam_room_grade`
    ADD COLUMN `question_ids` VARCHAR(2000) NULL DEFAULT '' COMMENT '试卷ID集合' COLLATE 'utf8mb4_general_ci' AFTER `grade_time`,
	ADD COLUMN `error_ids` VARCHAR(2000) NULL DEFAULT '' COMMENT '错题ID集合' COLLATE 'utf8mb4_general_ci' AFTER `question_ids`,
	ADD COLUMN `user_answers` TEXT NULL DEFAULT NULL COMMENT '用户答案集合' COLLATE 'utf8mb4_general_ci' AFTER `error_ids`;

-- 1.0.13
-- 考卷成绩新增考卷选题配置
ALTER TABLE `__PREFIX__exam_grade`
    ADD COLUMN `configs` VARCHAR(1000) NULL COMMENT '试卷选题配置' COLLATE 'utf8mb4_general_ci' AFTER `user_answers`;

-- 考场成绩新增考卷选题配置
ALTER TABLE `__PREFIX__exam_room_grade`
    ADD COLUMN `configs` VARCHAR(1000) NULL COMMENT '试卷选题配置' COLLATE 'utf8mb4_general_ci' AFTER `user_answers`;


-- 1.1.0
-- 错题新增用户答案
ALTER TABLE `__PREFIX__exam_question_wrong`
    ADD COLUMN `user_answer` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '用户答案' COLLATE 'utf8mb4_general_ci' AFTER `question_id`;

ALTER TABLE `__PREFIX__exam_grade`
    ADD COLUMN `mode` ENUM('RANDOM','FIX') NOT NULL DEFAULT 'RANDOM' COMMENT '选题模式' COLLATE 'utf8mb4_general_ci' AFTER `paper_id`;

ALTER TABLE `__PREFIX__exam_room_grade`
    ADD COLUMN `mode` ENUM('RANDOM','FIX') NOT NULL DEFAULT 'RANDOM' COMMENT '选题模式' COLLATE 'utf8mb4_general_ci' AFTER `paper_id`;

-- 1.2.1
-- 新增公告前端跳转信息
ALTER TABLE `__PREFIX__exam_notice`
    ADD COLUMN `front_info` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '前端跳转信息' AFTER `status`;

-- 1.3.0
-- 新增试题类型【简答题】、【材料题】
ALTER TABLE `__PREFIX__exam_question`
    CHANGE COLUMN `kind` `kind` ENUM('JUDGE','SINGLE','MULTI','FILL','SHORT','MATERIAL') NOT NULL DEFAULT 'JUDGE' COMMENT '试题类型' COLLATE 'utf8mb4_general_ci' AFTER `cate_id`;
ALTER TABLE `__PREFIX__exam_question`
    CHANGE COLUMN `title` `title` VARCHAR(2000) NOT NULL COMMENT '题目' COLLATE 'utf8mb4_general_ci' AFTER `kind`;
ALTER TABLE `__PREFIX__exam_question`
    CHANGE COLUMN `answer` `answer` TEXT NOT NULL COMMENT '正确答案' COLLATE 'utf8mb4_general_ci' AFTER `options_extend`;
ALTER TABLE `__PREFIX__exam_question`
    CHANGE COLUMN `options_extend` `options_extend` TEXT NULL COMMENT '选项扩展' COLLATE 'utf8mb4_general_ci' AFTER `options_img`;

-- 试卷标题、配置字段扩展长度
ALTER TABLE `__PREFIX__exam_paper`
    CHANGE COLUMN `title` `title` VARCHAR(3000) NOT NULL COMMENT '试卷名称' COLLATE 'utf8mb4_general_ci' AFTER `mode`,
    CHANGE COLUMN `configs` `configs` VARCHAR(3000) NOT NULL COMMENT '选题配置' COLLATE 'utf8mb4_general_ci' AFTER `title`;

-- 试卷固定选题新增试题答案
ALTER TABLE `__PREFIX__exam_paper_question`
    ADD COLUMN `answer_config` TEXT NULL COMMENT '正确答案配置' COLLATE 'utf8mb4_general_ci' AFTER `sort`;

-- 考试成绩新增系统得分、人工判分
ALTER TABLE `__PREFIX__exam_grade`
    ADD COLUMN `system_score` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT '系统得分' AFTER `score`,
	ADD COLUMN `manual_score` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT '人工判分' AFTER `system_score`;

-- 考场成绩新增系统得分、人工判分
ALTER TABLE `__PREFIX__exam_room_grade`
    ADD COLUMN `system_score` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT '系统得分' AFTER `score`,
	ADD COLUMN `manual_score` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT '人工判分' AFTER `system_score`;

-- 错题记录新增来源
ALTER TABLE `__PREFIX__exam_question_wrong`
    ADD COLUMN `kind` ENUM('PAPER','ROOM','TRAINING') NULL DEFAULT 'PAPER' COMMENT '来源：PAPER=试卷，ROOM=考场，TRAINING=练题' AFTER `user_answer`,
    DROP INDEX `user_id`,
	ADD INDEX `user_id` (`user_id`, `kind`) USING BTREE;

-- 新增材料题关联表
CREATE TABLE IF NOT EXISTS `__PREFIX__exam_material_question` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `parent_question_id` INT(11) UNSIGNED NOT NULL COMMENT '材料题主题目',
    `question_id` INT(11) UNSIGNED NOT NULL COMMENT '材料题子题目',
    `score` INT(11) UNSIGNED NOT NULL COMMENT '分数',
    `weigh` INT(10) UNSIGNED NOT NULL DEFAULT '1' COMMENT '排序',
    `answer` TEXT NULL DEFAULT NULL COMMENT '正确答案配置' COLLATE 'utf8mb4_general_ci',
    `createtime` BIGINT(16) UNSIGNED DEFAULT NULL COMMENT '创建时间',
    `updatetime` BIGINT(16) UNSIGNED NULL DEFAULT NULL COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `from_question_id` (`parent_question_id`, `question_id`) USING BTREE
) COMMENT='材料题关联表'
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

-- 试卷成绩手动判题记录表
CREATE TABLE IF NOT EXISTS `__PREFIX__exam_manual_grade_log` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `admin_id` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '操作管理员',
    `user_id` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '考试用户',
    `paper_id` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '试卷ID',
    `grade_id` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '考试成绩ID',
    `question_id` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '试题ID',
    `before_score` INT(11) UNSIGNED NOT NULL COMMENT '修改前分数',
    `after_score` INT(11) UNSIGNED NOT NULL COMMENT '修改后分数',
    `status` ENUM('0','1') NOT NULL DEFAULT '0' COMMENT '状态：0=未生效，1=已生效' COLLATE 'utf8mb4_general_ci',
    `createtime` BIGINT(16) UNSIGNED DEFAULT NULL COMMENT '创建时间',
    `updatetime` BIGINT(16) UNSIGNED NULL DEFAULT NULL COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `admin_id` (`admin_id`) USING BTREE,
    INDEX `user_id` (`user_id`) USING BTREE,
    INDEX `status` (`status`) USING BTREE,
    INDEX `grade_id` (`grade_id`, `status`) USING BTREE,
    INDEX `paper_id` (`paper_id`, `status`) USING BTREE,
    INDEX `question_id` (`question_id`) USING BTREE
) COMMENT='试卷成绩手动判题记录表'
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

-- 考场成绩手动判题记录表
CREATE TABLE IF NOT EXISTS `__PREFIX__exam_manual_room_grade_log` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `admin_id` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '操作管理员',
    `user_id` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '考试用户',
    `paper_id` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '试卷ID',
    `room_id` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '考场ID',
    `grade_id` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '考试成绩ID',
    `question_id` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '试题ID',
    `before_score` INT(11) UNSIGNED NOT NULL COMMENT '修改前分数',
    `after_score` INT(11) UNSIGNED NOT NULL COMMENT '修改后分数',
    `status` ENUM('0','1') NOT NULL DEFAULT '0' COMMENT '状态：0=未生效，1=已生效' COLLATE 'utf8mb4_general_ci',
    `createtime` BIGINT(16) UNSIGNED DEFAULT NULL COMMENT '创建时间',
    `updatetime` BIGINT(16) UNSIGNED NULL DEFAULT NULL COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `admin_id` (`admin_id`) USING BTREE,
    INDEX `user_id` (`user_id`) USING BTREE,
    INDEX `status` (`status`) USING BTREE,
    INDEX `grade_id` (`grade_id`, `status`) USING BTREE,
    INDEX `paper_id` (`paper_id`, `status`) USING BTREE,
    INDEX `question_id` (`question_id`) USING BTREE,
    INDEX `room_id` (`room_id`) USING BTREE
) COMMENT='考场成绩手动判题记录表'
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

-- 1.4.0
-- 成绩记录表扩展配置长度
ALTER TABLE `__PREFIX__exam_grade`
    CHANGE COLUMN `configs` `configs` TEXT NULL COMMENT '试卷选题配置' COLLATE 'utf8mb4_general_ci' AFTER `user_answers`;
ALTER TABLE `__PREFIX__exam_room_grade`
    CHANGE COLUMN `configs` `configs` TEXT NULL COMMENT '试卷选题配置' COLLATE 'utf8mb4_general_ci' AFTER `user_answers`;

-- 材料题关联表新增分数字段
ALTER TABLE `__PREFIX__exam_material_question`
    ADD COLUMN `score` INT(11) UNSIGNED NOT NULL COMMENT '分数' AFTER `question_id`,
    ADD COLUMN `weigh` INT(10) UNSIGNED NOT NULL DEFAULT '1' COMMENT '排序' AFTER `score`,
	ADD COLUMN `answer` TEXT NULL DEFAULT NULL COMMENT '正确答案配置' COLLATE 'utf8mb4_general_ci' AFTER `weigh`;

-- 题目新增材料题子题字段
ALTER TABLE `__PREFIX__exam_question`
    ADD COLUMN `is_material_child` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '属于材料题子题：0=否，1=是' AFTER `status`,
    DROP INDEX `kind`,
	ADD INDEX `kind` (`kind`, `status`, `is_material_child`) USING BTREE,
    DROP INDEX `cate_id`,
	ADD INDEX `cate_id` (`cate_id`, `kind`, `status`, `is_material_child`) USING BTREE,
	ADD INDEX `is_material_child` (`is_material_child`),
    ADD COLUMN `material_question_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '所属材料题' AFTER `is_material_child`,
	ADD INDEX `material_question_id` (`material_question_id`),
    ADD COLUMN `material_score` BIGINT(16) UNSIGNED NOT NULL DEFAULT '0' COMMENT '材料子题分数' AFTER `material_question_id`;

-- 1.4.5
-- 试题新增题目视频字段
ALTER TABLE `__PREFIX__exam_question`
    ADD COLUMN `title_video` VARCHAR(200) NULL COMMENT '题目视频' AFTER `title`,
	ADD COLUMN `explain_video` VARCHAR(200) NULL COMMENT '解析视频' AFTER `explain`;

-- 错题记录用户答案字段扩展长度（某些旧版本没有更新表结构）
ALTER TABLE `__PREFIX__exam_question_wrong`
    CHANGE COLUMN `user_answer` `user_answer` TEXT NOT NULL COMMENT '用户答案' COLLATE 'utf8mb4_general_ci' AFTER `question_id`;
ALTER TABLE `__PREFIX__exam_room_grade`
    CHANGE COLUMN `configs` `configs` TEXT NULL COMMENT '试卷选题配置' COLLATE 'utf8mb4_general_ci' AFTER `user_answers`,
    CHANGE COLUMN `user_answers` `user_answers` TEXT NULL COMMENT '用户答案集合' COLLATE 'utf8mb4_general_ci' AFTER `error_ids`;
ALTER TABLE `__PREFIX__exam_grade`
    CHANGE COLUMN `configs` `configs` TEXT NULL COMMENT '试卷选题配置' COLLATE 'utf8mb4_general_ci' AFTER `user_answers`,
    CHANGE COLUMN `user_answers` `user_answers` TEXT NULL COMMENT '用户答案集合' COLLATE 'utf8mb4_general_ci' AFTER `error_ids`;

-- 1.5.2
CREATE TABLE IF NOT EXISTS `__PREFIX__exam_correction_type` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '类型名称' COLLATE 'utf8_general_ci',
    `createtime` BIGINT(20) NULL DEFAULT NULL COMMENT '创建时间',
    `updatetime` BIGINT(20) NULL DEFAULT NULL COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE
) COMMENT='纠错反馈类型' COLLATE='utf8_general_ci' ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `__PREFIX__exam_correction_question` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) NOT NULL DEFAULT '0' COMMENT '反馈人',
    `question_id` INT(11) NOT NULL DEFAULT '0' COMMENT '反馈题目',
    `type_ids` INT(11) NOT NULL DEFAULT '0' COMMENT '纠错类型',
    `type_names` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '类型名称' COLLATE 'utf8_general_ci',
    `remark` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '其他说明' COLLATE 'utf8_general_ci',
    `status` ENUM('0','1','2') NOT NULL DEFAULT '0' COMMENT '状态:0=未处理,1=已处理,2=忽略' COLLATE 'utf8_general_ci',
    `message` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '处理说明' COLLATE 'utf8_general_ci',
    `createtime` BIGINT(20) NULL DEFAULT NULL COMMENT '创建时间',
    `updatetime` BIGINT(20) NULL DEFAULT NULL COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `question_id` (`question_id`) USING BTREE,
    INDEX `user_id` (`user_id`) USING BTREE
) COMMENT='纠错反馈试题' COLLATE='utf8_general_ci' ENGINE=InnoDB;


ALTER TABLE `__PREFIX__exam_cate`
    ADD COLUMN `status` ENUM('0','1') NOT NULL DEFAULT '1' COMMENT '状态:0=禁用,1=启用' COLLATE 'utf8_general_ci';


-- 1.5.8
ALTER TABLE `__PREFIX__exam_paper`
    CHANGE COLUMN `quantity` `quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '题目数量' AFTER `configs`;

ALTER TABLE `__PREFIX__exam_paper`
    CHANGE COLUMN `total_score` `total_score` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '试卷总分' AFTER `quantity`,
    CHANGE COLUMN `pass_score` `pass_score` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '及格线' AFTER `total_score`;

ALTER TABLE `__PREFIX__exam_grade`
    CHANGE COLUMN `score` `score` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '考试分数' AFTER `mode`,
    CHANGE COLUMN `system_score` `system_score` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '系统得分' AFTER `score`,
    CHANGE COLUMN `manual_score` `manual_score` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '人工判分' AFTER `system_score`,
    CHANGE COLUMN `total_score` `total_score` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '总分数' AFTER `is_pass`,
    CHANGE COLUMN `total_count` `total_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '总题数' AFTER `total_score`,
    CHANGE COLUMN `right_count` `right_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '答对数' AFTER `total_count`,
    CHANGE COLUMN `error_count` `error_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '答错数' AFTER `right_count`;

ALTER TABLE `__PREFIX__exam_grade`
    ADD COLUMN `pass_score` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '及格线' AFTER `is_pass`;

ALTER TABLE `__PREFIX__exam_room_grade`
    ADD COLUMN `pass_score` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '及格线' AFTER `is_makeup`;

-- 1.7.0 错题记录表新增来源试卷和考场字段
ALTER TABLE `__PREFIX__exam_question_wrong`
    ADD COLUMN `cate_id` INT(11) UNSIGNED NULL DEFAULT '0' COMMENT '所属题库' AFTER `kind`,
    ADD COLUMN `paper_id` INT(11) UNSIGNED NULL DEFAULT '0' COMMENT '来源试卷' AFTER `cate_id`,
	ADD COLUMN `room_id` INT(11) UNSIGNED NULL DEFAULT '0' COMMENT '来源考场' AFTER `paper_id`;