Vertica Health Check

VCheck_10.1.2.3_20261122_1130/
├── 00_run_meta.txt          environment and connection parameters (no password)
├── 00_view_columns.csv      system view column list (for version comparison)
├── 01_version.csv
├── 02_nodes.csv
├── ...                      40 CSV files in total
├── 37_config_params.csv
├── _check_status.csv        status and row count per check
├── _csv_integrity.csv       column-count consistency check
└── _errors.log              error messages for failed checks

VCheck_10.1.2.3_20261122_1130.tar.gz   <- send this file back

#!/usr/bin/env bash
# ==============================================================================
#  V_HCheck.sh  --  Vertica Health Check Data Collector
#
#  用途：在 Vertica 環境以 dbadmin 執行，匯出一組 CSV，供製作「Vertica 檢查報告」。
#  版本：2.0  (2026-11)
#  相容：Vertica 10.x / 11.x / 12.x / 23.x / 24.x / 25.x
#  需求：bash >= 3.2、vsql（client 即可）、awk、tar
#
#  範例：
#    ./V_HCheck.sh -h 10.1.2.3 -p 5433 -U dbadmin -d ACHI_DB
#    ./V_HCheck.sh -h 10.1.2.3 -d ACHI_DB -w 'MyPass' -D 30 -o ./out_20261122
#    ./V_HCheck.sh -d ACHI_DB --pwfile ~/.vcheck.pw
#
#  說明：
#    * 每一項檢查獨立執行；單一查詢失敗（例如該版本沒有某個 view）不會中斷，
#      失敗原因會寫進 _errors.log，最後在 _check_status.csv 列出。
#    * 建議一律使用 dbadmin 執行，否則部分 v_monitor 檢視表會因權限不足而空白。
#    * 執行完會自動打包成 VCheck_<host>_<日期>.tar.gz，把該檔回傳即可。
# ==============================================================================

set -u

SCRIPT_VER="2.0"
SELF="$(basename "$0")"

# ---------------------------- 預設參數 ---------------------------------------
HOST=""
PORT="5433"
DBUSER="dbadmin"
DB=""
PASS=""
PASS_SRC=""
PWFILE=""
DAYS="30"
OUTDIR=""
SSL=""

usage() {
cat <<EOF
用法: $SELF -h <host> [-p port] [-U user] -d <database> [-w 密碼 | --pwfile 檔] [-D 天數] [-o 目錄]

  -h, --host        Vertica 節點 IP / 主機名（必填）
  -p, --port        vsql 連接埠，預設 5433
  -U, --user        使用者，預設 dbadmin（請一律使用 dbadmin）
  -d, --database    資料庫名稱（必填）
  -w, --password    密碼；未提供則互動輸入（建議用互動輸入或 --pwfile）
      --pwfile      從檔案第一行讀取密碼
  -D, --days        觀察窗口天數，預設 30
  -o, --outdir      輸出目錄，預設 ./VCheck_<host>_<YYYYmmdd_HHMM>
      --ssl         連線啟用 SSL（等同 -s require）
  -V, --version     顯示版本
  -H, --help        顯示說明

範例:
  $SELF -h 10.1.2.3 -d ACHI_DB
  $SELF -h 10.1.2.3 -d ACHI_DB --pwfile ./pw.txt -D 30 -o ./out
EOF
}

# ---------------------------- 參數解析 ---------------------------------------
while [ $# -gt 0 ]; do
  case "$1" in
    -h|--host)     HOST="$2"; shift 2 ;;
    -p|--port)     PORT="$2"; shift 2 ;;
    -U|--user)     DBUSER="$2"; shift 2 ;;
    -d|--database) DB="$2"; shift 2 ;;
    -w|--password) PASS="$2"; PASS_SRC="command line (-w)"; shift 2 ;;
    --pwfile)      PWFILE="$2"; shift 2 ;;
    -D|--days)     DAYS="$2"; shift 2 ;;
    -o|--outdir)   OUTDIR="$2"; shift 2 ;;
    --ssl)         SSL="-s require"; shift ;;
    -V|--version)  echo "$SELF version $SCRIPT_VER"; exit 0 ;;
    -H|--help)     usage; exit 0 ;;
    *) echo "未知參數: $1" >&2; usage; exit 2 ;;
  esac
done

if [ -z "$HOST" ] || [ -z "$DB" ]; then
  echo "錯誤：-h/-d 為必填" >&2; usage; exit 2
fi

case "$DAYS" in
  ''|*[!0-9]*) echo "錯誤：-D 必須是整數天數" >&2; exit 2 ;;
