-- Webhook registry and delivery queue (standalone)
-- Compose after: durable-work-postgres/schema.sql if delivery workers share the workers table.

CREATE TABLE webhook_endpoints (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  tenant_id text NOT NULL,
  url text NOT NULL,
  status text NOT NULL DEFAULT 'active',
  secret_ref text NOT NULL,
  max_attempts int NOT NULL DEFAULT 12,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE webhook_subscriptions (
  endpoint_id uuid NOT NULL REFERENCES webhook_endpoints(id),
  event_type text NOT NULL,
  PRIMARY KEY (endpoint_id, event_type)
);

CREATE TABLE webhook_deliveries (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  endpoint_id uuid NOT NULL REFERENCES webhook_endpoints(id),
  tenant_id text NOT NULL,
  event_id text NOT NULL,
  event_type text NOT NULL,
  payload jsonb NOT NULL,
  status text NOT NULL DEFAULT 'pending',
  attempts int NOT NULL DEFAULT 0,
  max_attempts int NOT NULL DEFAULT 12,
  claimed_by text,
  lease_expires_at timestamptz,
  available_at timestamptz NOT NULL DEFAULT now(),
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (endpoint_id, event_id)
);

CREATE INDEX idx_webhook_deliveries_claim
  ON webhook_deliveries (available_at, created_at, id)
  WHERE status = 'pending';
