strategy_books.py

from __future__ import annotations

import numpy as np
import pandas as pd


INTERNAL_MANDATE = "委托资管"
RETURN_BASE_THRESHOLD = 0.0001
EXCLUDED_STRATEGY_BOOK = "流动性/其他未纳入五类"
STRATEGY_CLASSIFICATION_VERSION = "2026-08-02-v4"

INTERNAL_STRATEGY_BOOK_ORDER = [
    "固收-配置盘",
    "固收-交易盘",
    "非标",
    "权益-配置盘",
    "权益-交易盘",
]
EXTERNAL_STRATEGY_BOOK_ORDER = [
    "人保固收",
    "泰康固收",
    "中信建投固收",
    "中邮证券固收",
    "富国权益",
    "华泰权益",
    "华夏基金权益",
    "国泰海通权益",
    "大成基金权益",
    "广发基金权益",
    "太平资产香港",
    "太保投资香港",
    "国寿富兰克林",
]
STRATEGY_BOOK_ORDER = INTERNAL_STRATEGY_BOOK_ORDER + EXTERNAL_STRATEGY_BOOK_ORDER
STRATEGY_BOOK_SCOPE = {
    **{label: "委内" for label in INTERNAL_STRATEGY_BOOK_ORDER},
    **{label: "委外" for label in EXTERNAL_STRATEGY_BOOK_ORDER},
}
STRATEGY_BOOK_LABEL_ORDER = [f"{STRATEGY_BOOK_SCOPE[label]}-{label}" for label in STRATEGY_BOOK_ORDER]