esac

# 密碼來源
if [ -n "$PWFILE" ]; then
  if [ ! -r "$PWFILE" ]; then echo "錯誤：無法讀取 $PWFILE" >&2; exit 2; fi
  PASS="$(head -n 1 "$PWFILE")"
  PASS_SRC="pwfile ($PWFILE)"
elif [ -z "$PASS" ]; then
  if [ -n "${VCHECK_PASSWORD:-}" ]; then
    PASS="$VCHECK_PASSWORD"; PASS_SRC="環境變數 VCHECK_PASSWORD"
  else
    printf 'Vertica 密碼 (%s@%s): ' "$DBUSER" "$HOST" >&2
    stty -echo 2>/dev/null
    read -r PASS
    stty echo 2>/dev/null
    printf '\n' >&2
    PASS_SRC="interactive"
  fi
fi

if [ -z "$OUTDIR" ]; then
  OUTDIR="./VCheck_${HOST}_$(date +%Y%m%d_%H%M)"
fi
mkdir -p "$OUTDIR" || { echo "錯誤：無法建立輸出目錄 $OUTDIR" >&2; exit 1; }

# 前置檢查：找出 vsql（PATH 沒有時退回 /opt/vertica/bin）
if [ -n "${VSQL:-}" ] && [ -x "${VSQL}" ]; then
  VSQL_BIN="$VSQL"
elif command -v vsql >/dev/null 2>&1; then
  VSQL_BIN="$(command -v vsql)"
elif [ -x /opt/vertica/bin/vsql ]; then
  VSQL_BIN="/opt/vertica/bin/vsql"
else
  echo "錯誤：找不到 vsql。請確認已安裝 Vertica client 並在 PATH 中（或設定 VSQL 環境變數指向 vsql 路徑）。" >&2
  exit 1
fi

LOG="$OUTDIR/_errors.log"
STATUS="$OUTDIR/_check_status.csv"
META="$OUTDIR/00_run_meta.txt"

: > "$LOG"
printf 'file,status,rows,elapsed_sec,note,bad_rows\n' > "$STATUS"
: > "$OUTDIR/.integrity.tmp"

# ---------------------------- 連線參數 ---------------------------------------
CONN=(-h "$HOST" -p "$PORT" -U "$DBUSER" -d "$DB")
[ -n "$PASS" ] && CONN+=(-w "$PASS")
[ -n "$SSL" ]  && CONN+=($SSL)

VSQL_OPTS=(-A -F "$(printf '\001')" -q -X -P footer=off)

OK_CNT=0
FAIL_CNT=0
FAIL_LIST=""
BAD_LIST=""
START_TS="$(date '+%Y-%m-%d %H:%M:%S')"
START_EPOCH="$(date +%s)"

# 文字欄位淨化：把換行換成空白（避免單筆資料被拆成多列）。
# 逗號不需要處理：vsql 以 \001 分隔輸出，再由 awk 轉成標準 CSV（含引號轉義）。
# 用法： $(sancol message)
sancol() {
  printf "REPLACE(REPLACE(COALESCE(%s::VARCHAR,''), CHR(10), ' '), CHR(13), ' ')" "$1"
}

