extools

defmodule Extools.Tools.ExecuteSqlQuery do
  @moduledoc """
  Run a SQL query against the auto-detected Ecto Repo and return columns,
  rows, and num_rows. Read-only by default; dangerous statements can be
  refused. Ecto must be loaded — if no repo is found an error is returned.
  """

  use Hermes.Server.Component, type: :tool

  alias Hermes.Server.Response

  schema do
    field(:sql, {:required, :string}, description: "SQL to execute via Repo.query/2.")

    field(:repo, :string,
      description: "Optional module name of the repo to use (default: auto-detected)."
    )
  end

  @impl true
  def execute(params, frame) do
    params = Extools.Tools.Params.normalize(params)
    sql = params["sql"]

    if Code.ensure_loaded?(Ecto) do
      with {:ok, repo} <- resolve_repo(params["repo"]),
           {:ok, _} <- safety_check(sql) do
        run_query(repo, sql, frame)
      else
        {:error, reason} ->
          {:reply, Response.tool() |> Response.error(to_string(reason)), frame}
      end
    else
      {:reply,
       Response.tool()
       |> Response.error(
         "Ecto is not loaded in this runtime. Add :ecto_sql to your deps to use execute_sql_query."
       ), frame}
    end
  end

  defp run_query(repo, sql, frame) do
    result =
      try do
        repo.query(sql, [])
      rescue
        e -> {:error, Exception.format(:error, e, __STACKTRACE__)}
      catch
        kind, reason -> {:error, Exception.format(kind, reason, __STACKTRACE__)}
      end

    case result do
      {:ok, %{columns: cols, rows: rows, num_rows: n}} ->
        payload = %{
          columns: cols,
          num_rows: n,
          rows: Enum.map(rows || [], &format_row/1)
        }

        {:reply, Response.tool() |> Response.json(payload), frame}

      {:error, reason} ->
        {:reply, Response.tool() |> Response.error("Query failed: #{reason}"), frame}
    end
  end

  defp format_row(row) when is_list(row) do
    Enum.map(row, &format_value/1)
  end

  defp format_row(other), do: inspect(other)

  # NOTE: no struct pattern matches here (%Decimal{}, %Date{}...) — those
  # require the modules to be available at compile time, and :ecto is an
  # optional dep. Compare the __struct__ atom instead.
  @iso_structs [Date, DateTime, NaiveDateTime, Time]

  defp format_value(%{__struct__: mod} = v) do
    cond do
      mod in @iso_structs -> apply(mod, :to_iso8601, [v])
      mod == Decimal -> apply(Decimal, :to_string, [v])
      true -> inspect(v)
    end
  end

  defp format_value(nil), do: nil
  defp format_value(v) when is_binary(v), do: v
  defp format_value(v) when is_number(v), do: v
  defp format_value(v), do: inspect(v)

  defp resolve_repo(nil), do: Extools.Ecto.detect_repo()

  defp resolve_repo(name) when is_binary(name) do
    module = String.to_atom("Elixir." <> String.trim_leading(name, "Elixir."))
    {:ok, module}
  end

  # Allowlist approach: only read-only statements are accepted.
  defp safety_check(sql) do
    normalized = sql |> String.trim() |> String.downcase()

    if String.starts_with?(normalized, "select") or
         String.starts_with?(normalized, "with") or
         String.starts_with?(normalized, "explain") do
      {:ok, :allowed}
    else
      {:error,
       "execute_sql_query only allows read-only queries (select/with/explain). Got statement starting with: #{String.slice(sql, 0, 20)}"}
    end
  end
end