# Dashboard Design

> Produced by the Reversa **Writer** (phase: generation) · doc_level: `complete`
> Generated on 2026-09-19 · Updated 2026-09-22

**Confidence scale:** 🟢 CONFIRMED · 🟡 INFERRED · 🔴 GAP

## Data Model

The dashboard unit owns **no tables** and performs **no writes**. It aggregates read-only data from four tables owned by other units.

### Tables consumed (read-only)

| Table | Columns used | Purpose |
|-------|-------------|---------|
| `orders` | `status`, `created_at` | KPI today-done count; chart bucket aggregation (`COUNT(*)`) |
| `order_product` | `product_id`, `qty` | Top-10 products aggregation (`SUM(qty)`) |
| `products` | `id`, `name`, `deleted_at` | Top-10 join target; soft-delete filter |
| `customers` | *(any — count only)* | KPI total customers |

### Key column constraints (relevant to this unit)

- `orders.status` — string; this unit filters on the literal value `'done'`.
- `orders.created_at` — datetime; bucketed via `DATE(created_at)` (non-SARGable) and compared to `CURDATE()`.
- `products.deleted_at` — nullable datetime; `NULL` = active; non-`NULL` = soft-deleted (excluded from top-10).
- `order_product.qty` — numeric; summed per `product_id` for top-10 ranking.

No migrations, no seeders, and no schema changes belong to this unit. Schema ownership is confirmed in `_reversa_sdd/data-dictionary.md` and `_reversa_sdd/erd-complete.md`. 🟢

---

## Internal Flows

### Main flow — `DashboardController::index`

Source: `app/Http/Controllers/DashboardController.php:28-122` 🟢

```mermaid
flowchart TD
    A([GET /dashboard]) --> B{admin middleware}
    B -- anonymous --> C([302 → auth/login])
    B -- authenticated --> D[Build KPI totals]
    D --> E[Init chartRange map & yearRange=null]
    E --> F[Resolve range parameter]
    F --> G{range value}
    G -- day --> H[Build day CASE fragment + rangeData]
    G -- week --> I[Build week CASE fragment + rangeData]
    G -- month --> J[Build month CASE fragment + rangeData + yearRange]
    G -- absent/other --> J
    H & I & J --> K[Order::select DB::raw query → rangeGroup map]
    K --> L[Pivot rangeData → labelChart / valueChart\ndefault missing buckets to 0]
    L --> M[Top-10 products query\norder_product ⋈ products, deleted_at IS NULL]
    M --> N[return view pages.dashboard compact all vars]
```

#### Step-by-step

1. **KPIs** (`:34-38`) — `Product::count()`, `Customer::count()`, `Order::where('status','done')->whereRaw("DATE(created_at)=CURDATE()")->count()`.
2. **Static map & default** (`:40-46`) — `chartRange = ['day'=>'Day', 'week'=>'Week', 'month'=>'Month']`; `yearRange = null`.
3. **Range resolution** (`:47-50`) — `$range = request()->range ?: 'month'`; coerce any value outside `{day,week,month}` to `month` (whitelist added 2026-09-19).
4. **SQL CASE builder** (`:52-99`) — PHP loop constructs a raw `CASE … END AS range_date` fragment and an ordered `$rangeData` label list (see Bucketing Algorithm below).
5. **Aggregation** (`:102`) — `Order::select(DB::raw($query))->groupBy('range_date')->pluck('quantity','range_date')->toArray()` → `$rangeGroup` (label → count map).
6. **Pivot** (`:104-112`) — iterate `$rangeData`; for each label look up `$rangeGroup[$label] ?? 0`; split into `labelChart` (`Arr::pluck 'key'`) and `valueChart` (`Arr::pluck 'value'`).
7. **Top products** (`:114-119`) — `DB::table('order_product')->join('products',…)->whereRaw('`products`.`deleted_at` is null')->select(id, name, SUM(qty) as quantity)->groupBy('product_id')->orderBy('quantity','desc')->limit(10)->get()`.
8. **Render** (`:121`) — `$this->view('pages.dashboard', compact(…))`.

### Alternative flows

| Trigger | Behaviour |
|---------|-----------|
| `range` absent or falsy | Defaults to `month` (`:47`). 🟢 |
| `range` not in whitelist | Coerced to `month`; no query error (`:47-50`). ✅ Fixed 2026-09-19. 🟢 |
| Order created 00:00–06:00, `range=day` | Matches no `WHEN` branch → excluded from chart (intentional, store closed). 🟢 |
| Bucket with zero orders | Absent from `$rangeGroup`; pivot defaults it to `0`; label retained. 🟢 |
| Soft-deleted product with historical sales | Filtered by `deleted_at IS NULL` in the top-10 join (`:117`). 🟢 |

---

## Bucketing Algorithm

### Day range

Three fixed 6-hour store-open windows. Anchor: `$startDay = now()->startOfDay()->addHours(6)` (06:00 server time). 🟢 (`:52-64`)

