#!/bin/sh

# shellcheck source=conf/default.conf
. "${0%/*}/../conf/default.conf"

tmp_dir=$(mktemp -d 'tmp.bootstrap-mysql.XXXXXXXXXX' --tmpdir)
trap 'rm -rf --one-file-system "${tmp_dir}"' EXIT

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

for dir in $(ls_master_mirror 'i686'); do
  ls_master_mirror "i686/${dir}" | \
    sed '
      /\.pkg\.tar\.xz$/!d
      s|^|'"${dir}"' |
    '
done | \
  sort -k2,2 > \
  "${tmp_dir}/master-mirror-listing"

if [ ! "$1" = 'slim' ]; then
  tables=$(
    printf '%s\n' \
      'dependency_types SMALLINT' \
      ' name VARCHAR(32)' \
      ' relevant_for_building BIT' \
      ' relevant_for_binary_packages BIT' \
      ' UNIQUE name' \
      'dependencies BIGINT' \
      ' dependent BIGINT :binary_packages' \
      ' depending_on BIGINT :install_targets' \
      ' dependency_type SMALLINT :dependency_types' \
      ' UNIQUE content dependent depending_on dependency_type' \
      'install_targets BIGINT' \
      ' name VARCHAR(64)' \
      ' UNIQUE name' \
      'install_target_providers BIGINT' \
      ' package BIGINT :binary_packages' \
      ' install_target BIGINT :install_targets' \
      ' UNIQUE content package install_target' \
      'binary_packages BIGINT' \
      ' build_assignment BIGINT :build_assignments' \
      ' repository MEDIUMINT :repositories' \
      ' epoch MEDIUMINT' \
      ' pkgver VARCHAR(64)' \
      ' pkgrel MEDIUMINT' \
      ' sub_pkgrel MEDIUMINT' \
      ' has_issues BIT' \
      ' is_tested BIT' \
      ' is_to_be_deleted BIT' \
      ' pkgname VARCHAR(64)' \
      ' architecture SMALLINT :architectures' \
      ' UNIQUE content build_assignment sub_pkgrel pkgname architecture repository' \
      ' UNIQUE file_name pkgname epoch pkgver pkgrel sub_pkgrel architecture repository' \
      'repositories MEDIUMINT' \
      ' name VARCHAR(64)' \
      ' stability MEDIUMINT :repository_stabilities' \
      ' is_on_master_mirror BIT' \
      ' UNIQUE name' \
      'repository_stabilities MEDIUMINT' \
      ' name VARCHAR(32)' \
      ' bugtracker_category VARCHAR(64) NULL' \
      ' UNIQUE name' \
      'architectures SMALLINT' \
      ' name VARCHAR(16)' \
      ' UNIQUE name' \
      'build_assignments BIGINT' \
      ' package_source BIGINT :package_sources' \
      ' architecture SMALLINT :architectures' \
      ' is_blocked VARCHAR(128) NULL' \
      ' is_broken BIT' \
      ' priority SMALLINT' \
      ' UNIQUE content package_source architecture' \
      'build_dependency_loops BIGINT' \
      ' loop MEDIUMINT' \
      ' build_assignment BIGINT :build_assignments' \
      ' UNIQUE content loop build_assignment' \
      'build_slaves MEDIUMINT' \
      ' name VARCHAR(32)' \
      ' ssh_key VARCHAR(1024)' \
      ' operator VARCHAR(32)' \
      ' currently_building BIGINT NULL :build_assignments' \
      ' last_connection DATETIME' \
      ' UNIQUE name' \
      'package_sources BIGINT' \
      ' pkgbase VARCHAR(64)' \
      ' git_revision VARCHAR(40)' \
      ' mod_git_revision VARCHAR(40)' \
      ' upstream_package_repository SMALLINT :upstream_repositories' \
      ' uses_upstream BIT' \
      ' uses_modification BIT' \
      ' UNIQUE content pkgbase git_revision mod_git_revision' \
      'upstream_repositories SMALLINT' \
      ' name VARCHAR(64)' \
      ' git_repository SMALLINT :git_repositories' \
      ' UNIQUE content name git_repository' \
      'git_repositories SMALLINT' \
      ' name VARCHAR(64)' \
      ' url VARCHAR(128)' \
      ' directory VARCHAR(128)' \
      ' head VARCHAR(40)' \
      ' UNIQUE name' \
      ' UNIQUE url' \
      ' UNIQUE directory' \
      'fail_reasons SMALLINT' \
      ' name VARCHAR(32)' \
      ' identifier VARCHAR(64)' \
      ' severity SMALLINT' \
      ' UNIQUE name' \
      'failed_builds MEDIUMINT' \
      ' build_slave MEDIUMINT :build_slaves' \
      ' build_assignment BIGINT :build_assignments' \
      ' date DATETIME' \
      ' reason SMALLINT :fail_reasons' \
      ' log_file VARCHAR(512)' \
      'todos MEDIUMINT' \
      ' file VARCHAR(64)' \
      ' line MEDIUMINT' \
      ' description VARCHAR(512)'
      'todo_links MEDIUMINT' \
      ' dependent MEDIUMINT' \
      ' depending_on MEDIUMINT' \
      'repository_stability_relations MEDIUMINT' \
      ' more_stable MEDIUMINT :repository_stabilities' \
      ' less_stable MEDIUMINT :repository_stabilities' \
      'repository_moves MEDIUMINT' \
      ' from_repository MEDIUMINT :repositories' \
      ' to_repository MEDIUMINT :repositories' \
      ' upstream_package_repository SMALLINT :upstream_repositories' \
      ' UNIQUE source from_repository upstream_package_repository'
  )

  # shellcheck disable=SC2016
  {
    printf '%s\n' \
      '/*!40014 SET UNIQUE_CHECKS=0 */;' \
      '/*!40014 SET FOREIGN_KEY_CHECKS=0 */;'
    for turn in 'drop' 'create'; do # 'link'; do
      echo "${tables}" | \
        sed -n '
          /^\S/p
        ' | \
        while read -r table size; do
          case "${turn}" in
           'drop')
              printf 'DROP TABLE IF EXISTS `%s`;\n' "${table}"
            ;;
            'create'|'link')
              if [ "${turn}" = 'create' ]; then
                printf 'CREATE TABLE `%s` (\n' "${table}"
                printf '  `id` %s NOT NULL AUTO_INCREMENT,\n' "${size}"
              fi
              echo "${tables}" | \
                sed -n '
                  s/^'"$(str_to_regex "${table} ${size}")"'$//
                  T
                  :a
                    $!N
                    s/\n\S.*$//
                    $!Ta
                  s/^\n//
                  p
                ' | \
                while read -r column type rest; do
                  case "${column}" in
                    'UNIQUE')
                      if [ ! "${turn}" = 'create' ]; then
                        continue
                      fi
                      if [ -z "${rest}" ]; then
                        rest="${type}"
                      fi
                      printf '  UNIQUE KEY `%s` (' "${type}"
                      # shellcheck disable=SC2086
                      printf '`%s`,' ${rest} | \
                        sed 's|,$||'
                      printf ')'
                    ;;
                    *)
                      if [ "${turn}" = 'create' ]; then
                        printf '  `%s` %s' "${column}" "${type}"
                        if ! echo "${rest}" | \
                          grep -qwF 'NULL'; then
                          printf ' NOT NULL'
                        fi
                      else
                        echo "${rest}" | \
                          tr ' ' '\n' | \
                          sed -n 's/^://;T;p' | \
                          while read -r link; do
                            printf 'ALTER TABLE `%s` ADD FOREIGN KEY (`%s`) REFERENCES `%s` (`id`);\n' \
                              "${table}" \
                              "${column}" \
                              "${link}"
                          done
                      fi
                    ;;
                  esac
                  if [ "${turn}" = 'create' ]; then
                    printf ',\n'
                  fi
                done
              if [ "${turn}" = 'create' ]; then
                printf '  PRIMARY KEY (`id`));\n'
              fi
            ;;
            *)
              >&2 printf 'unknown turn "%s"\n' "${turn}"
              exit 2
          esac
        done
    done
    printf '%s\n' \
      '/*!40014 SET UNIQUE_CHECKS=1 */;' \
      '/*!40014 SET FOREIGN_KEY_CHECKS=1 */;'

    # 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_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_repositories 'new_bp' '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_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_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, must have its provided install_targets:
    #   a) provided by another moved or not-replaced package or
    #   b) not required by any not-replaced package.
    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'
    for table in 'moveable' 'replaced'; do
      for copy in '' '_copy'; do
        printf 'DROP TEMPORARY TABLE IF EXISTS `%s_binary_packages%s`;\n' \
          "${table}" "${copy}"
        printf 'CREATE TEMPORARY TABLE `%s_binary_packages%s` (`id` BIGINT, UNIQUE KEY (`id`));\n' \
          "${table}" "${copy}"
      done
    done
    # these packages are considered for moving:
    printf 'INSERT IGNORE INTO `moveable_binary_packages` (`id`)'
    printf ' SELECT `binary_packages`.`id`'
    printf ' FROM `binary_packages`'
    mysql_join_binary_packages_repositories
    mysql_join_repositories_repository_stabilities
    # 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`)'
    printf ' SELECT `r_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_repositories 'm_bp' '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 'r_r' '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'
    for s in 'replaced' 'moveable'; do
      printf 'DELETE FROM `%s_binary_packages_copy`;\n' "$s"
      printf 'INSERT IGNORE INTO `%s_binary_packages_copy` (`id`)' "$s"
      printf ' SELECT `%s_binary_packages`.`id`' "$s"
      printf ' FROM `%s_binary_packages`;\n' "$s"
    done
    # packages which should not be replaced:
    printf 'DELETE `replaced_binary_packages` FROM `replaced_binary_packages`'
    printf ' JOIN `binary_packages` AS `d_bp`'
    mysql_join_binary_packages_dependencies 'd_bp'
    mysql_join_binary_packages_repositories 'd_bp' 'd_r'
    # consider only dependencies which can be met at all
    printf ' AND EXISTS ('
      printf 'SELECT * FROM `install_target_providers`'
      printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
    printf ')'
    mysql_join_dependencies_dependency_types
    # only consider runtime dependencies
    printf ' AND `dependency_types`.`relevant_for_binary_packages`'
    # not provided by a not-replaced, not-less-stable package
    printf ' AND NOT EXISTS ('
      printf 'SELECT * FROM `install_target_providers`'
      mysql_join_install_target_providers_binary_packages '' 'itp_bp'
      mysql_join_binary_packages_repositories 'itp_bp' 'itp_r'
      printf ' JOIN `repository_stability_relations` ON `itp_r`.`stability`=`repository_stability_relations`.`more_stable`'
      printf ' WHERE `d_r`.`stability`=`repository_stability_relations`.`less_stable`'
      printf ' AND `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
      printf ' AND NOT EXISTS ('
        printf 'SELECT * FROM `replaced_binary_packages_copy`'
        printf ' WHERE `replaced_binary_packages_copy`.`id`=`itp_bp`.`id`'
      printf ')'
    printf ')'
    # not provided by a moved package
    printf ' AND NOT EXISTS ('
      printf 'SELECT * FROM `moveable_binary_packages`'
      mysql_join_binary_packages_install_target_providers 'moveable_binary_packages'
      printf ' WHERE `moveable_binary_packages`.`id`=`install_target_providers`.`package`'
    printf ');\n'
    # remove packages from the moveable-list which replace packages no longer on the replaced-list (e.g un-replaceable packages)
    printf 'DELETE `moveable_binary_packages` FROM `moveable_binary_packages`'
    printf ' JOIN `binary_packages` AS `m_bp` ON `m_bp`.`id`=`moveable_binary_packages`.`id`'
    mysql_join_binary_packages_repositories 'm_bp' '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`'
    printf ' JOIN `repositories` AS `r_r` ON `repository_moves`.`to_repository`=`r_r`.`id`'
    mysql_join_repositories_binary_packages 'r_r' 'r_bp'
    printf ' AND `r_bp`.`pkgname`=`m_bp`.`pkgname`'
    printf ' WHERE NOT EXISTS ('
      printf 'SELECT * FROM `replaced_binary_packages`'
      printf ' WHERE `replaced_binary_packages`.`id`=`r_bp`.`id`'
    printf ');\n'
    # actually, this only checks the count of deletions from the moveable-list, but that should be ok
    printf 'UNTIL ROW_COUNT()=0\n'
    printf 'END REPEAT;\n'
    for table in 'moveable' 'replaced'; do
      printf 'DROP TEMPORARY TABLE `%s_binary_packages_copy`;\n' \
        "${table}"
    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.*' \
      '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 -u root -p buildmaster
