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

  def up do
    # Check and add columns only if they don't exist
    unless column_exists?(:partners, :api_secret) do
      alter table(:partners) do
        add :api_secret, :string, size: 128
      end
    end

    unless column_exists?(:partners, :ip_whitelist) do
      alter table(:partners) do
        add :ip_whitelist, :text  # JSON array of allowed IPs
      end
    end

    unless column_exists?(:partners, :rate_limit_per_minute) do
      alter table(:partners) do
        add :rate_limit_per_minute, :integer, default: 100
      end
    end

    unless column_exists?(:partners, :last_used_at) do
      alter table(:partners) do
        add :last_used_at, :utc_datetime
      end
    end

    unless column_exists?(:partners, :api_key_expires_at) do
      alter table(:partners) do
        add :api_key_expires_at, :utc_datetime
      end
    end

    # Check and create index only if it doesn't exist
    unless index_exists?(:partners, [:api_key_expires_at]) do
      create index(:partners, [:api_key_expires_at])
    end
  end

  def down do
    # Remove index if it exists
    if index_exists?(:partners, [:api_key_expires_at]) do
      drop index(:partners, [:api_key_expires_at])
    end

    # Remove columns if they exist
    if column_exists?(:partners, :api_key_expires_at) do
      alter table(:partners) do
        remove :api_key_expires_at
      end
    end

    if column_exists?(:partners, :last_used_at) do
      alter table(:partners) do
        remove :last_used_at
      end
    end

    if column_exists?(:partners, :rate_limit_per_minute) do
      alter table(:partners) do
        remove :rate_limit_per_minute
      end
    end

    if column_exists?(:partners, :ip_whitelist) do
      alter table(:partners) do
        remove :ip_whitelist
      end
    end

    if column_exists?(:partners, :api_secret) do
      alter table(:partners) do
        remove :api_secret
      end
    end
  end

  # Helper function to check if a column exists in MySQL
  defp column_exists?(table, column) do
    table_name = Atom.to_string(table)
    column_name = Atom.to_string(column)
    
    query = """
    SELECT COUNT(*) as count
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = ?
    AND COLUMN_NAME = ?
    """
    
    case Ecto.Adapters.SQL.query(repo(), query, [table_name, column_name]) do
      {:ok, %{rows: [[count]]}} -> count > 0
      _ -> false
    end
  end

  # Helper function to check if an index exists in MySQL
  defp index_exists?(table, columns) do
    table_name = Atom.to_string(table)
    column_name = columns |> List.first() |> Atom.to_string()
    
    query = """
    SELECT COUNT(*) as count
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = ?
    AND COLUMN_NAME = ?
    """
    
    case Ecto.Adapters.SQL.query(repo(), query, [table_name, column_name]) do
      {:ok, %{rows: [[count]]}} -> count > 0
      _ -> false
    end
  end
end