#!/bin/sh

# report back on a build assignment
# either on success via:
#   "$0 $package $revision $mod_revision $repository $arch $sub_pkgrel" and tar'ed packages and logs
#   (= a tar of package(s), signature(s) and log(s)) on stdin
# or on failure via:
#   "$0 $package $revision $mod_revision $repository $arch ERROR" and tar'ed logs
# or to abort:
#   "$0 ABORT"

# exit codes:
#  0: ok
#  1: another instance was already running
#  2: outdated package
#  3: signature error
#  4: package error (e.g. wrong packages sent)
#  5: wrong number of arguments

# shellcheck disable=SC2119,SC2120

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

if [ -s "${work_dir}/build-master-sanity" ]; then
  >&2 echo 'Build master is not sane.'
  exit 1
fi

# aborting does not need any locks
if [ $# -eq 1 ] && \
  [ "$1" = 'ABORT' ]; then
  old_pkgbase=$(
    # shellcheck disable=SC2016
    {
      printf 'SELECT `package_sources`.`pkgbase`'
      printf ' FROM `build_slaves`'
      mysql_join_build_slaves_build_assignments
      mysql_join_build_assignments_package_sources
      # shellcheck disable=SC2154
      printf ' WHERE `build_slaves`.`id`=%s;\n' \
        "${slave_id}"
      printf 'UPDATE `build_slaves`'
      printf ' SET `build_slaves`.`currently_building`=NULL'
      # shellcheck disable=SC2154
      printf ' WHERE `build_slaves`.`id`=%s;\n' \
        "${slave_id}"
    } | \
      mysql_run_query 'unimportant'
  )
  if [ -z "${old_pkgbase}" ]; then
    >&2 printf 'Umm, nothing to abort for you.\n'
  else
    >&2 printf 'I aborted your build-assignment (%s).\n' \
      "${old_pkgbase}"
  fi
  exit
fi

if [ $# -ne 6 ]; then
  >&2 printf 'return-assignment: Wrong number of arguments (%s)\n' \
    "$#"
  >&2 printf 'call either:\n'
  >&2 printf '  return-assignment ABORT\n'
  >&2 printf '  return-assignment pkgbase git_revision mod_git_revision repository arch ERROR\n'
  >&2 printf '  return-assignment pkgbase git_revision mod_git_revision repository arch sub-pkgrel\n'
  exit 5
fi

# Create a lock file and a trap.

exec 9> "${build_list_lock_file}"
if ! verbose_flock -w 20 9; then
  >&2 echo 'come back (shortly) later - I cannot lock build list.'
  exit 1
fi

exec 8> "${sanity_check_lock_file}"
if ! verbose_flock -s -w 20 8; then
  >&2 echo 'come back (shortly) later - sanity-check running.'
  exit 1
fi

clean_up_tmp_dir() {
  cd "${base_dir}"
  rm -rf --one-file-system "${tmp_dir}"
}

tmp_dir=$(mktemp -d "${work_dir}/tmp.return-assignment.XXXXXXXXXX")
trap clean_up_tmp_dir EXIT

if [ "$6" = 'ERROR' ]; then
# the build failed on the build slave

  # shellcheck disable=SC2016
  infos=$(
    {
      printf 'SELECT'
      printf ' `build_assignments`.`id`,'
      printf 'IF(`build_assignments`.`is_broken`,"true","false"),'
      printf 'replace(to_base64(%s),"\\n",""),' \
         '`package_sources`.`upstream_flag_date`' \
         '`binary_packages`.`pkgver`'
      printf '`binary_packages`.`epoch`,'
      printf '`binary_packages`.`pkgrel`,'
      printf '`ba_a`.`name`'
      printf ' FROM `build_slaves`'
      mysql_join_build_slaves_build_assignments
      mysql_join_build_assignments_architectures '' 'ba_a'
      mysql_join_build_assignments_package_sources
      mysql_join_package_sources_upstream_repositories
      mysql_join_build_assignments_binary_packages
      mysql_join_binary_packages_binary_packages_in_repositories
      printf ' JOIN `architecture_compatibilities`'
      printf ' ON `architecture_compatibilities`.`fully_compatible`'
      printf ' AND `architecture_compatibilities`.`built_for`=`build_assignments`.`architecture`'
      printf ' JOIN `architectures`'
      printf ' ON `architecture_compatibilities`.`runs_on`=`architectures`.`id`'
      printf ' WHERE `build_slaves`.`id`=from_base64("%s")' \
        "$(
          # shellcheck disable=SC2154
          printf '%s' "${slave_id}" | \
            base64 -w0
        )"
      printf ' AND `package_sources`.`%s`=from_base64("%s")' \
        'pkgbase' "$(printf '%s' "$1" | base64 -w0)" \
        'git_revision' "$(printf '%s' "$2" | base64 -w0)" \
        'mod_git_revision' "$(printf '%s' "$3" | base64 -w0)"
      printf ' AND `upstream_repositories`.`name`=from_base64("%s")' \
        "$(printf '%s' "$4" | base64 -w0)"
      printf ' AND `architectures`.`name`=from_base64("%s")' \
        "$(printf '%s' "$5" | base64 -w0)"
      printf ' AND `binary_packages_in_repositories`.`repository`=%s' \
        "${repository_ids__any_build_list}"
      printf ' LIMIT 1;\n'
    } | \
      mysql_run_query | \
      tr '\t' ' '
  )
  if [ -z "${infos}" ]; then
    >&2 echo 'You do not build this package (anymore) - move on.'
    exit 2
  fi
  build_assignment_architecture="${infos##* }"
  infos="${infos% ${build_assignment_architecture}}"
  pkgrel="${infos##* }"
  infos="${infos% ${pkgrel}}"
  epoch="${infos##* }"
  infos="${infos% ${epoch}}"
  pkgver="${infos##* }"
  infos="${infos% ${pkgver}}"
  pkgver=$(
    printf '%s' "${pkgver}" \
    | base64 -d
  )
  upstream_flag_date="${infos##* }"
  infos="${infos% ${upstream_flag_date}}"
  upstream_flag_date=$(
    printf '%s' "${upstream_flag_date}" \
    | base64 -d
  )
  was_broken_before="${infos##* }"
  build_assignment_id="${infos% ${was_broken_before}}"

  # save sent build logs
  saved_build_logs=$(
    tar -vx \
      -C "${build_log_directory}/error" \
      --wildcards \
      --no-wildcards-match-slash \
      --transform="s|^|$1.$2.$3.$4.${build_assignment_architecture}.|" \
      '*.build-log.gz'
  )

  if [ "${upstream_flag_date}" = '0000-00-00 00:00:00' ]; then
    upstream_flag_date=$(
      curl -Ss 'https://www.archlinux.org/packages/search/json/?name='"$1" \
      | sed '
        s/{/\0\n/g
        s/}/\n\0/g
      ' \
      | sed '
        1,2 d
        $ d
      ' \
      | grep -F '"pkgname": "'"$1"'"' \
      | grep -F '"epoch": '"${epoch}"'' \
      | grep -F '"pkgver": "'"${pkgver}"'"' \
      | grep -F '"pkgrel": "'"${pkgrel}"'"' \
      | sed -n '
        s/^.*"flag_date": "\([^"]\+\)",.*$/\1/
        T
        s/\.[0-9]\+[^.]*$//
        T
        y/T/ /
        /^[0-9]\{4\}\(-[0-9]\{2\}\)\{2\} \([0-9]\{2\}:\)\{2\}[0-9]\{2\}$/ p
      '
    )
    if [ -z "${upstream_flag_date}" ]; then
      upstream_flag_date='0000-00-00 00:00:00'
    fi
  fi

  # shellcheck disable=SC2016
  {
    if [ -n "${saved_build_logs}" ]; then
      printf 'CREATE TEMPORARY TABLE `failures` ('
      printf '`%s` %s,' \
        'date' 'TIMESTAMP' \
        'reason' 'SMALLINT' \
        'log_file' 'VARCHAR(512)' | \
        sed 's/,$//'
      printf ');\n'
      fail_reason_identifiers=$(
        {
          printf 'SELECT `fail_reasons`.`id`,replace(to_base64(`fail_reasons`.`identifier`),"\\n","")'
          printf ' FROM `fail_reasons` ORDER BY `fail_reasons`.`severity`'
        } | \
          mysql_run_query
      )
      for saved_build_log in ${saved_build_logs}; do
        printf '%s\n' "${fail_reason_identifiers}" | \
          while read -r reason_id identifier; do
            if zgrep -qx "\s*$(
                printf '%s' "${identifier}" | \
                  base64 -d
              )\s*" \
              "${build_log_directory}/error/$1.$2.$3.$4.${build_assignment_architecture}.${saved_build_log}"; then

              printf ' (from_base64("%s"),%s,from_base64("%s")),' \
                "$(
                  printf '%s' "${saved_build_log}" | \
                    sed 's|\.build-log\.gz$||;s|^.*\.||' | \
                    base64 -w0
                )" \
                "${reason_id}" \
                "$(
                  printf '%s' "$1.$2.$3.$4.${build_assignment_architecture}.${saved_build_log}" | \
                    base64 -w0
                )"
              break
            fi
          done
      done | \
        sed '
          1 s/^/INSERT IGNORE INTO `failures` (`date`,`reason`,`log_file`) VALUES /
          s/,$/;\n/
        '
      printf 'INSERT IGNORE INTO `failed_builds` (`build_slave`,`build_assignment`,`date`,`reason`,`log_file`,`log_file_exists`)'
      printf ' SELECT '
      printf 'from_base64("%s"),' \
        "$(printf '%s' "${slave_id}" | base64 -w0)" \
        "$(printf '%s' "${build_assignment_id}" | base64 -w0)"
      printf '`failures`.`%s`,' \
        'date' 'reason' 'log_file'
      printf '1'
      printf ' FROM `failures`;\n'
      printf 'DROP TEMPORARY TABLE `failures`;\n'
      printf 'COMMIT;\n'
    fi
    printf 'UPDATE `build_assignments`'
    mysql_join_build_assignments_package_sources
    printf ' SET `build_assignments`.`is_broken`=1,'
    printf '`build_assignments`.`priority`=0,'
    printf '`package_sources`.`upstream_flag_date`=from_base64("%s")' \
      "$(
        printf '%s' "${upstream_flag_date}" | \
          base64 -w0
      )"
    printf ' WHERE `build_assignments`.`id`=from_base64("%s");\n' \
      "$(
        printf '%s' "${build_assignment_id}" | \
          base64 -w0
      )"
    printf 'COMMIT;\n'
    printf 'UPDATE `build_slaves`'
    printf ' SET `build_slaves`.`currently_building`=NULL,'
    printf ' `build_slaves`.`last_action`=NULL,'
    printf ' `build_slaves`.`logged_lines`=NULL,'
    printf ' `build_slaves`.`trials`=NULL'
    printf ' WHERE `build_slaves`.`id`=from_base64("%s");\n' \
      "$(
        printf '%s' "${slave_id}" | \
          base64 -w0
      )"
  } | \
    mysql_run_query

  mysql_load_min_and_max_versions

  # this will hold a list of "$build_time $haskell_package $version"
  # meaning that the given $haskell_package with version $version did
  # not work at $build_time
  # note, that $build_time is only considered when $architecture or
  # $version are unavailable
  find "${build_log_directory}/error" -type f \
    -name "$1.$2.$3.$4.${build_assignment_architecture}.*.build-log.gz" \
    -exec zgrep -qF "$(
      printf '%s\n' \
        'The following packages are broken because other packages they depend on are missing. These broken packages must be rebuilt before they can be used.' \
        'mismatched interface file versions (wanted ' \
    )" {} \; \
    -printf '%p\n' | \
    sed '
      s/^.*\.\([0-9]\{4\}-[0-9]\{2\}-[0-9]\{2\}T[0-9]\{2\}:[0-9]\{2\}:[0-9]\{2\}\)\.build-log\.gz$/\1 \0/
    ' | \
    while read -r build_time build_log; do
      build_time=$(
        date +%s -d"${build_time}"
      )
      zcat "${build_log}" | \
        sed -n '
          /^Packages ([0-9]\+) /,/^\s*$/ p
        ' | \
        tr ' ' '\n' | \
        sed '
          1,2 d
          /^\s*$/d
          p
          s/^haskell-//
          t
          d
        ' | \
        sort -u | \
        sed '
          s/-\(\([0-9]\+\):\)\?\([^-:]\+\)-\([^-.]\+\)\(\.\([^-.]\+\)\)\?$/ \2 \3 \4 \6/
          s/ $/ 0/
          s/  / 0 /g
        ' > \
        "${tmp_dir}/installed-versions"
      # shellcheck disable=SC1112
      zcat "${build_log}" | \
        sed '
          s/^installed package \(.*\) is broken due to missing package .*$/\1/
          t
          s@^.*Bad interface file: /usr/lib/ghc-[0-9.]\+/site-local/\([^/]\+\)-[0-9.]\+/.*$@\1@
          t
          s@^\s*It is a member of the package ‘\(\S\+\)\(-[^-]\+\)\{2\}’\s*$@\1@
          t
          d
        ' | \
        tr ' ' '\n' | \
        tr '[:upper:]' '[:lower:]' | \
        sed '
          s/^/'"${build_time}"' /
          s/-[0-9.]\+$//
        ' | \
        sort -u | \
        sort -k2,2 > \
        "${tmp_dir}/broken-packages"
      {
        cut -d' ' -f1 < \
          "${tmp_dir}/installed-versions" | \
          sed 'p'
        cut -d' ' -f2 < \
          "${tmp_dir}/broken-packages"
      } | \
        sort | \
        uniq -u | \
        sed '
          s/$/ '"${max_version%%:*}"' '"${max_version#*:}"' 0 0/
        ' | \
        sponge -a "${tmp_dir}/installed-versions"
      sort -k1,1 < \
        "${tmp_dir}/installed-versions" | \
        sponge "${tmp_dir}/installed-versions"
      join -1 2 -2 1 \
        "${tmp_dir}/broken-packages" \
        "${tmp_dir}/installed-versions"
    done | \
    sort -k2 -k1nr,1 | \
    uniq -f1 | \
    tr ' ' '\t' > \
    "${tmp_dir}/broken-packages-with-version"

  # now we look if the broken packages have been rebuilt in the meantime
  haskell_rebuild_packages=$(
    # shellcheck disable=SC2016
    {
      printf 'CREATE TEMPORARY TABLE `broken`('
        printf '`time_stamp` BIGINT,'
        printf '`pkgname` VARCHAR(64),'
        printf '`epoch` MEDIUMINT,'
        printf '`pkgver` VARCHAR(64),'
        printf '`pkgrel` MEDIUMINT,'
        printf '`sub_pkgrel` MEDIUMINT,'
        printf 'KEY `time_stamp`(`time_stamp`),'
        printf 'KEY `pkgname`(`pkgname`),'
        printf 'KEY `epoch`(`epoch`),'
        printf 'KEY `pkgver`(`pkgver`),'
        printf 'KEY `pkgrel`(`pkgrel`),'
        printf 'KEY `sub_pkgrel`(`sub_pkgrel`),'
        printf 'UNIQUE KEY `pkgfile`(`pkgname`,`epoch`,`pkgver`,`pkgrel`,`sub_pkgrel`)'
      printf ');\n'
      printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `broken`(' \
        "${tmp_dir}/broken-packages-with-version"
        printf '`%s`,' \
          'pkgname' \
          'time_stamp' \
          'epoch' \
          'pkgver' \
          'pkgrel' \
          'sub_pkgrel' | \
          sed 's/,$//'
      printf ');\n'

      printf 'SET @`max_priority` = ('
        printf 'SELECT MAX(`build_assignments`.`priority`)'
        printf ' FROM `build_assignments`'
      printf ');\n'

# TODO: We might want to make this architecture-sensitive.
      printf 'UPDATE `broken`'
      printf ' JOIN `binary_packages`'
      printf ' ON ('
        printf '`binary_packages`.`pkgname`=`broken`.`pkgname`'
        printf ' OR `binary_packages`.`pkgname`=CONCAT("haskell-",`broken`.`pkgname`)'
      printf ')'
      mysql_join_binary_packages_build_assignments
      mysql_join_binary_packages_binary_packages_in_repositories
      printf ' AND `binary_packages_in_repositories`.`repository`=%s' \
        "${repository_ids__any_build_list}"
      printf ' SET `build_assignments`.`priority`=GREATEST(0,@`max_priority`+IF(`build_assignments`.`is_broken`,-1,1));\n'

      printf 'SELECT DISTINCT `broken`.`pkgname`'
      printf ' FROM `broken`'
      printf ' WHERE NOT EXISTS ('
        printf 'SELECT 1'
        printf ' FROM `binary_packages`'
        mysql_join_binary_packages_binary_packages_in_repositories
        mysql_join_binary_packages_in_repositories_repositories
        printf ' AND ('
          printf '`repositories`.`is_on_master_mirror`'
          printf ' OR `repositories`.`id`=%s' \
            "${repository_ids__any_build_list}"
        printf ')'
        mysql_join_binary_packages_build_assignments
        printf ' JOIN `architecture_compatibilities`'
        printf ' ON `architecture_compatibilities`.`fully_compatible`'
        printf ' AND `architecture_compatibilities`.`built_for`=`build_assignments`.`architecture`'
        printf ' JOIN `architectures`'
        printf ' ON `architecture_compatibilities`.`runs_on`=`architectures`.`id`'
        printf ' AND `architectures`.`name`=from_base64("%s")' \
          "$(
            printf '%s' "${build_assignment_architecture}" | \
              base64 -w0
          )"
        printf ' WHERE ('
          printf '`binary_packages`.`pkgname`=`broken`.`pkgname`'
          printf ' OR `binary_packages`.`pkgname`=CONCAT("haskell-",`broken`.`pkgname`)'
        printf ') AND ('
          printf '`build_assignments`.`return_date`>FROM_UNIXTIME(`broken`.`time_stamp`)'
          printf ' OR ('
            printf '`binary_packages`.`epoch`>`broken`.`epoch`'
            printf ' OR ('
              printf '`binary_packages`.`epoch`=`broken`.`epoch`'
              printf ' AND `binary_packages`.`pkgver`>`broken`.`pkgver`'
            printf ')'
            printf ' OR ('
              printf '`binary_packages`.`epoch`=`broken`.`epoch`'
              printf ' AND `binary_packages`.`pkgver`=`broken`.`pkgver`'
              printf ' AND `binary_packages`.`pkgrel`>`broken`.`pkgrel`'
            printf ')'
            printf ' OR ('
              printf '`binary_packages`.`epoch`=`broken`.`epoch`'
              printf ' AND `binary_packages`.`pkgver`=`broken`.`pkgver`'
              printf ' AND `binary_packages`.`pkgrel`=`broken`.`pkgrel`'
              printf ' AND `binary_packages`.`sub_pkgrel`>`broken`.`sub_pkgrel`'
            printf ')'
          printf ')'
          printf ' OR `repositories`.`id`=%s' \
            "${repository_ids__any_build_list}"
        printf ')'
      printf ');\n'
    } | \
      mysql_run_query | \
      sed '
        s/^/-p ^(haskell-)?/
        s/$/$/
      '
  )

  # release lock on build-list - otherwise seed-build-list won't run
  flock -u 9

  rescheduled_packages=$(
    if [ -n "${haskell_rebuild_packages}" ]; then
      # shellcheck disable=SC2086
      "${base_dir}/bin/seed-build-list" ${haskell_rebuild_packages} | \
        sed 's/ .*$//'
    fi
  )

  # prioritize _this_ build assignment iff we rescheduled any broken dependencies
  if [ -n "${rescheduled_packages}" ]; then
    # shellcheck disable=SC2016
    {
      printf 'UPDATE `build_assignments`'
      printf ' SET `build_assignments`.`priority`=('
        printf 'SELECT MAX(`build_assignments`.`priority`)'
        printf ' FROM `build_assignments`'
      printf ')+1'
      printf ' WHERE `build_assignments`.`id`=from_base64("%s");\n' \
        "$(
          printf '%s' "${build_assignment_id}" | \
            base64 -w0
        )"
    } | \
      mysql_run_query
  fi

  if ! ${was_broken_before} || [ -n "${rescheduled_packages}" ]; then
    # shellcheck disable=SC2119
    {
      printf '%s/%s ' \
        "${build_assignment_architecture}" \
        "$1" \
      | sed '
        s/[+]/%2B/g
      '
      if [ "${1%s}s" = "$1" ]; then
        printf 'are'
      else
        printf 'is'
      fi
      if ${was_broken_before}; then
        printf ' still'
      fi
      # shellcheck disable=SC2154
      printf ' broken (says %s)' \
        "${slave}"
      if [ "$1" = 'electron' ] || [ "$1" = 'electron2' ]; then
        printf -- ' - as usual'
      fi
      if [ -n "${rescheduled_packages}" ]; then
        printf -- ' - I rescheduled:'
        # shellcheck disable=SC2086
        printf ' %s,' ${rescheduled_packages} | \
          sed 's/,$//'
      fi
      if [ "${upstream_flag_date}" != '0000-00-00 00:00:00' ]; then
        printf -- ' - already flagged out-of-date upstream on %s' \
          "${upstream_flag_date%% *}"
      fi
      printf ': https://archlinux32.org/buildmaster/build-log.php?a=%s&p=%s\n' \
        "${build_assignment_architecture}" \
        "$1"
    } | \
      irc_say
  fi

  exit 0

