#!/bin/sh

# shellcheck disable=SC2119

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

# this script shall host all the tests that are too slow to be run on
# the buildmaster (thus, it runs asynchronously and must therefor be
# resilient against transient states of the buildmaster):
# - check sanity of git
# - check expiration of package and master keys
# - complain about packages residing too long in [staging]/[testing]
# - check if our packages are still available upstream
# TODO:
# - check for differences of dependencies between mysql and git
# - check for differences of dependencies between mysql and packages
# - check for installability of packages

# shellcheck disable=SC2016
print_usage() {
  >&2 echo 'usage: nit-picker [-n|-r|-x] [$single_test]'
  >&2 echo '  -n: Do not join irc.'
  >&2 echo '  -r: Remove superfluid dependencies in the database.'
  >&2 echo '      To avoid catastrophic breakage, this option requires'
  >&2 echo '      $single_test being set.'
  >&2 echo '  -x: Exit upon first found inconsistency.'
  >&2 echo '  $single_test: Only execute the given test.'
}

exit_fast=false
irc=true
repair_dependencies=false

while [ $# -ge 1 ]; do
  case "$1" in
    '-n')
      >&2 echo 'not joining irc'
      irc=false
    ;;
    '-r')
      >&2 echo 'repair all wrong dependencies (remove superfluid, add missing)'
      repair_dependencies=true
    ;;
    '-x')
      >&2 echo 'exit, when the first inconsistency is being found'
      exit_fast=true
    ;;
    '-*')
      >&2 printf 'unknown option "%s"\n' "$1"
      print_usage
      exit 1
    ;;
    *)
      break
    ;;
  esac
  shift
done

