butAJLds

1DROP TABLE IF EXISTS "user" CASCADE;
2DROP TABLE IF EXISTS administrator CASCADE;
3DROP TABLE IF EXISTS activity CASCADE;
4DROP TABLE IF EXISTS friend_request CASCADE;
5DROP TABLE IF EXISTS creation CASCADE;
6DROP TABLE IF EXISTS rate CASCADE;
7DROP TABLE IF EXISTS content CASCADE;
8DROP TABLE IF EXISTS post CASCADE;
9DROP TABLE IF EXISTS "comment" CASCADE;
10DROP TABLE IF EXISTS category CASCADE;
11DROP TABLE IF EXISTS notifications CASCADE;
12DROP TABLE IF EXISTS friendship CASCADE;
13DROP TABLE IF EXISTS content_rates CASCADE;
14DROP TABLE IF EXISTS favorite_posts CASCADE;
15DROP TABLE IF EXISTS post_category CASCADE;
16DROP TABLE IF EXISTS ban CASCADE;
17
18CREATE TABLE "user" (
19	id SERIAL PRIMARY KEY,
20    username text NOT NULL,
21    name text NOT NULL,
22    password text NOT NULL,
23	email text NOT NULL,
24	birthdate TIMESTAMP WITH TIME zone,
25	bio text,
26	image text,
27	CONSTRAINT user_username_key UNIQUE (username),
28	CONSTRAINT user_email_key UNIQUE (email)
29);
30
31CREATE TABLE administrator (
32    id_user INTEGER PRIMARY KEY,
33	CONSTRAINT administrator_id_user_fkey FOREIGN KEY (id_user) REFERENCES "user"(id)
34);
35
36CREATE TABLE content (
37	id SERIAL PRIMARY KEY,
38	body text NOT NULL,
39	creation_date TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
40	last_edition_date TIMESTAMP WITH TIME zone DEFAULT NULL,
41	edited BOOLEAN DEFAULT FALSE,
42	rating INTEGER DEFAULT 0,
43	id_creator INTEGER NOT NULL,
44	CONSTRAINT content_id_creator_fkey FOREIGN KEY (id_creator) REFERENCES "user"(id)
45);
46
47CREATE TABLE activity (
48    id SERIAL PRIMARY KEY,
49	id_user INTEGER NOT NULL,
50	"date" TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
51	CONSTRAINT activity_id_user_fkey FOREIGN KEY (id_user) REFERENCES "user"(id)
52);
53
54CREATE TABLE friend_request (
55    id_activity INTEGER PRIMARY KEY,
56	sender_id INTEGER NOT NULL,
57	receiver_id INTEGER NOT NULL,
58	accept_date TIMESTAMP WITH TIME zone DEFAULT NULL,
59	accepted  BOOLEAN DEFAULT false,
60	rejected  BOOLEAN DEFAULT false,
61	CONSTRAINT friend_request_id_activity_fkey FOREIGN KEY (id_activity) REFERENCES activity(id),
62	CONSTRAINT friend_request_sender_id_fkey FOREIGN KEY (sender_id) REFERENCES "user"(id),
63	CONSTRAINT friend_request_receiver_id_fkey FOREIGN KEY (receiver_id) REFERENCES "user"(id)
64);
65
66CREATE TABLE creation (
67    id_activity INTEGER PRIMARY KEY,
68	id_content INTEGER NOT NULL,
69	CONSTRAINT creation_id_activity_fkey FOREIGN KEY (id_activity) REFERENCES activity(id),
70	CONSTRAINT creation_id_content_fkey FOREIGN KEY (id_content) REFERENCES content(id)
71);
72
73CREATE TABLE rate (
74    id_activity INTEGER PRIMARY KEY,
75	value INTEGER NOT NULL CHECK (((value = 1) OR (value = -1))),
76	id_content INTEGER NOT NULL,
77	CONSTRAINT rate_id_activity_fkey FOREIGN KEY (id_activity) REFERENCES activity(id),
78	CONSTRAINT rate_id_content_fkey FOREIGN KEY (id_content) REFERENCES content(id)
79);
80
81CREATE TABLE post (
82    id_content INTEGER PRIMARY KEY,
83	title text NOT NULL,
84	image text,
85	CONSTRAINT post_id_content_fkey FOREIGN KEY (id_content) REFERENCES content(id)
86);
87
88CREATE TABLE "comment" (
89    id_content INTEGER PRIMARY KEY,
90	id_commented_post  INTEGER NOT NULL,
91	CONSTRAINT comment_id_content_fkey FOREIGN KEY (id_content) REFERENCES content(id),
92	CONSTRAINT comment_id_commented_post_fkey FOREIGN KEY (id_commented_post) REFERENCES post(id_content)
93);
94
95CREATE TABLE category (
96	id SERIAL PRIMARY KEY,
97	name text NOT NULL,
98	CONSTRAINT category_name_key UNIQUE (name)
99);
100
101CREATE TABLE notifications (
102	id_user INTEGER NOT NULL,
103	id_activity INTEGER NOT NULL,
104	PRIMARY KEY(id_user, id_activity),
105	CONSTRAINT notifications_id_user_fkey FOREIGN KEY (id_user) REFERENCES "user"(id),
106	CONSTRAINT notifications_id_activity_fkey FOREIGN KEY (id_activity) REFERENCES activity(id)
107);
108
109CREATE TABLE friendship (
110	id_user1 INTEGER NOT NULL,
111	id_user2 INTEGER NOT NULL,
112	PRIMARY KEY(id_user1, id_user2),
113	CONSTRAINT friendship_id_user1_fkey FOREIGN KEY (id_user1) REFERENCES "user"(id),
114	CONSTRAINT friendship_id_user2_fkey FOREIGN KEY (id_user2) REFERENCES "user"(id)
115);
116
117CREATE TABLE content_rates (
118	id_content INTEGER NOT NULL,
119	id_user INTEGER NOT NULL,
120	PRIMARY KEY(id_content, id_user),
121	CONSTRAINT content_rates_id_content_fkey FOREIGN KEY (id_content) REFERENCES content(id),
122	CONSTRAINT content_rates_id_user_fkey FOREIGN KEY (id_user) REFERENCES "user"(id)
123);
124
125CREATE TABLE favorite_posts (
126	id_post INTEGER NOT NULL, 
127	id_user INTEGER NOT NULL,
128	PRIMARY KEY(id_post, id_user),
129	CONSTRAINT favorite_posts_id_post_fkey FOREIGN KEY (id_post) REFERENCES post(id_content),
130	CONSTRAINT favorite_posts_id_user_fkey FOREIGN KEY (id_user) REFERENCES "user"(id)
131);
132
133CREATE TABLE post_category (
134	id_post INTEGER NOT NULL,
135	id_category INTEGER NOT NULL,
136	PRIMARY KEY(id_post, id_category),
137	CONSTRAINT post_category_id_post_fkey FOREIGN KEY (id_post) REFERENCES post(id_content),
138	CONSTRAINT post_category_id_category_fkey FOREIGN KEY (id_category) REFERENCES category(id)
139);
140
141CREATE TABLE ban (
142	id_user INTEGER NOT NULL,
143	id_administrator INTEGER NOT NULL,
144	ban_date TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
145	ban_duration INTEGER NOT NULL CHECK (ban_duration > 0),
146	PRIMARY KEY(id_user, id_administrator),
147	CONSTRAINT ban_id_user_fkey FOREIGN KEY (id_user) REFERENCES "user"(id),
148	CONSTRAINT ban_id_administrator_fkey FOREIGN KEY (id_administrator) REFERENCES administrator(id_user)
149);