FIXED_ALLOCATION_CLASSES = {
    "存款",
    "同业存单",
    "政府债",
    "金融债",
    "企业债",
    "固收类保险资管产品",
}
FIXED_TRADING_CLASSES = {
    "同业存单",
    "政府债",
    "金融债",
    "企业债",
    "资产支持证券",
    "债券型基金",
    "固收类保险资管产品",
}
NONSTANDARD_CLASSES = {
    "信托计划",
    "债权计划",
    "资产支持计划",
    "持有型不动产ABS",
}
EQUITY_TRADING_CLASSES = {
    "股票",
    "股票型基金",
    "混合型基金",
    "股票型保险资管产品",
    "混合型保险资管产品",
}
INTERNAL_EQUITY_PRODUCT_CLASSES = EQUITY_TRADING_CLASSES - {"股票"}
OUTSOURCED_EQUITY_HOLDING_CLASSES = {
    "股票",
    "股票型基金",
    "混合型基金",
    "股票型保险资管产品",
    "混合型保险资管产品",
    "单一资产管理计划（股票类产品）",
    "股权基金",
    "未上市企业股权",
    "股权计划",
}
OUTSOURCED_EQUITY_STOCK_CLASSES = {"股票"}
OUTSOURCED_EQUITY_HOLDING_TYPE_ORDER = ["股票", "基金及产品"]
OUTSOURCED_FIXED_CLASSES = {
    "同业存单",
    "政府债",
    "金融债",
    "企业债",
    "资产支持证券",
    "债券型基金",
    "固收类保险资管产品",
    "信托计划",
    "债权计划",
    "资产支持计划",
    "持有型不动产ABS",
    "公募REITS",
}
OUTSOURCED_FULL_ACCOUNT_MANDATE_BOOKS = {
    "委托华泰": "华泰权益",
    "委托太平资产香港": "太平资产香港",
    "委托太保投资香港": "太保投资香港",
    "委托国寿富兰克林": "国寿富兰克林",
}
OUTSOURCED_SINGLE_PLAN_BOOKS = {
    "中信建投单一计划": ("中信建投固收", "中信建投"),
    "中邮证券单一计划": ("中邮证券固收", "中邮证券"),
    "华夏基金单一计划": ("华夏基金权益", "华夏基金"),
    "国泰海通单一计划": ("国泰海通权益", "国泰海通"),
    "大成基金单一计划": ("大成基金权益", "大成基金"),
    "富国基金单一计划": ("富国权益", "富国基金"),
    "广发基金单一计划": ("广发基金权益", "广发基金"),
}
OUTSOURCED_SINGLE_PLAN_FUND_BOOKS = {
    keyword: strategy_book
    for strategy_book, keyword in OUTSOURCED_SINGLE_PLAN_BOOKS.values()
}
OUTSOURCED_FIXED_BOOKS = {
    "人保固收",
    "泰康固收",
    "中信建投固收",
    "中邮证券固收",
}
OUTSOURCED_TRUSTEE_DISPLAY = {
    "人保固收": "人保",
    "泰康固收": "泰康",
    "中信建投固收": "中信建投",
    "中邮证券固收": "中邮证券",
    "富国权益": "富国基金",
    "华泰权益": "华泰",
    "华夏基金权益": "华夏基金",
    "国泰海通权益": "国泰海通",
    "大成基金权益": "大成基金",
    "广发基金权益": "广发基金",
    "太平资产香港": "太平资产香港",
    "太保投资香港": "太保投资香港",
    "国寿富兰克林": "国寿富兰克林",
}
MANAGER_DISPLAY_COLUMN = "manager_display"
OUTSOURCED_EQUITY_BOOKS = {
    "富国权益",
    "华泰权益",
    "华夏基金权益",
    "国泰海通权益",
    "大成基金权益",
    "广发基金权益",
    "太平资产香港",
    "太保投资香港",
    "国寿富兰克林",
}
OUTSOURCED_EQUITY_DASHBOARD_LABELS = {
    "富国权益": "委外-富国",
    "华泰权益": "委外-华泰",
    "华夏基金权益": "委外-华夏基金",
    "国泰海通权益": "委外-国泰海通",
    "大成基金权益": "委外-大成基金",
    "广发基金权益": "委外-广发基金",
    "太平资产香港": "委外-太平资产香港",
    "太保投资香港": "委外-太保投资香港",
    "国寿富兰克林": "委外-国寿富兰克林",
}
OUTSOURCED_EQUITY_DASHBOARD_ORDER = [
    label for label in EXTERNAL_STRATEGY_BOOK_ORDER if label in OUTSOURCED_EQUITY_BOOKS
]
EQUITY_DASHBOARD_INTERNAL_GROUPS = [
    ("internal_stock", "委内-股票"),
    ("internal_equity_products", "委内-权益产品"),
    ("internal_oci_stock", "委内-OCI股票"),
]
EQUITY_DASHBOARD_LABEL_ORDER = [
    *[display_label for _, display_label in EQUITY_DASHBOARD_INTERNAL_GROUPS],
    *[
        OUTSOURCED_EQUITY_DASHBOARD_LABELS[strategy_book]
        for strategy_book in OUTSOURCED_EQUITY_DASHBOARD_ORDER
    ],
]
OUTSOURCED_FULL_ACCOUNT_BOOKS = {
    *OUTSOURCED_EQUITY_BOOKS,
    *OUTSOURCED_FULL_ACCOUNT_MANDATE_BOOKS.values(),
}
CASH_LIQUIDITY_CLASSES = {
    "活期存款",
    "买入返售",
    "正回购",
    "逆回购",
    "货币类基金",
    "货币类产品",
    "其他（应收）",
    "其它",
}
PRIVATE_EQUITY_REAL_ESTATE_CLASSES = {
    "股权基金",
    "未上市企业股权",
    "股权计划",
    "不动产基金",
    "不动产直投",
}

SECTION_ORDER = {
    "存款": 0,
    "债券": 1,
    "基金": 2,
    "股票": 3,
    "流动性": 4,
    "其他": 9,
}
ITEM_ORDER = {
    "存款": 0,
    "同业存单": 1,
    "政府债": 2,
    "金融债": 3,
    "企业债": 4,
    "固收类基金及产品": 5,
    "非标": 6,
    "股票": 7,
    "股票型产品": 8,
    "公募REITS": 9,
}

RUNTIME_TEXT_COLUMNS = [
    "mandate_type",
    "asset_major_class",
    "asset_class_level_1",
    "asset_class_level_2",
    "asset_class_level_3",
    "asset_class",
    "trade_strategy",
    "manager",
    "fund_book_name",
    "group_book_name",
]
STRATEGY_BOOK_OUTPUT_COLUMNS = [
    "strategy_book",
    "strategy_book_scope",
    "strategy_book_display_label",
    "strategy_book_section",
    "strategy_book_item",
    "strategy_book_exclusion_reason",
    MANAGER_DISPLAY_COLUMN,
]


