# Smart Requisition — A Better Decision Framework

## What it does today

`product/smart-requisition.php` flags any product where `stock_pcs < low_stock_alert`, then suggests a reorder quantity of:

```
suggested_pcs = max(low_stock_alert × 2, avg_monthly_sales × 1.5) − current_stock
```

It ranks by 30-day sales volume (Urgent / High / Normal), shows the last supplier and last purchase price, and lets you check items off into per-supplier requisitions. That's a reasonable starting point, but it's making the reorder decision on three numbers (a static alert threshold, a 90-day average, and current stock) and ignoring several things a pharmacy actually needs to weigh.

## Problems with the current formula

**It already computes pending purchase-order quantity but never uses it.** The query pulls `pending_qty` (stock already on order via an active/ordered requisition) and displays it in its own column — but the suggested-quantity formula never subtracts it. If you've already requisitioned 500 pcs of something last week, Smart Requisition will tell you to order another full batch today. This is a live bug, not a design choice, and it's the cheapest fix on this list.

**No expiry awareness.** A product can be "below alert level" in total piece count while the stock it does have is about to expire. Reordering more of it without checking near-expiry batches (tracked separately on the Expired Products page) risks compounding waste — you'd be buying more of something that's about to become a write-off anyway.

**No days-of-stock-remaining metric.** Box counts and a fixed alert threshold don't tell you urgency the way "this covers 4 more days of sales" does. Two products can both sit at 50 pcs below alert, but one sells 2/day and the other sells 40/day — those are wildly different emergencies, and the current Urgent/High/Normal bucketing (based on raw 30-day volume, not stock coverage) doesn't capture that gap well.

**No lead time or safety stock.** The reorder point is just `low_stock_alert`, a number someone typed in once per product. A proper reorder point accounts for how long the supplier actually takes to deliver, plus a safety margin for demand variability. Right now that's implicit and manual — whoever set `low_stock_alert` had to guess.

**"Last purchase price/supplier" isn't "best" price/supplier.** The tool defaults every suggested item to whoever you bought it from last time, with no comparison against other suppliers who've sold you the same product, even though `purchase_items` has that history sitting right there.

**No minimum order quantity enforcement.** Products already have a `min_qty` field (visible in the product edit form as "Min Order Qty"), but the suggested-boxes math never applies it — a supplier's or pharmacy's own minimum order rule is just ignored.

**No demand trend, only a flat average.** `avg_monthly` is total sales over 90 days ÷ 3 — it can't tell a product that's trending up (e.g. seasonal flu medicine in winter) from one that's flat or declining. A flat average always suggests the same thing regardless of direction.

**No budget or cash-flow ceiling.** "Est. Reorder Value" is shown, but there's no way to say "I only have ৳50,000 to spend this week — show me the highest-priority items that fit."

**No generic-substitution check.** If a product is low but a therapeutic equivalent (same generic + strength + form, which the app already tracks via the Alternative Medicines feature in Sale Entry) is well-stocked, reordering the low one at full suggested quantity may not be necessary.

## The improved framework

**Core metric: days of stock remaining, not box count.**
```
days_remaining = stock_pcs / avg_daily_sales
```
This is what should drive urgency and sort order, not raw 30-day volume. A product with 3 days of cover is more urgent than one with 20 days of cover, independent of how big either number is in absolute pieces.

**Reorder point with lead time + safety stock**, replacing the flat `low_stock_alert` threshold:
```
reorder_point = (avg_daily_sales × supplier_lead_time_days) + safety_stock
safety_stock  = avg_daily_sales × safety_days   (e.g. 3–7 days, tunable per product or category)
```
This needs a `lead_time_days` field per supplier (or per product-supplier pair) — currently not tracked anywhere in the schema. Even a rough default (e.g. 3 days) beats today's fully-manual alert number.

