cma-management/backend/scripts/_archive/verify_bep_threshold_fix.snapshot-20260908.py

"""BEP保本KPI阈值+现金口径修复验收脚本 — fin-fix-bep-threshold-20260908

验收：
1. kpi_definitions 457/458 阈值与财务口径一致（COVER: 100/85/85；GAP: 0/15/15），456 保持 NULL
2. 现金口径2条定义 F_BEP_REVENUE_CASH/F_BEP_COVER_CASH 存在（无阈值/100/85/85）
3. 2026-07 派生5条落库：账面 169.9/66.9/33.1 + 现金 121.0/93.9（±1%财务权威 121.4/93.5）
4. 告警闭环：575/576 = red pending（66.9<85, 33.1>15）；577 = yellow pending（93.9>=85）
   旧黄色 573/574 已 resolved
5. bep-card 端点：book.cover_level=red, cash.cover_level=yellow, derived 5 条

用法: venv/bin/python3 scripts/verify_bep_threshold_fix.py  （需后端8010在线）

⚠️ 历史快照守卫（2026-09-12 加固，任务单 concurrent-duplicate-session-and-stale-verify-script-20260912）：
   本文件**不是活检查**，是 fin-fix-bep-threshold-20260908 的验收快照，硬编码
   period='2026-07' 期望值 169.9/66.9/33.1 与告警自增 ID 573~577。期值随派生器滚动
   （2026-07 现为 170.2/66.7/33.3，最新期已滚动到 2026-08）、告警 ID 已变为 596~598，
   且脚本所用的 cma_user 账号已按凭据治理退役 → 直接复跑必然报错/FAIL，属"快照过期"，
   **不代表本轮回归**。现行活检查 = ../verify_bep_threshold_fix.py（按最新派生期动态断言 + 只读凭据）。
   原版逐字留存于 git（HEAD:backend/scripts/verify_bep_threshold_fix.py）。
   确需复跑历史行为：FORCE_SNAPSHOT=1 python3 scripts/_archive/verify_bep_threshold_fix.snapshot-20260908.py
"""
import json
import os
import sys
import urllib.request

import pymysql

# 2026-09-14 凭据面收口（cma-defaultcred-code-fix）：原快照里的硬编码默认账号/口令改为同源加载，
# 快照的期/阈值断言一字未动（历史原文仍在 git 历史里）。
# ---- 管理员凭据同源加载（2026-09-14 cma-defaultcred-code-fix）----
# 生产凭据只存 600 密钥文件；禁止在脚本/文档/日志里硬编码口令。
# 顺序：$CMA_ADMIN_ENV → wecom-ops 主副本（ops 轮换落点，权威） → 本 profile 副本。
def _admin_creds():
    """返回 (user, password, 来源文件)；未找到 → 显式报错退出（不静默退回默认口令）"""
    for p in (os.environ.get("CMA_ADMIN_ENV"),
              "/root/.hermes/profiles/wecom-ops/.secrets/cma-admin.env",
              "/root/.hermes/profiles/wecom-fullstack/.secrets/cma-admin.env"):
        if p and os.path.isfile(p):
            kv = {}
            for line in open(p, encoding="utf-8"):
                line = line.strip()
                if "=" in line and not line.startswith("#"):
                    k, v = line.split("=", 1)
                    kv[k.strip()] = v.strip().strip('"').strip("'")
            if kv.get("CMA_ADMIN_USER") and kv.get("CMA_ADMIN_PASS"):
                return kv["CMA_ADMIN_USER"], kv["CMA_ADMIN_PASS"], p
    raise SystemExit("[错误] 未找到 CMA 管理员凭据：请设 $CMA_ADMIN_ENV，或准备 600 权限密钥文件 "
                     ".secrets/cma-admin.env（键名 CMA_ADMIN_USER / CMA_ADMIN_PASS）")


ADMIN_USER, ADMIN_PASS, CRED_SRC = _admin_creds()
print(f"[creds] 来源={CRED_SRC} 账号={ADMIN_USER}（口令不回显）")

if os.environ.get("FORCE_SNAPSHOT") != "1":
    print("⛔ 这是 20260908 的历史验收快照，不是活检查；复跑结果不代表当前回归。")
    print("   现行活检查：python3 backend/scripts/verify_bep_threshold_fix.py")
    print("   历史行为复跑：FORCE_SNAPSHOT=1 python3 scripts/_archive/verify_bep_threshold_fix.snapshot-20260908.py")
    sys.exit(2)

BASE = "http://127.0.0.1:8010"

passed = []


def check(name, cond, detail=""):
    if cond:
        passed.append(name)
        print(f"  PASS  {name}")
    else:
        print(f"  FAIL  {name}  {detail}")
        sys.exit(1)


