#!/usr/bin/env python
# coding=utf-8
"""
头部品类分析可视化
Tab 1: Matrix - 头部品类 × 推荐品类矩阵
Tab 2: Compare - Top 10 品类人群对比
"""
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("query_*.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"时间范围: {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)}")

# 人群列表
crowd_list = ['内部', '外部0层', '外部裂变']
print(f"人群: {crowd_list}")

# 曝光阈值
EXP_THRESHOLD = 1000

# 计算人群×日期的矩阵数据
def calc_matrix_data(crowd, date=None):
    ch_df = df[df['crowd'] == crowd].copy()
    if date and date != 'all':
        ch_df = ch_df[ch_df['dt'].astype(str) == str(date)]
    if len(ch_df) == 0:
        return None

    row_col = 'head_cate2'
    col_col = 'rec_cate2'

    matrix = ch_df.groupby([row_col, col_col]).agg({
        'exp': 'sum',
        'share_cnt': 'sum',
        'return_n_uv': 'sum',
        'new_exposure_cnt': 'sum',
    }).reset_index()

    matrix = matrix[matrix['exp'] >= EXP_THRESHOLD]
    if len(matrix) == 0:
        return None

    matrix['str'] = matrix['share_cnt'] / (matrix['exp'] + 1)
    matrix['ros'] = matrix['return_n_uv'] / (matrix['share_cnt'] + 1)
    matrix['rovn'] = matrix['return_n_uv'] / (matrix['exp'] + 1)
    matrix['vov'] = matrix['new_exposure_cnt'] / (matrix['exp'] + 1)

    exp_pivot = matrix.pivot(index=row_col, columns=col_col, values='exp').fillna(0)
    str_pivot = matrix.pivot(index=row_col, columns=col_col, values='str').fillna(0)
    ros_pivot = matrix.pivot(index=row_col, columns=col_col, values='ros').fillna(0)
    rovn_pivot = matrix.pivot(index=row_col, columns=col_col, values='rovn').fillna(0)
    vov_pivot = matrix.pivot(index=row_col, columns=col_col, values='vov').fillna(0)

    row_order = exp_pivot.sum(axis=1).sort_values(ascending=False).index.tolist()
    col_order = exp_pivot.sum(axis=0).sort_values(ascending=False).index.tolist()

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

    total_exp = int(ch_df['exp'].sum())
    total_share = int(ch_df['share_cnt'].sum())
    total_return = int(ch_df['return_n_uv'].sum())

    return {
        'rows': row_order,
        'cols': col_order,
        'exp': to_dict(exp_pivot, is_int=True),
        'str': to_dict(str_pivot),
        'ros': to_dict(ros_pivot),
        'rovn': to_dict(rovn_pivot),
        'vov': to_dict(vov_pivot),
        'total_exp': total_exp,
        'total_str': round(total_share / (total_exp + 1), 4),
        'total_rovn': round(total_return / (total_exp + 1), 4),
    }

