#!/usr/bin/env python3
"""一次性修复：
1. 每日线索底部加月度合计行
2. 落单渠道分布重构（合计在前，先总GMV再拆渠道）
3. 销转总览 GMV列格式改为金额
4. 过程数据阶段上课人数改为累计口径 + 5月Tom补全
"""

import json, requests, os, time, sys

CRED_DIR = "/root/.openclaw/credentials/xiaoxi"
SPREADSHEET_TOKEN = "NoZqsFi47hIOHEt9j8WcfRtbnug"

def get_token():
    with open(os.path.join(CRED_DIR, "config.json")) as f:
        cfg = json.load(f)
    resp = requests.post(
        "https://open.feishu.cn/open-apis/auth/v3/tenant_access_token/internal",
        json={"app_id": cfg["apps"][0]["appId"], "app_secret": cfg["apps"][0]["appSecret"]},
        timeout=15)
    return resp.json()["tenant_access_token"]

token = get_token()

def write_values(sheet_id, range_str, values):
    resp = requests.put(
        f"https://open.feishu.cn/open-apis/sheets/v2/spreadsheets/{SPREADSHEET_TOKEN}/values",
        headers={"Authorization": f"Bearer {token}", "Content-Type": "application/json"},
        json={"valueRange": {"range": range_str, "values": values}},
        params={"valueInputOption": "USER_ENTERED"},
        timeout=60)
    result = resp.json()
    ok = result.get("code") == 0
    if not ok:
        print(f"  ❌ {range_str}: code={result.get('code')} msg={result.get('msg')}")
    else:
        print(f"  ✅ {range_str}")
    return ok

# ============================================================
# 1. 每日线索 底部加月度合计行 (row 96-99)
# ============================================================
print("=== 1. 每日线索 月度合计 ===")
monthly_totals = [
    ["3月合计", '=SUMIF(A2:A95,"3月*",B2:B95)', '=SUMIF(A2:A95,"3月*",C2:C95)',
     '=SUMIF(A2:A95,"3月*",D2:D95)', '=SUMIF(A2:A95,"3月*",E2:E95)', '=SUM(F96:I96)'],
    ["4月合计", '=SUMIF(A2:A95,"4月*",B2:B95)', '=SUMIF(A2:A95,"4月*",C2:C95)',
     '=SUMIF(A2:A95,"4月*",D2:D95)', '=SUMIF(A2:A95,"4月*",E2:E95)', '=SUM(F97:I97)'],
    ["5月合计", '=SUMIF(A2:A95,"5月*",B2:B95)', '=SUMIF(A2:A95,"5月*",C2:C95)',
     '=SUMIF(A2:A95,"5月*",D2:D95)', '=SUMIF(A2:A95,"5月*",E2:E95)', '=SUM(F98:I98)'],
    ["6月合计", '=SUMIF(A2:A95,"6月*",B2:B95)', '=SUMIF(A2:A95,"6月*",C2:C95)',
     '=SUMIF(A2:A95,"6月*",D2:D95)', '=SUMIF(A2:A95,"6月*",E2:E95)', '=SUM(F99:I99)'],
]
write_values("1ANW6c", "1ANW6c!A96:F99", monthly_totals)

# ============================================================
# 2. 落单渠道分布重构
# 新布局: 月份 | 销售 | 总GMV | 总订单 | 销转GMV | 销转订单 | 端内GMV | 端内订单 | 直购GMV | 直购订单
# 合计行放在每个月的第一行
# ============================================================
print("\n=== 2. 落单渠道分布重构 ===")

# 先写新表头
new_header = [["月份", "销售", "总GMV", "总订单", "销转渠道-GMV", "销转渠道-订单",
               "端内-GMV", "端内-订单", "直购-GMV", "直购-订单"]]
write_values("3dIXcs", "3dIXcs!A1:J1", new_header)

# 新布局: 每个月 合计 → 小龙 → 吴迪 → Bob → Tom
months_list = ["3月", "4月", "5月", "6月"]
all_sales = {
    "3月": ["合计", "小龙", "Bob", "Tom"],
    "4月": ["合计", "小龙", "吴迪", "Bob", "Tom"],
    "5月": ["合计", "小龙", "吴迪", "Bob", "Tom"],
    "6月": ["合计", "小龙", "吴迪", "Bob", "Tom"],
}

