#!/usr/bin/env python
# coding=utf-8
"""
品类承接裂变率相关性分析 - HTML可视化
Tab 1: 品类一致性分析 - 同品类vs跨品类vov对比
Tab 2: 品类组合稳定性 - 跨人群相关性散点图
Tab 3: 品类亲和性矩阵 - 热力图
"""
import pandas as pd
import numpy as np
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 f.name.endswith('.html')]
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()}")

# 过滤掉 headvideoid为空 的记录
df_valid = df[~df['head_cate2'].isin(['headvideoid为空', '未匹配品类'])].copy()
df_valid['is_same_cate'] = df_valid['head_cate2'] == df_valid['rec_cate2']
df_valid['cate_pair'] = df_valid['head_cate2'] + ' → ' + df_valid['rec_cate2']

crowd_list = ['内部', '外部0层', '外部裂变']
date_list = ['全部'] + sorted([str(d) for d in df_valid['dt'].unique()])
EXP_THRESHOLD = 10000  # 亲和性矩阵的曝光阈值（全部天数）
EXP_THRESHOLD_DAILY = 1000  # 单日曝光阈值

# ========== 1. 品类一致性数据 ==========
consistency_data = {'crowds': crowd_list, 'same': [], 'diff': [], 'ratio': []}
for crowd in crowd_list:
    crowd_df = df_valid[df_valid['crowd'] == crowd]
    same = crowd_df[crowd_df['is_same_cate']]
    diff = crowd_df[~crowd_df['is_same_cate']]
    same_vov = same['new_exposure_cnt'].sum() / same['exp'].sum() if same['exp'].sum() > 0 else 0
    diff_vov = diff['new_exposure_cnt'].sum() / diff['exp'].sum() if diff['exp'].sum() > 0 else 0
    consistency_data['same'].append(round(same_vov, 4))
    consistency_data['diff'].append(round(diff_vov, 4))
    consistency_data['ratio'].append(round(same_vov / diff_vov, 2) if diff_vov > 0 else 0)

# 整体
same_all = df_valid[df_valid['is_same_cate']]
diff_all = df_valid[~df_valid['is_same_cate']]
consistency_data['total_same'] = round(same_all['new_exposure_cnt'].sum() / same_all['exp'].sum(), 4)
consistency_data['total_diff'] = round(diff_all['new_exposure_cnt'].sum() / diff_all['exp'].sum(), 4)
consistency_data['total_ratio'] = round(consistency_data['total_same'] / consistency_data['total_diff'], 2)

# 同品类曝光占比
consistency_data['same_exp'] = [int(df_valid[(df_valid['crowd'] == c) & df_valid['is_same_cate']]['exp'].sum()) for c in crowd_list]
consistency_data['diff_exp'] = [int(df_valid[(df_valid['crowd'] == c) & ~df_valid['is_same_cate']]['exp'].sum()) for c in crowd_list]

# ========== 2. 品类亲和性矩阵（按人群分开 + 整体） ==========
def calc_affinity_matrix(data_df, exp_threshold=EXP_THRESHOLD):
    """计算亲和性矩阵数据"""
    # 计算每个head_cate2的基准vov
    head_baseline = data_df.groupby('head_cate2').apply(
        lambda x: x['new_exposure_cnt'].sum() / x['exp'].sum(), include_groups=False
    ).to_dict()

    affinity_list = []
    for (head, rec), grp in data_df.groupby(['head_cate2', 'rec_cate2']):
        if grp['exp'].sum() >= exp_threshold:
            pair_vov = grp['new_exposure_cnt'].sum() / grp['exp'].sum()
            baseline = head_baseline.get(head, 1)
            affinity = pair_vov / baseline if baseline > 0 else 0
            affinity_list.append({
                'head': head, 'rec': rec,
                'vov': round(pair_vov, 4),
                'baseline': round(baseline, 4),
                'affinity': round(affinity, 2),
                'exp': int(grp['exp'].sum())
            })

    if not affinity_list:
        return None

    aff_df = pd.DataFrame(affinity_list)

    # 构建矩阵数据 - 行列使用相同品类列表，方便看对角线（同品类承接）
    # 合并 head 和 rec 的曝光量，按总曝光排序
    head_exp = aff_df.groupby('head')['exp'].sum()
    rec_exp = aff_df.groupby('rec')['exp'].sum()
    all_cates = set(head_exp.index) | set(rec_exp.index)
    cate_total_exp = {c: head_exp.get(c, 0) + rec_exp.get(c, 0) for c in all_cates}
    cate_list = sorted(cate_total_exp.keys(), key=lambda x: cate_total_exp[x], reverse=True)[:30]

    # 行列使用相同顺序
    head_list = cate_list
    rec_list = cate_list

    result = {'rows': head_list, 'cols': rec_list, 'affinity': {}, 'vov': {}, 'exp': {}}
    for head in head_list:
        result['affinity'][head] = {}
        result['vov'][head] = {}
        result['exp'][head] = {}
        for rec in rec_list:
            row = aff_df[(aff_df['head'] == head) & (aff_df['rec'] == rec)]
            if len(row) > 0:
                result['affinity'][head][rec] = float(row.iloc[0]['affinity'])
                result['vov'][head][rec] = float(row.iloc[0]['vov'])
                result['exp'][head][rec] = int(row.iloc[0]['exp'])
            else:
                result['affinity'][head][rec] = 0
                result['vov'][head][rec] = 0
                result['exp'][head][rec] = 0
    return result

