# 🚀 DOING: Social Inbox — SQLite Migration + Sentiment Upgrade

## 📁 Folders & Files liên quan
```
backend/
├── api/
│   ├── social_inbox/
│   │   └── social_inbox_route.py      ← MODIFY (JSON file → SQLite)
│   └── data/
│       └── social_messages.json       ← ❌ DELETE (thay bằng SQLite)
├── common/
│   └── social/
│       └── sentiment_vi.py            ← MODIFY (thêm LLM fallback)
└── database/
    └── social_inbox.db                ← 🆕 NEW (social_messages table)
```

## 📌 Context
- **Status:** 🚧 In Progress

## 🎯 Goal
Migrate Social Inbox từ JSON file storage → SQLite, nâng cấp sentiment analysis thêm LLM fallback, và kết nối negative comments vào Auto-Improve Pipeline (#17).

## ⚠️ Active Trade-offs
- Storage: SQLite `database/social_inbox.db` (file mới)
- Sentiment: giữ keyword-matching làm primary (nhanh, free), LLM là fallback cho ambiguous cases
- TikTok webhook: thêm basic signature check (optional vì TikTok API thay đổi thường xuyên)

---

## 🔍 Hiện trạng

### ✅ Đã có:
- `api/social_inbox/social_inbox_route.py` — Facebook/Instagram/TikTok webhook receive + REST API
- `common/social/sentiment_vi.py` — Keyword-matching sentiment (150 keywords VI + EN)
- `common/social/inbox_webhook.py` — Parse webhook payloads

### ❌ Thiếu:
1. **JSON file storage** — `social_messages.json` max 500 messages, race condition nếu concurrent writes
2. **Keyword-only sentiment** — ambiguous text (sarcasm, mixed) → default "neutral" → miss insights
3. **TikTok no signature** — webhook endpoint không verify signature
4. **Not feeding to autotrain** — negative comments go to `feedback_tracker` nhưng NOT vào autotrain pipeline

---

## 🛠 Impact & Files Touched
| File | Symbol | Change | Blast Radius |
|------|--------|--------|--------------|
| `api/social_inbox/social_inbox_route.py` | storage helpers | MODIFY (SQLite replace JSON) | social_inbox |
| `common/social/sentiment_vi.py` | `analyze_sentiment` | MODIFY (add LLM fallback) | social_inbox |
| DELETE: `api/data/social_messages.json` | file | DELETE | social_inbox |

**Risk:** Low

## 📋 Execution Checklist

### Phase 1: SQLite Migration (~20m)
- [x] Task 1.1: Tạo `database/social_inbox.db` với bảng `social_messages`:
  ```sql
  CREATE TABLE social_messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    platform TEXT NOT NULL,           -- facebook/instagram/tiktok
    sender_id TEXT,
    sender_name TEXT,
    body TEXT NOT NULL,
    message_type TEXT DEFAULT 'comment', -- comment/dm/mention
    sentiment TEXT DEFAULT 'neutral',
    pos_score INTEGER DEFAULT 0,
    neg_score INTEGER DEFAULT 0,
    matched_keywords TEXT,             -- JSON array
    post_id TEXT,
    received_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fed_to_autotrain BOOLEAN DEFAULT 0
  )
  ```
- [x] Task 1.2: Replace `_load_messages()` / `_save_messages()` / `_append_messages()` → SQLite INSERT/SELECT
- [x] Task 1.3: Xóa `social_messages.json` file + `_ensure_dir()` logic
- [x] Task 1.4: Update `/messages` endpoint → query SQLite with pagination
- [x] Task 1.5: Update `/messages/stats` → SQL GROUP BY thay vì Python loop

### Phase 2: Sentiment LLM Fallback (~15m)
- [x] Task 2.1: `sentiment_vi.py` → modify `analyze_sentiment()`:
  - If keyword pos_score == neg_score (ambiguous) → call LLM to decide
  - LLM prompt: "Phân tích sentiment của comment sau: {text}. Trả về JSON: {sentiment, confidence}"
  - Cache LLM result to avoid duplicate calls
- [x] Task 2.2: Add `analyze_sentiment_llm(text)` async function dùng `create_llm()`
- [x] Task 2.3: Giữ keyword-only làm default, LLM chỉ khi `pos_score == neg_score`

### Phase 3: Feed to Autotrain Pipeline (~10m)
- [x] Task 3.1: Trong `_feed_to_feedback_tracker()`, thêm logic:
  - Nếu sentiment == "negative" → cũng insert vào `autotrain.db` feedbacks table
  - Tag `source = "social_inbox"` để phân biệt với feedback trực tiếp
- [x] Task 3.2: Mark `fed_to_autotrain = 1` sau khi insert → tránh duplicate

### Phase 4: Testing (~10m)
- [x] Task 4.1: Test webhook receive → verify saved vào SQLite
- [x] Task 4.2: Test sentiment LLM fallback với ambiguous text
- [x] Task 4.3: Test negative comment → verify appears in autotrain feedbacks

## 🔄 Sub-Doings
_None yet_

## ✅ Completion Gate
- [x] All tasks [x] | [x] Sub-Doings resolved | [x] Smoke test passed | [x] Trade-offs confirmed

---
_Started: 2026-05-10 | Updated: 2026-05-10_
