BEGIN TRANSACTION;
SET search_path = public;

-- Migration Start

CREATE SCHEMA sys;

ALTER SCHEMA sys OWNER TO xssbook;

CREATE DOMAIN sys."*/*" AS BYTEA;

CREATE TABLE sys.database_info (
	name TEXT DEFAULT ''::text NOT NULL,
	jwt_secret TEXT DEFAULT ''::text NOT NULL,
	curr_revision INTEGER DEFAULT 0 NOT NULL
);

ALTER TABLE sys.database_info
	ADD CONSTRAINT database_info_pkey PRIMARY KEY (name);

ALTER TABLE sys.database_info OWNER TO xssbook;

INSERT INTO sys.database_info
	(name, curr_revision) VALUES (current_database(), 0);

CREATE TYPE sys.JWT AS (
	token TEXT
);

CREATE SCHEMA admin;

ALTER SCHEMA admin OWNER TO xssbook;

CREATE SEQUENCE IF NOT EXISTS sys.user_id_seq
	START WITH 1
	INCREMENT BY 1
	NO MINVALUE
	NO MAXVALUE
	CACHE 1;

ALTER TABLE sys.user_id_seq OWNER TO xssbook;

CREATE TABLE admin.user (
	id				INTEGER DEFAULT nextval('sys.user_id_seq'::regclass) NOT NULL,
	username		TEXT NOT NULL,
	password		TEXT NOT NULL,
	role			NAME DEFAULT 'rest_user'::text NOT NULL,
	first_name		TEXT DEFAULT ''::text NOT NULL,
	last_name		TEXT DEFAULT ''::text NOT NULL,
	middle_name		TEXT DEFAULT ''::text NOT NULL,
	email			TEXT DEFAULT ''::text NOT NULL,
	gender			TEXT DEFAULT ''::text NOT NULL,
	birth_date		TIMESTAMP WITH TIME ZONE NOT NULL,
	profile_bio		TEXT DEFAULT ''::text NOT NULL,
	created			TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
	modified		TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
	seen			TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
	deleted			BOOLEAN DEFAULT FALSE NOT NULL
);

ALTER TABLE admin.user OWNER TO xssbook;

ALTER TABLE ONLY admin.user
	ADD CONSTRAINT user_pkey PRIMARY KEY (id);

ALTER TABLE ONLY admin.user
	ADD CONSTRAINT user_username_unique UNIQUE (username);

CREATE SEQUENCE IF NOT EXISTS sys.post_id_seq
	START WITH 1
	INCREMENT BY 1
	NO MINVALUE
	NO MAXVALUE
	CACHE 1;

ALTER TABLE sys.post_id_seq OWNER TO xssbook;

CREATE TABLE admin.post (
	id			INTEGER DEFAULT nextval('sys.post_id_seq'::regclass) NOT NULL,
	user_id		INTEGER NOT NULL,
	content		TEXT DEFAULT ''::text NOT NULL,
	created		TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
	modified	TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
	deleted		BOOLEAN DEFAULT FALSE NOT NULL
);

ALTER TABLE admin.post OWNER TO xssbook;

ALTER TABLE ONLY admin.post
	ADD CONSTRAINT post_pkey PRIMARY KEY (id);

ALTER TABLE ONLY admin.post
	ADD CONSTRAINT post_user_id_fkey FOREIGN KEY (user_id) REFERENCES admin.user (id) ON DELETE CASCADE;

CREATE SEQUENCE IF NOT EXISTS sys.comment_id_seq
	START WITH 1
	INCREMENT BY 1
	NO MINVALUE
	NO MAXVALUE
	CACHE 1;

CREATE TABLE admin.comment (
	id			INTEGER DEFAULT nextval('sys.comment_id_seq'::regclass) NOT NULL,
	user_id		INTEGER NOT NULL,
	post_id		INTEGER NOT NULL,
	content		TEXT DEFAULT ''::text NOT NULL,
	created		TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
	modified	TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
	deleted		BOOLEAN DEFAULT FALSE NOT NULL
);

ALTER TABLE admin.comment OWNER TO xssbook;

ALTER TABLE ONLY admin.comment
	ADD CONSTRAINT comment_pkey PRIMARY KEY (id);

ALTER TABLE ONLY admin.comment
	ADD CONSTRAINT comment_user_id_fkey FOREIGN KEY (user_id) REFERENCES admin.user (id) ON DELETE CASCADE;

ALTER TABLE ONLY admin.comment
	ADD CONSTRAINT comment_post_id_fkey FOREIGN KEY (post_id) REFERENCES admin.post (id) ON DELETE CASCADE;

CREATE SEQUENCE IF NOT EXISTS sys.like_id_seq
	START WITH 1
	INCREMENT BY 1
	NO MINVALUE
	NO MAXVALUE
	CACHE 1;

