from __future__ import annotations

import ast
import io
import math
import re
from dataclasses import dataclass, field
from typing import Any

import numpy as np
import pandas as pd
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment, Font, PatternFill, Side, Border
from openpyxl.utils import get_column_letter


TRUE_VALUES = {"是", "启用", "有效", "Y", "YES", "TRUE", "1", "ON"}
FALSE_VALUES = {"否", "不启用", "无效", "N", "NO", "FALSE", "0", "OFF"}

CATEGORY_ALIASES = {
    "当前净值": "current_value",
    "当前市值": "current_value",
    "核心规模": "current_value",
    "投资规模": "current_value",
    "累计损益": "ytd",
    "年累计损益": "ytd",
    "当日损益": "daily",
    "每日损益": "daily",
}

ACTION_ALIASES = {
    "剔除直接债券": "exclude_direct_bond",
    "直接债券剔除": "exclude_direct_bond",
    "剔除银行理财": "exclude_bank",
    "剔除银行理财产品": "exclude_bank",
    "剔除货币基金": "exclude_money",
    "货币基金剔除": "exclude_money",
    "未匹配置0": "zero_unmatched",
    "未匹配置零": "zero_unmatched",
    "按规则置0": "zero_unmatched",
    "按规则置零": "zero_unmatched",
}

MATCH_ALIASES = {
    "包含": "contains",
    "等于": "equals",
    "开头是": "startswith",
    "以此开头": "startswith",
}

STRATEGY_ALIASES = {
    "口径策略名称": "strategy_name",
    "策略名称": "strategy_name",
    "当日损益来源优先级": "daily_source_priority",
    "每日损益来源优先级": "daily_source_priority",
    "标准O32当日口径": "standard_daily_mode",
    "O32当日口径": "standard_daily_mode",
    "表内当日口径": "embedded_daily_mode",
    "自包含表当日口径": "embedded_daily_mode",
    "累计损益默认口径": "ytd_default_mode",
    "年累计损益默认口径": "ytd_default_mode",
    "核心范围基金产品代码规则": "core_code_rule",
    "核心范围产品代码规则": "core_code_rule",
    "小额被剔除货币基金阈值": "small_money_fund_limit",
    "小额货基提示阈值": "small_money_fund_limit",
    "表内金额收益率差异容差": "embedded_amount_rate_tolerance",
    "表内金额重算差异容差": "embedded_amount_rate_tolerance",
}

SPECIAL_FORMULA_FIELDS = {
    "当前净值",
    "累计损益",
    "单位净值涨跌幅",
    "当日股票收益率",
    "当日债券收益率",
    "股票收益率",
    "债券收益率",
    "股票+债券收益率",
    "选定收益率",
    "表内当日损益金额",
    "表内当日收益率",
    "当日亏损金额",
}

EMAIL_PLACEHOLDERS = {
    "规模亿元",
    "当日盈亏方向",
    "当日损益亿元",
    "年累计盈亏方向",
    "年累计损益亿元",
    "风险提示",
    "当日损益占比",
}

DEFAULT_EMAIL_TEMPLATE = (
    "市场风险方面，兴全基金预估跟投产品的自有资金合计投资规模约{规模亿元}亿元，"
    "当日预计{当日盈亏方向}约{当日损益亿元}亿元，"
    "预计年累计{年累计盈亏方向}约{年累计损益亿元}亿元，{风险提示}"
    "信用风险方面，当日自有资金持仓信用债主体的资质较为稳定，未发现重大负面舆情。"
    "总体来看自有资金整体投资较为稳健，能应对当前的市场下跌风险。"
)


@dataclass
class ConfigMessage:
    level: str
    sheet: str
    row: int | None
    message: str


@dataclass
class FormulaRule:
    category: str
    name: str
    formula: str
    enabled: bool
    priority: int
    note: str = ""
    row_no: int | None = None


@dataclass
class KeywordRule:
    action: str
    field_name: str
    match_type: str
    keyword: str
    enabled: bool
    priority: int
    note: str = ""
    row_no: int | None = None


