#!/usr/bin/env bash
# MGH Universal Database Doctor
# Audit-first, server-wide MySQL/MariaDB maintenance for cPanel/WHM servers.

VERSION="1.2.0-universal"
CONFIG="/etc/mgh-db-doctor.conf"
LOG_DIR="/var/log/mgh-db-doctor"
DUMP_DIR="/var/lib/mgh-db-doctor/prechange-dumps"
LOCK_FILE="/run/lock/mgh-db-doctor.lock"
MODE="audit"
APPLY="no"
ASSUME_YES="no"
RUN_ID="$(date +%Y%m%d-%H%M%S)-$$"
REPORT=""
DETAIL=""

# Counters
DB_FOUND=0; DB_SCANNED=0; DB_EMPTY=0; DB_SKIPPED=0; DB_FAILED=0
TABLES_CHECKED=0; TABLES_OK=0; TABLES_UNSUPPORTED=0; TABLES_BAD=0; TABLES_REPAIRED=0
TABLES_OPTIMIZED=0; TABLES_ANALYZED=0; TABLES_OPTIMIZE_SKIPPED=0
WP_DBS=0; WP_TRANSIENT_ROWS=0; WC_SESSION_ROWS=0
AS_COMPLETE_CANDIDATES=0; AS_CANCELED_CANDIDATES=0; AS_FAILED_CANDIDATES=0; AS_ORPHAN_LOGS=0
AS_ACTIONS_REMOVED=0; AS_LOGS_REMOVED=0; AS_SKIPPED_DATABASES=0
WP_REVISIONS_FOUND=0; WP_AUTODRAFTS_FOUND=0; WP_TRASH_POSTS_FOUND=0
WP_SPAM_COMMENTS_FOUND=0; WP_TRASH_COMMENTS_FOUND=0; WP_AUTOLOAD_BYTES=0
WARNINGS=0; ERRORS=0
DB_SERVER_FLAVOR="Unknown"; DB_SERVER_VERSION="Unknown"
DB_CLIENT=""; DB_CHECK=""; DB_DUMP=""; AUTH_MODE=""

# Conservative defaults, overridden by config.
MIN_FREE_GB=20
MIN_FREE_PERCENT=10
MAX_LOAD_PER_CPU=1.50
MAX_IOWAIT_PERCENT=30
MAX_DB_DUMP_MB=0
MAX_OPTIMIZE_TABLE_MB=1024
MIN_RECLAIM_MB=64
MIN_RECLAIM_PERCENT=15
QUERY_TIMEOUT=300
SLEEP_BETWEEN_TABLES=1
SLEEP_BETWEEN_ANALYZE=0
ANALYZE_BATCH_SIZE=25
ANALYZE_BATCH_FALLBACK=yes
ENABLE_WP_TRANSIENT_CLEANUP=yes
ENABLE_WC_SESSION_CLEANUP=yes
ENABLE_ACTION_SCHEDULER_CLEANUP=yes
ACTION_SCHEDULER_COMPLETE_DAYS=45
ACTION_SCHEDULER_CANCELED_DAYS=45
ACTION_SCHEDULER_FAILED_DAYS=90
MAX_ACTION_SCHEDULER_DELETE_ROWS=50000
ENABLE_ENGINE_SAFE_OPTIMIZE=yes
EXCLUDED_DB_REGEX='^(information_schema|performance_schema|mysql|sys|cphulkd|eximstats|modsec|roundcube|leechprotect)$'
SENSITIVE_DB_REGEX='(whmcs|billing|invoice|accounting|finance|crm)'

say(){ printf '%s\n' "$*"; }
log(){
  local level="$1"; shift
  local line="$(date -Is) [$level] $*"
  say "$line"
  [[ -n "$REPORT" ]] && printf '%s\n' "$line" >> "$REPORT"
  [[ "$level" == WARN ]] && WARNINGS=$((WARNINGS+1))
  [[ "$level" == ERROR ]] && ERRORS=$((ERRORS+1))
  return 0
}
run_timeout(){
  if command -v timeout >/dev/null 2>&1; then timeout --signal=TERM --kill-after=15 "$QUERY_TIMEOUT" "$@"; else "$@"; fi
}
trim(){ local x="$*"; x="${x#${x%%[![:space:]]*}}"; x="${x%${x##*[![:space:]]}}"; printf '%s' "$x"; }
quote_ident(){ local x="${1//\`/\`\`}"; printf '`%s`' "$x"; }
sql_lit(){ printf '%s' "$1" | sed "s/'/''/g"; }

usage(){ cat <<EOF
MGH Universal Database Doctor $VERSION

Commands:
  mgh-db-manager audit             Safe server-wide health + WordPress opportunity scan; makes no changes
  mgh-db-manager safe-run          Guarded server-wide repair/cleanup/optimization
  mgh-db-manager --version

The safe-run command processes every eligible non-empty customer database automatically.
EOF
}

load_config(){
  if [[ -r "$CONFIG" ]]; then
    # shellcheck disable=SC1090
    . "$CONFIG"
  fi
  mkdir -p "$LOG_DIR" "$DUMP_DIR" /run/lock 2>/dev/null
  chmod 700 "$DUMP_DIR" 2>/dev/null
  REPORT="$LOG_DIR/report-$RUN_ID.txt"
  DETAIL="$LOG_DIR/detail-$RUN_ID.txt"
  : > "$REPORT"; : > "$DETAIL"
}

