-- Clients

CREATE TABLE clients (
  id          BIGSERIAL PRIMARY KEY,
  org_id      BIGINT      NOT NULL REFERENCES orgs (id),
  external_id VARCHAR     NOT NULL,
  name        VARCHAR     NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX clients_org_id_ix
  ON clients (org_id);
CREATE UNIQUE INDEX clients_org_id_external_id_uniq
  ON clients (org_id, external_id);

-- Sessions

CREATE TABLE client_sessions (
  id             BIGSERIAL PRIMARY KEY,
  org_id         BIGINT      NOT NULL REFERENCES orgs (id),
  client_id      BIGINT      NOT NULL REFERENCES clients (id),
  token          VARCHAR     NOT NULL,
  external       BOOLEAN     NOT NULL DEFAULT FALSE,
  external_token VARCHAR,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX client_sessions_client_id_ix
  ON client_sessions (client_id);
CREATE UNIQUE INDEX client_sessions_token_uniq
  ON client_sessions (token);
CREATE UNIQUE INDEX client_sessions_org_id_external_id_uniq
  ON client_sessions (org_id, external_token)
  WHERE external_token IS NOT NULL;