CREATE TABLE admin.like (
	id			INTEGER DEFAULT nextval('sys.like_id_seq'::regclass) NOT NULL,
	user_id		INTEGER NOT NULL,
	post_id		INTEGER,
	comment_id  INTEGER,
	value		BOOLEAN NOT NULL DEFAULT TRUE,
	created		TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
	modified	TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL
);

ALTER TABLE admin.like OWNER TO xssbook;

ALTER TABLE ONLY admin.like
	ADD CONSTRAINT like_pkey PRIMARY KEY (id);

ALTER TABLE ONLY admin.like
	ADD CONSTRAINT like_user_id_fkey FOREIGN KEY (user_id) REFERENCES admin.user (id) ON DELETE CASCADE;

ALTER TABLE ONLY admin.like
	ADD CONSTRAINT like_post_id_fkey FOREIGN KEY (post_id) REFERENCES admin.post (id) ON DELETE CASCADE;

ALTER TABLE ONLY admin.like
	ADD CONSTRAINT like_comment_id_fkey FOREIGN KEY (comment_id) REFERENCES admin.comment (id) ON DELETE CASCADE;

ALTER TABLE ONLY admin.like
	ADD CONSTRAINT like_post_id_unique UNIQUE (user_id, post_id);

ALTER TABLE ONLY admin.like
	ADD CONSTRAINT like_comment_id_unique UNIQUE (user_id, comment_id);

CREATE SEQUENCE IF NOT EXISTS sys.follow_id_seq
	START WITH 1
	INCREMENT BY 1
	NO MINVALUE
	NO MAXVALUE
	CACHE 1;

CREATE TABLE admin.follow (
	id			INTEGER DEFAULT nextval('sys.follow_id_seq'::regclass) NOT NULL,
	follower_id	INTEGER NOT NULL,
	followee_id	INTEGER NOT NULL,
	value		BOOLEAN NOT NULL DEFAULT TRUE,
	created		TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
	modified	TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL
);

ALTER TABLE admin.follow OWNER TO xssbook;

ALTER TABLE ONLY admin.follow
	ADD CONSTRAINT follow_pkey PRIMARY KEY (id);

ALTER TABLE ONLY admin.follow
	ADD CONSTRAINT follow_follower_id FOREIGN KEY (follower_id) REFERENCES admin.user (id) ON DELETE CASCADE;

ALTER TABLE ONLY admin.follow
	ADD CONSTRAINT follow_followee_id FOREIGN KEY (followee_id) REFERENCES admin.user (id) ON DELETE CASCADE;

ALTER TABLE ONLY admin.follow
	ADD CONSTRAINT follow_follower_unique UNIQUE (follower_id, followee_id);

CREATE SEQUENCE IF NOT EXISTS sys.media_id_seq
	START WITH 1
	INCREMENT BY 1
	NO MINVALUE
	NO MAXVALUE
	CACHE 1;

CREATE TABLE admin.media (
	id			INTEGER DEFAULT nextval('sys.media_id_seq'::regclass) NOT NULL,
	name		TEXT NOT NULL,
	content		BYTEA NOT NULL,
	type		TEXT NOT NULL,
	created		TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
	modified	TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL
);

ALTER TABLE admin.media OWNER TO xssbook;

ALTER TABLE ONLY admin.media
	ADD CONSTRAINT media_pkey PRIMARY KEY (id);

ALTER TABLE ONLY admin.media
	ADD CONSTRAINT media_name_unique UNIQUE (name);

CREATE SEQUENCE IF NOT EXISTS sys.user_media_id_seq
	START WITH 1
	INCREMENT BY 1
	NO MINVALUE
	NO MAXVALUE
	CACHE 1;

CREATE TYPE admin.user_media_type AS ENUM (
	'avatar', 'banner'
);

CREATE TABLE admin.user_media (
	id			INTEGER DEFAULT nextval('sys.user_media_id_seq'::regclass) NOT NULL,
	media_id	INTEGER NOT NULL,
	user_id		INTEGER NOT NULL,
	type		admin.user_media_type NOT NULL
);

ALTER TABLE admin.user_media OWNER TO xssbook;

ALTER TABLE ONLY admin.user_media
	ADD CONSTRAINT user_media_pkey PRIMARY KEY (id);

ALTER TABLE ONLY admin.user_media
	ADD CONSTRAINT user_media_media_id_fkey FOREIGN KEY (media_id) REFERENCES admin.media (id) ON DELETE CASCADE;

ALTER TABLE ONLY admin.user_media
	ADD CONSTRAINT user_media_user_id_fkey FOREIGN KEY (user_id) REFERENCES admin.user (id) ON DELETE CASCADE;

ALTER TABLE ONLY admin.user_media
	ADD CONSTRAINT user_media_type_unique UNIQUE (user_id, type);

ALTER DATABASE xssbook SET search_path = admin,public;
ALTER DATABASE xssbook SET bytea_output = 'hex';
-- Migration End;

-- Set Current Revision
UPDATE sys.database_info SET curr_revision = 1 WHERE name = current_database();

COMMIT TRANSACTION;
