--- projects


CREATE TABLE projects (
  id          BIGINT IDENTITY (1, 1) PRIMARY KEY,
  name        NVARCHAR(64)   NOT NULL,
  name_lower         AS lower(name),
  description NVARCHAR(255)  NOT NULL,
  state       VARCHAR(32)    NOT NULL,

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

CREATE INDEX projects_name_uniq
  ON projects (name_lower)
  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 = 1;

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