#!/usr/bin/env python
# coding=utf-8
"""
Step8: 月度详细对比分析 - 找出9月问题严重的原因
"""
import pandas as pd
import numpy as np
from pathlib import Path

output_dir = Path(__file__).parent / "output"

# 读取数据
df_jul = pd.read_csv(output_dir / "step8_月度对比_20250718_20250731.csv")
df_sep = pd.read_csv(output_dir / "step8_月度对比_20250901_20250930.csv")
df_dec = pd.read_csv(output_dir / "step8_月度对比_20251201_20251231.csv")

df_jul['month'] = '7月(基线)'
df_sep['month'] = '9月(问题)'
df_dec['month'] = '12月(改善)'

print("=" * 70)
print("Step8: 月度详细对比分析")
print("=" * 70)

# 1. 整体指标对比
print("\n【1. 整体指标对比】")
print(f"{'月份':<12} {'记录数':>8} {'vov均值':>10} {'str均值':>10} {'ros均值':>10} {'vor均值':>10}")
print("-" * 65)

for name, df in [('7月(基线)', df_jul), ('9月(问题)', df_sep), ('12月(改善)', df_dec)]:
    print(f"{name:<12} {len(df):>8} {df['vov0'].mean():>10.4f} {df['str_t0'].mean():>10.4f} "
          f"{df['ros_t0'].mean():>10.2f} {df['vor_t0'].mean():>10.2f}")

# 2. 问题视频分析（低vov高曝光）
print("\n【2. 问题视频分析】")
print("定义: vov < 0.35 且 rank <= 5")

for name, df in [('7月', df_jul), ('9月', df_sep), ('12月', df_dec)]:
    problem = df[(df['vov0'] < 0.35) & (df['曝光rank'] <= 5)]
    normal = df[~((df['vov0'] < 0.35) & (df['曝光rank'] <= 5))]

    print(f"\n{name}:")
    print(f"  问题视频: {len(problem)} 条 ({len(problem)/len(df)*100:.1f}%)")
    if len(problem) > 0:
        print(f"    - vov均值: {problem['vov0'].mean():.4f}")
        print(f"    - str均值: {problem['str_t0'].mean():.4f}")
        print(f"    - ros均值: {problem['ros_t0'].mean():.2f}")
        print(f"    - vor均值: {problem['vor_t0'].mean():.2f}")
    if len(normal) > 0:
        print(f"  正常视频: {len(normal)} 条")
        print(f"    - vov均值: {normal['vov0'].mean():.4f}")

# 3. 分解 vov = str × ros × vor，找差异来源
print("\n【3. vov 分解分析 (vov ≈ str × ros × vor)】")

def decompose_vov(df):
    return {
        'str': df['str_t0'].mean(),
        'ros': df['ros_t0'].mean(),
        'vor': df['vor_t0'].mean(),
        'vov': df['vov0'].mean(),
        'str*ros*vor': df['str_t0'].mean() * df['ros_t0'].mean() * df['vor_t0'].mean()
    }

jul_stats = decompose_vov(df_jul)
sep_stats = decompose_vov(df_sep)
dec_stats = decompose_vov(df_dec)

print(f"\n{'指标':<8} {'7月':>10} {'9月':>10} {'12月':>10} {'9月vs7月':>12} {'12月vs9月':>12}")
print("-" * 65)
for key in ['str', 'ros', 'vor', 'vov']:
    j, s, d = jul_stats[key], sep_stats[key], dec_stats[key]
    chg1 = (s - j) / j * 100 if j != 0 else 0
    chg2 = (d - s) / s * 100 if s != 0 else 0
    print(f"{key:<8} {j:>10.4f} {s:>10.4f} {d:>10.4f} {chg1:>+11.1f}% {chg2:>+11.1f}%")

# 4. 问题视频的具体分析
print("\n【4. 9月问题视频详情】")
sep_problem = df_sep[(df_sep['vov0'] < 0.35) & (df_sep['曝光rank'] <= 5)].copy()

