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

  def up do
    # Create switch_pos_temp_transaction table
    create table(:switch_pos_temp_transaction, primary_key: false) do
      add :id, :bigint, primary_key: true, null: false
      add :s_tid, :string, size: 8, null: false
      add :s_mid, :string, size: 15
      add :s_tid_stan, :string, size: 6
      add :s_tid_invoiceno, :string, size: 6
      add :s_tid_batchno, :string, size: 6
      add :b_tid, :string, size: 8
      add :b_mid, :string, size: 15
      add :acquirer_id, :integer
      add :b_tid_stan, :string, size: 6
      add :b_tid_invoiceno, :string, size: 6
      add :b_tid_batchno, :string, size: 6
      add :b_tid_date, :string, size: 8
      add :b_tid_time, :string, size: 6
      add :entry_mode, :string, size: 3
      add :condition_code, :string, size: 2
      add :currency_code, :string, size: 3
      add :mti, :string, size: 4, null: false
      add :proc_code, :string, size: 6, null: false
      add :total_amount, :decimal, precision: 12, scale: 2
      add :auth_amount, :decimal, precision: 12, scale: 2
      add :cash_amount, :decimal, precision: 12, scale: 2
      add :tip_amount, :decimal, precision: 12, scale: 2
      add :mcc_code, :string, size: 4
      add :encrypted_track2, :string, size: 256
      add :encrypted_expiry, :string, size: 4
      add :encrypted_pan, :string, size: 256
      add :masked_card_no, :string, size: 19
      add :pan_seq, :string, size: 3
      add :emv_data, :text
      add :status, :string, size: 30
      add :retry_count, :integer, default: 0
      add :error_message, :string, size: 255
      add :timeout_at, :naive_datetime
      add :original_message, :text
      add :acquirer_reference_no, :string, size: 23
      add :metadata, :text
      add :gateway_type, :string, size: 20
      add :gateway_reference_id, :string, size: 100
      add :gateway_status, :string, size: 20
      add :processing_state, :string, size: 20
      add :reversal_retry_count, :integer, default: 0
      add :last_reversal_attempt_at, :naive_datetime
      add :reversal_initiated_reason, :string, size: 100
      add :payment_method_id, :bigint

      timestamps()
    end

    execute("ALTER TABLE `switch_pos_temp_transaction` MODIFY COLUMN id BIGINT AUTO_INCREMENT")
    execute("ALTER TABLE `switch_pos_temp_transaction` ENGINE = InnoDB")
    execute("ALTER TABLE `switch_pos_temp_transaction` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")

    create index(:switch_pos_temp_transaction, [:s_tid], name: "idx_spt_s_tid")
    create index(:switch_pos_temp_transaction, [:acquirer_id], name: "idx_spt_acquirer_id")
    create index(:switch_pos_temp_transaction, [:status], name: "idx_spt_status")
    create index(:switch_pos_temp_transaction, [:timeout_at], name: "idx_spt_timeout_at")
    create index(:switch_pos_temp_transaction, [:inserted_at], name: "idx_spt_inserted_at")

    # Create switch_pos_transaction table
    create table(:switch_pos_transaction, primary_key: false) do
      add :id, :bigint, primary_key: true, null: false
      add :s_tid, :string, size: 8, null: false
      add :s_mid, :string, size: 15
      add :s_tid_stan, :string, size: 6
      add :s_tid_invoiceno, :string, size: 6
      add :s_tid_batchno, :string, size: 6
      add :b_tid, :string, size: 8
      add :b_mid, :string, size: 15
      add :acquirer_id, :integer, null: false
      add :b_tid_stan, :string, size: 6
      add :b_tid_invoiceno, :string, size: 6
      add :b_tid_batchno, :string, size: 6
      add :b_tid_date, :string, size: 8
      add :b_tid_time, :string, size: 6
      add :entry_mode, :string, size: 3
      add :condition_code, :string, size: 2
      add :currency_code, :string, size: 3
      add :mti, :string, size: 4, null: false
      add :proc_code, :string, size: 6, null: false
      add :total_amount, :decimal, precision: 12, scale: 2
      add :auth_amount, :decimal, precision: 12, scale: 2
      add :cash_amount, :decimal, precision: 12, scale: 2
      add :tip_amount, :decimal, precision: 12, scale: 2
      add :approval_code, :string, size: 6
      add :reference_no, :string, size: 12
      add :response_code, :string, size: 2
      add :response_message, :string, size: 100
      add :mcc_code, :string, size: 4
      add :encrypted_track2, :string, size: 256
      add :encrypted_expiry, :string, size: 4
      add :encrypted_pan, :string, size: 256
      add :masked_card_no, :string, size: 19
      add :pan_seq, :string, size: 3
      add :emv_data, :text
      add :acquirer_reference_no, :string, size: 23
      add :scheme_reference_no, :string, size: 23
      add :metadata, :text
      add :gateway_type, :string, size: 20
      add :gateway_reference_id, :string, size: 100
      add :gateway_status, :string, size: 20
      add :settlement_date, :date
      add :settlement_status, :string, size: 20
      add :payment_method_id, :bigint

      timestamps()
    end

    execute("ALTER TABLE `switch_pos_transaction` MODIFY COLUMN id BIGINT AUTO_INCREMENT")
    execute("ALTER TABLE `switch_pos_transaction` ENGINE = InnoDB")
    execute("ALTER TABLE `switch_pos_transaction` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")

    create index(:switch_pos_transaction, [:s_tid], name: "idx_sp_s_tid")
    create index(:switch_pos_transaction, [:b_tid], name: "idx_sp_b_tid")
    create index(:switch_pos_transaction, [:acquirer_id], name: "idx_sp_acquirer_id")
    create index(:switch_pos_transaction, [:mti], name: "idx_sp_mti")
    create index(:switch_pos_transaction, [:approval_code], name: "idx_sp_approval_code")
    create index(:switch_pos_transaction, [:reference_no], name: "idx_sp_reference_no")
    create index(:switch_pos_transaction, [:inserted_at], name: "idx_sp_inserted_at")
  end

  def down do
    drop table(:switch_pos_transaction)
    drop table(:switch_pos_temp_transaction)
  end
end