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

  @moduledoc """
  Phase 2.1 + 2.3: Add auth_number and refund_amount to reconciliation_exceptions.

  auth_number: Used as tertiary match key during reconciliation (RRN + TID + auth_number).
  Reduces false-positive 5.1 exceptions when RRN alone collides across schemes.

  refund_amount: For partial refund support (Phase 2.3). When a dump record is a
  partial refund, refund_amount < transaction_amount, and the exception is raised
  with a description indicating the partial refund gap rather than a full mismatch.

  amount_on_hold: Aggregated transaction amount still unresolved for this exception.
  Used in SLA monitoring and Finance alert emails.
  """

  def change do
    maybe_add_recon_exception_columns()

    unless index_exists?(:reconciliation_exceptions, "recon_exceptions_auth_number_index") do
      create index(:reconciliation_exceptions, [:auth_number],
        name: :recon_exceptions_auth_number_index
      )
    end

    # MySQL does not support partial indexes with WHERE clauses.
    unless index_exists?(:reconciliation_exceptions, "recon_exceptions_sla_due_index") do
      create index(:reconciliation_exceptions, [:sla_due_at],
        name: :recon_exceptions_sla_due_index
      )
    end
  end

  defp maybe_add_recon_exception_columns do
    maybe_add_column(:reconciliation_exceptions, :auth_number, fn ->
      alter table(:reconciliation_exceptions) do
        add :auth_number, :string, size: 12, null: true,
          comment: "Auth code from the dump record — tertiary match key after RRN + TID"
      end
    end)

    maybe_add_column(:reconciliation_exceptions, :refund_amount, fn ->
      alter table(:reconciliation_exceptions) do
        add :refund_amount, :decimal, precision: 18, scale: 4, null: true,
          comment: "Refund amount from dump (used for partial refund detection)"
      end
    end)

    maybe_add_column(:reconciliation_exceptions, :amount_on_hold, fn ->
      alter table(:reconciliation_exceptions) do
        add :amount_on_hold, :decimal, precision: 18, scale: 4, null: true,
          comment: "Amount blocked from payout due to this exception (may differ from txn amount)"
      end
    end)

    maybe_add_column(:reconciliation_exceptions, :sla_due_at, fn ->
      alter table(:reconciliation_exceptions) do
        add :sla_due_at, :naive_datetime, null: true,
          comment: "SLA deadline for this exception — set at creation, alerts triggered if overdue"
      end
    end)
  end

  defp maybe_add_column(table, column, add_fun) do
    unless column_exists?(table, column), do: add_fun.()
  end

  defp column_exists?(table, column) do
    query = """
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = ?
      AND COLUMN_NAME = ?
    LIMIT 1
    """

    %{num_rows: num_rows} =
      Ecto.Adapters.SQL.query!(repo(), query, [Atom.to_string(table), Atom.to_string(column)])

    num_rows > 0
  end

  defp index_exists?(table, index_name) do
    query = """
    SELECT 1
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = ?
      AND INDEX_NAME = ?
    LIMIT 1
    """

    %{num_rows: num_rows} =
      Ecto.Adapters.SQL.query!(repo(), query, [Atom.to_string(table), index_name])

    num_rows > 0
  end
end