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(10),
		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);