#!/usr/bin/env python
# coding=utf-8
"""
素材视频内容分析
分析视频内容特征（关键词、口播、引导）对传播效果的影响
包含：文章标题/分享标题与视频标题的相似度计算
"""
import pandas as pd
import numpy as np
from pathlib import Path
import sys
sys.path.insert(0, str(Path(__file__).parent.parent.parent))
from lib.text_embedding_api import compare_phrases_batch

# 找到最新的输出文件
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)


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

# ============================================================
# 计算标题相似度
# ============================================================
log("=" * 70)
log("计算标题相似度...")
log("=" * 70)

# 准备所有相似度计算对
# 1. 文章标题 vs 视频标题
# 2. 分享标题 vs 视频标题
# 3. 文章标题 vs 视频口播
# 4. 分享标题 vs 视频口播
# 5. 文章标题 vs 一级品类
# 6. 文章标题 vs 二级品类
# 7. 分享标题 vs 一级品类
# 8. 分享标题 vs 二级品类

similarity_configs = [
    ('文章标题', 'title', '文章标题_视频标题_相似度'),
    ('分享标题', 'title', '分享标题_视频标题_相似度'),
    ('文章标题', '视频口播', '文章标题_口播_相似度'),
    ('分享标题', '视频口播', '分享标题_口播_相似度'),
    ('文章标题', 'merge一级品类', '文章标题_一级品类_相似度'),
    ('文章标题', 'merge二级品类', '文章标题_二级品类_相似度'),
    ('分享标题', 'merge一级品类', '分享标题_一级品类_相似度'),
    ('分享标题', 'merge二级品类', '分享标题_二级品类_相似度'),
]

BATCH_SIZE = 500

for col1, col2, result_col in similarity_configs:
    # 初始化结果列
    df[result_col] = np.nan

    # 准备配对数据
    pairs = []
    valid_indices = []

    for idx, row in df.iterrows():
        text1 = str(row[col1]) if pd.notna(row[col1]) and row[col1] != '' else ''
        text2 = str(row[col2]) if pd.notna(row[col2]) and row[col2] != '' else ''

        if text1 and text2:
            pairs.append((text1, text2))
            valid_indices.append(idx)

    if not pairs:
        log(f"{result_col}: 无有效数据")
        continue

    log(f"计算 {result_col}: {len(pairs)} 对")

    # 批量计算
    scores = []
    for i in range(0, len(pairs), BATCH_SIZE):
        batch = pairs[i:i+BATCH_SIZE]
        results = compare_phrases_batch(batch)
        scores.extend([r['相似度'] for r in results])
        if (i + BATCH_SIZE) % 5000 == 0:
            log(f"  已处理 {min(i+BATCH_SIZE, len(pairs))}/{len(pairs)}")

    # 写入结果
    for idx, score in zip(valid_indices, scores):
        df.at[idx, result_col] = score

    log(f"  覆盖率: {df[result_col].notna().mean():.1%}")
log()

# ============================================================
# 基本信息
# ============================================================
log("=" * 70)
log("基本信息")
log("=" * 70)
log(f"记录数: {len(df):,}")
log(f"视频数: {df['videoid'].nunique():,}")
log(f"总点击uv: {df['点击uv'].sum():,}")
log(f"总回流uv: {df['再分享回流uv'].sum():,}")
log()

# 字段覆盖率
log("新增字段覆盖率:")
for col in ['视频关键词', '视频口播', '视频主题', '传播性判断', '是否有片尾引导']:
    if col in df.columns:
        coverage = df[col].notna().sum() / len(df)
        log(f"  {col}: {coverage:.1%}")
log()

# ============================================================
# 传播性判断 vs 实际效果
# ============================================================
log("=" * 70)
log("AI 传播性判断 vs 实际效果")
log("=" * 70)
if '传播性判断' in df.columns:
    spread_stats = df.groupby('传播性判断').agg({
        'videoid': 'nunique',
        '点击uv': 'sum',
        '再分享回流uv': 'sum'
    }).rename(columns={'videoid': '视频数'})
    spread_stats['回流率'] = spread_stats['再分享回流uv'] / (spread_stats['点击uv'] + 10)
    spread_stats = spread_stats.sort_values('点击uv', ascending=False)

    log(f"{'传播性判断':<15} {'视频数':>8} {'点击uv':>12} {'回流uv':>12} {'回流率':>10}")
    log("-" * 65)
    for spread, row in spread_stats.iterrows():
        spread_name = str(spread)[:13] if pd.notna(spread) else '(空)'
        log(f"{spread_name:<15} {int(row['视频数']):>8,} {int(row['点击uv']):>12,} {int(row['再分享回流uv']):>12,} {row['回流率']:>10.2%}")
