#!/usr/bin/env python
# coding=utf-8
"""
V3 综合分析：多维度深入分析
包含：分场景分析、特征归因、时间趋势、典型案例剖析
"""
import pandas as pd
import numpy as np
from pathlib import Path
import glob
from datetime import datetime
from scipy import stats

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

# =============================================================================
# 数据加载
# =============================================================================
data_dir = output_dir / "v3_扩展特征"
all_files = sorted(glob.glob(str(data_dir / "*.csv")))
dfs = []
for f in all_files:
    df_tmp = pd.read_csv(f)
    df_tmp['dt'] = int(Path(f).stem)
    dfs.append(df_tmp)
df = pd.concat(dfs, ignore_index=True)

print("=" * 100)
print("V3 综合分析：多维度深入分析")
print("=" * 100)

print(f"\n【数据概览】")
print(f"  日期范围: {df['dt'].min()} ~ {df['dt'].max()} ({df['dt'].nunique()}天)")
print(f"  总记录数: {len(df)}")
print(f"  去重视频数: {df['vid'].nunique()}")
print(f"  Page场景数: {df['page'].nunique()}")

# =============================================================================
# Part 1: 整体指标基准
# =============================================================================
print("\n" + "=" * 100)
print("Part 1: 整体指标基准")
print("=" * 100)

# COPC（加权）
ros_copc = df['real_ros'].sum() / df['pred_ros'].sum()
str_copc = df['real_str'].sum() / df['pred_str'].sum()

# 偏差统计
ros_bias_mean = df['ros_bias_pct'].mean()
ros_bias_median = df['ros_bias_pct'].median()
ros_bias_std = df['ros_bias_pct'].std()

str_bias_mean = df['str_bias_pct'].mean()
str_bias_median = df['str_bias_pct'].median()

print(f"\n【1.1 整体COPC】")
print(f"  ROS COPC = {ros_copc:.4f} ({'预估整体偏低' if ros_copc > 1 else '预估整体偏高'})")
print(f"  STR COPC = {str_copc:.4f} ({'预估整体偏低' if str_copc > 1 else '预估整体偏高'})")

print(f"\n【1.2 偏差分布】")
print(f"  ROS偏差: 均值={ros_bias_mean:+.1f}%, 中位数={ros_bias_median:+.1f}%, std={ros_bias_std:.1f}%")
print(f"  STR偏差: 均值={str_bias_mean:+.1f}%, 中位数={str_bias_median:+.1f}%")

# 分位数
print(f"\n【1.3 ROS偏差分位数】")
for p in [0.05, 0.10, 0.25, 0.50, 0.75, 0.90, 0.95]:
    val = df['ros_bias_pct'].quantile(p)
    print(f"  P{int(p*100):02d}: {val:+.1f}%")

# =============================================================================
# Part 2: 分场景分析（Page维度）
# =============================================================================
print("\n" + "=" * 100)
print("Part 2: 分场景分析（Page维度）")
print("=" * 100)

page_stats = df.groupby('page').agg({
    'vid': 'count',
    'total_exp': 'sum',
    'pred_ros': 'sum',
    'real_ros': 'sum',
    'pred_str': 'sum',
    'real_str': 'sum',
    'ros_bias_pct': ['mean', 'median', 'std'],
    'str_bias_pct': ['mean', 'median']
}).round(4)

page_stats.columns = ['记录数', '总曝光', 'pred_ros_sum', 'real_ros_sum',
                      'pred_str_sum', 'real_str_sum',
                      'ROS偏差均值', 'ROS偏差中位数', 'ROS偏差std',
                      'STR偏差均值', 'STR偏差中位数']
page_stats['ROS_COPC'] = page_stats['real_ros_sum'] / page_stats['pred_ros_sum']
page_stats['STR_COPC'] = page_stats['real_str_sum'] / page_stats['pred_str_sum']

