CREATE TYPE "IdentityVerificationLevel" AS ENUM ('LEVEL_1', 'LEVEL_2', 'LEVEL_3');
CREATE TYPE "IdentityVerificationKind" AS ENUM ('NATIONAL_IDENTITY', 'BIOMETRIC');
CREATE TYPE "IdentityVerificationStatus" AS ENUM ('PENDING', 'APPROVED', 'REJECTED', 'FAILED');

ALTER TABLE "identity_users"
  ADD COLUMN "first_name" VARCHAR(80),
  ADD COLUMN "last_name" VARCHAR(80),
  ADD COLUMN "phone_country_code" VARCHAR(5),
  ADD COLUMN "phone_number" VARCHAR(15),
  ADD COLUMN "national_code_encrypted" TEXT,
  ADD COLUMN "national_code_hash" CHAR(64),
  ADD COLUMN "national_code_last_four" CHAR(4),
  ADD COLUMN "verification_level" "IdentityVerificationLevel" NOT NULL DEFAULT 'LEVEL_1',
  ADD COLUMN "level_one_verified_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ADD COLUMN "level_two_verified_at" TIMESTAMPTZ(3),
  ADD COLUMN "level_three_verified_at" TIMESTAMPTZ(3);

UPDATE "identity_users"
SET
  "first_name" = COALESCE(NULLIF(SPLIT_PART("display_name", ' ', 1), ''), 'کاربر'),
  "last_name" = COALESCE(NULLIF(SUBSTRING("display_name" FROM POSITION(' ' IN "display_name") + 1), ''), 'کاپیلا'),
  "phone_country_code" = CASE WHEN "phone" LIKE '+98%' THEN '+98' ELSE LEFT("phone", 4) END,
  "phone_number" = CASE WHEN "phone" LIKE '+98%' THEN SUBSTRING("phone" FROM 4) ELSE "phone" END;

ALTER TABLE "identity_users"
  ALTER COLUMN "first_name" SET NOT NULL,
  ALTER COLUMN "last_name" SET NOT NULL,
  ALTER COLUMN "phone_country_code" SET NOT NULL,
  ALTER COLUMN "phone_number" SET NOT NULL,
  ALTER COLUMN "display_name" TYPE VARCHAR(161);

CREATE UNIQUE INDEX "identity_users_national_code_hash_key"
  ON "identity_users"("national_code_hash");

CREATE TABLE "identity_verification_requests" (
  "id" UUID NOT NULL,
  "user_id" UUID NOT NULL,
  "kind" "IdentityVerificationKind" NOT NULL,
  "status" "IdentityVerificationStatus" NOT NULL DEFAULT 'PENDING',
  "provider" VARCHAR(80) NOT NULL,
  "idempotency_key" VARCHAR(160) NOT NULL,
  "tracking_id" VARCHAR(160),
  "media_id" UUID,
  "consent_accepted_at" TIMESTAMPTZ(3),
  "failure_code" VARCHAR(120),
  "resolved_at" TIMESTAMPTZ(3),
  "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMPTZ(3) NOT NULL,
  CONSTRAINT "identity_verification_requests_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "identity_verification_requests_user_id_fkey"
    FOREIGN KEY ("user_id") REFERENCES "identity_users"("id")
    ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE UNIQUE INDEX "identity_verification_requests_user_id_kind_idempotency_key_key"
  ON "identity_verification_requests"("user_id", "kind", "idempotency_key");
CREATE INDEX "identity_verification_requests_user_id_kind_created_at_idx"
  ON "identity_verification_requests"("user_id", "kind", "created_at");
CREATE INDEX "identity_verification_requests_provider_tracking_id_idx"
  ON "identity_verification_requests"("provider", "tracking_id");
CREATE INDEX "identity_verification_requests_status_created_at_idx"
  ON "identity_verification_requests"("status", "created_at");