log()

# ============================================================
# 片尾引导效果分析
# ============================================================
log("=" * 70)
log("片尾引导效果分析")
log("=" * 70)
if '是否有片尾引导' in df.columns:
    guide_stats = df.groupby('是否有片尾引导').agg({
        'videoid': 'nunique',
        '点击uv': 'sum',
        '再分享回流uv': 'sum'
    }).rename(columns={'videoid': '视频数'})
    guide_stats['回流率'] = guide_stats['再分享回流uv'] / (guide_stats['点击uv'] + 10)

    log(f"{'是否有引导':<15} {'视频数':>8} {'点击uv':>12} {'回流率':>10}")
    log("-" * 50)
    for guide, row in guide_stats.iterrows():
        guide_name = str(guide)[:13] if pd.notna(guide) else '(空)'
        log(f"{guide_name:<15} {int(row['视频数']):>8,} {int(row['点击uv']):>12,} {row['回流率']:>10.2%}")
log()

# 引导强度分析
if '引导强度' in df.columns:
    log("引导强度细分:")
    strength_stats = df.groupby('引导强度').agg({
        'videoid': 'nunique',
        '点击uv': 'sum',
        '再分享回流uv': 'sum'
    }).rename(columns={'videoid': '视频数'})
    strength_stats['回流率'] = strength_stats['再分享回流uv'] / (strength_stats['点击uv'] + 10)
    strength_stats = strength_stats.sort_values('点击uv', ascending=False)

    for strength, row in strength_stats.iterrows():
        strength_name = str(strength)[:20] if pd.notna(strength) else '(空)'
        log(f"  {strength_name:<22} 视频数={int(row['视频数']):>5}, 回流率={row['回流率']:.2%}")
log()

# ============================================================
# 情感倾向分析
# ============================================================
log("=" * 70)
log("情感倾向效果分析")
log("=" * 70)
if '情感倾向' in df.columns:
    emotion_stats = df.groupby('情感倾向').agg({
        'videoid': 'nunique',
        '点击uv': 'sum',
        '再分享回流uv': 'sum'
    }).rename(columns={'videoid': '视频数'})
    emotion_stats['回流率'] = emotion_stats['再分享回流uv'] / (emotion_stats['点击uv'] + 10)
    emotion_stats = emotion_stats.sort_values('点击uv', ascending=False).head(10)

    log(f"{'情感倾向':<20} {'视频数':>8} {'点击uv':>12} {'回流率':>10}")
    log("-" * 55)
    for emotion, row in emotion_stats.iterrows():
        emotion_name = str(emotion)[:18] if pd.notna(emotion) else '(空)'
        log(f"{emotion_name:<20} {int(row['视频数']):>8,} {int(row['点击uv']):>12,} {row['回流率']:>10.2%}")
log()

# ============================================================
# 视频风格分析
# ============================================================
log("=" * 70)
log("视频风格效果分析（Top 15）")
log("=" * 70)
if '视频风格' in df.columns:
    style_stats = df.groupby('视频风格').agg({
        'videoid': 'nunique',
        '点击uv': 'sum',
        '再分享回流uv': 'sum'
    }).rename(columns={'videoid': '视频数'})
    style_stats['回流率'] = style_stats['再分享回流uv'] / (style_stats['点击uv'] + 10)
    style_stats = style_stats.sort_values('点击uv', ascending=False).head(15)

    log(f"{'视频风格':<25} {'视频数':>8} {'点击uv':>12} {'回流率':>10}")
    log("-" * 60)
    for style, row in style_stats.iterrows():
        style_name = str(style)[:23] if pd.notna(style) else '(空)'
        log(f"{style_name:<25} {int(row['视频数']):>8,} {int(row['点击uv']):>12,} {row['回流率']:>10.2%}")
log()

# ============================================================
# 高回流视频内容特征
# ============================================================
log("=" * 70)
log("高回流视频内容特征（回流率≥30%，点击≥1000）")
log("=" * 70)
high_return = df[(df['再分享回流率'] >= 0.3) & (df['点击uv'] >= 1000)]
log(f"符合条件视频数: {len(high_return)}")
log()