@dataclass
class BusinessConfig:
    source_name: str = "系统内置默认口径"
    enabled: bool = False
    field_synonyms: dict[str, list[str]] = field(default_factory=dict)
    formulas: list[FormulaRule] = field(default_factory=list)
    keyword_rules: list[KeywordRule] = field(default_factory=list)
    strategy: dict[str, str] = field(default_factory=dict)
    email_template: str | None = None
    messages: list[ConfigMessage] = field(default_factory=list)
    tables: dict[str, pd.DataFrame] = field(default_factory=dict)
    provided_sheets: set[str] = field(default_factory=set)

    @property
    def errors(self) -> list[ConfigMessage]:
        return [m for m in self.messages if m.level == "错误"]

    @property
    def valid(self) -> bool:
        return not self.errors

    def enabled_formulas(self, category: str) -> list[FormulaRule]:
        rows = [f for f in self.formulas if f.enabled and f.category == category]
        return sorted(rows, key=lambda item: item.priority)

    def enabled_keyword_rules(self, action: str) -> list[KeywordRule]:
        rows = [r for r in self.keyword_rules if r.enabled and r.action == action]
        return sorted(rows, key=lambda item: item.priority)


def clean_text(value: Any) -> str:
    if value is None:
        return ""
    try:
        if pd.isna(value):
            return ""
    except Exception:
        pass
    text = str(value).strip().replace("\u3000", " ")
    return re.sub(r"\s+", " ", text)


def truthy(value: Any, default: bool = True) -> bool:
    text = clean_text(value).upper()
    if not text:
        return default
    if text in TRUE_VALUES:
        return True
    if text in FALSE_VALUES:
        return False
    return default


def _col(df: pd.DataFrame, *names: str) -> str | None:
    normalized = {clean_text(c).replace(" ", ""): c for c in df.columns}
    for name in names:
        key = clean_text(name).replace(" ", "")
        if key in normalized:
            return normalized[key]
    return None


def _blank_filled(df: pd.DataFrame) -> pd.DataFrame:
    return df.astype(object).where(pd.notna(df), "")


def normalize_category(value: Any) -> str:
    text = clean_text(value)
    return CATEGORY_ALIASES.get(text, "")


def normalize_action(value: Any) -> str:
    text = clean_text(value)
    return ACTION_ALIASES.get(text, "")


def normalize_match(value: Any) -> str:
    text = clean_text(value)
    return MATCH_ALIASES.get(text, "")


def normalize_strategy_key(value: Any) -> str:
    text = clean_text(value)
    return STRATEGY_ALIASES.get(text, "")


def extract_formula_refs(formula: str) -> list[str]:
    return [clean_text(v) for v in re.findall(r"\[([^\[\]]+)\]", clean_text(formula))]


def _replace_formula_refs(formula: str, variables: dict[str, Any]) -> tuple[str, dict[str, Any]]:
    env: dict[str, Any] = {}
    counter = 0

    def repl(match: re.Match[str]) -> str:
        nonlocal counter
        ref = clean_text(match.group(1))
        if ref not in variables:
            raise ValueError(f"公式中 [{ref}] 不是当前数据中可用的字段")
        name = f"v{counter}"
        counter += 1
        env[name] = variables[ref]
        return name

    expr = re.sub(r"\[([^\[\]]+)\]", repl, clean_text(formula))
    expr = expr.replace("×", "*").replace("÷", "/").replace("，", ",")
    return expr, env


def _eval_ast(node: ast.AST, env: dict[str, Any]) -> Any:
    if isinstance(node, ast.Expression):
        return _eval_ast(node.body, env)
    if isinstance(node, ast.BinOp):
        left = _eval_ast(node.left, env)
        right = _eval_ast(node.right, env)
        if isinstance(node.op, ast.Add):
            return left + right
        if isinstance(node.op, ast.Sub):
            return left - right
        if isinstance(node.op, ast.Mult):
            return left * right
        if isinstance(node.op, ast.Div):
            return left / right
        raise ValueError("公式只支持 + - * / 四种运算")
    if isinstance(node, ast.UnaryOp):
        value = _eval_ast(node.operand, env)
        if isinstance(node.op, ast.UAdd):
            return value
        if isinstance(node.op, ast.USub):
            return -value
        raise ValueError("公式中存在不支持的符号")
    if isinstance(node, ast.Call):
        if isinstance(node.func, ast.Name) and node.func.id == "abs" and len(node.args) == 1 and not node.keywords:
            value = _eval_ast(node.args[0], env)
            return value.abs() if isinstance(value, pd.Series) else abs(value)
        raise ValueError("公式只支持 abs(...) 函数")
    if isinstance(node, ast.Constant) and isinstance(node.value, (int, float)):
        return float(node.value)
    if isinstance(node, ast.Name):
        if node.id not in env:
            raise ValueError(f"公式中存在无法识别的内容“{node.id}”")
        return env[node.id]
    raise ValueError("公式只支持数字、字段、括号、abs(...) 和 + - * / 运算")


