#!/usr/bin/env python3
"""销售订单数据管道 — MCP拉取→mcp_orders_input.json→顺丰物流
用法: python3 pull_orders.py [--days N]
输出: mcp_orders_input.json
"""
import subprocess, json, os, sys, time as _time
from datetime import datetime, timedelta, date

TK = "m-abfb29e8-3104-434f-9944-8d0bb592f8cd"
PK = "6593cd71471290e3cc6be6e6"
BASE = os.path.dirname(os.path.abspath(__file__))
OUTPUT = os.path.join(BASE, "mcp_orders_input.json")
TODAY = date.today()

# ── 拉取窗口 ──
# 支持 --days N（自然日窗口，兼容旧调用）或 --months N（日历月窗口，默认 2 = 上月1日~今天）
# ⚠️ 2026-09-10：MCP search_by_mql 的 LIMIT OFFSET 上限为 1000，单段最多 1050 行。
#    旧版单轮 OFFSET 翻页在 90 天窗口（4448 行）会静默截断到 1050 行，丢掉 8 月 73% 订单。
#    现改为按日期分段（7天/段，段内 <1000 行）逐段拉取，再按 work_item_id 全局去重。
def _parse_window():
    if "--days" in sys.argv:
        i = sys.argv.index("--days")
        n = int(sys.argv[i + 1]) if len(sys.argv) > i + 1 else 90
        return (TODAY - timedelta(days=n)).strftime("%Y-%m-%d")
    if "--months" in sys.argv:
        i = sys.argv.index("--months")
        n = int(sys.argv[i + 1]) if len(sys.argv) > i + 1 else 2
    else:
        n = 2
    y, m = TODAY.year, TODAY.month
    for _ in range(n - 1):
        m -= 1
        if m == 0:
            m, y = 12, y - 1
    return date(y, m, 1).strftime("%Y-%m-%d")


START = _parse_window()

def mcp(method, args):
    r = subprocess.run(['curl','-s','-X','POST','https://project.feishu.cn/mcp_server/v1',
        '-H',f'X-Mcp-Token: {TK}','-H','Content-Type: application/json',
        '-d', json.dumps({"jsonrpc":"2.0","method":"tools/call",
        "params":{"name":method,"arguments":args},"id":1})],
        capture_output=True, text=True, timeout=30)
    try:
        d = json.loads(r.stdout)
        if 'error' in d: return None
        for c in d['result']['content']:
            t = c.get('text','')
            if 'log_id' in t: continue
            return json.loads(t)
    except: return None

def parse_items(result):
    items = []
    if not result: return items
    for gid, gitems in result.get('data', {}).items():
        for item in gitems:
            fields = {}
            for f in (item.get('moql_field_list') or []): 
                k = f['key']
                v = f.get('value')
                if isinstance(v, list) and len(v) > 0: v = v[0]
                if v is None: fields[k] = ''
                elif 'string_value' in v: fields[k] = v['string_value']
                elif 'double_value' in v: fields[k] = v['double_value']
                elif 'long_value' in v: fields[k] = v['long_value']
                elif 'key_label_value' in v: fields[k] = v['key_label_value']
                elif 'user_value' in v: fields[k] = v['user_value']
                else: fields[k] = ''
            items.append(fields)
    return items

COLUMNS = 'name, start_time, field_96a245, field_e1001d, field_51d592, work_item_id, owner, field_3f38c1, field_0e6822, field_290c00, field_5ed7ab, field_4a1b47'

