DROP TABLE ec_prt_project;
DROP TABLE ec_prt_project_backup_setting;
DROP TABLE ec_prt_project_other_setting;
DROP TABLE ec_prt_project_statistic;
DROP TABLE ec_prt_course_statistic;
DROP TABLE ec_prt_course_paper;
DROP TABLE ec_prt_template;
DROP TABLE ec_prt_exam_structure;
DROP TABLE ec_prt_objective_question_structure;
DROP TABLE ec_prt_subjective_question_structure;

CREATE TABLE ec_prt_course_paper (
  id              bigint(20) NOT NULL AUTO_INCREMENT,
  creation_time   datetime            DEFAULT NULL,
  update_time     datetime            DEFAULT NULL,
  answer_html_url varchar(255)        DEFAULT NULL,
  answer_pdf_url  varchar(255)        DEFAULT NULL,
  converted       bit(1)              DEFAULT NULL,
  course_code     varchar(50)         DEFAULT NULL,
  course_id       bigint(20)          DEFAULT NULL,
  course_name     varchar(50)         DEFAULT NULL,
  exam_id         bigint(20)          DEFAULT NULL,
  org_id          bigint(20)          DEFAULT NULL,
  paper_html_url  varchar(255)        DEFAULT NULL,
  paper_id        varchar(50)         DEFAULT NULL,
  paper_name      varchar(100)        DEFAULT NULL,
  paper_p         int(11)             DEFAULT NULL,
  paper_pdf_url   varchar(255)        DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY INDEX_PRT_COURSE_PAPER_03 (exam_id, paper_id),
  KEY INDEX_PRT_COURSE_PAPER_01 (org_id),
  KEY INDEX_PRT_COURSE_PAPER_02 (exam_id)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE ec_prt_course_statistic (
  id              bigint(20) NOT NULL AUTO_INCREMENT,
  creation_time   datetime            DEFAULT NULL,
  update_time     datetime            DEFAULT NULL,
  course_code     varchar(50)         DEFAULT NULL,
  course_id       bigint(20)          DEFAULT NULL,
  course_name     varchar(50)         DEFAULT NULL,
  exam_id         bigint(20)          DEFAULT NULL,
  org_id          bigint(20)          DEFAULT NULL,
  paper_status    int(11)             DEFAULT NULL,
  paper_type      varchar(50)         DEFAULT NULL,
  total_student   int(11)             DEFAULT NULL,
  course_paper_id bigint(20)          DEFAULT NULL,
  PRIMARY KEY (id),
  KEY INDEX_PRT_COURSE_STATISTIC_01 (org_id),
  KEY INDEX_PRT_COURSE_STATISTIC_02 (exam_id),
  KEY INDEX_PRT_COURSE_STATISTIC_03 (course_id),
  KEY FK7nnn568j5img7r3fjbsni2t25 (course_paper_id),
  CONSTRAINT FK7nnn568j5img7r3fjbsni2t25 FOREIGN KEY (course_paper_id) REFERENCES ec_prt_course_paper (id)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE ec_prt_exam_structure (
  id            bigint(20) NOT NULL AUTO_INCREMENT,
  creation_time datetime            DEFAULT NULL,
  update_time   datetime            DEFAULT NULL,
  exam_id       bigint(20)          DEFAULT NULL,
  exam_name     varchar(100)         DEFAULT NULL,
  exam_type     varchar(50)         DEFAULT NULL,
  org_id        bigint(20)          DEFAULT NULL,
  org_name      varchar(100)         DEFAULT NULL,
  struct        varchar(500)        DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY INDEX_PRT_EXAM_STRUCTURE_01 (org_id, exam_id)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE ec_prt_objective_question_structure (
  id            bigint(20) NOT NULL AUTO_INCREMENT,
  creation_time datetime            DEFAULT NULL,
  update_time   datetime            DEFAULT NULL,
  answer        varchar(50)         DEFAULT NULL,
  course_code   varchar(50)         DEFAULT NULL,
  course_id     bigint(20)          DEFAULT NULL,
  course_name   varchar(50)         DEFAULT NULL,
  exam_id       bigint(20)          DEFAULT NULL,
  paper_id      varchar(50)         DEFAULT NULL,
  paper_name    varchar(100)        DEFAULT NULL,
  question_type varchar(50)         DEFAULT NULL,
  section_num   int(11)             DEFAULT NULL,
  unit_num      int(11)             DEFAULT NULL,
  unit_score    double              DEFAULT NULL,
  PRIMARY KEY (id),
  KEY INDEX_PRT_OBJECTIVE_QUESTION_01 (exam_id),
  KEY INDEX_PRT_OBJECTIVE_QUESTION_02 (course_id),
  KEY INDEX_PRT_OBJECTIVE_QUESTION_03 (paper_id)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE ec_prt_project (
  id                 bigint(20) NOT NULL AUTO_INCREMENT,
  creation_time      datetime            DEFAULT NULL,
  update_time        datetime            DEFAULT NULL,
  completed          bit(1)              DEFAULT NULL,
  exam_id            bigint(20)          DEFAULT NULL,
  exam_name          varchar(100)         DEFAULT NULL,
  mail_end_time      datetime            DEFAULT NULL,
  mail_start_time    datetime            DEFAULT NULL,
  org_id             bigint(20)          DEFAULT NULL,
  org_name           varchar(100)         DEFAULT NULL,
  pm_id              bigint(20)          DEFAULT NULL,
  pm_name            varchar(50)         DEFAULT NULL,
  prepare_end_time   datetime            DEFAULT NULL,
  prepare_start_time datetime            DEFAULT NULL,
  print_end_time     datetime            DEFAULT NULL,
  print_start_time   datetime            DEFAULT NULL,
  supplier_id        bigint(20)          DEFAULT NULL,
  supplier_name      varchar(50)         DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY INDEX_PRT_PROJECT_01 (org_id, exam_id)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE ec_prt_project_backup_setting (
  id                bigint(20) NOT NULL AUTO_INCREMENT,
  creation_time     datetime            DEFAULT NULL,
  update_time       datetime            DEFAULT NULL,
  alone_pkg_max     double              DEFAULT NULL,
  alone_pkg_min     double              DEFAULT NULL,
  alone_pkg_percent double              DEFAULT NULL,
  each_pkg_max      double              DEFAULT NULL,
  each_pkg_min      double              DEFAULT NULL,
  each_pkg_percent  double              DEFAULT NULL,
  group_type        varchar(255)        DEFAULT NULL,
  need_alone_pkg    bit(1)              DEFAULT NULL,
  need_each_pkg     bit(1)              DEFAULT NULL,
  project_id        bigint(20)          DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY INDEX_PRT_PROJECT_BACKUP_01 (project_id)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE ec_prt_project_other_setting (
  id            bigint(20) NOT NULL AUTO_INCREMENT,
  creation_time datetime            DEFAULT NULL,
  update_time   datetime            DEFAULT NULL,
  project_id    bigint(20)          DEFAULT NULL,
  remark        varchar(2000)       DEFAULT NULL,
  PRIMARY KEY (id),
  KEY INDEX_PRT_PROJECT_OTHER_01 (project_id)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE ec_prt_project_statistic (
  id            bigint(20) NOT NULL AUTO_INCREMENT,
  creation_time datetime            DEFAULT NULL,
  update_time   datetime            DEFAULT NULL,
  backupa3      int(11)             DEFAULT NULL,
  backupa4      int(11)             DEFAULT NULL,
  normala3      int(11)             DEFAULT NULL,
  normala4      int(11)             DEFAULT NULL,
  project_id    bigint(20)          DEFAULT NULL,
  total_course  int(11)             DEFAULT NULL,
  total_paper   int(11)             DEFAULT NULL,
  total_pkg     int(11)             DEFAULT NULL,
  total_student int(11)             DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY INDEX_PRT_PROJECT_STATISTIC_01 (project_id)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE ec_prt_subjective_question_structure (
  id            bigint(20) NOT NULL AUTO_INCREMENT,
  creation_time datetime            DEFAULT NULL,
  update_time   datetime            DEFAULT NULL,
  course_code   varchar(50)         DEFAULT NULL,
  course_id     bigint(20)          DEFAULT NULL,
  course_name   varchar(50)         DEFAULT NULL,
  exam_id       bigint(20)          DEFAULT NULL,
  paper_id      varchar(50)         DEFAULT NULL,
  paper_name    varchar(100)        DEFAULT NULL,
  section_name  varchar(100)        DEFAULT NULL,
  section_num   int(11)             DEFAULT NULL,
  unit_num      int(11)             DEFAULT NULL,
  unit_score    double              DEFAULT NULL,
  PRIMARY KEY (id),
  KEY INDEX_PRT_SUBJECTIVE_QUESTION_01 (exam_id),
  KEY INDEX_PRT_SUBJECTIVE_QUESTION_02 (course_id),
  KEY INDEX_PRT_SUBJECTIVE_QUESTION_03 (paper_id)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE ec_prt_template (
  id            bigint(20) NOT NULL AUTO_INCREMENT,
  creation_time datetime            DEFAULT NULL,
  update_time   datetime            DEFAULT NULL,
  exam_id       bigint(20)          DEFAULT NULL,
  file_name     varchar(255)        DEFAULT NULL,
  file_url      varchar(255)        DEFAULT NULL,
  org_id        bigint(20)          DEFAULT NULL,
  template_type int(11)             DEFAULT NULL,
  PRIMARY KEY (id),
  KEY INDEX_PRT_TEMPLATE_01 (org_id),
  KEY INDEX_PRT_TEMPLATE_02 (exam_id)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;
