# Customers Purchase History Design

## Data Model

The capability reads from five tables; it owns no schema of its own.

```mermaid
erDiagram
    customers {
        int id PK
        string name
        timestamp deleted_at
    }
    orders {
        int id PK
        int customer_id FK
        decimal total
        string status
    }
    order_product {
        int order_id FK
        int product_id FK
    }
    products {
        int id PK
        int category_id FK
    }
    categories {
        int id PK
        int parent_id FK
    }

    customers ||--o{ orders : "customer_id"
    orders ||--o{ order_product : "order_id"
    order_product }o--|| products : "product_id"
    products }o--|| categories : "category_id (child only)"
    categories }o--o| categories : "parent_id"
```

**Model surface used by this unit:**

| Model | Attributes read | Relation used | Notes |
|-------|-----------------|---------------|-------|
| `Customer` | `id`, `orders_count` (virtual) | `orders()` hasMany | SoftDeletes active; deleted customer → 404 |
| `Order` | `id`, `customer_id`, `total`, appended `code`/`is_editable`/`debt_locked` | `products()` belongsToMany via `order_product` | No SoftDeletes; deletes are permanent |
| `Category` | `id`, `name`, `parent_id` | — | Only rows where `parent_id IS NOT NULL` are selected |
| `order_product` | `order_id`, `product_id` | pivot | Used implicitly by `whereHas` EXISTS subquery |
| `products` | `category_id` | — | Used only inside the EXISTS subquery |

`orders.status` ∈ {`draft`, `done`}. Both values are included — no status filter is applied.

`orders.total` = subtotal − discount_amount (net amount). The lifetime `amountTotal` sums this field unfiltered.

Appended attribute `debt_locked` on `Order` runs `debts()->where('type','pos_debt')->exists()` lazily per instance — see N+1 note under Constraints.

## Internal Flows

### Request flow

```mermaid
sequenceDiagram
    participant Browser
    participant Middleware as ['web','admin'] middleware
    participant Action as CustomerController::orders
    participant DB

    Browser->>Middleware: GET /customers/{id}/orders[?category=&page=]
    alt unauthenticated
        Middleware-->>Browser: 302 → auth/login
    end
    Middleware->>Action: pass

    Action->>DB: Customer::withCount('orders')->findOrFail($id)
    alt unknown or soft-deleted id
        DB-->>Action: ModelNotFoundException
        Action-->>Browser: 404
    end
    DB-->>Action: $item (Customer + orders_count)

    Action->>DB: Category::whereNotNull('parent_id')->pluck('name','id')
    DB-->>Action: child categories map

    Action->>Action: build base query Order::where('customer_id',...)->orderBy('id','desc')

    alt request('category') is truthy
        Action->>Action: ->whereHas('products', category_id = ?)
    end

    Action->>DB: ->paginate(20)
    DB-->>Action: LengthAwarePaginator<Order>

    Action->>DB: Order::where('customer_id',$id)->sum('total')
    DB-->>Action: $amountTotal (unfiltered lifetime sum)

    Action-->>Browser: 200 HTML — pages.customer-orders
```

### Query construction detail

Two independent DB reads are issued after the customer is resolved:

**Read 1 — order list (possibly filtered)**
```
SELECT orders.*, ...
FROM orders
[WHERE EXISTS (
    SELECT 1 FROM order_product
    JOIN products ON products.id = order_product.product_id
    WHERE order_product.order_id = orders.id
      AND products.category_id = ?
)]
WHERE orders.customer_id = ?
ORDER BY orders.id DESC
LIMIT 20 OFFSET ?
```
The EXISTS subquery is added only when `request('category')` is truthy. No duplicate rows are possible.

**Read 2 — lifetime total**
```
SELECT SUM(total) FROM orders WHERE customer_id = ?
```
This query has no category constraint. It is always executed, even when the list is filtered.

## Technical Decisions

| Decision | Rationale |
|----------|-----------|
| Route declared before `resource('/customers')` | Without this ordering, `/customers/{customer}/orders` would be captured by the resource `show` route, since `{customer}` is a wildcard. The named route `customers.orders` must be registered first. (`routes/web.php:54,62`) |
| No `status` filter on the order query | The screen is a full purchase history, not a "completed sales" report. Both `draft` and `done` orders are intentional. (`CustomerController.php:204`) |
| Category filter uses `whereHas` EXISTS, not a JOIN | Avoids order-row duplication when an order has multiple products in the same category. The filter is order-level ("any line matches"), not line-level ("only matching lines"). (`CustomerController.php:205-207`) |
| Lifetime total is a separate unfiltered `sum` query | Intentionally decoupled from the paginated/filtered query to represent lifetime spend regardless of the current filter. The view currently never renders it, making the asymmetry invisible to users. (`CustomerController.php:210`) |
| Category dropdown limited to child categories (`whereNotNull('parent_id')`) | Products are assigned only to child category ids; parent (group) categories always produce empty filter results. The fix (2026-09-21) eliminates that dead-end from the UI. (`CustomerController.php:202`) |
| Read-only action; no session state mutated | The action resolves, queries, and renders. No writes, no session writes, no cache invalidation. |

## Notes

**`amountTotal` is dead weight.** `$amountTotal` is computed and passed to the view, but `pages/customer-orders.blade.php` never renders it — the single `<tfoot>` block that would display a total is commented out. The value is not user-visible. Product decision needed on reimplementation: either un-comment the footer (and decide whether it should reflect the filter or remain lifetime) or drop the variable.

**Per-row `debt_locked` N+1.** `Order::$appends` includes `debt_locked`, which resolves via `debts()->where('type','pos_debt')->exists()`. If the blade view reads this attribute for each of the 20 paginated orders, up to 20 extra queries fire per page load. Mitigation: eager-load the existence flag or precompute it in a subquery before rendering.

**No observability.** The action emits no logs, metrics, or traces despite running a `whereHas` EXISTS subquery and an aggregate on every request.

**No per-record authorization.** Any authenticated admin can view any customer's full order history. This is consistent with the system-wide authentication-only model (ADR-0009), but there is no ownership or role scope check.

**Order deletes are permanent.** `Order` has no SoftDeletes. Deleted orders never appear in the history; there is no "deleted order" state to handle.