CREATE TABLE STATUS (
       STATUS_ID INT NOT NULL
     , NAME VARCHAR(20) NOT NULL
     , SORT_ORDER INT DEFAULT 0 NOT NULL
     , IMAGE VARCHAR(256) NOT NULL
     , PRIMARY KEY (STATUS_ID)
);

CREATE TABLE FILE_INFO (
       FILE_ID INT NOT NULL
     , NAME VARCHAR(256) NOT NULL
     , PATH VARCHAR(256)
     , PRIMARY KEY (FILE_ID)
);

CREATE TABLE TAG_TYPE (
       TAG_TYPE_ID INT NOT NULL
     , NAME VARCHAR(20) NOT NULL
     , STYLE_CLASS VARCHAR(10) NOT NULL
     , SORT_ORDER INT DEFAULT 0
     , PRIMARY KEY (TAG_TYPE_ID)
);

CREATE TABLE TASK (
       TASK_ID INT NOT NULL
     , CONTENTS VARCHAR(7000) NOT NULL
     , ESTIMATED_MAN_HOUR DOUBLE PRECISION DEFAULT 0
     , TAG_LIST VARCHAR(200)
     , UPDATE_DATE TIMESTAMP
     , STATUS_ID INT NOT NULL
     , PRIMARY KEY (TASK_ID)
);

CREATE TABLE TASK_COMMENT (
       COMMENT_ID INT NOT NULL
     , CONTENTS VARCHAR(1000)
     , COMMENTATOR VARCHAR(50)
     , UPDATE_DATE TIMESTAMP
     , TASK_ID INT NOT NULL
     , PRIMARY KEY (COMMENT_ID)
);

CREATE TABLE TASK_FILE_RELATION (
       TASK_ID INT NOT NULL
     , FILE_ID INT NOT NULL
     , PRIMARY KEY (TASK_ID, FILE_ID)
);

CREATE TABLE TASK_RELATION (
       SRC_TASK_ID INT NOT NULL
     , DEST_TASK_ID INT NOT NULL
     , PRIMARY KEY (SRC_TASK_ID, DEST_TASK_ID)
);

CREATE TABLE TAG (
       TAG_ID INT NOT NULL
     , NAME VARCHAR(50) NOT NULL
     , ACTIVE BIT DEFAULT '1' NOT NULL
     , TAG_TYPE_ID INT NOT NULL
     , PRIMARY KEY (TAG_ID)
);

ALTER TABLE TASK
  ADD CONSTRAINT FK_TASK_1
      FOREIGN KEY (STATUS_ID)
      REFERENCES STATUS (STATUS_ID);

ALTER TABLE TASK_COMMENT
  ADD CONSTRAINT FK_TASK_COMMENT_1
      FOREIGN KEY (TASK_ID)
      REFERENCES TASK (TASK_ID);

ALTER TABLE TASK_FILE_RELATION
  ADD CONSTRAINT FK_TASK_FILE_RELATION_1
      FOREIGN KEY (TASK_ID)
      REFERENCES TASK (TASK_ID);

ALTER TABLE TASK_FILE_RELATION
  ADD CONSTRAINT FK_TASK_FILE_RELATION_2
      FOREIGN KEY (FILE_ID)
      REFERENCES FILE_INFO (FILE_ID);

ALTER TABLE TASK_RELATION
  ADD CONSTRAINT FK_TASK_RELATION_1
      FOREIGN KEY (SRC_TASK_ID)
      REFERENCES TASK (TASK_ID);

ALTER TABLE TASK_RELATION
  ADD CONSTRAINT FK_TASK_RELATION_2
      FOREIGN KEY (DEST_TASK_ID)
      REFERENCES TASK (TASK_ID);

ALTER TABLE TAG
  ADD CONSTRAINT FK_TAG_1
      FOREIGN KEY (TAG_TYPE_ID)
      REFERENCES TAG_TYPE (TAG_TYPE_ID);

