-- OTP replay prevention: a successfully verified code is marked consumed.
ALTER TABLE "OtpCode" ADD COLUMN "consumedAt" TIMESTAMP(3);

-- Shared PostgreSQL-backed fixed-window rate limiter.
CREATE TABLE "RateLimit" (
    "id" TEXT NOT NULL,
    "bucket" TEXT NOT NULL,
    "count" INTEGER NOT NULL DEFAULT 0,
    "expiresAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "RateLimit_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "RateLimit_bucket_key" ON "RateLimit"("bucket");
CREATE INDEX "RateLimit_expiresAt_idx" ON "RateLimit"("expiresAt");

-- A gateway authority can identify exactly one payment. PostgreSQL permits multiple NULLs.
--
-- Deployment preflight: this migration deliberately fails if historical duplicate non-NULL
-- providerRef values exist. Resolve them manually before deploying rather than silently
-- changing payment records:
--   SELECT "providerRef", COUNT(*) FROM "Payment"
--   WHERE "providerRef" IS NOT NULL
--   GROUP BY "providerRef" HAVING COUNT(*) > 1;
CREATE UNIQUE INDEX "Payment_providerRef_key" ON "Payment"("providerRef");