n8n ETL + AI: ดึงรีวิว วิเคราะห์ด้วย LLM เก็บลง Database

-- =============================================
-- Product Reviews Table (LLM Enriched)
-- =============================================

CREATE TABLE product_reviews (
  -- Primary Key
  id UUID PRIMARY KEY,
  
  -- Original Data (from API)
  product_id INT NOT NULL,
  product_name VARCHAR(100),
  review_text TEXT,
  review_date DATE,
  
  -- LLM Enriched Fields
  sentiment VARCHAR(20) CHECK (sentiment IN ('positive', 'negative', 'neutral')),
  sentiment_score DECIMAL(3,2) CHECK (sentiment_score >= 0 AND sentiment_score <= 1),
  quality VARCHAR(20),
  usability VARCHAR(20),
  issues TEXT[],
  highlights TEXT[],
  summary TEXT,
  
  -- Metadata
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- =============================================
-- Indexes
-- =============================================

CREATE INDEX idx_reviews_product_id ON product_reviews(product_id);
CREATE INDEX idx_reviews_sentiment ON product_reviews(sentiment);
CREATE INDEX idx_reviews_date ON product_reviews(review_date);
CREATE INDEX idx_reviews_created ON product_reviews(created_at DESC);

-- =============================================
-- Auto-update updated_at
-- =============================================

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_timestamp
  BEFORE UPDATE ON product_reviews
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

[
  {
    "date": "2025-12-21",
    "reviews": [
      {
        "id": "521077e2-2e0d-482e-800b-1fd6422f4e65",
        "productId": 112,
        "productName": "VR Scope 2",
        "review": "ภาพสมจริงมากๆ เล่นเกมสนุกจนลืมเวลา ใส่สบายไม่ปวดหัว",
        "createdAt": "2025-12-21T13:49:28.971Z"
      },
      {
        "id": "589e8498-3144-4144-b149-2c3ad31a5275",
        "productId": 109,
        "productName": "KeyPro Keyboard",
        "review": "ปุ่มแข็งมาก พิมพ์นานๆ เมื่อยนิ้ว ไม่เหมาะกับคนทำงานพิมพ์เยอะ",
        "createdAt": "2025-12-21T13:49:28.553Z"
      }
    ]
  }
]

คุณเป็น AI วิเคราะห์รีวิวสินค้า ตอบเป็น JSON เท่านั้น ห้ามมีข้อความอื่น

วิเคราะห์รีวิวนี้:
สินค้า: {{ $json.productName }}
รีวิว: {{ $json.review }}

ตอบ JSON format:
{
  "sentiment": "positive|negative|neutral",
  "sentiment_score": 0.0-1.0,
  "aspects": {
    "quality": "positive|negative|neutral|null",
    "usability": "positive|negative|neutral|null"
  },
  "issues": [],
  "highlights": [],
  "summary": "สรุปสั้นๆ"
}

[
  {
    "output": [
      {
        "id": "msg_059ee9a8f662922e00694829e7b5e48197bb2c4fd36cb73449",
        "type": "message",
        "status": "completed",
        "content": [
          {
            "type": "output_text",
            "annotations": [],
            "logprobs": [],
            "text": {
              "sentiment": "positive",
              "sentiment_score": 0.95,
              "aspects": {
                "quality": "positive",
                "usability": "positive"
              },
              "issues": [],
              "highlights": [
                "ภาพสมจริงมากๆ",
                "เล่นเกมสนุกจนลืมเวลา",
                "ใส่สบายไม่ปวดหัว"
              ],
              "summary": "ภาพสมจริง เล่นเกมสนุก ใส่สบายไม่ปวดหัว"
            }
          }
        ],
        "role": "assistant"
      }
    ]
  },
  {
    "output": [
      {
        "id": "msg_0aacc86feee528a100694829e9d2008196aafb9ca977faa9aa",
        "type": "message",
        "status": "completed",
        "content": [
          {
            "type": "output_text",
            "annotations": [],
            "logprobs": [],
            "text": {
              "sentiment": "negative",
              "sentiment_score": 0.2,
              "aspects": {
                "quality": "negative",
                "usability": "negative"
              },
              "issues": [
                "ปุ่มแข็ง",
                "พิมพ์นานๆ เมื่อยนิ้ว",
                "ไม่เหมาะกับคนทำงานพิมพ์เยอะ"
              ],
              "highlights": [],
              "summary": "คีย์บอร์ดปุ่มแข็ง พิมพ์นานแล้วเมื่อยนิ้ว ไม่เหมาะสำหรับงานที่ต้องพิมพ์เยอะ"
            }
          }
        ],
        "role": "assistant"
      }
    ]
  }
]

SELECT 
  product_name,
  COUNT(*) as total_reviews,
  COUNT(*) FILTER (WHERE sentiment = 'positive') as positive,
  COUNT(*) FILTER (WHERE sentiment = 'negative') as negative,
  COUNT(*) FILTER (WHERE sentiment = 'neutral') as neutral,
  ROUND(AVG(sentiment_score)::numeric, 2) as avg_score
FROM product_reviews
GROUP BY product_name
ORDER BY total_reviews DESC;

SELECT 
  product_name,
  unnest(issues) as issue,
  COUNT(*) as count
FROM product_reviews
WHERE array_length(issues, 1) > 0
GROUP BY product_name, issue
ORDER BY count DESC
LIMIT 20;

SELECT 
  product_name,
  review_text,
  sentiment,
  sentiment_score,
  summary,
  created_at
FROM product_reviews
ORDER BY created_at DESC
LIMIT 10;

SELECT 
  product_name,
  COUNT(*) as total_reviews,
  ROUND(AVG(sentiment_score)::numeric, 2) as avg_score,
  COUNT(*) FILTER (WHERE sentiment = 'negative') as negative_count
FROM product_reviews
GROUP BY product_name
ORDER BY avg_score ASC
LIMIT 5;

SELECT 
  product_name,
  unnest(highlights) as highlight,
  COUNT(*) as count
FROM product_reviews
WHERE sentiment = 'positive'
  AND array_length(highlights, 1) > 0
GROUP BY product_name, highlight
ORDER BY count DESC
LIMIT 15;