#!/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 = [f for f in output_dir.glob("*.csv") if f.stem.count('_') == 1]
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()

# ============================================================
# 一、渠道整体表现
# ============================================================
log("=" * 80)
log("一、渠道整体表现")
log("=" * 80)
log()

# 按渠道汇总（加权平均）
channel_stats = df.groupby('channel').agg({
    '点击uv': 'sum',
    '裂变uv': 'sum'
}).reset_index()

# 用加权方式计算整体指标
for ch in channel_stats['channel']:
    ch_df = df[df['channel'] == ch]
    uv = ch_df['点击uv'].sum()
    channel_stats.loc[channel_stats['channel'] == ch, '进入分发率'] = (ch_df['进入分发率'] * ch_df['点击uv']).sum() / uv
    channel_stats.loc[channel_stats['channel'] == ch, '整体裂变率'] = (ch_df['整体裂变率'] * ch_df['点击uv']).sum() / uv
    channel_stats.loc[channel_stats['channel'] == ch, '头部裂变率'] = (ch_df['头部裂变率'] * ch_df['点击uv']).sum() / uv
    channel_stats.loc[channel_stats['channel'] == ch, '推荐裂变率'] = (ch_df['推荐裂变率'] * ch_df['点击uv']).sum() / uv

channel_stats = channel_stats.sort_values('点击uv', ascending=False)

header = f"{'渠道':<25} {'点击UV':>12} {'进入分发率':>10} {'整体裂变率':>10} {'头部裂变率':>10} {'推荐裂变率':>10}"
log(header)
log("-" * 90)

for _, row in channel_stats.iterrows():
    log(f"{row['channel']:<25} {int(row['点击uv']):>12,} {row['进入分发率']:>10.4f} {row['整体裂变率']:>10.4f} {row['头部裂变率']:>10.4f} {row['推荐裂变率']:>10.4f}")

log()

# ============================================================
# 二、渠道每日趋势
# ============================================================
log("=" * 80)
log("二、渠道每日回流率趋势")
log("=" * 80)
log()

# 按日期和渠道汇总
daily_channel = df.groupby(['dt', 'channel']).apply(
    lambda x: pd.Series({
        '点击uv': x['点击uv'].sum(),
        '回流率': (x['再分享回流率'] * x['点击uv']).sum() / x['点击uv'].sum()
    }), include_groups=False
).reset_index()

main_channels = channel_stats[channel_stats['点击uv'] > 10000]['channel'].tolist()
dates = sorted(df['dt'].unique())

header = f"{'渠道':<25}" + "".join([f"{str(d)[-4:]:>8}" for d in dates])
log(header)
log("-" * 80)

for ch in main_channels:
    ch_data = daily_channel[daily_channel['channel'] == ch].set_index('dt')
    row_str = f"{ch:<25}"
    for d in dates:
        if d in ch_data.index:
            rate = ch_data.loc[d, '回流率']
            row_str += f"{rate:>8.4f}"
        else:
            row_str += f"{'--':>8}"
    log(row_str)

log()

# ============================================================
# 三、渠道×品类效果矩阵
# ============================================================
log("=" * 80)
log("三、渠道×品类效果矩阵（回流率）")
log("=" * 80)
log()

# 按渠道和品类汇总
channel_category = df.groupby(['channel', 'merge一级品类']).apply(
    lambda x: pd.Series({
        '点击uv': x['点击uv'].sum(),
        '回流率': (x['再分享回流率'] * x['点击uv']).sum() / x['点击uv'].sum() if x['点击uv'].sum() > 0 else 0
    }), include_groups=False
).reset_index()

pivot_uv = channel_category.pivot(index='merge一级品类', columns='channel', values='点击uv').fillna(0)
pivot_ror = channel_category.pivot(index='merge一级品类', columns='channel', values='回流率')

min_uv = 1000
valid_categories = pivot_uv[pivot_uv.sum(axis=1) >= min_uv].index
valid_channels = main_channels[:8]

log(f"品类数: {len(valid_categories)}, 渠道数: {len(valid_channels)}")
log()

# 输出矩阵
log("【渠道×品类 回流率矩阵】")
log("-" * 100)

# 简化渠道名用于表头
ch_short = {ch: ch[:8] for ch in valid_channels}
header = f"{'品类':<15}" + "".join([f"{ch_short[ch]:>12}" for ch in valid_channels])
log(header)
log("-" * 100)

for category in valid_categories:
    if category not in pivot_ror.index:
        continue
    cat_name = str(category)[:13] if pd.notna(category) else '(空)'
    row_str = f"{cat_name:<15}"
    for ch in valid_channels:
        if ch in pivot_ror.columns and pd.notna(pivot_ror.loc[category, ch]):
            rate = pivot_ror.loc[category, ch]
            row_str += f"{rate:>12.4f}"
        else:
            row_str += f"{'--':>12}"
    log(row_str)

