#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""宝锐礼品下单 · 每日汇总邮件（每天 20:00 触发）

流程：
1. 用 tcb db nosql execute 拉取 CloudBase orders 当天（date=YYYY-MM-DD）订单
2. 按销售分组汇总
3. 每销售一封邮件：收件人 liuxinyuan@biori.com，抄送对应区域经理
4. 无订单时静默退出（不打搅）

SMTP 配置在 脚本/smtp_config.json（网易企业邮箱授权码需填入）
"""
import subprocess, json, sys, os, datetime, smtplib
from email.mime.text import MIMEText
from email.header import Header
from email.utils import formataddr

BASE = "/Users/liuxinyuan/Desktop/Hermes输出-工作类"
TEAM_FILE = os.path.join(BASE, "数据", "sales_team.json")
SMTP_CONF = os.path.join(BASE, "脚本", "smtp_config.json")
ENV = "bier-sales-d0gatbvlx288724e9"
RECIPIENT = "liuxinyuan@biori.com"
TCB = "/Users/liuxinyuan/.npm-global/lib/node_modules/@cloudbase/cli/dist/standalone/cli.js"  # node 直调 cli.js（tcb symlink 在 Node v24 下静默失效，必须 node 调）


def tcb_exec(command_obj):
    """执行 tcb db nosql execute，返回解析后的 dict"""
    cmd = json.dumps([command_obj], ensure_ascii=False)
    r = subprocess.run(["node", TCB, "db", "nosql", "execute", "--command", cmd, "--json", "-e", ENV],
                       capture_output=True, text=True, timeout=60)
    raw = r.stdout
    start = raw.find("{")
    if start < 0:
        raise RuntimeError("tcb 输出解析失败: " + (r.stderr or raw)[:300])
    d = json.loads(raw[start:])
    if "error" in d:
        raise RuntimeError("tcb 查询出错: " + json.dumps(d["error"], ensure_ascii=False)[:300])
    return d


def to_num(v):
    """兼容 tcb 返回的 MongoDB 扩展 JSON 数值格式（如 {"$numberInt":"330"} / {"$numberDouble":"1.7"}）"""
    if isinstance(v, dict):
        for k in ("$numberInt", "$numberLong"):
            if k in v:
                return int(v[k])
        if "$numberDouble" in v:
            return float(v["$numberDouble"])
        return 0
    if isinstance(v, bool):
        return int(v)
    try:
        return float(v)
    except (TypeError, ValueError):
        return 0


def query_today_orders(today):
    cmd_obj = {
        "TableName": "orders",
        "CommandType": "QUERY",
        "Command": json.dumps({"find": "orders", "filter": {"date": today}, "limit": 500}),
    }
    d = tcb_exec(cmd_obj)
    rows = d.get("data", {}).get("results", [[]])[0]
    return rows or []


def send_mail(smtp_cfg, sales_name, region, orders, mgr_email):
    total = round(sum(to_num(o.get("total")) for o in orders), 2)
    d = datetime.date.today()
    lines = []
    lines.append(f"{sales_name}（{region}）今日礼品订单汇总")
    lines.append(f"日期：{d}    订单数：{len(orders)} 单    合计金额：¥{total:,.1f}")
    lines.append("=" * 46)
    for idx, o in enumerate(orders, 1):
        ts = str(o.get("created_at", ""))[:16].replace("T", " ")
        lines.append(f"【订单 {idx}】{ts}")
        for it in o.get("items", []):
            nm = it.get("name", "")
            qty = int(to_num(it.get("qty")))
            price = to_num(it.get("price"))
            lines.append(f"    · {nm} × {qty} = ¥{qty * price:,.1f}")
        if o.get("note"):
            lines.append(f"    备注：{o['note']}")
        lines.append(f"    小计：¥{to_num(o.get('total')):,.1f}")
        lines.append("-" * 46)
    lines.append(f"合计：¥{total:,.1f}")
    lines.append("")
    lines.append("（本邮件由宝锐礼品下单系统自动发送，请据此安排采购/发货。）")
    body = "\n".join(lines)

    subject = f"【宝锐礼品下单】{sales_name} 今日 {len(orders)} 单 · 合计¥{total:,.1f}"
    msg = MIMEText(body, "plain", "utf-8")
    msg["Subject"] = Header(subject, "utf-8")
    msg["From"] = formataddr((str(Header("宝锐礼品下单系统", "utf-8")), smtp_cfg["user"]))
    msg["To"] = RECIPIENT
    to_list = [RECIPIENT]
    if mgr_email and mgr_email != RECIPIENT and mgr_email != smtp_cfg["user"]:
        msg["Cc"] = mgr_email
        to_list.append(mgr_email)

    # 发送（SSL 端口 fallback：994 → 465 → 25）
    host_cfg = smtp_cfg.get("host", "smtphz.qiye.163.com")
    last_err = None
    for port in (994, 465, 25):
        try:
            if port == 25:
                s = smtplib.SMTP(host_cfg, port, timeout=30)
                s.starttls()
            else:
                s = smtplib.SMTP_SSL(host_cfg, port, timeout=30)
            s.login(smtp_cfg["user"], smtp_cfg["password"])
            s.sendmail(smtp_cfg["user"], to_list, msg.as_string())
            s.quit()
            return host_cfg, port
        except Exception as e:
            last_err = e
            try:
                s.quit()
            except Exception:
                pass
    raise RuntimeError(f"SMTP 发送失败（已尝试 994/465/25）：{last_err}")


def main():
    today = datetime.date.today().strftime("%Y-%m-%d")
    # 1) 先拉当天订单（无订单则静默，不依赖 SMTP 配置）
    orders = query_today_orders(today)
    if not orders:
        print(f"{today} 无礼品订单，静默。", file=sys.stderr)
        return

    # 2) SMTP 配置
    if not os.path.exists(SMTP_CONF):
        print("❌ 缺少 SMTP 配置：", SMTP_CONF, file=sys.stderr)
        sys.exit(1)
    smtp_cfg = json.load(open(SMTP_CONF, encoding="utf-8"))
    if not smtp_cfg.get("password") or smtp_cfg["password"] == "YOUR_AUTH_CODE":
        print("❌ SMTP 授权码未配置，请在", SMTP_CONF, "填入网易企业邮箱授权码", file=sys.stderr)
        sys.exit(1)

    # 3) 加载名单（sales_name → region / manager_email）
    team = json.load(open(TEAM_FILE, encoding="utf-8"))
    sales_map = {s["name"]: s for s in team.get("sales", [])}

    # 4) 按销售分组
    groups = {}
    for o in orders:
        name = o.get("sales_name") or o.get("sales_email") or "未知销售"
        groups.setdefault(name, []).append(o)

    # 5) 逐销售发邮件
    sent = 0
    for name, items in groups.items():
        info = sales_map.get(name, {})
        region = info.get("region", o.get("region", ""))
        mgr_email = info.get("manager_email")
        try:
            host, port = send_mail(smtp_cfg, name, region, items, mgr_email)
            cc = f"，抄送 {mgr_email}" if mgr_email else ""
            print(f"✅ {name}：{len(items)} 单 → {RECIPIENT}{cc}（via {host}:{port}）", file=sys.stderr)
            sent += 1
        except Exception as e:
            print(f"❌ {name} 发送失败：{e}", file=sys.stderr)

    print(f"今日礼品订单已推送：{sent}/{len(groups)} 位销售下单，邮件已发至 {RECIPIENT}（抄送对应区域经理）。")
    if sent < len(groups):
        sys.exit(1)


if __name__ == "__main__":
    main()