#!/usr/bin/env python3
"""生成港乐词曲资产数据缺口清单 Excel"""
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

wb = openpyxl.Workbook()

# ── 样式定义 ──────────────────────────────────────────
title_font = Font(name="Microsoft YaHei", size=12, bold=True, color="FFFFFFFF")
title_fill = PatternFill(start_color="FF4472C4", end_color="FF4472C4", fill_type="solid")
title_align = Alignment(horizontal="center", vertical="center", wrap_text=True)

header_font = Font(name="宋体", size=10.5, bold=True)
header_fill = PatternFill(start_color="FFD9E2F3", end_color="FFD9E2F3", fill_type="solid")
header_align = Alignment(horizontal="center", vertical="center", wrap_text=True)

data_font = Font(name="Microsoft YaHei", size=10)
data_align = Alignment(vertical="top", wrap_text=True)
center_align = Alignment(horizontal="center", vertical="top", wrap_text=True)

# 处理方式颜色
FILL_RED    = PatternFill(start_color="FFFFC7CE", end_color="FFFFC7CE", fill_type="solid")  # 🔴 人工
FILL_GREEN  = PatternFill(start_color="FFC6EFCE", end_color="FFC6EFCE", fill_type="solid")  # 🟢 爬虫
FILL_YELLOW = PatternFill(start_color="FFFFEB9C", end_color="FFFFEB9C", fill_type="solid")  # 🟡 待扩展
FILL_GRAY   = PatternFill(start_color="FFD9D9D9", end_color="FFD9D9D9", fill_type="solid")  # ⚪ 暂不可操作

thin_border = Border(
    left=Side(style="thin"), right=Side(style="thin"),
    top=Side(style="thin"), bottom=Side(style="thin"),
)

# ── Sheet 1: 数据缺口清单 ─────────────────────────────
ws1 = wb.active
ws1.title = "数据缺口清单"

# 标题行
ws1.merge_cells("A1:G1")
c = ws1["A1"]
c.value = "港乐词曲资产导入 — 数据缺口清单（2026-07-14）"
c.font = title_font; c.fill = title_fill; c.alignment = title_align

# 表头
headers = ["序号", "缺口类型", "数量", "处理方式", "涉及数据", "补齐说明", "优先级"]
for i, h in enumerate(headers, 1):
    cell = ws1.cell(row=2, column=i, value=h)
    cell.font = header_font; cell.fill = header_fill
    cell.alignment = header_align; cell.border = thin_border