print(f"\n【2.1 分场景COPC】")
print(f"{'场景':<25} {'记录数':>8} {'曝光占比':>10} {'ROS COPC':>10} {'STR COPC':>10} {'ROS偏差%':>12}")
print("-" * 80)
total_exp = page_stats['总曝光'].sum()
for page, row in page_stats.sort_values('总曝光', ascending=False).iterrows():
    exp_pct = row['总曝光'] / total_exp * 100
    print(f"{page:<25} {int(row['记录数']):>8} {exp_pct:>9.1f}% {row['ROS_COPC']:>10.3f} {row['STR_COPC']:>10.3f} {row['ROS偏差均值']:>+11.1f}%")

# 各场景问题严重程度排序
print(f"\n【2.2 场景问题严重程度】")
page_problem = page_stats.copy()
page_problem['问题得分'] = abs(page_problem['ROS_COPC'] - 1) * 100  # 离1越远问题越大
print(page_problem[['记录数', 'ROS_COPC', 'ROS偏差std', '问题得分']].sort_values('问题得分', ascending=False).round(2).to_string())

# =============================================================================
# Part 3: 分段COPC（回归均值深入分析）
# =============================================================================
print("\n" + "=" * 100)
print("Part 3: 回归均值问题深入分析")
print("=" * 100)

# 3.1 按真实ROS分段
print(f"\n【3.1 按真实ROS分段】")
df['real_ros_bin'] = pd.cut(df['real_ros'],
                           bins=[0, 1, 2, 3, 4, 5, 6, 8, 10, 100],
                           labels=['0-1', '1-2', '2-3', '3-4', '4-5', '5-6', '6-8', '8-10', '>10'])
ros_segment = df.groupby('real_ros_bin', observed=True).agg({
    'pred_ros': ['sum', 'mean'],
    'real_ros': ['sum', 'mean'],
    'ros_bias_pct': ['mean', 'median', 'count'],
    'total_exp': 'sum'
}).round(2)
ros_segment.columns = ['pred_ros_sum', '预估ROS均值', 'real_ros_sum', '真实ROS均值',
                       '偏差均值%', '偏差中位数%', '样本数', '总曝光']
ros_segment['COPC'] = (ros_segment['real_ros_sum'] / ros_segment['pred_ros_sum']).round(3)

print(f"{'真实ROS':<8} {'样本数':>8} {'预估均值':>10} {'真实均值':>10} {'偏差均值%':>12} {'COPC':>8}")
print("-" * 65)
for idx, row in ros_segment.iterrows():
    print(f"{idx:<8} {int(row['样本数']):>8} {row['预估ROS均值']:>10.2f} {row['真实ROS均值']:>10.2f} {row['偏差均值%']:>+11.1f}% {row['COPC']:>8.3f}")

# 3.2 按预估ROS分段
print(f"\n【3.2 按预估ROS分段】")
df['pred_ros_bin'] = pd.cut(df['pred_ros'],
                           bins=[0, 2, 2.5, 3, 3.5, 4, 5, 100],
                           labels=['<2', '2-2.5', '2.5-3', '3-3.5', '3.5-4', '4-5', '>5'])
pred_segment = df.groupby('pred_ros_bin', observed=True).agg({
    'pred_ros': 'mean',
    'real_ros': 'mean',
    'ros_bias_pct': ['mean', 'count'],
    'total_exp': 'sum'
}).round(2)
pred_segment.columns = ['预估ROS均值', '真实ROS均值', '偏差均值%', '样本数', '总曝光']
pred_segment['COPC'] = (pred_segment['真实ROS均值'] / pred_segment['预估ROS均值']).round(3)
print(pred_segment.to_string())

# 3.3 交叉分析：预估 vs 真实
print(f"\n【3.3 预估ROS vs 真实ROS 交叉分析】")
cross_tab = pd.crosstab(df['pred_ros_bin'], df['real_ros_bin'], margins=True)
print(cross_tab.to_string())

# =============================================================================
# Part 4: 排序效果多维分析
# =============================================================================
print("\n" + "=" * 100)
print("Part 4: 排序效果多维分析")
print("=" * 100)

