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

  @moduledoc """
  Individual transaction records parsed from daily QR Scheme Settled Dumps
  (received from respective QR schemes — AANI, Alipay, STC Pay etc.).

  Separate from switch_dump_records to:
    1. Allow independent file upload and processing per QR scheme
    2. Clearly distinguish SWITCH (card) vs QR scheme reconciliation source
    3. Support different column formats per QR scheme in future

  Reconciliation logic is identical to switch_dump_records:
    RRN + TID + Amount + Date → match against core_transactions

  Note: Column definitions are based on expected QR scheme dump format.
  Update when actual spec is received.
  """

  def change do
    create table(:qr_scheme_dump_records) do
      add :dump_file_id, :bigint, null: false,
          comment: "FK dump_files.id"
      add :dump_date, :date, null: false
      add :qr_scheme, :string, size: 30,
          comment: "AANI | ALIPAY | STC_PAY | etc."

      # Reconciliation key fields
      add :rrn, :string, size: 50, null: false,
          comment: "Transaction reference from QR scheme — may be longer than card RRN"
      add :tid, :string, size: 20
      add :mid, :string, size: 25
      add :qr_id, :string, size: 50,
          comment: "QR tag identifier"
      add :participant_id, :string, size: 50
      add :auth_number, :string, size: 20
      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"
      add :currency_code, :string, size: 3

      # Financial detail from QR scheme
      add :scheme_fee, :decimal, precision: 12, scale: 2,
          comment: "Fee charged by the QR scheme"
      add :net_amount, :decimal, precision: 12, scale: 2
      add :response_code, :string, size: 10

      # Raw dump row
      add :raw_data, :text

      # Matching outcome (same pattern as switch_dump_records)
      add :matched_at, :naive_datetime
      add :core_transaction_id, :bigint
      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(:qr_scheme_dump_records, [:rrn])
    create index(:qr_scheme_dump_records, [:dump_date])
    create index(:qr_scheme_dump_records, [:dump_file_id])
    create index(:qr_scheme_dump_records, [:qr_scheme, :dump_date])
    create index(:qr_scheme_dump_records, [:match_status])
    create index(:qr_scheme_dump_records, [:core_transaction_id])
    create index(:qr_scheme_dump_records, [:tid, :rrn, :transaction_date],
      name: :qr_dump_recon_key)
  end
end