Agent/examples/auto_put_ad_mini/tools/report_generator.py

"""
报告生成工具 — auto_put_ad_mini V3

职责：
  - 读取决策引擎输出
  - 生成 CSV（程序用）+ XLSX（人工查阅，带条件格式）
  - 输出到 outputs/reports/
"""

import logging
from datetime import datetime, timedelta
from pathlib import Path
from typing import Optional

import pandas as pd

from agent.tools import tool
from agent.tools.models import ToolContext, ToolResult

logger = logging.getLogger(__name__)

_MINI_DIR = Path(__file__).resolve().parent.parent
_REPORTS_DIR = _MINI_DIR / "outputs" / "reports"

# 最终输出列顺序
OUTPUT_COLUMNS = [
    "ad_id", "account_id", "ad_name", "audience_tier",
    "create_time", "ad_age_days", "configured_status", "bid_amount",
    # 昨日表现
    "yesterday_cost", "yesterday_revenue", "yesterday_roi",
    # 7日汇总
    "cost_7d_total", "cost_7d_avg", "revenue_7d_total",
    # f_7日动态ROI 组成
    "T0裂变系数_latest", "arpu_latest", "a_latest",
    "b_7d_mean", "T0裂变系数_7d_mean", "e_factor",
    "f_7日动态ROI",
    # 30日上下文
    "cost_30d_total", "cost_30d_avg",
    "stable_spend_days_30d", "creative_count",
    # 决策
    "action", "dimension", "reason",
    "recommended_change_pct", "current_bid", "recommended_bid",
    # 护栏 & 执行
    "guardrail_status", "guardrail_reason", "final_action", "final_bid",
    "execution_status",
    # 参考
    "f_7日动态ROI_mean_all",
]

# 中文列名映射
CN_COLUMNS = {
    "ad_id": "广告ID",
    "account_id": "账户ID",
    "ad_name": "广告名称",
    "audience_tier": "人群包",
    "create_time": "创建时间",
    "ad_age_days": "投放天数",
    "configured_status": "广告状态",
    "bid_amount": "出价(元)",
    "yesterday_cost": "昨日消耗(元)",
    "yesterday_revenue": "昨日收入(元)",
    "yesterday_roi": "昨日ROI",
    "cost_7d_total": "7日总消耗(元)",
    "cost_7d_avg": "7日日均消耗(元)",
    "revenue_7d_total": "7日总收入(元)",
    "T0裂变系数_latest": "T0裂变系数(最新)",
    "arpu_latest": "ARPU(最新)",
    "a_latest": "a值(最新)",
    "b_7d_mean": "b值(7日均值)",
    "T0裂变系数_7d_mean": "T0裂变系数(7日均值)",
    "e_factor": "e因子",
    "f_7日动态ROI": "f_7日动态ROI",
    "cost_30d_total": "30日总消耗(元)",
    "cost_30d_avg": "30日日均消耗(元)",
    "stable_spend_days_30d": "稳定消耗天数(30日)",
    "creative_count": "创意数",
    "action": "决策动作",
    "dimension": "命中维度",
    "reason": "决策理由",
    "recommended_change_pct": "建议调幅(%)",
    "current_bid": "当前出价(元)",
    "recommended_bid": "建议出价(元)",
    "guardrail_status": "护栏状态",
    "guardrail_reason": "护栏说明",
    "final_action": "最终动作",
    "final_bid": "最终出价(元)",
    "execution_status": "执行状态",
    "f_7日动态ROI_mean_all": "全体动态ROI均值",
}


