AiDOPWarehouse/scripts/s8/detection-retention-cleanup.sh

#!/usr/bin/env bash
# S8-DETECTION-LOG-RETENTION-1：TEMP_SCHED_* detection_log / rule_detection_state 治理脚本（dev/test only, aidopdev）。
#
# 严格范围：
#   - 仅 rule_code LIKE 'TEMP_SCHED_%' 的数据
#   - 默认 dry-run（只统计、不删除）；--apply 才真删
#   - 默认 --days=1，对早于 NOW - days 的 TEMP 行做 DELETE 候选
#   - 不动 ado_s8_exception
#   - 不动 demo rule 10/11/12 数据
#   - 不动非 TEMP_SCHED_% 的真实业务数据
#
# 用法：
#   bash scripts/s8/detection-retention-cleanup.sh                    # dry-run，默认 1 天
#   bash scripts/s8/detection-retention-cleanup.sh --days 7           # dry-run，7 天
#   bash scripts/s8/detection-retention-cleanup.sh --apply             # 真删，默认 1 天
#   bash scripts/s8/detection-retention-cleanup.sh --apply --days 0    # 真删全部 TEMP 历史（dev/test 验证用）
#
# 退出码：0 总是（只要查询/删除未抛异常）；致命错误 → 2。

set -uo pipefail

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
# shellcheck source=./s8-regression-common.sh
source "${SCRIPT_DIR}/s8-regression-common.sh"

APPLY=0
DAYS=1

while [[ $# -gt 0 ]]; do
  case "$1" in
    --apply) APPLY=1; shift ;;
    --days)  DAYS="${2:-1}"; shift 2 ;;
    -h|--help)
      sed -n '2,20p' "$0"
      exit 0 ;;
    *) echo "unknown arg: $1" >&2; exit 2 ;;
  esac
done

[[ "${DAYS}" =~ ^[0-9]+$ ]] || s8reg_die "--days must be a non-negative integer, got '${DAYS}'"

CUTOFF=$(mysql_run "SELECT DATE_SUB(NOW(), INTERVAL ${DAYS} DAY);")
NOW_TS=$(mysql_run "SELECT NOW();")
MODE=$([[ "${APPLY}" == "1" ]] && echo "APPLY (DELETE)" || echo "DRY-RUN (no delete)")

echo "============================================================"
echo "S8 detection retention cleanup"
echo "  DB         : ${DB_HOST}:${DB_PORT}/${DB_NAME}"
echo "  now        : ${NOW_TS}"
echo "  cutoff     : ${CUTOFF}  (--days=${DAYS})"
echo "  mode       : ${MODE}"
echo "  scope      : rule_code LIKE 'TEMP_SCHED_%'  (real business data NOT touched)"
echo "============================================================"

# ---------------------------------------------------------------------------
# 守恒前 snapshot
# ---------------------------------------------------------------------------
baseline_before=$(read_baseline)
demo_state_before=$(mysql_run "SELECT GROUP_CONCAT(CONCAT(id,':',enabled,':',IFNULL(paused_until,'NULL'),':',trigger_count_required,':',recover_count_required) ORDER BY id) FROM ado_s8_watch_rule WHERE id IN (10,11,12);")
temp_active_exc_before=$(mysql_run "SELECT COUNT(*) FROM ado_s8_exception WHERE source_rule_code LIKE 'TEMP_SCHED_%' AND is_deleted=0;")

echo
echo "---- BEFORE ----"
echo "  baseline                              = ${baseline_before}"
echo "  demo rule 10/11/12                    = ${demo_state_before}"
echo "  TEMP active exception (is_deleted=0)  = ${temp_active_exc_before}"

# ---------------------------------------------------------------------------
# detection_log 统计
# ---------------------------------------------------------------------------
log_total=$(mysql_run "SELECT COUNT(*) FROM ado_s8_detection_log;")
log_temp_total=$(mysql_run "SELECT COUNT(*) FROM ado_s8_detection_log WHERE rule_code LIKE 'TEMP_SCHED_%';")
log_temp_candidates=$(mysql_run "SELECT COUNT(*) FROM ado_s8_detection_log WHERE rule_code LIKE 'TEMP_SCHED_%' AND detected_at < '${CUTOFF}';")

echo
echo "---- detection_log ----"
echo "  total                = ${log_total}"
echo "  TEMP_SCHED_% total   = ${log_temp_total}"
echo "  TEMP candidates < cutoff = ${log_temp_candidates}"

if [[ "${log_temp_candidates}" != "0" ]]; then
  echo "  per-rule breakdown of candidates:"
  mysql_run "SELECT rule_code, COUNT(*) AS cnt, MIN(detected_at) AS earliest, MAX(detected_at) AS latest FROM ado_s8_detection_log WHERE rule_code LIKE 'TEMP_SCHED_%' AND detected_at < '${CUTOFF}' GROUP BY rule_code ORDER BY cnt DESC;" | sed 's/^/    /'
fi