def _pull_segment(start, end):
    """拉取 [start, end) 全部分录行；段内 OFFSET 翻页（自动避开 1000 行上限）"""
    where = [f"start_time >= \"{start}\""]
    if end:
        where.append(f"start_time < \"{end}\"")
    mql = f"SELECT {COLUMNS} FROM `销售管理`.`销售订单` WHERE " + " AND ".join(where) + " ORDER BY start_time DESC"
    items, seen, offset, empty = [], set(), 0, 0
    total = 0
    for _ in range(20):  # 20 页 = 1000 行，正好卡在 OFFSET 上限内
        result = mcp("search_by_mql", {"project_key": PK, "mql": f"{mql} LIMIT 50 OFFSET {offset}"})
        if not result:
            print(f"    ⚠️ 段 {start}~{end or 'now'} OFFSET={offset} 拉取失败", file=sys.stderr)
            break
        batch = parse_items(result)
        try:
            total = result["list"][0]["count"]
        except Exception:
            pass
        if not batch:
            empty += 1
            if empty >= 2:
                break
        else:
            empty = 0
            for it in batch:
                wid = str(it.get("work_item_id", ""))
                if wid and wid in seen:
                    continue
                if wid:
                    seen.add(wid)
                items.append(it)
        offset += 50
        if total and offset >= min(total, 1000):
            break
        _time.sleep(0.2)
    return items, total


def _pull_range(start, end):
    """把区间切成 7 天小段（每段 <1000 行），逐段拉取；段过大时自动对半再切"""
    def seg(s, e, depth=0):
        got, total = _pull_segment(s, e)
        if total and total > 1000 and depth < 2 and e:
            mid = (datetime.strptime(s, "%Y-%m-%d") + (datetime.strptime(e, "%Y-%m-%d") - datetime.strptime(s, "%Y-%m-%d")) / 2).strftime("%Y-%m-%d")
            if mid not in (s, e):
                print(f"    ↩︎ 段 {s}~{e} 超 1000 行({total})，切分为 {s}~{mid} + {mid}~{e}", file=sys.stderr)
                return seg(s, mid, depth + 1) + seg(mid, e, depth + 1)
        return got

    chunks, d = [], datetime.strptime(start, "%Y-%m-%d").date()
    while d < TODAY:
        nxt = min(d + timedelta(days=7), TODAY)
        chunks.append((d.isoformat(), nxt.isoformat() if nxt < TODAY else None))
        d = nxt
    out = []
    for s, e in chunks:
        got = seg(s, e)
        out.extend(got)
        print(f"    段 {s}~{e or 'now'}: {len(got)} 行", file=sys.stderr)
    return out


all_items = []
seen = set()
print(f"[{datetime.now().strftime('%H:%M:%S')}] 拉取 {START}~ 销售订单（按日期分段，避开 OFFSET≤1000 上限）...")
for i in _pull_range(START, None):
    wid = str(i.get("work_item_id", ""))
    if wid and wid in seen:
        continue
    if wid:
        seen.add(wid)
    all_items.append(i)
print(f"  {len(all_items)} 行（已按 work_item_id 全局去重）")

print(f"  共 {len(all_items)} 条（按 work_item_id 去重后）")

# ── 获取每个订单的「下单人」角色 ──
print(f"[{datetime.now().strftime('%H:%M:%S')}] 查询订单角色（下单人）...")
ROLE_CACHE_FILE = os.path.join(BASE, 'order_role_cache.json')
role_cache = {}
if os.path.exists(ROLE_CACHE_FILE):
    try:
        role_cache = json.load(open(ROLE_CACHE_FILE, encoding='utf-8'))
    except: pass

# 按单号去重后查询
unique_orders = []
seen_oid2 = set()
for o in all_items:
    oid = o.get('name', '')
    if oid not in seen_oid2:
        seen_oid2.add(oid)
        unique_orders.append(o)

