########################
# Drop all table
########################

DROP TABLE IF EXISTS book, class, course, department, order_book, resource, role, section, speciality, staff, student, takes, timetable, user;

CREATE TABLE book
(
  book_title       VARCHAR(30),
  isbn             VARCHAR(20),
  date_of_printing VARCHAR(20),
  author           VARCHAR(15),
  press            VARCHAR(15),
  category         CHAR,
  unit_price       FLOAT,
  PRIMARY KEY (book_title, isbn)
)
  DEFAULT CHARSET = utf8,
  ENGINE = InnoDB;

CREATE TABLE class
(
  class_id   VARCHAR(30),
  class_name VARCHAR(10),
  year       VARCHAR(20),
  spec_name  VARCHAR(15),
  PRIMARY KEY (class_id)
)
  DEFAULT CHARSET = utf8,
  ENGINE = InnoDB;

CREATE TABLE course
(
  course_title VARCHAR(30),
  type         VARCHAR(10),
  credits      FLOAT,
  speciality   VARCHAR(15),
  PRIMARY KEY (course_title)
)
  DEFAULT CHARSET = utf8,
  ENGINE = InnoDB;

CREATE TABLE department
(
  dept_id   INT AUTO_INCREMENT,
  dept_name VARCHAR(15),
  PRIMARY KEY (dept_id)
)
  DEFAULT CHARSET = utf8,
  ENGINE = InnoDB;

CREATE TABLE order_book
(
  staff_id   VARCHAR(30),
  sec_id     INT,
  book_title VARCHAR(30),
  isbn       VARCHAR(20),
  remark     VARCHAR(30),
  approval   TINYINT(1),
  PRIMARY KEY (staff_id, sec_id, book_title, isbn)
)
  DEFAULT CHARSET = utf8,
  ENGINE = InnoDB;

CREATE TABLE resource (
  id         BIGINT AUTO_INCREMENT,
  name       VARCHAR(100),
  type       VARCHAR(50),
  url        VARCHAR(200),
  parent_id  BIGINT,
  parent_ids VARCHAR(100),
  permission VARCHAR(100),
  available  BOOL   DEFAULT FALSE,
  PRIMARY KEY (id)
)
  CHARSET = utf8
  ENGINE = InnoDB;
CREATE INDEX idx_resource_parent_id ON resource (parent_id);
CREATE INDEX idx_resource_parent_ids ON resource (parent_ids);

CREATE TABLE role (
  id           BIGINT AUTO_INCREMENT,
  role         VARCHAR(100),
  description  VARCHAR(100),
  resource_ids VARCHAR(100),
  available    BOOL   DEFAULT FALSE,
  PRIMARY KEY (id)
)
  CHARSET = utf8
  ENGINE = InnoDB;
CREATE INDEX idx_sys_role_resource_ids ON role (resource_ids);

CREATE TABLE section
(
  sec_id       INT AUTO_INCREMENT,
  course_title VARCHAR(15),
  year         VARCHAR(5),
  limitCount   INT,
  staff_id     VARCHAR(20),
  PRIMARY KEY (sec_id)
)
  DEFAULT CHARSET = utf8,
  ENGINE = InnoDB;

CREATE TABLE speciality
(
  spec_id   INT AUTO_INCREMENT,
  dept_name VARCHAR(10),
  spec_name VARCHAR(15),
  year      VARCHAR(5),
  PRIMARY KEY (spec_id)
)
  DEFAULT CHARSET = utf8,
  ENGINE = InnoDB;

CREATE TABLE staff
(
  staff_id   VARCHAR(30),
  staff_name VARCHAR(20),
  PRIMARY KEY (staff_id)
)
  DEFAULT CHARSET = utf8,
  ENGINE = InnoDB;

CREATE TABLE student
(
  student_id          VARCHAR(30),
  student_name        VARCHAR(20),
  id_card             VARCHAR(20),
  year                VARCHAR(5),
  class_id            VARCHAR(10),
  telephone_number    VARCHAR(20),
  student_origin_base VARCHAR(10),
  gender              VARCHAR(4),
  pic_path            VARCHAR(45),
  leave_of_absence    BOOL,
  leave_school        BOOL,
    PRIMARY KEY (student_id)
)
  DEFAULT CHARSET = utf8,
  ENGINE = InnoDB;

CREATE TABLE takes
(
  student_id VARCHAR(30),
  sec_id     VARCHAR(30),
  score      FLOAT,
  PRIMARY KEY (student_id, sec_id)
)
  DEFAULT CHARSET = utf8,
  ENGINE = InnoDB;

CREATE TABLE timetable
(
  sec_id    INT,
  time      VARCHAR(20),
  weeks     VARCHAR(20),
  week      VARCHAR(20),
  classroom VARCHAR(10)
)
  DEFAULT CHARSET = utf8,
  ENGINE = InnoDB;

CREATE TABLE user
(
  user_id  VARCHAR(30),
  password VARCHAR(50),
  salt     VARCHAR(100),
  role_ids VARCHAR(100),
  locked   BOOL DEFAULT FALSE,
  PRIMARY KEY (user_id)
)
  DEFAULT CHARSET = utf8,
  ENGINE = InnoDB;
CREATE UNIQUE INDEX idx_user_username ON user (user_id);

#ALTER TABLE staff ADD CONSTRAINT fk_user_staff FOREIGN KEY (staff_id) REFERENCES orders (user_id);
#ALTER TABLE order_book ADD CONSTRAINT fk_courses_user_books_classe FOREIGN KEY (staffs_id) REFERENCES staffs (staff_id),
#FOREIGN KEY (book_title,isbn) REFERENCES book (book_title,isbn),
#FOREIGN KEY (grade,professional,department) REFERENCES class (grade,professional,department),
#FOREIGN KEY (course_id) REFERENCES course (courses_id);