#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
构建 AI 助手工作流的产品/促销底座（4 个 JSON 一次生成）

出：
  数据/product_recommendation_list.json  — 诊断原料·产品推荐清单（项目类型→推荐体系→货号变种）
  数据/ls_product_catalog.json           — 生命科学·产品目录（分类→货号→目录价/促销价/促销政策）
  数据/promo_campaigns.json              — 当季促销活动（da/ls 双线）
  数据/sales_policy.json                 — 销售政策（促销权限 + 费用额度）

源：~/Desktop/hermas输入-工作台数据库/产品清单/*.xlsx
    ~/Desktop/Hermes输出-工作类/科研2026秋季促销/*.xlsx

用法：/usr/bin/python3 脚本/build_product_catalogs.py
"""
import os
import re
import json
import glob
from datetime import datetime

BASE = os.path.expanduser('~/Desktop/Hermes输出-工作类')
SRC_DIR = os.path.expanduser('~/Desktop/hermas输入-工作台数据库/产品清单')
PROMO_DIRS = [
    os.path.join(BASE, '科研2026秋季促销'),
    os.path.expanduser('~/Desktop/hermas输入-工作台数据库/科研2026秋季促销'),
    os.path.join(BASE, '产品/促销素材'),
]
DATA = os.path.join(BASE, '数据')

SKU_RE = re.compile(r'[A-Za-z]{1,8}\d{2,6}(?:[-‐]\d{2,6}){0,3}')
PRIO_RE = re.compile(r'[①②③④⑤⑥⑦⑧⑨⑩]')
NOW = datetime.now().strftime('%Y-%m-%d %H:%M')


def clean(v):
    if v is None:
        return ''
    s = str(v).strip()
    if s.endswith('.0'):
        s = s[:-2]
    return s


def norm_header(h):
    """表头归一化：去换行、去'未排序'、去'及优先级'，便于跨版本匹配"""
    s = clean(h).replace('\n', '').replace(' ', '')
    s = s.replace('未排序', '').replace('及优先级', '')
    return s


def dump(path, obj):
    os.makedirs(os.path.dirname(path), exist_ok=True)
    with open(path, 'w', encoding='utf-8') as f:
        json.dump(obj, f, ensure_ascii=False, indent=1)
    print('  → %s (%d KB)' % (os.path.basename(path), os.path.getsize(path) // 1024))


def xls_mtime_month(fn):
    """从文件名提取 YYYYMM，用于选最新版"""
    m = re.search(r'(20\d{2})[-_ ]?(\d{2})', os.path.basename(fn))
    if m:
        return m.group(1) + m.group(2)
    m = re.search(r'(20\d{2})(\d{2})\d{2}', os.path.basename(fn))
    if m:
        return m.group(1) + m.group(2)
    return ''


# ────────────────────────── 1. 诊断原料·产品推荐清单 ──────────────────────────
def build_recommendation():
    print('[1/4] 诊断原料·产品推荐清单')
    cands = [f for f in glob.glob(os.path.join(SRC_DIR, '*产品推荐清单*.xlsx'))]
    cands = [f for f in cands if '物流' not in f and '套装' not in f]
    if not cands:
        print('  ! 未找到推荐清单'); return
    src = max(cands, key=lambda f: (xls_mtime_month(f), os.path.getmtime(f)))
    import openpyxl
    wb = openpyxl.load_workbook(src, data_only=True)
    items = []

    def split_systems(cell):
        txt = clean(cell)
        if not txt:
            return []
        out = []
        for p in [x.strip() for x in re.split(r'[\n；;]', txt) if x.strip()]:
            pm = PRIO_RE.search(p)
            skus = SKU_RE.findall(p)
            body = re.sub(r'^[、，,.\s]+', '', PRIO_RE.sub('', p)).strip()
            out.append({'priority': pm.group(0) if pm else '', 'sku': skus[0] if skus else '',
                        'skus': skus, 'raw': body[:160]})
        return out

    for ws in wb.worksheets:
        rows = list(ws.iter_rows(values_only=True))
        if len(rows) < 2:
            continue
        hdr = [norm_header(c) for c in rows[0]]
        if '项目类型' not in hdr:
            continue
        col = {}
        for want, key in [('技术方法', 'method'), ('DNA/RNA', 'nucleic'), ('推荐体系', 'systems'),
                          ('推荐体系-冻干', 'systems_lyo'), ('特征', 'feature'),
                          ('是否可全预混', 'can_premix'), ('快速程序', 'fast_program'),
                          ('试剂特征', 'reagent_feature'), ('样本类型', 'sample_type'),
                          ('是否快速', 'is_fast')]:
            col[key] = hdr.index(want) if want in hdr else -1
        cur_top, cur_sub = '', ''
        for r in rows[1:]:
            vals = [clean(c) for c in r]

            def g(key):
                i = col.get(key, -1)
                return vals[i] if 0 <= i < len(vals) else ''

            if vals[0]:
                cur_top, cur_sub = vals[0], ''
            if len(vals) > 1 and vals[1]:
                cur_sub = vals[1]
            top = vals[0] or cur_top
            it = {
                'sheet': ws.title, 'project': top, 'sub': cur_sub,
                'method': g('method'), 'nucleic': g('nucleic'),
                'systems': split_systems(g('systems')),
                'systems_lyo': split_systems(g('systems_lyo')),
                'feature': g('feature')[:300], 'can_premix': g('can_premix'),
                'fast_program': g('fast_program'), 'reagent_feature': g('reagent_feature')[:300],
                'sample_type': g('sample_type'), 'is_fast': g('is_fast'),
            }
            if not (it['project'] or it['systems'] or it['systems_lyo']):
                continue
            items.append(it)

    by_sku, by_base = {}, {}
    for i, it in enumerate(items):
        for s in it['systems'] + it['systems_lyo']:
            if not s['sku']:
                continue
            k = s['sku'].upper()
            by_sku.setdefault(k, []).append({'ref': i, 'priority': s['priority']})
            base = k.split('-')[0]
            by_base.setdefault(base, set()).add(k)
    dump(os.path.join(DATA, 'product_recommendation_list.json'), {
        'updated': NOW, 'source': os.path.basename(src), 'count': len(items),
        'items': items, 'by_sku': by_sku,
        'by_base': {k: sorted(v) for k, v in by_base.items()},
    })
    print('  条目 %d，货号 %d，母体 %d' % (len(items), len(by_sku), len(by_base)))


# ────────────────────────── 2. 生命科学·产品目录 ──────────────────────────
def build_ls_catalog():
    print('[2/4] 生命科学·产品目录')
    cands = glob.glob(os.path.join(SRC_DIR, '*生命科学*产品清单*.xlsx'))
    cands += glob.glob(os.path.join(SRC_DIR, '*生命科学*清单*.xlsx'))
    if not cands:
        print('  ! 未找到生命科学产品清单'); return
    src = max(set(cands), key=lambda f: (xls_mtime_month(f), os.path.getmtime(f)))
    import openpyxl
    wb = openpyxl.load_workbook(src, data_only=True)
    ws = wb.worksheets[0]
    rows = list(ws.iter_rows(values_only=True))
    hdr = [norm_header(c) for c in rows[0]]
    print('  源：%s（表头 %d 列）' % (os.path.basename(src), len(hdr)))

    def find(*names):
        for n in names:
            n2 = norm_header(n)
            for i, h in enumerate(hdr):
                if h == n2 or (n2 and n2 in h):
                    return i
        return -1

    idx = {
        'c1': find('一级分类'), 'c2': find('二级分类'), 'c3': find('三级分类'),
        'name': find('网站名称'), 'sku': find('货号'), 'list_price': find('产品目录价'),
        'promo_price': find('产品促销价'), 'promo_policy': find('促销政策1'),
        'promo_policy2': find('促销政策2'), 'test_sku': find('测试装货号'),
        'feature': find('特点'), 'app': find('推荐应用'), 'store': find('保存条件'),
        'conc': find('浓度'), 'spec': find('规格'),
    }
    items = []
    for r in rows[1:]:
        vals = [clean(c) for c in r]

        def g(k):
            i = idx.get(k, -1)
            return vals[i] if 0 <= i < len(vals) else ''

        name = g('name')
        sku_raw = g('sku')
        if not name and not sku_raw:
            continue
        skus = sorted(set(SKU_RE.findall(sku_raw.replace('/', ' ').replace('\n', ' '))))
        items.append({
            'c1': g('c1'), 'c2': g('c2'), 'c3': g('c3'), 'name': name,
            'spec': g('spec'), 'conc': g('conc'), 'skus': skus, 'sku_raw': sku_raw[:120],
            'list_price': g('list_price'), 'promo_price': g('promo_price'),
            'promo_policy': g('promo_policy'), 'promo_policy2': g('promo_policy2'),
            'test_sku': g('test_sku')[:120], 'feature': g('feature')[:400],
            'app': g('app')[:300], 'store': g('store'),
        })
    by_sku = {}
    for i, it in enumerate(items):
        for s in it['skus']:
            by_sku.setdefault(s.upper(), []).append(i)
    dump(os.path.join(DATA, 'ls_product_catalog.json'), {
        'updated': NOW, 'source': os.path.basename(src), 'count': len(items),
        'items': items, 'by_sku': by_sku,
    })
    print('  产品 %d，货号索引 %d' % (len(items), len(by_sku)))


# ────────────────────────── 3. 当季促销活动 ──────────────────────────
def build_promo():
    print('[3/4] 当季促销活动')
    found = []
    for d in PROMO_DIRS:
        if os.path.isdir(d):
            for f in glob.glob(os.path.join(d, '*.xlsx')):
                found.append(f)
    found = sorted(set(found))
    if not found:
        print('  ! 未找到促销文件'); 
        dump(os.path.join(DATA, 'promo_campaigns.json'), {'updated': NOW, 'count': 0, 'campaigns': []})
        return
    import openpyxl
    campaigns = []
    for f in found:
        bn = os.path.basename(f)
        try:
            wb = openpyxl.load_workbook(f, data_only=True)
        except Exception as e:
            print('  ! 跳过 %s: %s' % (bn, e)); continue
        line = 'ls' if ('科研' in bn or 'mRNA' in bn.upper()) else 'da'
        for ws in wb.worksheets:
            rows = list(ws.iter_rows(values_only=True))
            if len(rows) < 2:
                continue
            hdr = [clean(c) for c in rows[0]]
            recs = []
            for r in rows[1:]:
                v = [clean(x) for x in r if clean(x)]
                if v:
                    recs.append(v[:8])
            campaigns.append({
                'file': bn, 'line': line, 'sheet': ws.title,
                'title': bn.replace('.xlsx', ''),
                'headers': [h for h in hdr if h],
                'rows': recs[:80], 'row_count': len(recs),
            })
    dump(os.path.join(DATA, 'promo_campaigns.json'), {
        'updated': NOW, 'count': len(campaigns), 'campaigns': campaigns,
    })
    print('  促销表 %d 张（源文件 %d 个）' % (len(campaigns), len(found)))


# ────────────────────────── 4. 销售政策 ──────────────────────────
def build_policy():
    print('[4/4] 销售政策')
    pol = {
        'updated': NOW,
        'source': '用户口述（2026-09-10）+ 培训资源库',
        'note': '⚠️ 待用户确认细则后转正式版；当前仅录入已明确表述的条款',
        'policies': [
            {
                'id': 'promo_authority',
                'line': 'both',
                'title': '当季促销权限',
                'rule': '销售可依据当季促销活动（数据/promo_campaigns.json）灵活运用促销权限；'
                        '回答产品价格/方案问题时，必须主动检查当季促销是否可用，并给出促销后价格。',
                'source': '用户指令 2026-09-10',
            },
            {
                'id': 'sales_expense_quota',
                'line': 'both',
                'title': '销售费用额度',
                'rule': '促销期之外，每位销售每月有 700 元销售费用额度可灵活使用。',
                'amount_cny': 700,
                'period': 'monthly',
                'applies_when': '非促销期',
                'source': '用户指令 2026-09-10',
                'pending': ['额度用途范围', '是否可累积结转', '是否需要审批/备案', '超支处理'],
            },
        ],
    }
    dump(os.path.join(DATA, 'sales_policy.json'), pol)


if __name__ == '__main__':
    print('构建产品/促销底座 @ %s\n' % NOW)
    build_recommendation()
    build_ls_catalog()
    build_promo()
    build_policy()
    print('\n完成。')