WITH TaskBoundaries AS (
    SELECT *,
           SUM(CASE WHEN role = 'user' THEN 1 ELSE 0 END)
               OVER (PARTITION BY session_id ORDER BY row_id) AS chain_id
    FROM openclaw_logs.openclaw_sessions
    WHERE role IS NOT NULL
),
TaskChains AS (
    SELECT
        CONCAT(session_id, '_', chain_id)  AS unique_chain_id,
        GROUP_CONCAT(... ORDER BY row_id SEPARATOR ' >>> ')  AS full_trace,
        COUNT(CASE WHEN tool_name IS NOT NULL THEN 1 END)    AS tool_usage_count,
        ...
    FROM TaskBoundaries
    GROUP BY session_id, chain_id
)
SELECT chain_id, session_id, tool_usage_count, LEFT(full_trace, 200) AS trace_preview
FROM TaskChains LIMIT 5;

WITH TaskBoundaries AS ( ... ),  -- 同 Step 1
TaskChains AS ( ... )
SELECT
    unique_chain_id,
    ai_classify('qwen_max_test', LEFT(full_trace, 600),
        '["死循环", "工具参数幻觉", "拒绝执行", "逻辑断裂", "成功解决"]'
    ) AS failure_label,
    ai_generate('qwen_max_test',
        CONCAT('你是OpenClaw AI诊断员。分析以下任务链...', LEFT(full_trace, 400))
    ) AS root_cause_notes
FROM TaskChains
WHERE tool_usage_count > 0 OR last_stop_reason IS NULL OR last_stop_reason != 'stop';

WITH TaskBoundaries AS ( ... ),
ChainTokens AS (
    SELECT CONCAT(session_id, '_', chain_id) AS unique_chain_id,
           SUM(IFNULL(total_tokens, 0))      AS chain_total_tokens
    FROM TaskBoundaries GROUP BY session_id, chain_id
)
SELECT a.failure_label, COUNT(*) AS task_count,
       ROUND(AVG(ct.chain_total_tokens)) AS avg_tokens,
       SUM(ct.chain_total_tokens)        AS total_tokens_burned
FROM openclaw_logs.t_ai_audit_results a
JOIN ChainTokens ct ON a.unique_chain_id = ct.unique_chain_id
GROUP BY a.failure_label
ORDER BY total_tokens_burned DESC;

WITH TaskBoundaries AS ( ... ),
ChainTokens AS ( ... ),
FirstUserMsg AS (
    SELECT CONCAT(session_id, '_', chain_id) AS unique_chain_id,
           SUBSTRING_INDEX(content_text, CONCAT('```', CHAR(10), CHAR(10)), -1) AS original_prompt,
           ROW_NUMBER() OVER (PARTITION BY session_id, chain_id ORDER BY row_id) AS rn
    FROM TaskBoundaries WHERE role = 'user'
),
FailedChains AS (
    SELECT unique_chain_id, failure_label, root_cause_notes,
           ROW_NUMBER() OVER (PARTITION BY unique_chain_id ORDER BY created_at DESC) AS rn
    FROM openclaw_logs.t_ai_audit_results
    WHERE failure_label != '成功解决'
)
SELECT fc.unique_chain_id, LEFT(fu.original_prompt, 200) AS original_prompt,
       fc.root_cause_notes AS root_cause,
       ai_generate('qwen_max_test',
           CONCAT('你是Prompt优化专家。...原始指令：', LEFT(fu.original_prompt, 500),
                  '失败根因：', fc.root_cause_notes)
       ) AS optimized_prompt
FROM FailedChains fc
JOIN ChainTokens ct ON ...
JOIN FirstUserMsg fu ON ... AND fu.rn = 1
WHERE fc.rn = 1
ORDER BY ct.chain_total_tokens DESC LIMIT 3;