ALTER TABLE users
  ADD COLUMN IF NOT EXISTS avatar_url TEXT NOT NULL DEFAULT '';

ALTER TABLE users
  ADD COLUMN IF NOT EXISTS last_seen_at TIMESTAMPTZ;

ALTER TABLE users
  ADD COLUMN IF NOT EXISTS last_seen_ip TEXT NOT NULL DEFAULT '';

WITH deduplicated_users AS (
  SELECT
    id,
    username,
    ROW_NUMBER() OVER (PARTITION BY lower(username) ORDER BY created_at ASC, id ASC) AS row_number
  FROM users
)
UPDATE users
SET username = deduplicated_users.username || '-' || deduplicated_users.row_number
FROM deduplicated_users
WHERE users.id = deduplicated_users.id
  AND deduplicated_users.row_number > 1;

CREATE UNIQUE INDEX IF NOT EXISTS users_username_lower_unique_idx
  ON users (lower(username));