if [ $# -eq 0 ]; then
  single_run=false
else
  single_run=true
fi

if ${repair_dependencies} \
&& ! ${single_run}; then
  >&2 echo 'Option "-r" needs a test.'
  print_usage
  exit 1
fi

clean_up() {
  rm -rf --one-file-system "${tmp_dir}"
}

tmp_dir=$(mktemp -d 'tmp.nit-picker.XXXXXXXXXX' --tmpdir)
trap 'clean_up' EXIT

if ${irc}; then
  if pgrep -x ii; then
    >&2 echo 'ii is already running - this will not work'
    exit 1
  fi

  rm -rf --one-file-system "${irc_dir}"

  ii -s irc.libera.chat -n nit-picker -f nit-picker >/dev/null 2>&1 &
  ii_pid=$!

  clean_up() {
    rm -rf --one-file-system "${tmp_dir}"
    kill "${ii_pid}"
  }

  # wait for nickserv complaint
  while ! grep -qF 'This nickname is registered. Please choose a different nickname' "${irc_dir}/nickserv/out"; do
    sleep 1
  done

  # register
  printf 'identify %s\n' "${irc_password}" | \
    sponge "${irc_dir}/nickserv/in"

  # wait for registering to succeed
  while ! grep -qF 'You are now identified for' "${irc_dir}/nickserv/out"; do
    sleep 1
  done

  # join channel
  echo '/j #archlinux32-devops' | \
    sponge "${irc_dir}/in"

  while [ ! -f "${irc_dir}/#archlinux32-devops/out" ]; do
    sleep 1
  done

fi

# shellcheck disable=SC2120
local_irc_say() {
  if ${irc}; then
    if printf 'SHOW STATUS LIKE "Slave_running"' | mysql_run_query | cut -f2 | grep -qxF 'ON'; then
      irc_say "$@"
    else
      printf 'The replication slave is not running.\n' \
      | irc_say 'deep42thought'
      irc_say 'deep42thought'
    fi
  else
    sed 's/^/irc: /'
  fi
  if ${exit_fast}; then
    exit 1
  fi
  if ! ${single_run}; then
    sleep 60
  fi
}

mysql_load_min_and_max_versions

last_once_a_day_check=0

while pgrep -x ii >/dev/null \
|| ! ${irc}; do
  if [ "$(date +%s)" -gt "$((last_once_a_day_check + 60*60*24))" ]; then
    do_once_a_day_checks=true
    last_once_a_day_check=$(date +%s)
  else
    do_once_a_day_checks=false
  fi
  if ${single_run}; then
    printf '%s\n' "$*"
  else
    # shellcheck disable=SC2016
    {
      if ! "${repair_dependencies}"; then
        printf 'SELECT DISTINCT'
        printf ' "commit",'
        printf '`git_repositories`.`name`,'
        printf '`git_repositories`.`head`,'
        printf '`package_sources`.`git_revision`'
        printf ' FROM `package_sources`'
        mysql_join_package_sources_upstream_repositories
        mysql_join_upstream_repositories_git_repositories
        printf ';\n'

        printf 'SELECT DISTINCT'
        printf ' "commit",'
        printf '"archlinux32",'
        # shellcheck disable=SC2154
        printf '"%s",' \
          "${repo_heads__archlinux32}"
        printf '`package_sources`.`mod_git_revision`'
        printf ' FROM `package_sources`'
        printf ';\n'

        printf 'SELECT DISTINCT'
        printf ' "binary-signature",'
        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
        mysql_join_binary_packages_in_repositories_repositories
        printf ' WHERE `repositories`.`is_on_master_mirror`'
        printf ';\n'

        if "${do_once_a_day_checks}"; then
          printf 'SELECT DISTINCT'
          printf ' "keyring",'
          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
          mysql_join_binary_packages_in_repositories_repositories
          printf ' WHERE `repositories`.`is_on_master_mirror`'
          printf ' AND `binary_packages`.`pkgname` IN ('
            printf '"archlinux32-keyring",'
            printf '"archlinux32-keyring-transition"'
          printf ');\n'

          printf 'SELECT'
          printf ' "build-duration",'
          printf '`build_slaves`.`name`'
          printf ' FROM `build_slaves`'
          printf ';\n'

          printf 'SELECT DISTINCT'
          printf ' "package-blob",'
          printf 'CONCAT('
            printf '`binary_packages`.`build_assignment`,"-",'
            printf '`binary_packages`.`epoch`,":",'
            printf '`binary_packages`.`pkgver`,"-",'
            printf '`binary_packages`.`pkgrel`,".",'
            printf '`binary_packages`.`sub_pkgrel`'
          printf '),'
          printf '`repositories`.`architecture`'
          printf ' FROM `binary_packages`'
          mysql_join_binary_packages_binary_packages_in_repositories
          mysql_join_binary_packages_in_repositories_repositories
          printf ' AND `repositories`.`is_on_master_mirror`;\n'

          printf 'SELECT "repository-duration",CONCAT('
            printf '`architectures`.`name`,'
            printf '"/",'
            printf '`repositories`.`name`'
          printf '),'
          printf '`repositories`.`stability`'
          printf ' FROM `repositories`'
          mysql_join_repositories_architectures
          # shellcheck disable=SC2154
          printf ' WHERE `repositories`.`stability` IN (%s,%s);\n' \
            "${repository_stability_ids__testing}" \
            "${repository_stability_ids__staging}"

          printf 'SELECT DISTINCT'
          printf ' "upstream-availability",'
          printf '`binary_packages`.`pkgname`'
          printf ' FROM `binary_packages`'
          mysql_join_binary_packages_binary_packages_in_repositories
          printf ' AND NOT `binary_packages_in_repositories`.`is_to_be_deleted`'
          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
          mysql_join_build_assignments_package_sources
          printf ' AND `package_sources`.`uses_upstream`;\n'
        fi
      fi

      printf 'SELECT DISTINCT'
      printf ' "binary-dependencies",'
      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
      mysql_join_binary_packages_in_repositories_repositories
      printf ' WHERE `repositories`.`is_on_master_mirror`'
      printf ';\n'
    } | \
      mysql_run_query | \
      tr '\t' ' ' | \
      shuf
  fi | \
    while read -r action parameters; do
      if ${irc} && ! pgrep -x ii >/dev/null; then
        >&2 echo 'ii is no longer running'
        break
      fi
      case "${action}" in
        'commit') # check whether a given commit is present in the git repo
          git_repo="${parameters%% *}"
          git_rev="${parameters#"${git_repo}" }"
          git_head="${git_rev%% *}"
          git_rev="${git_rev#"${git_head}" }"
          # shellcheck disable=SC2016
          eval "$(
            printf 'git_dir="${repo_paths__%s}"\n' \
              "${git_repo}"
          )"
          # shellcheck disable=SC2154
          if ! git -C "${git_dir}" cat-file -t "${git_rev}" 2> /dev/null | \
            grep -qxF 'commit'; then
            git -C "${git_dir}" fetch --all -p >/dev/null 2>&1
            if ! git -C "${git_dir}" cat-file -t "${git_rev}" 2> /dev/null | \
              grep -qxF 'commit'; then
              printf 'commit %s is missing from repository %s\n' \
                "${git_rev}" \
                "${git_repo}" \
              | local_irc_say
            fi
          fi
          # shellcheck disable=SC2154
          if ! git -C "${git_dir}" cat-file -t "${git_head}" 2> /dev/null | \
            grep -qxF 'commit'; then
            git -C "${git_dir}" fetch --all -p >/dev/null 2>&1
            if ! git -C "${git_dir}" cat-file -t "${git_head}" 2> /dev/null | \
              grep -qxF 'commit'; then
              printf 'commit %s is missing from repository %s\n' \
                "${git_head}" \
                "${git_repo}" \
              | local_irc_say
            fi
          fi
          # shellcheck disable=SC2154
          if ! git -C "${git_dir}" merge-base --is-ancestor "${git_rev}" "${git_head}" 2> /dev/null; then
            current_git_head=$(
              # shellcheck disable=SC2016
              {
                printf 'SELECT DISTINCT'
                printf ' `git_repositories`.`head`'
                printf ' FROM `git_repositories`'
                printf ' WHERE `git_repositories`.`name`=from_base64("%s");\n' \
                  "$(
                    printf '%s' "${git_repo}" \
                    | base64 -w0
                  )"
              } \
              | mysql_run_query
            )
            if ! git -C "${git_dir}" merge-base --is-ancestor "${git_rev}" "${current_git_head}" 2> /dev/null; then
              git -C "${git_dir}" fetch --all -p >/dev/null 2>&1
              if ! git -C "${git_dir}" merge-base --is-ancestor "${git_rev}" "${current_git_head}" 2> /dev/null; then
                printf 'commit %s is not an ancestor of HEAD %s in repository %s\n' \
                  "${git_rev}" \
                  "${current_git_head}" \
                  "${git_repo}" \
                | local_irc_say
              fi
            fi
          fi
        ;;
        'binary-dependencies')
          if ! ${master_mirror_rsync_command} \
            "${master_mirror_rsync_directory}/pool/${parameters}" \
            "${tmp_dir}/"; then
            rm -f "${tmp_dir}/${parameters}"
            continue
          fi
          extract_dependencies_from_package \
            "${tmp_dir}/${parameters}" \
          > "${tmp_dir}/pkg-deps"
          # shellcheck disable=SC2016
          {
            printf 'SELECT'
            printf ' `dependency_types`.`name`,'
            printf '`install_targets`.`name`,'
            printf '`dependencies`.`version_relation`,'
            printf '`versions`.`epoch`,'
            printf '`versions`.`version`'
            printf ' FROM `binary_packages`'
            mysql_join_binary_packages_dependencies
            mysql_join_binary_packages_architectures
            printf ' LEFT'
            mysql_join_binary_packages_compressions
            mysql_join_dependencies_dependency_types
            mysql_join_dependencies_versions
            mysql_join_dependencies_install_targets
            printf ' WHERE '
            mysql_package_name_query
            printf '="%s"' \
              "${parameters}"
            printf ' AND `dependency_types`.`name` IN ("run","make","check")'
            printf ';\n'
          } \
          | mysql_run_query \
          | tr '\t' ' ' \
          | sort -u \
          > "${tmp_dir}/db-deps"
          if ! diff -q "${tmp_dir}/db-deps" "${tmp_dir}/pkg-deps"; then
            if "${repair_dependencies}"; then
              cat "${tmp_dir}/db-deps" "${tmp_dir}/pkg-deps" "${tmp_dir}/pkg-deps" \
              | sort \
              | uniq -u \
              | sed 's@^@'"${parameters}"' @' \
              | tr ' ' '\t' \
              >> "${tmp_dir}/remove-those-dependencies"
              cat "${tmp_dir}/db-deps" "${tmp_dir}/db-deps" "${tmp_dir}/pkg-deps" \
              | sort \
              | uniq -u \
              | sed 's@^@'"${parameters}"' @' \
              | tr ' ' '\t' \
              >> "${tmp_dir}/add-those-dependencies"
            else
              build_date=$(
                bsdtar -Oxf "${tmp_dir}/${parameters}" '.PKGINFO' \
                | sed '
                  s/^builddate = //
                  t
                  d
                '
              )
              build_date=$(
                date -I -d@"${build_date}"
              )
              printf 'dependencies of %s (built on %s) differ between the package and our database\n' \
                "${parameters}" \
                "${build_date}" \
              | local_irc_say
              if ! ${irc}; then
                diff -u --color "${tmp_dir}/db-deps" "${tmp_dir}/pkg-deps" || true
              fi
            fi
          fi
          rm \
            "${tmp_dir}/${parameters}" \
            "${tmp_dir}/db-deps" \
            "${tmp_dir}/pkg-deps"
        ;;
        'binary-signature')
          if ! ${master_mirror_rsync_command} \
            "${master_mirror_rsync_directory}/pool/${parameters}" \
            "${master_mirror_rsync_directory}/pool/${parameters}.sig" \
            "${tmp_dir}/"; then
            rm -f "${tmp_dir}/${parameters}" "${tmp_dir}/${parameters}.sig"
            continue
          fi
          unset error_message
          if ! gpg_output=$(
            gpg --batch --status-fd 1 -q --homedir /etc/pacman.d/gnupg \
              --verify "${tmp_dir}/${parameters}.sig" "${tmp_dir}/${parameters}" \
              2>/dev/null
          ); then
            sleep 1
            if ! gpg_output=$(
              gpg --batch --status-fd 1 -q --homedir /etc/pacman.d/gnupg \
                --verify "${tmp_dir}/${parameters}.sig" "${tmp_dir}/${parameters}" \
                2>/dev/null
            ); then
              error_message="package ${parameters} has an invalid signature."
            fi
          fi
          if [ -z "${error_message}" ]; then
            gpg_key=$(
              printf '%s\n' "${gpg_output}" \
              | sed '
                s/^\[GNUPG:] KEY_CONSIDERED \([0-9A-F]\{40\}\) 0$/\1/
                t
                d
              ' \
              | sort -u
            )
            if [ -z "${gpg_key}" ]; then
              error_message="cannot find pgp_key of package ${parameters}."
            fi
          fi
          if [ -z "${error_message}" ]; then
            for expiration in $(
              gpg --batch --homedir /etc/pacman.d/gnupg --with-colons --list-keys "0x${gpg_key}" \
              2>/dev/null \
              | grep '^\(sub\|pub\):' \
              | cut -d: -f7
            ); do
              expiration_days=$(((expiration - $(date +%s))/24/60/60))
              if [ ${expiration_days} -lt 100 ]; then
                error_message=$(
                  printf 'signing key %s (from %s) for package %s expires on %s (in %s < 100 days).\n' \
                  "${gpg_key}" \
                  "$(
                    gpg --batch --homedir /etc/pacman.d/gnupg --with-colons --list-keys "0x${gpg_key}" \
                    2>/dev/null \
                    | grep '^\(uid\):' \
                    | cut -d: -f10
                  )" \
                  "${parameters}" \
                  "$(date -I -d@"${expiration}")" \
                  "${expiration_days}"
                )
                break
              fi
            done
          fi
          if [ -n "${error_message}" ]; then
            printf '%s\n' "${error_message}" \
            | local_irc_say
          fi
          rm \
            "${tmp_dir}/${parameters}" \
            "${tmp_dir}/${parameters}.sig"
        ;;
        'build-duration')
          infos=$(
            # shellcheck disable=SC2016
            {
              printf 'SELECT'
              printf '`persons`.`name`,'
              printf '`build_slaves`.`name`,'
              printf '`architectures`.`name`,'
              printf '`package_sources`.`pkgbase`,'
              printf 'UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP('
                printf 'MAX('
                  printf 'IF('
                    printf '`ssh_log`.`action`="get-assignment",'
                    printf '`ssh_log`.`date`,'
                    printf 'NULL'
                  printf ')'
                printf ')'
              printf ')'
              printf ' FROM `build_slaves`'
              mysql_join_build_slaves_build_assignments
              mysql_join_build_assignments_architectures
              mysql_join_build_assignments_package_sources
              mysql_join_build_slaves_ssh_keys
              mysql_join_ssh_keys_persons
              mysql_join_build_slaves_ssh_log
              printf ' WHERE `ssh_log`.`date`>=ADDDATE(NOW(),"-7 00:00:00")'
              printf ' AND `build_slaves`.`name`=from_base64("%s")' \
                "$(
                  printf '%s' "${parameters}" \
                  | base64 -w0
                )"
              printf ' GROUP BY `build_slaves`.`id`'
              printf ';\n'
            } \
            | mysql_run_query \
            | tr '\t' ' '
          )
          if [ -z "${infos}" ]; then
            continue
          fi
          if [ "${infos##* }" -lt $((60*60*24)) ]; then
            continue
          fi
          printf '%s %s\n' \
            "${infos% *}" \
            "$(
              date -d"@${infos##* }" +'%-j %H:%M:%S'
            )" \
          | awk '{print $1 ": your slave " $2 " builds " $3 "/" $4 " for more than a day, now (" ($5-1) " day(s) " $6 ")"}' \
          | local_irc_say
        ;;
        'keyring')
          if ! ${master_mirror_rsync_command} \
            "${master_mirror_rsync_directory}/pool/${parameters}" \
            "${tmp_dir}/"; then
            rm -f "${tmp_dir}/${parameters}"
            continue
          fi
          mkdir "${tmp_dir}/pkg" "${tmp_dir}/gpg-home"
          bsdtar -C "${tmp_dir}/pkg" -xf "${tmp_dir}/${parameters}" --strip-components=4 'usr/share/pacman/keyrings'

          gpg --no-permission-warning --quiet --homedir "${tmp_dir}/gpg-home" --import \
          < "${tmp_dir}/pkg/archlinux32.gpg"
          cut -d: -f1 "${tmp_dir}/pkg/archlinux32-trusted" \
          | while read -r gpg_key; do
            gpg --no-permission-warning --homedir "${tmp_dir}/gpg-home" --with-colons --list-keys "0x${gpg_key}" \
            | grep '^pub:\|^sub:' \
            | cut -d: -f7 \
            | grep -vxF '' \
            | sort -u \
            | while read -r expiration; do
              expiration_days=$(((expiration - $(date +%s))/24/60/60))
              if [ ${expiration_days} -lt 100 ]; then
                printf 'key %s (from %s) in package %s expires on %s (in %s < 100 days).\n' \
                "${gpg_key}" \
                "$(
                  gpg --batch --homedir "${tmp_dir}/gpg-home" --with-colons --list-keys "0x${gpg_key}" \
                  2>/dev/null \
                  | grep '^\(uid\):' \
                  | cut -d: -f10
                )" \
                "${parameters}" \
                "$(date -I -d@"${expiration}")" \
                "${expiration_days}" \
                | local_irc_say
              fi
            done
          done

          rm "${tmp_dir}/${parameters}"
          rm -rf --one-file-system "${tmp_dir}/gpg-home" "${tmp_dir}/pkg"
        ;;
        'package-blob')
          infos=$(
            # shellcheck disable=SC2016
            {
              printf 'SELECT DISTINCT'
              printf ' CONCAT('
                printf '`architectures`.`name`,'
                printf '"/",'
                printf '`package_sources`.`pkgbase`'
              printf '),'
              printf '`repositories`.`name`'
              printf ' FROM `binary_packages`'
              mysql_join_binary_packages_build_assignments
              printf ' AND CONCAT('
                printf '`binary_packages`.`build_assignment`,"-",'
                printf '`binary_packages`.`epoch`,":",'
                printf '`binary_packages`.`pkgver`,"-",'
                printf '`binary_packages`.`pkgrel`,".",'
                printf '`binary_packages`.`sub_pkgrel`'
              printf ')=from_base64("%s")' \
                "$(
                  printf '%s' "${parameters% *}" \
                  | base64 -w0
                )"
              mysql_join_build_assignments_package_sources
              mysql_join_binary_packages_binary_packages_in_repositories
              mysql_join_binary_packages_in_repositories_repositories
              printf ' AND `repositories`.`is_on_master_mirror`'
              printf ' AND `repositories`.`architecture`=%s' \
                "${parameters#* }"
              mysql_join_repositories_architectures
              printf ';\n'
            } \
            | mysql_run_query \
            | tr '\t' ' '
          )
          # 0 is ok, too - then the package was removed in the meantime
          if [ "$(printf '%s\n' "${infos}" | wc -l)" -gt 1 ]; then
            printf '%s\n' \
              "${infos}" \
            | sed '
              :a
                $! N
                s/\n\S\+ /, /
                ta
              s/^\S\+ /parts of \0are in different repositories: /
            ' \
            | local_irc_say
          fi
        ;;
        'upstream-availability')
          upstream_mirror=$(
            # shellcheck disable=SC2016
            sed '
              s/^\s*Server\s*=\s*\(\S\+\)\$arch\(\S*\)$/\1x86_64\2/
              t
              d
            ' '/etc/pacman.d/mirrorlist' \
            | head -n1
          )
          if [ -n "$(
            # shellcheck disable=SC2154
            for repo in ${upstream_repository_names}; do
              if [ "${repo}" = 'build-support' ]; then
                continue
              fi
              if curl -s "$(
                # shellcheck disable=SC2016
                printf '%s\n' "${upstream_mirror}" \
                | sed '
                  s/\(\$repo\)/'"${repo}"'/
                  s@/$@@
                  s@$@/'"${repo}"'.db.tar.gz@
                '
              )" 2>/dev/null \
              | tar -tzf - 2>/dev/null \
              | grep '^'"$(str_to_regex "${parameters}")"'-[^-]\+-[^-]\+/desc$'; then
                break
              fi
            done
          )" ]; then
            continue
          fi
          # shellcheck disable=SC2016
          if {
            printf 'SELECT'
            printf ' COUNT(1)'
            printf ' FROM `binary_packages`'
            mysql_join_binary_packages_binary_packages_in_repositories
            printf ' AND `binary_packages`.`pkgname`=from_base64("%s")' \
              "$(
                printf '%s\n' "${parameters}" \
                | base64 -w0
              )"
            printf ' AND NOT `binary_packages_in_repositories`.`is_to_be_deleted`'
            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
            mysql_join_build_assignments_package_sources
            printf ' AND `package_sources`.`uses_upstream`;\n'
          } \
          | mysql_run_query \
          | grep -qxF 0; then
            continue
          fi
          printf 'Package %s is still in our repositories or on the build-list, but is not available upstream.\n' \
            "${parameters}" \
          | local_irc_say
        ;;
        'repository-duration')
          case "${parameters#* }" in
            "${repository_stability_ids__testing}")
              duration=$((max_testing_duration+3))
            ;;
            "${repository_stability_ids__staging}")
              duration=3
            ;;
            *)
              >&2 printf 'unknown stability: %s\n' "${parameters#* }"
              continue
            ;;
          esac
          errors=$(
            ${master_mirror_rsync_command} "${master_mirror_rsync_directory}/${parameters% *}/" \
            | sed '
              s/^l\(\S\+\s\+\)\{2\}\(\(\S\+\s\+\)\{2\}\S\+\.pkg\.tar\S*\)\.sig -> \S\+$/\2/
              t
              d
            ' \
            | {
              while read -r date time file; do
                printf '%s %s\n' \
                  "$(
                    date +%s -d"${date} ${time}"
                  )" \
                  "${file}"
              done
              printf '%s ####CUT_HERE####\n' \
                "$((
                  $(date +%s) - duration*60*60*24
                ))"
            } \
            | sort -k1n,1 \
            | sed '/ ####CUT_HERE####$/q' \
            | sed '$d' \
            | while read -r time file; do
              printf '%s: since %s (%s days)\n' \
                "${file}" \
                "$(
                  date -d"@${time}" -I
                )" \
                "$((
                  ($(date +%s) - time)/24/60/60
                ))"
            done
          )
          if [ -z "${errors}" ]; then
            continue
          fi
          {
            printf 'The following packages are longer than %s days in %s:\n' \
              "${duration}" \
              "${parameters% *}"
            printf '%s\n' "${errors}" \
            | head -n5
            if [ "$(
              printf '%s\n' "${errors}" \
              | wc -l
            )" -gt 5 ]; then
              printf '... (%s total)\n' \
                "$(
                  printf '%s\n' "${errors}" \
                  | wc -l
                )"
            fi
          } \
          | local_irc_say
        ;;
        *)
          >&2 printf 'action "%s" is not yet implemented.\n' "${action}"
          >&2 printf 'implemented actions are:\n'
          >&2 printf '  %s\n' \
            commit binary-dependencies binary-signature build-duration \
            keyring package-blob upstream-availability \
            repository-duration
        ;;
      esac
    done
  if ${single_run}; then
    >&2 echo 'only one run was requested'
    break
  fi
  sleep 120
