# Customers Loyalty Design

## Data Model

### Tables

**`customer_gift`** — redemption ledger (pivot between `customers` and `gifts`)

| Column | Type | Notes |
|--------|------|-------|
| `id` | bigint PK | auto-increment |
| `customer_id` | bigint FK | references `customers.id` |
| `gift_id` | bigint FK | references `gifts.id` |
| `points` | decimal(8,1) default 0 | cost snapshot at redemption time |
| `note` | text nullable | free-text annotated at redemption |
| `created_at` / `updated_at` | timestamps | present in schema but **not** maintained by `attach` — `Customer::gifts()` omits `withTimestamps()`, so both fields are null after `attach` |

The pivot is **append-only** through this unit; no delete or update path exists here.

**`customers`** (owned by `customers-crud`, mutated here)

| Column | Relevant to this unit |
|--------|-----------------------|
| `points` | live spendable balance; decremented by `gift.points` on each redemption; no separate ledger |

**`gifts`** (owned by `gifts-crud`, mutated here)

| Column | Relevant to this unit |
|--------|-----------------------|
| `active` | tinyint; only `active = 1` gifts are redeemable |
| `points` | cost in loyalty points |
| `quantity` | total allowed stock |
| `used` | running redemption count; incremented per redemption |
| `limit` | per-customer cap; `0` = unlimited |
| `quantity_available` | **computed accessor** `quantity − used` (not a column) |

### Entity Relationship

```mermaid
erDiagram
    customers {
        bigint id PK
        decimal points
    }
    gifts {
        bigint id PK
        tinyint active
        decimal points
        int quantity
        int used
        int limit
    }
    customer_gift {
        bigint id PK
        bigint customer_id FK
        bigint gift_id FK
        decimal points
        text note
        timestamp created_at
        timestamp updated_at
    }

    customers ||--o{ customer_gift : "redeems"
    gifts ||--o{ customer_gift : "redeemed as"
```

### Model Surface

| Symbol | Signature | Returns | Notes |
|--------|-----------|---------|-------|
| `Customer::gifts()` | `belongsToMany(Gift)->withPivot('points','note')` | relation | No `withTimestamps()` |
| `Customer::checkGiftAvailable` | `(Gift $gift)` | `['status'=>bool, 'message'?=>string]` | Non-nullable `Gift` type hint — must null-guard before calling |
| `Customer::reversePointsForOrder` | `(Order $order): void` | — | Owned by `orders-crud`; claws back `customers.points` on order delete |
| `Gift::scopeActive` | `($query)` | query | `where('active', 1)` |
| `Gift::getQuantityAvailableAttribute` | — | `quantity − used` | Appended computed attribute; gate uses strict `=== 0` |

---

## Internal Flows

### Redemption gate — `checkGiftAvailable(Gift $gift)`

```mermaid
flowchart TD
    A[receive Gift] --> B{quantity_available === 0?}
    B -- yes --> E1["return {status:false, message:'Quà tặng không khả dụng'}"]
    B -- no --> C{limit !== 0 AND prior_count >= limit?}
    C -- yes --> E2["return {status:false, message:'Đã vượt quá số lần đổi quà tối đa'}"]
    C -- no --> D{gift.points > customer.points?}
    D -- yes --> E3["return {status:false, message:'Không đủ điều kiện để nhận quà'}"]
    D -- no --> OK["return {status:true}"]
```

`prior_count` = `customer.gifts()->where('gift_id', id)->count()`. Sentinel `limit = 0` skips the count branch entirely.

---

### `POST /customers/{id}/redeem-points` — write path

```mermaid
flowchart TD
    A[request arrives] --> B[validate: gift_id required, note nullable]
    B -- fail --> RV[302 back + validation errors]
    B -- ok --> C["Customer::findOrFail(id)"]
    C -- 404 --> HTTP404[404]
    C -- ok --> D["Gift::active()->find(gift_id)"]
    D -- null --> E["302 back + 'Quà tặng không khả dụng'"]
    D -- found --> F["checkGiftAvailable (pre-lock)"]
    F -- blocked --> G["302 back + gate message"]
    F -- ok --> H["DB::transaction open"]
    H --> I["lockForUpdate customer + gift"]
    I --> J{gift still exists?}
    J -- no --> K["throw 'Quà tặng không khả dụng'"]
    J -- yes --> L["checkGiftAvailable (under lock)"]
    L -- blocked --> M[throw gate message]
    L -- ok --> N["gifts()->attach([gift_id => {note, points: gift.points}])"]
    N --> O["gift.used += 1"]
    O --> P["customer.points -= gift.points"]
    P --> Q[commit]
    Q --> R["admin_toastr('Đổi quà thành công') + 302 back"]
    K --> TX[rollback]
    M --> TX
    TX --> ERR["302 back + withInput + withErrors(e.getMessage())"]
```

