#!/bin/sh

# report about status of build master - according to mysql database

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

# TODO: add all (necessary) features from build-master-status

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

# do not block if locked
exec 9> "${sanity_check_lock_file}"
if ! flock -n 9; then
  >&2 echo 'Mysql-Sanity check skipped, cannot acquire lock.'
  exit
fi

# shellcheck disable=SC2016
{
  printf 'SELECT DISTINCT `package_sources`.`pkgbase`,`package_sources`.`git_revision`,`package_sources`.`mod_git_revision`,`upstream_repositories`.`name`'
  printf ' FROM `package_sources`'
  mysql_join_package_sources_upstream_repositories
  mysql_join_package_sources_build_assignments
  mysql_join_build_assignments_binary_packages
  mysql_join_binary_packages_repositories
  printf ' WHERE `repositories`.`name`="build-list"'
} | \
  mysql_run_query | \
  tr '\t' ' ' | \
  sort > \
  "${tmp_dir}/build-list.mysql"
sort "${work_dir}/build-list" > \
  "${tmp_dir}/build-list.file"

# shellcheck disable=SC2016
{
  printf 'SELECT DISTINCT '
  printf '`package_sources`.`%s`,' \
    'pkgbase' 'git_revision' 'mod_git_revision'
  printf '`upstream_repositories`.`name`'
  printf ' FROM `build_slaves`'
  mysql_join_build_slaves_build_assignments
  mysql_join_build_assignments_package_sources
  mysql_join_package_sources_upstream_repositories
} | \
  mysql_run_query | \
  tr '\t' ' ' | \
  sort > \
  "${tmp_dir}/locked.mysql"
find "${work_dir}/package-states" -mindepth 1 -maxdepth 1 -name '*.locked' -printf '%f\n' | \
  sed '
    s/\.\([^.]\+\)\.\([^.]\+\)\.\([^.]\+\)\.[^.]\+$/ \1 \2 \3/
  ' | \
  sort > \
  "${tmp_dir}/locked.file"

# shellcheck disable=SC2016
{
  printf 'SELECT DISTINCT `package_sources`.`pkgbase`'
  printf ' FROM `package_sources`'
  mysql_join_package_sources_build_assignments
  mysql_join_build_assignments_binary_packages
  mysql_join_binary_packages_repositories
  printf ' WHERE `repositories`.`name`="deletion-list"'
  printf ' OR `binary_packages`.`is_to_be_deleted`;\n'
} | \
  mysql_run_query | \
  sort > \
  "${tmp_dir}/deletion-list.mysql"
sort "${work_dir}/deletion-list" > \
  "${tmp_dir}/deletion-list.file"

# shellcheck disable=SC2016
{
  printf 'SELECT `package_sources`.`pkgbase`'
  printf ' FROM `package_sources`'
  mysql_join_package_sources_build_assignments
  mysql_join_build_assignments_build_dependency_loops
  printf ';\n'
} | \
  mysql_run_query | \
  sort > \
  "${tmp_dir}/loops.mysql"
find "${work_dir}/build-list.loops" -regextype grep -mindepth 1 -maxdepth 1 \
  -regex '.*/loop_[0-9]\+' \
  -exec 'cat' '{}' \; | \
  sort > \
  "${tmp_dir}/loops.file"

# shellcheck disable=SC2016
{
  printf 'SELECT '
  printf '`binary_packages`.`%s`,' \
    'pkgname' \
    'epoch' \
    'pkgver' \
    'pkgrel' \
    'sub_pkgrel'
  printf '`architectures`.`name`'
  printf ' FROM `binary_packages`'
  mysql_join_binary_packages_repositories
  mysql_join_binary_packages_architectures
  mysql_join_repositories_repository_stabilities
  printf ' WHERE `binary_packages`.`is_tested`'
  printf ' AND NOT `binary_packages`.`has_issues`'
  printf ' AND `repository_stabilities`.`name`="testing"'
} | \
  mysql_run_query | \
  sed '
    s/^\(\S\+\)\s\(\S\+\)\s\(\S\+\)\s\(\S\+\)\s\(\S\+\)\s\(\S\+\)$/\1-\2:\3-\4.\5-\6.pkg.tar.xz/
  ' | \
  sort -u > \
  "${tmp_dir}/tested.mysql"
find "${work_dir}/package-states" -mindepth 1 -maxdepth 1 \
  -name '*.tested' \
  -exec cat '{}' \; | \
  sed '
    s/-\([^-.]\+\)\(-[^-]\+\)$/-\1.0\2/
    s/-\([^-:]\+\)\(\(-[^-]\+\)\{2\}\)$/-0:\1\2/
  ' | \
  sort -u > \
  "${tmp_dir}/tested.file"