fi

# the build was successful on the build slave

# so we also need a lock on the package database

exec 7> "${package_database_lock_file}"
if ! verbose_flock -w 20 7; then
  >&2 echo 'come back (shortly) later - I cannot lock package database.'
  exit 1
fi

if intentions_left; then
  >&2 echo 'come back (shortly) later - There are still intentions in the queue.'
  exit 1
fi

# shellcheck disable=SC2016
build_assignment_id=$(
  {
    printf 'SELECT DISTINCT `build_assignments`.`id`'
    printf ' FROM `build_slaves`'
    mysql_join_build_slaves_build_assignments
    mysql_join_build_assignments_package_sources
    mysql_join_package_sources_upstream_repositories
    mysql_join_build_assignments_binary_packages
    mysql_join_binary_packages_binary_packages_in_repositories
    printf ' JOIN `architecture_compatibilities`'
    printf ' ON `build_assignments`.`architecture`=`architecture_compatibilities`.`built_for`'
    printf ' AND `architecture_compatibilities`.`fully_compatible`'
    printf ' JOIN `architectures`'
    printf ' ON `architecture_compatibilities`.`runs_on`=`architectures`.`id`'
    printf ' WHERE `build_slaves`.`id`=from_base64("%s")' \
      "$(
        # shellcheck disable=SC2154
        printf '%s' "${slave_id}" | \
          base64 -w0
      )"
    printf ' AND `package_sources`.`%s`=from_base64("%s")' \
      'pkgbase' "$(
        printf '%s' "$1" | \
          base64 -w0
      )" \
      'git_revision' "$(
        printf '%s' "$2" | \
          base64 -w0
      )" \
      'mod_git_revision' "$(
        printf '%s' "$3" | \
          base64 -w0
      )"
    printf ' AND `upstream_repositories`.`name`=from_base64("%s")' \
      "$(
        printf '%s' "$4" | \
          base64 -w0
      )"
    printf ' AND `architectures`.`name`=from_base64("%s")' \
      "$(
        printf '%s' "$5" | \
          base64 -w0
      )"
    printf ' AND `binary_packages_in_repositories`.`repository`=%s' \
      "${repository_ids__any_build_list}"
    printf ' AND `binary_packages`.`sub_pkgrel`=from_base64("%s");\n' \
      "$(
        printf '%s' "$6" | \
          base64 -w0
      )"
  } | \
    mysql_run_query | \
    tr '\t' ' '
)

