ALTER TABLE "user_preferences"
  ADD COLUMN IF NOT EXISTS "phone_country_dial" VARCHAR(8),
  ADD COLUMN IF NOT EXISTS "phone_local" VARCHAR(24);

CREATE INDEX IF NOT EXISTS "user_preferences_phone_country_dial_phone_local_idx"
  ON "user_preferences" ("phone_country_dial", "phone_local");

-- 回填已有手机号的区号/本地号（username 为纯数字国际号码时）
UPDATE "user_preferences" up
SET
  "phone_country_dial" = matched.dial,
  "phone_local" = SUBSTRING(u.username FROM (LENGTH(matched.dial) + 1))
FROM "users" u
JOIN LATERAL (
  SELECT dial FROM (
    VALUES ('880'), ('886'), ('60'), ('65'), ('66'), ('84'), ('91'), ('61')
  ) AS d(dial)
  WHERE u.username LIKE d.dial || '%'
  ORDER BY LENGTH(d.dial) DESC
  LIMIT 1
) matched ON TRUE
WHERE up.user_id = u.id
  AND up.phone IS NOT NULL
  AND up.phone_country_dial IS NULL
  AND u.username ~ '^[0-9]+$'
  AND LENGTH(u.username) >= 10;