# 数据
rows = [
    # (序号, 缺口类型, 数量, 处理方式, 涉及数据, 补齐说明, 优先级, fill)
    (1, "疑似重复，待人工审核", "4,410 首",
     "🔴 人工处理", "去重暂存表中的候选记录",
     "系统判定与已有歌曲高度相似，需人工逐条判断是同一首歌（合并）还是不同歌曲（新增导入）。已部署 L2 审核看板支持批量操作。",
     "P2", FILL_RED),

    (2, "歌词为空且词曲作者均不详", "1,167 首",
     "🔴 人工处理", "源库中歌词和作者字段同时缺失的歌曲",
     "源数据本身缺失，爬虫无法生成不存在的元数据。如需导入，需人工查找并补录歌词和作者信息后重新触发导入。可先抽样检查这批歌曲在平台上是否实际有歌词。",
     "P3", FILL_RED),

    (3, "主录音导入失败——缺歌手信息", "96 条",
     "🟢 爬虫补录", "records1 中因 spider DB 缺歌手关系而失败的记录",
     "平台上有歌手信息但尚未同步到本地爬虫数据库。补录歌手关系后系统自动重试。也可调整策略允许空歌手容错（补充录音已支持，主录音暂未支持）。",
     "P1", FILL_GREEN),

    (4, "主录音导入失败——音频/歌词转存失败", "19 条",
     "🟢 自动重试", "records1 中因网络或存储问题转存失败的记录（音频 10 条 + 歌词 9 条）",
     "属于临时性故障，源文件仍在平台上。重新触发转存即可，无需重新爬取。",
     "P1", FILL_GREEN),

    (5, "主录音导入失败——完全无歌词", "3 条",
     "🟢 爬虫尝试", "records1 中三个平台均无歌词数据的记录",
     "可尝试爬虫从其他来源抓取歌词；如平台确实无歌词，需人工确认是否为纯音乐作品。数量极少，影响可忽略。",
     "P2", FILL_GREEN),

    (6, "歌曲仅有非目标平台录音", "809 首",
     "🟡 扩展平台", "在三平台（QQ/酷狗/网易云）均无有效录音，仅在其他平台有录音的歌曲",
     "当前爬虫仅覆盖三个目标平台。扩展平台覆盖后可自动获取录音数据。需业务确认：是否接受这些歌曲暂不入库？",
     "P3", FILL_YELLOW),

    (7, "已软删歌曲——录音已成功但歌曲不可见", "25 首",
     "🔴 人工恢复", "records1 已成功但歌曲状态仍为 deleted 的记录",
     "历史遗留的状态不一致，主录音数据已入库但歌曲被标记为已删除导致前端不可见。确认后批量恢复即可，无需重新爬取。",
     "P0", FILL_RED),

    (8, "已软删歌曲——现具备三平台录音条件", "71 首",
     "🔴 人工恢复", "之前因审计/清理被软删除，但当前已具备有效录音条件的歌曲",
     "数据已补齐，只需逐条确认删除原因是否已过时，符合条件后恢复歌曲状态。",
     "P2", FILL_RED),

    (9, "已软删歌曲——当前仍无有效三平台录音", "133 首",
     "⚪ 暂不处理", "被软删除且当前仍无法获取三平台录音的歌曲（含 ETL 清理 143 中剩余部分 + song_time=0 + 元数据重复合并）",
     "当前无法补齐录音数据。若后续扩展平台覆盖或有其他数据来源，可重新评估。",
     "P3", FILL_GRAY),

    (10, "原声类歌名", "79 首",
     "🔴 人工处理", "歌名形如 '@XXX创作的原声' 或 '用户创作的原声' 的特殊歌曲",
     "歌名为平台自动生成的标记文本，通常不具备导入价值。其中 78 首同时满足歌词/作者缺失条件被前置规则先拦截，仅 1 首 dedup_reason 显式标注为原声类。建议直接跳过。",
     "P3", FILL_RED),
]

for r_idx, (no, gtype, count, method, scope, desc, priority, fill) in enumerate(rows, 3):
    vals = [no, gtype, count, method, scope, desc, priority]
    for c_idx, v in enumerate(vals, 1):
        cell = ws1.cell(row=r_idx, column=c_idx, value=v)
        cell.font = data_font
        cell.alignment = center_align if c_idx in (1, 3, 4, 7) else data_align
        cell.border = thin_border
        # 处理方式列上色
        if c_idx == 4:
            cell.fill = fill

# 列宽
col_widths = [6, 30, 10, 14, 30, 55, 6]
for i, w in enumerate(col_widths, 1):
    ws1.column_dimensions[get_column_letter(i)].width = w

# ── Sheet 2: 总览 ────────────────────────────────────
ws2 = wb.create_sheet("总览")

ws2.merge_cells("A1:C1")
c = ws2["A1"]
c.value = "港乐词曲资产导入 — 缺口处理总览"
c.font = title_font; c.fill = title_fill; c.alignment = title_align

for i, h in enumerate(["处理方式", "涉及数量", "占比"], 1):
    cell = ws2.cell(row=2, column=i, value=h)
    cell.font = header_font; cell.fill = header_fill
    cell.alignment = header_align; cell.border = thin_border

