--- projects


CREATE TABLE projects (
  id          BIGINT PRIMARY KEY,
  name        VARCHAR(64)  NOT NULL,
  description VARCHAR(255) NOT NULL,
  state       VARCHAR(32)  NOT NULL,

  created_at  TIMESTAMPTZ  NOT NULL,
  updated_at  TIMESTAMPTZ  NOT NULL,
  archived_at TIMESTAMPTZ,
  deleted_at  TIMESTAMPTZ,
  restored_at TIMESTAMPTZ
);

CREATE SEQUENCE projects_id_seq;

CREATE UNIQUE INDEX projects_name_uniq
  ON projects (lower(name))
  WHERE state = 'active';

-- Permissions
ALTER TABLE user_groups
  ADD perms_projects VARCHAR(255) NOT NULL DEFAULT '';

UPDATE user_groups
SET perms_projects = 'write'
WHERE admin = TRUE;

-- Channels
ALTER TABLE channels
  ADD project_id BIGINT REFERENCES projects (id);

CREATE INDEX channels_project_id_ix
  ON channels (project_id);

UPDATE channels
SET "type" = 'iqchat'
WHERE "type" = 'general';

-- Chats

ALTER TABLE chats
  ADD project_id BIGINT REFERENCES projects (id);
