#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
大客户1 / 大客户2 客户清单 · 分派决策表 生成器
================================================================
源（均为真实数据，不估算）：
  ① 岗位↔客户归属：数据/客户区域销售对应清单_诊断.csv（704 行，客户/区域/部门/销售员）
  ② 客户主体主数据：数据/pricing/_key_customers.json（集团 group + 累计 total）
  ③ 金额口径：ERP （find_latest_erp 选中的最新全年件）（客户+销售员+价税合计，排除赠品行）
输出：
  工具/大客户1-2客户清单_分派表.html   ← 自包含、可编辑、自动保存、可导出（排序/筛选/搜索）
  销售分析/大客户1-2客户清单_20260920.xlsx
"""
import os, io, re, csv, json, base64, subprocess
import pandas as pd
from collections import defaultdict

HERE = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
CSV_F = os.path.join(HERE, '数据/客户区域销售对应清单_诊断.csv')
KC_F = os.path.join(HERE, '数据/pricing/_key_customers.json')
import sys as _sys
_sys.path.insert(0, '/Users/liuxinyuan/Desktop/Hermes输出-工作类/脚本')
import erp_sales_core as _esc
ERP_F = _esc.find_latest_erp()   # 铁律二：禁止硬编码 ERP 文件名
print('  现用 ERP（find_latest_erp）:', ERP_F)
LOGO = '/Users/liuxinyuan/work/公司资料/logo_transparent.png'
OUT_HTML = os.path.join(HERE, '工具/大客户1-2客户清单_分派表.html')
OUT_XLSX = os.path.join(HERE, '销售分析/大客户1-2客户清单_20260920.xlsx')

import sys
sys.path.insert(0, '/Users/liuxinyuan/.hermes/skills/productivity/editable-html-deliverables/templates')
from html_editable_layer import apply as apply_edit  # noqa

def b64(p):
    with open(p, 'rb') as f:
        return 'data:image/png;base64,%s' % base64.b64encode(f.read()).decode()

# ── ① 岗位 → 集团（权威清单）──
rows = list(csv.DictReader(io.open(CSV_F, encoding='utf-8-sig')))
KA = {c: [r['客户'] for r in rows if r['区域'] == c] for c in ('大客户1', '大客户2')}

# ── ② 主体主数据 ──
kc = json.load(io.open(KC_F, encoding='utf-8'))
by_grp = defaultdict(list); by_name = {c['name'].strip(): c for c in kc}
for c in kc:
    by_grp[(c.get('group') or '').strip()].append(c)

# ── ③ ERP 2026（至 9/18，排除赠品）──
df = pd.read_excel(ERP_F, dtype=str)
df['金额'] = pd.to_numeric(df['价税合计'], errors='coerce').fillna(0)
df['客户'] = df['客户'].astype(str).str.strip()
paid = df[~df['是否赠品'].astype(str).str.contains('是|Y|赠', na=False)]
# 当前归属（改派后）：_cust_sp 优先，_key_customers 兜底
_cs = json.load(io.open(os.path.join(HERE, '数据/pricing/_cust_sp.json'), encoding='utf-8'))
_kk = {c['name']: (c.get('salesperson') or c.get('sales') or '') for c in kc}
OWNER = lambda nm: _cs.get(nm) or _kk.get(nm) or ''

erp = paid.groupby('客户').agg(amt=('金额', 'sum'), orders=('单据编号', 'nunique'),
                              last=('合同日期', 'max'),
                              sp=('销售员', lambda s: '、'.join(sorted(set(map(str, s)))[:2]))).to_dict('index')

records = []
for code, groups in KA.items():
    seen = set()
    for g in groups:
        mem = by_grp.get(g, []) or ([by_name[g]] if g in by_name else [])
        if not mem:
            mem = [{'name': g, 'total': 0, 'sales_2026': 0, 'orders': 0, 'salesperson': ''}]
        for m in mem:
            nm = m['name'].strip()
            if (code, nm) in seen:
                continue
            seen.add((code, nm))
            e = erp.get(nm, {})
            records.append(dict(
                code=code, group=g, entity=nm,
                hist=round((m.get('total') or 0) / 1e4, 1),
                y2026=round(e.get('amt', 0) / 1e4, 1) if e else round((m.get('sales_2026') or 0) / 1e4, 1),
                orders=int(e.get('orders', 0) or (m.get('orders') or 0)),
                last=str(e.get('last', '') or m.get('latest_date') or '')[:10],
                sp=str(e.get('sp', '') or m.get('salesperson') or '') or '—',
                owner=OWNER(nm)))

order = {'大客户1': 0, '大客户2': 1}
for r in records:
    r['active'] = 1 if r['y2026'] > 0 else 0
records.sort(key=lambda r: (order[r['code']], -r['y2026'], -r['hist']))

# ── xlsx 交付 ──
xdf = pd.DataFrame([{
    '岗位': r['code'], '集团/客户归口': r['group'], '客户主体': r['entity'],
    '2026年(至9/18·万)': r['y2026'], '历史累计(万)': r['hist'], '订单数': r['orders'],
    '最近下单': r['last'] or '—', '当前销售员': r['sp'], '当前归属（已落地）': r.get('owner',''), '是否存在2026交易': '是' if r['active'] else '否',
    '如需再调整，填此列': ''} for r in records])
xdf.to_excel(OUT_XLSX, index=False)

# ── HTML ──
def esc(s):
    return str(s).replace('&', '&amp;').replace('<', '&lt;').replace('>', '&gt;').replace('"', '&quot;')

def money(v, big=False):
    cls = 'num' + (' big' if big else '')
    return '<span class="%s">%s</span>' % (cls, format(v, ',.1f'))

trs = []
for r in records:
    trs.append(
        '<tr data-code="%s" data-group="%s" data-act="%d" data-y="%s" data-h="%s" data-o="%s" data-l="%s" data-n="%s">'
        '<td class="c"><span class="tag tag-%s">%s</span></td>'
        '<td class="g">%s</td>'
        '<td class="e">%s</td>'
        '<td>%s</td><td>%s</td><td class="c">%s</td><td class="c">%s</td><td class="c sp">%s</td>'
        '<td class="asg" title="点此修改归属">%s</td></tr>' % (
            r['code'], esc(r['group']), r['active'], r['y2026'], r['hist'], r['orders'], r['last'] or '—', esc(r['entity']),
            '1' if r['code'] == '大客户1' else '2', r['code'],
            esc(r['group']), esc(r['entity']),
            money(r['y2026']), money(r['hist']),
            r['orders'], r['last'] or '—', esc(r['sp']), esc(r.get('owner') or '')))

stat = {}
for code in ('大客户1', '大客户2'):
    rs = [r for r in records if r['code'] == code]
    stat[code] = dict(groups=len(set(r['group'] for r in rs)), ents=len(rs),
                      y=sum(r['y2026'] for r in rs), h=sum(r['hist'] for r in rs),
                      act=sum(1 for r in rs if r['active']), idle=sum(1 for r in rs if not r['active']))

CSS = """
*{margin:0;padding:0;box-sizing:border-box}
body{font-family:"PingFang SC","Microsoft YaHei",sans-serif;background:#f0f2f5;color:#1f2d3d;padding-bottom:132px}
.page{width:100%;max-width:1500px;margin:0 auto;background:#f0f2f5}
.topbar{background:#fff;border-bottom:2.5px solid #0a3d62;padding:12px 26px;display:flex;align-items:center;justify-content:space-between;position:sticky;top:0;z-index:20}
.topbar img{height:30px}
.topbar .tt{font-size:17px;font-weight:800;color:#0a3d62;margin-left:14px}
.topbar .tt small{display:block;font-weight:500;color:#7a8b9c;font-size:11px;margin-top:2px}
.hero{background:linear-gradient(135deg,#0a3d62,#0060c0 55%,#1e8fa8);color:#fff;padding:16px 26px 18px}
.hero h1{font-size:20px;font-weight:900}
.hero .note{font-size:12px;opacity:.92;margin-top:6px;line-height:1.6}
.cards{display:grid;grid-template-columns:repeat(2,1fr);gap:14px;padding:16px 26px 0}
.card{background:#fff;border-radius:10px;border-left:5px solid #0060c0;padding:14px 16px;box-shadow:0 1px 4px rgba(10,61,98,.07)}
.card.k2{border-left-color:#1e8fa8}
.card h3{font-size:15px;color:#0a3d62;margin-bottom:10px}
.card .row{display:flex;gap:22px;flex-wrap:wrap}
.card .kv{font-size:12px;color:#5c6b7a}
.card .kv b{display:block;font-size:19px;color:#0a3d62;font-weight:800;margin-top:2px}
.card .kv.warn b{color:#c0392b}
.tools{display:flex;gap:10px;align-items:center;flex-wrap:wrap;padding:14px 26px 10px}
.tools input[type=text]{padding:8px 12px;border:1px solid #d8e2ec;border-radius:8px;font-size:13px;min-width:240px;outline:none}
.tools input[type=text]:focus{border-color:#0060c0}
.btn{padding:8px 14px;border:1px solid #d8e2ec;background:#fff;border-radius:8px;font-size:13px;cursor:pointer;color:#33506b}
.btn:hover{border-color:#0060c0;color:#0060c0}
.btn.on{background:#0060c0;border-color:#0060c0;color:#fff}
.wrap{background:#fff;margin:6px 26px 22px;border-radius:10px;overflow:auto;box-shadow:0 1px 4px rgba(10,61,98,.07)}
table{border-collapse:collapse;width:100%;font-size:12.5px}
th{background:#0a3d62;color:#fff;font-weight:700;text-align:left;padding:10px 10px;position:sticky;top:0;white-space:nowrap;cursor:pointer;user-select:none}
th.no{cursor:default}
th .ar{opacity:.5;font-size:10px;margin-left:4px}
td{padding:8px 10px;border-bottom:1px solid #eef3f8;vertical-align:middle}
tr:hover td{background:#f7fbfd}
tr.hide{display:none}
td.c,th.c{text-align:center}
td.e{max-width:300px}
.num{font-variant-numeric:tabular-nums;font-weight:700;color:#0060c0}
.num.big{color:#0a3d62}
.tag{display:inline-block;padding:2px 8px;border-radius:999px;font-size:11px;font-weight:700}
.tag-1{background:#e8f2ff;color:#0060c0;border:1px solid #bcd9f7}
.tag-2{background:#e6f7f9;color:#12809a;border:1px solid #b3e2ea}
.g{color:#0a3d62;font-weight:700}
.e{color:#33506b}
.sp{color:#7a8b9c;font-size:11.5px}
td.asg{min-width:130px;background:#fffdf3;border-left:2px solid #f0d98a;color:#8a6d1f;font-weight:700;text-align:center}
td.asg:empty::before{content:'点此填写';color:#c9bc93;font-weight:500}
.foot{color:#7a8b9c;font-size:11.5px;padding:0 26px 26px;line-height:1.7}
.foot b{color:#0a3d62}
mark{background:#fff2a8;padding:0 2px;border-radius:3px}
/* 覆盖可编辑层默认工具栏位置：从右下(压住拟分派人列)改为左下横排 */
body .editbar{right:auto;left:26px;bottom:16px;flex-direction:row;align-items:center;padding:7px 9px;gap:6px}
body .editbar #saveState{border-bottom:none;border-right:1px solid #eef3f9;padding:0 8px 0 0;margin:0 4px 0 0;white-space:nowrap}
/* 「拟分派人」列横向滚动时常驻右侧 */
th.asg-h{position:sticky;right:0;z-index:4}
td.asg{position:sticky;right:0;z-index:3;background:#fffdf3}
"""

JS = """
/* ⚠️ 本页配合 editable-html-deliverables 图层：restore() 会整体替换 .page 的 innerHTML，
   因此 ① 所有事件必须用事件委托（绑在 document 上）；② 行节点必须每次实时查询，不能缓存。*/
var state = {code:'all', q:'', activeOnly:false, sortKey:null, dir:1};
function rows(){ return [].slice.call(document.querySelectorAll('tbody tr')); }
function txt(tr){ return tr.innerText.replace(/\s+/g,' '); }
function apply(){
  var shown=0, y=0, h=0;
  rows().forEach(function(tr){
    var ok = (state.code==='all' || tr.getAttribute('data-code')===state.code)
          && (!state.activeOnly || tr.getAttribute('data-act')==='1')
          && (!state.q || txt(tr).indexOf(state.q)>=0);
    tr.classList.toggle('hide', !ok);
    if(ok){ shown++; y += parseFloat(tr.getAttribute('data-y'))||0; h += parseFloat(tr.getAttribute('data-h'))||0; }
  });
  var e1=document.getElementById('shown'), e2=document.getElementById('sy'), e3=document.getElementById('sh');
  if(e1) e1.textContent=shown;
  if(e2) e2.textContent=y.toFixed(1);
  if(e3) e3.textContent=h.toFixed(1);
}
function sortBy(key){
  var numeric = (key==='data-y'||key==='data-h'||key==='data-o');
  if(state.sortKey===key){ state.dir=-state.dir; } else { state.sortKey=key; state.dir = numeric ? -1 : 1; }
  document.querySelectorAll('th .ar').forEach(function(a){ a.textContent=''; });
  var th=document.querySelector('th[data-k="'+key+'"] .ar');
  if(th) th.textContent = state.dir>0 ? '▲' : '▼';
  var tb=document.querySelector('tbody'); if(!tb) return;
  var list=rows().sort(function(a,b){
    var x=a.getAttribute(key)||'', z=b.getAttribute(key)||'';
    if(numeric){ x=parseFloat(x)||0; z=parseFloat(z)||0; }
    return (x>z?1:x<z?-1:0)*state.dir;
  });
  list.forEach(function(tr){ tb.appendChild(tr); });
}
function setCode(c){
  state.code=c;
  document.querySelectorAll('.btn[data-code]').forEach(function(x){ x.classList.toggle('on', x.getAttribute('data-code')===c); });
  apply();
}
/* 事件委托：容器 innerHTML 被替换后依然有效 */
document.addEventListener('click', function(e){
  var th=e.target.closest ? e.target.closest('th[data-k]') : null;
  if(th){ sortBy(th.getAttribute('data-k')); return; }
  var b=e.target.closest ? e.target.closest('.btn[data-code]') : null;
  if(b){ setCode(b.getAttribute('data-code')); return; }
  if(e.target.closest && e.target.closest('#activeOnly')){ state.activeOnly=!state.activeOnly;
    document.getElementById('activeOnly').classList.toggle('on', state.activeOnly); apply(); return; }
  if(e.target.closest && e.target.closest('#reset')){ state={code:'all',q:'',activeOnly:false,sortKey:null,dir:1};
    var q=document.getElementById('q'); if(q) q.value='';
    var ao=document.getElementById('activeOnly'); if(ao) ao.classList.remove('on');
    document.querySelectorAll('.btn[data-code]').forEach(function(x){ x.classList.toggle('on', x.getAttribute('data-code')==='all'); });
    document.querySelectorAll('th .ar').forEach(function(a){ a.textContent=''; }); apply(); return; }
});
document.addEventListener('input', function(e){
  if(e.target && e.target.id==='q'){ state.q=e.target.value.trim(); apply(); }
});
apply();
"""


HTML = """<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
<title>大客户1 / 大客户2 客户清单 · 分派决策表</title>
<style>__CSS__
__EDITCSS__</style>
<script>__EDITJS__</script>
</head>
<body>
__TOOLBAR__
<div class="page">
  <div class="topbar">
    <div style="display:flex;align-items:center">
      <img src="__LOGO__" alt="宝锐生物 BIORI BIOTECH">
      <div class="tt">大客户1 / 大客户2 客户清单 · 分派决策表<small>珠海宝锐生物科技有限公司 · 诊断原料业务线</small></div>
    </div>
    <div style="font-size:12px;color:#7a8b9c;text-align:right">归属口径：客户区域销售对应清单_诊断.csv<br>金额口径：ERP 至 2026-09-18（价税合计，不含赠品）</div>
  </div>

  <div class="hero">
    <h1>大客户1 与 大客户2 分别对应哪些客户</h1>
    <div class="note">大客户1/2 是<b>岗位代码</b>：<b>大客户1 = 刘子研</b>、<b>大客户2 = 赵云浩</b>（2026-09-20 裁定，已落地到 8 个归属文件）。本次调整：纳克智造·中道集团 → 赵云浩；艾康生物（杭州）→ 杨天雄（浙江）；陆军军医大学第一附属医院 → 刘子研；迈克生物 → 陈溪子（华西）；深圳锦瑞·朗瑞 → 林潇（华南2）；武汉千湖上品 → 赵云浩。
    下表按「集团 → 客户主体」展开，右侧<span style="background:#fffdf3;color:#8a6d1f;padding:0 6px;border-radius:4px">黄色「拟分派人」</span>列可直接点开填写，填完用工具栏导出即可回传给我。</div>
  </div>

  <div class="cards">
    <div class="card"><h3>大客户1 <span style="font-weight:600;color:#7a8b9c;font-size:12.5px">丽珠 · 天隆 · 华益美 · 佰奥莱博 · 海尔施 · 东方基因 等 10 个</span></h3>
      <div class="row">
        <div class="kv">集团/归口<b>__G1__ 个</b></div>
        <div class="kv">客户主体<b>__E1__ 个</b></div>
        <div class="kv">2026 至 9/18<b>__Y1__ 万</b></div>
        <div class="kv">历史累计<b>__H1__ 万</b></div>
        <div class="kv">有 2026 交易<b>__A1__ 个</b></div>
        <div class="kv warn">待激活<b>__I1__ 个主体</b></div>
      </div>
    </div>
    <div class="card k2"><h3>大客户2 <span style="font-weight:600;color:#7a8b9c;font-size:12.5px">圣湘 · 万孚 · 明日达 · 明德 · 安图 · 迈克 · 凯普 等 18 个</span></h3>
      <div class="row">
        <div class="kv">集团/归口<b>__G2__ 个</b></div>
        <div class="kv">客户主体<b>__E2__ 个</b></div>
        <div class="kv">2026 至 9/18<b>__Y2__ 万</b></div>
        <div class="kv">历史累计<b>__H2__ 万</b></div>
        <div class="kv">有 2026 交易<b>__A2__ 个</b></div>
        <div class="kv warn">待激活<b>__I2__ 个主体</b></div>
      </div>
    </div>
  </div>

  <div class="tools">
    <button class="btn on" data-code="all">全部</button>
    <button class="btn" data-code="大客户1">仅大客户1</button>
    <button class="btn" data-code="大客户2">仅大客户2</button>
    <input type="text" id="q" placeholder="搜索 集团 / 客户主体 / 销售员…">
    <button class="btn" id="activeOnly">仅看有 2026 交易</button>
    <button class="btn" id="reset">⟲ 重置</button>
    <span style="font-size:12.5px;color:#5c6b7a;margin-left:auto">当前显示 <b id="shown">0</b> 行 ｜ 合计 2026 <b id="sy">0</b> 万 ｜ 历史累计 <b id="sh">0</b> 万</span>
  </div>

  <div class="wrap">
    <table>
      <thead>
        <tr>
          <th class="c no">岗位</th>
          <th data-k="data-group" class="no">集团/归口<span class="ar"></span></th>
          <th data-k="data-n">客户主体<span class="ar"></span></th>
          <th data-k="data-y" class="c">2026至9/18（万）<span class="ar"></span></th>
          <th data-k="data-h" class="c">历史累计（万）<span class="ar"></span></th>
          <th data-k="data-o" class="c">订单数<span class="ar"></span></th>
          <th data-k="data-l" class="c">最近下单<span class="ar"></span></th>
          <th class="c no">当前销售员</th>
          <th class="c no asg-h">归属销售员（可编辑）</th>
        </tr>
      </thead>
      <tbody>
__ROWS__
      </tbody>
    </table>
  </div>

  <div class="foot">
    <b>数据来源</b>：① 岗位归属 = <code>数据/客户区域销售对应清单_诊断.csv</code>（704 行，客户/区域/部门/销售员）；② 客户主体与历史累计 = <code>数据/pricing/_key_customers.json</code>；③ 2026 金额 = ERP <code>（find_latest_erp 选中的最新全年件）</code>（按客户聚合价税合计，已排除赠品行）。<br>
    <b>口径提示</b>：历史累计为分客户统计（截至主数据快照）；「待激活」= 2026 年至今无付费交易。「当前销售员」为 ERP 近一年实际开单归属，可能已跨岗（如潮州凯普→黄文才、芯思微→林潇）。
  </div>
</div>
<script>__JS__</script>
</body>
</html>
"""

st = stat
html = (HTML.replace('__CSS__', CSS)
        .replace('__LOGO__', b64(LOGO))
        .replace('__ROWS__', '\n'.join(trs))
        .replace('__JS__', JS)
        .replace('__G1__', str(st['大客户1']['groups'])).replace('__E1__', str(st['大客户1']['ents']))
        .replace('__Y1__', format(st['大客户1']['y'], ',.1f')).replace('__H1__', format(st['大客户1']['h'], ',.1f'))
        .replace('__A1__', str(st['大客户1']['act'])).replace('__I1__', str(st['大客户1']['idle']))
        .replace('__G2__', str(st['大客户2']['groups'])).replace('__E2__', str(st['大客户2']['ents']))
        .replace('__Y2__', format(st['大客户2']['y'], ',.1f')).replace('__H2__', format(st['大客户2']['h'], ',.1f'))
        .replace('__A2__', str(st['大客户2']['act'])).replace('__I2__', str(st['大客户2']['idle'])))

html = apply_edit(html, ls_key='bior_ka_split_20260920', file_base='大客户1-2客户清单_分派表',
                  container='.page', hint_anchor='<div class="page">')
assert '__LSKEY__' not in html and '__EDITCSS__' not in html and '__EDITJS__' not in html, '可编辑层未注入'
# 工具栏被本页覆盖改到左下角 → 同步修正提示条文案，避免'右下角'与实际位置不符
html = html.replace('右下角', '左下角')
io.open(OUT_HTML, 'w', encoding='utf-8').write(html)

# JS 语法检查
js = '\n;\n'.join(re.findall(r'<script>(.*?)</script>', html, re.S))
io.open('/tmp/_ka_chk.js', 'w', encoding='utf-8').write(js)
r = subprocess.run(['node', '--check', '/tmp/_ka_chk.js'], capture_output=True, text=True)
print('  %s 内联 JS 语法' % ('✅' if r.returncode == 0 else '❌ ' + r.stderr[:400]))
print('  ✅ HTML %s（%.1f KB，自包含+可编辑）' % (os.path.basename(OUT_HTML), os.path.getsize(OUT_HTML) / 1024))
print('  ✅ XLSX %s' % os.path.basename(OUT_XLSX))
print('  行数：大客户1 %d 主体 / 大客户2 %d 主体' % (st['大客户1']['ents'], st['大客户2']['ents']))