/* Drop Tables */

DROP TABLE IF EXISTS SELIF;
DROP TABLE IF EXISTS TESTS;
DROP TABLE IF EXISTS USERS;




/* Create Tables */

CREATE TABLE SELIF
(
	SERIF_ID BIGSERIAL NOT NULL UNIQUE,
	SERIF TEXT NOT NULL,
	USER_ID BIGINT NOT NULL,
	CREATED_AT TIMESTAMP WITH TIME ZONE NOT NULL,
	PRIMARY KEY (SERIF_ID)
) WITHOUT OIDS;


CREATE TABLE TESTS
(
	TEST_ID BIGSERIAL NOT NULL UNIQUE,
	TEST_NAME VARCHAR NOT NULL,
	CREATED_AT TIMESTAMP WITH TIME ZONE NOT NULL,
	USER_ID BIGINT NOT NULL,
	PRIMARY KEY (TEST_ID)
) WITHOUT OIDS;


CREATE TABLE USERS
(
	USER_ID BIGSERIAL NOT NULL UNIQUE,
	NAME VARCHAR NOT NULL,
	CREATED_AT TIMESTAMP WITH TIME ZONE NOT NULL,
	PRIMARY KEY (USER_ID)
) WITHOUT OIDS;



/* Create Foreign Keys */

ALTER TABLE SELIF
	ADD CONSTRAINT FK_SELIF_USERS FOREIGN KEY (USER_ID)
	REFERENCES USERS (USER_ID)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT
;


ALTER TABLE TESTS
	ADD CONSTRAINT FK_TESTS_USERS FOREIGN KEY (USER_ID)
	REFERENCES USERS (USER_ID)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT
;



/* Comments */

COMMENT ON TABLE SELIF IS 'セリフ';
COMMENT ON COLUMN SELIF.SERIF_ID IS 'セリフID';
COMMENT ON COLUMN SELIF.SERIF IS 'セリフ : キャラクターセリフ
長い文書';
COMMENT ON COLUMN SELIF.USER_ID IS 'ユーザーID';
COMMENT ON COLUMN SELIF.CREATED_AT IS '作成日時';

COMMENT ON TABLE TESTS IS 'テスト';
COMMENT ON COLUMN TESTS.TEST_ID IS 'テストID';
COMMENT ON COLUMN TESTS.TEST_NAME IS 'テスト名';
COMMENT ON COLUMN TESTS.CREATED_AT IS '作成日時';
COMMENT ON COLUMN TESTS.USER_ID IS 'ユーザーID';

COMMENT ON TABLE USERS IS 'ユーザー';
COMMENT ON COLUMN USERS.USER_ID IS 'ユーザーID';
COMMENT ON COLUMN USERS.NAME IS '名前';
COMMENT ON COLUMN USERS.CREATED_AT IS '作成日時';