def evaluate_formula(formula: str, variables: dict[str, Any], index: pd.Index) -> pd.Series:
    expr, env = _replace_formula_refs(formula, variables)
    try:
        tree = ast.parse(expr, mode="eval")
    except SyntaxError as exc:
        raise ValueError("公式格式不正确，请检查括号或运算符") from exc
    result = _eval_ast(tree, env)
    if isinstance(result, pd.Series):
        values = pd.to_numeric(result.reindex(index), errors="coerce")
    else:
        values = pd.Series(float(result), index=index)
    values = values.replace([np.inf, -np.inf], np.nan)
    return values


def validate_formula_syntax(formula: str, allowed_refs: set[str]) -> str | None:
    text = clean_text(formula)
    if not text:
        return "公式不能为空"
    refs = extract_formula_refs(text)
    if not refs:
        return "公式里至少要有一个 [字段名称]"
    for ref in refs:
        if ref not in allowed_refs:
            return f"[{ref}] 不是系统能识别的字段，请改为字段识别表中的名称"
    try:
        env = {ref: pd.Series([1.0]) for ref in refs}
        evaluate_formula(text, env, pd.RangeIndex(1))
    except Exception as exc:
        return str(exc)
    return None


def parse_business_config(
    file_bytes: bytes,
    filename: str,
    field_labels: dict[str, str],
    field_synonyms: dict[str, list[str]],
) -> BusinessConfig:
    config = BusinessConfig(source_name=filename, enabled=True)
    label_to_field = {v: k for k, v in field_labels.items()}
    label_to_field.update({k: k for k in field_labels})
    allowed_formula_refs = set(field_labels.values()) | set(field_labels.keys()) | SPECIAL_FORMULA_FIELDS
    for aliases in field_synonyms.values():
        allowed_formula_refs.update(aliases)

    try:
        sheets = pd.read_excel(io.BytesIO(file_bytes), sheet_name=None, dtype=object, engine="openpyxl")
    except Exception as exc:
        config.messages.append(ConfigMessage("错误", "文件", None, f"配置文件读取失败：{exc}"))
        return config

    config.tables = {name: _blank_filled(df) for name, df in sheets.items()}
    config.provided_sheets = set(sheets)

    if "基础设置" in sheets:
        df = _blank_filled(sheets["基础设置"])
        key_col = _col(df, "配置项", "项目")
        value_col = _col(df, "值", "配置值")
        if key_col and value_col:
            for idx, row in df.iterrows():
                key = clean_text(row.get(key_col))
                if key == "启用自定义配置":
                    config.enabled = truthy(row.get(value_col), default=True)
        else:
            config.messages.append(ConfigMessage("提示", "基础设置", None, "未找到“配置项/值”两列，已按启用配置处理。"))

    if "字段识别" in sheets:
        df = _blank_filled(sheets["字段识别"])
        field_col = _col(df, "字段名称", "字段", "系统字段")
        alias_col = _col(df, "字段别名", "别名", "源表列名")
        enabled_col = _col(df, "生效", "启用")
        if not field_col or not alias_col:
            config.messages.append(ConfigMessage("错误", "字段识别", None, "需要包含“字段名称”和“字段别名”两列。"))
        else:
            for idx, row in df.iterrows():
                row_no = idx + 2
                enabled = truthy(row.get(enabled_col), default=True) if enabled_col else True
                if not enabled:
                    continue
                field_name = clean_text(row.get(field_col))
                alias = clean_text(row.get(alias_col))
                if not field_name and not alias:
                    continue
                field_key = label_to_field.get(field_name)
                if not field_key:
                    config.messages.append(ConfigMessage("错误", "字段识别", row_no, f"字段名称“{field_name}”不是系统字段。"))
                    continue
                if not alias:
                    config.messages.append(ConfigMessage("错误", "字段识别", row_no, "字段别名不能为空。"))
                    continue
                config.field_synonyms.setdefault(field_key, [])
                if alias not in config.field_synonyms[field_key]:
                    config.field_synonyms[field_key].append(alias)

    if "公式口径" in sheets:
        for aliases in config.field_synonyms.values():
            allowed_formula_refs.update(aliases)
        df = _blank_filled(sheets["公式口径"])
        category_col = _col(df, "公式类型", "类型")
        name_col = _col(df, "口径名称", "名称")
        formula_col = _col(df, "公式")
        enabled_col = _col(df, "生效", "启用")
        priority_col = _col(df, "优先级", "顺序")
        note_col = _col(df, "说明", "备注")
        required = [category_col, name_col, formula_col]
        if not all(required):
            config.messages.append(ConfigMessage("错误", "公式口径", None, "需要包含“公式类型、口径名称、公式”三列。"))
        else:
            seen: set[tuple[str, str]] = set()
            for idx, row in df.iterrows():
                row_no = idx + 2
                enabled = truthy(row.get(enabled_col), default=True) if enabled_col else True
                category = normalize_category(row.get(category_col))
                name = clean_text(row.get(name_col))
                formula = clean_text(row.get(formula_col))
                if not any([clean_text(row.get(category_col)), name, formula]):
                    continue
                if not category:
                    config.messages.append(ConfigMessage("错误", "公式口径", row_no, "公式类型必须是“当前净值、累计损益、当日损益”之一。"))
                    continue
                if not name:
                    config.messages.append(ConfigMessage("错误", "公式口径", row_no, "口径名称不能为空。"))
                    continue
                if enabled:
                    key = (category, name)
                    if key in seen:
                        config.messages.append(ConfigMessage("错误", "公式口径", row_no, f"口径名称“{name}”重复。"))
                    seen.add(key)
                    err = validate_formula_syntax(formula, allowed_formula_refs)
                    if err:
                        config.messages.append(ConfigMessage("错误", "公式口径", row_no, err))
                try:
                    priority = int(float(row.get(priority_col))) if priority_col and clean_text(row.get(priority_col)) else row_no
                except Exception:
                    priority = row_no
                config.formulas.append(
                    FormulaRule(
                        category=category,
                        name=name,
                        formula=formula,
                        enabled=enabled,
                        priority=priority,
                        note=clean_text(row.get(note_col)) if note_col else "",
                        row_no=row_no,
                    )
                )

    if "口径策略" in sheets:
        df = _blank_filled(sheets["口径策略"])
        key_col = _col(df, "策略项", "配置项", "项目")
        value_col = _col(df, "值", "配置值")
        enabled_col = _col(df, "生效", "启用")
        if not key_col or not value_col:
            config.messages.append(ConfigMessage("错误", "口径策略", None, "需要包含“策略项”和“值”两列。"))
        else:
            for idx, row in df.iterrows():
                row_no = idx + 2
                enabled = truthy(row.get(enabled_col), default=True) if enabled_col else True
                if not enabled:
                    continue
                raw_key = clean_text(row.get(key_col))
                value = clean_text(row.get(value_col))
                if not raw_key and not value:
                    continue
                key = normalize_strategy_key(raw_key)
                if not key:
                    config.messages.append(ConfigMessage("错误", "口径策略", row_no, f"策略项“{raw_key}”无法识别。"))
                    continue
                if not value:
                    config.messages.append(ConfigMessage("错误", "口径策略", row_no, "值不能为空。"))
                    continue
                config.strategy[key] = value

    if "关键词规则" in sheets:
        df = _blank_filled(sheets["关键词规则"])
        action_col = _col(df, "规则动作", "动作")
        field_col = _col(df, "字段", "匹配字段")
        match_col = _col(df, "匹配方式", "方式")
        keyword_col = _col(df, "关键词")
        enabled_col = _col(df, "生效", "启用")
        priority_col = _col(df, "优先级", "顺序")
        note_col = _col(df, "说明", "备注")
        if not all([action_col, field_col, match_col, keyword_col]):
            config.messages.append(ConfigMessage("错误", "关键词规则", None, "需要包含“规则动作、字段、匹配方式、关键词”四列。"))
        else:
            for idx, row in df.iterrows():
                row_no = idx + 2
                raw_action = clean_text(row.get(action_col))
                field_name = clean_text(row.get(field_col))
                raw_match = clean_text(row.get(match_col))
                keyword = clean_text(row.get(keyword_col))
                if not any([raw_action, field_name, raw_match, keyword]):
                    continue
                enabled = truthy(row.get(enabled_col), default=True) if enabled_col else True
                action = normalize_action(raw_action)
                match_type = normalize_match(raw_match)
                if enabled and not action:
                    config.messages.append(ConfigMessage("错误", "关键词规则", row_no, f"规则动作“{raw_action}”无法识别。"))
                if enabled and not match_type:
                    config.messages.append(ConfigMessage("错误", "关键词规则", row_no, f"匹配方式“{raw_match}”无法识别，只支持包含、等于、开头是。"))
                if enabled and not field_name:
                    config.messages.append(ConfigMessage("错误", "关键词规则", row_no, "字段不能为空。"))
                if enabled and not keyword:
                    config.messages.append(ConfigMessage("错误", "关键词规则", row_no, "关键词不能为空。"))
                try:
                    priority = int(float(row.get(priority_col))) if priority_col and clean_text(row.get(priority_col)) else row_no
                except Exception:
                    priority = row_no
                config.keyword_rules.append(
                    KeywordRule(
                        action=action,
                        field_name=field_name,
                        match_type=match_type,
                        keyword=keyword,
                        enabled=enabled,
                        priority=priority,
                        note=clean_text(row.get(note_col)) if note_col else "",
                        row_no=row_no,
                    )
                )

    if "邮件模板" in sheets:
        df = _blank_filled(sheets["邮件模板"])
        content_col = _col(df, "模板内容", "内容", "邮件正文")
        enabled_col = _col(df, "生效", "启用")
        if content_col:
            for idx, row in df.iterrows():
                enabled = truthy(row.get(enabled_col), default=True) if enabled_col else True
                if not enabled:
                    continue
                content = clean_text(row.get(content_col))
                if content:
                    placeholders = set(re.findall(r"\{([^{}]+)\}", content))
                    unknown = placeholders - EMAIL_PLACEHOLDERS
                    if unknown:
                        config.messages.append(
                            ConfigMessage("错误", "邮件模板", idx + 2, f"模板中存在不能识别的占位符：{', '.join(sorted(unknown))}")
                        )
                    config.email_template = content
                    break
        else:
            config.messages.append(ConfigMessage("错误", "邮件模板", None, "需要包含“模板内容”列。"))

    if not config.messages:
        config.messages.append(ConfigMessage("通过", "配置校验", None, "配置文件已通过校验。"))
    return config


