init.sql

DROP TABLE IF EXISTS "topic" CASCADE;
DROP TABLE IF EXISTS "category" CASCADE;
DROP TABLE IF EXISTS "user" CASCADE;
DROP TABLE IF EXISTS "post" CASCADE;
DROP TABLE IF EXISTS "follow" CASCADE;
DROP TABLE IF EXISTS "friendship" CASCADE;
DROP TABLE IF EXISTS "react" CASCADE;
DROP TABLE IF EXISTS "keyWords" CASCADE;
DROP TABLE IF EXISTS "topicAbout" CASCADE;
DROP TABLE IF EXISTS "subCategory" CASCADE;
DROP TABLE IF EXISTS "past_event" CASCADE;
DROP TABLE IF EXISTS "futur_event" CASCADE;
DROP TABLE IF EXISTS "coming_event" CASCADE;
DROP TABLE IF EXISTS "post_event_assign" CASCADE;
DROP TABLE IF EXISTS "likes" CASCADE;
DROP TABLE IF EXISTS "interaction_past" CASCADE;
DROP TABLE IF EXISTS "interaction_futur" CASCADE;
DROP TYPE IF EXISTS "group_id_t" CASCADE;
DROP TYPE IF EXISTS "emoji_t" CASCADE;
DROP TYPE IF EXISTS "user_action" CASCADE;

/**
Sujets de discussions
*/
CREATE TABLE "topic" (
	"id" serial NOT NULL,
	"title" varchar(255) NOT NULL,
	"category_id" int NOT NULL,
	"synopsis" text NOT NULL,
	"score" float NOT NULL,
	"percent_rank" float NOT NULL DEFAULT 1.0,
	"review_score" float NOT NULL DEFAULT 0.0,
	"actor" int,
	"language" varchar(255),
	"budget" int,
	PRIMARY KEY("id")
);

CREATE TABLE "keyWords" (
	"id" serial NOT NULL,
	"word" varchar(255) NOT NULL,
	"description" text,
	PRIMARY KEY("id")
);

CREATE TABLE "topicAbout" (
	"topic_id" int NOT NULL,
	"keyword_id" int NOT NULL,
	PRIMARY KEY("topic_id", "keyword_id")
);

/**
utilisateurs
*/

CREATE TYPE "group_id_t" AS ENUM ('admin', 'acteur', 'directeur', 'salle_cine', 'usr');

CREATE TABLE "user" (
	"id" serial NOT NULL,
	"username" varchar(255) NOT NULL,
	"password_hash" varchar(255) NOT NULL,
	"name" varchar(255) UNIQUE NOT NULL,
	"active" boolean NOT NULL DEFAULT FALSE,
	"group_id" group_id_t NOT NULL,
	PRIMARY KEY("id")
);

CREATE TABLE "follow" (
	"follower" int NOT NULL,
	"followed" int NOT NULL,
	PRIMARY KEY("follower", "followed"),
	CHECK(follower <> followed)
);

CREATE TABLE "friendship" (
	"user1" int NOT NULL,
	"user2" int NOT NULL,
	PRIMARY KEY("user1", "user2"),
	CHECK(user1 < user2)
);
/**
genre
*/

CREATE TABLE "category" (
	"id" serial NOT NULL,
	"name" varchar(255) UNIQUE NOT NULL,
	"description" text,
	"last_update" timestamp NOT NULL,
	"topic_count" int NOT NULL DEFAULT 0,
	PRIMARY KEY("id")
);

CREATE TABLE "subCategory" (
	"initial_cat" int NOT NULL,
	"sub_cat" int NOT NULL,
	PRIMARY KEY("initial_cat", "sub_cat"),
	CHECK(initial_cat <> sub_cat)
);

/**
discussion
*/

CREATE TABLE "post" (
	"id" serial NOT NULL,
	"user_id" int NOT NULL, FOREIGN KEY("user_id") REFERENCES "user"("id"),
	"topic_id" int NOT NULL, FOREIGN KEY("topic_id") REFERENCES "topic"("id"),
	"uploaded_at" timestamp NOT NULL,
	"content" text NOT NULL,
	"reply_to" int,
	"reply_count" int NOT NULL DEFAULT 0,
	"edited" boolean NOT NULL DEFAULT FALSE,
	"like_count" int NOT NULL DEFAULT 0,
	PRIMARY KEY("id")
);

CREATE TYPE "emoji_t" AS ENUM ('excellent', 'good', 'ok', 'bad', 'very_bad');

