BEGIN;
SET ROLE capability_role;
CREATE TABLE IF NOT EXISTS capability.notification_bindings (
  binding_id TEXT NOT NULL,
  capability_id TEXT NOT NULL,
  owner_principal_id TEXT NOT NULL,
  revision BIGINT NOT NULL,
  state TEXT NOT NULL,
  https_origin TEXT NOT NULL,
  delivery_path TEXT NOT NULL,
  receipt_path TEXT NOT NULL,
  protocol TEXT NOT NULL,
  secret_ref TEXT NOT NULL,
  verified_at_unix_millis BIGINT NOT NULL,
  health TEXT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (binding_id, revision),
  CHECK (revision > 0),
  CHECK (verified_at_unix_millis > 0),
  CHECK (state IN ('ACTIVE','RETIRED')),
  CHECK (health IN ('AVAILABLE','DEGRADED','UNAVAILABLE')),
  CHECK (protocol = 'IDEMPOTENT_RECEIPT_V1'),
  CHECK (https_origin LIKE 'https://%'),
  CHECK (delivery_path LIKE '/%'),
  CHECK (receipt_path LIKE '/%')
);
CREATE TABLE IF NOT EXISTS capability.notification_binding_heads (
  capability_id TEXT NOT NULL,
  owner_principal_id TEXT NOT NULL,
  binding_id TEXT NOT NULL,
  revision BIGINT NOT NULL,
  PRIMARY KEY (capability_id, owner_principal_id),
  CHECK (revision > 0)
);
RESET ROLE;
COMMIT;