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

  @moduledoc """
  Individual transaction records parsed from the daily YSP Switch Settled Dump
  (Base II / ISO 8583 file). One row per transaction in the dump file.

  The reconciliation engine matches these records against core_transactions
  using: RRN + TID + Amount + Date + Auth Number.

  On match:
  - core_transactions.switch_settled_date is set
  - matched_at and core_transaction_id are populated here
  - interchange_fee from this file is written to core_transactions.interchange_fee

  On no match: a reconciliation_exception of type '5.1' is created.

  Note: Column definitions are based on expected YSP Base II file format.
  Update when actual file spec is received from YSP.
  """

  def change do
    create table(:switch_dump_records) do
      add :dump_file_id, :bigint, null: false,
          comment: "FK dump_files.id — identifies which file this row came from"
      add :dump_date, :date, null: false,
          comment: "The settlement date of the dump file"

      # Reconciliation key fields (must match core_transactions)
      add :rrn, :string, size: 12, null: false,
          comment: "Retrieval Reference Number"
      add :tid, :string, size: 20
      add :mid, :string, size: 25
      add :auth_number, :string, size: 10
      add :stan, :string, size: 10
      add :transaction_amount, :decimal, precision: 12, scale: 2
      add :transaction_date, :date
      add :transaction_time, :string, size: 6
      add :transaction_type, :string, size: 20,
          comment: "SALE | REFUND | REVERSAL | VOID"

      # Card / scheme info from dump
      add :card_number_masked, :string, size: 20
      add :scheme_name, :string, size: 30
      add :currency_code, :string, size: 3

      # Financial detail from switch
      add :interchange_fee, :decimal, precision: 12, scale: 2,
          comment: "Actual interchange from YSP — written to core_transactions on match"
      add :interchange_currency, :string, size: 3
      add :response_code, :string, size: 2

      # Raw dump row for reference
      add :raw_data, :text,
          comment: "Original parsed row stored for audit / dispute resolution"

      # Matching outcome
      add :matched_at, :naive_datetime,
          comment: "NULL = unmatched; set when recon engine finds RRN in core_transactions"
      add :core_transaction_id, :bigint,
          comment: "FK core_transactions.id — set on successful match"
      add :match_status, :string, size: 20, null: false, default: "unmatched",
          comment: "unmatched | matched | duplicate | exception"

      add :inserted_at, :naive_datetime, null: false
      add :updated_at, :naive_datetime, null: false
    end

    create index(:switch_dump_records, [:rrn])
    create index(:switch_dump_records, [:dump_date])
    create index(:switch_dump_records, [:dump_file_id])
    create index(:switch_dump_records, [:match_status])
    create index(:switch_dump_records, [:core_transaction_id])
    create index(:switch_dump_records, [:tid, :rrn, :transaction_date],
      name: :switch_dump_recon_key)
  end
end