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

  @moduledoc """
  Line-level detail of the Settlement MIS — one row per transaction in the MIS.

  Merchant name, bank details, and IBAN are denormalized from shukria_mms at
  MIS generation time so the record is self-contained even if shukria_mms
  data changes later.

  Also links to merchant_adjustments so Finance can see what deductions/credits
  were applied to each merchant.
  """

  def change do
    create table(:settlement_mis_items) do
      add :settlement_mis_id, :bigint, null: false,
          comment: "FK settlement_mis.id"
      add :core_transaction_id, :bigint, null: false,
          comment: "FK core_transactions.id"

      # Merchant info (denormalized at 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,
          comment: "Beneficiary IBAN for payout"
      add :account_number, :string, size: 50

      # Transaction detail
      add :rrn, :string, size: 50
      add :tid, :string, size: 20
      add :auth_number, :string, size: 10
      add :transaction_date, :date
      add :transaction_type, :string, size: 20
      add :scheme_name, :string, size: 30
      add :card_type_code, :string, size: 50

      # Financial calculation
      add :gross_amount, :decimal, precision: 12, scale: 2
      add :mdr_percentage_rate, :decimal, precision: 8, scale: 4
      add :mdr_amount, :decimal, precision: 12, scale: 2
      add :vat_rate, :decimal, precision: 5, scale: 2
      add :vat_amount, :decimal, precision: 12, scale: 2
      add :interchange_fee, :decimal, precision: 12, scale: 2,
          comment: "Mercury cost — for profitability reporting"
      add :adjustment_amount, :decimal, precision: 12, scale: 2, default: 0,
          comment: "Net of all merchant_adjustments applied"
      add :ar_recovery, :decimal, precision: 12, scale: 2, default: 0,
          comment: "Chargeback recovery deducted"
      add :net_payable, :decimal, precision: 12, scale: 2, null: false,
          comment: "gross - mdr - vat - adjustment - ar_recovery"

      # Risk / hold status at time of MIS generation
      add :risk_status, :string, size: 30
      add :exception_type, :string, size: 10

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

    create index(:settlement_mis_items, [:settlement_mis_id])
    create index(:settlement_mis_items, [:core_transaction_id])
    create index(:settlement_mis_items, [:merchant_mid])
    create index(:settlement_mis_items, [:rrn])
    create unique_index(:settlement_mis_items, [:settlement_mis_id, :core_transaction_id],
      name: :mis_items_unique,
      comment: "A transaction can only appear once in a given MIS")
  end
end