fi

# shellcheck disable=SC2016
{
  printf 'INSERT IGNORE INTO `architectures` (`name`) VALUES '
  printf '("%s"),' \
    'any' 'i686' | \
    sed 's|,$||'
  printf ';\n'
  printf 'INSERT IGNORE INTO `fail_reasons` (`identifier`,`name`,`severity`) VALUES '
  printf '(from_base64("%s"),"%s",%s),' \
    "$(
      printf '%s' '==> ERROR: A failure occurred in build()\.' | \
        base64 -w0
    )" 'build()' 0 \
    "$(
      printf '%s' '==> ERROR: Could not download sources\.' | \
        base64 -w0
    )" 'source' 1 \
    "$(
      printf '%s' '.*error: failed to commit transaction (invalid or corrupted package)' | \
        base64 -w0
    )" 'package-cache' 2 \
    "$(
      printf '%s' '==> ERROR: A failure occurred in prepare()\.' | \
        base64 -w0
    )" 'prepare()' 2 \
    "$(
      printf '%s' '==> ERROR: A failure occurred in package\(_\S\+\)\?()\.' | \
        base64 -w0
    )" 'package()' 3 \
    "$(
      printf '%s' '==> ERROR: A failure occurred in check()\.' | \
        base64 -w0
    )" 'check()' 4 \
    "$(
      printf '%s' '==> ERROR: '"'"'pacman'"'"' failed to install missing dependencies\.' | \
        base64 -w0
    )" 'dependencies' 1 \
    "$(
      printf '%s' '==> ERROR: Running makepkg as root is not allowed as it can cause permanent,.*' | \
        base64 -w0
    )" 'run-as-root' 1 \
    "$(
      printf '.*' | \
        base64 -w0
    )" 'unknown' 100 | \
    sed 's|,$||'
  printf ';\n'
  printf 'INSERT IGNORE INTO `git_repositories` (`name`,`url`,`directory`,`head`) VALUES'
  {
    for repo in ${repo_names}; do
      eval 'repo_path="${repo_paths__'"${repo}"'}"'
      printf '\n  ('
      printf 'from_base64("%s"),' \
        "$(
          printf '%s' "${repo}" | \
            base64 -w0
        )" \
        "$(
          git -C "${repo_path}" remote -v | \
            awk '{print $2}' | \
            tail -n1 | \
            base64_encode_each
        )" \
        "$(
          printf '%s' "${repo_path}" | \
            base64 -w0
        )" \
        "$(
          base64_encode_each < \
            "${work_dir}/${repo}.revision"
        )" | \
        sed 's|,$|),|'
    done
    printf ';\n'
  } | \
    sed '
      s|,;|;|
    '

  for repo in \
    'core:packages' \
    'extra:packages' \
    'multilib:packages' \
    'community:community'; do
    printf 'INSERT IGNORE INTO `upstream_repositories` (`name`,`git_repository`) SELECT\n'
    printf '  from_base64("%s"),`id` FROM `git_repositories` WHERE `name` = from_base64("%s");\n' \
      "$(
        printf '%s' "${repo%:*}" | \
          base64 -w0
      )" \
      "$(
        printf '%s' "${repo#*:}" | \
          base64 -w0
      )"
  done

  printf 'INSERT IGNORE INTO `build_slaves` (`name`,`ssh_key`,`operator`,`last_connection`) VALUES'
  {
    sed -n '
      s/^command="\S\+ \(\S\+\)" \S\+ \(\S\+\) \S\+$/\1 \2/
      T
      p
    ' ~/.ssh/authorized_keys | \
      while read -r name key; do
        case "${name}" in
          'nlopc'*|'rechenknecht')
            operator='deep42thought'
          ;;
          'buildknecht'*)
            operator='deep42thought/vollzornbrot'
          ;;
          'eurobuild3')
            operator='abaumann'
          ;;
          *)
            operator="${name}"
          ;;
        esac
        printf '\n  ('
        printf 'from_base64("%s"),' \
          "$(
            printf '%s' "${name}" | \
              base64 -w0
          )" \
          "$(
            printf '%s' "${key}" | \
              base64 -w0
          )" \
          "$(
            printf '%s' "${operator}" | \
              base64 -w0
          )"
        printf 'NOW()),'
      done
    printf ';\n'
  } | \
    sed 's|,;|;|'

  printf 'INSERT IGNORE INTO `repository_stabilities` (`name`,`bugtracker_category`) VALUES'
  {
    printf '\n  ("%s",%s),' \
      'stable' '"Packages: Stable"' \
      'testing' '"Packages: Testing"' \
      'staging' 'NULL' \
      'standalone' 'NULL' \
      'unbuilt' '"Packages: Build-list"' \
      'forbidden' 'NULL'
    printf ';\n'
  } | \
    sed 's|,;|;|'

  {
    printf 'INSERT IGNORE INTO `repository_stability_relations` (`more_stable`,`less_stable`)'
    printf ' SELECT `ms`.`id`,`ls`.`id`'
    printf ' FROM `repository_stabilities` AS `ms` JOIN `repository_stabilities` AS `ls`'
    printf ' WHERE '
    printf '(`ms`.`name`="%s" AND `ls`.`name`="%s") OR ' \
      'stable' 'stable' \
      'stable' 'testing' \
      'stable' 'staging' \
      'stable' 'unbuilt' \
      'stable' 'forbidden' \
      'testing' 'testing' \
      'testing' 'staging' \
      'testing' 'unbuilt' \
      'testing' 'forbidden' \
      'staging' 'staging' \
      'staging' 'unbuilt' \
      'staging' 'forbidden' \
      'unbuilt' 'forbidden' \
      'build-support' 'build-support'
    printf ';\n'
  } | \
    sed 's| OR ;|;|'

  {
    printf 'INSERT IGNORE INTO `repository_moves` (`from_repository`,`to_repository`,`upstream_package_repository`)'
    printf ' SELECT `f`.`id`,`t`.`id`,`u`.`id`'
    printf ' FROM'
    printf ' `repositories` AS `%s` JOIN' \
      'f' 't'
    printf ' `upstream_repositories` AS `u`'
    printf ' WHERE '
    printf '(`f`.`name`="%s" AND `t`.`name`="%s" AND `u`.`name`="%s") OR ' \
      'staging' 'testing' 'core' \
      'staging' 'testing' 'extra' \
      'staging' 'testing' 'multilib' \
      'community-staging' 'community-testing' 'community' \
      'community-staging' 'community-testing' 'multilib' \
      'testing' 'core' 'core' \
      'testing' 'extra' 'extra' \
      'testing' 'extra' 'multilib' \
      'community-testing' 'community' 'community' \
      'community-testing' 'community' 'multilib'
    printf ';\n'
  } | \
    sed 's| OR ;|;|'

  for repo in \
    'core:stable:AQ==' \
    'extra:stable:AQ==' \
    'community:stable:AQ==' \
    'build-support:standalone:AQ==' \
    'testing:testing:AQ==' \
    'community-testing:testing:AQ==' \
    'staging:staging:AQ==' \
    'community-staging:staging:AQ==' \
    'build-list:unbuilt:AA==' \
    'deletion-list:forbidden:AA=='; do
    printf 'INSERT IGNORE INTO `repositories` (`name`,`stability`,`is_on_master_mirror`) SELECT'
    printf ' from_base64("%s"),`id`,from_base64("%s") FROM `repository_stabilities` WHERE `name`=from_base64("%s");\n' \
      "$(
        printf '%s' "${repo}" | \
          cut -d: -f1 | \
          base64_encode_each
      )" \
      "$(
        printf '%s' "${repo}" | \
          cut -d: -f3
      )" \
      "$(
        printf '%s' "${repo}" | \
          cut -d: -f2 | \
          base64_encode_each
      )"
  done

  printf 'INSERT IGNORE INTO `dependency_types` (`name`,`relevant_for_building`,`relevant_for_binary_packages`) VALUES'
  {
    printf '\n  ("%s",%s,%s),' \
      'make' '1' '0' \
      'check' '0' '0' \
      'link' '0' '1' \
      'run' '1' '1'
    printf ';\n'
  } | \
    sed 's|,;|;|'
} | \
  mysql buildmaster

