-- 056 — Transversais: callback de saída PERSISTIDO (decisão 47).
--
-- Retry antigo era 3x em memória (restart = evento perdido). Fila PG com
-- backoff persistido; URL por cliente (SSRF-safe) + HMAC opcional.
-- Opt-out sync é OBRIGATÓRIO: o descadastro chega ao sistema do cliente.

CREATE TABLE IF NOT EXISTS outbound_webhook_configs (
  owner_user_id varchar PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  url text NOT NULL,
  secret_token varchar(100),
  ativo boolean NOT NULL DEFAULT true,
  event_types jsonb,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE TABLE IF NOT EXISTS outbound_webhook_queue (
  id bigserial PRIMARY KEY,
  owner_user_id varchar NOT NULL,
  event_type varchar(30) NOT NULL,
  payload jsonb NOT NULL,
  status varchar(12) NOT NULL DEFAULT 'pending',
  attempt_count integer NOT NULL DEFAULT 0,
  next_attempt_at timestamptz NOT NULL DEFAULT now(),
  last_error text,
  created_at timestamptz DEFAULT now(),
  processed_at timestamptz
);
CREATE INDEX IF NOT EXISTS idx_outbound_webhook_due ON outbound_webhook_queue (status, next_attempt_at);