#!/usr/bin/env python
# coding=utf-8
"""
视频二级品类分析
"""
import pandas as pd
import numpy as np
from pathlib import Path

# 找到最新的输出文件
task_dir = Path(__file__).parent
output_dir = task_dir / "output"
csv_files = list(output_dir.glob("*.csv"))
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)

# 输出结果收集
lines = []


def log(text=""):
    print(text)
    lines.append(text)


def safe_div(a, b):
    return a / b if b and b > 0 else 0


log(f"分析文件: {latest_file.name}")
log()

# ============================================================
# 基本信息
# ============================================================
log("=" * 70)
log("基本信息")
log("=" * 70)
log(f"记录数: {len(df):,}")
log(f"视频数: {df['视频id'].nunique():,}")
log(f"二级品类数: {df['merge二级品类'].nunique()}")
log(f"总曝光pv: {df['分发曝光pv'].sum():,.0f}")
log(f"总回流uv: {df['分发回流_当日'].sum():,.0f}")
log(f"整体 vov0: {df['分发拉回曝光pv'].sum() / (df['分发曝光pv'].sum() + 1):.3f}")
log(f"整体 rov: {df['分发回流_当日'].sum() / (df['分发曝光pv'].sum() + 1):.4%}")
log()

# ============================================================
# 二级品类效果排名（按曝光）
# ============================================================
log("=" * 70)
log("二级品类效果排名（Top 20，按曝光）")
log("=" * 70)
cat_stats = df.groupby('merge二级品类').agg({
    '视频id': 'nunique',
    '分发曝光pv': 'sum',
    '分发拉回曝光pv': 'sum',
    '分发回流_当日': 'sum',
    '分发分享pv': 'sum'
}).rename(columns={'视频id': '视频数'})
cat_stats['vov0'] = cat_stats['分发拉回曝光pv'] / (cat_stats['分发曝光pv'] + 1)
cat_stats['rov'] = cat_stats['分发回流_当日'] / (cat_stats['分发曝光pv'] + 1)
cat_stats['str'] = cat_stats['分发分享pv'] / (cat_stats['分发曝光pv'] + 1)
cat_stats = cat_stats.sort_values('分发曝光pv', ascending=False).head(20)

log(f"{'品类':<25} {'视频数':>8} {'曝光':>14} {'vov0':>8} {'rov':>10} {'str':>8}")
log("-" * 80)
for cat, row in cat_stats.iterrows():
    cat_name = str(cat)[:23] if pd.notna(cat) else '(空)'
    log(f"{cat_name:<25} {int(row['视频数']):>8,} {int(row['分发曝光pv']):>14,} {row['vov0']:>8.3f} {row['rov']:>10.4%} {row['str']:>8.3%}")
log()

# ============================================================
# 二级品类效果排名（按 vov0）
# ============================================================
log("=" * 70)
log("二级品类效果排名（Top 20，按 vov0，曝光≥100万）")
log("=" * 70)
cat_by_vov = df.groupby('merge二级品类').agg({
    '视频id': 'nunique',
    '分发曝光pv': 'sum',
    '分发拉回曝光pv': 'sum',
    '分发回流_当日': 'sum'
}).rename(columns={'视频id': '视频数'})
cat_by_vov['vov0'] = cat_by_vov['分发拉回曝光pv'] / (cat_by_vov['分发曝光pv'] + 1)
cat_by_vov['rov'] = cat_by_vov['分发回流_当日'] / (cat_by_vov['分发曝光pv'] + 1)
# 过滤曝光≥100万
cat_by_vov = cat_by_vov[cat_by_vov['分发曝光pv'] >= 1000000]
cat_by_vov = cat_by_vov.sort_values('vov0', ascending=False).head(20)

log(f"{'品类':<25} {'视频数':>8} {'曝光':>14} {'vov0':>8} {'rov':>10}")
log("-" * 70)
for cat, row in cat_by_vov.iterrows():
    cat_name = str(cat)[:23] if pd.notna(cat) else '(空)'
    log(f"{cat_name:<25} {int(row['视频数']):>8,} {int(row['分发曝光pv']):>14,} {row['vov0']:>8.3f} {row['rov']:>10.4%}")
log()

# ============================================================
# Top 视频（按曝光）
# ============================================================
log("=" * 70)
log("Top 30 视频（按曝光）")
log("=" * 70)
top_videos = df.nlargest(30, '分发曝光pv')
for _, row in top_videos.iterrows():
    title = str(row['标题'])[:35] if pd.notna(row['标题']) else '(无标题)'
    cat = str(row['merge二级品类'])[:15] if pd.notna(row['merge二级品类']) else ''
    log(f"  {title}")
    log(f"    品类={cat}, 曝光={int(row['分发曝光pv']):,}, vov0={row['vov0']:.3f}, vov1={row['vov1']:.3f}")
log()

# ============================================================
# Top 视频（按 vov1，高质量视频）
# ============================================================
log("=" * 70)
log("高质量视频（vov1≥0.7，曝光≥10万，Top 30）")
log("=" * 70)
high_quality = df[(df['vov1'] >= 0.7) & (df['分发曝光pv'] >= 100000)]
high_quality = high_quality.nlargest(30, '分发曝光pv')
for _, row in high_quality.iterrows():
    title = str(row['标题'])[:35] if pd.notna(row['标题']) else '(无标题)'
    cat = str(row['merge二级品类'])[:15] if pd.notna(row['merge二级品类']) else ''
    log(f"  {title}")
    log(f"    品类={cat}, 曝光={int(row['分发曝光pv']):,}, vov0={row['vov0']:.3f}, vov1={row['vov1']:.3f}")
