--v1.1 题目表增加 创建者ID属性 QUE_CREATE_USER_ID
--v1.2 顺序修改
--v1.3 Submit表submitTime属性修正
--v1.4 Score表 sco_que_id自增属性去除
--v1.5 Score表 sco_que_id主键属性去除
--v1.6 Question表 check_state属性改为varcahr
create table TBL_USER
(
    USER_ID       INTEGER auto_increment,
    USER_ACCOUNT  CHARACTER VARYING,
    USER_PASSWORD CHARACTER VARYING,
    USER_SEX      CHARACTER VARYING,
    USER_TYPE     INTEGER,
    constraint TBL_USER_PK
        primary key (USER_ID)
);

comment on table TBL_USER is '用户';

comment on column TBL_USER.USER_ID is '用户ID';

comment on column TBL_USER.USER_ACCOUNT is '用户账号';

comment on column TBL_USER.USER_PASSWORD is '用户密码';

comment on column TBL_USER.USER_SEX is '用户性别';

comment on column TBL_USER.USER_TYPE is '用户类型';


create table TBL_CLASS
(
    CLASS_ID   INTEGER auto_increment,
    CLASS_NAME CHARACTER VARYING,
    constraint TBL_CLASS_PK
        primary key (CLASS_ID)
);

comment on table TBL_CLASS is '班级';

comment on column TBL_CLASS.CLASS_ID is '班级ID';

create table TBL_CODEFILE
(
    CODE_ID   INTEGER auto_increment,
    CODE_PATH CHARACTER VARYING,
    constraint TBL_CODEFILE_PK
        primary key (CODE_ID)
);

comment on table TBL_CODEFILE is '代码文件';

comment on column TBL_CODEFILE.CODE_ID is '代码文件ID';

comment on column TBL_CODEFILE.CODE_PATH is '代码文件路径';

create table TBL_QUESTION
(
    QUESTION_ID        INTEGER auto_increment,
    QUE_NAME           CHARACTER VARYING,
    QUE_DESCRIBE       CHARACTER VARYING,
    QUE_INPUT_FORMAT   CHARACTER VARYING,
    QUE_OUTPUT_FORMAT  CHARACTER VARYING,
    QUE_INPUT_CASE     CHARACTER VARYING,
    QUE_OUTPUT_CASE    CHARACTER VARYING,
    QUE_HELP           CHARACTER VARYING,
    QUE_DIFFICULTY     CHARACTER VARYING,
    QUE_CHECK_STATE    CHARACTER VARYING,
    QUE_TAG            CHARACTER VARYING,
    QUE_CREATE_USER_ID INTEGER,
    constraint TBL_QUESTION_PK
        primary key (QUESTION_ID),
    constraint TBL_QUESTION_TBL_USER_USER_ID_FK
        foreign key (QUE_CREATE_USER_ID) references TBL_USER
);

comment on table TBL_QUESTION is '题目';

comment on column TBL_QUESTION.QUE_NAME is '题目名称';

comment on column TBL_QUESTION.QUE_DESCRIBE is '题目描述';

comment on column TBL_QUESTION.QUE_INPUT_FORMAT is '输入格式';

comment on column TBL_QUESTION.QUE_OUTPUT_FORMAT is '输出格式';

comment on column TBL_QUESTION.QUE_INPUT_CASE is '输入样例';

comment on column TBL_QUESTION.QUE_OUTPUT_CASE is '输出样例';

comment on column TBL_QUESTION.QUE_HELP is '提示说明';

comment on column TBL_QUESTION.QUE_DIFFICULTY is '题目难度';

comment on column TBL_QUESTION.QUE_CHECK_STATE is '题目审核状态';

comment on column TBL_QUESTION.QUE_TAG is '题目标签';

comment on column TBL_QUESTION.QUE_CREATE_USER_ID is '创建者ID';


create table TBL_QUESTIONLIST
(
    QUELIST_ID   INTEGER auto_increment,
    QUELIST_NAME CHARACTER VARYING,
    constraint TBL_QUESTIONLIST_PK
        primary key (QUELIST_ID)
);

comment on table TBL_QUESTIONLIST is '习题集';

comment on column TBL_QUESTIONLIST.QUELIST_ID is '习题集ID';

comment on column TBL_QUESTIONLIST.QUELIST_NAME is '习题集名';

create table TBL_QUESTION_LIST_RELATION
(
    QUESTION_ID      INTEGER,
    QUESTION_LIST_ID INTEGER,
    constraint TBL_QUESTION_LIST_RELATION_TBL_QUESTIONLIST_QUELIST_ID_FK
        foreign key (QUESTION_LIST_ID) references TBL_QUESTIONLIST,
    constraint TBL_QUESTION_LIST_RELATION_TBL_QUESTION_QUESTION_ID_FK
        foreign key (QUESTION_ID) references TBL_QUESTION
);

