#!/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 = 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)
print(f"分析文件: {latest_file.name}")
print(f"渠道×场景组合数: {len(df)}")

# 按渠道汇总
print("\n" + "="*60)
print("按渠道汇总（场景合并）")
print("="*60)
ch_stats = df.groupby('channel').agg({
    '点击uv': 'sum',
    'hotsencetype': 'nunique'
}).rename(columns={'hotsencetype': '场景数'}).sort_values('点击uv', ascending=False)
print(ch_stats.to_string())

# 按场景汇总
print("\n" + "="*60)
print("按场景汇总（渠道合并）")
print("="*60)
scene_stats = df.groupby('hotsencetype').agg({
    '点击uv': 'sum',
    'channel': 'nunique'
}).rename(columns={'channel': '渠道数'}).sort_values('点击uv', ascending=False)
print(scene_stats.to_string())

# UV分布
print("\n" + "="*60)
print("渠道×场景组合 UV 分布")
print("="*60)
thresholds = [100000, 50000, 20000, 10000, 5000, 1000, 0]
for i, t in enumerate(thresholds[:-1]):
    lower = thresholds[i+1] if i+1 < len(thresholds) else 0
    count = len(df[(df['点击uv'] >= lower) & (df['点击uv'] < t)]) if i > 0 else len(df[df['点击uv'] >= t])
    if i == 0:
        print(f"  >= {t:,}: {count} 个")
    else:
        print(f"  {lower:,} ~ {t:,}: {count} 个")

# Top 30 组合
print("\n" + "="*60)
print("Top 30 渠道×场景组合")
print("="*60)
df_sorted = df.sort_values('点击uv', ascending=False).head(30)
for i, row in df_sorted.iterrows():
    print(f"  {row['channel']}_{row['hotsencetype']}: {row['点击uv']:,.0f} (裂变率:{row['整体裂变率']:.4f})")

# 生成简单HTML
html = f"""<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>渠道场景分布</title>
    <style>
        body {{ font-family: -apple-system, sans-serif; margin: 20px; }}
        table {{ border-collapse: collapse; margin: 20px 0; }}
        th, td {{ border: 1px solid #ddd; padding: 8px; text-align: right; }}
        th {{ background: #f5f5f5; }}
        td:first-child {{ text-align: left; }}
        h2 {{ margin-top: 30px; }}
    </style>
</head>
<body>
    <h1>渠道场景分布分析</h1>
    <p>组合数: {len(df)} | 渠道数: {df['channel'].nunique()} | 场景数: {df['hotsencetype'].nunique()}</p>

    <h2>按渠道汇总</h2>
    {ch_stats.reset_index().to_html(index=False, classes='table')}

    <h2>按场景汇总</h2>
    {scene_stats.reset_index().to_html(index=False, classes='table')}

    <h2>Top 50 组合</h2>
    <table>
        <tr><th>排名</th><th>渠道</th><th>场景</th><th>点击UV</th><th>进入分发率</th><th>整体裂变率</th></tr>
        {"".join([f"<tr><td>{i+1}</td><td>{r['channel']}</td><td>{r['hotsencetype']}</td><td>{r['点击uv']:,.0f}</td><td>{r['进入分发率']:.4f}</td><td>{r['整体裂变率']:.4f}</td></tr>" for i, r in df.sort_values('点击uv', ascending=False).head(50).iterrows()])}
    </table>
</body>
</html>
"""

html_file = output_dir / f"{latest_file.stem}_分布.html"
with open(html_file, 'w') as f:
    f.write(html)
print(f"\nHTML 报告: {html_file}")
