neiam /gitgud

defmodule GitGud.Repo.Migrations.ScopeReportsToOwner do
  use Ecto.Migration

  # Reports know only `(target_type, target_id)`, so asking "what has
  # been reported in my repo?" meant a different join per target type on
  # every page load. Denormalise the owning repo and org onto the row
  # instead, filled in at report time.
  #
  # Both stay nullable: `actor` and `inbox_delivery` reports are about
  # federation rather than a repo, and belong to instance admins alone.
  def up do
    alter table(:reports) do
      add :repository_id, references(:repositories, on_delete: :nilify_all)
      add :organization_id, references(:organizations, on_delete: :nilify_all)
    end

    create index(:reports, [:repository_id, :state])
    create index(:reports, [:organization_id, :state])

    flush()

    execute """
    UPDATE reports r SET repository_id = i.repository_id
    FROM issues i
    WHERE r.target_type = 'issue' AND r.target_id = i.id
    """

    execute """
    UPDATE reports r SET repository_id = i.repository_id
    FROM issue_comments c JOIN issues i ON i.id = c.issue_id
    WHERE r.target_type = 'issue_comment' AND r.target_id = c.id
    """

    execute """
    UPDATE reports r SET repository_id = p.repository_id
    FROM pull_requests p
    WHERE r.target_type = 'pull_request' AND r.target_id = p.id
    """

    execute """
    UPDATE reports r SET repository_id = p.repository_id
    FROM pr_comments c JOIN pull_requests p ON p.id = c.pull_request_id
    WHERE r.target_type = 'pr_comment' AND r.target_id = c.id
    """

    # The org follows from the repo for anything org-owned.
    execute """
    UPDATE reports r SET organization_id = repo.organization_id
    FROM repositories repo
    WHERE r.repository_id = repo.id AND repo.organization_id IS NOT NULL
    """
  end

  def down do
    drop index(:reports, [:repository_id, :state])
    drop index(:reports, [:organization_id, :state])

    alter table(:reports) do
      remove :repository_id
      remove :organization_id
    end
  end
end