parse_args(){
  # Support both:
  #   mgh-db-manager safe-run
  #   mgh-db-safe-run
  # The latter may be a wrapper or symlink.
  local invoked_as
  invoked_as="$(basename "$0")"
  local default_command="audit"
  [[ "$invoked_as" == "mgh-db-safe-run" ]] && default_command="safe-run"

  case "${1:-$default_command}" in
    audit) MODE="audit" ;;
    safe-run|maintain) MODE="safe-run"; APPLY="yes" ;;
    --version|-V) say "$VERSION"; exit 0 ;;
    --help|-h) usage; exit 0 ;;
    *) usage; exit 2 ;;
  esac

  # Shift only when an explicit command/option was supplied.
  [[ $# -gt 0 ]] && shift || true
  while [[ $# -gt 0 ]]; do
    case "$1" in --yes) ASSUME_YES=yes ;; *) log ERROR "Unknown option: $1"; exit 2 ;; esac
    shift
  done
}

acquire_lock(){
  exec 9>"$LOCK_FILE" || { log ERROR "Cannot create lock file"; exit 1; }
  if command -v flock >/dev/null 2>&1; then flock -n 9 || { log ERROR "Another database doctor run is already active"; exit 1; }; fi
}

select_clients(){
  if command -v mariadb >/dev/null 2>&1; then DB_CLIENT="mariadb"; elif command -v mysql >/dev/null 2>&1; then DB_CLIENT="mysql"; else log ERROR "Neither mysql nor mariadb client is installed"; return 1; fi
  if command -v mariadb-check >/dev/null 2>&1; then DB_CHECK="mariadb-check"; elif command -v mysqlcheck >/dev/null 2>&1; then DB_CHECK="mysqlcheck"; else DB_CHECK=""; fi
  if command -v mariadb-dump >/dev/null 2>&1; then DB_DUMP="mariadb-dump"; elif command -v mysqldump >/dev/null 2>&1; then DB_DUMP="mysqldump"; else DB_DUMP=""; fi
}

set_auth(){
  local candidates=("/root/.my.cnf" "/usr/local/cpanel/etc/my.cnf") f
  for f in "${candidates[@]}"; do
    [[ -r "$f" ]] || continue
    if "$DB_CLIENT" --defaults-extra-file="$f" --batch --skip-column-names -e 'SELECT 1' >/dev/null 2>&1; then AUTH_MODE="--defaults-extra-file=$f"; return 0; fi
  done
  if "$DB_CLIENT" --batch --skip-column-names -e 'SELECT 1' >/dev/null 2>&1; then AUTH_MODE=""; return 0; fi
  log ERROR "Cannot authenticate as database administrator. cPanel normally provides /root/.my.cnf"
  return 1
}

db_query(){
  # AUTH_MODE intentionally word-split into one option or empty.
  if [[ -n "$AUTH_MODE" ]]; then run_timeout "$DB_CLIENT" "$AUTH_MODE" --batch --skip-column-names --raw "$@"; else run_timeout "$DB_CLIENT" --batch --skip-column-names --raw "$@"; fi
}
check_command(){
  if [[ -z "$DB_CHECK" ]]; then return 127; fi
  if [[ -n "$AUTH_MODE" ]]; then run_timeout "$DB_CHECK" "$AUTH_MODE" "$@"; else run_timeout "$DB_CHECK" "$@"; fi
}
dump_command(){
  if [[ -z "$DB_DUMP" ]]; then return 127; fi
  if [[ -n "$AUTH_MODE" ]]; then "$DB_DUMP" "$AUTH_MODE" "$@"; else "$DB_DUMP" "$@"; fi
}

detect_environment(){
  [[ "$EUID" -eq 0 ]] || { log ERROR "Run this command as root"; return 1; }
  select_clients || return 1
  set_auth || return 1
  local ver comment
  ver="$(db_query -e 'SELECT VERSION()' 2>/dev/null | head -n1)"
  comment="$(db_query -e "SHOW VARIABLES LIKE 'version_comment'" 2>/dev/null | awk 'NR==1{print $2}')"
  DB_SERVER_VERSION="${ver:-Unknown}"
  case "${ver,,} ${comment,,}" in *mariadb*) DB_SERVER_FLAVOR="MariaDB" ;; *percona*) DB_SERVER_FLAVOR="Percona Server" ;; *) DB_SERVER_FLAVOR="MySQL-compatible" ;; esac
  log INFO "Version: $VERSION; mode: $MODE"
  log INFO "Database: $DB_SERVER_FLAVOR $DB_SERVER_VERSION; client: $DB_CLIENT"
  [[ -f /etc/redhat-release ]] && log INFO "OS: $(cat /etc/redhat-release)"
  if [[ -d /usr/local/cpanel ]]; then log INFO "cPanel/WHM detected"; else log WARN "cPanel/WHM not detected; database ownership will be inferred"; fi
  if command -v lswsctrl >/dev/null 2>&1 || [[ -x /usr/local/lsws/bin/lswsctrl ]]; then log INFO "LiteSpeed detected"; else log INFO "LiteSpeed not installed; continuing"; fi
  if command -v imunify360-agent >/dev/null 2>&1 || [[ -x /usr/bin/imunify360-agent ]]; then log INFO "Imunify360 detected"; else log INFO "Imunify360 not installed; continuing"; fi
  if [[ -d /usr/share/lve ]] || command -v lveinfo >/dev/null 2>&1; then log INFO "CloudLinux detected"; else log INFO "CloudLinux not installed; continuing"; fi
  if command -v jetbackup5api >/dev/null 2>&1 || [[ -d /usr/local/jetapps/usr/jetbackup5 ]]; then log INFO "JetBackup detected"; else log INFO "JetBackup not installed; local pre-change dumps will be used"; fi
  return 0
}