find "${work_dir}/package-states" \
  -maxdepth 1 \
  -mindepth 1 \
  \( \
    -name '*.done' -o \
    -name '*.testing' -o \
    -name '*.tested' \
  \) \
  -exec sed '
    1!d
    s@^.*-\([^-]\+\)-\([^-.]\+\)\.pkg\.tar\.xz$@{} \1 \2 \0@
    s@^\S*/@@
    s/^\(\S\+\)\.\(done\|testing\|tested\) /\1 /
    s/ \([0-9]\+\) / \1.0 /
    s/ [0-9]\+\.\([0-9]\+\) / \1 /
  ' {} \; | \
  while read -r state_file sub_pkgrel arch package_file; do
    printf '%s ' "${state_file}"
    repository=$(
      grep '^\S\+ '"$(str_to_regex "${package_file}")"'$' "${tmp_dir}/master-mirror-listing" | \
        cut -d' ' -f1
    )
    if [ "$(echo "${repository}" | grep -cx '\S\+')" -ne 1 ]; then
      printf 'not found exactly once on the master mirror.\n'
      >&2 printf '"%s"\n' "${state_file}" "${sub_pkgrel}" "${arch}" "${package_file}"
      continue
    fi
    mysql_generate_package_metadata "${sub_pkgrel}" "${repository}" "${state_file}"
    printf ' ok\n'
  done

