-- create database
create database audittest default character set utf8 collate utf8_general_ci;

-- create tables
-- 用户基础信息表
create table user (
  userbh varchar(64) comment '编号', -- primary key
  username varchar(64) comment '登录名称',
  userage int  comment '年龄',
  usersex char(1) default 'M' comment '性别:FM',
  userdep varchar(64) comment '所属机构编号',
  realname varchar(64) comment '用户真实名称',
  workstart varchar(64) comment '开始工作时间',
  workaudit varchar(64) comment '审计工作时间',
  workseq varchar(64) comment '工作序列',
  worktype varchar(64) comment '工作分类',
  remark varchar(256) comment '备注',
  curlevel varchar(64) comment '当前职级'
);

-- 工作序列
create table workseq (
  seqbh varchar(64) comment '序列编号', -- primary key
  seqname varchar(64) comment '序列名称',
  remark varchar(256)
);

-- 工作分类
create table category (
  catebh varchar(64) comment '分类编号', -- primary key
  catename varchar(64) comment '分类名称',
  remark varchar(256)
);

-- 职级
create table worklevel (
  levelbh varchar(64) comment '职级编号', -- primary key
  levelname varchar(64) comment '职级名称',
  remark varchar(256)
);

-- 机构基础信息
create table organize (
  orgbh varchar(64) comment '部门编号', -- primary key
  orgname varchar(64) comment '部门名称',
  parentbh varchar(64) comment '父级部门',
  remark varchar(256)
);

-- 试题库
create table question (
  itembh varchar(64) comment '试题编号', -- primary key
  itemseq varchar(64) comment '试题所属序列',
  itemcate varchar(64) comment '试题所属分类',
  itemcontent text comment '试题内容',
  itemlevel int default 0 comment '试题难度, 0..10',
  itemtype varchar(64) comment '试题类型：单选，多选，问答',
  itemcreate varchar(64) comment '试题录入日期',
  remark varchar(256)
);

-- 试题答案信息
create table options (
  optbh varchar(64) comment '答案编号', -- primary key
  itembh varchar(64) comment '所属试题',
  optcontent text comment '答案内容',
  optno char(1) comment '答案号码：ABCD',
  isanswer int default 0 comment '是否是答案，0-不是，1是',
  remark varchar(256)
);

-- 考试模板
create table testmodel (
  modelbh varchar(64) comment '模板编号', -- primary key
  modelname varchar(64) comment '名称',
  seqbh varchar(64) comment '所属序列',
  catebh varchar(64) comment '所属分类',
  minutes int default 60 comment '考试时间，默认60（分钟）',
  year int comment '考试年度',
  enabled int default 0 comment '考题模板是否有效，0无效，1有效',
  remark varchar(256)
);

-- 考试模板明细
create table testmodeldetail (
  detailbh varchar(64) comment '模板明细编号', -- primary key
  modelbh varchar(64) comment '模板编号',
  seqbh varchar(64) comment '所属序列',
  catebh varchar(64) comment '所属分类',
  itemtype varchar(64) comment '题目类型',
  itemcount int default 10 comment '题目数量',
  score int default 1 comment '每题的分数',
  itemlevel int default 0 comment '试题难度, 0..10'
);

-- 考卷
create table test (
  testbh varchar(64) comment '考试编号', -- primary key
  modelbh varchar(64) comment '模板编号',
  userbh varchar(64) comment '考试人员编号，如果是模板，就为--',
  starttime varchar(64) comment '开考时间',
  minutes int,
  finished int default 0 comment '是否结束判卷，0未结束，1结束',
  enabled int default 1 comment '是否有效，1有效，可以考试，0无效，不能考试'
);

-- 考试题
create table testquestion (
  testquestionbh varchar(64),  -- primary key
  testbh varchar(64) comment '考试编号',
  itembh varchar(64) comment '题目编号',
  score int default 1 comment '考题分数',
  itemtype varchar(64) comment '考题类型',
  sortno int comment '考题序号'
);

