-- TASK-0130 — Webhooks production: encrypted raw signing secret + DLQ status
-- + inspector index.
--
--   1. `webhooks.secretEncrypted BYTEA?` — the RAW signing secret, AES-256-GCM
--      (MASTER_ENCRYPTION_KEY), base64(iv:tag:ciphertext). Every webhook created
--      from now on stores its raw secret here; `secretHash` is recomputed as
--      SHA-256(raw) and stays for the incoming-verification/legacy path.
--      (C-13.8a: the Phase-11 rows stored the CIPHERTEXT in `secretHash`; the
--      backfill below moves those into the proper column, and the app's
--      resolveSigningSecret() falls back to decrypting `secretHash` directly
--      for any row the SQL predicate misses.)
--   2. `WebhookDeliveryStatus` gains DEAD_LETTER — the terminal status after the
--      final (8th) attempt fails. Kept in webhook_deliveries for the delivery
--      inspector; a resubmit re-queues it with a fresh attempts counter.
--   3. `webhook_deliveries [webhookId, createdAt desc]` — inspector pagination.
--
-- Backfill (best-effort, no decryption in SQL): copy a `secretHash` that is NOT
-- a bare 64-hex-char SHA-256 into `secretEncrypted`. A bare hash cannot be
-- recovered (its deliveries sign with the hash-derived key — documented legacy
-- behavior); a base64 ciphertext can, and this move makes the common case hit
-- the clean `secretEncrypted` path in the app.

ALTER TYPE "WebhookDeliveryStatus" ADD VALUE IF NOT EXISTS 'DEAD_LETTER';

ALTER TABLE "webhooks" ADD COLUMN "secretEncrypted" BYTEA;

UPDATE "webhooks"
SET "secretEncrypted" = convert_to("secretHash", 'UTF8')
WHERE "secretEncrypted" IS NULL
  AND "secretHash" IS NOT NULL
  AND "secretHash" !~ '^[0-9a-f]{64}$';

CREATE INDEX "webhook_deliveries_webhookId_createdAt_idx"
  ON "webhook_deliveries"("webhookId", "createdAt");