# 按视频聚合
if len(sep_problem) > 0:
    vid_stats = sep_problem.groupby('vid').agg({
        'dt': 'count',
        'vov0': 'mean',
        'str_t0': 'mean',
        'ros_t0': 'mean',
        'vor_t0': 'mean',
        'exp': 'sum',
        '标题': 'first'
    }).round(4)
    vid_stats.columns = ['出现天数', 'vov均值', 'str', 'ros', 'vor', '总曝光', '标题']
    vid_stats = vid_stats.sort_values('出现天数', ascending=False)

    print(f"问题视频数: {len(vid_stats)}")
    print(f"\n出现最多的问题视频 Top5:")
    for i, (vid, row) in enumerate(vid_stats.head(5).iterrows()):
        print(f"  {i+1}. vid={vid}, 出现{int(row['出现天数'])}天")
        print(f"     vov={row['vov均值']:.4f}, str={row['str']:.4f}, ros={row['ros']:.2f}, vor={row['vor']:.2f}")
        print(f"     标题: {row['标题'][:30]}...")

# 5. 对比问题视频的特征
print("\n【5. 问题视频 vs 正常视频特征对比】")
df_all = pd.concat([df_jul, df_sep, df_dec])
df_all['is_problem'] = (df_all['vov0'] < 0.35) & (df_all['曝光rank'] <= 5)

problem_df = df_all[df_all['is_problem']]
normal_df = df_all[~df_all['is_problem']]

print(f"\n{'特征':<12} {'问题视频':>12} {'正常视频':>12} {'差异':>12}")
print("-" * 50)
for col, name in [('str_t0', 'str'), ('ros_t0', 'ros'), ('vor_t0', 'vor')]:
    p_val = problem_df[col].mean()
    n_val = normal_df[col].mean()
    diff = (p_val - n_val) / n_val * 100 if n_val != 0 else 0
    print(f"{name:<12} {p_val:>12.4f} {n_val:>12.4f} {diff:>+11.1f}%")

# 6. 结论
print("\n" + "=" * 70)
print("【Step8 结论：9月问题严重的原因】")
print("=" * 70)

# 计算各因素的贡献
str_contrib = (sep_stats['str'] - jul_stats['str']) / jul_stats['str'] * 100
ros_contrib = (sep_stats['ros'] - jul_stats['ros']) / jul_stats['ros'] * 100
vor_contrib = (sep_stats['vor'] - jul_stats['vor']) / jul_stats['vor'] * 100

print(f"\n9月 vs 7月 各因素变化:")
print(f"  STR: {str_contrib:+.1f}%")
print(f"  ROS: {ros_contrib:+.1f}%")
print(f"  VOR: {vor_contrib:+.1f}%")

# 找主因
factors = [('STR', abs(str_contrib)), ('ROS', abs(ros_contrib)), ('VOR', abs(vor_contrib))]
main_factor = max(factors, key=lambda x: x[1])

print(f"\n主要原因: {main_factor[0]} 变化最大 ({factors[0][1]:.1f}% / {factors[1][1]:.1f}% / {factors[2][1]:.1f}%)")

# 保存结论
with open(output_dir / "step8_结论.txt", 'w', encoding='utf-8') as f:
    f.write("=" * 70 + "\n")
    f.write("Step8: 月度对比分析\n")
    f.write("=" * 70 + "\n\n")
    f.write("【整体指标】\n")
    f.write(f"7月: vov={jul_stats['vov']:.4f}, str={jul_stats['str']:.4f}, ros={jul_stats['ros']:.2f}, vor={jul_stats['vor']:.2f}\n")
    f.write(f"9月: vov={sep_stats['vov']:.4f}, str={sep_stats['str']:.4f}, ros={sep_stats['ros']:.2f}, vor={sep_stats['vor']:.2f}\n")
    f.write(f"12月: vov={dec_stats['vov']:.4f}, str={dec_stats['str']:.4f}, ros={dec_stats['ros']:.2f}, vor={dec_stats['vor']:.2f}\n")
    f.write(f"\n【9月问题原因】\n")
    f.write(f"STR变化: {str_contrib:+.1f}%\n")
    f.write(f"ROS变化: {ros_contrib:+.1f}%\n")
    f.write(f"VOR变化: {vor_contrib:+.1f}%\n")

print(f"\n结果已保存到 output/ 目录")
