

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 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 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 VARCHAR(100) NOT NULL,
		MILESTONE_ID INT AUTO_INCREMENT,
		MILESTONE_NAME VARCHAR(100) NOT NULL
);

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