#!/usr/bin/env python3
"""
ERP 销售数据核心提取模块（共享）
口径：标准销售订单 + 已审核 + 非赠品 + 剔除宝锐系
人员映射：黄明月/张立娅 → 刘子研；刘新元(代管23家客户) → 刘子研
兼容两种 ERP 导出格式：
  - 旧格式：含「销售员」列 + 「价税合计」列（含税金额）
  - 新格式：无「销售员」列（用 customer_sp_map.json 客户映射兜底）+「金额」列（不含税，需 ×(1+税率)）
供 refresh_erp.py / mcp_kpi_pipeline.py 复用。
"""
import json, os
import pandas as pd
from datetime import datetime, timedelta

BASE = os.path.expanduser('~/Desktop/Hermes输出-工作类')
DATA = os.path.join(BASE, '数据')

# 上传目录（用户拖入 ERP 文件的位置）
SRC_DIRS = [
    os.path.expanduser('~/Desktop/hermas输入-工作台数据库/月度销售额数据'),
    os.path.join(DATA, '每日上传'),
]

# 宝锐系内部采购（剔除）
BAORUI_PATTERN = '宝锐|宝泰|横琴'

def find_latest_erp():
    """按 mtime 找最新的销售订单 xlsx 文件"""
    files = []
    for d in SRC_DIRS:
        if not os.path.isdir(d):
            continue
        for f in os.listdir(d):
            if f.startswith('销售订单') and f.endswith('.xlsx') and not f.startswith('~'):
                files.append(os.path.join(d, f))
    if not files:
        return None
    files.sort(key=lambda f: os.path.getmtime(f))
    return files[-1]

def ser2date(v):
    try:
        n = float(v)
        if 20000 < n < 60000:
            return (datetime(1899, 12, 30) + timedelta(days=int(n))).strftime('%Y-%m-%d')
    except Exception:
        pass
    s = str(v)
    # 截断时间部分（如 "2026-09-07 00:00:00" → "2026-09-07"）
    if ' ' in s:
        s = s.split(' ')[0]
    # 兼容斜杠格式（"2026/09/07" → "2026-09-07"）
    s = s.replace('/', '-')
    return s

def load_mappings():
    """返回 (person_dept, lzy_custs)"""
    with open(os.path.join(DATA, 'pricing/_personnel.json'), encoding='utf-8') as f:
        personnel = json.load(f)['personnel']
    person_dept = {}
    for p in personnel:
        dept = p.get('dept', '')
        if '大客户' in dept:
            d = '大客户部'
        elif '生命科学' in dept:
            d = '生命科学部'
        elif '销售拓展' in dept:
            d = '销售拓展部'
        else:
            d = dept
        person_dept[p['name']] = d
    with open(os.path.join(DATA, 'pricing/_key_customers.json'), encoding='utf-8') as f:
        kc = json.load(f)
    lzy_custs = set(c.get('name') for c in kc if (c.get('salesperson') or c.get('sales') or '') == '刘子研')
    return person_dept, lzy_custs

def load_sp_map():
    """加载「客户 → 销售员」映射（新格式 ERP 无销售员列时的兜底）"""
    p = os.path.join(DATA, 'customer_sp_map.json')
    if os.path.exists(p):
        try:
            with open(p, encoding='utf-8') as f:
                return json.load(f).get('map', {})
        except Exception:
            return {}
    return {}

def load_order_sp_map():
    """加载「单据编号 → 销售员」映射（精确匹配，优先于客户映射）"""
    p = os.path.join(DATA, 'order_sp_map.json')
    if os.path.exists(p):
        try:
            with open(p, encoding='utf-8') as f:
                return json.load(f).get('map', {})
        except Exception:
            return {}
    return {}

KPI_PEOPLE = set([
    '路要峰', '刘虹晶', '李江辉', '杨天雄', '宋学伟', '冯贤平', '黄文才', '陈溪子',
    '任鑫', '林潇', '赵云浩', '张文蝶', '刘子研', '徐卓凡', '袁殿果', '王诗香',
    '黄建华', '侯海瑜', '叶信福', '刘斌', '吴云', '韩远怀',
])

