#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""科研秋季开学促销 · 分区域统计表（广东 9/1-10/16；浙江 8/20-9/30）"""
import datetime as dt
import json
import os
from collections import defaultdict

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

BASE = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/月度销售额数据'
F1 = os.path.join(BASE, '销售订单_20260101-20261015.xlsx')
F2 = os.path.join(BASE, '_增量归档/销售订单_20261001-1016.xlsx')
W = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
OUT = os.path.join(W, '营销活动/科研秋季促销_分区域统计_2026.xlsx')

people = json.load(open(os.path.join(W, '数据/目标基础数据_2026.json'), encoding='utf-8'))['people']
PMAP = {p['name']: p for p in people if '生命科学' in p.get('dept', '')}

WINDOWS = {'广东': (dt.date(2026, 9, 1), dt.date(2026, 10, 16)),
           '浙江': (dt.date(2026, 8, 20), dt.date(2026, 9, 30))}
TIERS = [(100000, '10W', 5000, 10000), (50000, '5W', 2500, 5000), (20000, '2W', 1000, 2000),
         (10000, '1W', 500, 1000), (5000, '5K', 250, 500), (2000, '2K', 100, 200), (1000, '1K', 50, 100)]


def tier(amount):
    for th, name, lo, hi in TIERS:
        if amount >= th:
            return name, lo, hi
    return '未达标', 0, 0


def load(path):
    wb = openpyxl.load_workbook(path, read_only=True, data_only=True)
    ws = wb.active
    rows = ws.iter_rows(values_only=True)
    head = [str(c).strip() if c is not None else '' for c in next(rows)]
    idx = {h: i for i, h in enumerate(head)}
    out = []
    for r in rows:
        if not r or r[idx.get('客户', 4)] is None:
            continue
        out.append(r)
    return head, idx, out


def norm_date(v):
    if isinstance(v, dt.datetime):
        return v.date()
    if isinstance(v, dt.date):
        return v
    s = str(v)[:10]
    try:
        return dt.datetime.strptime(s, '%Y-%m-%d').date()
    except Exception:
        return None


orders = {}
for path in (F1, F2):
    if not os.path.exists(path):
        print('缺文件', path)
        continue
    head, idx, rows = load(path)
    for r in rows:
        d = norm_date(r[idx.get('合同日期', 0)])
        cust = str(r[idx.get('客户', 4)] or '').strip()
        sales = str(r[idx.get('销售员', 5)] or '').strip()
        if not d or not cust or sales not in PMAP:
            continue
        region = PMAP[sales].get('region')
        if region not in WINDOWS:
            continue
        a, b = WINDOWS[region]
        if not (a <= d <= b):
            continue
        oid = str(r[idx.get('单据编号', 2)] or '')
        otype = str(r[idx.get('单据类型', 1)] or '')
        if otype == '测试销售订单':
            continue
        amt = r[idx.get('价税合计', 13)]
        try:
            amt = float(amt or 0)
        except Exception:
            amt = 0.0
        gift = str(r[idx.get('是否赠品', 14)] or '')
        if gift == '是':
            amt = 0.0
        if oid in orders:      # 同一订单多行物料：累加，不去重
            orders[oid]['amount'] += amt
            continue
        proj = ''
        for k in ('商机(飞书)#', '商机', '项目'):
            if k in idx:
                proj = str(r[idx[k]] or '').strip()
                break
        orders[oid] = {'region': region, 'date': d, 'order': oid, 'customer': cust,
                       'sales': sales, 'amount': amt, 'project': proj}

records = list(orders.values())
print('有效订单行', len(records))
# 按客户聚合
cust = defaultdict(lambda: {'amount': 0.0, 'orders': set(), 'region': '', 'sales': '', 'projects': set()})
for x in records:
    k = (x['customer'], x['region'])
    cust[k]['amount'] += x['amount']
    cust[k]['orders'].add(x['order'])
    cust[k]['region'] = x['region']
    cust[k]['sales'] = x['sales']
    if x['project']:
        cust[k]['projects'].add(x['project'])