def render_email_template(
    template: str,
    scale: float,
    daily_pnl: float,
    ytd_pnl: float,
    scale_decimals: int,
) -> str:
    daily_word = "盈利" if daily_pnl >= 0 else "亏损"
    ytd_word = "盈利" if ytd_pnl >= 0 else "亏损"
    ratio = abs(daily_pnl) / abs(scale) if scale else math.nan
    if daily_pnl < 0 and (math.isnan(ratio) or ratio <= 0.02):
        tail = "当日亏损金额可控，占总投资规模比例较小。"
    elif daily_pnl < 0:
        tail = "当日亏损占总投资规模比例相对较高，需持续关注市场风险。"
    else:
        tail = "当日整体实现盈利，市场风险敞口处于可控范围。"
    values = {
        "规模亿元": f"{scale / 1e8:.{scale_decimals}f}",
        "当日盈亏方向": daily_word,
        "当日损益亿元": f"{abs(daily_pnl) / 1e8:.3f}",
        "年累计盈亏方向": ytd_word,
        "年累计损益亿元": f"{abs(ytd_pnl) / 1e8:.3f}",
        "风险提示": tail,
        "当日损益占比": "" if math.isnan(ratio) else f"{ratio:.2%}",
    }
    return template.format_map(values)


def config_validation_frame(config: BusinessConfig | None) -> pd.DataFrame:
    if config is None:
        return pd.DataFrame([{"级别": "通过", "工作表": "系统默认", "行号": "", "说明": "未上传配置，使用系统内置 v2.0 默认口径。"}])
    return pd.DataFrame(
        [
            {"级别": m.level, "工作表": m.sheet, "行号": "" if m.row is None else m.row, "说明": m.message}
            for m in config.messages
        ]
    )