preflight(){
  local dbdir="/var/lib/mysql" line fs total used avail pct mount free_gb free_pct cpus load max iowait
  [[ -d "$dbdir" ]] || dbdir="/"
  line="$(df -Pk "$dbdir" 2>/dev/null | awk 'NR==2{print $1"|"$2"|"$3"|"$4"|"$5"|"$6}')"
  IFS='|' read -r fs total used avail pct mount <<< "$line"
  pct="${pct%%%}"
  [[ "$avail" =~ ^[0-9]+$ ]] || { log ERROR "Could not read free disk space for $dbdir"; return 1; }
  free_gb=$((avail/1024/1024)); free_pct=$((100-pct))
  log INFO "Database filesystem: ${fs:-unknown} mounted on ${mount:-unknown}; free=${free_gb} GB (${free_pct}%)"
  if (( free_gb < MIN_FREE_GB || free_pct < MIN_FREE_PERCENT )); then log ERROR "Insufficient free disk space"; return 1; fi
  cpus="$(getconf _NPROCESSORS_ONLN 2>/dev/null || echo 1)"; load="$(awk '{print $1}' /proc/loadavg 2>/dev/null)"
  max="$(awk -v c="$cpus" -v p="$MAX_LOAD_PER_CPU" 'BEGIN{printf "%.2f",c*p}')"
  if ! awk -v l="$load" -v m="$max" 'BEGIN{exit !(l<=m)}'; then log ERROR "Server load $load exceeds safe limit $max"; return 1; fi
  iowait=""
  if command -v mpstat >/dev/null 2>&1; then iowait="$(LC_ALL=C mpstat 1 1 2>/dev/null | awk '/Average:/ && ($2=="all" || $3=="all") {print $(NF-3); exit}')"; fi
  if [[ "$iowait" =~ ^[0-9]+([.][0-9]+)?$ ]] && ! awk -v i="$iowait" -v m="$MAX_IOWAIT_PERCENT" 'BEGIN{exit !(i<=m)}'; then log ERROR "I/O wait ${iowait}% exceeds safe limit"; return 1; fi
  if ! db_query -e 'SELECT 1' >/dev/null 2>&1; then log ERROR "Database service is not responding"; return 1; fi
  return 0
}

confirm_safe_run(){
  [[ "$MODE" == audit ]] && return 0
  say ""
  say "MGH SAFE-RUN will process ALL eligible databases on this server."
  say "It will never drop tables, delete posts/users/orders, purge binary logs, or force InnoDB recovery."
  say "It may repair confirmed MyISAM errors, remove only expired WordPress temporary records,"
  say "remove old terminal Action Scheduler records and orphan logs, analyze all InnoDB tables,"
  say "and optimize eligible MyISAM/Aria tables after a local compressed dump.\nInnoDB statistics are analyzed in conservative batches with automatic per-table fallback."
  say ""
  [[ "$ASSUME_YES" == yes ]] && return 0
  printf 'Type SAFE-RUN to continue: '
  local answer=""; IFS= read -r answer
  [[ "$answer" == "SAFE-RUN" ]] || { log WARN "Cancelled by administrator"; exit 0; }
}

is_excluded_db(){ local d="$1"; [[ "$d" =~ $EXCLUDED_DB_REGEX ]]; }
is_sensitive_db(){ local d="$1"; [[ "${d,,}" =~ $SENSITIVE_DB_REGEX ]]; }
owner_for_db(){
  local d="$1" o=""
  if [[ -r /var/cpanel/databases/dbindex.db ]]; then o="$(awk -F: -v q="$d" '$1==q{print $2;exit}' /var/cpanel/databases/dbindex.db 2>/dev/null)"; fi
  [[ -n "$o" ]] || o="${d%%_*}"
  printf '%s' "$o"
}

db_size_mb(){ db_query -e "SELECT ROUND(COALESCE(SUM(DATA_LENGTH+INDEX_LENGTH),0)/1024/1024) FROM information_schema.TABLES WHERE TABLE_SCHEMA='$(sql_lit "$1")'" 2>/dev/null | head -n1; }
db_table_count(){ db_query -e "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA='$(sql_lit "$1")' AND TABLE_TYPE='BASE TABLE'" 2>/dev/null | head -n1; }

wp_prefixes(){
  local d="$(sql_lit "$1")"
  db_query -e "SELECT DISTINCT LEFT(o.TABLE_NAME,LENGTH(o.TABLE_NAME)-7) FROM information_schema.TABLES o WHERE o.TABLE_SCHEMA='$d' AND o.TABLE_TYPE='BASE TABLE' AND RIGHT(o.TABLE_NAME,8)='_options' AND EXISTS(SELECT 1 FROM information_schema.TABLES p WHERE p.TABLE_SCHEMA=o.TABLE_SCHEMA AND p.TABLE_NAME=CONCAT(LEFT(o.TABLE_NAME,LENGTH(o.TABLE_NAME)-7),'posts'))" 2>/dev/null
}

make_dump(){
  local db="$1" size="$2" out free_mb required_mb
  [[ "$MODE" == safe-run ]] || return 0
  [[ -n "$DB_DUMP" ]] || { log WARN "No dump utility; skipping all changes for $db"; return 1; }
  [[ "$size" =~ ^[0-9]+$ ]] || size=0

  # MAX_DB_DUMP_MB=0 means no arbitrary database-size cap.
  if (( MAX_DB_DUMP_MB > 0 && size > MAX_DB_DUMP_MB )); then
    log WARN "Skipping changes for $db: ${size} MB exceeds configured dump limit ${MAX_DB_DUMP_MB} MB"
    return 1
  fi

  free_mb="$(df -Pm "$DUMP_DIR" 2>/dev/null | awk 'NR==2{print $4}')"
  [[ "$free_mb" =~ ^[0-9]+$ ]] || free_mb=0
  required_mb=$(( size + (MIN_FREE_GB * 1024) ))

  if (( free_mb < required_mb )); then
    log WARN "Skipping changes for $db: need approximately ${required_mb} MB free to preserve safety margin; available=${free_mb} MB"
    return 1
  fi

  out="$DUMP_DIR/${db}-${RUN_ID}.sql.gz"
  if (( size >= 4096 )); then
    log INFO "Creating streamed compressed pre-change dump for large database $db (${size} MB); no size-based skip"
  else
    log INFO "Creating local pre-change dump for $db"
  fi

  if dump_command --single-transaction --quick --routines --events --triggers --hex-blob "$db" 2>>"$DETAIL" | gzip -1 > "$out"; then
    if [[ -s "$out" ]]; then
      if gzip -t "$out" 2>>"$DETAIL"; then
        chmod 600 "$out"
        log INFO "Verified pre-change dump for $db: $out"
        return 0
      fi
      log WARN "Compressed dump verification failed for $db"
    fi
  fi
  rm -f "$out"
  log WARN "Dump failed; skipping every change for $db"
  return 1
}

