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

  @moduledoc """
  Unified TID Master table — one row per approved merchant in MMS.

  This is the settlement system's local snapshot of onboarded merchant data.
  It is populated (and kept in sync) from the shukria_mms `users` +
  `merchant_metadata` + `merchant_stores` tables.

  Only merchants whose `contract_approval_status = 'approved'` in the MMS
  `users` table are permitted to appear here. The MmsApprovalSyncWorker
  maintains this table daily; an on-demand sync API is available when a
  merchant is approved in MMS and needs to flow in immediately.

  MDR rates are snapshotted at sync time so the payout pipeline does not
  need to query MMS again. When MMS rates change, `mdr_rates` is refreshed
  on the next sync run.

  Bank details (bank_account_number, iban, virtual_iban) are sourced from
  `users.meta` JSON in MMS. If the MMS team promotes these to dedicated
  columns, update the ApprovalSync query accordingly.
  """

  def change do
    create table(:tid_masters) do
      # ── MMS Identity ────────────────────────────────────────────────────────
      add :mms_user_id, :bigint, null: false,
          comment: "shukria_mms users.id — the canonical merchant identifier in MMS"

      # ── Acquiring Identifiers ────────────────────────────────────────────────
      add :mid, :string, size: 25,
          comment: "merchant_metadata.merchant_id — MID used on terminal and in transactions"
      add :store_id, :bigint,
          comment: "merchant_stores.id WHERE is_primary = 1"
      add :merchant_ref_number, :string, size: 50,
          comment: "merchant_metadata.merchant_refrence_number"
      add :tid, :string, size: 20,
          comment: "Terminal ID — back-filled from pos_settlements on bootstrap"
      add :qr_id, :string, size: 50,
          comment: "QR merchant ID — back-filled from transactions on bootstrap"

      # ── Merchant Identity ────────────────────────────────────────────────────
      add :merchant_name_legal, :string, size: 255,
          comment: "users.name — legal registered entity name"
      add :merchant_name_dba, :string, size: 255,
          comment: "merchant_stores.dba_name WHERE is_primary = 1"
      add :registered_mobile, :string, size: 30,
          comment: "users.phone"
      add :registered_email, :string, size: 255,
          comment: "users.email"

      # ── MCC ──────────────────────────────────────────────────────────────────
      add :mcc_code, :string, size: 10,
          comment: "users.mcc_id joined to mcc table for the code"
      add :mcc_description, :string, size: 255,
          comment: "mdr_templates.sub_mcc — merchant category description"

      # ── Bank / Settlement Details ────────────────────────────────────────────
      add :bank_code, :string, size: 20,
          comment: "merchant_metadata.bank_code"
      add :bank_account_number, :string, size: 50,
          comment: "users.meta->>''bank_account_number''"
      add :iban, :string, size: 50,
          comment: "users.meta->>''iban'' — primary beneficiary IBAN for payout"
      add :virtual_iban, :string, size: 50,
          comment: "users.meta->>''virtual_iban''"

      # ── Products / Schemes ───────────────────────────────────────────────────
      add :enabled_schemes, :map,
          comment: "users.products JSONB — e.g. [''CARDS'', ''AANI'', ''ALIPAY'']"

      # ── MDR Snapshot ────────────────────────────────────────────────────────
      add :mdr_rates, :map,
          comment: "Snapshotted MDR rates at last sync — Map keyed by card_type_code"

      # ── Onboarding Dates ────────────────────────────────────────────────────
      add :onboarding_date, :date,
          comment: "users.contract_approved_at (date part)"
      add :contract_approved_at, :naive_datetime,
          comment: "users.contract_approved_at — full timestamp of MMS approval"

      # ── Activity Tracking ────────────────────────────────────────────────────
      add :last_transaction_date, :date,
          comment: "Updated by settlement engine after each payout"
      add :last_transaction_amount, :decimal, precision: 12, scale: 2,
          comment: "Last transaction amount from core_transactions"

      # ── Sync Metadata ────────────────────────────────────────────────────────
      add :mms_synced_at, :naive_datetime,
          comment: "Timestamp of last successful MMS sync for this merchant"

      # ── Status ───────────────────────────────────────────────────────────────
      add :status, :string, size: 20, null: false, default: "active",
          comment: "active | suspended | terminated"

      # ── Spare Fields (extensible JSON for future requirements) ───────────────
      add :spare_fields, :map,
          comment: "Reserved for future use — store any ad-hoc merchant attributes here"

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

    # Every approved MMS merchant appears exactly once
    create unique_index(:tid_masters, [:mms_user_id],
      name: :tid_masters_mms_user_id_unique,
      comment: "One TID Master record per MMS user")

    # MID lookups — used by recon engine and payout generator
    create index(:tid_masters, [:mid])

    # TID lookups — used by payout pipeline to resolve bank details
    create index(:tid_masters, [:tid])

    # QR ID lookups
    create index(:tid_masters, [:qr_id])

    # Status filtering for dashboards
    create index(:tid_masters, [:status])
  end
end