tr ' ' '.' < \
  "${work_dir}/build-list" | \
  while read -r state_file; do
    mysql_generate_package_metadata 'build-list' "${state_file}"
  done

# shellcheck disable=SC2016
find "${work_dir}/package-states" -name '*.blocked' -printf '%p %f\n' | \
  sed '
    s|\.\([^. ]\+\)\.\([^. ]\+\)\.\([^. ]\+\)\.blocked$| \1 \2 \3|
  ' | \
  while read -r state_file pkgbase git_revision mod_git_revision repository; do
    printf 'UPDATE `build_assignments`'
    mysql_join_build_assignments_package_sources
    mysql_join_package_sources_upstream_repositories
    printf ' SET `build_assignments`.`is_blocked`=from_base64("%s")' \
      "$(
        tr -d '\n' < \
          "${state_file}" | \
          base64 -w0
      )"
    printf ' WHERE'
    printf ' `package_sources`.`%s`=from_base64("%s") AND' \
      'pkgbase'          "$(printf '%s' "${pkgbase}" |          base64 -w0)" \
      'git_revision'     "$(printf '%s' "${git_revision}" |     base64 -w0)" \
      'mod_git_revision' "$(printf '%s' "${mod_git_revision}" | base64 -w0)"
    printf ' `upstream_repositories`.`name`=from_base64("%s");\n' \
      "$(printf '%s' "${repository}" | base64 -w0)"
  done | \
  mysql_run_query

