#!/usr/bin/env python3
"""行业景气轮动(分析师修正) 完整回测 —— /reports/industry-revision 的可复现脚本。

规则(与 /macro 行业景气轮动卡、gen_industry_revision.py 同口径):
  信号(月末 t): 个股 rev = mean(近90日 报告净利预测 np) / mean(前90~270日 np) − 1
               行业信号 = 成分股 rev 的中位数(申万一级, sw_member 当前成分)
  组合: 买信号最高的 top5 行业(行业=成分股等权), 行业间等权, 月度调仓
  成本: 默认每次换手按单边 bps 计(行业可用 ETF 实现, 10-20bps 现实)
数据: data/fundamental_cache.db (report_rc 130万条 + sw_member + daily 1100万行)
运行: cd /root/cb-allotment && python3 my-app/public/reports/industry-revision/backtest.py
输出: 同目录 backtest_result.json(供报告页数字引用) + stdout 摘要
"""
import json
import os
import sqlite3

import numpy as np
import pandas as pd

ROOT = "/root/cb-allotment"
OUT = os.path.join(os.path.dirname(os.path.abspath(__file__)), "backtest_result.json")
COST_BPS_SIDE = 15  # 单边成本(基点): 行业ETF口径; 敏感性另给 0/10/20/30

conn = sqlite3.connect(f"{ROOT}/data/fundamental_cache.db")
rc = pd.read_sql("SELECT report_date,ts_code,np FROM report_rc WHERE np IS NOT NULL", conn)
sw = pd.read_sql("SELECT l1_name,ts_code FROM sw_member", conn)
dd = pd.read_sql("SELECT trade_date,ts_code,pct_chg FROM daily WHERE trade_date>='20180101'", conn)
sb = pd.read_sql("SELECT ts_code,name FROM stock_basic", conn).set_index("ts_code")
conn.close()

# ---- 行业月收益(成分股等权) ----
dd["dt"] = pd.to_datetime(dd.trade_date, format="%Y%m%d")
r = dd.pivot(index="dt", columns="ts_code", values="pct_chg").sort_index() / 100.0
r = r.clip(-0.11, 0.21)
st = [x for x in sb.index[sb.name.fillna("").str.contains("ST")] if x in r.columns]
r = r.drop(columns=list(set(st)))
mo = (1 + r).resample("ME").prod() - 1
cnt = r.resample("ME").count()
mo = mo.where(cnt >= 5)
ind_ret = {}
for ind, g in sw.groupby("l1_name"):
    cols = [x for x in g.ts_code.unique() if x in mo.columns]
    if len(cols) >= 3:
        ind_ret[ind] = mo[cols].mean(axis=1)
IND = pd.DataFrame(ind_ret)  # 月 x 31行业
s2i = sw.drop_duplicates("ts_code").set_index("ts_code").l1_name

# ---- 行业修正信号面板 ----
rc["dt"] = pd.to_datetime(rc.report_date, format="%Y%m%d")
rc = rc.sort_values("dt")


def industry_signal(t: pd.Timestamp) -> pd.Series:
    rec = rc[(rc.dt <= t) & (rc.dt > t - pd.Timedelta(days=90))].groupby("ts_code").np.mean()
    pre = rc[(rc.dt <= t - pd.Timedelta(days=90)) & (rc.dt > t - pd.Timedelta(days=270))].groupby("ts_code").np.mean()
    rev = (rec / pre.reindex(rec.index) - 1).replace([np.inf, -np.inf], np.nan).dropna()
    return rev.groupby(s2i.reindex(rev.index)).median().dropna()


# ---- 回测主循环 ----
months = IND.index[(IND.index >= "2019-01-31") & (IND.index < IND.index.max())]
fwd = IND.shift(-1)
log_rows = []
prev_top: set = set()
hold_start: dict = {}
durations: list = []
nav_top = [1.0]; nav_bm = [1.0]; nav_bot = [1.0]; nav_net = {b: [1.0] for b in (0, 10, 15, 20, 30)}
nav_dates = []
for t in months:
    sig = industry_signal(t)
    sig = sig[[i for i in sig.index if i in IND.columns]]
    if len(sig) < 15 or t not in fwd.index:
        continue
    top5 = list(sig.nlargest(5).index)
    bot5 = list(sig.nsmallest(5).index)
    ret_top = fwd.loc[t, top5].mean()
    ret_bot = fwd.loc[t, bot5].mean()
    ret_bm = fwd.loc[t].mean()
    if pd.isna(ret_top) or pd.isna(ret_bm):
        continue
    # 换手: 5腿等权, 每换1腿=0.2权重单边
    changed = len(set(top5) - prev_top) if prev_top else 5
    turn_frac = changed / 5.0  # 单边换手占组合比例
    for b in nav_net:
        nav_net[b].append(nav_net[b][-1] * (1 + ret_top) * (1 - turn_frac * 2 * b / 10000))
    nav_top.append(nav_top[-1] * (1 + ret_top))
    nav_bm.append(nav_bm[-1] * (1 + ret_bm))
    nav_bot.append(nav_bot[-1] * (1 + ret_bot))
    nav_dates.append(str(t.date()))
    # 持有期统计
    for ind in set(top5) - prev_top:
        hold_start[ind] = t
    for ind in prev_top - set(top5):
        if ind in hold_start:
            durations.append(round((t - hold_start.pop(ind)).days / 30.4))
    log_rows.append({
        "date": str(t.date()), "top5": top5,
        "revs": [round(float(sig[i]) * 100, 1) for i in top5],
        "bot5": bot5, "changed": changed,
        "ret_top": round(float(ret_top) * 100, 2),
        "ret_bm": round(float(ret_bm) * 100, 2),
    })
    prev_top = set(top5)

