#!/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
{
  # show_broken_packages_and_dependencies
  # gives a list of broken packages and their dependencies if they're
  # still on the build- or deletion-list
  printf 'DROP PROCEDURE IF EXISTS show_broken_packages_and_dependencies;\n'
  printf 'DELIMITER //\n'
  printf 'CREATE PROCEDURE show_broken_packages_and_dependencies()\n'
  printf 'BEGIN\n'
  printf 'CREATE TEMPORARY TABLE `%s` (`id` BIGINT, UNIQUE KEY (`id`));\n' \
    'broken_packages_and_dependencies' 'broken_packages_and_dependencies_old'
  printf 'INSERT IGNORE INTO `broken_packages_and_dependencies` (`id`)'
  printf ' SELECT `build_assignments`.`id`'
  printf ' FROM `binary_packages`'
  mysql_join_binary_packages_build_assignments
  printf ' AND `build_assignments`.`is_broken`'
  mysql_join_binary_packages_binary_packages_in_repositories
  mysql_join_binary_packages_in_repositories_repositories
  printf ' AND `repositories`.`name`="build-list";\n'
  printf 'REPEAT\n'
  printf 'INSERT IGNORE INTO `broken_packages_and_dependencies_old` (`id`)'
  printf ' SELECT `broken_packages_and_dependencies`.`id` FROM `broken_packages_and_dependencies`;\n'
  printf 'INSERT IGNORE INTO `broken_packages_and_dependencies` (`id`)'
  printf ' SELECT `new_bp`.`build_assignment`'
  printf ' FROM `broken_packages_and_dependencies_old`'
  mysql_join_build_assignments_binary_packages 'broken_packages_and_dependencies_old' 'old_bp'
  mysql_join_binary_packages_dependencies 'old_bp'
  mysql_join_dependencies_dependency_types
  printf ' AND `dependency_types`.`relevant_for_building`'
  mysql_join_dependencies_install_target_providers
  mysql_join_install_target_providers_binary_packages '' 'new_bp'
  mysql_join_binary_packages_binary_packages_in_repositories 'new_bp' 'new_bpir'
  mysql_join_binary_packages_in_repositories_repositories 'new_bpir' 'new_repo'
  printf ' AND `new_repo`.`name` IN ("build-list","deletion-list");\n'
  printf 'UNTIL ROW_COUNT()=0\n'
  printf 'END REPEAT;\n'
  printf 'SELECT '
  mysql_query_select_pkgbase_and_revision
  printf ' JOIN `broken_packages_and_dependencies` ON `broken_packages_and_dependencies`.`id`=`build_assignments`.`id`;\n'
  printf 'END\n'
  printf '//\n'
  printf 'DELIMITER ;\n'

  # calculate_dependencies_of_package_upto_first_built_one
  # save binary packages (only their `id`) in `relevant_binary_packages` and `relevant_binary_packages_copy`
  printf 'DROP PROCEDURE IF EXISTS calculate_dependencies_of_package_upto_first_built_one;\n'
  printf 'DELIMITER //\n'
  printf 'CREATE PROCEDURE calculate_dependencies_of_package_upto_first_built_one(IN `target_pkgbase` VARCHAR(64))\n'
  printf 'BEGIN\n'
  printf 'INSERT IGNORE INTO `relevant_binary_packages` (`id`)'
  printf ' SELECT `binary_packages`.`id`'
  printf ' FROM `binary_packages`'
  mysql_join_binary_packages_binary_packages_in_repositories
  mysql_join_binary_packages_in_repositories_repositories
  printf ' AND `repositories`.`name`="build-list"'
  mysql_join_binary_packages_build_assignments
  mysql_join_build_assignments_package_sources
  printf ' WHERE `package_sources`.`pkgbase`=`target_pkgbase`;\n'
  printf 'REPEAT\n'
  printf 'INSERT IGNORE INTO `relevant_binary_packages_copy` (`id`)'
  printf ' SELECT `relevant_binary_packages`.`id` FROM `relevant_binary_packages`;\n'
  printf 'INSERT IGNORE INTO `relevant_binary_packages` (`id`)'
  printf ' SELECT `install_target_providers`.`package`'
  printf ' FROM `relevant_binary_packages_copy`'
  printf ' JOIN `binary_packages` ON `relevant_binary_packages_copy`.`id`=`binary_packages`.`id`'
  mysql_join_binary_packages_binary_packages_in_repositories
  mysql_join_binary_packages_in_repositories_repositories
  printf ' AND `repositories`.`name`="build-list"'
  mysql_join_binary_packages_dependencies
  mysql_join_dependencies_dependency_types
  printf ' AND `dependency_types`.`relevant_for_building`'
  mysql_join_dependencies_install_target_providers
  printf ';\n'
  printf 'UNTIL ROW_COUNT()=0\n'
  printf 'END REPEAT;\n'
  printf 'END\n'
  printf '//\n'
  printf 'DELIMITER ;\n'

  # calculate_maximal_moveable_set
  # stores results in `moveable_binary_packages` and `replaced_binary_packages`
  # 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.

  # TODO: [1] A "replaced" package may also be in a different repository
  # e.g. if a-2 is moved from [staging] to [testing] and there is only
  # a-1 in [core], then this will be "replaced" by a-2 on a system
  # running on [testing] repositories.

  printf 'DROP PROCEDURE IF EXISTS calculate_maximal_moveable_set;\n'
  printf 'DELIMITER //\n'
  printf 'CREATE PROCEDURE calculate_maximal_moveable_set(IN `from_stability` VARCHAR(32))\n'
  printf 'BEGIN\n'
  # variables 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_binary_packages%s`;\n' \
      'moveable' "${copy}" \
      'replaced' "${copy}"
    printf 'CREATE TEMPORARY TABLE `replaced_binary_packages%s` (`id` BIGINT, `replaced_by` BIGINT, UNIQUE KEY (`id`));\n' \
      "${copy}"
    if [ "${copy}" = '_copy2' ]; then
      continue
    fi
    printf 'CREATE TEMPORARY TABLE `moveable_binary_packages%s` (`id` BIGINT, `to_repository` MEDIUMINT, UNIQUE KEY (`id`));\n' \
      "${copy}"
  done
  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'
  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'
  mysql_join_repositories_repository_stabilities 'a_r' 'a_rs'
  mysql_join_repositories_repository_stabilities 'b_r' 'b_rs'
  printf ' WHERE `a_rs`.`name` = `from_stability`'
  printf ' AND `b_rs`.`name` = `from_stability`;\n'
  # these packages are considered for moving:
  printf 'INSERT IGNORE INTO `moveable_binary_packages` (`id`,`to_repository`)'
  printf ' SELECT `binary_packages`.`id`,`repository_moves`.`to_repository`'
  printf ' FROM `binary_packages`'
  mysql_join_binary_packages_binary_packages_in_repositories
  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`.`repository`'
  # correct stability: "testing"/"staging" - as chosen
  printf ' WHERE `repository_stabilities`.`name` = `from_stability`'
  printf ' AND ('
    printf '`from_stability`="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'
  # these packages are considered for being replaced:
  # for each moved package
  printf 'INSERT IGNORE INTO `replaced_binary_packages` (`id`,`replaced_by`)'
  printf ' SELECT `r_bp`.`id`,`m_bp`.`id`'
  printf ' FROM `moveable_binary_packages`'
  printf ' JOIN `binary_packages` AS `m_bp` ON `m_bp`.`id`=`moveable_binary_packages`.`id`'
  mysql_join_binary_packages_binary_packages_in_repositories 'm_bp' 'm_bpir'
  mysql_join_binary_packages_in_repositories_repositories 'm_bpir' 'm_r'
  mysql_join_binary_packages_build_assignments 'm_bp' 'm_ba'
  mysql_join_build_assignments_package_sources 'm_ba' 'm_ps'
  mysql_join_package_sources_upstream_repositories 'm_ps' 'm_ur'
  mysql_join_upstream_repositories_repository_moves 'm_ur'
  printf ' AND `repository_moves`.`from_repository`=`m_r`.`id`'
  # in its target repository
  printf ' JOIN `repositories` AS `r_r` ON `repository_moves`.`to_repository`=`r_r`.`id`'
  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'
  # now we delete all unreplaceable and unmoveable packages from the respective
  # list until no further changes are required
  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_binary_packages_copy%s`;\n' "${table}" "${copy}"
      printf 'INSERT IGNORE INTO `%s_binary_packages_copy%s`' "${table}" "${copy}"
      printf ' SELECT `%s_binary_packages`.*' "${table}"
      printf ' FROM `%s_binary_packages`;\n' "${table}"
    done
  done
  # a package is not moveable if its dependencies are not provided ...
  printf 'DELETE `replaced_binary_packages`,`moveable_binary_packages`'
  printf ' FROM `replaced_binary_packages`'
  printf ' RIGHT JOIN `moveable_binary_packages`'
  printf ' ON `moveable_binary_packages`.`id`=`replaced_binary_packages`.`replaced_by`'
  printf ' JOIN `binary_packages` ON `binary_packages`.`id`=`moveable_binary_packages`.`id`'
  printf ' JOIN `repositories` AS `target_repositories` ON `moveable_binary_packages`.`to_repository`=`target_repositories`.`id`'
  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 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` 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 NOT EXISTS ('
      printf 'SELECT 1 FROM `replaced_binary_packages_copy`'
      printf ' WHERE `replaced_binary_packages_copy`.`id`=`prov_bp`.`id`'
    printf ')'
  # ... by a moved package
  printf ') AND NOT EXISTS ('
    printf 'SELECT 1 FROM `install_target_providers`'
    printf ' JOIN `moveable_binary_packages_copy` ON `moveable_binary_packages_copy`.`id`=`install_target_providers`.`package`'
    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_binary_packages_copy%s`;\n' "${table}" "${copy}"
      printf 'INSERT IGNORE INTO `%s_binary_packages_copy%s`' "${table}" "${copy}"
      printf ' SELECT `%s_binary_packages`.*' "${table}"
      printf ' FROM `%s_binary_packages`;\n' "${table}"
    done
  done

  # 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_binary_packages`,`moveable_binary_packages` FROM `replaced_binary_packages`'
  printf ' JOIN `moveable_binary_packages` ON `replaced_binary_packages`.`replaced_by`=`moveable_binary_packages`.`id`'
  printf ' JOIN `binary_packages` AS `repl_bp` ON `repl_bp`.`id`=`replaced_binary_packages`.`id`'
  mysql_join_binary_packages_install_target_providers 'repl_bp'
  mysql_join_install_target_providers_dependencies
  printf ' AND NOT EXISTS ('
    # dependencies of replaced packages don't matter
    printf 'SELECT 1 FROM `replaced_binary_packages_copy`'
    printf ' WHERE `replaced_binary_packages_copy`.`id`=`dependencies`.`dependent`'
  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 wether req_bp's dependency is (un)critical
  mysql_join_binary_packages_binary_packages_in_repositories 'repl_bp' 'repl_bpir'
  mysql_join_binary_packages_in_repositories_repositories 'repl_bpir' 'repl_r'
  mysql_join_binary_packages_binary_packages_in_repositories 'req_bp' 'req_bpir'
  mysql_join_binary_packages_in_repositories_repositories 'req_bpir' 'req_r'
  # 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_binary_packages_copy`'
    mysql_join_binary_packages_install_target_providers 'moveable_binary_packages_copy' 'subst_itp'
    # ... provides the same
    printf ' WHERE `subst_itp`.`install_target`=`install_target_providers`.`install_target`'
  # b) 1) ^
  printf ') AND NOT EXISTS ('
    # no current package ...
    printf 'SELECT 1'
    printf ' FROM `binary_packages` AS `subst_bp`'
    mysql_join_binary_packages_install_target_providers 'subst_bp' 'subst_itp'
    # ... in a repository ...
    mysql_join_binary_packages_binary_packages_in_repositories 'subst_bp' 'subst_bpir'
    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 NOT EXISTS ('
    # ... and which is not replaced ...
      printf 'SELECT 1 FROM `replaced_binary_packages_copy2`'
      printf ' WHERE `replaced_binary_packages_copy2`.`id`=`subst_bp`.`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_binary_packages_copy%s`;\n' "${table}" "${copy}"
      printf 'INSERT IGNORE INTO `%s_binary_packages_copy%s`' "${table}" "${copy}"
      printf ' SELECT `%s_binary_packages`.*' "${table}"
      printf ' FROM `%s_binary_packages`;\n' "${table}"
    done
  done

  printf 'DELETE `replaced_binary_packages`,`moveable_binary_packages` FROM `replaced_binary_packages`'
  printf ' RIGHT JOIN `moveable_binary_packages`'
  printf ' ON `replaced_binary_packages`.`replaced_by`=`moveable_binary_packages`.`id`'
  printf ' JOIN `binary_packages`'
  printf ' ON `binary_packages`.`id`=`moveable_binary_packages`.`id`'
  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`'
  mysql_join_build_assignments_binary_packages 'bl_ba' 'bl_bp'
  printf ' WHERE NOT EXISTS ('
    printf 'SELECT 1 FROM `moveable_binary_packages_copy`'
    printf ' WHERE `moveable_binary_packages_copy`.`id`=`bl_bp`.`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'
  for table in 'moveable' 'replaced'; do
    for copy in '' '2'; do
      if [ "${table}" = 'moveable' ] && \
        [ "${copy}" = '2' ]; then
        continue
      fi
      printf 'DROP TEMPORARY TABLE `%s_binary_packages_copy%s`;\n' \
        "${table}" "${copy}"
    done
  done
  printf 'END\n'
  printf '//\n'
  printf 'DELIMITER ;\n'

  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 '"'"'webserver'"'"'@'"'"'localhost'"'"';\n' \
    'CREATE TEMPORARY TABLES' 'buildmaster.*' \
    'SELECT' 'buildmaster.*' \
    'SHOW VIEW' 'buildmaster.*'
  printf 'FLUSH PRIVILEGES;\n'
} | \
  mysql_run_query -u root -p