def _text_value(row: pd.Series | dict, column: str) -> str:
    value = row.get(column, "")
    if pd.isna(value):
        return ""
    return str(value).strip()


def classify_strategy_book(row: pd.Series | dict) -> str:
    mandate_type = _text_value(row, "mandate_type")
    fund_book_name = _text_value(row, "fund_book_name")
    asset_major_class = _text_value(row, "asset_major_class")
    asset_class = _text_value(row, "asset_class")
    trade_strategy = _text_value(row, "trade_strategy")
    manager = _text_value(row, "manager")

    if mandate_type == INTERNAL_MANDATE and asset_major_class == "固收":
        if trade_strategy == "配置" and asset_class in NONSTANDARD_CLASSES:
            return "非标"
        if trade_strategy == "配置" and asset_class in FIXED_ALLOCATION_CLASSES:
            return "固收-配置盘"
        if "交易" in trade_strategy and asset_class in FIXED_TRADING_CLASSES:
            return "固收-交易盘"

    if mandate_type == INTERNAL_MANDATE and asset_major_class == "权益":
        if trade_strategy == "配置" and manager == "鲍淼" and asset_class == "股票":
            return "权益-配置盘"
        if trade_strategy == "交易" and asset_class in EQUITY_TRADING_CLASSES:
            return "权益-交易盘"

    if mandate_type == "委托人保" and asset_class in OUTSOURCED_FIXED_CLASSES:
        return "人保固收"
    if mandate_type == "委托泰康" and asset_class in OUTSOURCED_FIXED_CLASSES:
        return "泰康固收"
    if mandate_type in OUTSOURCED_SINGLE_PLAN_BOOKS:
        return OUTSOURCED_SINGLE_PLAN_BOOKS[mandate_type][0]
    for keyword, strategy_book in OUTSOURCED_SINGLE_PLAN_FUND_BOOKS.items():
        if keyword in fund_book_name:
            return strategy_book
    if mandate_type in OUTSOURCED_FULL_ACCOUNT_MANDATE_BOOKS:
        return OUTSOURCED_FULL_ACCOUNT_MANDATE_BOOKS[mandate_type]

    return EXCLUDED_STRATEGY_BOOK


def strategy_book_scope(value: object) -> str:
    return STRATEGY_BOOK_SCOPE.get(str(value).strip(), "未纳入")


def strategy_book_display_label(value: object) -> str:
    label = str(value).strip()
    scope = strategy_book_scope(label)
    if scope in {"委内", "委外"}:
        return f"{scope}-{label}"
    return label


def manager_display_label(manager: object, strategy_book: object) -> str:
    """Use the recognized outsourced institution when the source manager is blank."""
    raw_manager = "" if pd.isna(manager) else str(manager).strip()
    trustee = OUTSOURCED_TRUSTEE_DISPLAY.get(str(strategy_book).strip())
    if trustee and raw_manager in {"", "未分配/待确认"}:
        return trustee
    return raw_manager or "未分配/待确认"


def strategy_book_section(row: pd.Series | dict) -> str:
    strategy_book = _text_value(row, "strategy_book") or classify_strategy_book(row)
    asset_class = _text_value(row, "asset_class")
    asset_class_level_1 = _text_value(row, "asset_class_level_1")

    if strategy_book in {"固收-配置盘", "固收-交易盘", "非标"} | OUTSOURCED_FIXED_BOOKS:
        if asset_class == "存款":
            return "存款"
        if asset_class in {"同业存单", "政府债", "金融债", "企业债", "资产支持证券", "持有型不动产ABS"}:
            return "债券"
        if asset_class in {
            "债券型基金",
            "固收类保险资管产品",
            "信托计划",
            "债权计划",
            "资产支持计划",
            "公募REITS",
            "单一资产管理计划（固收类产品）",
        }:
            return "基金"

    if strategy_book in {"权益-配置盘", "权益-交易盘"} | OUTSOURCED_FULL_ACCOUNT_BOOKS:
        if (
            strategy_book in OUTSOURCED_FULL_ACCOUNT_BOOKS
            and (asset_class in CASH_LIQUIDITY_CLASSES or asset_class_level_1 in {"现金", "回购", "存款"})
        ):
            return "流动性"
        if asset_class == "股票":
            return "股票"
        if asset_class in {
            "股票型基金",
            "混合型基金",
            "股票型保险资管产品",
            "混合型保险资管产品",
            "单一资产管理计划（股票类产品）",
        }:
            return "基金"

    return "其他"


