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

  @moduledoc """
  BIN (Bank Identification Number) lookup table.

  Maps the first 6–8 digits of a card PAN to card brand, issuer, and type.
  Used by POS team to resolve card_type_id before inserting into pos_settlements.
  Also available for core system validation and reporting.

  Data source: BIN file from YSP (pending — table created now, data loaded later).
  """

  def change do
    create table(:bin_table) do
      add :bin_prefix, :string, size: 10, null: false,
          comment: "First 6–8 digits of card PAN"
      add :card_type_id, :bigint,
          comment: "shukria_mms card_types.id — denormalized by lookup"
      add :card_type_code, :string, size: 50,
          comment: "Denormalized from card_types.code e.g. VISA_DEBIT_STD"
      add :card_brand, :string, size: 30,
          comment: "VISA | MASTERCARD | AMEX | UNIONPAY | DINERS"
      add :card_category, :string, size: 20,
          comment: "DEBIT | CREDIT | PREPAID"
      add :issuer_name, :string, size: 100
      add :issuer_country, :string, size: 5,
          comment: "ISO country code e.g. AE, US"
      add :status, :integer, null: false, default: 1,
          comment: "1 = active, 0 = inactive"
      add :inserted_at, :naive_datetime, null: false
      add :updated_at, :naive_datetime, null: false
    end

    create unique_index(:bin_table, [:bin_prefix], name: :bin_table_prefix_unique)
    create index(:bin_table, [:card_brand])
    create index(:bin_table, [:card_type_id])
  end
end