CREATE TABLE ACCOUNT(
		USER_NAME VARCHAR(100) NOT NULL,
		MAIL_ADDRESS VARCHAR(100) NOT NULL,
		PASSWORD VARCHAR(20) NOT NULL,
		USER_TYPE INT DEFAULT 0 NOT NULL,
		URL VARCHAR(200),
		REGISTERED_DATE TIMESTAMP NOT NULL,
		UPDATED_DATE TIMESTAMP NOT NULL,
		LAST_LOGIN_DATE TIMESTAMP
);

CREATE TABLE REPOSITORY(
		REPOSITORY_NAME VARCHAR(100) NOT NULL,
		USER_NAME VARCHAR(100) NOT NULL,
		REPOSITORY_TYPE INT DEFAULT 0 NOT NULL,
		DESCRIPTION TEXT,
		DEFAULT_BRANCH VARCHAR(100),
		REGISTERED_DATE TIMESTAMP NOT NULL,
		UPDATED_DATE TIMESTAMP NOT NULL,
		LAST_ACTIVITY_DATE TIMESTAMP NOT NULL
);

CREATE TABLE COLLABORATOR(
		REPOSITORY_NAME VARCHAR(100) NOT NULL,
		USER_NAME VARCHAR(100) NOT NULL,
		COLLABORATOR_NAME VARCHRA(100) NOT NULL
);

CREATE TABLE ISSUE(
		USER_NAME VARCHAR(100) NOT NULL,
		REPOSITORY_NAME VARCHAR(100) NOT NULL,
		ISSUE_ID INT NOT NULL,
		OPENED_USER_NAME VARCHAR(100) NOT NULL,
		MILESTONE_ID INT NOT NULL,
		TITLE TEXT NOT NULL,
		CONTENT TEXT NOT NULL,
		REGISTERED_DATE TIMESTAMP NOT NULL,
		UPDATED_DATE TIMESTAMP NOT NULL
);

CREATE TABLE ISSUE_ID(
		USER_NAME VARCHAR(100) NOT NULL,
		REPOSITORY_NAME VARCHAR(100) NOT NULL,
		ISSUE_ID INT NOT NULL
);

CREATE TABLE ISSUE_COMMENT(
		USER_NAME VARCHAR(100) NOT NULL,
		REPOSITORY_NAME VARCHAR(100) NOT NULL,
		ISSUE_ID INT NOT NULL,
		COMMENT_ID INT AUTO_INCREMENT,
		COMMENTED_USER_NAME VARCHAR(100) NOT NULL,
		CONTENT TEXT NOT NULL,
		REGISTERED_DATE TIMESTAMP NOT NULL,
		UPDATED_DATE TIMESTAMP NOT NULL
);

CREATE TABLE LABEL(
		USER_NAME VARCHAR(100) NOT NULL,
		REPOSITORY_NAME VARCHAR(100) NOT NULL,
		LABEL_ID INT AUTO_INCREMENT,
		LABEL_NAME VARCHAR(100) NOT NULL,
		COLOR CHAR(6) NOT NULL
);

CREATE TABLE ISSUE_LABEL(
		USER_NAME VARCHAR(100) NOT NULL,
		REPOSITORY_NAME VARCHAR(100) NOT NULL,
		ISSUE_ID INT NOT NULL,
		LABEL_ID INT NOT NULL
);

CREATE TABLE MILESTONE(
		USER_NAME VARCHAR(100) NOT NULL,
		REPOSITORY_NAME INT NOT NULL,
		MILESTONE_ID INT AUTO_INCREMENT,
		MILESTONE_NAME VARCHAR(100) NOT NULL
);

ALTER TABLE ACCOUNT ADD CONSTRAINT IDX_ACCOUNT_PK PRIMARY KEY (USER_NAME);
ALTER TABLE ACCOUNT ADD CONSTRAINT IDX_ACCOUNT_1 UNIQUE (MAIL_ADDRESS);

ALTER TABLE REPOSITORY ADD CONSTRAINT IDX_REPOSITORY_PK PRIMARY KEY (REPOSITORY_NAME, USER_NAME);
ALTER TABLE REPOSITORY ADD CONSTRAINT IDX_REPOSITORY_FK0 FOREIGN KEY (USER_NAME) REFERENCES ACCOUNT (USER_NAME);

