#!/usr/bin/env bash
set -Eeuo pipefail

EXPECTED_HOST=sqjcDB01
EXPECTED_IP=10.2.208.106
MYSQL80=/opt/mysql/8.0.46/bin/mysql
MYSQL80_SOCKET=/run/mysql80/mysql.sock
MYSQL56=/opt/mysql/5.6.51/bin/mysql
MYSQL56_SOCKET=/run/mysql56/mysql.sock
EXPECTED_PLUGIN=mysql_native_password
ACTION=${1:-}
CREATED=0
EVIDENCE_DIR=
AUTH_TEMP=
MYSQL80_SAFE_META=

finish() {
  local rc=$?
  if [[ -n "${AUTH_TEMP:-}" ]]; then
    rm -f -- "$AUTH_TEMP"
    AUTH_TEMP=
  fi
  if [[ -n "${EVIDENCE_DIR:-}" ]]; then
    printf 'finished_at=%s\nexit_code=%s\nrollback_state=%s\n' \
      "$(date '+%Y-%m-%dT%H:%M:%S%z')" "$rc" \
      "$([[ "$rc" -eq 0 ]] && echo NOT_REQUIRED || echo EXPLICIT_WHEN_NEW_ACCOUNT_CREATED)" \
      >"$EVIDENCE_DIR/result.env"
    chmod 0600 "$EVIDENCE_DIR/result.env"
  fi
  echo "END MYSQL01_MYSQL80_WWWWEB_CREATE RC=$rc"
}
trap finish EXIT

static_validate() {
  command -v awk grep mktemp sha256sum stat wc >/dev/null
  bash -n "$0"
  echo MYSQL01_MYSQL80_WWWWEB_STATIC_VALIDATE_PASS
}