# ---------------------------- 核心：執行單一檢查 -----------------------------
# run_check <輸出檔名(不含.csv)> <說明> [oneline]   ← SQL 由 stdin 傳入
#   oneline：適用回傳多行文字的函式（如 GET_COMPLIANCE_STATUS），合併為單列
run_check() {
  local name="$1"; local title="$2"; local mode="${3:-}"
  local f="$OUTDIR/$name.csv"
  local raw="$OUTDIR/.raw.$name"
  local t0 t1 rows rc bad hdr

  t0="$(date +%s)"
  printf '\n---- [%s] %s\n' "$name" "$title" >> "$LOG"

  "$VSQL_BIN" "${CONN[@]}" "${VSQL_OPTS[@]}" -o "$raw" 2>> "$LOG"
  rc=$?

  if [ -f "$raw" ]; then
    # 1) 去掉 vsql footer、空行   2) 檢查每列欄位數（\001 分隔，值內不會出現）
    read -r rows bad hdr <<EOF
$(awk 'BEGIN{FS="\001"}
       /^\([0-9]+ rows?\)$/{next}
       NF==0{next}
       { if(!seen){h=NF; seen=1; rows=1; next }
         rows++; if (NF!=h) b++ }
       END{ printf "%d %d %d", rows+0, b+0, h+0 }' "$raw")
EOF

    if [ "$mode" = "oneline" ]; then
      awk 'BEGIN{FS="\001"}
           /^\([0-9]+ rows?\)$/{next} NF==0{next}
           { if(!seen){print; seen=1; next}
             printf "%s | ", $0 }' "$raw" > "$f.ol"
      # 收尾：補換行
      { cat "$f.ol"; echo; } > "$f"; rm -f "$f.ol"
      rows=1; bad=0
    else
      # \001 → RFC4180 CSV：內含逗號/引號的欄位加引號並將 " 覆寫為 ""
      awk 'BEGIN{FS="\001"; OFS=","}
           /^\([0-9]+ rows?\)$/{next} NF==0{next}
           { out=""
             for(i=1;i<=NF;i++){ v=$i
               if (v ~ /[,"]/) { gsub(/"/,"\"\"",v); v="\"" v "\"" }
               out = out (i>1 ? OFS : "") v }
             print out }' "$raw" > "$f"
    fi
    rm -f "$raw"
  else
    : > "$f"; rows=0; bad=0
  fi

  t1="$(date +%s)"
  if [ $rc -eq 0 ] && [ -s "$f" ]; then
    [ "$rows" -lt 0 ] && rows=0
    OK_CNT=$((OK_CNT+1))
    printf '%s,OK,%s,%s,,%s\n' "$name" "$rows" "$((t1-t0))" "$bad" >> "$STATUS"
    printf '  [ OK ] %-38s %8s rows %3ss  cols=%s%s\n' "$name" "$rows" "$((t1-t0))" "$hdr" \
           "$( [ "${bad:-0}" -gt 0 ] && echo "  ⚠ 欄位數異常 ${bad} 列" )"
    [ "${bad:-0}" -gt 0 ] && { BAD_LIST="$BAD_LIST $name"; echo "$name,$hdr,$rows,$bad" >> "$OUTDIR/.integrity.tmp"; }
  else
    # 失敗：清空輸出，避免半截資料被誤用；原因見 _errors.log
    : > "$f"
    FAIL_CNT=$((FAIL_CNT+1))
    FAIL_LIST="$FAIL_LIST $name"
    printf '%s,FAIL,0,%s,see _errors.log,0\n' "$name" "$((t1-t0))" >> "$STATUS"
    printf '  [FAIL] %-38s %8s       %ss  (見 _errors.log)\n' "$name" "-" "$((t1-t0))"
  fi
}

# ---------------------------- 執行環境紀錄 -----------------------------------
{
  echo "=== Vertica Health Check run meta ==="
  echo "script_version : $SCRIPT_VER"
  echo "run_at         : $START_TS"
  echo "run_by_host    : $(hostname 2>/dev/null)"
  echo "run_by_user    : $(whoami 2>/dev/null)"
  echo "target_host    : $HOST:$PORT"
  echo "database       : $DB"
  echo "db_user        : $DBUSER"
  echo "password_src   : ${PASS_SRC:-none}"
  echo "window_days    : $DAYS"
  echo "outdir         : $(cd "$OUTDIR" && pwd)"
  echo "vsql_version   : $("$VSQL_BIN" --version 2>&1 | head -n 1)"
  echo "os             : $(uname -srm 2>/dev/null)"
} > "$META"

echo "=============================================================="
echo " Vertica Health Check  v$SCRIPT_VER"
echo " 目標: $DBUSER@$HOST:$PORT / DB=$DB   窗口: 最近 $DAYS 天"
echo " 輸出: $(cd "$OUTDIR" && pwd)"
echo "=============================================================="

# ==============================================================================
# 00. Pre-flight：關鍵檢視表欄位清單（若後續查詢失敗，可從此檔查出正確欄名）
# ==============================================================================
# 系統檢視表不會出現在 v_catalog.view_columns，改以 SELECT * ... LIMIT 0 取得實際欄名。
# 用途：若後續某項檢查失敗，從這個檔就能查出該版本的正確欄位名。
VIEW_LIST="v_catalog.nodes v_catalog.tables v_catalog.projection_columns v_catalog.projections
v_catalog.license_audits v_catalog.constraint_columns v_monitor.system v_monitor.disk_storage
v_monitor.cpu_usage v_monitor.memory_usage v_monitor.io_usage v_monitor.network_usage
v_monitor.query_requests v_monitor.query_events v_monitor.error_messages
v_monitor.resource_rejections v_monitor.resource_rejection_details
v_monitor.resource_pool_status v_monitor.projection_refreshes v_monitor.projection_storage
v_monitor.delete_vectors v_monitor.partition_status v_monitor.storage_containers
v_monitor.tuning_recommendations v_monitor.configuration_parameters"

{
  printf 'view_name,columns\n'
  for v in $VIEW_LIST; do
    cols="$("$VSQL_BIN" "${CONN[@]}" -A -F '|' -q -X -P footer=off \
              -c "SELECT * FROM $v LIMIT 0" 2>> "$LOG" | head -n 1)"
    if [ -n "$cols" ]; then
      printf '%s,"%s"\n' "$v" "$cols"
    else
      printf '%s,"<<NOT FOUND OR NO PERMISSION>>"\n' "$v"
    fi
  done
} > "$OUTDIR/00_view_columns.csv"
printf '00_view_columns,OK,%s,0,,\n' "$(($(wc -l < "$OUTDIR/00_view_columns.csv") - 1))" >> "$STATUS"
printf '  [ OK ] %-38s %8s rows\n' "00_view_columns" "$(($(wc -l < "$OUTDIR/00_view_columns.csv") - 1))"
OK_CNT=$((OK_CNT+1))

# ==============================================================================
# A. 版本與授權
# ==============================================================================
run_check 01_version "A1 版本資訊" <<SQL
SELECT version() AS vertica_version;
SQL

run_check 02_nodes "A1/B1 節點清單與狀態" <<SQL
SELECT * FROM v_catalog.nodes ORDER BY node_name;
SQL

run_check 03_compliance "A2 授權合規狀態 (GET_COMPLIANCE_STATUS)" oneline <<SQL
SELECT GET_COMPLIANCE_STATUS() AS compliance_status;
SQL

run_check 04_license_audits "A3 授權用量歷史 (license_audits)" <<SQL
SELECT * FROM v_catalog.license_audits ORDER BY audit_start_timestamp;
SQL

# ==============================================================================
# B. 叢集健康度
# ==============================================================================
run_check 05_ksafety_epoch "B2 K-Safety 與 epoch (v_monitor.system)" <<SQL
SELECT * FROM v_monitor.system;
SQL

run_check 06_disk_storage "B3 各節點儲存空間 (disk_storage)" <<SQL
SELECT * FROM v_monitor.disk_storage ORDER BY node_name, storage_path;
SQL

# ==============================================================================
# C. 主機資源（時序 + 聚合）
# ==============================================================================
run_check 07_cpu_timeseries "C1 CPU 時序" <<SQL
SELECT node_name, end_time, average_cpu_usage_percent
FROM   v_monitor.cpu_usage
WHERE  end_time >= now() - INTERVAL '${DAYS} days'
ORDER  BY node_name, end_time;
SQL

run_check 08_cpu_summary "C1 CPU 彙總 (min/avg/p95/max)" <<SQL
WITH base AS (
  SELECT node_name,
         average_cpu_usage_percent AS v,
         PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY average_cpu_usage_percent)
           OVER (PARTITION BY node_name) AS p95
  FROM   v_monitor.cpu_usage
  WHERE  end_time >= now() - INTERVAL '${DAYS} days'
)
SELECT node_name,
       COUNT(*)              AS samples,
       ROUND(MIN(v),2)       AS min_cpu_pct,
       ROUND(AVG(v),2)       AS avg_cpu_pct,
       ROUND(MAX(p95),2)     AS p95_cpu_pct,
       ROUND(MAX(v),2)       AS max_cpu_pct