| Bucket label | Window | CASE condition |
|---|---|---|
| `Sáng` | 06:00–12:00 | `created_at >= $startDay AND < $startDay+6h` |
| `Trưa` | 12:00–18:00 | `created_at >= $startDay+6h AND < $startDay+12h` |
| `Chiều` | 18:00–24:00 | `created_at >= $startDay+12h AND < nextDayMidnight` |

**Carbon-mutation dependency:** boundaries in the legacy code are computed via in-place mutating `addHours` calls on Carbon 1.25 inside a string concatenation expression. Left-to-right evaluation causes each call to advance the same `$startDay` variable, so the three thresholds resolve to 06:00 / 12:00 / 18:00. Under Carbon 2/3 (immutable arithmetic), all three thresholds would equal 06:00, collapsing the windows silently. **Reimplementations must use explicit per-window boundaries.** 🟢 (`composer.lock`)

### Week range

Loop `Carbon::now()->startOfWeek()` to `endOfWeek()`, one iteration per calendar day. Each iteration emits `WHEN DATE(created_at)='Y-m-d' THEN 'Y-m-d'` and appends the ISO date to `$rangeData`. 🟢 (`:65-77`)

### Month range

Loop `startOfMonth` to `endOfMonth` in contiguous 6-day windows. ✅ Fixed 2026-09-22. 🟢 (`:85-97`)

```
cursor = startOfMonth
while cursor <= endOfMonth:
    windowEnd = cursor + 5 days          // inclusive upper bound
    label = "dd/mm-dd/mm"               // cursor to windowEnd
    emit WHEN DATE(created_at) >= cursor AND <= windowEnd THEN label
    record year(cursor), year(windowEnd) for yearRange
    cursor = cursor + 6 days            // next window starts immediately after windowEnd
```

- **Previous bug (fixed):** upper bound was `< windowEnd` (exclusive) and cursor advanced by 6 days, so every 6th day matched no window.
- **Remaining open issue:** for a month whose length is not a multiple of 6 (e.g. 31-day October, 28/29-day February), the last loop iteration starts before `endOfMonth` and its `windowEnd` (cursor+5) spills into the next calendar month — e.g. October yields a final bucket `31/10–05/11`, attributing early-November orders to the October chart. Not yet resolved (see requirements.md BR-05). 🟡

`yearRange` is built as `implode(' - ', array_unique([$startYear, $endYear]))` over the years touched by all windows; `null` for day and week ranges. 🟢 (`:97`)

---

## Technical Decisions

| Decision | Rationale / evidence | Confidence |
|----------|----------------------|------------|
| Live per-request aggregation — no cache, no materialised view | No cache facade or queue usage in `DashboardController.php:34-119`. | 🟢 |
| Raw SQL `CASE` string assembled in PHP, not the query builder | Enables per-range dynamic label assignment inside a single SQL expression; no query-builder equivalent. `DashboardController.php:52-99`. | 🟢 |
| Chart values are order **counts** (`COUNT(*)`), not revenue or units sold | `COUNT(*)` on `orders` grouped by `range_date` (`:59,69,84,102`). Stakeholder intent unconfirmed — see Constraints. | 🟢 |
| Day range drops overnight orders (00:00–06:00) | Store is closed overnight; intentional exclusion by design (`:52-64`). | 🟢 |
| Top-products query uses the query builder (not raw SQL), with a `whereRaw` only for the soft-delete filter | Soft-delete column not on the pivot; join required. `DashboardController.php:114-119`. | 🟢 |
| Top-products respects soft-deletes at query time | Discontinued products vanish from the list immediately on soft-delete, even for historical sales. | 🟢 |
| Bucket pivot done in PHP (not SQL) | `$rangeData` label order must be preserved and empty buckets must be filled with `0`; SQL `GROUP BY` alone cannot guarantee label ordering or emit absent rows. | 🟢 |

---

## Notes

### Constraints and pitfalls

- **Carbon version coupling** — the `day`-range implementation is correct only with Carbon 1.25 mutating arithmetic (`composer.lock: nesbot/carbon 1.25.*`). Any upgrade to Carbon 2/3 silently breaks it. Reimplementations must not rely on mutation. 🟢
- **Non-SARGable today-KPI predicate** — `whereRaw("DATE(created_at)=CURDATE()")` wraps the column in a function call, preventing index use on `orders.created_at`. Cost grows linearly with the table. 🟢
- **Raw SQL string concatenation is brittle** — Carbon datetime values are formatted and embedded by string concatenation. No parameter binding. Prefer the query builder with bindings in any reimplementation. 🟢
- **Chart metric is unconfirmed** — the chart counts orders, not revenue or units sold. Confirm the intended metric with stakeholders before re-labelling or porting. 🟡
- **Dead imports must not be carried forward** — `App\Models\OrderProduct` (no such model exists) and `Cassandra\Custom` are erroneous imports in the legacy controller (`DashboardController.php:8,12`). 🟢
- **No observability** — no logs, metrics, or traces are emitted by this action. Encore\Admin `operation_log` is globally disabled (`config/admin.php`). Dashboard latency is invisible; add instrumentation at reimplementation if production data volumes make it relevant. 🔴
- **Stateless unit** — no cross-request state, no session writes, no side effects. Fully idempotent and safe to cache at the reimplementation's discretion. 🟢