def _write_xlsx_with_format(df: pd.DataFrame, path: Path) -> None:
    """生成带条件格式的 XLSX 文件。"""
    try:
        import openpyxl
        from openpyxl.styles import Font, PatternFill, Alignment
        from openpyxl.utils import get_column_letter
    except ImportError:
        logger.warning("openpyxl 未安装，跳过 XLSX 生成")
        return

    # 中文列名
    df_cn = df.rename(columns=CN_COLUMNS)
    df_cn.to_excel(path, index=False, engine="openpyxl")

    wb = openpyxl.load_workbook(path)
    ws = wb.active

    # 表头样式
    header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
    header_font = Font(color="FFFFFF", bold=True, size=10)

    for cell in ws[1]:
        cell.fill = header_fill
        cell.font = header_font
        cell.alignment = Alignment(horizontal="center")

    # 条件格式：不同动作不同颜色
    red_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
    red_font = Font(color="9C0006")
    yellow_fill = PatternFill(start_color="FFEB9C", end_color="FFEB9C", fill_type="solid")
    yellow_font = Font(color="9C6500")
    green_fill = PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid")
    green_font = Font(color="006100")

    action_col_idx = None
    for idx, cell in enumerate(ws[1], 1):
        if cell.value == "决策动作":
            action_col_idx = idx
            break

    if action_col_idx:
        for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
            action_val = row[action_col_idx - 1].value
            if action_val == "pause":
                for cell in row:
                    cell.fill = red_fill
                    cell.font = red_font
            elif action_val == "bid_down":
                for cell in row:
                    cell.fill = yellow_fill
                    cell.font = yellow_font
            elif action_val == "bid_up":
                for cell in row:
                    cell.fill = green_fill
                    cell.font = green_font

    # 自动列宽
    for col_idx in range(1, ws.max_column + 1):
        col_letter = get_column_letter(col_idx)
        max_len = max(
            len(str(ws.cell(row=r, column=col_idx).value or ""))
            for r in range(1, min(ws.max_row + 1, 50))
        )
        ws.column_dimensions[col_letter].width = min(max_len + 4, 30)

    wb.save(path)
    logger.info("XLSX 已生成: %s", path)


@tool(description="生成决策报告（CSV + XLSX 带条件格式）")
async def generate_report(
    ctx: ToolContext,
    decision_csv: str = "",
    end_date: str = "yesterday",
) -> ToolResult:
    """
    生成决策报告。

    Args:
        decision_csv: 决策结果 CSV 路径（analyze_ads 输出）
        end_date: 结束日期

    Returns:
        ToolResult，包含报告路径
    """
    try:
        if not decision_csv:
            decision_csv = str(_MINI_DIR / "outputs" / "decision_temp.csv")

        df = pd.read_csv(decision_csv)

        if df.empty:
            return ToolResult(title="报告生成", output="决策数据为空")

        if end_date == "yesterday":
            end_date = (datetime.now() - timedelta(days=1)).strftime("%Y%m%d")

        _REPORTS_DIR.mkdir(parents=True, exist_ok=True)

        # 选择输出列（存在的列）
        cols = [c for c in OUTPUT_COLUMNS if c in df.columns]
        df_out = df[cols].copy()

        # 排序：关停在前，按消耗降序
        sort_cols = []
        ascending_flags = []
        if "action" in df_out.columns:
            df_out["_sort_action"] = (df_out["action"] == "pause").astype(int) * -1
            sort_cols.append("_sort_action")
            ascending_flags.append(True)
        if "cost_7d_total" in df_out.columns:
            sort_cols.append("cost_7d_total")
            ascending_flags.append(False)

        if sort_cols:
            df_out = df_out.sort_values(sort_cols, ascending=ascending_flags)
            if "_sort_action" in df_out.columns:
                df_out.drop(columns=["_sort_action"], inplace=True)

        # CSV
        csv_path = _REPORTS_DIR / f"decision_{end_date}.csv"
        df_out.to_csv(csv_path, index=False, encoding="utf-8-sig")
        logger.info("CSV 已生成: %s", csv_path)

        # XLSX
        xlsx_path = _REPORTS_DIR / f"decision_{end_date}.xlsx"
        _write_xlsx_with_format(df_out, xlsx_path)

        pause_count = (df_out["action"] == "pause").sum() if "action" in df_out.columns else 0
        total_count = len(df_out)

        return ToolResult(
            title=f"报告已生成（{total_count}个广告）",
            output=f"报告已生成:\n  CSV: {csv_path}\n  XLSX: {xlsx_path}\n\n共 {total_count} 个广告，其中 {pause_count} 个建议关停",
            metadata={
                "csv_path": str(csv_path),
                "xlsx_path": str(xlsx_path),
                "total_ads": total_count,
                "pause_ads": pause_count,
            },
        )

    except Exception as e:
        logger.error("报告生成失败: %s", e, exc_info=True)
        return ToolResult(title="报告生成失败", output=str(e))


# ═══════════════════════════════════════════
# 双引擎对比工具
# ═══════════════════════════════════════════