check_tables(){
  local db="$1" count="$2" output rc ok unsupported bad
  if [[ -n "$DB_CHECK" ]]; then
    output="$(check_command --check --quick "$db" 2>&1)"; rc=$?
    printf '%s\n' "$output" >> "$DETAIL"
    ok="$(grep -cE '[[:space:]]OK$' <<< "$output" || true)"
    unsupported="$(grep -ciE "doesn't support check|does not support check|not supported for this engine" <<< "$output" || true)"
    [[ "$ok" =~ ^[0-9]+$ ]] || ok=0
    [[ "$unsupported" =~ ^[0-9]+$ ]] || unsupported=0
    bad=$((count-ok-unsupported)); (( bad < 0 )) && bad=0
    TABLES_CHECKED=$((TABLES_CHECKED+count))
    TABLES_OK=$((TABLES_OK+ok))
    TABLES_UNSUPPORTED=$((TABLES_UNSUPPORTED+unsupported))
    TABLES_BAD=$((TABLES_BAD+bad))
    if (( unsupported > 0 )); then log INFO "$db: $unsupported table check(s) unsupported by storage engine; not treated as damage"; fi
    if (( bad > 0 )); then
      log WARN "$db: $bad table result(s) require review"
      printf '%s\n' "$output" | grep -vE '[[:space:]]OK$|doesn.t support check|does not support check|not supported for this engine|^[[:space:]]*$' >> "$REPORT" || true
    fi
    return 0
  fi
  log WARN "No mysqlcheck/mariadb-check utility; using SQL CHECK TABLE per table"
  local t qd qt res
  while IFS= read -r t; do
    [[ -n "$t" ]] || continue
    qd="$(quote_ident "$db")"; qt="$(quote_ident "$t")"
    res="$(db_query -e "CHECK TABLE $qd.$qt QUICK" 2>&1)"; printf '%s\n' "$res" >> "$DETAIL"
    TABLES_CHECKED=$((TABLES_CHECKED+1))
    if grep -q $'status\tOK' <<< "$res"; then TABLES_OK=$((TABLES_OK+1))
    elif grep -qiE "doesn't support check|does not support check|not supported for this engine" <<< "$res"; then TABLES_UNSUPPORTED=$((TABLES_UNSUPPORTED+1))
    else TABLES_BAD=$((TABLES_BAD+1)); log WARN "Table check issue: $db.$t"; fi
  done < <(db_query -e "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='$(sql_lit "$db")' AND TABLE_TYPE='BASE TABLE'" 2>/dev/null)
  return 0
}

repair_myisam(){
  local db="$1" changed=0 t engine qd qt res
  while IFS=$'\t' read -r t engine; do
    [[ -n "$t" ]] || continue
    case "${engine^^}" in MYISAM|ARCHIVE|CSV) ;; *) continue ;; esac
    qd="$(quote_ident "$db")"; qt="$(quote_ident "$t")"
    res="$(db_query -e "CHECK TABLE $qd.$qt EXTENDED" 2>&1)"; printf '%s\n' "$res" >> "$DETAIL"
    if grep -Eq $'\t(error|warning)\t' <<< "$res"; then
      log WARN "Confirmed repairable error: $db.$t ($engine)"
      if db_query -e "REPAIR TABLE $qd.$qt" >>"$DETAIL" 2>&1; then TABLES_REPAIRED=$((TABLES_REPAIRED+1)); changed=1; else log WARN "Repair failed: $db.$t"; fi
    fi
  done < <(db_query -e "SELECT TABLE_NAME,COALESCE(ENGINE,'') FROM information_schema.TABLES WHERE TABLE_SCHEMA='$(sql_lit "$db")' AND TABLE_TYPE='BASE TABLE'" 2>/dev/null)
  return $changed
}

table_exists(){
  local db="$1" table="$2" n
  n="$(db_query -e "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA='$(sql_lit "$db")' AND TABLE_NAME='$(sql_lit "$table")'" 2>/dev/null | head -n1)"
  [[ "$n" == 1 ]]
}