-- 考试答案
create table testopt (
  testoptbh varchar(64),  -- primary key
  testbh varchar(64),
  itembh varchar(64),
  optbh varchar(64),
  itemtype varchar(64) comment '考题类型',
  answer text comment '答案',
  isanswer int default 0 comment '是否是答案'
);

-- 授权表
create table authentic(
  authbh varchar(64) comment '授权编号', -- primary key
  userbh varchar(64) comment '用户编号',
  authvalue int default 0 comment '权限类型 0 - 没有权限 1 系统管理员 2 阅卷人员 3 考生',
  resourcebh varchar(64) comment '关联资源编号，管理员不需要设置，阅卷人员对应考试模板，考生对应考试模板'
);

-- 个人成绩表
create table marking (
  markingbh varchar(64) comment '个人成绩编号',
  userbh varchar(64) comment '用户编号',
  modelbh varchar(64) comment '模板编号',
  score1 int comment '客观题分数',
  score2 int comment '客观题分数',
  ext varchar(256)
);

alter table user add password varchar(64);
alter table user add enabled int default 1;
alter table testmodel add builded int default 0;
alter table testopt add ext varchar(64); -- 扩展字段，暂时用于保存每个主观题的分数

-- 初始化管理员
insert into user (userbh, username, password,remark) values ('0000001', 'admin', UPPER(md5('admin')), '这是系统管理员');
-- 初始化管理员权限
insert into authentic (authbh, userbh, authvalue) values ('0000001', '0000001', 1);
-- 初始化序列
insert into workseq (seqbh,seqname) values ('0000001', '研发序列');
insert into workseq (seqbh,seqname) values ('0000002', '销售序列');
insert into workseq (seqbh,seqname) values ('0000003', '实施序列');
-- 初始化分类
insert into category (catebh,catename) values ('0000001', 'Delphi产品线');
insert into category (catebh,catename) values ('0000002', 'Java产品线');
-- 初始化职级
insert into worklevel (levelbh ,levelname) values ('0000001', '2A-初级研发工程师');
insert into worklevel (levelbh ,levelname) values ('0000002', '2B-中级研发工程师');
insert into worklevel (levelbh ,levelname) values ('0000003', '2C-高级研发工程师');

-- 文档管理
create table docs(
  docbh varchar(64) not null comment '文档编号', -- primary key
  docname varchar(64),
  filetoken varchar(128) comment '文件名称', 
  creater varchar(64),
  createtime varchar(32)
);

-- 认定职级
create table zjrd(
  zjrdbh varchar(64) not null comment '认定职级编号', -- primary key
  userbh varchar(64),
  username varchar(64),
  userno varchar(64),
  yzj varchar(10) comment '原职级',
  sqzj varchar(10) comment '申请职级',
  pdzj varchar(10) comment '评定职级',
  pwjy varchar(2000) comment '评定委员会建议',
  hrjy varchar(2000) comment 'HR会建议',
  rq varchar(64)
);

alter table zjrd add column markingbh varchar(64);
alter table zjrd add column modelbh varchar(64);
alter table zjrd add column realname varchar(64);
alter table zjrd add column pass int default 1; -- 考试是否通过，默认通过

--author 杜彦伟 增加学历和学校两个字段
ALTER TABLE USER ADD column education VARCHAR(20);
ALTER TABLE USER ADD column schoolname VARCHAR(100);


--author 成浩 增加获取题目的索引
CREATE INDEX idx_question_itembh ON question(itembh);

CREATE INDEX idx_OPTIONS_optbh ON `OPTIONS`(optbh);

CREATE INDEX idx_testquestion_itembh ON testquestion(itembh);

CREATE INDEX idx_testquestion_testbh ON testquestion(testbh);


--author 杜彦伟 增加游客账号

-- 初始化游客账号密码
insert into user (userbh, username, password,remark) values ('audittest', 'audittest', UPPER(md5('123456')), '这是游客账号');
-- 初始化考试权限
insert into authentic (authbh, userbh, authvalue) values ('audittest', 'audittest', 3);