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

  def up do
    execute("""
    CREATE TABLE IF NOT EXISTS pos_transaction_reversal (
      id BIGINT NOT NULL AUTO_INCREMENT,
      s_txn_type VARCHAR(25) DEFAULT NULL,
      original_temp_txn_id BIGINT NOT NULL,
      reversal_reason VARCHAR(50) NOT NULL,
      reversal_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
      s_tid VARCHAR(8) DEFAULT NULL,
      s_mid VARCHAR(15) DEFAULT NULL,
      s_tid_stan 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,
      reversal_mti VARCHAR(4) DEFAULT NULL,
      original_mti VARCHAR(4) DEFAULT NULL,
      original_encrypted_pan VARCHAR(128) DEFAULT NULL,
      original_encrypted_expiry_date VARCHAR(64) DEFAULT NULL,
      original_proc_code VARCHAR(6) DEFAULT NULL,
      original_amount DECIMAL(12,2) DEFAULT NULL,
      original_stan VARCHAR(6) DEFAULT NULL,
      original_time VARCHAR(6) DEFAULT NULL,
      original_date VARCHAR(8) DEFAULT NULL,
      original_entry_mode VARCHAR(3) DEFAULT NULL,
      original_pan_seq VARCHAR(3) DEFAULT NULL,
      original_reference_no VARCHAR(12) DEFAULT NULL,
      original_currency_code VARCHAR(3) DEFAULT NULL,
      original_batch_no VARCHAR(12) DEFAULT NULL,
      reversal_request TEXT,
      reversal_response TEXT,
      reversal_response_code VARCHAR(2) DEFAULT NULL,
      reversal_reference_no VARCHAR(12) DEFAULT NULL,
      retry_count INT NOT NULL DEFAULT 0,
      max_retry_attempts INT NOT NULL DEFAULT 3,
      next_retry_time DATETIME DEFAULT NULL,
      created_dateTime DATETIME NOT NULL,
      initiated_dateTime DATETIME DEFAULT NULL,
      completed_dateTime DATETIME DEFAULT NULL,
      updated_dateTime DATETIME DEFAULT NULL,
      error_message VARCHAR(255) DEFAULT NULL,
      metadata TEXT,
      PRIMARY KEY (id),
      KEY idx_reversal_btid_bmid (b_tid, b_mid),
      KEY idx_reversal_original_txn (original_temp_txn_id),
      KEY idx_reversal_status (reversal_status),
      KEY idx_reversal_created (created_dateTime)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    """)
  end

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