CREATE TABLE "identity_registration_otp_requests" (
    "id" UUID NOT NULL,
    "phone" VARCHAR(20) NOT NULL,
    "otp_hash" CHAR(64) NOT NULL,
    "sms_provider" VARCHAR(80),
    "sms_tracking_id" VARCHAR(160),
    "sms_status" VARCHAR(40),
    "attempts" INTEGER NOT NULL DEFAULT 0,
    "expires_at" TIMESTAMPTZ(3) NOT NULL,
    "verified_at" TIMESTAMPTZ(3),
    "consumed_at" TIMESTAMPTZ(3),
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

CREATE INDEX "identity_registration_otp_requests_phone_created_at_idx"
ON "identity_registration_otp_requests"("phone", "created_at");

CREATE INDEX "identity_registration_otp_requests_expires_at_consumed_at_idx"
ON "identity_registration_otp_requests"("expires_at", "consumed_at");

CREATE INDEX "identity_registration_otp_requests_sms_provider_sms_tracking_idx"
ON "identity_registration_otp_requests"("sms_provider", "sms_tracking_id");