-- Clients

CREATE TABLE clients (
  id          BIGINT IDENTITY(1,1) PRIMARY KEY,
  org_id      BIGINT      NOT NULL REFERENCES orgs (id),
  external_id VARCHAR(255)     NOT NULL,
  name        NVARCHAR(255)     NOT NULL,
  created_at  DATETIMEOFFSET NOT NULL DEFAULT SYSDATETIMEOFFSET(),
  updated_at  DATETIMEOFFSET NOT NULL DEFAULT SYSDATETIMEOFFSET()
);

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             BIGINT IDENTITY(1,1) PRIMARY KEY,
  org_id         BIGINT      NOT NULL REFERENCES orgs (id),
  client_id      BIGINT      NOT NULL REFERENCES clients (id),
  token          VARCHAR(255)     NOT NULL,
  "external"       BIT     NOT NULL DEFAULT 0,
  external_token VARCHAR(255),
  created_at     DATETIMEOFFSET NOT NULL DEFAULT SYSDATETIMEOFFSET()
);

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;