summary = [
    ("🔴 需人工处理（审核/恢复/补录）", "5,752 首", "84.1%", FILL_RED),
    ("🟢 可通过爬虫/自动手段解决", "118 条", "1.7%", FILL_GREEN),
    ("🟡 需扩展平台覆盖后自动导入", "809 首", "11.9%", FILL_YELLOW),
    ("⚪ 暂不可操作，等待外部条件变化", "133 首", "2.0%", FILL_GRAY),
    ("缺口合计", "约 6,812 首/条", "—", None),
]

for r_idx, (cat, cnt, pct, fill) in enumerate(summary, 3):
    for c_idx, v in enumerate([cat, cnt, pct], 1):
        cell = ws2.cell(row=r_idx, column=c_idx, value=v)
        cell.font = data_font if r_idx < 7 else Font(name="Microsoft YaHei", size=10, bold=True)
        cell.alignment = center_align if c_idx >= 2 else Alignment(vertical="top", wrap_text=True)
        cell.border = thin_border
        if fill and c_idx == 1:
            cell.fill = fill

ws2.column_dimensions["A"].width = 36
ws2.column_dimensions["B"].width = 18
ws2.column_dimensions["C"].width = 10

# ── Sheet 3: 优先级排序 ──────────────────────────────
ws3 = wb.create_sheet("优先级排序")

ws3.merge_cells("A1:E1")
c = ws3["A1"]
c.value = "港乐词曲资产导入 — 补齐优先级排序"
c.font = title_font; c.fill = title_fill; c.alignment = title_align

for i, h in enumerate(["优先级", "处理方式", "涉及数量", "预期效果", "备注"], 1):
    cell = ws3.cell(row=2, column=i, value=h)
    cell.font = header_font; cell.fill = header_fill
    cell.alignment = header_align; cell.border = thin_border

prio_rows = [
    ("P0", "恢复 25 首状态不一致的歌曲", "25 首", "立即恢复前端可见，零成本", "仅需修改 deleted 字段"),
    ("P1", "调整策略后自动重试 96 条缺歌手记录", "96 条", "主录音成功率 99.86% → 99.97%", "需开发评估空歌手容错策略"),
    ("P1", "重试 19 条转存失败记录", "19 条", "自动完成，无额外开发", "临时性故障，重跑即可"),
    ("P2", "人工审核 4,410 首疑似重复歌曲", "4,410 首", "释放暂存积压，可能新增歌曲或确认合并", "已部署审核看板"),
    ("P2", "确认恢复 71 首已删除但已有录音的歌曲", "71 首", "增加有效歌曲数", "逐条确认删除原因"),
    ("P2", "尝试爬取 3 条无歌词记录的歌词", "3 条", "数量极少，可忽略", "如确为纯音乐则标记跳过"),
    ("P3", "扩展爬虫平台覆盖后自动导入", "809 首", "需平台扩展开发", "依赖技术规划"),
    ("P3", "人工补录 1,245+1 首歌词/作者后重新导入", "1,246 首", "工作量大，建议先抽样评估价值", "优先检查是否有实际歌词"),
    ("P3", "等待外部条件后评估 133 首已删歌曲", "133 首", "视平台扩展情况", "当前无可用数据"),
]

for r_idx, (pri, action, cnt, effect, note) in enumerate(prio_rows, 3):
    vals = [pri, action, cnt, effect, note]
    for c_idx, v in enumerate(vals, 1):
        cell = ws3.cell(row=r_idx, column=c_idx, value=v)
        cell.font = data_font
        cell.alignment = center_align if c_idx in (1, 3) else data_align
        cell.border = thin_border
        # 优先级上色
        if c_idx == 1:
            if "P0" in str(v):
                cell.fill = PatternFill(start_color="FFFF6B6B", end_color="FFFF6B6B", fill_type="solid")
                cell.font = Font(name="Microsoft YaHei", size=10, bold=True, color="FFFFFFFF")
            elif "P1" in str(v):
                cell.fill = PatternFill(start_color="FFFFD93D", end_color="FFFFD93D", fill_type="solid")
            elif "P2" in str(v):
                cell.fill = PatternFill(start_color="FF6BCB77", end_color="FF6BCB77", fill_type="solid")
                cell.font = Font(name="Microsoft YaHei", size=10, color="FFFFFFFF")
            elif "P3" in str(v):
                cell.fill = PatternFill(start_color="FF4D96FF", end_color="FF4D96FF", fill_type="solid")
                cell.font = Font(name="Microsoft YaHei", size=10, color="FFFFFFFF")