# 先计算全部+整体的矩阵，获取固定的行列顺序
base_matrix = calc_affinity_matrix(df_valid, EXP_THRESHOLD)
fixed_cate_list = base_matrix['rows'] if base_matrix else []

def calc_affinity_matrix_fixed(data_df, exp_threshold, fixed_list):
    """计算亲和性矩阵数据，使用固定的行列顺序"""
    head_baseline = data_df.groupby('head_cate2').apply(
        lambda x: x['new_exposure_cnt'].sum() / x['exp'].sum(), include_groups=False
    ).to_dict()

    affinity_dict = {}
    for (head, rec), grp in data_df.groupby(['head_cate2', 'rec_cate2']):
        if grp['exp'].sum() >= exp_threshold:
            pair_vov = grp['new_exposure_cnt'].sum() / grp['exp'].sum()
            baseline = head_baseline.get(head, 1)
            affinity = pair_vov / baseline if baseline > 0 else 0
            affinity_dict[(head, rec)] = {
                'vov': round(pair_vov, 4),
                'affinity': round(affinity, 2),
                'exp': int(grp['exp'].sum())
            }

    # 使用固定的行列顺序
    result = {'rows': fixed_list, 'cols': fixed_list, 'affinity': {}, 'vov': {}, 'exp': {}}
    for head in fixed_list:
        result['affinity'][head] = {}
        result['vov'][head] = {}
        result['exp'][head] = {}
        for rec in fixed_list:
            if (head, rec) in affinity_dict:
                result['affinity'][head][rec] = float(affinity_dict[(head, rec)]['affinity'])
                result['vov'][head][rec] = float(affinity_dict[(head, rec)]['vov'])
                result['exp'][head][rec] = int(affinity_dict[(head, rec)]['exp'])
            else:
                result['affinity'][head][rec] = 0
                result['vov'][head][rec] = 0
                result['exp'][head][rec] = 0
    return result

# 计算各日期×人群的矩阵（使用固定行列顺序）
matrix_data = {}
for date in date_list:
    matrix_data[date] = {}
    if date == '全部':
        date_df = df_valid
        threshold = EXP_THRESHOLD
    else:
        date_df = df_valid[df_valid['dt'].astype(str) == date]
        threshold = EXP_THRESHOLD_DAILY

    # 整体
    matrix_data[date]['整体'] = calc_affinity_matrix_fixed(date_df, threshold, fixed_cate_list)
    # 各人群
    for crowd in crowd_list:
        matrix_data[date][crowd] = calc_affinity_matrix_fixed(
            date_df[date_df['crowd'] == crowd], threshold, fixed_cate_list
        )

# ========== 4. Top品类组合排名（按人群分开 + 整体） ==========
def calc_ranking(data_df, min_exp=1000):
    """计算品类组合排名"""
    pair_vov = data_df.groupby('cate_pair').apply(
        lambda x: pd.Series({
            'vov': x['new_exposure_cnt'].sum() / x['exp'].sum(),
            'exp': int(x['exp'].sum()),
        }), include_groups=False
    )
    pair_vov = pair_vov[pair_vov['exp'] >= min_exp]
    if len(pair_vov) == 0:
        return {'high': [], 'low': []}

    all_high = pair_vov.sort_values('vov', ascending=False).head(100)
    all_low = pair_vov.sort_values('vov', ascending=True).head(100)

    return {
        'high': [{'pair': idx, 'vov': float(round(row['vov'], 4)), 'exp': int(row['exp'])} for idx, row in all_high.iterrows()],
        'low': [{'pair': idx, 'vov': float(round(row['vov'], 4)), 'exp': int(row['exp'])} for idx, row in all_low.iterrows()]
    }

