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

PROGRAM=${0##*/}
PACKAGE_DIR=$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)
INPUT_ROOT=${INPUT_ROOT:-/root/zhct-q220-222-inputs}
EVIDENCE_ROOT=/var/backups/zhct-q220-222
PRIVATE_BACKUP_ROOT=/var/backups/zhct-q220-222-private
STATE_ROOT=/var/lib/zhct-q220-222
APPROVE_DANGEROUS=0

die() { printf 'ERROR: %s\n' "$*" >&2; exit 1; }
require_file() { [[ -f "$1" ]] || die "required file missing: $1"; }
require_dir() { [[ -d "$1" ]] || die "required directory missing: $1"; }
require_root() { [[ $(id -u) -eq 0 ]] || die "root is required"; }
usage() {
  printf '%s\n' \
    "Usage: $PROGRAM --static-validate" \
    "       $PROGRAM q220|q221|q222 preflight|execute|verify|rollback|archive --node NODE [--approve-dangerous]"
}

static_validate() {
  local f
  for f in README.md INPUTS_REQUIRED.md q220-222-mysql.sh \
    templates/mysql-recovery-hook.sh templates/mysql-primary-marker-check.sh \
    templates/mysql-fence-local.sh; do require_file "$PACKAGE_DIR/$f"; done
  bash -n "$PACKAGE_DIR/q220-222-mysql.sh"
  bash -n "$PACKAGE_DIR/templates/mysql-recovery-hook.sh"
  bash -n "$PACKAGE_DIR/templates/mysql-primary-marker-check.sh"
  bash -n "$PACKAGE_DIR/templates/mysql-fence-local.sh"
  ! grep -REni '(password|passwd|token|cookie|private[ _-]?key)[[:space:]]*[:=][[:space:]]*[^_[:space:]]' \
    "$PACKAGE_DIR" --exclude=SHA256SUMS || die "secret-like assignment detected"
  ! grep -REni '(^|[[:space:]])(INSERT|UPDATE|DELETE|LOAD[[:space:]]+DATA|SOURCE|CREATE[[:space:]]+TABLE|DROP[[:space:]]+TABLE)' \
    "$PACKAGE_DIR" --include='*.sql' || die "business SQL detected in package"
  grep -Fq -- '--approve-dangerous' "$PACKAGE_DIR/q220-222-mysql.sh" ||
    die "dangerous acknowledgement gate missing"
  grep -Fq 'Q-261' "$PACKAGE_DIR/README.md" || die "Q-261 gate missing"
  grep -Fq 'Q-223' "$PACKAGE_DIR/README.md" || die "Q-223 gate missing"
  printf 'Q220_222_MYSQL_STATIC_VALIDATE_OK\n'
}

configure_node() {
  case "$NODE" in
    mysql01) EXPECTED_HOST=sqjcDB01; EXPECTED_IP=10.2.208.106; ROLE=mysql ;;
    mysql02) EXPECTED_HOST=sqjcDB02; EXPECTED_IP=10.2.208.107; ROLE=mysql ;;
    nginx01) EXPECTED_HOST=sqjcnginx01; EXPECTED_IP=10.2.203.102; ROLE=orchestrator-haproxy ;;
    nginx02) EXPECTED_HOST=sqjcnginx02; EXPECTED_IP=10.2.203.103; ROLE=orchestrator-haproxy ;;
    redis01) EXPECTED_HOST=sqjcredis01; EXPECTED_IP=10.2.203.112; ROLE=orchestrator ;;
    *) die "unsupported node: $NODE" ;;
  esac
}

safe_run_id() {
  [[ "$1" =~ ^${TASK}-${NODE}-[0-9]{8}T[0-9]{6}[+-][0-9]{4}$ ]] ||
    die "unsafe run_id: $1"
}

new_run() {
  RUN_ID="${TASK}-${NODE}-$(date '+%Y%m%dT%H%M%S%z')"
  safe_run_id "$RUN_ID"
  EVIDENCE_DIR="$EVIDENCE_ROOT/$RUN_ID"
  install -d -m 0700 -o root -g root "$EVIDENCE_DIR" "$STATE_ROOT"
  printf '%s\n' "$RUN_ID" >"$STATE_ROOT/${TASK}-${NODE}.last"
  chmod 0600 "$STATE_ROOT/${TASK}-${NODE}.last"
}

load_run() {
  require_file "$STATE_ROOT/${TASK}-${NODE}.last"
  RUN_ID=$(<"$STATE_ROOT/${TASK}-${NODE}.last")
  safe_run_id "$RUN_ID"
  EVIDENCE_DIR="$EVIDENCE_ROOT/$RUN_ID"
  require_dir "$EVIDENCE_DIR"
}