@tool(description="对比规则引擎与智能引擎的决策差异")
async def compare_decisions(
    ctx: ToolContext,
    end_date: str = "yesterday",
) -> ToolResult:
    """
    加载同日期的规则引擎和智能引擎决策结果，输出对比报告。

    对比标签：
      - agree：两个引擎一致（都 pause 或都 hold）
      - rule_only_pause：只有规则引擎关停
      - llm_only_pause：只有智能引擎关停
      - disagree：决策不同（其他情况）

    Args:
        end_date: 结束日期（YYYYMMDD 或 "yesterday"）
    """
    try:
        if end_date == "yesterday":
            end_date = (datetime.now() - timedelta(days=1)).strftime("%Y%m%d")

        rule_path = _REPORTS_DIR / f"decision_{end_date}.csv"
        llm_path = _REPORTS_DIR / f"llm_decisions_{end_date}.csv"

        if not rule_path.exists():
            return ToolResult(
                title="compare_decisions",
                output=f"规则引擎结果不存在: {rule_path}",
            )
        if not llm_path.exists():
            return ToolResult(
                title="compare_decisions",
                output=f"智能引擎结果不存在: {llm_path}",
            )

        df_rule = pd.read_csv(rule_path)[["ad_id", "action", "dimension", "reason"]].copy()
        df_rule.columns = ["ad_id", "rule_action", "rule_dimension", "rule_reason"]

        df_llm = pd.read_csv(llm_path)[["ad_id", "action", "dimension", "reason", "confidence"]].copy()
        df_llm.columns = ["ad_id", "llm_action", "llm_dimension", "llm_reason", "llm_confidence"]

        # 外连接合并
        df = pd.merge(df_rule, df_llm, on="ad_id", how="outer")

        # 填充缺失
        df["rule_action"] = df["rule_action"].fillna("no_data")
        df["llm_action"] = df["llm_action"].fillna("no_data")

        # 打标签
        def _label(row):
            r = row["rule_action"]
            l = row["llm_action"]
            if r == l:
                return "agree"
            if r == "pause" and l != "pause":
                return "rule_only_pause"
            if l == "pause" and r != "pause":
                return "llm_only_pause"
            return "disagree"

        df["comparison"] = df.apply(_label, axis=1)

        # 保存对比报告
        _REPORTS_DIR.mkdir(parents=True, exist_ok=True)
        out_path = _REPORTS_DIR / f"comparison_{end_date}.csv"
        df.to_csv(out_path, index=False, encoding="utf-8-sig")

        # 统计摘要
        total = len(df)
        counts = df["comparison"].value_counts().to_dict()
        agree = counts.get("agree", 0)
        rule_only = counts.get("rule_only_pause", 0)
        llm_only = counts.get("llm_only_pause", 0)
        disagree = counts.get("disagree", 0)

        rule_pause = (df["rule_action"] == "pause").sum()
        llm_pause = (df["llm_action"] == "pause").sum()
        agree_rate = round(agree / total * 100, 1) if total > 0 else 0.0

        summary = (
            f"双引擎对比报告: {out_path}\n\n"
            f"总广告数: {total}\n"
            f"一致率: {agree_rate}%（{agree}/{total}）\n\n"
            f"规则引擎关停: {rule_pause} 个\n"
            f"智能引擎关停: {llm_pause} 个\n\n"
            f"分类明细:\n"
            f"  agree（两者一致）: {agree}\n"
            f"  rule_only_pause（仅规则关停）: {rule_only}\n"
            f"  llm_only_pause（仅智能关停）: {llm_only}\n"
            f"  disagree（其他差异）: {disagree}"
        )

        return ToolResult(
            title=f"双引擎对比（一致率 {agree_rate}%）",
            output=summary,
            metadata={
                "csv_path": str(out_path),
                "total": total,
                "agree_rate": agree_rate,
                "rule_pause": int(rule_pause),
                "llm_pause": int(llm_pause),
                "agree": agree,
                "rule_only_pause": rule_only,
                "llm_only_pause": llm_only,
                "disagree": disagree,
                "end_date": end_date,
            },
        )

    except Exception as e:
        logger.error("compare_decisions 失败: %s", e, exc_info=True)
        return ToolResult(title="compare_decisions 失败", output=str(e))