-- Webhook-Subscriptions für externe Integrationen.
-- ====================================================================
-- Externe Systeme abonnieren Events ("employee.created",
-- "employee.updated", "lead.created", etc.) und erhalten POST-Requests
-- mit JSON-Payload. Signatur via HMAC-SHA256 mit Secret pro Endpoint.
--
-- Delivery-Log: jede Zustellung wird protokolliert (für Retry-
-- Diagnose und Audit).

create table if not exists public.webhook_subscriptions (
  id          uuid primary key default gen_random_uuid(),
  name        text not null,
  url         text not null,
  events      text[] not null default array[]::text[],
  secret      text not null,           -- für HMAC-Signatur
  active      boolean not null default true,
  created_at  timestamptz not null default now(),
  updated_at  timestamptz not null default now()
);

create index if not exists webhook_subscriptions_active_idx
  on public.webhook_subscriptions (active) where active = true;

drop trigger if exists trg_webhook_subscriptions_updated_at
  on public.webhook_subscriptions;
create trigger trg_webhook_subscriptions_updated_at
  before update on public.webhook_subscriptions
  for each row execute function public.set_updated_at();

create table if not exists public.webhook_deliveries (
  id              uuid primary key default gen_random_uuid(),
  subscription_id uuid references public.webhook_subscriptions(id)
                    on delete cascade,
  event           text not null,
  payload         jsonb not null,
  status_code     integer,
  response_body   text,
  error           text,
  delivered_at    timestamptz not null default now()
);

create index if not exists webhook_deliveries_subscription_idx
  on public.webhook_deliveries (subscription_id, delivered_at desc);

alter table public.webhook_subscriptions enable row level security;
alter table public.webhook_deliveries enable row level security;
-- Service-Role-only.