if [ -z "${build_assignment_id}" ]; then
  >&2 echo 'Sorry, the sent package is outdated.'
  exit 2
fi

cd "${tmp_dir}"

export TMPDIR="${tmp_dir}"

# extract package(s)
tar -x

# check if all packages come with:
#  - a package file
#  - a signature
#  - a namcap log
#  - a list of needed libraries
#  - a list of provided libraries
missing_files=$(
  find . -maxdepth 1 -regextype sed \( \
    \( \
      -regex '.*\.pkg\.\('"${package_compression_suffix_regex}"'\)' \
      -printf '%f package\n' \
    \) -o \
    \( \
      -regex '.*\.pkg\.\('"${package_compression_suffix_regex}"'\)\.sig' \
      -printf '%f signature\n' \
    \) -o \
    \( \
      -regex '.*\.pkg\.\('"${package_compression_suffix_regex}"'\)-namcap\.log\.gz' \
      -printf '%f namcap\n' \
    \) -o \
    \( \
      -regex '.*\.pkg\.\('"${package_compression_suffix_regex}"'\)\.so\.needs\.gz' \
      -printf '%f needed-libraries\n' \
    \) -o \
    \( \
      -regex '.*\.pkg\.\('"${package_compression_suffix_regex}"'\)\.so\.provides\.gz' \
      -printf '%f provided-libraries\n' \
    \) \
  \) | \
    sed '
      s/\(\.pkg\.\('"${package_compression_suffix_regex}"'\)\)\(\.sig\|\(-namcap\.log\|\.so\.\(provides\|needs\)\)\.gz\) /\1 /
    ' | \
    sort -k1,1 -k2,2 | \
    sed '
      :a
        $!N
        s/^\(\(\S\+\) [^\n]\+\)\n\2 /\1 /
        ta
      P
      D
    ' | \
    sed -n '
      s/$/ /
      / package /!{
        h
        s/^\(\S\+\) .*$/Package "\1" is missing./
        p
        g
      }
      / signature /!{
        h
        s/^\(\S\+\) .*$/Signature of "\1" is missing./
        p
        g
      }
      / namcap /!{
        h
        s/^\(\S\+\) .*$/Namcap log of "\1" is missing./
        p
        g
      }
      / needed-libraries /!{
        h
        s/^\(\S\+\) .*$/List of libraries needed by "\1" is missing./
        p
        g
      }
      / provided-libraries /!{
        h
        s/^\(\S\+\) .*$/List of libraries provided by "\1" is missing./
        p
        g
      }
    '
)

