# Customers Statistics Design

## Data Model

### `customer_order_summary` (read model)

One row per customer. Written exclusively by `Order::summaryLogging()`; this unit only reads it.

| Column | Type | Meaning |
|--------|------|---------|
| `customer_id` | integer (PK) | one row per customer |
| `orders_count` | integer | Σ orders across all time |
| `amount_total` | decimal(14,2) | Σ `order.total` |
| `points_total` | decimal(14,2) | Σ `order.earned_point` |
| `categories_statistic` | longText, cast `object` | JSON per-category / per-`attr_weight` aggregates |
| `created_at` / `updated_at` | timestamps | last rebuild time; `updated_at` is exposed as `statisticsUpdatedAt` |

`categories_statistic` deserialises to an array of category objects:

```jsonc
[
  {
    "category_id": 1,
    "category_name": "Sữa",
    "items": [
      { "attr_weight": "900g", "qty_total": 12, "amount_total": 3600000 },
      { "attr_weight": "400g", "qty_total": 5,  "amount_total":  900000 }
    ]
  }
  // one object per tracked category
]
```

Tracked categories: milk (`category_id = 1`), medicine (`category_id = 8`), and consumption ids `[34, 41, 42]`. For consumption ids the rebuild forces `attr_weight = 'ALL'`, collapsing all per-weight detail into a single bucket per category.

### Live fields read from `Customer`

`debt_total` and `points` are read directly from the `Customer` model on every request and are never stale. They bypass the read model entirely.

### Annotated orders

`Order` rows for the customer filtered by `whereNotNull('notes')`, ordered `id DESC`, paginated 10 per page. These are live reads from the `orders` table.

---

## Internal Flows

### Main request flow (`CustomerController::statis`)

```mermaid
flowchart TD
    A[GET /customers/{id}/statistic] --> B[admin middleware\nauthentication check]
    B -- unauthenticated --> C[302 → auth/login]
    B -- authenticated --> D[Customer::findOrFail(id)]
    D -- not found --> E[404]
    D -- found --> F[CustomerOrderSummary::where\ncustomer_id = id → first()]
    F -- null --> G[summary = null]
    F -- found --> H[summary row]
    G & H --> I[Compute headline scalars\norders_count = summary.orders_count ?? 0\namountTotal = summary.amount_total ?? 0\npointsTotal = summary.points_total ?? 0\ndebtTotal = customer.debt_total live\nstatisticsUpdatedAt = summary.updated_at ?? null]
    I --> J{try: decode categories_statistic}
    J -- success --> K[Re-key array by category_id\ninto categoryAttrStatistic map]
    K --> L[attrStatistic = collect\ncategoryAttrStatistic 1 .items ?? \nunset entry 1]
    L --> M[medicineStatis = collect\ncategoryAttrStatistic 8 .items ?? \nunset entry 8]
    M --> N[goodsStatis = remaining entries\naggregated + sortBy name]
    J -- exception --> O[attrStatistic = empty\nmedicineStatis = empty\ngoodsStatis = empty\nheadline totals intact]
    N & O --> P[orders = Order::where customer_id\nwhereNotNull notes\norderBy id desc\npaginate 10]
    P --> Q[return view pages.customer-statis\ncompact all variables]
```

### Category decode algorithm (step 6 detail)

1. `categoriesStatistic = summary->categories_statistic ?? []` (already an array via the `object` cast; no explicit JSON decode needed).
2. Re-key into an associative map: `categoryAttrStatistic[obj->category_id] = obj`.
3. Extract milk: `attrStatistic = collect(categoryAttrStatistic[1]->items ?? [])`, then `unset(categoryAttrStatistic[1])`.
4. Extract medicine: `medicineStatis = collect(categoryAttrStatistic[8]->items ?? [])`, then `unset(categoryAttrStatistic[8])`.
5. Aggregate remainder into `goodsStatis`: for each surviving entry, emit `{name: category_name, qty_total: Σ items[*].qty_total, amount_total: Σ items[*].amount_total}`, then `sortBy('name')`.
6. Entire block (steps 1–5) is wrapped in `try/catch (\Exception)`: on any exception, all three collections are reset to empty and execution continues — headline totals computed before the `try` block are unaffected.