FROM   base
GROUP  BY node_name
ORDER  BY node_name;
SQL

run_check 09_memory_timeseries "C2 Memory 時序" <<SQL
SELECT node_name, end_time, average_memory_usage_percent
FROM   v_monitor.memory_usage
WHERE  end_time >= now() - INTERVAL '${DAYS} days'
ORDER  BY node_name, end_time;
SQL

run_check 10_memory_summary "C2 Memory 彙總 (min/avg/p95/max)" <<SQL
WITH base AS (
  SELECT node_name,
         average_memory_usage_percent AS v,
         PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY average_memory_usage_percent)
           OVER (PARTITION BY node_name) AS p95
  FROM   v_monitor.memory_usage
  WHERE  end_time >= now() - INTERVAL '${DAYS} days'
)
SELECT node_name,
       COUNT(*)           AS samples,
       ROUND(MIN(v),2)    AS min_mem_pct,
       ROUND(AVG(v),2)    AS avg_mem_pct,
       ROUND(MAX(p95),2)  AS p95_mem_pct,
       ROUND(MAX(v),2)    AS max_mem_pct
FROM   base
GROUP  BY node_name
ORDER  BY node_name;
SQL

run_check 11_io_timeseries "C3 磁碟 I/O 時序" <<SQL
SELECT node_name, end_time, read_kbytes_per_sec, written_kbytes_per_sec
FROM   v_monitor.io_usage
WHERE  end_time >= now() - INTERVAL '${DAYS} days'
ORDER  BY node_name, end_time;
SQL