def config_audit_frame(config: BusinessConfig | None) -> pd.DataFrame:
    if config is None:
        return pd.DataFrame([{"配置来源": "系统内置默认口径", "说明": "未上传口径配置.xlsx，使用 v2.0 内置默认规则。"}])
    rows: list[dict[str, Any]] = []
    for sheet_name, df in config.tables.items():
        for idx, row in _blank_filled(df).iterrows():
            item = {"配置来源": config.source_name, "工作表": sheet_name, "行号": idx + 2}
            for col in df.columns:
                item[clean_text(col) or "未命名列"] = row.get(col)
            rows.append(item)
    if not rows:
        rows.append({"配置来源": config.source_name, "说明": "配置文件未读取到可留痕的内容。"})
    return pd.DataFrame(rows)


def _append_rows(ws, rows: list[list[Any]]) -> None:
    for row in rows:
        ws.append(row)


def _style_workbook(wb: Workbook) -> None:
    header_fill = PatternFill("solid", fgColor="E7E6E6")
    thin = Side(style="thin", color="D9D9D9")
    for ws in wb.worksheets:
        ws.freeze_panes = "A2"
        for cell in ws[1]:
            cell.font = Font(bold=True)
            cell.fill = header_fill
            cell.alignment = Alignment(horizontal="center", vertical="center")
        for row in ws.iter_rows():
            for cell in row:
                cell.border = Border(bottom=thin)
                cell.alignment = Alignment(vertical="center", wrap_text=True)
        for col_idx, column in enumerate(ws.columns, start=1):
            width = 12
            for cell in column:
                value = clean_text(cell.value)
                width = max(width, min(55, len(value) * 1.2 + 2))
            ws.column_dimensions[get_column_letter(col_idx)].width = width


