CREATE DATABASE dumpfm;

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(),
    is_admin boolean NOT NULL DEFAULT false,
    avatar text NOT NULL DEFAULT '',
    contact text NOT NULL DEFAULT '',
    bio text NOT NULL DEFAULT '',
    profile_bg text
);
CREATE INDEX users_nick_idx ON users (lower(nick));

CREATE TABLE rooms (
    room_id SERIAL PRIMARY KEY,
    key text UNIQUE NOT NULL,
    name text NOT NULL,
    description text 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 messages_user_id_idx ON messages (user_id);
CREATE INDEX messages_room_id_idx ON messages (room_id);
CREATE INDEX messages_created_on_idx ON messages (created_on);
CREATE INDEX messages_is_image_idx ON messages (is_image);

-- Queries to support:
-- 1) What are my favorite images? (By room, time, or author)
-- 2) Who favorited me? (By user, image, or time)
-- 3) What are the most favorited images? (By room, time, or author)
CREATE TABLE favorites (
    favorite_id SERIAL PRIMARY KEY,
    src_user_id integer NOT NULL REFERENCES users,
    message_id integer NOT NULL REFERENCES messages,
    created_on timestamp NOT NULL DEFAULT now()
);
CREATE INDEX src_user_id_idx ON favorites (src_user_id);
CREATE INDEX favorites_created_on_idx on favorites (created_on);

-- Queries to support:
-- 1) What messages are tagged x? (ordered by time, popularity)
-- 2) What did I tag x?
-- 3) Which of my things are tagged x?
-- 4) Which of user u's messages are tagged x?
-- 5) What are the popular tags lately?

CREATE TABLE tags (
  user_id integer NOT NULL REFERENCES users,
  message_id integer NOT NULL REFERENCES messages,
  created_on timestamp NOT NULL DEFAULT now(),
  tag text NOT NULL,
  PRIMARY KEY (user_id, message_id, tag)
);
CREATE INDEX tags_user_id_idx ON tags (user_id);
CREATE INDEX tags_message_id_idx ON tags (message_id);
CREATE INDEX tags_created_on_id_idx ON tags (created_on DESC);
CREATE INDEX tags_tag_idx ON tags (lower(tag));

INSERT INTO rooms (key, name, description, admin_only) 
       VALUES ('dumpfm', 'Room A', 'Hangout', false);
INSERT INTO rooms (key, name, description, admin_only)
       VALUES ('VIP', 'The VIP Room', 'Command Post', true);