#!/usr/bin/env python3
"""
订单汇总 A–X 全量镜像刷新
触发：Step2（Cursor Step1 完成后 @小溪）
归属：小溪 (xiaoxi)

进表条件：K=是 · O>0 · 非全额退(P空或P<O) · L≥C
全额退 → 整行不进订单表（销售行清 K/O/P/Q）
镜像 A–V 原样 + W 渠道归属 + X=1

分工约定见 docs/bot-step2-schedule-and-orders.md
"""
import json, time, re, sys, requests, psycopg2
from datetime import datetime
from feishu_sheet_utils import FeishuSheetWriter

# ── 配置 ──
APP_ID = "cli_a929ae22e0b8dcc8"
APP_SECRET = "OtFjMy7p3qE3VvLbMdcWidwgHOnGD4FJ"
SPREADSHEET_TOKEN = "NoZqsFi47hIOHEt9j8WcfRtbnug"
SALES_SHEETS = {"f975f0": "吴迪", "qJF4I": "小龙", "qJF4J": "成都"}
SUMMARY_SHEET = "2smjwA"

def _get_pg_password():
    import os
    secrets_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), "..", "secrets.env")
    with open(secrets_path) as f:
        for line in f:
            if line.startswith("PG_ONLINE_PASSWORD="):
                return line.strip().split("=", 1)[1].strip('"').strip("'")
    raise RuntimeError("PG_ONLINE_PASSWORD not found in secrets.env")

PG_CONFIG = {
    "host": "bj-postgres-16pob4sg.sql.tencentcdb.com", "port": 28591,
    "user": "ai_member", "password": _get_pg_password(), "database": "vala_bi",
}

GOODS_MAP = {
    57: "瓦拉英语level1·单季", 60: "瓦拉英语level1", 63: "瓦拉英语level1·单季",
    31: "瓦拉英语年包", 32: "瓦拉英语单季度包", 33: "瓦拉英语level2", 54: "瓦拉英语季度包",
    61: "瓦拉英语level1+2",
}

# 达人昵称关键词
DAREN_NICKNAMES = ["晚柠", "学霸", "念妈", "神奇瓜妈", "三人行", "老王"]


def get_token():
    r = requests.post("https://open.feishu.cn/open-apis/auth/v3/tenant_access_token/internal",
                      json={"app_id": APP_ID, "app_secret": APP_SECRET}, timeout=15)
    return r.json()["tenant_access_token"]


def read_sheet(token, sheet_id, range_str):
    url = f"https://open.feishu.cn/open-apis/sheets/v2/spreadsheets/{SPREADSHEET_TOKEN}/values/{sheet_id}!{range_str}?valueRenderOption=ToString"
    r = requests.get(url, headers={"Authorization": f"Bearer {token}"}, timeout=30)
    data = r.json()
    if data.get("code") != 0:
        print(f"Error reading {sheet_id}: {data}")
        return []
    return data["data"]["valueRange"]["values"]


def put_values(token, sheet_id, range_str, values, retries=3):
    url = f"https://open.feishu.cn/open-apis/sheets/v2/spreadsheets/{SPREADSHEET_TOKEN}/values"
    body = {"valueRange": {"range": f"{sheet_id}!{range_str}", "values": values}}
    for attempt in range(retries):
        r = requests.put(url, headers={
            "Authorization": f"Bearer {token}",
            "Content-Type": "application/json"
        }, json=body, timeout=30)
        result = r.json()
        if result.get("code") == 0:
            return True
        print(f"  Retry {attempt+1} for {range_str}: {result.get('msg','')}")
        time.sleep(1)
    print(f"  FAILED {range_str}")
    return False


def parse_date(s):
    """Parse date string to (year, month, day) tuple."""
    s = str(s).strip()
    if not s:
        return None
    # YYYY-MM-DD
    m = re.match(r'(\d{4})-(\d{1,2})-(\d{1,2})', s)
    if m:
        return (int(m.group(1)), int(m.group(2)), int(m.group(3)))
    # M月D日
    m = re.match(r'(\d{1,2})月(\d{1,2})日', s)
    if m:
        return (2026, int(m.group(1)), int(m.group(2)))
    # YYYY/M/D
    m = re.match(r'(\d{4})/(\d{1,2})/(\d{1,2})', s)
    if m:
        return (int(m.group(1)), int(m.group(2)), int(m.group(3)))
    return None


def date_le(a, b):
    """Return True if date a <= date b."""
    if a is None or b is None:
        return False
    return a <= b


# W列渠道归属分类规则 [王虹茗确认 2026-06-15]
def classify_w_channel(m_channel, sales_name=""):
    """
    W 渠道归属，基于 M 成交渠道 + 销售昵称。
    端内: 精确匹配 3 个渠道
    销转: sales-adp-*
    达人: newmedia-daren-* / newmedia-dianpu-wwxx-0-0 / 昵称含达人关键词
    直购: 其余全部
    """
    m = str(m_channel).strip() if m_channel else ""

    # 达人（昵称关键词优先，保持原有逻辑）
    if any(nick in str(sales_name) for nick in DAREN_NICKNAMES):
        return "达人"

    if not m:
        return "直购"

    if m in ("app-active-h5-0-0", "app-sales-bj-qhm-0", "app-sales-bj-wd-0"):
        return "端内"
    if m.startswith("sales-adp-"):
        return "销转"
    if m.startswith("newmedia-daren-") or m == "newmedia-dianpu-wwxx-0-0":
        return "达人"
    # 其余: dianpu(不含wwxx) + partner/stream/miniprogram/jingxuan/空/shuadan等 → 直购
    return "直购"


