cma-management/backend/app/utils/alert_attribution.py

"""告警归因引擎 — 管理会计OS (P1-③ 2026-08-28)

告警从"差多少"到"差在哪+怎么办"：
  - 子KPI维度拆解: 查 kpi_hierarchy 下级KPI各自差异（量差方向）
  - 科目明细拆解: 查 kpi_subject_map → voucher_details 汇总科目发生额（价差方向）
  - 趋势归因: 复用 deviation_engine.check_trend_anomaly 连续3期检测
  - 场景建议: 按 alert_type 联查 scenario_suggestions

attribution JSON 结构:
{
  "dimensions": [{"kpi_id":1,"kpi_name":"销售费用","deviation_value":-3.2,"deviation_rate":-18.6,"weight":0.5}],
  "subjects": [{"subject_code":"6601","subject_name":"销售费用","amount_diff":2.1,"share_pct":34.5}],
  "variance_type": "quantity_diff|price_diff|mixed",
  "trend": {"anomaly":true,"type":"continuous_decline","periods":["2026-06","2026-07","2026-08"],"message":"连续3期下滑"}
}
"""
import logging
from typing import Optional

from sqlalchemy.orm import Session
from sqlalchemy import func

from app.models import KPIHierarchy, KPISubjectMap, VoucherDetail, ScenarioSuggestion, KPIDefinition

logger = logging.getLogger("cma.alert_attribution")

# 收入型KPI特征（量差方向: 子KPI量级偏离）
REVENUE_TYPE_CODES = (
    "SALES_TOTAL", "REVENUE", "F_REVENUE", "SALES_PROFIT_RATE",
    "CUSTOMER_COUNT", "NEW_CUSTOMER", "TURNOVER_RATE",
)


def build_dimension_attribution(db: Session, kpi_id: int, period: str) -> list:
    """子KPI维度拆解 — 查 kpi_hierarchy 下级KPI各自差异（实际vs预算）"""
    from app.models import KPIValue, BudgetPlan

    children = db.query(KPIHierarchy).filter(
        KPIHierarchy.parent_kpi_id == kpi_id
    ).all()
    if not children:
        return []

    result = []
    for rel in children:
        child_id = rel.child_kpi_id
        actual = db.query(KPIValue).filter(
            KPIValue.kpi_id == child_id,
            KPIValue.period == period,
            KPIValue.actual_value.isnot(None),
        ).order_by(KPIValue.calculated_at.desc()).first()
        budget = db.query(BudgetPlan).filter(
            BudgetPlan.kpi_id == child_id,
            BudgetPlan.period == period,
            BudgetPlan.status == "active",
        ).order_by(BudgetPlan.updated_at.desc()).first()

        av = actual.actual_value if actual else None
        bv = budget.budget_value if budget else None
        dev_value = None
        dev_rate = None
        if av is not None and bv is not None and bv != 0:
            dev_value = round(av - bv, 2)
            dev_rate = round(dev_value / bv * 100, 2)

        child_kpi = db.query(KPIDefinition).filter(KPIDefinition.id == child_id).first()
        result.append({
            "kpi_id": child_id,
            "kpi_name": child_kpi.kpi_name if child_kpi else f"KPI-{child_id}",
            "actual_value": av,
            "budget_value": bv,
            "deviation_value": dev_value,
            "deviation_rate": dev_rate,
            "weight": float(rel.weight or 0),
        })
    # 按偏差绝对值降序，最异常的排前面
    result.sort(key=lambda x: -(abs(x["deviation_value"]) if x["deviation_value"] is not None else 0))
    return result