# 计算各日期×人群的排名
ranking_data = {}
for date in date_list:
    ranking_data[date] = {}
    if date == '全部':
        date_df = df_valid
        min_exp = 1000
    else:
        date_df = df_valid[df_valid['dt'].astype(str) == date]
        min_exp = 100  # 单日阈值更低

    ranking_data[date]['整体'] = calc_ranking(date_df, min_exp)
    for crowd in crowd_list:
        ranking_data[date][crowd] = calc_ranking(date_df[date_df['crowd'] == crowd], min_exp)

# 转为JSON
consistency_json = json.dumps(consistency_data, ensure_ascii=False)
matrix_json = json.dumps(matrix_data, ensure_ascii=False)
ranking_json = json.dumps(ranking_data, ensure_ascii=False)
dates_json = json.dumps(date_list, ensure_ascii=False)

# 日期选项HTML
date_options_html = "".join([f'<option value="{d}" {"selected" if d == "全部" else ""}>{d}</option>' for d in date_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: 10px; color: #333; }}
        .subtitle {{ color: #666; margin-bottom: 20px; font-size: 14px; }}

        /* Tabs */
        .tabs {{ display: flex; gap: 5px; margin-bottom: 20px; border-bottom: 2px solid #e0e0e0; }}
        .tab {{ padding: 10px 20px; cursor: pointer; border: none; background: none;
               font-size: 14px; color: #666; border-bottom: 2px solid transparent; margin-bottom: -2px; }}
        .tab:hover {{ color: #333; }}
        .tab.active {{ color: #1976D2; border-bottom-color: #1976D2; font-weight: 500; }}
        .tab-content {{ display: none; }}
        .tab-content.active {{ display: block; }}

        /* Summary cards */
        .summary {{ display: flex; gap: 15px; margin-bottom: 25px; flex-wrap: wrap; }}
        .stat-card {{ background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
                     padding: 15px 20px; border-radius: 8px; text-align: center; color: white; min-width: 140px; }}
        .stat-card.green {{ background: linear-gradient(135deg, #11998e 0%, #38ef7d 100%); }}
        .stat-card.orange {{ background: linear-gradient(135deg, #f093fb 0%, #f5576c 100%); }}
        .stat-card.blue {{ background: linear-gradient(135deg, #4facfe 0%, #00f2fe 100%); }}
        .stat-card h4 {{ font-size: 28px; margin-bottom: 5px; }}
        .stat-card p {{ font-size: 12px; opacity: 0.9; }}

        /* Bar chart */
        .chart-section {{ margin-bottom: 30px; }}
        .chart-title {{ font-size: 16px; font-weight: 500; margin-bottom: 15px; color: #333; }}
        .bar-chart {{ display: flex; gap: 30px; align-items: flex-end; justify-content: center; padding: 20px; }}
        .bar-group {{ text-align: center; }}
        .bar-pair {{ display: flex; gap: 8px; align-items: flex-end; height: 200px; }}
        .bar {{ width: 50px; border-radius: 4px 4px 0 0; transition: all 0.3s; cursor: pointer; position: relative; }}
        .bar:hover {{ opacity: 0.8; }}
        .bar-value {{ position: absolute; top: -25px; left: 50%; transform: translateX(-50%); font-size: 12px; font-weight: 500; white-space: nowrap; }}
        .bar-label {{ margin-top: 10px; font-size: 13px; color: #333; }}
        .bar-ratio {{ font-size: 11px; color: #666; margin-top: 3px; }}
        .legend {{ display: flex; gap: 20px; justify-content: center; margin-bottom: 15px; }}
        .legend-item {{ display: flex; align-items: center; gap: 6px; font-size: 13px; }}
        .legend-color {{ width: 16px; height: 16px; border-radius: 3px; }}

        /* Scatter plot */
        .scatter-container {{ display: flex; gap: 20px; flex-wrap: wrap; }}
        .scatter-box {{ flex: 1; min-width: 350px; background: #f8f9fa; border-radius: 8px; padding: 15px; }}
        .scatter-title {{ font-size: 14px; font-weight: 500; margin-bottom: 10px; }}
        .scatter-stats {{ font-size: 12px; color: #666; margin-bottom: 10px; }}
        .scatter-canvas {{ width: 100%; height: 300px; position: relative; background: white; border: 1px solid #e0e0e0; border-radius: 4px; }}

        /* Matrix */
        .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 {{ background: linear-gradient(to top right, #f5f5f5 49.5%, #ccc 49.5%, #ccc 50.5%, #f5f5f5 50.5%); }}

        /* Controls */
        .controls {{ display: flex; gap: 15px; margin-bottom: 15px; align-items: center; flex-wrap: wrap; }}
        .control-group {{ display: flex; align-items: center; gap: 6px; }}
        .control-group label {{ font-size: 13px; color: #666; }}
        select {{ padding: 6px 10px; border: 1px solid #ddd; border-radius: 4px; font-size: 13px; }}
        .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 {{ background: #4CAF50; color: white; border: none; border-radius: 4px; padding: 5px 12px; font-size: 12px; cursor: pointer; }}
        .play-btn:hover {{ background: #45a049; }}
        .play-btn.playing {{ background: #f44336; }}
        /* Matrix highlight */
        th.highlight, td.row-header.highlight {{ background: #bbdefb !important; }}
        td.cell-highlight {{ outline: 2px solid #1565C0; outline-offset: -1px; }}

        /* Ranking table */
        .ranking-section {{ display: flex; gap: 30px; }}
        .ranking-box {{ flex: 1; }}
        .ranking-box h4 {{ font-size: 14px; margin-bottom: 10px; padding: 8px; border-radius: 4px; }}
        .ranking-box.high h4 {{ background: #e8f5e9; color: #2e7d32; }}
        .ranking-box.low h4 {{ background: #ffebee; color: #c62828; }}
        .ranking-table {{ width: 100%; border-collapse: collapse; }}
        .ranking-table th {{ background: #f5f5f5; padding: 8px; text-align: left; font-size: 12px; }}
        .ranking-table td {{ padding: 6px 8px; border-bottom: 1px solid #eee; font-size: 12px; }}
        .ranking-table .rn {{ width: 30px; color: #999; }}
        .ranking-table .vov {{ font-family: monospace; text-align: right; }}
        .ranking-table .exp {{ color: #666; text-align: right; }}

        /* Insight box */
        .insight-box {{ background: #e3f2fd; border-left: 4px solid #1976D2; padding: 15px; margin: 20px 0; border-radius: 0 8px 8px 0; }}
        .insight-box h5 {{ color: #1565C0; margin-bottom: 8px; font-size: 14px; }}
        .insight-box p {{ color: #333; font-size: 13px; line-height: 1.6; }}
    </style>
</head>
<body>
    <div class="container">
        <h1>品类承接裂变率相关性分析</h1>
        <p class="subtitle">分析进入品类与承接品类的关系对裂变效果的影响</p>

        <div class="tabs">
            <button class="tab active" onclick="switchTab('consistency')">品类一致性</button>
            <button class="tab" onclick="switchTab('affinity')">品类亲和性矩阵</button>
            <button class="tab" onclick="switchTab('ranking')">品类组合排名</button>
        </div>

        <!-- Tab 1: 品类一致性 -->
        <div id="tab-consistency" class="tab-content active">
            <div class="summary">
                <div class="stat-card green">
                    <h4 id="same-vov">-</h4>
                    <p>同品类承接 vov</p>
                </div>
                <div class="stat-card orange">
                    <h4 id="diff-vov">-</h4>
                    <p>跨品类承接 vov</p>
                </div>
                <div class="stat-card blue">
                    <h4 id="vov-ratio">-</h4>
                    <p>同/跨品类比值</p>
                </div>
            </div>

            <div class="insight-box">
                <h5>核心发现</h5>
                <p>同品类承接（进入品类=承接品类）的裂变率显著高于跨品类承接，约为 <strong id="insight-ratio">-</strong> 倍。
                这说明用户对同类内容有更强的分享意愿，推荐系统在品类匹配上有优化空间。</p>
            </div>

            <div class="chart-section">
                <div class="chart-title">各人群同品类 vs 跨品类 vov 对比</div>
                <div class="legend">
                    <div class="legend-item"><div class="legend-color" style="background:#4CAF50"></div>同品类承接</div>
                    <div class="legend-item"><div class="legend-color" style="background:#2196F3"></div>跨品类承接</div>
                </div>
                <div class="bar-chart" id="consistency-chart"></div>
            </div>

            <div class="chart-section">
                <div class="chart-title">同品类曝光占比</div>
                <div id="exp-ratio-chart" style="display:flex;gap:20px;justify-content:center;"></div>
            </div>
        </div>

        <!-- Tab 2: 品类亲和性矩阵 -->
        <div id="tab-affinity" class="tab-content">
            <div class="insight-box">
                <h5>亲和性 = 这个组合的表现 / 进入品类的平均表现</h5>
                <p>
                <strong>举例</strong>：用户从「搞笑段子」进入，平均裂变率 0.4<br>
                • 推荐「搞笑段子→搞笑段子」裂变率 0.8，亲和性 = 0.8/0.4 = <span style="color:#2e7d32;font-weight:bold">2.0 ✓ 更对味</span><br>
                • 推荐「搞笑段子→历史名人」裂变率 0.2，亲和性 = 0.2/0.4 = <span style="color:#c62828;font-weight:bold">0.5 ✗ 不对味</span><br><br>
                <strong>颜色</strong>：<span style="background:#c8e6c9;padding:2px 6px;border-radius:3px">绿色=高亲和</span>
                <span style="background:#ffcdd2;padding:2px 6px;border-radius:3px;margin-left:10px">红色=低亲和</span>
                </p>
            </div>

            <div class="controls">
                <div class="control-group date-switcher">
                    <label>日期:</label>
                    <button onclick="switchMatrixDate(-1)">◀</button>
                    <select id="matrix-date" onchange="updateMatrix()">
                        {date_options_html}
                    </select>
                    <button onclick="switchMatrixDate(1)">▶</button>
                    <button id="matrix-play-btn" class="play-btn" onclick="toggleMatrixPlay()">▶ 播放</button>
                </div>
                <div class="control-group">
                    <label>人群:</label>
                    <select id="matrix-crowd" onchange="updateMatrix()">
                        <option value="整体" selected>整体</option>
                        <option value="内部">内部</option>
                        <option value="外部0层">外部0层</option>
                        <option value="外部裂变">外部裂变</option>
                    </select>
                </div>
                <div class="control-group">
                    <label>显示指标:</label>
                    <select id="matrix-metric" onchange="updateMatrix()">
                        <option value="affinity" selected>亲和性 (affinity)</option>
                        <option value="vov">裂变率 (vov)</option>
                        <option value="exp">曝光量 (exp)</option>
                    </select>
                </div>
            </div>

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

        <!-- Tab 4: 品类组合排名 -->
        <div id="tab-ranking" class="tab-content">
            <div class="insight-box">
                <h5>筛选条件</h5>
                <p>仅展示在 ≥2 个人群中都有数据且曝光量 ≥1000 的品类组合，确保结果稳定可靠。</p>
            </div>

            <div class="controls">
                <div class="control-group date-switcher">
                    <label>日期:</label>
                    <button onclick="switchRankingDate(-1)">◀</button>
                    <select id="ranking-date" onchange="initRanking()">
                        {date_options_html}
                    </select>
                    <button onclick="switchRankingDate(1)">▶</button>
                    <button id="ranking-play-btn" class="play-btn" onclick="toggleRankingPlay()">▶ 播放</button>
                </div>
                <div class="control-group">
                    <label>人群:</label>
                    <select id="ranking-crowd" onchange="initRanking()">
                        <option value="整体" selected>整体</option>
                        <option value="内部">内部</option>
                        <option value="外部0层">外部0层</option>
                        <option value="外部裂变">外部裂变</option>
                    </select>
                </div>
                <div class="control-group">
                    <label>展示数量:</label>
                    <select id="ranking-topn" onchange="initRanking()">
                        <option value="20">Top 20</option>
                        <option value="50">Top 50</option>
                        <option value="100">Top 100</option>
                    </select>
                </div>
            </div>

            <div class="ranking-section">
                <div class="ranking-box high">
                    <h4>Top 20 高裂变品类组合</h4>
                    <table class="ranking-table" id="high-ranking"></table>
                </div>
                <div class="ranking-box low">
                    <h4>Top 20 低裂变品类组合</h4>
                    <table class="ranking-table" id="low-ranking"></table>
                </div>
            </div>
        </div>
    </div>

    <script>
    const consistencyData = {consistency_json};
    const matrixData = {matrix_json};
    const rankingData = {ranking_json};
    const dateList = {dates_json};

    let matrixPlayInterval = null;
    let rankingPlayInterval = null;

    // Tab switching
    function switchTab(tabId) {{
        document.querySelectorAll('.tab').forEach(t => t.classList.remove('active'));
        document.querySelectorAll('.tab-content').forEach(c => c.classList.remove('active'));
        document.querySelector(`[onclick="switchTab('${{tabId}}')"]`).classList.add('active');
        document.getElementById('tab-' + tabId).classList.add('active');
    }}

    // Initialize consistency chart
    function initConsistency() {{
        const data = consistencyData;
        document.getElementById('same-vov').textContent = data.total_same.toFixed(4);
        document.getElementById('diff-vov').textContent = data.total_diff.toFixed(4);
        document.getElementById('vov-ratio').textContent = data.total_ratio.toFixed(2) + 'x';
        document.getElementById('insight-ratio').textContent = data.total_ratio.toFixed(2);

        const maxVov = Math.max(...data.same, ...data.diff);
        const chartHtml = data.crowds.map((crowd, i) => {{
            const sameH = Math.round(data.same[i] / maxVov * 180);
            const diffH = Math.round(data.diff[i] / maxVov * 180);
            return `
                <div class="bar-group">
                    <div class="bar-pair">
                        <div class="bar" style="height:${{sameH}}px;background:#4CAF50">
                            <span class="bar-value">${{data.same[i].toFixed(4)}}</span>
                        </div>
                        <div class="bar" style="height:${{diffH}}px;background:#2196F3">
                            <span class="bar-value">${{data.diff[i].toFixed(4)}}</span>
                        </div>
                    </div>
                    <div class="bar-label">${{crowd}}</div>
                    <div class="bar-ratio">${{data.ratio[i]}}x</div>
                </div>
            `;
        }}).join('');
        document.getElementById('consistency-chart').innerHTML = chartHtml;

        // Exp ratio
        const expHtml = data.crowds.map((crowd, i) => {{
            const total = data.same_exp[i] + data.diff_exp[i];
            const sameRatio = total > 0 ? (data.same_exp[i] / total * 100).toFixed(1) : 0;
            return `
                <div style="text-align:center">
                    <div style="font-size:13px;margin-bottom:5px">${{crowd}}</div>
                    <div style="width:150px;height:20px;background:#e0e0e0;border-radius:10px;overflow:hidden">
                        <div style="width:${{sameRatio}}%;height:100%;background:#4CAF50"></div>
                    </div>
                    <div style="font-size:11px;color:#666;margin-top:3px">同品类占比: ${{sameRatio}}%</div>
                </div>
            `;
        }}).join('');
        document.getElementById('exp-ratio-chart').innerHTML = expHtml;
    }}

    // Matrix
    function updateMatrix() {{
        const date = document.getElementById('matrix-date').value;
        const crowd = document.getElementById('matrix-crowd').value;
        const metric = document.getElementById('matrix-metric').value;

        if (!matrixData[date] || !matrixData[date][crowd]) {{
            document.getElementById('affinity-header').innerHTML = '<tr><th>无数据</th></tr>';
            document.getElementById('affinity-body').innerHTML = '';
            return;
        }}

        const data = matrixData[date][crowd];
        const metricData = data[metric];

        // Calculate color range
        const allVals = [];
        data.rows.forEach(r => data.cols.forEach(c => {{
            const val = metricData[r]?.[c] || 0;
            if (val > 0) allVals.push(val);
        }}));

        let maxVal, minVal = 0;
        if (metric === 'affinity') {{
            maxVal = 2; minVal = 0.5;
        }} else if (metric === 'vov') {{
            allVals.sort((a, b) => a - b);
            maxVal = allVals[Math.floor(allVals.length * 0.95)] || 1;
        }} else {{
            allVals.sort((a, b) => a - b);
            maxVal = allVals[Math.floor(allVals.length * 0.9)] || 100000;
        }}

        function getColor(val) {{
            if (metric === 'affinity') {{
                if (val >= 1) {{
                    const ratio = Math.min((val - 1) / (maxVal - 1), 1);
                    return `rgb(${{Math.round(200 - ratio * 200)}}, ${{Math.round(230 - ratio * 30)}}, ${{Math.round(200 - ratio * 200)}})`;
                }} else {{
                    const ratio = Math.min((1 - val) / (1 - minVal), 1);
                    return `rgb(${{Math.round(230 - ratio * 30)}}, ${{Math.round(200 - ratio * 200)}}, ${{Math.round(200 - ratio * 200)}})`;
                }}
            }} else {{
                const ratio = Math.min(val / maxVal, 1);
                return `rgb(${{Math.round(255 - ratio * 215)}}, ${{Math.round(255 - ratio * 88)}}, ${{Math.round(255 - ratio * 186)}})`;
            }}
        }}

        const expData = data.exp;

        // 计算每行和每列的总曝光量
        const rowTotals = {{}};
        const colTotals = {{}};
        data.rows.forEach(r => {{
            rowTotals[r] = data.cols.reduce((sum, c) => sum + (expData[r]?.[c] || 0), 0);
        }});
        data.cols.forEach(c => {{
            colTotals[c] = data.rows.reduce((sum, r) => sum + (expData[r]?.[c] || 0), 0);
        }});

        document.getElementById('affinity-header').innerHTML = `
            <tr>
                <th class="corner-cell" style="width:120px">进入↓ 承接→</th>
                ${{data.cols.map((c, ci) => `<th data-col="${{ci}}" title="${{c}}\\nexp: ${{colTotals[c].toLocaleString()}}">${{c.length > 6 ? c.substring(0,6) + '..' : c}}</th>`).join('')}}
            </tr>
        `;

        document.getElementById('affinity-body').innerHTML = data.rows.map((r, ri) => {{
            const cells = data.cols.map((c, ci) => {{
                const val = metricData[r]?.[c] || 0;
                const exp = expData[r]?.[c] || 0;
                const bg = val > 0 ? getColor(val) : '#f8f9fa';
                const isDiagonal = (r === c);  // 对角线：同品类承接
                let display;
                if (metric === 'exp') {{
                    display = val > 0 ? (val >= 10000 ? Math.round(val/1000) + 'k' : val) : '-';
                }} else {{
                    display = val > 0 ? val.toFixed(2) : '-';
                }}
                // 计算横向和纵向占比
                const rowPct = rowTotals[r] > 0 ? (exp / rowTotals[r] * 100).toFixed(1) : '0.0';
                const colPct = colTotals[c] > 0 ? (exp / colTotals[c] * 100).toFixed(1) : '0.0';
                const tooltip = `进入: ${{r}}\\n承接: ${{c}}\\n${{metric}}: ${{val}}\\nexp: ${{exp.toLocaleString()}}\\n横向占比: ${{rowPct}}%\\n纵向占比: ${{colPct}}%${{isDiagonal ? '\\n★ 同品类承接' : ''}}`;
                const border = isDiagonal ? 'border:2px solid #1565C0;' : '';
                return `<td data-row="${{ri}}" data-col="${{ci}}" style="background:${{bg}};${{border}}" title="${{tooltip}}" onmouseenter="highlightCell(${{ri}},${{ci}})" onmouseleave="unhighlightCell()">${{display}}</td>`;
            }}).join('');
            return `<tr><td class="row-header" data-row="${{ri}}" title="${{r}}\\nexp: ${{rowTotals[r].toLocaleString()}}">${{r.length > 10 ? r.substring(0,10) + '..' : r}}</td>${{cells}}</tr>`;
        }}).join('');
    }}

    // Highlight row/col headers on cell hover
    function highlightCell(row, col) {{
        // Highlight column header
        document.querySelectorAll('#affinity-header th[data-col]').forEach(th => {{
            if (parseInt(th.dataset.col) === col) th.classList.add('highlight');
        }});
        // Highlight row header
        document.querySelectorAll('#affinity-body .row-header').forEach(td => {{
            if (parseInt(td.dataset.row) === row) td.classList.add('highlight');
        }});
    }}

    function unhighlightCell() {{
        document.querySelectorAll('.highlight').forEach(el => el.classList.remove('highlight'));
    }}

    // Ranking
    function initRanking() {{
        const date = document.getElementById('ranking-date').value;
        const crowd = document.getElementById('ranking-crowd').value;
        const topN = parseInt(document.getElementById('ranking-topn').value);

        if (!rankingData[date] || !rankingData[date][crowd]) {{
            document.getElementById('high-ranking').innerHTML = '<tbody><tr><td>无数据</td></tr></tbody>';
            document.getElementById('low-ranking').innerHTML = '<tbody><tr><td>无数据</td></tr></tbody>';
            return;
        }}

        const data = rankingData[date][crowd];

        function renderTable(items, tableId) {{
            const sliced = items.slice(0, topN);
            const html = `
                <thead><tr><th class="rn">#</th><th>品类组合</th><th class="vov">vov</th><th class="exp">曝光</th></tr></thead>
                <tbody>
                    ${{sliced.map((item, i) => `
                        <tr>
                            <td class="rn">${{i + 1}}</td>
                            <td>${{item.pair}}</td>
                            <td class="vov">${{item.vov.toFixed(4)}}</td>
                            <td class="exp">${{item.exp.toLocaleString()}}</td>
                        </tr>
                    `).join('')}}
                </tbody>
            `;
            document.getElementById(tableId).innerHTML = html;
        }}

        // 更新标题
        const dateLabel = date === '全部' ? '' : ` [${{date}}]`;
        const crowdLabel = crowd === '整体' ? '' : ` (${{crowd}})`;
        document.querySelector('.ranking-box.high h4').textContent = `Top ${{topN}} 高裂变品类组合${{crowdLabel}}${{dateLabel}}`;
        document.querySelector('.ranking-box.low h4').textContent = `Top ${{topN}} 低裂变品类组合${{crowdLabel}}${{dateLabel}}`;

        renderTable(data.high, 'high-ranking');
        renderTable(data.low, 'low-ranking');
    }}

    // Matrix date switching
    function switchMatrixDate(delta) {{
        const select = document.getElementById('matrix-date');
        const idx = dateList.indexOf(select.value);
        const newIdx = idx + delta;
        if (newIdx >= 0 && newIdx < dateList.length) {{
            select.value = dateList[newIdx];
            updateMatrix();
        }}
    }}

    function toggleMatrixPlay() {{
        const btn = document.getElementById('matrix-play-btn');
        if (matrixPlayInterval) {{
            clearInterval(matrixPlayInterval);
            matrixPlayInterval = null;
            btn.classList.remove('playing');
            btn.textContent = '▶ 播放';
        }} else {{
            btn.classList.add('playing');
            btn.textContent = '⏸ 停止';
            let idx = 1;  // 从第一个日期开始（跳过"全部"）
            const play = () => {{
                if (idx >= dateList.length) {{
                    clearInterval(matrixPlayInterval);
                    matrixPlayInterval = null;
                    btn.classList.remove('playing');
                    btn.textContent = '▶ 播放';
                    return;
                }}
                document.getElementById('matrix-date').value = dateList[idx];
                updateMatrix();
                idx++;
            }};
            play();
            matrixPlayInterval = setInterval(play, 1500);
        }}
    }}

    // Ranking date switching
    function switchRankingDate(delta) {{
        const select = document.getElementById('ranking-date');
        const idx = dateList.indexOf(select.value);
        const newIdx = idx + delta;
        if (newIdx >= 0 && newIdx < dateList.length) {{
            select.value = dateList[newIdx];
            initRanking();
        }}
    }}

    function toggleRankingPlay() {{
        const btn = document.getElementById('ranking-play-btn');
        if (rankingPlayInterval) {{
            clearInterval(rankingPlayInterval);
            rankingPlayInterval = null;
            btn.classList.remove('playing');
            btn.textContent = '▶ 播放';
        }} else {{
            btn.classList.add('playing');
            btn.textContent = '⏸ 停止';
            let idx = 1;
            const play = () => {{
                if (idx >= dateList.length) {{
                    clearInterval(rankingPlayInterval);
                    rankingPlayInterval = null;
                    btn.classList.remove('playing');
                    btn.textContent = '▶ 播放';
                    return;
                }}
                document.getElementById('ranking-date').value = dateList[idx];
                initRanking();
                idx++;
            }};
            play();
            rankingPlayInterval = setInterval(play, 1500);
        }}
    }}

    // Initialize
    initConsistency();
    updateMatrix();
    initRanking();
    </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}")