# shellcheck disable=SC2016
{
  printf 'SELECT '
  printf '`binary_packages`.`%s`,' \
    'pkgname' \
    'epoch' \
    'pkgver' \
    'pkgrel' \
    'sub_pkgrel'
  printf '`architectures`.`name`'
  printf ' FROM `binary_packages`'
  mysql_join_binary_packages_repositories
  mysql_join_binary_packages_architectures
  mysql_join_repositories_repository_stabilities
  printf ' WHERE NOT `binary_packages`.`is_tested`'
  printf ' AND NOT `binary_packages`.`has_issues`'
  printf ' AND `repository_stabilities`.`name`="testing"'
} | \
  mysql_run_query | \
  sed '
    s/^\(\S\+\)\s\(\S\+\)\s\(\S\+\)\s\(\S\+\)\s\(\S\+\)\s\(\S\+\)$/\1-\2:\3-\4.\5-\6.pkg.tar.xz/
  ' | \
  sort -u > \
  "${tmp_dir}/testing.mysql"
find "${work_dir}/package-states" -mindepth 1 -maxdepth 1 \
  -name '*.testing' \
  -exec cat '{}' \; | \
  sed '
    s/-\([^-.]\+\)\(-[^-]\+\)$/-\1.0\2/
    s/-\([^-:]\+\)\(\(-[^-]\+\)\{2\}\)$/-0:\1\2/
  ' | \
  sort -u > \
  "${tmp_dir}/testing.file"

# shellcheck disable=SC2016
{
  printf 'SELECT '
  printf '`package_sources`.`%s`,' \
    'pkgbase' \
    'git_revision' \
    'mod_git_revision'
  printf '`upstream_repositories`.`name`,`install_targets`.`name`'
  printf ' FROM `binary_packages`'
  mysql_join_binary_packages_dependencies
  mysql_join_dependencies_dependency_types
  mysql_join_dependencies_install_targets
  mysql_join_binary_packages_build_assignments
  mysql_join_build_assignments_package_sources
  mysql_join_package_sources_upstream_repositories
  printf ' WHERE `dependency_types`.`relevant_for_building`'
} | \
  mysql_run_query | \
  tr '\t' ' ' | \
  sort -u > \
  "${tmp_dir}/build-dependencies.mysql"
{
  cat "${tmp_dir}/build-dependencies.mysql"
  # shellcheck disable=SC2016
  {
    printf 'SELECT DISTINCT '
    printf '`package_sources`.`%s`,' \
      'pkgbase' 'git_revision' 'mod_git_revision'
    printf '`upstream_repositories`.`name`'
    printf ' FROM `upstream_repositories`'
    mysql_join_upstream_repositories_package_sources
    mysql_join_package_sources_build_assignments
    mysql_join_build_assignments_binary_packages
  } | \
    mysql_run_query | \
    tr '\t' ' ' | \
    while read -r pkgbase git_revision mod_git_revision repository; do
      if [ -f "${work_dir}/package-infos/${pkgbase}.${git_revision}.${mod_git_revision}.${repository}.build-depends" ]; then
        # TODO: consider versions dependencies, too
        grep -v '[<=>]' "${work_dir}/package-infos/${pkgbase}.${git_revision}.${mod_git_revision}.${repository}.build-depends" | \
          sed '
            s/^/'"${pkgbase} ${git_revision} ${mod_git_revision} ${repository}"' /
          '
      fi
    done
} | \
  sort -u > \
  "${tmp_dir}/build-dependencies.file"

{
  diff -u \
    "${tmp_dir}/locked.file" \
    "${tmp_dir}/locked.mysql" || \
    true
  mysql_sanity_check || true
  diff -u \
    "${tmp_dir}/build-list.file" \
    "${tmp_dir}/build-list.mysql" || \
    true
  diff -u \
    "${tmp_dir}/deletion-list.file" \
    "${tmp_dir}/deletion-list.mysql" || \
    true
  diff -u \
    "${tmp_dir}/loops.file" \
    "${tmp_dir}/loops.mysql" >&2 || \
    true
  diff -u \
    "${tmp_dir}/tested.file" \
    "${tmp_dir}/tested.mysql" || \
    true
  diff -u \
    "${tmp_dir}/testing.file" \
    "${tmp_dir}/testing.mysql" || \
    true
  diff -u \
    "${tmp_dir}/build-dependencies.file" \
    "${tmp_dir}/build-dependencies.mysql" || \
    true
} | \
  sed '
    s,^-.*$,<font color="#FF0000">\0</font>,
    s,^+.*$,<font color="#00FF00">\0</font>,
    s/$/<br>/
    1 i <html><head><title>sanity of the buildmaster'"'"'s mysql database</title></head><body>
    $ a </body></html>
  ' | \
  sponge "${webserver_directory}/mysql-sanity.html"

if [ -s "${webserver_directory}/mysql-sanity.html" ] && \
  [ ! -s "${work_dir}/build-master-sanity" ]; then
  printf 'girls, my database is dirty again ...\n' | \
    irc_say
  echo 'build master is insane' > \
    "${work_dir}/build-master-sanity"
fi

