defmodule PlatformCore.Repo.Migrations.AddBankNameAndOnboardingFieldsTidMasters do
  use Ecto.Migration

  @moduledoc """
  Phase 3.1 + 3.3: Add bank_name, onboarding_source, and onboarding_id to tid_masters.

  bank_name: Human-readable bank name (e.g. "Emirates NBD", "FAB") — needed in
  payout_items and payout CSV export. Sourced from MMS merchant_metadata.bank_name
  during ApprovalSync.

  onboarding_source: Channel that originated the merchant (e.g. "B2C_APP", "DIRECT",
  "PARTNER_API"). Useful for segmentation in MIS and payout reports.

  onboarding_id: Unique reference number assigned at onboarding time. Alternative
  merchant reference for bank file.
  """

  def change do
    maybe_add_tid_master_columns()

    unless index_exists?(:tid_masters, "tid_masters_onboarding_source_index") do
      create index(:tid_masters, [:onboarding_source],
        name: :tid_masters_onboarding_source_index
      )
    end
  end

  defp maybe_add_tid_master_columns do
    maybe_add_column(:tid_masters, :bank_name, fn ->
      alter table(:tid_masters) do
        add :bank_name, :string, size: 128, null: true,
          comment: "Human-readable bank name for payout CSV (e.g. 'Emirates NBD')"
      end
    end)

    maybe_add_column(:tid_masters, :onboarding_source, fn ->
      alter table(:tid_masters) do
        add :onboarding_source, :string, size: 64, null: true,
          comment: "Channel that originated the merchant onboarding (B2C_APP, DIRECT, PARTNER_API)"
      end
    end)

    maybe_add_column(:tid_masters, :onboarding_id, fn ->
      alter table(:tid_masters) do
        add :onboarding_id, :string, size: 64, null: true,
          comment: "Unique merchant reference from onboarding system (alternative bank file ref)"
      end
    end)
  end

  defp maybe_add_column(table, column, add_fun) do
    unless column_exists?(table, column), do: add_fun.()
  end

  defp column_exists?(table, column) do
    query = """
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = ?
      AND COLUMN_NAME = ?
    LIMIT 1
    """

    %{num_rows: num_rows} =
      Ecto.Adapters.SQL.query!(repo(), query, [Atom.to_string(table), Atom.to_string(column)])

    num_rows > 0
  end

  defp index_exists?(table, index_name) do
    query = """
    SELECT 1
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = ?
      AND INDEX_NAME = ?
    LIMIT 1
    """

    %{num_rows: num_rows} =
      Ecto.Adapters.SQL.query!(repo(), query, [Atom.to_string(table), index_name])

    num_rows > 0
  end
end