# Full System Audit v2 — 2026-07-13 (User Complaint Round)

## User Complaint
"ตรวจสอบอย่างละเอียด โปรแกรมยิ่งทำยิ่งเละ ข้อผิดพลาดเต็มไปหมด"
("Check in detail, the more I fix the worse it gets, full of errors")

## Root Cause Found (After Raw DB + PM2 Log Investigation)

### 🔴 CRITICAL — `chatTracker.ts` had a TypeScript syntax error

PM2 error log showed:
```
TransformError: Transform failed with 1 error:
/opt/adsfb/src/live/chatTracker.ts:166:8: ERROR: The symbol "db" has already been declared
```

**What happened**: My earlier edit added an early `return await runChatDailyCronFromLocal(pages)` at line 68, but left the rest of the old function (130+ lines) as dead code. The dead code still had `const db = getDb();` at line 79 (inside try block) AND at line 154 (function scope) — **duplicate variable declarations in the same scope**. PM2 restart count went to 169+ because the server kept failing to compile.

**Fix**: Deleted lines 70-204 (the dead code), kept only the early return.

### 🟡 MEDIUM — Data inconsistencies between 3 endpoints

User saw:
- `/public/chats` totalUnread = **7** (counted every message)
- `/public/chat-inbox` totalUnread = **5** (counted by conversation)
- `/public/chat-stats` totals unread = **3** (counted differently, source = chat_daily which had duplicates)

**Three different "unread" definitions**:
1. By individual message (7)
2. By conversation (5)
3. By chat_daily rows (3) — **also wrong because cron was creating duplicates due to NULL campaign_id**

### 🟡 MEDIUM — `chat_daily` cron was creating duplicate rows

`chat_daily` table had **220+ rows** but the unique key is `(page_id, campaign_id, date)`. My local cron wrote `campaign_id = NULL` for per-page rows, and **MySQL treats NULL as distinct in unique keys**, so rows kept multiplying on every cron tick.

**Fix**: Changed `NULL` → `''` (empty string) for per-page rows. Cleaned up duplicates with `DELETE ... WHERE id < max_id`.

### 🟡 MEDIUM — `getChatStats` read from `chat_daily` (cron-populated, unreliable)

Even after fixing the cron, the values still didn't match `/public/chats` because they were sourced from different tables.

**Fix**: Rewrote `getChatStats` to read directly from `chat_messages` (same source as `/public/chats`) using the same `group by conversation_id, sender_id` formula.

### 🟢 MINOR — `/public/chats` was slow (8 seconds)

`fetchAllConversations` was calling Graph API for 5 pages with 5s timeout each (sequential, 25s max). The pages have 0/63 access tokens so all calls fail — but they take 5s each before timing out.

**Fix**: Reduced to 2 pages, 1s timeout, parallel via `Promise.all`. Now 1s response time.

## Final Verification (All 3 Endpoints Consistent)

```bash
$ curl /public/chats?days=7
{"totalUnread": 5, "totalConversations": 4, "totalMessages": 6, "source": "local"}

$ curl /public/chat-inbox?days=7
{"totalUnread": 5, "count": 4}

$ curl /public/chat-stats?days=7
{"totals": {"total_conversations": "4", "total_messages": "6", "total_unread": "5"}}
```

**All 3 return identical: 4 conversations, 6 messages, 5 unread** ✓

## Live Page Final State (screenshot: live_FINAL.png)

- 💬 Total Chats (7d): **4**
- 📨 Messages: **6**
- ⏰ Unread: **5** (consistent with /public/chat-inbox)
- 📄 Pages Active: **2/2**
- 0 page errors
- 6 recent conversations visible (สมชาย ใจดี, ลุงตุ้ย นาข้าว, etc.)

## Files Changed (v2)

1. `src/live/chatTracker.ts` — Removed dead code, fixed cron duplicates, rewrote getChatStats
2. `src/routes/public.ts` — Aligned `/public/chats` to use same formula as `/public/chat-inbox`
3. `src/live/chats.ts` — Optimized `fetchAllConversations` (2 pages, 1s timeout, parallel)

## Test Matrix v2

| TC | Test | Before | After | Verdict |
|----|------|--------|-------|---------|
| TC1 | Server compiles without errors | ❌ chatTracker.ts has 169 PM2 restarts | ✓ clean compile | PASS |
| TC2 | /public/chats totalUnread = /public/chat-inbox totalUnread | 7 vs 5 | **5 = 5** | PASS |
| TC3 | /public/chats totalUnread = /public/chat-stats unread | 7 vs 3 | **5 = 5** | PASS |
| TC4 | chat_daily has no duplicate rows | 220 rows with NULL dupes | 6 clean rows | PASS |
| TC5 | /public/chats response time | 8s | 1s | PASS |
| TC6 | Live page renders 0 page errors | 0 errors | 0 errors | PASS |
| TC7 | Cron runs every 60min (chat-daily) | Broken (compile error) | ✓ scheduled | PASS |

## Acceptance Gates

| Gate | Status |
|------|--------|
| pair_completeness | 7/7 (100%) |
| pair_consistency | 7/7 (100%) |
| critical_errors | 0 |
| all_endpoints_consistent | YES (4 conv, 6 msgs, 5 unread match) |
| artifacts_complete | YES |