#!/usr/bin/env python
# coding=utf-8
"""
品类命中分析可视化
分析推荐品类是否命中用户历史品类
横行：命中、未命中、无历史
纵列：再分享品类
"""
import pandas as pd
import json
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)

print(f"分析文件: {latest_file.name}")
print(f"时间范围: {df['dt'].min()} ~ {df['dt'].max()}")

# 日期列表
all_dates = sorted([str(d) for d in df['dt'].unique()])
date_options = ['all'] + all_dates
latest_date = all_dates[-1] if all_dates else 'all'
print(f"日期数: {len(all_dates)}")

# 渠道列表（按UV排序）
channel_uv = df.groupby('channel')['点击uv'].sum().sort_values(ascending=False)
channel_list = channel_uv.index.tolist()
print(f"渠道数: {len(channel_list)}")

# 命中状态顺序
match_order = ['命中', '未命中', '无历史']

# 获取品类标签（处理空值）
def get_cat_label(val):
    if pd.notna(val) and str(val).strip():
        return str(val)
    return '未知'

# 计算渠道×日期×品类级别的矩阵数据
def calc_matrix_data(channel, date=None, level='cat1'):
    """计算指定渠道和日期的命中矩阵"""
    ch_df = df[df['channel'] == channel].copy()
    if date and date != 'all':
        ch_df = ch_df[ch_df['dt'].astype(str) == str(date)]

    if len(ch_df) == 0:
        return None

    # 根据级别选择品类列和命中列
    if level == 'cat1':
        ch_df['再分享品类'] = ch_df['再分享一级品类'].apply(get_cat_label)
        match_col = '一级品类命中'
    else:
        ch_df['再分享品类'] = ch_df['再分享二级品类'].apply(get_cat_label)
        match_col = '二级品类命中'

    # 按命中状态和品类聚合
    matrix = ch_df.groupby([match_col, '再分享品类']).agg({
        '点击uv': 'sum',
        '裂变uv': 'sum',
    }).reset_index()

    # 重新计算各种裂变率
    matrix['整体裂变率'] = matrix['裂变uv'] / (matrix['点击uv'] + 10)

    # 头部裂变率和推荐裂变率需要从原始数据聚合
    orig_agg = ch_df.groupby([match_col, '再分享品类']).apply(
        lambda x: pd.Series({
            '头部裂变率': (x['头部裂变率'] * x['点击uv']).sum() / (x['点击uv'].sum() + 10) if x['点击uv'].sum() > 0 else 0,
            '推荐裂变率': (x['推荐裂变率'] * x['点击uv']).sum() / (x['点击uv'].sum() + 10) if x['点击uv'].sum() > 0 else 0,
        }), include_groups=False
    ).reset_index()

    matrix = matrix.merge(orig_agg, on=[match_col, '再分享品类'], how='left')

    # 生成pivot表
    uv_pivot = matrix.pivot(index=match_col, columns='再分享品类', values='点击uv').fillna(0)
    ror_pivot = matrix.pivot(index=match_col, columns='再分享品类', values='整体裂变率').fillna(0)
    orig_pivot = matrix.pivot(index=match_col, columns='再分享品类', values='头部裂变率').fillna(0)
    rec_pivot = matrix.pivot(index=match_col, columns='再分享品类', values='推荐裂变率').fillna(0)

    # 行按固定顺序，列按总UV排序
    row_order = [m for m in match_order if m in uv_pivot.index]
    col_order = uv_pivot.sum(axis=0).sort_values(ascending=False).index.tolist()

    def to_dict(pivot, is_int=False):
        result = {}
        for r in row_order:
            result[str(r)] = {}
            for c in col_order:
                if r in pivot.index and c in pivot.columns:
                    val = pivot.loc[r, c]
                    result[str(r)][str(c)] = int(val) if is_int else float(val)
                else:
                    result[str(r)][str(c)] = 0
        return result

    return {
        'rows': row_order,
        'cols': col_order,
        'uv': to_dict(uv_pivot, is_int=True),
        'ror': to_dict(ror_pivot),
        'orig': to_dict(orig_pivot),
        'rec': to_dict(rec_pivot),
        'total_uv': int(ch_df['点击uv'].sum()),
        'total_ror': float(ch_df['裂变uv'].sum() / (ch_df['点击uv'].sum() + 10)) if ch_df['点击uv'].sum() > 0 else 0,
    }

# 预计算所有渠道×日期×品类级别的数据
all_data = {}
for ch in channel_list:
    all_data[ch] = {'cat1': {}, 'cat2': {}}
    for dt in date_options:
        for level in ['cat1', 'cat2']:
            matrix = calc_matrix_data(ch, dt, level)
            if matrix:
                all_data[ch][level][dt] = matrix

