#!/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

# always block if locked
exec 9> "${build_list_lock_file}"
if ! flock -n -s 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`'
  printf ' JOIN `%s` ON `%s`.`%s`=`%s`.`id`' \
    'upstream_repositories' 'package_sources' 'upstream_package_repository' 'upstream_repositories' \
    'build_assignments' 'build_assignments' 'package_source' 'package_sources' \
    'binary_packages' 'binary_packages' 'build_assignment' 'build_assignments' \
    'repositories' 'binary_packages' 'repository' 'repositories'
  printf ' WHERE `repositories`.`name`="build-list"'
} | \
  ${mysql_command} --batch | \
  sed '
    1d
    y/\t/ /
  ' | \
  sort > \
  "${tmp_dir}/build-list.mysql"
sort "${work_dir}/build-list" > \
  "${tmp_dir}/build-list.file"


# shellcheck disable=SC2016
{
  printf 'SELECT DISTINCT `package_sources`.`pkgbase`'
  printf ' FROM `package_sources`'
  printf ' JOIN `%s` ON `%s`.`%s`=`%s`.`id`' \
    'build_assignments' 'build_assignments' 'package_source' 'package_sources' \
    'binary_packages' 'binary_packages' 'build_assignment' 'build_assignments' \
    'repositories' 'binary_packages' 'repository' 'repositories'
  printf ' WHERE `repositories`.`name`="deletion-list"'
  printf ' OR `binary_packages`.`is_to_be_deleted`;\n'
} | \
  ${mysql_command} --batch | \
  sed '
    1d
    y/\t/ /
  ' | \
  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`'
  printf ' JOIN `%s` ON `%s`.`%s`=`%s`.`id`' \
    'build_assignments' 'build_assignments' 'package_source' 'package_sources' \
    'build_dependency_loops' 'build_dependency_loops' 'build_assignment' 'build_assignments'
  printf ';\n'
} | \
  ${mysql_command} --batch | \
  sed '
    1d
    y/\t/ /
  ' | \
  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`'
  printf ' JOIN `%s` ON `%s`.`id`=`%s`.`%s`' \
    'repositories'           'repositories'           'binary_packages' 'repository' \
    'architectures'          'architectures'          'binary_packages' 'architecture' \
    'repository_stabilities' 'repository_stabilities' 'repositories'    'stability'
  printf ' WHERE `binary_packages`.`is_tested`'
  printf ' AND NOT `binary_packages`.`has_issues`'
  printf ' AND `repository_stabilities`.`name`="testing"'
} | \
  ${mysql_command} --batch | \
  sed '
    1d
    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`'
  printf ' JOIN `%s` ON `%s`.`id`=`%s`.`%s`' \
    'repositories'           'repositories'           'binary_packages' 'repository' \
    'architectures'          'architectures'          'binary_packages' 'architecture' \
    'repository_stabilities' 'repository_stabilities' 'repositories'    'stability'
  printf ' WHERE NOT `binary_packages`.`is_tested`'
  printf ' AND NOT `binary_packages`.`has_issues`'
  printf ' AND `repository_stabilities`.`name`="testing"'
} | \
  ${mysql_command} --batch | \
  sed '
    1d
    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`'
  printf ' JOIN `%s` ON `%s`.`id`=`%s`.`%s`' \
    'dependencies'     'binary_packages'  'dependencies' 'dependent' \
    'dependency_types' 'dependency_types' 'dependencies' 'dependency_type'
  printf ' AND `dependency_types`.`relevant_for_building`'
  printf ' JOIN `%s` ON `%s`.`id`=`%s`.`%s`' \
    'install_targets'       'install_targets'       'dependencies'      'depending_on' \
    'build_assignments'     'build_assignments'     'binary_packages'   'build_assignment' \
    'package_sources'       'package_sources'       'build_assignments' 'package_source' \
    'upstream_repositories' 'upstream_repositories' 'package_sources'   'upstream_package_repository'
} | \
  ${mysql_command} --raw --batch | \
  sed '
    1d
    y/\t/ /
  ' | \
  sort -u > \
  "${tmp_dir}/build-dependencies.mysql"
{
  cat "${tmp_dir}/build-dependencies.mysql"
  find "${work_dir}/package-infos" -name '*.build-depends' \
    -exec grep -HF '' {} \; | \
    sed '
      s,^.*/,,
      s/\.\([^.]\+\)\.\([^.]\+\)\.\([^.:]\+\)\.build-depends:\([^:]\+\)$/ \1 \2 \3 \4/
    '
} | \
  sort -u > \
  "${tmp_dir}/build-dependencies.file"

{
  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" || \
    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"