if [ -n "${missing_files}" ]; then
  >&2 echo 'The following packages lack a signature, namcap log, package file or list of needed/provided libraries:'
  printf 'Your buildslave "%s" uploaded some incomplete package(s):\n' \
    "${slave}" | \
    irc_say "${operator}"
  printf '%s\n' "${missing_files}" | \
    irc_say "${operator}" 'copy' >&2
  exit 3
fi

# check if the signatures are valid
signatures=$(
  find . -maxdepth 1 -regextype sed \
    -regex '.*\.pkg\.\('"${package_compression_suffix_regex}"'\)' \
    -printf 'package file %f\n' \
    -exec gpg --batch --status-fd 1 -q --homedir /etc/pacman.d/gnupg --verify '{}.sig' '{}' \; 2> /dev/null
)
if [ -z "$(
  printf '%s\n' "${signatures}" | \
    cut -d' ' -f2 | \
    grep -x 'file\|TRUST_FULLY' | \
    sort | \
    uniq -c | \
    awk '{print $1}' | \
    uniq -d
)" ]; then
  >&2 echo 'Signature(s) is/are not fully trusted:'
  printf 'Your buildslave "%s" uploaded a package with a not fully-trusted signature:\n' \
    "${slave}" | \
    irc_say "${operator}"
  printf '%s\n' "${signatures}" | \
    irc_say "${operator}" 'copy' >&2
  exit 3