log()

log("【各品类 Top 5 渠道】")
log("-" * 80)

for category in valid_categories:
    if category not in pivot_ror.index:
        continue
    row = pivot_ror.loc[category, [c for c in valid_channels if c in pivot_ror.columns]].dropna().sort_values(ascending=False)
    if len(row) == 0:
        continue
    cat_name = str(category)[:15] if pd.notna(category) else '(空)'
    cat_total_uv = int(pivot_uv.loc[category].sum())
    log(f"  {cat_name:<17} 总UV={cat_total_uv:>10,}")
    for ch in row.head(5).index:
        ch_name = ch[:20] if pd.notna(ch) else ''
        ch_uv = int(pivot_uv.loc[category, ch]) if ch in pivot_uv.columns else 0
        log(f"      {ch_name:<20} UV={ch_uv:>8,} 回流率={row[ch]:.4f}")
    log()

log()

log("【各渠道 Top 5 品类】")
log("-" * 80)

for ch in valid_channels:
    if ch not in pivot_ror.columns:
        continue
    col = pivot_ror.loc[valid_categories, ch].dropna().sort_values(ascending=False)
    if len(col) == 0:
        continue
    ch_uv = int(channel_stats[channel_stats['channel'] == ch]['点击uv'].values[0])
    log(f"  {ch:<25} UV={ch_uv:>10,}")
    for cat in col.head(5).index:
        cat_name = str(cat)[:15] if pd.notna(cat) else '(空)'
        cat_uv = int(pivot_uv.loc[cat, ch]) if ch in pivot_uv.columns else 0
        log(f"      {cat_name:<17} UV={cat_uv:>8,} 回流率={col[cat]:.4f}")
    log()

log()

# ============================================================
# 四、渠道×二级品类效果
# ============================================================
log("=" * 80)
log("四、渠道×二级品类效果")
log("=" * 80)
log()

# 按渠道和二级品类汇总
if 'merge二级品类' in df.columns:
    channel_cat2 = df.groupby(['channel', 'merge一级品类', 'merge二级品类']).apply(
        lambda x: pd.Series({
            '点击uv': x['点击uv'].sum(),
            '回流率': (x['再分享回流率'] * x['点击uv']).sum() / x['点击uv'].sum() if x['点击uv'].sum() > 0 else 0
        }), include_groups=False
    ).reset_index()

    # 二级品类汇总（跨渠道）
    cat2_stats = channel_cat2.groupby(['merge一级品类', 'merge二级品类']).agg({
        '点击uv': 'sum'
    }).reset_index()
    cat2_stats = cat2_stats[cat2_stats['点击uv'] >= 500].sort_values('点击uv', ascending=False)

    # 二级品类的回流率 pivot
    pivot_cat2_ror = channel_cat2.pivot_table(
        index=['merge一级品类', 'merge二级品类'],
        columns='channel',
        values='回流率'
    )
    pivot_cat2_uv = channel_cat2.pivot_table(
        index=['merge一级品类', 'merge二级品类'],
        columns='channel',
        values='点击uv',
        fill_value=0
    )

    valid_cat2 = [(row['merge一级品类'], row['merge二级品类']) for _, row in cat2_stats.head(30).iterrows()]

    log("【各二级品类 Top 5 渠道】")
    log("-" * 80)

    for cat1, cat2 in valid_cat2:
        if (cat1, cat2) not in pivot_cat2_ror.index:
            continue
        row = pivot_cat2_ror.loc[(cat1, cat2), [c for c in valid_channels if c in pivot_cat2_ror.columns]].dropna().sort_values(ascending=False)
        if len(row) == 0:
            continue
        cat2_name = f"{str(cat1)[:8]}/{str(cat2)[:10]}" if pd.notna(cat2) else str(cat1)[:15]
        cat2_total_uv = int(pivot_cat2_uv.loc[(cat1, cat2)].sum())
        log(f"  {cat2_name:<20} 总UV={cat2_total_uv:>10,}")
        for ch in row.head(5).index:
            ch_name = ch[:20] if pd.notna(ch) else ''
            ch_uv = int(pivot_cat2_uv.loc[(cat1, cat2), ch]) if ch in pivot_cat2_uv.columns else 0
            log(f"      {ch_name:<20} UV={ch_uv:>8,} 回流率={row[ch]:.4f}")
        log()

    log()

    log("【各渠道 Top 5 二级品类】")
    log("-" * 80)

    for ch in valid_channels:
        if ch not in pivot_cat2_ror.columns:
            continue
        col = pivot_cat2_ror.loc[valid_cat2, ch].dropna().sort_values(ascending=False)
        if len(col) == 0:
            continue
        ch_uv = int(channel_stats[channel_stats['channel'] == ch]['点击uv'].values[0])
        log(f"  {ch:<25} UV={ch_uv:>10,}")
        for (cat1, cat2) in col.head(5).index:
            cat2_name = f"{str(cat1)[:8]}/{str(cat2)[:10]}" if pd.notna(cat2) else str(cat1)[:15]
            cat2_uv = int(pivot_cat2_uv.loc[(cat1, cat2), ch]) if ch in pivot_cat2_uv.columns else 0
            log(f"      {cat2_name:<20} UV={cat2_uv:>8,} 回流率={col[(cat1, cat2)]:.4f}")
        log()

    log()

    # 回流率 Top 20
    log("【回流率 Top 20 渠道×二级品类组合】")
    log("-" * 80)
    cat2_filtered = channel_cat2[channel_cat2['点击uv'] >= 500].nlargest(20, '回流率')
    header = f"{'渠道':<20} {'一级品类':<10} {'二级品类':<12} {'UV':>10} {'回流率':>8}"
    log(header)
    log("-" * 80)
    for _, row in cat2_filtered.iterrows():
        cat1 = str(row['merge一级品类'])[:10] if pd.notna(row['merge一级品类']) else ''
        cat2 = str(row['merge二级品类'])[:12] if pd.notna(row['merge二级品类']) else ''
        log(f"{row['channel']:<20} {cat1:<10} {cat2:<12} {int(row['点击uv']):>10,} {row['回流率']:>8.4f}")
    log()

