--- project-roles

CREATE TABLE project_roles (
  id          BIGINT PRIMARY KEY  IDENTITY (1, 1),
  project_id  BIGINT         NOT NULL REFERENCES projects (id),
  name        NVARCHAR(64)   NOT NULL,
  name_lower                      AS LOWER(name),
  description NVARCHAR(255)  NOT NULL,
  admin       BIT            NOT NULL,
  deleted     BIT            NOT NULL,
  permissions VARCHAR(4000)  NOT NULL,

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

CREATE UNIQUE INDEX project_roles_uniq
  ON project_roles (project_id, name_lower)
  WHERE deleted = 0;

CREATE INDEX project_roles_project_ix
  ON project_roles (project_id);

-- project-members

CREATE TABLE project_members (
  id          BIGINT PRIMARY KEY IDENTITY (1, 1),
  project_id  BIGINT         NOT NULL REFERENCES projects (id),
  user_id     BIGINT         NOT NULL REFERENCES users (id),
  role_id     BIGINT         NOT NULL REFERENCES project_roles (id),
  deleted     BIT            NOT NULL,

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

CREATE UNIQUE INDEX project_members_uniq
  ON project_members (project_id, user_id);
CREATE INDEX project_members_user_ix
  ON project_members (user_id);
CREATE INDEX project_members_role_ix
  ON project_members (role_id);
