#!/bin/sh

# contains functions used to access mysql db

# shellcheck disable=SC2039

if [ -z "${base_dir}" ]; then
  # just to make shellcheck happy
  . 'conf/default.conf'
fi

# base64_encode_each encode each line of stdin with base64

base64_encode_each() {
  local line

  while read -r line; do
    printf '%s' \
      "${line}" | \
      base64 -w0
    printf '\n'
  done
}

# mysql_add_package_source $pkgbase $git_revision $mod_git_revision $upstream_package_repository

# shellcheck disable=SC2016,SC2086
mysql_add_package_source() {
  local names='pkgbase git_revision mod_git_revision upstream_package_repository'
  local values;
  for _ in ${names}; do
    values="${values}$(
      printf '%s' "$1" | \
        base64 -w0
    ) "
    shift
  done
  values="${values% }"

  {
    printf 'INSERT IGNORE INTO package_sources'
    printf ' ('
    printf '`%s`, ' ${names}
    printf ') SELECT'
    printf ' from_base64("%s"), ' ${values% *}
    printf ' `upstream_repositories`.`id`'
    printf ' FROM `upstream_repositories`'
    printf ' WHERE `upstream_repositories`.`name` = from_base64("%s");' \
      "${values##* }"
  } | \
    sed 's|, )|)|g' | \
    ${mysql_command}
}

# mysql_add_binary_package $pkgbase $git_revision $mod_git_revision $upstream_package_repository $pkgname $sub_pkgrel $architecture $repository

# shellcheck disable=SC2016,SC2031,SC2086,SC2154
mysql_add_binary_package() {
  local names='pkgbase git_revision mod_git_revision upstream_package_repository pkgname sub_pkgrel architecture repository'
  local name
  for name in ${names}; do
    eval 'local '"${name}"
    eval "${name}"'=$(
      printf "%s" "$1" |
        base64 -w0
    )'
    shift
  done

  {
    printf 'INSERT IGNORE INTO binary_packages'
    printf ' ('
    printf '`%s`, ' 'sub_pkgrel' 'pkgname' 'package_source' 'repository' 'architecture'
    printf ') SELECT'
    printf ' from_base64("%s"), ' "${sub_pkgrel}" "${pkgname}"
    printf ' `%s`.`id`,' 'package_sources' 'repositories' 'architectures'
    printf ' FROM'
    printf ' `%s` JOIN' 'package_sources' 'repositories' 'architectures'
    printf ' `upstream_repositories` ON `package_sources`.`upstream_package_repository` = `upstream_repositories`.`id`'
    printf ' WHERE'
    printf ' `%s`.`name` = from_base64("%s") AND' \
      'repositories' "${repository}" \
      'architectures' "${architecture}"
    printf ' `package_sources`.`%s` = from_base64("%s") AND' \
      'pkgbase' "${pkgbase}" \
      'git_revision' "${git_revision}" \
      'mod_git_revision' "${mod_git_revision}"
    printf ' `upstream_repositories`.`name` = from_base64("%s")' \
      "${upstream_package_repository}"
  } | \
    sed '
      s|, )|)|g
      s|, FROM| FROM|g
    ' | \
    ${mysql_command}
}

# mysql_show_binary_package $pkgname $pkgver $pkgrel $sub_pkgrel

# shellcheck disable=SC2016,SC2031,SC2086,SC2154
mysql_show_binary_package() {
  local names='pkgname pkgver pkgrel sub_pkgrel'
  local name
  for name in ${names}; do
    eval 'local '"${name}"
    eval "${name}"'=$(
      printf "%s" "$1" |
        base64 -w0
    )'
    shift
  done

  local joint
  {
    printf 'SELECT'
    printf ' `%s`.`%s`,' \
      'repositories' 'name' \
      'binary_packages' 'pkgname' \
      'package_sources' 'pkgver' \
      'package_sources' 'pkgrel' \
      'binary_packages' 'sub_pkgrel' \
      'architectures' 'name' \
      'package_sources' 'pkgbase' \
      'package_sources' 'git_revision' \
      'package_sources' 'mod_git_revision' \
      'upstream_repositories' 'name'
    printf ' FROM `binary_packages`'
    for joint in \
      'architectures:binary_packages:architecture' \
      'package_sources:binary_packages:package_source' \
      'repositories:binary_packages:repository' \
      'upstream_repositories:package_sources:upstream_package_repository'; do
      printf ' JOIN `%s` ON `%s`.`id` =' \
        "${joint%%:*}" "${joint%%:*}"
      joint="${joint#*:}"
      printf ' `%s`.`%s`' \
        "${joint%:*}" "${joint#*:}"
    done
    printf ' WHERE'
    printf ' `%s`.`%s` = from_base64("%s") AND' \
      'binary_packages' 'pkgname' "${pkgname}" \
      'binary_packages' 'sub_pkgrel' "${sub_pkgrel}" \
      'package_sources' 'pkgver' "${pkgver}" \
      'package_sources' 'pkgrel' "${pkgrel}"
    printf ';'
  } | \
    sed '
      s|, FROM| FROM|g
      s|AND;|;|g
    ' | \
    ${mysql_command} --html
}

