#!/bin/sh

# shellcheck disable=SC2119,SC2120

# shellcheck source=../lib/load-configuration
. "${0%/*}/../lib/load-configuration"

exec 9> "${build_list_lock_file}"
if ! verbose_flock -n 9; then
  >&2 echo 'Cannot get build-list lock.'
  exit 1
fi
exec 8> "${sanity_check_lock_file}"
if ! verbose_flock -s -n 9; then
  >&2 echo 'Cannot get sanity-check lock.'
  exit 1
fi
exec 7> "${package_database_lock_file}"
if ! verbose_flock -s -n 7; then
  >&2 echo 'Cannot get package-database lock.'
  exit 1
fi

#shellcheck disable=SC2016
{
  # calculate_maximal_moveable_set
  # stores results in `moveable_bpir` and `replaced_bpir`
  # Give a maximal list of packages to be moved, while implementing the
  # condition from db-update:

  # Every package which is replaced[1], must have its provided install_targets:
  #   a) provided by another moved or not-replaced package or
  #   b) not required by any not-replaced package.

  # Every package being moved needs to have all dependencies
  # installable in the target repository.

  printf 'DELIMITER //\n'
  printf 'DROP PROCEDURE IF EXISTS `calculate_maximal_moveable_set`//\n'
  printf 'CREATE PROCEDURE `calculate_maximal_moveable_set`('
    printf 'IN `arch_id` MEDIUMINT,'
    printf 'IN `from_stability` MEDIUMINT'
  printf ')\n'
  printf 'BEGIN\n'
  # variable to store count of changed rows
  printf 'DECLARE row_count_saved INT DEFAULT 0;\n'
  for copy in '' '_copy' '_copy2'; do
    printf 'DROP TEMPORARY TABLE IF EXISTS `%s_bpir%s`;\n' \
      'moveable' "${copy}" \
      'replaced' "${copy}"
    printf 'CREATE TEMPORARY TABLE `replaced_bpir%s` (`id` BIGINT, `replaced_by` BIGINT, UNIQUE KEY (`id`));\n' \
      "${copy}"
    if [ "${copy}" = '_copy2' ]; then
      continue
    fi
    printf 'CREATE TEMPORARY TABLE `moveable_bpir%s` (`id` BIGINT, `to_repository` MEDIUMINT, UNIQUE KEY (`id`));\n' \
      "${copy}"
  done
  # TODO: `package_blobs` needs too much space!
  printf 'DROP TEMPORARY TABLE IF EXISTS `package_blobs`;\n'
  printf 'CREATE TEMPORARY TABLE `package_blobs` (`ps_a` BIGINT, `ps_b` BIGINT, UNIQUE KEY `content` (`ps_a`,`ps_b`));\n'
  # all packages being vaguely relevant enter `package_blobs`, because
  # they might block any other package from moving
  printf 'INSERT IGNORE INTO `package_blobs` (`ps_a`,`ps_b`)'
  printf ' SELECT `a_ps`.`id`,`b_ps`.`id`'
  printf ' FROM `package_sources` AS `a_ps`'
  printf ' JOIN `package_sources` AS `b_ps`'
  printf ' ON UNIX_TIMESTAMP(`a_ps`.`commit_time`) - UNIX_TIMESTAMP(`b_ps`.`commit_time`) BETWEEN -10 AND 10'
  printf ' AND `a_ps`.`commit_time`!="0000-00-00 00:00:00"'
  printf ' AND `b_ps`.`commit_time`!="0000-00-00 00:00:00"'
  mysql_join_package_sources_build_assignments 'a_ps' 'a_ba'
  mysql_join_package_sources_build_assignments 'b_ps' 'b_ba'
  mysql_join_build_assignments_binary_packages 'a_ba' 'a_bp'
  mysql_join_build_assignments_binary_packages 'b_ba' 'b_bp'
  mysql_join_binary_packages_binary_packages_in_repositories 'a_bp' 'a_bpir'
  mysql_join_binary_packages_in_repositories_repositories 'a_bpir' 'a_r'
  mysql_join_binary_packages_binary_packages_in_repositories 'b_bp' 'b_bpir'
  mysql_join_binary_packages_in_repositories_repositories 'b_bpir' 'b_r'
  printf ' WHERE `a_r`.`stability` = `from_stability`'
  printf ' AND `a_r`.`architecture` = `arch_id`'
  printf ' AND `b_r`.`stability` = `from_stability`'
  printf ' AND `b_r`.`architecture` = `arch_id`;\n'
  # these packages are considered for moving:
  printf 'INSERT IGNORE INTO `moveable_bpir` (`id`,`to_repository`)'
  printf ' SELECT `binary_packages_in_repositories`.`id`,`repository_moves`.`to_repository`'
  printf ' FROM `binary_packages_in_repositories`'
  mysql_join_binary_packages_in_repositories_binary_packages
  mysql_join_binary_packages_in_repositories_repositories
  mysql_join_repositories_repository_stabilities
  mysql_join_binary_packages_build_assignments
  mysql_join_build_assignments_package_sources
  mysql_join_package_sources_upstream_repositories
  mysql_join_upstream_repositories_repository_moves
  printf ' AND `repository_moves`.`from_repository`=`binary_packages_in_repositories`.`repository`'
  # correct stability: "testing"/"staging" - as chosen
  printf ' WHERE `repository_stabilities`.`id`=`from_stability`'
  printf ' AND `repositories`.`architecture`=`arch_id`'
  printf ' AND ('
    # this should not be replaced by ${repository_stability_ids__staging},
    # because it may change
    printf '`repository_stabilities`.`name`="staging"'
    # "testing" packages must be tested
    printf ' OR `binary_packages`.`is_tested`'
  printf ')'
  # no open issues
  printf ' AND NOT `binary_packages`.`has_issues`'
  printf ';\n'

  # add all considered packages, so we move split packages together, too
  printf 'INSERT IGNORE INTO `package_blobs` (`ps_a`,`ps_b`)'
  printf ' SELECT `package_sources`.`id`,`package_sources`.`id`'
  printf ' FROM `package_sources`'
  mysql_join_package_sources_build_assignments
  mysql_join_build_assignments_binary_packages
  mysql_join_binary_packages_binary_packages_in_repositories
  mysql_join_binary_packages_in_repositories_repositories
  printf ' WHERE `repositories`.`stability`=`from_stability`'
  printf ' AND `repositories`.`architecture`=`arch_id`;\n'

  # these packages are considered for being replaced:
  # for each moved package
  printf 'INSERT IGNORE INTO `replaced_bpir` (`id`,`replaced_by`)'
  printf ' SELECT `r_bpir`.`id`,`m_bpir`.`id`'
  printf ' FROM `moveable_bpir`'
  printf ' JOIN `binary_packages_in_repositories` AS `m_bpir`'
  printf ' ON `m_bpir`.`id`=`moveable_bpir`.`id`'
  mysql_join_binary_packages_in_repositories_binary_packages 'm_bpir' 'm_bp'
  # in all repositories more stable than its target repository
  printf ' JOIN `repositories` AS `m_to_r`'
  printf ' ON `moveable_bpir`.`to_repository`=`m_to_r`.`id`'
  printf ' JOIN `repository_stability_relations` AS `rsr`'
  printf ' ON `rsr`.`less_stable`=`m_to_r`.`stability`'
  printf ' JOIN `repositories` AS `r_r`'
  printf ' ON `rsr`.`more_stable`=`r_r`.`stability`'
  printf ' AND `r_r`.`architecture`=`m_to_r`.`architecture`'
  mysql_join_repositories_binary_packages_in_repositories 'r_r' 'r_bpir'
  mysql_join_binary_packages_in_repositories_binary_packages 'r_bpir' 'r_bp'
  # all packages with identical names
  printf ' AND `r_bp`.`pkgname`=`m_bp`.`pkgname`;\n'

  # create copies of our temporary tables *yuck*
  for table in 'replaced' 'moveable'; do
    for copy in '' '2'; do
      if [ "${table}" = 'moveable' ] && \
        [ "${copy}" = '2' ]; then
        continue
      fi
      printf 'DELETE FROM `%s_bpir_copy%s`;\n' "${table}" "${copy}"
      printf 'INSERT IGNORE INTO `%s_bpir_copy%s`' "${table}" "${copy}"
      printf ' SELECT `%s_bpir`.*' "${table}"
      printf ' FROM `%s_bpir`;\n' "${table}"
    done
  done

  # only the least stable of equally named to-be-replaced packages is
  # kept
  printf 'DELETE `replaced_bpir`'
  printf ' FROM `replaced_bpir`'
  printf ' JOIN `binary_packages_in_repositories` AS `not_r_bpir`'
  printf ' ON `not_r_bpir`.`id`=`replaced_bpir`.`id`'
  mysql_join_binary_packages_in_repositories_binary_packages 'not_r_bpir' 'not_r_bp'
  mysql_join_binary_packages_in_repositories_repositories 'not_r_bpir' 'not_r_r'
  printf ' JOIN `binary_packages` AS `r_bp`'
  printf ' ON `r_bp`.`pkgname`=`not_r_bp`.`pkgname`'
  printf ' AND `r_bp`.`id`!=`not_r_bp`.`id`'
  mysql_join_binary_packages_binary_packages_in_repositories 'r_bp' 'r_bpir'
  printf ' JOIN `replaced_bpir_copy`'
  printf ' ON `r_bpir`.`id`=`replaced_bpir_copy`.`id`'
  mysql_join_binary_packages_in_repositories_repositories 'r_bpir' 'r_r'
  printf ' AND `not_r_r`.`architecture`=`r_r`.`architecture`'
  printf ' JOIN `repository_stability_relations` AS `rsr`'
  printf ' ON `rsr`.`less_stable`=`r_r`.`stability`'
  printf ' AND `rsr`.`more_stable`=`not_r_r`.`stability`;\n'

  # now we delete all unreplaceable and unmoveable packages from the respective
  # list until no further changes are required, e.g. until all packages are
  # moveable or replaceable respectively
  printf 'REPEAT\n'
  printf 'SET row_count_saved = 0;\n'

  # create copies of our temporary tables *yuck*
  for table in 'replaced' 'moveable'; do
    for copy in '' '2'; do
      if [ "${table}" = 'moveable' ] && \
        [ "${copy}" = '2' ]; then
        continue
      fi
      printf 'DELETE FROM `%s_bpir_copy%s`;\n' "${table}" "${copy}"
      printf 'INSERT IGNORE INTO `%s_bpir_copy%s`' "${table}" "${copy}"
      printf ' SELECT `%s_bpir`.*' "${table}"
      printf ' FROM `%s_bpir`;\n' "${table}"
    done
  done
  # a package is not moveable if its dependencies are not provided ...
  printf 'DELETE `replaced_bpir`,`moveable_bpir`'
  printf ' FROM `replaced_bpir`'
  printf ' RIGHT JOIN `moveable_bpir`'
  printf ' ON `moveable_bpir`.`id`=`replaced_bpir`.`replaced_by`'
  printf ' JOIN `binary_packages_in_repositories`'
  printf ' ON `binary_packages_in_repositories`.`id`=`moveable_bpir`.`id`'
  printf ' JOIN `repositories` AS `target_repositories`'
  printf ' ON `moveable_bpir`.`to_repository`=`target_repositories`.`id`'
  mysql_join_binary_packages_in_repositories_binary_packages
  mysql_join_binary_packages_dependencies
  mysql_join_dependencies_dependency_types
  printf ' AND `dependency_types`.`relevant_for_binary_packages`'
  # ... by a not-deleted, "more stable" package already in place or ...
  printf ' WHERE NOT EXISTS ('
    printf 'SELECT 1'
    printf ' FROM `install_target_providers`'
    mysql_join_install_target_providers_binary_packages '' 'prov_bp'
    mysql_join_binary_packages_binary_packages_in_repositories 'prov_bp' 'prov_bpir'
    mysql_join_binary_packages_in_repositories_repositories 'prov_bpir' 'prov_r'
    printf ' JOIN `repository_stability_relations`'
    printf ' ON `prov_r`.`stability`=`repository_stability_relations`.`more_stable`'
    printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
    printf ' AND `target_repositories`.`stability`=`repository_stability_relations`.`less_stable`'
    printf ' AND `target_repositories`.`architecture`=`prov_r`.`architecture`'
    # This assumes, the moved package which replaces the install-target-provider
    # will not be deleted from the moveable list. Note, that there is no greedy
    # way to find out wether that is the case or not - so we just assume,
    # it stays on the list.
    printf ' AND NOT EXISTS ('
      printf 'SELECT 1'
      printf ' FROM `replaced_bpir_copy`'
      printf ' WHERE `replaced_bpir_copy`.`id`=`prov_bpir`.`id`'
    printf ')'
  # ... by a moved package
  printf ') AND NOT EXISTS ('
    printf 'SELECT 1'
    printf ' FROM `install_target_providers`'
    mysql_join_install_target_providers_binary_packages_in_repositories '' 'itp_bpir'
    printf ' JOIN `moveable_bpir_copy`'
    printf ' ON `moveable_bpir_copy`.`id`=`itp_bpir`.`id`'
    printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
  printf ');\n'
  printf 'SET row_count_saved = row_count_saved + ROW_COUNT();\n'

  # refresh copies of our temporary tables *yuck*
  for table in 'replaced' 'moveable'; do
    for copy in '' '2'; do
      if [ "${table}" = 'moveable' ] && \
        [ "${copy}" = '2' ]; then
        continue
      fi
      printf 'DELETE FROM `%s_bpir_copy%s`;\n' "${table}" "${copy}"
      printf 'INSERT IGNORE INTO `%s_bpir_copy%s`' "${table}" "${copy}"
      printf ' SELECT `%s_bpir`.*' "${table}"
      printf ' FROM `%s_bpir`;\n' "${table}"
    done
  done

  # TODO: we might want to deliberately break packages that fail(ed) to build
  # for quite some time

  # packages which should not be replaced: ones providing something that is:
  # a) still needed by a not-replaced package x "less stable" than the target repository and
  # b) not provided by:
  #   1) a moved package or
  #   2) any current, not-replaced package in a repository more stable than x's repository
  #
  # Note, that this is not 100% clean from an academic point of view:
  # It might require _downgrading_ of a package to keep fulfilling a dependency
  # and it might require to do so _without_ any chance for the user to notice,
  # because there may be more "dependencies" in the database than in the package files.
  #
  # However, in practice both should not happen.
  #
  printf 'DELETE `replaced_bpir`,`moveable_bpir`'
  printf ' FROM `replaced_bpir`'
  printf ' JOIN `moveable_bpir`'
  printf ' ON `replaced_bpir`.`replaced_by`=`moveable_bpir`.`id`'
  printf ' JOIN `binary_packages_in_repositories` AS `repl_bpir`'
  printf ' ON `repl_bpir`.`id`=`replaced_bpir`.`id`'
  mysql_join_binary_packages_in_repositories_install_target_providers 'repl_bpir'
  mysql_join_binary_packages_in_repositories_repositories 'repl_bpir' 'repl_r'
  mysql_join_install_target_providers_dependencies_with_versions
  printf ' AND NOT EXISTS ('
    # dependencies of replaced packages don't matter
    printf 'SELECT 1'
    printf ' FROM `replaced_bpir_copy`'
    printf ' JOIN `binary_packages_in_repositories` AS `repl_bpir_copy`'
    printf ' ON `repl_bpir_copy`.`id`=`replaced_bpir_copy`.`id`'
    mysql_join_binary_packages_in_repositories_repositories 'repl_bpir_copy' 'repl_r_copy'
    printf ' WHERE `repl_bpir_copy`.`package`=`dependencies`.`dependent`'
    printf ' AND `repl_r_copy`.`architecture`=`repl_r`.`architecture`'
  printf ')'
  mysql_join_dependencies_dependency_types
  # consider only runtime dependencies
  printf ' AND `dependency_types`.`relevant_for_binary_packages`'
  mysql_join_dependencies_binary_packages '' 'req_bp'
  # we need to check whether req_bp's dependency is (un)critical
  mysql_join_binary_packages_binary_packages_in_repositories 'req_bp' 'req_bpir'
  mysql_join_binary_packages_in_repositories_repositories 'req_bpir' 'req_r'
  printf ' AND `repl_r`.`architecture`=`req_r`.`architecture`'
  # dependent package is "less stable" than dependency
  printf ' JOIN `repository_stability_relations` AS `repl_rr`'
  printf ' ON `repl_rr`.`more_stable`=`repl_r`.`stability`'
  printf ' AND `repl_rr`.`less_stable`=`req_r`.`stability`'
  # a) ^
  printf ' WHERE NOT EXISTS ('
    # no moved package ...
    printf 'SELECT 1'
    printf ' FROM `moveable_bpir_copy`'
    printf ' JOIN `binary_packages_in_repositories` AS `subst_bpir`'
    printf ' ON `subst_bpir`.`id`=`moveable_bpir_copy`.`id`'
    mysql_join_binary_packages_in_repositories_install_target_providers 'subst_bpir' 'subst_itp'
    mysql_join_binary_packages_in_repositories_repositories 'subst_bpir' 'subst_r'
    # ... provides the same
    printf ' WHERE `subst_itp`.`install_target`=`install_target_providers`.`install_target`'
    printf ' AND `subst_r`.`architecture`=`repl_r`.`architecture`'
  # b) 1) ^
  printf ') AND NOT EXISTS ('
    # no current package ...
    printf 'SELECT 1'
    printf ' FROM `binary_packages_in_repositories` AS `subst_bpir`'
    mysql_join_binary_packages_in_repositories_install_target_providers 'subst_bpir' 'subst_itp'
    # ... in a repository ...
    mysql_join_binary_packages_in_repositories_repositories 'subst_bpir' 'subst_r'
    # ... more stable ...
    printf ' JOIN `repository_stability_relations` AS `subst_rr`'
    printf ' ON `subst_rr`.`more_stable`=`subst_r`.`stability`'
    # ... than x's repository ...
    printf ' WHERE `subst_rr`.`less_stable`=`repl_r`.`stability`'
    printf ' AND `subst_r`.`architecture`=`repl_r`.`architecture`'
    # (architecture check is not necessary, as repositories of different
    # architectures are not less or more stable than each other)
    printf ' AND NOT EXISTS ('
    # ... and which is not replaced ...
      printf 'SELECT 1'
      printf ' FROM `replaced_bpir_copy2`'
      printf ' WHERE `replaced_bpir_copy2`.`id`=`subst_bpir`.`id`'
    printf ')'
    # ... and provides the same
    printf ' AND `subst_itp`.`install_target`=`install_target_providers`.`install_target`'
    # b) 2) ^
  printf ');\n'
  printf 'SET row_count_saved = row_count_saved + ROW_COUNT();\n'

  # refresh copies of our temporary tables *yuck*
  for table in 'replaced' 'moveable'; do
    for copy in '' '2'; do
      if [ "${table}" = 'moveable' ] && \
        [ "${copy}" = '2' ]; then
        continue
      fi
      printf 'DELETE FROM `%s_bpir_copy%s`;\n' "${table}" "${copy}"
      printf 'INSERT IGNORE INTO `%s_bpir_copy%s`' "${table}" "${copy}"
      printf ' SELECT `%s_bpir`.*' "${table}"
      printf ' FROM `%s_bpir`;\n' "${table}"
    done
  done

  # remove all packages of a blob, where some part cannot be moved
  printf 'DELETE `replaced_bpir`,`moveable_bpir`'
  printf ' FROM `replaced_bpir`'
  printf ' RIGHT JOIN `moveable_bpir`'
  printf ' ON `replaced_bpir`.`replaced_by`=`moveable_bpir`.`id`'
  printf ' JOIN `binary_packages_in_repositories`'
  printf ' ON `binary_packages_in_repositories`.`id`=`moveable_bpir`.`id`'
  mysql_join_binary_packages_in_repositories_binary_packages
  mysql_join_binary_packages_build_assignments
  printf ' JOIN `package_blobs`'
  printf ' ON `build_assignments`.`package_source`=`package_blobs`.`ps_a`'
  printf ' JOIN `build_assignments` AS `bl_ba`'
  printf ' ON `bl_ba`.`package_source`=`package_blobs`.`ps_b`'
  printf ' AND `bl_ba`.`architecture`=`build_assignments`.`architecture`'
  mysql_join_build_assignments_binary_packages 'bl_ba' 'bl_bp'
  mysql_join_binary_packages_binary_packages_in_repositories 'bl_bp' 'bl_bpir'
  printf ' WHERE NOT EXISTS ('
    printf 'SELECT 1'
    printf ' FROM `moveable_bpir_copy`'
    printf ' WHERE `moveable_bpir_copy`.`id`=`bl_bpir`.`id`'
  printf ');\n'
  printf 'SET row_count_saved = row_count_saved + ROW_COUNT();\n'

  printf 'UNTIL row_count_saved=0\n'
  printf 'END REPEAT;\n'
  # we now need to remove the replaced_bpir which are not actually to be
  # replaced, but simply hidden

  # refresh copies of our temporary tables *yuck*
  for table in 'replaced' 'moveable'; do
    for copy in '' '2'; do
      if [ "${table}" = 'moveable' ] && \
        [ "${copy}" = '2' ]; then
        continue
      fi
      printf 'DELETE FROM `%s_bpir_copy%s`;\n' "${table}" "${copy}"
      printf 'INSERT IGNORE INTO `%s_bpir_copy%s`' "${table}" "${copy}"
      printf ' SELECT `%s_bpir`.*' "${table}"
      printf ' FROM `%s_bpir`;\n' "${table}"
    done
  done

  printf 'DELETE `replaced_bpir`'
  printf ' FROM `replaced_bpir`'
  printf ' JOIN `moveable_bpir`'
  printf ' ON `replaced_bpir`.`replaced_by`=`moveable_bpir`.`id`'
  printf ' JOIN `binary_packages_in_repositories` AS `r_bpir`'
  printf ' ON `replaced_bpir`.`id`=`r_bpir`.`id`'
  mysql_join_binary_packages_in_repositories_repositories 'r_bpir' 'r_r'
  printf ' JOIN `repositories` AS `m_to_r`'
  printf ' ON `m_to_r`.`id`=`moveable_bpir`.`to_repository`'
  printf ' WHERE `m_to_r`.`stability`!=`r_r`.`stability`;\n'

  for table in 'moveable' 'replaced'; do
    for copy in '' '2'; do
      if [ "${table}" = 'moveable' ] && \
        [ "${copy}" = '2' ]; then
        continue
      fi
      printf 'DROP TEMPORARY TABLE `%s_bpir_copy%s`;\n' \
        "${table}" "${copy}"
    done
  done
  printf 'END\n'
  printf '//\n'

  printf 'DROP PROCEDURE IF EXISTS `find_the_culprit`//\n'
  printf 'CREATE PROCEDURE `find_the_culprit`(IN `bpir` BIGINT)\n'
  printf 'find_the_culprit:BEGIN\n'
  printf 'DECLARE row_count_saved INT DEFAULT 0;\n'

  printf 'UPDATE `knots`'
  printf ' SET `knots`.`reason_length`=NULL,'
  printf ' `knots`.`relevant`=NULL,'
  printf ' `knots`.`active`=1;\n'
  printf 'DELETE FROM `edges_copy`;\n'
  printf 'INSERT INTO `edges_copy` (`cause`,`impact`,`invert`)'
  printf ' SELECT `edges`.`cause`,'
  printf '`edges`.`impact`,'
  printf '`edges`.`invert`'
  printf ' FROM `edges`;\n'

  printf 'REPEAT\n'
    printf 'UPDATE `knots_copy`'
    printf ' JOIN `knots`'
    printf ' ON `knots_copy`.`id`=`knots`.`id`'
    printf ' SET `knots_copy`.`reason_length`=`knots`.`reason_length`,'
    printf ' `knots_copy`.`active`=`knots`.`active`;\n'
    printf 'SET row_count_saved=0;\n'
    printf '%s\n' \
      '0 0 1' \
      '0 1 0' \
      '1 1 1' \
      '1 0 0' | \
      while read -r and left value; do
        printf 'UPDATE `knots`'
        if [ "${left}" = '1' ]; then
          printf ' LEFT'
        fi
        printf ' JOIN ('
          printf 'SELECT `edges`.`impact`,'
          if [ "${and}" = '1' ]; then
            printf ' MIN('
          else
            printf ' MAX('
          fi
            printf 'IF('
              printf '`knots_copy`.`reason_length` IS NULL,'
              if [ "${value}" = '1' ]; then
                printf '0,'
              else
                printf '1,'
              fi
              printf '`edges`.`invert` XOR `knots_copy`.`active`'
            printf ')'
          printf ') AS `active`,'
          if [ "${left}" = '0' ]; then
            printf ' MIN('
          else
            printf ' MAX('
          fi
            printf '`knots_copy`.`reason_length`'
          printf ') AS `reason_length`'
          printf ' FROM `edges`'
          printf ' JOIN `knots_copy`'
          printf ' ON `knots_copy`.`id`=`edges`.`cause`'
          printf ' GROUP BY `edges`.`impact`'
        printf ') AS `edges_combined`'
        printf ' ON `edges_combined`.`impact`=`knots`.`id`'
        printf ' SET `knots`.`active`=%s,' \
          "${value}"
        printf ' `knots`.`reason_length`='
        if [ "${left}" = '1' ]; then
          printf 'IFNULL('
        fi
        printf '`edges_combined`.`reason_length`+1'
        if [ "${left}" = '1' ]; then
          printf ',0)'
        fi
        printf ' WHERE'
        if [ "${and}" = '0' ]; then
          printf ' NOT'
        fi
        printf ' `knots`.`and`'
        printf ' AND '
        if [ "${left}" = '1' ]; then
          printf 'IFNULL('
        fi
        printf '`edges_combined`.`active`'
        if [ "${left}" = '1' ]; then
          printf ',%s)' \
            "${value}"
        fi
        printf '=%s;\n' \
          "${value}"
        printf 'SET row_count_saved = row_count_saved + ROW_COUNT();\n'
      done
  printf 'UNTIL (row_count_saved=0) OR EXISTS ('
    printf 'SELECT 1'
    printf ' FROM `knots`'
    printf ' WHERE `knots`.`reason_length` IS NOT NULL'
    printf ' AND `knots`.`content_id`=`bpir`'
    printf ' AND `knots`.`content_type`="bpir"'
    printf ')\n'
  printf 'END REPEAT;\n'

  printf 'UPDATE `knots_copy`'
  printf ' JOIN `knots`'
  printf ' ON `knots_copy`.`id`=`knots`.`id`'
  printf ' SET `knots_copy`.`reason_length`=`knots`.`reason_length`,'
  printf ' `knots_copy`.`active`=`knots`.`active`;\n'

  printf 'IF NOT EXISTS ('
    printf 'SELECT 1'
    printf ' FROM `knots`'
    printf ' WHERE `knots`.`reason_length` IS NOT NULL'
    printf ' AND `knots`.`content_id`=`bpir`'
    printf ' AND `knots`.`content_type`="bpir"'
  printf ') THEN\n'
    printf 'SELECT CONCAT('
      printf '"I cannot decide whether ",'
      printf '`binary_packages`.`pkgname`,'
      printf ' " can be moved or not."'
    printf ') FROM `binary_packages`'
    mysql_join_binary_packages_binary_packages_in_repositories
    printf ' WHERE `binary_packages_in_repositories`.`id`=`bpir`;\n'
    printf 'LEAVE find_the_culprit;\n'
  printf 'END IF;\n'

  printf 'IF ('
    printf 'SELECT `knots`.`active`'
    printf ' FROM `knots`'
    printf ' WHERE `knots`.`reason_length` IS NOT NULL'
    printf ' AND `knots`.`content_id`=`bpir`'
    printf ' AND `knots`.`content_type`="bpir"'
  printf ') THEN\n'
    printf 'SELECT CONCAT('
      printf '`binary_packages`.`pkgname`,'
      printf ' " can be moved."'
    printf ') FROM `binary_packages`'
    mysql_join_binary_packages_binary_packages_in_repositories
    printf ' WHERE `binary_packages_in_repositories`.`id`=`bpir`;\n'
    printf 'LEAVE find_the_culprit;\n'
  printf 'END IF;\n'

  printf 'SELECT CONCAT('
    printf '`binary_packages`.`pkgname`,'
    printf ' " cannot be moved:"'
  printf ') FROM `binary_packages`'
  mysql_join_binary_packages_binary_packages_in_repositories
  printf ' WHERE `binary_packages_in_repositories`.`id`=`bpir`;\n'

  # now we mark all relevant knots
  printf 'UPDATE `knots`'
  printf ' SET `knots`.`relevant`=('
    printf '`knots`.`reason_length` IS NOT NULL'
    printf ' AND `knots`.`content_id`=`bpir`'
    printf ' AND `knots`.`content_type`="bpir"'
  printf ');\n'

  printf 'REPEAT\n'
    printf 'SET row_count_saved=0;\n'

    printf 'UPDATE `knots_copy`'
    printf ' JOIN `knots`'
    printf ' ON `knots_copy`.`id`=`knots`.`id`'
    printf ' SET `knots_copy`.`relevant`=`knots`.`relevant`;\n'

    printf 'UPDATE `knots`'
    printf ' JOIN `edges`'
    printf ' ON `knots`.`id`=`edges`.`cause`'
    printf ' JOIN `knots_copy`'
    printf ' ON `knots_copy`.`id`=`edges`.`impact`'
    printf ' SET `knots`.`relevant`=1'
    printf ' WHERE `knots_copy`.`relevant`'
    printf ' AND NOT `knots`.`relevant`'
    printf ' AND NOT ('
      printf '`edges`.`invert`'
      printf ' XOR `knots`.`active`'
      printf ' XOR `knots_copy`.`active`'
    printf ') AND ('
      printf '`knots_copy`.`and`=`knots_copy`.`active`'
      printf ' OR `knots_copy`.`reason_length`=`knots`.`reason_length`+1'
    printf ');\n'

    printf 'SET row_count_saved = row_count_saved + ROW_COUNT();\n'
  printf 'UNTIL row_count_saved=0\n'
  printf 'END REPEAT;\n'

  for copy in '' '_copy'; do
    printf 'CREATE TEMPORARY TABLE `knot_names%s` (' \
      "${copy}"
    printf '`id` BIGINT NOT NULL,'
    printf ' `name` VARCHAR (128),'
    printf ' `and` BIT,'
    printf ' `active` BIT,'
    printf ' `relevant` BIT,'
    printf ' `reason_length` MEDIUMINT,'
    printf ' UNIQUE KEY (`id`)'
    printf ');\n'
  done

  printf 'INSERT INTO `knot_names` (`id`,`name`,`reason_length`,`and`,`relevant`,`active`)'
  printf ' SELECT `knots`.`id`,'
  printf ' `install_targets`.`name`,'
  printf ' `knots`.`reason_length`,'
  printf ' `knots`.`and`,'
  printf ' `knots`.`relevant`,'
  printf ' `knots`.`active`'
  printf ' FROM `knots`'
  printf ' JOIN `install_targets`'
  printf ' ON `knots`.`content_id`=`install_targets`.`id`'
  printf ' AND `knots`.`content_type`="it";\n'

  printf 'INSERT INTO `knot_names` (`id`,`name`,`reason_length`,`and`,`relevant`,`active`)'
  printf ' SELECT `knots`.`id`,'
  printf ' CONCAT('
    printf '`r_a`.`name`,"/",'
    printf '`repositories`.`name`,"/",'
    mysql_package_name_query
  printf ') AS `name`,'
  printf ' `knots`.`reason_length`,'
  printf ' `knots`.`and`,'
  printf ' `knots`.`relevant`,'
  printf ' `knots`.`active`'
  printf ' FROM `knots`'
  printf ' JOIN `binary_packages_in_repositories`'
  printf ' ON `knots`.`content_id`=`binary_packages_in_repositories`.`id`'
  printf ' AND `knots`.`content_type`="bpir"'
  mysql_join_binary_packages_in_repositories_binary_packages
  mysql_join_binary_packages_in_repositories_repositories
  mysql_join_repositories_architectures '' 'r_a'
  mysql_join_binary_packages_architectures
  printf ';\n'

  printf 'INSERT INTO `knot_names_copy`'
  printf ' SELECT * FROM `knot_names`;\n'

  printf 'SELECT CONCAT('
    printf 'IF(`impact`.`and`,"(and) ","(or) "),'
    printf '`impact`.`name`,'
    printf '" ",IF(`impact`.`active`,"1","0"),'
    printf 'IF(`edges`.`invert`," -NOT-> "," --> "),'
    printf '`cause`.`name`,'
    printf '" ",IF(`cause`.`active`,"1","0")'
  printf ') FROM `edges`'
  printf ' JOIN `knot_names` AS `cause`'
  printf ' ON `cause`.`id`=`edges`.`cause`'
  printf ' JOIN `knot_names_copy` AS `impact`'
  printf ' ON `impact`.`id`=`edges`.`impact`'
  printf ' WHERE `cause`.`relevant`'
  printf ' AND `impact`.`relevant`;\n'

  printf 'DROP TEMPORARY TABLE `knot_names`;\n'

  printf 'END\n'
  printf '//\n'

  printf 'DROP PROCEDURE IF EXISTS `blacklist_packages`//\n'
  printf 'CREATE PROCEDURE `blacklist_packages`()\n'
  printf 'blacklist_packages:BEGIN\n'

  for suffix in '' '_copy'; do
    printf 'CREATE TEMPORARY TABLE `bl%s` (' \
      "${suffix}"
      printf '`arch` SMALLINT,'
      printf '`pkgbase` VARCHAR(64),'
      printf 'UNIQUE KEY `content`(`arch`,`pkgbase`)'
    printf ');\n'
  done
  printf 'INSERT IGNORE INTO `bl`(`arch`,`pkgbase`)'
  printf ' SELECT'
  printf ' `architectures`.`id`,'
  printf '`blacklist`.`pkgbase`'
  printf ' FROM `blacklist`'
  printf ' JOIN `architectures`'
  printf ' ON `architectures`.`name`=`blacklist`.`arch`;\n'

  printf 'REPEAT\n'

    printf 'DELETE FROM `bl_copy`;\n'
    printf 'INSERT IGNORE INTO `bl_copy` (`arch`,`pkgbase`)'
    printf ' SELECT'
    printf ' `bl`.`arch`,'
    printf ' `bl`.`pkgbase`'
    printf ' FROM `bl`;\n'

    printf 'INSERT IGNORE INTO `bl`(`arch`,`pkgbase`)'
    printf ' SELECT'
    printf ' `architecture_compatibilities`.`built_for`,'
    printf '`bl_copy`.`pkgbase`'
    printf ' FROM `bl_copy`'
    printf ' JOIN `architecture_compatibilities`'
    printf ' ON ('
      printf '`architecture_compatibilities`.`runs_on`=`bl_copy`.`arch`'
      # shellcheck disable=SC2154
      printf ' OR `bl_copy`.`arch`=%s' \
        "${architecture_ids__any}"
    printf ') AND `architecture_compatibilities`.`built_for`!=%s;\n' \
      "${architecture_ids__any}"

    printf 'DELETE FROM `bl_copy`;\n'
    printf 'INSERT IGNORE INTO `bl_copy` (`arch`,`pkgbase`)'
    printf ' SELECT'
    printf ' `bl`.`arch`,'
    printf ' `bl`.`pkgbase`'
    printf ' FROM `bl`;\n'

    printf 'INSERT IGNORE INTO `bl` (`arch`,`pkgbase`)'
    printf ' SELECT'
    printf ' `a_bp`.`architecture`,'
    printf '`a_ps`.`pkgbase`'
    printf ' FROM `package_sources` AS `a_ps`'
    mysql_join_package_sources_build_assignments 'a_ps' 'a_ba'
    mysql_join_build_assignments_binary_packages 'a_ba' 'a_bp'
    mysql_join_binary_packages_architectures 'a_bp' 'a_a'
    mysql_join_binary_packages_dependencies 'a_bp'
    mysql_join_dependencies_dependency_types
    printf ' AND (`dependency_types`.`relevant_for_building`'
    printf ' OR `dependency_types`.`relevant_for_binary_packages`)'
    # TODO: This should be corrected at the root: automatic install targets, which are bogus should
    #       not be added in the first place - but how do we detect that? -> add exceptions!
    mysql_join_dependencies_install_target_providers '' 'itp_dummy'
    mysql_join_install_target_providers_binary_packages 'itp_dummy' 'itp_bp_dummy'
    printf ' AND ('
      printf '`itp_bp_dummy`.`architecture`=`a_ba`.`architecture`'
      printf ' OR `a_ba`.`architecture`=%s' \
        "${architecture_ids__any}"
      printf ' OR `itp_bp_dummy`.`architecture`=%s' \
        "${architecture_ids__any}"
    printf ')'
    printf ' LEFT JOIN ('
      printf 'SELECT DISTINCT'
        printf ' `install_target_providers`.`install_target`,'
        printf '`architectures`.`id` AS `architecture`'
      printf ' FROM `install_target_providers`'
      mysql_join_install_target_providers_binary_packages
      mysql_join_binary_packages_build_assignments
      mysql_join_build_assignments_package_sources
      printf ' JOIN `architectures`'
      printf ' ON ('
        printf '`architectures`.`id`=`binary_packages`.`architecture`'
        printf ' OR `binary_packages`.`architecture`=%s' \
          "${architecture_ids__any}"
        printf ' OR `architectures`.`id`=%s' \
          "${architecture_ids__any}"
      printf ')'
      printf ' LEFT JOIN `bl_copy`'
      printf ' ON `bl_copy`.`pkgbase`=`package_sources`.`pkgbase`'
      printf ' AND ('
        printf '`bl_copy`.`arch`=`build_assignments`.`architecture`'
        printf ' OR `bl_copy`.`arch`=%s' \
          "${architecture_ids__any}"
        # we intentionally do not allow arch-specific packages to cause
        # "any"-packages to be blacklisted (this is seldom and makes the
        # query quite complex, because we must check if /all/
        # arch-specific packages are unavailable)
      printf ')'
      # we're only interested in those which are not (yet) blacklisted
      printf ' WHERE `bl_copy`.`pkgbase` IS NULL'
    printf ') AS `itp_query`' # list of not-blacklisted install targets
    printf ' ON `itp_query`.`install_target`=`dependencies`.`depending_on`'
    printf ' AND `itp_query`.`architecture`=`a_ba`.`architecture`'
    # we only add those to the blacklist, that have not install_target_provider
    printf ' WHERE `itp_query`.`install_target` IS NULL'
    printf ';\n'

  printf 'UNTIL ROW_COUNT()=0\n'
  printf 'END REPEAT;\n'

  printf 'SELECT'
  printf ' `architectures`.`name`,'
  printf '`bl`.`pkgbase`'
  printf ' FROM `bl`'
  printf ' JOIN `architectures`'
  printf ' ON `architectures`.`id`=`bl`.`arch`'
  printf ';\n'

  printf 'DROP TEMPORARY TABLE `bl%s`;\n' \
    '' \
    '_copy'

  printf 'END\n'
  printf '//\n'

  printf 'DELIMITER ;\n'

  if [ "x$1" != 'x-no-privileges' ]; then
    printf 'GRANT %s ON %s TO '"'"'buildmaster'"'"'@'"'"'localhost'"'"';\n' \
      'CREATE ROUTINE' 'buildmaster.*' \
      'CREATE TEMPORARY TABLES' 'buildmaster.*' \
      'EXECUTE' 'buildmaster.*' \
      'RELOAD' '*.*' \
      'SELECT' 'buildmaster.*' \
      'SELECT' 'mysql.proc' \
      'SHOW VIEW' 'buildmaster.*' \
      'UPDATE' 'buildmaster.*'
    printf 'GRANT %s ON %s TO '"'"'replikat'"'"'@'"'"'localhost'"'"';\n' \
      'REPLICATION SLAVE' '*.*'
    printf 'GRANT %s ON %s TO '"'"'mysqldump'"'"'@'"'"'localhost'"'"';\n' \
      'RELOAD' '*.*' \
      'FILE' '*.*'
    printf 'GRANT %s ON `buildmaster`.* TO '"'"'webserver'"'"'@'"'"'localhost'"'"';\n' \
      'CREATE TEMPORARY TABLES' \
      'SELECT'
    printf 'FLUSH PRIVILEGES;\n'
  fi
} | \
  mysql_run_query -u root -p