if Code.ensure_loaded?(Postgrex) do
  defmodule Pyex.Stdlib.SQL do
    @moduledoc """
    Python `sql` module backed by Postgrex.

    Provides `sql.query(sql, params)` for parameterized queries
    against a PostgreSQL database. The connection URL is read from
    `DATABASE_URL` in the execution context's environ.

    Returns a list of dicts (one per row) with column names as keys.

        import sql
        rows = sql.query("SELECT id, name FROM users WHERE id = $1", [42])
        # [{"id": 42, "name": "Alice"}]
    """

    @behaviour Pyex.Stdlib.Module

    alias Pyex.PyDict

    @doc """
    Returns the module value -- a map with callable attributes.
    """
    @impl Pyex.Stdlib.Module
    @spec module_value() :: Pyex.Stdlib.Module.module_value()
    def module_value do
      %{
        "query" => {:builtin, &do_query/1}
      }
    end

    @spec do_query([Pyex.Interpreter.pyvalue()]) ::
            {:io_call, (Pyex.Env.t(), Pyex.Ctx.t() -> {term(), Pyex.Env.t(), Pyex.Ctx.t()})}
            | {:exception, String.t()}
    defp do_query([sql, {:py_list, params, _}]) when is_binary(sql) and is_list(params) do
      do_query([sql, Enum.reverse(params)])
    end

    defp do_query([sql, params]) when is_binary(sql) and is_list(params) do
      Pyex.Ctx.guarded_io_call(:sql, fn env, ctx ->
        case Map.fetch(ctx.env, "DATABASE_URL") do
          {:ok, url} when is_binary(url) ->
            run_query(sql, params, url, env, ctx)

          _ ->
            {{:exception, "sql.query: DATABASE_URL not set in environ"}, env, ctx}
        end
      end)
    end

    defp do_query([sql]) when is_binary(sql) do
      do_query([sql, []])
    end

    defp do_query(_args) do
      {:exception, "TypeError: sql.query(sql_string, params_list)"}
    end

    @spec run_query(
            String.t(),
            [Pyex.Interpreter.pyvalue()],
            String.t(),
            Pyex.Env.t(),
            Pyex.Ctx.t()
          ) ::
            {term(), Pyex.Env.t(), Pyex.Ctx.t()}
    defp run_query(sql, params, url, env, ctx) do
      start_mono = System.monotonic_time()
      telemetry_meta = %{statement: sql}

      :telemetry.execute(
        [:pyex, :query, :start],
        %{system_time: System.system_time()},
        telemetry_meta
      )

      case parse_url(url) do
        {:ok, opts} ->
          conn = Postgrex.start_link(opts)

          case conn do
            {:ok, conn} ->
              try do
                pg_params = Enum.map(params, &to_pg/1)
                result = Postgrex.query(conn, sql, pg_params, timeout: 15_000)

                case result do
                  {:ok, %Postgrex.Result{columns: nil}} ->
                    duration = System.monotonic_time() - start_mono

                    :telemetry.execute([:pyex, :query, :stop], %{duration: duration}, %{
                      statement: sql,
                      rows_returned: 0
                    })

                    {[], env, ctx}

                  {:ok, %Postgrex.Result{columns: cols, rows: rows, num_rows: n}} ->
                    duration = System.monotonic_time() - start_mono

                    :telemetry.execute([:pyex, :query, :stop], %{duration: duration}, %{
                      statement: sql,
                      rows_returned: n
                    })

                    result = Enum.map(rows, fn row -> row_to_dict(cols, row) end)
                    {result, env, ctx}

                  {:error, %Postgrex.Error{postgres: %{message: msg}}} ->
                    duration = System.monotonic_time() - start_mono

                    :telemetry.execute([:pyex, :query, :stop], %{duration: duration}, %{
                      statement: sql,
                      error: msg
                    })

                    {{:exception, "sql.DatabaseError: #{msg}"}, env, ctx}

                  {:error, reason} ->
                    duration = System.monotonic_time() - start_mono

                    :telemetry.execute([:pyex, :query, :stop], %{duration: duration}, %{
                      statement: sql,
                      error: inspect(reason)
                    })

                    {{:exception, "sql.DatabaseError: #{inspect(reason)}"}, env, ctx}
                end
              after
                GenServer.stop(conn)
              end

            {:error, reason} ->
              duration = System.monotonic_time() - start_mono

              :telemetry.execute([:pyex, :query, :stop], %{duration: duration}, %{
                statement: sql,
                error: inspect(reason)
              })

              {{:exception, "sql.ConnectionError: #{inspect(reason)}"}, env, ctx}
          end

        {:error, msg} ->
          duration = System.monotonic_time() - start_mono

          :telemetry.execute([:pyex, :query, :stop], %{duration: duration}, %{
            statement: sql,
            error: msg
          })

          {{:exception, msg}, env, ctx}
      end
    end

    @spec parse_url(String.t()) :: {:ok, keyword()} | {:error, String.t()}
    defp parse_url(url) do
      case URI.parse(url) do
        %URI{scheme: scheme, host: host, port: port, path: path, userinfo: userinfo}
        when scheme in ["postgres", "postgresql"] and is_binary(host) ->
          database =
            case path do
              "/" <> db -> db
              _ -> nil
            end

          {username, password} = parse_userinfo(userinfo)

          opts =
            [
              hostname: host,
              port: port || 5432,
              database: database,
              username: username,
              password: password,
              show_sensitive_data_on_connection_error: false
            ]
            |> Enum.reject(fn {_k, v} -> is_nil(v) end)

          {:ok, opts}

        _ ->
          {:error, "sql.ConnectionError: invalid DATABASE_URL"}
      end
    end

    @spec parse_userinfo(String.t() | nil) :: {String.t() | nil, String.t() | nil}
    defp parse_userinfo(nil), do: {nil, nil}

    defp parse_userinfo(info) do
      case String.split(info, ":", parts: 2) do
        [user, pass] -> {URI.decode(user), URI.decode(pass)}
        [user] -> {URI.decode(user), nil}
      end
    end

    @spec to_pg(Pyex.Interpreter.pyvalue()) :: term()
    defp to_pg(nil), do: nil
    defp to_pg(val) when is_binary(val), do: val
    defp to_pg(val) when is_integer(val), do: val
    defp to_pg(val) when is_float(val), do: Decimal.from_float(val)
    defp to_pg(true), do: true
    defp to_pg(false), do: false
    defp to_pg(val), do: to_string(val)

    @spec row_to_dict([String.t()], [term()]) :: PyDict.t()
    defp row_to_dict(columns, values) do
      columns
      |> Enum.zip(values)
      |> Enum.map(fn {col, val} -> {col, from_pg(val)} end)
      |> PyDict.from_pairs()
    end

    @spec from_pg(term()) :: Pyex.Interpreter.pyvalue()
    defp from_pg(nil), do: nil

    defp from_pg(val) when is_binary(val) do
      if byte_size(val) == 16 and not String.printable?(val) do
        <<a::32, b::16, c::16, d::16, e::48>> = val

        :io_lib.format(
          "~8.16.0b-~4.16.0b-~4.16.0b-~4.16.0b-~12.16.0b",
          [a, b, c, d, e]
        )
        |> to_string()
      else
        val
      end
    end

    defp from_pg(val) when is_integer(val), do: val
    defp from_pg(val) when is_float(val), do: val
    defp from_pg(true), do: true
    defp from_pg(false), do: false
    defp from_pg(%Decimal{} = d), do: Decimal.to_float(d)
    defp from_pg(%Date{} = d), do: Date.to_iso8601(d)
    defp from_pg(%Time{} = t), do: Time.to_iso8601(t)
    defp from_pg(%NaiveDateTime{} = dt), do: NaiveDateTime.to_iso8601(dt)
    defp from_pg(%DateTime{} = dt), do: DateTime.to_iso8601(dt)
    defp from_pg(list) when is_list(list), do: Enum.map(list, &from_pg/1)
    defp from_pg(%Postgrex.INET{address: addr}), do: :inet.ntoa(addr) |> to_string()

    defp from_pg(val) when is_map(val) do
      pairs = Enum.map(val, fn {k, v} -> {to_string(k), from_pg(v)} end)
      PyDict.from_pairs(pairs)
    end

    defp from_pg(val), do: to_string(val)
  end
end
