DROP TABLE IF EXISTS "user" CASCADE;
DROP TABLE IF EXISTS administrator CASCADE;
DROP TABLE IF EXISTS activity CASCADE;
DROP TABLE IF EXISTS friend_request CASCADE;
DROP TABLE IF EXISTS creation CASCADE;
DROP TABLE IF EXISTS rate CASCADE;
DROP TABLE IF EXISTS content CASCADE;
DROP TABLE IF EXISTS post CASCADE;
DROP TABLE IF EXISTS "comment" CASCADE;
DROP TABLE IF EXISTS category CASCADE;
DROP TABLE IF EXISTS notifications CASCADE;
DROP TABLE IF EXISTS friendship CASCADE;
DROP TABLE IF EXISTS content_rates CASCADE;
DROP TABLE IF EXISTS favorite_posts CASCADE;
DROP TABLE IF EXISTS post_category CASCADE;

CREATE TABLE "user" (
	id SERIAL PRIMARY KEY,
    username text NOT NULL,
    name text NOT NULL,
    password text NOT NULL,
	email text NOT NULL,
	birthdate TIMESTAMP WITH TIME zone,
	bio text,
	image text,
	id_admin_ban INTEGER DEFAULT NULL,
	CONSTRAINT user_username_key UNIQUE (username),
	CONSTRAINT user_email_key UNIQUE (email)
);

CREATE TABLE administrator (
    id_user INTEGER PRIMARY KEY,
	CONSTRAINT administrator_id_user_fkey FOREIGN KEY (id_user) REFERENCES "user"(id)
);

CREATE TABLE activity (
    id SERIAL PRIMARY KEY,
	id_user INTEGER NOT NULL,
	"date" TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
	CONSTRAINT activity_id_user_fkey FOREIGN KEY (id_user) REFERENCES "user"(id)
);

CREATE TABLE friend_request (
    id_activity INTEGER PRIMARY KEY,
	sender_id INTEGER NOT NULL,
	receiver_id INTEGER NOT NULL,
	accept_date TIMESTAMP WITH TIME zone DEFAULT NULL,
	accepted  BOOLEAN DEFAULT false,
	rejected  BOOLEAN DEFAULT false,
	CONSTRAINT friend_request_id_activity_fkey FOREIGN KEY (id_activity) REFERENCES activity(id),
	CONSTRAINT friend_request_sender_id_fkey FOREIGN KEY (sender_id) REFERENCES "user"(id),
	CONSTRAINT friend_request_receiver_id_fkey FOREIGN KEY (receiver_id) REFERENCES "user"(id)
);

CREATE TABLE creation (
    id_activity INTEGER PRIMARY KEY,
	id_content INTEGER NOT NULL,
	CONSTRAINT creation_id_activity_fkey FOREIGN KEY (id_activity) REFERENCES activity(id)
);

CREATE TABLE rate (
    id_activity INTEGER PRIMARY KEY,
	value INTEGER NOT NULL CHECK (((value = 1) OR (value = -1))),
	id_content INTEGER NOT NULL,
	CONSTRAINT rate_id_activity_fkey FOREIGN KEY (id_activity) REFERENCES activity(id)
);

CREATE TABLE content (
	id SERIAL PRIMARY KEY,
	body text NOT NULL,
	creation_date TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
	last_edition_date TIMESTAMP WITH TIME zone DEFAULT NULL,
	edited BOOLEAN DEFAULT FALSE,
	rating INTEGER DEFAULT 0,
	id_creator INTEGER NOT NULL,
	CONSTRAINT content_id_creator_fkey FOREIGN KEY (id_creator) REFERENCES "user"(id)
);

CREATE TABLE post (
    id_content INTEGER PRIMARY KEY,
	title text NOT NULL,
	image text,
	CONSTRAINT post_id_content_fkey FOREIGN KEY (id_content) REFERENCES content(id)
);

CREATE TABLE "comment" (
    id_content INTEGER PRIMARY KEY,
	id_commented_post  INTEGER NOT NULL,
	CONSTRAINT comment_id_content_fkey FOREIGN KEY (id_content) REFERENCES content(id),
	CONSTRAINT comment_id_commented_post_fkey FOREIGN KEY (id_commented_post) REFERENCES post(id_content)
);

CREATE TABLE category (
	id SERIAL PRIMARY KEY,
	name text NOT NULL,
	CONSTRAINT category_name_key UNIQUE (name)
);

CREATE TABLE notifications (
	id_user INTEGER NOT NULL,
	id_activity INTEGER NOT NULL,
	PRIMARY KEY(id_user, id_activity),
	CONSTRAINT notifications_id_user_fkey FOREIGN KEY (id_user) REFERENCES "user"(id),
	CONSTRAINT notifications_id_activity_fkey FOREIGN KEY (id_activity) REFERENCES activity(id)
);

CREATE TABLE friendship (
	id_user1 INTEGER NOT NULL,
	id_user2 INTEGER NOT NULL,
	PRIMARY KEY(id_user1, id_user2),
	CONSTRAINT friendship_id_user1_fkey FOREIGN KEY (id_user1) REFERENCES "user"(id),
	CONSTRAINT friendship_id_user2_fkey FOREIGN KEY (id_user2) REFERENCES "user"(id)
);

CREATE TABLE content_rates (
	id_content INTEGER NOT NULL,
	id_user INTEGER NOT NULL,
	PRIMARY KEY(id_content, id_user),
	CONSTRAINT content_rates_id_content_fkey FOREIGN KEY (id_content) REFERENCES content(id),
	CONSTRAINT content_rates_id_user_fkey FOREIGN KEY (id_user) REFERENCES "user"(id)
);

CREATE TABLE favorite_posts (
	id_post INTEGER NOT NULL,
	id_user INTEGER NOT NULL,
	PRIMARY KEY(id_post, id_user),
	CONSTRAINT favorite_posts_id_post_fkey FOREIGN KEY (id_post) REFERENCES post(id_content),
	CONSTRAINT favorite_posts_id_user_fkey FOREIGN KEY (id_user) REFERENCES "user"(id)
);

CREATE TABLE post_category (
	id_post INTEGER NOT NULL,
	id_category INTEGER NOT NULL,
	PRIMARY KEY(id_post, id_category),
	CONSTRAINT post_category_id_post_fkey FOREIGN KEY (id_post) REFERENCES post(id_content),
	CONSTRAINT post_category_id_category_fkey FOREIGN KEY (id_category) REFERENCES category(id)
);

-- Circular dependencies
ALTER TABLE "user" ADD CONSTRAINT user_id_admin_ban_fkey FOREIGN KEY (id_admin_ban) REFERENCES administrator(id_user);
ALTER TABLE creation ADD CONSTRAINT creation_id_content_fkey FOREIGN KEY (id_content) REFERENCES content(id);
ALTER TABLE rate ADD CONSTRAINT rate_id_content_fkey FOREIGN KEY (id_content) REFERENCES content(id);