import openpyxl, json, re, os
from collections import defaultdict
from datetime import datetime

outdir = "/Users/liuxinyuan/Desktop/Hermes输出-工作类/数据"
base = "/Users/liuxinyuan/Desktop/Hermes输出-工作类/销售分析/2026订单分析"

# ====== Group mappings from workbench ======
with open("/Users/liuxinyuan/Desktop/Hermes输出-工作类/工具/宝锐客户综合管理工作台.html") as f:
    content = f.read()
idx = content.find('var CL=')
entries = re.findall(r'\["([^"]+)","([^"]*)","([^"]*)"\]', content[idx:content.find('];',idx)+2])
cust_group = {}
for cust, sp, grp in entries:
    if cust: cust_group[cust] = grp if grp else cust
print(f"集团映射: {len(cust_group)}客户 -> {len(set(cust_group.values()))}集团")

# ====== Region & Department mappings ======
sp_region = {}
sp_dept = {}

# From 2026 file (Sheet1 has region col[5], sp col[6])
wb = openpyxl.load_workbook(base+"/2026年1-6月订单.xlsx", read_only=True, data_only=True)
ws = wb['Sheet1']
for row in ws.iter_rows(min_row=2, values_only=True):
    if len(row) <= 6: continue
    r = str(row[5] or '').strip()
    s = str(row[6] or '').strip()
    if r and s and s not in sp_region: sp_region[s] = r
wb.close()

# Region from ERP — try Sheet1, skip if column count too small
wb2 = openpyxl.load_workbook("/Users/liuxinyuan/work/secure/CRM及ERP数据/erp销售订单_2026032814241310_102319.xlsx", read_only=True, data_only=True)
ws2 = wb2['Sheet1']
for row in ws2.iter_rows(min_row=2, values_only=True):
    if len(row) <= 22: continue
    r = str(row[22] or '').strip()
    s = str(row[2] or '').strip()
    if r and s and s not in sp_region: sp_region[s] = r
wb2.close()
print(f"区域映射: {len(sp_region)}人")

# Department from KPI
with open(outdir+"/diag_kpis.json") as f:
    kpi = json.load(f)
sp_dept = {p['name']:p['dept'] for p in kpi if p['dept'] and p['dept']!='未分配'}
print(f"部门映射: {len(sp_dept)}人")

# Save mappings
with open(outdir+"/diag_mappings.json","w") as f:
    json.dump({"cust_to_group":cust_group,"sp_to_region":sp_region,"sp_to_dept":sp_dept},f,ensure_ascii=False)

# ====== 内部客户（宝锐生物、宝泰仪） — 剔除 ======
INTERNAL_CUSTS = {'珠海宝锐生物科技有限公司','珠海横琴宝锐生物科技有限公司','珠海宝泰仪生物科技有限公司'}
def is_internal(cust):
    return cust in INTERNAL_CUSTS

# ====== Rebuild data with Group + Region/Dept ======
custs = {}
sp_agg = defaultdict(lambda: {'amount':0,'orders':0,'customers':set(),'y2024':0,'y2025':0,'y2026':0})
monthly = defaultdict(float)

def add(cust, sp, amt, dt):
    if not cust or is_internal(cust): return
    grp = cust_group.get(cust, cust)
    y = str(dt.year)
    if grp not in custs:
        custs[grp] = {'amount':0,'orders':0,'first_date':None,'last_date':None,'salespeople':set(),'members':set(),'y2024':0,'y2025':0,'y2026':0}
    c = custs[grp]
    c['amount'] += amt; c['orders'] += 1; c['y'+y] += amt
    c['members'].add(cust)
    if not c['first_date'] or dt < c['first_date']: c['first_date'] = dt
    if not c['last_date'] or dt > c['last_date']: c['last_date'] = dt
    if sp: c['salespeople'].add(sp)
    monthly[dt.strftime('%Y-%m')] += amt
    if sp:
        sd = sp_agg[sp]
        sd['amount'] += amt; sd['orders'] += 1; sd['y'+y] += amt; sd['customers'].add(grp)

def proc(f, sheet_name, dt_c, tp_c, cust_c, sp_c, amt_c, cat_c, gift_c, month_range=None):
    wb = openpyxl.load_workbook(f, read_only=True, data_only=True)
    ws = wb[sheet_name]; n = 0
    for row in ws.iter_rows(min_row=2, values_only=True):
        if len(row) <= max(cat_c, amt_c, gift_c): continue
        if str(row[cat_c] or '').strip() != '核酸诊断': continue
        if '测试' in str(row[tp_c] or ''): continue
        if str(row[gift_c] or '') == '是': continue
        dt_val = row[dt_c]
        if isinstance(dt_val, str):
            # Parse YYYY-MM or YYYY-MM-DD
            try:
                parts = dt_val.strip().split('-')
                if len(parts) >= 2:
                    dt = datetime(int(parts[0]), int(parts[1]), 1)
                else:
                    continue
            except:
                continue
        elif isinstance(dt_val, datetime):
            dt = dt_val
        else:
            continue
        if month_range:
            mkey = dt.year*100 + dt.month
            if mkey < month_range[0] or mkey > month_range[1]: continue
        cust = str(row[cust_c] or '').strip()
        sp = str(row[sp_c] or '').strip()
        amt = float(row[amt_c] or 0)
        if cust: add(cust, sp, amt, dt); n += 1
    wb.close()
    return n

