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

  def up do
    execute("DROP TABLE IF EXISTS pos_transaction")

    execute("""
    CREATE TABLE pos_transaction (
      id BIGINT NOT NULL AUTO_INCREMENT,
      s_txn_type VARCHAR(25) DEFAULT NULL,
      s_tid VARCHAR(8) DEFAULT NULL,
      s_mid VARCHAR(15) DEFAULT NULL,
      s_tid_stan VARCHAR(6) DEFAULT NULL,
      s_tid_invoiceno VARCHAR(6) DEFAULT NULL,
      s_tid_batchno VARCHAR(6) DEFAULT NULL,
      b_tid VARCHAR(8) DEFAULT NULL,
      b_mid VARCHAR(15) DEFAULT NULL,
      acquirer_id BIGINT DEFAULT NULL,
      b_tid_stan VARCHAR(6) DEFAULT NULL,
      b_tid_invoiceno VARCHAR(6) DEFAULT NULL,
      b_tid_batchno VARCHAR(6) DEFAULT NULL,
      b_tid_date VARCHAR(8) DEFAULT NULL,
      b_tid_time VARCHAR(6) DEFAULT NULL,
      entry_mode VARCHAR(3) DEFAULT NULL,
      condition_code VARCHAR(2) DEFAULT NULL,
      currency_code VARCHAR(3) DEFAULT NULL,
      mti VARCHAR(4) DEFAULT NULL,
      proc_code VARCHAR(6) DEFAULT NULL,
      total_amount DECIMAL(12,2) DEFAULT NULL,
      auth_amount DECIMAL(12,2) DEFAULT NULL,
      cash_amount DECIMAL(12,2) DEFAULT NULL,
      tip_amount DECIMAL(12,2) DEFAULT NULL,
      approval_code VARCHAR(6) DEFAULT NULL,
      reference_no VARCHAR(12) DEFAULT NULL,
      response_code VARCHAR(2) DEFAULT NULL,
      response_message VARCHAR(100) DEFAULT NULL,
      mcc_code VARCHAR(4) DEFAULT NULL,
      encrypted_track2 VARCHAR(256) DEFAULT NULL,
      encrypted_pan VARCHAR(256) DEFAULT NULL,
      hash_pan CHAR(64) DEFAULT NULL,
      masked_card_no VARCHAR(19) DEFAULT NULL,
      encrypted_expiry VARCHAR(64) DEFAULT NULL,
      pan_seq VARCHAR(3) DEFAULT NULL,
      emv_data TEXT,
      acquirer_reference_no VARCHAR(23) DEFAULT NULL,
      scheme_reference_no VARCHAR(23) DEFAULT NULL,
      created_dateTime DATETIME DEFAULT NULL,
      updated_dateTime DATETIME DEFAULT NULL,
      metadata TEXT,
      order_number VARCHAR(50) DEFAULT NULL,
      PRIMARY KEY (id),
      KEY idx_transaction_btid_bmid (b_tid, b_mid),
      KEY idx_transaction_stid (s_tid),
      KEY idx_transaction_created (created_dateTime),
      KEY idx_transaction_response_code (response_code),
      KEY idx_transaction_approval_code (approval_code),
      KEY idx_transaction_amount (total_amount),
      KEY idx_transaction_mti (mti)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    """)
  end

  def down do
    execute("DROP TABLE IF EXISTS pos_transaction")
  end
end