fi

# get the fingerprints of the signing keys for the sent packages
printf '%s\n' "${signatures}" \
| sed -n '
  s/^\S\+ //
  /^file /,/^TRUST_FULLY / {
    /^file / p
    /^KEY_CONSIDERED / p
  }
' \
| sed '
  /^file / {
    N
    s/^file \(\S\+\)\nKEY_CONSIDERED \([0-9A-F]\{40\}\) .*$/\1\t\2/
    t
  }
  d
' \
| sort -k2,2 \
> "${tmp_dir}/signing-keys"

# shellcheck disable=SC2016
{
  printf 'SELECT '
  printf '`gpg_keys`.`id`,'
  printf '`gpg_keys`.`fingerprint`n'
  printf ' FROM `gpg_keys`;\n'
} \
| mysql_run_query \
| sort -k2,2 \
| join -1 2 -2 2 -o 1.1,2.1 -a 2 -e 'NULL' - "${tmp_dir}/signing-keys" \
| sort -k2,2 \
| sponge "${tmp_dir}/signing-keys"

if grep -q '^NULL ' "${tmp_dir}/signing-keys"; then
  >&2 echo 'Signing key is unknown to the buildmaster'"'"'s mysql database:'
  printf 'Your buildslave "%s" uploaded a package with a signature of a key unknown to the mysql database:\n' \
    "${slave}" | \
    irc_say "${operator}"
  irc_say "${operator}" 'copy' \
  <"${tmp_dir}/signing-keys" \
  >&2
  exit 3
fi

# check if the package maintainer is set
errors=$(
  find . -maxdepth 1 -regextype sed \
    -regex '.*\.pkg\.\('"${package_compression_suffix_regex}"'\)' | \
    while read -r pkg; do
      tar -Oxf "${pkg}" --zstd '.BUILDINFO' 2>/dev/null | \
        grep -vxF 'packager = Unknown Packager' | \
        grep -q '^packager = ' || \
        printf '%s misses a valid packager.\n' \
          "${pkg##*/}"
    done
)
if [ -n "${errors}" ]; then
  >&2 echo 'Packager error(s):'
  printf 'Your buildslave "%s" uploaded package(s) with invalid packager:\n' \
    "${slave}" | \
    irc_say "${operator}"
  printf '%s\n' "${errors}" | \
    irc_say "${operator}" 'copy' >&2
  exit 3
fi

# check if the sent packages are the expected ones
find . -maxdepth 1 -regextype sed \
  -regex '.*\.pkg\.\('"${package_compression_suffix_regex}"'\)' \
  -printf '%f\n' > \
  "${tmp_dir}/packages"

# shellcheck disable=SC2016
{
  printf 'SELECT'
  printf ' `binary_packages`.`id`,'
  mysql_package_name_query
  printf ' FROM `binary_packages`'
  mysql_join_binary_packages_architectures
  printf ' LEFT'
  mysql_join_binary_packages_compressions
  mysql_join_binary_packages_binary_packages_in_repositories
  printf ' WHERE `binary_packages`.`build_assignment`=from_base64("%s")' \
    "$(
      printf '%s' "${build_assignment_id}" | \
        base64 -w0
    )"
  printf ' AND `binary_packages_in_repositories`.`repository`=%s' \
    "${repository_ids__any_build_list}"
  printf ';\n'
} | \
  mysql_run_query | \
  tr '\t' ' ' | \
  sort -k2 > \
  "${tmp_dir}/package-ids"

package_errors=$(
  {
    sed '
      s|^|was_built: |
    ' "${tmp_dir}/packages"
    sed '
      s|^[0-9]\+ |expected: |
    ' "${tmp_dir}/package-ids"
  } | \
    sort -k2 | \
    uniq -u -f1
)

if [ -n "${package_errors}" ]; then
  >&2 echo 'The following packages should have been built but are missing or vice versa:'
  >&2 printf '%s\n' "${package_errors}"
  {
    printf 'Your buildslave "%s" uploaded the wrong package(s):\n' \
      "${slave}"
    printf '%s\n' "${package_errors}"
  } | \
    irc_say "${operator}"
  exit 4
fi

if [ ! -s "${tmp_dir}/package-ids" ]; then
  >&2 echo 'No package was expected, no package was built.'
  >&2 echo 'That should not happen!'
  exit 4
fi

# TODO: maybe, we should not put "any" packages into repositories of all
# architectures at once, but wait until they can actually be installed
# on that architecture? (They might be missing an architecture specific
# dependency)

