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

  @moduledoc """
  Per-merchant payout line items within a payout_batch.

  One row per merchant per payout batch. After the bank processes the payout
  file it sends back a confirmation per item with the bank transfer reference
  number. On confirmation, transactions.settlement_status is updated to 'paid'.

  For rejected items, rejection_reason is captured and Finance can initiate
  a re-payout batch for these items via the payment rejection handling UI.

  Payout file row format (from ShukriaPayout sample):
    OB, BANK_NAME, MERCHANT_NAME, AMOUNT, CURRENCY, IBAN, ...
  """

  def change do
    create table(:payout_items) do
      add :payout_batch_id, :bigint, null: false,
          comment: "FK payout_batches.id"
      add :settlement_mis_id, :bigint,
          comment: "FK settlement_mis.id"

      # Merchant payout details (denormalized at payout generation time)
      add :merchant_mid, :string, size: 25, null: false
      add :merchant_name, :string, size: 200
      add :merchant_ref_number, :string, size: 50
      add :bank_name, :string, size: 100
      add :iban, :string, size: 50, null: false,
          comment: "Beneficiary IBAN"
      add :account_number, :string, size: 50
      add :bank_code, :string, size: 20
      add :currency, :string, size: 5, default: "AED"

      # Amounts
      add :gross_amount, :decimal, precision: 12, scale: 2
      add :total_deductions, :decimal, precision: 12, scale: 2,
          comment: "mdr + vat + adjustments + ar_recovery"
      add :payout_amount, :decimal, precision: 12, scale: 2, null: false,
          comment: "Final net amount to credit to merchant account"
      add :transaction_count, :integer, default: 0

      # Status lifecycle
      add :status, :string, size: 20, null: false, default: "pending",
          comment: "pending | paid | rejected | re_initiated"

      # Bank confirmation (received from bank after processing)
      add :bank_transfer_ref, :string, size: 100,
          comment: "Transfer reference number from Mercury Bank"
      add :confirmed_at, :naive_datetime

      # Rejection handling
      add :rejection_reason, :string, size: 255,
          comment: "e.g. invalid IBAN, account closed"
      add :rejected_at, :naive_datetime
      add :re_initiated_at, :naive_datetime
      add :re_initiated_batch_id, :bigint,
          comment: "FK payout_batches.id of the re-payout batch"

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

    create index(:payout_items, [:payout_batch_id])
    create index(:payout_items, [:merchant_mid])
    create index(:payout_items, [:status])
    create index(:payout_items, [:iban])
    create index(:payout_items, [:bank_transfer_ref])
    create unique_index(:payout_items, [:payout_batch_id, :merchant_mid],
      name: :payout_items_batch_merchant_unique,
      comment: "Each merchant appears only once per payout batch")
  end
end