defmodule PlatformCore.Repo.Migrations.AddReconIndexCoreTransactions do
  use Ecto.Migration

  @moduledoc """
  Add composite index on (rrn, tid) to accelerate the reconciliation engine's
  bulk match query (`UPDATE switch_dump_records ... FROM core_transactions ...`).

  Also adds a partial index on unmatched/exception-hold transactions within the
  30-day window to speed up the 5.2 exception query.
  """

  def change do
    # Primary recon match key — used by ReconciliationEngine match_records/1
    # Composite (rrn, tid) covers all join predicates in the UPDATE...FROM query.
    # Not unique because the same RRN could theoretically exist across POS and QR.
    create index(:core_transactions, [:rrn, :tid],
      name: :core_transactions_rrn_tid_idx)

    # auth_number lookup for finer matching (optional, used as tie-breaker)
    create index(:core_transactions, [:rrn, :tid, :auth_number],
      name: :core_transactions_rrn_tid_auth_idx)

    # Index for the 5.2 exception query:
    # "unmatched transactions with no switch_settled_date in the last 30 days"
    # Note: MySQL does not support partial indexes (WHERE clause), so this is a
    # regular composite index on the same columns.
    execute(
      "CREATE INDEX core_transactions_recon_5_2_idx ON core_transactions (transaction_datetime, settlement_status)",
      "DROP INDEX core_transactions_recon_5_2_idx ON core_transactions"
    )
  end
end