order_roles = {}  # orderId → {name_cn, email}
uncached = [o for o in unique_orders if o.get('name') not in role_cache]
if uncached:
    print(f"  需查询 {len(uncached)} 个订单的角色（已缓存 {len(unique_orders)-len(uncached)}）...")
    for i, o in enumerate(uncached):
        wid = str(o.get('work_item_id', ''))
        oid = o.get('name', '')
        if not wid:
            role_cache[oid] = {}
            continue
        try:
            result = mcp("get_workitem_brief", {
                "project_key": PK,
                "work_item_id": wid
            })
            # 从 role_members 中提取「下单人」
            role_members = (result or {}).get('work_item_attribute', {}).get('role_members', [])
            xdr = {}
            for rm in role_members:
                if rm.get('name') == '下单人':
                    members = rm.get('members', [])
                    if members:
                        m = members[0]
                        xdr = {'name_cn': m.get('name', ''), 'email': m.get('email', '')}
                    break
            role_cache[oid] = xdr
        except Exception as e:
            role_cache[oid] = {}
        if (i + 1) % 20 == 0:
            print(f"    {i+1}/{len(uncached)}...")
        if i < len(uncached) - 1:
            _time.sleep(0.3)  # 限速
    # 写回缓存
    with open(ROLE_CACHE_FILE, 'w', encoding='utf-8') as f:
        json.dump(role_cache, f, ensure_ascii=False)
    print(f"  角色查询完成，缓存已更新")

# 汇总
order_roles = {oid: role_cache.get(oid, {}) for oid in seen_oid2}
role_hits = sum(1 for v in order_roles.values() if v)
print(f"  下单人角色命中: {role_hits}/{len(order_roles)}", file=sys.stderr)

# 旧 sales_map 作为兜底
cust_file = os.path.join(BASE, 'mcp_customers.json')
sales_map = {}
if os.path.exists(cust_file):
    custs = json.load(open(cust_file, encoding='utf-8')).get('customers', [])
    for c in custs:
        owner = c.get('owner', '')
        name = c.get('name', '')
        if isinstance(owner, dict) and owner.get('email') and '@biori.com' in owner.get('email', ''):
            sales_map[name] = owner
erp_file = os.path.join(BASE, 'pricing', '_key_customers.json')
if os.path.exists(erp_file):
    erp = json.load(open(erp_file, encoding='utf-8'))
    for c in erp:
        sp = c.get('salesperson', '')
        if sp and c.get('name') and c['name'] not in sales_map:
            sales_map[c['name']] = {'name_cn': sp, 'email': ''}
print(f"  兜底销售员映射: {len(sales_map)} 客户", file=sys.stderr)

# 转为 shipment_pipeline.py 期望的格式（按单号去重，保留第一条）
output = []
seen_oid = set()
for o in all_items:
    customer = o.get('field_e1001d', '')
    if isinstance(customer, dict):
        customer = customer.get('label', '')
    oid = o.get('name', '')
    if oid in seen_oid:
        continue
    seen_oid.add(oid)
    # 下单人：优先从订单角色取，兜底用客户→销售员映射
    role = order_roles.get(oid, {})
    if role:
        owner_name = role.get('name_cn', '')
        owner_email = role.get('email', '')
    else:
        sales = sales_map.get(customer, {})
        owner_name = sales.get('name_cn', sales.get('name_en', ''))
        owner_email = sales.get('email', '')
    output.append({
        '单据编号': o.get('name', ''),
        '购货单位#': customer,
        '销售订单关联客户': customer,
        '创建时间': o.get('start_time', ''),
        '物流单号': o.get('field_96a245', ''),
        '金额': o.get('field_51d592', 0),
        '工作项id': str(o.get('work_item_id', '')),
        '创建者': owner_name,
        '创建者邮箱': owner_email,
        '收货人联系电话#': o.get('field_3f38c1', ''),
        '收货人姓名': o.get('field_0e6822', ''),
        '收货地址': o.get('field_290c00', ''),
        '销售部门': (lambda v: (v.get('label','') if isinstance(v, dict) else str(v)) if v else '')(o.get('field_5ed7ab','')),
        '合同编号#': o.get('field_4a1b47', ''),
    })

with open(OUTPUT, 'w', encoding='utf-8') as f:
    json.dump(output, f, ensure_ascii=False)

print(f"✅ 写入 {OUTPUT} ({len(output)} 条)")