**Suggested order quantity, subtracting what's already incoming:**
```
target_stock    = reorder_point + (avg_daily_sales × order_cycle_days)
suggested_pcs   = max(min_qty, target_stock − stock_pcs − pending_qty)
suggested_boxes = ceil(suggested_pcs / qty_in_box)
```
`order_cycle_days` is how often you're willing to place orders for that product (weekly for fast movers, monthly for slow ones). This replaces the current `max(alert×2, avg×1.5)` guess with something tied to actual velocity and lead time, and it finally uses both `pending_qty` and `min_qty`.

**Expiry gate before suggesting a reorder:**
- Cross-reference `purchase_items.exp` for existing stock of that product.
- If a meaningful chunk of current stock expires within your normal sell-through window (using `avg_daily_sales`), reduce or flag the suggestion instead of ordering more on top of soon-to-expire stock.
- For genuinely slow movers, warn before ordering more than the product will realistically sell before *new* stock's expiry.

**Movement classification (ABC / XYZ style)**, computed once and cached:
- **Fast movers** (top ~20% of sales value): tight safety stock, frequent small reorders, prioritized at the top of the list regardless of current stock ratio.
- **Slow movers**: infrequent, smaller-batch reorders; flag but don't auto-suggest large quantities.
- **Dead stock** (zero sales in 90 days despite being below alert): surface separately with a "review — no recent demand" tag rather than an auto-suggested purchase, since blindly reordering these wastes cash.

**Best-price/supplier suggestion, not just "last."**
Pull the lowest recent `btp_vat` across all suppliers who've sold this product (from `purchase_items` joined to `purchases`), and show it alongside the last-used supplier — let the user pick, defaulting to whichever is cheaper unless a supplier relationship reason overrides it.

**Budget cap filter.**
Add an optional "Budget ceiling" input; when set, auto-select items in priority order (most urgent first, by days-remaining) until the running total hits the cap, so a cash-constrained order run doesn't require manually deselecting items one at a time.

**Trend-adjusted demand**, replacing the flat 90-day average:
```
trend_factor = avg_sales(last_30d) / avg_sales(31–90d)   (guard against divide-by-zero)
adjusted_avg_daily = avg_daily_sales × clamp(trend_factor, 0.5, 2.0)
```
This lets a genuinely rising or falling product pull its suggested quantity up or down instead of always reverting to a flat historical mean — clamped so one freak day doesn't spike the suggestion.

**Generic-substitution cross-check.**
Before flagging a branded product as urgent, check whether stock of a same-generic/strength/form alternative (the app's existing Alternative Medicines logic) comfortably covers demand — if so, downgrade the priority instead of suggesting a full reorder of the specific brand.

## Suggested priority order

1. **Fix the pending-quantity bug** — one line, `suggested_pcs` should subtract `pending_qty`. Immediate, no schema change, stops over-ordering today.
2. **Switch the core metric to days-of-stock-remaining** and re-rank Urgent/High/Normal off that instead of raw 30-day volume. No schema change — pure query/display logic.
3. **Enforce `min_qty`** in the suggested-boxes calculation — the field already exists and is already collected on the product form, it's just unused here.
4. **Add expiry awareness** — join existing `purchase_items.exp` data into the smart-requisition query and flag/suppress reorders for products sitting on soon-to-expire stock.
5. **Best-price-across-suppliers** — data already exists in `purchase_items`/`purchases`, just needs a different aggregate query than "last purchase."
6. **Movement classification + trend adjustment** — more query work, no schema change, meaningfully improves ranking quality.
7. **Lead time + formal reorder point, budget cap, generic-substitution check** — these need either a new field (`lead_time_days` on suppliers or products) or cross-page logic (Alternative Medicines), so they're the right next phase rather than immediate fixes.

Items 1–3 are small, safe changes I can make directly against the current schema with no migrations. Items 4–5 need slightly larger queries but still no new tables/columns. Items 6–7 are where I'd want your input on defaults (safety days, lead time, budget workflow) before building.