def strategy_book_item(row: pd.Series | dict) -> str:
    asset_class = _text_value(row, "asset_class")
    if asset_class == "资产支持证券":
        return "企业债"
    if asset_class in NONSTANDARD_CLASSES:
        return "非标"
    if asset_class in {"债券型基金", "固收类保险资管产品", "单一资产管理计划（固收类产品）"}:
        return "固收类基金及产品"
    if asset_class in {
        "股票型基金",
        "混合型基金",
        "股票型保险资管产品",
        "混合型保险资管产品",
        "单一资产管理计划（股票类产品）",
    }:
        return "股票型产品"
    return asset_class or "未填报"


def exclusion_reason(row: pd.Series | dict) -> str:
    asset_major_class = _text_value(row, "asset_major_class")
    asset_class = _text_value(row, "asset_class")
    asset_class_level_1 = _text_value(row, "asset_class_level_1")
    asset_class_level_2 = _text_value(row, "asset_class_level_2")
    trade_strategy = _text_value(row, "trade_strategy")
    mandate_type = _text_value(row, "mandate_type")
    fund_book_name = _text_value(row, "fund_book_name")

    if (
        asset_class in PRIVATE_EQUITY_REAL_ESTATE_CLASSES
        or "股权" in asset_class
        or "不动产" in asset_class
        or "股权" in asset_class_level_2
        or "不动产" in asset_class_level_2
    ):
        return "股权/不动产直投，未纳入委内/委外比较核心分类"
    if asset_class in CASH_LIQUIDITY_CLASSES or asset_class_level_1 in {"现金", "回购", "存款"}:
        return "流动性、现金、回购、应收或费用科目"
    if asset_major_class in {"", "-", "未填报", "缺省"} or trade_strategy in {"", "-", "未填报", "缺省"}:
        return "源表缺少资产大类或交易策略"
    if (
        mandate_type not in {INTERNAL_MANDATE, "委托人保", "委托泰康"}
        and mandate_type not in OUTSOURCED_FULL_ACCOUNT_MANDATE_BOOKS
        and mandate_type not in OUTSOURCED_SINGLE_PLAN_BOOKS
        and not any(keyword in fund_book_name for keyword in OUTSOURCED_SINGLE_PLAN_FUND_BOOKS)
    ):
        return "非委内/指定委外账户"
    return "不符合委内/委外比较分类规则"


def _resolve_single_plan_hierarchy(working: pd.DataFrame) -> pd.Series:
    """Choose one source level per exact snapshot for each single outsourced plan."""
    reasons = pd.Series("", index=working.index, dtype=object)
    if working.empty:
        return reasons

    market_value = pd.to_numeric(
        working.get("full_market_value", pd.Series(0.0, index=working.index)),
        errors="coerce",
    ).fillna(0.0)
    if "snapshot_date" in working.columns:
        month_key = working["snapshot_date"].fillna("").astype(str)
    elif "snapshot_month" in working.columns:
        month_key = working["snapshot_month"].fillna("").astype(str)
    else:
        month_key = pd.Series("__all__", index=working.index)

    for mandate_type, (strategy_book, keyword) in OUTSOURCED_SINGLE_PLAN_BOOKS.items():
        top_mask = working["mandate_type"].eq(mandate_type)
        detail_mask = working["mandate_type"].eq("单一委外") & working["fund_book_name"].str.contains(
            keyword,
            regex=False,
            na=False,
        )
        candidate_mask = top_mask | detail_mask
        if not candidate_mask.any():
            continue

        for month in month_key[candidate_mask].drop_duplicates().tolist():
            month_mask = month_key.eq(month)
            top_index = working.index[top_mask & month_mask]
            detail_index = working.index[detail_mask & month_mask]
            if top_index.empty or detail_index.empty:
                continue

            top_value = float(market_value.loc[top_index].abs().sum())
            detail_value = float(market_value.loc[detail_index].abs().sum())
            if detail_value > RETURN_BASE_THRESHOLD:
                excluded_index = top_index
                reason = f"{strategy_book}顶层产品汇总行，已排除以避免重复计算底层持仓"
            elif top_value > RETURN_BASE_THRESHOLD:
                excluded_index = detail_index
                reason = f"{strategy_book}底层持仓行规模为零，已改用顶层产品汇总行"
            else:
                excluded_index = top_index
                reason = f"{strategy_book}顶层产品汇总行，已排除以避免重复计算底层持仓"

            working.loc[excluded_index, "strategy_book"] = EXCLUDED_STRATEGY_BOOK
            reasons.loc[excluded_index] = reason

    return reasons


