#!/usr/bin/env python
# coding=utf-8
"""
素材视频内容分析 - HTML 可视化（简化版：相似度 vs 回流率）
"""
import pandas as pd
import numpy as np
from pathlib import Path
import json

# 找到最新的含相似度文件
task_dir = Path(__file__).parent
output_dir = task_dir / "output"
csv_files = list(output_dir.glob("*含相似度*.csv"))
if not csv_files:
    csv_files = list(output_dir.glob("*.csv"))
if not csv_files:
    print("没有找到数据文件")
    exit(1)

latest_file = max(csv_files, key=lambda x: x.stat().st_mtime)
print(f"读取文件: {latest_file.name}")
df = pd.read_csv(latest_file)

# 相似度列配置
similarity_cols = [
    ('分享标题_视频标题_相似度', '分享标题与视频标题'),
    ('分享标题_口播_相似度', '分享标题与口播内容'),
    ('分享标题_一级品类_相似度', '分享标题与一级品类'),
    ('分享标题_二级品类_相似度', '分享标题与二级品类'),
    ('文章标题_视频标题_相似度', '文章标题与视频标题'),
    ('文章标题_口播_相似度', '文章标题与口播内容'),
]

# 过滤有效的相似度列
valid_cols = [(col, label) for col, label in similarity_cols
              if col in df.columns and df[col].notna().sum() > 100]

print(f"有效相似度指标: {len(valid_cols)} 个")

# 回流率字段
rate_cols = ['再分享回流率', '原视频再分享回流率', '推荐再分享回流率']
rate_cols = [c for c in rate_cols if c in df.columns]

# 准备原始数据表（取关键字段）- 调整列顺序
table_cols = ['dt', 'channel', 'hotsencetype', '合作方名', '公众号名']  # 日期、渠道、场景、合作方、公众号在最前
table_cols += ['文章标题', '分享封面', '分享标题', 'title', 'videoid']  # 标题、封面和视频ID
table_cols += rate_cols  # 三个回流率
table_cols += ['点击uv']  # 点击量
table_cols += [col for col, _ in valid_cols]  # 相似度
table_cols += ['merge一级品类', 'merge二级品类']  # 品类在后
table_cols = [c for c in table_cols if c in df.columns]

# 过滤有相似度数据的记录
raw_df = df[df[[col for col, _ in valid_cols[:2]]].notna().any(axis=1)].copy()

# 计算分享标题聚合UV
share_title_uv = raw_df.groupby('分享标题')['点击uv'].transform('sum')
raw_df['分享标题聚合UV'] = share_title_uv

# 按分享标题聚合UV排序，再按点击UV排序，取前2000条
raw_df = raw_df.sort_values(['分享标题聚合UV', '点击uv'], ascending=[False, False]).head(2000)

# 更新table_cols，加入聚合UV
table_cols_with_agg = table_cols.copy()
# 在点击uv后面插入分享标题聚合UV
if '点击uv' in table_cols_with_agg:
    idx = table_cols_with_agg.index('点击uv')
    table_cols_with_agg.insert(idx, '分享标题聚合UV')

raw_data = raw_df[table_cols_with_agg].fillna('').to_dict('records')
table_cols = table_cols_with_agg

# 相似度分组统计
bins = [0, 0.3, 0.5, 0.7, 0.9, 1.0]
labels_bin = ['0-0.3', '0.3-0.5', '0.5-0.7', '0.7-0.9', '0.9-1.0']

group_stats = []
for col, label in valid_cols:
    df['_group'] = pd.cut(df[col], bins=bins, labels=labels_bin)

    stats = []
    for grp in labels_bin:
        grp_df = df[df['_group'] == grp]
        if len(grp_df) == 0:
            continue

        row = {
            'group': grp,
            'count': len(grp_df),
            'click_uv': int(grp_df['点击uv'].sum()),
        }

        # 计算加权平均回流率（保持原始小数）
        for rate_col in rate_cols:
            weighted = (grp_df[rate_col] * grp_df['点击uv']).sum()
            total_click = grp_df['点击uv'].sum()
            row[rate_col] = round(weighted / (total_click + 1), 4) if total_click > 0 else 0

        stats.append(row)

    group_stats.append({
        'label': label,
        'col': col,
        'stats': stats
    })