done

{
  if [ -s "${tmp_dir}/remove-those-dependencies" ]; then
    # shellcheck disable=SC2016
    {
      printf 'CREATE TEMPORARY TABLE `rd`('
        printf '`pf` VARCHAR(128),'
        printf '`dt` VARCHAR(32),'
        printf '`it` VARCHAR(128),'
        printf '`vr` VARCHAR(2),'
        printf '`e` MEDIUMINT,'
        printf '`v` VARCHAR(64)'
      printf ');\n'
      printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `rd`(`pf`,`dt`,`it`,`vr`,`e`,`v`);\n' \
        "${tmp_dir}/remove-those-dependencies"
      printf 'SELECT `d`.`id` FROM `binary_packages`'
      mysql_join_binary_packages_architectures
      printf ' LEFT'
      mysql_join_binary_packages_compressions
      printf ' JOIN `rd`'
      printf ' ON `rd`.`pf`='
      mysql_package_name_query
      mysql_join_binary_packages_dependencies '' 'd'
      printf ' AND `d`.`version_relation`=`rd`.`vr`'
      mysql_join_dependencies_install_targets 'd' 'it'
      printf ' AND `it`.`name`=`rd`.`it`'
      mysql_join_dependencies_versions 'd' 'v'
      printf ' AND `v`.`epoch`=`rd`.`e`'
      printf ' AND `v`.`version`=`rd`.`v`'
      mysql_join_dependencies_dependency_types 'd' 'dt'
      printf ' AND `dt`.`name`=`rd`.`dt`'
      printf ';\n'
    } \
    | mysql_run_query \
    | sed '
      1 i DELETE `dependencies` FROM `dependencies` WHERE `dependencies`.`id` IN (
      $! s/$/,/
      $ a );
    '
  fi
  # shellcheck disable=SC2016
  if [ -s "${tmp_dir}/add-those-dependencies" ]; then
    rsync "${tmp_dir}/add-those-dependencies" 'buildmaster:/tmp/add-those-dependencies' >/dev/null 2>&1

    printf 'CREATE TEMPORARY TABLE `ad`('
      printf '`pf` VARCHAR(128),'
      printf '`dt` VARCHAR(32),'
      printf '`it` VARCHAR(128),'
      printf '`vr` VARCHAR(2),'
      printf '`e` MEDIUMINT,'
      printf '`v` VARCHAR(64)'
    printf ');\n'

    printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `ad`(`pf`,`dt`,`it`,`vr`,`e`,`v`);\n' \
      '/tmp/add-those-dependencies'

    printf 'INSERT IGNORE INTO `install_targets`('
      printf '`name`'
    printf ')'
    printf ' SELECT'
    printf ' `ad`.`it`'
    printf ' FROM `ad`;\n'

    printf 'INSERT IGNORE INTO `versions`('
      printf '`epoch`,'
      printf '`version`'
    printf ')'
    printf ' SELECT'
    printf ' `ad`.`e`,'
    printf '`ad`.`v`'
    printf ' FROM `ad`;\n'

    printf 'INSERT IGNORE INTO `dependencies`('
      printf '`dependent`,'
      printf '`depending_on`,'
      printf '`dependency_type`,'
      printf '`version`,'
      printf '`version_relation`'
    printf ')'
    printf 'SELECT'
    printf ' `binary_packages`.`id`,'
    printf '`install_targets`.`id`,'
    printf '`dependency_types`.`id`,'
    printf '`versions`.`id`,'
    printf '`ad`.`vr`'
    printf ' FROM `binary_packages`'
    mysql_join_binary_packages_architectures
    printf ' LEFT'
    mysql_join_binary_packages_compressions
    printf ' JOIN `ad`'
    printf ' ON `ad`.`pf`='
    mysql_package_name_query
    printf ' JOIN `install_targets`'
    printf ' ON `install_targets`.`name`=`ad`.`it`'
    printf ' JOIN `versions`'
    printf ' ON `versions`.`epoch`=`ad`.`e`'
    printf ' AND `versions`.`version`=`ad`.`v`'
    printf ' JOIN `dependency_types`'
    printf ' ON `dependency_types`.`name`=`ad`.`dt`'
    printf ';\n'
  fi
} \
| ifne ssh -o PasswordAuthentication=No buildmaster 'mysql buildmaster
  rm -f "/tmp/add-those-dependencies"
'