class AddRepsoitoryDockerMetaTagFKey < ActiveRecord::Migration[5.2]
  def up
    Katello::RepositoryDockerMetaTag.where.not(:repository_id => Katello::Repository.select(:id)).delete_all
    Katello::RepositoryDockerMetaTag.where.not(:docker_meta_tag_id => Katello::DockerMetaTag.select(:id)).delete_all

    add_foreign_key :katello_repository_docker_meta_tags, :katello_repositories, :column => :repository_id
    add_foreign_key :katello_repository_docker_meta_tags, :katello_docker_meta_tags, :column => :docker_meta_tag_id

    if Katello::DockerMetaTag.any?
      query = "DELETE FROM katello_repository_docker_meta_tags T1
                USING   katello_repository_docker_meta_tags T2
                WHERE   T1.ctid < T2.ctid
                AND T1.repository_id  = T2.repository_id
                AND T1.docker_meta_tag_id = T2.docker_meta_tag_id;"
      ActiveRecord::Base.connection.execute(query)
    end
    add_index :katello_repository_docker_meta_tags, [:repository_id, :docker_meta_tag_id], :unique => true, :name => 'repository_docker_meta_tags_rid_dmtid'

    Katello::RepositoryDockerTag.where.not(:repository_id => Katello::Repository.select(:id)).delete_all
    # rubocop:disable Layout/LineLength
    Katello::RepositoryDockerTag.delete_by('katello_repository_docker_tags.id IN (SELECT katello_repository_docker_tags.id FROM katello_repository_docker_tags LEFT JOIN katello_docker_tags d ON katello_repository_docker_tags.docker_tag_id = d.id GROUP BY katello_repository_docker_tags.id HAVING (count(d.id) = 0))')
    # rubocop:enable Layout/LineLength

    add_foreign_key :katello_repository_docker_tags, :katello_repositories, :column => :repository_id
    add_foreign_key :katello_repository_docker_tags, :katello_docker_tags, :column => :docker_tag_id

    if Katello::DockerTag.any?
      query = "DELETE FROM katello_repository_docker_tags T1
                USING   katello_repository_docker_tags T2
                WHERE   T1.ctid < T2.ctid
                AND T1.repository_id = T2.repository_id
                AND T1.docker_tag_id = T2.docker_tag_id;"
      ActiveRecord::Base.connection.execute(query)
    end
    add_index :katello_repository_docker_tags, [:repository_id, :docker_tag_id], :unique => true, :name => 'repository_docker_tags_rid_dtid'
  end

  def down
    remove_foreign_key :katello_repository_docker_meta_tags, :katello_repositories
    remove_foreign_key :katello_repository_docker_meta_tags, :katello_docker_meta_tags
    remove_index :katello_repository_docker_meta_tags,  [:repository_id, :docker_meta_tag_id]
    remove_foreign_key :katello_repository_docker_tags, :katello_repositories
    remove_foreign_key :katello_repository_docker_tags, :katello_docker_tags
    remove_index :katello_repository_docker_tags, [:repository_id, :docker_tag_id]
  end
end