# Orders CRUD Design

## Data Model

### `orders` table

| Column | Type | Notes |
|--------|------|-------|
| `id` | PK | Human-facing code derived as `#QT78-{id}` via `$prefix_id` |
| `customer_id` | FK nullable | Null for walk-in orders |
| `status` | enum `draft\|done` | `$status = ['draft','done']`; defaults `draft` |
| `count` | int | Number of resolved product **lines** (not summed qty) |
| `subtotal` | decimal | `Σ round(price,1) * qty` across resolved lines |
| `discount_amount` | decimal | Free-form; rounded to 1 decimal; no upper bound |
| `total` | decimal | `subtotal − discount_amount`; can be negative if discount exceeds subtotal |
| `paid` | decimal | Equals `total` at write time |
| `earned_point` | decimal | `Σ round(reward_point,1) * qty`; frozen at write time |
| `debt_amount` | decimal | POS credit; 0 for cash orders; frozen once `debt_locked` |
| `points_awarded_at` | timestamp nullable | Null until first `done` finalisation with a customer; idempotency sentinel |
| `notes` | string nullable | Free-form order notes |
| `created_by` | FK | Admin user who created/last finalised the order |
| `updated_at` | timestamp | Drives the 24 h edit window via `is_editable` |

`$fillable = ['notes','debt_amount']`
`$appends = ['code','is_editable','debt_locked']`
`$limit_hours_editable = 24`
No `SoftDeletes`.

### `order_product` pivot table

| Column | Notes |
|--------|-------|
| `order_id` | FK → `orders.id` CASCADE DELETE |
| `product_id` | FK |
| `qty` | Submitted quantity |
| `price` | Sale-time price snapshot from `getPriceByCustomerType` — immune to later re-pricing |
| `unit_id` | Resolved selling unit; null means base unit |
| `conversion_qty` | `ProductUnit.conversion_qty` snapshot at sale time; null for base unit |

### Computed attributes on `Order`

| Attribute | Derivation |
|-----------|------------|
| `code` | `$prefix_id . $this->id` → `#QT78-{id}` |
| `is_editable` | `status === 'draft'` OR `updated_at` within `$limit_hours_editable` hours of now |
| `debt_locked` | True when a `pos_debt` row already exists in `customer_debts` for this order |

### Relations

- `Order::customer()` — BelongsTo `Customer` (nullable)
- `Order::products()` — BelongsToMany `Product` via `order_product`, `withPivot(['qty','price','unit_id','conversion_qty'])`
- `Order::debts()` — HasMany `CustomerDebt` where `order_id = this.id`

### Related tables (owned by other units)

- `customers.points` — written by this unit on finalisation and reversal
- `customer_debts` — `pos_debt` rows inserted by `CustomerDebt::record`; `order_id` set NULL on `orders` hard delete

### ERD

```mermaid
erDiagram
    orders {
        int id PK
        int customer_id FK
        string status
        int count
        decimal subtotal
        decimal discount_amount
        decimal total
        decimal paid
        decimal earned_point
        decimal debt_amount
        timestamp points_awarded_at
        string notes
        int created_by FK
        timestamp updated_at
    }
    order_product {
        int order_id FK
        int product_id FK
        decimal qty
        decimal price
        int unit_id
        decimal conversion_qty
    }
    customers {
        int id PK
        string phone
        string type
        decimal points
        decimal debt_total
    }
    customer_debts {
        int id PK
        int customer_id FK
        int order_id FK
        string kind
        decimal amount
    }
    products {
        int id PK
        string code
        decimal reward_point
    }

    orders ||--o| customers : "customer_id"
    orders ||--o{ order_product : "id → order_id (CASCADE)"
    order_product }o--|| products : "product_id"
    customer_debts }o--o| orders : "order_id (SET NULL on delete)"
    customer_debts }o--|| customers : "customer_id"
```

---

## Internal Flows

### Order lifecycle state machine

```mermaid
stateDiagram-v2
    [*] --> draft : POST /orders (default or explicit)
    draft --> done : POST /orders status=done\nPUT /orders/{id} status=done
    done --> draft : PUT /orders/{id} status=draft (within 24h)
    done --> [*] : DELETE /orders/{id} (hard delete + reversal)
    draft --> [*] : DELETE /orders/{id} (hard delete)

    note right of done
        points_awarded_at set on first done
        debt_locked set on first done with debt
        Both sentinels survive done→draft→done
    end note
```

### Totalling engine (shared by `store` and `update`)

Runs in-memory; produces `count`, `subtotal`, `earned_point`, and the `$order_lines` array for later pivot attachment. No DB writes occur inside the engine itself.