run_check 12_io_summary "C3 磁碟 I/O 彙總" <<SQL
SELECT node_name,
       ROUND(MIN(read_kbytes_per_sec),2)    AS min_read_kbps,
       ROUND(AVG(read_kbytes_per_sec),2)    AS avg_read_kbps,
       ROUND(MAX(read_kbytes_per_sec),2)    AS max_read_kbps,
       ROUND(MIN(written_kbytes_per_sec),2) AS min_written_kbps,
       ROUND(AVG(written_kbytes_per_sec),2) AS avg_written_kbps,
       ROUND(MAX(written_kbytes_per_sec),2) AS max_written_kbps
FROM   v_monitor.io_usage
WHERE  end_time >= now() - INTERVAL '${DAYS} days'
GROUP  BY node_name
ORDER  BY node_name;
SQL

run_check 13_network_timeseries "C4 網路 I/O 時序" <<SQL
SELECT node_name, end_time, tx_kbytes_per_sec, rx_kbytes_per_sec
FROM   v_monitor.network_usage
WHERE  end_time >= now() - INTERVAL '${DAYS} days'
ORDER  BY node_name, end_time;
SQL

run_check 14_network_summary "C4 網路 I/O 彙總" <<SQL
SELECT node_name,
       ROUND(MIN(tx_kbytes_per_sec),2) AS min_tx_kbps,
       ROUND(AVG(tx_kbytes_per_sec),2) AS avg_tx_kbps,
       ROUND(MAX(tx_kbytes_per_sec),2) AS max_tx_kbps,
       ROUND(MIN(rx_kbytes_per_sec),2) AS min_rx_kbps,
       ROUND(AVG(rx_kbytes_per_sec),2) AS avg_rx_kbps,
       ROUND(MAX(rx_kbytes_per_sec),2) AS max_rx_kbps
FROM   v_monitor.network_usage
WHERE  end_time >= now() - INTERVAL '${DAYS} days'
GROUP  BY node_name
ORDER  BY node_name;
SQL

# ==============================================================================
# D. 資源池與拒絕
# ==============================================================================
run_check 15_resource_pool_status "D1 資源池狀態 (resource_pool_status)" <<SQL
SELECT * FROM v_monitor.resource_pool_status
ORDER  BY node_name, pool_name;
SQL

run_check 16_resource_rejections "D2 資源拒絕彙總 (resource_rejections)" <<SQL
SELECT * FROM v_monitor.resource_rejections
WHERE  last_rejected_timestamp >= now() - INTERVAL '${DAYS} days'
ORDER  BY last_rejected_timestamp DESC;
SQL

run_check 17_resource_rejection_details "D3 資源拒絕明細 (resource_rejection_details)" <<SQL
SELECT * FROM v_monitor.resource_rejection_details;
SQL

run_check 18_top_memory_queries "D4 最耗記憶體查詢 Top 200" <<SQL
SELECT node_name, user_name, session_id, request_id, request_type,
       request_duration_ms,
       memory_acquired_mb,
       start_timestamp, end_timestamp,
       $(sancol "LEFT(request,300)") AS query_text
FROM   v_monitor.query_requests
WHERE  start_timestamp >= now() - INTERVAL '${DAYS} days'
  AND  memory_acquired_mb IS NOT NULL
ORDER  BY memory_acquired_mb DESC
LIMIT  200;
SQL

run_check 19_rejection_window_queries "D4 資源拒絕時段前後 15 分鐘的查詢" <<SQL
SELECT q.node_name, r.pool_name, r.last_rejected_timestamp,
       q.user_name, q.session_id, q.request_id,
       q.request_duration_ms, q.memory_acquired_mb, q.start_timestamp,
       $(sancol "LEFT(q.request,300)") AS query_text
FROM   v_monitor.query_requests q
JOIN   v_monitor.resource_rejections r
       ON r.node_name = q.node_name
WHERE  ABS(DATEDIFF('minute', r.last_rejected_timestamp, q.start_timestamp)) <= 15
ORDER  BY q.memory_acquired_mb DESC
LIMIT  100;
SQL