n = len(nav_dates)
years = n / 12


def stats(nav):
    nav = np.array(nav)
    rets = nav[1:] / nav[:-1] - 1
    cagr = nav[-1] ** (1 / years) - 1
    shp = rets.mean() / rets.std() * np.sqrt(12) if rets.std() > 0 else 0
    mdd = float((nav / np.maximum.accumulate(nav) - 1).min())
    return round(cagr * 100, 1), round(shp, 2), round(mdd * 100, 1)


cagr_t, shp_t, mdd_t = stats(nav_top)
cagr_b, shp_b, mdd_b = stats(nav_bm)
cagr_bot, _, _ = stats(nav_bot)
df = pd.DataFrame(log_rows)
df["dt"] = pd.to_datetime(df.date)
df["ex"] = df.ret_top - df.ret_bm
tr = df[df.dt < "2022-01-01"]; te = df[df.dt >= "2022-01-01"]
ann_ex = lambda g: round(((1 + g.ret_top / 100).prod() ** (12 / len(g)) - (1 + g.ret_bm / 100).prod() ** (12 / len(g))) * 100, 1)
yearly = [{"year": int(y), "top": round(((1 + g.ret_top / 100).prod() - 1) * 100, 1),
           "bm": round(((1 + g.ret_bm / 100).prod() - 1) * 100, 1),
           "ex": round(((1 + g.ret_top / 100).prod() - (1 + g.ret_bm / 100).prod()) * 100, 1)}
          for y, g in df.groupby(df.dt.dt.year)]
turn = df.changed.mean()
result = {
    "as_of": nav_dates[-1], "n_months": n,
    "stats": {"top5": {"cagr": cagr_t, "sharpe": shp_t, "mdd": mdd_t},
              "benchmark": {"cagr": cagr_b, "sharpe": shp_b, "mdd": mdd_b},
              "bot5_cagr": cagr_bot,
              "excess": round(cagr_t - cagr_b, 1),
              "excess_train": ann_ex(tr), "excess_test": ann_ex(te),
              "monthly_ex_t": round(float(df.ex.mean() / df.ex.std() * np.sqrt(len(df))), 2),
              "monthly_win": round(float((df.ex > 0).mean()) * 100, 0)},
    "turnover": {"avg_changed_per_month": round(float(turn), 2),
                 "annual_oneway_pct": round(float(turn / 5 * 12) * 100, 0),
                 "avg_holding_months": round(float(np.mean(durations)), 1) if durations else None,
                 "median_holding_months": float(np.median(durations)) if durations else None,
                 "pct_months_no_change": round(float((df.changed == 0).mean()) * 100, 0)},
    "net_of_cost": {f"{b}bps": stats(nav_net[b])[0] for b in (0, 10, 15, 20, 30)},
    "yearly": yearly,
    "nav": {"dates": nav_dates,
            "top5": [round(v, 3) for v in nav_top[1:]],
            "bm": [round(v, 3) for v in nav_bm[1:]],
            "net15": [round(v, 3) for v in nav_net[15][1:]]},
    "holdings": log_rows,
}
json.dump(result, open(OUT, "w"), ensure_ascii=False, indent=1)
print(f"月数 {n} | top5 {cagr_t}%/{shp_t}/{mdd_t}% vs bm {cagr_b}%/{shp_b} | 超额 {result['stats']['excess']} (train {result['stats']['excess_train']}/test {result['stats']['excess_test']})")
print(f"换手: 月均换 {turn:.2f}/5 腿 | 年单边换手 {result['turnover']['annual_oneway_pct']}% | 平均持有 {result['turnover']['avg_holding_months']} 月 | {result['turnover']['pct_months_no_change']}% 的月份零调仓")
print(f"含成本 CAGR: {result['net_of_cost']}")
print(f"写入 {OUT}")