CREATE TABLE ACCOUNT(
		USER_NAME VARCHAR(100) NOT NULL,
		MAIL_ADDRESS VARCHAR(100) NOT NULL,
		PASSWORD VARCHAR(40) NOT NULL,
		ADMINISTRATOR BOOLEAN NOT NULL,
		URL VARCHAR(200),
		REGISTERED_DATE TIMESTAMP NOT NULL,
		UPDATED_DATE TIMESTAMP NOT NULL,
		LAST_LOGIN_DATE TIMESTAMP
);

CREATE TABLE REPOSITORY(
		USER_NAME VARCHAR(100) NOT NULL,
		REPOSITORY_NAME VARCHAR(100) NOT NULL,
		PRIVATE BOOLEAN 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(
		USER_NAME VARCHAR(100) NOT NULL,
		REPOSITORY_NAME VARCHAR(100) NOT NULL,
		COLLABORATOR_NAME VARCHAR(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,
		ASSIGNED_USER_NAME VARCHAR(100),
		TITLE TEXT NOT NULL,
		CONTENT TEXT,
		CLOSED BOOLEAN 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,
		ACTION VARCHAR(10),
		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 VARCHAR(100) NOT NULL,
		MILESTONE_ID INT AUTO_INCREMENT,
		TITLE VARCHAR(100) NOT NULL,
		DESCRIPTION TEXT,
		DUE_DATE TIMESTAMP,
		CLOSED_DATE TIMESTAMP
);

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 (USER_NAME, REPOSITORY_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 (USER_NAME, REPOSITORY_NAME);
ALTER TABLE COLLABORATOR ADD CONSTRAINT IDX_COLLABORATOR_FK0 FOREIGN KEY (USER_NAME, REPOSITORY_NAME) REFERENCES REPOSITORY (USER_NAME, REPOSITORY_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_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,
  ADMINISTRATOR,
  URL,
  REGISTERED_DATE,
  UPDATED_DATE,
  LAST_LOGIN_DATE
) VALUES (
  'root',
  'root@localhost',
  'dc76e9f0c0006e8f919e0c515c66dbba3982f785',
  true,
  'https://github.com/gitbucket/gitbucket',
  SYSDATE,
  SYSDATE,
  NULL
);