if len(high_return) > 0:
    # 传播性分布
    if '传播性判断' in high_return.columns:
        spread_dist = high_return['传播性判断'].value_counts(normalize=True)
        log("传播性判断分布:")
        for spread, pct in spread_dist.items():
            log(f"  {spread}: {pct:.1%}")
        log()

    # 引导分布
    if '是否有片尾引导' in high_return.columns:
        guide_dist = high_return['是否有片尾引导'].value_counts(normalize=True)
        log("片尾引导分布:")
        for guide, pct in guide_dist.items():
            log(f"  {guide}: {pct:.1%}")
        log()

    # Top 视频样例
    log("Top 10 高回流视频:")
    log("-" * 70)
    top_return = high_return.nlargest(10, '再分享回流uv')
    for _, row in top_return.iterrows():
        title = str(row['title'])[:40] if pd.notna(row['title']) else '(无标题)'
        keywords = str(row['视频关键词'])[:50] if pd.notna(row['视频关键词']) else ''
        log(f"  {title}")
        log(f"    关键词: {keywords}")
        log(f"    点击uv={int(row['点击uv'])}, 回流率={row['再分享回流率']:.1%}, 传播性={row['传播性判断']}")
log()

# ============================================================
# 关键词词频分析
# ============================================================
log("=" * 70)
log("视频关键词词频（Top 30）")
log("=" * 70)
if '视频关键词' in df.columns:
    # 提取所有关键词
    all_keywords = []
    for kw in df['视频关键词'].dropna():
        if isinstance(kw, str):
            # 按常见分隔符拆分
            for sep in [',', '，', '、', ';', '；']:
                kw = kw.replace(sep, ',')
            all_keywords.extend([k.strip() for k in kw.split(',') if k.strip()])

    from collections import Counter
    kw_counts = Counter(all_keywords).most_common(30)
    for kw, cnt in kw_counts:
        log(f"  {kw}: {cnt}")
log()

# ============================================================
# 标题相似度效果分析
# ============================================================
log("=" * 70)
log("标题相似度效果分析")
log("=" * 70)

# 所有相似度指标
similarity_cols = [
    ('文章标题_视频标题_相似度', '文章标题 vs 视频标题'),
    ('分享标题_视频标题_相似度', '分享标题 vs 视频标题'),
    ('文章标题_口播_相似度', '文章标题 vs 视频口播'),
    ('分享标题_口播_相似度', '分享标题 vs 视频口播'),
    ('文章标题_一级品类_相似度', '文章标题 vs 一级品类'),
    ('文章标题_二级品类_相似度', '文章标题 vs 二级品类'),
    ('分享标题_一级品类_相似度', '分享标题 vs 一级品类'),
    ('分享标题_二级品类_相似度', '分享标题 vs 二级品类'),
]

# 相似度汇总统计
log("\n相似度汇总统计:")
log(f"{'指标':<30} {'均值':>8} {'中位数':>8} {'标准差':>8} {'覆盖率':>8}")
log("-" * 70)
for col, label in similarity_cols:
    if col in df.columns and df[col].notna().any():
        mean_val = df[col].mean()
        median_val = df[col].median()
        std_val = df[col].std()
        coverage = df[col].notna().mean()
        log(f"{label:<30} {mean_val:>8.3f} {median_val:>8.3f} {std_val:>8.3f} {coverage:>8.1%}")

# 逐个分析相似度与回流率的关系
for col, label in similarity_cols:
    if col not in df.columns or not df[col].notna().any():
        continue

    log(f"\n{label} vs 回流率:")

    # 按相似度分组
    group_col = f'{col}_分组'
    df[group_col] = pd.cut(
        df[col],
        bins=[0, 0.3, 0.5, 0.7, 0.9, 1.0],
        labels=['低(0-0.3)', '较低(0.3-0.5)', '中等(0.5-0.7)', '较高(0.7-0.9)', '高(0.9-1)']
    )

    sim_effect = df.groupby(group_col, observed=True).agg({
        'videoid': 'count',
        '点击uv': 'sum',
        '再分享回流uv': 'sum'
    }).rename(columns={'videoid': '记录数'})
    sim_effect['回流率'] = sim_effect['再分享回流uv'] / (sim_effect['点击uv'] + 10)

    log(f"{'相似度分组':<20} {'记录数':>8} {'点击uv':>12} {'回流率':>10}")
    log("-" * 55)
    for group, row in sim_effect.iterrows():
        log(f"{str(group):<20} {int(row['记录数']):>8,} {int(row['点击uv']):>12,} {row['回流率']:>10.2%}")

    # 清理临时列
    df.drop(columns=[group_col], inplace=True)

log()

# 保存带相似度的数据
output_with_sim = output_dir / f"{latest_file.stem}_含相似度.csv"
df.to_csv(output_with_sim, index=False)
log(f"含相似度数据已保存到: {output_with_sim}")

# 保存分析结果
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}")