CREATE TABLE "react" (
	"emoji" emoji_t NOT NULL,
	"date" timestamp NOT NULL,
	"user_id" int NOT NULL,
	"post_id" int NOT NULL,
	PRIMARY KEY("user_id", "post_id")
);

CREATE TABLE "likes" (
	"date" timestamp NOT NULL,
	"post_id" int NOT NULL,
	"user_id" int NOT NULL,
	PRIMARY KEY("post_id", "user_id")
);

CREATE TABLE "past_event" (
	"id_p" int NOT NULL CHECK(id_p % 2 = 1),
	"title" varchar(255) NOT NULL,
	"place" varchar(255) NOT NULL,
	"date" timestamp NOT NULL,
	"organisators" text NOT NULL,
	"film" varchar(255) NOT NULL,
	"participants_count" int NOT NULL,
	"links" text,
	PRIMARY KEY("id_p")
);

CREATE TABLE "futur_event" (
	"id_f" int NOT NULL CHECK(id_f % 2 = 0),
	"title" varchar(255) NOT NULL,
	"place" varchar(255) NOT NULL,
	"date" timestamp NOT NULL,
	"organisators" text NOT NULL,
	"film" varchar(255) NOT NULL,
	"available_places" int NOT NULL,
	"price" int NOT NULL,
	"program" text,
	PRIMARY KEY("id_f")
);

CREATE TYPE "user_action" AS ENUM ('interessed', 'participate');


CREATE TABLE "post_event_assign" (
	"id" serial NOT NULL,
	"id_post" int NOT NULL,
	"id_past" int,
	"id_futur" int,
	PRIMARY KEY("id"),
	CHECK((id_past IS NOT NULL AND id_futur IS NULL) OR 
		(id_past IS NULL AND id_futur IS NOT NULL))
);

CREATE TABLE "interaction_past" (
	"user_id" int NOT NULL, FOREIGN KEY("user_id") REFERENCES "user"("id"),
	"event_id" int NOT NULL, FOREIGN KEY("event_id") REFERENCES "past_event"("id_p"),
	"act" user_action NOT NULL, 
	PRIMARY KEY("user_id", "event_id")
);

CREATE TABLE "interaction_futur" (
	"user_id" int NOT NULL, FOREIGN KEY("user_id") REFERENCES "user"("id"),
	"event_id" int NOT NULL, FOREIGN KEY("event_id") REFERENCES "futur_event"("id_f"),
	"act" user_action NOT NULL, 
	PRIMARY KEY("user_id", "event_id")
);

ALTER TABLE "post"
ADD FOREIGN KEY("reply_to") REFERENCES "post"("id")
ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE "topic"
ADD FOREIGN KEY("actor") REFERENCES "user"("id")
ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE "topic"
ADD FOREIGN KEY("category_id") REFERENCES "category"("id")
ON UPDATE NO ACTION ON DELETE CASCADE;
ALTER TABLE "follow"
ADD FOREIGN KEY("follower") REFERENCES "user"("id")
ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE "follow"
ADD FOREIGN KEY("followed") REFERENCES "user"("id")
ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE "friendship"
ADD FOREIGN KEY("user1") REFERENCES "user"("id")
ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE "friendship"
ADD FOREIGN KEY("user2") REFERENCES "user"("id")
ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE "react"
ADD FOREIGN KEY("user_id") REFERENCES "user"("id")
ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE "react"
ADD FOREIGN KEY("post_id") REFERENCES "post"("id")
ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE "topicAbout"
ADD FOREIGN KEY("topic_id") REFERENCES "topic"("id")
ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE "topicAbout"
ADD FOREIGN KEY("keyword_id") REFERENCES "keyWords"("id")
ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE "subCategory"
ADD FOREIGN KEY("initial_cat") REFERENCES "category"("id")
ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE "subCategory"
ADD FOREIGN KEY("sub_cat") REFERENCES "category"("id")
ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE "post_event_assign"
ADD FOREIGN KEY("id_post") REFERENCES "post"("id")
ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE "post_event_assign"
ADD FOREIGN KEY("id_past") REFERENCES "past_event"("id_p")
ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE "post_event_assign"
ADD FOREIGN KEY("id_futur") REFERENCES "futur_event"("id_f")
ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE "likes"
ADD FOREIGN KEY("post_id") REFERENCES "post"("id")
ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE "likes"
ADD FOREIGN KEY("user_id") REFERENCES "user"("id")
ON UPDATE NO ACTION ON DELETE NO ACTION;