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

  def change do
    # Create International FX Rates table (separate from fx_quotes for QR validations)
    create table(:international_fx_rates, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :base_currency, :string, null: false
      add :target_currency, :string, null: false
      add :fx_rate, :decimal, precision: 18, scale: 8, null: false
      add :markup_rate, :decimal, precision: 5, scale: 2, default: 0
      add :effective_from, :utc_datetime
      add :effective_until, :utc_datetime
      add :active, :boolean, default: true
      add :source, :string
      add :corridor, :string
      add :last_modified_ts, :utc_datetime
      add :created_by, :string
      add :rate_type, :string

      timestamps()
    end

    create unique_index(:international_fx_rates, [:base_currency, :target_currency, :corridor], 
           name: :intl_fx_rates_unique_active_rate)
    create index(:international_fx_rates, [:corridor])
    create index(:international_fx_rates, [:effective_from, :effective_until])
    create index(:international_fx_rates, [:active])

    # Create Partners table
    create table(:partners, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :partner_code, :string, null: false
      add :partner_name, :string, null: false
      add :partner_type, :string, null: false
      add :api_endpoint, :string
      add :api_key, :string
      add :webhook_url, :string
      add :webhook_secret, :string
      add :settlement_account, :string
      add :commission_rate, :decimal, precision: 5, scale: 2
      add :status, :string, default: "ACTIVE"
      add :contact_email, :string
      add :contact_phone, :string
      add :corridor, :string
      add :base_currency, :string
      add :onboarded_at, :utc_datetime
      add :last_reconciled_at, :utc_datetime

      timestamps()
    end

    create unique_index(:partners, [:partner_code])
    create index(:partners, [:corridor])
    create index(:partners, [:status])

    # Extend existing Merchants table with international fields
    alter table(:merchants) do
      add :partner_id, references(:partners, type: :binary_id, on_delete: :restrict)
      add :merchant_code, :string
      add :merchant_vpa, :string
      add :business_type, :string
      add :business_category, :string
      add :static_qr_code, :text
      add :qr_enabled, :boolean, default: true
      add :dynamic_qr_enabled, :boolean, default: false
      add :max_transaction_limit, :decimal, precision: 15, scale: 2
      add :daily_transaction_limit, :decimal, precision: 15, scale: 2
      add :settlement_frequency, :string, default: "T+1"
      add :settlement_account_intl, :string
      add :contact_person, :string
      add :contact_email, :string
      add :contact_phone, :string
      add :address, :text
      add :city, :string
      add :state, :string
      add :pincode, :string
      add :gstin, :string
      add :pan, :string
      add :intl_status, :string, default: "ACTIVE"
      add :onboarded_at, :utc_datetime
      add :last_transaction_at, :utc_datetime
    end

    create index(:merchants, [:merchant_code])
    create index(:merchants, [:merchant_vpa])
    create index(:merchants, [:partner_id])
    create index(:merchants, [:intl_status])

    # Update QR Validations table for international support
    alter table(:qr_validations) do
      add :payee_addr, :string
      add :payee_name, :string
      add :base_amount, :decimal, precision: 15, scale: 2
      add :base_currency, :string
      add :fx_timestamp, :utc_datetime
      add :corridor, :string
      add :fx_provider, :string
      add :validation_type, :string, default: "INTERNATIONAL"
      add :merchant_category, :string
      add :transaction_purpose, :string
      add :partner_id, references(:partners, type: :binary_id, on_delete: :restrict)
      add :merchant_id, references(:merchants, on_delete: :restrict)
    end

    # Update Transactions table for international support  
    alter table(:transactions) do
      # Change primary key to binary_id (if not already)
      # add :id, :binary_id, primary_key: true  # Uncomment if needed
      
      # Add customer details
      add :payer_name, :string
      add :payee_name, :string
      add :currency, :string, default: "INR"
      
      # Add international fields
      add :markup_rate, :decimal, precision: 5, scale: 2
      add :corridor, :string
      add :transaction_type, :string, default: "INTERNATIONAL"
      add :fx_provider, :string
      add :fx_locked_at, :utc_datetime
      
      # Add UPI protocol fields
      add :req_msg_id, :string
      add :resp_msg_id, :string
      
      # Add new timestamps
      add :npci_received_at, :utc_datetime
      add :partner_credited_at, :utc_datetime
      add :completed_at, :utc_datetime
      
      # Add error handling fields
      add :failure_reason, :string
      add :partner_txn_id, :string
      add :npci_reversal_ref, :string
      
      # Add relationships
      add :partner_id, references(:partners, type: :binary_id, on_delete: :restrict)
      add :merchant_id, references(:merchants, on_delete: :restrict)
    end

    # Add indexes for transactions
    create index(:transactions, [:corridor])
    create index(:transactions, [:transaction_type])
    create index(:transactions, [:partner_id])
    create index(:transactions, [:merchant_id])
    create index(:transactions, [:npci_received_at])

    # Add indexes for QR validations
    create index(:qr_validations, [:validation_type])
    create index(:qr_validations, [:corridor])
    create index(:qr_validations, [:partner_id])
    create index(:qr_validations, [:merchant_id])
  end
end