CREATE TABLE ACCOUNT(
		USER_ID IDENTITY NOT NULL,
		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 PROJECT(
		PROJECT_ID IDENTITY NOT NULL,
		PROJECT_NAME VARCHAR(100) NOT NULL,
		USER_ID INT NOT NULL,
		PROJECT_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 PROJECT_ACCOUNT(
		PROJECT_ID INT NOT NULL,
		USER_ID INT NOT NULL
);


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

--ALTER TABLE PROJECT ADD CONSTRAINT IDX_PROJECT_PK PRIMARY KEY (PROJECT_ID);
ALTER TABLE PROJECT ADD CONSTRAINT IDX_PROJECT_1 UNIQUE (PROJECT_NAME, USER_ID);

ALTER TABLE PROJECT_ACCOUNT ADD CONSTRAINT IDX_PROJECT_ACCOUNT_PK PRIMARY KEY (PROJECT_ID, USER_ID);
ALTER TABLE PROJECT_ACCOUNT ADD CONSTRAINT IDX_PROJECT_ACCOUNT_FK0 FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT (PROJECT_ID);
ALTER TABLE PROJECT_ACCOUNT ADD CONSTRAINT IDX_PROJECT_ACCOUNT_FK1 FOREIGN KEY (USER_ID) REFERENCES ACCOUNT (USER_ID);

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