validate_mysql56_contract() {
  local account_count=$1 auth_hash=$2 grants_text=$3
  local prefix="GRANT ALL PRIVILEGES ON *.* TO 'wwwweb'@'%' IDENTIFIED BY PASSWORD '"
  local suffix="' WITH GRANT OPTION"
  local grant_hash
  [[ "$account_count" == 1 ]] || return 1
  [[ "$auth_hash" =~ ^\*[0-9A-F]{40}$ ]] || return 1
  [[ $(wc -l <<<"$grants_text") -eq 1 ]] || return 1
  [[ "$grants_text" == "$prefix"*"$suffix" ]] || return 1
  grant_hash=${grants_text#"$prefix"}
  grant_hash=${grant_hash%"$suffix"}
  [[ "$grant_hash" == "$auth_hash" ]] || return 1
}

self_test_contract() {
  local mock_hash positive missing_privilege extra_grant
  mock_hash="*$(printf 'A%.0s' {1..40})"
  positive="GRANT ALL PRIVILEGES ON *.* TO 'wwwweb'@'%' IDENTIFIED BY PASSWORD '$mock_hash' WITH GRANT OPTION"
  missing_privilege="GRANT SELECT ON *.* TO 'wwwweb'@'%' IDENTIFIED BY PASSWORD '$mock_hash' WITH GRANT OPTION"
  extra_grant="$positive"$'\n'"GRANT SELECT ON test.* TO 'wwwweb'@'%'"
  validate_mysql56_contract 1 "$mock_hash" "$positive"
  echo MYSQL01_MYSQL56_GRANT_POSITIVE_CASE_PASS
  ! validate_mysql56_contract 1 "$mock_hash" "$missing_privilege"
  echo MYSQL01_MYSQL56_GRANT_MISSING_PRIVILEGE_NEGATIVE_CASE_PASS
  ! validate_mysql56_contract 1 "$mock_hash" "$extra_grant"
  echo MYSQL01_MYSQL56_GRANT_EXTRA_GRANT_NEGATIVE_CASE_PASS
  echo MYSQL01_MYSQL56_GRANT_CONTRACT_SELF_TEST_PASS
}

rollback_new_account() {
  [[ "$CREATED" -eq 1 ]] || return 0
  if $MYSQL80 --no-defaults --protocol=socket --socket="$MYSQL80_SOCKET" \
    -e "DROP USER IF EXISTS 'wwwweb'@'%';" >/dev/null 2>&1; then
    CREATED=0
    echo MYSQL80_WWWWEB_AUTOMATIC_ROLLBACK_PASS_DROPPED_NEW_ACCOUNT
    return 0
  fi
  echo MYSQL80_WWWWEB_AUTOMATIC_ROLLBACK_FAIL_MANUAL_REVIEW_REQUIRED
  return 1
}

verify_mysql80_account_contract() {
  local expected_hash=$1 meta account plugin locked current_hash grants prefix suffix grant_hash
  meta=$($MYSQL80 --no-defaults --protocol=socket --socket="$MYSQL80_SOCKET" --batch --skip-column-names \
    -e "SELECT CONCAT(User,'@',Host),plugin,account_locked,authentication_string FROM mysql.user WHERE User='wwwweb' AND Host='%'") || return 1
  IFS=$'\t' read -r account plugin locked current_hash <<<"$meta"
  [[ "$account" == 'wwwweb@%' && "$plugin" == mysql_native_password && "$locked" == N ]] || return 1
  [[ "$current_hash" =~ ^\*[0-9A-F]{40}$ && "$current_hash" == "$expected_hash" ]] || return 1
  grants=$($MYSQL80 --no-defaults --protocol=socket --socket="$MYSQL80_SOCKET" --batch --skip-column-names \
    -e "SHOW GRANTS FOR 'wwwweb'@'%'") || return 1
  [[ $(wc -l <<<"$grants") -eq 1 ]] || return 1
  if [[ "$grants" != 'GRANT ALL PRIVILEGES ON *.* TO `wwwweb`@`%` WITH GRANT OPTION' ]]; then
    prefix="GRANT ALL PRIVILEGES ON *.* TO \`wwwweb\`@\`%\` IDENTIFIED WITH 'mysql_native_password' AS '"
    suffix="' WITH GRANT OPTION"
    [[ "$grants" == "$prefix"*"$suffix" ]] || return 1
    grant_hash=${grants#"$prefix"}
    grant_hash=${grant_hash%"$suffix"}
    [[ "$grant_hash" == "$expected_hash" ]] || return 1
  fi
  MYSQL80_SAFE_META='wwwweb@%|mysql_native_password|N'
}

[[ "$ACTION" == run || "$ACTION" == --static-validate || "$ACTION" == --self-test-contract ]] || {
  echo "usage: $0 --static-validate|--self-test-contract|run" >&2
  exit 2
}
[[ "$ACTION" == --static-validate ]] && { static_validate; exit 0; }
[[ "$ACTION" == --self-test-contract ]] && { self_test_contract; exit 0; }

echo BEGIN MYSQL01_MYSQL80_WWWWEB_CREATE
[[ "$(hostname -s)" == "$EXPECTED_HOST" ]] || { echo 'ERROR: wrong hostname'; exit 1; }
[[ "$EUID" -eq 0 ]] || { echo 'ERROR: root required'; exit 1; }
ip -o -4 addr show scope global | grep -q "${EXPECTED_IP//./\\.}/" || { echo 'ERROR: wrong IP'; exit 1; }

run_id="mysql80-wwwweb-$(date '+%Y%m%dT%H%M%S%z')"
EVIDENCE_DIR="/var/backups/zhct-mysql80-wwwweb/$run_id"
install -d -m 0700 -o root -g root "$EVIDENCE_DIR"
exec > >(tee "$EVIDENCE_DIR/operator.log") 2>&1
chmod 0600 "$EVIDENCE_DIR/operator.log"

echo "started_at=$(date '+%Y-%m-%dT%H:%M:%S%z')"
echo "target_hostname=$EXPECTED_HOST"
echo "target_ip=$EXPECTED_IP"
echo 'permission_contract=ALL_PRIVILEGES_ON_ALL_DATABASES_WITH_GRANT_OPTION'
echo "authentication_plugin=$EXPECTED_PLUGIN"
echo 'authentication_source=MYSQL56_WWWWEB_PASSWORD_HASH_LOCAL_ONLY'

[[ -x "$MYSQL80" && -S "$MYSQL80_SOCKET" ]] || { echo 'ERROR: MySQL8 client or socket unavailable'; exit 1; }
[[ -x "$MYSQL56" && -S "$MYSQL56_SOCKET" ]] || { echo 'ERROR: MySQL5.6 client or socket unavailable'; exit 1; }
ss -lnt | awk '$4=="10.2.208.106:3308" {ok=1} END{exit !ok}' || { echo 'ERROR: MySQL8 listener mismatch'; exit 1; }

identity80=$($MYSQL80 --no-defaults --protocol=socket --socket="$MYSQL80_SOCKET" --batch --skip-column-names \
  -e 'SELECT @@port,@@server_id,@@server_uuid,@@global.gtid_mode,@@global.enforce_gtid_consistency,@@global.log_bin,@@global.log_replica_updates,@@global.read_only,@@global.super_read_only')
read -r port sid uuid gtid enforce logbin logupdates ro sro <<<"$identity80"
[[ "$port:$sid:$gtid:$enforce:$logbin:$logupdates:$ro:$sro" == '3308:80106:ON:ON:1:1:0:0' ]] || {
  echo 'ERROR: MySQL8 primary identity/GTID contract mismatch'; exit 1;
}
echo "MYSQL80_PRIMARY_IDENTITY=PASS port=$port server_id=$sid server_uuid=$uuid"
echo MYSQL80_GTID_BINLOG_GATE=PASS
echo 'MYSQL02_READ_ONLY_BASELINE=ACCEPTED_PRIOR_REPLICATION_EVIDENCE; LIVE_RECHECK_REQUIRED_AFTER_CREATE'

account56=$($MYSQL56 --no-defaults --protocol=socket --socket="$MYSQL56_SOCKET" --batch --skip-column-names \
  -e "SELECT COUNT(*) FROM mysql.user WHERE User='wwwweb' AND Host='%'")
AUTH_HASH=$($MYSQL56 --no-defaults --protocol=socket --socket="$MYSQL56_SOCKET" --batch --skip-column-names \
  -e "SELECT Password FROM mysql.user WHERE User='wwwweb' AND Host='%'")
grants56=$($MYSQL56 --no-defaults --protocol=socket --socket="$MYSQL56_SOCKET" --batch --skip-column-names \
  -e "SHOW GRANTS FOR 'wwwweb'@'%'")
if ! validate_mysql56_contract "$account56" "$AUTH_HASH" "$grants56"; then
  unset AUTH_HASH grants56
  echo 'ERROR: MySQL5.6 account/hash/grant contract mismatch; MySQL5.6 was not changed'
  exit 1
fi
unset grants56
echo MYSQL56_WWWWEB_BASELINE=PASS_READ_ONLY
echo MYSQL56_AUTHENTICATION_HASH=PASS_FORMAT_ONLY_NOT_OUTPUT

version80=$($MYSQL80 --no-defaults --protocol=socket --socket="$MYSQL80_SOCKET" --batch --skip-column-names -e 'SELECT VERSION()')
[[ "$version80" =~ ^8[.]0[.] ]] || { unset AUTH_HASH; echo 'ERROR: MySQL8 version is outside approved 8.0 family'; exit 1; }
plugin_status=$($MYSQL80 --no-defaults --protocol=socket --socket="$MYSQL80_SOCKET" --batch --skip-column-names \
  -e "SELECT PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME='mysql_native_password'")
[[ "$plugin_status" == ACTIVE ]] || { unset AUTH_HASH; echo 'ERROR: mysql_native_password plugin is not ACTIVE'; exit 1; }
echo "MYSQL80_VERSION_GATE=PASS version=$version80"
echo MYSQL80_NATIVE_PASSWORD_PLUGIN_GATE=PASS_ACTIVE

existing=$($MYSQL80 --no-defaults --protocol=socket --socket="$MYSQL80_SOCKET" --batch --skip-column-names \
  -e "SELECT COUNT(*) FROM mysql.user WHERE User='wwwweb' AND Host='%'")
if [[ "$existing" == 0 ]]; then
  umask 077
  AUTH_TEMP=$(mktemp /run/zhct-mysql80-wwwweb.XXXXXX.sql)
  chmod 0600 "$AUTH_TEMP"
  printf "CREATE USER 'wwwweb'@'%%' IDENTIFIED WITH mysql_native_password AS '%s';\n" "$AUTH_HASH" >"$AUTH_TEMP"
  $MYSQL80 --no-defaults --protocol=socket --socket="$MYSQL80_SOCKET" <"$AUTH_TEMP"
  rm -f -- "$AUTH_TEMP"
  AUTH_TEMP=
  CREATED=1
  if ! $MYSQL80 --no-defaults --protocol=socket --socket="$MYSQL80_SOCKET" \
    -e "GRANT ALL PRIVILEGES ON *.* TO 'wwwweb'@'%' WITH GRANT OPTION;"; then
    rollback_new_account || true
    echo 'ERROR: MySQL8 grant failed; new account rollback attempted'
    exit 1
  fi
  echo MYSQL80_WWWWEB_CHANGE=CREATED
elif [[ "$existing" == 1 ]]; then
  echo MYSQL80_WWWWEB_CHANGE=PREEXISTING_VERIFY_ONLY
else
  unset AUTH_HASH
  echo 'ERROR: duplicate account metadata'
  exit 1
fi

if ! verify_mysql80_account_contract "$AUTH_HASH"; then
  unset AUTH_HASH
  rollback_new_account || true
  echo 'ERROR: MySQL8 account metadata or grant contract mismatch; new account rollback attempted when applicable'
  exit 1
fi
unset AUTH_HASH
echo "MYSQL80_WWWWEB_METADATA=PASS account=$MYSQL80_SAFE_META"
echo MYSQL80_WWWWEB_GRANT_CONTRACT=PASS
echo MYSQL56_CHANGE=NOT_PERFORMED
echo BUSINESS_SQL=NOT_PERFORMED
echo SERVER_CONFIG_FIREWALL_ORCHESTRATOR=NOT_PERFORMED
echo NEXT_REQUIRED=RUN_MYSQL02_READONLY_VERIFIER
echo MYSQL01_MYSQL80_WWWWEB_CREATE_PASS