grep '^\('"$(
  # shellcheck disable=SC2086
  printf '%s\\|' ${standalone_package_repositories} ${stable_package_repositories}
)"'\) ' "${tmp_dir}/master-mirror-listing" | \
  sed '
    s/\.pkg\.tar\.xz$//
    s/-\([^-:]\+\)\(\(-[^-]\+\)\{2\}\)$/-0:\1\2/
    s/\(-[0-9]\+\)\(-[^-]\+\)$/\1.0\2/
    s/-\([^-:]\+\):\([^-:]\+\)-\([^-.]\+\)\.\([^-.]\+\)-\([^-]\+\)$/ \1 \2 \3 \4 \5/
  ' | \
  while read -r repo pkgname epoch pkgver pkgrel sub_pkgrel arch; do
    # we don't care too much about those - they won't be moved anyways
    printf '('
    # shellcheck disable=SC2046
    printf 'from_base64("%s"),' \
      $(
        printf '%s\n' \
          "${pkgname}" \
          "${epoch}" \
          "${pkgver}" \
          "${pkgrel}" \
          "${sub_pkgrel}" \
          "${arch}" \
          "${repo}" | \
          base64_encode_each
      ) | \
      sed 's/,$/),/'
    printf '\n'
  done > \
  "${tmp_dir}/new-stable-packages"

