connect 'jdbc:derby:workstyleDB;create=true;user=workstyle;password=hogehoge';
--connect 'jdbc:derby://localhost:1527/workstyleDB;create=true;user=workstyle;password=hogehoge';
create schema workstyle;

CREATE TABLE ID_TABLE
(
    ID_TABLE_ID INT,
    TABLE_NAME VARCHAR (255) NOT NULL,
    NEXT_ID INT,
    QUANTITY INT,
    PRIMARY KEY(ID_TABLE_ID),
    UNIQUE (TABLE_NAME)
);

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 INT 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);

CREATE VIEW ATTACHED_FILE AS
SELECT F.FILE_ID, F.NAME, F.PATH, R.TASK_ID
FROM FILE_INFO F, TASK_FILE_RELATION R
WHERE F.FILE_ID = R.FILE_ID;

INSERT INTO ID_TABLE (ID_TABLE_ID, TABLE_NAME, NEXT_ID, QUANTITY) VALUES (1, 'TASK', 2001, 1);
INSERT INTO ID_TABLE (ID_TABLE_ID, TABLE_NAME, NEXT_ID, QUANTITY) VALUES (2, 'TAG', 2001, 1);
INSERT INTO ID_TABLE (ID_TABLE_ID, TABLE_NAME, NEXT_ID, QUANTITY) VALUES (4, 'TASK_COMMENT', 2001, 1);
INSERT INTO ID_TABLE (ID_TABLE_ID, TABLE_NAME, NEXT_ID, QUANTITY) VALUES (5, 'TASK_FILE_RELATION', 2001, 1);
INSERT INTO ID_TABLE (ID_TABLE_ID, TABLE_NAME, NEXT_ID, QUANTITY) VALUES (6, 'FILE_INFO', 2001, 1);
INSERT INTO ID_TABLE (ID_TABLE_ID, TABLE_NAME, NEXT_ID, QUANTITY) VALUES (7, 'TAG_TYPE', 2001, 1);

INSERT INTO TAG_TYPE (TAG_TYPE_ID, NAME, STYLE_CLASS, SORT_ORDER) VALUES (0, '未分類', 'tag', 11);
INSERT INTO TAG_TYPE (TAG_TYPE_ID, NAME, STYLE_CLASS, SORT_ORDER) VALUES (1, 'タスク種別', 'tag1', 1);
INSERT INTO TAG_TYPE (TAG_TYPE_ID, NAME, STYLE_CLASS, SORT_ORDER) VALUES (2, 'プロジェクト', 'tag2', 2);
INSERT INTO TAG_TYPE (TAG_TYPE_ID, NAME, STYLE_CLASS, SORT_ORDER) VALUES (3, '担当', 'tag3', 3);
INSERT INTO TAG_TYPE (TAG_TYPE_ID, NAME, STYLE_CLASS, SORT_ORDER) VALUES (4, '処理方法', 'tag4', 4);
INSERT INTO TAG_TYPE (TAG_TYPE_ID, NAME, STYLE_CLASS, SORT_ORDER) VALUES (5, '備考', 'tag5', 5);
INSERT INTO TAG_TYPE (TAG_TYPE_ID, NAME, STYLE_CLASS, SORT_ORDER) VALUES (6, 'イテレーション', 'tag6', 6);
INSERT INTO TAG_TYPE (TAG_TYPE_ID, NAME, STYLE_CLASS, SORT_ORDER) VALUES (7, 'コンテクスト', 'tag7', 7);
INSERT INTO TAG_TYPE (TAG_TYPE_ID, NAME, STYLE_CLASS, SORT_ORDER) VALUES (8, '予備１', 'tag8', 8);
INSERT INTO TAG_TYPE (TAG_TYPE_ID, NAME, STYLE_CLASS, SORT_ORDER) VALUES (9, '予備２', 'tag9', 9);
INSERT INTO TAG_TYPE (TAG_TYPE_ID, NAME, STYLE_CLASS, SORT_ORDER) VALUES (10, '予備３', 'tag10', 10);

INSERT INTO STATUS(STATUS_ID, NAME, SORT_ORDER, IMAGE) VALUES (1, '実行中', 	1, 'on_going.png');
INSERT INTO STATUS(STATUS_ID, NAME, SORT_ORDER, IMAGE) VALUES (2, 'すぐやる', 	2, 'rapid.png');
INSERT INTO STATUS(STATUS_ID, NAME, SORT_ORDER, IMAGE) VALUES (3, 'そのうち', 	3, 'normal.png');
INSERT INTO STATUS(STATUS_ID, NAME, SORT_ORDER, IMAGE) VALUES (4, '保留', 		4, 'pending.png');
INSERT INTO STATUS(STATUS_ID, NAME, SORT_ORDER, IMAGE) VALUES (5, 'レビュー待ち', 5, 'review.png');
INSERT INTO STATUS(STATUS_ID, NAME, SORT_ORDER, IMAGE) VALUES (6, '終わった', 	6, 'finish.png');
INSERT INTO STATUS(STATUS_ID, NAME, SORT_ORDER, IMAGE) VALUES (7, '未確認', 	0, 'unknown.png');
