-- Groups

CREATE TABLE user_groups (
  id            BIGINT IDENTITY(1,1) PRIMARY KEY,
  org_id        BIGINT      NOT NULL REFERENCES orgs (id),
  name          NVARCHAR(255)     NOT NULL,
  name_lower AS LOWER(name),
  deleted       BIT     NOT NULL DEFAULT 0,
  total_members INT     NOT NULL DEFAULT 0,
  created_at    DATETIMEOFFSET NOT NULL DEFAULT SYSDATETIMEOFFSET()
);

CREATE UNIQUE INDEX user_groups_org_id_name_nondeleted_uniq
  ON user_groups (org_id, name_lower)
  WHERE deleted = 0;

-- Groups members

CREATE TABLE user_groups_members (
  id           BIGINT IDENTITY(1,1) PRIMARY KEY,
  group_id     BIGINT      NOT NULL REFERENCES user_groups (id),
  user_id      BIGINT      NOT NULL REFERENCES users (id),
  deleted      BIT     NOT NULL DEFAULT 0,
  role_deleted BIT     NOT NULL DEFAULT 0,
  created_at   DATETIMEOFFSET NOT NULL DEFAULT SYSDATETIMEOFFSET()
);

CREATE INDEX user_groups_members_group_id_ix
  ON user_groups_members (group_id);
CREATE UNIQUE INDEX user_groups_members_group_id_user_id_uniq
  ON user_groups_members (group_id, user_id);
