lib/plausible/stats/query_optimizer.ex

defmodule Plausible.Stats.QueryOptimizer do
  @moduledoc """
  Methods to manipulate Query for business logic reasons before building an ecto query.
  """

  use Plausible

  alias Plausible.Stats.{
    DateTimeRange,
    Filters,
    Query,
    TableDecider,
    TimeOnPage,
    Util,
    Time,
    Legacy
  }

  @doc """
    This module manipulates an existing query, updating it according to business logic.

    For example, it:
    1. Figures out what the right granularity to group by time is
    2. Adds a missing order_by clause to a query
    3. Updating "time" dimension in order_by to the right granularity
    4. Updates event:hostname filters to also apply on visit level for sane results.
    5. [DEPRECATED AND WILL BE REMOVED] Removes revenue metrics from legacy queries if ineligible
    6. Trims the date range to the current time if query.include.trim_relative_date_range is true.
    7. Sets the join_type for the query based on the query.

  """
  def optimize(query) do
    Enum.reduce(pipeline(), query, fn step, acc -> step.(acc) end)
  end

  @doc """
  Splits a query into event and sessions subcomponents as not all metrics can be
  queried from a single table.

  event:page dimension is treated in a special way, doing a breakdown of visit:entry_page
  for sessions.
  """
  def split(query) do
    query.metrics
    |> Util.maybe_add_visitors_metric()
    |> TableDecider.partition_metrics(query)
    |> Enum.map(fn {table_type, metrics} ->
      build_split_query(table_type, metrics, query)
    end)
  end

  defp pipeline() do
    [
      &update_group_by_time/1,
      &add_missing_order_by/1,
      &update_time_in_order_by/1,
      &extend_hostname_filters_to_visit/1,
      &set_time_on_page_data/1,
      &remove_time_on_page_if_unavailable/1,
      &remove_revenue_metrics_if_unavailable/1,
      &trim_relative_date_range/1,
      &set_sql_join_type/1
    ]
  end

  defp add_missing_order_by(%Query{order_by: nil} = query) do
    order_by =
      case time_dimension(query) do
        nil -> [{hd(query.metrics), :desc}]
        time_dimension -> [{time_dimension, :asc}, {hd(query.metrics), :desc}]
      end

    %Query{query | order_by: order_by}
  end

  defp add_missing_order_by(query), do: query

  defp update_group_by_time(
         %Query{
           utc_time_range: %DateTimeRange{first: first, last: last}
         } = query
       ) do
    dimensions =
      query.dimensions
      |> Enum.map(fn
        "time" -> resolve_time_dimension(first, last)
        entry -> entry
      end)

    %Query{query | dimensions: dimensions}
  end

  defp update_group_by_time(query), do: query

  defp resolve_time_dimension(first, last) do
    cond do
      DateTime.diff(last, first, :hour) <= 48 -> "time:hour"
      DateTime.diff(last, first, :day) <= 40 -> "time:day"
      Plausible.Times.diff(last, first, :week) <= 52 -> "time:week"
      true -> "time:month"
    end
  end

  defp update_time_in_order_by(%Query{} = query) do
    order_by =
      query.order_by
      |> Enum.map(fn
        {"time", direction} -> {time_dimension(query), direction}
        entry -> entry
      end)

    %Query{query | order_by: order_by}
  end

  @dimensions_hostname_map %{
    "visit:source" => "visit:entry_page_hostname",
    "visit:entry_page" => "visit:entry_page_hostname",
    "visit:utm_medium" => "visit:entry_page_hostname",
    "visit:utm_source" => "visit:entry_page_hostname",
    "visit:utm_campaign" => "visit:entry_page_hostname",
    "visit:utm_content" => "visit:entry_page_hostname",
    "visit:utm_term" => "visit:entry_page_hostname",
    "visit:referrer" => "visit:entry_page_hostname",
    "visit:exit_page" => "visit:exit_page_hostname"
  }

  # To avoid showing referrers across hostnames when event:hostname
  # filter is present for breakdowns, add entry/exit page hostname
  # filters
  defp extend_hostname_filters_to_visit(%Query{} = query) do
    # Note: Only works since event:hostname is only allowed as a top level filter
    hostname_filters =
      query.filters
      |> Enum.filter(fn [_operation, dimension | _rest] -> dimension == "event:hostname" end)

    if length(hostname_filters) > 0 do
      extra_filters =
        query.dimensions
        |> Enum.flat_map(&hostname_filters_for_dimension(&1, hostname_filters))

      %Query{query | filters: query.filters ++ extra_filters}
    else
      query
    end
  end

  defp hostname_filters_for_dimension(dimension, hostname_filters) do
    if Map.has_key?(@dimensions_hostname_map, dimension) do
      dimension = Map.get(@dimensions_hostname_map, dimension)

      hostname_filters
      |> Enum.map(fn [operation, _dimension | rest] -> [operation, dimension | rest] end)
    else
      []
    end
  end

  defp time_dimension(query) do
    Enum.find(query.dimensions, &Time.time_dimension?/1)
  end

  defp build_split_query(:events, metrics, query) do
    {
      :events,
      Query.set(query,
        metrics: metrics,
        include_imported: query.include_imported
      )
    }
  end

  @event_page_dim_renames %{
    "event:page" => "visit:entry_page",
    "event:hostname" => "visit:entry_page_hostname"
  }

  defp build_split_query(:sessions, metrics, query) do
    active_renames = Map.take(@event_page_dim_renames, query.dimensions)

    dimensions = Enum.map(query.dimensions, &Map.get(active_renames, &1, &1))

    filters =
      if map_size(active_renames) > 0 do
        Filters.rename_dimensions_used_in_filter(query.filters, active_renames)
      else
        query.filters
      end

    {
      :sessions,
      Query.set(query,
        filters: filters,
        metrics: metrics,
        dimensions: dimensions,
        include_imported: query.include_imported
      )
    }
  end

  defp build_split_query(:sessions_smeared, metrics, query) do
    {_, query} = build_split_query(:sessions, metrics, query)

    {:sessions, Query.set(query, smear_session_metrics: true)}
  end

  on_ee do
    defp remove_revenue_metrics_if_unavailable(query) do
      if query.include.drop_unavailable_revenue_metrics and
           map_size(query.revenue_currencies) == 0 do
        Query.set(query, metrics: query.metrics -- Plausible.Stats.Goal.Revenue.revenue_metrics())
      else
        query
      end
    end
  else
    defp remove_revenue_metrics_if_unavailable(query), do: query
  end

  # Unavailable in this context means not implemented. Imported data is ignored in the
  # aggregate legacy time on page query, while the legacy breakdown query includes it.
  # We drop the metric to avoid reporting different numbers in different reports.
  defp remove_time_on_page_if_unavailable(query) do
    if query.include.drop_unavailable_time_on_page and time_on_page_unavailable?(query) do
      Query.set(query, metrics: query.metrics -- [:time_on_page])
    else
      query
    end
  end

  defp time_on_page_unavailable?(%Query{
         include_imported: true,
         dimensions: [],
         time_on_page_data: %{include_legacy_metric: true}
       }) do
    true
  end

  defp time_on_page_unavailable?(_), do: false

  defp set_time_on_page_data(query) do
    case {:time_on_page in query.metrics, query.time_on_page_data} do
      {true, %{new_metric_visible: true, cutoff_date: cutoff_date}} ->
        cutoff =
          cutoff_date
          |> TimeOnPage.cutoff_datetime(query.timezone)
          |> DateTime.shift_zone!("Etc/UTC")
          |> DateTime.truncate(:second)

        Query.set(
          query,
          time_on_page_data:
            Map.merge(query.time_on_page_data, %{
              include_new_metric: DateTime.before?(cutoff, query.utc_time_range.last),
              include_legacy_metric:
                DateTime.after?(cutoff, query.utc_time_range.first) and
                  Legacy.TimeOnPage.can_merge_legacy_time_on_page?(query),
              cutoff:
                if(DateTime.after?(cutoff, query.utc_time_range.first), do: cutoff, else: nil)
            })
        )

      _ ->
        Query.set(
          query,
          time_on_page_data:
            Map.merge(query.time_on_page_data, %{
              include_new_metric: false,
              include_legacy_metric: true,
              cutoff: nil
            })
        )
    end
  end

  defp trim_relative_date_range(%Query{include: %{trim_relative_date_range: true}} = query) do
    # This is here to trim future bucket labels on the main graph
    if should_trim_date_range?(query) do
      trimmed_range = trim_date_range_to_now(query)
      %Query{query | utc_time_range: trimmed_range}
    else
      query
    end
  end

  defp trim_relative_date_range(query), do: query

  defp should_trim_date_range?(%Query{input_date_range: :month} = query) do
    today = query.now |> DateTime.shift_zone!(query.timezone) |> DateTime.to_date()
    date_range = Query.date_range(query)

    current_month_start = Date.beginning_of_month(today)
    current_month_end = Date.end_of_month(today)

    date_range.first == current_month_start and date_range.last == current_month_end
  end

  defp should_trim_date_range?(%Query{input_date_range: :year} = query) do
    today = query.now |> DateTime.shift_zone!(query.timezone) |> DateTime.to_date()
    date_range = Query.date_range(query)

    current_year_start = Date.new!(today.year, 1, 1)
    current_year_end = Date.new!(today.year, 12, 31)

    date_range.first == current_year_start and date_range.last == current_year_end
  end

  defp should_trim_date_range?(%Query{input_date_range: :day} = query) do
    today = query.now |> DateTime.shift_zone!(query.timezone) |> DateTime.to_date()
    date_range = Query.date_range(query)

    is_nil(query.include.compare) and date_range.first == today and date_range.last == today
  end

  defp should_trim_date_range?(_query), do: false

  defp trim_date_range_to_now(query) do
    if query.input_date_range == :day do
      time_range = query.utc_time_range |> DateTimeRange.to_timezone(query.timezone)

      current_hour =
        query.now
        |> DateTime.shift_zone!(query.timezone)
        |> Map.merge(%{minute: 59, second: 59, millisecond: 999})

      time_range.first
      |> DateTimeRange.new!(current_hour)
      |> DateTimeRange.to_timezone("Etc/UTC")
    else
      date_range = Query.date_range(query)
      today = query.now |> DateTime.shift_zone!(query.timezone) |> DateTime.to_date()

      trimmed_to_date =
        Enum.min([date_range.last, today], Date)

      date_range.first
      |> DateTimeRange.new!(trimmed_to_date, query.timezone)
      |> DateTimeRange.to_timezone("Etc/UTC")
    end
  end

  # Normally we can always LEFT JOIN as this is more performant and tables
  # are expected to contain the same dimensions.

  # The only exception is using the "time:minute"/"time:hour" dimension where the sessions
  # subquery might return more rows than the events one. That's because we're
  # counting sessions in all time buckets they were active in even if no event
  # occurred during that particular bucket.
  defp set_sql_join_type(query) do
    if "time:minute" in query.dimensions or "time:hour" in query.dimensions do
      Query.set(query, sql_join_type: :full)
    else
      query
    end
  end
end