def build_subject_attribution(db: Session, kpi_id: int, period: str) -> list:
    """科目明细拆解 — 查 kpi_subject_map → voucher_details 汇总科目发生额"""
    mappings = db.query(KPISubjectMap).filter(KPISubjectMap.kpi_id == kpi_id).all()
    if not mappings:
        return []

    result = []
    for m in mappings:
        q = db.query(
            func.coalesce(func.sum(VoucherDetail.debit_amount), 0),
            func.coalesce(func.sum(VoucherDetail.credit_amount), 0),
        ).filter(
            VoucherDetail.subject_code == m.subject_code,
            VoucherDetail.period == period,
        )
        row = q.first()
        debit_sum = float(row[0] or 0)
        credit_sum = float(row[1] or 0)
        # 方向: credit贷方(收入/流入) / debit借方(费用/流出)
        if m.calc_type == "ratio":
            amount = credit_sum - debit_sum
        elif m.calc_type in ("avg", "other"):
            amount = (credit_sum - debit_sum) / 2
        else:  # sum
            amount = credit_sum - debit_sum
        amount = round(amount * float(m.weight or 1.0), 2)

        result.append({
            "subject_code": m.subject_code,
            "subject_name": m.remark or m.subject_code,
            "amount_diff": amount,
            "calc_type": m.calc_type,
            "weight": float(m.weight or 1.0),
        })

    total = sum(abs(r["amount_diff"]) for r in result) or 0
    for r in result:
        r["share_pct"] = round(abs(r["amount_diff"]) / total * 100, 1) if total else 0
    result.sort(key=lambda x: -abs(x["amount_diff"]))
    return result


def detect_variance_type(kpi_code: str, dimensions: list, subjects: list) -> str:
    """量价差判定简化版：
    成本型KPI科目发生额偏离 → price_diff（价差）
    收入型KPI子KPI量级偏离 → quantity_diff（量差）
    两者都有 → mixed
    """
    has_dimension_dev = any(d.get("deviation_value") is not None and abs(d["deviation_value"]) > 0.01 for d in dimensions)
    has_subject_dev = any(abs(s.get("amount_diff", 0)) > 0.01 for s in subjects)

    is_revenue = any(code in (kpi_code or "").upper() for code in REVENUE_TYPE_CODES)

    if is_revenue:
        # 收入型: 子KPI(量)偏离为主 → quantity_diff
        if has_dimension_dev:
            return "quantity_diff"
        if has_subject_dev:
            return "price_diff"
        return "mixed"
    else:
        # 成本型: 科目发生额(价)偏离为主 → price_diff
        if has_subject_dev:
            return "price_diff"
        if has_dimension_dev:
            return "quantity_diff"
        return "mixed"


def match_scenario(db: Session, alert_type: Optional[str]) -> Optional[dict]:
    """按 alert_type 取 scenario_suggestions 建议（四类模板）"""
    if not alert_type:
        return None
    s = db.query(ScenarioSuggestion).filter(
        ScenarioSuggestion.alert_type == alert_type
    ).order_by(ScenarioSuggestion.sort_order.asc(), ScenarioSuggestion.id.asc()).first()
    if not s:
        return None
    return {
        "scenario_id": s.id,
        "alert_type": s.alert_type,
        "title": s.title,
        "description": s.description,
        "action_template": s.action_template,
        "priority": s.priority,
    }


def build_attribution(db: Session, kpi_id: int, period: str, alert_type: Optional[str] = None) -> dict:
    """组装完整归因JSON（供告警生成/详情接口共用）"""
    from app.utils.deviation_engine import check_trend_anomaly

    kpi = db.query(KPIDefinition).filter(KPIDefinition.id == kpi_id).first()
    kpi_code = kpi.kpi_code if kpi else ""

    dimensions = build_dimension_attribution(db, kpi_id, period)
    subjects = build_subject_attribution(db, kpi_id, period)
    variance_type = detect_variance_type(kpi_code, dimensions, subjects)
    trend = check_trend_anomaly(db, kpi_id, period, consecutive=3)

    # 默认场景归类（未显式传入时按KPI名称特征推断）
    if not alert_type:
        alert_type = infer_alert_type(kpi_code, kpi.kpi_name if kpi else "")

    attribution = {
        "dimensions": dimensions,
        "subjects": subjects,
        "variance_type": variance_type,
        "trend": trend,
    }
    return attribution, alert_type


def infer_alert_type(kpi_code: str = "", kpi_name: str = "") -> str:
    """按KPI特征推断告警场景类型（四类: cash_low/cash_critical/cost_high/revenue_drop）"""
    text = (kpi_code or "").upper() + (kpi_name or "")
    if any(k in text for k in ("CASH", "现金", "货币资金", "资金")):
        return "cash_low"
    if any(k in text for k in ("COST", "费用", "成本", "支出")):
        return "cost_high"
    if any(k in text for k in ("REVENUE", "收入", "销售", "营收")):
        return "revenue_drop"
    return "cost_high"