defmodule Selecto do
  defstruct [:repo, :domain, :config, :set]

  import Ecto.Query

  @moduledoc """
  Documentation for `Selecto,` a query writer and report generator for Elixir/Ecto

    TODO

    having

    json/embeds/arrays/maps?
       json:  tablen[field].somejsonkey tablen[field][index].somekey...


    distinct

    select into tuple or list instead of map more efficient?
    ability to add synthetic root, joins, filters, columns

    union, union all, intersect, intersect all
    -- pass in lists of alternative filters
    -- allow multiple unions

    limit, offset

    subqueries

  Mebbie:
    windows?
    CTEs? recursive?
    first, last?? as limit, reverse_order

  ERROR CHECKS
   -- Has association by right name?


  """

  @doc """
    Generate a selecto structure from this Repo following
    the instructinos in Domain map
  """
  def configure(repo, domain) do
    %Selecto{
      repo: repo,
      domain: domain,
      config: configure_domain(domain),
      set: %{
        selected: Map.get(domain, :required_selected, []),
        filtered: [],
        order_by: Map.get(domain, :required_order_by, []),
        group_by: Map.get(domain, :required_group_by, [])
      }
    }
  end

  # generate the selecto configuration
  defp configure_domain(%{source: source} = domain) do
    primary_key = source.__schema__(:primary_key)

    fields =
      Selecto.Schema.Column.configure_columns(
        :selecto_root,
        ## Add in keys from domain.columns ...
        source.__schema__(:fields) -- source.__schema__(:redact_fields),
        source,
        domain
      )

    joins = Selecto.Schema.Join.recurse_joins(source, domain)
    ## Combine fields from Joins into fields list
    fields =
      List.flatten([fields | Enum.map(Map.values(joins), fn e -> e.fields end)])
      |> Enum.reduce(%{}, fn m, acc -> Map.merge(m, acc) end)

    ### Extra filters (all normal fields can be a filter)
    filters = Map.get(domain, :filters, %{})

    filters =
      Enum.reduce(
        Map.values(joins),
        filters,
        fn e, acc ->
          Map.merge(Map.get(e, :filters, %{}), acc)
        end
      )

    %{
      primary_key: primary_key,
      columns: fields,
      joins: joins,
      filters: filters,
      domain_data: Map.get(domain, :domain_data)
    }
  end

  @doc """
    add a field to the Select list. Send in one or a list of field names or selectable tuples
    TODO allow to send single, and special forms..
  """
  def select(selecto, fields) when is_list(fields) do
    put_in(selecto.set.selected, Enum.uniq(selecto.set.selected ++ fields))
  end

  def select(selecto, field) do
    Selecto.select(selecto, [field])
  end

  #### Selects
  ### Add parameterized select functions...

  defp check_string( string ) do
    if string |> String.match?(~r/^[^a-zA-Z0-9_]+$/) do
      raise "Invalid String #{string}"
    end
    string
  end

  ## need more? upper, lower, ???, postgres specifics?
  defp apply_selection({query, aliases}, config, {:subquery, func, field}) do
    conf = config.columns[field]

    join = config.joins[conf.requires_join]
    my_func = check_string( Atom.to_string(func) )
    my_key = Atom.to_string(join.my_key)
    my_field = Atom.to_string(conf.field)



    # from a in SelectoTest.Test.SolarSystem, select: {fragment("(select json_agg(planets) from planets where solar_system_id = ?)", a.id)}
    # from a in SelectoTest.Test.SolarSystem, select: {fragment("(select count(id) from planets where solar_system_id = ?)", a.id)}
    as = "#{func}(#{field})"

    dyn = %{
      as =>
        dynamic(
          [{^join.requires_join, par}],
          fragment(
            "(select ?(?) from ? where ? = ?)",
            literal(^my_func),
            literal(^my_field),
            literal(^join.source),
            literal(^my_key),
            par.id
          )
        )
    }

    query = from(a in query, select_merge: ^dyn)
    {query, [as | aliases]}
  end

  # ARRAY - auto gen array from otherwise denorm'ing selects using postgres 'array' func
  # ---- eg {"array", "item_orders", select: ["item[name]", "item_orders[quantity]"], filters: [{item[type], "Pin"}]}
  # ---- postgres has functions to put those into json!
  # to select the items into an array and apply the filter to the subq. Would ahve to be something that COULD join
  # to one of the main query joins
  #TODOs
  # defp apply_selection({query, aliases}, _config, {:array, _field, _selects}) do
  #   {query, aliases}
  # end

  # # COALESCE ... ??
  # defp apply_selection({query, aliases}, _config, {:coalesce, _field, _selects}) do
  #   {query, aliases}
  # end

  # # CASE ... {:case, %{{...filter...}}=>val, cond2=>val, :else=>val}}
  # defp apply_selection({query, aliases}, _config, {:case, _field, _case_map}) do
  #   {query, aliases}
  # end

  defp apply_selection({query, aliases}, config, {:extract, field, format}) do
    conf = config.columns[field]
    as = "#{format} from #{field}"

    check_string(format)

    query =
      from({^conf.requires_join, owner} in query,
        select_merge: %{
          ^"#{as}" => fragment("extract(? from ?)", literal(^format), field(owner, ^conf.field))
        }
      )

    {query, [as | aliases]}
  end

  defp apply_selection({query, aliases}, config, {:to_char, {field, format}, as}) do
    conf = config.columns[field]

    query =
      from({^conf.requires_join, owner} in query,
        select_merge: %{
          ^"#{as}" => fragment("to_char(?, ?)", field(owner, ^conf.field), ^format)
        }
      )

    {query, [as | aliases]}
  end

  ## Todo why this does not work with numbers?
  defp apply_selection({query, aliases}, _config, {:literal, name, value}) do
    query = from({:selecto_root, owner} in query, select_merge: %{^name => ^value})
    {query, [name | aliases]}
  end

  ### works with any func/agg of normal form
  defp apply_selection({query, aliases}, config, {func, field}) when is_atom(func) do
    use_as = "#{func}(#{field})"
    apply_selection({query, aliases}, config, {func, field, use_as})
  end

  ## Case of literal value arg
  defp apply_selection({query, aliases}, _config, {func, {:literal, field}, as})
       when is_atom(func) do
    func = Atom.to_string(func) |> check_string()

    query =
      from(query,
        select_merge: %{
          ^"#{as}" => fragment("?(?)", literal(^func), ^field)
        }
      )

    {query, [as | aliases]}
  end

  # Case for func call with field as arg
  ## Check for SQL INJ TODO
  ## TODO allow for func call args
  ## TODO variant for 2 arg aggs eg string_agg, jsonb_object_agg, Grouping
  ## ^^ and mixed lit/field args - field as list?

  defp apply_selection({query, aliases}, config, {func, field, as}) when is_atom(func) do
    conf = config.columns[field]
    func = Atom.to_string(func) |> check_string()

    query =
      from({^conf.requires_join, owner} in query,
        select_merge: %{
          ^"#{as}" => fragment("?(?)", literal(^func), field(owner, ^conf.field))
        }
      )

    {query, [as | aliases]}
  end

  # Case of 'count(*)' which we can just ref as count
  defp apply_selection({query, aliases}, _config, {:count}) do
    query = from(query, select_merge: %{"count" => fragment("count(*)")})
    {query, ["count" | aliases]}
  end

  # case of other non-arg funcs eg now()
  defp apply_selection({query, aliases}, _config, {func}) when is_atom(func) do
    func = Atom.to_string(func) |> check_string()
    from(query, select_merge: %{^func => fragment("?()", literal(^func))})
    {query, [func | aliases]}
  end

  ### regular old fields. Allow atoms?
  defp apply_selection({query, aliases}, config, field) when is_binary(field) do
    conf = config.columns[field]

    query =
      from({^conf.requires_join, owner} in query,
        select_merge: %{^field => field(owner, ^conf.field)}
      )

    {query, [field | aliases]}
  end

  ### applies the selections to the query
  defp apply_selections(query, config, selected) do
    {query, aliases} =
      selected
      |> Enum.reduce({query, []}, fn s, acc ->
        apply_selection(acc, config, s)
      end)

    {query, Enum.reverse(aliases)}
  end

  # get a map of joins to list of selected
  defp joins_from_selects(fields, selected) do
    selected
    |> Enum.map(fn
      {:array, _n, sels} -> sels
      {:coalesce, _n, sels} -> sels
      {:case, _n, case_map} -> Map.values(case_map)
      {:literal, _a, _b} -> []
      {_f, {s, _d}, _p} -> s
      {_f, s, _p} -> s
      {_f, s} -> s
      {_f} -> nil
      s -> s
    end)
    |> List.flatten()
    |> Enum.filter(fn
      {:literal, _s} -> false
      s -> not is_nil(s) and Map.get(fields, s)
    end)
    |> Enum.reduce(%{}, fn e, acc ->
      Map.put(acc, fields[e].requires_join, 1)
    end)
    |> Map.keys()
  end

  @doc """
    add a filter to selecto. Send in a tuple with field name and filter value
  """
  def filter(selecto, filters) when is_list(filters) do
    put_in(selecto.set.filtered, selecto.set.filtered ++ filters)
  end

  def filter(selecto, filters) do
    put_in(selecto.set.filtered, selecto.set.filtered ++ [filters])
  end

  # Thanks to https://medium.com/swlh/how-to-write-a-nested-and-or-query-using-elixirs-ecto-library-b7755de79b80
  defp combine_fragments_with_and(fragments) do
    conditions = false

    Enum.reduce(fragments, conditions, fn fragment, conditions ->
      if !conditions do
        dynamic([q], ^fragment)
      else
        dynamic([q], ^conditions and ^fragment)
      end
    end)
  end

  defp combine_fragments_with_or(fragments) do
    conditions = false

    Enum.reduce(fragments, conditions, fn fragment, conditions ->
      if !conditions do
        dynamic([q], ^fragment)
      else
        dynamic([q], ^conditions or ^fragment)
      end
    end)
  end

  defp apply_filters(query, config, filters) do
    filter =
      Enum.map(filters, fn f ->
        filters_recurse(config, f)
      end)
      |> combine_fragments_with_and()

    query |> where(^filter)
  end

  defp filters_recurse(config, {:or, filters}) do
    Enum.map(filters, fn f ->
      filters_recurse(config, f)
    end)
    |> combine_fragments_with_or()
  end

  defp filters_recurse(config, {:and, filters}) do
    Enum.map(filters, fn f ->
      filters_recurse(config, f)
    end)
    |> combine_fragments_with_and()
  end

  ### TODO add :not

  defp filters_recurse(config, {name, val}) do
    def = config.columns[name]
    table = def.requires_join
    field = def.field

    ### how to allow function calls/subqueries in field and val?
    case val do
      x when is_nil(x) ->
        dynamic([{^table, a}], is_nil(field(a, ^field)))

      x when is_bitstring(x) or is_number(x) or is_boolean(x) ->
        dynamic([{^table, a}], field(a, ^field) == ^val)

      x when is_list(x) ->
        dynamic([{^table, a}], field(a, ^field) in ^val)

      # TODO not-in

      # sucks to not be able to do these 6 in one with a fragment!
      {x, v} when x == "!=" ->
        dynamic([{^table, a}], field(a, ^field) != ^v)

      {x, v} when x == "<" ->
        dynamic([{^table, a}], field(a, ^field) < ^v)

      {x, v} when x == ">" ->
        dynamic([{^table, a}], field(a, ^field) > ^v)

      {x, v} when x == "<=" ->
        dynamic([{^table, a}], field(a, ^field) <= ^v)

      {x, v} when x == ">=" ->
        dynamic([{^table, a}], field(a, ^field) >= ^v)

      {:between, min, max} ->
        dynamic([{^table, a}], fragment("? between ? and ?", field(a, ^field), ^min, ^max))

      :not_true ->
        dynamic([{^table, a}], not field(a, ^field))

      {:like, v} ->
          dynamic([{^table, a}], like(field(a, ^field), ^v))

      {:ilike, v} ->
        dynamic([{^table, a}], ilike(field(a, ^field), ^v))

      _ -> raise "Filter Recurse not implemented for #{inspect(val)}"
        # date shortcuts (:today, :tomorrow, :last_week, etc )

        # {:case, %{filter=>}}
        # {:exists, etc-, subq} # how to do subq????
    end
  end

  # Can only give us the joins.. make this recurse and handle :or, :and, etc
  defp joins_from_filters(config, filters) do
    filters
    |> Enum.reduce(%{}, fn
      {:or, list}, acc ->
        Map.merge(
          acc,
          Enum.reduce(joins_from_filters(config, list), %{}, fn i, acc -> Map.put(acc, i, 1) end)
        )
      {:and, list}, acc ->
        Map.merge(
          acc,
          Enum.reduce(joins_from_filters(config, list), %{}, fn i, acc -> Map.put(acc, i, 1) end)
        )

      {fil, _val}, acc ->
        Map.put(acc, config.columns[fil].requires_join, 1)
    end)
    |> Map.keys()
  end

  @doc """
    Add to the Order By
  """
  def order_by(selecto, orders) do
    put_in(selecto.set.order_by, selecto.set.order_by ++ orders)
  end

  defp apply_order_by(query, config, order_bys) do
    order_bys =
      order_bys
      |> Enum.map(fn
        {dir, field} -> {dir, field}
        field -> {:asc_nulls_first, field}
      end)
      |> Enum.map(fn
        {dir, field} ->
          {dir,
           dynamic(
             [{^config.columns[field].requires_join, owner}],
             field(owner, ^config.columns[field].field)
           )}
      end)

    from(query,
      order_by: ^order_bys
    )
  end
  @doc """
    Add to the Group By
  """
  def group_by(selecto, groups) do
    put_in(selecto.set.group_by, selecto.set.group_by ++ groups)
  end



 # From Ecto.OLAP Copyright (c) 2017 Łukasz Jan Niemier THANKS!
  defmacro rollup(columns), do: mkquery(columns, "ROLLUP")

  defp mkquery(data, name) do
    quote do: fragment(unquote(name <> " ?"), unquote(fragment_list(data)))
  end

  defp fragment_list(list) when is_list(list) do
    query = "?" |> List.duplicate(Enum.count(list)) |> Enum.join(",")
    quote do: fragment(unquote("(" <> query <> ")"), unquote_splicing(list))
  end
  ###

  defp apply_group_by(query, _config, [], _) do
    query
  end


  defp recurse_group_by(config, group_by) do
    ## Todo make these use 1, 2, 3 etc when possible

    case group_by do
      {:extract, field, format} ->
        check_string(format)
        dynamic(
          [{^config.columns[field].requires_join, owner}],
          fragment(
            "extract( ? from ? )",
            literal(^format),
            field(owner, ^config.columns[field].field)
          )
        )

      ### how to dedupe?!?!
      {:rollup, [a]} ->
        dynamic([], rollup( [^recurse_group_by(config, a)] ) )

      {:rollup, [a, b]} ->
        dynamic([], rollup( [^recurse_group_by(config, a), ^recurse_group_by(config, b) ] ) )

      {:rollup, [a, b, c]} ->
        dynamic([], rollup( [^recurse_group_by(config, a), ^recurse_group_by(config, b),
        ^recurse_group_by(config, c) ] ) )

      {:rollup, [a, b, c, d]} ->
        dynamic([], rollup( [^recurse_group_by(config, a), ^recurse_group_by(config, b),
        ^recurse_group_by(config, c), ^recurse_group_by(config, d) ] ) )


      field ->
        dynamic(
          [{^config.columns[field].requires_join, owner}],
          field(owner, ^config.columns[field].field)
        )
    end

  end

  defp apply_group_by(query, config, group_bys, mode) do
    group_bys =
      group_bys |> Enum.map(fn g -> recurse_group_by(config, g) end)
    case mode do
      _ -> from(query, group_by: ^group_bys )

    end
  end

  @doc """
    Returns an Ecto.Query with all your filters and selections added..eventually!
  """
  def gen_query(selecto, opts \\ []) do
    IO.puts("Gen Query")

    {group_by_type, opts} = Keyword.pop(opts, :group_by_type, :group)

    joins_from_selects = joins_from_selects(selecto.config.columns, selecto.set.selected)
    filters_to_use = Map.get(selecto.domain, :required_filters, []) ++ selecto.set.filtered
    filtered_by_join = joins_from_filters(selecto.config, filters_to_use)

    joins_from_order_by =
      joins_from_selects(
        selecto.config.columns,
        Enum.map(selecto.set.order_by, fn
          {_dir, field} -> field
          field -> field
        end)
      )

    joins_from_group_by = joins_from_selects(selecto.config.columns, selecto.set.group_by)

    ## We select nothing from the initial query because we are going to select_merge everything and
    ## if we don't select empty map here, it will include the full * of our source!
    query = from(root in selecto.domain.source, as: :selecto_root, select: %{})

    ##### If we are GROUP BY and have AGGREGATES that live on a join path with any :many
    ##### cardinality we have to force the aggregates to subquery

    {query, aliases} =
      get_join_order(
        selecto.config.joins,
        Enum.uniq(
          joins_from_selects ++ filtered_by_join ++ joins_from_order_by ++ joins_from_group_by
        )
      )
      |> Enum.reduce(query, fn j, acc -> apply_join(selecto.config, acc, j) end)
      |> apply_selections(selecto.config, selecto.set.selected)

    IO.inspect(query, label: "Second Last")

    query =
      query
      |> apply_filters(selecto.config, filters_to_use)
      |> apply_group_by(selecto.config, selecto.set.group_by, group_by_type)
      |> apply_order_by(selecto.config, selecto.set.order_by)

    IO.inspect(query, label: "Last")

    {query, aliases}
  end

  def gen_sql(selecto) do
    #todo!
  end

  # apply the join to the query
  # we don't need to join root!
  defp apply_join(_config, query, :selecto_root) do
    query
  end

  defp apply_join(config, query, join) do
    join_map = config.joins[join]

    from({^join_map.requires_join, par} in query,
      left_join: b in ^join_map.i_am,
      as: ^join,
      on: field(par, ^join_map.owner_key) == field(b, ^join_map.my_key)
    )
  end

  ### We walk the joins pushing deps in front of joins recursively, then flatten and uniq to make final list
  defp get_join_order(joins, requested_joins) do
    requested_joins
    |> Enum.map(fn j ->
      case Map.get(joins, j, %{}) |> Map.get(:requires_join, nil) do
        nil ->
          j

        req ->
          [get_join_order(joins, [req]), req, j]
      end
    end)
    |> List.flatten()
    |> Enum.uniq()
  end

  @doc """
    Generate and run the query, returning list of maps (for now...)
  """
  def execute(selecto, opts \\ []) do
    IO.puts("Execute Query")

    {query, aliases} =
      selecto
      |> gen_query(opts)

    IO.inspect(query, label: "Exe")

    results =
      query
      |> selecto.repo.all()
      |> IO.inspect(label: "Results")

    {results, aliases}
  end

  def available_columns(selecto) do
    selecto.config.columns
  end

  def available_filters(selecto) do
    selecto.config.filters
  end
end