--- groups


CREATE TABLE groups (
  id            BIGINT PRIMARY KEY    IDENTITY (1, 1),
  project_id    BIGINT         NOT NULL REFERENCES projects (id),
  parent_id     BIGINT REFERENCES groups (id),
  title         NVARCHAR(64)   NOT NULL,
  title_lower                         AS lower(title),
  description   NVARCHAR(255)  NOT NULL,
  admin         BIT            NOT NULL,
  permissions   NVARCHAR(MAX)  NOT NULL,

  total_groups  INTEGER        NOT NULL,
  total_members INTEGER        NOT NULL,

  deleted       BIT            NOT NULL,
  created_at    DATETIMEOFFSET NOT NULL,
  updated_at    DATETIMEOFFSET NOT NULL,
  deleted_at    DATETIMEOFFSET
);

CREATE INDEX groups_project_id_ix
  ON groups (project_id);

CREATE INDEX groups_parent_id_ix
  ON groups (parent_id);

CREATE UNIQUE INDEX groups_project_id_title_null_parent_id_not_deleted_uniq
  ON groups (project_id, title_lower)
  WHERE parent_id IS NULL AND deleted = 0;

CREATE UNIQUE INDEX groups_parent_id_title_nonnull_parent_id_not_deleted_uniq
  ON groups (parent_id, title_lower)
  WHERE parent_id IS NOT NULL AND deleted = 0;

-- group-members

CREATE TABLE group_members (
  id          BIGINT PRIMARY KEY IDENTITY (1, 1),
  group_id    BIGINT         NOT NULL REFERENCES groups (id),
  user_id     BIGINT         NOT NULL REFERENCES users (id),
  deleted     BIT            NOT NULL,

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

CREATE UNIQUE INDEX group_members_group_id_user_id_uniq
  ON group_members (group_id, user_id);

CREATE INDEX group_members_user_id_ix
  ON group_members (user_id);