# 4.1 按天计算排序相关性
print(f"\n【4.1 每日排序相关性】")
daily_metrics = []
for dt in sorted(df['dt'].unique()):
    df_day = df[df['dt'] == dt].copy()
    if len(df_day) > 10:
        df_day['pred_rank'] = df_day['pred_score'].rank(ascending=False, pct=True)
        df_day['real_rank'] = df_day['real_score'].rank(ascending=False, pct=True)

        # 相关系数
        corr = df_day['pred_rank'].corr(df_day['real_rank'])

        # 问题比例（预估Top25%，真实Bottom50%）
        problem_cnt = ((df_day['pred_rank'] <= 0.25) & (df_day['real_rank'] > 0.5)).sum()
        problem_pct = problem_cnt / len(df_day) * 100

        # NDCG近似
        df_day_sorted = df_day.sort_values('pred_score', ascending=False)
        top10 = df_day_sorted.head(10)
        avg_real_score_top10 = top10['real_score'].mean()
        best_top10 = df_day.nlargest(10, 'real_score')['real_score'].mean()
        ndcg_approx = avg_real_score_top10 / best_top10 if best_top10 > 0 else 0

        daily_metrics.append({
            'dt': dt,
            'n': len(df_day),
            'corr': corr,
            'problem_pct': problem_pct,
            'ndcg_approx': ndcg_approx
        })

metrics_df = pd.DataFrame(daily_metrics)
print(f"  排序相关系数: 均值={metrics_df['corr'].mean():.3f}, std={metrics_df['corr'].std():.3f}")
print(f"  问题比例: 均值={metrics_df['problem_pct'].mean():.1f}%, max={metrics_df['problem_pct'].max():.1f}%")
print(f"  NDCG近似: 均值={metrics_df['ndcg_approx'].mean():.3f}")

# 4.2 分场景排序效果
print(f"\n【4.2 分场景排序效果】")
page_corr = []
for page in df['page'].unique():
    df_page = df[df['page'] == page].copy()
    if len(df_page) > 20:
        df_page['pred_rank'] = df_page['pred_score'].rank(ascending=False, pct=True)
        df_page['real_rank'] = df_page['real_score'].rank(ascending=False, pct=True)
        corr = df_page['pred_rank'].corr(df_page['real_rank'])
        problem_pct = ((df_page['pred_rank'] <= 0.25) & (df_page['real_rank'] > 0.5)).mean() * 100
        page_corr.append({'page': page, 'n': len(df_page), 'corr': corr, 'problem_pct': problem_pct})

page_corr_df = pd.DataFrame(page_corr)
print(page_corr_df.sort_values('corr', ascending=False).round(3).to_string(index=False))

# =============================================================================
# Part 5: 问题视频深入剖析
# =============================================================================
print("\n" + "=" * 100)
print("Part 5: 问题视频深入剖析")
print("=" * 100)

# 每日独立定义问题视频
df['pred_rank_daily'] = df.groupby('dt')['pred_score'].rank(ascending=False, pct=True)
df['real_rank_daily'] = df.groupby('dt')['real_score'].rank(ascending=False, pct=True)
df['is_problem'] = (df['pred_rank_daily'] <= 0.25) & (df['real_rank_daily'] > 0.5)

problem = df[df['is_problem']]
normal = df[~df['is_problem']]

print(f"\n【5.1 问题视频统计】")
print(f"  问题视频记录: {len(problem)} ({len(problem)/len(df)*100:.1f}%)")
print(f"  正常视频记录: {len(normal)} ({len(normal)/len(df)*100:.1f}%)")
print(f"  问题视频去重: {problem['vid'].nunique()} 个")

# 5.2 问题视频 vs 正常视频对比
print(f"\n【5.2 问题视频 vs 正常视频对比】")
metrics = ['pred_ros', 'real_ros', 'ros_bias_pct', 'pred_str', 'real_str', 'str_bias_pct',
           'total_exp', 'pred_score', 'real_score']
comparison = pd.DataFrame({
    '问题视频': problem[metrics].mean(),
    '正常视频': normal[metrics].mean(),
})
comparison['差异'] = comparison['问题视频'] - comparison['正常视频']
comparison['差异%'] = (comparison['问题视频'] / comparison['正常视频'] - 1) * 100
print(comparison.round(4).to_string())

