#!/usr/bin/env python
# coding=utf-8
"""
品类承接裂变率分析
分析目标:
1. 进入/承接品类一致时，承接裂变率(vov)是否更高
2. 不同品类组合间的承接裂变率是否存在稳定的相关性
"""
import pandas as pd
import numpy as np
from pathlib import Path
from scipy import stats
import matplotlib.pyplot as plt
import matplotlib
matplotlib.rcParams['font.sans-serif'] = ['Arial Unicode MS', 'SimHei', 'PingFang SC']
matplotlib.rcParams['axes.unicode_minus'] = False

# ========== 数据加载 ==========
task_dir = Path(__file__).parent
output_dir = task_dir / "output"

csv_files = [f for f in output_dir.glob("query_*.csv") if not f.name.endswith('.html')]
if not csv_files:
    print("没有找到数据文件，请先运行 query.sql")
    exit(1)

latest_file = max(csv_files, key=lambda x: x.stat().st_mtime)
df = pd.read_csv(latest_file)

print("=" * 60)
print("品类承接裂变率分析")
print("=" * 60)
print(f"数据文件: {latest_file.name}")
print(f"时间范围: {df['dt'].min()} ~ {df['dt'].max()}")
print(f"记录数: {len(df)}")
print()

# 过滤掉 headvideoid为空 的记录（无法判断进入品类）
df_valid = df[~df['head_cate2'].isin(['headvideoid为空', '未匹配品类'])].copy()
print(f"有效记录数(排除headvideoid为空/未匹配): {len(df_valid)}")

# ========== 分析1: 品类一致性分析 ==========
print("\n" + "=" * 60)
print("分析1: 进入/承接品类一致性 vs 承接裂变率(vov)")
print("=" * 60)

# 标记是否为同品类
df_valid['is_same_cate'] = df_valid['head_cate2'] == df_valid['rec_cate2']

# 按人群分组分析
for crowd in ['内部', '外部0层', '外部裂变']:
    crowd_df = df_valid[df_valid['crowd'] == crowd]
    if len(crowd_df) == 0:
        continue

    # 同品类 vs 跨品类
    same_cate = crowd_df[crowd_df['is_same_cate']]
    diff_cate = crowd_df[~crowd_df['is_same_cate']]

    # 加权平均 vov (按曝光量加权)
    same_vov = (same_cate['new_exposure_cnt'].sum() / same_cate['exp'].sum()) if same_cate['exp'].sum() > 0 else 0
    diff_vov = (diff_cate['new_exposure_cnt'].sum() / diff_cate['exp'].sum()) if diff_cate['exp'].sum() > 0 else 0

    print(f"\n【{crowd}】")
    print(f"  同品类承接: 曝光 {same_cate['exp'].sum():,.0f}, vov = {same_vov:.4f}")
    print(f"  跨品类承接: 曝光 {diff_cate['exp'].sum():,.0f}, vov = {diff_vov:.4f}")
    print(f"  同品类/跨品类 vov比值: {same_vov/diff_vov:.2f}x" if diff_vov > 0 else "  跨品类无数据")

    # 统计检验: Mann-Whitney U检验 (非参数检验)
    if len(same_cate) >= 5 and len(diff_cate) >= 5:
        stat, pvalue = stats.mannwhitneyu(same_cate['vov'], diff_cate['vov'], alternative='greater')
        print(f"  Mann-Whitney U检验 (同品类vov > 跨品类vov): p-value = {pvalue:.4f}")
        print(f"  结论: {'显著' if pvalue < 0.05 else '不显著'} (α=0.05)")

# 整体汇总
print("\n【整体汇总】")
same_all = df_valid[df_valid['is_same_cate']]
diff_all = df_valid[~df_valid['is_same_cate']]
same_vov_all = same_all['new_exposure_cnt'].sum() / same_all['exp'].sum()
diff_vov_all = diff_all['new_exposure_cnt'].sum() / diff_all['exp'].sum()
print(f"  同品类承接: 曝光 {same_all['exp'].sum():,.0f}, vov = {same_vov_all:.4f}")
print(f"  跨品类承接: 曝光 {diff_all['exp'].sum():,.0f}, vov = {diff_vov_all:.4f}")
print(f"  同品类/跨品类 vov比值: {same_vov_all/diff_vov_all:.2f}x")

# ========== 分析2: 品类组合稳定性分析 ==========
print("\n" + "=" * 60)
print("分析2: 品类组合间的承接裂变率稳定性相关性")
print("=" * 60)

# 2.1 跨日期稳定性: 同一品类组合在不同日期的vov相关性
print("\n【2.1 跨日期稳定性】")
print("分析同一品类组合在不同日期的vov是否稳定")

