database.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 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)
);