{
  "name": "PostgreSQL 慢查詢監控 (含自動修復)",
  "nodes": [
    {
      "parameters": { "rule": { "interval": [{ "field": "minutes", "minutesInterval": 15 }] } },
      "id": "schedule", "name": "每 15 分鐘", "type": "n8n-nodes-base.scheduleTrigger", "typeVersion": 1.1, "position": [0, 0]
    },
    {
      "parameters": {
        "url": "http://192.168.0.110:5001/api/system/db/slow_queries",
        "options": { "timeout": 30000 }
      },
      "id": "http-slow", "name": "取得慢查詢", "type": "n8n-nodes-base.httpRequest", "typeVersion": 4.1, "position": [220, 0],
      "onError": "continueRegularOutput"
    },
    {
      "parameters": {
        "jsCode": "const response = $input.first().json;\nconst queries = response.slow_queries || response.data || [];\nconst threshold = 5; // 秒\n\n// 過濾超過閾值的查詢\nconst slowQueries = queries.filter(q => (q.duration || q.total_time || 0) > threshold * 1000);\n\nlet needsOptimize = false;\nlet optimizeReason = '';\n\n// 檢查是否需要優化\nif (slowQueries.length >= 3) {\n  needsOptimize = true;\n  optimizeReason = `發現 ${slowQueries.length} 個慢查詢`;\n}\n\n// 檢查是否有重複的慢查詢（可能需要索引）\nconst queryPatterns = {};\nslowQueries.forEach(q => {\n  const pattern = (q.query || '').substring(0, 50);\n  queryPatterns[pattern] = (queryPatterns[pattern] || 0) + 1;\n});\n\nconst repeatedSlowQueries = Object.entries(queryPatterns).filter(([k, v]) => v >= 2);\nif (repeatedSlowQueries.length > 0) {\n  needsOptimize = true;\n  optimizeReason += `, 重複慢查詢: ${repeatedSlowQueries.length} 種`;\n}\n\nreturn [{ json: { \n  hasSlowQueries: slowQueries.length > 0,\n  slowQueryCount: slowQueries.length,\n  needsOptimize,\n  optimizeReason,\n  topSlowQueries: slowQueries.slice(0, 5).map(q => ({\n    query: (q.query || '').substring(0, 100),\n    duration: ((q.duration || q.total_time || 0) / 1000).toFixed(2) + 's',\n    calls: q.calls || 1\n  }))\n} }];"
      },
      "id": "analyze", "name": "分析慢查詢", "type": "n8n-nodes-base.code", "typeVersion": 2, "position": [440, 0]
    },
    {
      "parameters": {
        "conditions": {
          "options": { "caseSensitive": true },
          "conditions": [
            { "id": "c1", "leftValue": "={{ $json.hasSlowQueries }}", "rightValue": true, "operator": { "type": "boolean", "operation": "equals" } }
          ],
          "combinator": "and"
        }
      },
      "id": "if-slow", "name": "有慢查詢?", "type": "n8n-nodes-base.if", "typeVersion": 2, "position": [660, 0]
    },
    {
      "parameters": {
        "jsCode": "const data = $input.first().json;\nconst time = new Date().toLocaleString('zh-TW', {timeZone: 'Asia/Taipei'});\n\nlet queryList = '';\nif (data.topSlowQueries && data.topSlowQueries.length > 0) {\n  queryList = data.topSlowQueries.map((q, i) => \n    `${i+1}. ${q.duration} - ${q.query.substring(0, 60)}...`\n  ).join('\\n');\n}\n\nconst text = '🐢 *PostgreSQL 慢查詢告警*\\n\\n' +\n  '⏰ 時間: ' + time + '\\n' +\n  '📊 慢查詢數: ' + data.slowQueryCount + '\\n\\n' +\n  '🔍 *Top 5 慢查詢:*\\n```\\n' + queryList + '\\n```\\n\\n' +\n  (data.needsOptimize ? '🔧 *建議執行優化*: ' + data.optimizeReason : '');\n\nreturn [{ json: { \n  telegramBody: { chat_id: '5619078117', text: text, parse_mode: 'Markdown' },\n  needsOptimize: data.needsOptimize\n} }];"
      },
      "id": "code-alert", "name": "組合告警", "type": "n8n-nodes-base.code", "typeVersion": 2, "position": [880, 100]
    },
    {
      "parameters": {
        "method": "POST", "url": "https://api.telegram.org/bot8075645931:AAH-EGKMo8ZC4QJs-Nc1_0s92xHrGdQvdpg/sendMessage",
        "sendBody": true, "specifyBody": "json",
        "jsonBody": "={{ JSON.stringify($json.telegramBody) }}"
      },
      "id": "telegram", "name": "發送告警", "type": "n8n-nodes-base.httpRequest", "typeVersion": 4.1, "position": [1100, 100]
    },
    {
      "parameters": {
        "conditions": {
          "options": { "caseSensitive": true },
          "conditions": [
            { "id": "c1", "leftValue": "={{ $json.needsOptimize }}", "rightValue": true, "operator": { "type": "boolean", "operation": "equals" } }
          ],
          "combinator": "and"
        }
      },
      "id": "if-optimize", "name": "需要優化?", "type": "n8n-nodes-base.if", "typeVersion": 2, "position": [1320, 100]
    },
    {
      "parameters": {
        "method": "POST",
        "url": "http://192.168.0.110:5001/api/system/db/optimize",
        "sendBody": true,
        "specifyBody": "json",
        "jsonBody": "{\"action\": \"vacuum_analyze\", \"auto_triggered\": true}",
        "options": { "timeout": 120000 }
      },
      "id": "auto-optimize", "name": "自動優化", "type": "n8n-nodes-base.httpRequest", "typeVersion": 4.1, "position": [1540, 200]
    },
    {
      "parameters": {
        "jsCode": "const result = $input.first().json;\nconst success = result.success || result.status === 'ok';\nconst text = success \n  ? '✅ *資料庫自動優化完成*\\n\\n執行了 VACUUM ANALYZE，效能應該有所改善。'\n  : '❌ *資料庫自動優化失敗*\\n\\n' + (result.error || result.message || '未知錯誤');\n\nreturn [{ json: { telegramBody: { chat_id: '5619078117', text: text, parse_mode: 'Markdown' } } }];"
      },
      "id": "code-result", "name": "組合結果", "type": "n8n-nodes-base.code", "typeVersion": 2, "position": [1760, 200]
    },
    {
      "parameters": {
        "method": "POST", "url": "https://api.telegram.org/bot8075645931:AAH-EGKMo8ZC4QJs-Nc1_0s92xHrGdQvdpg/sendMessage",
        "sendBody": true, "specifyBody": "json",
        "jsonBody": "={{ JSON.stringify($json.telegramBody) }}"
      },
      "id": "telegram-result", "name": "通知結果", "type": "n8n-nodes-base.httpRequest", "typeVersion": 4.1, "position": [1980, 200]
    },
    { "parameters": {}, "id": "no-op", "name": "無慢查詢", "type": "n8n-nodes-base.noOp", "typeVersion": 1, "position": [880, -100] },
    { "parameters": {}, "id": "no-op2", "name": "不需優化", "type": "n8n-nodes-base.noOp", "typeVersion": 1, "position": [1540, 0] }
  ],
  "connections": {
    "每 15 分鐘": { "main": [[{ "node": "取得慢查詢", "type": "main", "index": 0 }]] },
    "取得慢查詢": { "main": [[{ "node": "分析慢查詢", "type": "main", "index": 0 }]] },
    "分析慢查詢": { "main": [[{ "node": "有慢查詢?", "type": "main", "index": 0 }]] },
    "有慢查詢?": { "main": [[{ "node": "組合告警", "type": "main", "index": 0 }], [{ "node": "無慢查詢", "type": "main", "index": 0 }]] },
    "組合告警": { "main": [[{ "node": "發送告警", "type": "main", "index": 0 }]] },
    "發送告警": { "main": [[{ "node": "需要優化?", "type": "main", "index": 0 }]] },
    "需要優化?": { "main": [[{ "node": "自動優化", "type": "main", "index": 0 }], [{ "node": "不需優化", "type": "main", "index": 0 }]] },
    "自動優化": { "main": [[{ "node": "組合結果", "type": "main", "index": 0 }]] },
    "組合結果": { "main": [[{ "node": "通知結果", "type": "main", "index": 0 }]] }
  },
  "settings": { "executionOrder": "v1" }
}