### Read model rebuild (upstream, not in this unit)

`Order::summaryLogging()` executes a single `INSERT … SELECT … ON DUPLICATE KEY UPDATE` over `orders + order_product + products + categories`. It runs via:
- `Kernel::schedule()->daily()` — nightly, automatic.
- `php artisan customer-summary:logging` — on demand.

This unit **never triggers a rebuild**; it only reads the result.

---

## Technical Decisions

| Decision | Rationale | Evidence |
|----------|-----------|----------|
| Headline totals served from a nightly precomputed read model, not a live aggregate | Avoid a multi-join aggregate on every page load; O(1) single-row read instead (ADR-0005) | `Order::summaryLogging`, `Kernel::schedule()->daily()` |
| Debt and current points read live from `Customer`, not from the summary | Debt and balance change frequently; showing stale values here would cause confusion — only historical totals can tolerate the nightly lag | `CustomerController.php:228`, `customer-statis.blade.php:50-51` |
| Milk (id `1`) and medicine (id `8`) special-cased into separate tables; all other tracked categories aggregated | Business domain rule: these two product families warrant dedicated per-weight breakdowns (ADR-0006) | `Order.php:23-25`, `CustomerController.php:239-251` |
| Consumption categories `[34, 41, 42]` flattened to a single `attr_weight = 'ALL'` bucket | Per-weight detail is not meaningful for consumption products; collapse is applied during rebuild, not at read time | `Order.php:27,141-161` |
| Category decode wrapped in `try/catch` | A corrupt read model must not 500 the page; headline totals are computed before the `try` block so they survive any decode failure | `CustomerController.php:233-256` |
| Annotated-orders panel filters `whereNotNull('notes')` only | This panel is for note review, not full order history; full history belongs to the `customers-purchase-history` unit | `CustomerController.php:258` |
| Route registered before `resource('/customers', …)` | Prevents `/customers/{customer}/statistic` from being captured by the resource `show` route | `routes/web.php:57` vs `:62` |
| No per-record authorization | Authentication-only access control; any authenticated admin can view any customer's statistics (ADR-0009) | `routes/web.php:57`, `config/admin.php` |
| Medicine id `8` used as-is (known placeholder) | Confirmed Non-Goal in `openspec/changes/archive/2026-08-27-customer-statis-and-order-filter/design.md`; the business will supply the correct id later — implement against `8` without further confirmation | `Order.php:25` |

---

## Notes

### Staleness model

| Field | Source | Freshness |
|-------|--------|-----------|
| `orders_count`, `amount_total`, `points_total` | `customer_order_summary` | nightly; up to ~24 h stale |
| `categories_statistic` breakdown | `customer_order_summary` | nightly; up to ~24 h stale |
| `debt_total` | live `Customer` | always current |
| `points` (spendable balance) | live `Customer` | always current |
| `statisticsUpdatedAt` | `customer_order_summary.updated_at` | reflects last successful rebuild |

### Distinguishing "not yet computed" from "all-zero customer"

When `summary === null` (brand-new customer or one never processed by the nightly job), `statisticsUpdatedAt` is `null` and the template renders a "not yet updated" placeholder. This distinguishes a missing summary from a genuinely zero customer. The residual gap: if an existing summary row fails to refresh on a subsequent night, the page shows a stale-but-present date — detectable only if an operator notices the age of the timestamp.

### Observability gap

The `statis` action emits no logs, metrics, or traces. A failed nightly rebuild is invisible at this endpoint — the only in-page signal is `statisticsUpdatedAt`, which requires a human to notice it is stale.

### `amount_total` vs sibling screen

`amount_total` here is the precomputed Σ `order.total` from the summary. The `customers-purchase-history` unit computes its own live Σ `total`. Both derive from the same source rows but via different paths, so a filtered or stale mismatch between the two screens is expected, not a bug.