ALTER TABLE COLLABORATOR ADD CONSTRAINT IDX_COLLABORATOR_PK PRIMARY KEY (REPOSITORY_NAME, USER_NAME);
ALTER TABLE COLLABORATOR ADD CONSTRAINT IDX_COLLABORATOR_FK0 FOREIGN KEY (REPOSITORY_NAME, USER_NAME) REFERENCES REPOSITORY (REPOSITORY_NAME, USER_NAME);
ALTER TABLE COLLABORATOR ADD CONSTRAINT IDX_COLLABORATOR_FK1 FOREIGN KEY (COLLABORATOR_NAME) REFERENCES ACCOUNT (USER_NAME);

ALTER TABLE ISSUE ADD CONSTRAINT IDX_ISSUE_PK PRIMARY KEY (ISSUE_ID, USER_NAME, REPOSITORY_NAME);
ALTER TABLE ISSUE ADD CONSTRAINT IDX_ISSUE_FK0 FOREIGN KEY (USER_NAME, REPOSITORY_NAME) REFERENCES REPOSITORY (USER_NAME, REPOSITORY_NAME);
ALTER TABLE ISSUE ADD CONSTRAINT IDX_ISSUE_FK1 FOREIGN KEY (OPENED_USER_NAME) REFERENCES ACCOUNT (USER_NAME);
ALTER TABLE ISSUE ADD CONSTRAINT IDX_ISSUE_FK2 FOREIGN KEY (MILESTONE_ID) REFERENCES MILESTONE (MILESTONE_ID);

ALTER TABLE ISSUE_ID ADD CONSTRAINT IDX_ISSUE_ID_PK PRIMARY KEY (USER_NAME, REPOSITORY_NAME);
ALTER TABLE ISSUE_ID ADD CONSTRAINT IDX_ISSUE_ID_FK1 FOREIGN KEY (USER_NAME, REPOSITORY_NAME) REFERENCES REPOSITORY (USER_NAME, REPOSITORY_NAME);

ALTER TABLE ISSUE_COMMENT ADD CONSTRAINT IDX_ISSUE_COMMENT_PK PRIMARY KEY (COMMENT_ID);
ALTER TABLE ISSUE_COMMENT ADD CONSTRAINT IDX_ISSUE_COMMENT_1 UNIQUE (USER_NAME, REPOSITORY_NAME, ISSUE_ID, COMMENT_ID);
ALTER TABLE ISSUE_COMMENT ADD CONSTRAINT IDX_ISSUE_COMMENT_FK0 FOREIGN KEY (USER_NAME, REPOSITORY_NAME, ISSUE_ID) REFERENCES ISSUE (USER_NAME, REPOSITORY_NAME, ISSUE_ID);

ALTER TABLE LABEL ADD CONSTRAINT IDX_LABEL_PK PRIMARY KEY (USER_NAME, REPOSITORY_NAME, LABEL_ID);
ALTER TABLE LABEL ADD CONSTRAINT IDX_LABEL_1 UNIQUE (USER_NAME, REPOSITORY_NAME, LABEL);
ALTER TABLE LABEL ADD CONSTRAINT IDX_LABEL_FK0 FOREIGN KEY (USER_NAME, REPOSITORY_NAME) REFERENCES REPOSITORY (USER_NAME, REPOSITORY_NAME);

ALTER TABLE ISSUE_LABEL ADD CONSTRAINT IDX_ISSUE_LABEL_PK PRIMARY KEY (USER_NAME, REPOSITORY_NAME, ISSUE_ID, LABEL_ID);
ALTER TABLE ISSUE_LABEL ADD CONSTRAINT IDX_ISSUE_LABEL_FK0 FOREIGN KEY (USER_NAME, REPOSITORY_NAME, ISSUE_ID) REFERENCES ISSUE (USER_NAME, REPOSITORY_NAME, ISSUE_ID);

ALTER TABLE MILESTONE ADD CONSTRAINT IDX_MILESTONE_PK PRIMARY KEY (USER_NAME, REPOSITORY_NAME, MILESTONE_ID);
ALTER TABLE MILESTONE ADD CONSTRAINT IDX_MILESTONE_FK0 FOREIGN KEY (USER_NAME, REPOSITORY_NAME) REFERENCES REPOSITORY (USER_NAME, REPOSITORY_NAME);

INSERT INTO ACCOUNT (
  USER_NAME,
  MAIL_ADDRESS,
  PASSWORD,
  USER_TYPE,
  URL,
  REGISTERED_DATE,
  UPDATED_DATE,
  LAST_LOGIN_DATE
) VALUES (
  'admin',
  'admin@localhost',
  'admin',
  1,
  'https://github.com/takezoe/gitbucket',
  SYSDATE,
  SYSDATE,
  NULL
);