ws3.column_dimensions["A"].width = 8
ws3.column_dimensions["B"].width = 38
ws3.column_dimensions["C"].width = 12
ws3.column_dimensions["D"].width = 38
ws3.column_dimensions["E"].width = 30

# ── Sheet 4: 字段级缺口 ──────────────────────────────
ws4 = wb.create_sheet("字段级缺口")

ws4.merge_cells("A1:G1")
c = ws4["A1"]
c.value = "港乐词曲资产导入 — 字段级缺口（已入库但关键字段为空）"
c.font = title_font; c.fill = title_fill; c.alignment = title_align

for i, h in enumerate(["序号", "缺口类型", "为空数量", "所在表/范围", "平台分布", "补齐方式", "优先级"], 1):
    cell = ws4.cell(row=2, column=i, value=h)
    cell.font = header_font; cell.fill = header_fill
    cell.alignment = header_align; cell.border = thin_border

field_rows = [
    (1, "歌曲封面图片缺失", "1,960 首",
     "hk_songs_test（86,870 首有效歌曲）", "全平台", 
     "🟢 爬虫可回补：重新触发封面下载/转存；如平台本身无封面则需人工提供",
     "P1", FILL_GREEN),

    (2, "曲作者字段为空", "3,232 首",
     "hk_songs_test（86,870 首有效歌曲）", "全平台",
     "🟢 爬虫可回补：从爬虫数据库 composer_name 批量回写；爬虫也无数据则需人工查找",
     "P1", FILL_GREEN),

    (3, "词作者字段为空", "1,488 首",
     "hk_songs_test（86,870 首有效歌曲）", "全平台",
     "🟢 爬虫可回补：从爬虫数据库 lyricist_name 批量回写；爬虫也无数据则需人工查找",
     "P1", FILL_GREEN),

    (4, "歌手信息为空（singers=[]）", "393 条",
     "爬虫库 songs 表（25,369 首已导入歌曲）",
     "QQ: 373 / 酷狗: 5 / 网易云: 15",
     "🟢 爬虫可回补：系统已内置 backfill-empty-singers 功能，从 spider DB 补录歌手关系",
     "P1", FILL_GREEN),

    (5, "歌手头像缺失", "4,057 人",
     "爬虫库 singers 表（13,142 位歌手）",
     "QQ: 3,039 / 酷狗: 549 / 网易云: 469",
     "🟢 爬虫可回补：重新爬取歌手页面获取最新头像 URL 并转存 OSS",
     "P1", FILL_GREEN),

    (6, "歌词文本为空", "366 条",
     "爬虫库 songs 表（25,369 首已导入歌曲）",
     "QQ: 201 / 酷狗: 157 / 网易云: 8",
     "🟢 爬虫可回补：重新爬取歌词页面；如平台确实无歌词则为纯音乐，无需补齐",
     "P2", FILL_GREEN),

    (7, "歌曲封面为空（爬虫侧）", "139 条",
     "爬虫库 songs 表（25,369 首已导入歌曲）",
     "QQ: 18 / 酷狗: 58 / 网易云: 63",
     "🟢 爬虫可回补：重新触发封面下载/转存",
     "P1", FILL_GREEN),

    (8, "专辑封面为空", "82 个",
     "爬虫库 albums 表（21,081 个专辑）",
     "QQ: 0 / 酷狗: 21 / 网易云: 61",
     "🟢 爬虫可回补：重新触发专辑封面下载/转存",
     "P2", FILL_GREEN),
]