dates = sorted(df_valid['dt'].unique())
if len(dates) >= 2:
    # 创建品类组合 pivot table
    df_valid['cate_pair'] = df_valid['head_cate2'] + ' → ' + df_valid['rec_cate2']

    # 按日期和品类组合汇总
    daily_vov = df_valid.groupby(['dt', 'cate_pair']).apply(
        lambda x: x['new_exposure_cnt'].sum() / x['exp'].sum()
    ).unstack(level=0)

    # 计算相邻日期间的相关性
    date_correlations = []
    for i in range(len(dates) - 1):
        d1, d2 = dates[i], dates[i+1]
        if d1 in daily_vov.columns and d2 in daily_vov.columns:
            valid_pairs = daily_vov[[d1, d2]].dropna()
            if len(valid_pairs) >= 10:
                corr, pval = stats.pearsonr(valid_pairs[d1], valid_pairs[d2])
                date_correlations.append({'date1': d1, 'date2': d2, 'corr': corr, 'pval': pval, 'n': len(valid_pairs)})

    if date_correlations:
        corr_df = pd.DataFrame(date_correlations)
        print(f"  相邻日期vov相关性:")
        for _, row in corr_df.iterrows():
            print(f"    {row['date1']} vs {row['date2']}: r={row['corr']:.3f}, p={row['pval']:.4f}, n={row['n']}")
        print(f"  平均相关系数: {corr_df['corr'].mean():.3f}")
        print(f"  结论: 品类组合的vov在跨日期间{'高度稳定' if corr_df['corr'].mean() > 0.7 else '较为稳定' if corr_df['corr'].mean() > 0.5 else '不太稳定'}")

# 2.2 跨人群稳定性: 同一品类组合在不同人群的vov相关性
print("\n【2.2 跨人群稳定性】")
print("分析同一品类组合在不同人群的vov排序是否一致")

crowd_vov = df_valid.groupby(['crowd', 'cate_pair']).apply(
    lambda x: x['new_exposure_cnt'].sum() / x['exp'].sum()
).unstack(level=0)

crowds = ['内部', '外部0层', '外部裂变']
crowd_pairs = [(crowds[i], crowds[j]) for i in range(len(crowds)) for j in range(i+1, len(crowds))]

for c1, c2 in crowd_pairs:
    if c1 in crowd_vov.columns and c2 in crowd_vov.columns:
        valid = crowd_vov[[c1, c2]].dropna()
        if len(valid) >= 10:
            corr, pval = stats.pearsonr(valid[c1], valid[c2])
            spearman_corr, spearman_pval = stats.spearmanr(valid[c1], valid[c2])
            print(f"  {c1} vs {c2}:")
            print(f"    Pearson r = {corr:.3f} (p={pval:.4f})")
            print(f"    Spearman ρ = {spearman_corr:.3f} (p={spearman_pval:.4f})")
            print(f"    样本数: {len(valid)} 品类组合")

# 2.3 高/低裂变品类组合识别
print("\n【2.3 稳定的高/低裂变品类组合】")
print("识别在所有人群中都表现稳定的品类组合")

# 计算每个品类组合在所有人群的平均vov
overall_vov = df_valid.groupby('cate_pair').apply(
    lambda x: pd.Series({
        'vov': x['new_exposure_cnt'].sum() / x['exp'].sum(),
        'exp': x['exp'].sum(),
        'crowd_count': x['crowd'].nunique()
    })
)

# 只看在多个人群都有数据的组合
stable_pairs = overall_vov[overall_vov['crowd_count'] >= 2].copy()
stable_pairs = stable_pairs.sort_values('vov', ascending=False)

print(f"\n  Top 10 高裂变品类组合 (vov最高):")
for i, (pair, row) in enumerate(stable_pairs.head(10).iterrows(), 1):
    print(f"    {i}. {pair}: vov={row['vov']:.4f}, 曝光={row['exp']:,.0f}")

print(f"\n  Top 10 低裂变品类组合 (vov最低):")
for i, (pair, row) in enumerate(stable_pairs.tail(10).iloc[::-1].iterrows(), 1):
    print(f"    {i}. {pair}: vov={row['vov']:.4f}, 曝光={row['exp']:,.0f}")

# ========== 分析3: 品类亲和性矩阵 ==========
print("\n" + "=" * 60)
print("分析3: 品类亲和性矩阵 (进入品类 → 承接品类)")
print("=" * 60)

# 计算每个head_cate2的基准vov
head_baseline = df_valid.groupby('head_cate2').apply(
    lambda x: x['new_exposure_cnt'].sum() / x['exp'].sum()
).to_dict()

# 计算亲和性: 特定组合vov / 进入品类基准vov
affinity_data = []
for (head, rec), grp in df_valid.groupby(['head_cate2', 'rec_cate2']):
    if grp['exp'].sum() >= 10000:  # 只看曝光量足够的组合
        pair_vov = grp['new_exposure_cnt'].sum() / grp['exp'].sum()
        baseline = head_baseline.get(head, 1)
        affinity = pair_vov / baseline if baseline > 0 else 0
        affinity_data.append({
            'head_cate2': head,
            'rec_cate2': rec,
            'vov': pair_vov,
            'baseline_vov': baseline,
            'affinity': affinity,
            'exp': grp['exp'].sum()
        })

affinity_df = pd.DataFrame(affinity_data)