# shellcheck disable=SC2016
{
  printf 'SELECT `binary_packages`.`id`,'
  mysql_package_name_query
  printf ',`t`.`id`,`t_a`.`name`,`t`.`name`'
  printf ' FROM `build_assignments`'
  mysql_join_build_assignments_binary_packages
  mysql_join_binary_packages_binary_packages_in_repositories
  printf ' AND `binary_packages_in_repositories`.`repository`=%s' \
    "${repository_ids__any_build_list}"
  mysql_join_binary_packages_architectures
  printf ' LEFT'
  mysql_join_binary_packages_compressions
  mysql_join_build_assignments_package_sources
  mysql_join_package_sources_upstream_repositories
  mysql_join_upstream_repositories_repository_moves
  printf ' JOIN `repositories` as `t`'
  printf ' ON `t`.`id`=`repository_moves`.`to_repository`'
  mysql_join_repositories_architectures 't' 't_a'
  printf ' JOIN `architecture_compatibilities`'
  printf ' ON `architecture_compatibilities`.`built_for`=`binary_packages`.`architecture`'
  printf ' AND `architecture_compatibilities`.`runs_on`=`t`.`architecture`'
  printf ' AND `architecture_compatibilities`.`fully_compatible`'
  printf ' WHERE `repository_moves`.`from_repository`=%s' \
    "${repository_ids__any_build_list}"
  printf ' AND `build_assignments`.`id`=from_base64("%s");\n' \
    "$(
      printf '%s' "${build_assignment_id}" | \
        base64 -w0
    )"
} | \
  mysql_run_query | \
  tr '\t' ' ' | \
  sort -u > \
  "${tmp_dir}/repository-ids"

errors=$(
  {
    cut -d' ' -f2 < \
      "${tmp_dir}/repository-ids" | \
      sort -u | \
      sed 's/^/repository-was-found: /'
    cut -d' ' -f2 < \
      "${tmp_dir}/package-ids" | \
      sort -u | \
      sed 's/^/package-was-sent: /'
  } | \
    sort -k2 | \
    uniq -uf1
)

if [ -n "${errors}" ]; then
  >&2 echo 'I cannot determine, where this package (or some part of it)'
  >&2 echo 'should be published:'
  >&2 printf '%s\n' "${errors}"
  >&2 echo 'This is some internal error and not (necessarily) your fault.'
  # We give a temporary error (although resolving this needs manual
  # intervention), because there is nothing wrong with the sent package
  # whence it does not need to be built again, but can simply be sent again.
  exit 1
fi

join -1 2 -2 2 -o 1.1,1.2,2.1 "${tmp_dir}/package-ids" "${tmp_dir}/signing-keys" \
| sponge "${tmp_dir}/package-ids"

mysql_load_min_and_max_versions

while read -r package_id package_name key_id; do
  # move namcap.logs
  mv \
    "${tmp_dir}/${package_name}-namcap.log.gz" \
    "${build_log_directory}/success/"
  # generate checksum
  sha512sum "${tmp_dir}/${package_name}" \
  | awk '{print "'"${package_id}"'\t" $1}' \
  | sed '
    s/$/\t'"${key_id}"'/
  ' \
  >> "${tmp_dir}/sha512sums"
  # generate list of required/provided libraries
  for lib in 'provides' 'needs'; do
    zcat "${tmp_dir}/${package_name}.so.${lib}.gz" | \
      sed '
        s/\(=\|<\|<=\|>=\|>\)\([^[:space:]-]\+\)$/\t\1\t\2/
        t
        h
        s/$/\t>=\t'"${min_version}"'/
      '"$(
        if [ "${lib}" = 'provides' ]; then
          printf '%s\n' \
            'p' \
            'g' \
            's/$/\t<=\t'"${max_version}"'/'
        fi
      )" | \
      sed '
        s/\(\s[0-9]\+\):\(\S\+\)$/\1\t\2/
        t coda
        s/\s\S\+$/\t0\0/
        :coda
        s/^/'"${package_id}"'\t'"$(
          if [ "${lib}" = 'needs' ]; then
            printf 'link\\t'
          fi
        )"'/
      ' >> "${tmp_dir}/${lib}"
  done
  # generate list of make-,check-,rundepends according to .PKGINFO
  extract_dependencies_from_package \
    "${tmp_dir}/${package_name}" \
  | sed 's/^/'"${package_id}"' /' \
  | tr ' ' '\t' \
  >> "${tmp_dir}/needs"
# TODO: this makes sense for provide= entries in .PKGINFO, too
done < \
  "${tmp_dir}/package-ids"

# move packages

cut -d' ' -f4,5 "${tmp_dir}/repository-ids" | \
  sort -u | \
  while read -r arch repo; do

    {
      printf 'cd "%s"\n' \
        "${tmp_dir}"
      printf 'mkdir -p "%s/%s"\n' \
        "${arch}" \
        "${repo}"

      printf 'failsafe_rsync'
      for suffix in 'db' 'files'; do
        printf ' "%s/%s/%s/%s.%s."*' \
          "${master_mirror_rsync_directory}" \
          "${arch}" \
          "${repo}" \
          "${repo}" \
          "${suffix}"
      done
      printf ' "%s/%s/"\n' \
        "${arch}" \
        "${repo}"

# TODO: sign database

      # repo-add -v -s -k "${repo_key}" "${destination}.db.tar.gz"
      printf 'repo-add "%s/%s/%s.db.tar.gz" ' \
        "${arch}" \
        "${repo}" \
        "${repo}"
      grep " $(str_to_regex "${arch} ${repo}")\$" "${tmp_dir}/repository-ids" \
      | cut -d' ' -f2 \
      | tr '\n' ' '
      printf '\n'
    } \
    | intent_something

  done

# upload the packages into /pool
{
  printf 'failsafe_rsync -c --copy-dest=/.transfer'
  find "${tmp_dir}" -maxdepth 1 -regextype sed \
    -regex '.*\.pkg\.\('"${package_compression_suffix_regex}"'\)\(\.sig\)\?' \
    -printf ' "%p"'
  printf ' "%s/pool/"\n' \
    "${master_mirror_rsync_directory}"
} \
| intent_something

# create symlinks
{
  find . -regextype sed \
    -regex '.*\.pkg\.\('"${package_compression_suffix_regex}"'\)\(\.sig\)\?' \
    -printf '%f\n' | \
    while read -r file; do
      printf 'rm "%s/%s"\n' \
        "${tmp_dir}" \
        "${file}"
      printf 'ln -s "../../pool/%s" "%s/%s"\n' \
        "${file}" \
        "${tmp_dir}" \
        "${file}"
    done
} \
| intent_something

# upload the database and the symlinks into /$arch/$repo

cut -d' ' -f4,5 "${tmp_dir}/repository-ids" | \
  sort -u | \
  while read -r arch repo; do
    {
      printf 'cd "%s"\n' \
        "${tmp_dir}"
      printf 'recompress_gz "%s"' \
        "${tmp_dir}"
      for suffix in '' '.old'; do
        printf ' "%s/%s/%s."*".tar.gz%s"' \
          "${arch}" \
          "${repo}" \
          "${repo}" \
          "${suffix}"
      done
      printf '\n'

      printf 'failsafe_rsync '
      for suffix in 'db' 'files'; do
        printf '"%s/%s/%s.%s."* ' \
          "${arch}" \
          "${repo}" \
          "${repo}" \
          "${suffix}"
        done
      grep " $(str_to_regex "${arch} ${repo}")\$" "${tmp_dir}/repository-ids" \
      | cut -d' ' -f2 \
      | sed '
        s,^,./,
        p
        s/$/.sig/
      ' \
      | tr '\n' ' '
      printf '"%s/%s/%s/"\n' \
        "${master_mirror_rsync_directory}" \
        "${arch}" \
        "${repo}"
    } \
    | intent_something
  done