summary = defaultdict(lambda: {'customers': 0, 'orders': 0, 'amount': 0.0, 'hit': 0, 'gift_lo': 0, 'gift_hi': 0})
rows_detail = []
for (cname, region), v in sorted(cust.items(), key=lambda kv: -kv[1]['amount']):
    t, lo, hi = tier(v['amount'])
    rows_detail.append([region, v['sales'], cname, '、'.join(sorted(v['projects']))[:40],
                        len(v['orders']), round(v['amount'], 2), t,
                        lo if t != '未达标' else 0, hi if t != '未达标' else 0])
    s = summary[region]
    s['customers'] += 1
    s['orders'] += len(v['orders'])
    s['amount'] += v['amount']
    if t != '未达标':
        s['hit'] += 1
        s['gift_lo'] += lo
        s['gift_hi'] += hi

wb = openpyxl.Workbook()
head_fill = PatternFill('solid', fgColor='1F4E79')
head_font = Font(color='FFFFFF', bold=True, size=10)


def style(ws, headers, widths):
    for i, h in enumerate(headers, 1):
        c = ws.cell(row=1, column=i, value=h)
        c.fill = head_fill; c.font = head_font
        c.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
        ws.column_dimensions[get_column_letter(i)].width = widths[i - 1]
    ws.freeze_panes = 'A2'


ws = wb.active; ws.title = '汇总_分区域'
style(ws, ['区域', '统计窗口', '客户数', '订单数', '销售额(元)', '达档客户数', '赠品价值下限(元)', '赠品价值上限(元)'],
      [10, 26, 9, 9, 14, 11, 15, 15])
r = 2
for reg in ('广东', '浙江'):
    a, b = WINDOWS[reg]; s = summary[reg]
    vals = [reg, '%s ~ %s' % (a, b), s['customers'], s['orders'], round(s['amount'], 2),
            s['hit'], s['gift_lo'], s['gift_hi']]
    for i, v in enumerate(vals, 1):
        ws.cell(row=r, column=i, value=v)
    r += 1
tot = ['合计', '—', sum(summary[x]['customers'] for x in summary), sum(summary[x]['orders'] for x in summary),
       round(sum(summary[x]['amount'] for x in summary), 2), sum(summary[x]['hit'] for x in summary),
       sum(summary[x]['gift_lo'] for x in summary), sum(summary[x]['gift_hi'] for x in summary)]
for i, v in enumerate(tot, 1):
    c = ws.cell(row=r, column=i, value=v); c.font = Font(bold=True)

ws2 = wb.create_sheet('按客户明细')
style(ws2, ['区域', '主跟销售', '客户', '课题组/项目', '订单数', '采购额(元)', '达标档位', '赠品下限(元)', '赠品上限(元)'],
      [8, 10, 34, 30, 8, 13, 10, 12, 12])
for i, row in enumerate(rows_detail, 2):
    for j, v in enumerate(row, 1):
        ws2.cell(row=i, column=j, value=v)

ws3 = wb.create_sheet('按销售员')
style(ws3, ['区域', '销售员', '客户数', '订单数', '销售额(元)', '达档客户数', '赠品下限(元)', '赠品上限(元)'],
      [8, 10, 9, 9, 14, 11, 12, 12])
by_sales = defaultdict(lambda: {'c': set(), 'o': set(), 'a': 0.0, 'hit': 0, 'lo': 0, 'hi': 0})
for x in records:
    s = by_sales[(x['region'], x['sales'])]
    s['c'].add(x['customer']); s['o'].add(x['order']); s['a'] += x['amount']