print("\n  高亲和组合 (affinity > 1.2, 即vov比基准高20%):")
high_affinity = affinity_df[affinity_df['affinity'] > 1.2].sort_values('affinity', ascending=False).head(15)
for _, row in high_affinity.iterrows():
    print(f"    {row['head_cate2']} → {row['rec_cate2']}: affinity={row['affinity']:.2f}, vov={row['vov']:.4f}")

print("\n  低亲和组合 (affinity < 0.8, 即vov比基准低20%):")
low_affinity = affinity_df[affinity_df['affinity'] < 0.8].sort_values('affinity').head(15)
for _, row in low_affinity.iterrows():
    print(f"    {row['head_cate2']} → {row['rec_cate2']}: affinity={row['affinity']:.2f}, vov={row['vov']:.4f}")

# ========== 可视化 ==========
print("\n" + "=" * 60)
print("生成可视化图表...")
print("=" * 60)

fig, axes = plt.subplots(2, 2, figsize=(14, 12))

# 图1: 同品类 vs 跨品类 vov对比
ax1 = axes[0, 0]
crowds = ['内部', '外部0层', '外部裂变']
same_vovs = []
diff_vovs = []
for crowd in crowds:
    crowd_df = df_valid[df_valid['crowd'] == crowd]
    same = crowd_df[crowd_df['is_same_cate']]
    diff = crowd_df[~crowd_df['is_same_cate']]
    same_vovs.append(same['new_exposure_cnt'].sum() / same['exp'].sum() if same['exp'].sum() > 0 else 0)
    diff_vovs.append(diff['new_exposure_cnt'].sum() / diff['exp'].sum() if diff['exp'].sum() > 0 else 0)

x = np.arange(len(crowds))
width = 0.35
ax1.bar(x - width/2, same_vovs, width, label='同品类承接', color='#4CAF50')
ax1.bar(x + width/2, diff_vovs, width, label='跨品类承接', color='#2196F3')
ax1.set_ylabel('承接裂变率 (vov)')
ax1.set_title('同品类 vs 跨品类 承接裂变率对比')
ax1.set_xticks(x)
ax1.set_xticklabels(crowds)
ax1.legend()
ax1.grid(axis='y', alpha=0.3)

# 图2: 品类组合vov分布
ax2 = axes[0, 1]
ax2.hist(stable_pairs['vov'], bins=30, edgecolor='black', alpha=0.7, color='#FF9800')
ax2.axvline(stable_pairs['vov'].median(), color='red', linestyle='--', label=f'中位数: {stable_pairs["vov"].median():.4f}')
ax2.axvline(stable_pairs['vov'].mean(), color='blue', linestyle='--', label=f'均值: {stable_pairs["vov"].mean():.4f}')
ax2.set_xlabel('承接裂变率 (vov)')
ax2.set_ylabel('品类组合数')
ax2.set_title('品类组合vov分布')
ax2.legend()

# 图3: 跨人群vov相关性散点图 (内部 vs 外部0层)
ax3 = axes[1, 0]
if '内部' in crowd_vov.columns and '外部0层' in crowd_vov.columns:
    valid = crowd_vov[['内部', '外部0层']].dropna()
    ax3.scatter(valid['内部'], valid['外部0层'], alpha=0.5, s=30)
    # 添加对角线
    max_val = max(valid['内部'].max(), valid['外部0层'].max())
    ax3.plot([0, max_val], [0, max_val], 'r--', alpha=0.5, label='y=x')
    ax3.set_xlabel('内部 vov')
    ax3.set_ylabel('外部0层 vov')
    ax3.set_title('跨人群vov相关性 (内部 vs 外部0层)')
    corr, _ = stats.pearsonr(valid['内部'], valid['外部0层'])
    ax3.text(0.05, 0.95, f'r = {corr:.3f}', transform=ax3.transAxes, fontsize=12, verticalalignment='top')
    ax3.legend()

# 图4: 亲和性分布
ax4 = axes[1, 1]
ax4.hist(affinity_df['affinity'], bins=30, edgecolor='black', alpha=0.7, color='#9C27B0')
ax4.axvline(1.0, color='red', linestyle='--', label='基准线 (affinity=1)')
ax4.set_xlabel('亲和性 (vov / 基准vov)')
ax4.set_ylabel('品类组合数')
ax4.set_title('品类亲和性分布')
ax4.legend()

plt.tight_layout()
plt.savefig(output_dir / 'category_correlation_analysis.png', dpi=150, bbox_inches='tight')
print(f"图表已保存: {output_dir / 'category_correlation_analysis.png'}")

# ========== 导出分析结果 ==========
print("\n导出分析结果...")

# 导出品类组合vov排名
stable_pairs.to_csv(output_dir / 'category_pair_vov_ranking.csv')
print(f"品类组合vov排名: {output_dir / 'category_pair_vov_ranking.csv'}")

# 导出亲和性矩阵
affinity_df.to_csv(output_dir / 'category_affinity_matrix.csv', index=False)
print(f"品类亲和性矩阵: {output_dir / 'category_affinity_matrix.csv'}")

print("\n" + "=" * 60)
print("分析完成!")
print("=" * 60)