def phone_match(sheet_phone, db_tel):
    """Match sheet phone number against DB tel (masked like 138****4503)."""
    if not sheet_phone or not db_tel:
        return False
    sheet_phone = str(sheet_phone).strip()
    db_tel = str(db_tel).strip()
    if sheet_phone == db_tel:
        return True
    if "****" in db_tel:
        parts = db_tel.split("****")
        if len(parts) == 2:
            prefix, suffix = parts
            if sheet_phone.startswith(prefix) and sheet_phone.endswith(suffix):
                return True
    return False


def main():
    print(f"[{datetime.now():%Y-%m-%d %H:%M:%S}] 订单汇总全量刷新 启动")
    token = get_token()

    # ── Step 1: 读取销售三表（含 X/Y 列）──
    all_rows = []
    for sid, name in SALES_SHEETS.items():
        print(f"Reading {name}...")
        vals = read_sheet(token, sid, "A3:Y10000")
        filtered = []
        for i, row in enumerate(vals):
            while len(row) < 25:
                row.append("")
            b = str(row[1]).strip() if row[1] else ""
            e = str(row[4]).strip() if row[4] else ""
            h = str(row[7]).strip() if row[7] else ""
            if b or e or h:
                filtered.append({
                    "sid": sid, "name": name, "row": i + 3,
                    "raw": row[:25],  # A-Y
                })
        print(f"  {len(filtered)} non-empty rows")
        all_rows.extend(filtered)

    print(f"Total rows: {len(all_rows)}")

    # ── Step 2: 筛选进订单汇总的行 ──
    # 条件：X=1 (gate)
    order_rows = []
    for r in all_rows:
        raw = r["raw"]
        # X 列 (index 23) = 1
        try:
            x_val = int(float(raw[23])) if len(raw) > 23 and raw[23] not in (None, "") else 0
        except (ValueError, TypeError):
            x_val = 0
        if x_val != 1:
            continue

        order_rows.append(r)

    print(f"Order rows after filter: {len(order_rows)}")

    # ── Step 2.5: 去重（同 UID 多表出现，保留行号最小）──
    uid_best = {}
    for r in order_rows:
        raw = r["raw"]
        uid = str(raw[7]).strip() if len(raw) > 7 and raw[7] not in (None, "") else ""
        try:
            uid = str(int(float(raw[7])))
        except:
            pass
        if not uid:
            continue
        if uid not in uid_best or r["row"] < uid_best[uid][0]:
            uid_best[uid] = (r["row"], r)
    order_rows = [v[1] for v in uid_best.values()]
    print(f"  After UID dedup: {len(order_rows)} rows")

    # ── Step 3: 按 K 下单日降序 ──
    order_rows.sort(key=lambda r: str(r["raw"][10]) if len(r["raw"]) > 10 and r["raw"][10] else "", reverse=True)

    # ── Step 4: 构建 A–W 行（A-V 镜像 + W=渠道=三表Y + 无X列）──
    summary_rows = []
    for r in order_rows:
        raw = r["raw"]
        # A–V 原样镜像 (indices 0-21)
        new_row = list(raw[:22])

        # W: 渠道归属 = 三表 Y 列 (index 24)
        y_val = str(raw[24]).strip() if len(raw) > 24 and raw[24] not in (None, "") else ""
        new_row.append(y_val)

        summary_rows.append(new_row)

    print(f"Summary rows: {len(summary_rows)}")

    # ── Step 5: 写入订单汇总（使用安全写入工具，自动遵守 5000 格上限）──
    print("Writing to 订单汇总...")
    writer = FeishuSheetWriter(SPREADSHEET_TOKEN, token)

    # 先清空旧数据区（23 列 A-W，自动计算批大小 ≤ 4400 格/批）
    writer.clear(SUMMARY_SHEET, start_row=3, end_row=2000, cols=23)
    time.sleep(0.5)

    # 写入新数据（23 列 A-W，自动分批）
    total = len(summary_rows)
    writer.write(SUMMARY_SHEET, start_row=3, rows=summary_rows, cols=23)

    # ── Step 6: 清除多余旧行 ──
    existing = read_sheet(token, SUMMARY_SHEET, "A3:A4000")
    old_count = len([r for r in existing if r and any(c for c in r if c)])
    if old_count > total:
        writer.clear(SUMMARY_SHEET, start_row=3 + total, end_row=3 + old_count - 1, cols=23)

    print(f"[{datetime.now():%Y-%m-%d %H:%M:%S}] ✅ 订单汇总刷新完成")


if __name__ == "__main__":
    main()