defmodule Animina.Repo.Migrations.AddPhotoFlagable do
  @moduledoc """
  Updates resources based on their most recent snapshots.

  This file was autogenerated with `mix ash_postgres.generate_migrations`
  """

  use Ecto.Migration

  def up do
    unless column_exists?(:traits_flags, :photo_flagable) do
      alter table(:traits_flags) do
        add :photo_flagable, :boolean, default: false
      end
    end

    execute """
    UPDATE traits_flags
SET photo_flagable = CASE
    WHEN traits_categories.name IN ('animals', 'sports', 'travels', 'favorite_destinations', 'at_home', 'creativity') THEN true
    ELSE false
END
FROM traits_categories
WHERE traits_flags.category_id = traits_categories.id;
    """
  end

  def down do
    if column_exists?(:traits_flags, :photo_flagable) do
      alter table(:traits_flags) do
        remove :photo_flagable
      end
    end
  end

  defp column_exists?(table, column) do
    query = """
    SELECT COUNT(*)
    FROM information_schema.columns
    WHERE table_name = '#{table}'
    AND column_name = '#{column}';
    """
    case Ecto.Adapters.SQL.query(Animina.Repo, query, []) do
      {:ok, %{rows: [[1]]}} -> true
      _ -> false
    end
  end
end