# 5.3 问题视频的特征分析
print(f"\n【5.3 问题视频特征分析】")
print("\n真实ROS分布对比:")
for label, low, high in [('<2', 0, 2), ('2-4', 2, 4), ('4-6', 4, 6), ('>6', 6, 100)]:
    problem_pct = ((problem['real_ros'] >= low) & (problem['real_ros'] < high)).mean() * 100
    normal_pct = ((normal['real_ros'] >= low) & (normal['real_ros'] < high)).mean() * 100
    diff = problem_pct - normal_pct
    print(f"  {label:>5}: 问题视频 {problem_pct:5.1f}% vs 正常视频 {normal_pct:5.1f}% (差异{diff:+5.1f}%)")

# 5.4 问题视频的场景分布
print(f"\n问题视频场景分布:")
problem_page_dist = problem.groupby('page').size() / len(problem) * 100
normal_page_dist = normal.groupby('page').size() / len(normal) * 100
page_compare = pd.DataFrame({
    '问题视频占比': problem_page_dist,
    '正常视频占比': normal_page_dist
}).fillna(0)
page_compare['差异'] = page_compare['问题视频占比'] - page_compare['正常视频占比']
print(page_compare.round(1).to_string())

# =============================================================================
# Part 6: 高频问题视频分析
# =============================================================================
print("\n" + "=" * 100)
print("Part 6: 高频问题视频分析")
print("=" * 100)

vid_problem_count = problem.groupby('vid').size().sort_values(ascending=False)
print(f"\n问题视频出现频次分布:")
print(f"  出现1次: {(vid_problem_count == 1).sum()} 个视频")
print(f"  出现2-3次: {((vid_problem_count >= 2) & (vid_problem_count <= 3)).sum()} 个视频")
print(f"  出现4-5次: {((vid_problem_count >= 4) & (vid_problem_count <= 5)).sum()} 个视频")
print(f"  出现6次以上: {(vid_problem_count >= 6).sum()} 个视频")

# 高频问题视频详情
print(f"\n【高频问题视频Top10详情】")
for vid, cnt in vid_problem_count.head(10).items():
    vid_data = problem[problem['vid'] == vid]
    vid_all = df[df['vid'] == vid]
    print(f"\n  vid={vid} (出现{cnt}次/{len(vid_all)}天)")
    print(f"    预估ROS: {vid_data['pred_ros'].mean():.2f} (范围: {vid_data['pred_ros'].min():.2f} ~ {vid_data['pred_ros'].max():.2f})")
    print(f"    真实ROS: {vid_data['real_ros'].mean():.2f} (范围: {vid_data['real_ros'].min():.2f} ~ {vid_data['real_ros'].max():.2f})")
    print(f"    ROS偏差: {vid_data['ros_bias_pct'].mean():+.1f}%")
    print(f"    主要场景: {vid_data['page'].value_counts().index[0]}")
    print(f"    总曝光: {vid_data['total_exp'].sum():,.0f}")

# =============================================================================
# Part 7: 时间趋势分析
# =============================================================================
print("\n" + "=" * 100)
print("Part 7: 时间趋势分析")
print("=" * 100)

# 每日指标
print(f"\n【7.1 每日COPC趋势】")
daily_stats = df.groupby('dt').agg({
    'pred_ros': 'sum',
    'real_ros': 'sum',
    'ros_bias_pct': 'mean',
    'vid': 'count',
    'total_exp': 'sum'
}).round(4)
daily_stats['ros_copc'] = daily_stats['real_ros'] / daily_stats['pred_ros']

print(f"{'日期':<12} {'记录数':>8} {'ROS COPC':>10} {'偏差均值%':>12} {'曝光量':>12}")
print("-" * 55)
for dt, row in daily_stats.iterrows():
    print(f"{dt:<12} {int(row['vid']):>8} {row['ros_copc']:>10.3f} {row['ros_bias_pct']:>+11.1f}% {row['total_exp']:>12,.0f}")

