#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""西安佰奥莱博 · 客户360一页纸（Word 版，A4 单页）
口径：仅西安佰奥莱博单一主体；不含任何其他主体的人、项目、客诉。
"""
import datetime as dt
import json
import os
import re

from docx import Document
from docx.enum.table import WD_TABLE_ALIGNMENT
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.oxml import OxmlElement
from docx.oxml.ns import qn
from docx.shared import Cm, Pt, RGBColor

BASE = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
OUT = BASE + '/销售分析/客户360_西安佰奥莱博_2026-10-21.docx'
LOGO = os.path.expanduser('~/work/公司资料/logo_transparent.png')
CHART = BASE + '/销售分析/_chart_baiaolab_xian.png'
SLOGAN = '守护生命健康，缔造美好生活'
BLUE = RGBColor(0x1F, 0x4E, 0x79)
GREY = RGBColor(0x6B, 0x7C, 0x8F)
RED = RGBColor(0xC0, 0x39, 0x2B)

P = json.load(open(BASE + '/数据/baiaolab_xian_3y.json', encoding='utf-8'))
Y24, Y25, Y26 = P['2024'], P['2025'], P['2026']
AR = P['ar']
AR_TOT = sum(float(r['amt']) for r in AR)
AR_OD = sum(float(r.get('od_amt') or 0) for r in AR)
AR_MAXD = max([int(r['od_days']) for r in AR if r.get('od_days')] or [0])
COMPS = sorted(P['complaints'], key=lambda c: c.get('created', ''))
OPEN = [c for c in COMPS if c.get('status') != '结束']
TODAY = dt.date(2026, 10, 21)
TOT3 = Y24['total'] + Y25['total'] + Y26['total']
J25 = sum(v for k, v in Y25['by_month'].items() if k <= '2025-10')
YOY = (Y26['total'] / J25 - 1) * 100
G25 = (Y25['total'] / Y24['total'] - 1) * 100
OPEN_DAYS = (TODAY - dt.date.fromisoformat(OPEN[0]['created'])).days if OPEN else 0


def wan(x):
    return '%.1f' % (x / 1e4)


def set_font(run, size=9, bold=False, color=None, name='微软雅黑'):
    run.font.size = Pt(size)
    run.font.bold = bold
    run.font.name = name
    run._element.rPr.rFonts.set(qn('w:eastAsia'), name)
    if color is not None:
        run.font.color.rgb = color


def shade(cell, hexcolor):
    el = OxmlElement('w:shd')
    el.set(qn('w:val'), 'clear')
    el.set(qn('w:color'), 'auto')
    el.set(qn('w:fill'), hexcolor)
    cell._tc.get_or_add_tcPr().append(el)


def cell(c, text, size=8.5, bold=False, color=None, align=WD_ALIGN_PARAGRAPH.LEFT):
    c.text = ''
    p = c.paragraphs[0]
    p.alignment = align
    p.paragraph_format.space_before = Pt(1)
    p.paragraph_format.space_after = Pt(1)
    p.paragraph_format.line_spacing = 1.0
    set_font(p.add_run(str(text)), size, bold, color)


def table(doc, rows, cols, widths, head=True):
    t = doc.add_table(rows=1, cols=cols)
    t.alignment = WD_TABLE_ALIGNMENT.CENTER
    t.autofit = False
    for j in range(cols):
        t.rows[0].cells[j].width = Cm(widths[j])
    b = OxmlElement('w:tblBorders')
    for e in ('top', 'left', 'bottom', 'right', 'insideH', 'insideV'):
        x = OxmlElement('w:%s' % e)
        x.set(qn('w:val'), 'single'); x.set(qn('w:sz'), '4'); x.set(qn('w:color'), 'DCE3EC')
        b.append(x)
    t._tbl.tblPr.append(b)
    return t


def h(doc, text):
    p = doc.add_paragraph()
    p.paragraph_format.space_before = Pt(5)
    p.paragraph_format.space_after = Pt(2)
    set_font(p.add_run(text), 9.5, True, BLUE)


def line(doc, text, size=8.3, color=None, bold=False, indent=0, space=1):
    p = doc.add_paragraph()
    p.paragraph_format.space_before = Pt(0)
    p.paragraph_format.space_after = Pt(space)
    p.paragraph_format.line_spacing = 1.1
    if indent:
        p.paragraph_format.left_indent = Cm(indent)
    set_font(p.add_run(text), size, bold, color)
    return p


doc = Document()
s = doc.sections[0]
s.page_width, s.page_height = Cm(21), Cm(29.7)
s.top_margin, s.bottom_margin = Cm(0.9), Cm(0.9)
s.left_margin, s.right_margin = Cm(1.2), Cm(1.2)

hp = s.header.paragraphs[0]
if os.path.exists(LOGO):
    hp.add_run().add_picture(LOGO, height=Cm(0.5))
set_font(hp.add_run('    客户 360 卡 · 内部资料 · 2026-10-21'), 7, False, GREY)
hp.paragraph_format.tab_stops.add_tab_stop(Cm(18.4), 2)

fp = s.footer.paragraphs[0]
fp.alignment = WD_ALIGN_PARAGRAPH.CENTER
set_font(fp.add_run(SLOGAN + '　｜　仅西安佰奥莱博单一主体　｜　含客户与金额信息，禁止对外'), 6.5, False, GREY)

p = doc.add_paragraph()
p.paragraph_format.space_after = Pt(0)
set_font(p.add_run('西安佰奥莱博生物科技有限公司'), 15, True, BLUE)
p = doc.add_paragraph()
p.paragraph_format.space_after = Pt(3)
set_font(p.add_run('诊断原料 · 大客户销售部　｜　A 类　｜　销售：刘子研　｜　可冻干预混液（兽药 GMP）　｜　数据截止 2026-10-20'), 7.4, False, GREY)

# KPI
kpi = doc.add_table(rows=2, cols=6)
kpi.autofit = False
vals = [('三年累计', wan(TOT3), '万', '2024–2026.10', False),
        ('2025 全年', wan(Y25['total']), '万', '+%.1f%%' % G25, False),
        ('2026 年 1–10 月', wan(Y26['total']), '万', '%.1f%%' % YOY, False),
        ('在采货号', str(Y26['sku_count']), '个', '2025 年 %d 个' % Y25['sku_count'], False),
        ('应收未收', wan(AR_TOT), '万', '逾期 %s 万 · %d 天' % (wan(AR_OD), AR_MAXD), True),
        ('客诉未闭环', str(len(OPEN)), '条', '已挂 %d 天' % OPEN_DAYS, True)]
for j in range(6):
    top = kpi.rows[0].cells[j]
    bot = kpi.rows[1].cells[j]
    top.width = Cm(3.06); bot.width = Cm(3.06)
    cell(top, vals[j][0], 6.8, False, GREY)
    bot.paragraphs[0].text = ''
    pp = bot.paragraphs[0]
    pp.paragraph_format.space_after = Pt(0)
    set_font(pp.add_run(vals[j][1]), 11.5, True, RED if vals[j][4] else RGBColor(0x12, 0x32, 0x4F))
    set_font(pp.add_run(' ' + vals[j][2]), 6.8, False, GREY)
    set_font(pp.add_run('　' + vals[j][3]), 6.2, False, RED if vals[j][4] else GREY)
    shade(top, 'F4F7FB'); shade(bot, 'F4F7FB')

# 一、销售趋势
h(doc, '一、销售趋势：先翻倍，再腰斩')
line(doc, '2024 年 %s 万 → 2025 年 %s 万（+%.1f%%）→ 2026 年 1–10 月 %s 万（同比 %.1f%%）。2025 年是靠 FMD5084-000901、FMD2011 两个直扩货号冲起来的；今年两个货号同步回落，8、9 月只有测试没有成交，10 月才恢复 2.6 万。' % (
    wan(Y24['total']), wan(Y25['total']), G25, wan(Y26['total']), YOY), 8.3)
if os.path.exists(CHART):
    doc.add_picture(CHART, width=Cm(16.0))
    doc.paragraphs[-1].alignment = WD_ALIGN_PARAGRAPH.CENTER
    doc.paragraphs[-1].paragraph_format.space_after = Pt(0)

# 二、主销货号
h(doc, '二、主销货号：从常规 RT 预混切到直扩冻干')


def sm(y):
    return {x['sku']: x['amt'] for x in y['top_sku']}


M24, M25, M26 = sm(Y24), sm(Y25), sm(Y26)
KEYS = []
for m in (M26, M25, M24):
    for k, v in sorted(m.items(), key=lambda kv: -kv[1]):
        if k not in KEYS:
            KEYS.append(k)
KEYS = KEYS[:8]
NAME = {x['sku']: x['name'] for y in (Y24, Y25, Y26) for x in y['top_sku']}


def clean(n):
    n = re.sub(r'\s*\(.*$', '', n or '')
    n = re.sub(r'^(2×|5×|20×|25×)', '', n).strip()
    return n.rstrip('-').strip()[:26]


t = table(doc, None, 5, [4.2, 7.6, 2.2, 2.2, 2.2])
for j, hd in enumerate(['货号', '产品', '2024（万元/年）', '2025（万元/年）', '2026（万元/年，1–10 月）']):
    cell(t.rows[0].cells[j], hd, 7.4, True, RGBColor(0xFF, 0xFF, 0xFF),
         WD_ALIGN_PARAGRAPH.RIGHT if j >= 2 else WD_ALIGN_PARAGRAPH.LEFT)
    shade(t.rows[0].cells[j], '1F4E79')
for k in KEYS:
    r = t.add_row().cells
    cell(r[0], k, 7.4)
    cell(r[1], clean(NAME.get(k, '')), 7.4)
    cell(r[2], wan(M24[k]) if k in M24 else '—', 7.4, align=WD_ALIGN_PARAGRAPH.RIGHT)
    cell(r[3], wan(M25[k]) if k in M25 else '—', 7.4, align=WD_ALIGN_PARAGRAPH.RIGHT)
    cell(r[4], wan(M26[k]) if k in M26 else '—', 7.4, align=WD_ALIGN_PARAGRAPH.RIGHT)
line(doc, '单位：万元。2024 年主力是 FM513（16.8 万，占 42%）；2025 年 FMD5084-000901 做到 36.4 万、FMD2011 26.3 万，两个直扩货号合计 57%；2026 年回落到 15.3 万和 9.2 万，FM5144-007701-TP 成了第三名。', 6.8, GREY, space=0)
doc.add_paragraph().paragraph_format.space_after = Pt(0)

# 三、客诉
h(doc, '三、客诉：5 条，4 条已关，1 条还挂着')
t = table(doc, None, 4, [2.6, 8.6, 4.0, 3.2])
for j, hd in enumerate(['日期', '问题', '状态', '天数']):
    cell(t.rows[0].cells[j], hd, 7.4, True, RGBColor(0xFF, 0xFF, 0xFF),
         WD_ALIGN_PARAGRAPH.RIGHT if j == 3 else WD_ALIGN_PARAGRAPH.LEFT)
    shade(t.rows[0].cells[j], '1F4E79')
for c in COMPS:
    days = (TODAY - dt.date.fromisoformat(c['created'])).days
    op = c.get('status') != '结束'
    r = t.add_row().cells
    cell(r[0], c['created'], 7.4)
    cell(r[1], str(c.get('name', '')).split('-', 2)[-1], 7.4)
    cell(r[2], ('3个月改善闭环跟进中' if c.get('status') == '大区确认中' else c.get('status', '')), 7.4, op, RED if op else None)
    cell(r[3], '%d 天' % days, 7.4, align=WD_ALIGN_PARAGRAPH.RIGHT)
line(doc, 'FM5144 一个货号占了 3 条（两次沉淀、一次漏液），是当前最需要盯的质量点；漏液那条 5 月就报了，现在还在3个月改善闭环跟进中。已关闭的按处理历时计。', 6.8, GREY, space=0)

# 四、项目 + 风险/下一步（两栏）
h(doc, '四、在测项目与卡点｜风险与下一步')
box = doc.add_table(rows=1, cols=2)
box.autofit = False
box.rows[0].cells[0].width = Cm(10.6)
box.rows[0].cells[1].width = Cm(7.8)
left, right = box.rows[0].cells

left.text = ''
for txt, col in [
    ('宠物真菌粪便直扩（卡住，需要优化酶在强粪便抑制背景下的性能）', RED),
    ('FM507 冻干后比液体滞后 1 个 Ct；新酶系 FM2181-4-020303、FMD5104-014429 滞后 2–3 个 Ct，配方没定（9-19、9-23、10-16 连续跟进）。', None),
    ('马 ACTB / FMD5084-000901（卡住）', RED),
    ('个别靶标 Ct 延迟约 3 个，加探针、翻倍镁离子都没解决 Rn 下降（9-23）。', None),
    ('二代 POC 平台（放缓）', RGBColor(0x5B, 0x7A, 0x52)),
    ('客户自评“项目定位错误、操作复杂”，不是重点，还在做但没方向（7-21）。', None),
    ('货号切换（已完成，配合兽药GMP已报证项目原料升级需求）', BLUE),
    ('FMD5084-000901 → FMD5084-007703-DB，FMD2011 由 2× 改 5×，7 月完成并按新货号下单。', None),
]:
    p = left.add_paragraph() if left.paragraphs[0].text else left.paragraphs[0]
    p.paragraph_format.space_before = Pt(1)
    p.paragraph_format.space_after = Pt(0)
    p.paragraph_format.line_spacing = 1.1
    set_font(p.add_run(txt), 7.6 if col else 7.3, bool(col), col)

right.text = ''
for txt, col, pre in [
    ('风险', RED, ''),
    ('应收 %s 万，逾期 %s 万，占今年销售额 %.0f%%' % (wan(AR_TOT), wan(AR_OD), AR_TOT / Y26['total'] * 100), None, '· '),
        ('下一步', BLUE, ''),
    ('① 逾期款逐票对账，锁付款计划｜刘子研 · 10 月底', None, ''),
    ('② FM5144-007701 漏液闭环｜质量部 · 立刻', None, ''),
    ('③ Q4 邀约明年业绩计划，锁最低采购量｜刘新元、刘子研 · 11 月中', None, ''),
    ('④ 两个在测项目出配方结论｜技术支持 · 10 月底', None, ''),
]:
    p = right.add_paragraph() if right.paragraphs[0].text else right.paragraphs[0]
    p.paragraph_format.space_before = Pt(1)
    p.paragraph_format.space_after = Pt(0)
    p.paragraph_format.line_spacing = 1.1
    if col:
        set_font(p.add_run(txt), 7.8, True, col)
    else:
        set_font(p.add_run(pre + txt), 7.2)

doc.save(OUT)
print('SAVED', OUT, os.path.getsize(OUT))