def _db_cfg():
    """从 backend/.env 读生产 DB 凭据（root@localhost 走 auth_socket，pymysql 直连须用应用账号 cma_user）"""
    env = {}
    env_path = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), ".env")
    if os.path.exists(env_path):
        for line in open(env_path):
            line = line.strip()
            if line and "=" in line and not line.startswith("#"):
                k, v = line.split("=", 1)
                env[k] = v.strip().strip('"').strip("'")
    return dict(
        host=env.get("CMA_DB_HOST", "127.0.0.1"),
        port=int(env.get("CMA_DB_PORT", "3306")),
        user=env.get("CMA_DB_USER", "cma_user"),
        password=env.get("CMA_DB_PASS", ""),
        database=env.get("CMA_DB_NAME", "cma"),
        charset="utf8mb4",
    )


def db_q(sql):
    conn = pymysql.connect(**_db_cfg())
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            return cur.fetchall()
    finally:
        conn.close()


def http_json(path, method="GET", payload=None, token=None):
    headers = {}
    if token:
        headers["Authorization"] = "Bearer " + token
    data = json.dumps(payload).encode() if payload is not None else None
    if data:
        headers["Content-Type"] = "application/json"
    req = urllib.request.Request(BASE + path, data=data, headers=headers, method=method)
    with urllib.request.urlopen(req) as r:
        return json.loads(r.read().decode())


print("== 1. 库阈值 ==")
rows = db_q("SELECT id, kpi_code, threshold_green, threshold_yellow, threshold_red FROM kpi_definitions WHERE id IN (456,457,458)")
m = {r[0]: r for r in rows}
check("457 COVER 100/85/85", m[457][2:5] == (">=100", ">=85", "<85"), str(m[457]))
check("458 GAP 0/15/15", m[458][2:5] == ("<=0", "<=15", ">15"), str(m[458]))
check("456 REVENUE 无阈值", m[456][2] is None and m[456][3] is None and m[456][4] is None, str(m[456]))

print("== 2. 现金口径定义 ==")
rows = db_q("SELECT kpi_code, unit, threshold_green, threshold_yellow, threshold_red FROM kpi_definitions WHERE kpi_code IN ('F_BEP_REVENUE_CASH','F_BEP_COVER_CASH')")
cash = {r[0]: r for r in rows}
check("REVENUE_CASH 存在且无阈值", "F_BEP_REVENUE_CASH" in cash and cash["F_BEP_REVENUE_CASH"][1] == "万元" and cash["F_BEP_REVENUE_CASH"][2] is None, str(cash.get("F_BEP_REVENUE_CASH")))
check("COVER_CASH 存在 100/85/85", "F_BEP_COVER_CASH" in cash and cash["F_BEP_COVER_CASH"][1] == "%" and cash["F_BEP_COVER_CASH"][2:5] == (">=100", ">=85", "<85"), str(cash.get("F_BEP_COVER_CASH")))

print("== 3. 2026-07 派生5条 ==")
rows = db_q("SELECT k.kpi_code, v.actual_value, v.source_type FROM kpi_values v JOIN kpi_definitions k ON v.kpi_id=k.id WHERE v.period='2026-07' AND k.kpi_code LIKE 'F_BEP%' AND v.source_type='derived'")
vals = {r[0]: r[1] for r in rows}
exp_book = {"F_BEP_REVENUE": 169.9, "F_BEP_COVER": 66.9, "F_BEP_GAP": 33.1}
for code, ev in exp_book.items():
    check(f"{code} = {ev}", vals.get(code) == ev, f"got {vals.get(code)}")
check("REVENUE_CASH ~121.4±1%", abs(vals.get("F_BEP_REVENUE_CASH", 0) - 121.4) / 121.4 < 0.01, f"got {vals.get('F_BEP_REVENUE_CASH')}")
check("COVER_CASH ~93.5±1%", abs(vals.get("F_BEP_COVER_CASH", 0) - 93.5) / 93.5 < 0.01, f"got {vals.get('F_BEP_COVER_CASH')}")

print("== 4. 告警闭环 ==")
rows = db_q("SELECT id, alert_level, status FROM kpi_alerts WHERE kpi_id IN (457,458,463) ORDER BY id")
alerts = {r[0]: (r[1], r[2]) for r in rows}
check("573 旧黄已resolved", alerts.get(573) == ("yellow", "resolved"), str(alerts.get(573)))
check("574 旧黄已resolved", alerts.get(574) == ("yellow", "resolved"), str(alerts.get(574)))
check("575 COVER red pending", alerts.get(575) == ("red", "pending"), str(alerts.get(575)))
check("576 GAP red pending", alerts.get(576) == ("red", "pending"), str(alerts.get(576)))
check("577 COVER_CASH yellow pending", alerts.get(577) == ("yellow", "pending"), str(alerts.get(577)))

print("== 5. bep-card 端点 ==")
login = http_json("/api/cma/auth/login", "POST", {"username": ADMIN_USER, "password": ADMIN_PASS, "entity_id": 1})
card = http_json("/api/cma/dashboard/bep-card", token=login["token"])
check("available", card.get("available") is True)
check("book red", card["book"]["cover_level"] == "red", str(card["book"]))
check("cash yellow", card["cash"]["cover_level"] == "yellow", str(card["cash"]))
check("derived 5条", len(card.get("derived", {})) == 5, str(list(card.get("derived", {}).keys())))

print(f"\nALL {len(passed)} CHECKS PASSED")