AiDOPWarehouse/scripts/s8/r6-detection-log-edge-regression.sh

#!/usr/bin/env bash
# R6-2-DETECTION-LOG-EDGE-CASES-1 edge regression script (dev/test only, aidopdev).
#
# Drives detection_log NO_HIT and EVALUATE_FAILED edge cases that the
# baseline three-class regression does not naturally exercise:
#   1. G01_TEST_TIMEOUT_NO_HIT — TIMEOUT rule that returns rows whose
#      due_at is in the future, so evaluator succeeds with hits.Count==0;
#   2. G01_TEST_TIMEOUT_FAILED — TIMEOUT rule with params_json missing
#      dueAtField, so evaluator throws S8RuleEvaluatorException
#      (reason=params_schema_invalid).
#
# Idempotent: rules are upserted with INSERT ... ON DUPLICATE KEY UPDATE
# semantics via SELECT-then-INSERT/UPDATE; rules are left enabled=0 after
# verification (kept in DB for audit replay). Detection_log entries are
# not cleaned. id=34/52/53 must remain unaffected.

set -euo pipefail

PROJECT_DIR="${PROJECT_DIR:-/home/yy968/work/New9S/AiDOPWarehouse}"
STORAGE_STATE="${PROJECT_DIR}/Web/tests/e2e/.auth/storage-state.json"
BACKEND_BASE="${BACKEND_BASE:-http://localhost:5005}"
TENANT_ID="${TENANT_ID:-1}"
FACTORY_ID="${FACTORY_ID:-1}"
DB_HOST="${DB_HOST:-123.60.180.165}"
DB_PORT="${DB_PORT:-3306}"
DB_NAME="${DB_NAME:-aidopdev}"
DB_USER="${DB_USER:-aidopremote}"
DB_PASS="${DB_PASS:-1234567890aiDOP#}"

NO_HIT_RULE='G01_TEST_TIMEOUT_NO_HIT'
FAILED_RULE='G01_TEST_TIMEOUT_FAILED'

NO_HIT_PARAMS='{"dueAtField":"due_at","statusField":"status","completedStates":["CLOSED","DONE","COMPLETED"],"objectCodeField":"related_object_code","objectIdField":"source_object_id","graceMinutes":0,"exceptionTypeCode":"DELIVERY_DELAY"}'
NO_HIT_EXPR="SELECT 'ORDER-NO-HIT-01' AS related_object_code, 'ORDER-NO-HIT-01' AS source_object_id, DATE_ADD(NOW(), INTERVAL 1 HOUR) AS due_at, 'PENDING' AS status"

# FAILED rule: deliberately omit dueAtField so evaluator throws params_schema_invalid.
FAILED_PARAMS='{"statusField":"status","completedStates":["CLOSED","DONE","COMPLETED"],"objectCodeField":"related_object_code","objectIdField":"source_object_id","graceMinutes":0,"exceptionTypeCode":"DELIVERY_DELAY"}'
FAILED_EXPR="SELECT 'ORDER-FAILED-01' AS related_object_code, 'ORDER-FAILED-01' AS source_object_id, DATE_SUB(NOW(), INTERVAL 1 HOUR) AS due_at, 'PENDING' AS status"

fail() { echo "FAIL: $*" >&2; exit 1; }
ok()   { echo "OK: $*"; }

[[ "${DB_NAME}" == "aidopdev" ]] || fail "DB_NAME must be aidopdev, got ${DB_NAME}"
[[ -f "${STORAGE_STATE}" ]] || fail "storage state not found: ${STORAGE_STATE}"

AT="$(python3 -c "
import json,sys
d=json.load(open(sys.argv[1]))
for kv in d['origins'][0]['localStorage']:
    if kv['name']=='admin.net:access-token':
        print(kv['value'].strip('\"')); break
" "${STORAGE_STATE}")"
XAT="$(python3 -c "
import json,sys
d=json.load(open(sys.argv[1]))
for kv in d['origins'][0]['localStorage']:
    if kv['name']=='admin.net:x-access-token':
        print(kv['value'].strip('\"')); break
" "${STORAGE_STATE}")"
[[ -n "${AT}" && "${AT}" != "null" ]] || fail "access-token missing in storage-state"
[[ -n "${XAT}" && "${XAT}" != "null" ]] || fail "x-access-token missing in storage-state"

mysql_run() {
  MYSQL_PWD="${DB_PASS}" mysql -h "${DB_HOST}" -P "${DB_PORT}" -u "${DB_USER}" "${DB_NAME}" \
    --default-character-set=utf8mb4 --connect-timeout=8 -N -B -e "$1" 2>/dev/null
}

