#!/usr/bin/env python3
"""诊断：订单表按日期分段拉取统计（避免 MCP OFFSET<=1000 截断）
用法: python3 _orders_probe.py [start_date]   默认 2026-06-12
输出统计，不写任何正式数据文件。
"""
import json, re, sys, time, urllib.request
from datetime import date, datetime, timedelta
from collections import Counter, defaultdict

MCP_URL = "https://project.feishu.cn/mcp_server/v1"
MCP_TOKEN = "m-abfb29e8-3104-434f-9944-8d0bb592f8cd"
SALES_PK = "6593cd71471290e3cc6be6e6"
TYPE_ORDER = "662612aea6bb7089fea044ef"
COLUMNS = 'name, start_time, field_e1001d, field_51d592, field_96a245, work_item_id'


def mcp_call(method, args, timeout=120):
    payload = {"jsonrpc": "2.0", "id": 1, "method": "tools/call",
               "params": {"name": method, "arguments": args}}
    for attempt in range(5):
        try:
            req = urllib.request.Request(
                MCP_URL, data=json.dumps(payload).encode("utf-8"),
                headers={"X-Mcp-Token": MCP_TOKEN, "Content-Type": "application/json"})
            with urllib.request.urlopen(req, timeout=timeout) as r:
                raw = r.read().decode("utf-8")
        except Exception as e:
            if attempt == 4:
                print(f"  ❌ HTTP 5次失败: {e}")
                return None
            time.sleep(5 * (2 ** attempt))
            continue
        if raw.startswith("data:"):
            raw = raw.split("\n")[0].replace("data: ", "", 1)
        try:
            d = json.loads(raw)
        except Exception:
            if attempt == 4:
                return None
            time.sleep(8)
            continue
        if "error" in d or "result" not in d:
            if attempt == 4:
                return None
            time.sleep(10)
            continue
        for c in d["result"]["content"]:
            t = c.get("text", "")
            if not t or "log_id" in t:
                continue
            cleaned = re.sub(r"\nlog_id:.*$", "", t.strip())
            try:
                return json.loads(cleaned)
            except Exception:
                break
        if attempt == 4:
            return None
        time.sleep(10)
    return None


def parse_items(result):
    """返回 (items, total_count)"""
    items, total = [], 0
    if not result:
        return items, total
    try:
        total = result["list"][0]["count"]
    except Exception:
        pass
    for gid, gitems in (result.get("data") or {}).items():
        if not isinstance(gitems, list):
            continue
        for item in gitems:
            f = {}
            for fl in (item.get("moql_field_list") or []):
                k, v = fl["key"], fl.get("value")
                if isinstance(v, list) and v:
                    if isinstance(v[0], dict) and "label" in v[0]:
                        f[k] = v[0]["label"]
                        continue
                    v = v[0]
                if v is None:
                    f[k] = ""
                elif isinstance(v, dict):
                    for key in ("string_value", "double_value", "long_value"):
                        if key in v:
                            f[k] = v[key]
                            break
                    else:
                        if "key_label_value" in v:
                            f[k] = v["key_label_value"]
                        elif "user_value" in v:
                            f[k] = v["user_value"]
                        elif "user_value_list" in v:
                            f[k] = v["user_value_list"]
                        else:
                            f[k] = str(v)
                else:
                    f[k] = str(v)
            items.append(f)
    return items, total


def pull_window(start, end):
    """拉取 [start, end) 全部分录行（OFFSET 安全，段内最多 40 页）"""
    where = [f"start_time >= '{start}'"]
    if end:
        where.append(f"start_time < '{end}'")
    mql = f"SELECT {COLUMNS} FROM `销售管理`.`{TYPE_ORDER}` WHERE " + " AND ".join(where) + " ORDER BY start_time DESC"
    items, seen, offset, empty = [], set(), 0, 0
    for _ in range(40):
        r = mcp_call("search_by_mql", {"project_key": SALES_PK, "mql": f"{mql} LIMIT 50 OFFSET {offset}", "session_id": ""})
        if r is None:
            print(f"  ⚠️ 段 {start}~{end} OFFSET={offset} 失败")
            break
        batch, total = parse_items(r)
        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
        time.sleep(0.2)
        if total and offset >= min(total, 1000):
            break  # OFFSET 上限
    return items


def main():
    start = sys.argv[1] if len(sys.argv) > 1 else "2026-06-12"
    today = date.today()
    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
    print(f"窗口 {start} ~ {today}，分 {len(chunks)} 段（7天/段）")
    rows = []
    for s, e in chunks:
        got = pull_window(s, e)
        print(f"  段 {s}~{e or 'now'}: {len(got)} 行")
        rows.extend(got)
    # 全局去重
    seen, uniq = set(), []
    for r in rows:
        wid = str(r.get("work_item_id", ""))
        if wid and wid in seen:
            continue
        if wid:
            seen.add(wid)
        uniq.append(r)
    orders = {}
    for r in uniq:
        oid = r.get("name", "")
        orders.setdefault(oid, r)
    tn = [o for o in orders.values() if (o.get("field_96a245") or "").strip()]
    print(f"\n分录行(去重) {len(uniq)} | 唯一单据 {len(orders)} | 含物流单号 {len(tn)}")
    m = Counter((o.get("start_time") or "")[:7] for o in orders.values())
    print("按月唯一单据:", dict(sorted(m.items())))
    mt = Counter((o.get("start_time") or "")[:7] for o in tn)
    print("按月含物流单号:", dict(sorted(mt.items())))
    amt = sum(float(o.get("field_51d592") or 0) for o in orders.values())
    print(f"价税合计: {amt:,.0f}")
    json.dump(list(orders.values()), open("/tmp/orders_probe.json", "w"), ensure_ascii=False)


if __name__ == "__main__":
    main()