# 计算头部品类下钻数据：head_cate2 -> crowd -> rec_cate2
def calc_head_drill_data(date=None):
    ch_df = df.copy()
    if date and date != 'all':
        ch_df = ch_df[ch_df['dt'].astype(str) == str(date)]
    if len(ch_df) == 0:
        return None

    # 按 head_cate2 + crowd + rec_cate2 聚合
    agg = ch_df.groupby(['head_cate2', 'crowd', 'rec_cate2']).agg({
        'exp': 'sum',
        'share_cnt': 'sum',
        'return_n_uv': 'sum',
        'new_exposure_cnt': 'sum',
    }).reset_index()

    agg['str'] = agg['share_cnt'] / (agg['exp'] + 1)
    agg['ros'] = agg['return_n_uv'] / (agg['share_cnt'] + 1)
    agg['rovn'] = agg['return_n_uv'] / (agg['exp'] + 1)
    agg['vov'] = agg['new_exposure_cnt'] / (agg['exp'] + 1)

    # 构建嵌套字典: head_cate2 -> crowd -> {rec_cate2: metrics}
    result = {}

    # 添加 "all" 选项：不区分头部品类，按 crowd + rec_cate2 聚合
    agg_all = ch_df.groupby(['crowd', 'rec_cate2']).agg({
        'exp': 'sum',
        'share_cnt': 'sum',
        'return_n_uv': 'sum',
        'new_exposure_cnt': 'sum',
    }).reset_index()
    agg_all['str'] = agg_all['share_cnt'] / (agg_all['exp'] + 1)
    agg_all['ros'] = agg_all['return_n_uv'] / (agg_all['share_cnt'] + 1)
    agg_all['rovn'] = agg_all['return_n_uv'] / (agg_all['exp'] + 1)
    agg_all['vov'] = agg_all['new_exposure_cnt'] / (agg_all['exp'] + 1)

    result['all'] = {}
    for crowd in crowd_list:
        crowd_df = agg_all[agg_all['crowd'] == crowd]
        result['all'][crowd] = {}
        # 计算整体汇总
        total_exp = int(crowd_df['exp'].sum())
        total_share = crowd_df['share_cnt'].sum()
        total_return = crowd_df['return_n_uv'].sum()
        total_new_exp = crowd_df['new_exposure_cnt'].sum()
        result['all'][crowd]['_total'] = {
            'exp': total_exp,
            'str': round(total_share / (total_exp + 1), 4),
            'ros': round(total_return / (total_share + 1), 4),
            'rovn': round(total_return / (total_exp + 1), 4),
            'vov': round(total_new_exp / (total_exp + 1), 4),
        }
        for _, row in crowd_df.iterrows():
            result['all'][crowd][row['rec_cate2']] = {
                'exp': int(row['exp']),
                'str': round(row['str'], 4),
                'ros': round(row['ros'], 4),
                'rovn': round(row['rovn'], 4),
                'vov': round(row['vov'], 4),
            }

    # 按头部品类聚合
    for head_cate in agg['head_cate2'].unique():
        result[head_cate] = {}
        for crowd in crowd_list:
            crowd_df = agg[(agg['head_cate2'] == head_cate) & (agg['crowd'] == crowd)]
            result[head_cate][crowd] = {}
            # 计算该头部品类下的整体汇总
            total_exp = int(crowd_df['exp'].sum())
            total_share = crowd_df['share_cnt'].sum()
            total_return = crowd_df['return_n_uv'].sum()
            total_new_exp = crowd_df['new_exposure_cnt'].sum()
            result[head_cate][crowd]['_total'] = {
                'exp': total_exp,
                'str': round(total_share / (total_exp + 1), 4),
                'ros': round(total_return / (total_share + 1), 4),
                'rovn': round(total_return / (total_exp + 1), 4),
                'vov': round(total_new_exp / (total_exp + 1), 4),
            }
            for _, row in crowd_df.iterrows():
                result[head_cate][crowd][row['rec_cate2']] = {
                    'exp': int(row['exp']),
                    'str': round(row['str'], 4),
                    'ros': round(row['ros'], 4),
                    'rovn': round(row['rovn'], 4),
                    'vov': round(row['vov'], 4),
                }

    # 获取所有头部品类列表（按总曝光排序）
    head_exp = ch_df.groupby('head_cate2')['exp'].sum().sort_values(ascending=False)
    head_list = head_exp.index.tolist()

    return {
        'heads': ['all'] + head_list,  # all 放在最前面
        'data': result
    }


# 预计算所有数据
all_data = {}
for crowd in crowd_list:
    all_data[crowd] = {}
    for dt in date_options:
        matrix = calc_matrix_data(crowd, dt)
        if matrix:
            all_data[crowd][dt] = matrix

# 预计算头部品类下钻数据
head_drill_data = {}
for dt in date_options:
    drill = calc_head_drill_data(dt)
    if drill:
        head_drill_data[dt] = drill

