#!/usr/bin/env python
# coding=utf-8
"""
分析各渠道素材字段填充情况
素材字段：文章标题、分享标题、分享封面、公众号名、wx_sn、contenturl
"""
import pandas as pd
import numpy as np
from pathlib import Path

# 找到最新的原始数据文件
task_dir = Path(__file__).parent
output_dir = task_dir / "output"
csv_files = [f for f in output_dir.glob("*.csv")
             if '_匹配' not in f.name and '_分析' not in f.name and '_素材' not in f.name]
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(f"时间范围: {df['dt'].min()} ~ {df['dt'].max()}")
log()

# 素材相关字段
material_fields = [
    ('文章标题', '长文维度'),
    ('分享标题', '卡片维度'),
    ('分享封面', '卡片维度'),
    ('公众号名', '渠道细分'),
    ('wx_sn', '长文ID'),
    ('contenturl', '长文链接'),
    ('rootsourceid', '素材ID'),
    ('shareid', '卡片ID'),
]

# 检查哪些字段存在
existing_fields = [(f, desc) for f, desc in material_fields if f in df.columns]

log("=" * 80)
log("一、各渠道素材字段填充率总览")
log("=" * 80)
log()

# 构建表头
header = f"{'渠道':<28}"
for field, desc in existing_fields:
    header += f"{field[:6]:>8}"
log(header)
log("-" * 80)

# 按渠道统计
channels = df['channel'].unique()
channel_stats = []

for ch in channels:
    ch_df = df[df['channel'] == ch]
    n = len(ch_df)

    row_data = {'渠道': ch, '记录数': n}
    row_str = f"{ch:<28}"

    for field, desc in existing_fields:
        rate = ch_df[field].notna().mean()
        row_data[field] = rate
        row_str += f"{rate:>8.0%}"

    log(row_str)
    channel_stats.append(row_data)

log()

# ============================================================
# 二、素材类型分类
# ============================================================
log("=" * 80)
log("二、渠道素材类型分类")
log("=" * 80)
log()

# 分类规则
log("【有文章标题的渠道】（公众号系，可分析长文素材）")
log("-" * 60)
for stat in channel_stats:
    if stat.get('文章标题', 0) >= 0.3:
        log(f"  {stat['渠道']:<30} 文章标题填充率={stat['文章标题']:.0%}")
log()

log("【有卡片标题的渠道】（群/企微系，可分析卡片素材）")
log("-" * 60)
for stat in channel_stats:
    if stat.get('分享标题', 0) >= 0.3:
        log(f"  {stat['渠道']:<30} 卡片标题填充率={stat['分享标题']:.0%}")
log()

log("【素材信息缺失的渠道】（只能按视频维度分析）")
log("-" * 60)
for stat in channel_stats:
    art = stat.get('文章标题', 0)
    card = stat.get('分享标题', 0)
    if art < 0.3 and card < 0.3:
        log(f"  {stat['渠道']:<30} 文章={art:.0%}, 卡片={card:.0%}")
log()

# ============================================================
# 三、详细字段分析
# ============================================================
log("=" * 80)
log("三、各渠道素材字段详细分析")
log("=" * 80)

for ch in channels:
    ch_df = df[df['channel'] == ch]
    n = len(ch_df)

    log()
    log(f"【{ch}】({n} 条记录)")
    log("-" * 60)

    for field, desc in existing_fields:
        rate = ch_df[field].notna().mean()
        count = ch_df[field].notna().sum()

        # 状态标记
        if rate >= 0.8:
            status = "✓ 完整"
        elif rate >= 0.3:
            status = "△ 部分"
        elif rate > 0:
            status = "○ 稀少"
        else:
            status = "✗ 无"

        log(f"  {field:<12} ({desc:<6}): {rate:>6.1%} ({count}/{n}) {status}")

    # 给出建议
    art = ch_df['文章标题'].notna().mean() if '文章标题' in ch_df.columns else 0
    card = ch_df['分享标题'].notna().mean() if '分享标题' in ch_df.columns else 0

    log()
    if art >= 0.5:
        log(f"  → 建议：按「文章标题」分析素材效果")
    elif card >= 0.5:
        log(f"  → 建议：按「卡片标题」分析素材效果")
    elif art >= 0.1 or card >= 0.1:
        log(f"  → 建议：素材信息不完整，可结合 rootsourceid 分析")
    else:
        log(f"  → 建议：无素材信息，只能按视频维度分析")

log()

# ============================================================
# 四、素材字段示例
# ============================================================
log("=" * 80)
log("四、各渠道素材示例（有素材的渠道）")
log("=" * 80)

for ch in channels:
    ch_df = df[df['channel'] == ch]

    # 检查是否有素材
    has_article = ch_df['文章标题'].notna().any() if '文章标题' in ch_df.columns else False
    has_card = ch_df['分享标题'].notna().any() if '分享标题' in ch_df.columns else False

    if not has_article and not has_card:
        continue

    log()
    log(f"【{ch}】")
    log("-" * 60)

    # 取有素材的示例
    if has_article:
        sample = ch_df[ch_df['文章标题'].notna()].head(3)
        log("  文章标题示例:")
        for _, row in sample.iterrows():
            art = str(row['文章标题'])[:50]
            log(f"    - {art}")

    if has_card:
        sample = ch_df[ch_df['分享标题'].notna()].head(3)
        log("  卡片标题示例:")
        for _, row in sample.iterrows():
            card = str(row['分享标题'])[:50]
            log(f"    - {card}")

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}")

# 同时输出 CSV 汇总表
csv_data = []
for stat in channel_stats:
    csv_data.append(stat)

summary_df = pd.DataFrame(csv_data)
summary_file = output_dir / f"{latest_file.stem}_素材字段汇总.csv"
summary_df.to_csv(summary_file, index=False, encoding='utf-8-sig')
log(f"汇总表已保存到: {summary_file}")
