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

  @moduledoc """
  Settlement-stamped snapshot of pos_transaction rows after EOD batch close.

  Created by the POS team's EOD process. Each settled POS transaction gets
  one row here referencing the original pos_transaction. This table is the
  trigger source for the core_transactions sync API call.

  card_type_id must be resolved by the POS application (from BIN or scheme)
  before inserting, as it is the foreign key into shukria_mms.card_types
  used to look up MDR rates.
  """

  def change do
    create table(:pos_settlements) do
      # Link to source transaction
      add :pos_transaction_id, :bigint, null: false,
          comment: "FK pos_transaction.id"
      add :settlement_batch_id, :string, size: 50, null: false,
          comment: "Unique batch identifier for this EOD run"
      add :settlement_date, :date, null: false

      # Settlement outcome
      add :settlement_status, :string, size: 20, null: false, default: "settled",
          comment: "settled | rejected | pending"
      add :rejection_reason, :string, size: 255

      # Card type for MDR lookup — resolved by POS app from BIN / scheme
      add :card_type_id, :bigint, null: true,
          comment: "shukria_mms card_types.id — resolved by POS team"
      add :scheme_name, :string, size: 30,
          comment: "VISA | MASTERCARD | AMEX | UNIONPAY | DINERS"

      # Core transaction fields (denormalized from pos_transaction for fast sync)
      add :s_txn_type, :string, size: 25
      add :s_tid, :string, size: 8
      add :s_mid, :string, size: 15
      add :b_tid, :string, size: 8
      add :b_mid, :string, size: 15
      add :b_tid_date, :string, size: 8
      add :b_tid_time, :string, size: 6
      add :b_tid_stan, :string, size: 6
      add :b_tid_invoiceno, :string, size: 6
      add :b_tid_batchno, :string, size: 6
      add :mti, :string, size: 4
      add :proc_code, :string, size: 6
      add :total_amount, :decimal, precision: 12, scale: 2
      add :currency_code, :string, size: 3
      add :approval_code, :string, size: 6
      add :reference_no, :string, size: 12
      add :response_code, :string, size: 2
      add :mcc_code, :string, size: 4
      add :masked_card_no, :string, size: 19
      add :acquirer_id, :bigint
      add :acquirer_reference_no, :string, size: 23
      add :scheme_reference_no, :string, size: 23
      add :created_date_time, :naive_datetime
      add :metadata, :text

      # Sync tracking
      add :synced_to_core, :boolean, null: false, default: false
      add :synced_at, :naive_datetime

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

    create index(:pos_settlements, [:pos_transaction_id])
    create index(:pos_settlements, [:settlement_batch_id])
    create index(:pos_settlements, [:settlement_date])
    create index(:pos_settlements, [:settlement_status])
    create index(:pos_settlements, [:b_tid, :b_mid])
    create index(:pos_settlements, [:synced_to_core])
    create unique_index(:pos_settlements, [:pos_transaction_id],
      name: :pos_settlements_transaction_unique,
      comment: "Each POS transaction can only be settled once")
  end
end