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

  def change do
    create table(:qr_validations) do
      add :txn_id, :string, size: 50
      add :msg_id, :string, null: false
      add :org_id, :string, null: false
      add :payer_addr, :string
      add :payer_name, :string
      add :network_inst_id, :string, size: 20
      add :con_code, :string, size: 2
      add :qr_version, :string
      add :qr_medium, :string
      add :ver_token, :string
      add :qr_expires_at, :utc_datetime
      add :foreign_amount, :decimal, precision: 18, scale: 6
      add :foreign_currency, :string, size: 3
      add :fx_rate, :decimal, precision: 18, scale: 8
      add :markup_pct, :decimal, precision: 7, scale: 4
      add :inr_amount_calc, :decimal, precision: 18, scale: 2
      add :qr_hash, :binary
      add :raw_xml, :binary
      add :status, :string, null: false, default: "validated"
      timestamps()
    end

    create unique_index(:qr_validations, [:msg_id, :org_id])
    create unique_index(:qr_validations, [:ver_token])
    create index(:qr_validations, [:network_inst_id])
    create index(:qr_validations, [:status])

    create table(:qr_validation_events) do
      add :qr_validation_id, references(:qr_validations, on_delete: :delete_all), null: false
      add :seq, :integer, null: false
      add :event_type, :string, null: false
      add :payload, :map
      add :prev_hash, :binary
      add :hash, :binary, null: false
      add :inserted_at, :utc_datetime, null: false
    end

    create unique_index(:qr_validation_events, [:qr_validation_id, :seq])
    create index(:qr_validation_events, [:event_type])

    create table(:fx_quotes) do
      add :qr_validation_id, references(:qr_validations, on_delete: :delete_all), null: false
      add :base_currency, :string, size: 3, null: false
      add :base_amount, :decimal, precision: 18, scale: 6
      add :fx_rate, :decimal, precision: 18, scale: 8, null: false
      add :markup_pct, :decimal, precision: 7, scale: 4
      add :active, :boolean, default: true, null: false
      add :last_modified_ts, :utc_datetime
      timestamps(updated_at: false)
    end

    create index(:fx_quotes, [:qr_validation_id])
    create index(:fx_quotes, [:active])

    create table(:merchants) do
      add :mid, :string, null: false
      add :sid, :string
      add :tid, :string
      add :sub_code, :string
      add :merchant_type, :string
      add :merchant_genre, :string
      add :onboarding_type, :string
      add :inst_code, :string
      add :brand_name, :string
      add :legal_name, :string
      add :franchise_name, :string
      add :ownership_type, :string
      add :country_code, :string, size: 2
      add :network_inst_id, :string
      timestamps()
    end

    create unique_index(:merchants, [:mid, :tid])

    create table(:merchant_invoices) do
      add :merchant_id, references(:merchants, on_delete: :delete_all), null: false
      add :qr_validation_id, references(:qr_validations, on_delete: :delete_all)
      add :invoice_name, :string
      add :invoice_number, :string
      add :invoice_date, :utc_datetime
      timestamps(updated_at: false)
    end

    create index(:merchant_invoices, [:merchant_id])
    create index(:merchant_invoices, [:qr_validation_id])
  end
end