start_log() {
  local action=$1
  exec > >(tee -a "$EVIDENCE_DIR/${action}.log") 2>&1
  printf 'started_at=%s\nrun_id=%s\ntask=%s\naction=%s\nnode=%s\nhostname_expected=%s\nip_expected=%s\n' \
    "$(date --iso-8601=seconds)" "$RUN_ID" "$TASK" "$action" "$NODE" "$EXPECTED_HOST" "$EXPECTED_IP"
  printf 'dangerous_ack=%s\ninput_root=%s\n' "$APPROVE_DANGEROUS" "$INPUT_ROOT"
}

finish() {
  local rc=$? action=$1
  trap - EXIT
  printf 'finished_at=%s\naction=%s\nexit_code=%s\n' \
    "$(date --iso-8601=seconds)" "$action" "$rc" | tee -a "$EVIDENCE_DIR/action-result.env"
  exit "$rc"
}

assert_identity() {
  local actual
  actual=$(hostname -s)
  [[ "${actual,,}" == "${EXPECTED_HOST,,}" ]] ||
    die "hostname mismatch: expected=$EXPECTED_HOST actual=$actual"
  ip -o -4 addr show scope global | awk -v ip="$EXPECTED_IP" '
    {split($4,a,"/"); if(a[1]==ip) ok=1} END{exit !ok}
  ' || die "expected IP not present: $EXPECTED_IP"
  printf 'identity_gate=PASS\n'
}

assert_secure_file() {
  local f=$1
  require_file "$f"
  [[ $(stat -c '%U:%G' "$f") == root:root ]] || die "unsafe owner: $f"
  case "$(stat -c '%a' "$f")" in 400|600) ;; *) die "unsafe mode: $f" ;; esac
}

approval_value() {
  awk -F= -v key="$1" '$1==key{print substr($0,index($0,"=")+1)}' "$INPUT_ROOT/approval.env"
}

assert_inputs_root() {
  require_dir "$INPUT_ROOT"
  [[ $(stat -c '%U:%G' "$INPUT_ROOT") == root:root ]] || die "input root owner must be root:root"
  [[ $(stat -c '%a' "$INPUT_ROOT") == 700 ]] || die "input root mode must be 0700"
  assert_secure_file "$INPUT_ROOT/approval.env"
  assert_secure_file "$INPUT_ROOT/SHA256SUMS.inputs"
  (cd "$INPUT_ROOT" && sha256sum -c SHA256SUMS.inputs) \
    >"$EVIDENCE_DIR/input-checksums.txt" 2>&1 || die "external input checksum mismatch"
  printf 'external_input_digest_gate=PASS\n'
}

require_approval() {
  [[ "$APPROVE_DANGEROUS" -eq 1 ]] || die "--approve-dangerous is required"
  [[ $(approval_value TASK) == "$TASK" ]] || die "approval TASK mismatch"
  [[ $(approval_value NODE) == "$NODE" ]] || die "approval NODE mismatch"
  [[ $(approval_value ALLOW_EXECUTE) == yes ]] || die "approval does not allow execute"
  [[ -n $(approval_value CHANGE_TICKET) ]] || die "CHANGE_TICKET missing"
  [[ -n $(approval_value MAINTENANCE_WINDOW) ]] || die "MAINTENANCE_WINDOW missing"
  [[ -n $(approval_value APPROVED_BY) ]] || die "APPROVED_BY missing"
  printf 'explicit_change_approval_gate=PASS\n'
}

assert_admin_files() {
  assert_secure_file "$INPUT_ROOT/mysql56-admin.cnf"
  assert_secure_file "$INPUT_ROOT/mysql80-admin.cnf"
}

validate_no_business_sql() {
  local f=$1
  assert_secure_file "$f"
  ! grep -Eiq '(^|[;[:space:]])(INSERT|UPDATE|DELETE|REPLACE|LOAD[[:space:]]+DATA|SOURCE|CREATE[[:space:]]+TABLE|ALTER[[:space:]]+TABLE|DROP[[:space:]]+(TABLE|DATABASE)|TRUNCATE)' "$f" ||
    die "business/data-destructive SQL forbidden: $f"
}

validate_readonly_sql() {
  local f=$1
  assert_secure_file "$f"
  ! grep -Eiv '^[[:space:]]*(--|#|$|SELECT([[:space:]]|$)|SHOW([[:space:]]|$))' "$f" | grep -q . ||
    die "verify SQL contains a non-read-only statement: $f"
}