The under-lock re-check (step L) is the **authoritative gate**. Every write is inside one transaction; any thrown exception rolls the entire set back — no partial mutations are possible.

---

### `GET /customers/{customer}/check-gift` — advisory JSON

```mermaid
flowchart TD
    A[request arrives] --> B["Customer::findOrFail(customer)"]
    B -- 404 --> HTTP404[404]
    B -- ok --> C["Gift::active()->find(gift_id)"]
    C -- null --> D["200 {status:false, message:'Quà tặng không khả dụng'}"]
    C -- found --> E["checkGiftAvailable(gift)"]
    E -- blocked --> F["200 {status:false, message:gate_message}"]
    E -- ok --> G["200 {status:true}"]
    G -.advisory only.-> note[No writes; no stock reserved]
```

A `true` verdict here can still be rejected at redemption if state changes in between.

---

### `GET /customers/{customer}/gift-received` — read page

1. `Customer::findOrFail($customer)` → 404 if unknown or soft-deleted.
2. Base query: `$customer->gifts()->orderBy('id', 'desc')`.
3. If `?q=` present: `->where(fn($q) => $q->where('name', 'like', "%{$search}%"))` — grouped closure, `name` column only, scoped to this customer's pivot rows.
4. `->paginate(30)`; render `pages.customer-gift-received` with `$gifts`.

---

## Technical Decisions

| Decision | Rationale | Confidence |
|----------|-----------|------------|
| Redemption wrapped in `DB::transaction` with `lockForUpdate` on both customer and gift rows, plus an under-lock re-check of `checkGiftAvailable` | Prevents oversell and overspend under concurrent requests; a race loser's re-check throws and the transaction rolls back atomically — no pivot row, no `used`/`points` delta. Introduced in ADR-0007. | 🟢 |
| Points debited directly from `customers.points` — no dedicated points ledger | Simpler than the `customer_debts` ledger pattern used in `customers-debt-actions`. The only redemption trail is the `customer_gift` row plus the `used`/`points` deltas. | 🟢 |
| Cost snapshotted as `pivot.points = gift.points` at attach time | Historical redemptions retain their original cost even if the gift is later repriced. | 🟢 |
| `check-gift` is stateless and advisory; authority lives in the under-lock re-check inside `redeem-points` | Allows the UI to give instant feedback without reserving stock; the write path is the single point of enforcement. | 🟢 |
| `limit = 0` sentinel means unlimited redemptions | Avoids a nullable column; `checkGiftAvailable` short-circuits the count query when `limit === 0`. | 🟢 |
| Out-of-stock test is strict `=== 0` on `quantity_available` | A gift where `used` overruns `quantity` (negative `quantity_available`) is not caught — considered an invariant the platform must uphold externally. | 🟡 |
| Any authenticated admin may act on any customer (no per-record scope) | ADR-0009; applies equally to `check-gift`, `redeem-points`, and `gift-received`. | 🟡 |
| `orderBy('id', 'desc')` on the `gift-received` query is left unqualified | Both `gifts` and `customer_gift` carry an `id` column. Empirically verified against MariaDB 10.11.13 via `php artisan tinker` (2026-09-23): the engine resolves the ambiguity without raising error 1052 on this version. No qualification added (`questions.md#question-5`). | 🟢 |

---

## Notes

### Constraints and pitfalls

- **No observability.** None of the three routes emit log lines, metrics, or audit events. A rolled-back redemption and all failure branches are invisible to operations; the only durable trace of a successful redemption is the pivot row plus the `used`/`points` deltas.
- **`customer_gift` timestamps unreliable.** `Customer::gifts()` uses `withPivot('points','note')` without `withTimestamps()`. The `created_at`/`updated_at` columns exist in the schema but are null after every `attach`. The `gift-received` view renders the *gift's* `created_at`, not the actual redemption timestamp.
- **Route-parameter naming inconsistency.** `redeem-points` uses the path parameter `{id}` while `check-gift` and `gift-received` use `{customer}`. Both resolve to the customer's primary key; the inconsistency is cosmetic and has no routing effect.
- **`check-gift` dead guard.** The original guard at `CustomerController.php:363` (`if (!\request()->gift_id) response()->json(null,400)`) is missing its `return` statement and is effectively dead code. The null-guard added after `Gift::active()->find()` (the 2026-09-21 fix) catches both the missing- and inactive-`gift_id` cases before `checkGiftAvailable` can receive a `null`.
- **Points balance shared with `orders-crud`.** `customers.points` is credited on `done` orders and clawed back (clamped ≥ 0) via `Customer::reversePointsForOrder` on order delete. This unit only debits; the shared field means the balance reflects both order activity and redemptions without a full ledger.