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

  def change do
    create table(:pos_reversal) do
      # Original Transaction Reference
      add :original_transaction_id, references(:pos_transaction, on_delete: :nothing)
      add :original_s_tid, :string, size: 8
      add :original_s_mid, :string, size: 15
      add :original_s_stan, :string, size: 6
      add :original_b_stan, :string, size: 6
      add :original_reference_no, :string, size: 23
      add :original_approval_code, :string, size: 6
      add :original_amount, :decimal, precision: 12, scale: 2
      add :original_date, :string, size: 8
      add :original_time, :string, size: 6
      
      # Reversal Transaction Details
      add :s_tid, :string, size: 8
      add :s_mid, :string, size: 15
      add :s_tid_stan, :string, size: 6
      add :b_tid, :string, size: 8
      add :b_mid, :string, size: 15
      add :acquirer_id, :integer
      add :b_tid_stan, :string, size: 6
      add :b_tid_date, :string, size: 8
      add :b_tid_time, :string, size: 6
      
      # Reversal Specifics
      add :mti, :string, size: 4
      add :proc_code, :string, size: 6
      add :reversal_reason_code, :string, size: 3
      add :reversal_reason_desc, :string, size: 100
      add :reversal_amount, :decimal, precision: 12, scale: 2
      add :currency_code, :string, size: 3
      
      # Card details (optional for some reversals)
      add :masked_card_no, :string, size: 19
      add :entry_mode, :string, size: 3
      add :condition_code, :string, size: 2
      
      # Response Information
      add :response_code, :string, size: 3
      add :response_message, :string, size: 255
      add :approval_code, :string, size: 6
      add :reference_no, :string, size: 23
      
      # Status and Processing
      add :status, :string, size: 20
      add :processed_at, :utc_datetime
      add :upstream_sent_at, :utc_datetime
      add :upstream_response_at, :utc_datetime
      
      # Audit and Metadata
      add :metadata, :text
      add :created_dateTime, :utc_datetime
      add :updated_dateTime, :utc_datetime
      add :created_by, :string, size: 50
      add :updated_by, :string, size: 50
    end

    # Indexes for performance
    create index(:pos_reversal, [:original_transaction_id])
    create index(:pos_reversal, [:original_s_tid, :original_s_mid, :original_s_stan], name: :idx_reversal_original_txn)
    create index(:pos_reversal, [:s_tid, :s_tid_stan], name: :idx_reversal_source)
    create index(:pos_reversal, [:status])
    create index(:pos_reversal, [:created_dateTime])
    create index(:pos_reversal, [:mti])
  end
end