def create_default_config_workbook(field_labels: dict[str, str], field_synonyms: dict[str, list[str]]) -> bytes:
    wb = Workbook()
    ws = wb.active
    ws.title = "使用说明"
    _append_rows(
        ws,
        [
            ["说明", "内容"],
            ["怎么改", "只改黄色/白色数据行，不需要打开代码。生效列填“是”才会启用。"],
            ["公式写法", "用中文字段名放在方括号里，例如：[当前净值] * [当日股票收益率]。只支持 + - * / × ÷、abs(...) 和括号。"],
            ["字段别名", "如果源表列名变化，在“字段识别”增加别名即可，例如 当前净值 / 最新市值。"],
            ["关键词规则", "支持包含、等于、开头是。规则动作支持剔除直接债券、剔除银行理财、未匹配置0。基金产品代码非六位由系统默认规则处理。"],
            ["上线前检查", "上传配置后先看预览差异，确认无误后再生成正式邮件和结果表。"],
        ],
    )

    ws = wb.create_sheet("基础设置")
    _append_rows(
        ws,
        [
            ["配置项", "值", "说明"],
            ["启用自定义配置", "是", "填“否”时，上传了配置也按系统默认口径运行。"],
        ],
    )

    ws = wb.create_sheet("字段识别")
    _append_rows(ws, [["角色", "字段名称", "字段别名", "生效", "说明"]])
    role_map = {
        "holding": "持仓主表",
        "o32": "O32每日收益率表",
        "dimension": "产品信息表",
    }
    for field_key, aliases in field_synonyms.items():
        label = field_labels.get(field_key, field_key)
        role = "持仓主表"
        if field_key.startswith("o32_") or field_key == "date":
            role = role_map["o32"]
        elif field_key.startswith("dim_"):
            role = role_map["dimension"]
        for alias in aliases:
            ws.append([role, label, alias, "是", "系统默认别名，可按需要新增行。"])

    ws = wb.create_sheet("公式口径")
    _append_rows(
        ws,
        [
            ["公式类型", "口径名称", "公式", "生效", "优先级", "说明"],
            ["当前净值", "直接使用当前净值", "[当前净值]", "是", 10, "与 v2.0 默认一致。"],
            ["当前净值", "本年投入规模 + 年累计浮盈变化", "[本年投入规模] + [年累计浮盈变化]", "是", 20, "没有当前净值列时使用。"],
            ["当前净值", "当前成本 + 当前浮盈", "[当前成本] + [当前浮盈]", "是", 30, ""],
            [
                "当前净值",
                "投入规模 - 上年末市值 + 上年末本金 + 当前浮盈",
                "[本年投入规模] - [上年末市值] + [上年末投入本金] + [当前浮盈]",
                "是",
                40,
                "",
            ],
            ["累计损益", "浮盈@上年末 + 当日亏损金额", "[上年末浮盈]", "是", 10, "系统内置默认会自动加上本次最终当日损益；缺少上年末浮盈时直接报错。"],
            ["累计损益", "上年末市值 - 本年投入规模", "[上年末市值] - [本年投入规模]", "是", 20, "历史核对口径。"],
            ["累计损益", "年内已实现及未实现投资收益", "[年内已实现及未实现投资收益]", "是", 30, "历史口径，保留作核对。"],
            ["累计损益", "年累计浮盈变化 + 年累计投资损益", "[年累计浮盈变化] + [年累计投资损益]", "是", 40, ""],
            ["累计损益", "仅年累计投资损益", "[年累计投资损益]", "是", 50, ""],
            ["累计损益", "仅年累计浮盈变化", "[年累计浮盈变化]", "是", 60, ""],
            [
                "累计损益",
                "当前浮盈 - 上年末浮盈 + 年累计投资损益",
                "[当前浮盈] - [上年末浮盈] + [年累计投资损益]",
                "是",
                70,
                "",
            ],
            [
                "累计损益",
                "仅已实现损益（总收益-浮盈变化推导）",
                "[年内已实现及未实现投资收益] - [年累计浮盈变化]",
                "是",
                80,
                "",
            ],
            ["当日损益", "当前净值 × 当日股票收益率", "[当前净值] * [当日股票收益率]", "是", 10, "当前默认老师口径。"],
        ],
    )

    ws = wb.create_sheet("口径策略")
    _append_rows(
        ws,
        [
            ["策略项", "值", "生效", "说明"],
            ["口径策略名称", "老师口径", "是", "用于结果留痕。"],
            [
                "当日损益来源优先级",
                "标准O32股票收益率 > 表内股票收益率重算 > 表内金额",
                "是",
                "没有标准O32时，优先使用表内股票收益率重算，金额仅作校验或兜底。",
            ],
            ["标准O32当日口径", "当日股票收益率", "是", "单位净值涨跌幅和债券收益率仅保留为候选核对。"],
            ["表内当日口径", "股票收益率优先，金额校验，金额兜底", "是", "有股票收益率时用当前净值×表内股票收益率。"],
            ["累计损益默认口径", "浮盈@上年末 + 当日亏损金额", "是", "使用浮盈@上年末之和 + 本次最终当日损益之和，缺少默认字段时报错，不自动降级。"],
            ["核心范围基金产品代码规则", "剔除基金产品代码非六位", "是", "优先用基金代码判断；没有基金代码时回退初始基金代码/产品代码。"],
            ["表内金额收益率差异容差", 0.01, "是", "金额与收益率重算差异超过1%时提示低置信度。"],
        ],
    )

    ws = wb.create_sheet("关键词规则")
    _append_rows(
        ws,
        [
            ["规则动作", "字段", "匹配方式", "关键词", "生效", "优先级", "说明"],
            ["剔除直接债券", "产品类别", "等于", "债券", "是", 10, "与 v2.0 默认一致。"],
            ["剔除直接债券", "报表类型", "开头是", "债券-", "是", 20, "与 v2.0 默认一致。"],
            ["剔除银行理财", "任一分类字段", "包含", "银行理财", "是", 10, ""],
        ],
    )

    ws = wb.create_sheet("邮件模板")
    _append_rows(
        ws,
        [
            ["模板名称", "模板内容", "生效", "说明"],
            [
                "默认邮件",
                DEFAULT_EMAIL_TEMPLATE,
                "是",
                "可用占位符：{规模亿元}、{当日盈亏方向}、{当日损益亿元}、{年累计盈亏方向}、{年累计损益亿元}、{风险提示}、{当日损益占比}",
            ],
        ],
    )

    ws = wb.create_sheet("校验说明")
    _append_rows(
        ws,
        [
            ["检查项", "说明"],
            ["公式字段", "方括号里的字段必须是字段识别表中的字段名称，或系统支持的特殊字段。"],
            ["规则动作", "只能使用：剔除直接债券、剔除银行理财、未匹配置0。基金产品代码非六位由系统默认规则处理。"],
            ["匹配方式", "只能使用：包含、等于、开头是。"],
            ["结果留痕", "正式结果 Excel 会自动保存本次配置、配置校验结果和与默认口径差异。"],
        ],
    )

    _style_workbook(wb)
    output = io.BytesIO()
    wb.save(output)
    return output.getvalue()


def normalize_config_workbook(file_bytes: bytes) -> bytes:
    """Re-save uploaded config so openpyxl can read files created by strict Excel clones."""
    wb = load_workbook(io.BytesIO(file_bytes))
    output = io.BytesIO()
    wb.save(output)
    return output.getvalue()
