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

  def change do
    # First check if column already exists
    execute """
    SELECT COUNT(*) INTO @col_exists FROM information_schema.columns
    WHERE table_schema = DATABASE()
    AND table_name = 'brands'
    AND column_name = 'merchant_reference_id'
    """

    # Only add column if it doesn't exist
    execute """
    SET @add_column = IF(@col_exists = 0,
      'ALTER TABLE brands ADD COLUMN merchant_reference_id VARCHAR(255)',
      'SELECT 1')
    """

    execute "PREPARE stmt FROM @add_column"
    execute "EXECUTE stmt"
    execute "DEALLOCATE PREPARE stmt"

    # Create index for better query performance
    execute """
    SELECT COUNT(*) INTO @idx_exists FROM information_schema.statistics
    WHERE table_schema = DATABASE()
    AND table_name = 'brands'
    AND index_name = 'brands_merchant_reference_id_index'
    """

    execute """
    SET @create_idx = IF(@idx_exists = 0,
      'CREATE INDEX brands_merchant_reference_id_index ON brands (merchant_reference_id)',
      'SELECT 1')
    """

    execute "PREPARE stmt FROM @create_idx"
    execute "EXECUTE stmt"
    execute "DEALLOCATE PREPARE stmt"
  end
end