for r_idx, (no, gtype, count, scope, dist, method, priority, fill) in enumerate(field_rows, 3):
    vals = [no, gtype, count, scope, dist, method, priority]
    for c_idx, v in enumerate(vals, 1):
        cell = ws4.cell(row=r_idx, column=c_idx, value=v)
        cell.font = data_font
        cell.alignment = center_align if c_idx in (1, 3, 7) else data_align
        cell.border = thin_border
        if c_idx == 6:
            cell.fill = fill

# 汇总行
sum_row = 3 + len(field_rows)
ws4.merge_cells(f"A{sum_row}:B{sum_row}")
ws4.cell(row=sum_row, column=1, value="字段级缺口合计").font = Font(name="Microsoft YaHei", size=10, bold=True)
ws4.cell(row=sum_row, column=3, value="约 11,578").font = Font(name="Microsoft YaHei", size=10, bold=True)
ws4.cell(row=sum_row, column=6, value="绝大部分可通过爬虫回补").font = Font(name="Microsoft YaHei", size=10, bold=True)
for c_idx in range(1, 8):
    ws4.cell(row=sum_row, column=c_idx).border = thin_border

ws4.column_dimensions["A"].width = 6
ws4.column_dimensions["B"].width = 26
ws4.column_dimensions["C"].width = 12
ws4.column_dimensions["D"].width = 32
ws4.column_dimensions["E"].width = 28
ws4.column_dimensions["F"].width = 52
ws4.column_dimensions["G"].width = 6

# ── 更新 Sheet 3 优先级排序：加入字段级缺口 ──────────────
# 在现有 prio_rows 后面插入字段级缺口的优先级行
field_prio_rows = [
    ("P1", "爬虫回补 1,960 首封面图片", "1,960 首", "前端展示效果显著改善", "重新触发封面下载/转存"),
    ("P1", "爬虫回补 4,057 位歌手头像", "4,057 人", "歌手页面展示完整", "重新爬取歌手页面"),
    ("P1", "爬虫回补词曲作者（3,232 + 1,488 首）", "4,720 首", "元数据完整性提升", "从爬虫库批量回写"),
    ("P1", "爬虫回补 393 条歌手信息", "393 条", "歌手关联完整性提升", "backfill-empty-singers"),
    ("P2", "爬虫回补 366 条歌词文本", "366 条", "歌词展示完整", "重新爬取歌词页面"),
    ("P2", "爬虫回补 139 条歌曲封面 + 82 个专辑封面", "221 条", "爬虫侧数据完整性提升", "重新触发转存"),
]

insert_row = 3 + len(prio_rows)
for i, (pri, action, cnt, effect, note) in enumerate(field_prio_rows):
    r = insert_row + i
    vals = [pri, action, cnt, effect, note]
    for c_idx, v in enumerate(vals, 1):
        cell = ws3.cell(row=r, column=c_idx, value=v)
        cell.font = data_font
        cell.alignment = center_align if c_idx in (1, 3) else data_align
        cell.border = thin_border
        if c_idx == 1:
            if "P1" in str(v):
                cell.fill = PatternFill(start_color="FFFFD93D", end_color="FFFFD93D", fill_type="solid")
            elif "P2" in str(v):
                cell.fill = PatternFill(start_color="FF6BCB77", end_color="FF6BCB77", fill_type="solid")
                cell.font = Font(name="Microsoft YaHei", size=10, color="FFFFFFFF")

# ── 更新 Sheet 2 总览：加入字段级缺口分类 ──────────────
# 先取消已有的合并单元格，再插入新行
for merged in list(ws2.merged_cells.ranges):
    ws2.unmerge_cells(str(merged))

# 在现有 summary 行后插入字段级缺口行
field_summary = [
    ("", "", "", None),  # 空行分隔
    ("— 字段级缺口 —", "", "", None),
    ("🟢 爬虫可回补（封面/歌手/作者/头像/歌词）", "约 11,578 条", "—", FILL_GREEN),
    ("  其中：歌曲封面图片", "1,960 首", "—", None),
    ("  其中：词曲作者", "4,720 首", "—", None),
    ("  其中：歌手信息", "393 条", "—", None),
    ("  其中：歌手头像", "4,057 人", "—", None),
    ("  其中：歌词文本", "366 条", "—", None),
    ("  其中：歌曲/专辑封面（爬虫侧）", "221 条", "—", None),
]