def infer_dept_from_pt(pt):
    """根据客户采购类型/产品类型推断部门（仅用于无法映射到销售员时的兜底）"""
    if not pt:
        return None
    pt = str(pt)
    if '生命科学' in pt or '生物医药' in pt:
        return '生命科学部'
    if '分子诊断' in pt or '诊断' in pt:
        return '销售拓展部'
    return None

def map_person(sp, cust, person_dept, lzy_custs, pt=''):
    """返回 (kpi_person或None, dept或None)"""
    if sp == '黄明月':
        return ('刘子研', '大客户部')
    if sp == '张立娅':
        return ('刘子研', '大客户部')
    if sp == '刘新元':
        if cust in lzy_custs:
            return ('刘子研', '大客户部')
        return (None, person_dept.get(sp, '销售拓展部'))
    dept = person_dept.get(sp)
    if dept is None:
        # 不在册销售员（如已离职且未更新）或新客户无销售员 → 按采购类型归部门兜底
        d = infer_dept_from_pt(pt)
        return (None, d)
    if sp in KPI_PEOPLE:
        return (sp, dept)
    return (None, dept)

def aggregate(fp):
    """读 ERP 文件，返回 (monthly_total, monthly_person, monthly_dept, source_file)
    兼容新旧两种格式。"""
    df = pd.read_excel(fp, dtype=str)
    has_jshj = '价税合计' in df.columns
    has_amt = '金额' in df.columns
    has_sp = '销售员' in df.columns

    # 金额口径：旧=价税合计（含税）；新=金额（不含税，× (1+税率/100)）
    if has_jshj:
        df['_amt'] = pd.to_numeric(df['价税合计'], errors='coerce').fillna(0)
    elif has_amt:
        rate = pd.to_numeric(df['税率%'], errors='coerce').fillna(0)
        df['_amt'] = pd.to_numeric(df['金额'], errors='coerce').fillna(0) * (1 + rate / 100)
    else:
        return {}, {}, {}, os.path.basename(fp)

    # 销售员：旧=销售员列；新=单据编号精确匹配 → 客户映射兜底
    if has_sp:
        df['_sp'] = df['销售员'].fillna('').astype(str)
    else:
        order_map = load_order_sp_map()
        sp_map = load_sp_map()
        df['_oid'] = df['单据编号'].astype(str).str.strip()
        df['_sp'] = df['_oid'].map(order_map).fillna('')
        miss = df['_sp'] == ''
        df.loc[miss, '_sp'] = df.loc[miss, '客户'].astype(str).str.strip().map(sp_map).fillna('')

    # 采购类型（用于无法映射时的部门兜底）
    if '客户采购类型#' in df.columns:
        pt_col = '客户采购类型#'
    elif '产品类型#' in df.columns:
        pt_col = '产品类型#'
    else:
        pt_col = None

    df['_date'] = df['合同日期'].apply(ser2date)
    df['_month'] = df['_date'].str[:7]

    std = df[(df['单据类型'] == '标准销售订单') & (df['单据状态'] == '已审核') & (df['是否赠品'] != '是')]
    std = std[~std['客户'].str.contains(BAORUI_PATTERN, na=False)]
    std = std.copy()
    std['_amt'] = std['_amt'].astype(float)

    person_dept, lzy_custs = load_mappings()

    monthly_total = {}
    monthly_person = {}
    monthly_dept = {}
    for _, r in std.iterrows():
        m = r['_month']
        if not (m.startswith('2026') and len(m) == 7):
            continue
        sp = str(r['_sp']).strip()
        cust = str(r['客户']).strip()
        amt = float(r['_amt'])
        pt = str(r[pt_col]) if pt_col else ''
        kpi_person, dept = map_person(sp, cust, person_dept, lzy_custs, pt)
        if dept is None:
            continue
        monthly_total[m] = monthly_total.get(m, 0) + amt
        monthly_dept.setdefault(m, {})
        monthly_dept[m][dept] = monthly_dept[m].get(dept, 0) + amt
        if kpi_person:
            monthly_person.setdefault(m, {})
            monthly_person[m][kpi_person] = monthly_person[m].get(kpi_person, 0) + amt

    return monthly_total, monthly_person, monthly_dept, os.path.basename(fp)


