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(),
    last_login timestamp,
    created_ip cidr DEFAULT '0',
    last_ip cidr DEFAULT '0',
    is_admin boolean NOT NULL DEFAULT false,
    avatar text NOT NULL DEFAULT '',
    contact text NOT NULL DEFAULT '',
    bio text NOT NULL DEFAULT '',
    is_bot boolean NOT NULL DEFAULT false,
    profile_bg text
);

ALTER TABLE users ADD CONSTRAINT users_nick_constraint UNIQUE (LOWER(nick));

CREATE INDEX users_nick_lowercase_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,
    active bool NOT NULL DEFAULT true,
    history_size integer NOT NULL DEFAULT 25
);

-- ALTER TABLE rooms ADD COLUMN history_size integer NOT NULL DEFAULT 25;

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,
    is_text bool NOT NULL DEFAULT false
);

-- historical image dumps per user
CREATE INDEX messages_user_created_on_image_only_idx ON messages (user_id, created_on)
    WHERE is_image;

-- historical image dumps in a room. needs to handle non-image messages.
CREATE INDEX messages_room_id_created_on_idx ON messages (room_id, 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_message_id_idx ON tags (message_id);
CREATE INDEX tags_tag_lowercase_idx ON tags (lower(tag));
CREATE INDEX tags_user_id_created_on_idx ON tags(user_id, created_on);

CREATE TABLE mutes (
  mute_id SERIAL PRIMARY KEY,
  user_id integer NOT NULL REFERENCES users,
  admin_id integer NOT NULL REFERENCES users,
  set_on timestamp NOT NULL DEFAULT now(),
  duration interval NOT NULL,
  reason text NOT NULL,
  cancelled bool NOT NULL DEFAULT false,
  cancel_admin_id integer REFERENCES users,
  cancel_reason text
);

CREATE INDEX mutes_expires_idx ON mutes ((set_on + duration));

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

-- Initially, feed_images are added with no message_id,
-- and are used as a queue of pending messages to post.
-- When the image is posted, message_id is updated to link
-- to the resulting message.
CREATE TABLE feed_images (
  feed_url text NOT NULL,
  link text NOT NULL,
  title text NOT NULL,
  image_url text NOT NULL,
  room text NOT NULL,
  message_id integer REFERENCES messages,
  queued_on timestamp NOT NULL DEFAULT now(),
  PRIMARY KEY (room, image_url)
);

CREATE INDEX feed_images_url_room_idx ON feed_images (image_url, room);
CREATE INDEX feed_images_room_message_idx ON feed_images (room, message_id);
CREATE INDEX feed_images_room_message_id_idx ON feed_images (room, message_id)
    WHERE message_id IS NULL;

CREATE TABLE invalid_feed_images (
  image_url text NOT NULL,
  reason text NOT NULL,
  added_on timestamp NOT NULL DEFAULT now(),
  PRIMARY KEY (image_url)
);

CREATE INDEX invalid_feed_images_idx ON invalid_feed_images (image_url);

-- dont add this yet
CREATE TABLE avatars (
  avatar_id SERIAL PRIMARY KEY,
  user_id NOT NULL REFERENCES users,
  created_on timestamp NOT NULL DEFAULT now(),
  type text,
  url text,
  w integer,
  h integer,
  thumb text,
  tw integer,
  th integer
);

-- dont add this yet
CREATE TABLE images (
  image_id SERIAL PRIMARY KEY,
  
);