ALTER TABLE "identity_users"
  ADD COLUMN "password_hash" TEXT,
  ADD COLUMN "password_changed_at" TIMESTAMPTZ(3);

CREATE TABLE "identity_auth_sessions" (
  "id" UUID NOT NULL,
  "user_id" UUID NOT NULL,
  "refresh_token_hash" CHAR(64) NOT NULL,
  "device_id" VARCHAR(120) NOT NULL,
  "user_agent" VARCHAR(500),
  "ip_address" VARCHAR(64),
  "expires_at" TIMESTAMPTZ(3) NOT NULL,
  "last_used_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "revoked_at" TIMESTAMPTZ(3),
  "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "identity_auth_sessions_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "identity_auth_sessions_user_id_fkey"
    FOREIGN KEY ("user_id") REFERENCES "identity_users"("id")
    ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX "identity_auth_sessions_user_id_revoked_at_idx"
  ON "identity_auth_sessions"("user_id", "revoked_at");
CREATE INDEX "identity_auth_sessions_expires_at_idx"
  ON "identity_auth_sessions"("expires_at");

CREATE TABLE "identity_password_reset_requests" (
  "id" UUID NOT NULL,
  "user_id" UUID NOT NULL,
  "otp_hash" CHAR(64) NOT NULL,
  "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_password_reset_requests_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "identity_password_reset_requests_user_id_fkey"
    FOREIGN KEY ("user_id") REFERENCES "identity_users"("id")
    ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX "identity_password_reset_requests_user_id_created_at_idx"
  ON "identity_password_reset_requests"("user_id", "created_at");
CREATE INDEX "identity_password_reset_requests_expires_at_consumed_at_idx"
  ON "identity_password_reset_requests"("expires_at", "consumed_at");