neiam /gitgud

defmodule GitGud.Repo.Migrations.AddProfileFields do
  @moduledoc """
  Adds an explicit `handle` to `users` (parallel to `organizations.handle`)
  plus the shared profile fields — display name, bio, url, location, etc.
  — to both tables.

  `handle` for users is backfilled from `lower(split_part(email, '@', 1))`
  with `_N` suffixes appended on collisions, so existing rows get a
  stable, URL-safe namespace token without anyone having to claim one.
  """

  use Ecto.Migration

  @profile_fields [
    {:display_name, :string},
    {:bio, :text},
    {:url, :string},
    {:location, :string},
    {:company, :string},
    {:public_email, :string},
    {:timezone, :string},
    {:socials, :map}
  ]

  def up do
    # users.handle
    alter table(:users) do
      add :handle, :citext
    end

    execute("""
    WITH ranked AS (
      SELECT
        id,
        lower(split_part(email, '@', 1)) AS base,
        row_number() OVER (
          PARTITION BY lower(split_part(email, '@', 1))
          ORDER BY id
        ) - 1 AS rn
      FROM users
    )
    UPDATE users u
    SET handle = CASE
      WHEN r.rn = 0 THEN r.base
      ELSE r.base || '_' || r.rn::text
    END
    FROM ranked r
    WHERE u.id = r.id;
    """)

    execute("ALTER TABLE users ALTER COLUMN handle SET NOT NULL;")
    create unique_index(:users, [:handle])

    # users profile fields
    alter table(:users) do
      for {name, type} <- @profile_fields do
        add name, type
      end
    end

    # organizations profile fields
    alter table(:organizations) do
      for {name, type} <- @profile_fields do
        add name, type
      end
    end
  end

  def down do
    alter table(:organizations) do
      for {name, _type} <- @profile_fields do
        remove name
      end
    end

    alter table(:users) do
      for {name, _type} <- @profile_fields do
        remove name
      end

      remove :handle
    end
  end
end