hxHgZDTE

1CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
2CREATE EXTENSION IF NOT EXISTS "citext";
3
4CREATE TABLE "users"
5(
6    "id"            UUID PRIMARY KEY            DEFAULT uuid_generate_v1mc(),
7    "username"      VARCHAR(25)                 NOT NULL UNIQUE,
8    "firstname"     VARCHAR(255)                NOT NULL,
9    "lastname"      VARCHAR(255)                NOT NULL,
10    "password"      VARCHAR(255)                NOT NULL,
11    "email"         VARCHAR(255)                NOT NULL,
12    "profile_pic"   VARCHAR(255)                        ,
13    "is_admin"      BOOLEAN                     NOT NULL DEFAULT false,
14    "discord_token" UUID                        NOT NULL DEFAULT uuid_generate_v4()
15);
16
17CREATE TABLE "posts"
18(
19    "id"            UUID PRIMARY KEY            DEFAULT uuid_generate_v1mc(),
20    "is_approved"   BOOLEAN                     NOT NULL DEFAULT false,
21    "title"         VARCHAR(255)                NOT NULL,
22    "thumbnail"     VARCHAR(255)                NOT NULL,
23    "body"          TEXT                        NOT NULL,
24    "creation_date" TIMESTAMP                   NOT NULL DEFAULT current_timestamp,
25    "approval_date" TIMESTAMP,
26    "post_author"     UUID,
27    CONSTRAINT fk0_author
28        FOREIGN KEY (post_author)
29            REFERENCES users(id)
30            ON DELETE SET NULL
31);
32
33CREATE TABLE "comments"
34(
35    "id"            UUID PRIMARY KEY            DEFAULT uuid_generate_v1mc(),
36    "post_id"       UUID                        NOT NULL,
37    "author_id"     UUID                        NOT NULL,
38    "body"          VARCHAR(500)                NOT NULL,
39    "upvotes"       INTEGER                     NOT NULL DEFAULT 0,
40
41    CONSTRAINT fk0_post
42        FOREIGN KEY (post_id)
43            REFERENCES posts(id)
44            ON DELETE SET NULL,
45    CONSTRAINT fk1_author
46        FOREIGN KEY (author_id)
47            REFERENCES users(id)
48            ON DELETE SET NULL
49)