# ==============================================================================
# E. 查詢效能
# ==============================================================================
run_check 20_query_duration_buckets "E1 查詢回應時間分布" <<SQL
SELECT duration_bucket,
       COUNT(*) AS query_count,
       ROUND(CAST(100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS FLOAT), 2) AS pct
FROM (
  SELECT CASE WHEN request_duration_ms < 1000 THEN '1) < 1 sec'
              WHEN request_duration_ms < 2000 THEN '2) 1 - 2 sec'
              WHEN request_duration_ms < 3000 THEN '3) 2 - 3 sec'
              WHEN request_duration_ms < 4000 THEN '4) 3 - 4 sec'
              WHEN request_duration_ms < 5000 THEN '5) 4 - 5 sec'
              ELSE '6) > 5 sec' END AS duration_bucket
  FROM   v_monitor.query_requests
  WHERE  start_timestamp >= now() - INTERVAL '${DAYS} days'
    AND  request_duration_ms IS NOT NULL
) b
GROUP  BY duration_bucket
ORDER  BY duration_bucket;
SQL

run_check 20b_query_window "E1 查詢資料涵蓋範圍（實際可取得的起訖時間）" <<SQL
SELECT MIN(start_timestamp) AS first_query_ts,
       MAX(start_timestamp) AS last_query_ts,
       COUNT(*)             AS query_cnt,
       MIN(request_duration_ms) AS min_ms,
       MAX(request_duration_ms) AS max_ms
FROM   v_monitor.query_requests
WHERE  start_timestamp >= now() - INTERVAL '${DAYS} days';
SQL

run_check 21_top_slow_queries "E2 最慢查詢 Top 500" <<SQL
SELECT node_name, user_name, session_id, request_id, request_type,
       request_duration_ms, memory_acquired_mb,
       start_timestamp, end_timestamp, error_count,
       $(sancol "LEFT(request,500)") AS query_text
FROM   v_monitor.query_requests
WHERE  start_timestamp >= now() - INTERVAL '${DAYS} days'
  AND  request_duration_ms IS NOT NULL
ORDER  BY request_duration_ms DESC
LIMIT  500;
SQL

run_check 22_failed_queries "E3 失敗查詢明細" <<SQL
SELECT node_name, user_name, session_id, request_id, request_type,
       request_duration_ms, memory_acquired_mb, error_count,
       start_timestamp, $(sancol "LEFT(request,500)") AS query_text
FROM   v_monitor.query_requests
WHERE  start_timestamp >= now() - INTERVAL '${DAYS} days'
  AND  NOT success
ORDER  BY start_timestamp DESC
LIMIT  2000;
SQL

run_check 23_query_events_summary "E4 查詢事件統計 (event_type)" <<SQL
SELECT event_type, COUNT(*) AS event_count
FROM   v_monitor.query_events
WHERE  event_timestamp >= now() - INTERVAL '${DAYS} days'
GROUP  BY event_type
ORDER  BY event_count DESC;
SQL

run_check 24_query_events_detail "E4 查詢事件明細" <<SQL
SELECT event_timestamp, node_name, session_id, transaction_id,
       event_type, $(sancol "LEFT(event_description,300)") AS event_description
FROM   v_monitor.query_events
WHERE  event_timestamp >= now() - INTERVAL '${DAYS} days'
ORDER  BY event_timestamp DESC
LIMIT  50000;
SQL

run_check 25_query_load_by_user "E5 各使用者/請求型態的負載" <<SQL
SELECT user_name, request_type,
       COUNT(*)                                   AS request_cnt,
       ROUND(SUM(request_duration_ms)/1000.0, 1)  AS total_sec,
       ROUND(AVG(request_duration_ms),1)          AS avg_ms,
       ROUND(MAX(memory_acquired_mb),1)           AS max_mem_mb
FROM   v_monitor.query_requests
WHERE  start_timestamp >= now() - INTERVAL '${DAYS} days'
GROUP  BY user_name, request_type
ORDER  BY request_cnt DESC;
SQL

# ==============================================================================
# F. 錯誤訊息
# ==============================================================================
run_check 26_error_severity "F1 錯誤訊息依嚴重度統計" <<SQL
SELECT error_level,
       COUNT(*)                     AS msg_cnt,
       COUNT(DISTINCT node_name)    AS node_cnt,
       MIN(event_timestamp)         AS first_ts,
       MAX(event_timestamp)         AS last_ts
FROM   v_monitor.error_messages
WHERE  event_timestamp >= now() - INTERVAL '${DAYS} days'
GROUP  BY error_level
ORDER  BY msg_cnt DESC;
SQL

