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

  def up do
    execute("""
    CREATE TABLE IF NOT EXISTS pos_terminal_data (
      id BIGINT NOT NULL AUTO_INCREMENT,
      pos_terminalId VARCHAR(8) NOT NULL,
      stan VARCHAR(6) NOT NULL,
      batch_number VARCHAR(6) NOT NULL,
      transaction_date VARCHAR(4) NOT NULL,
      transaction_time VARCHAR(6) NOT NULL,
      transaction_id BIGINT DEFAULT NULL,
      created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      KEY idx_pos_terminal_data_terminal_id (pos_terminalId),
      KEY idx_pos_terminal_data_stan (stan),
      KEY idx_pos_terminal_data_batch (batch_number),
      KEY idx_pos_terminal_data_transaction_id (transaction_id),
      KEY idx_pos_terminal_data_composite (pos_terminalId, stan, batch_number),
      KEY idx_pos_terminal_data_date_time (transaction_date, transaction_time)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
      COMMENT='Stores POS terminal transaction data extracted from ISO messages'
    """)
  end

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