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

  @repo ExDbmigrate.Config.repo()

  import Exflect

  @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 CatalogMeta catalog_metas key:string data:string product_id:integer",
       "mix phx.gen.migration CatalogVideosToProducts CatalogVideosToProduct catalog_videos_to_product product_id:integer video_id:integer",
       "mix phx.gen.migration CatalogProducts CatalogProduct catalog_products name:string",
       "mix phx.gen.migration CatalogVideos CatalogVideo catalog_videos path:string"
      ]

  """
  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.schema CatalogMetas CatalogMeta catalog_metas product_id:references:catalog_products",
      "mix phx.gen.schema CatalogVideosToProducts CatalogVideosToProduct catalog_videos_to_product product_id:references:catalog_products video_id:references:catalog_videos",
      "mix phx.gen.schema CatalogProducts CatalogProduct catalog_products ",
      "mix phx.gen.schema CatalogVideos CatalogVideo catalog_videos "]

  """
  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 CatalogMeta catalog_metas key:string data:string product_id:integer --no-migration",
      "mix phx.gen.schema CatalogVideosToProducts CatalogVideosToProduct catalog_videos_to_product product_id:integer video_id:integer --no-migration",
      "mix phx.gen.schema CatalogProducts CatalogProduct catalog_products name:string --no-migration",
      "mix phx.gen.schema CatalogVideos CatalogVideo catalog_videos path:string --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 CatalogMeta catalog_metas key:string data:string product_id:integer",
       "mix phx.gen.html CatalogVideosToProducts CatalogVideosToProduct catalog_videos_to_product product_id:integer video_id:integer",
       "mix phx.gen.html CatalogProducts CatalogProduct catalog_products name:string",
       "mix phx.gen.html CatalogVideos CatalogVideo catalog_videos path:string"
      ]

  """
  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 CatalogMeta catalog_metas key:string data:string product_id:integer",
      "mix phx.gen.json CatalogVideosToProducts CatalogVideosToProduct catalog_videos_to_product product_id:integer video_id:integer",
      "mix phx.gen.json CatalogProducts CatalogProduct catalog_products name:string",
      "mix phx.gen.json CatalogVideos CatalogVideo catalog_videos path:string"]

  """
  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 CatalogMeta catalog_metas key:string data:string product_id:integer",
      "mix phx.gen.live CatalogVideosToProducts CatalogVideosToProduct catalog_videos_to_product product_id:integer video_id:integer",
      "mix phx.gen.live CatalogProducts CatalogProduct catalog_products name:string",
      "mix phx.gen.live CatalogVideos CatalogVideo catalog_videos path:string"]

  """
  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 = migration_string(data)

    migration_module = migration_module(migration_name)

    table = table_name(migration_name)

    module_name = migration_module |> singularize()

    module = migration_module |> pluralize()

    "mix phx.gen.json #{module} #{module_name} #{table} #{migration_string}"
  end

  def generate_lives_command(data, [migration_name]) do
    migration_string = migration_string(data)

    migration_module = migration_module(migration_name)

    table = table_name(migration_name)

    module_name = migration_module |> singularize()

    module = migration_module |> pluralize()

    "mix phx.gen.live #{module} #{module_name} #{table} #{migration_string}"
  end

  def migration_module(data) do
    String.split(data, "_")
    |> Enum.map(fn x -> String.capitalize(x) end)
    |> Enum.join("")
    |> String.trim()
  end

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

  def table_name(migration_name) do
    migration_name

    String.split(migration_name, "_")
    |> Enum.map(fn x -> String.downcase(x) end)
    |> Enum.join("_")
    |> String.trim()
  end

  def generate_htmls_command(data, [migration_name]) do
    migration_string = migration_string(data)

    migration_module = migration_module(migration_name)

    table = table_name(migration_name)

    module_name = migration_module |> singularize()

    module = migration_module |> pluralize()

    "mix phx.gen.html #{module} #{module_name} #{table} #{migration_string}"
  end

  def generate_migration_command(data, [migration_name]) do
    migration_string = migration_string(data)

    migration_module = migration_module(migration_name)

    table = table_name(migration_name)

    module_name = migration_module |> singularize()

    module = migration_module |> pluralize()

    "mix phx.gen.migration #{module} #{module_name} #{table} #{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(" ")
      |> String.trim()

    migration_module = migration_module(migration_name)

    table = table_name(migration_name)

    module_name = migration_module |> singularize()

    module = migration_module |> pluralize()

    migration_module = migration_module <> "Relations"

    migration_name = String.downcase(migration_name <> "relations")

    "mix phx.gen.schema #{module} #{module_name} #{table} #{migration_string}"
  end

  def generate_schema_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(" ")
      |> String.trim()

    migration_module = migration_module(migration_name)

    table = table_name(migration_name)

    module_name = migration_module |> singularize()

    module = migration_module |> pluralize()

    migration_module = migration_module <> "Relations"

    module_name = String.downcase(module_name <> "relations")

    "mix phx.gen.schema #{module} #{module_name} #{table} #{migration_string}"
  end

  def generate_schemas_command(data, [migration_name]) do
    migration_string = migration_string(data)

    migration_module = migration_module(migration_name)

    table = table_name(migration_name)

    module_name = migration_module |> singularize()

    module = migration_module |> pluralize()

    "mix phx.gen.schema #{module} #{module_name} #{table} #{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" -> "map"
      "jsonb" -> "map"
      "ARRAY" -> "array:string"
      "any" -> "string"
      nil -> "string"
      data -> data
    end
  end
end