# ---------------------------------------------------------------------------
# rule_detection_state 统计
# ---------------------------------------------------------------------------
state_total=$(mysql_run "SELECT COUNT(*) FROM ado_s8_rule_detection_state;")
state_temp_total=$(mysql_run "SELECT COUNT(*) FROM ado_s8_rule_detection_state WHERE rule_code LIKE 'TEMP_SCHED_%';")
state_temp_candidates=$(mysql_run "SELECT COUNT(*) FROM ado_s8_rule_detection_state WHERE rule_code LIKE 'TEMP_SCHED_%' AND last_seen_at < '${CUTOFF}';")

echo
echo "---- rule_detection_state ----"
echo "  total                = ${state_total}"
echo "  TEMP_SCHED_% total   = ${state_temp_total}"
echo "  TEMP candidates < cutoff = ${state_temp_candidates}"

if [[ "${state_temp_candidates}" != "0" ]]; then
  echo "  per-rule breakdown of candidates:"
  mysql_run "SELECT rule_code, dedup_key, last_seen_at FROM ado_s8_rule_detection_state WHERE rule_code LIKE 'TEMP_SCHED_%' AND last_seen_at < '${CUTOFF}' ORDER BY last_seen_at;" | sed 's/^/    /'
fi

# ---------------------------------------------------------------------------
# Apply
# ---------------------------------------------------------------------------
log_deleted=0
state_deleted=0
if [[ "${APPLY}" == "1" ]]; then
  echo
  echo "---- APPLY ----"
  log_deleted=$(mysql_run "DELETE FROM ado_s8_detection_log WHERE rule_code LIKE 'TEMP_SCHED_%' AND detected_at < '${CUTOFF}'; SELECT ROW_COUNT();")
  echo "  detection_log: deleted ${log_deleted} row(s)"
  state_deleted=$(mysql_run "DELETE FROM ado_s8_rule_detection_state WHERE rule_code LIKE 'TEMP_SCHED_%' AND last_seen_at < '${CUTOFF}'; SELECT ROW_COUNT();")
  echo "  rule_detection_state: deleted ${state_deleted} row(s)"

  if [[ "${log_deleted}" != "${log_temp_candidates}" ]]; then
    echo "  WARNING: detection_log delete count (${log_deleted}) != pre-count (${log_temp_candidates}) — possible concurrent insert"
  fi
  if [[ "${state_deleted}" != "${state_temp_candidates}" ]]; then
    echo "  WARNING: rule_detection_state delete count (${state_deleted}) != pre-count (${state_temp_candidates})"
  fi
else
  echo
  echo "---- DRY-RUN (no DELETE executed) ----"
  echo "  would-delete detection_log:        ${log_temp_candidates}"
  echo "  would-delete rule_detection_state: ${state_temp_candidates}"
  echo "  re-run with --apply to actually delete"
fi

# ---------------------------------------------------------------------------
# 守恒后 snapshot
# ---------------------------------------------------------------------------
baseline_after=$(read_baseline)
demo_state_after=$(mysql_run "SELECT GROUP_CONCAT(CONCAT(id,':',enabled,':',IFNULL(paused_until,'NULL'),':',trigger_count_required,':',recover_count_required) ORDER BY id) FROM ado_s8_watch_rule WHERE id IN (10,11,12);")
temp_active_exc_after=$(mysql_run "SELECT COUNT(*) FROM ado_s8_exception WHERE source_rule_code LIKE 'TEMP_SCHED_%' AND is_deleted=0;")

echo
echo "---- AFTER ----"
echo "  baseline                              = ${baseline_after}"
echo "  demo rule 10/11/12                    = ${demo_state_after}"
echo "  TEMP active exception (is_deleted=0)  = ${temp_active_exc_after}"

echo
echo "---- ASSERTIONS ----"
fail=0
if [[ "${baseline_before}" == "${baseline_after}" ]]; then
  echo "  PASS: baseline unchanged (${baseline_before})"
else
  echo "  FAIL: baseline drifted ${baseline_before} → ${baseline_after}"
  fail=$((fail+1))
fi
if [[ "${demo_state_before}" == "${demo_state_after}" ]]; then
  echo "  PASS: demo rule 10/11/12 conserved"
else
  echo "  FAIL: demo rule drift  before=${demo_state_before}  after=${demo_state_after}"
  fail=$((fail+1))
fi
if [[ "${temp_active_exc_before}" == "${temp_active_exc_after}" ]]; then
  echo "  PASS: TEMP active exception count unchanged (${temp_active_exc_before})"
else
  echo "  FAIL: TEMP active exception drift  before=${temp_active_exc_before}  after=${temp_active_exc_after}"
  fail=$((fail+1))
fi

echo
echo "============================================================"
if [[ "${APPLY}" == "1" ]]; then
  echo "Summary: APPLY mode — deleted log=${log_deleted} state=${state_deleted}; days=${DAYS} cutoff=${CUTOFF}"
else
  echo "Summary: DRY-RUN — would-delete log=${log_temp_candidates} state=${state_temp_candidates}; days=${DAYS} cutoff=${CUTOFF}"
fi
echo "============================================================"

exit $(( fail > 0 ? 1 : 0 ))