console.sql

CREATE table users (
    id serial,
    username text,
    email text,
    num_credits integer DEFAULT 0,
    first_name text,
    last_name text,
    password text,
    address text,
    organisation BOOL,
    region integer,
    join_date DATE,
    email_confirmed BOOL default false,
    totp_secret varchar(64),
    totp_confirmed BOOL default false,
    banned BOOL default false,
    PRIMARY KEY (id)
);

CREATE table challenges (
    id serial,
    creator integer,
    title text,
    description text,
    team_challenge BOOL,
    global BOOL,
    begin_time TIMESTAMP,
    end_time TIMESTAMP,
    regions integer[],
    PRIMARY KEY (id),
    FOREIGN KEY (creator) REFERENCES users (id)
);

CREATE table prizes (
    challenge integer,
    place integer,
    amount integer,
    PRIMARY KEY (challenge, place),
    FOREIGN KEY (challenge) REFERENCES challenges (id)
);

CREATE table posts (
    id serial,
    text text,
    creator integer,
    media text[],
    awards integer default 0,
    timestamp TIMESTAMP,
    PRIMARY KEY (id),
    FOREIGN KEY (creator) REFERENCES users (id)
);

CREATE table teams (
    id serial,
    team_name text,
    challenges integer,
    PRIMARY KEY (id),
    FOREIGN KEY (challenges) REFERENCES challenges (id)
);

CREATE table team_members (
    user_id integer,
    team integer,
    PRIMARY KEY (user_id, team),
    FOREIGN KEY (user_id) REFERENCES users (id),
    FOREIGN KEY (team) REFERENCES teams (id)
);

CREATE table friends (
    user1 integer,
    user2 integer,
    PRIMARY KEY (user1, user2),
    FOREIGN KEY (user1) REFERENCES users(id),
    FOREIGN KEY (user2) REFERENCES users(id)
);

create table friend_requests (
    requester integer,
    requestee integer,
    primary key(requester, requestee),
    foreign key (requester) references users(id),
    FOREIGN KEY (requestee) REFERENCES users(id)
);

create table feedback(
    post integer,
    user_id integer,
    type varchar(7),
    primary key(post, user_id),
    foreign key (post) references posts(id),
    foreign key(user_id) references users(id)
);

create table post_comment (
    id serial,
    user_id integer,
    post integer,
    timestamp timestamp,
    text varchar(1000),
    primary key(id),
    foreign key(post) references posts(id),
    foreign key(user_id) references users(id)
);