comment on table TBL_QUESTION_LIST_RELATION is '题目所属关系';

comment on column TBL_QUESTION_LIST_RELATION.QUESTION_ID is '题目ID';

comment on column TBL_QUESTION_LIST_RELATION.QUESTION_LIST_ID is '习题集ID';


create table TBL_ADMIN
(
    ADMIN_USER_ID INTEGER not null,
    constraint TBL_ADMIN_PK
        primary key (ADMIN_USER_ID),
    constraint TBL_ADMIN_TBL_USER_USER_ID_FK
        foreign key (ADMIN_USER_ID) references TBL_USER
);

comment on column TBL_ADMIN.ADMIN_USER_ID is '用户ID';

create table TBL_STUDENT
(
    STU_ID       INTEGER auto_increment,
    STU_CLASS_ID INTEGER,
    STU_USER_ID  INTEGER,
    STU_NAME     CHARACTER VARYING,
    constraint TBL_STUDENT_PK
        primary key (STU_ID),
    constraint TBL_STUDENT_TBL_CLASS_CLASS_ID_FK
        foreign key (STU_CLASS_ID) references TBL_CLASS,
    constraint TBL_STUDENT_TBL_USER_USER_ID_FK
        foreign key (STU_USER_ID) references TBL_USER
);

comment on table TBL_STUDENT is '学生';

comment on column TBL_STUDENT.STU_ID is '学号';

comment on column TBL_STUDENT.STU_CLASS_ID is '班级ID';

comment on column TBL_STUDENT.STU_USER_ID is '用户ID';

comment on column TBL_STUDENT.STU_NAME is '姓名';

create table TBL_SCORE
(
    SCO_QUE_ID INTEGER auto_increment,
    SCO_STU_ID INTEGER,
    SCO_SCORE  INTEGER,
    constraint TBL_SCORE_PK
        primary key (SCO_QUE_ID),
    constraint TBL_SCORE_TBL_QUESTION_QUESTION_ID_FK
        foreign key (SCO_QUE_ID) references TBL_QUESTION,
    constraint TBL_SCORE_TBL_STUDENT_STU_ID_FK
        foreign key (SCO_STU_ID) references TBL_STUDENT
);

comment on table TBL_SCORE is '成绩记录';

comment on column TBL_SCORE.SCO_STU_ID is '学号';

comment on column TBL_SCORE.SCO_SCORE is '成绩';

create table TBL_SUBMIT
(
    SUB_ID          INTEGER auto_increment,
    SUB_QUE_ID      INTEGER,
    SUB_USER_ID     INTEGER,
    SUB_CODEFILE_ID INTEGER,
    SUB_TIME        CHARACTER VARYING,
    constraint TBL_SUBMIT_PK
        primary key (SUB_ID),
    constraint TBL_SUBMIT_TBL_CODEFILE_CODE_ID_FK
        foreign key (SUB_CODEFILE_ID) references TBL_CODEFILE,
    constraint TBL_SUBMIT_TBL_QUESTION_QUESTION_ID_FK
        foreign key (SUB_QUE_ID) references TBL_QUESTION,
    constraint TBL_SUBMIT_TBL_USER_USER_ID_FK
        foreign key (SUB_USER_ID) references TBL_USER
);

comment on table TBL_SUBMIT is '提交表';

comment on column TBL_SUBMIT.SUB_QUE_ID is '题目ID';

comment on column TBL_SUBMIT.SUB_USER_ID is '提交用户ID';

comment on column TBL_SUBMIT.SUB_CODEFILE_ID is '代码文件ID';

comment on column TBL_SUBMIT.SUB_TIME is '提交时间';

create table TBL_TEACHER
(
    TEA_ID       INTEGER auto_increment,
    TEA_CLASS_ID INTEGER,
    TEA_USER_ID  INTEGER,
    TEA_NAME     CHARACTER VARYING,
    TEA_POSITION INTEGER,
    constraint TBL_TEACHER_PK
        primary key (TEA_ID),
    constraint TBL_TEACHER_TBL_CLASS_CLASS_ID_FK
        foreign key (TEA_CLASS_ID) references TBL_CLASS,
    constraint TBL_TEACHER_TBL_USER_USER_ID_FK
        foreign key (TEA_USER_ID) references TBL_USER
);

comment on table TBL_TEACHER is '教师';

comment on column TBL_TEACHER.TEA_ID is '教职工号';

comment on column TBL_TEACHER.TEA_CLASS_ID is '班级ID';

comment on column TBL_TEACHER.TEA_USER_ID is '用户ID';

comment on column TBL_TEACHER.TEA_NAME is '教师姓名';

comment on column TBL_TEACHER.TEA_POSITION is '职位';