require_receipt() {
  local file=$1 marker=$2
  assert_secure_file "$file"
  grep -Fxq "$marker=PASS" "$file" || die "PASS receipt marker missing: $marker"
  grep -Eq '^archive_sha256=[0-9a-f]{64}$' "$file" || die "receipt archive SHA missing"
}

q220_preflight() {
  [[ "$ROLE" == mysql ]] || die "Q-220 only supports mysql nodes"
  assert_admin_files
  assert_secure_file "$INPUT_ROOT/q220-contract.env"
  if [[ "$NODE" == mysql01 ]]; then
    local f
    for f in q220-mysql56.sql q220-mysql80.sql q220-rollback-mysql56.sql \
      q220-rollback-mysql80.sql; do validate_no_business_sql "$INPUT_ROOT/$f"; done
    grep -Eiq 'CREATE[[:space:]]+DATABASE.*zhct_sanquan' "$INPUT_ROOT/q220-mysql56.sql" ||
      die "MySQL5.6 zhct_sanquan empty schema contract missing"
    grep -Eiq 'CREATE[[:space:]]+DATABASE.*aizhct_sanquan' "$INPUT_ROOT/q220-mysql56.sql" ||
      die "MySQL5.6 aizhct_sanquan empty schema contract missing"
  fi
  validate_readonly_sql "$INPUT_ROOT/q220-verify-mysql56.sql"
  validate_readonly_sql "$INPUT_ROOT/q220-verify-mysql80.sql"
  printf 'q220_preflight=PASS\nbusiness_sql=FORBIDDEN_Q261_GATE\n'
}

q221_preflight() {
  [[ "$NODE" == mysql02 ]] || die "Q-221 execute/verify is restricted to mysql02"
  assert_admin_files
  require_receipt "$INPUT_ROOT/q220-pass.env" Q220
  assert_secure_file "$INPUT_ROOT/q221-policy.env"
  local f
  for f in q221-mysql56-replication.sql q221-mysql80-replication.sql \
    q221-rollback-mysql56.sql q221-rollback-mysql80.sql; do
    validate_no_business_sql "$INPUT_ROOT/$f"
  done
  printf 'q221_preflight=PASS\nq220_dependency=PASS\n'
}

q222_preflight() {
  require_receipt "$INPUT_ROOT/q221-pass.env" Q221
  assert_secure_file "$INPUT_ROOT/q222-executor.env"
  case "$ROLE" in
    orchestrator|orchestrator-haproxy)
      assert_secure_file "$INPUT_ROOT/q222-orchestrator-${NODE}.json"
      python3 -m json.tool "$INPUT_ROOT/q222-orchestrator-${NODE}.json" >/dev/null ||
        die "invalid orchestrator JSON"
      ! grep -Eq '__[A-Z0-9_]+__' "$INPUT_ROOT/q222-orchestrator-${NODE}.json" ||
        die "orchestrator config still contains placeholders"
      ;;
  esac
  if [[ "$ROLE" == orchestrator-haproxy ]]; then
    assert_secure_file "$INPUT_ROOT/q222-haproxy-${NODE}.cfg"
    haproxy -c -f "$INPUT_ROOT/q222-haproxy-${NODE}.cfg" \
      >"$EVIDENCE_DIR/haproxy-dry-run.txt" 2>&1 || die "HAProxy dry-run failed"
  fi
  if [[ "$ROLE" == mysql ]]; then assert_admin_files; fi
  printf 'q222_preflight=PASS\nlive_failover=NOT_PERFORMED_Q223_GATE\n'
}

preflight() {
  assert_identity
  assert_inputs_root
  case "$TASK" in q220) q220_preflight ;; q221) q221_preflight ;; q222) q222_preflight ;; esac
}

run_sql_file() {
  local version=$1 file=$2 cnf client
  case "$version" in
    56) client=/opt/mysql/5.6.51/bin/mysql; cnf="$INPUT_ROOT/mysql56-admin.cnf" ;;
    80) client=/opt/mysql/8.0.46/bin/mysql; cnf="$INPUT_ROOT/mysql80-admin.cnf" ;;
  esac
  printf 'sql_asset=%s\nsql_asset_sha256=%s\n' "${file##*/}" "$(sha256sum "$file" | awk '{print $1}')"
  "$client" --defaults-extra-file="$cnf" --batch <"$file"
  printf 'sql_asset_rc=0\n'
}