insert_row2 = 3 + len(summary)
for i, (cat, cnt, pct, fill) in enumerate(field_summary):
    r = insert_row2 + i
    for c_idx, v in enumerate([cat, cnt, pct], 1):
        cell = ws2.cell(row=r, column=c_idx, value=v)
        cell.font = data_font
        cell.alignment = center_align if c_idx >= 2 else Alignment(vertical="top", wrap_text=True)
        cell.border = thin_border
        if fill and c_idx == 1:
            cell.fill = fill
        if cat.startswith("—"):
            cell.font = Font(name="Microsoft YaHei", size=10, bold=True)

# 重新设置说明区域
notes_start = insert_row2 + len(field_summary) + 1
ws2.merge_cells(f"A{notes_start}:C{notes_start}")
ws2.cell(row=notes_start, column=1, value="📋 处理方式说明：").font = Font(name="Microsoft YaHei", size=10, bold=True)

notes = [
    "1. 🔴 人工处理：需业务/版权人员人工审核、恢复或补录源数据，系统仅辅助呈现和标记",
    "2. 🟢 爬虫/自动：可通过爬虫补录歌手关系、重试转存失败、或重新触发导入流程自动完成",
    "3. 🟡 扩展平台：当前爬虫仅覆盖 QQ/酷狗/网易云三平台，扩展覆盖范围后可自动导入",
    "4. ⚪ 暂不处理：当前无法获取录音数据，等待平台扩展或外部数据源接入后再评估",
]
for i, note in enumerate(notes):
    cell = ws2.cell(row=notes_start + 1 + i, column=1, value=note)
    cell.font = Font(name="Microsoft YaHei", size=9, color="FF666666")
    ws2.merge_cells(f"A{notes_start + 1 + i}:C{notes_start + 1 + i}")

# ── Sheet 5: 已同步数据（records1）─────────────────────
ws5 = wb.create_sheet("已同步数据（records1）")

ws5.merge_cells("A1:G1")
c = ws5["A1"]
c.value = "港乐词曲资产导入 — 已同步数据总览（records1 主录音）"
c.font = title_font; c.fill = title_fill; c.alignment = title_align

# ── 5.1 平台分布与导入状态 ──
r = 3
ws5.cell(row=r, column=1, value="一、各平台导入状态").font = Font(name="Microsoft YaHei", size=10.5, bold=True)
r = 4
for i, h in enumerate(["平台", "总量", "已成功", "失败", "成功率"], 1):
    cell = ws5.cell(row=r, column=i, value=h)
    cell.font = header_font; cell.fill = header_fill
    cell.alignment = header_align; cell.border = thin_border

plat_rows = [
    ("QQ 音乐", "56,132", "56,068", "64", "99.89%"),
    ("酷狗", "14,716", "14,698", "18", "99.88%"),
    ("网易云", "15,338", "15,302", "36", "99.77%"),
    ("合计", "86,186", "86,068", "118", "99.86%"),
]
for i, (plat, total, ok, fail, rate) in enumerate(plat_rows, 5):
    vals = [plat, total, ok, fail, rate]
    for c_idx, v in enumerate(vals, 1):
        cell = ws5.cell(row=i, column=c_idx, value=v)
        cell.font = data_font if i < 8 else Font(name="Microsoft YaHei", size=10, bold=True)
        cell.alignment = center_align
        cell.border = thin_border
        if i == 8:
            cell.fill = PatternFill(start_color="FFD9E2F3", end_color="FFD9E2F3", fill_type="solid")

