CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    nick text UNIQUE NOT NULL,
    hash text NOT NULL,
    email text NOT NULL,
    created_on timestamp NOT NULL DEFAULT now(),
    avatar text NOT NULL DEFAULT '',
    contact text NOT NULL DEFAULT '',
    bio text NOT NULL DEFAULT ''
);

CREATE TABLE rooms (
    room_id SERIAL PRIMARY KEY,
    name text UNIQUE NOT NULL,
    created_on timestamp NOT NULL DEFAULT now(),
    admin_only bool NOT NULL DEFAULT false
);

CREATE TABLE messages (
    message_id SERIAL PRIMARY KEY,
    user_id integer NOT NULL REFERENCES users,
    room_id integer NOT NULL REFERENCES rooms,
    content text NOT NULL,
    created_on timestamp NOT NULL DEFAULT now(),
    is_image bool NOT NULL
);

CREATE INDEX user_id_idx ON messages (user_id);
CREATE INDEX room_id_idx ON messages (room_id);
CREATE INDEX created_on_idx ON messages (created_on);

INSERT INTO rooms (room_id, name) VALUES (1, 'dumpfm');