# ============================================================
# 五、汇总统计
# ============================================================
log("=" * 80)
log("五、汇总统计")
log("=" * 80)
log()

total_uv = df['点击uv'].sum()
total_return = df['再分享回流uv'].sum()
avg_recommend = (df['进入推荐率'] * df['点击uv']).sum() / total_uv
avg_return = (df['再分享回流率'] * df['点击uv']).sum() / total_uv

log(f"总点击UV: {int(total_uv):,}")
log(f"平均进入推荐率: {avg_recommend:.4f}")
log(f"平均再分享回流率: {avg_return:.4f}")
log(f"渠道数: {df['channel'].nunique()}")
log(f"品类数: {df['merge一级品类'].nunique()}")
log()

# 保存 TXT
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
# ============================================================
log()
log("=" * 80)
log("导出 CSV 文件")
log("=" * 80)

# 1. 渠道整体表现
channel_csv = channel_stats[['channel', '点击uv', '进入推荐率', '再分享回流率', '原视频再分享回流率', '推荐再分享回流率']].copy()
channel_csv.columns = ['渠道', '点击UV', '进入推荐率', '再分享回流率', '原视频回流率', '推荐回流率']
channel_csv.to_csv(output_dir / f"{latest_file.stem}_渠道整体.csv", index=False, encoding='utf-8-sig')
log(f"  渠道整体表现: {latest_file.stem}_渠道整体.csv")

# 2. 渠道×一级品类矩阵
pivot_ror_csv = pivot_ror.copy()
pivot_ror_csv.index.name = '一级品类'
pivot_ror_csv.to_csv(output_dir / f"{latest_file.stem}_渠道品类矩阵.csv", encoding='utf-8-sig')
log(f"  渠道×品类矩阵: {latest_file.stem}_渠道品类矩阵.csv")

# 3. 渠道×品类明细
channel_category_csv = channel_category.copy()
channel_category_csv.columns = ['渠道', '一级品类', '点击UV', '回流率']
channel_category_csv = channel_category_csv.sort_values(['渠道', '点击UV'], ascending=[True, False])
channel_category_csv.to_csv(output_dir / f"{latest_file.stem}_渠道品类明细.csv", index=False, encoding='utf-8-sig')
log(f"  渠道×品类明细: {latest_file.stem}_渠道品类明细.csv")

# 4. 渠道×二级品类明细
if 'merge二级品类' in df.columns:
    cat2_csv = channel_cat2[channel_cat2['点击uv'] >= 100].copy()
    cat2_csv.columns = ['渠道', '一级品类', '二级品类', '点击UV', '回流率']
    cat2_csv = cat2_csv.sort_values('回流率', ascending=False)
    cat2_csv.to_csv(output_dir / f"{latest_file.stem}_渠道二级品类明细.csv", index=False, encoding='utf-8-sig')
    log(f"  渠道×二级品类明细: {latest_file.stem}_渠道二级品类明细.csv")

    # 5. 渠道×二级品类矩阵（回流率）
    pivot_cat2_ror.to_csv(output_dir / f"{latest_file.stem}_渠道二级品类矩阵.csv", encoding='utf-8-sig')
    log(f"  渠道×二级品类矩阵: {latest_file.stem}_渠道二级品类矩阵.csv")

# 5. 每日趋势
daily_csv = daily_channel.copy()
daily_csv.columns = ['日期', '渠道', '点击UV', '回流率']
daily_csv.to_csv(output_dir / f"{latest_file.stem}_每日趋势.csv", index=False, encoding='utf-8-sig')
log(f"  每日趋势: {latest_file.stem}_每日趋势.csv")
