defmodule EctoPSQLExtras.DiagnoseLogic do
  @moduledoc """
  Diagnose report logic
  """

  require Logger

  @table_cache_hit_min_expected 0.985
  @index_cache_hit_min_expected 0.985
  @unused_indexes_max_scans 20
  @unused_indexes_min_size_bytes 1_000_000
  # 1 MB
  @null_indexes_min_size_mb 1
  # 50%
  @null_min_null_frac_percent 50
  @bloat_min_value 10.0
  # 33%
  @outliers_min_exec_ratio 33

  def run(repo) do
    try do
      %{
        columns: ["ok", "check_name", "message"],
        rows: [
          missing_fk_indexes(repo),
          missing_fk_constraints(repo),
          table_cache_hit(repo),
          index_cache_hit(repo),
          unused_indexes(repo),
          null_indexes(repo),
          bloat(repo),
          duplicate_indexes(repo),
          outliers(repo),
          ssl_used(repo)
        ]
      }
    catch
      kind, error ->
        Logger.warning("#{__MODULE__}\n#{Exception.format(kind, error, __STACKTRACE__)}")

        %{
          columns: ["ok", "check_name", "message"],
          rows: [
            [
              false,
              "diagnose_error",
              "There was an error when generating your diagnose report, see logs"
            ]
          ]
        }
    end
  end

  defp missing_fk_indexes(repo) do
    missing = EctoPSQLExtras.missing_fk_indexes(repo, format: :raw)

    [ok, message] =
      case missing.rows do
        [] ->
          [true, "No missing foreign key indexes detected."]

        _ ->
          print_missing =
            Enum.map_join(missing.rows, ", ", fn el ->
              "'#{Enum.at(el, 0)}'.'#{Enum.at(el, 1)}'"
            end)

          [false, "Missing foreign key indexes detected: #{print_missing}"]
      end

    [ok, "missing_fk_indexes", message]
  end

  defp missing_fk_constraints(repo) do
    missing = EctoPSQLExtras.missing_fk_constraints(repo, format: :raw)

    [ok, message] =
      case missing.rows do
        [] ->
          [true, "No missing foreign key constraints detected."]

        _ ->
          print_missing =
            Enum.map_join(missing.rows, ", ", fn el ->
              "'#{Enum.at(el, 0)}'.'#{Enum.at(el, 1)}'"
            end)

          [false, "Missing foreign key constraints detected: #{print_missing}"]
      end

    [ok, "missing_fk_constraints", message]
  end

  defp table_cache_hit(repo) do
    table_cache_hit_val =
      EctoPSQLExtras.cache_hit(repo, format: :raw).rows |> Enum.at(1) |> Enum.at(1)

    [ok, message] =
      case table_cache_hit_val do
        nil ->
          [false, "Table cache hit ratio is not yet reported."]

        %Decimal{} = val ->
          val = Decimal.to_float(val) |> Float.round(6)

          if val >= @table_cache_hit_min_expected do
            [true, "Table cache hit ratio is correct: #{Float.round(val, 5)}"]
          else
            [false, "Table cache hit ratio is too low: #{Float.round(val, 5)}"]
          end
      end

    [ok, "table_cache_hit", message]
  end

  defp index_cache_hit(repo) do
    index_cache_hit_val =
      EctoPSQLExtras.cache_hit(repo, format: :raw).rows |> Enum.at(0) |> Enum.at(1)

    [ok, message] =
      case index_cache_hit_val do
        nil ->
          [false, "Index cache hit ratio is not yet reported."]

        %Decimal{} = val ->
          val = Decimal.to_float(val) |> Float.round(6)

          if val >= @index_cache_hit_min_expected do
            [true, "Index cache hit ratio is correct: #{Float.round(val, 5)}"]
          else
            [false, "Index cache hit ratio is too low: #{Float.round(val, 5)}"]
          end
      end

    [ok, "index_cache_hit", message]
  end

  defp unused_indexes(repo) do
    indexes =
      EctoPSQLExtras.unused_indexes(
        repo,
        format: :raw,
        args: [min_scans: @unused_indexes_max_scans]
      ).rows
      |> Enum.filter(fn el ->
        Enum.at(el, 3) >= @unused_indexes_min_size_bytes
      end)

    [ok, message] =
      case indexes do
        [] ->
          [true, "No unused indexes detected."]

        _ ->
          print_indexes =
            Enum.map_join(indexes, ", ", fn el ->
              "'#{Enum.at(el, 2)}'"
            end)

          [false, "Unused indexes detected: #{print_indexes}"]
      end

    [ok, "unused_indexes", message]
  end

  defp null_indexes(repo) do
    indexes =
      EctoPSQLExtras.null_indexes(
        repo,
        format: :raw,
        args: [min_relation_size_mb: @null_indexes_min_size_mb]
      ).rows
      |> Enum.filter(fn el ->
        {null_frac, ""} =
          Enum.at(el, 5)
          |> String.trim_leading()
          |> String.replace("%", "")
          |> String.replace(~r/^\./, "0.")
          |> Float.parse()

        null_frac > @null_min_null_frac_percent
      end)

    [ok, message] =
      case indexes do
        [] ->
          [true, "No null indexes detected."]

        _ ->
          print_indexes =
            Enum.map(indexes, fn el ->
              "'#{Enum.at(el, 1)}'"
            end)
            |> Enum.join(", ")

          [false, "Null indexes detected: #{print_indexes}"]
      end

    [ok, "null_indexes", message]
  end

  defp bloat(repo) do
    bloated_objects =
      EctoPSQLExtras.bloat(
        repo,
        format: :raw
      ).rows
      |> Enum.filter(fn el ->
        # el = [type, schemaname, object_name, bloat, waste]
        bloat = Decimal.to_float(Enum.at(el, 3)) |> Float.round(6)
        bloat > @bloat_min_value
      end)

    [ok, message] =
      case bloated_objects do
        [] ->
          [true, "No bloated tables or indexes detected."]

        _ ->
          print_bloat =
            Enum.map_join(bloated_objects, ", ", fn el ->
              "#{Enum.at(el, 0)} '#{Enum.at(el, 2)}'"
            end)

          [false, "Bloat detected: #{print_bloat}"]
      end

    [ok, "bloat", message]
  end

  defp duplicate_indexes(repo) do
    indexes =
      EctoPSQLExtras.duplicate_indexes(
        repo,
        format: :raw
      ).rows

    [ok, message] =
      case indexes do
        [] ->
          [true, "No duplicate indexes detected."]

        _ ->
          print_indexes =
            Enum.map_join(indexes, ", ", fn el ->
              "'#{Enum.at(el, 1)}' - '#{Enum.at(el, 2)}' size #{Enum.at(el, 0)}'"
            end)

          [false, "Duplicate indexes detected: #{print_indexes}"]
      end

    [ok, "duplicate_indexes", message]
  end

  defp outliers(repo) do
    case EctoPSQLExtras.pg_stat_statements_version(repo) do
      nil ->
        [
          false,
          "outliers",
          "Cannot check outliers because 'pg_stat_statements' extension is not enabled."
        ]

      _ ->
        outliers_data(repo)
    end
  end

  defp outliers_data(repo) do
    queries =
      EctoPSQLExtras.outliers(
        repo,
        format: :raw
      ).rows
      |> Enum.filter(fn el ->
        Enum.at(el, 2) > @outliers_min_exec_ratio
      end)

    [ok, message] =
      case queries do
        [] ->
          [true, "No queries using significant execution ratio detected."]

        _ ->
          print_queries =
            Enum.map_join(queries, ", ", fn el ->
              "'#{Enum.at(el, 0) |> String.slice(0, 20)}...' using #{Enum.at(el, 2)}%"
            end)

          [false, "Queries using significant execution ratio detected: #{print_queries}"]
      end

    [ok, "outliers", message]
  end

  defp ssl_used(repo) do
    if EctoPSQLExtras.ssl_info_enabled(repo) do
      ssl_used_data(repo)
    else
      [
        false,
        "ssl_used",
        "Cannot check connection status because 'sslinfo' extension is not enabled."
      ]
    end
  end

  defp ssl_used_data(repo) do
    %Postgrex.Result{rows: [[ssl_used_result]]} =
      EctoPSQLExtras.ssl_used(
        repo,
        format: :raw
      )

    [ok, message] =
      case ssl_used_result do
        true ->
          [true, "Database client is using a secure SSL connection."]

        false ->
          [false, "Database client is using an unencrypted connection."]
      end

    [ok, "ssl_used", message]
  end
end