run_check 27_error_message_groups "F2 錯誤訊息正規化分群 Top 100" <<SQL
SELECT message_pattern,
       COUNT(*)                  AS msg_cnt,
       COUNT(DISTINCT node_name) AS node_cnt,
       MIN(event_timestamp)      AS first_ts,
       MAX(event_timestamp)      AS last_ts
FROM (
  SELECT node_name, event_timestamp,
         LEFT(
           REGEXP_REPLACE(
             REGEXP_REPLACE(
               REGEXP_REPLACE(
                 REPLACE(REPLACE(LEFT(message, 200), CHR(10), ' '), CHR(13), ' '),
               '[A-Za-z0-9_]{8}-[A-Za-z0-9_]{4}-[A-Za-z0-9_]{4}-[A-Za-z0-9_]{4}-[A-Za-z0-9_]{12}', '<GUID>'),
             '''[^'']*''', '''?'''),
           '[0-9]+', '#'),
         180) AS message_pattern
  FROM   v_monitor.error_messages
  WHERE  event_timestamp >= now() - INTERVAL '${DAYS} days'
    AND  error_level IN ('ERROR','FATAL','PANIC')
    AND  message NOT LIKE 'A Mergeout operation is already in progress on%'
    AND  message NOT LIKE 'No projections found%'
) t
GROUP  BY message_pattern
ORDER  BY msg_cnt DESC
LIMIT  100;
SQL

run_check 28_error_objects "F3 錯誤訊息關聯物件 (schema.table)" <<SQL
SELECT REGEXP_SUBSTR(message, '[A-Za-z0-9_]+[.][A-Za-z0-9_]+') AS object_name,
       COUNT(*)             AS msg_cnt,
       MIN(event_timestamp) AS first_ts,
       MAX(event_timestamp) AS last_ts
FROM   v_monitor.error_messages
WHERE  event_timestamp >= now() - INTERVAL '${DAYS} days'
  AND  error_level IN ('ERROR','FATAL')
  AND  REGEXP_SUBSTR(message, '[A-Za-z0-9_]+[.][A-Za-z0-9_]+') IS NOT NULL
GROUP  BY object_name
ORDER  BY msg_cnt DESC
LIMIT  100;
SQL

# ==============================================================================
# G. 資料庫物件維護
# ==============================================================================
run_check 29_tables_without_pk "G1 無主鍵的表（依 schema 統計 + 總計）" <<SQL
WITH nopk AS (
  SELECT DISTINCT t.table_id, t.table_schema
  FROM   v_catalog.tables t
  WHERE  t.is_system_table = 'f'
    AND  NOT EXISTS (
           SELECT 1 FROM v_catalog.constraint_columns cc
           WHERE  cc.table_id = t.table_id
             AND  cc.constraint_type = 'p')
)
SELECT table_schema, COUNT(*) AS table_cnt
FROM   nopk GROUP BY table_schema
UNION ALL
SELECT '*** TOTAL ***' AS table_schema, COUNT(*) AS table_cnt FROM nopk
ORDER  BY 1;
SQL

run_check 30_projections_last_refresh "G2 逾 3 個月未 refresh 的投影" <<SQL
SELECT p.projection_schema,
       p.projection_name,
       p.anchor_table_name,
       p.is_up_to_date,
       p.has_statistics,
       MAX(pr.refresh_start) AS last_refresh_start
FROM   v_catalog.projections p
LEFT   JOIN v_monitor.projection_refreshes pr
       ON pr.projection_id = p.projection_id
GROUP  BY p.projection_schema, p.projection_name, p.anchor_table_name,
          p.is_up_to_date, p.has_statistics
HAVING MAX(pr.refresh_start) IS NULL
    OR MAX(pr.refresh_start) < now() - INTERVAL '3 months'
ORDER  BY CASE WHEN last_refresh_start IS NULL THEN 0 ELSE 1 END, last_refresh_start;
SQL

run_check 31_projection_stats_gaps "G3 統計資料 NONE/ROWCOUNT/逾期 的投影欄位" <<SQL
SELECT p.projection_schema, p.projection_name, p.anchor_table_name,
       pc.statistics_type,
       COUNT(*) AS column_cnt,
       SUM(CASE WHEN pc.statistics_updated_timestamp IS NULL
                  OR pc.statistics_updated_timestamp < now() - INTERVAL '${DAYS} days'
                THEN 1 ELSE 0 END) AS stale_column_cnt