# ── 5.2 失败原因分布 ──
r = 10
ws5.cell(row=r, column=1, value="二、导入失败原因分布").font = Font(name="Microsoft YaHei", size=10.5, bold=True)
r = 11
for i, h in enumerate(["失败原因", "失败码", "QQ", "酷狗", "网易云", "合计", "占比"], 1):
    cell = ws5.cell(row=r, column=i, value=h)
    cell.font = header_font; cell.fill = header_fill
    cell.alignment = header_align; cell.border = thin_border

fail_rows = [
    ("spider DB 缺歌手关系", "-5", "48", "13", "35", "96", "81.4%"),
    ("音频下载或 OSS 转存失败", "-6", "9", "1", "0", "10", "8.5%"),
    ("歌词 OSS 转存失败", "-8", "7", "1", "1", "9", "7.6%"),
    ("无可用歌词", "-2", "0", "3", "0", "3", "2.5%"),
    ("合计", "", "64", "18", "36", "118", "100%"),
]
for i, (reason, code, qq, kg, ne, total, pct) in enumerate(fail_rows, 12):
    vals = [reason, code, qq, kg, ne, total, pct]
    for c_idx, v in enumerate(vals, 1):
        cell = ws5.cell(row=i, column=c_idx, value=v)
        cell.font = data_font if i < 16 else Font(name="Microsoft YaHei", size=10, bold=True)
        cell.alignment = center_align
        cell.border = thin_border
        if i == 16:
            cell.fill = PatternFill(start_color="FFD9E2F3", end_color="FFD9E2F3", fill_type="solid")

# ── 5.3 已入库歌曲字段完整度 ──
r = 18
ws5.cell(row=r, column=1, value="三、已入库歌曲字段完整度（records1 严格范围，86,186 首）").font = Font(name="Microsoft YaHei", size=10.5, bold=True)
r = 19
for i, h in enumerate(["字段", "有值", "为空", "完整率", "补齐方式"], 1):
    cell = ws5.cell(row=r, column=i, value=h)
    cell.font = header_font; cell.fill = header_fill
    cell.alignment = header_align; cell.border = thin_border

field_rows = [
    ("歌名（name）", "86,186", "0", "100%", "—"),
    ("音频链接（audio_url）", "86,186", "0", "100%", "—"),
    ("歌词链接（lyrics_url）", "86,183", "3", "100%", "🟢 可忽略"),
    ("歌手文本（singer）", "86,186", "0", "100%", "—"),
    ("封面图片（cover_url）", "84,427", "1,759", "98.0%", "🟢 爬虫回补"),
    ("曲作者（composer）", "84,702", "1,484", "98.3%", "🟢 从爬虫库批量回写"),
    ("词作者（lyricist）", "82,955", "3,231", "96.3%", "🟢 从爬虫库批量回写"),
    ("发行时间（issue_time）", "68,797", "17,389", "79.8%", "🟡 爬虫可尝试爬取"),
    ("BPM 分类（bpm_class）", "35,169", "51,017", "40.8%", "🟡 需 BPM 分析工具计算"),
]
for i, (field, has, empty, rate, method) in enumerate(field_rows, 20):
    vals = [field, has, empty, rate, method]
    for c_idx, v in enumerate(vals, 1):
        cell = ws5.cell(row=i, column=c_idx, value=v)
        cell.font = data_font
        cell.alignment = center_align if c_idx >= 2 else data_align
        cell.border = thin_border
        if c_idx == 4 and rate != "—":
            pct = float(rate.replace('%', ''))
            if pct < 95:
                cell.fill = FILL_YELLOW
            elif pct < 99:
                cell.fill = FILL_GREEN

ws5.column_dimensions["A"].width = 30
ws5.column_dimensions["B"].width = 12
ws5.column_dimensions["C"].width = 12
ws5.column_dimensions["D"].width = 12
ws5.column_dimensions["E"].width = 24
ws5.column_dimensions["F"].width = 10
ws5.column_dimensions["G"].width = 8

# 保存
out = "docs/港乐词曲资产数据缺口清单.xlsx"
wb.save(out)
print(f"✅ 已生成 {out}")