if '_group' in df.columns:
    df.drop(columns=['_group'], inplace=True)

# 列名映射（用于表头显示）
col_labels = {col: label for col, label in valid_cols}
col_labels.update({
    'dt': '日期',
    'channel': '渠道',
    'hotsencetype': '场景类型',
    '合作方名': '合作方',
    '公众号名': '公众号',
    '文章标题': '文章标题',
    '分享标题': '分享标题',
    '分享封面': '分享封面',
    'title': '视频标题',
    'videoid': '视频ID',
    'merge一级品类': '一级品类',
    'merge二级品类': '二级品类',
    '分享标题聚合UV': '分享标题聚合UV',
    '点击uv': '点击UV',
    '再分享回流率': '再分享回流率',
    '原视频再分享回流率': '原视频回流率',
    '推荐再分享回流率': '推荐回流率',
})

# 获取筛选项的唯一值
date_list = sorted(df['dt'].dropna().unique().tolist()) if 'dt' in df.columns else []
channel_list = sorted(df['channel'].dropna().unique().tolist()) if 'channel' in df.columns else []
hotsencetype_list = sorted(df['hotsencetype'].dropna().unique().tolist()) if 'hotsencetype' in df.columns else []
partner_list = sorted(df['合作方名'].dropna().unique().tolist()) if '合作方名' in df.columns else []
account_list = sorted(df['公众号名'].dropna().unique().tolist()) if '公众号名' in df.columns else []

