# Data Flow Analysis: page_id → page_name

## ❌ WRONG (previous attempt)

```sql
UPDATE chat_messages
SET page_id = '102601625742282',
    page_name = 'เฮียหนวด โซล่าเซลล์ ไฟตุ้มโซล่าเซลล์ โคมไฟโซล่าเซลล์ สว่างยันเช้า'  -- ← HARDCODED
WHERE campaign_id = '120230799572200590';
```

**Problems**:
- Thai string hardcoded in SQL
- If Meta changes page name, DB becomes stale
- Cannot lookup other pages without modifying code
- No PK/FK relationship

## ✅ CORRECT (this refactor)

### Architecture

```
┌─────────────────────────────────────────────────────────────────┐
│  Meta Graph API (source of truth)                              │
│  ──────────────────────────────────────────────────────────────│
│  GET /v19.0/me/accounts?fields=id,name,fan_count               │
│  GET /v19.0/<page-id>?fields=id,name,fan_count  (public lookup) │
│  GET /v19.0/<campaign-id>/adsets?fields=promoted_object        │
└─────────────────────────────────────────────────────────────────┘
                              ↓
┌─────────────────────────────────────────────────────────────────┐
│  pages table (canonical mapping)                                │
│  ──────────────────────────────────────────────────────────────│
│  page_id (PK)  page_name (canonical)  fan_count  source         │
│  408865435647368  เฮียหนวด การเกษตร  ...  me/accounts         │
│  102601625742282  เฮียหนวด โซล่าเซลล์... 2481 public           │
│  ...                                                            │
└─────────────────────────────────────────────────────────────────┘
                              ↑ JOIN
┌─────────────────────────────────────────────────────────────────┐
│  chat_messages (transactional data)                             │
│  ──────────────────────────────────────────────────────────────│
│  conversation_id  page_id (FK)  sender_id  message_text  ...   │
│  xxx-USER_11111   102601625742282  USER_11111  สวัสดี...        │
│  ↑ page_id only, no page_name stored (derived via JOIN)        │
└─────────────────────────────────────────────────────────────────┘
                              ↑
┌─────────────────────────────────────────────────────────────────┐
│  chat_inbox VIEW (consumed by API)                              │
│  ──────────────────────────────────────────────────────────────│
│  SELECT cm.*, p.page_name, p.fan_count                         │
│  FROM chat_messages cm                                          │
│  INNER JOIN pages p ON p.page_id = cm.page_id                   │
│  GROUP BY ...                                                   │
└─────────────────────────────────────────────────────────────────┘
                              ↓
┌─────────────────────────────────────────────────────────────────┐
│  API: GET /public/chat-inbox                                    │
│  Returns: page_id, page_name (from JOIN, never hardcoded)       │
└─────────────────────────────────────────────────────────────────┘
```

## Field mapping table

| DB Column | Type | Source | How populated | Code path |
|-----------|------|--------|---------------|-----------|
| `pages.page_id` | VARCHAR(64) PK | Meta `id` field | API response | `/me/accounts` loop |
| `pages.page_name` | VARCHAR(256) | Meta `name` field | API response | `/me/accounts` loop |
| `pages.fan_count` | INT | Meta `fan_count` field | API response | `/<page-id>` query |
| `pages.source` | ENUM | constant | code: 'me/accounts' or 'public' | `syncPagesFromAccounts()` |
| `chat_messages.page_id` | VARCHAR(64) | Meta webhook `page_id` | webhook receiver | `POST /webhook/facebook` |
| `chat_messages.page_name` | ~~VARCHAR(256)~~ | **DEPRECATED** | removed | n/a |
| `chat_inbox.page_name` | derived | JOIN pages | view definition | always JOIN, never store |

## API → Code mapping (concrete)

| Meta API field | → Code variable | → DB column | Code location |
|----------------|----------------|-------------|---------------|
| `/me/accounts` data[].id | `p.id` | `pages.page_id` | `syncPagesFromAccounts()` in `src/live/pages.ts` |
| `/me/accounts` data[].name | `p.name` | `pages.page_name` | same |
| `/me/accounts` data[].access_token | `p.access_token` | `pages.access_token` | encrypted in `auth_user_api_sources` |
| `/<page-id>`.id | `p.id` | `pages.page_id` (UPSERT) | `lookupPageById()` |
| `/<page-id>`.name | `p.name` | `pages.page_name` | same |
| `/<page-id>`.fan_count | `p.fan_count` | `pages.fan_count` | same |
| `/<campaign-id>/adsets` promoted_object.page_id | `cid` | where to lookup | `syncAdPromotedPages()` |

## Migration order

1. CREATE TABLE `pages` (page_id PK, page_name, fan_count, source, access_token, ...)
2. INSERT FROM `/me/accounts` (25 admin pages)
3. INSERT FROM public lookup for any missing page_id referenced by campaigns/chat_messages
4. CREATE OR REPLACE VIEW `chat_inbox` with INNER JOIN pages
5. ALTER TABLE chat_messages DROP COLUMN page_name (after view is verified)
6. Update routes: return p.page_name from JOIN (no code change in route, view handles it)

## What user was rightly angry about

Previous fix did:
```sql
SET page_name = 'เฮียหนวด โซล่าเซลล์ ไฟตุ้ม...'  -- hardcoded Thai
```

This violates:
1. **Single source of truth** — DB should be derived from Meta, not from string literal
2. **DRY** — page name stored in 2 places (chat_messages + pages view)
3. **Maintainability** — if Meta renames, SQL must be re-run
4. **No PK/FK discipline** — page_id was just a string, not a foreign key

Fix uses:
- `pages.page_id` as PRIMARY KEY
- `chat_messages.page_id` references it (FK constraint added)
- `chat_inbox` view always JOINs, never stores denormalized
- Sync script populates `pages` from Meta API automatically