defmodule Tarakan.Repo.Migrations.IndexUnindexedForeignKeys do
  use Ecto.Migration

  # Postgres does not index a foreign key for you. Without one, deleting a
  # parent row sequentially scans the whole child table to enforce the
  # constraint - so removing a single account or repository degrades with the
  # size of every table that references it, and the cascades declared on these
  # columns make that certain rather than hypothetical.
  #
  # Created plainly because these tables are still small. Adding an index to a
  # large, live table wants `concurrently: true` with
  # `@disable_ddl_transaction true`, which is a different migration.
  @columns [
    {:bounties, :winner_account_id},
    {:bounty_claims, :review_task_id},
    {:canonical_finding_checks, :scan_finding_id},
    {:client_authorizations, :account_id},
    {:code_pattern_rules, :author_account_id},
    {:finding_comments, :removed_by_id},
    {:finding_comments, :repository_id},
    {:finding_regressions, :detected_by_scan_id},
    {:fix_propagations, :canonical_finding_id},
    {:fix_propagations, :review_task_id},
    {:fix_templates, :created_by_id},
    {:fix_templates, :source_repository_id},
    {:moderation_appeals, :appellant_id},
    {:moderation_appeals, :decided_by_id},
    {:moderation_cases, :assigned_to_id},
    {:moderation_cases, :resolved_by_id},
    {:registry_shouts, :removed_by_id},
    {:repository_memberships, :verified_by_account_id}
  ]

  def change do
    for {table, column} <- @columns do
      create_if_not_exists index(table, [column])
    end
  end
end