q220_execute() {
  [[ "$NODE" == mysql01 ]] || die "Q-220 execute is restricted to mysql01"
  install -d -m 0700 -o root -g root "$PRIVATE_BACKUP_ROOT/$RUN_ID"
  /opt/mysql/5.6.51/bin/mysql --defaults-extra-file="$INPUT_ROOT/mysql56-admin.cnf" \
    --batch --skip-column-names -e 'SHOW DATABASES' \
    >"$PRIVATE_BACKUP_ROOT/$RUN_ID/mysql56-schemas-before.txt"
  /opt/mysql/8.0.46/bin/mysql --defaults-extra-file="$INPUT_ROOT/mysql80-admin.cnf" \
    --batch --skip-column-names -e 'SHOW DATABASES' \
    >"$PRIVATE_BACKUP_ROOT/$RUN_ID/mysql80-schemas-before.txt"
  run_sql_file 56 "$INPUT_ROOT/q220-mysql56.sql"
  run_sql_file 80 "$INPUT_ROOT/q220-mysql80.sql"
  printf 'q220_execute=PASS\nbusiness_data_import=NOT_PERFORMED_Q261_GATE\n'
}

q221_execute() {
  [[ "$NODE" == mysql02 ]] || die "Q-221 execute is restricted to mysql02"
  run_sql_file 56 "$INPUT_ROOT/q221-mysql56-replication.sql"
  run_sql_file 80 "$INPUT_ROOT/q221-mysql80-replication.sql"
  printf 'q221_execute=PASS\n'
}

q222_execute() {
  local stage=/etc/zhct/q222-staged backup="$PRIVATE_BACKUP_ROOT/$RUN_ID"
  install -d -m 0700 -o root -g root "$backup"
  if [[ -e "$stage" ]]; then cp -a "$stage" "$backup/staged-before"; fi
  install -d -m 0700 -o root -g root "$stage"
  install -m 0750 -o root -g root "$PACKAGE_DIR/templates/mysql-recovery-hook.sh" "$stage/"
  install -m 0750 -o root -g root "$PACKAGE_DIR/templates/mysql-primary-marker-check.sh" "$stage/"
  install -m 0750 -o root -g root "$PACKAGE_DIR/templates/mysql-fence-local.sh" "$stage/"
  install -m 0600 -o root -g root "$INPUT_ROOT/q222-executor.env" "$stage/"
  case "$ROLE" in
    orchestrator|orchestrator-haproxy)
      install -m 0600 -o root -g root "$INPUT_ROOT/q222-orchestrator-${NODE}.json" "$stage/orchestrator.json"
      ;;
  esac
  if [[ "$ROLE" == orchestrator-haproxy ]]; then
    install -m 0600 -o root -g root "$INPUT_ROOT/q222-haproxy-${NODE}.cfg" "$stage/haproxy.cfg"
  fi
  find "$stage" -maxdepth 1 -type f -exec sha256sum {} + >"$EVIDENCE_DIR/q222-staged.sha256"
  printf 'q222_execute=PASS_STAGE_ONLY\nservice_reload=NOT_PERFORMED\n'
  printf 'live_recovery_hook=NOT_ACTIVATED_Q223_GATE\n'
}

execute() {
  preflight
  require_approval
  case "$TASK" in q220) q220_execute ;; q221) q221_execute ;; q222) q222_execute ;; esac
}

run_validator() {
  local version=$1 file=$2 expected=$3 client cnf output="$EVIDENCE_DIR/${expected}.txt"
  case "$version" in
    56) client=/opt/mysql/5.6.51/bin/mysql; cnf="$INPUT_ROOT/mysql56-admin.cnf" ;;
    80) client=/opt/mysql/8.0.46/bin/mysql; cnf="$INPUT_ROOT/mysql80-admin.cnf" ;;
  esac
  "$client" --defaults-extra-file="$cnf" --batch --skip-column-names <"$file" >"$output"
  [[ $(cat "$output") == "$expected" ]] || die "validator marker mismatch: $expected"
}

q220_verify() {
  q220_preflight
  run_validator 56 "$INPUT_ROOT/q220-verify-mysql56.sql" Q220_MYSQL56_CONTRACT_PASS
  run_validator 80 "$INPUT_ROOT/q220-verify-mysql80.sql" Q220_MYSQL80_CONTRACT_PASS
  printf 'Q220_VERIFY_PASS\n'
}

