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

  def up do
    alter table(:addresses) do
      add :country_code, :string
      add :merchant_reference_id, :string
    end

    create index(:addresses, [:merchant_reference_id])
  end

  def down do
    # First check if the index exists before dropping it
    execute """
    SET @index_exists = (
      SELECT COUNT(1) FROM information_schema.statistics
      WHERE table_schema = DATABASE()
      AND table_name = 'addresses'
      AND index_name = 'addresses_merchant_reference_id_index'
    );
    """

    execute """
    SET @sql = IF(@index_exists > 0,
      'DROP INDEX addresses_merchant_reference_id_index ON addresses',
      'SELECT 1');
    """

    execute "PREPARE stmt FROM @sql;"
    execute "EXECUTE stmt;"
    execute "DEALLOCATE PREPARE stmt;"

    # Check if the columns exist before trying to remove them
    execute """
    SET @country_code_exists = (
      SELECT COUNT(1) FROM information_schema.columns
      WHERE table_schema = DATABASE()
      AND table_name = 'addresses'
      AND column_name = 'country_code'
    );
    """

    execute """
    SET @merchant_reference_id_exists = (
      SELECT COUNT(1) FROM information_schema.columns
      WHERE table_schema = DATABASE()
      AND table_name = 'addresses'
      AND column_name = 'merchant_reference_id'
    );
    """

    # Only remove columns if they exist
    execute """
    SET @sql_country = IF(@country_code_exists > 0,
      'ALTER TABLE addresses DROP COLUMN country_code',
      'SELECT 1');
    """

    execute """
    SET @sql_merchant = IF(@merchant_reference_id_exists > 0,
      'ALTER TABLE addresses DROP COLUMN merchant_reference_id',
      'SELECT 1');
    """

    execute "PREPARE stmt_country FROM @sql_country;"
    execute "EXECUTE stmt_country;"
    execute "DEALLOCATE PREPARE stmt_country;"

    execute "PREPARE stmt_merchant FROM @sql_merchant;"
    execute "EXECUTE stmt_merchant;"
    execute "DEALLOCATE PREPARE stmt_merchant;"
  end
end