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

  @moduledoc """
  Unified transaction table — the single source of truth for all downstream
  settlement, reconciliation, risk, and payout processing.

  Populated via INSERT INTO ... SELECT from pos_settlements (POS channel)
  or transactions (QR channel) after the EOD batch sync API is triggered.

  All operational fields are fully denormalized here, including MDR rates
  fetched from shukria_mms at sync time. No JOIN back to source tables is
  required for any business operation after this table is populated.

  Lifecycle:
    inserted by sync → switch_settled_date set by recon engine
    → risk_hold set by risk engine → settlement_status = approved
    → settlement_status = paid after bank confirmation
  """

  def change do
    create table(:core_transactions) do
      # ── Source Reference (audit only — never JOIN for business logic) ──
      add :source_type, :string, size: 10, null: false,
          comment: "POS | QR"
      add :source_ref_id, :bigint, null: false,
          comment: "pos_settlements.id OR transactions.id — audit ref only"
      add :settlement_batch_id, :string, size: 50, null: false,
          comment: "The EOD batch that sourced this row"

      # ── Reconciliation Keys (spec: RRN + TID + Amount + Date + Auth) ──
      add :rrn, :string, size: 12, null: false,
          comment: "Retrieval Reference Number — primary recon match key"
      add :tid, :string, size: 20, null: false,
          comment: "Terminal ID"
      add :auth_number, :string, size: 10
      add :stan, :string, size: 10
      add :invoice_number, :string, size: 20
      add :batch_number, :string, size: 10

      # ── Merchant Identifiers ──
      add :merchant_mid, :string, size: 25, null: false,
          comment: "MID from the acquiring side"
      add :merchant_id, :bigint,
          comment: "shukria_mms merchants.id — denormalized"
      add :merchant_ref_number, :string, size: 50,
          comment: "shukria_mms merchant_metadata.merchant_refrence_number"

      # ── Transaction Detail ──
      add :transaction_amount, :decimal, precision: 12, scale: 2, null: false
      add :transaction_currency, :string, size: 5, default: "AED"
      add :transaction_type, :string, size: 20, null: false,
          comment: "SALE | REFUND | REVERSAL | VOID"
      add :transaction_datetime, :naive_datetime, null: false
      add :masked_pan, :string, size: 20
      add :scheme_name, :string, size: 30,
          comment: "VISA | MASTERCARD | AMEX | UNIONPAY | DINERS | AANI | ALIPAY"

      # ── MDR & Card Type (denormalized from shukria_mms at sync time) ──
      add :mcc_code, :string, size: 10
      add :card_type_id, :bigint,
          comment: "shukria_mms card_types.id — frozen at sync"
      add :card_type_code, :string, size: 50,
          comment: "e.g. VISA_DEBIT_STD — frozen at sync"
      add :mdr_template_id, :bigint,
          comment: "shukria_mms mdr_templates.id — frozen at sync"
      add :mdr_percentage_rate, :decimal, precision: 8, scale: 4,
          comment: "Frozen from mdr_rates at sync time"
      add :mdr_fixed_fee, :decimal, precision: 10, scale: 2, default: 0,
          comment: "Frozen from mdr_rates at sync time"
      add :mdr_minimum_fee, :decimal, precision: 10, scale: 2
      add :mdr_maximum_fee, :decimal, precision: 10, scale: 2
      add :mdr_amount, :decimal, precision: 12, scale: 2,
          comment: "Calculated: max(min_fee, min(max_fee, amount×rate + fixed_fee))"

      # ── Settlement Calculation ──
      add :vat_rate, :decimal, precision: 5, scale: 2,
          comment: "VAT % at time of settlement"
      add :vat_amount, :decimal, precision: 12, scale: 2
      add :interchange_fee, :decimal, precision: 12, scale: 2,
          comment: "From interchange_rates table — Mercury cost"
      add :gross_amount, :decimal, precision: 12, scale: 2
      add :net_settlement_amount, :decimal, precision: 12, scale: 2,
          comment: "gross - mdr_amount - vat_amount"

      # ── Lifecycle Status (updated by the various processing engines) ──
      add :settlement_status, :string, size: 30, null: false, default: "unmatched",
          comment: "unmatched | matched | risk_hold | chargeback_hold | exception_hold | approved | paid | rejected"
      add :switch_settled_date, :date,
          comment: "NULL = not reconciled yet. Set by recon engine when RRN matches dump."
      add :risk_hold, :boolean, null: false, default: false
      add :risk_rule_name, :string, size: 100,
          comment: "Which risk rule triggered the hold"
      add :chargeback_hold, :boolean, null: false, default: false
      add :exception_type, :string, size: 10,
          comment: "NULL | 5.1 | 5.2 | 5.3 | 5.4 — set by recon engine"
      add :refund_status, :string, size: 10,
          comment: "NULL | PARTIAL | FULL — set when refund found in dump"
      add :refund_date, :date

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

    # Recon engine match key — must be unique to prevent duplicate sync
    create unique_index(:core_transactions, [:rrn, :tid, :transaction_datetime],
      name: :core_transactions_recon_key_unique)

    # Audit lookup: find source of a core transaction
    create unique_index(:core_transactions, [:source_type, :source_ref_id],
      name: :core_transactions_source_unique)

    create index(:core_transactions, [:merchant_mid])
    create index(:core_transactions, [:settlement_batch_id])
    create index(:core_transactions, [:settlement_status])
    create index(:core_transactions, [:switch_settled_date])
    create index(:core_transactions, [:transaction_datetime])
    create index(:core_transactions, [:mcc_code])
    create index(:core_transactions, [:risk_hold])
    create index(:core_transactions, [:transaction_type])
  end
end