replication_status() {
  local version=$1 client cnf statement out="$EVIDENCE_DIR/mysql${version}-replication-status.txt"
  case "$version" in
    56) client=/opt/mysql/5.6.51/bin/mysql; cnf="$INPUT_ROOT/mysql56-admin.cnf"; statement='SHOW SLAVE STATUS' ;;
    80) client=/opt/mysql/8.0.46/bin/mysql; cnf="$INPUT_ROOT/mysql80-admin.cnf"; statement='SHOW REPLICA STATUS' ;;
  esac
  "$client" --defaults-extra-file="$cnf" --vertical -e "$statement" |
    awk -F: '
      /^[[:space:]]*(Master_Host|Source_Host|Master_Port|Source_Port|Slave_IO_Running|Replica_IO_Running|Slave_SQL_Running|Replica_SQL_Running|Seconds_Behind_Master|Seconds_Behind_Source|Auto_Position|Last_IO_Error|Last_SQL_Error):/ {print}
    ' >"$out"
  grep -Eq '(Slave_IO_Running|Replica_IO_Running): Yes' "$out" || die "replication IO thread not running"
  grep -Eq '(Slave_SQL_Running|Replica_SQL_Running): Yes' "$out" || die "replication SQL thread not running"
}

q221_verify() {
  q221_preflight
  replication_status 56
  replication_status 80
  printf 'Q221_VERIFY_PASS\n'
}

q222_verify() {
  q222_preflight
  require_dir /etc/zhct/q222-staged
  sha256sum /etc/zhct/q222-staged/* >"$EVIDENCE_DIR/q222-staged-verify.sha256"
  printf 'Q222_VERIFY_PASS_STAGE_DRY_RUN_ONLY\nlive_failover=NOT_PERFORMED_Q223_GATE\n'
}

verify() {
  assert_identity
  assert_inputs_root
  case "$TASK" in q220) q220_verify ;; q221) q221_verify ;; q222) q222_verify ;; esac
}

rollback() {
  assert_identity
  assert_inputs_root
  require_approval
  case "$TASK" in
    q220)
      [[ "$NODE" == mysql01 ]] || die "Q-220 rollback is restricted to mysql01"
      run_sql_file 80 "$INPUT_ROOT/q220-rollback-mysql80.sql"
      run_sql_file 56 "$INPUT_ROOT/q220-rollback-mysql56.sql"
      ;;
    q221)
      [[ "$NODE" == mysql02 ]] || die "Q-221 rollback is restricted to mysql02"
      run_sql_file 80 "$INPUT_ROOT/q221-rollback-mysql80.sql"
      run_sql_file 56 "$INPUT_ROOT/q221-rollback-mysql56.sql"
      ;;
    q222)
      local stage=/etc/zhct/q222-staged backup="$PRIVATE_BACKUP_ROOT/$RUN_ID"
      [[ -d "$backup" ]] || die "private backup missing"
      if [[ -d "$backup/staged-before" ]]; then
        rm -rf -- "$stage"
        mv "$backup/staged-before" "$stage"
      else
        rm -rf -- "$stage"
      fi
      ;;
  esac
  printf '%s_ROLLBACK_OK\n' "${TASK^^}"
}

archive() {
  local archive="$EVIDENCE_ROOT/${RUN_ID}.tar.gz"
  [[ ! -e "$archive" ]] || die "archive already exists: $archive"
  tar -czf "$archive" -C "$EVIDENCE_ROOT" "$RUN_ID"
  chmod 0600 "$archive"
  sha256sum "$archive" | tee "$archive.sha256"
  printf 'archive=%s\n%s_ARCHIVE_OK\n' "$archive" "${TASK^^}"
}

main() {
  if [[ ${1:-} == --static-validate ]]; then static_validate; exit 0; fi
  [[ $# -ge 4 ]] || { usage; exit 2; }
  TASK=$1 ACTION=$2
  shift 2
  [[ ${1:-} == --node && -n ${2:-} ]] || { usage; exit 2; }
  NODE=$2
  shift 2
  while [[ $# -gt 0 ]]; do
    case "$1" in --approve-dangerous) APPROVE_DANGEROUS=1 ;; *) usage; exit 2 ;; esac
    shift
  done
  case "$TASK" in q220|q221|q222) ;; *) usage; exit 2 ;; esac
  case "$ACTION" in preflight|execute|verify|rollback|archive) ;; *) usage; exit 2 ;; esac
  configure_node
  if [[ "$TASK" != q222 && "$ROLE" != mysql ]]; then die "$TASK only supports MySQL nodes"; fi
  require_root
  case "$ACTION" in preflight|execute) new_run ;; verify|rollback|archive) load_run ;; esac
  start_log "$ACTION"
  trap 'finish "$ACTION"' EXIT
  "$ACTION"
}
main "$@"