--- project-roles

CREATE TABLE project_roles (
  id          BIGINT PRIMARY KEY,
  project_id  BIGINT        NOT NULL REFERENCES projects (id),
  name        VARCHAR(64)   NOT NULL,
  description VARCHAR(255)  NOT NULL,
  admin       BOOLEAN       NOT NULL,
  deleted     BOOLEAN       NOT NULL,
  permissions VARCHAR(4000) NOT NULL,

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

CREATE SEQUENCE project_roles_id_seq;

CREATE UNIQUE INDEX project_roles_uniq
  ON project_roles (project_id, lower(name))
  WHERE deleted = FALSE;

CREATE INDEX project_roles_project_ix
  ON project_roles (project_id);

-- project-members

CREATE TABLE project_members (
  id          BIGINT PRIMARY KEY,
  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     BOOLEAN     NOT NULL,

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

CREATE SEQUENCE project_members_id_seq;

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