if [ -s "${tmp_dir}/new-stable-packages" ]; then
  # shellcheck disable=SC2016
  {
    printf 'CREATE TEMPORARY TABLE `stable_packages` ('
    printf '`%s` %s,' \
      'pkgname' 'VARCHAR(64)' \
      'epoch' 'MEDIUMINT' \
      'pkgver' 'VARCHAR(64)' \
      'pkgrel' 'MEDIUMINT' \
      'sub_pkgrel' 'MEDIUMINT' \
      'architecture' 'VARCHAR(16)' \
      'repository' 'VARCHAR(64)' \
      'build_assignment' 'BIGINT NOT NULL AUTO_INCREMENT'
    printf 'PRIMARY KEY (`build_assignment`));\n'
    sed '
      1~10 ! b not_start
      s/^/INSERT IGNORE INTO `stable_packages` (`pkgname`,`epoch`,`pkgver`,`pkgrel`,`sub_pkgrel`,`architecture`,`repository`) VALUES \n/
      :not_start
      $    b end
      0~10 b end
      b
      :end
      s/,$/;/
    ' "${tmp_dir}/new-stable-packages"
    printf 'INSERT IGNORE INTO `binary_packages` (`pkgname`,`epoch`,`pkgver`,`pkgrel`,`sub_pkgrel`,`architecture`,`repository`,`build_assignment`,`has_issues`,`is_tested`,`is_to_be_deleted`)'
    printf ' SELECT '
    printf '`stable_packages`.`%s`,' \
      'pkgname' \
      'epoch' \
      'pkgver' \
      'pkgrel' \
      'sub_pkgrel'
    printf '`%s`.`id`,' \
      'architectures' \
      'repositories'
    printf -- '-`build_assignment`,0,1,0 FROM `stable_packages`'
    printf ' JOIN `%s` ON `stable_packages`.`%s`=`%s`.`name`' \
      'repositories' 'repository' 'repositories' \
      'architectures' 'architecture' 'architectures'
    printf ';\n'
    printf 'DROP TABLE `stable_packages`;\n'
  } | \
    mysql_run_query
fi

mysql_repair_binary_packages_without_build_assignment