# mysql_add_install_target $install_target

# shellcheck disable=SC2016,2086
mysql_add_install_target() {
  local install_target
  install_target=$(
    printf "%s" "$1" | \
      base64 -w0
  )

  {
    printf 'INSERT IGNORE INTO `install_targets` (`name`)'
    printf ' VALUES (from_base64("%s"))' \
      "${install_target}"
  } | \
    ${mysql_command}
}

# mysql_generate_package_metadata $package $git_revision $mod_git_revision $repository
# or
# mysql_generate_package_metadata $package.$git_revision.$mod_git_revision.$repository
# if sub_pkgrel should be determined automatically and the package is on the build-list
# and
# mysql_generate_package_metadata $sub_pkgrel $package $current_repository $git_revision $mod_git_revision $repository
# or
# mysql_generate_package_metadata $sub_pkgrel $current_repository $package.$git_revision.$mod_git_revision.$repository
# if $sub_pkgrel should be forced and the package is currently in $current_repository
# generate the meta data of a package (dependencies, built packages, ...) in the database

# shellcheck disable=SC2016
mysql_generate_package_metadata() {

  ( # new shell is intentional
    case "$1" in
      ''|*[!0-9]*)
        unset forced_sub_pkgrel
        current_repository=$(
          printf 'build-list' | \
            base64 -w0
        )
      ;;
      *)
        forced_sub_pkgrel=$(
          printf '%s' "$1" | \
            base64 -w0
        )
        shift
        current_repository=$(
          printf '%s' "$1" | \
            base64 -w0
        )
        shift
      ;;
    esac
    package="$1"
    git_revision="$2"
    mod_git_revision="$3"
    repository="$4"
    temp_dir=$(mktemp -d 'tmp.mysql-functions.mysql_generate_package_metadata.XXXXXXXXXX' --tmpdir)
    trap 'rm -rf --one-file-system "${temp_dir}"' EXIT

    if [ $# -eq 1 ]; then
      # second form
      repository="${package##*.}"
      package="${package%.*}"
      mod_git_revision="${package##*.}"
      package="${package%.*}"
      git_revision="${package##*.}"
      package="${package%.*}"
    fi

    printf '.' >&2
    if ! make_source_info "${package}" "${repository}" "${git_revision}" "${mod_git_revision}" "${temp_dir}/SRCINFO"; then
      printf '"make_source_info %s %s %s %s %s" failed.\n' "${package}" "${repository}" "${git_revision}" "${mod_git_revision}" "${temp_dir}/SRCINFO"
      exit 2
    fi
    # remove empty lines and unsupported architectures
    sed -i '
      /^[^=]*=\s*$/d
      /^\s*arch = /{
        / \(i686\|any\)$/!d
      }
    ' "${temp_dir}/SRCINFO"

    if [ ! -s "${temp_dir}/SRCINFO" ]; then
      >&2 printf '"make_source_info" had empty output - eh, what?\n'
      exit 2
    fi
    printf '\n\n' >> "${temp_dir}/SRCINFO"

    printf '.' >&2
    pkgbase=$(
      grep '^pkgbase = ' "${temp_dir}/SRCINFO" | \
        cut -d' ' -f3
    )
    if [ -z "${pkgbase}" ]; then
      >&2 printf '"make_source_info" did not return a "pkgbase" - eh, what?\n'
      exit 2
    fi

    # add the package source
    mysql_add_package_source "${pkgbase}" "${git_revision}" "${mod_git_revision}" "${repository}"
    printf '.' >&2

    # now we encode everything in base64
    pkgbase=$(
      printf '%s' "${pkgbase}" | \
        base64 -w0
    )
    git_revision=$(
      printf '%s' "${git_revision}" | \
        base64 -w0
    )
    mod_git_revision=$(
      printf '%s' "${mod_git_revision}" | \
        base64 -w0
    )
    repository=$(
      printf '%s' "${repository}" | \
        base64 -w0
    )

    # add the build assignment(s)
    {
      archs=$(
        sed -n '
          s/^\tarch = //
          T
          p
        ' "${temp_dir}/SRCINFO" | \
          grep -vxF 'any' | \
          sort -u
      )
      if [ -z "${archs}" ]; then
        echo 'any'
      else
        printf '%s\n' "${archs}"
      fi
    } | \
      while read -r arch; do
        printf 'INSERT IGNORE INTO `build_assignments` (`package_source`,`architecture`,`is_blocked`)'
        printf ' SELECT `package_sources`.`id`,`architectures`.`id`,0'
        printf ' FROM `architectures` JOIN `package_sources`'
        printf ' WHERE `architectures`.`name` = from_base64("%s")' \
          "$(
            printf '%s' "${arch}" | \
              base64 -w0
          )"
        printf ' AND `package_sources`.`%s` = from_base64("%s")' \
          'pkgbase' "${pkgbase}" \
          'git_revision' "${git_revision}" \
          'mod_git_revision' "${mod_git_revision}"
        printf ';\n'
      done > \
      "${temp_dir}/add-build-assignments-command"

    # TODO: correctly link between binary_packages and build_assignments using any_arch

    # shellcheck disable=SC2034
    # select any specific arch (which will be building the 'any' part of a split package)
    any_arch=$(
      {
        sed -n '
          s/^\tarch = //
          T
          p
        ' "${temp_dir}/SRCINFO" | \
          sort -r | \
          grep -vxFm 1 'any' || \
          echo 'any'
      } | \
        base64_encode_each
    )

    grep '^pkgname = ' "${temp_dir}/SRCINFO" | \
      cut -d' ' -f3 | \
      while read -r pkgname; do
        pkgname64=$(
          printf '%s' "${pkgname}" | \
            base64 -w0
        )
        sed -n '
          /^pkgbase = \|^pkgname = '"$(str_to_regex "${pkgname}")"'$/{
            :a
              N
              /\n$/{
                p
                T
              }
              ba
          }
        ' "${temp_dir}/SRCINFO" | \
          sed '
            /^\S/d
            s/^\s*//
          ' > \
          "${temp_dir}/BINARYINFO.${pkgname64}"

        grep '^arch = ' "${temp_dir}/BINARYINFO.${pkgname64}" | \
          cut -d' ' -f3 | \
          while read -r arch; do
            arch64=$(
              printf '%s' "${arch}" | \
                base64 -w0
            )
            sed '
              s/^\(\S\+\)_'"${arch}"' = /\1 = /
            ' "${temp_dir}/BINARYINFO.${pkgname64}" > \
              "${temp_dir}/ARCHINFO ${pkgname64} ${arch64}"
          done
      done
    find "${temp_dir}" -mindepth 1 -maxdepth 1 -name 'ARCHINFO * *' -printf '%f\n' | \
      while read -r _ pkgname arch; do
        pkgver=$(
          grep '^pkgver = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" | \
            cut -d' ' -f3 | \
            base64_encode_each
        )
        pkgrel=$(
          grep '^pkgrel = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" | \
            cut -d' ' -f3 | \
            base64_encode_each
        )
        epoch=$(
          {
            grep '^epoch = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" || \
              echo 'epoch = 0'
          } | \
            cut -d' ' -f3 | \
            base64_encode_each
        )
        provides=$(
          grep '^\(groups\|provides\) = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" | \
            cut -d' ' -f3 | \
            sed 's/[<>=].*$//' | \
            base64_encode_each
        )
        builddepends=$(
          grep '^\(checkdepends\|makedepends\) = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" | \
            cut -d' ' -f3 | \
            sed 's/[<>=].*$//' | \
            base64_encode_each
        )
        rundepends=$(
          grep '^depends = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" | \
            cut -d' ' -f3 | \
            sed 's/[<>=].*$//' | \
            base64_encode_each
        )
        if [ -n "${forced_sub_pkgrel}" ]; then
          sub_pkgrel='from_base64("'"${forced_sub_pkgrel}"'")'
        else
          sub_pkgrel=$(
            printf '(SELECT COALESCE('
            # do not add binary packages which are currently on the build-list
            printf '(SELECT `sub_pkgrel` FROM `binary_packages`'
            printf ' JOIN `architectures` ON `binary_packages`.`architecture`=`architectures`.`id`'
            printf ' JOIN `repositories` ON `binary_packages`.`repository`=`repositories`.`id`'
            printf ' WHERE'
            printf ' `binary_packages`.`%s`=from_base64("%s") AND' \
              'epoch' "${epoch}" \
              'pkgver' "${pkgver}" \
              'pkgrel' "${pkgrel}" \
              'pkgname' "${pkgname}"
            printf ' `architectures`.`name`=from_base64("%s")' \
              "${arch}"
            printf ' AND `repositories`.`name`="build-list"),'
            # max(sub_pkgrel)+1
            printf '(SELECT 1+MAX(`binary_packages`.`sub_pkgrel`) FROM `binary_packages`'
            printf ' JOIN `architectures` ON `binary_packages`.`architecture`=`architectures`.`id`'
            printf ' WHERE'
            printf ' `binary_packages`.`%s`=from_base64("%s") AND' \
              'epoch' "${epoch}" \
              'pkgver' "${pkgver}" \
              'pkgrel' "${pkgrel}" \
              'pkgname' "${pkgname}"
            if printf '%s' "${arch}" | base64 -d | grep -qxF 'any'; then
              # 'any' gets higher sub_pkgrel than any architecture
              printf ' 1'
            else
              # not-'any' gets higher sub_pkgrel than same or 'any' architecture
              printf ' (`architectures`.`name`=from_base64("%s") OR `architectures`.`name`="any")' \
                "${arch}"
            fi
            printf ')'
            printf ',0))'
          )
        fi
        {
          printf 'INSERT IGNORE INTO `binary_packages` ('
          printf '`%s`,' \
            'build_assignment' \
            'repository' \
            'architecture' \
            'epoch' \
            'pkgver' \
            'pkgrel' \
            'pkgname' \
            'sub_pkgrel' \
            'has_issues' \
            'is_tested'
          printf ') SELECT '
          printf '`%s`.`id`,' \
            'build_assignments' \
            'repositories' \
            'architectures'
          printf 'from_base64("%s"),' \
            "${epoch}" \
            "${pkgver}" \
            "${pkgrel}" \
            "${pkgname}"
          printf '%s,0,0 FROM' \
            "${sub_pkgrel}"
          printf ' `%s` JOIN' \
            'repositories' \
            'architectures' \
            'build_assignments' \
            'package_sources'
          printf ' ON `build_assignments`.`package_source` = `package_sources`.`id`'
          printf ' JOIN `upstream_repositories`'
          printf ' ON `package_sources`.`upstream_package_repository` = `upstream_repositories`.`id`'
          printf ' WHERE'
          printf ' `%s`.`%s` = from_base64("%s") AND' \
            'repositories' 'name' "${current_repository}" \
            'architectures' 'name' "${arch}" \
            'package_sources' 'pkgbase' "${pkgbase}" \
            'package_sources' 'git_revision' "${git_revision}" \
            'package_sources' 'mod_git_revision' "${mod_git_revision}" \
            'upstream_repositories' 'name' "${repository}"
          printf ';\n'
        } | \
          sed '
            s|,)|)|g
            s|JOIN ON|ON|g
            s| AND;$|;|
          ' >> \
          "${temp_dir}/add-binary-packages-command"
        {
          printf 'CREATE TEMPORARY TABLE `%s` (`name` VARCHAR(64));\n' \
            'provides' \
            'builddepends' \
            'rundepends'

          printf 'INSERT INTO `provides` VALUES\n'
          echo "${provides}" | \
            sort -u | \
            grep -vxF '' | \
            sed '
              s|^| (from_base64("|
              s|$|")),|
            '
          printf ' (from_base64("%s"));\n' \
            "${pkgname}"

          printf 'INSERT INTO `rundepends` VALUES\n'
          echo "${rundepends}" | \
            sort -u | \
            grep -vxF '' | \
            sed '
              s|^| (from_base64("|
              s|$|")),|
            '
          printf ' ("base");\n'

          printf 'INSERT INTO `builddepends` VALUES\n'
          echo "${builddepends}" | \
            sort -u | \
            grep -vxF '' | \
            sed '
              s|^| (from_base64("|
              s|$|")),|
            '
          printf ' ("base-devel");\n'

          printf 'INSERT IGNORE INTO `install_targets` (`name`)'
          printf ' SELECT (`name`) FROM `%s` UNION' \
            'provides' \
            'builddepends' \
            'rundepends' | \
            sed 's| UNION$|;\n|'

          for link in 'provides' 'builddepends' 'rundepends'; do
            case "${link}" in
              'provides')
                printf 'INSERT IGNORE INTO `install_target_providers` (`package`,`install_target`) SELECT'
                printf ' `binary_packages`.`id`,`install_targets`.`id` FROM'
              ;;
              'builddepends'|'rundepends')
                printf 'INSERT IGNORE INTO `dependencies` (`dependent`,`depending_on`,`dependency_type`) SELECT'
                printf ' `binary_packages`.`id`,`install_targets`.`id`,`dependency_types`.`id` FROM'
                printf ' `dependency_types` JOIN'
              ;;
            esac
            printf ' `binary_packages`'
            printf ' JOIN `architectures`'
            printf ' ON `binary_packages`.`architecture` = `architectures`.`id`'
            printf ' JOIN `repositories`'
            printf ' ON `binary_packages`.`repository` = `repositories`.`id`'
            printf ' JOIN `install_targets`'
            printf ' JOIN `%s`' "${link}"
            printf ' ON `%s`.`name` = `install_targets`.`name`' "${link}"
            printf ' WHERE'
            if [ "${link}" = 'builddepends' ] || \
              [ "${link}" = 'rundepends' ]; then
              printf ' `dependency_types`.`name` = "%s" AND' \
                "${link%depends}"
            fi
            printf ' `binary_packages`.`%s` = from_base64("%s") AND' \
              'epoch' "${epoch}" \
              'pkgver' "${pkgver}" \
              'pkgrel' "${pkgrel}" \
              'pkgname' "${pkgname}"
            printf ' `binary_packages`.`sub_pkgrel` = %s AND' \
              "${sub_pkgrel}"
            printf ' `architectures`.`name` = from_base64("%s") AND' \
              "${arch}"
            printf ' `repositories`.`name` = from_base64("%s");\n' \
              "${current_repository}"
          done

          printf 'DROP TABLE `%s`;\n' \
            'provides' \
            'builddepends' \
            'rundepends'
        } >> \
          "${temp_dir}/add-install-targets-command"
      done
    printf '.' >&2

    {
      if [ -s "${temp_dir}/add-build-assignments-command" ]; then
        cat "${temp_dir}/add-build-assignments-command"
      fi
      if [ -s "${temp_dir}/add-binary-packages-command" ]; then
        cat "${temp_dir}/add-binary-packages-command"
      fi
      if [ -s "${temp_dir}/add-install-targets-command" ]; then
        cat "${temp_dir}/add-install-targets-command"
      fi
    } | \
      ${mysql_command}
    printf '.' >&2

  )
}

# mysql_sanity_check
# do a sanity check on the mysql database

mysql_sanity_check() {
  ( # new shell is intentional
    temp_dir=$(mktemp -d 'tmp.mysql-functions.mysql_sanity_check.XXXXXXXXXX' --tmpdir)
    trap 'rm -rf --one-file-system "${temp_dir}"' EXIT

    for dir in $(ls_master_mirror 'i686'); do
      ls_master_mirror "i686/${dir}" | \
        sed -n '
          s/\.pkg\.tar\.xz$//
          T
          s/-\([0-9]\+\)-\([^-]\+\)$/-\1.0-\2/
          s/-\([^-:]\+-[^-]\+-[^-]\+\)$/-0:\1/
          s|^|'"${dir}"'/|
          p
        '
    done | \
      sort > \
      "${temp_dir}/master-mirror-listing"

    # shellcheck disable=SC2016
    {
      printf 'SELECT `repositories`.`name`,`pkgname`,`epoch`,`pkgver`,`pkgrel`,`sub_pkgrel`,`architectures`.`name`'
      printf ' FROM `binary_packages`'
      printf ' JOIN `architectures` ON `binary_packages`.`architecture`=`architectures`.`id`'
      printf ' JOIN `repositories` ON `binary_packages`.`repository`=`repositories`.`id`'
      printf ' WHERE `repositories`.`is_on_master_mirror`'
    } | \
      ${mysql_command} --batch | \
      sed '
        1d
        s,\t,/,
        s,\t,-,
        s,\t,:,
        s,\t,-,
        s,\t,.,
        s,\t,-,
      ' | \
      sort > \
      "${temp_dir}/mysql-packages"

    diff -u \
      "${temp_dir}/master-mirror-listing" \
      "${temp_dir}/mysql-packages"
  )
}