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

  def up do
    execute("""
    CREATE TABLE batch_numbers (
      id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      merchant_id VARCHAR(255) NOT NULL,
      batch_number VARCHAR(255) NOT NULL,
      date DATE NOT NULL,
      transaction_count INT DEFAULT 0,
      last_used_at DATETIME,
      inserted_at DATETIME NOT NULL,
      updated_at DATETIME NOT NULL,
      UNIQUE KEY unique_merchant_date (merchant_id, date),
      INDEX idx_merchant_id (merchant_id),
      INDEX idx_date (date)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    """)
  end

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