def assign_strategy_book_columns(data: pd.DataFrame) -> pd.DataFrame:
    working = data.copy()
    for column in RUNTIME_TEXT_COLUMNS:
        if column not in working.columns:
            working[column] = ""
        working[column] = working[column].fillna("").astype(str).str.strip()

    if working.empty:
        working["strategy_book"] = pd.Series(dtype=object)
        working["strategy_book_scope"] = pd.Series(dtype=object)
        working["strategy_book_display_label"] = pd.Series(dtype=object)
        working["strategy_book_section"] = pd.Series(dtype=object)
        working["strategy_book_item"] = pd.Series(dtype=object)
        working["strategy_book_exclusion_reason"] = pd.Series(dtype=object)
        working[MANAGER_DISPLAY_COLUMN] = pd.Series(dtype=object)
        return working

    working["strategy_book"] = working.apply(classify_strategy_book, axis=1)
    hierarchy_exclusion_reason = _resolve_single_plan_hierarchy(working)
    working["strategy_book_scope"] = working["strategy_book"].map(strategy_book_scope)
    working["strategy_book_display_label"] = working["strategy_book"].map(strategy_book_display_label)
    working["strategy_book_section"] = working.apply(strategy_book_section, axis=1)
    working["strategy_book_item"] = working.apply(strategy_book_item, axis=1)
    working["strategy_book_exclusion_reason"] = working.apply(exclusion_reason, axis=1)
    hierarchy_excluded = hierarchy_exclusion_reason.ne("")
    working.loc[hierarchy_excluded, "strategy_book_exclusion_reason"] = hierarchy_exclusion_reason.loc[
        hierarchy_excluded
    ]
    working[MANAGER_DISPLAY_COLUMN] = [
        manager_display_label(manager, strategy_book)
        for manager, strategy_book in zip(working["manager"], working["strategy_book"])
    ]
    return working


def ensure_strategy_book_columns(data: pd.DataFrame) -> pd.DataFrame:
    if all(column in data.columns for column in STRATEGY_BOOK_OUTPUT_COLUMNS):
        return data.copy()
    return assign_strategy_book_columns(data)


def outsourced_equity_holding_type(asset_class: object) -> str:
    label = "" if pd.isna(asset_class) else str(asset_class).strip()
    if label in OUTSOURCED_EQUITY_STOCK_CLASSES:
        return "股票"
    if label in OUTSOURCED_EQUITY_HOLDING_CLASSES:
        return "基金及产品"
    return "其他"


def outsourced_equity_holding_slice(data: pd.DataFrame) -> pd.DataFrame:
    working = ensure_strategy_book_columns(data)
    working["outsourced_equity_holding_type"] = working["asset_class"].map(outsourced_equity_holding_type)
    if working.empty:
        return working
    return working[
        working["strategy_book_scope"].eq("委外")
        & working["outsourced_equity_holding_type"].isin(OUTSOURCED_EQUITY_HOLDING_TYPE_ORDER)
    ].copy()


def _metric_columns(comparison_mode: str) -> tuple[str, str, str]:
    if comparison_mode == "年初以来":
        return "finance_income_ytd", "comprehensive_income_ytd", "avg_capital_ytd"
    return "finance_income_mtd", "comprehensive_income_mtd", "avg_capital_mtd"


def _current_strategy_slice(data: pd.DataFrame, current_month: str) -> pd.DataFrame:
    snapshot_key = "snapshot_date" if "snapshot_date" in data.columns else "snapshot_month"
    working = data[data[snapshot_key] == current_month].copy()
    return ensure_strategy_book_columns(working)


