defmodule ExDbmigrate do
  @moduledoc """
  Documentation for `ExDbmigrate`.
  """

  @repo ExDbmigrate.Config.repo()

  @doc """
  List the foreign keys for specified table.

  ## Examples

      iex> ExDbmigrate.list_foreign_keys("catalog_metas")
      [%{column_name: "product_id", constraint_name: "catalog_metas_product_id_fkey", foreign_column_name: "id", foreign_table_name: "catalog_products", foreign_table_schema: "public", table_name: "catalog_metas", table_schema: "public"}
      ]

  """
  def list_foreign_keys(table) do
    query = "
SELECT
    tc.table_schema,
    tc.constraint_name,
    tc.table_name,
    kcu.column_name,
    ccu.table_schema AS foreign_table_schema,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM
    information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
      AND tc.table_schema = kcu.table_schema
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
      AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name='#{table}';
"
    results = Ecto.Adapters.SQL.query!(@repo, query, [])

    Enum.map(results.rows, fn rows ->
      %{
        table_schema: List.first(rows),
        constraint_name: Enum.at(rows, 1),
        table_name: Enum.at(rows, 2),
        column_name: Enum.at(rows, 3),
        foreign_table_schema: Enum.at(rows, 4),
        foreign_table_name: Enum.at(rows, 5),
        foreign_column_name: List.last(rows)
      }
    end)
  end

  @doc """
  Generate migration from config.

  ## Examples

      iex> ExDbmigrate.migration()
      ["mix phx.gen.migration CatalogMetas catalog_metas  key:string data:string product_id:integer inserted_at:naive_datetime updated_at:naive_datetime",
        "mix phx.gen.migration CatalogVideosToProduct catalog_videos_to_product  product_id:integer video_id:integer",
        "mix phx.gen.migration CatalogProducts catalog_products  name:string inserted_at:naive_datetime updated_at:naive_datetime",
        "mix phx.gen.migration CatalogVideos catalog_videos  path:string inserted_at:naive_datetime updated_at:naive_datetime"
      ]

  """
  def migration() do
    results = fetch_results()

    Enum.map(results.rows, fn r ->
      fetch_table_data(r)
      |> generate_migration_command(r)
    end)
  end

  @doc """
  Generate migration from config.

  ## Examples

      iex> ExDbmigrate.migration_relations()
      ["mix phx.gen.migration CatalogMetasRelations catalog_metasrelations product_id:references:catalog_products",
       "mix phx.gen.migration CatalogVideosToProductRelations catalog_videos_to_productrelations product_id:references:catalog_products video_id:references:catalog_videos",
       "mix phx.gen.migration CatalogProductsRelations catalog_productsrelations ",
       "mix phx.gen.migration CatalogVideosRelations catalog_videosrelations "
      ]

  """
  def migration_relations() do
    results = fetch_results()

    Enum.map(results.rows, fn r ->
      list_foreign_keys(r)
      |> generate_migration_relations_command(r)
    end)
  end

  @doc """
  Generate schema from config.

  ## Examples

      iex> ExDbmigrate.schema()
      ["mix phx.gen.schema CatalogMetas catalog_metas  key:string data:string product_id:integer inserted_at:naive_datetime updated_at:naive_datetime --no-migration",
       "mix phx.gen.schema CatalogVideosToProduct catalog_videos_to_product  product_id:integer video_id:integer --no-migration",
       "mix phx.gen.schema CatalogProducts catalog_products  name:string inserted_at:naive_datetime updated_at:naive_datetime --no-migration",
       "mix phx.gen.schema CatalogVideos catalog_videos  path:string inserted_at:naive_datetime updated_at:naive_datetime --no-migration"
      ]

  """
  def schema() do
    results = fetch_results()

    Enum.map(results.rows, fn r ->
      fetch_table_data(r)
      |> generate_schemas_command(r)
    end)
  end

  @doc """
  Generate schema from config.

  ## Examples

      iex> ExDbmigrate.html()
      ["mix phx.gen.html CatalogMetas catalog_metas  key:string data:string product_id:integer inserted_at:naive_datetime updated_at:naive_datetime",
       "mix phx.gen.html CatalogVideosToProduct catalog_videos_to_product  product_id:integer video_id:integer",
       "mix phx.gen.html CatalogProducts catalog_products  name:string inserted_at:naive_datetime updated_at:naive_datetime",
       "mix phx.gen.html CatalogVideos catalog_videos  path:string inserted_at:naive_datetime updated_at:naive_datetime"
      ]

  """
  def html() do
    results = fetch_results()

    Enum.map(results.rows, fn r ->
      fetch_table_data(r)
      |> generate_htmls_command(r)
    end)
  end

  @doc """
  Generate schema from config.

  ## Examples

      iex> ExDbmigrate.json()
      ["mix phx.gen.json CatalogMetas catalog_metas  key:string data:string product_id:integer inserted_at:naive_datetime updated_at:naive_datetime",
       "mix phx.gen.json CatalogVideosToProduct catalog_videos_to_product  product_id:integer video_id:integer",
       "mix phx.gen.json CatalogProducts catalog_products  name:string inserted_at:naive_datetime updated_at:naive_datetime",
       "mix phx.gen.json CatalogVideos catalog_videos  path:string inserted_at:naive_datetime updated_at:naive_datetime"
      ]

  """
  def json() do
    results = fetch_results()

    Enum.map(results.rows, fn r ->
      fetch_table_data(r)
      |> generate_jsons_command(r)
    end)
  end

  @doc """
  Generate schema from config.

  ## Examples

      iex> ExDbmigrate.live()
      ["mix phx.gen.live CatalogMetas catalog_metas  key:string data:string product_id:integer inserted_at:naive_datetime updated_at:naive_datetime",
       "mix phx.gen.live CatalogVideosToProduct catalog_videos_to_product  product_id:integer video_id:integer",
       "mix phx.gen.live CatalogProducts catalog_products  name:string inserted_at:naive_datetime updated_at:naive_datetime",
       "mix phx.gen.live CatalogVideos catalog_videos  path:string inserted_at:naive_datetime updated_at:naive_datetime"
      ]

  """
  def live() do
    results = fetch_results()

    Enum.map(results.rows, fn r ->
      fetch_table_data(r)
      |> generate_lives_command(r)
    end)
  end

  def fetch_results() do
    query =
      "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' and table_name != 'schema_migrations';"

    Ecto.Adapters.SQL.query!(@repo, query, [])
  end

  def fetch_table_data(r) do
    query =
      "SELECT column_name, is_nullable, data_type, ordinal_position, character_maximum_length FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name   = '#{r}'"

    Ecto.Adapters.SQL.query!(@repo, query, [])
  end

  def generate_jsons_command(data, [migration_name]) do
    migration_string =
      data.rows
      |> Enum.map(fn [id, _is_null, type, _position, _max_length] ->
        unless id == "id" do
          type = type_select(type)
          "#{id}:#{type}"
        end
      end)
      |> Enum.join(" ")

    migration_module =
      String.split(migration_name, "_")
      |> Enum.map(fn x -> String.capitalize(x) end)
      |> Enum.join("")

    "mix phx.gen.json #{migration_module} #{migration_name} #{migration_string}"
  end

  def generate_lives_command(data, [migration_name]) do
    migration_string =
      data.rows
      |> Enum.map(fn [id, _is_null, type, _position, _max_length] ->
        unless id == "id" do
          type = type_select(type)
          "#{id}:#{type}"
        end
      end)
      |> Enum.join(" ")

    migration_module =
      String.split(migration_name, "_")
      |> Enum.map(fn x -> String.capitalize(x) end)
      |> Enum.join("")

    "mix phx.gen.live #{migration_module} #{migration_name} #{migration_string}"
  end

  def generate_htmls_command(data, [migration_name]) do
    migration_string =
      data.rows
      |> Enum.map(fn [id, _is_null, type, _position, _max_length] ->
        unless id == "id" do
          type = type_select(type)
          "#{id}:#{type}"
        end
      end)
      |> Enum.join(" ")

    migration_module =
      String.split(migration_name, "_")
      |> Enum.map(fn x -> String.capitalize(x) end)
      |> Enum.join("")

    "mix phx.gen.html #{migration_module} #{migration_name} #{migration_string}"
  end

  def generate_migration_command(data, [migration_name]) do
    migration_string =
      data.rows
      |> Enum.map(fn [id, _is_null, type, _position, _max_length] ->
        unless id == "id" do
          type = type_select(type)
          "#{id}:#{type}"
        end
      end)
      |> Enum.join(" ")

    migration_module =
      String.split(migration_name, "_")
      |> Enum.map(fn x -> String.capitalize(x) end)
      |> Enum.join("")

    "mix phx.gen.migration #{migration_module} #{migration_name} #{migration_string}"
  end

  def generate_migration_relations_command(fk_data, [migration_name]) do
    migration_string =
      fk_data
      |> Enum.map(fn map ->
        "#{map.column_name}:references:#{map.foreign_table_name}"
      end)
      |> Enum.join(" ")

    migration_module =
      String.split(migration_name, "_")
      |> Enum.map(fn x -> String.capitalize(x) end)
      |> Enum.join("")

    migration_module = migration_module <> "Relations"

    migration_name = migration_name <> "relations"

    "mix phx.gen.migration #{migration_module} #{migration_name} #{migration_string}"
  end

  def generate_schemas_command(data, [migration_name]) do
    migration_string =
      data.rows
      |> Enum.map(fn [id, _is_null, type, _position, _max_length] ->
        unless id == "id" do
          type = type_select(type)
          "#{id}:#{type}"
        end
      end)
      |> Enum.join(" ")

    migration_module =
      String.split(migration_name, "_")
      |> Enum.map(fn x -> String.capitalize(x) end)
      |> Enum.join("")

    "mix phx.gen.schema #{migration_module} #{migration_name} #{migration_string} --no-migration"
  end

  def type_select(t) do
    case(t) do
      "character varying" -> "string"
      "timestamp without time zone" -> "naive_datetime"
      "timestamp with time zone" -> "utc_datetime"
      "USER-DEFINED" -> "any"
      nil -> "string"
      data -> data
    end
  end
end