FROM   v_catalog.projection_columns pc
JOIN   v_catalog.projections p ON p.projection_id = pc.projection_id
WHERE  pc.statistics_type IN ('NONE','ROWCOUNT')
GROUP  BY p.projection_schema, p.projection_name, p.anchor_table_name, pc.statistics_type
ORDER  BY stale_column_cnt DESC, column_cnt DESC
LIMIT  1000;
SQL

run_check 31b_projection_uptodate "G4 投影 up-to-date 狀態統計" <<SQL
SELECT is_up_to_date, is_segmented, COUNT(*) AS projection_cnt
FROM   v_catalog.projections
GROUP  BY is_up_to_date, is_segmented
ORDER  BY projection_cnt DESC;
SQL

run_check 32_delete_vectors "G5 未 purge 的 delete vector" <<SQL
SELECT * FROM v_monitor.delete_vectors;
SQL

run_check 33_projection_storage "G6 投影儲存空間" <<SQL
SELECT * FROM v_monitor.projection_storage;
SQL

run_check 34_partition_status "G7 分割區狀態" <<SQL
SELECT * FROM v_monitor.partition_status;
SQL

run_check 35_storage_containers "G7 ROS 容器分布" <<SQL
SELECT * FROM v_monitor.storage_containers;
SQL

run_check 36_tuning_recommendations "G8 調校建議（含 resegment）" <<SQL
SELECT * FROM v_monitor.tuning_recommendations
ORDER  BY tuning_cost DESC;
SQL

# ==============================================================================
# H. 環境快照
# ==============================================================================
run_check 37_config_params "H1 關鍵設定參數" <<SQL
SELECT node_name, parameter_name,
       $(sancol current_value) AS current_value,
       $(sancol database_value) AS database_value,
       $(sancol default_value) AS default_value,
       current_level, restart_level, is_mismatch,
       $(sancol groups) AS groups
FROM   v_monitor.configuration_parameters
ORDER  BY parameter_name, node_name;
SQL

# ==============================================================================
# 收尾：統計、打包
# ==============================================================================
END_EPOCH="$(date +%s)"
ELAPSED=$(( END_EPOCH - START_EPOCH ))

{
  echo ""
  echo "=== result ==="
  echo "finished_at    : $(date '+%Y-%m-%d %H:%M:%S')"
  echo "elapsed_sec    : $ELAPSED"
  echo "checks_ok      : $OK_CNT"
  echo "checks_failed  : $FAIL_CNT"
  echo "failed_list    :$FAIL_LIST"
} >> "$META"

echo "--------------------------------------------------------------"
echo " 完成：OK=$OK_CNT  FAIL=$FAIL_CNT  耗時 ${ELAPSED}s"
if [ -n "$FAIL_LIST" ]; then
  echo " 失敗項目：$FAIL_LIST"
  echo " 失敗原因請看：${LOG}（多為權限或該版本無此檢視表）"
fi

# ------------------------------------------------------------------
# CSV 完整性檢查：每列欄位數必須與表頭一致（抓出夾帶換行/逗號的漏網之魚）
# ------------------------------------------------------------------
INTEG="$OUTDIR/_csv_integrity.csv"
printf 'file,header_cols,total_rows,bad_rows\n' > "$INTEG"
cat "$OUTDIR/.integrity.tmp" >> "$INTEG" 2>/dev/null
rm -f "$OUTDIR/.integrity.tmp"
BAD_CNT="$(printf '%s' "$BAD_LIST" | wc -w)"
if [ "${BAD_CNT:-0}" -gt 0 ]; then
  echo " 注意：有 ${BAD_CNT} 個 CSV 出現欄位數不一致的列：${BAD_LIST}"
  echo "       （多半是文字欄位夾帶換行，請連同 _errors.log 一併回報）"
fi

# 打包（放在輸出目錄的上一層，檔名帶時間避免同日覆蓋）
ARCHIVE="$(cd "$OUTDIR/.." && pwd)/VCheck_${HOST}_$(date +%Y%m%d_%H%M).tar.gz"
if tar --exclude='.raw.*' -czf "$ARCHIVE" -C "$(dirname "$OUTDIR")" "$(basename "$OUTDIR")" 2>/dev/null; then
  echo " 打包完成：$ARCHIVE"
  echo "archive        : $ARCHIVE" >> "$META"
else
  echo " 打包失敗，請手動壓縮目錄：$(cd "$OUTDIR" && pwd)"
fi
echo "--------------------------------------------------------------"
echo "請將上面那個 .tar.gz 回傳即可（內含全部 CSV 與 _errors.log）"

# 全部失敗 → 回傳非 0，方便排程/自動化偵測
[ "$OK_CNT" -eq 0 ] && exit 1
exit 0