# 生成 HTML
html_content = f'''<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>相似度 vs 回流率分析</title>
    <script src="https://cdn.jsdelivr.net/npm/echarts@5.4.3/dist/echarts.min.js"></script>
    <style>
        * {{ margin: 0; padding: 0; box-sizing: border-box; }}
        body {{
            font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, sans-serif;
            background: #f5f7fa;
            padding: 20px;
        }}
        .container {{ max-width: 1600px; margin: 0 auto; }}
        h1 {{ text-align: center; color: #333; margin-bottom: 20px; }}
        .section {{
            background: white;
            border-radius: 12px;
            padding: 20px;
            margin-bottom: 20px;
            box-shadow: 0 2px 8px rgba(0,0,0,0.08);
        }}
        .section h2 {{
            color: #333;
            margin-bottom: 15px;
            padding-bottom: 10px;
            border-bottom: 2px solid #667eea;
            display: inline-block;
        }}

        /* 图表网格 */
        .chart-grid {{
            display: grid;
            grid-template-columns: repeat(3, 1fr);
            gap: 15px;
        }}
        .chart-item {{ height: 300px; }}

        /* 可排序表格 */
        .table-controls {{
            display: flex;
            gap: 15px;
            margin-bottom: 15px;
            flex-wrap: wrap;
            align-items: center;
        }}
        .table-controls input {{
            padding: 8px 12px;
            border: 1px solid #ddd;
            border-radius: 6px;
            width: 300px;
        }}
        .table-controls select {{
            padding: 8px 12px;
            border: 1px solid #ddd;
            border-radius: 6px;
        }}
        .table-wrapper {{
            overflow-x: auto;
            max-height: 600px;
            overflow-y: auto;
        }}
        table {{
            width: 100%;
            border-collapse: collapse;
            font-size: 13px;
        }}
        th {{
            background: #667eea;
            color: white;
            padding: 10px 8px;
            text-align: left;
            cursor: pointer;
            user-select: none;
            white-space: nowrap;
            position: sticky;
            top: 0;
            z-index: 10;
        }}
        th:hover {{ background: #5a6fd6; }}
        th .sort-icon {{ margin-left: 5px; opacity: 0.5; }}
        th.sorted .sort-icon {{ opacity: 1; }}
        td {{
            padding: 8px;
            border-bottom: 1px solid #eee;
            max-width: 250px;
            overflow: hidden;
            text-overflow: ellipsis;
            white-space: nowrap;
        }}
        td.wrap {{
            white-space: normal;
            word-break: break-word;
            min-width: 180px;
            max-width: 220px;
        }}
        tr:hover {{ background: #f8f9fa; }}
        tr:nth-child(even) {{ background: #fafbfc; }}
        tr:nth-child(even):hover {{ background: #f0f1f2; }}
        .num {{ text-align: right; font-family: monospace; }}
        .highlight {{ background: #fff3cd !important; }}

        /* 图片预览模态框 */
        .img-modal {{
            display: none;
            position: fixed;
            top: 0;
            left: 0;
            width: 100%;
            height: 100%;
            background: rgba(0,0,0,0.8);
            z-index: 1000;
            cursor: pointer;
            justify-content: center;
            align-items: center;
        }}
        .img-modal img {{
            max-width: 90%;
            max-height: 90%;
            border-radius: 8px;
            box-shadow: 0 4px 20px rgba(0,0,0,0.3);
        }}
        .img-modal.show {{ display: flex; }}

        /* 统计表格 */
        .stats-table {{ margin-top: 10px; }}
        .stats-table th {{ background: #5a6fd6; font-size: 12px; }}
        .stats-table td {{ font-size: 12px; padding: 6px 8px; }}

        @media (max-width: 1200px) {{
            .chart-grid {{ grid-template-columns: repeat(2, 1fr); }}
        }}
        @media (max-width: 768px) {{
            .chart-grid {{ grid-template-columns: 1fr; }}
        }}
    </style>
</head>
<body>
    <!-- 图片预览模态框 -->
    <div id="imgModal" class="img-modal" onclick="closeImgModal()">
        <img id="modalImg" src="" alt="预览图片">
    </div>

    <div class="container">
        <h1>相似度 vs 回流率分析</h1>

        <!-- 分组统计图表 -->
        <div class="section">
            <h2>相似度分组 vs 回流率</h2>
            <div class="chart-grid">
                {' '.join(f'<div id="chart_{i}" class="chart-item"></div>' for i in range(len(group_stats)))}
            </div>
        </div>

        <!-- 分组统计表格 -->
        <div class="section">
            <h2>分组详细数据</h2>
            <div id="statsTablesContainer"></div>
        </div>

        <!-- 原始数据表 -->
        <div class="section">
            <h2>原始数据（Top 2000 by 点击UV）</h2>
            <div class="table-controls">
                <select id="dateFilter">
                    <option value="">全部日期</option>
                    {' '.join(f'<option value="{d}">{d}</option>' for d in date_list)}
                </select>
                <select id="channelFilter">
                    <option value="">全部渠道</option>
                    {' '.join(f'<option value="{c}">{c}</option>' for c in channel_list)}
                </select>
                <select id="hotsencetypeFilter">
                    <option value="">全部场景</option>
                    {' '.join(f'<option value="{h}">{h}</option>' for h in hotsencetype_list)}
                </select>
                <select id="partnerFilter">
                    <option value="">全部合作方</option>
                    {' '.join(f'<option value="{p}">{p}</option>' for p in partner_list)}
                </select>
                <select id="accountFilter">
                    <option value="">全部公众号</option>
                    {' '.join(f'<option value="{a}">{a}</option>' for a in account_list)}
                </select>
                <input type="text" id="searchInput" placeholder="搜索标题...">
                <select id="simFilter">
                    <option value="">全部相似度</option>
                    <option value="high">高相似度 (≥0.7)</option>
                    <option value="mid">中相似度 (0.3-0.7)</option>
                    <option value="low">低相似度 (<0.3)</option>
                </select>
                <label>点击UV ≥ <input type="number" id="minUvInput" value="100" min="0" style="width:80px;padding:8px;border:1px solid #ddd;border-radius:6px;"></label>
                <span id="rowCount" style="color:#666;"></span>
            </div>
            <div class="table-wrapper">
                <table id="dataTable">
                    <thead>
                        <tr id="headerRow"></tr>
                    </thead>
                    <tbody id="tableBody"></tbody>
                </table>
            </div>
        </div>
    </div>

    <script>
        // 数据
        const rawData = {json.dumps(raw_data, ensure_ascii=False, default=str)};
        const groupStats = {json.dumps(group_stats, ensure_ascii=False)};
        const tableCols = {json.dumps(table_cols, ensure_ascii=False)};
        const colLabels = {json.dumps(col_labels, ensure_ascii=False)};
        const rateCols = {json.dumps(rate_cols, ensure_ascii=False)};
        const validCols = {json.dumps([col for col, _ in valid_cols], ensure_ascii=False)};

        // 当前排序状态
        let currentSort = {{ col: '分享标题聚合UV', dir: 'desc' }};
        let filteredData = [...rawData];

        // 渲染图表
        function renderCharts() {{
            groupStats.forEach((gs, idx) => {{
                const chart = echarts.init(document.getElementById('chart_' + idx));
                const groups = gs.stats.map(s => s.group);

                const series = rateCols.map((rc, i) => ({{
                    name: colLabels[rc] || rc,
                    type: 'bar',
                    data: gs.stats.map(s => s[rc] || 0),
                    itemStyle: {{ color: ['#667eea', '#f093fb', '#43e97b'][i] }}
                }}));

                chart.setOption({{
                    title: {{ text: gs.label, left: 'center', textStyle: {{ fontSize: 14 }} }},
                    tooltip: {{ trigger: 'axis' }},
                    legend: {{ bottom: 0, textStyle: {{ fontSize: 10 }} }},
                    xAxis: {{ type: 'category', data: groups, axisLabel: {{ fontSize: 11 }} }},
                    yAxis: {{ type: 'value', name: '回流率(%)', axisLabel: {{ fontSize: 10 }} }},
                    series: series,
                    grid: {{ top: 50, bottom: 60, left: 50, right: 20 }}
                }});
            }});
        }}

        // 渲染统计表格
        function renderStatsTables() {{
            const container = document.getElementById('statsTablesContainer');
            let html = '<div style="display:grid;grid-template-columns:repeat(2,1fr);gap:20px;">';

            groupStats.forEach(gs => {{
                html += `<div>
                    <h4 style="margin-bottom:10px;color:#333;">${{gs.label}}</h4>
                    <table class="stats-table">
                        <tr>
                            <th>相似度区间</th>
                            <th>记录数</th>
                            <th>点击UV</th>
                            ${{rateCols.map(rc => `<th>${{colLabels[rc] || rc}}</th>`).join('')}}
                        </tr>`;

                gs.stats.forEach(row => {{
                    html += `<tr>
                        <td>${{row.group}}</td>
                        <td class="num">${{row.count.toLocaleString()}}</td>
                        <td class="num">${{row.click_uv.toLocaleString()}}</td>
                        ${{rateCols.map(rc => `<td class="num">${{(row[rc] || 0).toFixed(4)}}</td>`).join('')}}
                    </tr>`;
                }});

                html += '</table></div>';
            }});

            html += '</div>';
            container.innerHTML = html;
        }}

        // 渲染表头
        function renderHeader() {{
            const headerRow = document.getElementById('headerRow');
            headerRow.innerHTML = tableCols.map(col => {{
                const label = colLabels[col] || col;
                const isSorted = currentSort.col === col;
                const icon = isSorted ? (currentSort.dir === 'asc' ? '▲' : '▼') : '▼';
                return `<th class="${{isSorted ? 'sorted' : ''}}" onclick="sortBy('${{col}}')">
                    ${{label}}<span class="sort-icon">${{icon}}</span>
                </th>`;
            }}).join('');
        }}

        // 计算每列的最大最小值（用于渐变）
        function getColumnRange(data, col) {{
            const vals = data.map(r => r[col]).filter(v => typeof v === 'number' && !isNaN(v));
            if (vals.length === 0) return {{ min: 0, max: 1 }};
            return {{ min: Math.min(...vals), max: Math.max(...vals) }};
        }}

        // 根据值获取渐变背景色（绿色系）
        function getGradientColor(val, min, max) {{
            if (typeof val !== 'number' || isNaN(val)) return '';
            const ratio = max > min ? (val - min) / (max - min) : 0;
            // 绿色系，alpha 从 0.05 到 0.6
            const alpha = 0.05 + ratio * 0.55;
            return `rgba(34, 197, 94, ${{alpha}})`;
        }}

        // 渲染表格数据
        function renderTable() {{
            const tbody = document.getElementById('tableBody');
            const search = document.getElementById('searchInput').value.toLowerCase();
            const simFilter = document.getElementById('simFilter').value;

            // 筛选条件
            const minUv = parseInt(document.getElementById('minUvInput').value) || 0;
            const dateFilter = document.getElementById('dateFilter').value;
            const channelFilter = document.getElementById('channelFilter').value;
            const hotsencetypeFilter = document.getElementById('hotsencetypeFilter').value;
            const partnerFilter = document.getElementById('partnerFilter').value;
            const accountFilter = document.getElementById('accountFilter').value;

            // 过滤
            filteredData = rawData.filter(row => {{
                // 日期过滤（转字符串比较）
                if (dateFilter && String(row['dt']) !== dateFilter) return false;

                // 渠道过滤
                if (channelFilter && row['channel'] !== channelFilter) return false;

                // 场景类型过滤（转字符串比较）
                if (hotsencetypeFilter && String(row['hotsencetype']) !== hotsencetypeFilter) return false;

                // 合作方过滤
                if (partnerFilter && row['合作方名'] !== partnerFilter) return false;

                // 公众号过滤
                if (accountFilter && row['公众号名'] !== accountFilter) return false;

                // 点击UV过滤
                if (row['点击uv'] < minUv) return false;

                // 搜索过滤
                if (search) {{
                    const title1 = (row['分享标题'] || '').toLowerCase();
                    const title2 = (row['title'] || '').toLowerCase();
                    if (!title1.includes(search) && !title2.includes(search)) return false;
                }}

                // 相似度过滤
                if (simFilter) {{
                    const simVal = validCols.map(c => row[c]).find(v => v !== '' && v !== null);
                    if (simVal === undefined) return false;
                    if (simFilter === 'high' && simVal < 0.7) return false;
                    if (simFilter === 'mid' && (simVal < 0.3 || simVal >= 0.7)) return false;
                    if (simFilter === 'low' && simVal >= 0.3) return false;
                }}

                return true;
            }});

            // 排序
            filteredData.sort((a, b) => {{
                let va = a[currentSort.col];
                let vb = b[currentSort.col];

                // 处理空值
                if (va === '' || va === null) va = currentSort.dir === 'asc' ? Infinity : -Infinity;
                if (vb === '' || vb === null) vb = currentSort.dir === 'asc' ? Infinity : -Infinity;

                // 数值比较
                if (typeof va === 'number' && typeof vb === 'number') {{
                    return currentSort.dir === 'asc' ? va - vb : vb - va;
                }}

                // 字符串比较
                va = String(va);
                vb = String(vb);
                return currentSort.dir === 'asc' ? va.localeCompare(vb) : vb.localeCompare(va);
            }});

            // 计算全局列范围（用于渐变）
            const globalRanges = {{}};
            tableCols.forEach(col => {{
                globalRanges[col] = getColumnRange(filteredData, col);
            }});

            // 渲染行
            tbody.innerHTML = filteredData.map(row => {{
                return '<tr>' + tableCols.map(col => {{
                    let val = row[col];
                    const isNum = typeof val === 'number';

                    if (val === '' || val === null || val === undefined) {{
                        return '<td>-</td>';
                    }}

                    // 分享封面 - 显示为图片缩略图，点击放大预览
                    if (col === '分享封面') {{
                        return `<td><img src="${{val}}" style="max-width:80px;max-height:60px;cursor:pointer;border-radius:4px;" onclick="showImgModal('${{val}}')" onerror="this.style.display='none'"></td>`;
                    }}

                    // videoid - 显示为超链接（优先处理，避免被数字判断拦截）
                    if (col === 'videoid') {{
                        return `<td><a href="https://admin.piaoquantv.com/cms/post-detail/${{val}}/detail" target="_blank" style="color:#667eea;text-decoration:none;">${{val}}</a></td>`;
                    }}

                    // 日期、场景类型 - 强制显示为字符串
                    if (col === 'dt' || col === 'hotsencetype') {{
                        return `<td>${{val}}</td>`;
                    }}

                    if (isNum) {{
                        const range = globalRanges[col] || {{ min: 0, max: 1 }};
                        let displayVal = '';
                        let needGradient = false;

                        // 相似度列 - 需要渐变
                        if (col.includes('相似度')) {{
                            displayVal = val.toFixed(3);
                            needGradient = true;
                        }}
                        // 回流率列 - 需要渐变
                        else if (col.includes('回流率')) {{
                            displayVal = val.toFixed(4);
                            needGradient = true;
                        }}
                        // UV列 - 不需要渐变
                        else if (col.includes('UV') || col.includes('uv')) {{
                            displayVal = Math.round(val).toLocaleString();
                        }}
                        else {{
                            displayVal = val.toFixed(2);
                        }}

                        const bgColor = needGradient ? getGradientColor(val, range.min, range.max) : '';
                        const style = bgColor ? `style="background:${{bgColor}}"` : '';
                        return `<td class="num" ${{style}}>${{displayVal}}</td>`;
                    }}

                    // 标题列 - 允许换行不截断
                    if (col === 'title' || col === '分享标题' || col === '文章标题') {{
                        return `<td class="wrap">${{val}}</td>`;
                    }}

                    // 其他文本列，截断显示
                    const displayVal = String(val).substring(0, 40) + (String(val).length > 40 ? '...' : '');
                    return `<td title="${{val}}">${{displayVal}}</td>`;
                }}).join('') + '</tr>';
            }}).join('');

            document.getElementById('rowCount').textContent = `显示 ${{filteredData.length}} 条`;
        }}

        // 排序
        function sortBy(col) {{
            if (currentSort.col === col) {{
                currentSort.dir = currentSort.dir === 'asc' ? 'desc' : 'asc';
            }} else {{
                currentSort.col = col;
                currentSort.dir = 'desc';
            }}
            renderHeader();
            renderTable();
        }}

        // 图片预览功能
        function showImgModal(src) {{
            document.getElementById('modalImg').src = src;
            document.getElementById('imgModal').classList.add('show');
        }}
        function closeImgModal() {{
            document.getElementById('imgModal').classList.remove('show');
        }}
        // ESC 关闭预览
        document.addEventListener('keydown', (e) => {{
            if (e.key === 'Escape') closeImgModal();
        }});

        // 事件绑定
        document.getElementById('searchInput').addEventListener('input', renderTable);
        document.getElementById('simFilter').addEventListener('change', renderTable);
        document.getElementById('minUvInput').addEventListener('input', renderTable);
        document.getElementById('dateFilter').addEventListener('change', renderTable);
        document.getElementById('channelFilter').addEventListener('change', renderTable);
        document.getElementById('hotsencetypeFilter').addEventListener('change', renderTable);
        document.getElementById('partnerFilter').addEventListener('change', renderTable);
        document.getElementById('accountFilter').addEventListener('change', renderTable);

        // 初始化
        renderCharts();
        renderStatsTables();
        renderHeader();
        renderTable();

        // 响应式
        window.addEventListener('resize', () => {{
            groupStats.forEach((_, idx) => {{
                echarts.getInstanceByDom(document.getElementById('chart_' + idx))?.resize();
            }});
        }});
    </script>
</body>
</html>
'''

# 保存 HTML
output_path = output_dir / '素材视频内容分析.html'
with open(output_path, 'w', encoding='utf-8') as f:
    f.write(html_content)

print(f"\n已保存: {output_path}")