wp_opportunity_audit(){
  local db="$1" prefix qd posts comments options actions logs n complete canceled failed orphan revisions autodrafts trashposts spam trashcomments autoload
  while IFS= read -r prefix; do
    [[ -n "$prefix" ]] || continue
    qd="$(quote_ident "$db")"
    posts="$(quote_ident "${prefix}posts")"; comments="$(quote_ident "${prefix}comments")"; options="$(quote_ident "${prefix}options")"
    revisions="$(db_query -e "SELECT COUNT(*) FROM $qd.$posts WHERE post_type='revision'" 2>/dev/null | head -n1)"; [[ "$revisions" =~ ^[0-9]+$ ]] || revisions=0
    autodrafts="$(db_query -e "SELECT COUNT(*) FROM $qd.$posts WHERE post_status='auto-draft'" 2>/dev/null | head -n1)"; [[ "$autodrafts" =~ ^[0-9]+$ ]] || autodrafts=0
    trashposts="$(db_query -e "SELECT COUNT(*) FROM $qd.$posts WHERE post_status='trash'" 2>/dev/null | head -n1)"; [[ "$trashposts" =~ ^[0-9]+$ ]] || trashposts=0
    spam=0; trashcomments=0
    if table_exists "$db" "${prefix}comments"; then
      spam="$(db_query -e "SELECT COUNT(*) FROM $qd.$comments WHERE comment_approved='spam'" 2>/dev/null | head -n1)"; [[ "$spam" =~ ^[0-9]+$ ]] || spam=0
      trashcomments="$(db_query -e "SELECT COUNT(*) FROM $qd.$comments WHERE comment_approved='trash'" 2>/dev/null | head -n1)"; [[ "$trashcomments" =~ ^[0-9]+$ ]] || trashcomments=0
    fi
    autoload="$(db_query -e "SELECT COALESCE(SUM(LENGTH(option_value)),0) FROM $qd.$options WHERE autoload IN ('yes','on','auto-on','auto')" 2>/dev/null | head -n1)"; [[ "$autoload" =~ ^[0-9]+$ ]] || autoload=0
    WP_REVISIONS_FOUND=$((WP_REVISIONS_FOUND+revisions)); WP_AUTODRAFTS_FOUND=$((WP_AUTODRAFTS_FOUND+autodrafts)); WP_TRASH_POSTS_FOUND=$((WP_TRASH_POSTS_FOUND+trashposts))
    WP_SPAM_COMMENTS_FOUND=$((WP_SPAM_COMMENTS_FOUND+spam)); WP_TRASH_COMMENTS_FOUND=$((WP_TRASH_COMMENTS_FOUND+trashcomments)); WP_AUTOLOAD_BYTES=$((WP_AUTOLOAD_BYTES+autoload))

    actions="${prefix}actionscheduler_actions"; logs="${prefix}actionscheduler_logs"
    if table_exists "$db" "$actions"; then
      complete="$(db_query -e "SELECT COUNT(*) FROM $qd.$(quote_ident "$actions") WHERE status='complete' AND scheduled_date_gmt < UTC_TIMESTAMP()-INTERVAL ${ACTION_SCHEDULER_COMPLETE_DAYS} DAY" 2>/dev/null | head -n1)"; [[ "$complete" =~ ^[0-9]+$ ]] || complete=0
      canceled="$(db_query -e "SELECT COUNT(*) FROM $qd.$(quote_ident "$actions") WHERE status='canceled' AND scheduled_date_gmt < UTC_TIMESTAMP()-INTERVAL ${ACTION_SCHEDULER_CANCELED_DAYS} DAY" 2>/dev/null | head -n1)"; [[ "$canceled" =~ ^[0-9]+$ ]] || canceled=0
      failed="$(db_query -e "SELECT COUNT(*) FROM $qd.$(quote_ident "$actions") WHERE status='failed' AND scheduled_date_gmt < UTC_TIMESTAMP()-INTERVAL ${ACTION_SCHEDULER_FAILED_DAYS} DAY" 2>/dev/null | head -n1)"; [[ "$failed" =~ ^[0-9]+$ ]] || failed=0
      orphan=0
      if table_exists "$db" "$logs"; then
        orphan="$(db_query -e "SELECT COUNT(*) FROM $qd.$(quote_ident "$logs") l LEFT JOIN $qd.$(quote_ident "$actions") a ON a.action_id=l.action_id WHERE a.action_id IS NULL" 2>/dev/null | head -n1)"; [[ "$orphan" =~ ^[0-9]+$ ]] || orphan=0
      fi
      AS_COMPLETE_CANDIDATES=$((AS_COMPLETE_CANDIDATES+complete)); AS_CANCELED_CANDIDATES=$((AS_CANCELED_CANDIDATES+canceled)); AS_FAILED_CANDIDATES=$((AS_FAILED_CANDIDATES+failed)); AS_ORPHAN_LOGS=$((AS_ORPHAN_LOGS+orphan))
      if (( complete+canceled+failed+orphan > 0 )); then log INFO "Action Scheduler opportunity $db/$prefix: complete=$complete canceled=$canceled failed=$failed orphan_logs=$orphan"; fi
    fi
    if (( revisions+autodrafts+trashposts+spam+trashcomments > 0 || autoload > 1048576 )); then
      log INFO "WP diagnostic $db/$prefix: revisions=$revisions auto_drafts=$autodrafts trash_posts=$trashposts spam_comments=$spam trash_comments=$trashcomments autoload_bytes=$autoload (diagnostic only)"
    fi
  done < <(wp_prefixes "$db")
}