if [ ! -s "${work_dir}/build-master-sanity" ]; then
  # shellcheck disable=SC2016
  {
    printf 'INSERT IGNORE INTO `statistics` ('
    printf '`%s`,' \
      'date' \
      'stable_packages_count' \
      'pending_tasks_count' \
      'pending_packages_count' \
      'staging_packages_count' \
      'testing_packages_count' \
      'tested_packages_count' \
      'broken_tasks_count' \
      'dependency_loops_count' \
      'dependency_looped_tasks_count' \
      'locked_tasks_count' \
      'blocked_tasks_count' \
      'next_tasks_count' | \
      sed 's/,$//'
    printf ') VALUES ('
    # date
    printf 'NOW(),'
    # stable_packages_count
    printf '(SELECT COUNT(DISTINCT `binary_packages`.`id`) FROM'
    printf ' `binary_packages`'
    mysql_join_binary_packages_repositories
    mysql_join_repositories_repository_stabilities
    printf ' WHERE `repository_stabilities`.`name`="stable"),'
    # pending_tasks_count
    printf '(SELECT COUNT(DISTINCT `build_assignments`.`id`) FROM'
    printf ' `build_assignments`'
    mysql_join_build_assignments_binary_packages
    mysql_join_binary_packages_repositories
    printf ' WHERE `repositories`.`name`="build-list"),'
    # pending_packages_count
    printf '(SELECT COUNT(DISTINCT `binary_packages`.`id`) FROM'
    printf ' `binary_packages`'
    mysql_join_binary_packages_repositories
    printf ' WHERE `repositories`.`name`="build-list"),'
    # staging_packages_count
    printf '(SELECT COUNT(DISTINCT `binary_packages`.`id`) FROM'
    printf ' `binary_packages`'
    mysql_join_binary_packages_repositories
    mysql_join_repositories_repository_stabilities
    printf ' WHERE `repository_stabilities`.`name`="staging"),'
    # testing_packages_count
    printf '(SELECT COUNT(DISTINCT `binary_packages`.`id`) FROM'
    printf ' `binary_packages`'
    mysql_join_binary_packages_repositories
    mysql_join_repositories_repository_stabilities
    printf ' WHERE `repository_stabilities`.`name`="testing"'
    printf ' AND NOT `binary_packages`.`is_tested`),'
    # tested_packages_count
    printf '(SELECT COUNT(DISTINCT `binary_packages`.`id`) FROM'
    printf ' `binary_packages`'
    mysql_join_binary_packages_repositories
    mysql_join_repositories_repository_stabilities
    printf ' WHERE `repository_stabilities`.`name`="testing"'
    printf ' AND `binary_packages`.`is_tested`),'
    # broken_tasks_count
    printf '(SELECT COUNT(DISTINCT `build_assignments`.`id`) FROM'
    printf ' `build_assignments`'
    mysql_join_build_assignments_binary_packages
    mysql_join_binary_packages_repositories
    printf ' WHERE `repositories`.`name`="build-list"'
    printf ' AND `build_assignments`.`is_broken`),'
    # dependency_loops_count
    printf '(SELECT COUNT(DISTINCT `build_dependency_loops`.`loop`) FROM'
    printf ' `build_dependency_loops`),'
    # dependency_looped_tasks_count
    printf '(SELECT COUNT(DISTINCT `build_dependency_loops`.`build_assignment`) FROM'
    printf ' `build_dependency_loops`),'
    # locked_tasks_count
    printf '(SELECT COUNT(DISTINCT `build_slaves`.`currently_building`) FROM'
    printf ' `build_slaves`'
    mysql_join_build_slaves_build_assignments
    mysql_join_build_assignments_binary_packages
    mysql_join_binary_packages_repositories
    printf ' WHERE `repositories`.`name`="build-list"),'
    # blocked_tasks_count
    printf '(SELECT COUNT(DISTINCT `build_assignments`.`id`) FROM'
    printf ' `build_assignments`'
    mysql_join_build_assignments_binary_packages
    mysql_join_binary_packages_repositories
    printf ' WHERE `repositories`.`name`="build-list"'
    printf ' AND `build_assignments`.`is_blocked` IS NOT NULL),'
    # next_tasks_count
    printf '(SELECT COUNT(DISTINCT `build_assignments`.`id`) FROM'
    printf ' `build_assignments`'
    mysql_join_build_assignments_binary_packages
    mysql_join_binary_packages_repositories
    printf ' WHERE `repositories`.`name`="build-list"'
    printf ' AND NOT EXISTS ('
      printf 'SELECT * FROM `dependencies`'
      mysql_join_dependencies_dependency_types
      printf ' AND `dependency_types`.`relevant_for_binary_packages`'
      mysql_join_dependencies_install_target_providers
      mysql_join_install_target_providers_binary_packages '' 'prov_bp'
      mysql_join_binary_packages_repositories 'prov_bp' 'prov_r'
      printf ' WHERE `prov_r`.`name`="build-list"'
      printf ' AND `dependencies`.`dependent`=`binary_packages`.`id`'
    printf '))'
    printf ');\n'
  } | \
    mysql_run_query
fi