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

  def up do
    alter table(:canonical_findings) do
      add :fixed_at, :utc_datetime_usec
      add :fixed_commit_sha, :string
      add :regressions_count, :integer, null: false, default: 0
      add :last_regressed_at, :utc_datetime_usec
    end

    # Existing fixed rows predate the stamp; `updated_at` is the closest
    # honest approximation of when they settled.
    execute """
    UPDATE canonical_findings
    SET fixed_at = updated_at,
        fixed_commit_sha = last_seen_commit_sha
    WHERE status = 'fixed'
    """

    # Time-to-fix reads verified/fixed pairs per repository.
    create index(:canonical_findings, [:repository_id, :fixed_at],
             where: "fixed_at IS NOT NULL",
             name: :canonical_findings_repository_fixed_at_index
           )

    # The suppression corpus reads disputed rows by pattern.
    create index(:canonical_findings, [:pattern_key, :status],
             where: "pattern_key IS NOT NULL",
             name: :canonical_findings_pattern_status_index
           )
  end

  def down do
    drop index(:canonical_findings, [:repository_id, :fixed_at],
           name: :canonical_findings_repository_fixed_at_index
         )

    drop index(:canonical_findings, [:pattern_key, :status],
           name: :canonical_findings_pattern_status_index
         )

    alter table(:canonical_findings) do
      remove :fixed_at
      remove :fixed_commit_sha
      remove :regressions_count
      remove :last_regressed_at
    end
  end
end