# 2024 Sheet1: dt=0, tp=2, cust=4, sp=5, amt=12, cat=26(产品大类), gift=14 — 全年
a = proc(base+'/2024全年订单.xlsx', 'Sheet1', 0,2,4,5,12,26,14, month_range=(202401,202412))
# 2025 Sheet1: dt=0(string YYYY-MM), tp=1, cust=3, sp=5, amt=12, cat=26, gift=14 — 全年
b = proc(base+'/2025全年订单.xlsx', 'Sheet1', 0,1,3,5,12,26,14)
# 2026 Sheet1: dt=0(string YYYY-MM), tp=1, cust=4, sp=6, amt=13, cat=25, gift=15 — 1-6月
c = proc(base+'/2026年1-6月订单.xlsx', 'Sheet1', 0,1,4,6,13,25,15)
print(f"处理: {a+b+c}行, {len(custs)}集团/客户, {len(sp_agg)}销售员")

# ====== Output ======
now=datetime.now()
cl=[{'name':n,'amount':round(c['amount'],2),'orders':c['orders'],
     'y2024':round(c['y2024'],2),'y2025':round(c['y2025'],2),'y2026':round(c['y2026'],2),
     'first_date':c['first_date'].strftime('%Y-%m-%d') if c['first_date'] else None,
     'last_date':c['last_date'].strftime('%Y-%m-%d') if c['last_date'] else None,
     'days_inactive':(now-c['last_date']).days if c['last_date'] else 999,
     'status':'active' if (now-c['last_date']).days<=90 else ('at_risk' if (now-c['last_date']).days<=180 else 'dormant') if c['last_date'] else 'dormant',
     'member_count':len(c['members']),'salespeople':list(c['salespeople'])} for n,c in custs.items()]
cl.sort(key=lambda x:x['amount'],reverse=True)

sl=[{'name':n,'amount':round(s['amount'],2),'orders':s['orders'],
     'y2024':round(s['y2024'],2),'y2025':round(s['y2025'],2),'y2026':round(s['y2026'],2),
     'customers':len(s['customers']),'region':sp_region.get(n,'未分配'),'dept':sp_dept.get(n,'未分配'),
     'avg_order':round(s['amount']/s['orders'],2) if s['orders']>0 else 0} for n,s in sp_agg.items()]
sl.sort(key=lambda x:x['amount'],reverse=True)

ms=[{'month':m,'amount':round(v,2)} for m,v in sorted(monthly.items())]
tr=sum(c['amount'] for c in cl)
ac=sum(1 for c in cl if c['status']=='active')
ar=sum(1 for c in cl if c['status']=='at_risk')
dm=sum(1 for c in cl if c['status']=='dormant')

# Compute region revenue from sp aggregation
region_rev = defaultdict(float)
for n, s in sp_agg.items():
    r = sp_region.get(n, '')
    if r: region_rev[r] += s['amount']
summary={'total_revenue':round(tr,2),'total_customers':len(cl),'active_customers':ac,
         'at_risk_customers':ar,'dormant_customers':dm,'period':f'{ms[0]["month"]} ~ {ms[-1]["month"]}',
         'regions':{r:round(v,2) for r,v in sorted(region_rev.items(), key=lambda x:-x[1])}}

for fn,data in [('diag_customers.json',cl),('diag_salespeople.json',sl),('diag_monthly.json',ms),('diag_summary.json',summary)]:
    with open(os.path.join(outdir,fn),'w') as f: json.dump(data,f,ensure_ascii=False)

y24=sum(m['amount'] for m in ms if m['month'].startswith('2024'))
y25=sum(m['amount'] for m in ms if m['month'].startswith('2025'))
y26=sum(m['amount'] for m in ms if m['month'].startswith('2026'))

print(f'\n营收:{tr/1e4:.0f}w ({y24/1e4:.0f}/{y25/1e4:.0f}/{y26/1e4:.0f})')
print(f'集团/客户:{len(cl)}(活跃{ac})')

print(f'\nTop10集团:')
for c in cl[:10]:
    print(f'  {c["name"][:30]:30s} {c["amount"]/1e4:6.0f}w ({c["member_count"]}家) {c["y2024"]/1e4:.0f}/{c["y2025"]/1e4:.0f}/{c["y2026"]/1e4:.0f}w')

print(f'\nTop12销售员(区域|部门):')
for s in sl[:12]:
    print(f'  {s["name"]:8s} [{s["region"]:6s}|{s["dept"]:8s}] {s["amount"]/1e4:5.0f}w {s["customers"]:3d}客 24:{s["y2024"]/1e4:.0f} 25:{s["y2025"]/1e4:.0f} 26:{s["y2026"]/1e4:.0f}')