#!/bin/sh

# usage: why_dont_you $action $package1 $package2 ...

# shellcheck disable=SC2119,SC2120

# investigate, why a certain operation is not done with certain packages

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

# TODO: reintrocude "keep", "stubbornly_keep", "stabilize" and "unstage"
# using information from the database.

action="$1"
shift

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

case "${action}" in

  'build')

    # shellcheck disable=SC2016
    {
      printf 'CREATE TEMPORARY TABLE `searches` (`search` VARCHAR(64));\n'
      printf 'INSERT INTO `searches` VALUES '
      # shellcheck disable=SC2046
      printf '(from_base64("%s")),' \
        $(
          printf '%s\n' "$@" | \
            base64_encode_each
        ) | \
        sed 's/,$/;\n/'
      # we select everything which is possibly of any interest:
      # - search term
      # - id (to see if it actually is on the build-list)
      # - build_assignments.architecture.name
      # - to_build.is_broken
      # - failed_builds_count
      # - to_build.is_blocked
      # - deps.pkgbase (any dependency pending?)
      # - build_slaves.name (is anyone building this?)
      # - pkgbase
      printf 'SELECT DISTINCT replace(to_base64(`searches`.`search`),"\\n",""),'
      printf '`to_build`.`ba_id`,'
      printf '`to_build`.`baa_name`,'
      printf 'If(`to_build`.`is_broken`,1,0),'
      printf '('
        printf 'SELECT COUNT(1)'
        printf ' FROM `failed_builds`'
        printf 'WHERE `failed_builds`.`build_assignment`=`to_build`.`ba_id`'
      printf ')'
      printf ',replace(to_base64(`%s`.`%s`),"\\n","")' \
        'to_build' 'is_blocked' \
        'deps' 'pkgbase' \
        'build_slaves' 'name' \
        'to_build' 'pkgbase'
      # at least one row for each given `search`
      printf ' FROM `searches`'
      printf ' LEFT JOIN '
        printf '('
        # join the tables for the to-be-built packages:
        # package_source, build_assignment, binary_package, repostory
        printf 'SELECT DISTINCT '
        printf '`tb_ps`.`pkgbase`,'
        printf '`tb_bin`.`id` AS `bin_id`,'
        printf '`tb_ba`.`id` AS `ba_id`,'
        printf '`tb_ba`.`is_blocked`,'
        printf '`tb_ba`.`is_broken`,'
        printf '`tb_baa`.`name` AS `baa_name`'
        printf ' FROM `package_sources` AS `tb_ps`'
        mysql_join_package_sources_build_assignments 'tb_ps' 'tb_ba'
        mysql_join_build_assignments_architectures 'tb_ba' 'tb_baa'
        mysql_join_build_assignments_binary_packages 'tb_ba' 'tb_bin'
        mysql_join_binary_packages_binary_packages_in_repositories 'tb_bin' 'tb_bir'
        printf ' WHERE `tb_bir`.`repository`=%s' \
          "${repository_ids__any_build_list}"
        printf ') AS `to_build`'
      printf ' ON ('
        printf '`to_build`.`pkgbase`=`searches`.`search`'
        printf ' OR `searches`.`search`=CONCAT(`to_build`.`baa_name`,"/",`to_build`.`pkgbase`)'
      printf ') LEFT JOIN '
        printf '('
        # same join as above, but with different names - for the
        # potential dependencies
        printf 'SELECT DISTINCT `dep_ps`.`pkgbase`,`dependencies`.`dependent`'
        printf ' FROM `package_sources` AS `dep_ps`'
        mysql_join_package_sources_build_assignments 'dep_ps' 'dep_ba'
        mysql_join_build_assignments_binary_packages 'dep_ba' 'dep_bin'
        mysql_join_binary_packages_binary_packages_in_repositories 'dep_bin' 'dep_bir'
        # now we have some (=3) additional joins,
        # because we are interested in dependency relations to `to_build`
        mysql_join_binary_packages_install_target_providers 'dep_bin'
        mysql_join_install_target_providers_dependencies_with_versions
        mysql_join_dependencies_dependency_types
        printf ' WHERE `dep_bir`.`repository`=%s' \
          "${repository_ids__any_build_list}"
        printf ' AND `dependency_types`.`relevant_for_building`'
        printf ') AS `deps`'
      printf ' ON `deps`.`dependent`=`to_build`.`bin_id`'
      # now we join with build slaves to see if someone builds this
      printf ' LEFT JOIN `build_slaves`'
      printf ' ON `build_slaves`.`currently_building`=`to_build`.`ba_id`'
      printf ';\n'
    } | \
      mysql_run_query 'unimportant' | \
      tr '\t' ' ' | \
      sort -k9,9 -k8,8 -k7,7 | \
      sed '
        / NULL \S\+$/ b multi-dep
        :multi-slave
          $!N
          s/^\(\(\S\+ \)\{6\}\)\(\S\+\)\( \S\+\)\n\(\S\+ \)\{6\}\(\S\+\)\4/\1\3,\6\4/
          t multi-slave
          P
          D
        :multi-dep
          / NULL\( \S\+\)\{3\}$/! b
          $!N
          s/^\(\(\S\+ \)\{5\}\)\(\S\+\)\(\( \S\+\)\{2\}\)\n\(\S\+ \)\{5\}\(\S\+\)\4/\1\3,\7\4/
          t multi-dep
          P
          D
      ' | \
      sed '
        s/NULL,//g
      ' | \
      while read -r search id ba_arch is_broken trials is_blocked dependency slave pkgbase; do
        search=$(
          printf '%s' "${search}" | \
            base64 -d
        )
        pkgbase=$(
          printf '%s' "${pkgbase}" | \
            base64 -d
        )
        if [ "${id}" = 'NULL' ]; then
          >&2 printf '"%s" is not on the build list.\n' \
            "${search}"
          continue
        fi
        if [ "${slave}" != 'NULL' ]; then
          # beware: A slave named "5BË" will look exactly like this!
          printf '"%s/%s" is locked by %s.\n' \
            "${ba_arch}" \
            "${pkgbase}" \
            "$(
              printf '%s\n' "${slave}" | \
                tr ',' '\n' | \
                while read -r line; do
                  printf '%s\n' "${line}" | \
                    base64 -d
                  printf ','
                done | \
                sed 's/,$//'
            )"
          continue
        fi
        if [ "${is_blocked}" != 'NULL' ]; then
          # beware: A block-reason "5BË" will look exactly like this!
          printf '"%s/%s" is blocked: "%s".\n' \
            "${ba_arch}" \
            "${pkgbase}" \
            "$(
              printf '%s' "${is_blocked}" | \
                base64 -d
            )"
          continue
        fi
        if [ "${dependency}" != 'NULL' ]; then
          printf '"%s/%s" has unmet dependencies:\n' \
            "${ba_arch}" \
            "${pkgbase}"
          printf '%s\n' "${dependency}" | \
            tr ',' '\n' | \
            while read -r line; do
              printf '  '
              printf '%s\n' "${line}" | \
                base64 -d
              printf '\n'
            done | \
            sort -u
          continue
        fi
        if [ "${is_broken}" = '1' ]; then
          printf '"%s/%s" is broken (%sx built), but would be built.\n' \
            "${ba_arch}" \
            "${pkgbase}" \
            "${trials}"
          continue
        fi
        printf '"%s/%s" would be built.\n' \
          "${ba_arch}" \
          "${pkgbase}"
      done

  ;;

  'stabilize'|'unstage')

    if [ "${action}" = 'stabilize' ]; then
      # shellcheck disable=SC2154
      from_stability=${repository_stability_ids__testing}
      # shellcheck disable=SC2154
      to_stability=${repository_stability_ids__stable}
      # shellcheck disable=SC2016
      test_filter='`binary_packages`.`is_tested`'
    else
      # shellcheck disable=SC2154
      from_stability=${repository_stability_ids__staging}
      # shellcheck disable=SC2154
      to_stability=${repository_stability_ids__testing}
      test_filter='1'
    fi

    for pkgname in "$@"; do
      infos=$(
        # shellcheck disable=SC2016
        {
          printf 'SELECT'
          printf ' `binary_packages_in_repositories`.`id`,'
          printf ' IF(%s,1,0),' "${test_filter}"
          printf ' IF(`binary_packages`.`has_issues`,1,0)'
          printf ' FROM `binary_packages_in_repositories`'
          mysql_join_binary_packages_in_repositories_repositories
          mysql_join_binary_packages_in_repositories_binary_packages
          printf ' WHERE `repositories`.`stability`=%s' \
            "${from_stability}"
          printf ' AND `binary_packages`.`pkgname`=from_base64("%s")' \
            "$(
              printf '%s' "${pkgname}" | \
                base64 -w0
            )"
        } | \
          mysql_run_query 'unimportant' | \
          tr '\t' ' '
      )
      if [ -z "${infos}" ]; then
        printf 'Cannot find "%s"\n' \
          "${pkgname}"
        continue
      fi
      bpir="${infos%% *}"
      infos="${infos#* }"
      if [ "${infos% *}" = '0' ]; then
        printf '"%s" is not tested.\n' \
          "${pkgname}"
        continue
      fi
      infos="${infos#* }"
      if [ "${infos}" = '1' ]; then
        printf 'There are unresolved bugs reported against "%s".\n' \
          "${pkgname}"
        continue
      fi

      printf '%s %s\n' "${bpir}" "${pkgname}" >> \
        "${tmp_dir}/packages"
    done

    if [ ! -s "${tmp_dir}/packages" ]; then
      exit
    fi

    # shellcheck disable=SC2016
    {
      printf 'CREATE TEMPORARY TABLE `moveable_bpir` (`id` BIGINT, `to_repository` BIGINT, UNIQUE KEY `id`(`id`));\n'
      printf 'CREATE TEMPORARY TABLE `replaceable_bpir` (`id` BIGINT, `replaced_by` BIGINT, UNIQUE KEY `id`(`id`));\n'

      for copy in '' '_copy'; do
        printf 'CREATE TEMPORARY TABLE `knots%s` (' \
          "${copy}"
        printf '`id` BIGINT NOT NULL AUTO_INCREMENT,'
        printf ' `and` BIT,'
        printf ' `content_id` BIGINT,'
        printf ' `content_type` ENUM("bpir","it"),'
        printf ' `reason_length` MEDIUMINT,'
        printf ' `active` BIT,'
        printf ' `relevant` BIT,'
        printf ' UNIQUE KEY (`id`),'
        printf ' UNIQUE KEY `content`(`content_id`,`content_type`));\n'
        printf 'CREATE TEMPORARY TABLE `edges%s` (' \
          "${copy}"
        printf '`cause` BIGINT,'
        printf ' `impact` BIGINT,'
        printf ' `invert` BIT,'
        printf ' UNIQUE KEY `content` (`cause`,`impact`));\n'
      done

      printf 'INSERT IGNORE INTO `moveable_bpir` (`id`,`to_repository`)'
      printf ' SELECT '
      printf '`binary_packages_in_repositories`.`id`,'
      printf '`repository_moves`.`to_repository`'
      printf ' FROM `binary_packages_in_repositories`'
      mysql_join_binary_packages_in_repositories_repositories
      mysql_join_binary_packages_in_repositories_binary_packages
      mysql_join_binary_packages_build_assignments
      mysql_join_build_assignments_package_sources
      mysql_join_package_sources_upstream_repositories
      mysql_join_upstream_repositories_repository_moves
      printf ' AND `repository_moves`.`from_repository`=`repositories`.`id`'
      printf ' WHERE `repositories`.`stability`=%s' \
        "${from_stability}"
      printf ' AND %s' \
        "${test_filter}"
      printf ' AND NOT `binary_packages`.`has_issues`;\n'

      printf 'INSERT IGNORE INTO `replaceable_bpir` (`id`,`replaced_by`)'
      printf ' SELECT `r_bpir`.`id`,`m_bpir`.`id`'
      printf ' FROM `moveable_bpir`'
      printf ' JOIN `binary_packages_in_repositories` AS `m_bpir`'
      printf ' ON `m_bpir`.`id`=`moveable_bpir`.`id`'
      mysql_join_binary_packages_in_repositories_binary_packages 'm_bpir' 'm_bp'
      printf ' JOIN `binary_packages` AS `r_bp`'
      printf ' ON `r_bp`.`pkgname`=`m_bp`.`pkgname`'
      mysql_join_binary_packages_binary_packages_in_repositories 'r_bp' 'r_bpir'
      mysql_join_binary_packages_in_repositories_repositories 'r_bpir' 'r_r'
      printf ' JOIN `repositories` AS `m_r`'
      printf ' ON `m_r`.`id`=`moveable_bpir`.`to_repository`'
      printf ' AND `m_r`.`architecture`=`r_r`.`architecture`'
      printf ' JOIN `repository_stability_relations` AS `rsr`'
      printf ' ON `rsr`.`more_stable`=`r_r`.`stability`'
      printf ' AND `rsr`.`less_stable`=`m_r`.`stability`'
      printf ' WHERE NOT EXISTS ('
        printf 'SELECT 1'
        printf ' FROM `binary_packages_in_repositories` AS `subst_bpir`'
        mysql_join_binary_packages_in_repositories_repositories 'subst_bpir' 'subst_r'
        mysql_join_binary_packages_in_repositories_binary_packages 'subst_bpir' 'subst_bp'
        printf ' JOIN `repository_stability_relations` AS `rsr_a`'
        printf ' ON `rsr_a`.`more_stable`=`subst_r`.`stability`'
        printf ' JOIN `repository_stability_relations` AS `rsr_b`'
        printf ' ON `rsr_b`.`less_stable`=`subst_r`.`stability`'
        printf ' WHERE `subst_bp`.`pkgname`=`r_bp`.`pkgname`'
        printf ' AND `subst_bp`.`id`=`r_bp`.`id`'
        printf ' AND `rsr_a`.`less_stable`=`m_r`.`stability`'
        printf ' AND `rsr_b`.`more_stable`=`r_r`.`stability`'
      printf ');\n'

      # we generate a graph with bpir and it as knots

      printf 'INSERT INTO `knots` (`and`,`content_id`,`content_type`)'
      printf ' SELECT 1,`moveable_bpir`.`id`,"bpir"'
      printf ' FROM `moveable_bpir`;\n'

      printf 'INSERT INTO `knots` (`and`,`content_id`,`content_type`)'
      printf ' SELECT 1,`binary_packages_in_repositories`.`id`,"bpir"'
      printf ' FROM `binary_packages_in_repositories`'
      mysql_join_binary_packages_in_repositories_repositories
      mysql_join_binary_packages_in_repositories_binary_packages
      printf ' JOIN `repository_stability_relations`'
      printf ' ON `repository_stability_relations`.`less_stable`=%s' \
        "${to_stability}"
      printf ' AND `repository_stability_relations`.`more_stable`=`repositories`.`stability`'
      printf ' WHERE NOT EXISTS('
        printf 'SELECT 1'
        printf ' FROM `binary_packages_in_repositories` AS `subst_bpir`'
        mysql_join_binary_packages_in_repositories_repositories 'subst_bpir' 'subst_r'
        printf ' JOIN `repository_stability_relations` AS `rsr_a`'
        printf ' ON `rsr_a`.`less_stable`=%s' \
          "${to_stability}"
        printf ' AND `rsr_a`.`more_stable`=`subst_r`.`stability`'
        printf ' JOIN `repository_stability_relations` AS `rsr_b`'
        printf ' ON `rsr_b`.`less_stable`=`subst_r`.`stability`'
        mysql_join_binary_packages_in_repositories_binary_packages 'subst_bpir' 'subst_bp'
        printf ' WHERE `subst_bp`.`pkgname`=`binary_packages`.`pkgname`'
        printf ' AND `subst_bpir`.`id`!=`binary_packages_in_repositories`.`id`'
        printf ' AND `rsr_b`.`more_stable`=`repositories`.`stability`'
      printf ');\n'

      printf 'INSERT INTO `knots` (`and`,`content_id`,`content_type`)'
      printf ' SELECT 0,`install_targets`.`id`,"it"'
      printf ' FROM `install_targets`;\n'

      # create copy
      printf 'INSERT INTO `knots_copy`'
      printf ' SELECT *'
      printf ' FROM `knots`;\n'

      # "available" bpir requires available install_target
      printf 'INSERT INTO `edges` (`cause`,`impact`,`invert`)'
      printf ' SELECT DISTINCT `c`.`id`,`i`.`id`,0'
      printf ' FROM `knots` AS `i`'
      printf ' JOIN `binary_packages_in_repositories`'
      printf ' ON `binary_packages_in_repositories`.`id`=`i`.`content_id`'
      printf ' AND `i`.`content_type`="bpir"'
      mysql_join_binary_packages_in_repositories_dependencies
      mysql_join_dependencies_dependency_types
      printf ' AND `dependency_types`.`relevant_for_binary_packages`'
      printf ' JOIN `knots_copy` AS `c`'
      printf ' ON `dependencies`.`depending_on`=`c`.`content_id`'
      printf ' AND `c`.`content_type`="it";\n'

      # "available" install_target requires available bpir - but we
      # only care about bpir being moved or being more stable than
      # target stability
      for what in 'moveable' 'there'; do
        printf 'INSERT INTO `edges` (`cause`,`impact`,`invert`)'
        printf ' SELECT DISTINCT `c`.`id`,`i`.`id`,0'
        printf ' FROM `knots` AS `c`'
        printf ' JOIN `binary_packages_in_repositories`'
        printf ' ON `binary_packages_in_repositories`.`id`=`c`.`content_id`'
        printf ' AND `c`.`content_type`="bpir"'
        if [ "${what}" = 'moveable' ]; then
          printf ' JOIN `moveable_bpir`'
          printf ' ON `moveable_bpir`.`id`=`binary_packages_in_repositories`.`id`'
        else
          mysql_join_binary_packages_in_repositories_repositories
          printf ' JOIN `repository_stability_relations`'
          printf ' ON `repository_stability_relations`.`more_stable`=`repositories`.`stability`'
          printf ' AND `repository_stability_relations`.`less_stable`=%s' \
            "${to_stability}"
        fi
        mysql_join_binary_packages_in_repositories_install_target_providers
        printf ' JOIN `knots_copy` AS `i`'
        printf ' ON `install_target_providers`.`install_target`=`i`.`content_id`'
        printf ' AND `i`.`content_type`="it";\n'
      done

      # additionally, "available" moveable_bpir requires matching
      # replaceable_bpir unavailable
      printf 'INSERT INTO `edges` (`cause`,`impact`,`invert`)'
      printf ' SELECT `c`.`id`,`i`.`id`,1'
      printf ' FROM `replaceable_bpir`'
      printf ' JOIN `knots` AS `c`'
      printf ' ON `c`.`content_id`=`replaceable_bpir`.`id`'
      printf ' AND `c`.`content_type`="bpir"'
      printf ' JOIN `knots_copy` AS `i`'
      printf ' ON `i`.`content_id`=`replaceable_bpir`.`replaced_by`'
      printf ' AND `i`.`content_type`="bpir";\n'

      while read -r bpir pkgname; do
        printf 'CALL `find_the_culprit`(%s);\n' \
          "${bpir}"
      done < \
        "${tmp_dir}/packages"
    } | \
      mysql_run_query 'unimportant'

  ;;

  'keep'|'stubbornly_keep')

    printf 'Sry, "why-dont-you %s" is unavailable, until someone recodes it to look into the database.\n' "${action}"

  ;;

  *)
    >&2 printf 'unknown action "%s" - known actions are:' "${action}"
    >&2 printf ' "%s"' \
      'build' \
      'stabilize' \
      'unstage' \
      'keep' \
      'stubbornly_keep'
    >&2 printf '\n'
    exit 1

esac