# shellcheck disable=SC2016
{
  printf '{\n'
  printf '  mysql_run_query |'
  printf '  sort -u |'
  printf '  remove_old_package_versions\n'
  printf '} <<END_OF_MYSQL_QUERY\n'

  # insert checksums into database
  printf 'CREATE TEMPORARY TABLE `pkg_hashes` (`pkgid` BIGINT, `sha512sum` VARCHAR(128), `key_id` BIGINT);\n'
  printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `pkg_hashes`;\n' \
    "${tmp_dir}/sha512sums"
  printf 'UPDATE `binary_packages`'
  printf ' JOIN `pkg_hashes`'
  printf ' ON `pkg_hashes`.`pkgid`=`binary_packages`.`id`'
  printf ' SET `binary_packages`.`sha512sum`=`pkg_hashes`.`sha512sum`,'
  printf '`binary_packages`.`signing_key`=`pkg_hashes`.`key_id`;\n'
  printf 'COMMIT;\n'

  # insert provided/needed libraries into database
  for lib_link in 'pl:provides' 'nl:needs'; do
    printf 'CREATE TEMPORARY TABLE `%s` (' \
      "${lib_link%:*}"
      printf '`pkgid` BIGINT,'
    if [ "${lib_link}" = 'nl:needs' ]; then
      printf '`dep_type` VARCHAR(32),'
    fi
      printf '`lib` VARCHAR(128),'
      printf '`relation` VARCHAR(2),'
      printf '`epoch` MEDIUMINT,'
      printf '`version` VARCHAR(32)'
    printf ');\n'
    printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `%s`;\n' \
      "${tmp_dir}/${lib_link#*:}" "${lib_link%:*}"

    printf 'INSERT IGNORE INTO `install_targets` (`name`)'
    printf ' SELECT DISTINCT `%s`.`lib`' \
      "${lib_link%:*}"
    printf ' FROM `%s`;\n' \
      "${lib_link%:*}"
    printf 'COMMIT;\n'

# TODO: possibly remove install_target_providers with less restrictive
# versions than we have now
    printf 'INSERT IGNORE INTO `versions` (`epoch`,`version`)'
    printf ' SELECT DISTINCT `%s`.`epoch`,`%s`.`version`' \
      "${lib_link%:*}" "${lib_link%:*}"
    printf ' FROM `%s`;\n' \
      "${lib_link%:*}"
    printf 'COMMIT;\n'

    if [ "${lib_link%:*}" = 'pl' ]; then
      printf 'INSERT IGNORE INTO `install_target_providers` (`package`,`install_target`,`install_target_is_group`,`version`)'
    else
      # clear out the old dependencies - we will add the ones from the built package
      printf 'DELETE `dependencies`'
      printf ' FROM `nl`'
      printf ' JOIN `dependencies`'
      printf ' ON `dependencies`.`dependent`=`nl`.`pkgid`;\n'

      printf 'INSERT IGNORE INTO `dependencies` (`dependent`,`depending_on`,`dependency_type`,`version_relation`,`version`)'
    fi
    printf ' SELECT `%s`.`pkgid`,`install_targets`.`id`,' \
      "${lib_link%:*}"
    if [ "${lib_link%:*}" = 'nl' ]; then
      printf '`dependency_types`.`id`,'
      printf '`%s`.`relation`,' \
        "${lib_link%:*}"
    else
      printf '0,'
    fi
    printf '`versions`.`id`'
    printf ' FROM `install_targets`'
    printf ' JOIN `%s`' \
      "${lib_link%:*}"
    printf ' ON `%s`.`lib`=`install_targets`.`name`' \
      "${lib_link%:*}"
    if [ "${lib_link%:*}" = 'nl' ]; then
      printf ' JOIN `dependency_types`'
      printf ' ON `dependency_types`.`name`=`nl`.`dep_type`'
    fi
    printf ' JOIN `versions`'
    printf ' ON `versions`.`epoch`=`%s`.`epoch`' \
      "${lib_link%:*}"
    printf ' AND `versions`.`version`=`%s`.`version`' \
      "${lib_link%:*}"
    printf ';\n'
    printf 'COMMIT;\n'
  done

  # remove build_assignment's markers
  printf 'UPDATE `build_assignments`'
  printf ' SET'
  printf ' `build_assignments`.`is_broken`=0,'
  printf ' `build_assignments`.`priority`=0,'
  printf ' `build_assignments`.`return_date`=NOW()'
  printf ' WHERE `build_assignments`.`id`=from_base64("%s");\n' \
    "$(
      printf '%s' "${build_assignment_id}" | \
        base64 -w0
    )"
  printf 'COMMIT;\n'

  # insert into appropriate repositories and retrieve ids
  while read -r package_id _ repository_id _; do
    printf 'INSERT INTO `binary_packages_in_repositories` (`package`,`repository`,`is_to_be_deleted`) VALUES '
    printf '(%s,%s,0)' \
      "${package_id}" "${repository_id}"
    printf ' ON DUPLICATE KEY UPDATE `id`=LAST_INSERT_ID(`binary_packages_in_repositories`.`id`);\n'
    printf 'SELECT LAST_INSERT_ID();\n'
  done < \
    "${tmp_dir}/repository-ids"
  printf 'COMMIT;\n'

  # reschedule toolchain packages if they were not fully unblocked
  printf 'INSERT IGNORE INTO `binary_packages` ('
  printf '`build_assignment`,'
  printf '`epoch`,'
  printf '`pkgver`,'
  printf '`pkgrel`,'
  printf '`has_issues`,'
  printf '`is_tested`,'
  printf '`pkgname`,'
  printf '`architecture`,'
  printf '`sub_pkgrel`)'
  printf ' SELECT '
  printf '`binary_packages`.`%s`,' \
    'build_assignment' \
    'epoch' \
    'pkgver' \
    'pkgrel' \
    'has_issues' \
    'is_tested' \
    'pkgname' \
    'architecture'
  printf '`binary_packages`.`sub_pkgrel`+1'
  printf ' FROM `binary_packages`'
  printf ' JOIN `pkg_hashes`'
  printf ' ON `pkg_hashes`.`pkgid`=`binary_packages`.`id`'
  mysql_join_binary_packages_build_assignments
  mysql_join_build_assignments_package_sources
  mysql_join_package_sources_toolchain_order '' 'late'
  printf ' JOIN `toolchain_order` AS `early`'
  printf ' ON `early`.`number`<`late`.`number`'
  printf ' AND `early`.`pkgbase`!=`late`.`pkgbase`'
  mysql_join_toolchain_order_package_sources 'early' 'early_ps'
  mysql_join_package_sources_build_assignments 'early_ps' 'early_ba'
  mysql_join_build_assignments_binary_packages 'early_ba' 'early_bp'
  mysql_join_binary_packages_binary_packages_in_repositories 'early_bp' 'early_bpir'
  printf ' AND `early_bpir`.`repository`=%s' \
    "${repository_ids__any_build_list}"
  printf ' WHERE ('
    printf '`early_bp`.`architecture`=`binary_packages`.`architecture`'
    # shellcheck disable=SC2154
    printf ' OR `%s`.`architecture`=%s' \
      'early_bp' "${architecture_ids__any}" \
      'binary_packages' "${architecture_ids__any}"
  printf ') AND NOT EXISTS ('
    printf 'SELECT 1'
    printf ' FROM `binary_packages` AS `early_bp`'
    mysql_join_binary_packages_binary_packages_in_repositories 'early_bp' 'early_bpir'
    mysql_join_binary_packages_in_repositories_repositories 'early_bpir' 'early_r'
    printf ' WHERE `early_r`.`is_on_master_mirror`'
    printf ' AND `early_bp`.`build_assignment`=`early_ba`.`id`'
  printf ');\n'

  join_part=$(
    printf ' JOIN `pkg_hashes`'
    printf ' ON `built_bp`.`id`=`pkg_hashes`.`pkgid`'
    printf ' JOIN `binary_packages` AS `new_bp`'
    printf ' ON'
    printf ' `built_bp`.`%s`=`new_bp`.`%s` AND' \
      'build_assignment' 'build_assignment' \
      'epoch' 'epoch' \
      'pkgver' 'pkgver' \
      'pkgrel' 'pkgrel' \
      'has_issues' 'has_issues' \
      'is_tested' 'is_tested' \
      'pkgname' 'pkgname' \
      'architecture' 'architecture'
    printf ' `built_bp`.`sub_pkgrel`+1=`new_bp`.`sub_pkgrel`'
  )
  printf 'INSERT IGNORE INTO `binary_packages_in_repositories`'
  printf ' (`package`,`repository`,`is_to_be_deleted`)'
  printf ' SELECT `new_bp`.`id`,%s,0' \
    "${repository_ids__any_build_list}"
  printf ' FROM `binary_packages` AS `built_bp`'
  printf '%s;\n' "${join_part}"
  printf 'INSERT IGNORE INTO `dependencies`'
  printf ' (`dependent`,`depending_on`,`dependency_type`,`version`,`version_relation`)'
  printf ' SELECT `new_bp`.`id`'
  printf ',`dependencies`.`%s`' \
    'depending_on' \
    'dependency_type' \
    'version' \
    'version_relation'
  printf ' FROM `dependencies`'
  mysql_join_dependencies_binary_packages '' 'built_bp'
  printf '%s;\n' "${join_part}"
  # TODO: set _correct_ version of install_target_providers
  printf 'INSERT IGNORE INTO `install_target_providers`'
  printf ' (`package`,`install_target`,`version`,`install_target_is_group`)'
  printf ' SELECT `new_bp`.`id`'
  printf ',`install_target_providers`.`%s`' \
    'install_target' \
    'version' \
    'install_target_is_group'
  printf ' FROM `install_target_providers`'
  mysql_join_install_target_providers_binary_packages '' 'built_bp'
  printf '%s;\n' "${join_part}"

  # remove from build-list
  printf 'DELETE `binary_packages_in_repositories`'
  printf ' FROM `binary_packages_in_repositories`'
  printf ' JOIN `pkg_hashes`'
  printf ' ON `pkg_hashes`.`pkgid`=`binary_packages_in_repositories`.`package`'
  printf ' WHERE `binary_packages_in_repositories`.`repository`=%s;\n' \
    "${repository_ids__any_build_list}"
  printf 'COMMIT;\n'

  # update package information
  printf 'UPDATE `binary_packages`'
  printf ' SET'
  printf ' `binary_packages`.`has_issues`=0,'
  printf ' `binary_packages`.`is_tested`=0'
  printf ' WHERE `binary_packages`.`id` IN ('
  cut -d' ' -f1 < \
    "${tmp_dir}/package-ids" | \
    base64_encode_each | \
    sed '
      s/^/from_base64("/
      s/$/"),/
      $ s/,$//
    '
  printf ');\n'
  printf 'COMMIT;\n'

  # remove from build slave's `currently_building`
  printf 'UPDATE `build_slaves`'
  printf ' SET `build_slaves`.`currently_building`=NULL,'
  printf ' `build_slaves`.`last_action`=NULL,'
  printf ' `build_slaves`.`logged_lines`=NULL,'
  printf ' `build_slaves`.`trials`=NULL'
  printf ' WHERE `build_slaves`.`currently_building`=from_base64("%s");\n' \
    "$(
      printf '%s' "${build_assignment_id}" | \
        base64 -w0
    )"
  printf 'COMMIT;\n'

  # remove broken loops
  printf 'CREATE TEMPORARY TABLE `loops_to_delete` (`loop` MEDIUMINT);\n'
  printf 'INSERT IGNORE INTO `loops_to_delete`'
  printf ' SELECT `build_dependency_loops`.`loop`'
  printf ' FROM `build_dependency_loops`'
  mysql_join_build_dependency_loops_binary_packages
  mysql_join_binary_packages_binary_packages_in_repositories
  printf ' WHERE NOT `binary_packages_in_repositories`.`repository`=%s;\n' \
    "${repository_ids__any_build_list}"
  printf 'COMMIT;\n'
  printf 'DELETE FROM `build_dependency_loops`'
  printf ' WHERE EXISTS ('
    printf 'SELECT 1'
    printf ' FROM `loops_to_delete`'
    printf ' WHERE `loops_to_delete`.`loop`=`build_dependency_loops`.`loop`'
  printf ');\n'
  printf 'DROP TEMPORARY TABLE `loops_to_delete`;\n'

  printf 'END_OF_MYSQL_QUERY\n'
} \
| sed 's/`/\\`/g' \
| intent_something

# handle cleanup in intentions-queue
printf 'rm -rf --one-file-system "%s"\n' \
  "${tmp_dir}" \
| intent_something
trap - EXIT

execute_all_intentions

cd "${base_dir}"

export TMPDIR="${work_dir}"
trigger_mirror_refreshs