# COPC趋势统计
print(f"\nROS COPC趋势统计:")
print(f"  均值: {daily_stats['ros_copc'].mean():.3f}")
print(f"  标准差: {daily_stats['ros_copc'].std():.3f}")
print(f"  最小: {daily_stats['ros_copc'].min():.3f} ({daily_stats['ros_copc'].idxmin()})")
print(f"  最大: {daily_stats['ros_copc'].max():.3f} ({daily_stats['ros_copc'].idxmax()})")

# 趋势检验
slope, intercept, r_value, p_value, std_err = stats.linregress(
    range(len(daily_stats)), daily_stats['ros_copc'].values)
print(f"\n时间趋势检验:")
print(f"  斜率: {slope:.6f} ({'上升' if slope > 0 else '下降'}趋势)")
print(f"  R²: {r_value**2:.4f}")
print(f"  P值: {p_value:.4f} ({'显著' if p_value < 0.05 else '不显著'})")

# 周度汇总
print(f"\n【7.2 周度汇总】")
df['week'] = pd.to_datetime(df['dt'].astype(str)).dt.isocalendar().week
weekly = df.groupby('week').agg({
    'pred_ros': 'sum',
    'real_ros': 'sum',
    'ros_bias_pct': 'mean',
    'vid': 'count'
})
weekly['ros_copc'] = (weekly['real_ros'] / weekly['pred_ros']).round(3)
print(weekly[['vid', 'ros_copc', 'ros_bias_pct']].round(2).to_string())

# =============================================================================
# Part 8: 特征归因分析
# =============================================================================
print("\n" + "=" * 100)
print("Part 8: 特征归因分析（什么因素导致预估不准）")
print("=" * 100)

# 8.1 曝光量 vs 偏差
print(f"\n【8.1 曝光量 vs 偏差】")
df['exp_bin'] = pd.qcut(df['total_exp'], q=5, labels=['极低', '低', '中', '高', '极高'])
exp_bias = df.groupby('exp_bin', observed=True).agg({
    'ros_bias_pct': ['mean', 'median', 'count'],
    'total_exp': 'mean'
}).round(2)
exp_bias.columns = ['偏差均值%', '偏差中位数%', '样本数', '平均曝光']
print(exp_bias.to_string())

# 8.2 预估值高低 vs 偏差
print(f"\n【8.2 预估值高低 vs 偏差】")
df['pred_level'] = pd.qcut(df['pred_ros'], q=5, labels=['极低', '低', '中', '高', '极高'])
pred_bias = df.groupby('pred_level', observed=True).agg({
    'ros_bias_pct': ['mean', 'count'],
    'pred_ros': 'mean',
    'real_ros': 'mean'
}).round(2)
pred_bias.columns = ['偏差均值%', '样本数', '预估ROS均值', '真实ROS均值']
print(pred_bias.to_string())

# 8.3 STR高低 vs ROS偏差
print(f"\n【8.3 STR高低 vs ROS偏差】")
df['str_level'] = pd.qcut(df['real_str'], q=5, labels=['极低', '低', '中', '高', '极高'], duplicates='drop')
str_ros = df.groupby('str_level', observed=True).agg({
    'ros_bias_pct': ['mean', 'count'],
    'real_str': 'mean'
}).round(2)
str_ros.columns = ['ROS偏差均值%', '样本数', '真实STR均值']
print(str_ros.to_string())

# =============================================================================
# Part 9: 综合结论
# =============================================================================
print("\n" + "=" * 100)
print("Part 9: 综合结论")
print("=" * 100)

# 找出问题最严重的场景
worst_page = page_corr_df.loc[page_corr_df['problem_pct'].idxmax(), 'page'] if len(page_corr_df) > 0 else "N/A"