log()

# ============================================================
# VoV 分布分析
# ============================================================
log("=" * 70)
log("VoV 分布分析")
log("=" * 70)
# 整体 VoV 分布
total_pv = df['分发曝光pv'].sum()
total_vov0 = df['分发拉回曝光pv'].sum() / (total_pv + 1)
total_vov1 = df['0_1日分发拉回曝光pv'].sum() / (total_pv + 1) if '0_1日分发拉回曝光pv' in df.columns else 0
total_vov7 = df['0_7日分发拉回曝光pv'].sum() / (total_pv + 1) if '0_7日分发拉回曝光pv' in df.columns else 0

log(f"整体 vov0: {total_vov0:.3f}")
log(f"整体 vov1: {total_vov1:.3f}")
log(f"整体 vov7: {total_vov7:.3f}")
log(f"vov1-vov0 (次日增量): {total_vov1 - total_vov0:.3f}")
log()

# 高 VoV 视频占比
if 't0_500曝光视频量' in df.columns:
    total_500 = df['t0_500曝光视频量'].sum()
    vov07_500 = df['vov1_07_500视频量'].sum() if 'vov1_07_500视频量' in df.columns else 0
    vov08_500 = df['vov1_08_500视频量'].sum() if 'vov1_08_500视频量' in df.columns else 0
    log(f"500+曝光视频中 vov1≥0.7 占比: {vov07_500 / (total_500 + 1):.2%}")
    log(f"500+曝光视频中 vov1≥0.8 占比: {vov08_500 / (total_500 + 1):.2%}")
    log()

# ============================================================
# 新视频 vs 老视频
# ============================================================
log("=" * 70)
log("新视频 vs 老视频效果对比")
log("=" * 70)
# 尝试多种列名格式
col_mapping = {col: col for col in df.columns}
for col in df.columns:
    col_mapping[col.replace('_', '')] = col

if '新0_7曝光占比' in df.columns or '新07曝光占比' in col_mapping:
    ratio_col = '新0_7曝光占比' if '新0_7曝光占比' in df.columns else col_mapping.get('新07曝光占比')
    weights = df['分发曝光pv']

    if ratio_col and ratio_col in df.columns:
        new_07_ratio = (df[ratio_col] * weights).sum() / weights.sum() if weights.sum() > 0 else 0
        log(f"新0~7天视频曝光占比: {new_07_ratio:.2%}")

    # 新视频 vov 分析（从聚合数据计算）
    new_vov0_col = [c for c in df.columns if '新0_7VoV0' in c or '新07VoV0' in c]
    if new_vov0_col:
        new_07_vov0 = df[df[new_vov0_col[0]].notna()][new_vov0_col[0]].mean()
        log(f"新0~7天视频平均 vov0: {new_07_vov0:.3f}")

    old_vov0_col = [c for c in df.columns if '非0_7_VoV0' in c or '非07VoV0' in c]
    if old_vov0_col:
        old_vov0 = df[df[old_vov0_col[0]].notna()][old_vov0_col[0]].mean()
        log(f"老视频（>7天）平均 vov0: {old_vov0:.3f}")
log()

# ============================================================
# 群聊 vs 单聊
# ============================================================
log("=" * 70)
log("群聊 vs 单聊效果对比")
log("=" * 70)
if '群聊占比' in df.columns and '1008回流人数' in df.columns:
    total_1008 = df['1008回流人数'].sum()
    total_1007 = df['1007回流人数'].sum()
    total_return = total_1008 + total_1007

    log(f"群聊回流占比: {total_1008 / (total_return + 1):.2%}")
    log(f"单聊回流占比: {total_1007 / (total_return + 1):.2%}")

    if '群聊ros' in df.columns:
        avg_qunliao_ros = df[df['群聊ros'].notna() & (df['群聊ros'] < 10)]['群聊ros'].mean()
        avg_danliao_ros = df[df['单聊ros'].notna() & (df['单聊ros'] < 10)]['单聊ros'].mean()
        log(f"群聊 ros（平均）: {avg_qunliao_ros:.2f}")
        log(f"单聊 ros（平均）: {avg_danliao_ros:.2f}")
log()

# ============================================================
# 上传渠道分析
# ============================================================
log("=" * 70)
log("上传渠道效果（Top 10）")
log("=" * 70)
channel_stats = df.groupby('上传渠道').agg({
    '视频id': 'nunique',
    '分发曝光pv': 'sum',
    '分发拉回曝光pv': 'sum',
    '分发回流_当日': 'sum'
}).rename(columns={'视频id': '视频数'})
channel_stats['vov0'] = channel_stats['分发拉回曝光pv'] / (channel_stats['分发曝光pv'] + 1)
channel_stats['rov'] = channel_stats['分发回流_当日'] / (channel_stats['分发曝光pv'] + 1)
channel_stats = channel_stats.sort_values('分发曝光pv', ascending=False).head(10)

log(f"{'上传渠道':<20} {'视频数':>8} {'曝光':>14} {'vov0':>8} {'rov':>10}")
log("-" * 65)
for ch, row in channel_stats.iterrows():
    ch_name = str(ch)[:18] if pd.notna(ch) else '(空)'
    log(f"{ch_name:<20} {int(row['视频数']):>8,} {int(row['分发曝光pv']):>14,} {row['vov0']:>8.3f} {row['rov']:>10.4%}")
log()

# 保存分析结果
result_file = output_dir / f"{latest_file.stem}_分析.txt"
with open(result_file, 'w', encoding='utf-8') as f:
    f.write("\n".join(lines))

log(f"分析结果已保存到: {result_file}")