cleanup_action_scheduler(){
  local db="$1" prefix qd actions logs qa ql complete canceled failed orphan total terminal_logs
  [[ "$ENABLE_ACTION_SCHEDULER_CLEANUP" == yes ]] || return 0
  while IFS= read -r prefix; do
    [[ -n "$prefix" ]] || continue
    actions="${prefix}actionscheduler_actions"; logs="${prefix}actionscheduler_logs"
    table_exists "$db" "$actions" || continue
    qd="$(quote_ident "$db")"; qa="$(quote_ident "$actions")"; ql="$(quote_ident "$logs")"
    complete="$(db_query -e "SELECT COUNT(*) FROM $qd.$qa WHERE status='complete' AND scheduled_date_gmt < UTC_TIMESTAMP()-INTERVAL ${ACTION_SCHEDULER_COMPLETE_DAYS} DAY" 2>/dev/null | head -n1)"; [[ "$complete" =~ ^[0-9]+$ ]] || complete=0
    canceled="$(db_query -e "SELECT COUNT(*) FROM $qd.$qa WHERE status='canceled' AND scheduled_date_gmt < UTC_TIMESTAMP()-INTERVAL ${ACTION_SCHEDULER_CANCELED_DAYS} DAY" 2>/dev/null | head -n1)"; [[ "$canceled" =~ ^[0-9]+$ ]] || canceled=0
    failed="$(db_query -e "SELECT COUNT(*) FROM $qd.$qa WHERE status='failed' AND scheduled_date_gmt < UTC_TIMESTAMP()-INTERVAL ${ACTION_SCHEDULER_FAILED_DAYS} DAY" 2>/dev/null | head -n1)"; [[ "$failed" =~ ^[0-9]+$ ]] || failed=0
    total=$((complete+canceled+failed))
    orphan=0; terminal_logs=0
    if table_exists "$db" "$logs"; then
      orphan="$(db_query -e "SELECT COUNT(*) FROM $qd.$ql l LEFT JOIN $qd.$qa a ON a.action_id=l.action_id WHERE a.action_id IS NULL" 2>/dev/null | head -n1)"; [[ "$orphan" =~ ^[0-9]+$ ]] || orphan=0
      terminal_logs="$(db_query -e "SELECT COUNT(*) FROM $qd.$ql l JOIN $qd.$qa a ON a.action_id=l.action_id WHERE (a.status='complete' AND a.scheduled_date_gmt<UTC_TIMESTAMP()-INTERVAL ${ACTION_SCHEDULER_COMPLETE_DAYS} DAY) OR (a.status='canceled' AND a.scheduled_date_gmt<UTC_TIMESTAMP()-INTERVAL ${ACTION_SCHEDULER_CANCELED_DAYS} DAY) OR (a.status='failed' AND a.scheduled_date_gmt<UTC_TIMESTAMP()-INTERVAL ${ACTION_SCHEDULER_FAILED_DAYS} DAY)" 2>/dev/null | head -n1)"; [[ "$terminal_logs" =~ ^[0-9]+$ ]] || terminal_logs=0
    fi
    if (( total + orphan > MAX_ACTION_SCHEDULER_DELETE_ROWS )); then
      AS_SKIPPED_DATABASES=$((AS_SKIPPED_DATABASES+1)); log WARN "Skipping Action Scheduler cleanup for $db/$prefix: $((total+orphan)) rows exceeds limit $MAX_ACTION_SCHEDULER_DELETE_ROWS"; continue
    fi
    if (( total == 0 && orphan == 0 )); then continue; fi
    log INFO "Cleaning old terminal Action Scheduler data in $db/$prefix: actions=$total terminal_logs=$terminal_logs orphan_logs=$orphan"
    local log_cleanup_ok=yes
    if table_exists "$db" "$logs"; then
      # Run each DELETE independently. This gives accurate error reporting and
      # avoids a multi-statement failure hiding which operation was rejected.
      if (( terminal_logs > 0 )); then
        if db_query -e "
          DELETE FROM $qd.$ql
          WHERE action_id IN (
            SELECT action_id FROM $qd.$qa
            WHERE (status='complete' AND scheduled_date_gmt<UTC_TIMESTAMP()-INTERVAL ${ACTION_SCHEDULER_COMPLETE_DAYS} DAY)
               OR (status='canceled' AND scheduled_date_gmt<UTC_TIMESTAMP()-INTERVAL ${ACTION_SCHEDULER_CANCELED_DAYS} DAY)
               OR (status='failed' AND scheduled_date_gmt<UTC_TIMESTAMP()-INTERVAL ${ACTION_SCHEDULER_FAILED_DAYS} DAY)
          )" >>"$DETAIL" 2>&1; then
          AS_LOGS_REMOVED=$((AS_LOGS_REMOVED+terminal_logs))
        else
          log_cleanup_ok=no
          log WARN "Terminal Action Scheduler log cleanup failed for $db/$prefix; actions retained"
        fi
      fi

      if (( orphan > 0 )); then
        if db_query -e "
          DELETE l FROM $qd.$ql AS l
          LEFT JOIN $qd.$qa AS a ON a.action_id=l.action_id
          WHERE a.action_id IS NULL" >>"$DETAIL" 2>&1; then
          AS_LOGS_REMOVED=$((AS_LOGS_REMOVED+orphan))
        else
          log WARN "Orphan Action Scheduler log cleanup failed for $db/$prefix"
        fi
      fi
    fi

    # Never delete terminal actions if their corresponding logs could not be
    # removed. This preserves referential integrity on stricter schemas.
    if (( total > 0 )) && [[ "$log_cleanup_ok" == yes ]]; then
      if db_query -e "
        DELETE FROM $qd.$qa
        WHERE (status='complete' AND scheduled_date_gmt<UTC_TIMESTAMP()-INTERVAL ${ACTION_SCHEDULER_COMPLETE_DAYS} DAY)
           OR (status='canceled' AND scheduled_date_gmt<UTC_TIMESTAMP()-INTERVAL ${ACTION_SCHEDULER_CANCELED_DAYS} DAY)
           OR (status='failed' AND scheduled_date_gmt<UTC_TIMESTAMP()-INTERVAL ${ACTION_SCHEDULER_FAILED_DAYS} DAY)" >>"$DETAIL" 2>&1; then
        AS_ACTIONS_REMOVED=$((AS_ACTIONS_REMOVED+total))
      else
        log WARN "Action Scheduler action cleanup failed for $db/$prefix"
      fi
    fi
  done < <(wp_prefixes "$db")
}

cleanup_wp(){
  local db="$1" prefix qd qo qs before after n
  while IFS= read -r prefix; do
    [[ -n "$prefix" ]] || continue
    qd="$(quote_ident "$db")"; qo="$(quote_ident "${prefix}options")"
    if [[ "$ENABLE_WP_TRANSIENT_CLEANUP" == yes ]]; then
      before="$(db_query -e "SELECT COUNT(*) FROM $qd.$qo WHERE (option_name LIKE '\\_transient\\_timeout\\_%' OR option_name LIKE '\\_site\\_transient\\_timeout\\_%') AND CAST(option_value AS UNSIGNED)<UNIX_TIMESTAMP()" 2>/dev/null | head -n1)"; [[ "$before" =~ ^[0-9]+$ ]] || before=0
      if (( before > 0 )); then
        db_query -e "DELETE v FROM $qd.$qo v JOIN $qd.$qo t ON t.option_name=CONCAT('_transient_timeout_',SUBSTRING(v.option_name,12)) WHERE v.option_name LIKE '\\_transient\\_%' AND v.option_name NOT LIKE '\\_transient\\_timeout\\_%' AND CAST(t.option_value AS UNSIGNED)<UNIX_TIMESTAMP(); DELETE v FROM $qd.$qo v JOIN $qd.$qo t ON t.option_name=CONCAT('_site_transient_timeout_',SUBSTRING(v.option_name,17)) WHERE v.option_name LIKE '\\_site\\_transient\\_%' AND v.option_name NOT LIKE '\\_site\\_transient\\_timeout\\_%' AND CAST(t.option_value AS UNSIGNED)<UNIX_TIMESTAMP(); DELETE FROM $qd.$qo WHERE (option_name LIKE '\\_transient\\_timeout\\_%' OR option_name LIKE '\\_site\\_transient\\_timeout\\_%') AND CAST(option_value AS UNSIGNED)<UNIX_TIMESTAMP();" >>"$DETAIL" 2>&1
        WP_TRANSIENT_ROWS=$((WP_TRANSIENT_ROWS+before))
      fi
    fi
    qs="$(quote_ident "${prefix}woocommerce_sessions")"
    n="$(db_query -e "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA='$(sql_lit "$db")' AND TABLE_NAME='$(sql_lit "${prefix}woocommerce_sessions")'" 2>/dev/null | head -n1)"
    if [[ "$ENABLE_WC_SESSION_CLEANUP" == yes && "$n" == 1 ]]; then
      before="$(db_query -e "SELECT COUNT(*) FROM $qd.$qs WHERE session_expiry<UNIX_TIMESTAMP()" 2>/dev/null | head -n1)"; [[ "$before" =~ ^[0-9]+$ ]] || before=0
      if (( before > 0 )); then db_query -e "DELETE FROM $qd.$qs WHERE session_expiry<UNIX_TIMESTAMP()" >>"$DETAIL" 2>&1 && WC_SESSION_ROWS=$((WC_SESSION_ROWS+before)); fi
    fi
  done < <(wp_prefixes "$db")
}