for (reg, sales), v in sorted(by_sales.items(), key=lambda kv: (kv[0][0], -kv[1]['a'])):
    hit = sum(1 for (cn, rg), cv in cust.items() if rg == reg and cv['sales'] == sales and tier(cv['amount'])[0] != '未达标')
    lo = sum(tier(cv['amount'])[1] for (cn, rg), cv in cust.items() if rg == reg and cv['sales'] == sales)
    hi = sum(tier(cv['amount'])[2] for (cn, rg), cv in cust.items() if rg == reg and cv['sales'] == sales)
    r = ws3.max_row + 1
    for i, val in enumerate([reg, sales, len(v['c']), len(v['o']), round(v['a'], 2), hit, lo, hi], 1):
        ws3.cell(row=r, column=i, value=val)

ws4 = wb.create_sheet('档位分布')
style(ws4, ['区域', '档位', '客户数', '销售额(元)', '赠品下限(元)', '赠品上限(元)'], [8, 12, 9, 14, 12, 12])
dist = defaultdict(lambda: {'n': 0, 'a': 0.0, 'lo': 0, 'hi': 0})
for (cname, region), v in cust.items():
    t, lo, hi = tier(v['amount'])
    d = dist[(region, t)]
    d['n'] += 1; d['a'] += v['amount']; d['lo'] += lo; d['hi'] += hi
order = ['10W', '5W', '2W', '1W', '5K', '2K', '1K', '未达标']
r = 2
for reg in ('广东', '浙江'):
    for t in order:
        d = dist.get((reg, t))
        if not d:
            continue
        for i, val in enumerate([reg, t, d['n'], round(d['a'], 2), d['lo'], d['hi']], 1):
            ws4.cell(row=r, column=i, value=val)
        r += 1

ws5 = wb.create_sheet('口径与说明')
notes = [
    ['项目', '说明'],
    ['活动', '2026 科研秋季开学促销（预付款档位赠礼）'],
    ['广东窗口', '2026-09-01 至 2026-10-16（含首尾）'],
    ['浙江窗口', '2026-08-20 至 2026-09-30（含首尾）'],
    ['统计对象', '生命科学部销售员名下订单（按 目标基础数据_2026 人员区域归属）'],
    ['计入规则', '仅计标准销售订单；测试销售订单不计入'],
    ['金额口径', 'ERP 价税合计（含税），按订单去重后汇总'],
    ['档位规则', '1K≥1,000；2K≥2,000；5K≥5,000；1W≥10,000；2W≥20,000；5W≥50,000；10W≥100,000'],
    ['赠品价值', '按活动方案 5%–10% 计算区间，取整到档位给定上下限'],
    ['数据截止', '2026-10-16（广东含 10-16 增量文件）'],
    ['注意', '若客户跨区域下单，按订单销售员所属区域归集，不按客户注册地'],
]
for i, row in enumerate(notes, 1):
    for j, v in enumerate(row, 1):
        c = ws5.cell(row=i, column=j, value=v)
        if i == 1:
            c.fill = head_fill; c.font = head_font
ws5.column_dimensions['A'].width = 14
ws5.column_dimensions['B'].width = 78

# 导出 JSON 供 HTML 使用
_maxd = {}
for x in records:
    _maxd[x['region']] = max(_maxd.get(x['region'], x['date']), x['date'])
json.dump({'windows': {k: [str(v[0]), str(v[1])] for k, v in WINDOWS.items()},
           'max_date': {k: str(v) for k, v in _maxd.items()},
           'summary': {k: dict(v) for k, v in summary.items()},
           'detail': rows_detail,
           'sales': [[k[0], k[1], len(v['c']), len(v['o']), round(v['a'], 2)] for k, v in by_sales.items()]},
          open(os.path.join(W, '数据/promo_stats_2026.json'), 'w'), ensure_ascii=False, indent=1)

wb.save(OUT)
print('✅', OUT)
for reg in ('广东', '浙江'):
    s = summary[reg]
    print('  %s：客户 %d ｜订单 %d ｜金额 %.2f ｜达档 %d ｜赠品 %d–%d 元' % (
        reg, s['customers'], s['orders'], s['amount'], s['hit'], s['gift_lo'], s['gift_hi']))