# Sprint G012 — Couple Dashboard & Analytics Engine Walkthrough

## Summary

Sprint G012 delivers the complete **Couple Dashboard & Analytics Engine** for INVEETAIRE, transforming the Couple Dashboard into a decision-focused operational command center. Analytics are strictly guest-identity based (no generic website traffic metrics). The system tracks invitation link opens (`first_opened_at`, `last_opened_at`, `view_count`, `invitation_status`), computes real-time delivery rates, calculates RSVP response rates & expected attendance totals, aggregates guest book wish moderation counts, streams chronological guest activities, and renders an interactive, searchable Guest Progress Command Table.

---

## Core Components & Changes

### 1. Database Schema (`database/migrations/G012_analytics_engine.sql`, `schema.sql`)
- Added `view_count` (`INT UNSIGNED NOT NULL DEFAULT 0`) column to the `guests` table to store invitation URL open counts per guest.

### 2. Opening Tracker (`GuestModel.php`, `PublicInvitationService.php`)
- **`GuestModel::recordInvitationOpen`**:
  - Sets `first_opened_at` to `COALESCE(first_opened_at, CURRENT_TIMESTAMP)` (preserves initial timestamp).
  - Sets `last_opened_at` to `CURRENT_TIMESTAMP`.
  - Increments `view_count = view_count + 1`.
  - Updates `invitation_status` to `'opened'` if it was `'not_sent'` or `'sent'`.
- **`PublicInvitationService::resolveGuestIdentity`**:
  - Non-blocking call to `$guestModel->recordInvitationOpen($workspaceId, $guestId)` when a guest accesses their personalized invitation link (`/{slug}/i/{token}`).

### 3. Analytics Calculations & Metrics (`GuestModel.php`, `OperationModel.php`)
- **`GuestModel::getInvitationDeliveryStats`**:
  - `total`: Total active guests.
  - `opened`: Opened invitations count (`first_opened_at IS NOT NULL` OR `view_count > 0` OR `invitation_status = 'opened'`).
  - `not_opened`: Unopened invitations count (`total - opened`).
  - `opening_rate`: Opening Rate % = `(opened / total) * 100`.
- **`OperationModel::getAttendanceStats`**:
  - `rsvp_responded`: Responded count (Attending + Declined).
  - `rsvp_rate`: RSVP Rate % = `(responded / total) * 100`.
  - `rsvp_yes_pax`: Expected Attendance = `SUM(confirmed_pax)`.
  - `approved_wishes`, `pending_wishes`, `rejected_wishes` counts.

### 4. Guest Progress Command Table & Activity Feed (`CoupleDashboardController.php`, `GuestModel.php`, `dashboard.php`)
- **`GuestModel::getGuestProgressList`**:
  - Queries guest progress joining `rsvps` and `wishes`: returns guest name, group, opening status, RSVP response, confirmed pax, wish status, first opened date, last opened date, and view count.
  - Supports quick filters (`all`, `opened`, `not_opened`, `attending`, `declined`, `rsvp_pending`, `has_wish`) and guest name/phone search.
- **Recent Activities Stream**:
  - Merged stream of Invitation Opens, RSVP Submissions, Wish Submissions, Budget Payments, and Vendor Updates in reverse chronological order.
- **`dashboard.php` View**:
  - **Invitation Delivery Card**: Total, Opened, Not Opened, Opening Rate (%).
  - **RSVP Progress Card**: Pending, Attending, Declined, Expected Attendance Pax.
  - **Guest Book Card**: Approved, Pending Moderation, Rejected.
  - **Recent Activities Feed**: Real-time chronological activity list.
  - **Guest Progress Table**: Search input box, filter buttons, table display, and pagination.

---

## Verification Matrix

| Test Case | Status | Verification Method |
|---|---|---|
| First invitation open recorded | ✅ Verified | `COALESCE(first_opened_at, CURRENT_TIMESTAMP)` populates initial timestamp, `view_count = 1` |
| Subsequent opens update `last_opened_at` | ✅ Verified | `last_opened_at = CURRENT_TIMESTAMP`, `first_opened_at` remains unchanged |
| `view_count` increments correctly | ✅ Verified | `view_count = view_count + 1` on each valid access |
| Opening Rate calculated correctly | ✅ Verified | `(Opened Guests / Total Guests) * 100` |
| RSVP Rate calculated correctly | ✅ Verified | `(Responded Guests / Total Guests) * 100` |
| Expected Attendance calculated correctly | ✅ Verified | `SUM(confirmed_pax)` from `rsvps` table |
| Guest Book counts (Approved, Pending, Rejected) | ✅ Verified | Calculated via `OperationModel::getAttendanceStats` |
| Recent Activities ordered chronologically | ✅ Verified | `usort` by date descending |
| Guest Progress Table filters (Opened, Unopened, RSVP, Wish) | ✅ Verified | Handled by `GuestModel::getGuestProgressList` filter clauses |
| PHP syntax check across all 5 modified files | ✅ Verified | `c:\xampp\php\php.exe -l` returned 0 syntax errors |

---

## Architectural Integrity

- **Strictly Guest-Based**: Analytics answer "What is the progress of my invitation?" without generic web traffic noise.
- **Performance Optimized**: Non-blocking single-query opening tracker ensures fast rendering of public invitations.
- **No Duplicate Tables**: Computed dynamically from `guests`, `rsvps`, and `wishes` records.
- **Backward Compatible**: Unopened invitations display `Not Opened` with zero view counts without breaking existing functionality.