run_once() {
  curl -fsS --max-time 30 -X POST \
    -H "Authorization: Bearer ${AT}" -H "X-Access-Token: ${XAT}" \
    "${BACKEND_BASE}/api/aidop/s8/watch-debug/run-once?tenantId=${TENANT_ID}&factoryId=${FACTORY_ID}"
}

# Idempotent upsert by rule_code: ensure enabled=1 with the desired expression+params for this run.
upsert_rule() {
  local code="$1"; local expr="$2"; local params="$3"
  local exists
  exists=$(mysql_run "SELECT COUNT(*) FROM ado_s8_watch_rule WHERE rule_code='${code}' AND tenant_id=${TENANT_ID} AND factory_id=${FACTORY_ID};")
  if [[ "${exists}" == "0" ]]; then
    mysql_run "INSERT INTO ado_s8_watch_rule
      (tenant_id, factory_id, rule_code, scene_code, data_source_id, watch_object_type, expression, severity, poll_interval_seconds, enabled, created_at, rule_type, source_object_type, params_json)
      VALUES (${TENANT_ID}, ${FACTORY_ID}, '${code}', 'S1S7_DELIVERY', 1, 'ORDER', \"${expr}\", 'HIGH', 60, 1, NOW(), 'TIMEOUT', 'ORDER', '${params}');" >/dev/null
    ok "upsert_rule INSERT ${code}"
  else
    mysql_run "UPDATE ado_s8_watch_rule SET expression=\"${expr}\", params_json='${params}', enabled=1, updated_at=NOW() WHERE rule_code='${code}' AND tenant_id=${TENANT_ID} AND factory_id=${FACTORY_ID};" >/dev/null
    ok "upsert_rule UPDATE ${code}"
  fi
}

baseline=$(mysql_run "SELECT COUNT(*) FROM ado_s8_exception_type WHERE tenant_id=0 AND factory_id=0;")
[[ "${baseline}" == "13" ]] || fail "baseline regression: expected 13, got ${baseline}"
ok "baseline = 13"

# Pre-condition: id=34/52/53 must be the only active samples and not recovered.
oor_active=$(mysql_run "SELECT COUNT(*) FROM ado_s8_exception WHERE source_rule_code='G01_TEST_WATCH' AND status<>'CLOSED' AND is_deleted=0;")
to_active=$(mysql_run  "SELECT COUNT(*) FROM ado_s8_exception WHERE source_rule_code='G01_TEST_TIMEOUT' AND status<>'CLOSED' AND is_deleted=0;")
sh_active=$(mysql_run  "SELECT COUNT(*) FROM ado_s8_exception WHERE source_rule_code='G01_TEST_SHORTAGE' AND status<>'CLOSED' AND is_deleted=0;")
[[ "${oor_active}" == "1" ]] || fail "pre-OUT_OF_RANGE active count expected 1, got ${oor_active}"
[[ "${to_active}"  == "1" ]] || fail "pre-TIMEOUT active count expected 1, got ${to_active}"
[[ "${sh_active}"  == "1" ]] || fail "pre-SHORTAGE active count expected 1, got ${sh_active}"
ok "pre-active counts: OUT_OF_RANGE=1 / TIMEOUT=1 / SHORTAGE=1"

upsert_rule "${NO_HIT_RULE}"  "${NO_HIT_EXPR}"  "${NO_HIT_PARAMS}"
upsert_rule "${FAILED_RULE}" "${FAILED_EXPR}" "${FAILED_PARAMS}"

# Marker timestamp so subsequent log queries scope to this run only.
marker=$(mysql_run "SELECT NOW();")
sleep 1

resp="$(run_once)"
count=$(printf '%s' "${resp}" | python3 -c "import json,sys;print(json.load(sys.stdin).get('count',0))")
ok "run-once HTTP 200, count=${count}"

# 1. NO_HIT log present, no exception created.
nohit_logs=$(mysql_run "SELECT COUNT(*) FROM ado_s8_detection_log WHERE rule_code='${NO_HIT_RULE}' AND detect_result='NO_HIT' AND detected_at >= '${marker}';")
[[ "${nohit_logs}" -ge 1 ]] || fail "expected at least 1 NO_HIT log for ${NO_HIT_RULE}, got ${nohit_logs}"
nohit_other=$(mysql_run "SELECT COUNT(*) FROM ado_s8_detection_log WHERE rule_code='${NO_HIT_RULE}' AND detect_result IN ('CREATED','RECOVERED','REFRESHED') AND detected_at >= '${marker}';")
[[ "${nohit_other}" == "0" ]] || fail "${NO_HIT_RULE} should not produce CREATED/RECOVERED/REFRESHED in this run, got ${nohit_other}"
nohit_excs=$(mysql_run "SELECT COUNT(*) FROM ado_s8_exception WHERE source_rule_code='${NO_HIT_RULE}' AND is_deleted=0;")
[[ "${nohit_excs}" == "0" ]] || fail "${NO_HIT_RULE} must not create exceptions, got ${nohit_excs}"
ok "NO_HIT log present (${nohit_logs}) / no CREATED/REFRESHED/RECOVERED / no exception"

# 2. EVALUATE_FAILED log present with reason+message, no exception created.
failed_logs=$(mysql_run "SELECT COUNT(*) FROM ado_s8_detection_log WHERE rule_code='${FAILED_RULE}' AND detect_result='EVALUATE_FAILED' AND detected_at >= '${marker}';")
[[ "${failed_logs}" -ge 1 ]] || fail "expected at least 1 EVALUATE_FAILED log for ${FAILED_RULE}, got ${failed_logs}"
failed_reason=$(mysql_run "SELECT failure_reason FROM ado_s8_detection_log WHERE rule_code='${FAILED_RULE}' AND detect_result='EVALUATE_FAILED' AND detected_at >= '${marker}' ORDER BY id DESC LIMIT 1;")
[[ -n "${failed_reason}" && "${failed_reason}" != "NULL" ]] || fail "${FAILED_RULE} EVALUATE_FAILED missing failure_reason"
failed_msg=$(mysql_run "SELECT CHAR_LENGTH(IFNULL(failure_message,'')) FROM ado_s8_detection_log WHERE rule_code='${FAILED_RULE}' AND detect_result='EVALUATE_FAILED' AND detected_at >= '${marker}' ORDER BY id DESC LIMIT 1;")
[[ "${failed_msg}" -gt 0 ]] || fail "${FAILED_RULE} EVALUATE_FAILED missing failure_message"
failed_excs=$(mysql_run "SELECT COUNT(*) FROM ado_s8_exception WHERE source_rule_code='${FAILED_RULE}' AND is_deleted=0;")
[[ "${failed_excs}" == "0" ]] || fail "${FAILED_RULE} must not create exceptions, got ${failed_excs}"
ok "EVALUATE_FAILED log present (${failed_logs}) / failure_reason='${failed_reason}' / message non-empty / no exception"

# 3. Main three-class samples must still be REFRESHED in this run, and untouched (not recovered).
for code in G01_TEST_WATCH G01_TEST_TIMEOUT G01_TEST_SHORTAGE; do
  cnt=$(mysql_run "SELECT COUNT(*) FROM ado_s8_detection_log WHERE rule_code='${code}' AND detect_result='REFRESHED' AND detected_at >= '${marker}';")
  [[ "${cnt}" -ge 1 ]] || fail "${code}: expected REFRESHED log in this run, got ${cnt}"
done
ok "main three-class samples each produced REFRESHED in this run (not blocked by FAILED)"

main_recovered=$(mysql_run "SELECT COUNT(*) FROM ado_s8_exception WHERE id IN (34,52,53) AND recovered_at IS NOT NULL;")
[[ "${main_recovered}" == "0" ]] || fail "id=34/52/53 must NOT be marked recovered, got ${main_recovered}"
ok "id=34/52/53 recovered_at remain NULL"

main_status=$(mysql_run "SELECT GROUP_CONCAT(CONCAT(id,':',status) ORDER BY id) FROM ado_s8_exception WHERE id IN (34,52,53);")
[[ "${main_status}" == "34:IN_PROGRESS,52:NEW,53:NEW" ]] || fail "id=34/52/53 status drift: ${main_status}"
ok "id=34/52/53 status unchanged: ${main_status}"

# Cleanup: disable the two edge rules but keep them in DB for audit replay.
mysql_run "UPDATE ado_s8_watch_rule SET enabled=0, updated_at=NOW() WHERE rule_code IN ('${NO_HIT_RULE}','${FAILED_RULE}') AND tenant_id=${TENANT_ID} AND factory_id=${FACTORY_ID};" >/dev/null
ok "disabled edge rules ${NO_HIT_RULE} / ${FAILED_RULE}"

ok "R6 detection_log edge regression PASSED"