defmodule WhoNeedHelp.Repo.Migrations.AddTrustAndModerationControls do
  use Ecto.Migration

  def change do
    alter table(:users) do
      add :moderation_status, :string, null: false, default: "active"
      add :moderation_note, :text
    end

    create index(:users, [:moderation_status, :inserted_at])

    alter table(:category_proposals) do
      add :reviewed_by_id, references(:users, type: :binary_id, on_delete: :nilify_all)
      add :reviewed_at, :utc_datetime
      add :moderation_note, :text
    end

    create index(:category_proposals, [:reviewed_by_id])

    alter table(:help_requests) do
      add :hidden_at, :utc_datetime
      add :hidden_reason, :text
    end

    create index(:help_requests, [:status, :hidden_at, :expires_at])

    alter table(:help_assignments) do
      add :helper_movement_observed_at, :utc_datetime
    end

    alter table(:tracking_sessions) do
      add :distance_meters, :float, null: false, default: 0.0
      add :sample_count, :integer, null: false, default: 0
      add :movement_observed_at, :utc_datetime
    end

    create constraint(:tracking_sessions, :tracking_distance_non_negative,
             check: "distance_meters >= 0"
           )

    create constraint(:tracking_sessions, :tracking_sample_count_non_negative,
             check: "sample_count >= 0"
           )

    alter table(:reports) do
      add :resolution_note, :text
      add :reviewed_at, :utc_datetime
      add :reviewed_by_id, references(:users, type: :binary_id, on_delete: :nilify_all)
    end

    create index(:reports, [:reviewed_by_id])

    create constraint(:reports, :report_exactly_one_target,
             check: "num_nonnulls(request_id, assignment_id, message_id) = 1"
           )

    create table(:rate_limit_buckets, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :action, :string, null: false
      add :scope_hash, :binary, null: false
      add :window_started_at, :utc_datetime, null: false
      add :count, :integer, null: false, default: 0
      add :expires_at, :utc_datetime, null: false
      timestamps(type: :utc_datetime)
    end

    create unique_index(:rate_limit_buckets, [:action, :scope_hash, :window_started_at])
    create index(:rate_limit_buckets, [:expires_at])
    create constraint(:rate_limit_buckets, :rate_limit_count_non_negative, check: "count >= 0")

    create table(:abuse_signals, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :kind, :string, null: false
      add :status, :string, null: false, default: "open"
      add :metadata, :map, null: false, default: %{}
      add :reviewed_at, :utc_datetime
      add :review_note, :text
      add :subject_id, references(:users, type: :binary_id, on_delete: :delete_all), null: false

      add :assignment_id,
          references(:help_assignments, type: :binary_id, on_delete: :delete_all)

      add :reviewed_by_id, references(:users, type: :binary_id, on_delete: :nilify_all)
      timestamps(type: :utc_datetime)
    end

    create index(:abuse_signals, [:status, :inserted_at])
    create index(:abuse_signals, [:subject_id, :kind])
    create index(:abuse_signals, [:assignment_id])
    create index(:abuse_signals, [:reviewed_by_id])

    create unique_index(:abuse_signals, [:kind, :subject_id, :assignment_id],
             where: "assignment_id IS NOT NULL",
             name: :abuse_signals_one_kind_per_assignment
           )
  end
end