# 转为JSON
data_json = json.dumps(all_data, ensure_ascii=False)
head_drill_json = json.dumps(head_drill_data, ensure_ascii=False)
crowd_list_json = json.dumps(crowd_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'{"all" if dt == "all" else dt}</option>'
    for dt in date_options
])

# 人群选项HTML
crowd_options_html = "".join([
    f'<option value="{c}">{c}</option>'
    for c in crowd_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; }}
        .controls .date-switcher {{ margin-left: auto; }}
        .play-btn {{ background: #4CAF50; color: white; border: none; border-radius: 4px; padding: 6px 12px; font-size: 14px; }}
        .play-btn:hover {{ background: #45a049; }}
        .play-btn.playing {{ background: #f44336; }}
        .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: 120px; }}
        .summary {{ display: flex; gap: 20px; margin-bottom: 20px; }}
        .stat-card {{ background: #f8f9fa; padding: 15px 20px; border-radius: 6px; text-align: center; }}
        .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: 600px; overflow-y: auto; }}
        table {{ border-collapse: collapse; font-size: 11px; }}
        th, td {{ border: 1px solid #e0e0e0; padding: 4px 6px; 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; text-align: left; }}
        .corner-cell {{
            position: relative;
            width: 100px;
            height: 50px;
            background: linear-gradient(to top right, #f5f5f5 49.5%, #ccc 49.5%, #ccc 50.5%, #f5f5f5 50.5%);
        }}
        .corner-cell .row-label {{
            position: absolute;
            bottom: 4px;
            left: 4px;
            font-size: 10px;
            color: #666;
        }}
        .corner-cell .col-label {{
            position: absolute;
            top: 4px;
            right: 4px;
            font-size: 10px;
            color: #666;
        }}
        .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; }}
        /* Compare tab styles */
        .chart-container {{ width: 100%; overflow-x: auto; }}
        .bar-chart {{ min-width: 800px; }}
        .bar-group {{ display: flex; align-items: flex-end; gap: 4px; margin-bottom: 8px; }}
        .bar {{ min-width: 60px; text-align: center; font-size: 10px; color: white;
               border-radius: 3px 3px 0 0; transition: all 0.3s; cursor: pointer; }}
        .bar:hover {{ opacity: 0.8; }}
        .bar-label {{ font-size: 11px; color: #333; margin-bottom: 5px; font-weight: 500; }}
        .chart-legend {{ display: flex; gap: 20px; margin-bottom: 15px; }}
        .legend-item {{ display: flex; align-items: center; gap: 5px; font-size: 12px; }}
        .legend-color {{ width: 16px; height: 16px; border-radius: 3px; }}
        .compare-table {{ width: 100%; border-collapse: collapse; }}
        .compare-table th {{ background: #f5f5f5; padding: 8px 10px; text-align: center; font-weight: 600; border: 1px solid #ddd; }}
        .compare-table td {{ padding: 6px 8px; border: 1px solid #eee; text-align: center; }}
        .compare-table .crowd-header {{ background: #e8e8e8; font-size: 14px; }}
        .compare-table .cat-cell {{ text-align: left; padding-left: 10px; }}
        .compare-section {{ display: flex; gap: 20px; }}
        .crowd-block {{ flex: 1; min-width: 250px; }}
        .crowd-block table {{ width: 100%; border-collapse: collapse; }}
        .crowd-block th {{ background: #f0f0f0; padding: 8px; border: 1px solid #ddd; }}
        .crowd-block td {{ padding: 6px 8px; border: 1px solid #eee; }}
        .crowd-block .rn {{ width: 40px; text-align: center; color: #666; }}
        .crowd-block .cat {{ text-align: left; cursor: pointer; transition: all 0.2s; }}
        .crowd-block .val {{ text-align: right; font-family: monospace; }}
        .crowd-block .cat.highlight {{
            font-weight: bold;
        }}
        .crowd-block tr.row-highlight {{
            outline: 2px solid #1565C0;
            outline-offset: -1px;
        }}
    </style>
</head>
<body>
    <div class="container">
        <h1>头部品类 → 推荐品类</h1>

        <!-- Matrix Tab -->
        <div id="tab-matrix">
            <div class="controls">
                <div class="control-group">
                    <label>人群:</label>
                    <select id="crowd-select" onchange="updateMatrix()">
                        {crowd_options_html}
                    </select>
                </div>
                <div class="control-group">
                    <label>指标:</label>
                    <select id="metric-select" onchange="updateMatrix()">
                        <option value="exp">exp</option>
                        <option value="str">str</option>
                        <option value="ros">ros</option>
                        <option value="rovn">rovn</option>
                        <option value="vov" selected>vov</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" class="play-btn" onclick="togglePlay()">▶</button>
                </div>
            </div>

            <div class="summary" id="summary"></div>

            <div class="legend">
                行=头部品类，列=推荐品类 | 颜色越深=数值越高 | 点击表头排序
                <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 style="margin-top: 30px; border-top: 2px solid #e0e0e0; padding-top: 20px;">
                <h3 style="margin-bottom: 15px; font-size: 16px; color: #333;">头部品类下钻：各人群推荐品类 Top N</h3>
                <div class="controls">
                    <div class="control-group">
                        <label>头部品类:</label>
                        <select id="drill-head" onchange="updateHeadDrill()">
                        </select>
                    </div>
                    <div class="control-group">
                        <label>排序:</label>
                        <select id="drill-sort" onchange="updateHeadDrill()">
                            <option value="exp" selected>exp</option>
                            <option value="str">str</option>
                            <option value="ros">ros</option>
                            <option value="rovn">rovn</option>
                            <option value="vov">vov</option>
                        </select>
                    </div>
                    <div class="control-group">
                        <label>展示:</label>
                        <select id="drill-metric" onchange="updateHeadDrill()">
                            <option value="exp">exp</option>
                            <option value="str">str</option>
                            <option value="ros">ros</option>
                            <option value="rovn">rovn</option>
                            <option value="vov" selected>vov</option>
                        </select>
                    </div>
                    <div class="control-group">
                        <label>Top:</label>
                        <select id="drill-topn" onchange="updateHeadDrill()">
                            <option value="5">5</option>
                            <option value="10" selected>10</option>
                            <option value="15">15</option>
                            <option value="20">20</option>
                        </select>
                    </div>
                    <div class="control-group date-switcher">
                        <label>日期:</label>
                        <button onclick="switchDrillDate(-1)">◀</button>
                        <select id="drill-date" onchange="updateHeadDrill()">
                            {date_options_html}
                        </select>
                        <button onclick="switchDrillDate(1)">▶</button>
                        <button id="drill-play-btn" class="play-btn" onclick="toggleDrillPlay()">▶</button>
                    </div>
                </div>
                <div class="compare-section" id="drill-section"></div>
            </div>
        </div>

    </div>

    <script>
    const allData = {data_json};
    const headDrillData = {head_drill_json};
    const crowdList = {crowd_list_json};
    const dates = {dates_json};
    const crowdColors = {{ '内部': '#4CAF50', '外部0层': '#2196F3', '外部裂变': '#FF9800' }};
    let playInterval = null;
    let drillPlayInterval = null;
    let currentRowOrder = null;
    let currentColOrder = null;
    let sortState = {{ row: null, col: null, asc: true }};
    let lastCrowd = null;
    let lastDate = 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 updateMatrix() {{
        const crowd = document.getElementById('crowd-select').value;
        const metric = document.getElementById('metric-select').value;
        const date = document.getElementById('date-select').value;

        if (!allData[crowd] || !allData[crowd][date]) {{
            document.getElementById('summary').innerHTML = '<div class="stat-card"><h4>-</h4><p>no data</p></div>';
            document.getElementById('matrix-header').innerHTML = '';
            document.getElementById('matrix-body').innerHTML = '';
            return;
        }}

        const data = allData[crowd][date];

        document.getElementById('summary').innerHTML = `
            <div class="stat-card"><h4>${{data.total_exp.toLocaleString()}}</h4><p>总 exp</p></div>
            <div class="stat-card"><h4>${{data.total_str.toFixed(4)}}</h4><p>总 str</p></div>
            <div class="stat-card"><h4>${{data.total_rovn.toFixed(4)}}</h4><p>总 rovn</p></div>
            <div class="stat-card"><h4>${{data.rows.length}}</h4><p>头部品类数</p></div>
            <div class="stat-card"><h4>${{data.cols.length}}</h4><p>推荐品类数</p></div>
        `;

        const metricData = data[metric];
        const allVals = [];
        data.rows.forEach(r => data.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 = {{ exp: 10000, str: 0.1, ros: 0.5, rovn: 0.05, vov: 0.3 }};
        maxVal = Math.max(maxVal, thresholds[metric] || 0.1);

        // 切换人群或日期时，重置排序，使用新数据的 exp 排序
        if (crowd !== lastCrowd || date !== lastDate) {{
            currentRowOrder = null;
            currentColOrder = null;
            sortState = {{ row: null, col: null, asc: true }};
            lastCrowd = crowd;
            lastDate = date;
        }}

        if (!currentRowOrder) currentRowOrder = [...data.rows];
        if (!currentColOrder) currentColOrder = [...data.cols];

        const rows = currentRowOrder.filter(r => data.rows.includes(r));
        const cols = currentColOrder.filter(c => data.cols.includes(c));

        const expData = data.exp;
        const rowExpTotals = {{}};
        const colExpTotals = {{}};
        rows.forEach(r => {{ rowExpTotals[r] = cols.reduce((sum, c) => sum + (expData[r]?.[c] || 0), 0); }});
        cols.forEach(c => {{ colExpTotals[c] = rows.reduce((sum, r) => sum + (expData[r]?.[c] || 0), 0); }});

        // 计算原始排名（按exp排序）
        const origRowOrder = [...data.rows];
        const origColOrder = [...data.cols];

        document.getElementById('matrix-header').innerHTML = `
            <tr>
                <th class="corner-cell" style="cursor:pointer" onclick="sortByRowSum()">
                    <span class="row-label">头部品类 ↓</span>
                    <span class="col-label">推荐品类 →</span>
                </th>
                ${{cols.map((c, i) => {{
                    const origRank = origColOrder.indexOf(c) + 1;
                    return `<th style="cursor:pointer" onclick="sortByCol('${{c}}')" title="推荐品类: ${{c}}&#10;exp排名: #${{origRank}}&#10;exp: ${{colExpTotals[c].toLocaleString()}}">#${{origRank}} ${{c}}</th>`;
                }}).join('')}}
            </tr>
        `;

        document.getElementById('matrix-body').innerHTML = rows.map((r, ri) => {{
            const origRowRank = origRowOrder.indexOf(r) + 1;
            const cells = cols.map(c => {{
                const val = metricData[r]?.[c] || 0;
                const cellExp = expData[r]?.[c] || 0;
                const bg = getGradient(val, maxVal);
                const display = metric === 'exp' ? parseInt(val).toLocaleString() : val.toFixed(4);
                const rowPct = rowExpTotals[r] > 0 ? (cellExp / rowExpTotals[r] * 100).toFixed(1) : '0.0';
                const colPct = colExpTotals[c] > 0 ? (cellExp / colExpTotals[c] * 100).toFixed(1) : '0.0';
                return `<td style="background:${{bg}}" title="头部: ${{r}}&#10;推荐: ${{c}}&#10;${{metric}}: ${{display}}&#10;exp: ${{cellExp.toLocaleString()}}&#10;横向占比: ${{rowPct}}%&#10;纵向占比: ${{colPct}}%">${{display}}</td>`;
            }}).join('');
            return `<tr><td style="cursor:pointer;background:#f5f5f5" onclick="sortByRow('${{r}}')" title="头部品类: ${{r}}&#10;exp排名: #${{origRowRank}}&#10;exp: ${{rowExpTotals[r].toLocaleString()}}">#${{origRowRank}} ${{r}}</td>${{cells}}</tr>`;
        }}).join('');
    }}

    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 switchDrillDate(delta) {{
        const select = document.getElementById('drill-date');
        const idx = dates.indexOf(select.value);
        const newIdx = idx + delta;
        if (newIdx >= 0 && newIdx < dates.length) {{
            select.value = dates[newIdx];
            // 触发 change 事件以更新头部品类列表
            select.dispatchEvent(new Event('change'));
        }}
    }}

    function toggleDrillPlay() {{
        const btn = document.getElementById('drill-play-btn');
        if (drillPlayInterval) {{
            clearInterval(drillPlayInterval);
            drillPlayInterval = null;
            btn.classList.remove('playing');
            btn.textContent = '▶';
        }} else {{
            btn.classList.add('playing');
            btn.textContent = '⏸';
            let idx = 0;
            const play = () => {{
                if (idx >= dates.length) {{
                    clearInterval(drillPlayInterval);
                    drillPlayInterval = null;
                    btn.classList.remove('playing');
                    btn.textContent = '▶';
                    return;
                }}
                document.getElementById('drill-date').value = dates[idx];
                document.getElementById('drill-date').dispatchEvent(new Event('change'));
                idx++;
            }};
            play();
            drillPlayInterval = setInterval(play, 1500);
        }}
    }}

    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);
        }}
    }}

    function getCurrentData() {{
        const crowd = document.getElementById('crowd-select').value;
        const date = document.getElementById('date-select').value;
        const metric = document.getElementById('metric-select').value;
        if (!allData[crowd] || !allData[crowd][date]) return null;
        return {{ data: allData[crowd][date], metric }};
    }}

    function sortByRowSum() {{
        const result = getCurrentData();
        if (!result) return;
        const {{ data, metric }} = result;
        const metricData = data[metric];
        const rowSums = {{}};
        data.rows.forEach(r => {{ rowSums[r] = data.cols.reduce((sum, c) => sum + (metricData[r]?.[c] || 0), 0); }});
        sortState.asc = sortState.row === 'sum' ? !sortState.asc : false;
        sortState.row = 'sum';
        currentRowOrder = [...data.rows].sort((a, b) => sortState.asc ? rowSums[a] - rowSums[b] : rowSums[b] - rowSums[a]);
        updateMatrix();
    }}

    function sortByCol(colName) {{
        const result = getCurrentData();
        if (!result) return;
        const {{ data, metric }} = result;
        const metricData = data[metric];
        sortState.asc = sortState.col === colName ? !sortState.asc : false;
        sortState.col = colName;
        currentRowOrder = [...data.rows].sort((a, b) => {{
            const va = metricData[a]?.[colName] || 0;
            const vb = metricData[b]?.[colName] || 0;
            return sortState.asc ? va - vb : vb - va;
        }});
        updateMatrix();
    }}

    function sortByRow(rowName) {{
        const result = getCurrentData();
        if (!result) return;
        const {{ data, metric }} = result;
        const metricData = data[metric];
        sortState.asc = sortState.row === rowName ? !sortState.asc : false;
        sortState.row = rowName;
        currentColOrder = [...data.cols].sort((a, b) => {{
            const va = metricData[rowName]?.[a] || 0;
            const vb = metricData[rowName]?.[b] || 0;
            return sortState.asc ? va - vb : vb - va;
        }});
        updateMatrix();
    }}

    function resetSort() {{
        currentRowOrder = null;
        currentColOrder = null;
        sortState = {{ row: null, col: null, asc: true }};
        updateMatrix();
    }}

    function highlightCat(el) {{
        const cat = el.getAttribute('data-cat');
        document.querySelectorAll('.cat[data-cat]').forEach(cell => {{
            if (cell.getAttribute('data-cat') === cat) {{
                cell.classList.add('highlight');
                cell.closest('tr').classList.add('row-highlight');
            }}
        }});
    }}

    function unhighlightCat() {{
        document.querySelectorAll('.cat.highlight').forEach(cell => {{
            cell.classList.remove('highlight');
            cell.closest('tr').classList.remove('row-highlight');
        }});
    }}

    // 初始化头部品类下钻
    function initHeadDrill() {{
        const date = document.getElementById('drill-date').value;
        const headSelect = document.getElementById('drill-head');

        if (!headDrillData[date]) {{
            headSelect.innerHTML = '<option value="">无数据</option>';
            return;
        }}

        const heads = headDrillData[date].heads;
        headSelect.innerHTML = heads.map((h, i) => {{
            const label = h === 'all' ? '全部（不区分头部品类）' : `#${{i}} ${{h}}`;
            return `<option value="${{h}}">${{label}}</option>`;
        }}).join('');

        updateHeadDrill();
    }}

    function updateHeadDrill() {{
        const date = document.getElementById('drill-date').value;
        const headCate = document.getElementById('drill-head').value;
        const sortBy = document.getElementById('drill-sort').value;
        const showMetric = document.getElementById('drill-metric').value;
        const topN = parseInt(document.getElementById('drill-topn').value);

        // 检查日期变化，更新头部品类列表
        const headSelect = document.getElementById('drill-head');
        if (headDrillData[date] && headSelect.options.length > 0) {{
            const currentHeads = headDrillData[date].heads;
            const firstOption = headSelect.options[0]?.value;
            if (currentHeads[0] !== firstOption) {{
                headSelect.innerHTML = currentHeads.map((h, i) => {{
                    const label = h === 'all' ? '全部（不区分头部品类）' : `#${{i}} ${{h}}`;
                    return `<option value="${{h}}" ${{h === headCate ? 'selected' : ''}}>${{label}}</option>`;
                }}).join('');
            }}
        }}

        if (!headDrillData[date] || !headCate) {{
            document.getElementById('drill-section').innerHTML = '<p>无数据</p>';
            return;
        }}

        const data = headDrillData[date].data[headCate];
        if (!data) {{
            document.getElementById('drill-section').innerHTML = '<p>该头部品类无数据</p>';
            return;
        }}

        // 为每个人群计算 Top N 和整体汇总
        const crowdTopN = {{}};
        const crowdTotal = {{}};
        crowdList.forEach(crowd => {{
            const items = [];
            if (data[crowd]) {{
                for (const cat in data[crowd]) {{
                    if (cat === '_total') {{
                        // 保存整体汇总
                        crowdTotal[crowd] = {{
                            exp: data[crowd][cat].exp || 0,
                            showVal: data[crowd][cat][showMetric] || 0
                        }};
                    }} else {{
                        items.push({{
                            cat: cat,
                            sortVal: data[crowd][cat][sortBy] || 0,
                            showVal: data[crowd][cat][showMetric] || 0,
                            exp: data[crowd][cat].exp || 0
                        }});
                    }}
                }}
            }}
            items.sort((a, b) => b.sortVal - a.sortVal);
            crowdTopN[crowd] = items.slice(0, topN);
        }});

        // 收集所有品类用于颜色映射
        const allCats = new Set();
        crowdList.forEach(crowd => {{
            crowdTopN[crowd].forEach(item => allCats.add(item.cat));
        }});
        const catList = Array.from(allCats);

        const catColors = {{}};
        const colorPalette = [
            '#FFCDD2', '#F8BBD0', '#E1BEE7', '#D1C4E9', '#C5CAE9',
            '#BBDEFB', '#B3E5FC', '#B2EBF2', '#B2DFDB', '#C8E6C9',
            '#DCEDC8', '#F0F4C3', '#FFF9C4', '#FFECB3', '#FFE0B2',
            '#FFCCBC', '#D7CCC8', '#CFD8DC', '#BCAAA4', '#B0BEC5'
        ];
        catList.forEach((cat, i) => {{
            catColors[cat] = colorPalette[i % colorPalette.length];
        }});

        // 计算指标渐变范围
        let maxVal = 0, minVal = Infinity;
        crowdList.forEach(crowd => {{
            crowdTopN[crowd].forEach(item => {{
                if (item.showVal > maxVal) maxVal = item.showVal;
                if (item.showVal < minVal) minVal = item.showVal;
            }});
        }});
        if (minVal === Infinity) minVal = 0;

        function getValueColor(val) {{
            if (maxVal === minVal) return '#C8E6C9';
            const ratio = (val - minVal) / (maxVal - minVal);
            const r = Math.round(200 - ratio * 120);
            const g = Math.round(230 - ratio * 80);
            const b = Math.round(201 - ratio * 120);
            return `rgb(${{r}},${{g}},${{b}})`;
        }}

        // 生成表格
        let html = '';
        crowdList.forEach(crowd => {{
            const colSpan = showMetric === 'exp' ? 3 : 4;
            html += `<div class="crowd-block">
                <table>
                    <thead>
                        <tr><th colspan="${{colSpan}}" style="background:${{crowdColors[crowd]}};color:white">${{crowd}}</th></tr>
                        <tr><th class="rn">rn</th><th>推荐品类</th><th>exp</th>${{showMetric !== 'exp' ? `<th>${{showMetric}}</th>` : ''}}</tr>
                    </thead>
                    <tbody>`;

            if (crowdTopN[crowd].length === 0) {{
                html += `<tr><td colspan="${{colSpan}}" style="color:#999">无数据</td></tr>`;
            }} else {{
                // 先添加整体汇总行 (rn=0)
                if (crowdTotal[crowd]) {{
                    const totalExp = parseInt(crowdTotal[crowd].exp).toLocaleString();
                    const totalMetric = (crowdTotal[crowd].showVal * 100).toFixed(1) + '%';
                    html += `<tr style="background:#f5f5f5;font-weight:bold">
                        <td class="rn">0</td>
                        <td class="cat" style="background:#e0e0e0">整体</td>
                        <td class="val">${{totalExp}}</td>
                        ${{showMetric !== 'exp' ? `<td class="val">${{totalMetric}}</td>` : ''}}
                    </tr>`;
                }}
                // 添加 Top N 品类
                crowdTopN[crowd].forEach((item, i) => {{
                    const expDisplay = parseInt(item.exp).toLocaleString();
                    const metricDisplay = (item.showVal * 100).toFixed(1) + '%';
                    const valColor = getValueColor(item.showVal);
                    const catColor = catColors[item.cat];
                    const catAttr = item.cat.replace(/"/g, '&quot;');
                    html += `<tr>
                        <td class="rn">${{i + 1}}</td>
                        <td class="cat" style="background:${{catColor}}" data-cat="${{catAttr}}" onmouseenter="highlightCat(this)" onmouseleave="unhighlightCat()">${{item.cat}}</td>
                        <td class="val">${{expDisplay}}</td>
                        ${{showMetric !== 'exp' ? `<td class="val" style="background:${{valColor}}">${{metricDisplay}}</td>` : ''}}
                    </tr>`;
                }});
            }}

            html += `</tbody></table></div>`;
        }});

        document.getElementById('drill-section').innerHTML = html;
    }}

    // 监听日期变化，更新头部品类列表
    document.getElementById('drill-date').addEventListener('change', function() {{
        const date = this.value;
        const headSelect = document.getElementById('drill-head');
        const currentHead = headSelect.value;

        if (headDrillData[date]) {{
            const heads = headDrillData[date].heads;
            headSelect.innerHTML = heads.map((h, i) => {{
                const label = h === 'all' ? '全部（不区分头部品类）' : `#${{i}} ${{h}}`;
                return `<option value="${{h}}" ${{h === currentHead ? 'selected' : ''}}>${{label}}</option>`;
            }}).join('');
        }} else {{
            headSelect.innerHTML = '<option value="">无数据</option>';
        }}
        updateHeadDrill();
    }});

    updateMatrix();
    initHeadDrill();
    </script>
</body>
</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}")
