#!/bin/bash
# 渠道每日订单统计脚本
# 执行周期：每天早上 9:00 自动运行
# 功能：统计各渠道每日订单量 → 导出Excel → 发送到指定飞书群
set -euo pipefail

REPORT_DATE=$(date +%Y-%m-%d)
REPORT_FILE="/tmp/openclaw/渠道每日订单统计_${REPORT_DATE}.xlsx"
CSV_FILE="/tmp/openclaw/channel_order_temp.csv"
TARGET_CHAT_ID="oc_af81515caefe26918736ad1941286224"
LOG_FILE="/var/log/weekly_channel_order_report.log"

log() { echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE" >&2; }

log "========== 开始执行渠道每日订单统计任务 =========="

# 加载环境变量
source /root/.openclaw/workspace/secrets.env

# 确保 PATH 包含必要工具
export PATH="/usr/bin:/bin:/usr/local/bin:/root/.nvm/versions/node/v24.14.0/bin:$PATH"

# 1. 执行统计查询导出CSV
log "执行SQL查询..."
PGPASSWORD="${PG_ONLINE_PASSWORD}" psql \
  -h bj-postgres-16pob4sg.sql.tencentcdb.com \
  -p 28591 \
  -U ai_member \
  -d vala_bi \
  -A -F "," \
  -o "${CSV_FILE}" \
  -c "
select 
    key_from as \"购课渠道\",
    goods_name as \"购买课包\",
    created_at as \"购课日期\",
    count(distinct trade_no) as \"总订单数\",
    count(distinct case when order_status = 3 then trade_no else NULL end) as \"未退费订单数\",
    count(distinct case when order_status = 4 then trade_no else NULL end) as \"退费订单数\"
from
(
    select id
    from bi_vala_app_account
    where status = 1
    group by id
) as a
left join 
(
    select 
        account_id,
        trade_no,
        date(created_at) as created_at,
        order_status,
        key_from,
        goods_name
    from bi_vala_order
    group by 
        account_id,
        trade_no,
        date(created_at),
        order_status,
        key_from,
        goods_name
) as b on a.id = b.account_id
where key_from is not null
group by 
    key_from,
    goods_name,
    created_at
order by 
    created_at desc,
    总订单数 desc;
" 2>> "$LOG_FILE"

if [ ! -s "$CSV_FILE" ]; then
    log "ERROR: SQL查询结果为空"
    exit 1
fi
log "SQL查询完成，CSV大小: $(wc -c < "$CSV_FILE") bytes"

# 2. CSV转Excel
log "生成Excel..."
python3 -c "import pandas as pd; pd.read_csv('${CSV_FILE}').to_excel('${REPORT_FILE}', index=False)" 2>> "$LOG_FILE"
if [ ! -f "$REPORT_FILE" ]; then
    log "ERROR: Excel生成失败"
    exit 1
fi
log "Excel生成完成，大小: $(wc -c < "$REPORT_FILE") bytes"

# 3. 获取飞书 token
log "获取飞书访问令牌..."
APP_ID=$(jq -r '.apps[0].appId' /root/.openclaw/credentials/xiaoxi/config.json)
APP_SECRET=$(jq -r '.apps[0].appSecret' /root/.openclaw/credentials/xiaoxi/config.json)
TOKEN=$(curl -s -X POST "https://open.feishu.cn/open-apis/auth/v3/tenant_access_token/internal" \
  -H "Content-Type: application/json" \
  -d "{\"app_id\":\"$APP_ID\",\"app_secret\":\"$APP_SECRET\"}" \
  | jq -r '.tenant_access_token')

if [ -z "$TOKEN" ] || [ "$TOKEN" = "null" ]; then
    log "ERROR: 获取飞书token失败"
    exit 1
fi

# 4. 先发文本消息
log "发送文本通知..."
TEXT_PAYLOAD=$(jq -n --arg rid "$TARGET_CHAT_ID" --arg text "📊 渠道每日订单统计（${REPORT_DATE}）" \
  '{receive_id: $rid, msg_type: "text", content: ({text: $text} | tostring)}')
TEXT_RESP=$(curl -s -X POST "https://open.feishu.cn/open-apis/im/v1/messages?receive_id_type=chat_id" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d "$TEXT_PAYLOAD")
TEXT_CODE=$(echo "$TEXT_RESP" | jq -r '.code')
if [ "$TEXT_CODE" != "0" ]; then
    log "WARN: 文本消息发送失败: $TEXT_RESP"
fi

# 5. 上传Excel文件（带重试）
upload_file() {
    local attempt=1
    local max_attempts=3
    while [ $attempt -le $max_attempts ]; do
        log "上传文件 (第${attempt}次尝试)..."
        FILE_KEY=$(curl -s --max-time 30 -X POST "https://open.feishu.cn/open-apis/im/v1/files" \
          -H "Authorization: Bearer $TOKEN" \
          -F "file_type=xls" \
          -F "file_name=渠道每日订单统计_${REPORT_DATE}.xlsx" \
          -F "file=@${REPORT_FILE}" | jq -r '.data.file_key')
        if [ -n "$FILE_KEY" ] && [ "$FILE_KEY" != "null" ]; then
            echo "$FILE_KEY"
            return 0
        fi
        log "WARN: 第${attempt}次文件上传失败，等待3秒后重试..."
        sleep 3
        attempt=$((attempt + 1))
    done
    return 1
}

FILE_KEY=$(upload_file)
if [ -z "$FILE_KEY" ] || [ "$FILE_KEY" = "null" ]; then
    log "ERROR: 文件上传失败（已重试3次），文件保留在: ${REPORT_FILE}"
    exit 1
fi
log "文件上传成功，file_key: ${FILE_KEY}"

# 6. 发送文件消息到群（带重试）
send_file_msg() {
    local attempt=1
    local max_attempts=3
    local fk="$1"
    while [ $attempt -le $max_attempts ]; do
        log "发送文件消息 (第${attempt}次尝试)..."
        local payload
        payload=$(jq -n --arg rid "$TARGET_CHAT_ID" --arg fk "$fk" \
          '{receive_id: $rid, msg_type: "file", content: ({file_key: $fk} | tostring)}')
        local resp
        resp=$(curl -s --max-time 15 -X POST "https://open.feishu.cn/open-apis/im/v1/messages?receive_id_type=chat_id" \
          -H "Authorization: Bearer $TOKEN" \
          -H "Content-Type: application/json" \
          -d "$payload")
        local code
        code=$(echo "$resp" | jq -r '.code')
        if [ "$code" = "0" ]; then
            log "文件消息发送成功"
            return 0
        fi
        log "WARN: 第${attempt}次文件消息发送失败 (code=${code}): $resp"
        sleep 3
        attempt=$((attempt + 1))
    done
    return 1
}

if ! send_file_msg "$FILE_KEY"; then
    log "ERROR: 文件消息发送失败（已重试3次），文件保留在: ${REPORT_FILE}"
    exit 1
fi

# 7. 发送成功后清理临时文件
rm -f "${CSV_FILE}" "${REPORT_FILE}"
log "========== 任务执行完成 =========="
