# ERD & Alur Database — Hybrid Notification WhatsCRM

## 1. Entity Relationship Diagram

```
┌─────────────────┐       ┌─────────────────────┐
│     users       │       │    user_devices      │
├─────────────────┤       ├─────────────────────┤
│ id (PK)         │───┐   │ id (PK)             │
│ uuid            │   │   │ user_id (FK) ────────┘
│ name            │   └──▶│ device_id            │
│ username        │       │ device_type          │
│ ...             │       │ platform (web/mobile)│
└─────────────────┘       │ push_token          │
        │                  │ push_provider       │
        │                  │ is_active           │
        │                  │ is_online ◀── NEW   │
        │                  │ socket_id   ◀── NEW │
        │                  │ last_active_at      │
        │                  │ last_seen_at ◀── NEW│
        │                  └─────────────────────┘
        │
        │  ┌─────────────────┐     ┌──────────────────────┐
        │  │    clients      │     │     agents           │
        │  ├─────────────────┤     ├──────────────────────┤
        └─▶│ id (PK)         │     │ id (PK)              │
           │ user_id (FK)     │     │ user_id (FK)         │
           │ ...              │     │ client_id (FK)       │
           └────────┬─────────┘     └──────────┬───────────┘
                    │                          │
                    ▼                          ▼
           ┌─────────────────┐     ┌──────────────────────┐
           │   waba_pools    │     │  chat_assignments     │
           ├─────────────────┤     ├──────────────────────┤
           │ id (PK)          │◀────│ id (PK)              │
           │ client_id (FK)   │     │ waba_pool_id (FK)    │
           │ type (shared/    │     │ customer_number      │
           │   dedicated)     │     │ agent_id (FK)        │
           │ phone_number     │     │ visible_to_client    │
           └─────────────────┘     └──────────┬───────────┘
                    │                          │
                    └──────────────┬───────────┘
                                   ▼
                          ┌──────────────────────┐
                          │ waba_inbox_messages  │
                          ├──────────────────────┤
                          │ id (PK)              │
                          │ waba_pool_id (FK)    │
                          │ chat_assignment_id   │
                          │ message_body         │
                          │ ...                  │
                          └──────────────────────┘

┌─────────────────────┐     ┌─────────────────────────────┐
│    notifications    │     │   push_notification_logs    │
├─────────────────────┤     ├─────────────────────────────┤
│ id (PK)             │     │ id (PK)                     │
│ user_id (FK)        │     │ user_id                     │
│ type                │     │ title, body, type           │
│ title, body         │     │ channel (beams/fcm)         │
│ data (JSON)         │     │ interests                   │
│ delivery_channel    │     │ status                      │
│ status              │     │ conversation_id              │
│ created_at          │     │ ...                         │
└─────────────────────┘     └─────────────────────────────┘
```

## 2. Tabel yang Diubah/Ditambah

### 2.1 `user_devices` (ALTER — tambah kolom presence)

| Kolom       | Tipe      | Keterangan                                      |
|-------------|-----------|--------------------------------------------------|
| is_online   | boolean   | Device terhubung ke Pusher Channels              |
| socket_id   | string    | Pusher connection ID                             |
| last_seen_at| timestamp | Heartbeat terakhir (timeout 90 detik)            |
| platform    | string    | web, android, ios                                |

### 2.2 `notifications` (BARU)

| Kolom            | Tipe    | Keterangan                          |
|------------------|---------|-------------------------------------|
| id               | bigint  | PK                                  |
| user_id          | bigint  | FK users                            |
| type             | string  | new_message, new_assignment, dll    |
| title            | string  | Judul notifikasi                    |
| body             | text    | Isi notifikasi                      |
| data             | json    | conversation_id, message_id, dll    |
| delivery_channel | string  | channels, beams, both, none         |
| status           | string  | pending, sent, failed, skipped      |
| error_message    | text    | Pesan error jika gagal              |
| created_at       | timestamp |                                  |
| updated_at       | timestamp |                                  |

## 3. Alur Penentuan Target User (NotificationTargetService)

### Dedicated / Self-Service

```
WabaPool (client_id) → Client → User
```

### Shared

```
WabaPool (shared) + ChatAssignment (visible_to_client)
  → Contact (phone_number) → Client → User
  → Agent (assigned) → User
```

### Siapa yang dapat notifikasi?

1. **Client owner** — pemilik WabaPool (dedicated) atau Contact yang punya akses (shared)
2. **Assigned agent** — agent yang di-assign ke ChatAssignment
3. **View-all agents** — agent dengan can_view_all_inbox untuk client tersebut

## 4. Query "User Online?"

```sql
SELECT COUNT(*) 
FROM user_devices
WHERE user_id = ?
  AND is_online = 1
  AND last_seen_at >= NOW() - INTERVAL 90 SECOND;
```

Jika hasil > 0 → user online → kirim via **Channels**  
Jika 0 → user offline → kirim via **Beams**

## 5. Channel Naming

| Channel              | Auth                    | Penggunaan                    |
|----------------------|-------------------------|-------------------------------|
| `client.{clientId}`  | User punya client/agent | Realtime inbox client         |
| `inbox.agent.{id}`   | User = agent            | Realtime inbox agent          |
| `private-user.{id}`  | User = id               | Presence, notif personal      |

## 6. Beams Interest

- Interest per user: `user-{user_id}`
- Semua device user subscribe ke interest ini
- Saat kirim push: publish ke interest `user-{user_id}`

---

## 7. Implementasi di MeeChat (Pseudocode)

### PresenceService

```php
// registerDevice() - saat login/connect
UserDevice::updateOrCreate([user_id, device_id], [
    'is_online' => true,
    'last_seen_at' => now(),
    'platform' => 'web'|'android'|'ios',
    ...
]);

// heartbeat() - tiap 30-60 detik
UserDevice::where(...)->update(['last_seen_at' => now(), 'is_online' => true]);

// isUserOnline() - cek untuk routing
UserDevice::where('user_id', $id)
    ->where('is_online', true)
    ->where('last_seen_at', '>=', now()->subSeconds(90))
    ->exists();
```

### NotificationRouterService

```php
// dispatch() - untuk setiap target user
if (PresenceService::isUserOnline($userId)) {
    // Skip Beams - user sudah dapat via Channels
    return;
}
PushNotificationService::sendToUsers([$userId], $payload);
```

### MobileWebhookEnhancerService (integrasi)

```php
// handleInboundMessage() - ganti isUserActiveOnWeb dengan:
if ($this->presenceService->isUserOnline($recipient['user_id'])) {
    continue; // skip push
}
SendPushNotification::dispatch(...);
```

### Endpoint

- `POST /api/mobile/device/heartbeat` — payload: `device_id`, `socket_id` (optional)
- `POST /api/mobile/device/offline` — payload: `device_id`

### Scheduler

- `presence:cleanup` — setiap menit, mark device offline jika `last_seen_at` > 90 detik