def aggregate_daily(fp):
    """按天聚合 ERP 金额（供订单管理页按周/月/季/年统计）
    返回 (daily_total, daily_person, monthly_total, monthly_person, monthly_dept, source_file)
    daily_total: {日期YYYY-MM-DD: 金额}
    """
    df = pd.read_excel(fp, dtype=str)
    has_jshj = '价税合计' in df.columns
    has_amt = '金额' in df.columns
    has_sp = '销售员' in df.columns

    if has_jshj:
        df['_amt'] = pd.to_numeric(df['价税合计'], errors='coerce').fillna(0)
    elif has_amt:
        rate = pd.to_numeric(df['税率%'], errors='coerce').fillna(0)
        df['_amt'] = pd.to_numeric(df['金额'], errors='coerce').fillna(0) * (1 + rate / 100)
    else:
        return {}, {}, {}, {}, {}, os.path.basename(fp)

    if has_sp:
        df['_sp'] = df['销售员'].fillna('').astype(str)
    else:
        order_map = load_order_sp_map()
        sp_map = load_sp_map()
        df['_oid'] = df['单据编号'].astype(str).str.strip()
        df['_sp'] = df['_oid'].map(order_map).fillna('')
        miss = df['_sp'] == ''
        df.loc[miss, '_sp'] = df.loc[miss, '客户'].astype(str).str.strip().map(sp_map).fillna('')

    if '客户采购类型#' in df.columns:
        pt_col = '客户采购类型#'
    elif '产品类型#' in df.columns:
        pt_col = '产品类型#'
    else:
        pt_col = None

    df['_date'] = df['合同日期'].apply(ser2date)
    df['_month'] = df['_date'].str[:7]

    std = df[(df['单据类型'] == '标准销售订单') & (df['单据状态'] == '已审核') & (df['是否赠品'] != '是')]
    std = std[~std['客户'].str.contains(BAORUI_PATTERN, na=False)]
    std = std.copy()
    std['_amt'] = std['_amt'].astype(float)

    person_dept, lzy_custs = load_mappings()

    daily_total = {}
    daily_person = {}
    monthly_total = {}
    monthly_person = {}
    monthly_dept = {}
    customer_monthly = {}
    for _, r in std.iterrows():
        d = str(r['_date']).strip()
        m = str(r['_month']).strip()
        if not (m.startswith('2026') and len(m) == 7):
            continue
        sp = str(r['_sp']).strip()
        cust = str(r['客户']).strip()
        amt = float(r['_amt'])
        pt = str(r[pt_col]) if pt_col else ''
        kpi_person, dept = map_person(sp, cust, person_dept, lzy_custs, pt)
        if dept is None:
            continue
        daily_total[d] = daily_total.get(d, 0) + amt
        monthly_total[m] = monthly_total.get(m, 0) + amt
        monthly_dept.setdefault(m, {})
        monthly_dept[m][dept] = monthly_dept[m].get(dept, 0) + amt
        customer_monthly.setdefault(m, {})
        customer_monthly[m][cust] = customer_monthly[m].get(cust, 0) + amt
        if kpi_person:
            daily_person.setdefault(d, {})
            daily_person[d][kpi_person] = daily_person[d].get(kpi_person, 0) + amt
            monthly_person.setdefault(m, {})
            monthly_person[m][kpi_person] = monthly_person[m].get(kpi_person, 0) + amt

    return daily_total, daily_person, monthly_total, monthly_person, monthly_dept, customer_monthly, os.path.basename(fp)

if __name__ == '__main__':
    fp = find_latest_erp()
    print('最新ERP文件:', fp)
    mt, mp, md, fn = aggregate(fp)
    for m in sorted(mt):
        print(f'{m}: {mt[m]/1e4:.2f}万')