def _aggregate_current(frame: pd.DataFrame, group_cols: list[str], comparison_mode: str) -> pd.DataFrame:
    finance_col, comprehensive_col, capital_col = _metric_columns(comparison_mode)
    for column in ["full_market_value", finance_col, comprehensive_col, capital_col]:
        if column not in frame:
            frame[column] = 0.0
        frame[column] = pd.to_numeric(frame[column], errors="coerce").fillna(0.0)

    if frame.empty:
        return pd.DataFrame(
            columns=group_cols
            + [
                "full_market_value_current",
                "finance_income_mtd_current",
                "comprehensive_income_mtd_current",
                "avg_capital_mtd_current",
                "finance_return_mtd",
                "comprehensive_return_mtd",
                "record_count_current",
            ]
        )

    summary = (
        frame.groupby(group_cols, dropna=False)
        .agg(
            full_market_value_current=("full_market_value", "sum"),
            finance_income_mtd_current=(finance_col, "sum"),
            comprehensive_income_mtd_current=(comprehensive_col, "sum"),
            avg_capital_mtd_current=(capital_col, "sum"),
            record_count_current=("asset_name", "size"),
        )
        .reset_index()
    )
    valid_base = summary["avg_capital_mtd_current"].abs() > RETURN_BASE_THRESHOLD
    summary["finance_return_mtd"] = np.nan
    summary["comprehensive_return_mtd"] = np.nan
    summary.loc[valid_base, "finance_return_mtd"] = (
        summary.loc[valid_base, "finance_income_mtd_current"]
        / summary.loc[valid_base, "avg_capital_mtd_current"]
    )
    summary.loc[valid_base, "comprehensive_return_mtd"] = (
        summary.loc[valid_base, "comprehensive_income_mtd_current"]
        / summary.loc[valid_base, "avg_capital_mtd_current"]
    )
    return summary


def equity_dashboard_summary(
    data: pd.DataFrame,
    current_month: str,
    comparison_mode: str,
) -> pd.DataFrame:
    current = _current_strategy_slice(data, current_month)
    internal = current[
        current["mandate_type"].eq(INTERNAL_MANDATE)
        & current["asset_major_class"].eq("权益")
    ].copy()
    internal_oci_mask = (
        internal["asset_class"].eq("股票")
        & internal["manager"].eq("鲍淼")
        & internal["trade_strategy"].eq("配置")
    )

    internal_slices = {
        "internal_stock": internal[
            internal["asset_class"].eq("股票") & ~internal_oci_mask
        ].copy(),
        "internal_equity_products": internal[
            internal["asset_class"].isin(INTERNAL_EQUITY_PRODUCT_CLASSES)
        ].copy(),
        "internal_oci_stock": internal[internal_oci_mask].copy(),
    }

    grouped_frames: list[pd.DataFrame] = []
    internal_labels = dict(EQUITY_DASHBOARD_INTERNAL_GROUPS)
    for group_key, frame in internal_slices.items():
        frame["equity_scope"] = "委内"
        frame["equity_group"] = group_key
        frame["equity_group_display_label"] = internal_labels[group_key]
        grouped_frames.append(frame)

    outsourced = outsourced_equity_holding_slice(current)
    outsourced = outsourced[
        outsourced["strategy_book"].isin(OUTSOURCED_EQUITY_DASHBOARD_ORDER)
    ].copy()
    outsourced["equity_scope"] = "委外"
    outsourced["equity_group"] = outsourced["strategy_book"]
    outsourced["equity_group_display_label"] = outsourced["strategy_book"].map(
        OUTSOURCED_EQUITY_DASHBOARD_LABELS
    )
    grouped_frames.append(outsourced)

    group_cols = ["equity_scope", "equity_group", "equity_group_display_label"]
    combined = pd.concat(grouped_frames, ignore_index=True)
    summary = _aggregate_current(combined, group_cols, comparison_mode)

    template_rows = [
        {
            "equity_scope": "委内",
            "equity_group": group_key,
            "equity_group_display_label": display_label,
            "_equity_dashboard_order": order,
        }
        for order, (group_key, display_label) in enumerate(EQUITY_DASHBOARD_INTERNAL_GROUPS)
    ]
    template_rows.extend(
        {
            "equity_scope": "委外",
            "equity_group": strategy_book,
            "equity_group_display_label": OUTSOURCED_EQUITY_DASHBOARD_LABELS[strategy_book],
            "_equity_dashboard_order": order,
        }
        for order, strategy_book in enumerate(
            OUTSOURCED_EQUITY_DASHBOARD_ORDER,
            start=len(EQUITY_DASHBOARD_INTERNAL_GROUPS),
        )
    )
    template = pd.DataFrame(template_rows)
    result = template.merge(summary, on=group_cols, how="left")

    for column in [
        "full_market_value_current",
        "finance_income_mtd_current",
        "comprehensive_income_mtd_current",
        "avg_capital_mtd_current",
        "record_count_current",
    ]:
        result[column] = pd.to_numeric(result[column], errors="coerce").fillna(0.0)
    result["record_count_current"] = result["record_count_current"].astype(int)
    for column in ["finance_return_mtd", "comprehensive_return_mtd"]:
        result[column] = pd.to_numeric(result[column], errors="coerce")

    return result.sort_values("_equity_dashboard_order").reset_index(drop=True)