analyze_innodb_tables(){
  local db="$1" qd total processed=0 batch_no=0 batch_total i end t qt sql list
  local -a tables batch

  qd="$(quote_ident "$db")"
  mapfile -t tables < <(
    db_query -e "SELECT TABLE_NAME
                 FROM information_schema.TABLES
                 WHERE TABLE_SCHEMA='$(sql_lit "$db")'
                   AND TABLE_TYPE='BASE TABLE'
                   AND ENGINE='InnoDB'
                 ORDER BY TABLE_NAME" 2>/dev/null
  )

  total="${#tables[@]}"
  (( total > 0 )) || return 0

  [[ "$ANALYZE_BATCH_SIZE" =~ ^[0-9]+$ ]] || ANALYZE_BATCH_SIZE=25
  (( ANALYZE_BATCH_SIZE >= 1 )) || ANALYZE_BATCH_SIZE=25
  # Keep statements modest even if the configuration is edited incorrectly.
  (( ANALYZE_BATCH_SIZE <= 100 )) || ANALYZE_BATCH_SIZE=100

  batch_total=$(( (total + ANALYZE_BATCH_SIZE - 1) / ANALYZE_BATCH_SIZE ))
  log INFO "Analyzing $total InnoDB tables in $db using $batch_total batch(es), up to $ANALYZE_BATCH_SIZE tables each"

  for (( i=0; i<total; i+=ANALYZE_BATCH_SIZE )); do
    if ! preflight >/dev/null 2>&1; then
      local remaining=$((total-processed))
      TABLES_OPTIMIZE_SKIPPED=$((TABLES_OPTIMIZE_SKIPPED+remaining))
      log WARN "Load/disk safety threshold changed; skipping remaining $remaining ANALYZE operation(s) in $db"
      return 0
    fi

    end=$((i+ANALYZE_BATCH_SIZE))
    (( end > total )) && end=$total
    batch=( "${tables[@]:i:end-i}" )
    batch_no=$((batch_no+1))
    list=""

    for t in "${batch[@]}"; do
      qt="$(quote_ident "$t")"
      [[ -n "$list" ]] && list+=","
      list+="$qd.$qt"
    done

    log INFO "ANALYZE progress $db: batch $batch_no/$batch_total, tables $((i+1))-$end of $total"

    if db_query -e "ANALYZE TABLE $list" >>"$DETAIL" 2>&1; then
      TABLES_ANALYZED=$((TABLES_ANALYZED+${#batch[@]}))
      processed=$((processed+${#batch[@]}))
    else
      log WARN "Batch ANALYZE failed for $db batch $batch_no/$batch_total"
      if [[ "$ANALYZE_BATCH_FALLBACK" == yes ]]; then
        log INFO "Retrying the affected batch table-by-table to preserve complete coverage"
        for t in "${batch[@]}"; do
          qt="$(quote_ident "$t")"
          if db_query -e "ANALYZE TABLE $qd.$qt" >>"$DETAIL" 2>&1; then
            TABLES_ANALYZED=$((TABLES_ANALYZED+1))
          else
            TABLES_OPTIMIZE_SKIPPED=$((TABLES_OPTIMIZE_SKIPPED+1))
            log WARN "ANALYZE failed or timed out: $db.$t"
          fi
          processed=$((processed+1))
        done
      else
        TABLES_OPTIMIZE_SKIPPED=$((TABLES_OPTIMIZE_SKIPPED+${#batch[@]}))
        processed=$((processed+${#batch[@]}))
      fi
    fi

    if [[ "$SLEEP_BETWEEN_ANALYZE" != "0" ]]; then
      sleep "$SLEEP_BETWEEN_ANALYZE"
    fi
  done

  log INFO "Completed InnoDB statistics analysis for $db: $processed/$total table(s) processed"
}

optimize_tables(){
  local db="$1" t engine size free pct qd qt
  [[ "$ENABLE_ENGINE_SAFE_OPTIMIZE" == yes ]] || return 0
  while IFS=$'\t' read -r t engine size free pct; do
    [[ -n "$t" ]] || continue
    case "${engine^^}" in MYISAM|ARIA) ;; *) continue ;; esac
    if ! awk -v f="$free" -v fm="$MIN_RECLAIM_MB" -v p="$pct" -v pm="$MIN_RECLAIM_PERCENT" -v s="$size" -v sm="$MAX_OPTIMIZE_TABLE_MB" 'BEGIN{exit !((f>=fm)&&(p>=pm)&&(s<=sm)&&(f<=s))}'; then continue; fi
    if ! preflight >/dev/null 2>&1; then TABLES_OPTIMIZE_SKIPPED=$((TABLES_OPTIMIZE_SKIPPED+1)); log WARN "Load/disk changed; skipping optimize $db.$t"; continue; fi
    qd="$(quote_ident "$db")"; qt="$(quote_ident "$t")"
    log INFO "Optimizing engine-safe table $db.$t ($engine; ${size} MB; estimated reclaim ${free} MB)"
    if db_query -e "OPTIMIZE TABLE $qd.$qt" >>"$DETAIL" 2>&1; then TABLES_OPTIMIZED=$((TABLES_OPTIMIZED+1)); else TABLES_OPTIMIZE_SKIPPED=$((TABLES_OPTIMIZE_SKIPPED+1)); log WARN "Optimize failed or was refused: $db.$t"; fi
    sleep "$SLEEP_BETWEEN_TABLES"
  done < <(db_query -e "SELECT TABLE_NAME,COALESCE(ENGINE,''),ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2),ROUND(DATA_FREE/1024/1024,2),ROUND(IF(DATA_LENGTH+INDEX_LENGTH=0,0,DATA_FREE*100/(DATA_LENGTH+INDEX_LENGTH)),2) FROM information_schema.TABLES WHERE TABLE_SCHEMA='$(sql_lit "$db")' AND TABLE_TYPE='BASE TABLE'" 2>/dev/null)
}

process_db(){
  local db="$1" owner size count prefixes change_ok=no
  DB_SCANNED=$((DB_SCANNED+1))
  owner="$(owner_for_db "$db")"
  count="$(db_table_count "$db")"; size="$(db_size_mb "$db")"
  [[ "$count" =~ ^[0-9]+$ ]] || { log WARN "Cannot inventory $db; continuing"; DB_FAILED=$((DB_FAILED+1)); return 0; }
  [[ "$size" =~ ^[0-9]+$ ]] || size=0
  if (( count == 0 )); then DB_EMPTY=$((DB_EMPTY+1)); log INFO "[$DB_SCANNED/$DB_FOUND] $db owner=$owner: empty database, skipped"; return 0; fi
  log INFO "[$DB_SCANNED/$DB_FOUND] Auditing $db owner=$owner: $count tables, ${size} MB"
  check_tables "$db" "$count" || log WARN "Database checker returned a warning for $db; continuing"
  prefixes="$(wp_prefixes "$db")"
  if [[ -n "$prefixes" ]]; then
    WP_DBS=$((WP_DBS+1))
    wp_opportunity_audit "$db"
  fi
  [[ "$MODE" == audit ]] && return 0
  if is_sensitive_db "$db"; then DB_SKIPPED=$((DB_SKIPPED+1)); log WARN "Sensitive database name detected; maintenance skipped for $db"; return 0; fi
  if make_dump "$db" "$size"; then change_ok=yes; fi
  [[ "$change_ok" == yes ]] || return 0
  repair_myisam "$db"
  if [[ -n "$prefixes" ]]; then
    cleanup_wp "$db"
    cleanup_action_scheduler "$db"
  fi
  analyze_innodb_tables "$db"
  optimize_tables "$db"
  return 0
}

list_databases(){ db_query -e 'SHOW DATABASES' 2>/dev/null; }

summary(){
  cat | tee -a "$REPORT" <<EOF

============================================================
MGH UNIVERSAL DATABASE DOCTOR — FINAL SUMMARY
============================================================
Mode:                         $MODE
Database platform:            $DB_SERVER_FLAVOR $DB_SERVER_VERSION
Databases discovered:         $DB_FOUND
Databases scanned:            $DB_SCANNED
Empty databases skipped:      $DB_EMPTY
Sensitive databases skipped:  $DB_SKIPPED
Database-level failures:      $DB_FAILED
Tables checked:               $TABLES_CHECKED
Healthy table results:        $TABLES_OK
Unsupported engine checks:    $TABLES_UNSUPPORTED
Confirmed/review table issues: $TABLES_BAD
Tables repaired:              $TABLES_REPAIRED
InnoDB tables analyzed:       $TABLES_ANALYZED
Analyze batch size:            $ANALYZE_BATCH_SIZE
Tables optimized/rebuilt:     $TABLES_OPTIMIZED
Optimizations skipped:        $TABLES_OPTIMIZE_SKIPPED
WordPress databases detected: $WP_DBS
Expired WP records removed:   $WP_TRANSIENT_ROWS
Expired WC sessions removed:  $WC_SESSION_ROWS
AS complete candidates:       $AS_COMPLETE_CANDIDATES
AS canceled candidates:       $AS_CANCELED_CANDIDATES
AS failed candidates:         $AS_FAILED_CANDIDATES
AS orphan log candidates:     $AS_ORPHAN_LOGS
AS terminal actions removed:  $AS_ACTIONS_REMOVED
AS log rows removed:          $AS_LOGS_REMOVED
AS databases safety-skipped:  $AS_SKIPPED_DATABASES
WP revisions found (report):  $WP_REVISIONS_FOUND
WP auto-drafts found (report): $WP_AUTODRAFTS_FOUND
WP trash posts found (report): $WP_TRASH_POSTS_FOUND
WP spam comments (report):    $WP_SPAM_COMMENTS_FOUND
WP trash comments (report):   $WP_TRASH_COMMENTS_FOUND
WP autoload bytes (report):   $WP_AUTOLOAD_BYTES
Warnings:                     $WARNINGS
Errors:                       $ERRORS
Report:                       $REPORT
Detailed table log:           $DETAIL
Pre-change dumps:              $DUMP_DIR
============================================================
EOF
}

main(){
  parse_args "$@"
  load_config
  acquire_lock
  detect_environment || { summary; exit 1; }
  preflight || { summary; exit 1; }
  confirm_safe_run
  local all db
  all="$(list_databases)"
  while IFS= read -r db; do [[ -n "$db" ]] || continue; is_excluded_db "$db" && continue; DB_FOUND=$((DB_FOUND+1)); done <<< "$all"
  while IFS= read -r db; do
    [[ -n "$db" ]] || continue
    if is_excluded_db "$db"; then continue; fi
    process_db "$db" || { DB_FAILED=$((DB_FAILED+1)); log WARN "Unexpected error while processing $db; continuing"; }
  done <<< "$all"
  summary
  return 0
}

main "$@"