# 转为JSON
data_json = json.dumps(all_data, ensure_ascii=False)
channel_list_json = json.dumps(channel_list, ensure_ascii=False)
dates_json = json.dumps(date_options)

# 日期选项HTML
date_options_html = "".join([
    f'<option value="{dt}" {"selected" if dt == latest_date else ""}>'
    f'{"汇总" if dt == "all" else dt}</option>'
    for dt in date_options
])

# 渠道选项HTML
channel_options_html = "".join([
    f'<option value="{ch}">{ch}</option>'
    for ch in channel_list
])

html_content = f"""<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>品类命中分析</title>
    <style>
        * {{ margin: 0; padding: 0; box-sizing: border-box; }}
        body {{ font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif;
               background: #f5f5f5; padding: 20px; }}
        .container {{ max-width: 1600px; margin: 0 auto; background: white;
                     border-radius: 8px; padding: 20px; box-shadow: 0 2px 8px rgba(0,0,0,0.1); }}
        h1 {{ font-size: 24px; margin-bottom: 20px; color: #333; }}
        .controls {{ display: flex; gap: 20px; margin-bottom: 20px; align-items: center; flex-wrap: wrap; }}
        .control-group {{ display: flex; align-items: center; gap: 8px; }}
        .control-group label {{ font-weight: 500; color: #666; }}
        select {{ padding: 8px 12px; border: 1px solid #ddd; border-radius: 4px; font-size: 14px; min-width: 150px; }}
        .summary {{ display: flex; gap: 20px; margin-bottom: 20px; flex-wrap: wrap; }}
        .stat-card {{ background: #f8f9fa; padding: 15px 20px; border-radius: 6px; text-align: center; min-width: 120px; }}
        .stat-card h4 {{ font-size: 24px; color: #28a745; margin-bottom: 5px; }}
        .stat-card p {{ font-size: 12px; color: #666; }}
        .matrix-container {{ overflow-x: auto; max-height: 400px; overflow-y: auto; }}
        table {{ border-collapse: collapse; font-size: 12px; }}
        th, td {{ border: 1px solid #e0e0e0; padding: 6px 10px; text-align: center; white-space: nowrap; }}
        th {{ background: #f5f5f5; font-weight: 600; position: sticky; top: 0; z-index: 1; }}
        th:first-child {{ position: sticky; left: 0; z-index: 3; }}
        td:first-child {{ background: #f5f5f5; font-weight: 500; position: sticky; left: 0; z-index: 1; }}
        .legend {{ font-size: 12px; color: #666; margin-bottom: 10px; }}
        .date-switcher {{ display: flex; align-items: center; gap: 5px; }}
        .date-switcher button {{ padding: 5px 10px; border: 1px solid #ddd; background: white;
                                cursor: pointer; border-radius: 3px; }}
        .date-switcher button:hover {{ background: #f0f0f0; }}
        .play-btn.playing {{ background: #28a745; color: white; }}
        .match-hit {{ background: #d4edda !important; }}
        .match-miss {{ background: #f8d7da !important; }}
        .match-none {{ background: #e2e3e5 !important; }}
    </style>
</head>
<body>
    <div class="container">
        <h1>品类命中分析</h1>
        <p style="margin-bottom:20px;color:#666;">分析推荐品类是否命中用户历史品类</p>

        <div class="controls">
            <div class="control-group">
                <label>渠道：</label>
                <select id="channel-select" onchange="updateMatrix()">
                    {channel_options_html}
                </select>
            </div>
            <div class="control-group">
                <label>品类：</label>
                <select id="level-select" onchange="updateMatrix()">
                    <option value="cat1">一级品类</option>
                    <option value="cat2">二级品类</option>
                </select>
            </div>
            <div class="control-group">
                <label>指标：</label>
                <select id="metric-select" onchange="updateMatrix()">
                    <option value="rec" selected>推荐裂变率</option>
                    <option value="ror">整体裂变率</option>
                    <option value="orig">头部裂变率</option>
                    <option value="uv">点击UV</option>
                </select>
            </div>
            <div class="control-group date-switcher">
                <label>日期：</label>
                <button onclick="switchDate(-1)">◀</button>
                <select id="date-select" onchange="updateMatrix()">
                    {date_options_html}
                </select>
                <button onclick="switchDate(1)">▶</button>
                <button id="play-btn" onclick="togglePlay()">▶</button>
            </div>
        </div>

        <div class="summary" id="summary">
            <!-- 由JS填充 -->
        </div>

        <div class="legend">
            行=命中状态（<span class="match-hit" style="padding:2px 8px;">命中</span>
            <span class="match-miss" style="padding:2px 8px;">未命中</span>
            <span class="match-none" style="padding:2px 8px;">无历史</span>）
            列=再分享品类 | 颜色越深=数值越高
            <button onclick="resetSort()" style="margin-left:15px;padding:3px 10px;cursor:pointer;">重置排序</button>
        </div>

        <div class="matrix-container">
            <table id="matrix-table">
                <thead id="matrix-header"></thead>
                <tbody id="matrix-body"></tbody>
            </table>
        </div>
    </div>

    <script>
    const allData = {data_json};
    const channelList = {channel_list_json};
    const dates = {dates_json};
    let playInterval = null;
    let currentColOrder = null;
    let lastChannel = null;
    let lastLevel = null;

    function getGradient(val, maxVal, minVal = 0) {{
        if (val <= minVal || maxVal <= minVal) return '#f8f9fa';
        const ratio = Math.min((val - minVal) / (maxVal - minVal), 1);
        const r = Math.round(255 - ratio * 215);
        const g = Math.round(255 - ratio * 88);
        const b = Math.round(255 - ratio * 186);
        return `rgb(${{r}},${{g}},${{b}})`;
    }}

    function getRowClass(match) {{
        if (match === '命中') return 'match-hit';
        if (match === '未命中') return 'match-miss';
        return 'match-none';
    }}

    function updateMatrix() {{
        const channel = document.getElementById('channel-select').value;
        const level = document.getElementById('level-select').value;
        const metric = document.getElementById('metric-select').value;
        const date = document.getElementById('date-select').value;

        if (!allData[channel] || !allData[channel][level] || !allData[channel][level][date]) {{
            document.getElementById('summary').innerHTML = '<div class="stat-card"><h4>无数据</h4><p>该渠道/日期无数据</p></div>';
            document.getElementById('matrix-header').innerHTML = '';
            document.getElementById('matrix-body').innerHTML = '';
            return;
        }}

        const data = allData[channel][level][date];
        const levelLabel = level === 'cat1' ? '一级' : '二级';

        // 渠道或品类级别变化时重置排序
        if (channel !== lastChannel || level !== lastLevel) {{
            currentColOrder = null;
            lastChannel = channel;
            lastLevel = level;
        }}

        // 初始化列顺序
        if (!currentColOrder || !currentColOrder.some(c => data.cols.includes(c))) {{
            currentColOrder = [...data.cols];
        }}

        const rows = data.rows;  // 行顺序固定：命中、未命中、无历史
        const cols = currentColOrder.filter(c => data.cols.includes(c));

        // 计算行/列UV总和
        const uvData = data.uv;
        const rowUvTotals = {{}};
        const colUvTotals = {{}};
        rows.forEach(r => {{
            rowUvTotals[r] = cols.reduce((sum, c) => sum + (uvData[r]?.[c] || 0), 0);
        }});
        cols.forEach(c => {{
            colUvTotals[c] = rows.reduce((sum, r) => sum + (uvData[r]?.[c] || 0), 0);
        }});

        // 更新汇总
        document.getElementById('summary').innerHTML = `
            <div class="stat-card">
                <h4>${{data.total_uv.toLocaleString()}}</h4>
                <p>总点击UV</p>
            </div>
            <div class="stat-card">
                <h4>${{data.total_ror.toFixed(4)}}</h4>
                <p>整体裂变率</p>
            </div>
            <div class="stat-card match-hit">
                <h4>${{rowUvTotals['命中']?.toLocaleString() || 0}}</h4>
                <p>命中UV</p>
            </div>
            <div class="stat-card match-miss">
                <h4>${{rowUvTotals['未命中']?.toLocaleString() || 0}}</h4>
                <p>未命中UV</p>
            </div>
            <div class="stat-card match-none">
                <h4>${{rowUvTotals['无历史']?.toLocaleString() || 0}}</h4>
                <p>无历史UV</p>
            </div>
            <div class="stat-card">
                <h4>${{data.cols.length}}</h4>
                <p>再分享${{levelLabel}}品类数</p>
            </div>
        `;

        // 计算95分位数作为渐变上限
        const metricData = data[metric];
        const allVals = [];
        rows.forEach(r => {{
            cols.forEach(c => {{
                const val = metricData[r]?.[c] || 0;
                if (val > 0) allVals.push(val);
            }});
        }});
        allVals.sort((a, b) => a - b);
        const p95Idx = Math.floor(allVals.length * 0.95);
        let maxVal = allVals.length > 0 ? allVals[Math.min(p95Idx, allVals.length - 1)] : 0;
        const thresholds = {{ uv: 1000, ror: 0.3, orig: 0.1, rec: 0.2 }};
        maxVal = Math.max(maxVal, thresholds[metric] || 0.3);

        // 生成表头
        const metricLabels = {{ uv: '点击UV', ror: '整体裂变率', orig: '头部裂变率', rec: '推荐裂变率' }};
        document.getElementById('matrix-header').innerHTML = `
            <tr>
                <th style="font-size:10px;line-height:1.3">命中状态<br>再分享品类→</th>
                ${{cols.map(c => `<th style="cursor:pointer" onclick="sortByCol('${{c}}')" title="再分享品类: ${{c}}&#10;点击UV: ${{colUvTotals[c]?.toLocaleString()}}">${{c}}</th>`).join('')}}
            </tr>
        `;

        // 生成数据行
        document.getElementById('matrix-body').innerHTML = rows.map(r => {{
            const cells = cols.map(c => {{
                const val = metricData[r]?.[c] || 0;
                const cellUv = uvData[r]?.[c] || 0;
                const bg = getGradient(val, maxVal);
                const display = metric === 'uv' ? parseInt(val).toLocaleString() : val.toFixed(4);
                const tipMetric = metric === 'uv' ? '' : `${{metricLabels[metric]}}: ${{val.toFixed(4)}}&#10;`;
                const rowPct = rowUvTotals[r] > 0 ? (cellUv / rowUvTotals[r] * 100).toFixed(1) : '0.0';
                const colPct = colUvTotals[c] > 0 ? (cellUv / colUvTotals[c] * 100).toFixed(1) : '0.0';
                return `<td style="background:${{bg}}" title="命中: ${{r}}&#10;再分享: ${{c}}&#10;${{tipMetric}}点击UV: ${{cellUv.toLocaleString()}}&#10;横向占比: ${{rowPct}}%&#10;纵向占比: ${{colPct}}%">${{display}}</td>`;
            }}).join('');
            return `<tr><td class="${{getRowClass(r)}}" title="命中状态: ${{r}}&#10;点击UV: ${{rowUvTotals[r]?.toLocaleString()}}">${{r}}</td>${{cells}}</tr>`;
        }}).join('');
    }}

    function sortByCol(colName) {{
        const channel = document.getElementById('channel-select').value;
        const level = document.getElementById('level-select').value;
        const date = document.getElementById('date-select').value;
        const metric = document.getElementById('metric-select').value;

        if (!allData[channel] || !allData[channel][level] || !allData[channel][level][date]) return;
        const data = allData[channel][level][date];
        const metricData = data[metric];

        // 按该列的值排序（命中在前，值从高到低）
        // 这里只排序列，不排序行
        currentColOrder = [...data.cols].sort((a, b) => {{
            const sumA = data.rows.reduce((sum, r) => sum + (metricData[r]?.[a] || 0), 0);
            const sumB = data.rows.reduce((sum, r) => sum + (metricData[r]?.[b] || 0), 0);
            return sumB - sumA;
        }});

        updateMatrix();
    }}

    function resetSort() {{
        currentColOrder = null;
        updateMatrix();
    }}

    function switchDate(delta) {{
        const select = document.getElementById('date-select');
        const idx = dates.indexOf(select.value);
        const newIdx = idx + delta;
        if (newIdx >= 0 && newIdx < dates.length) {{
            select.value = dates[newIdx];
            updateMatrix();
        }}
    }}

    function togglePlay() {{
        const btn = document.getElementById('play-btn');
        if (playInterval) {{
            clearInterval(playInterval);
            playInterval = null;
            btn.classList.remove('playing');
            btn.textContent = '▶';
        }} else {{
            btn.classList.add('playing');
            btn.textContent = '⏸';
            let idx = 0;
            const play = () => {{
                if (idx >= dates.length) {{
                    clearInterval(playInterval);
                    playInterval = null;
                    btn.classList.remove('playing');
                    btn.textContent = '▶';
                    return;
                }}
                document.getElementById('date-select').value = dates[idx];
                updateMatrix();
                idx++;
            }};
            play();
            playInterval = setInterval(play, 1500);
        }}
    }}

    // 初始化
    updateMatrix();
    </script>
</body>
</html>
"""

# 保存HTML
html_file = output_dir / f"{latest_file.stem}_品类命中.html"
with open(html_file, 'w', encoding='utf-8') as f:
    f.write(html_content)

print(f"\nHTML 报告已生成: {html_file}")