def strategy_book_summary(data: pd.DataFrame, current_month: str, comparison_mode: str) -> pd.DataFrame:
    current = _current_strategy_slice(data, current_month)
    current = current[current["strategy_book"].isin(STRATEGY_BOOK_ORDER)].copy()
    summary = _aggregate_current(current, ["strategy_book"], comparison_mode)

    if summary.empty:
        summary = pd.DataFrame({"strategy_book": STRATEGY_BOOK_ORDER})
    else:
        summary = (
            summary.set_index("strategy_book")
            .reindex(STRATEGY_BOOK_ORDER)
            .rename_axis("strategy_book")
            .reset_index()
        )

    numeric_defaults = {
        "full_market_value_current": 0.0,
        "finance_income_mtd_current": 0.0,
        "comprehensive_income_mtd_current": 0.0,
        "avg_capital_mtd_current": 0.0,
        "record_count_current": 0,
    }
    for column, default in numeric_defaults.items():
        if column not in summary.columns:
            summary[column] = default
        summary[column] = pd.to_numeric(summary[column], errors="coerce").fillna(default)

    for column in ["finance_return_mtd", "comprehensive_return_mtd"]:
        if column not in summary.columns:
            summary[column] = np.nan
    summary["strategy_book_scope"] = summary["strategy_book"].map(strategy_book_scope)
    summary["strategy_book_display_label"] = summary["strategy_book"].map(strategy_book_display_label)
    summary["_strategy_book_order"] = summary["strategy_book"].map(
        {label: index for index, label in enumerate(STRATEGY_BOOK_ORDER)}
    )
    return summary.sort_values("_strategy_book_order").drop(columns=["_strategy_book_order"])


def strategy_book_detail_summary(data: pd.DataFrame, current_month: str, comparison_mode: str) -> pd.DataFrame:
    current = _current_strategy_slice(data, current_month)
    current = current[current["strategy_book"].isin(STRATEGY_BOOK_ORDER)].copy()
    detail = _aggregate_current(
        current,
        ["strategy_book_scope", "strategy_book", "strategy_book_section", "strategy_book_item"],
        comparison_mode,
    )
    if detail.empty:
        return detail

    detail["_strategy_book_order"] = detail["strategy_book"].map(
        {label: index for index, label in enumerate(STRATEGY_BOOK_ORDER)}
    )
    detail["_section_order"] = detail["strategy_book_section"].map(SECTION_ORDER).fillna(9)
    detail["_item_order"] = detail["strategy_book_item"].map(ITEM_ORDER).fillna(99)
    return detail.sort_values(
        ["_strategy_book_order", "_section_order", "_item_order", "full_market_value_current"],
        ascending=[True, True, True, False],
    ).drop(columns=["_strategy_book_order", "_section_order", "_item_order"])


def excluded_strategy_book_detail(data: pd.DataFrame, current_month: str, comparison_mode: str) -> pd.DataFrame:
    current = _current_strategy_slice(data, current_month)
    excluded = current[current["strategy_book"] == EXCLUDED_STRATEGY_BOOK].copy()
    detail = _aggregate_current(
        excluded,
        [
            "strategy_book_exclusion_reason",
            "mandate_type",
            "fund_book_name",
            "asset_major_class",
            "trade_strategy",
            "asset_class_level_1",
            "asset_class_level_2",
            "asset_class",
        ],
        comparison_mode,
    )
    if detail.empty:
        return detail
    detail["_abs_market_value"] = detail["full_market_value_current"].abs()
    return detail.sort_values("_abs_market_value", ascending=False).drop(columns=["_abs_market_value"])