#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""产品系列资料补齐甘特工作台 V02
- 把每项任务拆成：缺失核心项×2d + 建档2d + DG专项2d + NA专项2d
- 每项任务再加 30% 容错，向上取整到工作日
- Excel：拆解明细 / 6D构成 / 资源方案 / 日历视图
- HTML：参数填写框 + 实时重算 + 可筛选甘特图
"""
import base64
import datetime as dt
import json
import math
import os
import sys

import openpyxl
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter

sys.path.insert(0, '/Users/liuxinyuan/Desktop/Hermes输出-工作类/脚本')
import brand_kit as BK

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
SRC = os.path.join(ROOT, '销售分析', '产品系列资料归档与缺口清单_v3_2026-10-26.xlsx')
OUT_XLSX = os.path.join(ROOT, '经营分析', '产品系列资料补齐_排期与甘特图_20261030_V02.xlsx')
OUT_HTML = os.path.join(ROOT, '销售分析', '产品系列资料补齐_甘特工作台_20261030_V02.html')

START = dt.date(2026, 11, 1)
WINDOW_END = dt.date(2027, 1, 31)
HOLIDAYS = {dt.date(2027, 1, 1)}

DEFAULTS = {
    'core': 2.0,
    'archive': 2.0,
    'dg': 2.0,
    'na': 2.0,
    'buffer': 30.0,
    'resources': 5,
    'start': START.isoformat(),
    'window_end': WINDOW_END.isoformat(),
}

RESOURCE_NAMES = [
    '研发-占位A', '研发-占位B', '研发-占位C', '产品-占位D', '产品-占位E',
    '研发-占位F', '产品-占位G', '资源-占位H',
]


def workdays(start, count):
    out, d = [], start
    while len(out) < count:
        if d.weekday() < 5 and d not in HOLIDAYS:
            out.append(d)
        d += dt.timedelta(days=1)
    return out


def load_tasks():
    ws = openpyxl.load_workbook(SRC, data_only=True)['系列缺口任务清单']
    tasks = []
    for r in ws.iter_rows(min_row=2, values_only=True):
        if not r[0]:
            continue
        missing = str(r[9] or '')
        items = [x.strip() for x in missing.split('、') if x.strip() and x.strip() != '无']
        special = str(r[10] or '')
        tasks.append({
            'id': str(r[0]),
            'rep': str(r[1] or ''),
            'series': str(r[2] or ''),
            'cat': str(r[3] or ''),
            'finished': int(r[4] or 0),
            'sales': float(r[5] or 0),
            'customers': int(r[6] or 0),
            'sole_share': float(r[7] or 0),
            'docs': int(r[8] or 0),
            'missing': missing,
            'missing_items': items,
            'special': special,
            'priority': str(r[11] or 'P2'),
            'action': str(r[12] or ''),
            'dept': str(r[13] or ''),
            'n_missing': len(items),
            'has_dg': 1 if 'DG' in special else 0,
            'has_na': 1 if 'NA' in special else 0,
        })
    return tasks


def calc_task(t, p):
    core = p['core'] * t['n_missing']
    archive = p['archive'] if t['docs'] == 0 else 0
    dg = p['dg'] if t['has_dg'] else 0
    na = p['na'] if t['has_na'] else 0
    base = core + archive + dg + na
    buffer_raw = base * p['buffer'] / 100.0
    buffer = int(math.ceil(buffer_raw - 1e-9))
    total = int(math.ceil(base + buffer_raw - 1e-9))
    return {
        'core_days': round(core, 2),
        'archive_days': round(archive, 2),
        'dg_days': round(dg, 2),
        'na_days': round(na, 2),
        'base_days': round(base, 2),
        'buffer_raw': round(buffer_raw, 2),
        'buffer_days': int(total - base),
        'total_days': total,
        'base_formula': f"{p['core']:g}×{t['n_missing']} + {p['archive']:g}×{'1' if t['docs']==0 else '0'} + {p['dg']:g}×{t['has_dg']} + {p['na']:g}×{t['has_na']}",
        'total_formula': f"ceil({base:g}×1.{int(round(p['buffer']*10)):02d})",
    }


def schedule(tasks, p, resource_count):
    cal = workdays(dt.date.fromisoformat(p['start']), 900)
    resources = RESOURCE_NAMES[:resource_count]
    free = {r: 0 for r in resources}
    order = {'P0': 0, 'P1': 1, 'P2': 2}
    scheduled = []
    for t in sorted(tasks, key=lambda x: (order.get(x['priority'], 9), -x['sales'])):
        c = calc_task(t, p)
        r = min(resources, key=lambda x: (free[x], resources.index(x)))
        s = free[r]
        e = s + c['total_days'] - 1
        row = dict(t)
        row.update(c)
        row['owner'] = r
        row['start'] = cal[s].isoformat()
        row['end'] = cal[e].isoformat()
        row['start_idx'] = s
        row['end_idx'] = e
        row['window_status'] = '超窗' if cal[e] > WINDOW_END else '窗口内'
        scheduled.append(row)
        free[r] = e + 1
    return scheduled, cal


def build_xlsx(tasks, p):
    sched5, _ = schedule(tasks, p, 5)
    wb = openpyxl.Workbook()
    H = Font(name='Arial', color='FFFFFF', bold=True, size=10)
    HF = PatternFill('solid', fgColor='1F4E79')
    thin = Alignment(vertical='center', wrap_text=True)
    base_fill = PatternFill('solid', fgColor='E2EFDA')
    buffer_fill = PatternFill('solid', fgColor='FCE4D6')
    warn_fill = PatternFill('solid', fgColor='F8CBAD')
    ok_fill = PatternFill('solid', fgColor='C6EFCE')

    def style_header(ws, ncol):
        for c in ws[1][:ncol]:
            c.font = H; c.fill = HF; c.alignment = thin
        ws.freeze_panes = 'A2'
        ws.sheet_view.showGridLines = False

    ws = wb.active
    ws.title = '甘特数据'
    heads = ['任务ID', '优先级', '系列', '代表货号', '缺失核心项', '核心项数',
             '核心项人日', '建档人日', 'DG专项人日', 'NA专项人日', '基准人日',
             '容错率%', '容错人日', '计划工期', '负责人(5人方案)', '开始', '完成',
             '窗口状态', '2026销售额(万)', '客户数', '资料份数']
    ws.append(heads)
    for t in sched5:
        ws.append([t['id'], t['priority'], t['series'], t['rep'], t['missing'], t['n_missing'],
                   t['core_days'], t['archive_days'], t['dg_days'], t['na_days'], t['base_days'],
                   p['buffer'], t['buffer_days'], t['total_days'], t['owner'], t['start'], t['end'],
                   t['window_status'], t['sales'], t['customers'], t['docs']])
    style_header(ws, len(heads))
    for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
        for c in row:
            c.alignment = thin
        row[10].fill = base_fill
        row[12].fill = buffer_fill
        row[17].fill = ok_fill if row[17].value == '窗口内' else warn_fill
    for i, w in enumerate([8, 7, 28, 11, 34, 7, 8, 8, 9, 9, 9, 8, 8, 9, 14, 11, 11, 9, 11, 7, 7], 1):
        ws.column_dimensions[get_column_letter(i)].width = w

    # 工期拆解
    ws2 = wb.create_sheet('工期拆解')
    h2 = ['任务ID', '优先级', '系列', '缺失核心项', '核心项构成', '建档',
          'DG专项', 'NA专项', '基准人日', '构成公式', '30%容错人日', '计划工期',
          '计划公式', '开始', '完成', '负责人']
    ws2.append(h2)
    for t in sched5:
        core_formula = f"{t['n_missing']}项×{p['core']:g}"
        ws2.append([t['id'], t['priority'], t['series'], t['missing'], core_formula,
                    f"{p['archive']:g}×{'1' if t['docs']==0 else '0'}",
                    f"{p['dg']:g}×{t['has_dg']}", f"{p['na']:g}×{t['has_na']}",
                    t['base_days'], t['base_formula'], t['buffer_days'], t['total_days'],
                    t['total_formula'], t['start'], t['end'], t['owner']])
    style_header(ws2, len(h2))
    for i, w in enumerate([8, 7, 28, 34, 12, 8, 9, 9, 9, 42, 10, 9, 22, 11, 11, 14], 1):
        ws2.column_dimensions[get_column_letter(i)].width = w

    # 6D 构成（基准确为 6 的任务）
    ws3 = wb.create_sheet('6D构成')
    h3 = ['任务ID', '代表货号', '产品系列', '缺失项', '核心项构成', '核心项人日',
          '建档', 'DG专项', 'NA专项', '基准合计', '30%容错', '计划工期', '构成说明']
    ws3.append(h3)
    n6 = 0
    for t in sched5:
        if t['base_days'] == 6:
            n6 += 1
            parts = []
            if t['n_missing']:
                parts.append(f"核心项{t['n_missing']}×{p['core']:g}={t['core_days']:g}d")
            if t['archive_days']:
                parts.append(f"建档{p['archive']:g}={t['archive_days']:g}d")
            if t['dg_days']:
                parts.append(f"DG{p['dg']:g}={t['dg_days']:g}d")
            if t['na_days']:
                parts.append(f"NA{p['na']:g}={t['na_days']:g}d")
            ws3.append([t['id'], t['rep'], t['series'], t['missing'],
                        f"{t['n_missing']}项×{p['core']:g}", t['core_days'],
                        t['archive_days'], t['dg_days'], t['na_days'], t['base_days'],
                        t['buffer_days'], t['total_days'], ' + '.join(parts)])
    if n6 == 0:
        ws3.append(['—', '', '当前参数下无基准=6D任务', '', '', '', '', '', '', '', '', '', ''])
    style_header(ws3, len(h3))
    for i, w in enumerate([8, 11, 28, 34, 12, 9, 8, 9, 9, 9, 9, 9, 52], 1):
        ws3.column_dimensions[get_column_letter(i)].width = w

    # 资源方案
    ws4 = wb.create_sheet('资源方案')
    h4 = ['资源数', '基准人日', '容错人日', '计划人日', '最晚完成', '超出目标窗口任务数', '说明']
    ws4.append(h4)
    for n in [5, 6, 7, 8]:
        sch, _ = schedule(tasks, p, n)
        last = max(x['end'] for x in sch)
        over = sum(1 for x in sch if x['window_status'] == '超窗')
        note = '当前方案' if n == 5 else ('满足 2027-01-31 窗口' if over == 0 else '')
        ws4.append([n, sum(x['base_days'] for x in sch), sum(x['buffer_days'] for x in sch),
                    sum(x['total_days'] for x in sch), last, over, note])
    style_header(ws4, len(h4))
    for row in ws4.iter_rows(min_row=2, max_row=ws4.max_row):
        for c in row:
            c.alignment = thin
        row[6].fill = ok_fill if row[5].value == 0 else warn_fill
    for i, w in enumerate([8, 10, 10, 10, 12, 18, 26], 1):
        ws4.column_dimensions[get_column_letter(i)].width = w

    # 日历视图（自然日铺格，5人方案）
    ws5 = wb.create_sheet('日历视图')
    d0 = dt.date.fromisoformat(p['start'])
    d1 = max(dt.date.fromisoformat(x['end']) for x in sched5)
    days = []
    d = d0
    while d <= d1:
        days.append(d); d += dt.timedelta(days=1)
    ws5.cell(1, 1, '任务'); ws5.cell(1, 2, '负责人'); ws5.cell(1, 3, '计划工期')
    ws5.cell(2, 1, ''); ws5.cell(2, 2, '')
    for j, day in enumerate(days, 4):
        ws5.cell(2, j, day.strftime('%m/%d')).font = Font(size=8)
        ws5.column_dimensions[get_column_letter(j)].width = 2.2
    for i, t in enumerate(sched5, 3):
        ws5.cell(i, 1, f"{t['id']} {t['rep']}").font = Font(size=9, bold=True)
        ws5.cell(i, 2, t['owner']).font = Font(size=8)
        ws5.cell(i, 3, f"{t['total_days']}d（{t['base_days']:g}+{t['buffer_days']}）").font = Font(size=8)
        for j, day in enumerate(days, 4):
            if t['start'] <= day.isoformat() <= t['end']:
                ws5.cell(i, j).fill = PatternFill('solid', fgColor={'P0': 'C0392B', 'P1': 'E67E22', 'P2': '2E86C1'}[t['priority']])
    ws5.column_dimensions['A'].width = 26; ws5.column_dimensions['B'].width = 13; ws5.column_dimensions['C'].width = 14
    ws5.freeze_panes = 'D3'; ws5.sheet_view.showGridLines = False

    # 口径与资源
    ws6 = wb.create_sheet('口径与资源')
    sched5_last = max(x['end'] for x in sched5)
    over5 = sum(1 for x in sched5 if x['window_status'] == '超窗')
    rows = [
        ['时间窗', '2026-11-01 ~ 2027-01-31'],
        ['默认方案', '5 名占位资源；30% 容错；计划工期 = ceil(基准人日 × 1.30)'],
        ['基准公式', '基准 = 每缺失核心项 2d + 无资料建档 2d + DG专项 2d + NA专项 2d'],
        ['容错公式', '容错 = ceil(基准 × 30%)；计划工期 = 基准 + 容错'],
        ['取整规则', '每个任务独立向上取整到工作日，因此实际容错高于30%（6d→8d，12d→16d）'],
        ['工作日', '周一至周五，跳过 2027-01-01；周末、元旦不排'],
        ['资源规则', '每个任务由1名资源独占；P0→P1→P2，同优先级按销售额降序'],
        ['5人方案结果', f'总基准 {sum(x["base_days"] for x in sched5):.0f} 人日；总容错 {sum(x["buffer_days"] for x in sched5):.0f} 人日；计划 {sum(x["total_days"] for x in sched5):.0f} 人日；最晚 {sched5_last}'],
        ['窗口冲突', f'5人方案有 {over5} 个任务超出 2027-01-31；6人方案最晚 2027-01-22，可落窗'],
        ['说明', '这是管理估算，不含实验排队、样品到位、外部检测周期；资源为占位符。'],
    ]
    for row in rows:
        ws6.append(row)
    ws6.column_dimensions['A'].width = 16; ws6.column_dimensions['B'].width = 105
    for row in ws6.iter_rows(min_row=1, max_row=ws6.max_row):
        row[0].font = Font(bold=True)
        row[1].alignment = Alignment(wrap_text=True, vertical='top')
    ws6.sheet_view.showGridLines = False

    wb.save(OUT_XLSX)
    return sched5, sched5_last, over5


def build_html(tasks, p):
    data = json.dumps(tasks, ensure_ascii=False)
    defaults = json.dumps(DEFAULTS, ensure_ascii=False)
    logo = base64.b64encode(open(os.path.expanduser('~/work/公司资料/logo_transparent.png'), 'rb').read()).decode()
    html = r'''<!DOCTYPE html>
<html lang="zh-CN"><head><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
<title>产品系列资料补齐 · 甘特工作台（可调参）</title>
<style>
*{box-sizing:border-box;margin:0;padding:0}
html,body{max-width:100%;overflow-x:hidden}
body{font-family:-apple-system,"PingFang SC","Microsoft YaHei",sans-serif;background:#f4f7fb;color:#1c2b3a}
.wrap{max-width:1560px;margin:0 auto;padding:18px 22px 48px;overflow:hidden}
h1{font-size:23px;color:#12446e;margin:6px 0 4px}
.sub{font-size:13px;color:#5b7186;margin-bottom:14px;line-height:1.7}
.kpis{display:grid;grid-template-columns:repeat(6,1fr);gap:10px;margin:12px 0 14px}
.kpi{background:#fff;border-radius:10px;padding:11px 14px;box-shadow:0 1px 3px rgba(16,42,67,.08);border-left:4px solid #2e86c1}
.kpi b{display:block;font-size:21px;color:#12446e;line-height:1.2}
.kpi span{font-size:11px;color:#6b8199}
.kpi.warn{border-left-color:#c0392b}.kpi.ok{border-left-color:#2e8b57}
.logic{background:#fff;border:1px solid #dce8f3;border-radius:12px;padding:14px 16px;margin:12px 0 14px;box-shadow:0 1px 3px rgba(16,42,67,.05)}
.logic h2{font-size:14px;color:#12446e;margin-bottom:3px}
.logic .hint{font-size:11.5px;color:#73899d;margin-bottom:10px;line-height:1.6}
.grid{display:grid;grid-template-columns:repeat(6,1fr);gap:10px}
.field{background:#f8fbfe;border:1px solid #e3edf5;border-radius:8px;padding:8px 9px}
.field label{display:block;font-size:11px;color:#5b7186;margin-bottom:4px}
.field input,.field select{width:100%;border:1px solid #cfdce8;border-radius:5px;background:#fff;padding:5px 7px;font-size:13px;color:#17364f}
.formula{margin-top:10px;background:#f2f7fc;border-left:3px solid #2e86c1;padding:8px 11px;font-size:12px;color:#33566f;line-height:1.75}
.actions{display:flex;gap:8px;align-items:center;flex-wrap:wrap;margin-top:10px}
.actions button{border:1px solid #cfdce8;background:#fff;color:#33566f;padding:6px 13px;border-radius:15px;font-size:12px;cursor:pointer}
.actions button.primary{background:#12446e;color:#fff;border-color:#12446e}
#status{font-size:12px;font-weight:600}
.ctrl{display:flex;gap:8px;flex-wrap:wrap;align-items:center;margin:8px 0 10px}
.ctrl button{border:1px solid #cfdce8;background:#fff;color:#33566f;padding:6px 14px;border-radius:16px;font-size:12px;cursor:pointer}
.ctrl button.on{background:#12446e;color:#fff;border-color:#12446e}
.ctrl input{border:1px solid #cfdce8;border-radius:16px;padding:6px 13px;font-size:12px;width:220px}
.legend{margin-left:auto;font-size:11.5px;color:#5b7186}
.dot{display:inline-block;width:9px;height:9px;border-radius:2px;margin:0 4px 0 10px}
.board{background:#fff;border-radius:12px;box-shadow:0 2px 8px rgba(16,42,67,.07);overflow:hidden;max-width:100%}
.months{display:flex;border-bottom:1px solid #e3ecf4;background:#f9fbfd;min-width:max-content}
.mh{flex:none;text-align:center;font-size:11px;color:#41637e;padding:7px 0;border-right:1px solid #e3ecf4;font-weight:600}
.scroll{overflow-x:auto;max-width:100%;-webkit-overflow-scrolling:touch}
.grid2{position:relative;width:max-content;min-width:100%}
.timeline{position:relative;height:26px;border-bottom:1px solid #e7eef6;background:#fbfdff;margin-left:360px}
.tw{position:absolute;top:6px;font-size:9px;color:#8ba2b8;transform:translateX(1px)}
.tw.wk{color:#41637e;font-weight:700;border-left:1px solid #dbe6f0;height:26px;top:0;padding-top:8px}
.row{display:flex;border-bottom:1px solid #eef4f9;min-height:52px}
.row:hover{background:#f6faff}
.task{width:360px;flex:none;padding:8px 12px;border-right:1px solid #e7eef6}
.tname{font-size:12.5px;font-weight:600;color:#17364f;line-height:1.4}
.tser{font-weight:400;color:#7690a6;font-size:10.5px;margin-left:4px}
.tmeta{font-size:10.5px;color:#8aa1b6;margin-top:3px;line-height:1.5}
.pri{display:inline-block;font-size:9px;color:#fff;border-radius:3px;padding:1px 5px;margin-right:4px}
.pP0{background:#c0392b}.pP1{background:#e67e22}.pP2{background:#2e86c1}
.lane{position:relative;flex:1;min-width:__N__px}
.bar{position:absolute;top:15px;height:21px;border-radius:4px;color:#fff;font-size:10px;display:flex;overflow:hidden;box-shadow:0 1px 2px rgba(0,0,0,.12);background:#2e86c1}
.bar .base{height:100%;display:flex;align-items:center;justify-content:flex-end;padding-right:4px;font-size:10px;white-space:nowrap}
.bar .buf{height:100%;background:repeating-linear-gradient(135deg,rgba(255,255,255,.48) 0 4px,rgba(255,255,255,.16) 4px 8px);border-left:1px dashed rgba(255,255,255,.75)}
.bar .lab{position:absolute;right:4px;top:3px;font-size:9.5px;color:#fff;text-shadow:0 1px 1px rgba(0,0,0,.25)}
.bar.pP0{background:#c0392b}.bar.pP1{background:#e67e22}.bar.pP2{background:#2e86c1}
.six{background:#fff;border-radius:12px;padding:13px 16px;margin:14px 0;box-shadow:0 1px 3px rgba(16,42,67,.07)}
.six h2{font-size:14px;color:#12446e;margin-bottom:3px}.six .hint{font-size:11px;color:#73899d;margin-bottom:8px}
.sixrow{display:flex;gap:8px;flex-wrap:wrap;align-items:center;border-top:1px solid #eef4f9;padding:6px 0;font-size:11.5px;color:#33566f}
.sixrow b{color:#17364f}.sixrow .tag{background:#eaf3fb;border:1px solid #d6e6f5;border-radius:10px;padding:1px 7px;font-size:10.5px}
.sixrow .plus{color:#2e86c1;font-weight:700}
.notes{background:#fff;border-radius:10px;padding:12px 15px;margin-top:14px;font-size:11.5px;color:#5b7186;line-height:1.85;box-shadow:0 1px 3px rgba(16,42,67,.05)}
.notes b{color:#33566f}
.empty{padding:24px;text-align:center;color:#8aa1b6;font-size:12px}
@media(max-width:900px){.kpis{grid-template-columns:repeat(3,1fr)}.grid{grid-template-columns:repeat(3,1fr)}}
@media(max-width:760px){
 .kpis{grid-template-columns:repeat(2,1fr)}.grid{grid-template-columns:repeat(2,1fr)}
 .task{width:200px}.timeline{margin-left:200px}.tname{font-size:11.5px}.tser{display:none}.tmeta{font-size:10px}
 .wrap{padding:14px 12px 36px}h1{font-size:18px}.sub{font-size:11.5px}
 .legend{margin-left:0;width:100%}
}
</style></head><body>
<div class="wrap">
<h1>产品系列资料补齐 · 甘特工作台</h1>
<div class="sub">时间窗 2026-11-01 → 2027-01-31 ｜ 任务按“缺失核心项 + 建档 + DG/NA专项”拆解 ｜ 每项预留 30% 容错 ｜ 参数改动后甘特图实时重算</div>

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

<section class="logic">
  <h2>逻辑调整填写框</h2>
  <div class="hint">改下面任意数字，任务工期、容错、资源排队和甘特条会立即重算；默认口径为 2026-10-26 排期口径 + 30% 容错。</div>
  <div class="grid">
    <div class="field"><label>每缺失核心项（人日）</label><input id="core" type="number" min="0" max="20" step="0.5"></div>
    <div class="field"><label>无资料建档（人日）</label><input id="archive" type="number" min="0" max="20" step="0.5"></div>
    <div class="field"><label>DG专项（人日）</label><input id="dg" type="number" min="0" max="20" step="0.5"></div>
    <div class="field"><label>NA专项（人日）</label><input id="na" type="number" min="0" max="20" step="0.5"></div>
    <div class="field"><label>容错率（%）</label><input id="buffer" type="number" min="0" max="100" step="1"></div>
    <div class="field"><label>资源数（人）</label><select id="resources"></select></div>
    <div class="field"><label>排期开始日</label><input id="start" type="date"></div>
  </div>
  <div class="formula" id="formula"></div>
  <div class="actions">
    <button class="primary" id="fit">切到 6 人 · 满足窗口</button>
    <button id="reset">重置默认</button>
    <span id="status"></span>
  </div>
</section>

<div class="ctrl">
  <button class="on" data-pri="all">全部</button><button data-pri="P0">P0</button><button data-pri="P1">P1</button><button data-pri="P2">P2</button>
  <input id="q" placeholder="搜索货号 / 系列…">
  <span class="legend"><span class="dot" style="background:#c0392b"></span>P0 <span class="dot" style="background:#e67e22"></span>P1 <span class="dot" style="background:#2e86c1"></span>P2 ｜ 实色=基准，斜纹=30%容错</span>
</div>

<div class="board"><div class="scroll"><div class="grid2" id="board"></div></div></div>

<section class="six">
  <h2>6D 任务拆解</h2>
  <div class="hint">只列“基准=6D”的任务；每项都是 2D+2D+2D 的组合，再按容错率加缓冲。</div>
  <div id="sixd"></div>
</section>

<section class="notes">
  <b>口径：</b>基准 = 2×缺失核心项 + 2×无资料建档 + 2×DG专项 + 2×NA专项；计划 = ceil(基准×(1+容错率))。
  每个任务由 1 名资源独占；P0→P1→P2，同优先级按 2026 销售额降序；只排周一至周五，跳过 2027-01-01。
  <br><b>注意：</b>按 30% 容错、5 名资源，最晚约 2027-02-15，会有任务超出 2027-01-31；切到 6 名资源可落窗（约 2027-01-22）。
  本页是管理估算，不含实验排队、样品到位、外部检测周期；资源为占位符。
</section>
</div>
<script>
const TASKS=__TASKS__;
const DEFAULTS=__DEFAULTS__;
const SLOT=9;
const HOLIDAY='2027-01-01';
const RESOURCE_NAMES=['研发-占位A','研发-占位B','研发-占位C','产品-占位D','产品-占位E','研发-占位F','产品-占位G','资源-占位H'];
const PRI_ORDER={P0:0,P1:1,P2:2};
let filterPri='all', query='';

function parseDate(s){const [y,m,d]=s.split('-').map(Number);return new Date(y,m-1,d);}
function fmt(d){return d.getFullYear()+'-'+String(d.getMonth()+1).padStart(2,'0')+'-'+String(d.getDate()).padStart(2,'0');}
function addDays(d,n){const x=new Date(d);x.setDate(x.getDate()+n);return x;}
function isWD(d){return d.getDay()!==0 && d.getDay()!==6 && fmt(d)!==HOLIDAY;}
function workdays(start,count){const out=[];let d=new Date(start);while(out.length<count){if(isWD(d))out.push(new Date(d));d=addDays(d,1);}return out;}
function money(n){return Number(n).toLocaleString('zh-CN',{maximumFractionDigits:0});}
function fmtNum(n){return Number(n).toLocaleString('zh-CN',{maximumFractionDigits:1});}
function clampNum(id,min,max,def){let v=parseFloat(document.getElementById(id).value);if(!isFinite(v))v=def;return Math.max(min,Math.min(max,v));}

function params(){
  return {core:clampNum('core',0,20,DEFAULTS.core),archive:clampNum('archive',0,20,DEFAULTS.archive),
    dg:clampNum('dg',0,20,DEFAULTS.dg),na:clampNum('na',0,20,DEFAULTS.na),
    buffer:clampNum('buffer',0,100,DEFAULTS.buffer),
    resources:parseInt(document.getElementById('resources').value,10)||DEFAULTS.resources,
    start:document.getElementById('start').value||DEFAULTS.start};
}
function calc(t,p){
  const core=p.core*t.n_missing, archive=t.docs===0?p.archive:0;
  const dg=t.has_dg?p.dg:0, na=t.has_na?p.na:0;
  const base=core+archive+dg+na;
  const bufRaw=base*p.buffer/100;
  const buffer=Math.ceil(bufRaw-1e-9);
  const total=Math.ceil(base+bufRaw-1e-9);
  return {core,archive,dg,na,base,buffer,total,base_formula:`${p.core}×${t.n_missing} + ${p.archive}×${t.docs===0?1:0} + ${p.dg}×${t.has_dg} + ${p.na}×${t.has_na}`};
}
function schedule(p){
  const cal=workdays(parseDate(p.start),900);
  const res=RESOURCE_NAMES.slice(0,p.resources);
  const free=Object.fromEntries(res.map(r=>[r,0]));
  const rows=[...TASKS].sort((a,b)=>(PRI_ORDER[a.priority]-PRI_ORDER[b.priority])||(b.sales-a.sales));
  const out=[];
  for(const t of rows){
    const c=calc(t,p);
    let owner=res[0];
    for(const r of res){if(free[r]<free[owner]||(free[r]===free[owner]&&res.indexOf(r)<res.indexOf(owner)))owner=r;}
    const s=free[owner], e=s+c.total-1;
    out.push({...t,...c,owner,start:fmt(cal[s]),end:fmt(cal[e]),s,e});
    free[owner]=e+1;
  }
  const last=out.reduce((a,b)=>a.end>b.end?a:b).end;
  return {rows:out,cal,last};
}
function render(){
  const p=params(), sch=schedule(p);
  const base=sch.rows.reduce((a,x)=>a+x.base,0);
  const buffer=sch.rows.reduce((a,x)=>a+x.buffer,0);
  const total=sch.rows.reduce((a,x)=>a+x.total,0);
  const over=sch.rows.filter(x=>x.end>DEFAULTS.window_end).length;
  const bufferPct=base?buffer/base*100:0;
  document.getElementById('kpis').innerHTML=[
    `<div class="kpi"><b>${sch.rows.length}</b><span>任务数（P0 ${sch.rows.filter(x=>x.priority==='P0').length} / P1 ${sch.rows.filter(x=>x.priority==='P1').length} / P2 ${sch.rows.filter(x=>x.priority==='P2').length}）</span></div>`,
    `<div class="kpi"><b>${base.toFixed(0)}</b><span>基准人日（拆解合计）</span></div>`,
    `<div class="kpi"><b>${buffer}</b><span>容错人日（实际 ${bufferPct.toFixed(1)}%）</span></div>`,
    `<div class="kpi"><b>${total}</b><span>计划人日（基准+容错）</span></div>`,
    `<div class="kpi ${over?'warn':'ok'}"><b>${sch.last.slice(5)}</b><span>最晚完成</span></div>`,
    `<div class="kpi ${over?'warn':'ok'}"><b>${over?'超窗 '+over+' 项':'窗口内'}</b><span>目标 2027-01-31</span></div>`
  ].join('');
  document.getElementById('formula').innerHTML=`基准 = <b>${p.core}×缺失项 + ${p.archive}×无资料建档 + ${p.dg}×DG + ${p.na}×NA</b>　｜　计划 = <b>ceil(基准 × (1+${p.buffer}%))</b>　｜　当前 ${sch.rows.length} 项，基准 ${base.toFixed(0)}D，容错 ${buffer}D，计划 ${total}D`;
  const st=document.getElementById('status');
  if(over){st.style.color='#c0392b';st.textContent=`5人/当前 ${p.resources} 人方案：最晚 ${sch.last}，${over} 项超出 2027-01-31；可点“切到 6 人”`;}
  else{st.style.color='#2e8b57';st.textContent=`当前 ${p.resources} 人方案：最晚 ${sch.last}，在 2027-01-31 窗口内`;}
  renderBoard(sch);
  renderSix(sch);
}
function visibleRows(sch){
  return sch.rows.filter(x=>(filterPri==='all'||x.priority===filterPri)&&(!query||((x.rep+' '+x.series).toLowerCase().includes(query))));
}
function renderBoard(sch){
  const maxIdx=Math.max(...sch.rows.map(x=>x.e));
  const days=sch.cal.slice(0,maxIdx+1);
  const months=[]; days.forEach((d,i)=>{const k=d.getFullYear()+'-'+(d.getMonth()+1);let m=months.find(x=>x.k===k);if(!m){m={k,label:`${d.getFullYear()}年${d.getMonth()+1}月`,n:0};months.push(m);}m.n++;});
  let html='<div class="months"><div style="width:360px;flex:none;border-right:1px solid #e3ecf4"></div>';
  months.forEach(m=>{html+=`<div class="mh" style="width:${m.n*SLOT}px">${m.label}</div>`;});
  html+='</div><div class="timeline">';
  days.forEach((d,i)=>{const isMon=d.getDay()===1;if(isMon||i===0)html+=`<div class="tw ${isMon?'wk':''}" style="left:${i*SLOT}px">${d.getDate()}</div>`;});
  html+='</div>';
  const rows=visibleRows(sch);
  if(!rows.length)html+='<div class="empty">没有匹配任务</div>';
  rows.forEach(x=>{
    const baseW=x.base*SLOT, bufW=x.buffer*SLOT;
    html+=`<div class="row"><div class="task"><div class="tname"><span class="pri p${x.priority}">${x.priority}</span>${x.rep}<span class="tser">${x.series}</span></div>
      <div class="tmeta">${x.owner} · 基准${fmtNum(x.base)}D + 容错${x.buffer}D = ${x.total}D · ${x.start.slice(5)}→${x.end.slice(5)} · ${x.sales}万 · ${x.customers}家</div></div>
      <div class="lane" style="min-width:${days.length*SLOT}px"><div class="bar p${x.priority}" style="left:${x.s*SLOT}px;width:${x.total*SLOT-2}px" title="${x.rep}｜${x.series}\n${x.base_formula} = ${fmtNum(x.base)}D；+${x.buffer}D容错 = ${x.total}D\n${x.owner}｜${x.start} → ${x.end}">
      <div class="base" style="width:${baseW}px">${fmtNum(x.base)}D</div><div class="buf" style="width:${bufW}px"></div><span class="lab">${x.total}D</span></div></div></div>`;
  });
  document.getElementById('board').innerHTML=html;
}
function renderSix(sch){
  const six=sch.rows.filter(x=>Math.abs(x.base-6)<1e-9);
  if(!six.length){document.getElementById('sixd').innerHTML='<div class="empty">当前参数下没有基准=6D的任务</div>';return;}
  let html='';
  six.forEach(x=>{
    const parts=[];if(x.core)parts.push(`核心${x.n_missing}项×${x.core/x.n_missing}=${fmtNum(x.core)}D`);if(x.archive)parts.push(`建档${fmtNum(x.archive)}D`);if(x.dg)parts.push(`DG${fmtNum(x.dg)}D`);if(x.na)parts.push(`NA${fmtNum(x.na)}D`);
    html+=`<div class="sixrow"><b>${x.id} ${x.rep}</b><span class="tag">${x.series}</span>${parts.join('<span class="plus"> + </span>')}<span class="plus"> = </span><b>6D</b><span class="tag">+容错 ${x.buffer}D</span><span class="plus"> → </span><b>${x.total}D</b><span style="color:#8aa1b6">${x.start.slice(5)}→${x.end.slice(5)}</span></div>`;
  });
  document.getElementById('sixd').innerHTML=html;
}
function init(){
  document.getElementById('core').value=DEFAULTS.core;document.getElementById('archive').value=DEFAULTS.archive;
  document.getElementById('dg').value=DEFAULTS.dg;document.getElementById('na').value=DEFAULTS.na;
  document.getElementById('buffer').value=DEFAULTS.buffer;document.getElementById('start').value=DEFAULTS.start;
  const sel=document.getElementById('resources');for(let i=1;i<=8;i++){const o=document.createElement('option');o.value=i;o.textContent=i+' 人';sel.appendChild(o);}sel.value=DEFAULTS.resources;
  document.querySelectorAll('.logic input,.logic select').forEach(el=>el.addEventListener('input',render));
  document.querySelectorAll('.ctrl button').forEach(b=>b.addEventListener('click',()=>{document.querySelectorAll('.ctrl button').forEach(x=>x.classList.remove('on'));b.classList.add('on');filterPri=b.dataset.pri;render();}));
  document.getElementById('q').addEventListener('input',e=>{query=e.target.value.trim().toLowerCase();render();});
  document.getElementById('reset').addEventListener('click',()=>{document.getElementById('core').value=DEFAULTS.core;document.getElementById('archive').value=DEFAULTS.archive;document.getElementById('dg').value=DEFAULTS.dg;document.getElementById('na').value=DEFAULTS.na;document.getElementById('buffer').value=DEFAULTS.buffer;document.getElementById('resources').value=DEFAULTS.resources;document.getElementById('start').value=DEFAULTS.start;render();});
  document.getElementById('fit').addEventListener('click',()=>{document.getElementById('resources').value=6;render();});
  render();
}
init();
</script>
</body></html>'''
    html = html.replace('__TASKS__', data).replace('__DEFAULTS__', defaults).replace('__N__', '620')
    html = BK.inject(html, theme='light', tag='营销中心 · 产品资料')
    open(OUT_HTML, 'w', encoding='utf-8').write(html)


if __name__ == '__main__':
    tasks = load_tasks()
    p = dict(DEFAULTS)
    sched5, last5, over5 = build_xlsx(tasks, p)
    build_html(tasks, p)
    print('tasks', len(tasks), 'base', sum(x['base_days'] for x in sched5), 'buffer', sum(x['buffer_days'] for x in sched5), 'plan', sum(x['total_days'] for x in sched5))
    print('5人最晚', last5, '超窗', over5)
    print('xlsx', OUT_XLSX)
    print('html', OUT_HTML)