print(f"""
【数据基础】
  - 分析时间: {df['dt'].min()} ~ {df['dt'].max()} ({df['dt'].nunique()}天)
  - 分析样本: {len(df):,} 条记录，{df['vid'].nunique():,} 个视频
  - 场景覆盖: {df['page'].nunique()} 个场景

【COPC分析】
  - ROS 整体 COPC = {ros_copc:.3f} ({'预估整体偏低' if ros_copc > 1 else '预估整体偏高'})
  - STR 整体 COPC = {str_copc:.3f}

【回归均值问题】（核心问题）
  - 真实ROS < 2: 预估偏高 {df[df['real_ros'] < 2]['ros_bias_pct'].mean():+.0f}% ({len(df[df['real_ros'] < 2])} 样本)
  - 真实ROS 2-4: 预估偏高 {df[(df['real_ros'] >= 2) & (df['real_ros'] < 4)]['ros_bias_pct'].mean():+.0f}%
  - 真实ROS > 6: 预估偏低 {df[df['real_ros'] > 6]['ros_bias_pct'].mean():+.0f}% ({len(df[df['real_ros'] > 6])} 样本)

【排序效果】
  - 预估排名与真实排名相关系数: {metrics_df['corr'].mean():.2f} (±{metrics_df['corr'].std():.2f})
  - 排序失准比例: {metrics_df['problem_pct'].mean():.1f}% (最高{metrics_df['problem_pct'].max():.1f}%)
  - NDCG近似: {metrics_df['ndcg_approx'].mean():.2f}

【分场景差异】
  - 问题最严重场景: {worst_page}
  - 场景间COPC差异: {page_stats['ROS_COPC'].max():.2f} ~ {page_stats['ROS_COPC'].min():.2f}

【问题视频特征】
  - 问题视频占比: {len(problem)/len(df)*100:.1f}%
  - 问题视频ROS偏差: {problem['ros_bias_pct'].mean():+.1f}% (vs 正常视频 {normal['ros_bias_pct'].mean():+.1f}%)
  - 问题视频中真实ROS<2占比: {(problem['real_ros'] < 2).mean()*100:.0f}% (vs 正常视频 {(normal['real_ros'] < 2).mean()*100:.0f}%)
  - 高频问题视频(≥4次): {(vid_problem_count >= 4).sum()} 个

【根本原因】
  1. 回归均值问题：模型对极端值预估不准确
     - 低ROS视频被高估 → 排名靠前 → 获得过多曝光
     - 高ROS视频被低估 → 排名靠后 → 曝光不足
  2. 场景差异：不同page的COPC差异明显，说明模型泛化性不足
  3. 高频问题：部分视频反复出现在问题列表，可能存在系统性特征

【量化影响】
  - 问题视频占总曝光: {problem['total_exp'].sum() / df['total_exp'].sum() * 100:.1f}%
  - 预估Top10实际应该在的位置: 真实排名均值={df[df['pred_rank_daily'] <= 0.1]['real_rank_daily'].mean()*100:.0f}%ile
""")

# =============================================================================
# 保存分析结果
# =============================================================================
# 保存各类中间数据
df.to_csv(output_dir / "v3_综合分析_全量.csv", index=False)
metrics_df.to_csv(output_dir / "v3_每日指标.csv", index=False)
page_stats.to_csv(output_dir / "v3_分场景统计.csv")
ros_segment.to_csv(output_dir / "v3_ROS分段统计.csv")

# 高频问题视频详情
top_problem_vids = vid_problem_count.head(20)
top_vid_details = []
for vid, cnt in top_problem_vids.items():
    vid_data = df[df['vid'] == vid]
    top_vid_details.append({
        'vid': vid,
        '问题出现次数': cnt,
        '总出现天数': len(vid_data),
        '问题比例': cnt / len(vid_data) * 100,
        '预估ROS均值': vid_data['pred_ros'].mean(),
        '真实ROS均值': vid_data['real_ros'].mean(),
        'ROS偏差均值': vid_data['ros_bias_pct'].mean(),
        '总曝光': vid_data['total_exp'].sum(),
        '主要场景': vid_data['page'].mode().iloc[0] if len(vid_data) > 0 else ''
    })
pd.DataFrame(top_vid_details).to_csv(output_dir / "v3_高频问题视频.csv", index=False)

print(f"\n数据已保存到 output/ 目录")
print(f"  - v3_综合分析_全量.csv")
print(f"  - v3_每日指标.csv")
print(f"  - v3_分场景统计.csv")
print(f"  - v3_ROS分段统计.csv")
print(f"  - v3_高频问题视频.csv")