row_idx = 2
for month in months_list:
    for sales in all_sales[month]:
        if sales == "合计":
            total_gmv = f'=SUMIFS(\'订单汇总\'!G:G,\'订单汇总\'!B:B,"{month}*")'
            total_orders = f'=COUNTIFS(\'订单汇总\'!B:B,"{month}*")'
            xz_gmv = f'=SUMIFS(\'订单汇总\'!G:G,\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!J:J,"销转")'
            xz_orders = f'=COUNTIFS(\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!J:J,"销转")'
            dn_gmv = f'=SUMIFS(\'订单汇总\'!G:G,\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!J:J,"端内")'
            dn_orders = f'=COUNTIFS(\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!J:J,"端内")'
            zg_gmv = f'=SUMIFS(\'订单汇总\'!G:G,\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!J:J,"直购")'
            zg_orders = f'=COUNTIFS(\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!J:J,"直购")'
        else:
            total_gmv = f'=SUMIFS(\'订单汇总\'!G:G,\'订单汇总\'!A:A,"{sales}",\'订单汇总\'!B:B,"{month}*")'
            total_orders = f'=COUNTIFS(\'订单汇总\'!A:A,"{sales}",\'订单汇总\'!B:B,"{month}*")'
            xz_gmv = f'=SUMIFS(\'订单汇总\'!G:G,\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!A:A,"{sales}",\'订单汇总\'!J:J,"销转")'
            xz_orders = f'=COUNTIFS(\'订单汇总\'!A:A,"{sales}",\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!J:J,"销转")'
            dn_gmv = f'=SUMIFS(\'订单汇总\'!G:G,\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!A:A,"{sales}",\'订单汇总\'!J:J,"端内")'
            dn_orders = f'=COUNTIFS(\'订单汇总\'!A:A,"{sales}",\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!J:J,"端内")'
            zg_gmv = f'=SUMIFS(\'订单汇总\'!G:G,\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!A:A,"{sales}",\'订单汇总\'!J:J,"直购")'
            zg_orders = f'=COUNTIFS(\'订单汇总\'!A:A,"{sales}",\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!J:J,"直购")'

        row = [[month, sales, total_gmv, total_orders, xz_gmv, xz_orders,
                dn_gmv, dn_orders, zg_gmv, zg_orders]]
        write_values("3dIXcs", f"3dIXcs!A{row_idx}:J{row_idx}", row)
        row_idx += 1
        time.sleep(0.3)

# 清除旧数据（row 20+ 如果有的话）
print("  清除旧数据行...")
write_values("3dIXcs", "3dIXcs!A21:J30", [[None]*10]*10)

# ============================================================
# 3. 销转总览 GMV列(F) 格式改为金额
# ============================================================
print("\n=== 3. 销转总览 GMV格式 ===")
# 飞书 API 设置单元格格式
resp = requests.put(
    f"https://open.feishu.cn/open-apis/sheets/v2/spreadsheets/{SPREADSHEET_TOKEN}/style",
    headers={"Authorization": f"Bearer {token}", "Content-Type": "application/json"},
    json={
        "appendStyle": {
            "range": "3fR34k!F2:F20",
            "style": {
                "formatter": "0.00"
            }
        }
    },
    timeout=30)
print(f"  GMV格式: code={resp.json().get('code')} msg={resp.json().get('msg')}")

# 也设置 G(退款) H(GSV) I(消耗) J(达人GMV) K(佣金) L(总成本) 为金额格式
for col in ["G", "H", "I", "J", "K", "L"]:
    resp = requests.put(
        f"https://open.feishu.cn/open-apis/sheets/v2/spreadsheets/{SPREADSHEET_TOKEN}/style",
        headers={"Authorization": f"Bearer {token}", "Content-Type": "application/json"},
        json={
            "appendStyle": {
                "range": f"3fR34k!{col}2:{col}20",
                "style": {"formatter": "#,##0"}
            }
        },
        timeout=30)
    time.sleep(0.2)

# E列(转化率) 百分比格式
resp = requests.put(
    f"https://open.feishu.cn/open-apis/sheets/v2/spreadsheets/{SPREADSHEET_TOKEN}/style",
    headers={"Authorization": f"Bearer {token}", "Content-Type": "application/json"},
    json={
        "appendStyle": {
            "range": "3fR34k!E2:E20",
            "style": {"formatter": "0.00%"}
        }
    },
    timeout=30)
print(f"  转化率格式: code={resp.json().get('code')}")

print("\n✅ 任务1-3完成！")
print("任务4-5(过程数据)需要查询数据库，继续执行...")