```
count = 0; subtotal = 0; earned_point = 0; order_lines = []
customerType = customer ? customer.type : ''

for each item in items:
    prod = Product::with('units')->code(item.code)->first()
    if not prod: continue                       # unknown code silently skipped

    count++                                     # increments per LINE, not per qty unit

    unitId = item.unit_id ? (int) item.unit_id : null
    if unitId:
        productUnit = prod.units()->where('unit_id', unitId)->first()
        if not productUnit: unitId = null       # fall back to base unit

    price = prod.getPriceByCustomerType(customerType, unitId)
    subtotal += round(price, 1) * item.qty
    earned_point += round(prod.reward_point, 1) * item.qty

    order_lines[prod.id] = {
        qty:            item.qty,
        price:          price,
        unit_id:        unitId,
        conversion_qty: productUnit ? productUnit.conversion_qty : null
    }

total = subtotal - discount_amount
paid  = total
```

### `store` flow (POST /orders)

```mermaid
flowchart TD
    A[Validate payload] -->|items empty| B[422 — Không thể tạo đơn hàng rỗng]
    A -->|valid| C[Instantiate Order\nstatus = input.status ?: draft]
    C --> D[Resolve customer by phone\nCustomer::code phone .first]
    D --> E[Run totalling engine]
    E --> F[total = subtotal − discount_amount\npaid = total]
    F --> G{debt_amount > 0?}
    G -->|yes, no customer| H[302 pos.index\nPhải chọn khách hàng…]
    G -->|yes, debt > total| I[302 pos.index\nSố tiền nợ không được…]
    G -->|ok| J[order.debt_amount = debtAmount]
    J --> K[DB::transaction]
    K --> L[associate customer]
    L --> M{status == done\n&& customer?}
    M -->|yes| N[customer.points += earned_point\ncustomer.save\norder.points_awarded_at = now]
    M -->|no| O[skip points]
    N --> P[order.save]
    O --> P
    P -->|save failed| Q[return false → rollback\n302 pos.index + Tạo đơn hàng thất bại]
    P -->|saved| R[attach order_product pivots]
    R --> S{done && debt > 0\n&& customer?}
    S -->|yes| T[CustomerDebt::record pos_debt]
    S -->|no| U[skip debt]
    T --> V{status?}
    U --> V
    V -->|draft| W[302 pos.index\nLưu nháp thành công]
    V -->|done| X[200 pages.pos-print]
```

Key invariant: the entire block from `order.save` through pivot attach and `CustomerDebt::record` runs inside a single `DB::transaction`; any failure rolls back atomically. Pivot attachment is skipped if `order.save()` returns falsy.

### `update` flow (PUT /orders/{id})

```mermaid
flowchart TD
    A[Validate\nitems required_without:create_now_mode] --> B[Order::findOrFail id]
    B -->|404| Z1[404]
    B --> C[capture debtLocked = order.debt_locked]
    C --> D{create_now_mode?}
    D -->|yes| E[Rebuild items from order.products pivots\nstatus = input.status ?: order.status\nkeep stored customer/notes/discount/debt]
    D -->|no| F[Use submitted items]
    E --> G
    F --> G{order.is_editable?}
    G -->|false| Z2[302 back\nKhông thể cập nhật đơn hàng hoàn thành quá 24h]
    G -->|true| H{order.status == draft?}
    H -->|yes| I[Resolve customer from input phone]
    H -->|no| J[Keep order's existing customer]
    I --> K
    J --> K[Assign status/notes/discount_amount\nReset count/subtotal/earned_point]
    K --> L[Run totalling engine in-memory]
    L --> M[total = subtotal − discount_amount]
    M --> N{debtLocked?}
    N -->|false| O{debt guard}
    O -->|violation| Z3[302 back + error\nno DB writes yet]
    O -->|ok| P[order.debt_amount = debtAmount]
    N -->|true| Q[leave debt_amount frozen]
    P --> R
    Q --> R[DB::transaction]
    R --> S[products detach then re-attach new pivots]
    S --> T[associate customer]
    T --> U{status == done\n&& !points_awarded_at\n&& customer?}
    U -->|yes| V[customer.points += earned_point\npoints_awarded_at = now]
    U -->|no| W[skip points]
    V --> X[order.save]
    W --> X
    X --> Y{!debtLocked && done\n&& customer && debt > 0?}
    Y -->|yes| AA[CustomerDebt::record pos_debt]
    Y -->|no| BB[skip debt]
    AA --> CC{status?}
    BB --> CC
    CC -->|draft| DD[302 pos.index + toast]
    CC -->|done| EE[order.load products\n200 pages.pos-print]
```

Critical ordering: the debt guard runs **before** any pivot mutation (detach/attach). A guard rejection leaves the order's `order_product` rows and `updated_at` byte-identical to their pre-request state.

### `destroy` flow (DELETE /orders/{id})

```mermaid
flowchart TD
    A[Order::find id] -->|null| B[isSuccess = false]
    A -->|found| C[DB::transaction]
    C --> D[Customer::reversePointsForOrder\npoints -= earned_point clamped ≥ 0\nonly if points_awarded_at set]
    D --> E[CustomerDebt::voidForOrder\ninsert debt_void per un-voided pos_debt\nreduce debt_total by min entry balance]
    E --> F[order.delete — hard delete\norder_product CASCADE\ncustomer_debts.order_id SET NULL]
    F -->|success| G[isSuccess = true]
    F -->|failure| H[rollback → isSuccess = false]
    B --> I{isSuccess?}
    G --> I
    H --> I
    I -->|true| J[200 JSON status:true\ntrans admin.delete_succeeded]
    I -->|false| K[200 JSON status:false\ntrans admin.delete_failed]
```

