defmodule DaProductApp.Repo.Migrations.AddMissingFieldsCoreTransactions do
  use Ecto.Migration

  @moduledoc """
  Adds cardholder and reversal tracking fields to core_transactions.

  These fields were identified as missing from the original schema in the
  gap analysis against the Settlement_reports_completion.txt requirements.

  ## New Fields

  - card_holder_name  — populated from POS source where the terminal captures
                        the name from the card (EMV chip / NFC swipe).
  - customer_mobile   — customer mobile number if captured at POS/QR checkout.
  - customer_country  — issuer country (ISO 3166-1 alpha-3) resolved from
                        BIN table lookup.
  - reversal_status   — true if the original SALE was reversed. Distinct from
                        `transaction_type = 'REVERSAL'` (that field identifies
                        the reversal transaction itself; this field marks the
                        *original* SALE as having been reversed).
  - reversal_date     — date the reversal was processed by the switch.
  """

  def change do
    alter table(:core_transactions) do
      # Cardholder identity — sourced from POS terminal at transaction time
      add :card_holder_name, :string, size: 100,
          comment: "Name embossed on card — captured by EMV/NFC at terminal"
      add :customer_mobile, :string, size: 20,
          comment: "Customer mobile number if provided at QR or contactless checkout"
      add :customer_country, :string, size: 3,
          comment: "ISO 3166-1 alpha-3 issuer country — resolved from BIN table"

      # Reversal tracking (on the original SALE transaction row)
      add :reversal_status, :boolean, default: false, null: false,
          comment: "true if this SALE transaction was subsequently reversed by the switch"
      add :reversal_date, :date,
          comment: "Date the reversal was processed — set by reconciliation engine"
    end

    # Index for quickly finding reversed transactions in exception reports
    create index(:core_transactions, [:reversal_status])
  end
end