#!/usr/bin/env python
# coding=utf-8
"""
公众号投流-稳定 素材缺失排查
分析为什么只有部分记录有文章标题
"""
import pandas as pd
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]
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()

# 汇总统计
has_title = df[df['文章标题状态'] == '有文章标题']
no_title = df[df['文章标题状态'] == '无文章标题']

total_has = has_title['记录数'].sum()
total_no = no_title['记录数'].sum()
total = total_has + total_no

log("=" * 70)
log("一、整体情况")
log("=" * 70)
log()
log(f"总记录数: {int(total):,}")
log(f"有文章标题: {int(total_has):,} ({total_has/total:.1%})")
log(f"无文章标题: {int(total_no):,} ({total_no/total:.1%})")
log()

# 分析无文章标题的特征
log("=" * 70)
log("二、无文章标题记录的特征")
log("=" * 70)
log()

no_title_wx_sn = no_title['有wx_sn'].sum()
no_title_contenturl = no_title['有contenturl'].sum()
no_title_rootsourceid = no_title['有rootsourceid'].sum()

log(f"无文章标题记录中：")
log(f"  有 wx_sn: {int(no_title_wx_sn):,} ({no_title_wx_sn/total_no:.1%})")
log(f"  有 contenturl: {int(no_title_contenturl):,} ({no_title_contenturl/total_no:.1%})")
log(f"  有 rootsourceid: {int(no_title_rootsourceid):,} ({no_title_rootsourceid/total_no:.1%})")
log()
log("结论：无文章标题的记录有 rootsourceid，但缺少 wx_sn 和 contenturl")
log()

# 按公众号对比
log("=" * 70)
log("三、同一公众号的有/无文章标题对比")
log("=" * 70)
log()

# 合并同一公众号的数据
gzh_has = has_title.groupby('公众号名')['记录数'].sum().reset_index()
gzh_has.columns = ['公众号名', '有标题数']
gzh_no = no_title.groupby('公众号名')['记录数'].sum().reset_index()
gzh_no.columns = ['公众号名', '无标题数']

gzh_compare = gzh_no.merge(gzh_has, on='公众号名', how='outer').fillna(0)
gzh_compare['总数'] = gzh_compare['有标题数'] + gzh_compare['无标题数']
gzh_compare['缺失率'] = gzh_compare['无标题数'] / gzh_compare['总数']
gzh_compare = gzh_compare.sort_values('总数', ascending=False)

header = f"{'公众号':<20} {'总数':>10} {'有标题':>10} {'无标题':>10} {'缺失率':>8}"
log(header)
log("-" * 70)

for _, row in gzh_compare.head(20).iterrows():
    gzh = row['公众号名'] if pd.notna(row['公众号名']) else '(空)'
    log(f"{gzh:<20} {int(row['总数']):>10,} {int(row['有标题数']):>10,} {int(row['无标题数']):>10,} {row['缺失率']:>8.0%}")

log()

# 结论
log("=" * 70)
log("四、结论")
log("=" * 70)
log()
log("1. 同一公众号的流量分为两种：")
log("   - 有完整长文信息（wx_sn, contenturl, 文章标题）")
log("   - 只有 rootsourceid，无长文信息")
log()
log("2. 缺失原因推测：")
log("   - 可能是不同的投放方式（直接投放 vs 长文投放）")
log("   - 或数据采集链路不同导致字段缺失")
log()
log("3. 建议：")
log("   - 排查 rootsourceid 的生成规则")
log("   - 确认是否存在非长文的投放方式")
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}")