Note: an unknown `id` returns `{status: false}` (no 404) because `Order::find` is used instead of `findOrFail`.

### `index` flow (GET /orders)

1. Base query: `Order::with('customer')->orderBy('updated_at','desc')`
2. If `q` present: apply a **grouped closure** `->where(fn => where('id',$q)->orWhere(CONCAT('#QT78-',id), $q)->orWhereHas('customer', phone LIKE %q%))` — grouping ensures `status` stays ANDed with the entire OR group.
3. If `status` present: `->where('status', $status)`.
4. `->paginate(30)` → render `pages.orders` with `list`.

---

## Technical Decisions

| Decision | Rationale | Evidence |
|----------|-----------|----------|
| `store`/`update` wrap points + save + pivots + `pos_debt` in a single `DB::transaction` | A failure between steps (e.g. save succeeds but `CustomerDebt::record` throws) previously left partial state with no recovery path | `OrderController.php:135-160,314-337` |
| Debt guard in `update` runs **before** `products()->detach()` | A routine guard rejection (debt > total) is not a system error; running it after detach left line items mutated with the order unsaved | `OrderController.php:301-312` |
| `points_awarded_at` timestamp as award-once sentinel | Allows `done → draft → done` cycles without double-awarding; a null check is cheaper and safer than counting ledger rows | `Order.php`, `OrderController.php:311` |
| `debt_locked` (derived from existence of a `pos_debt` row) as post-once sentinel | Mirrors `points_awarded_at` for debt; freezes `debt_amount` on edit so the A/R balance is never double-posted | `Order.php:65-67`, `OrderController.php:296,321` |
| Pivot captures sale-time `price` and `conversion_qty` | Freezes the economics of each line at sale time; re-pricing a product later does not retroactively change closed orders | `order_product.price`, `order_product.conversion_qty` |
| `create_now_mode` re-prices at **current** catalog prices, not stored pivot prices | Rebuilds items from stored pivots but passes them through the live totalling engine; finalising an old draft intentionally re-prices at the time of finalisation | `OrderController.php:223-240,276`; confirmed 2026-09-25 |
| `count` = number of resolved product **lines**, not summed qty | Counting units of quantity was deemed less useful than counting distinct SKUs in the cart | `OrderController.php:97,266` |
| `create`/`edit` verbs are empty stubs | Order creation and editing are driven by the POS terminal UI, not RESTful resource forms | `OrderController.php:53-56,192-195` |
| Hard delete with no `SoftDeletes` | A deleted order must not reappear in history or financial aggregates; soft deletion would require filtering every read path | `data-dictionary.md:203` |
| `destroy` uses `Order::find` (not `findOrFail`) | Unknown-id deletions return `{status: false}` JSON rather than a 404, keeping the caller's JS handling uniform | `OrderController.php:364` |
| `q` search wrapped in a grouped closure | Prevents an id/code match from bypassing a concurrent `status` filter (ungrouped `orWhere` changes operator precedence) | `OrderController.php:31-39` |
| `lockForUpdate` inside `CustomerDebt::record`, `reversePointsForOrder`, `voidForOrder` | Prevents concurrent requests from overdrawing a customer's point balance or double-posting debt | `CustomerDebt.php:41,94`, `Customer.php:104` |

---

## Notes

**Unknown product codes are silently skipped.** A line whose `code` resolves to no live product is dropped from the engine with no error. The order persists without that line, and `count`/`subtotal`/`earned_point` reflect only resolved products. This means a cart submitted with all-unknown codes creates an order with `count = 0` and `total = 0 − discount_amount`.

**`discount_amount` has no upper bound.** A discount exceeding `subtotal` drives `total` negative. The debt guard then rejects any `debt_amount > 0` submission (debt cannot exceed a negative total), so a negative-total order can only be `draft` or a zero-debt `done`.

**`create_now_mode` re-prices at current catalog prices.** When a stored draft is finalised via `create_now_mode`, the engine receives the stored `code`/`qty`/`unit_id` tuples but calls `getPriceByCustomerType` live — so totals at finalisation may differ from totals when the draft was saved. This is confirmed intentional behaviour.

**No observability.** The create, finalise, point-award, debt-post, and delete paths emit no log entries, metrics, or traces. A transaction rollback (e.g. a `CustomerDebt::record` failure inside `store`) leaves no operational signal of what failed or why.

**Authorization is authentication-only.** Any authenticated admin may view, edit, finalise, or delete any order regardless of who created it. There is no per-record ownership check.

**`destroy` returns 200 for unknown ids.** `Order::find` returns null rather than throwing; `isSuccess` becomes false and the response is `{status: false, message: trans('admin.delete_failed')}` with HTTP 200, not 404.