neiam /oh-my-ex-oh-ircd

defmodule OhMyExOhIrcd.Repo.Migrations.CreateUserSightings do
  use Ecto.Migration

  # "Seen on this network" history for the operator console. A session
  # authenticated to an account is one row per account (user_id); an
  # anonymous session is one row per downcased nick (user_id NULL).
  # Unregistered rows expire after 90 days (OhMyExOhIrcd.Sightings.Pruner).
  #
  # on_delete: :delete_all — a deleted account takes its history (and the
  # IPs in it) with it. :nilify_all would turn the row into an anonymous
  # one, which can collide with an existing anonymous row for the same nick
  # under the partial unique index below.
  def change do
    create table(:user_sightings) do
      add :nick_key, :string, null: false
      add :nick, :string, null: false
      add :user_id, references(:users, on_delete: :delete_all)
      add :host, :string
      add :real_ip, :string
      add :first_seen_at, :utc_datetime, null: false
      add :last_seen_at, :utc_datetime, null: false
      add :last_quit_reason, :string

      timestamps(type: :utc_datetime)
    end

    create unique_index(:user_sightings, [:user_id])

    create unique_index(:user_sightings, [:nick_key],
             where: "user_id IS NULL",
             name: :user_sightings_anonymous_nick_key_index
           )

    create index(:user_sightings, [:last_seen_at])
  end
end