#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
OpenClaw Telegram 群組智能助理 v5
─────────────────────────────────────────
核心功能：
  • 群組自然對話，Gemini Flash 主引擎（2~5s）
  • Inline Keyboard 15 個功能入口
  • 全商品查詢帶出商品ID
  • AI 分析強制比對內部DB + 外部MCP情報

v5 新增（2026-04-16）：
  • 每日早報 08:30 / 晚報 21:00 / 週一週報
  • 異常偵測（4次/日，偏差>30%即告警）
  • 目標達成率（日/月目標設定）
  • 分類業績、同期比較（上週/上月）
  • 商品策略矩陣（加碼/機會/收割/觀察/持穩）
  • 商品健康分析（異常+策略分佈）
  • 趨勢圖 + 熱銷商品橫條圖（matplotlib）
  • 完整報表 PDF 下載（fpdf2 → reportlab → CSV）
"""

import os
import re
import threading
import requests
from datetime import datetime, timezone, timedelta
from flask import Blueprint, request, jsonify
from sqlalchemy import text

from database.manager import DatabaseManager
from services.logger_manager import SystemLogger
from services.mcp_context_service import (
    build_mcp_context, MCPRouter, query_mcp,
    get_tw_media_news, get_ecommerce_news, get_taiwan_trends,
    get_dcard_trends, get_youtube_trending, get_taiwan_weather,
    get_twbank_exchange_rates, get_upcoming_events,
)
try:
    from services.openclaw_learning_service import (
        build_rag_context, store_conversation, store_insight,
        update_feedback, get_learning_stats,
    )
    _LEARNING_ENABLED = True
except Exception as _le:
    _LEARNING_ENABLED = False
    sys_log = __import__('logging').getLogger('openclaw')
    sys_log.warning(f"[OCLearn] learning service unavailable: {_le}")
try:
    from services.pchome_crawler import (
        compare_product as pchome_compare,
        batch_compare_top as pchome_batch,
        save_matches as pchome_save,
        fmt_compare_msg as pchome_fmt_compare,
        fmt_daily_report as pchome_fmt_report,
        search_pchome as pchome_search,
        ensure_tables as pchome_ensure_tables,
    )
    _PCHOME_AVAILABLE = True
except ImportError:
    _PCHOME_AVAILABLE = False

# AI 引擎：Gemini Flash（主，2~5秒）→ NIM（備援，45~90秒）
GEMINI_API_KEY  = os.getenv('GEMINI_API_KEY', '')
GEMINI_BASE_URL = 'https://generativelanguage.googleapis.com/v1beta/models'
GEMINI_MODEL    = 'gemini-2.0-flash'

TAIPEI_TZ = timezone(timedelta(hours=8))
sys_log = SystemLogger("OpenClawBot").get_logger()

openclaw_bot_bp = Blueprint('openclaw_bot', __name__)

# ── Telegram retry 去重 (update_id 快取，最多保留 500 筆) ─────
_seen_update_ids: set = set()
_SEEN_MAX = 500

BOT_TOKEN       = os.getenv('OPENCLAW_BOT_TOKEN', '')
BOT_API_URL     = f"https://api.telegram.org/bot{BOT_TOKEN}"
ALLOWED_GROUP   = int(os.getenv('OPENCLAW_GROUP_ID', '-1003940688311'))
MOMO_BASE_URL   = os.getenv('MOMO_BASE_URL', 'https://mo.wooo.work')
NVIDIA_API_KEY  = os.getenv('NVIDIA_API_KEY', '')
NVIDIA_BASE_URL = 'https://integrate.api.nvidia.com/v1'
CHAT_MODEL      = 'deepseek-ai/deepseek-v3.2'
BOT_USERNAME    = '@OpenClawAwoooI_Bot'

# ── 存取控制白名單 ─────────────────────────────────────────────
# OPENCLAW_ALLOWED_USERS：逗號分隔的 Telegram user_id（整數）
# 空字串 = 只允許 ALLOWED_GROUP 群組，禁止所有私訊
# 例：'123456789,987654321'
_allowed_users_raw = os.getenv('OPENCLAW_ALLOWED_USERS', '')
ALLOWED_USERS: set = (
    {int(uid.strip()) for uid in _allowed_users_raw.split(',') if uid.strip().isdigit()}
    if _allowed_users_raw.strip() else set()
)

# ── fail-closed 統一授權檢查 ───────────────────────────────────
# 規則（任一滿足即通過，否則一律拒絕）：
#   1. group/supergroup 且 chat_id == ALLOWED_GROUP
#   2. private 且 user_id ∈ ALLOWED_USERS（env 未設 → 空 set → 全拒）
# channel / 未知 chat_type / 缺欄位 → 拒絕
# 修補 C3：callback handler 原本只擋 group/supergroup 不匹配，private 完全放行；
#          message handler `if ALLOWED_USERS and ...` 空 set 時整段失效。
def _is_authorized(chat_type: str, chat_id, user_id) -> bool:
    try:
        cid = int(chat_id) if chat_id is not None else None
        uid = int(user_id) if user_id is not None else None
    except (TypeError, ValueError):
        return False
    if chat_type in ('group', 'supergroup'):
        return cid == ALLOWED_GROUP
    if chat_type == 'private':
        return uid is not None and uid in ALLOWED_USERS
    return False

# ── 速率限制（每用戶每分鐘最多 30 次 AI 呼叫）──────────────────
import time as _time_mod
_rate_tracker: dict = {}   # {user_id: [timestamp, ...]}
_RATE_LIMIT_PER_MIN = 30   # 每分鐘上限
_RATE_WINDOW_SEC    = 60

def _check_rate_limit(user_id: int) -> bool:
    """回傳 True = 允許，False = 超過速率限制"""
    now = _time_mod.time()
    window = _rate_tracker.setdefault(user_id, [])
    # 清掉 60 秒以前的紀錄
    _rate_tracker[user_id] = [t for t in window if now - t < _RATE_WINDOW_SEC]
    if len(_rate_tracker[user_id]) >= _RATE_LIMIT_PER_MIN:
        return False
    _rate_tracker[user_id].append(now)
    return True

# 群組內回應觸發（包含這些字才回應；若為空則全部回應）
# 設為空 list = 所有訊息都回應
TRIGGER_KEYWORDS = []  # 空 = 全部回應（小龍蝦是專用業務群組）


# ── Telegram API ──────────────────────────────────────────────
def _tg(method: str, payload: dict):
    try:
        r = requests.post(f"{BOT_API_URL}/{method}", json=payload, timeout=10)
        if not r.ok:
            sys_log.warning(f"[OpenClawBot] {method} failed: {r.text[:200]}")
        return r.json()
    except Exception as e:
        sys_log.error(f"[OpenClawBot] {method} error: {e}")
        return {}


def _strip_markdown(text: str) -> str:
    """移除 Telegram Markdown v1 格式符號，轉為純文字（send fallback 用）"""
    import re
    # 移除 *bold*, _italic_, `code`, [link](url)
    text = re.sub(r'\*([^*]+)\*', r'\1', text)
    text = re.sub(r'_([^_]+)_', r'\1', text)
    text = re.sub(r'`([^`]+)`', r'\1', text)
    text = re.sub(r'\[([^\]]+)\]\([^)]+\)', r'\1', text)
    return text


def send_message(chat_id, text, reply_to=None, keyboard=None, parse_mode='Markdown'):
    """送出 Telegram 訊息。Markdown 解析失敗時自動降級為純文字重送。"""
    def _build_payload(txt, pm):
        p = {'chat_id': chat_id, 'text': txt, 'disable_web_page_preview': True}
        if pm:
            p['parse_mode'] = pm
        if reply_to:
            p['reply_to_message_id'] = reply_to
        if keyboard:
            p['reply_markup'] = {'inline_keyboard': keyboard}
        return p

    # 第一次嘗試（帶 parse_mode）
    result = _tg('sendMessage', _build_payload(text, parse_mode))
    if result.get('ok'):
        return result

    # Markdown 解析失敗 → 降級為純文字重送
    if parse_mode and not result.get('ok'):
        err = result.get('description', '')
        if 'parse' in err.lower() or 'entity' in err.lower() or 'can\'t find' in err.lower():
            sys_log.warning(f"[OpenClawBot] Markdown parse failed, retrying as plain text")
            plain = _strip_markdown(text)
            result2 = _tg('sendMessage', _build_payload(plain, None))
            if result2.get('ok'):
                return result2

    # 最後嘗試：截斷過長文字
    if len(text) > 4000:
        sys_log.warning(f"[OpenClawBot] Message too long ({len(text)}), truncating")
        result3 = _tg('sendMessage', _build_payload(_strip_markdown(text[:3900]) + '\n...(訊息過長已截斷)', None))
        return result3

    return result


def answer_callback(cq_id, text=''):
    _tg('answerCallbackQuery', {'callback_query_id': cq_id, 'text': text})


def send_typing(chat_id):
    try:
        requests.post(f"{BOT_API_URL}/sendChatAction",
                      json={'chat_id': chat_id, 'action': 'typing'}, timeout=5)
    except Exception:
        pass


def send_photo(chat_id, file_path, caption='', reply_to=None):
    """透過 Telegram Bot API 傳送圖片"""
    try:
        with open(file_path, 'rb') as f:
            data = {'chat_id': chat_id}
            if caption:
                data['caption'] = caption
            if reply_to:
                data['reply_to_message_id'] = reply_to
            r = requests.post(
                f"{BOT_API_URL}/sendPhoto",
                data=data, files={'photo': f}, timeout=30
            )
        if not r.ok:
            sys_log.warning(f"[OpenClawBot] sendPhoto failed: {r.text[:200]}")
        return r.json()
    except Exception as e:
        sys_log.error(f"[OpenClawBot] sendPhoto error: {e}")
        return {}


def send_document(chat_id, file_path, caption='', reply_to=None):
    """透過 Telegram Bot API 傳送文件"""
    try:
        with open(file_path, 'rb') as f:
            data = {'chat_id': chat_id}
            if caption:
                data['caption'] = caption
            if reply_to:
                data['reply_to_message_id'] = reply_to
            r = requests.post(
                f"{BOT_API_URL}/sendDocument",
                data=data, files={'document': f}, timeout=30
            )
        if not r.ok:
            sys_log.warning(f"[OpenClawBot] sendDocument failed: {r.text[:200]}")
        return r.json()
    except Exception as e:
        sys_log.error(f"[OpenClawBot] sendDocument error: {e}")
        return {}


# ── 目標管理（記憶體，跨 session 用 DB 儲存）─────────────────────
_GOALS: dict = {}  # {'daily','monthly','quarterly','half','yearly': float}
_scheduler = None

# ── 輸入等待狀態機（chat_id → pending action）────────────────────
_input_pending: dict = {}  # {chat_id: {'action': str, 'label': str}}

# ── Excel 匯入暫存（chat_id → pending import info）────────────────
_excel_pending: dict = {}  # {chat_id: {'file_path': str, 'filename': str, 'preview': str}}


# ── 中文字型搜尋 ──────────────────────────────────────────────
_CHINESE_FONT_PATHS = [
    '/usr/share/fonts/truetype/wqy/wqy-zenhei.ttc',
    '/usr/share/fonts/truetype/wqy/wqy-microhei.ttc',
    '/usr/share/fonts/opentype/noto/NotoSansCJK-Regular.ttc',
    '/usr/share/fonts/truetype/noto/NotoSansCJK-Regular.ttc',
    '/usr/share/fonts/noto-cjk/NotoSansCJK-Regular.ttc',
    '/tmp/ocbot_chinese_font.ttf',
]
_FONT_DL_URL = (
    'https://github.com/googlefonts/noto-cjk/raw/main/'
    'Sans/SubsetOTF/SC/NotoSansSC-Regular.otf'
)


def _get_chinese_font() -> str:
    """取得中文字型路徑（找不到就嘗試下載），回傳路徑或空字串"""
    for p in _CHINESE_FONT_PATHS:
        if os.path.exists(p):
            return p
    # 嘗試下載 Noto Sans SC（約 3MB）
    cache = '/tmp/ocbot_chinese_font.ttf'
    try:
        r = requests.get(_FONT_DL_URL, timeout=20)
        if r.ok:
            with open(cache, 'wb') as f:
                f.write(r.content)
            return cache
    except Exception:
        pass
    return ''


# ── 報表產生：Excel（主）→ CSV（備援）────────────────────────────
def generate_daily_pdf(date_str: str) -> str:
    """
    產生日報：優先用 openpyxl 生成 .xlsx（支援中文、可直接在 Excel/Numbers 開啟）。
    openpyxl 未安裝時 fallback CSV。
    """
    import tempfile

    sales      = query_sales(date_str)
    products   = query_top_products(date_str, 20)
    vendors    = query_top_vendors(date_str, 10)
    weekly     = query_weekly_trend()
    now_str    = datetime.now(TAIPEI_TZ).strftime('%Y-%m-%d %H:%M')
    safe_date  = date_str.replace('/', '-')

    # ── openpyxl Excel（主要，支援中文，秒產生）─────────────────
    try:
        from openpyxl import Workbook
        from openpyxl.styles import (Font, PatternFill, Alignment,
                                     Border, Side, numbers)
        from openpyxl.utils import get_column_letter

        wb  = Workbook()
        HDR = Font(bold=True, color='FFFFFF', size=11)
        HDR_FILL = PatternFill('solid', fgColor='1565C0')   # 深藍
        SUB_FILL = PatternFill('solid', fgColor='E3F2FD')   # 淡藍
        ORG_FILL = PatternFill('solid', fgColor='FFF8E1')   # 淡黃
        thin     = Side(style='thin', color='BDBDBD')
        border   = Border(left=thin, right=thin, top=thin, bottom=thin)
        center   = Alignment(horizontal='center', vertical='center')
        right    = Alignment(horizontal='right',  vertical='center')

        def _hdr(ws, row, cols, fills=HDR_FILL):
            for c in cols:
                ws.cell(row, c).font = HDR
                ws.cell(row, c).fill = fills
                ws.cell(row, c).alignment = center

        def _border_row(ws, row, max_col):
            for c in range(1, max_col + 1):
                ws.cell(row, c).border = border

        # ════ Sheet 1：業績摘要 ════
        ws1 = wb.active
        ws1.title = '業績摘要'
        ws1.column_dimensions['A'].width = 18
        ws1.column_dimensions['B'].width = 22

        ws1.merge_cells('A1:B1')
        ws1['A1'] = f'📊 業績報表 — {date_str}'
        ws1['A1'].font = Font(bold=True, size=14, color='1565C0')
        ws1['A1'].alignment = center

        ws1.merge_cells('A2:B2')
        ws1['A2'] = f'產生時間：{now_str} (UTC+8)  |  OpenClaw AI'
        ws1['A2'].font = Font(size=9, color='757575')
        ws1['A2'].alignment = center

        r = 4
        if sales.get('found'):
            headers = ['項目', '數值']
            for i, h in enumerate(headers, 1):
                ws1.cell(r, i, h)
            _hdr(ws1, r, [1, 2])
            _border_row(ws1, r, 2)
            r += 1

            rows_data = [
                ('💰 總業績',  f"NT$ {float(sales.get('revenue', 0)):,.0f}"),
                ('📦 訂單數',  f"{sales.get('orders', '-')} 筆"),
                ('🛒 客單價',  f"NT$ {sales.get('avg_order', 0):,.0f}"),
                ('📈 毛利率',  f"{sales.get('gross_margin', 0):.1f}%"),
                ('🛍️ 商品數', f"{sales.get('products', '-')} 件"),
            ]
            for i, (k, v) in enumerate(rows_data):
                ws1.cell(r, 1, k)
                ws1.cell(r, 2, v)
                fill = SUB_FILL if i % 2 == 0 else None
                if fill:
                    ws1.cell(r, 1).fill = fill
                    ws1.cell(r, 2).fill = fill
                ws1.cell(r, 2).alignment = right
                _border_row(ws1, r, 2)
                r += 1

        # ── 近7日趨勢 ──
        if weekly:
            r += 1
            ws1.cell(r, 1, '📅 近7日業績趨勢').font = Font(bold=True, size=11, color='1565C0')
            r += 1
            for h, hdr in enumerate(['日期', '業績', '環比'], 1):
                ws1.cell(r, h, hdr)
            _hdr(ws1, r, [1, 2, 3])
            _border_row(ws1, r, 3)
            r += 1
            for idx, w in enumerate(weekly):
                prev = weekly[idx - 1]['revenue'] if idx > 0 else None
                rev  = w['revenue']
                pct  = f"{'▲' if rev >= prev else '▼'}{abs((rev - prev) / prev * 100):.1f}%" \
                       if prev else '—'
                ws1.cell(r, 1, w['date'])
                ws1.cell(r, 2, f"NT$ {rev:,.0f}")
                ws1.cell(r, 3, pct)
                ws1.cell(r, 2).alignment = right
                ws1.cell(r, 3).alignment = center
                if idx % 2 == 0:
                    for c in range(1, 4):
                        ws1.cell(r, c).fill = ORG_FILL
                _border_row(ws1, r, 3)
                r += 1
            ws1.column_dimensions['C'].width = 10

        # ════ Sheet 2：熱銷商品 ════
        if products:
            ws2 = wb.create_sheet('熱銷商品 TOP20')
            cols = ['排名', '商品ID', '商品名稱', '業績 (NT$)', '數量 (件)', '佔比 %']
            widths = [6, 28, 30, 16, 10, 10]
            for i, (h, w) in enumerate(zip(cols, widths), 1):
                ws2.cell(1, i, h)
                ws2.column_dimensions[get_column_letter(i)].width = w
            _hdr(ws2, 1, list(range(1, len(cols) + 1)))
            _border_row(ws2, 1, len(cols))

            total_rev = sum(p['revenue'] for p in products)
            for idx, p in enumerate(products, 1):
                pct = p['revenue'] / total_rev * 100 if total_rev else 0
                row_data = [idx, p.get('id', ''), p['name'],
                            p['revenue'], p['qty'], round(pct, 2)]
                for c, val in enumerate(row_data, 1):
                    ws2.cell(idx + 1, c, val)
                    ws2.cell(idx + 1, c).border = border
                ws2.cell(idx + 1, 4).number_format = '#,##0'
                ws2.cell(idx + 1, 5).number_format = '#,##0'
                ws2.cell(idx + 1, 6).number_format = '0.00"%"'
                ws2.cell(idx + 1, 4).alignment = right
                ws2.cell(idx + 1, 5).alignment = right
                ws2.cell(idx + 1, 6).alignment = right
                if idx % 2 == 0:
                    for c in range(1, len(cols) + 1):
                        ws2.cell(idx + 1, c).fill = SUB_FILL

        # ════ Sheet 3：熱銷廠商 ════
        if vendors:
            ws3 = wb.create_sheet('熱銷廠商 TOP10')
            vcols = ['排名', '廠商名稱', '業績 (NT$)', '佔比 %']
            vwidths = [6, 32, 16, 10]
            for i, (h, w) in enumerate(zip(vcols, vwidths), 1):
                ws3.cell(1, i, h)
                ws3.column_dimensions[get_column_letter(i)].width = w
            _hdr(ws3, 1, list(range(1, len(vcols) + 1)))
            _border_row(ws3, 1, len(vcols))

            total_vrev = sum(v['revenue'] for v in vendors)
            for idx, v in enumerate(vendors, 1):
                pct = v['revenue'] / total_vrev * 100 if total_vrev else 0
                for c, val in enumerate([idx, v['name'], v['revenue'], round(pct, 2)], 1):
                    ws3.cell(idx + 1, c, val)
                    ws3.cell(idx + 1, c).border = border
                ws3.cell(idx + 1, 3).number_format = '#,##0'
                ws3.cell(idx + 1, 4).number_format = '0.00"%"'
                ws3.cell(idx + 1, 3).alignment = right
                ws3.cell(idx + 1, 4).alignment = right
                if idx % 2 == 0:
                    for c in range(1, len(vcols) + 1):
                        ws3.cell(idx + 1, c).fill = ORG_FILL

        # ════ Sheet 4：分類業績 ════
        try:
            cats = query_category_sales(date_str, lim=15)
            if cats:
                ws4 = wb.create_sheet('分類業績 TOP15')
                ccols = ['排名', '分類名稱', '業績 (NT$)', '訂單數', '佔比 %']
                cwidths = [6, 28, 16, 10, 10]
                for i, (h, w) in enumerate(zip(ccols, cwidths), 1):
                    ws4.cell(1, i, h)
                    ws4.column_dimensions[get_column_letter(i)].width = w
                _hdr(ws4, 1, list(range(1, len(ccols) + 1)))
                _border_row(ws4, 1, len(ccols))
                total_crev = sum(c.get('revenue', 0) for c in cats)
                for idx, c in enumerate(cats, 1):
                    pct = c.get('revenue', 0) / total_crev * 100 if total_crev else 0
                    for col, val in enumerate([idx, c.get('cat', c.get('category','')),
                                               c.get('revenue',0), c.get('qty', c.get('orders',0)),
                                               round(pct,2)], 1):
                        ws4.cell(idx+1, col, val)
                        ws4.cell(idx+1, col).border = border
                    ws4.cell(idx+1, 3).number_format = '#,##0'
                    ws4.cell(idx+1, 5).number_format = '0.00"%"'
                    if idx % 2 == 0:
                        for col in range(1, len(ccols)+1):
                            ws4.cell(idx+1, col).fill = SUB_FILL
        except Exception as _e:
            sys_log.warning(f"[Excel] 分類業績 sheet 失敗: {_e}")

        # ════ Sheet 5：同期比較 ════
        try:
            cmp = query_comparison(date_str)
            if cmp:
                ws5 = wb.create_sheet('同期比較')
                ws5.column_dimensions['A'].width = 18
                ws5.column_dimensions['B'].width = 20
                ws5.column_dimensions['C'].width = 20
                ws5.column_dimensions['D'].width = 12
                ws5.cell(1, 1, '指標'); ws5.cell(1, 2, '本期'); ws5.cell(1, 3, '對比期'); ws5.cell(1, 4, '增減 %')
                _hdr(ws5, 1, [1,2,3,4])
                _border_row(ws5, 1, 4)
                periods = ['today', 'yesterday', 'last_week', 'last_month']
                labels  = ['今日', '昨日', '上週同日', '上月同日']
                metrics = [('業績 (NT$)', 'revenue'), ('訂單數', 'orders'),
                           ('客單價 (NT$)', 'avg_order'), ('毛利率 (%)', 'gross_margin')]
                base = cmp.get('today', {})
                r = 2
                for period, label in zip(periods[1:], labels[1:]):
                    comp = cmp.get(period, {})
                    if not comp:
                        continue
                    ws5.cell(r, 1, f'vs {label}').font = Font(bold=True, color='1565C0')
                    ws5.cell(r, 1).fill = PatternFill('solid', fgColor='E3F2FD')
                    _border_row(ws5, r, 4)
                    r += 1
                    for mlabel, mkey in metrics:
                        bv = float(base.get(mkey, 0) or 0)
                        cv = float(comp.get(mkey, 0) or 0)
                        pct = f"{'▲' if bv >= cv else '▼'}{abs((bv-cv)/cv*100):.1f}%" if cv else '—'
                        ws5.cell(r, 1, mlabel); ws5.cell(r, 2, f'{bv:,.1f}')
                        ws5.cell(r, 3, f'{cv:,.1f}'); ws5.cell(r, 4, pct)
                        for col in range(1,5): ws5.cell(r,col).border = border
                        ws5.cell(r,4).alignment = center
                        r += 1
        except Exception as _e:
            sys_log.warning(f"[Excel] 同期比較 sheet 失敗: {_e}")

        # ════ Sheet 6：目標達成率 ════
        try:
            goal = get_goal_status(date_str)
            if goal:
                ws6 = wb.create_sheet('目標達成率')
                ws6.column_dimensions['A'].width = 16
                ws6.column_dimensions['B'].width = 20
                ws6.column_dimensions['C'].width = 20
                ws6.column_dimensions['D'].width = 14
                ws6.cell(1, 1, '週期'); ws6.cell(1, 2, '目標 (NT$)'); ws6.cell(1, 3, '實際 (NT$)'); ws6.cell(1, 4, '達成率')
                _hdr(ws6, 1, [1,2,3,4])
                _border_row(ws6, 1, 4)
                r = 2
                for period, label in [('daily','日'), ('monthly','月'), ('quarterly','季'),
                                       ('half','半年'), ('yearly','年')]:
                    tgt = float(goal.get(f'{period}_target', 0) or 0)
                    act = float(goal.get(f'{period}_actual', 0) or 0)
                    if tgt <= 0: continue
                    rate = act / tgt * 100
                    ws6.cell(r,1,label); ws6.cell(r,2,tgt); ws6.cell(r,3,act)
                    ws6.cell(r,4,f'{rate:.1f}%')
                    ws6.cell(r,2).number_format = '#,##0'
                    ws6.cell(r,3).number_format = '#,##0'
                    color = '1B5E20' if rate >= 100 else ('E65100' if rate < 70 else '1565C0')
                    ws6.cell(r,4).font = Font(bold=True, color=color)
                    for col in range(1,5): ws6.cell(r,col).border = border
                    r += 1
        except Exception as _e:
            sys_log.warning(f"[Excel] 目標達成率 sheet 失敗: {_e}")

        tmp = tempfile.NamedTemporaryFile(
            suffix=f'_{safe_date}.xlsx', prefix='momo_report_', delete=False, dir='/tmp'
        )
        wb.save(tmp.name)
        sys_log.info(f"[OpenClawBot] Excel report generated: {tmp.name}")
        return tmp.name

    except ImportError:
        sys_log.warning("[OpenClawBot] openpyxl 未安裝，fallback CSV")
    except Exception as e:
        sys_log.error(f"[OpenClawBot] openpyxl error: {e}")

    # ── Fallback：CSV ──────────────────────────────────────────
    try:
        import csv
        tmp = tempfile.NamedTemporaryFile(
            suffix=f'_{safe_date}.csv', prefix='momo_report_', delete=False,
            dir='/tmp', mode='w', encoding='utf-8-sig', newline=''
        )
        writer = csv.writer(tmp)
        writer.writerow(['業績報表', date_str, f'產生時間：{now_str}'])
        writer.writerow([])
        if sales.get('found'):
            writer.writerow(['=== 業績摘要 ==='])
            writer.writerow(['總業績', float(sales.get('revenue', 0))])
            writer.writerow(['訂單數', sales.get('orders', '-')])
            writer.writerow(['商品數', sales.get('products', '-')])
            writer.writerow([])
        if products:
            writer.writerow(['=== 熱銷商品 TOP20 ==='])
            writer.writerow(['排名', '商品ID', '商品名稱', '業績', '數量'])
            for i, p in enumerate(products, 1):
                writer.writerow([i, p.get('id', ''), p['name'],
                                 p['revenue'], p['qty']])
            writer.writerow([])
        if vendors:
            writer.writerow(['=== 熱銷廠商 TOP10 ==='])
            writer.writerow(['排名', '廠商名稱', '業績'])
            for i, v in enumerate(vendors, 1):
                writer.writerow([i, v['name'], v['revenue']])
        tmp.close()
        sys_log.info(f"[OpenClawBot] CSV fallback generated: {tmp.name}")
        return tmp.name
    except Exception as e:
        sys_log.error(f"[OpenClawBot] CSV fallback error: {e}")
        return ''


# ══════════════════════════════════════════════════════════════
# v5 — 進階業績智能功能
# ══════════════════════════════════════════════════════════════

# ── 新增 DB 查詢 ──────────────────────────────────────────────

def query_category_sales(date_str, lim=10):
    """按商品分類查業績（優先用 商品分類L1，fallback 小分類）"""
    d = normalize_date(date_str)
    for col in ('"商品分類L1"', '"商品分類L2"', '"小分類"', '"商品分類"', '"類別"', '"category"'):
        try:
            with _db().connect() as c:
                rows = c.execute(text(f"""
                    SELECT COALESCE({col}, '未分類') as cat,
                           COUNT(DISTINCT "商品ID") as products,
                           SUM(CAST("總業績" AS FLOAT)) as revenue,
                           SUM(CAST("數量" AS INTEGER)) as qty
                    FROM realtime_sales_monthly WHERE "日期"=:d
                    GROUP BY cat ORDER BY revenue DESC LIMIT :lim
                """), {'d': d, 'lim': lim}).fetchall()
            if rows:
                return [{'cat': r[0], 'products': r[1], 'revenue': r[2], 'qty': r[3]}
                        for r in rows]
        except Exception:
            continue
    return []


def query_category_monthly(year: int, month: int, lim: int = 10) -> list:
    """按月份查分類業績（用 LIKE YYYY/MM/%）"""
    prefix = f"{year}/{month:02d}/%"
    for col in ('"商品分類L1"', '"商品分類L2"', '"小分類"', '"商品分類"'):
        try:
            with _db().connect() as c:
                rows = c.execute(text(f"""
                    SELECT COALESCE({col}, '未分類') as cat,
                           COUNT(DISTINCT "商品ID") as products,
                           SUM(CAST("總業績" AS FLOAT)) as revenue,
                           SUM(CAST("數量" AS INTEGER)) as qty
                    FROM realtime_sales_monthly WHERE "日期" LIKE :prefix
                    GROUP BY cat ORDER BY revenue DESC LIMIT :lim
                """), {'prefix': prefix, 'lim': lim}).fetchall()
            if rows:
                return [{'cat': r[0], 'products': int(r[1]), 'revenue': float(r[2]), 'qty': int(r[3])}
                        for r in rows]
        except Exception:
            continue
    return []


def query_comparison(date_str):
    """今日 vs 上週同日 vs 上月同日"""
    from datetime import datetime as dt
    try:
        d = dt.strptime(normalize_date(date_str).replace('/', '-'), '%Y-%m-%d').date()
        lw_str = (d - timedelta(days=7)).strftime('%Y/%m/%d')
        lm_str = (d - timedelta(days=30)).strftime('%Y/%m/%d')

        def _fetch(day_s):
            try:
                with _db().connect() as c:
                    row = c.execute(text("""
                        SELECT SUM(CAST("總業績" AS FLOAT)),
                               COUNT(DISTINCT "商品ID")
                        FROM realtime_sales_monthly WHERE "日期"=:d
                    """), {'d': day_s}).fetchone()
                if row and row[0]:
                    return {'date': day_s, 'revenue': float(row[0]), 'products': row[1]}
            except Exception:
                pass
            return {'date': day_s, 'revenue': 0, 'products': 0}

        return {
            'today':      _fetch(normalize_date(date_str)),
            'last_week':  _fetch(lw_str),
            'last_month': _fetch(lm_str),
        }
    except Exception as e:
        sys_log.error(f"[OpenClawBot] query_comparison: {e}")
        return None


def query_daily_history(days=14):
    """取得近N天日業績（用於趨勢圖）"""
    try:
        with _db().connect() as c:
            rows = c.execute(text(f"""
                SELECT "日期", SUM(CAST("總業績" AS FLOAT)) as rev
                FROM realtime_sales_monthly
                WHERE CAST("日期" AS DATE) >= CURRENT_DATE - INTERVAL '{int(days)} days'
                GROUP BY "日期" ORDER BY "日期" ASC
            """)).fetchall()
        return [{'date': r[0], 'revenue': float(r[1])} for r in rows if r[1]]
    except Exception as e:
        sys_log.error(f"[OpenClawBot] query_daily_history: {e}")
        return []


def query_restock_forecast(top_n: int = 20) -> list:
    """基於近7日銷售速度的補貨預測（高速+加速商品優先）"""
    try:
        with _db().connect() as c:
            rows = c.execute(text("""
                WITH recent AS (
                    SELECT "商品ID", "商品名稱",
                           SUM(CAST("數量" AS INTEGER))  AS qty7,
                           SUM(CAST("總業績" AS FLOAT))  AS rev7,
                           COUNT(DISTINCT "日期")        AS active_days
                    FROM realtime_sales_monthly
                    WHERE CAST("日期" AS DATE) >= CURRENT_DATE - INTERVAL '7 days'
                    GROUP BY "商品ID", "商品名稱"
                ),
                older AS (
                    SELECT "商品ID",
                           COALESCE(SUM(CAST("數量" AS INTEGER)), 0) AS qty_prev7
                    FROM realtime_sales_monthly
                    WHERE CAST("日期" AS DATE) BETWEEN
                          CURRENT_DATE - INTERVAL '14 days' AND
                          CURRENT_DATE - INTERVAL '8 days'
                    GROUP BY "商品ID"
                )
                SELECT r."商品ID", r."商品名稱",
                       r.qty7, r.active_days, r.rev7,
                       COALESCE(o.qty_prev7, 0) AS qty_prev7
                FROM recent r
                LEFT JOIN older o ON r."商品ID" = o."商品ID"
                WHERE r.active_days >= 3 AND r.qty7 > 0
                ORDER BY r.qty7 DESC
                LIMIT :n
            """), {'n': top_n}).fetchall()
        result = []
        for row in rows:
            pid, name, qty7, active, rev7, qty_prev7 = row
            daily_vel  = qty7 / 7
            prev_vel   = qty_prev7 / 7 if qty_prev7 else daily_vel
            accel      = (daily_vel - prev_vel) / prev_vel * 100 if prev_vel > 0 else 0
            urgency    = ('HIGH' if daily_vel >= 5 and accel >= 15
                          else 'MED' if daily_vel >= 2 or accel >= 30
                          else 'LOW')
            result.append({
                'id': pid, 'name': name,
                'daily_vel': round(daily_vel, 1),
                'qty7': int(qty7), 'rev7': rev7,
                'accel': round(accel, 1),
                'urgency': urgency,
            })
        return result
    except Exception as e:
        sys_log.error(f"[restock] {e}")
        return []


def query_category_detail(category: str, date_str: str = '', limit: int = 10) -> list:
    """查詢指定L1分類的L2細項業績"""
    try:
        # 決定查詢範圍：有日期查單日，否則查近7天
        if date_str:
            d = normalize_date(date_str)
            where_clause = '"日期" = :d'
            params = {'d': d, 'lim': limit, 'cat': category}
        else:
            where_clause = 'CAST("日期" AS DATE) >= CURRENT_DATE - INTERVAL \'7 days\''
            params = {'lim': limit, 'cat': category}

        for l2_col in ('"商品分類L2"', '"小分類"', '"商品分類"'):
            try:
                with _db().connect() as c:
                    rows = c.execute(text(f"""
                        SELECT COALESCE({l2_col}, '其他') AS sub_cat,
                               COUNT(DISTINCT "商品ID") AS products,
                               SUM(CAST("總業績"  AS FLOAT))   AS revenue,
                               SUM(CAST("數量" AS INTEGER)) AS qty
                        FROM realtime_sales_monthly
                        WHERE "商品分類L1" = :cat AND {where_clause}
                        GROUP BY sub_cat ORDER BY revenue DESC LIMIT :lim
                    """), params).fetchall()
                if rows:
                    return [{'cat': r[0], 'products': r[1], 'revenue': float(r[2]), 'qty': int(r[3])}
                            for r in rows]
            except Exception:
                continue
        return []
    except Exception as e:
        sys_log.error(f"[category_detail] {e}")
        return []


def query_promo_comparison(start_str: str, end_str: str) -> dict:
    """查詢促銷期間 vs 前同天數業績比較"""
    try:
        from datetime import datetime as _dt
        start = _dt.strptime(start_str.replace('/', '-'), '%Y-%m-%d').date()
        end   = _dt.strptime(end_str.replace('/', '-'), '%Y-%m-%d').date()
        days  = (end - start).days + 1
        # 前期：同等天數
        from datetime import timedelta as _td
        pre_end   = start - _td(days=1)
        pre_start = pre_end - _td(days=days - 1)

        def _fetch_period(s, e):
            try:
                with _db().connect() as c:
                    row = c.execute(text("""
                        SELECT COUNT(DISTINCT "訂單編號"),
                               COALESCE(SUM(CAST("總業績" AS FLOAT)), 0),
                               COALESCE(SUM(CAST("總成本"  AS FLOAT)), 0),
                               COUNT(DISTINCT "商品ID")
                        FROM realtime_sales_monthly
                        WHERE CAST("日期" AS DATE) BETWEEN CAST(:s AS DATE) AND CAST(:e AS DATE)
                    """), {'s': str(s), 'e': str(e)}).fetchone()
                    tops = c.execute(text("""
                        SELECT "商品名稱", SUM(CAST("總業績" AS FLOAT)) AS rev
                        FROM realtime_sales_monthly
                        WHERE CAST("日期" AS DATE) BETWEEN CAST(:s AS DATE) AND CAST(:e AS DATE)
                        GROUP BY "商品名稱" ORDER BY rev DESC LIMIT 5
                    """), {'s': str(s), 'e': str(e)}).fetchall()
                orders, rev, cost, prods = row
                margin = (rev - cost) / rev * 100 if rev else 0
                return {
                    'start': str(s), 'end': str(e), 'days': days,
                    'orders': int(orders or 0), 'revenue': float(rev),
                    'margin': round(margin, 1), 'products': int(prods or 0),
                    'top_products': [{'name': r[0], 'revenue': float(r[1])} for r in tops],
                }
            except Exception as ex:
                sys_log.error(f"[promo_cmp] {ex}")
                return {'start': str(s), 'end': str(e), 'days': days,
                        'orders': 0, 'revenue': 0, 'margin': 0, 'products': 0, 'top_products': []}

        promo = _fetch_period(start, end)
        pre   = _fetch_period(pre_start, pre_end)
        rev_lift = (promo['revenue'] - pre['revenue']) / pre['revenue'] * 100 if pre['revenue'] else 0
        ord_lift = (promo['orders'] - pre['orders']) / pre['orders'] * 100 if pre['orders'] else 0
        return {
            'promo': promo, 'pre': pre,
            'rev_lift': round(rev_lift, 1),
            'ord_lift': round(ord_lift, 1),
        }
    except Exception as e:
        sys_log.error(f"[promo_comparison] {e}")
        return {}


def query_anomalies(date_str):
    """偵測當日業績異常商品（vs 7日均值，偏差>30%）"""
    d = normalize_date(date_str)
    try:
        with _db().connect() as c:
            rows = c.execute(text("""
                WITH today AS (
                    SELECT "商品ID", "商品名稱",
                           SUM(CAST("總業績" AS FLOAT)) AS today_rev
                    FROM realtime_sales_monthly WHERE "日期"=:d
                    GROUP BY "商品ID", "商品名稱"
                ),
                avg7 AS (
                    SELECT "商品ID", AVG(day_rev) AS avg_rev
                    FROM (
                        SELECT "商品ID", "日期",
                               SUM(CAST("總業績" AS FLOAT)) AS day_rev
                        FROM realtime_sales_monthly
                        WHERE CAST("日期" AS DATE)
                              BETWEEN CAST(:d AS DATE) - INTERVAL '7 days'
                                  AND CAST(:d AS DATE) - INTERVAL '1 day'
                        GROUP BY "商品ID", "日期"
                    ) sub
                    GROUP BY "商品ID"
                )
                SELECT t."商品ID", t."商品名稱", t.today_rev, a.avg_rev,
                       (t.today_rev - a.avg_rev) / NULLIF(a.avg_rev, 0) * 100 AS pct
                FROM today t
                JOIN avg7 a ON t."商品ID" = a."商品ID"
                WHERE ABS((t.today_rev - a.avg_rev) / NULLIF(a.avg_rev, 0)) > 0.3
                ORDER BY ABS((t.today_rev - a.avg_rev) / NULLIF(a.avg_rev, 0)) DESC
                LIMIT 10
            """), {'d': d}).fetchall()
        return [{'id': r[0], 'name': r[1], 'today': r[2], 'avg7': r[3], 'pct': r[4]}
                for r in rows]
    except Exception as e:
        sys_log.error(f"[OpenClawBot] query_anomalies: {e}")
        return []


# ── 目標管理 ──────────────────────────────────────────────────

def get_goal_status(date_str: str) -> dict:
    d = normalize_date(date_str)
    sales = query_sales(date_str)
    today_rev = float(sales.get('revenue', 0)) if sales.get('found') else 0.0

    # 月業績
    try:
        month_prefix = d[:7]  # e.g. '2026/04'
        with _db().connect() as c:
            row = c.execute(text("""
                SELECT SUM(CAST("總業績" AS FLOAT))
                FROM realtime_sales_monthly WHERE "日期" LIKE :prefix
            """), {'prefix': f"{month_prefix}%"}).fetchone()
        month_rev = float(row[0]) if row and row[0] else 0.0
    except Exception:
        month_rev = 0.0

    daily_goal     = _GOALS.get('daily', 0)
    monthly_goal   = _GOALS.get('monthly', 0)
    quarterly_goal = _GOALS.get('quarterly', 0)
    half_goal      = _GOALS.get('half', 0)
    yearly_goal    = _GOALS.get('yearly', 0)
    # P9 — 週目標自動推算：若未手動設定，從月目標 ÷ 4.3 推算
    weekly_goal = _GOALS.get('weekly', 0)
    if not weekly_goal and monthly_goal:
        weekly_goal = round(monthly_goal / 4.3)

    # 季/半年/年業績
    year_s = d[:4]
    try:
        with _db().connect() as c:
            row_y = c.execute(text("""
                SELECT SUM(CAST("總業績" AS FLOAT))
                FROM realtime_sales_monthly WHERE "日期" LIKE :y
            """), {'y': f"{year_s}/%"}).fetchone()
        year_rev = float(row_y[0]) if row_y and row_y[0] else 0.0
    except Exception:
        year_rev = 0.0

    # 近7日業績（週目標達成率用）
    try:
        weekly_rows = query_weekly_trend()
        week_rev = sum(w['revenue'] for w in weekly_rows) if weekly_rows else 0.0
    except Exception:
        week_rev = 0.0

    return {
        'date':           d,
        'today_rev':      today_rev,
        'daily_goal':     daily_goal,
        'daily_pct':      today_rev / daily_goal * 100 if daily_goal > 0 else None,
        'week_rev':       week_rev,
        'weekly_goal':    weekly_goal,
        'weekly_pct':     week_rev / weekly_goal * 100 if weekly_goal > 0 else None,
        'weekly_auto':    not bool(_GOALS.get('weekly', 0)) and bool(monthly_goal),  # 是否自動推算
        'month_rev':      month_rev,
        'monthly_goal':   monthly_goal,
        'monthly_pct':    month_rev / monthly_goal * 100 if monthly_goal > 0 else None,
        'year_rev':       year_rev,
        'quarterly_goal': quarterly_goal,
        'quarterly_pct':  year_rev / 4 / quarterly_goal * 100 if quarterly_goal > 0 else None,
        'half_goal':      half_goal,
        'half_pct':       year_rev / 2 / half_goal * 100 if half_goal > 0 else None,
        'yearly_goal':    yearly_goal,
        'yearly_pct':     year_rev / yearly_goal * 100 if yearly_goal > 0 else None,
        # 月目標追蹤輔助欄位
        'days_elapsed':   int(d[8:10]) if len(d) >= 10 else 0,
        'days_in_month':  __import__('calendar').monthrange(int(d[:4]), int(d[5:7]))[1] if len(d) >= 7 else 30,
    }


# ── 圖表產生（matplotlib）────────────────────────────────────

_MPL_FONT_SETUP_DONE = False

def _setup_mpl_chinese():
    """確保 matplotlib 使用中文字型（只執行一次）"""
    global _MPL_FONT_SETUP_DONE
    if _MPL_FONT_SETUP_DONE:
        return
    try:
        import matplotlib.font_manager as fm
        import matplotlib as mpl

        font_paths = [
            '/usr/local/lib/python3.11/site-packages/matplotlib/mpl-data/fonts/ttf/WQYZenHei.ttf',
            '/usr/share/fonts/truetype/wqy/wqy-zenhei.ttc',
            '/usr/share/fonts/truetype/wqy/wqy-microhei.ttc',
        ]
        for fp in font_paths:
            if os.path.exists(fp):
                fm.fontManager.addfont(fp)
                prop = fm.FontProperties(fname=fp)
                mpl.rcParams['font.family'] = prop.get_name()
                mpl.rcParams['axes.unicode_minus'] = False
                _MPL_FONT_SETUP_DONE = True
                sys_log.info(f"[OpenClawBot] matplotlib 中文字型: {fp}")
                return
        sys_log.warning("[OpenClawBot] 找不到中文字型，圖表文字可能亂碼")
    except Exception as e:
        sys_log.warning(f"[OpenClawBot] _setup_mpl_chinese: {e}")


def gen_trend_chart(days=14, data_points=None, title=None) -> str:
    """產生業績趨勢折線圖 PNG — 上升紅色/下降綠色，每日金額標註
    data_points: 若提供則使用此資料 [{'date': ..., 'revenue': ...}]，否則查近 days 日
    title: 自訂圖表標題
    """
    try:
        import matplotlib
        matplotlib.use('Agg')
        _setup_mpl_chinese()
        import matplotlib.pyplot as plt
        import matplotlib.dates as mdates
        import matplotlib.patches as mpatches
        from datetime import datetime as dt
        import tempfile

        history = data_points if data_points else query_daily_history(days)
        if not history:
            return ''

        dates    = [dt.strptime(r['date'].replace('/', '-'), '%Y-%m-%d') for r in history]
        revenues = [r['revenue'] / 10000 for r in history]  # 萬元

        # 台灣股市慣例：上升=紅，下降=綠
        UP_COLOR   = '#E53935'   # 上升 紅
        DOWN_COLOR = '#43A047'   # 下降 綠
        NEUTRAL    = '#1565C0'   # 第一點 藍

        fig, ax = plt.subplots(figsize=(14, 6))
        fig.patch.set_facecolor('#FAFAFA')
        ax.set_facecolor('#FAFAFA')

        # 逐段繪製（每段依漲跌著色）
        for i in range(1, len(dates)):
            seg_color = UP_COLOR if revenues[i] >= revenues[i - 1] else DOWN_COLOR
            ax.plot([dates[i - 1], dates[i]], [revenues[i - 1], revenues[i]],
                    '-', color=seg_color, linewidth=2.8, solid_capstyle='round')

        # 每日節點（顏色依和前日比較）
        for i, (d, r) in enumerate(zip(dates, revenues)):
            if i == 0:
                pt_color = NEUTRAL
            elif r >= revenues[i - 1]:
                pt_color = UP_COLOR
            else:
                pt_color = DOWN_COLOR
            ax.plot(d, r, 'o', color=pt_color, markersize=8, zorder=5,
                    markeredgecolor='white', markeredgewidth=1.5)

        # 每日金額標籤
        for i, (d, r) in enumerate(zip(dates, revenues)):
            # 決定標籤位置（奇偶交錯避免重疊）
            y_offset = 12 if i % 2 == 0 else -22
            ha = 'center'
            if i == 0:
                label_color = NEUTRAL
            elif r >= revenues[i - 1]:
                label_color = UP_COLOR
            else:
                label_color = DOWN_COLOR
            ax.annotate(f'NT${r:,.1f}萬',
                        (d, r),
                        textcoords='offset points', xytext=(0, y_offset),
                        ha=ha, fontsize=8.5, color=label_color, fontweight='bold',
                        bbox=dict(boxstyle='round,pad=0.2', facecolor='white',
                                  edgecolor=label_color, alpha=0.85, linewidth=0.8))

        # 全域最高/最低特別標記
        if revenues:
            max_i = revenues.index(max(revenues))
            min_i = revenues.index(min(revenues))
            ax.scatter([dates[max_i]], [revenues[max_i]], s=180, color=UP_COLOR,
                       zorder=6, marker='*', edgecolors='white', linewidths=0.8)
            ax.scatter([dates[min_i]], [revenues[min_i]], s=180, color=DOWN_COLOR,
                       zorder=6, marker='v', edgecolors='white', linewidths=0.8)

        # X 軸：日期 + 星期
        WEEKDAYS_ZH = ['週一', '週二', '週三', '週四', '週五', '週六', '週日']
        ax.xaxis.set_major_formatter(mdates.DateFormatter('%m/%d'))
        ax.xaxis.set_major_locator(mdates.DayLocator(interval=1))

        # 替換 x tick labels 加上星期
        tick_dates = dates
        tick_labels = [f"{d.strftime('%m/%d')}\n{WEEKDAYS_ZH[d.weekday()]}" for d in tick_dates]
        ax.set_xticks(tick_dates)
        ax.set_xticklabels(tick_labels, fontsize=9)

        ax.set_ylabel('業績（萬元）', fontsize=12)
        today_str = datetime.now(TAIPEI_TZ).strftime('%Y/%m/%d')
        chart_title = title or f'業績趨勢走勢圖 — 近 {days} 日  （截至 {today_str}）'
        ax.set_title(chart_title, fontsize=14, fontweight='bold', pad=14)
        ax.grid(True, alpha=0.25, linestyle='--', color='#BDBDBD')
        ax.yaxis.set_major_formatter(plt.FuncFormatter(lambda x, _: f'{x:,.0f}'))

        # 圖例
        up_patch   = mpatches.Patch(color=UP_COLOR,   label='▲ 較前日上升')
        down_patch = mpatches.Patch(color=DOWN_COLOR, label='▼ 較前日下降')
        ax.legend(handles=[up_patch, down_patch], loc='upper left', fontsize=10,
                  framealpha=0.85)

        # 統計副標題
        if len(revenues) >= 2:
            total_rev = sum(revenues)
            avg_rev   = total_rev / len(revenues)
            max_rev   = max(revenues)
            min_rev   = min(revenues)
            last_change_pct = (revenues[-1] - revenues[-2]) / revenues[-2] * 100 if revenues[-2] else 0
            arrow = '▲' if last_change_pct >= 0 else '▼'
            stats_text = (f"總計 NT${total_rev:,.1f}萬  |  "
                          f"日均 NT${avg_rev:,.1f}萬  |  "
                          f"最高 NT${max_rev:,.1f}萬  |  最低 NT${min_rev:,.1f}萬  |  "
                          f"昨日 {arrow}{abs(last_change_pct):.1f}%")
            fig.text(0.5, 0.01, stats_text, ha='center', fontsize=9,
                     color='#616161', style='italic')

        plt.tight_layout(rect=[0, 0.04, 1, 1])
        tmp = tempfile.NamedTemporaryFile(
            suffix='.png', prefix='ocbot_trend_', delete=False, dir='/tmp')
        plt.savefig(tmp.name, dpi=130, bbox_inches='tight')
        plt.close()
        return tmp.name
    except ImportError:
        sys_log.warning('[OpenClawBot] matplotlib not installed — chart disabled')
        return ''
    except Exception as e:
        sys_log.error(f'[OpenClawBot] gen_trend_chart: {e}')
        return ''


def gen_products_chart(date_str, n=10) -> str:
    """產生熱銷商品橫條圖 PNG — 上升紅/下降綠，標示業績金額"""
    try:
        import matplotlib
        matplotlib.use('Agg')
        _setup_mpl_chinese()
        import matplotlib.pyplot as plt
        import matplotlib.patches as mpatches
        from datetime import datetime as dt
        import tempfile

        products = query_top_products(date_str, n)
        if not products:
            return ''

        # 取得上週同日資料做顏色比較
        try:
            d = dt.strptime(date_str.replace('/', '-'), '%Y-%m-%d').date()
            lw_str = (d - timedelta(days=7)).strftime('%Y/%m/%d')
            lw_products = query_top_products(lw_str, n * 2)
            lw_map = {p.get('id'): p['revenue'] for p in lw_products if p.get('id')}
        except Exception:
            lw_map = {}

        UP_COLOR   = '#E53935'   # 上升/優 紅
        DOWN_COLOR = '#43A047'   # 下降 綠
        NEUTRAL    = '#1976D2'   # 無前期資料 藍

        # 短名稱（限14字）+ ID
        labels   = [f"{p['name'][:13]}…\n[{_short_id(p.get('id',''))}]"
                    if len(p['name']) > 13
                    else f"{p['name']}\n[{_short_id(p.get('id',''))}]"
                    for p in products]
        revenues = [p['revenue'] / 10000 for p in products]

        # 決定每條顏色
        colors = []
        for p in products:
            pid = p.get('id')
            lw_rev = lw_map.get(pid)
            if lw_rev is None:
                colors.append(NEUTRAL)
            elif p['revenue'] >= lw_rev:
                colors.append(UP_COLOR)
            else:
                colors.append(DOWN_COLOR)

        # 翻轉（matplotlib barh 從下往上）
        labels.reverse(); revenues.reverse(); colors.reverse()

        fig, ax = plt.subplots(figsize=(13, max(6, n * 0.75)))
        fig.patch.set_facecolor('#FAFAFA')
        ax.set_facecolor('#FAFAFA')

        bars = ax.barh(labels, revenues, color=colors, alpha=0.88,
                       height=0.62, edgecolor='white', linewidth=0.8)

        max_rev = max(revenues) if revenues else 1
        for bar, rev, col in zip(bars, revenues, colors):
            # 金額標籤（在 bar 右側）
            ax.text(bar.get_width() + max_rev * 0.008,
                    bar.get_y() + bar.get_height() / 2,
                    f'NT${rev:,.2f}萬',
                    va='center', ha='left', fontsize=9.5,
                    color=col, fontweight='bold')

        ax.set_xlabel('業績（萬元）', fontsize=11)
        ax.set_title(f'🏆 熱銷商品 TOP{n}  —  {date_str}',
                     fontsize=14, fontweight='bold', pad=12)
        ax.xaxis.set_major_formatter(plt.FuncFormatter(lambda x, _: f'{x:,.0f}'))
        ax.grid(True, alpha=0.25, linestyle='--', color='#BDBDBD', axis='x')

        # 設定 xlim 留右側空間給標籤
        ax.set_xlim(0, max_rev * 1.35)

        # 圖例
        up_p   = mpatches.Patch(color=UP_COLOR,   label='▲ 週環比上升')
        dn_p   = mpatches.Patch(color=DOWN_COLOR, label='▼ 週環比下降')
        ne_p   = mpatches.Patch(color=NEUTRAL,    label='— 無前期資料')
        ax.legend(handles=[up_p, dn_p, ne_p], loc='lower right', fontsize=9,
                  framealpha=0.85)

        plt.tight_layout()
        tmp = tempfile.NamedTemporaryFile(
            suffix='.png', prefix='ocbot_products_', delete=False, dir='/tmp')
        plt.savefig(tmp.name, dpi=130, bbox_inches='tight')
        plt.close()
        return tmp.name
    except ImportError:
        return ''
    except Exception as e:
        sys_log.error(f'[OpenClawBot] gen_products_chart: {e}')
        return ''


# ── 商品策略矩陣 ───────────────────────────────────────────────

def analyze_product_strategy(date_str: str, top_n=10) -> list:
    """內部業績週環比 × 外部MCP趨勢 → 策略標籤"""
    products = query_top_products(date_str, top_n * 2)
    if not products:
        return []

    from datetime import datetime as dt
    try:
        d = dt.strptime(normalize_date(date_str).replace('/', '-'), '%Y-%m-%d').date()
        lw_str = (d - timedelta(days=7)).strftime('%Y/%m/%d')
        lw_products = query_top_products(lw_str, top_n * 2)
        lw_map = {p.get('id'): p['revenue'] for p in lw_products if p.get('id')}
    except Exception:
        lw_map = {}

    try:
        from services.mcp_context_service import get_taiwan_trends
        trend_kws = [t['keyword'] for t in get_taiwan_trends().get('trends', [])][:20]
    except Exception:
        trend_kws = []

    result = []
    for p in products[:top_n]:
        pid, name, rev = p.get('id', ''), p['name'], p['revenue']
        lw_rev = lw_map.get(pid, 0)
        growth = (rev - lw_rev) / lw_rev if lw_rev > 0 else 0
        ext_hot = any(kw in name for kw in trend_kws) if trend_kws else None

        if growth > 0.1 and (ext_hot or ext_hot is None):
            tag, strat, advice = '🔥', '加碼', '內外皆熱，立即加大廣告投放'
        elif growth >= -0.1 and ext_hot:
            tag, strat, advice = '💡', '機會', '外部熱搜但放量不足，提升曝光'
        elif growth > 0.1 and not ext_hot:
            tag, strat, advice = '⚡', '收割', '內部增長強，外部降溫前先收割'
        elif growth < -0.1 and not ext_hot:
            tag, strat, advice = '⚠️', '觀察', '內外皆疲，考慮轉移資源'
        else:
            tag, strat, advice = '✅', '持穩', '表現平穩，維持現狀'

        result.append({
            'id': pid, 'name': name, 'revenue': rev,
            'growth': growth, 'ext_hot': ext_hot,
            'tag': tag, 'strategy': strat, 'advice': advice,
        })
    return result


def _analyze_strategy_range(start_str: str, end_str: str, products: list) -> list:
    """區間版策略分析（週/月/季/年用）：比對前一等長區間的成長率"""
    if not products:
        return []
    try:
        s = datetime.strptime(start_str.replace('/', '-'), '%Y-%m-%d')
        e = datetime.strptime(end_str.replace('/', '-'), '%Y-%m-%d')
        delta = (e - s).days + 1
        prev_end   = (s - timedelta(days=1)).strftime('%Y/%m/%d')
        prev_start = (s - timedelta(days=delta)).strftime('%Y/%m/%d')
        prev_prods = query_top_products_range(prev_start, prev_end, len(products) * 2)
        prev_map   = {p.get('id'): p['revenue'] for p in prev_prods if p.get('id')}
    except Exception:
        prev_map = {}

    try:
        from services.mcp_context_service import get_taiwan_trends
        trend_kws = [t['keyword'] for t in get_taiwan_trends().get('trends', [])][:20]
    except Exception:
        trend_kws = []

    result = []
    for p in products:
        pid, name, rev = p.get('id', ''), p['name'], p['revenue']
        prev_rev = prev_map.get(pid, 0)
        growth   = (rev - prev_rev) / prev_rev if prev_rev > 0 else 0
        ext_hot  = any(kw in name for kw in trend_kws) if trend_kws else None

        if growth > 0.1 and (ext_hot or ext_hot is None):
            tag, strat, advice = '🔥', '加碼', '內外皆熱，立即加大廣告投放'
        elif growth >= -0.1 and ext_hot:
            tag, strat, advice = '💡', '機會', '外部熱搜但放量不足，提升曝光'
        elif growth > 0.1 and not ext_hot:
            tag, strat, advice = '⚡', '收割', '內部增長強，外部降溫前先收割'
        elif growth < -0.1 and not ext_hot:
            tag, strat, advice = '⚠️', '觀察', '內外皆疲，考慮轉移資源'
        else:
            tag, strat, advice = '✅', '持穩', '表現平穩，維持現狀'

        result.append({
            'id': pid, 'name': name, 'revenue': rev,
            'growth': growth, 'ext_hot': ext_hot,
            'tag': tag, 'strategy': strat, 'advice': advice,
        })
    return result


# ── 新增格式化函數 ─────────────────────────────────────────────

def fmt_category(cats, date_str):
    if not cats:
        return (f"⚠️ *分類資料不足*\n\n"
                f"`{date_str}` 無分類業績資料\n"
                f"_請確認資料是否包含「商品分類」欄位_")
    total = sum(c['revenue'] for c in cats)
    lines = [
        f"🗂 *{date_str} 分類業績分析*",
        f"合計 `NT$ {total:,.0f}` | 共 {len(cats)} 個分類",
        f"{'─' * 30}",
        "",
    ]
    for i, c in enumerate(cats):
        rev = c['revenue']
        pct = rev / total * 100 if total > 0 else 0
        medal = MEDALS[i] if i < len(MEDALS) else f"`{i+1}.`"
        bar_len = max(1, int(pct / 100 * 10))
        mini_bar = '█' * bar_len + '░' * (10 - bar_len)
        lines.append(f"{medal} *{c['cat']}*")
        lines.append(f"   💰 `NT$ {rev:,.0f}`  🛍 {c['products']}款")
        lines.append(f"   `{mini_bar}` 佔比 *{pct:.1f}%*")
        lines.append("")

    # 前3名佔比
    top3_pct = sum(c['revenue'] for c in cats[:3]) / total * 100 if total else 0
    lines.append(f"_📌 前3類合計佔比：{top3_pct:.1f}%_")
    return "\n".join(lines)


def fmt_comparison(data, date_str):
    if not data:
        return "⚠️ *無法取得比較資料*\n\n請確認歷史資料已匯入。"

    def _delta_str(a, b):
        if not b:
            return '`—`'
        pct = (a - b) / b * 100
        arrow = '▲' if pct >= 0 else '▼'
        emoji = '🟢' if pct >= 0 else '🔴'
        return f"{emoji} `{arrow}{abs(pct):.1f}%`  (`NT$ {a - b:+,.0f}`)"

    today = data.get('today', {})
    lw    = data.get('last_week', {})
    lm    = data.get('last_month', {})
    r_t   = today.get('revenue', 0)
    r_lw  = lw.get('revenue', 0)
    r_lm  = lm.get('revenue', 0)

    lines = [
        f"🔄 *{date_str} 業績同期比較*",
        f"{'─' * 30}",
        "",
        f"📅 *本日業績*  `NT$ {r_t:,.0f}`",
        "",
    ]
    if r_lw:
        lines.append(f"📌 *vs 上週同日*（{lw['date']}）")
        lines.append(f"   上週：`NT$ {r_lw:,.0f}`")
        lines.append(f"   差異：{_delta_str(r_t, r_lw)}")
        lines.append("")
    if r_lm:
        lines.append(f"📌 *vs 上月同日*（{lm['date']}）")
        lines.append(f"   上月：`NT$ {r_lm:,.0f}`")
        lines.append(f"   差異：{_delta_str(r_t, r_lm)}")
        lines.append("")

    # 趨勢判斷
    if r_lw and r_lm:
        both_up   = r_t >= r_lw and r_t >= r_lm
        both_down = r_t < r_lw  and r_t < r_lm
        if both_up:
            lines.append("✅ *整體趨勢*：本日業績優於上週及上月同期，表現亮眼！")
        elif both_down:
            lines.append("⚠️ *整體趨勢*：本日業績低於上週及上月同期，需關注原因。")
        else:
            lines.append("📊 *整體趨勢*：表現不一，建議深入分析商品結構。")

    return "\n".join(lines)


def fmt_goal_status(status):
    if not status:
        return "⚠️ *無法取得目標資料*"
    rev = status.get('today_rev', 0) or 0
    dg  = status.get('daily_goal', 0) or 0
    mg  = status.get('monthly_goal', 0) or 0

    lines = [
        f"🎯 *目標達成率*  _({status.get('date','')})_",
        f"{'─' * 30}",
        "",
    ]

    lines.append(f"💰 *今日業績*  `NT$ {rev:,.0f}`")
    lines.append("")

    if dg:
        pct     = status.get('daily_pct', 0) or 0
        gap     = dg - rev
        bar_len = min(10, max(0, int(pct // 10)))
        bar     = '█' * bar_len + '░' * (10 - bar_len)

        # 顏色 emoji
        if pct >= 100:
            status_emoji = '🏆'
            status_text  = f"*超標達成！* 🎉  超出 `NT$ {abs(gap):,.0f}`"
        elif pct >= 80:
            status_emoji = '🟢'
            status_text  = f"接近達標  還差 `NT$ {gap:,.0f}`"
        elif pct >= 50:
            status_emoji = '🟡'
            status_text  = f"進度落後  還差 `NT$ {gap:,.0f}`"
        else:
            status_emoji = '🔴'
            status_text  = f"需要加速  還差 `NT$ {gap:,.0f}`"

        lines.append(f"📌 *日目標*  `NT$ {dg:,.0f}`")
        lines.append(f"   `[{bar}]` *{pct:.1f}%*  {status_emoji}")
        lines.append(f"   {status_text}")
        lines.append("")
    else:
        lines.append("_⚙️ 尚未設定日目標_")
        lines.append("_用 `/goal 200000` 設定每日目標_")
        lines.append("")

    def _goal_bar(pct, goal, actual, label, emoji):
        if not goal:
            return None
        gap     = goal - actual
        bar_len = min(10, max(0, int(pct // 10)))
        bar     = '█' * bar_len + '░' * (10 - bar_len)
        suffix  = f"  🎉 超標 `NT$ {abs(gap):,.0f}`" if gap <= 0 else f"  還差 `NT$ {gap:,.0f}`"
        return [
            f"{emoji} *{label}*  `NT$ {goal:,.0f}`  →  已達 `NT$ {actual:,.0f}`",
            f"   `[{bar}]` *{pct:.1f}%*{suffix}",
        ]

    # 週目標（P9）
    wg = status.get('weekly_goal', 0) or 0
    if wg:
        week_rows = _goal_bar(status.get('weekly_pct') or 0, wg,
                              status.get('week_rev', 0) or 0, '週目標', '📅')
        auto_tag = ' _(自動推算)_' if status.get('weekly_auto') else ''
        if week_rows:
            week_rows[0] += auto_tag
            lines += week_rows
            lines.append("")

    for rows in [
        _goal_bar(status.get('monthly_pct') or 0, mg,
                  status.get('month_rev', 0) or 0, '月目標', '📅'),
        _goal_bar(status.get('quarterly_pct') or 0, status.get('quarterly_goal', 0) or 0,
                  (status.get('year_rev', 0) or 0) / 4, '季目標', '📆'),
        _goal_bar(status.get('half_pct') or 0, status.get('half_goal', 0) or 0,
                  (status.get('year_rev', 0) or 0) / 2, '半年目標', '🗓'),
        _goal_bar(status.get('yearly_pct') or 0, status.get('yearly_goal', 0) or 0,
                  status.get('year_rev', 0) or 0, '年度目標', '🏁'),
    ]:
        if rows:
            lines += rows
            lines.append("")

    # ── 月目標倒計時 ─────────────────────────────────────────
    if mg:
        month_rev    = status.get('month_rev', 0) or 0
        days_elapsed = status.get('days_elapsed', 0) or 0
        days_total   = status.get('days_in_month', 30) or 30
        days_remain  = days_total - days_elapsed
        if days_remain > 0 and month_rev < mg:
            needed_daily = (mg - month_rev) / days_remain
            gap_pct      = (mg - month_rev) / mg * 100
            lines.append(f"{'─' * 26}")
            lines.append(f"📆 *月目標倒計時*")
            lines.append(f"   已過 {days_elapsed} 天 / 剩 {days_remain} 天")
            lines.append(f"   還差 `NT$ {mg - month_rev:,.0f}`（{gap_pct:.0f}%）")
            lines.append(f"   ✅ 每日至少需達 `NT$ {needed_daily:,.0f}`")
            if days_elapsed > 0:
                actual_daily = month_rev / days_elapsed
                diff = actual_daily - needed_daily
                if diff >= 0:
                    lines.append(f"   📈 日均 `NT$ {actual_daily:,.0f}` — *超標 +`NT$ {diff:,.0f}`*")
                else:
                    lines.append(f"   📉 日均 `NT$ {actual_daily:,.0f}` — 落後 `NT$ {abs(diff):,.0f}`")
        elif days_remain > 0 and month_rev >= mg:
            lines.append(f"🎉 *月目標已達成！* 超出 `NT$ {month_rev - mg:,.0f}`，剩 {days_remain} 天繼續衝！")

    if not any([mg, status.get('quarterly_goal'), status.get('half_goal'), status.get('yearly_goal')]):
        lines.append("_⚙️ 尚未設定長期目標_")
        lines.append("_點選 🎯 目標管理 → 設定各週期目標_")

    return "\n".join(lines)


def _short_id(pid: str) -> str:
    """DDABGC-A900H854P-000 → A900H854P（取中段，較易讀）"""
    parts = str(pid).split('-')
    return parts[1] if len(parts) >= 3 else str(pid)[:12]


def fmt_restock_forecast(items: list) -> str:
    """補貨預測報告格式化"""
    if not items:
        return "⚠️ *補貨預測*\n\n暫無足夠銷售資料（需 3 天以上紀錄）"
    high = [i for i in items if i['urgency'] == 'HIGH']
    med  = [i for i in items if i['urgency'] == 'MED']
    low  = [i for i in items if i['urgency'] == 'LOW']
    lines = [
        "📦 *補貨預測報告*",
        f"{'─' * 26}",
        f"_基於近 7 日銷售速度分析_",
        "",
    ]
    if high:
        lines.append("🔴 *緊急補貨（高銷速 + 加速中）*")
        for i in high[:6]:
            accel_s = f"↑{i['accel']:.0f}%" if i['accel'] > 0 else f"↓{abs(i['accel']):.0f}%"
            lines.append(
                f"  🔥 *{i['name'][:22]}*\n"
                f"     日均 {i['daily_vel']:.1f} 件  趨勢 {accel_s}  週業績 `NT$ {i['rev7']:,.0f}`"
            )
        lines.append("")
    if med:
        lines.append("🟡 *建議補貨（中速 or 快速加速）*")
        for i in med[:5]:
            accel_s = f"↑{i['accel']:.0f}%" if i['accel'] > 0 else f"↓{abs(i['accel']):.0f}%"
            lines.append(f"  ⚡ *{i['name'][:22]}*  日均 {i['daily_vel']:.1f} 件  {accel_s}")
        lines.append("")
    if low:
        lines.append(f"🟢 *持續追蹤（{len(low)} 件）*  銷售平穩，暫無急迫性")
    lines.append("")
    lines.append("_💡 高速商品建議備貨 ≥ 14 日量；中速備貨 ≥ 7 日_")
    return "\n".join(lines)


def fmt_category_detail(category: str, items: list, date_label: str = '') -> str:
    """分類業績鑽取格式化"""
    if not items:
        return f"⚠️ *{category}* 分類無細項資料"
    total_rev = sum(i['revenue'] for i in items)
    period    = f"  _{date_label}_" if date_label else ''
    lines = [
        f"🗂 *{category} 分類業績*{period}",
        f"{'─' * 26}",
        f"共 {len(items)} 個子類  合計 `NT$ {total_rev:,.0f}`",
        "",
    ]
    medals = ['🥇','🥈','🥉','④','⑤','⑥','⑦','⑧','⑨','⑩']
    for i, item in enumerate(items[:10]):
        pct = item['revenue'] / total_rev * 100 if total_rev else 0
        bar_len = min(8, max(1, int(pct / 12.5)))
        bar = '█' * bar_len + '░' * (8 - bar_len)
        medal = medals[i] if i < 10 else str(i + 1)
        lines.append(
            f"  {medal} *{item['cat']}*\n"
            f"     `{bar}` {pct:.1f}%  `NT$ {item['revenue']:,.0f}`  {item['products']}商品  {item['qty']}件"
        )
    return "\n".join(lines)


def fmt_promo_comparison(data: dict, label: str = '') -> str:
    """促銷效果比較格式化"""
    if not data or not data.get('promo'):
        return "⚠️ *促銷比較*\n\n查無資料，請確認日期範圍"
    promo = data['promo']
    pre   = data['pre']
    rev_lift = data.get('rev_lift', 0)
    ord_lift = data.get('ord_lift', 0)
    lift_emoji = '📈' if rev_lift >= 0 else '📉'
    lines = [
        f"🎉 *促銷活動效益分析*",
        f"{'─' * 26}",
        f"活動期間：`{promo['start']}` ~ `{promo['end']}`（{promo['days']}天）",
        f"對比前期：`{pre['start']}` ~ `{pre['end']}`",
        "",
        f"{'─' * 26}",
        f"💰 *業績比較*",
        f"   活動期：`NT$ {promo['revenue']:,.0f}`  訂單 {promo['orders']} 筆",
        f"   前同期：`NT$ {pre['revenue']:,.0f}`   訂單 {pre['orders']} 筆",
        f"   {lift_emoji} 業績成長 *{rev_lift:+.1f}%*  訂單成長 *{ord_lift:+.1f}%*",
        "",
    ]
    if promo.get('top_products'):
        lines.append("🏆 *活動期間熱銷 TOP5*")
        for i, p in enumerate(promo['top_products'][:5]):
            medals = ['🥇','🥈','🥉','④','⑤']
            lines.append(f"  {medals[i]} {p['name'][:22]}  `NT$ {p['revenue']:,.0f}`")
    lines.append("")
    # 效益評估
    if rev_lift >= 20:
        lines.append("✅ *效益優良！* 建議複製此促銷模式")
    elif rev_lift >= 5:
        lines.append("🟡 *效益普通* 可調整折扣力度或品項")
    else:
        lines.append("🔴 *效益不佳* 建議檢視促銷定價與商品組合")
    return "\n".join(lines)


def track_competitor_price_changes(results: list) -> list:
    """追蹤 momo 競品價格變動，回傳降價警報清單"""
    if not results:
        return []
    try:
        import redis as _redis
        r = _redis.Redis(host='localhost', port=6379, db=12, socket_connect_timeout=2)
        r.ping()
        changes = []
        for item in results:
            momo_id = str(item.get('momo_icode') or item.get('momo_id') or item.get('id', ''))
            if not momo_id:
                continue
            curr_price = float(item.get('momo_price') or 0)
            if curr_price <= 0:
                continue
            key      = f"price_hist:{momo_id}"
            prev_raw = r.get(key)
            if prev_raw:
                prev_price = float(prev_raw)
                if prev_price > 0:
                    pct = (curr_price - prev_price) / prev_price * 100
                    if pct <= -5:   # momo 降價 ≥5% → PChome 需注意
                        changes.append({
                            'name':       item.get('momo_name', '')[:28],
                            'prev_price': prev_price,
                            'curr_price': curr_price,
                            'pct':        round(pct, 1),
                            'momo_id':    momo_id,
                        })
            # 更新快照（保留 8 天）
            r.setex(key, 8 * 86400, str(curr_price))
        return changes
    except Exception as _e:
        sys_log.warning(f"[price_track] Redis 不可用：{_e}")
        return []


def fmt_monthly(ms: dict) -> str:
    """月份業績格式化"""
    if not ms.get('found'):
        return (f"⚠️ *{ms.get('month','?')} 月份無業績資料*\n\n"
                f"此月份資料尚未匯入，或超出資料範圍。\n"
                f"_使用 /history 查看所有可用月份_")

    month  = ms['month']
    rev    = ms['revenue']
    orders = ms['orders']
    days   = ms['days_with_data']
    avg_d  = rev / days if days else 0
    avg_o  = ms['avg_order']
    margin = ms['gross_margin']

    # 月進度 bar（以最高日為基準）
    daily = ms.get('daily', [])
    max_d_rev = max((d['revenue'] for d in daily), default=1)
    WEEKDAYS_ZH = ['週一', '週二', '週三', '週四', '週五', '週六', '週日']

    lines = [
        f"📅 *{month} 月份業績報告*",
        f"{'─' * 30}",
        "",
        f"💰 *月業績*　`NT$ {rev:,.0f}`",
        f"📦 月訂單　`{orders:,}` 筆  |  有資料 `{days}` 天",
        f"🛒 日均業績　`NT$ {avg_d:,.0f}`",
        f"🛒 客單均價　`NT$ {avg_o:,.0f}`",
        f"📈 整月毛利率　`{margin:.1f}%`",
        "",
    ]

    if daily:
        lines.append(f"📊 *逐日業績*")
        for i, d in enumerate(daily):
            bar_len = max(1, int(d['revenue'] / max_d_rev * 8))
            bar     = '█' * bar_len + '·' * (8 - bar_len)
            # 漲跌
            if i > 0:
                prev = daily[i - 1]['revenue']
                chg  = (d['revenue'] - prev) / prev * 100 if prev else 0
                chg_str = f" {'▲' if chg >= 0 else '▼'}{abs(chg):.0f}%"
            else:
                chg_str = ''
            try:
                from datetime import datetime as dt
                d_obj = dt.strptime(d['date'].replace('/', '-'), '%Y-%m-%d')
                wday  = WEEKDAYS_ZH[d_obj.weekday()]
            except Exception:
                wday = ''
            lines.append(
                f"  `{d['date']}` {wday}  `{bar}`  `NT$ {d['revenue']:>10,.0f}`{chg_str}"
            )
        lines.append("")

    if ms.get('top_products'):
        lines.append(f"🏆 *月熱銷 TOP10*")
        total_rev = rev
        for i, p in enumerate(ms['top_products'][:10]):
            medal = MEDALS[i] if i < len(MEDALS) else f"{i+1}."
            pid   = p.get('id', '') or ''
            sid   = _short_id(pid)
            link  = _pchome_link(pid, p['name'], 22)
            pct   = p['revenue'] / total_rev * 100 if total_rev else 0
            lines.append(f"  {medal} {link}")
            lines.append(f"      🆔 `{sid}`  💰 `NT$ {p['revenue']:,.0f}`  佔 {pct:.1f}%  📦{p.get('qty',0)}件")
        lines.append("")

    if ms.get('top_vendors'):
        lines.append(f"🏭 *月熱銷廠商 TOP10*")
        for i, v in enumerate(ms['top_vendors'][:10]):
            medal = MEDALS[i] if i < len(MEDALS) else f"{i+1}."
            pct   = v['revenue'] / rev * 100 if rev else 0
            lines.append(f"  {medal} {_esc(v['name'][:22])}  `NT$ {v['revenue']:,.0f}`  {pct:.1f}%")

    return "\n".join(lines)


def fmt_strategy(items, date_str=''):
    if not items:
        return "⚠️ *暫無策略資料*\n\n請確認商品業績資料已匯入。"

    GROUP_META = {
        '加碼': ('🔥', '加碼爆量',  '內外皆熱 → 立刻加大廣告預算、補庫存',    '#FF5722'),
        '機會': ('💡', '把握機會',  '外部熱搜 → 快速提升曝光，搶奪市佔',     '#2196F3'),
        '收割': ('⚡', '趁勢收割',  '內部增長 → 鎖定高毛利，衝刺轉換率',     '#9C27B0'),
        '觀察': ('⚠️', '謹慎觀察',  '內外皆疲 → 停止加碼，評估庫存去化',     '#FF9800'),
        '持穩': ('✅', '穩定經營',  '表現平穩 → 維持現狀，小幅優化素材',     '#4CAF50'),
    }
    ORDER = ['加碼', '機會', '收割', '觀察', '持穩']

    groups: dict = {k: [] for k in ORDER}
    for s in items:
        key = s.get('strategy', '持穩')
        if key in groups:
            groups[key].append(s)
        else:
            groups['持穩'].append(s)

    title = f"🧬 *商品策略矩陣*"
    if date_str:
        title += f"  _({date_str})_"
    lines = [title, ""]

    # 計算各組商品數 & 業績小計
    summary_parts = []
    for k in ORDER:
        if groups[k]:
            meta = GROUP_META[k]
            summary_parts.append(f"{meta[0]}{k}×{len(groups[k])}")
    if summary_parts:
        lines.append("_分佈：" + "  ".join(summary_parts) + "_")
        lines.append("")

    for key in ORDER:
        group_items = groups[key]
        if not group_items:
            continue
        meta = GROUP_META[key]
        emoji, label, advice, _ = meta
        group_rev = sum(s['revenue'] for s in group_items)
        lines.append(f"{emoji} *{label}*  _{advice}_")
        lines.append(f"   共 {len(group_items)} 件 | 業績合計 `NT$ {group_rev:,.0f}`")
        for s in group_items:
            pid    = s.get('id', '') or ''
            sid    = _short_id(pid)
            link   = _pchome_link(pid, s['name'], 20)
            rev    = s['revenue']
            growth = s.get('growth', 0)
            if growth and growth != 0:
                g_val  = abs(growth) * 100
                g_str  = f"  週{'▲' if growth > 0 else '▼'}{g_val:.0f}%"
                g_emoji = '📈' if growth > 0 else '📉'
            else:
                g_str  = ''
                g_emoji = ''
            lines.append(f"  › {link}")
            lines.append(f"    🆔 `{sid}`  💰 `NT$ {rev:,.0f}`{g_str} {g_emoji}")
        lines.append("")

    lines.append("_💡 策略：內部週環比 × 外部 Google 熱搜雙維度分析_")
    return "\n".join(lines).rstrip()


# ── 簡報生成 ──────────────────────────────────────────────────

def _clean_ai_text(text: str) -> str:
    """清理 AI 輸出：移除 Markdown 語法、去除開場白"""
    import re
    # 去除 **bold**、*italic*、`code`
    text = re.sub(r'\*{1,3}([^*\n]+)\*{1,3}', r'\1', text)
    text = re.sub(r'`([^`\n]+)`', r'\1', text)
    # 去除 ## 標題符號
    text = re.sub(r'^#{1,4}\s*', '', text, flags=re.MULTILINE)
    # 去除 AI 慣用開場白
    text = re.sub(
        r'^(好的[，,。]?|以下是.*?[:：]|針對.*?分析[如下如下]?[:：]?|'
        r'根據.*?資料[，,]?|以下為.*?[:：])\s*\n?',
        '', text, flags=re.IGNORECASE
    )
    return text.strip()


def _ppt_ai_analysis(prompt_data: str, report_type: str = '') -> str:
    """
    用 NIM DeepSeek 生成簡報 AI 分析文字
    （批次任務用 NIM，節省 Gemini 即時對話額度）
    """
    is_strategy   = '策略' in report_type
    is_competitor = '競品' in report_type
    is_promo      = '促銷' in report_type

    # ── 格式鐵律（所有 prompt 共用後綴）────────────────────────
    FORMAT_RULES = (
        "\n\n【輸出格式鐵律 — 絕對遵守】\n"
        "1. 禁止使用任何 Markdown 語法：禁止 **粗體**、*斜體*、`程式碼`、## 標題\n"
        "2. 段落標題用【】全形括號，例如：【整體競爭態勢】\n"
        "3. 開頭直接進入分析內容，禁止「好的」「以下是」「針對您的資料」等 AI 慣用語\n"
        "4. 每個建議條目以 ✅ 開頭\n"
        "5. 繁體中文，語氣專業、精準、業績導向"
    )

    if is_promo:
        sys_instruction = (
            "你是資深電商活動行銷策略分析師，擁有 10 年以上台灣電商促銷活動策劃與效益評估實戰經驗，"
            "精通促銷活動 ROI 分析、商品選品策略、消費者行為洞察，深度熟悉美妝、保健、母嬰等品類的"
            "活動規劃模式。\n\n"
            f"請針對以下{report_type}資料，輸出一份完整專業的促銷效益分析報告，結構如下：\n\n"
            "【整體活動評估】（3-4句）\n"
            "以業績成長率、訂單成長率、客單價變化、毛利率表現為核心，綜合評估本次活動效益等級（優良/良好/普通/偏弱/不佳），"
            "點出最顯著的亮點或警訊，並與業界平均促銷效益（10~30%業績提升為正常區間）作比較。\n\n"
            "【關鍵發現】（4-5句）\n"
            "分析活動期間「新晉商品」的表現意義、客單價變化背後的消費行為訊號、"
            "毛利率壓縮或提升的結構性原因，以及訂單與業績成長率的背離（若有）代表什麼。\n\n"
            "【市場信號解讀】（2-3句）\n"
            "結合台灣電商促銷節奏（雙11/母親節/年中慶等）與品類季節性，說明本次活動時機的優劣，"
            "以及消費者對該價格帶的敏感度判斷。\n\n"
            "【方案A：短期優化（活動後1週）】（3條，每條以 ✅ 開頭）\n"
            "針對本次活動結果的立即行動：庫存管理、廣告預算調整、回購引導、售後溝通。\n"
            "每條含具體商品/品類名稱或量化目標。\n\n"
            "【方案B：中期強化（下次活動）】（3條，每條以 ✅ 開頭）\n"
            "針對下次促銷活動的優化方向：商品組合、滿額門檻設計、新晉商品扶植、"
            "跨品類搭配促銷策略，含預期效益（轉換率↑/客單↑/毛利改善）。\n\n"
            "【方案C：長期結構改善（季度層級）】（3條，每條以 ✅ 開頭）\n"
            "從商業模式角度提升促銷健康度：RFM 分群精準投放、忠誠訂閱制降低促銷依賴、"
            "促銷效益基準線 KPI 建立、供應鏈協同提升毛利空間。\n\n"
            "【風險預警】（1-2句）\n"
            "指出最大潛在風險（毛利侵蝕/庫存積壓/價格形象破壞），提出防禦建議。\n\n"
            "要求：每段必須引用至少一個具體數字，全文不超過 600 字，語氣如資深顧問報告。"
            + FORMAT_RULES
        )
        max_tokens = 1400
    elif is_strategy:
        sys_instruction = (
            "你是資深電商策略分析師（10年以上經驗），精通 BCG 矩陣、商品組合管理、電商行銷策略。\n"
            f"請針對以下{report_type}資料，輸出一份繁體中文專業策略報告，結構如下：\n\n"
            "【業績解讀】（2-3句）總結核心業績表現，點出最關鍵的亮點與警訊。\n\n"
            "【策略矩陣分析】（3-4句）解讀矩陣分佈，指出哪類商品最值得關注，成長動因為何。\n\n"
            "【市場信號整合】（2-3句）結合外部市場情報，說明趨勢對自家業績的影響。\n\n"
            "【本期 TOP3 行動建議】（3條，每條以 ✅ 開頭）具體可執行，含量化目標或時程。\n\n"
            "【風險預警】（1-2句）指出最大潛在風險，提出防禦建議。\n\n"
            "要求：每段引用具體數字，不超過 400 字。"
            + FORMAT_RULES
        )
        max_tokens = 900
    elif is_competitor:
        sys_instruction = (
            "你是資深電商競品策略分析師，專精美妝（開架/專櫃）、保健食品、母嬰用品，"
            "具備 10 年以上台灣電商市場實戰經驗，深度熟悉 PChome、momo 競爭生態、"
            "台灣消費者行為與美妝/保健品類購買決策模式。\n\n"
            "═══════════════════════════════\n"
            "角色設定（絕對不可違反）\n"
            "  我方 = PChome　　競品 = momo\n"
            "  price_diff = momo售價 − PChome售價\n"
            "  正值 → momo偏貴 → PChome定價優勢 → 加強曝光與轉換\n"
            "  負值 → momo較便宜 → 競品威脅 → 研擬差異化因應策略\n"
            "═══════════════════════════════\n\n"
            f"請以 PChome 視角，針對以下{report_type}輸出一份專業競品分析報告：\n\n"
            "【整體競爭態勢】（3-4句）\n"
            "從 PChome 角度評估本期整體定價競爭力，引用平均價差百分比，"
            "指出最關鍵亮點與警訊，並結合美妝/保健/母嬰市場脈動說明意涵。\n\n"
            "【PChome 定價優勢商品深度解析】（4-5句）\n"
            "點名具體優勢商品與品類（如施巴、ESTEE LAUDER 等），"
            "推測 PChome 具優勢的可能原因（進貨量優勢、活動定價、品牌授權），"
            "給出具體的曝光放大策略：首頁置頂、搜尋關鍵字投放、會員專案推廣。\n\n"
            "【momo 威脅商品應對方案】（4-5句）\n"
            "點名具體威脅商品，判斷 momo 低價策略來源（平台補貼/獨家代理/批量進貨），"
            "提出 PChome 差異化應對：加值服務（快速到貨/禮盒包裝）、"
            "組合促銷、PChome 幣回饋、VIP 會員專屬折扣，"
            "避免純粹降價而犧牲毛利。\n\n"
            "【美妝/保健/母嬰品類專項洞察】（3-4句）\n"
            "針對本期出現的具體商品，結合台灣市場趨勢深度分析：\n"
            "美妝：成分透明化趨勢、敏感肌/無添加需求、社群口碑行銷；\n"
            "保健：機能性訴求、族群細分（銀髮/運動/女性）、定期訂購轉換；\n"
            "母嬰：安全認證標章、日韓品牌偏好、媽媽社群影響力。\n\n"
            "【本期 TOP3 業績導向行動建議】（3條，每條以 ✅ 開頭）\n"
            "每條包含：具體商品或品類 + 行動方向 + 預期業績效益（轉換率↑/客單價↑/市佔↑）。\n\n"
            "要求：每段必須引用至少一個具體數字或商品名，不超過 500 字，語氣如資深顧問報告。"
            + FORMAT_RULES
        )
        max_tokens = 1200
    else:
        sys_instruction = (
            f"你是資深電商策略顧問，請根據業績資料和外部市場情報，"
            f"為{report_type}撰寫精準策略分析與行動建議。\n\n"
            "【整體業績解讀】（2句）點出核心表現與最大亮點。\n\n"
            "【市場機會與風險】（2-3句）結合外部情報說明當前機會點與潛在風險。\n\n"
            "【TOP3 行動建議】（3條，每條以 ✅ 開頭）具體可執行，含量化目標。\n\n"
            "要求：引用具體數字、250字以內。"
            + FORMAT_RULES
        )
        max_tokens = 600

    def _call_gemini(prompt: str, tokens: int) -> str:
        r = requests.post(
            f"{GEMINI_BASE_URL}/{GEMINI_MODEL}:generateContent?key={GEMINI_API_KEY}",
            headers={'Content-Type': 'application/json'},
            json={
                'contents': [{'parts': [{'text': prompt}]}],
                'generationConfig': {'maxOutputTokens': tokens, 'temperature': 0.35},
            },
            timeout=40,
        )
        r.raise_for_status()
        return (r.json().get('candidates', [{}])[0]
                .get('content', {}).get('parts', [{}])[0]
                .get('text', '').strip())

    if not NVIDIA_API_KEY:
        if GEMINI_API_KEY:
            try:
                raw = _call_gemini(f"{sys_instruction}\n\n--- 資料 ---\n{prompt_data}", max_tokens)
                return _clean_ai_text(raw)
            except Exception as e:
                sys_log.warning(f"[PPT] Gemini error: {e}")
        return '（AI 分析暫不可用，請確認 API Key 設定）'

    # ── NIM (快速失敗 20s，失敗後 fallback Gemini) ────────────
    try:
        r = requests.post(
            f'{NVIDIA_BASE_URL}/chat/completions',
            headers={'Authorization': f'Bearer {NVIDIA_API_KEY}',
                     'Content-Type': 'application/json'},
            json={
                'model': CHAT_MODEL,
                'messages': [
                    {'role': 'system', 'content': sys_instruction},
                    {'role': 'user',   'content': f'--- 資料 ---\n{prompt_data}'},
                ],
                'max_tokens': max_tokens,
                'temperature': 0.35,
            },
            timeout=25,
        )
        r.raise_for_status()
        result_text = _clean_ai_text(r.json()['choices'][0]['message']['content'])
        # ── PPT 分析自動入知識庫 ──────────────────────────────
        if _LEARNING_ENABLED and result_text:
            import threading as _thr
            _thr.Thread(
                target=store_insight,
                args=(datetime.now(TAIPEI_TZ).strftime('%Y-%m-%d'),
                      report_type or 'analysis', result_text),
                daemon=True
            ).start()
        return result_text
    except Exception as e:
        sys_log.warning(f"[PPT] NIM unavailable ({type(e).__name__}), fallback Gemini")

    # ── Gemini fallback ───────────────────────────────────────
    if GEMINI_API_KEY:
        try:
            raw = _call_gemini(f"{sys_instruction}\n\n--- 資料 ---\n{prompt_data}", max_tokens)
            result_text = _clean_ai_text(raw)
            if _LEARNING_ENABLED and result_text:
                import threading as _thr
                _thr.Thread(
                    target=store_insight,
                    args=(datetime.now(TAIPEI_TZ).strftime('%Y-%m-%d'),
                          report_type or 'analysis', result_text),
                    daemon=True
                ).start()
            return result_text
        except Exception as e2:
            sys_log.error(f"[PPT] Gemini fallback error: {e2}")
    return '（AI 分析暫時無法使用，請稍後重試）'


def _generate_ppt_cmd(sub_type: str, sub_arg: str, _chat_id: int, target: str) -> str:
    """依 sub_type 生成對應 pptx，回傳檔案路徑"""
    try:
        from services.ppt_generator import (
            generate_daily_ppt, generate_weekly_ppt,
            generate_monthly_ppt, generate_strategy_ppt,
            generate_competitor_ppt, generate_promo_ppt,
            check_pptx_available
        )
    except ImportError:
        raise RuntimeError("ppt_generator 模組不可用，請確認 python-pptx 已安裝")

    if not check_pptx_available():
        raise RuntimeError("python-pptx 未安裝，請執行：pip install python-pptx")

    now = datetime.now(TAIPEI_TZ)

    # ── MCP 外部情報（所有報告共用）──────────────────────────
    try:
        mcp_text = build_mcp_context('', [])
    except Exception:
        mcp_text = ''

    if sub_type in ('daily', '日報'):
        # sub_arg 若有效日期格式則用，否則取最新有資料的日期
        if sub_arg and re.fullmatch(r'\d{4}[/-]\d{1,2}[/-]\d{1,2}', sub_arg):
            date_str = normalize_date(sub_arg)
        else:
            date_str = latest_date() or now.strftime('%Y/%m/%d')

        sales        = query_sales(date_str)
        top_products = query_top_products(date_str, 10)
        top_vendors  = query_top_vendors(date_str, 5)
        weekly       = query_weekly_trend()

        data_summary = (
            f"日期：{date_str}\n"
            f"業績：NT$ {float(sales.get('revenue', 0)):,.0f} | "
            f"訂單：{sales.get('orders', '-')}筆 | 毛利率：{sales.get('gross_margin', 0):.1f}%\n"
            f"熱銷商品：" + " / ".join(
                f"{p['name']}(NT${p['revenue']:,.0f})" for p in top_products[:5]) + "\n"
            f"外部情報：{mcp_text[:500]}"
        )
        ai_text = _ppt_ai_analysis(data_summary, '日報')
        db_data = {
            'sales': sales, 'top_products': top_products,
            'top_vendors': top_vendors, 'weekly': weekly, 'mcp': mcp_text,
        }
        return generate_daily_ppt(date_str, db_data, ai_text)

    elif sub_type in ('weekly', '週報'):
        weekly      = query_weekly_trend()
        top_products = query_top_products(target, 10)
        top_vendors  = query_top_vendors(target, 10)
        strat        = analyze_product_strategy(target, 10)

        data_summary = (
            f"週期：{now.strftime('%Y/%m/%d')} 週報\n"
            f"週業績：NT$ {sum(w['revenue'] for w in weekly):,.0f}\n"
            f"逐日：" + " | ".join(f"{w['date']}(NT${w['revenue']:,.0f})" for w in weekly) + "\n"
            f"熱銷：" + " / ".join(f"{p['name']}(NT${p['revenue']:,.0f})" for p in top_products[:5]) + "\n"
            f"外部情報：{mcp_text[:500]}"
        )
        ai_text = _ppt_ai_analysis(data_summary, '週報')
        db_data = {
            'weekly': weekly, 'top_products': top_products,
            'top_vendors': top_vendors, 'strategy': strat, 'mcp': mcp_text,
        }
        return generate_weekly_ppt(db_data, ai_text)

    elif sub_type in ('monthly', '月報'):
        # sub_arg 格式 YYYY/MM 或 YYYY-MM
        if sub_arg:
            parts = sub_arg.replace('-', '/').split('/')
            yr, mo = int(parts[0]), int(parts[1]) if len(parts) >= 2 else now.month
        else:
            yr, mo = now.year, now.month

        ms = query_monthly_summary(yr, mo)
        # 月報分類業績（用月份 LIKE 查詢）
        top_cats = query_category_monthly(yr, mo, lim=8)
        # 把分類資料注入 ms，供 PPT P4 使用
        ms['top_categories'] = top_cats
        data_summary = (
            f"月份：{yr}/{mo:02d}\n"
            f"月業績：NT$ {ms.get('revenue', 0):,.0f} | "
            f"訂單：{ms.get('orders', 0)} | 毛利率：{ms.get('gross_margin', 0):.1f}%\n"
            f"熱銷：" + " / ".join(
                f"{p['name']}(NT${p['revenue']:,.0f})"
                for p in ms.get('top_products', [])[:5]) + "\n"
            f"分類：" + " / ".join(f"{c['cat']}(NT${c['revenue']:,.0f})" for c in top_cats[:3]) + "\n"
            f"外部情報：{mcp_text[:500]}"
        )
        ai_text = _ppt_ai_analysis(data_summary, '月報')
        db_data = {'monthly': ms, 'mcp': mcp_text}
        return generate_monthly_ppt(yr, mo, db_data, ai_text)

    elif sub_type in ('strategy', '策略'):
        # 支援: strategy / strategy 2026/04/10 / strategy weekly /
        #       strategy monthly [2026/03] / strategy quarterly /
        #       strategy half / strategy yearly
        period_label = '日報'
        if sub_arg and re.fullmatch(r'\d{4}[/-]\d{1,2}[/-]\d{1,2}', sub_arg):
            # 指定單日
            date_str = normalize_date(sub_arg)
            start_str, end_str = date_str, date_str
            period_label = f'{date_str} 日策略'
        elif sub_arg in ('weekly', 'week', '週', '週報'):
            end_str = latest_date() or now.strftime('%Y/%m/%d')
            start_str = (datetime.strptime(end_str.replace('/', '-'), '%Y-%m-%d')
                         - timedelta(days=6)).strftime('%Y/%m/%d')
            date_str = f'{start_str}~{end_str}'
            period_label = '週策略（近7日）'
        elif sub_arg in ('monthly', 'month', '月', '月報') or (
                sub_arg and re.match(r'\d{4}[/-]\d{1,2}$', sub_arg)):
            m_parts = sub_arg.replace('-', '/').split('/') if sub_arg and '/' in sub_arg else []
            if len(m_parts) == 2:
                yr_s, mo_s = int(m_parts[0]), int(m_parts[1])
            else:
                yr_s, mo_s = now.year, now.month
            import calendar
            last_day = calendar.monthrange(yr_s, mo_s)[1]
            start_str = f'{yr_s}/{mo_s:02d}/01'
            end_str   = f'{yr_s}/{mo_s:02d}/{last_day:02d}'
            date_str  = f'{yr_s}/{mo_s:02d}'
            period_label = f'{yr_s}/{mo_s:02d} 月策略'
        elif sub_arg in ('quarterly', 'quarter', 'q', '季', '季報'):
            end_str = latest_date() or now.strftime('%Y/%m/%d')
            start_str = (datetime.strptime(end_str.replace('/', '-'), '%Y-%m-%d')
                         - timedelta(days=89)).strftime('%Y/%m/%d')
            date_str = f'{start_str}~{end_str}'
            period_label = '季策略（近90日）'
        elif sub_arg in ('half', 'h1', 'h2', '半年', '半年報'):
            end_str = latest_date() or now.strftime('%Y/%m/%d')
            start_str = (datetime.strptime(end_str.replace('/', '-'), '%Y-%m-%d')
                         - timedelta(days=179)).strftime('%Y/%m/%d')
            date_str = f'{start_str}~{end_str}'
            period_label = '半年策略（近180日）'
        elif sub_arg in ('yearly', 'year', 'annual', '年', '年報'):
            end_str = latest_date() or now.strftime('%Y/%m/%d')
            start_str = (datetime.strptime(end_str.replace('/', '-'), '%Y-%m-%d')
                         - timedelta(days=364)).strftime('%Y/%m/%d')
            date_str = f'{start_str}~{end_str}'
            period_label = '年度策略（近365日）'
        else:
            # 預設：最新單日
            date_str = latest_date() or now.strftime('%Y/%m/%d')
            start_str, end_str = date_str, date_str
            period_label = f'{date_str} 日策略'

        # 查詢資料
        if start_str == end_str:
            sales        = query_sales(date_str)
            top_products = query_top_products(date_str, 15)
            strat        = analyze_product_strategy(date_str, 20)
        else:
            rng          = query_date_range(start_str, end_str)
            sales        = {'revenue': rng.get('revenue', 0),
                            'orders': rng.get('orders', 0),
                            'gross_margin': rng.get('gross_margin', 0),
                            'avg_order': rng.get('avg_order', 0)}
            top_products = query_top_products_range(start_str, end_str, 15)
            strat        = _analyze_strategy_range(start_str, end_str, top_products)

        # 策略分佈統計
        from collections import Counter
        strat_cnt = Counter(s['strategy'] for s in strat)
        strat_detail = "\n".join(
            f"  [{k}×{v}件] " + " / ".join(
                f"{s['name']}(NT${s['revenue']:,.0f})"
                for s in strat if s['strategy'] == k)[:3]
            for k, v in strat_cnt.most_common()
        )

        data_summary = (
            f"分析週期：{period_label}\n"
            f"業績：NT$ {float(sales.get('revenue', 0)):,.0f} | "
            f"訂單：{sales.get('orders', 0)}筆 | "
            f"毛利率：{sales.get('gross_margin', 0):.1f}% | "
            f"客單價：NT$ {float(sales.get('avg_order', 0)):,.0f}\n\n"
            f"策略矩陣分佈：\n{strat_detail}\n\n"
            f"TOP5商品：" + " / ".join(
                f"{p['name']}(NT${p['revenue']:,.0f})" for p in top_products[:5]) + "\n\n"
            f"外部市場信號：{mcp_text[:600]}"
        )
        ai_text = _ppt_ai_analysis(data_summary, f'策略簡報（{period_label}）')
        db_data = {
            'sales': sales, 'top_products': top_products,
            'strategy': strat, 'mcp': mcp_text,
            'period_label': period_label,
        }
        return generate_strategy_ppt(date_str, db_data, ai_text)

    elif sub_type in ('competitor', '競品', 'compare'):
        if not _PCHOME_AVAILABLE:
            raise RuntimeError("PChome 比價模組不可用")

        # 決定日期範圍（與 strategy 相同邏輯）
        if sub_arg in ('weekly', 'week', '週'):
            end_d   = datetime.strptime(
                (latest_date() or now.strftime('%Y/%m/%d')).replace('/', '-'), '%Y-%m-%d')
            start_d = end_d - timedelta(days=6)
            period_label = '週比較（近7日）'
        elif sub_arg in ('monthly', 'month', '月'):
            end_d   = datetime.strptime(
                (latest_date() or now.strftime('%Y/%m/%d')).replace('/', '-'), '%Y-%m-%d')
            start_d = end_d.replace(day=1)
            period_label = f'{end_d.year}/{end_d.month:02d} 月比較'
        elif sub_arg in ('quarterly', 'quarter', 'q', '季'):
            end_d   = datetime.strptime(
                (latest_date() or now.strftime('%Y/%m/%d')).replace('/', '-'), '%Y-%m-%d')
            start_d = end_d - timedelta(days=89)
            period_label = '季比較（近90日）'
        elif sub_arg in ('half', '半年'):
            end_d   = datetime.strptime(
                (latest_date() or now.strftime('%Y/%m/%d')).replace('/', '-'), '%Y-%m-%d')
            start_d = end_d - timedelta(days=179)
            period_label = '半年比較（近180日）'
        elif sub_arg in ('yearly', 'year', '年'):
            end_d   = datetime.strptime(
                (latest_date() or now.strftime('%Y/%m/%d')).replace('/', '-'), '%Y-%m-%d')
            start_d = end_d - timedelta(days=364)
            period_label = '年度比較（近365日）'
        elif sub_arg and re.match(r'\d{4}[/-]\d{1,2}[/-]\d{1,2}', sub_arg):
            # 指定日期（今日/昨日/自訂）
            d_str    = normalize_date(sub_arg)
            start_d  = end_d = datetime.strptime(d_str.replace('/', '-'), '%Y-%m-%d')
            period_label = f'{d_str} 日比較'
        else:
            # 預設：昨日日報
            yd       = (now - timedelta(days=1)).strftime('%Y/%m/%d')
            start_d  = end_d = datetime.strptime(yd.replace('/', '-'), '%Y-%m-%d')
            period_label = f'{yd} 日比較'

        date_str_for_query = start_d.strftime('%Y/%m/%d')

        # 爬取比對資料（批量掃描 TOP30）
        results = pchome_batch(_db(), top_n=30,
                               date_str=date_str_for_query)
        if results:
            pchome_save(_db(), results)

        # MCP 外部情報
        mcp_text_c = mcp_text  # 已在上方取得

        # AI 分析
        found_c   = [r for r in results if r.get('found')]
        pc_wins_c = [r for r in found_c if r.get('price_diff', 0) > 10]
        mo_wins_c = [r for r in found_c if r.get('price_diff', 0) < -10]
        avg_diff_c = (sum(r.get('price_diff_pct', 0) for r in found_c) / len(found_c)
                      if found_c else 0)
        data_summary = (
            f"【我方=PChome，競品=momo】\n"
            f"分析週期：{period_label}\n"
            f"掃描商品：{len(results)} 件  |  比對成功：{len(found_c)} 件\n"
            f"PChome定價優勢（比momo便宜）：{len(pc_wins_c)} 件  |  momo威脅（比PChome便宜）：{len(mo_wins_c)} 件\n"
            f"平均價差：{avg_diff_c:+.1f}%（正值=momo偏貴=PChome具優勢）\n\n"
            f"PChome優勢 TOP3（應加強曝光宣傳）：" + " / ".join(
                f"{r['momo_name'][:15]}（PChome便宜NT${r['price_diff']:,.0f}）"
                for r in pc_wins_c[:3]) + "\n"
            f"momo威脅 TOP3（需研擬因應策略）：" + " / ".join(
                f"{r['momo_name'][:15]}（momo便宜NT${abs(r['price_diff']):,.0f}）"
                for r in mo_wins_c[:3]) + "\n\n"
            f"外部情報：{mcp_text_c[:400]}"
        )
        ai_text = _ppt_ai_analysis(data_summary, f'競品比較簡報（{period_label}）')

        db_data = {
            'results': results,
            'period_label': period_label,
            'mcp': mcp_text_c,
        }
        return generate_competitor_ppt(period_label, db_data, ai_text)

    elif sub_type in ('promo', '促銷'):
        # sub_arg = "2026/04/01-2026/04/07"
        m_p = re.findall(r'\d{4}[/\-]\d{1,2}[/\-]\d{1,2}', sub_arg)
        if len(m_p) < 2:
            raise ValueError(f"促銷簡報需要日期範圍，例如：promo 2026/04/01-2026/04/07")
        start_s_p = normalize_date(m_p[0])
        end_s_p   = normalize_date(m_p[1])
        promo_label_p = f'{start_s_p}~{end_s_p}'
        data_p = query_promo_comparison(start_s_p, end_s_p)
        if not data_p:
            raise ValueError("查無促銷期間資料")
        pd = data_p.get('promo', {})
        prev = data_p.get('pre', {})
        tops_str = ' / '.join(
            f"{p['name'][:12]}（NT${p['revenue']:,.0f}）"
            for p in (pd.get('top_products') or [])[:3]
        )
        data_summary_p = (
            f"促銷活動期間：{start_s_p} ~ {end_s_p}（{pd.get('days',0)}天）\n"
            f"對比前期：{prev.get('start','')} ~ {prev.get('end','')}\n\n"
            f"【活動期業績】NT$ {pd.get('revenue',0):,.0f}  訂單 {pd.get('orders',0):,}筆  "
            f"毛利率 {pd.get('margin',0):.1f}%\n"
            f"【對比期業績】NT$ {prev.get('revenue',0):,.0f}  訂單 {prev.get('orders',0):,}筆  "
            f"毛利率 {prev.get('margin',0):.1f}%\n"
            f"業績成長：{data_p.get('rev_lift',0):+.1f}%  訂單成長：{data_p.get('ord_lift',0):+.1f}%\n\n"
            f"活動期熱銷 TOP3：{tops_str or '（無資料）'}\n"
            f"外部市場情報：{mcp_text[:300]}"
        )
        ai_text_p = _ppt_ai_analysis(data_summary_p, f'促銷效益分析（{promo_label_p}）')
        return generate_promo_ppt(promo_label_p, data_p, ai_text_p)

    else:
        raise ValueError(f"不支援的簡報類型：{sub_type}（支援：daily / weekly / monthly / strategy / competitor / promo）")


# ── Telegram Excel 匯入 ──────────────────────────────────────────

# 必要欄位定義
_EXCEL_REQUIRED_COLS  = ['日期', '商品ID', '商品名稱', '總業績', '數量']
_EXCEL_OPTIONAL_COLS  = ['廠商名稱', '總成本', '商品分類L1', '毛%', '折扣活動名稱', '折價券活動名稱']

def _validate_excel_format(filepath: str, filename: str) -> dict:
    """
    讀取 Excel，回傳格式驗證報告 dict：
      ok, row_count, col_count, missing_required, present_optional,
      dates, date_count, rev_total, report_type, warnings
    """
    import pandas as pd
    import os
    result = {
        'ok': False, 'row_count': 0, 'col_count': 0,
        'missing_required': [], 'present_optional': [],
        'dates': [], 'date_count': 0, 'rev_total': 0.0,
        'report_type': '未知格式', 'warnings': [],
    }
    try:
        df = pd.read_excel(filepath, engine='openpyxl', dtype=str)
        if df.empty:
            result['warnings'].append('Excel 檔案為空')
            return result

        result['row_count'] = len(df)
        result['col_count'] = len(df.columns)
        cols = df.columns.tolist()

        # 判斷報表類型
        if '即時業績' in filename and '當日' in filename:
            result['report_type'] = '當日業績報表'
        elif '即時業績' in filename and '全月' in filename:
            result['report_type'] = '全月業績報表'
        else:
            result['report_type'] = '業績報表（自動偵測）'
            result['warnings'].append('檔名不符合標準格式（建議：即時業績_當日_YYYYMMDD.xlsx）')

        # 必要欄位檢查
        result['missing_required'] = [c for c in _EXCEL_REQUIRED_COLS if c not in cols]
        result['present_optional'] = [c for c in _EXCEL_OPTIONAL_COLS if c in cols]

        # 日期分析（找日期欄）
        date_col = next((c for c in ['日期', '訂單日期', '交易日期'] if c in cols), None)
        if date_col:
            import pandas as _pd2
            dates_s = _pd2.to_datetime(df[date_col], errors='coerce').dt.strftime('%Y/%m/%d')
            result['dates'] = sorted(dates_s.dropna().unique().tolist())
            result['date_count'] = len(result['dates'])

        # 業績預覽
        if '總業績' in cols:
            try:
                result['rev_total'] = df['總業績'].apply(
                    lambda x: float(str(x).replace(',', '').strip() or '0')
                ).sum()
            except Exception:
                pass

        result['ok'] = len(result['missing_required']) == 0
    except Exception as _e:
        result['warnings'].append(f'讀取失敗：{str(_e)[:80]}')
    return result


def _fmt_excel_validation_report(filename: str, v: dict) -> str:
    """格式化 Excel 驗證報告為 Telegram 訊息"""
    ok_mark   = '✅' if v['ok'] else '❌'
    lines = [
        f"📊 *Excel 格式驗證報告*",
        f"{'━' * 24}",
        f"",
        f"📄 *檔案*：`{filename}`",
        f"📋 *類型*：{v['report_type']}",
        f"📦 *資料量*：{v['row_count']:,} 筆 × {v['col_count']} 欄",
        f"",
    ]

    # 必要欄位
    if v['missing_required']:
        missing_str = '  '.join(f'❌ {c}' for c in v['missing_required'])
        lines.append(f"❌ *必要欄位缺失*：{missing_str}")
        lines.append(f"   _缺少以上欄位，無法匯入_")
    else:
        present_str = '  '.join(f'✔ {c}' for c in _EXCEL_REQUIRED_COLS)
        lines.append(f"✅ *必要欄位*（全部通過）")
        lines.append(f"   {present_str}")

    if v['present_optional']:
        opt_str = '  '.join(f'✔ {c}' for c in v['present_optional'])
        lines.append(f"📌 *附加欄位*：{opt_str}")

    lines.append("")

    # 日期範圍
    if v['dates']:
        if v['date_count'] == 1:
            lines.append(f"📅 *涵蓋日期*：`{v['dates'][0]}`（1 天）")
        elif v['date_count'] <= 5:
            date_str = ' / '.join(v['dates'])
            lines.append(f"📅 *涵蓋日期*：{v['date_count']} 天（{date_str}）")
        else:
            lines.append(f"📅 *涵蓋日期*：{v['dates'][0]} ~ {v['dates'][-1]}（共 {v['date_count']} 天）")

    if v['rev_total']:
        lines.append(f"💰 *業績預覽*：`NT$ {v['rev_total']:,.0f}`")

    if v['warnings']:
        lines.append("")
        for w in v['warnings']:
            lines.append(f"⚠️ {w}")

    lines.append("")
    lines.append(f"{'━' * 24}")
    if v['ok']:
        lines.append(f"✅ *格式驗證通過，可以匯入！*")
        lines.append(f"_寫入後將覆蓋相同日期的現有資料_")
    else:
        lines.append(f"❌ *格式驗證失敗，無法匯入*")
        lines.append(f"_請修正上述缺失欄位後重新上傳_")
    return "\n".join(lines)


def _download_telegram_file(file_id: str, suffix: str = '.xlsx') -> str:
    """從 Telegram 下載檔案到 /tmp，回傳本地路徑；失敗回傳 ''"""
    try:
        r = requests.get(f"{BOT_API_URL}/getFile", params={'file_id': file_id}, timeout=10)
        file_path_tg = r.json().get('result', {}).get('file_path', '')
        if not file_path_tg:
            return ''
        file_url  = f"https://api.telegram.org/file/bot{BOT_TOKEN}/{file_path_tg}"
        file_data = requests.get(file_url, timeout=60).content
        import tempfile as _tf
        tmp = _tf.NamedTemporaryFile(suffix=suffix, prefix='ocbot_xl_', delete=False, dir='/tmp')
        tmp.write(file_data)
        tmp.close()
        return tmp.name
    except Exception as _e:
        sys_log.error(f"[ExcelImport] download failed: {_e}")
        return ''


def _handle_excel_import(doc: dict, chat_id: int, reply_to: int):
    """背景執行緒：下載 Excel → 驗證 → 發送驗證報告 + 確認按鈕"""
    filename = doc.get('file_name', 'upload.xlsx')
    file_id  = doc.get('file_id', '')
    file_size = doc.get('file_size', 0)

    # 大小限制 50MB
    if file_size > 50 * 1024 * 1024:
        send_message(chat_id, "⚠️ 檔案太大（上限 50MB），請壓縮後重新上傳", reply_to)
        return

    send_message(chat_id,
                 f"⏳ 正在下載 Excel 檔案...\n`{filename}`",
                 reply_to, parse_mode='Markdown')

    suffix = '.xlsx' if filename.lower().endswith('.xlsx') else '.xls'
    local_path = _download_telegram_file(file_id, suffix)
    if not local_path:
        send_message(chat_id, "❌ 檔案下載失敗，請重試", reply_to)
        return

    # 驗證格式
    v = _validate_excel_format(local_path, filename)
    report_text = _fmt_excel_validation_report(filename, v)

    if v['ok']:
        # 儲存待確認資訊
        _excel_pending[chat_id] = {
            'file_path': local_path,
            'filename':  filename,
        }
        kb = [
            [{'text': '✅ 確認匯入資料庫', 'callback_data': 'cmd:import_confirm'},
             {'text': '❌ 取消',           'callback_data': 'cmd:import_cancel'}],
        ]
        send_message(chat_id, report_text, reply_to, kb)
    else:
        # 驗證失敗 → 直接顯示報告，不提供匯入選項
        try:
            import os
            os.unlink(local_path)
        except Exception:
            pass
        send_message(chat_id, report_text, reply_to)


# ── 排程自動報告 ───────────────────────────────────────────────

def send_morning_report():
    """每日 08:30 早報 — P10 升級版：TOP15 + PPT導引按鈕"""
    try:
        now   = datetime.now(TAIPEI_TZ)
        td    = now.strftime('%Y/%m/%d')
        yd    = (now - timedelta(days=1)).strftime('%Y/%m/%d')
        wdays = ['週一', '週二', '週三', '週四', '週五', '週六', '週日']
        sales  = query_sales(yd)
        top15  = query_top_products(yd, 15)
        weekly = query_weekly_trend()

        # 計算7日均值做基準
        avg7 = 0
        if weekly and len(weekly) >= 3:
            avg7 = sum(w['revenue'] for w in weekly[-7:]) / min(7, len(weekly))

        lines = [
            f"☀️ *早安！今天是 {now.strftime('%m/%d')}（{wdays[now.weekday()]}）*",
            f"{'─' * 30}",
            "",
        ]

        if sales.get('found'):
            rev    = float(sales['revenue'])
            orders = sales.get('orders', 0) or 0
            margin = float(sales.get('gross_margin', 0))
            lines.append(f"📅 *昨日業績回顧*  _({yd})_")
            lines.append(f"   💰 業績：`NT$ {rev:,.0f}`  📦 訂單：`{orders:,}` 筆")
            lines.append(f"   🛒 客單：`NT$ {float(sales.get('avg_order',0)):,.0f}`  📈 毛利率：`{margin:.1f}%`")
            if avg7 > 0:
                diff = rev - avg7
                pct  = diff / avg7 * 100
                arrow = '▲' if diff >= 0 else '▼'
                vs_avg = f"{arrow}{abs(pct):.1f}% vs 7日均  (`NT$ {diff:+,.0f}`)"
                lines.append(f"   📊 {vs_avg}")
        else:
            lines.append(f"📅 _昨日資料尚未匯入（{yd}）_")
        lines.append("")

        if top15:
            lines.append(f"🏆 *昨日熱銷 TOP15*")
            lines.append(f"{'─' * 26}")
            for i, p in enumerate(top15):
                pid  = p.get('id', '') or ''
                sid  = _short_id(pid)
                link = _pchome_link(pid, p['name'], 20)
                rank = MEDALS[i] if i < len(MEDALS) else f"`{i+1}.`"
                lines.append(f"  {rank} {link}")
                lines.append(f"      🆔 `{sid}`  `NT$ {p['revenue']:,.0f}`  📦 {p.get('qty','-')}件")
            lines.append("")

        gs = get_goal_status(td)
        if gs.get('daily_goal'):
            dg  = gs['daily_goal']
            lines.append(f"🎯 *今日目標*  `NT$ {dg:,.0f}`")
            lines.append(f"   💪 加油衝刺！祝業績長紅！")
        elif gs.get('monthly_goal'):
            mg = gs['monthly_goal']
            lines.append(f"🎯 *月目標*  `NT$ {mg:,.0f}`")
            lines.append(f"   月累計：`NT$ {gs.get('month_rev',0):,.0f}`  (`{gs.get('monthly_pct',0) or 0:.1f}%`)")
        else:
            lines.append("💪 *今日加油！* 祝業績長紅！")
        lines.append("")

        try:
            from services.mcp_context_service import get_taiwan_weather
            taipei = get_taiwan_weather().get('weather', {}).get('臺北市', {})
            if taipei:
                wx  = taipei.get('Wx', '')
                pop = taipei.get('PoP', '')
                temp = taipei.get('temp', '') or f"{taipei.get('MinT','')}-{taipei.get('MaxT','')}°C"
                weather_line = f"🌤 *台北天氣*  {wx}"
                if temp.strip('-'):
                    weather_line += f"  {temp}"
                if pop:
                    weather_line += f"  💧降雨 {pop}%"
                lines.append(weather_line)
                if int(pop or 0) >= 60:
                    lines.append("   ☂️ _建議做室內促銷活動_")
        except Exception:
            pass

        # P10 — 導引按鈕：產出日報PPT + 查看業績數據
        morning_kb = [
            [{'text': f'📊 產出 {yd} 日報PPT', 'callback_data': f'cmd:ppt:daily {yd}'},
             {'text': '📈 業績數據',           'callback_data': f'cmd:sales:{yd}'}],
            [{'text': '🏆 完整熱銷排行',       'callback_data': f'cmd:top:{yd}'},
             {'text': '📋 下載 Excel',         'callback_data': f'cmd:report:{yd}'}],
        ]
        send_message(ALLOWED_GROUP, "\n".join(lines), keyboard=morning_kb, parse_mode='Markdown')
        sys_log.info("[OpenClawBot] 早報已發送")
    except Exception as e:
        sys_log.error(f"[OpenClawBot] 早報失敗: {e}")


def send_evening_report():
    """每日 21:00 晚報"""
    try:
        now   = datetime.now(TAIPEI_TZ)
        td    = now.strftime('%Y/%m/%d')
        sales = query_sales(td)
        weekly = query_weekly_trend()

        lines = [
            f"🌙 *今日業績收盤  {now.strftime('%m/%d')} 21:00*",
            f"{'─' * 30}",
            "",
        ]

        if sales.get('found'):
            rev    = float(sales['revenue'])
            orders = sales.get('orders', 0) or 0
            prods  = sales.get('products', 0) or 0
            avg_o  = float(sales.get('avg_order', 0))
            margin = float(sales.get('gross_margin', 0))

            lines.append(f"💰 *今日業績*  `NT$ {rev:,.0f}`")
            lines.append(f"📦 訂單 `{orders:,}` 筆  🛍 商品 `{prods:,}` 件  🛒 客單 `NT$ {avg_o:,.0f}`")
            lines.append(f"📈 毛利率  `{margin:.1f}%`")
            lines.append("")

            gs = get_goal_status(td)
            if gs.get('daily_goal'):
                pct     = gs.get('daily_pct', 0) or 0
                dg      = gs['daily_goal']
                gap     = dg - rev
                bar_len = min(10, max(0, int(pct // 10)))
                bar     = '█' * bar_len + '░' * (10 - bar_len)
                result  = "🏆 超標達成！" if gap <= 0 else f"差 `NT$ {gap:,.0f}` 達標"
                lines.append(f"🎯 *日目標達成*  `[{bar}]` *{pct:.1f}%*  {result}")
                lines.append("")

            # vs 昨日比較
            if weekly and len(weekly) >= 2:
                yd_rev = weekly[-2]['revenue'] if len(weekly) >= 2 else 0
                if yd_rev:
                    diff = rev - yd_rev
                    pct_d = diff / yd_rev * 100
                    arrow = '▲' if diff >= 0 else '▼'
                    emoji = '📈' if diff >= 0 else '📉'
                    lines.append(f"{emoji} *vs 昨日*  {arrow}`{abs(pct_d):.1f}%`  (`NT$ {diff:+,.0f}`)")
                    lines.append("")
        else:
            lines.append("⚠️ *今日業績資料尚未匯入*")
            lines.append("")

        top15_ev = query_top_products(td, 15)
        if top15_ev:
            lines.append(f"🏆 *今日熱銷 TOP15*")
            lines.append(f"{'─' * 26}")
            for i, p in enumerate(top15_ev):
                pid  = p.get('id', '') or ''
                sid  = _short_id(pid)
                link = _pchome_link(pid, p['name'], 20)
                rank = MEDALS[i] if i < len(MEDALS) else f"`{i+1}.`"
                lines.append(f"  {rank} {link}")
                lines.append(f"      🆔 `{sid}`  `NT$ {p['revenue']:,.0f}`  📦 {p.get('qty','-')}件")
            lines.append("")

        try:
            strat = analyze_product_strategy(td, 8)
            hot_list = [s for s in strat if s['strategy'] == '加碼'][:2]
            opp_list = [s for s in strat if s['strategy'] == '機會'][:2]
            if hot_list or opp_list:
                lines.append(f"🧬 *明日行動建議*")
                for s in hot_list:
                    pid_s = s.get('id', '') or ''
                    link_s = _pchome_link(pid_s, s['name'], 18)
                    lines.append(f"  🔥 加碼：{link_s}  週▲{abs(s.get('growth',0))*100:.0f}%")
                for s in opp_list:
                    pid_s = s.get('id', '') or ''
                    link_s = _pchome_link(pid_s, s['name'], 18)
                    lines.append(f"  💡 機會：{link_s}")
                lines.append("")
        except Exception:
            pass

        # P10 — 晚報導引按鈕
        evening_kb = [
            [{'text': f'📊 產出 {td} 日報PPT', 'callback_data': f'cmd:ppt:daily {td}'},
             {'text': '📈 完整業績數據',         'callback_data': f'cmd:sales:{td}'}],
            [{'text': '📋 下載 Excel 報表',     'callback_data': f'cmd:report:{td}'},
             {'text': '🧬 策略矩陣分析',         'callback_data': f'cmd:strategy:{td}'}],
        ]
        send_message(ALLOWED_GROUP, "\n".join(lines), keyboard=evening_kb, parse_mode='Markdown')
        sys_log.info("[OpenClawBot] 晚報已發送")
    except Exception as e:
        sys_log.error(f"[OpenClawBot] 晚報失敗: {e}")


def send_weekly_report():
    """每週一 09:00 週報"""
    try:
        now    = datetime.now(TAIPEI_TZ)
        td     = now.strftime('%Y/%m/%d')
        weekly = query_weekly_trend()
        WEEKDAYS_ZH = ['週一', '週二', '週三', '週四', '週五', '週六', '週日']

        lines = [
            f"📊 *週報  {now.strftime('%m/%d')}（週一）*",
            f"{'─' * 30}",
            "",
        ]

        if weekly:
            total  = sum(d.get('revenue', 0) for d in weekly)
            avg    = total / len(weekly)
            max_r  = max(d['revenue'] for d in weekly)
            min_r  = min(d['revenue'] for d in weekly)
            max_rev = max_r or 1
            lines.append(f"📅 *上週業績總覽*")
            lines.append(f"   合計：`NT$ {total:,.0f}`  日均：`NT$ {avg:,.0f}`")
            lines.append(f"   最高：`NT$ {max_r:,.0f}`  最低：`NT$ {min_r:,.0f}`")
            lines.append("")
            lines.append("*逐日明細*")
            for i, d in enumerate(weekly[-7:]):
                rev = d.get('revenue', 0)
                bar_len = max(1, int(rev / max_rev * 8))
                bar = '█' * bar_len + '·' * (8 - bar_len)
                try:
                    from datetime import datetime as dt
                    d_obj = dt.strptime(d['date'].replace('/', '-'), '%Y-%m-%d')
                    wday  = WEEKDAYS_ZH[d_obj.weekday()]
                except Exception:
                    wday = ''
                # vs 前日漲跌
                if i > 0:
                    prev = weekly[i - 1]['revenue']
                    pct_chg = (rev - prev) / prev * 100 if prev else 0
                    chg_str = f"  {'▲' if pct_chg >= 0 else '▼'}{abs(pct_chg):.1f}%"
                else:
                    chg_str = ''
                lines.append(f"  `{d['date']}`  {wday}  `{bar}`  `NT$ {rev:>10,.0f}`{chg_str}")
            lines.append("")

        top5 = query_top_products(td, 5)
        if top5:
            lines.append(f"🏆 *本週熱銷 TOP5*")
            for i, p in enumerate(top5[:5]):
                sid = _short_id(p.get('id', ''))
                lines.append(f"  {MEDALS[i]} {p['name'][:18]}  `NT$ {p['revenue']:,.0f}`")
                lines.append(f"     🆔 `{sid}`")
            lines.append("")

        lines.append(f"📋 [報表中心]({MOMO_BASE_URL}/reports)")

        # 附趨勢圖
        chart = gen_trend_chart(14)
        if chart:
            send_photo(ALLOWED_GROUP, chart, caption="\n".join(lines))
            os.unlink(chart)
        else:
            send_message(ALLOWED_GROUP, "\n".join(lines), parse_mode='Markdown')

        sys_log.info("[OpenClawBot] 週報已發送")
    except Exception as e:
        sys_log.error(f"[OpenClawBot] 週報失敗: {e}")


def check_anomalies():
    """每日 9/12/15/18 點異常偵測（整體業績 + 商品級）"""
    try:
        now = datetime.now(TAIPEI_TZ)
        td  = now.strftime('%Y/%m/%d')
        ts  = now.strftime('%H:%M')
        alerts = []

        # ── ① 整體業績異常（今日 vs 7日均） ──────────────────
        try:
            weekly = query_weekly_trend()
            today_data = next((w for w in weekly if w['date'] == td), None)
            past_data  = [w for w in weekly if w['date'] != td]
            if today_data and len(past_data) >= 3:
                avg7 = sum(w['revenue'] for w in past_data[-7:]) / len(past_data[-7:])
                if avg7 > 0:
                    pct = (today_data['revenue'] - avg7) / avg7 * 100
                    if abs(pct) >= 25:
                        direction = '📈 急升' if pct > 0 else '📉 急降'
                        tip = '建議加強曝光推廣' if pct > 0 else '⚠️ 請立即確認原因（活動下架？資料延遲？）'
                        alerts.insert(0,
                            f"🔔 *整體業績異常*  {direction}  {abs(pct):.0f}%\n"
                            f"  今日：`NT$ {today_data['revenue']:,.0f}`  "
                            f"7日均：`NT$ {avg7:,.0f}`\n"
                            f"  💡 {tip}"
                        )
        except Exception as _e:
            sys_log.warning(f"[anomaly] 整體業績檢查失敗: {_e}")

        # ── ② 商品級異常（偏差>30%） ─────────────────────────
        anomalies = query_anomalies(td)
        for a in anomalies:
            pct = a.get('pct', 0) or 0
            if abs(pct) < 30:
                continue
            direction = '📈 *急升*' if pct > 0 else '📉 *急降*'
            today_rev = a.get('today', 0)
            avg7_rev  = a.get('avg7', 0)
            sid = _short_id(a.get('id', ''))
            if avg7_rev:
                ratio = today_rev / avg7_rev
                bar_len = min(10, max(0, int(ratio * 5)))
                ratio_bar = '█' * bar_len + '░' * (10 - bar_len)
                alerts.append(
                    f"{direction} {abs(pct):.0f}%\n"
                    f"  商品：{a['name'][:20]}\n"
                    f"  🆔 `{sid}`\n"
                    f"  今日：`NT$ {today_rev:,.0f}`  7日均：`NT$ {avg7_rev:,.0f}`\n"
                    f"  比率：`{ratio_bar}` {ratio:.1f}x"
                )
            else:
                alerts.append(
                    f"{direction} {abs(pct):.0f}%\n"
                    f"  商品：{a['name'][:20]}\n"
                    f"  🆔 `{sid}`\n"
                    f"  今日：`NT$ {today_rev:,.0f}`  7日均：`NT$ {avg7_rev:,.0f}`"
                )

        if alerts:
            header = (
                f"🚨 *業績異常偵測  {td} {ts}*\n"
                f"{'─' * 28}\n"
                f"共發現 {len(alerts)} 件異常\n\n"
            )
            msg = header + "\n\n".join(alerts)
            ack_kb = [
                [{'text': '✅ 已知悉', 'callback_data': 'cmd:ack:anomaly'},
                 {'text': '🔄 追蹤中', 'callback_data': 'cmd:ack:tracking'}],
                [{'text': '📊 查看今日業績', 'callback_data': f'cmd:sales:{td}'},
                 {'text': '🏆 熱銷商品',   'callback_data': f'cmd:top:{td}'}],
            ]
            send_message(ALLOWED_GROUP, msg, keyboard=ack_kb, parse_mode='Markdown')
            sys_log.info(f"[OpenClawBot] 異常告警 {len(alerts)} 筆")
        else:
            sys_log.info(f"[OpenClawBot] 異常偵測完成，無告警 ({ts})")
    except Exception as e:
        sys_log.error(f"[OpenClawBot] check_anomalies: {e}")


def send_competitor_report():
    """每日 08:00 推播競品比價日報 + 降價警報"""
    if not _PCHOME_AVAILABLE:
        return
    try:
        yesterday = (datetime.now(TAIPEI_TZ).date() - timedelta(days=1)).strftime('%Y/%m/%d')
        sys_log.info(f'[PChome] 每日競品日報開始 {yesterday}')
        results = pchome_batch(_db(), top_n=30, date_str=yesterday)
        pchome_save(_db(), results)
        msg = pchome_fmt_report(results, yesterday)
        kb  = [[{'text': '🔍 搜尋比價', 'callback_data': 'await:search_compare'},
                {'text': '📄 比價簡報', 'callback_data': 'menu:competitor_ppt'}]]
        send_message(ALLOWED_GROUP, msg, None, kb)
        sys_log.info(f'[PChome] 競品日報已推播 {len(results)} 件商品')

        # ── 追蹤價格變動，有降價則額外發送警報 ──────────────
        try:
            changes = track_competitor_price_changes(results)
            if changes:
                alert_lines = [
                    f"⚡ *momo 降價警報！*  共 {len(changes)} 件",
                    f"{'─' * 26}",
                    f"_momo 降價 ≥5% → PChome 需評估因應策略_",
                    "",
                ]
                for c in changes[:8]:
                    alert_lines.append(
                        f"📉 *{c['name']}*\n"
                        f"   `NT$ {c['prev_price']:,.0f}` → `NT$ {c['curr_price']:,.0f}`  "
                        f"*{c['pct']:+.1f}%*"
                    )
                send_message(ALLOWED_GROUP, "\n".join(alert_lines), parse_mode='Markdown')
                sys_log.info(f"[price_track] 降價警報 {len(changes)} 件")
        except Exception as _pe:
            sys_log.warning(f"[price_track] {_pe}")
    except Exception as e:
        sys_log.error(f'[PChome] send_competitor_report: {e}', exc_info=True)


def send_daily_excel():
    """每日 08:45 自動發送昨日 Excel 業績報表"""
    try:
        yesterday = (datetime.now(TAIPEI_TZ).date() - timedelta(days=1)).strftime('%Y/%m/%d')
        sys_log.info(f"[AutoExcel] 開始產生 {yesterday} Excel 報表")
        path = generate_daily_pdf(yesterday)
        if not path:
            sys_log.warning("[AutoExcel] 報表產生失敗")
            return
        ext = 'Excel' if path.endswith('.xlsx') else 'CSV'
        caption = (
            f"📊 *{yesterday} 完整業績報表（{ext}）*\n"
            f"_自動定時發送 — 每日 08:45_"
        )
        send_document(ALLOWED_GROUP, path, caption=caption)
        try:
            import os as _os; _os.unlink(path)
        except Exception:
            pass
        sys_log.info(f"[AutoExcel] {yesterday} 報表已發送")
    except Exception as e:
        sys_log.error(f"[AutoExcel] {e}")


def start_scheduler():
    """啟動排程（Flask app 啟動後呼叫）"""
    global _scheduler
    try:
        from apscheduler.schedulers.background import BackgroundScheduler
        from apscheduler.triggers.cron import CronTrigger

        _scheduler = BackgroundScheduler(timezone='Asia/Taipei')
        _scheduler.add_job(send_morning_report,    CronTrigger(hour=8,  minute=30))
        _scheduler.add_job(send_competitor_report, CronTrigger(hour=8,  minute=0))
        _scheduler.add_job(send_daily_excel,       CronTrigger(hour=8,  minute=45))
        _scheduler.add_job(send_evening_report,    CronTrigger(hour=21, minute=0))
        _scheduler.add_job(send_weekly_report,     CronTrigger(day_of_week='mon', hour=9, minute=0))
        _scheduler.add_job(check_anomalies,        CronTrigger(hour='9,12,15,18', minute=0))
        _scheduler.start()
        sys_log.info("[OpenClawBot] Scheduler started ✓ (competitor/morning/excel/evening/weekly/anomaly)")
    except ImportError:
        sys_log.warning("[OpenClawBot] APScheduler 未安裝 — 排程功能停用")
    except Exception as e:
        sys_log.error(f"[OpenClawBot] start_scheduler: {e}")


def register_commands():
    """Telegram 只支援英文小寫指令"""
    cmds = [
        {'command': 'sales',    'description': '今日業績總覽'},
        {'command': 'top',      'description': '熱銷商品 TOP10（含商品ID）'},
        {'command': 'vendor',   'description': '熱銷廠商排行'},
        {'command': 'trend',    'description': '近7日業績趨勢'},
        {'command': 'compare',  'description': '同期比較（vs上週/上月）'},
        {'command': 'category', 'description': '分類業績鑽取'},
        {'command': 'restock',  'description': '📦 補貨預測（銷售速度分析）'},
        {'command': 'promo',    'description': '🎉 促銷效益追蹤 /promo 2026/04/01-2026/04/07'},
        {'command': 'goal',     'description': '目標達成率（/goal 200000 設定）'},
        {'command': 'chart',    'description': '業績趨勢圖'},
        {'command': 'health',   'description': '商品健康分析'},
        {'command': 'strategy', 'description': '商品策略矩陣'},
        {'command': 'ppt',      'description': '生成業績簡報 /ppt daily|weekly|monthly|strategy'},
        {'command': 'report',   'description': '下載完整業績報表 (Excel)'},
        {'command': 'history',  'description': '月份業績總覽（/history 2026/03）'},
        {'command': 'news',     'description': '即時電商新聞'},
        {'command': 'weather',  'description': '今日天氣（行銷參考）'},
        {'command': 'menu',     'description': '顯示主選單'},
        {'command': 'help',     'description': '使用說明'},
    ]
    return _tg('setMyCommands', {'commands': cmds})


# ── Inline Keyboard ───────────────────────────────────────────
_BACK = [{'text': '← 返回主選單', 'callback_data': 'menu:main'}]

def main_menu_keyboard():
    """第一層主選單 — 7大功能類別"""
    return [
        [{'text': '📊 業績查詢',  'callback_data': 'menu:sales'},
         {'text': '🏆 商品廠商',  'callback_data': 'menu:products'}],
        [{'text': '🎯 目標管理',  'callback_data': 'menu:goals'},
         {'text': '📈 智能分析',  'callback_data': 'menu:analysis'}],
        [{'text': '📄 簡報報表',  'callback_data': 'menu:reports'},
         {'text': '🌐 市場情報',  'callback_data': 'menu:market'}],
        [{'text': '🔍 競品日報',  'callback_data': 'menu:competitor'}],
        [{'text': '❓ 使用說明',  'callback_data': 'cmd:help'}],
    ]

def _submenu_sales():
    ld = latest_date() or ''
    yesterday = ''
    current_month = datetime.now(TAIPEI_TZ).strftime('%Y/%m')
    if ld:
        try:
            from datetime import datetime as _dt
            yesterday = (_dt.strptime(ld.replace('/', '-'), '%Y-%m-%d') - timedelta(days=1)).strftime('%Y/%m/%d')
        except Exception:
            pass
    d_label  = ld[-5:]  if ld        else '-'
    y_label  = yesterday[-5:] if yesterday else '-'
    return [
        [{'text': f'📊 今日 ({d_label})',  'callback_data': f'cmd:sales:{ld}'},
         {'text': f'⬅ 昨日 ({y_label})',  'callback_data': f'cmd:sales:{yesterday}'}],
        [{'text': '📅 每週業績',  'callback_data': 'cmd:trend:week'},
         {'text': '📅 每月業績',  'callback_data': f'cmd:history:{current_month}'}],
        [{'text': '📅 每季業績',  'callback_data': 'cmd:trend:quarter'},
         {'text': '📅 近半年',    'callback_data': 'cmd:trend:half'}],
        [{'text': '📈 趨勢分析', 'callback_data': 'menu:trend'},
         {'text': '🔄 同期比較', 'callback_data': f'cmd:compare:{ld}'}],
        [{'text': '🗂 分類業績', 'callback_data': f'cmd:category:{ld}'},
         {'text': '📅 日期/區間', 'callback_data': 'await:date_range_sales'}],
        [{'text': '🗃 月份覽', 'callback_data': 'cmd:history'}],
        _BACK,
    ]

def _submenu_products():
    ld = latest_date() or ''
    yesterday = ''
    if ld:
        try:
            from datetime import datetime as _dt
            yesterday = (_dt.strptime(ld.replace('/', '-'), '%Y-%m-%d') - timedelta(days=1)).strftime('%Y/%m/%d')
        except Exception:
            pass
    d_label = ld[-5:]  if ld        else '-'
    y_label = yesterday[-5:] if yesterday else '-'
    return [
        [{'text': f'🏆 熱銷商品 ({d_label})', 'callback_data': f'cmd:top:{ld}'},
         {'text': f'🏭 熱銷廠商 ({d_label})', 'callback_data': f'cmd:vendor:{ld}'}],
        [{'text': f'⬅ 昨日商品 ({y_label})',  'callback_data': f'cmd:top:{yesterday}'},
         {'text': '🧬 商品健康',              'callback_data': f'cmd:health:{ld}'}],
        [{'text': '📦 補貨預測',              'callback_data': 'cmd:restock'},
         {'text': '🗂 分類鑽取',              'callback_data': 'menu:category'}],
        [{'text': '📅 指定日期', 'callback_data': 'await:date_top'}],
        _BACK,
    ]

def _submenu_goals():
    dg  = _GOALS.get('daily', 0)
    mg  = _GOALS.get('monthly', 0)
    qg  = _GOALS.get('quarterly', 0)
    hg  = _GOALS.get('half', 0)
    yg  = _GOALS.get('yearly', 0)
    def _fmt(v): return f'{v/10000:.0f}萬' if v else '未設'
    return [
        [{'text': '📋 查看達成率', 'callback_data': 'cmd:goal'}],
        [{'text': f'日目標 ({_fmt(dg)})',   'callback_data': 'await:goal_daily'},
         {'text': f'月目標 ({_fmt(mg)})',   'callback_data': 'await:goal_monthly'}],
        [{'text': f'季目標 ({_fmt(qg)})',   'callback_data': 'await:goal_quarterly'},
         {'text': f'半年目標 ({_fmt(hg)})', 'callback_data': 'await:goal_half'}],
        [{'text': f'年目標 ({_fmt(yg)})',   'callback_data': 'await:goal_yearly'}],
        _BACK,
    ]

def _submenu_analysis():
    ld = latest_date() or ''
    return [
        [{'text': '🎲 策略矩陣', 'callback_data': f'cmd:strategy:{ld}'},
         {'text': '📈 業績趨勢', 'callback_data': 'menu:trend'}],
        [{'text': '🧬 商品健康', 'callback_data': f'cmd:health:{ld}'},
         {'text': '🗂 分類業績', 'callback_data': f'cmd:category:{ld}'}],
        [{'text': '🎉 促銷追蹤', 'callback_data': 'await:promo_range'},
         {'text': '📦 補貨預測', 'callback_data': 'cmd:restock'}],
        [{'text': '📊 趨勢圖表', 'callback_data': 'cmd:chart'},          # P8 exposed
         {'text': '🔄 同期比較', 'callback_data': f'cmd:compare:{ld}'}],
        [{'text': '📅 指定日期', 'callback_data': 'await:date_analysis'}],
        _BACK,
    ]


def _submenu_category():
    """分類業績鑽取 — 顯示 L1 固定分類按鈕"""
    ld = latest_date() or ''
    CATS = [
        ('美妝保養', '💄'), ('保健食品/用品', '💊'), ('母嬰', '👶'),
        ('食品飲料', '🍱'), ('家電', '🏠'), ('服裝內著', '👕'),
        ('個人清潔', '🧴'), ('運動用品/器材', '🏃'), ('寵物', '🐾'), ('其他', '📦'),
    ]
    rows = []
    for i in range(0, len(CATS), 2):
        pair = []
        for cat, icon in CATS[i:i+2]:
            pair.append({'text': f'{icon} {cat}', 'callback_data': f'cmd:catdetail:{cat}:{ld}'})
        rows.append(pair)
    rows.append([{'text': '🗂 全分類清單', 'callback_data': f'cmd:category:{ld}'}])
    rows.append(_BACK)
    return rows


def _submenu_trend():
    return [
        [{'text': '📅 近7日',    'callback_data': 'cmd:trend:7'},
         {'text': '📅 近1個月',  'callback_data': 'cmd:trend:month'}],
        [{'text': '📅 近3個月',  'callback_data': 'cmd:trend:quarter'},
         {'text': '📅 近半年',   'callback_data': 'cmd:trend:half'}],
        [{'text': '📅 本年度',   'callback_data': 'cmd:trend:year'},
         {'text': '📅 指定月份', 'callback_data': 'await:date_trend_month'}],
        [{'text': '📅 指定年份', 'callback_data': 'await:date_trend_year'},
         {'text': '📅 指定季度', 'callback_data': 'await:date_trend_quarter'}],
        [{'text': '← 返回業績查詢', 'callback_data': 'menu:sales'}],
    ]

def _submenu_reports():
    return [
        # ── 定期報告
        [{'text': '📄 日報',    'callback_data': 'cmd:ppt:daily'},
         {'text': '📈 週報',    'callback_data': 'cmd:ppt:weekly'}],
        [{'text': '📅 月報',    'callback_data': 'cmd:ppt:monthly'},
         {'text': '📋 下載報表','callback_data': 'cmd:report'}],
        # ── 策略簡報
        [{'text': '🧩 策略(日)', 'callback_data': 'cmd:ppt:strategy'},
         {'text': '🧩 策略(週)', 'callback_data': 'cmd:ppt:strategy weekly'}],
        [{'text': '🧩 策略(月)', 'callback_data': 'cmd:ppt:strategy monthly'},
         {'text': '🧩 策略(季)', 'callback_data': 'cmd:ppt:strategy quarterly'}],
        [{'text': '🧩 策略(半年)','callback_data': 'cmd:ppt:strategy half'},
         {'text': '🧩 策略(年)', 'callback_data': 'cmd:ppt:strategy yearly'}],
        # ── 促銷 & 競品
        [{'text': '🎉 促銷效益簡報', 'callback_data': 'await:promo_range'},
         {'text': '🔍 競品比較',     'callback_data': 'menu:competitor'}],
        # ── 自訂
        [{'text': '📅 指定日期日報', 'callback_data': 'await:date_ppt_daily'},
         {'text': '📅 指定月份月報', 'callback_data': 'await:date_ppt_monthly'}],
        _BACK,
    ]

def _submenu_market():
    return [
        [{'text': '📰 電商新聞',        'callback_data': 'cmd:news'},
         {'text': '🌤 台北天氣',        'callback_data': 'cmd:weather'}],
        [{'text': '🔥 Google熱搜',      'callback_data': 'cmd:trends'},
         {'text': '💬 Dcard口碑',       'callback_data': 'cmd:dcard'}],
        [{'text': '💱 台銀匯率',        'callback_data': 'cmd:exchange'},
         {'text': '📅 電商節慶',        'callback_data': 'cmd:calendar'}],
        [{'text': '▶️ YouTube爆紅商品', 'callback_data': 'cmd:youtube'},
         {'text': '🧠 AI學習狀態',      'callback_data': 'cmd:learn'}],
        [{'text': '🔍 關鍵字比價',      'callback_data': 'await:search_compare'},
         {'text': '📷 圖片比價說明',    'callback_data': 'cmd:photo_search_help'}],
        _BACK,
    ]


def _submenu_competitor():
    """競品日報第二層：所有選項直接產 PPT"""
    today     = datetime.now(TAIPEI_TZ).date()
    yesterday = today - timedelta(days=1)
    td_str    = today.strftime('%Y/%m/%d')
    yd_str    = yesterday.strftime('%Y/%m/%d')
    td_label  = today.strftime('%m/%d')
    yd_label  = yesterday.strftime('%m/%d')
    return [
        [{'text': f'📊 今日簡報 ({td_label})',  'callback_data': f'cmd:ppt:competitor {td_str}'},
         {'text': f'📊 昨日簡報 ({yd_label})',  'callback_data': f'cmd:ppt:competitor {yd_str}'}],
        [{'text': '📈 本週比較',                'callback_data': 'cmd:ppt:competitor weekly'},
         {'text': '📆 本月比較',                'callback_data': 'cmd:ppt:competitor monthly'}],
        [{'text': '🗃 本季比較',                'callback_data': 'cmd:ppt:competitor quarterly'},
         {'text': '📅 指定日期',                'callback_data': 'await:date_competitor'}],
        [{'text': '📄 更多週期 →',              'callback_data': 'menu:competitor_ppt'}],
        _BACK,
    ]


def _submenu_competitor_ppt():
    """競品 PPT 長週期選單（第三層）— 半年/年；日/週/月/季已在第二層"""
    return [
        [{'text': '📆 半年比較', 'callback_data': 'cmd:ppt:competitor half'},
         {'text': '🗓 年比較',   'callback_data': 'cmd:ppt:competitor yearly'}],
        [{'text': '← 返回競品日報', 'callback_data': 'menu:competitor'}],
    ]

_SUBMENUS = {
    'main':             main_menu_keyboard,
    'sales':            _submenu_sales,
    'products':         _submenu_products,
    'goals':            _submenu_goals,
    'analysis':         _submenu_analysis,
    'trend':            _submenu_trend,
    'reports':          _submenu_reports,
    'market':           _submenu_market,
    'competitor':       _submenu_competitor,
    'competitor_ppt':   _submenu_competitor_ppt,
    'category':         _submenu_category,
}

_AWAIT_PROMPTS = {
    'date_sales':        ('📅 請輸入查詢日期\n格式：`2026/04/15`', '業績日期'),
    'date_range_sales':  (
        '📅 請輸入日期或日期區間\n\n'
        '📌 單日：`2026/04/15`\n'
        '📌 區間：`2026/04/01-2026/04/15`\n'
        '📌 月份：`2026/04`',
        '日期/區間',
    ),
    'date_top':       ('📅 請輸入查詢日期\n格式：`2026/04/15`', '商品日期'),
    'date_analysis':  ('📅 請輸入分析日期\n格式：`2026/04/15`', '分析日期'),
    'date_ppt_daily': ('📅 請輸入日報日期\n格式：`2026/04/15`', 'PPT日期'),
    'date_ppt_monthly':('📅 請輸入月報月份\n格式：`2026/03`', 'PPT月份'),
    'goal_daily':        ('🎯 請輸入每日業績目標金額（NT$）\n例如：`150000`', '日目標'),
    'goal_monthly':      ('🎯 請輸入每月業績目標金額（NT$）\n例如：`3000000`', '月目標'),
    'goal_quarterly':    ('🎯 請輸入每季業績目標金額（NT$）\n例如：`9000000`', '季目標'),
    'goal_half':         ('🎯 請輸入半年業績目標金額（NT$）\n例如：`18000000`', '半年目標'),
    'goal_yearly':       ('🎯 請輸入全年業績目標金額（NT$）\n例如：`36000000`', '年目標'),
    'search_compare':    ('🔍 請輸入商品關鍵字\n例如：`休足時間貼片`', '比價關鍵字'),
    'date_trend_month':  ('📅 請輸入查詢月份\n格式：`2026/03`', '趨勢月份'),
    'date_trend_year':   ('📅 請輸入查詢年份\n格式：`2026`', '趨勢年份'),
    'date_trend_quarter':('📅 請輸入查詢季度\n格式：`2026/Q1`（Q1~Q4）', '趨勢季度'),
    'date_competitor':   ('📅 請輸入競品分析日期\n格式：`2026/04/15`', '競品日期'),
    'promo_range':       ('🎉 請輸入促銷日期範圍\n格式：`2026/04/01-2026/04/07`\n（活動開始日-活動結束日）', '促銷範圍'),
}


def sales_quick_kb(date_str):
    try:
        from datetime import datetime as dt
        d = dt.strptime(date_str.replace('/', '-'), '%Y-%m-%d').date()
        yesterday = (d - timedelta(days=1)).strftime('%Y/%m/%d')
        return [
            [{'text': '⬅️ 昨日業績', 'callback_data': f'cmd:sales:{yesterday}'},
             {'text': '🏆 熱銷商品',  'callback_data': f'cmd:top:{date_str}'}],
            [{'text': '📋 完整報表',  'callback_data': f'cmd:report:{date_str}'}],
        ]
    except Exception:
        return None


# ── 資料查詢 ──────────────────────────────────────────────────
def _db():
    return DatabaseManager().engine


def normalize_date(s: str) -> str:
    return s.replace('-', '/') if s else s


def latest_date() -> str:
    try:
        with _db().connect() as c:
            row = c.execute(
                text('SELECT MAX("日期") FROM realtime_sales_monthly')
            ).fetchone()
        return str(row[0]) if row and row[0] else None
    except Exception:
        return None


def query_sales(d: str) -> dict:
    try:
        with _db().connect() as c:
            row = c.execute(text("""
                SELECT COUNT(DISTINCT "訂單編號"),
                       COALESCE(SUM(CAST("總業績" AS FLOAT)),0),
                       COALESCE(SUM(CAST("總成本" AS FLOAT)),0),
                       COUNT(DISTINCT "商品ID")
                FROM realtime_sales_monthly WHERE "日期"=:d
            """), {'d': d}).fetchone()
        if row and row[0] > 0:
            rev, cost, orders = row[1], row[2], row[0]
            return {'found': True, 'date': d, 'orders': orders, 'revenue': rev,
                    'avg_order': rev/orders if orders else 0,
                    'gross_margin': (rev-cost)/rev*100 if rev>0 else 0,
                    'products': row[3]}
        return {'found': False, 'date': d}
    except Exception as e:
        sys_log.error(f"[OpenClawBot] query_sales: {e}")
        return {'found': False, 'date': d}


def query_top_products(d, lim=10):
    try:
        with _db().connect() as c:
            rows = c.execute(text("""
                SELECT "商品ID", "商品名稱", SUM(CAST("總業績" AS FLOAT)),
                       SUM(CAST("數量" AS INTEGER))
                FROM realtime_sales_monthly WHERE "日期"=:d
                GROUP BY "商品ID", "商品名稱" ORDER BY 3 DESC LIMIT :lim
            """), {'d': d, 'lim': lim}).fetchall()
        return [{'id': r[0], 'name': r[1], 'revenue': r[2], 'qty': r[3]} for r in rows]
    except Exception:
        return []


def query_top_vendors(d, lim=10):
    try:
        with _db().connect() as c:
            rows = c.execute(text("""
                SELECT "廠商名稱", SUM(CAST("總業績" AS FLOAT))
                FROM realtime_sales_monthly
                WHERE "日期"=:d AND "廠商名稱" IS NOT NULL AND "廠商名稱"!=''
                GROUP BY "廠商名稱" ORDER BY 2 DESC LIMIT :lim
            """), {'d': d, 'lim': lim}).fetchall()
        return [{'name': r[0], 'revenue': r[1]} for r in rows]
    except Exception:
        return []


def query_weekly_trend():
    try:
        with _db().connect() as c:
            rows = c.execute(text("""
                SELECT "日期", COALESCE(SUM(CAST("總業績" AS FLOAT)),0)
                FROM realtime_sales_monthly
                WHERE CAST("日期" AS DATE) >= CURRENT_DATE - INTERVAL '7 days'
                GROUP BY "日期" ORDER BY "日期" DESC LIMIT 7
            """)).fetchall()
        return [{'date': str(r[0]), 'revenue': r[1]} for r in rows]
    except Exception as e:
        sys_log.error(f"[OpenClawBot] weekly_trend: {e}")
        return []


def query_trend_range(start_str: str, end_str: str) -> list:
    """指定區間每日業績（用於趨勢圖，回傳 [{'date', 'revenue'}] 按日期升序）"""
    try:
        with _db().connect() as c:
            rows = c.execute(text("""
                SELECT "日期", COALESCE(SUM(CAST("總業績" AS FLOAT)), 0)
                FROM realtime_sales_monthly
                WHERE CAST("日期" AS DATE) BETWEEN CAST(:s AS DATE) AND CAST(:e AS DATE)
                GROUP BY "日期" ORDER BY "日期" ASC
            """), {'s': start_str.replace('/', '-'), 'e': end_str.replace('/', '-')}).fetchall()
        return [{'date': str(r[0]), 'revenue': float(r[1])} for r in rows]
    except Exception as e:
        sys_log.error(f"[OpenClawBot] query_trend_range: {e}")
        return []


def query_monthly_summary(year: int, month: int) -> dict:
    """取得指定年月業績摘要（逐日 + 合計 + TOP10商品/廠商）
    支援 YYYY/MM/DD 與 YYYY-MM-DD 兩種日期格式
    """
    month_str  = f"{year}/{month:02d}"
    # BETWEEN 比 LIKE 更可靠，且同時支援 DATE 與 TEXT 格式
    import calendar as _cal2
    last_day   = _cal2.monthrange(year, month)[1]
    start_date = f"{year}-{month:02d}-01"
    end_date   = f"{year}-{month:02d}-{last_day:02d}"
    try:
        with _db().connect() as c:
            # 月合計 — 用 BETWEEN 避免 LIKE 格式相依
            row = c.execute(text("""
                SELECT COUNT(DISTINCT "訂單編號"),
                       COALESCE(SUM(CAST("總業績" AS FLOAT)),0),
                       COALESCE(SUM(CAST("總成本" AS FLOAT)),0),
                       COUNT(DISTINCT "商品ID"),
                       COUNT(DISTINCT "日期")
                FROM realtime_sales_monthly
                WHERE CAST("日期" AS DATE)
                      BETWEEN CAST(:s AS DATE) AND CAST(:e AS DATE)
            """), {'s': start_date, 'e': end_date}).fetchone()

            daily_rows = c.execute(text("""
                SELECT "日期", SUM(CAST("總業績" AS FLOAT)) as rev,
                       COUNT(DISTINCT "訂單編號") as orders
                FROM realtime_sales_monthly
                WHERE CAST("日期" AS DATE)
                      BETWEEN CAST(:s AS DATE) AND CAST(:e AS DATE)
                GROUP BY "日期" ORDER BY "日期" ASC
            """), {'s': start_date, 'e': end_date}).fetchall()

            prod_rows = c.execute(text("""
                SELECT "商品ID", "商品名稱",
                       SUM(CAST("總業績" AS FLOAT)) as rev,
                       SUM(CAST("數量" AS INTEGER)) as qty
                FROM realtime_sales_monthly
                WHERE CAST("日期" AS DATE)
                      BETWEEN CAST(:s AS DATE) AND CAST(:e AS DATE)
                GROUP BY "商品ID", "商品名稱" ORDER BY 3 DESC LIMIT 10
            """), {'s': start_date, 'e': end_date}).fetchall()

            vendor_rows = c.execute(text("""
                SELECT "廠商名稱", SUM(CAST("總業績" AS FLOAT)) as rev
                FROM realtime_sales_monthly
                WHERE CAST("日期" AS DATE)
                      BETWEEN CAST(:s AS DATE) AND CAST(:e AS DATE)
                  AND "廠商名稱" IS NOT NULL AND "廠商名稱" != ''
                GROUP BY "廠商名稱" ORDER BY 2 DESC LIMIT 10
            """), {'s': start_date, 'e': end_date}).fetchall()

        if not row or row[0] == 0:
            return {'found': False, 'month': month_str}

        orders, revenue, cost = row[0], row[1], row[2]
        return {
            'found': True, 'month': month_str,
            'orders': orders, 'revenue': revenue,
            'cost': cost,
            'gross_margin': (revenue - cost) / revenue * 100 if revenue > 0 else 0,
            'avg_order': revenue / orders if orders else 0,
            'products': row[3], 'days_with_data': row[4],
            'daily': [{'date': str(r[0]), 'revenue': float(r[1]), 'orders': int(r[2])}
                      for r in daily_rows],
            'top_products': [{'id': r[0], 'name': r[1], 'revenue': float(r[2]), 'qty': int(r[3])}
                             for r in prod_rows],
            'top_vendors':  [{'name': r[0], 'revenue': float(r[1])} for r in vendor_rows],
        }
    except Exception as e:
        sys_log.error(f"[OpenClawBot] query_monthly_summary {month_str}: {e}")
        return {'found': False, 'month': month_str}


def query_date_range(start_str: str, end_str: str) -> dict:
    """查詢指定日期區間業績（start/end 格式 YYYY/MM/DD）"""
    try:
        with _db().connect() as c:
            row = c.execute(text("""
                SELECT COUNT(DISTINCT "訂單編號"),
                       COALESCE(SUM(CAST("總業績" AS FLOAT)),0),
                       COALESCE(SUM(CAST("總成本" AS FLOAT)),0),
                       COUNT(DISTINCT "商品ID"),
                       COUNT(DISTINCT "日期")
                FROM realtime_sales_monthly
                WHERE CAST("日期" AS DATE) BETWEEN CAST(:s AS DATE) AND CAST(:e AS DATE)
            """), {'s': start_str.replace('/', '-'), 'e': end_str.replace('/', '-')}).fetchone()

            daily = c.execute(text("""
                SELECT "日期", SUM(CAST("總業績" AS FLOAT))
                FROM realtime_sales_monthly
                WHERE CAST("日期" AS DATE) BETWEEN CAST(:s AS DATE) AND CAST(:e AS DATE)
                GROUP BY "日期" ORDER BY "日期" ASC
            """), {'s': start_str.replace('/', '-'), 'e': end_str.replace('/', '-')}).fetchall()

        if not row or row[0] == 0:
            return {'found': False, 'range': f'{start_str}~{end_str}'}

        orders, revenue, cost = row[0], row[1], row[2]
        return {
            'found': True, 'range': f'{start_str}~{end_str}',
            'orders': orders, 'revenue': revenue,
            'gross_margin': (revenue - cost) / revenue * 100 if revenue > 0 else 0,
            'avg_order': revenue / orders if orders else 0,
            'products': row[3], 'days_with_data': row[4],
            'daily': [{'date': str(r[0]), 'revenue': float(r[1])} for r in daily],
        }
    except Exception as e:
        sys_log.error(f"[OpenClawBot] query_date_range: {e}")
        return {'found': False, 'range': f'{start_str}~{end_str}'}


def query_available_months() -> list:
    """取得 DB 中有資料的月份清單（支援 YYYY/MM/DD 和 YYYY-MM-DD 兩種日期格式）"""
    try:
        with _db().connect() as c:
            rows = c.execute(text("""
                SELECT REPLACE(SUBSTRING("日期"::TEXT, 1, 7), '-', '/') AS ym,
                       COUNT(DISTINCT "日期") AS days
                FROM realtime_sales_monthly
                GROUP BY ym ORDER BY ym DESC LIMIT 24
            """)).fetchall()
        return [{'month': r[0], 'days': r[1]} for r in rows]
    except Exception:
        return []


def query_top_products_range(start_str: str, end_str: str, lim: int = 10) -> list:
    """指定區間熱銷商品"""
    try:
        with _db().connect() as c:
            rows = c.execute(text("""
                SELECT "商品ID", "商品名稱",
                       SUM(CAST("總業績" AS FLOAT)) as rev,
                       SUM(CAST("數量" AS INTEGER)) as qty
                FROM realtime_sales_monthly
                WHERE CAST("日期" AS DATE) BETWEEN CAST(:s AS DATE) AND CAST(:e AS DATE)
                GROUP BY "商品ID", "商品名稱" ORDER BY 3 DESC LIMIT :lim
            """), {'s': start_str.replace('/', '-'),
                   'e': end_str.replace('/', '-'), 'lim': lim}).fetchall()
        return [{'id': r[0], 'name': r[1], 'revenue': float(r[2]), 'qty': int(r[3])}
                for r in rows]
    except Exception:
        return []


def resolve_date(q: str) -> str:
    """從問題文字解析目標日期，回傳 YYYY/MM/DD"""
    import re
    today = datetime.now(TAIPEI_TZ).date()

    if '昨天' in q or '昨日' in q:
        return (today - timedelta(days=1)).strftime('%Y/%m/%d')
    if '前天' in q:
        return (today - timedelta(days=2)).strftime('%Y/%m/%d')
    if '大前天' in q:
        return (today - timedelta(days=3)).strftime('%Y/%m/%d')

    # 明確日期格式：2026/04/15 or 2026-04-15 or 04/15
    m = re.search(r'(\d{4})[/-](\d{1,2})[/-](\d{1,2})', q)
    if m:
        return f"{m.group(1)}/{int(m.group(2)):02d}/{int(m.group(3)):02d}"
    m = re.search(r'(\d{1,2})[/-](\d{1,2})(?![/-]\d)', q)
    if m:
        return f"{today.year}/{int(m.group(1)):02d}/{int(m.group(2)):02d}"

    return latest_date() or today.strftime('%Y/%m/%d')


def resolve_query_intent(q: str) -> dict:
    """
    NIM fallback 用：解析問題中的時間參數
    types: 'day' / 'month' / 'range' / 'all'
    （Function Calling 架構不需要此函數，僅 NIM 備援使用）
    """
    import re
    today = datetime.now(TAIPEI_TZ).date()

    # ── 月份查詢 ─────────────────────────────────────────────
    # 「上個月」「上月」
    if any(kw in q for kw in ['上個月', '上月', '上月份']):
        first = today.replace(day=1)
        last_m = (first - timedelta(days=1))
        return {'type': 'month', 'year': last_m.year, 'month': last_m.month}

    # 「這個月」「本月」
    if any(kw in q for kw in ['這個月', '本月', '這月', '當月']):
        return {'type': 'month', 'year': today.year, 'month': today.month}

    # 「N月」or「N月份」
    m = re.search(r'(\d{1,2})月(?:份)?', q)
    if m:
        month_num = int(m.group(1))
        if 1 <= month_num <= 12:
            year = today.year if month_num <= today.month else today.year - 1
            # 也可能帶年份
            ym = re.search(r'(\d{4})年.*?(\d{1,2})月', q)
            if ym:
                year = int(ym.group(1))
                month_num = int(ym.group(2))
            return {'type': 'month', 'year': year, 'month': month_num}

    # ── 週查詢 ───────────────────────────────────────────────
    if any(kw in q for kw in ['上週', '上个星期', '上星期', '上週']):
        mon = today - timedelta(days=today.weekday() + 7)
        sun = mon + timedelta(days=6)
        return {'type': 'range', 'start': mon.strftime('%Y/%m/%d'),
                'end': sun.strftime('%Y/%m/%d'), 'label': '上週'}
    if any(kw in q for kw in ['這週', '本週', '這周', '本周']):
        mon = today - timedelta(days=today.weekday())
        return {'type': 'range', 'start': mon.strftime('%Y/%m/%d'),
                'end': today.strftime('%Y/%m/%d'), 'label': '本週'}

    # ── 近N天 ─────────────────────────────────────────────────
    m = re.search(r'近(\d+)[天日]', q)
    if m:
        n = int(m.group(1))
        start = (today - timedelta(days=n - 1)).strftime('%Y/%m/%d')
        return {'type': 'range', 'start': start,
                'end': today.strftime('%Y/%m/%d'), 'label': f'近{n}天'}

    # ── 多月查詢（全部資料/歷史）──────────────────────────────
    if any(kw in q for kw in ['全部', '所有', '歷史', '整年', '今年', '全年', '各月']):
        return {'type': 'all'}

    # ── 預設：單日 ────────────────────────────────────────────
    return {'type': 'day', 'date': resolve_date(q)}


# ── 格式化 ────────────────────────────────────────────────────
MEDALS = ['🥇','🥈','🥉','4️⃣','5️⃣','6️⃣','7️⃣','8️⃣','9️⃣','🔟']


def fmt_sales(sales, top, report_url):
    if not sales.get('found'):
        return (f"⚠️ *查無資料*\n\n"
                f"`{sales.get('date','?')}` 尚無業績資料\n"
                f"請確認當日資料是否已匯入系統。")
    rev  = float(sales.get('revenue', 0))
    orders = sales.get('orders', 0) or 0
    avg_o  = float(sales.get('avg_order', 0))
    margin = float(sales.get('gross_margin', 0))
    prods  = sales.get('products', 0) or 0

    rev_wan  = rev / 10000

    lines = [
        f"📊 *{sales['date']} 業績總覽*",
        f"{'─' * 26}",
        f"",
        f"💰 總業績　　`NT$ {rev:,.0f}`",
        f"📦 訂單數量　`{orders:,}` 筆",
        f"🛒 平均客單　`NT$ {avg_o:,.0f}`",
        f"📈 毛利率　　`{margin:.1f}%`",
        f"🛍 商品件數　`{prods:,}` 件",
    ]
    if top:
        lines.append(f"")
        lines.append(f"{'─' * 26}")
        lines.append(f"🏆 *今日熱銷 TOP3*")
        for i, p in enumerate(top[:3]):
            pid   = p.get('id', '') or ''
            sid   = _short_id(pid)
            link  = _pchome_link(pid, p['name'], 22)
            rev_p = p['revenue']
            qty   = p.get('qty', '-')
            lines.append(f"  {MEDALS[i]} {link}")
            lines.append(f"     🆔 `{sid}`  💰 `NT$ {rev_p:,.0f}`  📦 {qty}件")
    lines.append(f"")
    lines.append(f"📥 點選下方「完整報表」按鈕下載 Excel")
    return "\n".join(lines)


def _esc(s: str) -> str:
    """Escape Telegram Markdown v1 special chars in plain text"""
    for ch in ('_', '*', '`', '['):
        s = s.replace(ch, f'\\{ch}')
    return s


PCHOME_URL = 'https://24h.pchome.com.tw/prod/'

def _pchome_link(pid: str, name: str, max_len: int = 22) -> str:
    """生成可點選的 PChome 商品頁 Markdown 連結（Telegram Markdown v1）"""
    safe = name[:max_len].replace('[', '').replace(']', '').replace('(', '').replace(')', '')
    if pid and pid.strip():
        return f"[{safe}]({PCHOME_URL}{pid.strip()})"
    return _esc(name[:max_len])


def fmt_products(products, date_str):
    if not products:
        return f"⚠️ *查無資料*\n\n`{date_str}` 尚無商品業績資料"
    # 計算合計
    total_rev = sum(p['revenue'] for p in products)
    total_qty = sum(p.get('qty', 0) or 0 for p in products)
    lines = [
        f"🏆 *{date_str} 熱銷商品排行*",
        f"共 {len(products)} 件商品 | 合計 `NT$ {total_rev:,.0f}` | {total_qty:,} 件",
        f"{'─' * 30}",
        "",
    ]
    for i, p in enumerate(products):
        pid   = p.get('id', '') or ''
        sid   = _short_id(pid)
        link  = _pchome_link(pid, p['name'], 22)
        rev   = p['revenue']
        qty   = p.get('qty', 0) or 0
        pct   = rev / total_rev * 100 if total_rev else 0
        medal = MEDALS[i] if i < len(MEDALS) else f"`{i+1}.`"
        bar_len = max(1, int(pct / 100 * 8))
        mini_bar = '▪' * bar_len + '·' * (8 - bar_len)
        lines.append(f"{medal} {link}")
        lines.append(f"    🆔 `{sid}`  💰 `NT$ {rev:,.0f}`  📦 {qty}件  `{mini_bar}` {pct:.1f}%")
        lines.append("")
    lines.append(f"_💡 點商品名稱可開啟 PChome 商品頁_")
    return "\n".join(lines)


def fmt_vendors(vendors, date_str):
    if not vendors:
        return f"⚠️ *查無資料*\n\n`{date_str}` 尚無廠商業績資料"
    total_rev = sum(v['revenue'] for v in vendors)
    lines = [
        f"🏭 *{date_str} 熱銷廠商排行*",
        f"共 {len(vendors)} 家廠商 | 合計 `NT$ {total_rev:,.0f}`",
        f"{'─' * 30}",
        "",
    ]
    for i, v in enumerate(vendors):
        medal = MEDALS[i] if i < len(MEDALS) else f"`{i+1}.`"
        name  = _esc(v['name'][:24])
        rev   = v['revenue']
        pct   = rev / total_rev * 100 if total_rev else 0
        bar_len = max(1, int(pct / 100 * 8))
        mini_bar = '▪' * bar_len + '·' * (8 - bar_len)
        lines.append(f"{medal} *{name}*")
        lines.append(f"    💰 `NT$ {rev:,.0f}`  `{mini_bar}` 佔比 {pct:.1f}%")
        lines.append("")
    return "\n".join(lines)


def fmt_trend(weekly, period_label: str = ''):
    """格式化趨勢資料 — 彩色進度條 + emoji + 統計摘要（週/月用）"""
    if not weekly:
        return "⚠️ *尚無趨勢資料*\n\n請確認資料是否已匯入。"

    # 按日期升序排列
    data = sorted(weekly, key=lambda x: x['date'])
    revs = [w['revenue'] for w in data]
    max_rev = max(revs) if revs else 1
    min_rev = min(revs) if revs else 0
    avg_rev = sum(revs) / len(revs) if revs else 0
    total   = sum(revs)
    BAR_LEN = 6
    WEEKDAYS_ZH = ['週一', '週二', '週三', '週四', '週五', '週六', '週日']

    label = period_label or f'近 {len(data)} 日'
    lines = [f"📈 *業績趨勢 — {label}*", ""]

    for i, w in enumerate(data):
        rev    = w['revenue']
        filled = max(0, min(BAR_LEN, int(rev / max_rev * BAR_LEN) if max_rev else 0))
        empty  = BAR_LEN - filled

        # 台灣慣例：上升=🟥紅, 下降=🟩綠, 初始=🟦藍
        if i == 0:
            bar_str  = '🟦' * filled + '▪️' * empty
            dir_icon = '🔵'
            pct_str  = ''
        else:
            diff    = rev - data[i - 1]['revenue']
            prev    = data[i - 1]['revenue'] or 1
            pct_chg = diff / prev * 100
            if diff > 0:
                bar_str  = '🟥' * filled + '▪️' * empty
                dir_icon = '📈'
                pct_str  = f"  ▲`{pct_chg:.1f}%`"
            elif diff < 0:
                bar_str  = '🟩' * filled + '▪️' * empty
                dir_icon = '📉'
                pct_str  = f"  ▼`{abs(pct_chg):.1f}%`"
            else:
                bar_str  = '🟦' * filled + '▪️' * empty
                dir_icon = '→'
                pct_str  = '  —'

        # 日期 + 星期
        try:
            from datetime import datetime as _dt2
            d_obj     = _dt2.strptime(w['date'].replace('/', '-'), '%Y-%m-%d')
            date_disp = d_obj.strftime('%m/%d')
            wday      = WEEKDAYS_ZH[d_obj.weekday()]
        except Exception:
            date_disp = w['date'][-5:]
            wday      = ''

        rev_wan = rev / 10000
        lines.append(f"{dir_icon} `{date_disp}` {wday}  {bar_str}{pct_str}")
        lines.append(f"　　　💰 `NT$ {rev:>10,.0f}` ({rev_wan:.1f}萬)")

    # 統計摘要
    lines.append("")
    lines.append("─────────────────────")
    lines.append("📊 *統計摘要*")
    lines.append(f"  📦 合計：`NT$ {total:,.0f}` ({total/10000:.1f}萬)")
    lines.append(f"  📐 日均：`NT$ {avg_rev:,.0f}` ({avg_rev/10000:.1f}萬)")
    if len(revs) >= 2:
        up_days   = sum(1 for j in range(1, len(revs)) if revs[j] > revs[j - 1])
        down_days = sum(1 for j in range(1, len(revs)) if revs[j] < revs[j - 1])
        flat_days = len(revs) - 1 - up_days - down_days
        lines.append(f"  📈 上升 {up_days}天  📉 下降 {down_days}天  → 持平 {flat_days}天")
        lines.append(f"  🏆 最高：`NT$ {max_rev:,.0f}` ({max_rev/10000:.1f}萬)")
        lines.append(f"  🔻 最低：`NT$ {min_rev:,.0f}` ({min_rev/10000:.1f}萬)")
        last_diff = revs[-1] - revs[-2]
        last_pct  = last_diff / (revs[-2] or 1) * 100
        icon      = '🔴' if last_diff >= 0 else '🟢'
        arrow     = '▲' if last_diff >= 0 else '▼'
        lines.append(f"  {icon} 最新日：{arrow}`{abs(last_pct):.1f}%`  (`NT$ {last_diff:+,.0f}`)")

    return "\n".join(lines)


def fmt_trend_summary(data: list, period_label: str) -> str:
    """長週期趨勢摘要（不顯示逐日列表，只顯示統計，搭配圖表使用）"""
    if not data:
        return "⚠️ *尚無趨勢資料*\n\n請確認資料是否已匯入。"
    data = sorted(data, key=lambda x: x['date'])
    revs     = [w['revenue'] for w in data]
    total    = sum(revs)
    avg_rev  = total / len(revs) if revs else 0
    max_rev  = max(revs)
    min_rev  = min(revs)
    max_date = data[revs.index(max_rev)]['date'][-5:]
    min_date = data[revs.index(min_rev)]['date'][-5:]
    up_days  = sum(1 for j in range(1, len(revs)) if revs[j] > revs[j-1])
    dn_days  = sum(1 for j in range(1, len(revs)) if revs[j] < revs[j-1])
    ft_days  = len(revs) - 1 - up_days - dn_days
    def _fmt_date(d):
        # 將 YYYY-MM-DD 或 YYYY/MM/DD 轉成 YYYY/MM/DD
        return str(d).replace('-', '/')[:10]
    start_d = _fmt_date(data[0]['date'])
    end_d   = _fmt_date(data[-1]['date'])

    # 前半段 vs 後半段比較
    mid   = len(revs) // 2
    first = sum(revs[:mid]) / mid if mid else 0
    last  = sum(revs[mid:]) / (len(revs) - mid) if len(revs) > mid else 0
    trend_icon = '📈' if last >= first else '📉'
    trend_pct  = (last - first) / first * 100 if first else 0

    lines = [
        f"📊 *業績趨勢摘要 — {period_label}*",
        f"  {start_d} ~ {end_d}  共 {len(revs)} 天有資料",
        "",
        f"  💰 *合計*：`NT$ {total:,.0f}` ({total/10000:.1f}萬)",
        f"  📐 *日均*：`NT$ {avg_rev:,.0f}` ({avg_rev/10000:.1f}萬)",
        "",
        f"  🏆 *最高*：`NT$ {max_rev:,.0f}` ({max_rev/10000:.1f}萬) [{max_date}]",
        f"  🔻 *最低*：`NT$ {min_rev:,.0f}` ({min_rev/10000:.1f}萬) [{min_date}]",
        "",
        f"  📈 上升 {up_days}天  📉 下降 {dn_days}天  → 持平 {ft_days}天",
        f"  {trend_icon} 整體趨勢：後半段日均 vs 前半段 {'+' if trend_pct >= 0 else ''}{trend_pct:.1f}%",
        "",
        "_📊 走勢圖如下_",
    ]
    return "\n".join(lines)


def gen_aggregated_chart(data_points: list, title: str, granularity: str = 'weekly') -> str:
    """產生聚合柱狀圖 PNG — 週/月粒度，上升紅/下降綠
    granularity: 'weekly' → 按週分組, 'monthly' → 按月分組
    """
    try:
        import matplotlib
        matplotlib.use('Agg')
        _setup_mpl_chinese()
        import matplotlib.pyplot as plt
        import matplotlib.patches as mpatches
        from datetime import datetime as _dt3
        import tempfile
        from collections import defaultdict

        data = sorted(data_points, key=lambda x: x['date'])
        if not data:
            return ''

        buckets = defaultdict(float)
        for w in data:
            try:
                d_obj = _dt3.strptime(w['date'].replace('/', '-'), '%Y-%m-%d')
                if granularity == 'monthly':
                    key = d_obj.strftime('%Y/%m')
                else:
                    # ISO week key: YYYY-Www
                    iso = d_obj.isocalendar()
                    key = f"{iso[0]}/W{iso[1]:02d}"
            except Exception:
                continue
            buckets[key] += w['revenue']

        if not buckets:
            return ''

        labels   = sorted(buckets.keys())
        revenues = [buckets[k] / 10000 for k in labels]  # 萬元

        UP_COLOR   = '#E53935'
        DOWN_COLOR = '#43A047'
        NEUTRAL    = '#1565C0'

        colors = []
        for i, r in enumerate(revenues):
            if i == 0:
                colors.append(NEUTRAL)
            elif r >= revenues[i - 1]:
                colors.append(UP_COLOR)
            else:
                colors.append(DOWN_COLOR)

        fig, ax = plt.subplots(figsize=(max(10, len(labels) * 0.9), 6))
        fig.patch.set_facecolor('#FAFAFA')
        ax.set_facecolor('#FAFAFA')

        bars = ax.bar(range(len(labels)), revenues, color=colors,
                      edgecolor='white', linewidth=0.8, width=0.65)

        # 金額標籤
        for i, (bar, r) in enumerate(zip(bars, revenues)):
            ax.text(bar.get_x() + bar.get_width() / 2, bar.get_height() + max(revenues) * 0.02,
                    f'{r:.1f}萬', ha='center', va='bottom', fontsize=8.5,
                    color=colors[i], fontweight='bold')

        # 漲跌百分比標籤
        for i in range(1, len(revenues)):
            pct = (revenues[i] - revenues[i-1]) / revenues[i-1] * 100 if revenues[i-1] else 0
            arrow = '▲' if pct >= 0 else '▼'
            ax.text(i, revenues[i] / 2,
                    f'{arrow}{abs(pct):.1f}%', ha='center', va='center',
                    fontsize=7.5, color='white', fontweight='bold')

        # 平均線
        avg = sum(revenues) / len(revenues)
        ax.axhline(avg, color='#FF6F00', linestyle='--', linewidth=1.5, alpha=0.8,
                   label=f'平均 {avg:.1f}萬')

        ax.set_xticks(range(len(labels)))
        ax.set_xticklabels(labels, fontsize=9, rotation=30 if len(labels) > 8 else 0)
        ax.set_ylabel('業績（萬元）', fontsize=11)
        ax.set_title(title, fontsize=13, fontweight='bold', pad=12)
        ax.grid(True, axis='y', alpha=0.25, linestyle='--', color='#BDBDBD')
        ax.yaxis.set_major_formatter(plt.FuncFormatter(lambda x, _: f'{x:,.0f}'))
        ax.legend(fontsize=9, framealpha=0.85)

        up_p   = mpatches.Patch(color=UP_COLOR,   label='▲ 上升')
        dn_p   = mpatches.Patch(color=DOWN_COLOR, label='▼ 下降')
        ax.legend(handles=[up_p, dn_p] + ax.get_legend_handles_labels()[0],
                  loc='upper left', fontsize=9, framealpha=0.85)

        plt.tight_layout()
        tmp = tempfile.NamedTemporaryFile(
            suffix='.png', prefix='ocbot_agg_', delete=False, dir='/tmp')
        plt.savefig(tmp.name, dpi=130, bbox_inches='tight')
        plt.close()
        return tmp.name
    except ImportError:
        sys_log.warning('[OpenClawBot] matplotlib not installed')
        return ''
    except Exception as e:
        sys_log.error(f'[OpenClawBot] gen_aggregated_chart: {e}')
        return ''


# ── AI 自然語言 ───────────────────────────────────────────────

# 功能說明關鍵詞（P8：讓使用者問功能時能得到正確答案）
_HELP_KEYWORDS = (
    '怎麼用', '如何用', '怎麼操作', '教我', '說明', '功能', '按鈕',
    '怎麼查', '哪個按鈕', '在哪裡', '怎麼產出', '怎麼看', '如何查',
    '圖片比價', '照片比價', '如何比價', '怎麼比價',
    '簡報怎麼', '怎麼出簡報', '如何產報告', 'PPT怎麼', 'ppt怎麼',
    '目標怎麼設', '如何設定目標', '怎麼設目標',
    '告警', '異常通知', '怎麼知悉',
    '早報', '晚報', '週報', '自動推播',
    '使用說明', '操作說明', 'help', '幫助',
)

def _is_help_question(q: str) -> bool:
    ql = q.lower()
    return any(kw in ql for kw in _HELP_KEYWORDS)


# ══════════════════════════════════════════════════════════════
# Gemini Function Calling 工具定義
# AI 自主決定要呼叫哪些工具，不再靠 if/else 規則
# ══════════════════════════════════════════════════════════════
_FC_TOOLS = [{
    "function_declarations": [
        {
            "name": "query_sales",
            "description": (
                "查詢公司內部業績資料庫。"
                "當用戶詢問自家業績、銷售數字、訂單量、毛利率、熱銷商品、廠商排行等內部數據時使用。"
            ),
            "parameters": {
                "type": "OBJECT",
                "properties": {
                    "period": {
                        "type": "STRING",
                        "enum": ["today","yesterday","this_week","last_week",
                                 "this_month","last_month","date","month","range","all"],
                        "description": "查詢時間範圍"
                    },
                    "date":       {"type": "STRING",  "description": "具體日期 YYYY/MM/DD，period=date 時填"},
                    "year":       {"type": "INTEGER", "description": "年份，period=month 時填"},
                    "month":      {"type": "INTEGER", "description": "月份1-12，period=month 時填"},
                    "start_date": {"type": "STRING",  "description": "開始日期 YYYY/MM/DD，period=range 時填"},
                    "end_date":   {"type": "STRING",  "description": "結束日期 YYYY/MM/DD，period=range 時填"},
                },
                "required": ["period"]
            }
        },
        {
            "name": "get_market_intel",
            "description": (
                "取得外部市場情報。"
                "當用戶詢問市場趨勢、電商新聞、消費者討論（PTT/Dcard）、"
                "熱搜關鍵字、YouTube爆紅商品、匯率、天氣、即將到來的節慶促銷檔期等外部資訊時使用。"
            ),
            "parameters": {
                "type": "OBJECT",
                "properties": {
                    "sources": {
                        "type": "ARRAY",
                        "items": {
                            "type": "STRING",
                            "enum": ["news","trends","social","youtube","weather","exchange","calendar"]
                        },
                        "description": "需要哪些情報來源，可多選。全部不確定時選 ['news','trends','calendar']"
                    }
                },
                "required": ["sources"]
            }
        },
        {
            "name": "get_knowledge",
            "description": (
                "從歷史知識庫和過去 AI 分析記錄中語義檢索相關資訊。"
                "當需要參考過去的分析結論、歷史業績模式、或相似問題的回答時使用。"
            ),
            "parameters": {
                "type": "OBJECT",
                "properties": {
                    "query": {"type": "STRING", "description": "檢索關鍵字或問題描述"}
                },
                "required": ["query"]
            }
        }
    ]
}]


def _execute_tool(name: str, args: dict) -> dict:
    """執行 Gemini 指定的工具，回傳結構化結果"""
    now   = datetime.now(TAIPEI_TZ)
    today = now.strftime("%Y/%m/%d")
    yd    = (now - timedelta(days=1)).strftime("%Y/%m/%d")

    # ── query_sales ───────────────────────────────────────────
    if name == "query_sales":
        period = args.get("period", "today")
        result = {}

        if period in ("today", "date"):
            d = args.get("date", today)
            sales = query_sales(d)
            tops  = query_top_products(d, 10)
            vens  = query_top_vendors(d, 5)
            result = {"date": d, "sales": sales, "top_products": tops, "top_vendors": vens}

        elif period == "yesterday":
            sales = query_sales(yd)
            tops  = query_top_products(yd, 10)
            result = {"date": yd, "sales": sales, "top_products": tops}

        elif period in ("this_week", "last_week"):
            trend = query_weekly_trend()
            result = {"weekly_trend": trend}

        elif period == "this_month":
            ms = query_monthly_summary(now.year, now.month)
            result = {"monthly": ms, "year": now.year, "month": now.month}

        elif period == "last_month":
            first = now.replace(day=1)
            lm = (first - timedelta(days=1))
            ms = query_monthly_summary(lm.year, lm.month)
            result = {"monthly": ms, "year": lm.year, "month": lm.month}

        elif period == "month":
            yr = args.get("year", now.year)
            mo = args.get("month", now.month)
            ms = query_monthly_summary(yr, mo)
            result = {"monthly": ms, "year": yr, "month": mo}

        elif period == "range":
            s = args.get("start_date", yd)
            e = args.get("end_date", today)
            rng = query_date_range(s, e)
            tops = query_top_products_range(s, e, 10)
            result = {"range": rng, "start": s, "end": e, "top_products": tops}

        elif period == "all":
            history = query_daily_history(30)
            avail   = query_available_months()
            result  = {"history_30d": history, "available_months": avail}

        # 附上可查詢月份清單
        avail = query_available_months()
        result["available_months"] = [m["month"] for m in avail] if avail else []
        result["report_url"] = f"{MOMO_BASE_URL}/reports/daily/{args.get('date', today)}"
        return result

    # ── get_market_intel ──────────────────────────────────────
    elif name == "get_market_intel":
        sources = args.get("sources", ["news", "trends", "calendar"])
        data = {}
        source_map = {
            "news":     lambda: {**get_tw_media_news(), **{"google": get_ecommerce_news()}},
            "trends":   get_taiwan_trends,
            "social":   get_dcard_trends,
            "youtube":  get_youtube_trending,
            "weather":  get_taiwan_weather,
            "exchange": get_twbank_exchange_rates,
            "calendar": get_upcoming_events,
        }
        for src in sources:
            if src in source_map:
                try:
                    data[src] = source_map[src]()
                except Exception as e:
                    data[src] = {"error": str(e)}
        return data

    # ── get_knowledge ─────────────────────────────────────────
    elif name == "get_knowledge":
        if not _LEARNING_ENABLED:
            return {"results": [], "note": "知識庫未啟用"}
        try:
            items = retrieve_knowledge(args.get("query", ""), top_k=4, min_quality=0.4)
            return {"results": [
                {"category": i["category"], "title": i["title"],
                 "content": i["content"][:400], "score": i["similarity"]}
                for i in items
            ]}
        except Exception as e:
            return {"results": [], "error": str(e)}

    return {"error": f"unknown tool: {name}"}


def openclaw_answer(question: str):
    """
    Function Calling 架構 — AI 自主決定查什麼、怎麼回答
    不再靠 if/else 規則判斷意圖
    """
    now       = datetime.now(TAIPEI_TZ)
    today_str = now.strftime("%Y/%m/%d")

    # ── 功能說明直接導 help ───────────────────────────────────
    if _is_help_question(question):
        help_text = (
            "📖 *OpenClaw 功能說明*\n\n"
            "你可以直接點下方按鈕進入對應功能，或問我：\n\n"
            "🔍 *常見問題*\n"
            "  「今天業績」→ 點 📊業績查詢 → 今日\n"
            "  「熱銷商品」→ 點 🏆商品廠商 → 熱銷商品\n"
            "  「出日報PPT」→ 點 📄簡報報表 → 日報\n"
            "  「促銷分析」→ 點 📈智能分析 → 促銷追蹤\n"
            "  「設定月目標」→ 點 🎯目標管理 → 月目標\n"
            "  「圖片比價」→ 直接傳圖片給我\n"
            "  「競品比較」→ 點 🔍競品日報\n\n"
            "📌 *完整說明*：點下方「❓使用說明」按鈕\n\n"
            "_或直接問我任何問題，我會自動找資料回答！_"
        )
        return help_text, [
            [{"text": "❓ 完整使用說明", "callback_data": "cmd:help"}],
            [{"text": "📊 業績查詢", "callback_data": "menu:sales"},
             {"text": "🏆 商品廠商", "callback_data": "menu:products"}],
        ]

    from services.ollama_service import ollama_service

    # ── Ollama 首選（使用傳統 prompt 注入上下文）─────────────────
    try:
        if ollama_service.check_connection():
            intent = resolve_query_intent(question)
            today  = today_str
            yd     = (now - timedelta(days=1)).strftime("%Y/%m/%d")
            d      = intent.get("date", yd) if intent["type"] == "day" else yd

            db_ctx = ""
            if intent["type"] == "day":
                s = query_sales(d)
                t = query_top_products(d, 8)
                if s.get("found"):
                    db_ctx = (
                        f"日期{d} 業績NT${s.get('revenue',0):,.0f} "
                        f"訂單{s.get('orders',0)}筆 毛利{s.get('gross_margin',0):.1f}% "
                        f"TOP5: " + " / ".join(
                            f"[{p.get('id','')}]{p['name']}(NT${p['revenue']:,.0f})"
                            for p in t[:5])
                    )
            elif intent["type"] == "month":
                ms = query_monthly_summary(intent["year"], intent["month"])
                if ms.get("found"):
                    db_ctx = (f"{intent['year']}年{intent['month']:02d}月 "
                              f"業績NT${ms.get('revenue',0):,.0f} "
                              f"訂單{ms.get('orders',0)}筆 "
                              f"毛利{ms.get('gross_margin',0):.1f}%")

            mcp_ctx = build_mcp_context(question)

            sys_prompt = (
                f"你是 OpenClaw（小O），電商智能助理。今天{today}。\n"
                + (f"【業績資料】{db_ctx}\n" if db_ctx else "")
                + (f"【市場情報】{mcp_ctx[:400]}\n" if mcp_ctx else "")
                + "請用繁體中文直接回答，不要開場白，300字以內。"
            )
            
            resp = ollama_service.generate(question, system_prompt=sys_prompt, timeout=180)
            if resp.success and resp.content:
                if _LEARNING_ENABLED:
                    import threading as _thr
                    _thr.Thread(target=store_conversation,
                        args=(0, 0, question, resp.content, "ollama", []),
                        daemon=True).start()
                return resp.content, None
            else:
                sys_log.warning(f"[Ollama] 生成失敗: {resp.error}，fallback 到 Gemini")
    except Exception as e:
        sys_log.warning(f"[Ollama] 例外發生: {e}，fallback 到 Gemini")

    if not GEMINI_API_KEY and not NVIDIA_API_KEY:
        return "（AI 引擎未設定，請確認 API Key 或啟動 Ollama 服務）", None

    # ── Gemini Function Calling (備援 1) ─────────────────────────
    if GEMINI_API_KEY:
        try:
            sys_msg = (
                f"你是 OpenClaw（小O），服務「小龍蝦」電商業務團隊的 AI 助理。\n"
                f"今天是 {today_str}。\n"
                "你有三個工具可以使用：\n"
                "1. query_sales — 查自家業績資料庫\n"
                "2. get_market_intel — 取得外部市場情報（新聞/熱搜/PTT口碑/匯率/天氣/節慶）\n"
                "3. get_knowledge — 查歷史分析知識庫\n\n"
                "根據用戶問題，自主決定要呼叫哪些工具（可以同時呼叫多個）。\n"
                "工具結果回來後，用繁體中文自然回答，不要開場白，不要多餘的客套話。"
            )

            # 第一輪：讓 Gemini 判斷需要呼叫哪些工具
            conversation = [
                {"role": "user", "parts": [{"text": sys_msg + "\n\n用戶問：" + question}]}
            ]
            payload = {
                "contents": conversation,
                "tools": _FC_TOOLS,
                "tool_config": {"function_calling_config": {"mode": "AUTO"}},
                "generationConfig": {"temperature": 0.3, "maxOutputTokens": 600},
            }
            r1 = requests.post(
                f"{GEMINI_BASE_URL}/{GEMINI_MODEL}:generateContent?key={GEMINI_API_KEY}",
                headers={"Content-Type": "application/json"},
                json=payload, timeout=30,
            )
            r1.raise_for_status()
            resp1 = r1.json()
            candidate = resp1.get("candidates", [{}])[0]
            parts     = candidate.get("content", {}).get("parts", [])

            # 如果沒有 function call，直接回傳文字
            tool_calls = [p["functionCall"] for p in parts if "functionCall" in p]
            if not tool_calls:
                text = "".join(p.get("text", "") for p in parts if "text" in p).strip()
                if text:
                    if _LEARNING_ENABLED:
                        import threading as _thr
                        _thr.Thread(target=store_conversation,
                            args=(0, 0, question, text, "direct", []),
                            daemon=True).start()
                    return text, None

            # 第二輪：執行所有工具，把結果送回 Gemini
            tool_results = []
            used_sources = []
            for tc in tool_calls:
                fn_name = tc["name"]
                fn_args = tc.get("args", {})
                sys_log.info(f"[FC] calling {fn_name}({fn_args})")
                fn_result = _execute_tool(fn_name, fn_args)
                used_sources.append(fn_name)
                tool_results.append({
                    "functionResponse": {
                        "name": fn_name,
                        "response": fn_result
                    }
                })

            # 組建多輪對話
            conversation.append({"role": "model", "parts": parts})
            conversation.append({"role": "user",  "parts": tool_results})

            payload2 = {
                "contents": conversation,
                "tools": _FC_TOOLS,
                "tool_config": {"function_calling_config": {"mode": "NONE"}},  # 第二輪強制回文字
                "generationConfig": {
                    "temperature": 0.3,
                    "maxOutputTokens": 600,
                },
            }
            r2 = requests.post(
                f"{GEMINI_BASE_URL}/{GEMINI_MODEL}:generateContent?key={GEMINI_API_KEY}",
                headers={"Content-Type": "application/json"},
                json=payload2, timeout=35,
            )
            r2.raise_for_status()
            resp2    = r2.json()
            parts2   = resp2.get("candidates", [{}])[0].get("content", {}).get("parts", [])
            final    = "".join(p.get("text", "") for p in parts2 if "text" in p).strip()

            if final:
                sys_log.info(f"[FC] done tools={used_sources} reply={len(final)}chars")
                if _LEARNING_ENABLED:
                    import threading as _thr
                    _thr.Thread(target=store_conversation,
                        args=(0, 0, question, final, ",".join(used_sources), used_sources),
                        daemon=True).start()
                return final, None

        except Exception as e:
            sys_log.warning(f"[FC] Gemini Function Calling failed: {e}, fallback NIM")

    # ── NIM 備援（不支援 FC，用傳統 prompt）─────────────────
    if NVIDIA_API_KEY:
        try:
            intent = resolve_query_intent(question)
            today  = today_str
            yd     = (now - timedelta(days=1)).strftime("%Y/%m/%d")
            d      = intent.get("date", yd) if intent["type"] == "day" else yd

            db_ctx = ""
            if intent["type"] == "day":
                s = query_sales(d)
                t = query_top_products(d, 8)
                if s.get("found"):
                    db_ctx = (
                        f"日期{d} 業績NT${s.get('revenue',0):,.0f} "
                        f"訂單{s.get('orders',0)}筆 毛利{s.get('gross_margin',0):.1f}% "
                        f"TOP5: " + " / ".join(
                            f"[{p.get('id','')}]{p['name']}(NT${p['revenue']:,.0f})"
                            for p in t[:5])
                    )
            elif intent["type"] == "month":
                ms = query_monthly_summary(intent["year"], intent["month"])
                if ms.get("found"):
                    db_ctx = (f"{intent['year']}年{intent['month']:02d}月 "
                              f"業績NT${ms.get('revenue',0):,.0f} "
                              f"訂單{ms.get('orders',0)}筆 "
                              f"毛利{ms.get('gross_margin',0):.1f}%")

            mcp_ctx = build_mcp_context(question)

            nim_prompt = (
                f"你是 OpenClaw（小O），電商智能助理。今天{today}。\n"
                + (f"【業績資料】{db_ctx}\n" if db_ctx else "")
                + (f"【市場情報】{mcp_ctx[:400]}\n" if mcp_ctx else "")
                + f"\n用戶問：{question}\n"
                "請用繁體中文直接回答，不要開場白，300字以內。"
            )
            r = requests.post(
                f"{NVIDIA_BASE_URL}/chat/completions",
                headers={"Authorization": f"Bearer {NVIDIA_API_KEY}",
                         "Content-Type": "application/json"},
                json={
                    "model": CHAT_MODEL,
                    "messages": [{"role": "user", "content": nim_prompt}],
                    "max_tokens": 500, "temperature": 0.3,
                },
                timeout=20,
            )
            r.raise_for_status()
            return r.json()["choices"][0]["message"]["content"].strip(), None
        except Exception as e:
            sys_log.error(f"[FC] NIM fallback error: {e}")

    return "（AI 引擎全部異常：Ollama 超時，Gemini/NIM 備援亦無回應或達速率限制，請稍後再試）", None



# ── 指令處理 ──────────────────────────────────────────────────
def handle_cmd(cmd, arg, chat_id, reply_to):
    ld = latest_date() or datetime.now(TAIPEI_TZ).strftime('%Y/%m/%d')
    target = normalize_date(arg) if arg else ld

    if cmd in ('sales', '業績'):
        s = query_sales(target)
        t = query_top_products(target, 3)
        send_message(chat_id, fmt_sales(s, t, f"{MOMO_BASE_URL}/reports/daily/{target}"),
                     reply_to, sales_quick_kb(target) if s.get('found') else None)

    elif cmd in ('top', '熱銷'):
        p = query_top_products(target, 10)
        kb = [[{'text': '📊 查業績', 'callback_data': f'cmd:sales:{target}'},
               {'text': '📋 完整報表', 'callback_data': f'cmd:report:{target}'}]]
        send_message(chat_id, fmt_products(p, target), reply_to, kb)

    elif cmd in ('vendor', '廠商'):
        v = query_top_vendors(target, 10)
        kb = [[{'text': '🏆 熱銷商品', 'callback_data': f'cmd:top:{target}'},
               {'text': '📊 查業績',   'callback_data': f'cmd:sales:{target}'}]]
        send_message(chat_id, fmt_vendors(v, target), reply_to, kb)

    elif cmd in ('trend', '趨勢'):
        import calendar as _cal
        from datetime import date as _date
        now_d  = datetime.now(TAIPEI_TZ).date()
        sub    = arg.lower().strip() if arg else '7'

        # 決定查詢區間
        if sub in ('', '7', 'week', 'weekly', '週', '近7日', '七天'):
            ld_str  = latest_date() or now_d.strftime('%Y/%m/%d')
            end_d   = datetime.strptime(ld_str.replace('/', '-'), '%Y-%m-%d').date()
            start_d = end_d - timedelta(days=6)
            period_label = '近7日'
        elif sub in ('month', '30', '月', '本月', '近30日', '近一月'):
            end_d   = now_d
            start_d = end_d - timedelta(days=29)
            period_label = '近30日'
        elif sub in ('quarter', 'q', '季', '近季', '近3月', '近三月'):
            end_d   = now_d
            start_d = end_d - timedelta(days=89)
            period_label = '近3個月'
        elif sub in ('half', '半年', '近半年', '6月', '六個月'):
            end_d   = now_d
            start_d = end_d - timedelta(days=179)
            period_label = '近半年'
        elif sub in ('year', 'yearly', '年', '本年', '近年', '近一年'):
            end_d   = now_d
            start_d = end_d - timedelta(days=364)
            period_label = '近一年'
        elif re.fullmatch(r'\d{4}/\d{1,2}', sub):
            yr_s, mo_s = sub.split('/')
            yr, mo     = int(yr_s), int(mo_s)
            start_d    = _date(yr, mo, 1)
            end_d      = _date(yr, mo, _cal.monthrange(yr, mo)[1])
            period_label = f'{yr}年{mo:02d}月'
        elif re.fullmatch(r'\d{4}', sub):
            yr      = int(sub)
            start_d = _date(yr, 1, 1)
            end_d   = _date(yr, 12, 31)
            period_label = f'{yr}年度'
        elif re.fullmatch(r'\d{4}/[qQ]([1-4])', sub):
            m2     = re.fullmatch(r'(\d{4})/[qQ]([1-4])', sub)
            yr, qn = int(m2.group(1)), int(m2.group(2))
            q_months = [(1, 3), (4, 6), (7, 9), (10, 12)]
            sm, em  = q_months[qn - 1]
            start_d = _date(yr, sm, 1)
            end_d   = _date(yr, em, _cal.monthrange(yr, em)[1])
            period_label = f'{yr}年Q{qn}'
        else:
            ld_str  = latest_date() or now_d.strftime('%Y/%m/%d')
            end_d   = datetime.strptime(ld_str.replace('/', '-'), '%Y-%m-%d').date()
            start_d = end_d - timedelta(days=6)
            period_label = '近7日'

        start_str  = start_d.strftime('%Y/%m/%d')
        end_str    = end_d.strftime('%Y/%m/%d')
        days_count = (end_d - start_d).days + 1
        data       = query_trend_range(start_str, end_str)
        if not data:
            data = query_weekly_trend()

        chart_title = f'業績趨勢走勢圖 — {period_label}（{start_str}~{end_str}）'

        if days_count <= 35:
            # 週/月：逐日文字 + 折線圖
            send_message(chat_id, fmt_trend(data, period_label), reply_to, _submenu_trend())
            try:
                png = gen_trend_chart(data_points=data, title=chart_title)
                if png:
                    send_photo(chat_id, png, caption=f'📈 {period_label} 趨勢走勢圖')
                    try: os.unlink(png)
                    except Exception: pass
            except Exception as _te:
                sys_log.warning(f'[OpenClawBot] trend chart error: {_te}')
        else:
            # 季/半年/年：摘要 + 聚合柱狀圖（不洗版面）
            granularity = 'monthly' if days_count > 100 else 'weekly'
            send_message(chat_id, fmt_trend_summary(data, period_label), reply_to, _submenu_trend())
            try:
                png = gen_aggregated_chart(data, chart_title, granularity=granularity)
                if png:
                    unit = '月' if granularity == 'monthly' else '週'
                    send_photo(chat_id, png, caption=f'📊 {period_label} 業績走勢（按{unit}）')
                    try: os.unlink(png)
                    except Exception: pass
            except Exception as _te:
                sys_log.warning(f'[OpenClawBot] trend agg chart error: {_te}')

    elif cmd in ('report', '報表'):
        send_message(chat_id, f"⏳ 正在產生 {target} 完整報表...", reply_to, parse_mode=None)
        try:
            import os as _os
            pdf_path = generate_daily_pdf(target)
            if pdf_path:
                if pdf_path.endswith('.xlsx'):
                    ext = 'Excel'
                elif pdf_path.endswith('.csv'):
                    ext = 'CSV'
                else:
                    ext = 'PDF'
                send_document(
                    chat_id, pdf_path,
                    caption=f"📊 {target} 業績報表（{ext}）",
                    reply_to=reply_to
                )
                _os.unlink(pdf_path)
            else:
                send_message(chat_id,
                             f"⚠️ 報表產生失敗，請稍後再試",
                             reply_to)
        except Exception as e:
            sys_log.error(f"[OpenClawBot] report cmd error: {e}")
            send_message(chat_id,
                         f"⚠️ 報表產生發生錯誤，請稍後再試",
                         reply_to)

    elif cmd == 'news':
        try:
            from services.mcp_context_service import get_ecommerce_news
            data = get_ecommerce_news()
            news = data.get('news', [])
            sys_log.info(f"[OpenClawBot] news: {len(news)} items")
            if news:
                lines = ["📰 即時電商新聞"]
                for n in news[:6]:
                    title = n['title'][:60]
                    url   = n.get('url', '')
                    src   = n.get('source', '')
                    if url:
                        lines.append(f"• <a href='{url}'>{title}</a>（{src}）")
                    else:
                        lines.append(f"• {title}（{src}）")
                send_message(chat_id, "\n".join(lines), reply_to, parse_mode='HTML')
            else:
                send_message(chat_id, "⚠️ 新聞資料暫時無法取得", reply_to, parse_mode=None)
        except Exception as e:
            sys_log.error(f"[OpenClawBot] news error: {e}", exc_info=True)
            send_message(chat_id, "⚠️ 新聞功能暫時異常，請稍後再試", reply_to, parse_mode=None)

    elif cmd == 'weather':
        try:
            from services.mcp_context_service import get_taiwan_weather
            data = get_taiwan_weather()
            weather = data.get('weather', {})
            sys_log.info(f"[OpenClawBot] weather: {list(weather.keys())}")
            if weather:
                lines = ["🌤 台灣天氣預報"]
                for city, w in list(weather.items())[:6]:
                    wx   = w.get('Wx', w.get('weatherDesc', ''))
                    pop  = w.get('PoP', '')
                    tmin = w.get('MinT', '')
                    tmax = w.get('MaxT', '')
                    tmp  = w.get('temp', f"{tmin}~{tmax}°C" if tmin else '')
                    lines.append(f"• {city}：{wx} {tmp}" +
                                  (f" 降雨{pop}%" if pop else ""))
                send_message(chat_id, "\n".join(lines), reply_to, parse_mode=None)
            else:
                send_message(chat_id, "⚠️ 天氣資料暫時無法取得", reply_to, parse_mode=None)
        except Exception as e:
            sys_log.error(f"[OpenClawBot] weather error: {e}", exc_info=True)
            send_message(chat_id, "⚠️ 天氣功能暫時異常，請稍後再試", reply_to, parse_mode=None)

    elif cmd == 'trends':
        try:
            from services.mcp_context_service import get_taiwan_trends
            data = get_taiwan_trends()
            trends = data.get('trends', [])
            if trends:
                lines = ["🔥 *台灣 Google 熱搜（即時）*\n"]
                for i, t in enumerate(trends[:12], 1):
                    lines.append(f"{i}. {t['keyword']}")
                send_message(chat_id, "\n".join(lines), reply_to, parse_mode='Markdown')
            else:
                send_message(chat_id, "⚠️ 熱搜資料暫時無法取得", reply_to)
        except Exception as e:
            send_message(chat_id, f"⚠️ 取得失敗: {e}", reply_to)

    elif cmd == 'dcard':
        try:
            from services.mcp_context_service import get_dcard_trends
            data = get_dcard_trends()
            posts = data.get('posts', [])
            if posts:
                lines = ["💬 *Dcard 消費者討論熱點*\n"]
                for p in posts[:8]:
                    lines.append(f"• [{p['board']}] {p['title'][:50]}")
                send_message(chat_id, "\n".join(lines), reply_to, parse_mode='Markdown')
            else:
                send_message(chat_id, "⚠️ Dcard 資料暫時無法取得", reply_to)
        except Exception as e:
            send_message(chat_id, f"⚠️ 取得失敗: {e}", reply_to)

    elif cmd == 'exchange':
        try:
            from services.mcp_context_service import get_twbank_exchange_rates
            data = get_twbank_exchange_rates()
            rates = data.get('rates', {})
            if rates:
                lines = ["💱 *台灣銀行即時匯率*\n"]
                for code, info in rates.items():
                    name = info.get('name', code)
                    if 'buy' in info:
                        lines.append(f"• {name}（{code}） 買入 `{info['buy']}` / 賣出 `{info['sell']}`")
                    elif 'mid' in info:
                        lines.append(f"• {name}（{code}） `{info['mid']}`")
                lines.append(f"\n_資料來源：台灣銀行 {data.get('fetched_at','')[:16]}_")
                send_message(chat_id, "\n".join(lines), reply_to, parse_mode='Markdown')
            else:
                send_message(chat_id, "⚠️ 匯率資料暫時無法取得", reply_to)
        except Exception as e:
            send_message(chat_id, f"⚠️ 取得失敗: {e}", reply_to)

    elif cmd == 'calendar':
        try:
            from services.mcp_context_service import get_upcoming_events
            data = get_upcoming_events(60)
            events = data.get('events', [])
            if events:
                lines = ["📅 *近期電商節慶行事曆*\n"]
                for ev in events:
                    days = ev['days_to_event']
                    day_str = f"還有 {days} 天" if days > 0 else ("今天" if days == 0 else f"進行中（+{-days}天）")
                    lines.append(f"{ev['status']} *{ev['name']}*（{ev['date']}，{day_str}）")
                    lines.append(f"  建議提前 {ev['warmup_days']} 天開始備戰")
                send_message(chat_id, "\n".join(lines), reply_to, parse_mode='Markdown')
            else:
                send_message(chat_id, "✅ 近 60 天無重大電商節慶", reply_to)
        except Exception as e:
            send_message(chat_id, f"⚠️ 取得失敗: {e}", reply_to)

    elif cmd == 'youtube':
        try:
            from services.mcp_context_service import get_youtube_trending
            data = get_youtube_trending()
            videos = data.get('videos', [])
            if videos:
                lines = ["▶️ *YouTube 熱門開箱/推薦影片*\n"]
                for v in videos[:6]:
                    title = v['title'][:55]
                    url   = v.get('url', '')
                    if url:
                        lines.append(f"• <a href='{url}'>{title}</a>")
                    else:
                        lines.append(f"• {title}")
                send_message(chat_id, "\n".join(lines), reply_to, parse_mode='HTML')
            else:
                send_message(chat_id, "⚠️ YouTube 資料暫時無法取得", reply_to)
        except Exception as e:
            send_message(chat_id, f"⚠️ 取得失敗: {e}", reply_to)

    # ── PChome 比價指令 ───────────────────────────────────────────

    elif cmd in ('competitor', '比價', 'price'):
        # /competitor          → 昨日 TOP30 競品比對日報
        # /competitor 商品名    → 單品即時比價
        if not _PCHOME_AVAILABLE:
            send_message(chat_id, '⚠️ PChome 比價服務未啟用', reply_to)
            return

        keyword = arg.strip() if arg else ''
        is_date = bool(keyword and re.match(r'\d{4}[/-]\d{1,2}[/-]\d{1,2}', keyword))
        if keyword and not is_date:
            # 即時比價（商品關鍵字）
            send_message(chat_id, f'🔍 正在比價「{keyword}」，請稍候...', reply_to, parse_mode=None)

            def _compare_bg(_kw, _chat_id, _reply_to):
                try:
                    from services.pchome_crawler import search_pchome as _sp, find_best_match as _fm
                    # 搜尋 momo products
                    with _db().connect() as c:
                        rows = c.execute(text("""
                            SELECT p.name, p.i_code,
                                   COALESCE(pr.price, 0)
                            FROM products p
                            LEFT JOIN (
                                SELECT product_id, price,
                                       ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY timestamp DESC) as rn
                                FROM price_records
                            ) pr ON p.id=pr.product_id AND pr.rn=1
                            WHERE p.name ILIKE :kw
                            ORDER BY pr.price DESC NULLS LAST
                            LIMIT 5
                        """), {'kw': f'%{_kw}%'}).fetchall()

                    results = []
                    for row in rows:
                        name, icode, price = row[0], row[1], float(row[2] or 0)
                        cmp = pchome_compare(name, price, icode)
                        results.append(cmp)
                        import time; time.sleep(0.6)

                    if not results:
                        # 沒有 momo 商品 → 直接搜尋 PChome
                        pc_results = _sp(_kw, limit=5)
                        if pc_results:
                            lines = [f'🔍 *PChome 商品搜尋：{_kw}*', '']
                            for i, p in enumerate(pc_results[:5], 1):
                                stk = '✅' if p['in_stock'] else '❌'
                                lines.append(f'{i}. {p["name"][:30]}')
                                lines.append(f'   {stk} `NT$ {p["price"]:,.0f}`  [查看]({p["url"]})')
                            send_message(_chat_id, '\n'.join(lines), _reply_to)
                        else:
                            send_message(_chat_id, f'⚠️ 在 PChome 找不到「{_kw}」相關商品', _reply_to)
                        return

                    msg = pchome_fmt_compare(results, _kw)
                    kb  = [[{'text': '🔍 重新搜尋', 'callback_data': 'await:search_compare'},
                             {'text': '📊 競品日報', 'callback_data': 'menu:competitor'}]]
                    send_message(_chat_id, msg, _reply_to, kb)
                except Exception as _e:
                    sys_log.error(f'[PChome] compare_bg: {_e}', exc_info=True)
                    send_message(_chat_id, f'⚠️ 比價失敗：{str(_e)[:80]}', _reply_to)

            threading.Thread(target=_compare_bg, args=(keyword, chat_id, reply_to), daemon=True).start()

        else:
            # 無關鍵字或日期參數 → 昨日（或指定日期）熱銷競品日報（背景執行）
            if is_date:
                yesterday = normalize_date(keyword)
                date_label = yesterday
            else:
                yesterday  = (datetime.now(TAIPEI_TZ).date() - timedelta(days=1)).strftime('%Y/%m/%d')
                date_label = f'昨日 ({yesterday[-5:]})'
            send_message(chat_id,
                         f'📊 正在分析 {date_label} TOP30 熱銷商品 vs PChome 比價，預計 30~60 秒...',
                         reply_to, parse_mode=None)

            def _daily_report_bg(_date_str, _chat_id, _reply_to):
                try:
                    results = pchome_batch(_db(), top_n=30, date_str=_date_str)
                    pchome_save(_db(), results)
                    msg = pchome_fmt_report(results, _date_str)
                    kb  = [[{'text': '🔍 搜尋比價',   'callback_data': 'await:search_compare'},
                             {'text': '📄 比價簡報',   'callback_data': 'menu:competitor_ppt'}]]
                    send_message(_chat_id, msg, _reply_to, kb)
                except Exception as _e:
                    sys_log.error(f'[PChome] daily_report_bg: {_e}', exc_info=True)
                    send_message(_chat_id, f'⚠️ 競品分析失敗：{str(_e)[:80]}', _reply_to)

            threading.Thread(target=_daily_report_bg, args=(yesterday, chat_id, reply_to), daemon=True).start()

    # ── v5 新增指令 ──────────────────────────────────────────────

    elif cmd in ('compare', '同比'):
        data = query_comparison(target)
        send_message(chat_id, fmt_comparison(data, target), reply_to,
                     [[{'text': '📊 今日業績', 'callback_data': f'cmd:sales:{target}'},
                       {'text': '🧬 策略矩陣', 'callback_data': f'cmd:strategy:{target}'}]])

    elif cmd in ('category', '分類'):
        cats = query_category_sales(target)
        kb = [
            [{'text': '🗂 鑽取分類', 'callback_data': 'menu:category'},
             {'text': '🏆 熱銷商品', 'callback_data': f'cmd:top:{target}'}],
        ]
        send_message(chat_id, fmt_category(cats, target), reply_to, kb)

    elif cmd in ('catdetail', '分類細項'):
        # arg = "母嬰:2026/04/15" or "母嬰"
        parts_cd = arg.split(':') if arg else []
        cat_name  = parts_cd[0].strip() if parts_cd else ''
        date_cd   = parts_cd[1].strip() if len(parts_cd) > 1 else target
        if not cat_name:
            send_message(chat_id, "⚠️ 請指定分類名稱", reply_to)
        else:
            items = query_category_detail(cat_name, date_cd)
            d_label = date_cd[-5:] if date_cd else '近7日'
            msg = fmt_category_detail(cat_name, items, d_label)
            kb  = [
                [{'text': '⬅ 分類總覽', 'callback_data': 'menu:category'},
                 {'text': '🏆 熱銷商品', 'callback_data': f'cmd:top:{target}'}],
            ]
            send_message(chat_id, msg, reply_to, kb)

    elif cmd in ('restock', '補貨'):
        send_message(chat_id, "⏳ 正在計算補貨預測...", reply_to, parse_mode=None)
        items = query_restock_forecast(20)
        msg   = fmt_restock_forecast(items)
        kb    = [
            [{'text': '🏆 熱銷商品',   'callback_data': f'cmd:top:{target}'},
             {'text': '🧬 商品健康',   'callback_data': f'cmd:health:{target}'}],
            [{'text': '🔄 重新計算',   'callback_data': 'cmd:restock'}],
        ]
        send_message(chat_id, msg, reply_to, kb)

    elif cmd in ('promo', '促銷'):
        # arg = "2026/04/01-2026/04/07"
        if arg and '-' in arg and re.search(r'\d{4}[/-]\d{1,2}[/-]\d{1,2}', arg):
            parts_p = arg.split('-')
            # 可能是 "2026/04/01-2026/04/07" → split on '-' 中間需要處理日期格式
            # 先嘗試用空格分割，再用 '-' 分割最後部分
            m = re.findall(r'\d{4}[/\-]\d{1,2}[/\-]\d{1,2}', arg)
            if len(m) >= 2:
                start_s = normalize_date(m[0])
                end_s   = normalize_date(m[1])
                send_message(chat_id, f"⏳ 正在比較 {start_s} ~ {end_s} 促銷效益...", reply_to, parse_mode=None)
                data = query_promo_comparison(start_s, end_s)
                msg  = fmt_promo_comparison(data)
                promo_range_arg = f'{start_s}-{end_s}'
                kb   = [
                    [{'text': '🎉 再查一個促銷', 'callback_data': 'await:promo_range'},
                     {'text': '📊 業績查詢',     'callback_data': f'cmd:sales:{start_s}'}],
                    [{'text': '📊 產出促銷簡報', 'callback_data': f'cmd:ppt:promo {promo_range_arg}'}],
                ]
                send_message(chat_id, msg, reply_to, kb)
            else:
                send_message(chat_id,
                             "⚠️ 格式錯誤\n請輸入：`2026/04/01-2026/04/07`", reply_to)
        else:
            send_message(chat_id,
                         "🎉 *促銷效益追蹤*\n\n請輸入活動日期範圍：\n`/promo 2026/04/01-2026/04/07`\n或點選 🎉 促銷追蹤 按鈕",
                         reply_to, [[{'text': '🎉 設定促銷範圍', 'callback_data': 'await:promo_range'}]])

    elif cmd in ('goal', '目標'):
        # /goal 200000  設定日目標
        # /goal monthly 5000000  設定月目標
        if arg:
            parts2 = arg.split()
            if parts2[0].lower() == 'monthly' and len(parts2) > 1:
                try:
                    _GOALS['monthly'] = float(parts2[1].replace(',', ''))
                    send_message(chat_id,
                                 f"✅ 月目標設定為 `NT$ {_GOALS['monthly']:,.0f}`",
                                 reply_to, parse_mode='Markdown')
                except ValueError:
                    send_message(chat_id, "⚠️ 格式：`/goal monthly 5000000`",
                                 reply_to, parse_mode='Markdown')
            else:
                try:
                    _GOALS['daily'] = float(parts2[0].replace(',', ''))
                    send_message(chat_id,
                                 f"✅ 日目標設定為 `NT$ {_GOALS['daily']:,.0f}`",
                                 reply_to, parse_mode='Markdown')
                except ValueError:
                    send_message(chat_id, "⚠️ 格式：`/goal 200000`",
                                 reply_to, parse_mode='Markdown')
        else:
            status = get_goal_status(target)
            kb = [[{'text': '📊 今日業績', 'callback_data': f'cmd:sales:{target}'},
                   {'text': '🔄 同期比較', 'callback_data': f'cmd:compare:{target}'}]]
            send_message(chat_id, fmt_goal_status(status), reply_to, kb)

    elif cmd in ('chart', '圖表'):
        send_message(chat_id, "⏳ 正在產生趨勢圖...", reply_to, parse_mode=None)
        try:
            png = gen_trend_chart(14)
            if png:
                send_photo(chat_id, png,
                           caption=f"📉 近14日業績趨勢",
                           reply_to=reply_to)
                os.unlink(png)
                # 再發熱銷商品圖
                png2 = gen_products_chart(target, 10)
                if png2:
                    send_photo(chat_id, png2,
                               caption=f"🏆 {target} 熱銷商品 TOP10",
                               reply_to=None)
                    os.unlink(png2)
            else:
                send_message(chat_id,
                             "⚠️ 圖表功能需安裝 matplotlib（pip install matplotlib）",
                             reply_to, parse_mode=None)
        except Exception as e:
            sys_log.error(f"[OpenClawBot] chart cmd error: {e}")
            send_message(chat_id, "⚠️ 圖表產生失敗", reply_to, parse_mode=None)

    elif cmd in ('health', '健康'):
        anomalies = query_anomalies(target)
        strat     = analyze_product_strategy(target, 10)

        lines = [f"🏥 *商品健康報告*  _({target})_", ""]

        # ── 異常偵測 ──
        if anomalies:
            lines.append(f"⚠️ *業績異常商品*  _（偏差 > 30%）_")
            for a in anomalies[:6]:
                pct  = a.get('pct') or 0
                icon = '📈' if pct > 0 else '📉'
                sid  = _short_id(a['id'])
                name = _esc(a['name'][:18])
                today_r = a.get('today', 0)
                avg_r   = a.get('avg7', 0)
                lines.append(
                    f"{icon} *{abs(pct):.0f}%* {'急升' if pct > 0 else '急降'}  ·  {name}"
                )
                lines.append(
                    f"   今 `${today_r:,.0f}`  ／  7日均 `${avg_r:,.0f}`"
                    f"  `{sid}`"
                )
            lines.append("")
        else:
            lines.append("✅ *無業績異常商品*  _（7日均值偏差均在 30% 以內）_")
            lines.append("")

        # ── 策略分佈 ──
        if strat:
            from collections import Counter
            cnt   = Counter(s['strategy'] for s in strat)
            total = len(strat)
            tag_icon = {'加碼': '🔥', '機會': '💡', '收割': '⚡', '觀察': '⚠️', '持穩': '✅'}
            lines.append(f"📊 *策略分佈*  _（共 {total} 件）_")
            for k, v in cnt.most_common():
                bar  = '▓' * v + '░' * (total - v)
                icon = tag_icon.get(k, '•')
                lines.append(f"  {icon} {k}  {v} 件  `{bar}`")

        kb = [[{'text': '🎲 策略矩陣',  'callback_data': f'cmd:strategy:{target}'},
               {'text': '🏆 熱銷商品',  'callback_data': f'cmd:top:{target}'}],
              [{'text': '📊 今日業績',  'callback_data': f'cmd:sales:{target}'},
               {'text': '🔄 同期比較',  'callback_data': f'cmd:compare:{target}'}]]
        send_message(chat_id, "\n".join(lines), reply_to, kb)

    elif cmd in ('strategy', '策略'):
        strat = analyze_product_strategy(target, 10)
        kb = [[{'text': '🏥 商品健康',  'callback_data': f'cmd:health:{target}'},
               {'text': '🔄 同期比較',  'callback_data': f'cmd:compare:{target}'}],
              [{'text': '🏆 熱銷商品',  'callback_data': f'cmd:top:{target}'},
               {'text': '📊 今日業績',  'callback_data': f'cmd:sales:{target}'}]]
        send_message(chat_id, fmt_strategy(strat, target), reply_to, kb)

    elif cmd in ('ppt', 'slides', '簡報'):
        # /ppt daily [日期]
        # /ppt weekly
        # /ppt monthly [YYYY/MM]
        # /ppt strategy [日期]
        sub_type = arg.lower().strip() if arg else 'daily'
        sub_arg  = ''
        if ' ' in sub_type:
            parts    = sub_type.split(' ', 1)
            sub_type = parts[0]
            sub_arg  = parts[1].strip()

        send_message(chat_id, f"⏳ 正在生成 *{sub_type}* 簡報，請稍候（30~60秒）...",
                     reply_to, parse_mode='Markdown')

        def _ppt_background(_sub_type, _sub_arg, _chat_id, _target, _reply_to):
            try:
                ppt_path = _generate_ppt_cmd(_sub_type, _sub_arg, _chat_id, _target)
                if ppt_path and os.path.exists(ppt_path):
                    type_labels = {
                        'daily': '📊 日報', 'weekly': '📈 週報',
                        'monthly': '📅 月報', 'strategy': '🧬 策略簡報',
                        'competitor': '🔍 競品比較', 'compare': '🔍 競品比較',
                        '競品': '🔍 競品比較', 'promo': '🎉 促銷效益報告',
                    }
                    label   = type_labels.get(_sub_type, '簡報')
                    caption = f"{label} — 由 OpenClaw AI 自動生成\n💡 可用 PowerPoint / Keynote / Google Slides 開啟"
                    send_document(_chat_id, ppt_path, caption=caption, reply_to=_reply_to)
                    try:
                        os.unlink(ppt_path)
                    except Exception:
                        pass
                else:
                    send_message(_chat_id, "⚠️ 簡報生成失敗，請稍後再試", _reply_to)
            except Exception as e:
                sys_log.error(f"[OpenClawBot] /ppt bg error: {e}", exc_info=True)
                send_message(_chat_id, f"⚠️ 簡報生成失敗：{str(e)[:100]}", _reply_to)

        threading.Thread(
            target=_ppt_background,
            args=(sub_type, sub_arg, chat_id, target, reply_to),
            daemon=True
        ).start()

    elif cmd in ('history', 'monthly', '月報', '月份'):
        # /history [YYYY/MM]  — 顯示月份業績，不帶參數時列出所有可用月份
        months = query_available_months()
        if arg and re.match(r'\d{4}/\d{2}', arg):
            yr, mo_s = arg.split('/')
            ms = query_monthly_summary(int(yr), int(mo_s))
            msg = fmt_monthly(ms)
        else:
            # 列出可用月份清單
            if months:
                lines = ["📅 *業績月份索引*",
                         f"共 {len(months)} 個月份有資料", ""]
                for am in months:
                    lines.append(f"  📊 `{am['month']}`  _{am['days']} 天_")
                lines.append("\n_用法：`/history 2026/03` 查看指定月份業績_")
                msg = "\n".join(lines)
            else:
                msg = "⚠️ 暫無月份資料"
        # 快捷按鈕：最近3個月 + 月報PPT（僅在查看特定月份時顯示）
        kb_months = []
        for am in months[:3]:
            kb_months.append({'text': f"📊 {am['month']}", 'callback_data': f"cmd:history:{am['month']}"})
        if arg and re.match(r'\d{4}/\d{2}', arg):
            # 查看特定月份時，額外顯示「產出月報PPT」按鈕
            kb = [kb_months] if kb_months else []
            kb.append([{'text': f'📊 產出 {arg} 月報',
                        'callback_data': f'cmd:ppt:monthly {arg}'}])
        else:
            kb = [kb_months] if kb_months else None
        send_message(chat_id, msg, reply_to, kb)

    # ── 原有指令 ─────────────────────────────────────────────────

    elif cmd in ('menu', 'start', '選單'):
        send_message(chat_id,
                     "👋 *OpenClaw（小O）* — 電商智能助理\n\n"
                     "點下方按鈕，或直接用中文跟我說話 👇",
                     reply_to, main_menu_keyboard())

    elif cmd in ('help', '幫助', '說明'):
        reply = (
            "📖 *OpenClaw 完整功能指南*\n"
            "━━━━━━━━━━━━━━━━━━━━━━━━━━━\n\n"

            "💬 *自然語言對話（直接發訊息）*\n"
            "  「今天業績多少？」\n"
            "  「哪個商品最需要加碼？」\n"
            "  「上個月整體表現如何？」\n"
            "  「幫我看外部熱搜跟銷售的落差」\n"
            "  「怎麼用圖片比價？」  ← 也可問功能說明\n\n"

            "📊 *業績查詢 ▸ 點「業績查詢」按鈕*\n"
            "  今日/昨日業績 → 業績摘要 + TOP3 商品（可點連結）\n"
            "  每週/每月/每季/半年 → 趨勢走勢\n"
            "  同期比較 → vs 上週同日\n"
            "  分類業績 → 各品類佔比\n"
            "  日期/區間 → 單日或起迄區間  格式: `2026/04/01-2026/04/15`\n"
            "  月份覽 → 列出所有可查月份，點月份查詳情\n\n"

            "🏆 *商品廠商 ▸ 點「商品廠商」按鈕*\n"
            "  熱銷商品 TOP10 → 含商品ID + 可點 PChome 連結\n"
            "  熱銷廠商 TOP10\n"
            "  商品健康分析 → 偏低/異常/策略分佈\n"
            "  補貨預測 → 動銷率 × 庫存天數估算\n"
            "  分類鑽取 → 進分類後再看商品排行\n\n"

            "🎯 *目標管理 ▸ 點「目標管理」按鈕*\n"
            "  查看達成率 → 日/週（自動推算）/月/季/半年/年\n"
            "  設定目標 → 點按鈕輸入金額\n"
            "  月倒計時 → 顯示每日需達金額\n\n"

            "📈 *智能分析 ▸ 點「智能分析」按鈕*\n"
            "  策略矩陣 → 加碼/機會/收割/觀察/持穩\n"
            "  業績趨勢 → 7日/30日/季/半年/年，含趨勢圖\n"
            "  商品健康 → 異常商品 + 策略建議\n"
            "  促銷追蹤 → 輸入活動區間，計算業績增幅\n"
            "  📊 趨勢圖表 → 直接生成折線圖 + 熱銷橫條圖\n"
            "  同期比較 → vs 上週/上月同日\n\n"

            "📄 *簡報報表 ▸ 點「簡報報表」按鈕*\n"
            "  日報/週報/月報 → 自動生成 .pptx\n"
            "  策略簡報(日/週/月/季/半年/年)\n"
            "  促銷效益簡報 → 輸入活動區間\n"
            "  下載 Excel 報表\n"
            "  指定日期日報 / 指定月份月報\n\n"

            "🌐 *市場情報 ▸ 點「市場情報」按鈕*\n"
            "  電商新聞 → 即時台灣電商資訊\n"
            "  台北天氣 → 含行銷建議\n"
            "  關鍵字比價 → 輸入商品名，搜尋 PChome 比價\n"
            "  📷 圖片比價 → 直接傳商品照片，自動辨識比價\n\n"

            "🔍 *競品日報 ▸ 點「競品日報」按鈕*\n"
            "  今日/昨日/本週/本月競品比價簡報\n\n"

            "⏰ *自動推播（每日無需操作）*\n"
            "  08:00  競品比價日報\n"
            "  08:30  每日早報（昨日業績 + TOP15 熱銷）\n"
            "  21:00  每日晚報（今日業績 + TOP15 + 明日建議）\n"
            "  週一 09:00  週報\n"
            "  09/12/15/18 時  異常偵測（偏差>30% 即告警）\n\n"

            "💡 *日期格式*：`2026/04/10` 或 `2026-04-10` 皆可\n"
            "💡 *有疑問*：直接用中文問我，我會回答！"
        )
        help_kb = [
            [{'text': '📊 業績查詢', 'callback_data': 'menu:sales'},
             {'text': '🏆 商品廠商', 'callback_data': 'menu:products'}],
            [{'text': '📈 智能分析', 'callback_data': 'menu:analysis'},
             {'text': '📄 簡報報表', 'callback_data': 'menu:reports'}],
            [{'text': '🔙 返回主選單', 'callback_data': 'menu:main'}],
        ]
        send_message(chat_id, reply, reply_to, help_kb)

    elif cmd == 'ack':
        # 告警確認（來自 anomaly alert 的 inline button）
        action_map = {'anomaly': '已知悉', 'tracking': '追蹤中'}
        label = action_map.get(arg or '', '確認')
        send_message(chat_id, f"✅ *{label}* — 感謝回覆，繼續監控中。", reply_to)
        # ── 回饋學習：告警 ack → quality_score +0.1 ────────
        if _LEARNING_ENABLED:
            import threading as _thr
            feedback_type = 'acknowledged' if arg == 'anomaly' else 'tracking'
            _thr.Thread(
                target=update_feedback,
                args=(feedback_type, msg.get('from', {}).get('id', 0)),
                daemon=True
            ).start()

    elif cmd == 'learn':
        # ── 學習系統狀態查詢 ──────────────────────────────────
        if _LEARNING_ENABLED:
            st = get_learning_stats()
            msg_text = (
                "🧠 *OpenClaw 自主學習狀態*\n\n"
                f"📚 知識庫：`{st.get('total_knowledge',0):,}` 條\n"
                f"💬 對話記憶：`{st.get('total_conversations',0):,}` 筆\n"
                f"📊 市場洞察：`{st.get('total_insights',0):,}` 份\n"
                f"🛒 商品知識：`{st.get('total_products',0):,}` 個\n"
                f"👍 用戶回饋：`{st.get('total_feedback',0):,}` 次\n"
                f"⭐ 平均品質分：`{st.get('avg_quality',0):.2f}`\n"
                f"📈 本週新增：`{st.get('knowledge_last7d',0)}` 條\n\n"
                "_每次 AI 回答、PPT 分析、告警確認都會自動加入學習庫_"
            )
        else:
            msg_text = "⚠️ 學習系統暫未啟用"
        send_message(chat_id, msg_text, reply_to, parse_mode='Markdown')

    elif cmd == 'import_confirm':
        # ── Excel 匯入確認 ────────────────────────────────────
        pending = _excel_pending.pop(chat_id, None)
        if not pending:
            send_message(chat_id, "⚠️ 匯入已逾期或不存在，請重新上傳 Excel 檔案。", reply_to)
            return

        file_path = pending['file_path']
        filename  = pending['filename']

        def _do_import():
            try:
                send_message(chat_id,
                             f"⏳ *正在匯入 Excel 資料...*\n`{filename}`\n\n請稍候，資料量大時需要一點時間。",
                             None, parse_mode='Markdown')

                from services.import_service import ImportService, Session
                from database.import_models import ImportJob
                import pytz as _pytz
                _TAIPEI = _pytz.timezone('Asia/Taipei')
                from datetime import datetime as _dt
                svc = ImportService()

                # 建立匯入任務
                session = Session()
                job = ImportJob(
                    job_type='telegram_upload',
                    status='pending',
                    drive_file_name=filename,
                    local_file_path=file_path,
                    created_at=_dt.now(_TAIPEI).replace(tzinfo=None)
                )
                session.add(job)
                session.commit()
                job_id = job.id
                session.close()

                # 執行匯入
                success = svc.process_daily_sales_import(job_id, file_path)
                status  = svc.get_job_status(job_id)

                # 清理暫存檔
                try:
                    import os as _os
                    _os.unlink(file_path)
                except Exception:
                    pass

                if success and status.get('status') == 'completed':
                    sr   = status.get('success_rows', 0) or 0
                    tr   = status.get('total_rows', 0)   or 0
                    summ = status.get('import_summary') or {}
                    if isinstance(summ, str):
                        import json as _json
                        try: summ = _json.loads(summ)
                        except Exception: summ = {}

                    date_min = summ.get('date_min', '')
                    date_max = summ.get('date_max', '')
                    synced   = summ.get('synced_to', '')
                    date_range_str = (
                        f"`{date_min}` ~ `{date_max}`"
                        if date_min and date_max and date_min != date_max
                        else f"`{date_min}`" if date_min else '—'
                    )

                    result_msg = (
                        f"✅ *Excel 匯入成功！*\n"
                        f"{'─' * 26}\n\n"
                        f"📄 *檔案*：`{filename}`\n"
                        f"📦 *匯入筆數*：`{sr:,}` / `{tr:,}` 筆\n"
                        f"📅 *涵蓋日期*：{date_range_str}\n"
                    )
                    if synced:
                        result_msg += f"🔄 *同步至*：`{synced}`\n"
                    result_msg += f"\n_✨ 業績資料已更新，可立即查詢！_"

                    # 取涵蓋日期的 latest date 顯示快速按鈕
                    quick_date = date_max.replace('-', '/') if date_max else (latest_date() or '')
                    import_kb = [
                        [{'text': f'📊 查看 {quick_date} 業績', 'callback_data': f'cmd:sales:{quick_date}'},
                         {'text': '🏆 熱銷商品排行',            'callback_data': f'cmd:top:{quick_date}'}],
                        [{'text': '📄 產出日報 PPT',           'callback_data': f'cmd:ppt:daily {quick_date}'},
                         {'text': '📅 月份業績覽',             'callback_data': 'cmd:history'}],
                    ] if quick_date else None
                    send_message(chat_id, result_msg, None, import_kb)
                else:
                    err = status.get('error_message', '') or '未知錯誤'
                    send_message(chat_id,
                                 f"❌ *匯入失敗*\n\n`{filename}`\n\n錯誤訊息：\n`{err[:200]}`",
                                 None, parse_mode='Markdown')
            except Exception as _ie:
                sys_log.error(f"[ExcelImport] import_confirm error: {_ie}", exc_info=True)
                send_message(chat_id, f"❌ 匯入過程發生例外錯誤：`{str(_ie)[:150]}`", None)

        threading.Thread(target=_do_import, daemon=True).start()

    elif cmd == 'import_cancel':
        # ── Excel 匯入取消 ────────────────────────────────────
        pending = _excel_pending.pop(chat_id, None)
        if pending:
            try:
                import os as _os
                _os.unlink(pending['file_path'])
            except Exception:
                pass
        send_message(chat_id, "✖ 已取消匯入，暫存檔案已刪除。", reply_to)

    elif cmd == 'photo_search_help':
        photo_help = (
            "📷 *圖片比價功能說明*\n\n"
            "直接在此群組傳送商品圖片，我會自動：\n"
            "  1️⃣ 辨識圖片中的商品名稱\n"
            "  2️⃣ 在 PChome 搜尋同款商品\n"
            "  3️⃣ 回傳比價結果與競品連結\n\n"
            "*使用方式*\n"
            "  📤 直接拍照或截圖傳送\n"
            "  🏷 或輸入「搜尋 商品名稱」（文字比價）\n\n"
            "*支援商品類型*\n"
            "  ✅ 美妝保養品  ✅ 保健食品\n"
            "  ✅ 母嬰用品    ✅ 個人清潔\n"
            "  ✅ 食品飲料    ✅ 家電用品\n\n"
            "_💡 圖片越清晰、商品標籤越完整，辨識準確度越高_"
        )
        send_message(chat_id, photo_help, reply_to, _submenu_market())

    else:
        # 不認識的指令 → 自然語言
        txt, kb = openclaw_answer(cmd + (' ' + arg if arg else ''))
        send_message(chat_id, txt, reply_to, kb)


# ── Webhook ───────────────────────────────────────────────────
@openclaw_bot_bp.route('/bot/telegram/webhook', methods=['POST'])
def telegram_webhook():
    try:
        update = request.get_json(silent=True)
        if not update:
            return jsonify({'ok': True})

        # ── Telegram retry 去重 ───────────────────────────────
        uid = update.get('update_id')
        if uid is not None:
            if uid in _seen_update_ids:
                sys_log.debug(f"[OpenClawBot] duplicate update_id={uid}, skip")
                return jsonify({'ok': True})
            _seen_update_ids.add(uid)
            if len(_seen_update_ids) > _SEEN_MAX:
                # 清掉最舊的 100 筆（set 無序，直接 pop 100 個）
                for _ in range(100):
                    _seen_update_ids.pop()

        # ── Callback Query（按鈕）─────────────────────────────
        if 'callback_query' in update:
            cq      = update['callback_query']
            cq_id   = cq['id']
            data    = cq.get('data', '')
            chat_id = cq['message']['chat']['id']
            chat_type = cq['message']['chat'].get('type', '')
            cq_from_id = (cq.get('from') or {}).get('id')
            sys_log.info(f'[OpenClawBot] CB: chat={chat_id} type={chat_type} data={data} allowed={ALLOWED_GROUP}')

            # fail-closed：未授權一律安靜拒絕（關閉 loading，不回任何訊息避免偵察）
            if not _is_authorized(chat_type, chat_id, cq_from_id):
                sys_log.warning(
                    f'[OpenClawBot] CB rejected: chat={chat_id} type={chat_type} user={cq_from_id}'
                )
                answer_callback(cq_id)
                return jsonify({'ok': False, 'error': 'forbidden'}), 403

            answer_callback(cq_id)
            send_typing(chat_id)

            if data.startswith('menu:'):
                # 顯示子選單或返回主選單
                key = data[5:]
                fn  = _SUBMENUS.get(key)
                if fn:
                    kb = fn()
                    titles = {
                        'main':     '👋 *OpenClaw* — 請選擇功能類別',
                        'sales':    '📊 *業績查詢* — 選擇日期或直接輸入',
                        'products': '🏆 *商品廠商* — 選擇查詢範圍',
                        'goals':    '🎯 *目標管理* — 查看或設定業績目標',
                        'analysis': '📈 *智能分析* — 選擇分析類型',
                        'trend':           '📈 *業績趨勢* — 選擇時間範圍',
                        'reports':         '📄 *簡報報表* — 選擇報告類型',
                        'market':          '🌐 *市場情報* — 即時資訊',
                        'competitor':      '📊 *競品比價日報* — 選擇分析日期',
                        'competitor_ppt':  '📄 *競品比價簡報* — 選擇時間範圍',
                        'category':        '🗂 *分類業績鑽取* — 點選分類深入分析',
                    }
                    send_message(chat_id, titles.get(key, '請選擇'), None, kb)

            elif data.startswith('await:'):
                # 進入輸入等待狀態
                action = data[6:]
                if action in _AWAIT_PROMPTS:
                    prompt_text, label = _AWAIT_PROMPTS[action]
                    _input_pending[chat_id] = {'action': action, 'label': label}
                    send_message(chat_id, f"{prompt_text}\n\n_輸入 `/取消` 可退出_", None,
                                 [[{'text': '✖ 取消', 'callback_data': 'menu:main'}]],
                                 parse_mode='Markdown')

            elif data.startswith('cmd:'):
                parts = data[4:].split(':', 1)
                handle_cmd(parts[0], parts[1] if len(parts) > 1 else '', chat_id, None)

            return jsonify({'ok': True})

        # ── Message ───────────────────────────────────────────
        msg = update.get('message') or update.get('edited_message')
        if not msg:
            return jsonify({'ok': True})

        chat      = msg.get('chat', {})
        chat_id   = chat.get('id')
        chat_type = chat.get('type', '')
        text_raw  = (msg.get('text') or '').strip()
        msg_id    = msg.get('message_id')

        # fail-closed 統一授權檢查（覆蓋 group/supergroup/private/channel/unknown）
        _uid = (msg.get('from') or {}).get('id')
        if not _is_authorized(chat_type, chat_id, _uid):
            sys_log.warning(
                f'[OpenClawBot] MSG rejected: chat={chat_id} type={chat_type} user={_uid}'
            )
            # 靜默拒絕：不回 Telegram 訊息（避免陌生人偵察 bot 存在與白名單機制）
            return jsonify({'ok': False, 'error': 'forbidden'}), 403

        if chat_type in ('group', 'supergroup'):
            # 移除 @mention（不強制要求，但如有則移除）
            question = text_raw.replace(BOT_USERNAME, '').strip()
        else:
            # 已通過授權的 private chat
            question = text_raw

        # ── 圖片訊息：Gemini Vision 商品辨識 ─────────────────────
        if not question and msg.get('photo'):
            send_typing(chat_id)
            try:
                photos = msg['photo']
                file_id = photos[-1]['file_id']  # 取最大尺寸
                # 取得 file path
                r_file = requests.get(
                    f"{BOT_API_URL}/getFile", params={'file_id': file_id}, timeout=10
                ).json()
                file_path_tg = r_file.get('result', {}).get('file_path', '')
                if not file_path_tg:
                    send_message(chat_id, "⚠️ 無法取得圖片", msg_id)
                    return jsonify({'ok': True})
                # 下載圖片
                img_url  = f"https://api.telegram.org/file/bot{BOT_TOKEN}/{file_path_tg}"
                img_data = requests.get(img_url, timeout=15).content
                import base64 as _b64
                img_b64  = _b64.b64encode(img_data).decode()
                # Gemini Vision 辨識商品名稱
                vision_payload = {
                    'contents': [{
                        'parts': [
                            {'text': (
                                '這是一張商品圖片。請辨識商品名稱（品牌、型號、規格），'
                                '輸出格式：只回商品名稱，不超過 30 字，繁體中文。'
                                '如果是多個商品，只取最顯眼的一個。'
                            )},
                            {'inline_data': {'mime_type': 'image/jpeg', 'data': img_b64}}
                        ]
                    }]
                }
                vis_r = requests.post(
                    f"{GEMINI_BASE_URL}/gemini-1.5-flash:generateContent?key={GEMINI_API_KEY}",
                    json=vision_payload, timeout=20
                )
                if vis_r.ok:
                    product_name = (
                        vis_r.json()
                        .get('candidates', [{}])[0]
                        .get('content', {})
                        .get('parts', [{}])[0]
                        .get('text', '').strip()
                    )
                    if product_name:
                        send_message(chat_id,
                                     f"🔍 辨識到商品：*{product_name}*\n正在搜尋 momo 比價...",
                                     msg_id, parse_mode='Markdown')
                        # 直接執行比價
                        handle_cmd('competitor', product_name, chat_id, msg_id)
                    else:
                        send_message(chat_id, "⚠️ 無法辨識圖片中的商品，請嘗試更清晰的圖片", msg_id)
                else:
                    send_message(chat_id, "⚠️ 圖片辨識失敗，請直接輸入商品名稱搜尋", msg_id,
                                 [[{'text': '🔍 文字搜尋', 'callback_data': 'await:search_compare'}]])
            except Exception as _img_e:
                sys_log.error(f"[VisionSearch] {_img_e}")
                send_message(chat_id, "⚠️ 圖片處理失敗，請直接輸入商品名稱搜尋", msg_id)
            return jsonify({'ok': True})

        # ── Excel 文件匯入（document）────────────────────────────
        if not question and msg.get('document'):
            doc   = msg['document']
            fname = doc.get('file_name', '')
            if fname.lower().endswith(('.xlsx', '.xls')):
                send_typing(chat_id)
                threading.Thread(
                    target=_handle_excel_import,
                    args=(doc, chat_id, msg_id),
                    daemon=True
                ).start()
            else:
                send_message(chat_id,
                             f"⚠️ 僅支援 `.xlsx` / `.xls` 格式的業績 Excel 檔案\n"
                             f"收到的檔案：`{fname}`",
                             msg_id, parse_mode='Markdown')
            return jsonify({'ok': True})

        if not question:
            return jsonify({'ok': True})

        # ── 頻率限制 ─────────────────────────────────────────────
        _uid_rl = msg.get('from', {}).get('id', 0)
        if not _check_rate_limit(_uid_rl):
            send_message(chat_id, "⚠️ 操作太頻繁，請稍後再試（每分鐘上限 30 次）。", msg_id)
            return jsonify({'ok': True})

        sys_log.info(f"[OpenClawBot] ← chat={chat_id} msg={question[:60]}")
        send_typing(chat_id)

        # ── 輸入等待狀態處理 ─────────────────────────────────────
        if chat_id in _input_pending and question not in ('/取消', '/cancel'):
            pending = _input_pending.pop(chat_id)
            action  = pending['action']
            label   = pending.get('label', '')
            val     = question.strip().replace(',', '').replace('NT$', '').replace('$', '').strip()

            if action.startswith('goal_'):
                period_map = {
                    'goal_daily':     'daily',
                    'goal_monthly':   'monthly',
                    'goal_quarterly': 'quarterly',
                    'goal_half':      'half',
                    'goal_yearly':    'yearly',
                }
                period = period_map[action]
                try:
                    amount = float(val)
                    _GOALS[period] = amount
                    send_message(chat_id,
                                 f"✅ *{label}* 已設定為 `NT$ {amount:,.0f}`",
                                 msg_id, _submenu_goals(), parse_mode='Markdown')
                except ValueError:
                    send_message(chat_id,
                                 f"⚠️ 格式錯誤，請輸入數字（例如：`150000`）",
                                 msg_id, [[{'text': f'重新設定 {label}',
                                            'callback_data': f'await:{action}'}],
                                          _BACK], parse_mode='Markdown')
                sys_log.info(f"[OpenClawBot] → replied chat={chat_id}")
                return jsonify({'ok': True})

            elif action == 'search_compare':
                # PChome 比價關鍵字輸入
                handle_cmd('competitor', val, chat_id, msg_id)

            elif action == 'date_range_sales':
                # 支援：單日 / 日期區間 / 月份
                dates = re.findall(r'\d{4}[/\-]\d{1,2}[/\-]\d{1,2}', val)
                month_only = re.match(r'(\d{4})[/\-](\d{1,2})$', val.strip())
                if len(dates) >= 2:
                    s = normalize_date(dates[0])
                    e = normalize_date(dates[1])
                    if s == e:
                        # 起迄同日 → 單日業績
                        handle_cmd('sales', s, chat_id, msg_id)
                    else:
                        # 日期區間 → 趨勢
                        from datetime import datetime as _dt2
                        s_d = _dt2.strptime(s.replace('/', '-'), '%Y-%m-%d').date()
                        e_d = _dt2.strptime(e.replace('/', '-'), '%Y-%m-%d').date()
                        days_c = (e_d - s_d).days + 1
                        send_message(chat_id,
                                     f"⏳ 查詢 {s} ~ {e}（{days_c}天）...",
                                     msg_id, parse_mode=None)
                        data_r = query_trend_range(s, e)
                        if data_r:
                            period_label = f'{s} ~ {e}（{days_c}天）'
                            send_message(chat_id, fmt_trend(data_r, period_label),
                                         msg_id, _submenu_sales())
                        else:
                            send_message(chat_id,
                                         f"⚠️ `{s}` ~ `{e}` 查無業績資料",
                                         msg_id, [[{'text': '重新輸入', 'callback_data': 'await:date_range_sales'}]],
                                         parse_mode='Markdown')
                elif len(dates) == 1:
                    # 單一日期
                    handle_cmd('sales', normalize_date(dates[0]), chat_id, msg_id)
                elif month_only:
                    # 月份格式：2026/04
                    handle_cmd('history', f"{month_only.group(1)}/{int(month_only.group(2)):02d}",
                               chat_id, msg_id)
                else:
                    send_message(chat_id,
                                 "⚠️ 格式錯誤\n📌 單日：`2026/04/15`\n📌 區間：`2026/04/01-2026/04/15`\n📌 月份：`2026/04`",
                                 msg_id,
                                 [[{'text': '重新輸入', 'callback_data': 'await:date_range_sales'}], _BACK],
                                 parse_mode='Markdown')
                sys_log.info(f"[OpenClawBot] → replied chat={chat_id}")
                return jsonify({'ok': True})

            elif action.startswith('date_trend_'):
                # 趨勢區間查詢（月份/年份/季度）
                trend_val = val.replace('-', '/')
                handle_cmd('trend', trend_val, chat_id, msg_id)

            elif action.startswith('date_'):
                # 驗證日期格式
                date_val = val.replace('-', '/')
                if re.match(r'\d{4}/\d{1,2}(/\d{1,2})?$', date_val):
                    if action == 'date_sales':
                        handle_cmd('sales', date_val, chat_id, msg_id)
                    elif action == 'date_top':
                        handle_cmd('top', date_val, chat_id, msg_id)
                    elif action == 'date_analysis':
                        # 分析日期：同時出矩陣 + 健康
                        handle_cmd('strategy', date_val, chat_id, msg_id)
                    elif action == 'date_ppt_daily':
                        handle_cmd('ppt', f'daily {date_val}', chat_id, msg_id)
                    elif action == 'date_ppt_monthly':
                        handle_cmd('ppt', f'monthly {date_val}', chat_id, msg_id)
                    elif action == 'date_competitor':
                        handle_cmd('ppt', f'competitor {date_val}', chat_id, msg_id)
                else:
                    send_message(chat_id,
                                 f"⚠️ 日期格式錯誤，請重新輸入（例如：`2026/04/15`）",
                                 msg_id, [[{'text': '重新輸入', 'callback_data': f'await:{action}'}],
                                          _BACK], parse_mode='Markdown')

            elif action == 'promo_range':
                # 促銷範圍：格式 2026/04/01-2026/04/07
                m = re.findall(r'\d{4}[/\-]\d{1,2}[/\-]\d{1,2}', val)
                if len(m) >= 2:
                    handle_cmd('promo', f'{normalize_date(m[0])}-{normalize_date(m[1])}', chat_id, msg_id)
                else:
                    send_message(chat_id,
                                 "⚠️ 格式錯誤，例如：`2026/04/01-2026/04/07`",
                                 msg_id, [[{'text': '重新輸入', 'callback_data': 'await:promo_range'}]],
                                 parse_mode='Markdown')
                sys_log.info(f"[OpenClawBot] → replied chat={chat_id}")
                return jsonify({'ok': True})

        # 取消輸入等待
        if question in ('/取消', '/cancel') and chat_id in _input_pending:
            _input_pending.pop(chat_id, None)
            send_message(chat_id, '已取消', msg_id, main_menu_keyboard())
            return jsonify({'ok': True})

        # 解析指令（/xxx 或已知指令詞）
        q = question.lstrip('/')
        parts = q.split(None, 1)
        cmd = parts[0].lower() if parts else ''
        arg = parts[1] if len(parts) > 1 else ''

        KNOWN = {
            'sales', 'top', 'vendor', 'trend', 'report', 'news',
            'weather', 'menu', 'start', 'help',
            'compare', 'category', 'catdetail', 'goal', 'chart', 'health', 'strategy',
            'competitor', 'price', 'restock', 'promo', 'ppt',
            '業績', '熱銷', '廠商', '趨勢', '報表', '選單', '幫助',
            '同比', '分類', '分類細項', '目標', '圖表', '健康', '策略', '比價',
            '補貨', '促銷',
        }

        if question.startswith('/') or cmd in KNOWN:
            handle_cmd(cmd, arg, chat_id, msg_id)
        else:
            txt, kb = openclaw_answer(question)
            send_message(chat_id, txt, msg_id, kb)

        sys_log.info(f"[OpenClawBot] → replied chat={chat_id}")

    except Exception as e:
        sys_log.error(f"[OpenClawBot] webhook error: {e}", exc_info=True)

    return jsonify({'ok': True})


# ── 管理端點 ──────────────────────────────────────────────────
@openclaw_bot_bp.route('/bot/telegram/set_webhook', methods=['POST'])
def set_webhook():
    webhook_url = f"{MOMO_BASE_URL}/bot/telegram/webhook"
    result = _tg('setWebhook', {
        'url': webhook_url,
        'allowed_updates': ['message', 'callback_query'],
        'drop_pending_updates': True,
    })
    register_commands()
    sys_log.info(f"[OpenClawBot] setWebhook → {result}")
    return jsonify({'ok': result.get('ok'), 'webhook_url': webhook_url})


@openclaw_bot_bp.route('/bot/telegram/webhook_info')
def webhook_info():
    try:
        r = requests.get(f"{BOT_API_URL}/getWebhookInfo", timeout=10)
        return jsonify(r.json())
    except Exception as e:
        return jsonify({'ok': False, 'error': str(e)}), 500


@openclaw_bot_bp.record_once
def _on_register(_state):
    """Blueprint 被 app.register_blueprint 時自動啟動排程"""
    start_scheduler()
    sys_log.info("[OpenClawBot] Blueprint registered — scheduler boot triggered")
