"""Order Proposal service — supplier-scoped (s, S) reorder tool. Replaces the old "Reorder Alerts" page. This is procurement's primary weekly tool: pick a supplier, see exactly what to order this week, export as Excel PO. Layering: takes a SQLAlchemy Session at construction, delegates SQL to SupplyRepository, never imports FastAPI. Pure dict in / pure dict out. Reorder logic — (s, S) inventory system: trigger (reorder point) = trigger_mult × LT × avg_weekly_demand target (order-up-to) = target_mult × LT × avg_weekly_demand SKU appears when projected_stock_at_W+LT < trigger Order qty = ceil((target - projected) / pkg) × pkg if order_qty < MOQ → order_qty = MOQ Uniform multipliers (no tier differentiation per user spec): All tiers: trigger 1.5×LT, target 1.5×LT (Polleo-flat rule) i.e. (s, S) collapses to (s, s) — order is sized to lift projected stock exactly back to the trigger line. Projected stock at W+LT — walk forward week-by-week: closing[w0] = current WH stock closing[wN] = max(0, closing[wN-1] − demand[wN] + incoming[wN]) ↑ all POs from EVERY supplier projected = closing[w0 + LT] Demand source per week: 1. forecasts.total for latest run (next ~13w) 2. backtest_results.forecast as fallback 3. 13w non-promo run rate beyond the forecast horizon ABC Nutritional truck packing (33 EUR pallets): Only when supplier matches ABC. After computing the per-SKU proposal: • if total pallets < 33 → pull-forward closest-to-trigger non-triggered SKUs until truck fills • if total pallets > 33 → defer triggered SKUs with most buffer until pallet count is ≤ 33 Pallet count = ceil(order_qty / pcs_per_pallet) from logistic_master. Pallet & MOQ refinement: pcs_per_pallet from `data/logistic_master.csv` (falls back to supply_master.moq if unavailable). Package size mirror MOQ (no separate pack column in the schema today; rounding up to MOQ multiples is therefore the rounding rule for non-MOQ-floor cases). """ from __future__ import annotations import math from pathlib import Path from typing import Optional import pandas as pd from sqlalchemy.orm import Session from backend.repositories.supply_repo import SupplyRepository ROOT = Path(__file__).resolve().parents[2] LOGISTIC_CSV = ROOT / "data" / "logistic_master.csv" MOQ_CSV = ROOT / "data" / "moq_master.csv" ABC_SUPPLIER_PATTERN = "ABC NUTRITIONAL" TRUCK_TARGET_PALLETS = 33 TRUCK_GREEN_LOW = 31 TRUCK_GREEN_HIGH = 35 TRUCK_YELLOW_LOW = 28 TRUCK_YELLOW_HIGH = 38 # When pcs_per_box is missing from logistic_master, round order_qty up to # multiples of this. Catches the long-tail of SKUs without curated pallet # data — keeps PO quantities tidy without forcing per-unit ordering. DEFAULT_ROUND_TO = 10.0 # Coverage threshold (× LT × avg_weekly) used for BOTH: # • Critical floor — SKUs whose projected cover < 1.5×LT are NEVER # deferred even if the ABC truck overshoots 33 pallets. # • Pull-forward eligibility — non-triggered SKUs with projected cover # > 1.5×LT are safe to pull in to fill an under-loaded truck. # Same number, symmetric semantics: 1.5×LT divides "needs ordering soon" # from "comfortably stocked". SAFE_COVER_MULT = 1.5 # (s, S) thresholds — uniform across tiers per user spec. # Trigger and target are the same multiplier: when projected coverage at # W+LT drops below 1.5×LT × weekly forecast, order enough to bring it # back up to 1.5×LT × weekly forecast. No tier differentiation — Gold, # Silver, Bronze all treated the same way for now. TRIGGER_MULT = 1.5 TARGET_MULT = 1.5 def _load_moq_map() -> dict[str, dict]: """SKU → {moq_pieces, moq_pallets, is_eol} from moq_master.csv. This is the curated MOQ source — `supply_master.moq` covers only a fraction of SKUs (3 of 179 for ABC at audit time), so we read the CSV directly and fall back to supply_master.moq when the CSV is silent. `is_eol` flag pulls a SKU out of the proposal entirely.""" if not MOQ_CSV.exists(): return {} df = pd.read_csv(MOQ_CSV) out: dict[str, dict] = {} for _, r in df.iterrows(): sku = str(r.get("sku") or "").strip() if not sku: continue def _f(value) -> Optional[float]: try: return float(value) if pd.notna(value) and float(value) > 0 else None except (TypeError, ValueError): return None out[sku] = { "moq_pieces": _f(r.get("moq_pieces")), "moq_pallets": _f(r.get("moq_pallets")), "is_eol": bool(r.get("is_eol", False)), } return out def _load_logistic_map() -> dict[str, dict]: """SKU → {pcs_per_pallet, pcs_per_box} from logistic_master.csv. Empty dict for SKUs not in the file — caller falls back to defaults.""" if not LOGISTIC_CSV.exists(): return {} df = pd.read_csv(LOGISTIC_CSV) out: dict[str, dict] = {} def _f(value) -> Optional[float]: try: return float(value) if pd.notna(value) and float(value) > 0 else None except (TypeError, ValueError): return None for _, r in df.iterrows(): sku = str(r.get("sku") or "").strip() if not sku: continue out[sku] = { "pcs_per_pallet": _f(r.get("pcs_per_pallet")), "pcs_per_box": _f(r.get("pcs_per_box")), } return out def _truck_status(total_pallets: float) -> str: if TRUCK_GREEN_LOW <= total_pallets <= TRUCK_GREEN_HIGH: return "green" if (TRUCK_YELLOW_LOW <= total_pallets < TRUCK_GREEN_LOW or TRUCK_GREEN_HIGH < total_pallets <= TRUCK_YELLOW_HIGH): return "yellow" return "red" def _build_demand_per_week( *, repo: SupplyRepository, product_ids: list[int], horizon: list[tuple[int, int]], ) -> dict[tuple[int, int, int], float]: """Per (product, year, week) demand for the supplied horizon. Cascade: 1. live forecasts.total, 2. per-SKU 13w non-promo run rate as fallback.""" fc_map = repo.get_forecast_demand_horizon(product_ids=product_ids, horizon=horizon) rr_map = repo.get_avg_weekly_demand(product_ids=product_ids) out: dict[tuple[int, int, int], float] = {} for pid in product_ids: run_rate = float(rr_map.get(pid, 0.0)) for (y, w) in horizon: key = (pid, y, w) out[key] = float(fc_map.get(key, run_rate)) return out class OrderProposalService: """Build (s, S) proposals per supplier.""" def __init__(self, db: Session): self.db = db self.repo = SupplyRepository(db) # ------------------------------------------------------------------ def list_suppliers(self) -> list[dict]: rows = self.repo.get_suppliers_for_proposal() out = [] for r in rows: name = str(r["supplier_name"]) out.append({ "supplier_id": int(r["supplier_id"]), "supplier_name": name, "n_planned_skus": int(r["n_planned_skus"]), "avg_lead_time_weeks": float(r["avg_lead_time_weeks"]) if r.get("avg_lead_time_weeks") is not None else None, "is_abc": ABC_SUPPLIER_PATTERN in name.upper(), }) return out # ------------------------------------------------------------------ def build_proposal(self, *, supplier_id: int) -> dict: skus = self.repo.get_supplier_planned_skus(supplier_id=supplier_id) if not skus: return self._empty(supplier_id=supplier_id) supplier_name = self._resolve_supplier_name(supplier_id) is_abc = ABC_SUPPLIER_PATTERN in supplier_name.upper() product_ids = [int(s["product_id"]) for s in skus] # Horizon length = max lead time across this supplier's SKUs, clamped # so we always cover at least 4 weeks and at most 13 weeks of forecast. max_lt = max(int(round(s["lead_time_weeks"])) for s in skus) horizon_weeks = max(4, min(13, max_lt + 1)) horizon = self.repo.get_horizon_weeks(n_weeks=horizon_weeks) current_year, current_week = horizon[0] if horizon else (0, 0) # ── Data sources (loaded once per request) ────────────────────── demand_map = _build_demand_per_week( repo=self.repo, product_ids=product_ids, horizon=horizon, ) # Incoming POs from ALL suppliers (per spec) — projected stock walk # needs every inbound, not just the supplier being ordered from. incoming_map = self.repo.get_incoming_horizon( product_ids=product_ids, horizon=horizon, ) logistic_map = _load_logistic_map() moq_map = _load_moq_map() # ── Per-SKU walk forward + (s, S) calculation ─────────────────── rows: list[dict] = [] n_eol_skipped = 0 for s in skus: # Skip EOL SKUs entirely — never order again per moq_master.csv flag. moq_info = moq_map.get(s["sku"], {}) if moq_info.get("is_eol"): n_eol_skipped += 1 continue pid = int(s["product_id"]) lt = max(1, int(round(float(s["lead_time_weeks"])))) # Uniform 1.5×LT trigger + target across all tiers per spec. trigger_mult = TRIGGER_MULT target_mult = TARGET_MULT # Walk-forward closing stock for the LT window. opening = float(s["current_wh_stock"] or 0) week_idx = 0 projected = opening for (y, w) in horizon[:lt]: demand = float(demand_map.get((pid, y, w), 0.0)) incoming = float(incoming_map.get((pid, y, w), 0.0)) opening = max(0.0, opening - demand + incoming) projected = opening week_idx += 1 # Demand summed over the LT window — uses the REAL per-week # forecast values from `demand_map` (which itself cascades: # forecasts.total → 13w non-promo run-rate fallback). No # averaging in the math: trigger/target/safe are direct # multiples of this LT-window sum. demand_lt_sum = sum( float(demand_map.get((pid, y, w), 0.0)) for (y, w) in horizon[:lt] ) reorder_point = trigger_mult * demand_lt_sum target_stock = target_mult * demand_lt_sum triggered = projected < reorder_point and demand_lt_sum > 0 # ── Order qty calc (rounded UP to package multiple, MOQ floor) ─ # Round-to source per spec: # 1. pcs_per_box from logistic_master (curated pack size) — # ONLY when > 1. A pcs_per_box of 0 or 1 means "single # units" which produces ugly PO numbers like 194; treat # it as no-pack-data and use DEFAULT_ROUND_TO instead. # 2. fallback DEFAULT_ROUND_TO = 10 (tidy PO numbers) # MOQ cascade: moq_master.csv (curated, ~95% ABC coverage) → # supply_master.moq fallback (sparse) → 0 = no floor. moq_pieces = moq_info.get("moq_pieces") if moq_info else None sm_moq = float(s["moq"] or 0) moq = float(moq_pieces) if moq_pieces else sm_moq log_info = logistic_map.get(s["sku"], {}) ppp = log_info.get("pcs_per_pallet") pcs_per_box = log_info.get("pcs_per_box") pkg = pcs_per_box if (pcs_per_box and pcs_per_box > 1) else DEFAULT_ROUND_TO if triggered: raw_qty = max(0.0, target_stock - projected) # Round UP to package multiple order_qty = math.ceil(raw_qty / pkg) * pkg if pkg > 0 else raw_qty if order_qty < moq: order_qty = moq else: raw_qty = 0.0 order_qty = 0.0 # ── Stockout-protection flags (used by ABC truck adjustment) ─── # Single threshold at 1.5 × sum-of-forecast-over-LT-window splits # the universe: # projected < 1.5×demand_lt → critical (cannot be deferred) # projected > 1.5×demand_lt → safe (eligible for pull-forward) safe_threshold = SAFE_COVER_MULT * demand_lt_sum is_critical = demand_lt_sum > 0 and projected < safe_threshold has_safe_buffer = demand_lt_sum > 0 and projected > safe_threshold pallets = (math.ceil(order_qty / ppp) if (ppp and order_qty > 0) else None) cost = float(s["cost_price"] or 0) or None order_value = (order_qty * cost) if (cost and order_qty > 0) else None rows.append({ "product_id": pid, "sku": s["sku"], "name": s.get("name"), "tier": s.get("tier"), "category": s.get("category"), "current_wh_stock": round(float(s["current_wh_stock"] or 0), 2), "projected_stock_at_w_lt": round(projected, 2), "demand_lt_window": round(demand_lt_sum, 2), "avg_weekly_demand_lt": round(demand_lt_sum / lt, 2) if lt > 0 else 0.0, "lead_time_weeks": float(lt), "reorder_point": round(reorder_point, 2), "target_stock": round(target_stock, 2), "raw_qty": round(raw_qty, 2), "proposed_order_qty": round(order_qty, 2), "moq": moq if moq > 0 else None, "package_size": pkg if pkg > 0 else None, "pcs_per_pallet": ppp, "pallets": round(pallets, 2) if pallets else None, "cost_price": cost, "order_value_eur": round(order_value, 2) if order_value else None, "triggered": triggered, "pull_forward_reason": None, # Internal flags used by ABC truck adjustment — not exposed # in the response schema, but the dict is reused for the # _adjust_for_abc_truck pass. "_is_critical": is_critical, "_has_safe_buffer": has_safe_buffer, }) # ── ABC truck packing adjustment ───────────────────────────────── abc_truck = None if is_abc: abc_truck = self._adjust_for_abc_truck(rows) # ── Final filter: ship only rows with proposed_order_qty > 0 ───── # Triggered + ABC pull-forward are both kept; deferred get qty 0 # back so they drop out here. Strip internal `_*` flags that were # only used by the ABC adjustment pass — they're not in the API # schema and would cause Pydantic to drop them silently / warn. shipped = [ {k: v for k, v in r.items() if not k.startswith("_")} for r in rows if r["proposed_order_qty"] > 0 ] shipped.sort(key=lambda r: ( r.get("tier") or "", -float(r["proposed_order_qty"]) )) total_value = sum( float(r["order_value_eur"]) for r in shipped if r.get("order_value_eur") ) total_pallets = sum( float(r["pallets"]) for r in shipped if r.get("pallets") ) if abc_truck is not None: abc_truck["proposed_pallets"] = round(total_pallets, 2) abc_truck["fill_status"] = _truck_status(total_pallets) n_triggered = sum(1 for r in rows if r["triggered"]) return { "supplier_id": supplier_id, "supplier_name": supplier_name, "current_year": current_year, "current_week": current_week, "horizon_weeks": horizon_weeks, "n_skus_total": len(skus), "n_skus_triggered": n_triggered, "rows": shipped, "total_order_value_eur":round(total_value, 2), "total_pallets": round(total_pallets, 2), "abc_truck": abc_truck, "methodology_notes": self._methodology_note(is_abc), } # ------------------------------------------------------------------ def _adjust_for_abc_truck(self, rows: list[dict]) -> dict: """Pull forward / defer to land near 33 pallets. Mutates rows. Returns a partial abc_truck dict (proposed_pallets + fill_status are filled in by the caller after the final tally).""" def pallets_of(r: dict) -> float: return float(r.get("pallets") or 0) triggered = [r for r in rows if r["triggered"] and r["proposed_order_qty"] > 0] # Pull-forward candidate pool: only non-triggered SKUs whose projected # cover at W+LT is > 1.5×LT (safe buffer). Pulling in a SKU that's # already close to its trigger would just push the same problem # forward by a week and risk overstocking next month. non_triggered = [r for r in rows if not r["triggered"] and r.get("_has_safe_buffer")] total_p = sum(pallets_of(r) for r in triggered) pulled = 0 deferred = 0 n_critical_protected = 0 note_bits: list[str] = [] if total_p < TRUCK_TARGET_PALLETS: # Sort safe-buffer non-triggered by closest-to-trigger first. # Among safe SKUs we still prefer the ones nearest to needing # an order — keeps inventory rotation tight. def buffer_score(r: dict) -> float: proj = float(r["projected_stock_at_w_lt"]) rop = float(r["reorder_point"]) return proj - rop # positive = buffer above reorder candidates = sorted(non_triggered, key=buffer_score) for r in candidates: if total_p >= TRUCK_TARGET_PALLETS: break # Pull this SKU forward — order to target_stock from current # projected level. raw = max(0.0, float(r["target_stock"]) - float(r["projected_stock_at_w_lt"])) pkg = float(r.get("package_size") or 1) qty = math.ceil(raw / pkg) * pkg if pkg > 0 else raw moq = float(r.get("moq") or 0) if qty < moq: qty = moq if qty <= 0: continue ppp = r.get("pcs_per_pallet") pallets = math.ceil(qty / ppp) if ppp else 0 if not pallets: continue cost = r.get("cost_price") r["proposed_order_qty"] = round(qty, 2) r["raw_qty"] = round(raw, 2) r["pallets"] = round(pallets, 2) r["order_value_eur"] = round(qty * cost, 2) if cost else None r["pull_forward_reason"] = "pulled forward to fill ABC truck" total_p += pallets pulled += 1 if pulled: note_bits.append(f"Pulled forward {pulled} SKUs to reach {TRUCK_TARGET_PALLETS} pallets.") elif total_p > TRUCK_TARGET_PALLETS: # Defer triggered SKUs with the largest BUFFER ABOVE reorder # point (most slack) until we land in the green band [31, 35]. # Critical SKUs (projected < 1.5×LT × avg_weekly) are NEVER # deferred — they'd risk stockout before next order cycle. # Skip a candidate if dropping it would undershoot the green # floor (TRUCK_GREEN_LOW). candidates = sorted( [r for r in triggered if not r.get("_is_critical")], key=lambda r: float(r["projected_stock_at_w_lt"]) - float(r["reorder_point"]), reverse=True, ) n_critical_protected = sum(1 for r in triggered if r.get("_is_critical")) for r in candidates: if total_p <= TRUCK_TARGET_PALLETS: break p = pallets_of(r) if p <= 0: continue if (total_p - p) < TRUCK_GREEN_LOW: # Skip — dropping this SKU would put us below the green # floor. Try the next (smaller) candidate. continue # Drop this SKU from the proposal — defer to next week. r["proposed_order_qty"] = 0.0 r["raw_qty"] = 0.0 r["pallets"] = None r["order_value_eur"] = None r["pull_forward_reason"]= "deferred to next week (truck overfull)" total_p -= p deferred += 1 if deferred: note_bits.append(f"Deferred {deferred} SKUs to next week to land near {TRUCK_TARGET_PALLETS} pallets.") if n_critical_protected: note_bits.append( f"{n_critical_protected} SKU(s) kept in proposal despite truck overflow — " f"projected cover < {SAFE_COVER_MULT}×LT, deferring would risk stockout." ) if not note_bits: note_bits.append(f"Triggered SKUs fit naturally near {TRUCK_TARGET_PALLETS} pallets — no adjustment.") return { "target_pallets": TRUCK_TARGET_PALLETS, "proposed_pallets": 0.0, # filled in by caller after final tally "fill_status": "green", "n_skus_pulled_forward": pulled, "n_skus_deferred": deferred, "note": " ".join(note_bits), } # ------------------------------------------------------------------ def _resolve_supplier_name(self, supplier_id: int) -> str: from sqlalchemy import text r = self.db.execute( text("SELECT name FROM dim_suppliers WHERE id = :sid"), {"sid": supplier_id}, ).first() return str(r[0]) if r and r[0] else f"Supplier #{supplier_id}" def _empty(self, *, supplier_id: int) -> dict: return { "supplier_id": supplier_id, "supplier_name": self._resolve_supplier_name(supplier_id), "current_year": 0, "current_week": 0, "horizon_weeks": 0, "n_skus_total": 0, "n_skus_triggered": 0, "rows": [], "total_order_value_eur":0.0, "total_pallets": 0.0, "abc_truck": None, "methodology_notes": "No planned SKUs configured for this supplier.", } def _methodology_note(self, is_abc: bool) -> str: parts = [ "(s, S) reorder: trigger × LT × avg_weekly < projected_stock_at_W+LT.", f"Uniform trigger/target: {TRIGGER_MULT}×LT × weekly forecast (no tier differentiation).", "Projected stock walks forward over LT weeks using forecasts + all incoming POs.", "Order qty rounded up to package/MOQ multiple; MOQ enforced as floor.", ] if is_abc: parts.append( f"ABC truck packing targets {TRUCK_TARGET_PALLETS} pallets — " "pull forward non-triggered SKUs to fill, or defer to next week if over." ) return " ".join(parts) # ------------------------------------------------------------------ # Excel export # ------------------------------------------------------------------ def build_xlsx(self, *, supplier_id: int) -> bytes: """Build the Excel PO for a supplier. One sheet of rows, ready to forward to the supplier as a purchase order.""" proposal = self.build_proposal(supplier_id=supplier_id) rows = proposal["rows"] from openpyxl import Workbook wb = Workbook() ws = wb.active ws.title = "Order Proposal" headers = [ "SKU", "Name", "Tier", "Category", "Current WH stock", "Projected @ W+LT", "Demand LT window", "Lead time (w)", "Reorder point", "Target stock", "Order qty", "MOQ", "Package", "Pallets", "Cost / unit", "Order value €", "Note", ] ws.append(headers) for r in rows: ws.append([ r["sku"], r.get("name") or "", r.get("tier") or "", r.get("category") or "", r["current_wh_stock"], r["projected_stock_at_w_lt"], r["demand_lt_window"], r["lead_time_weeks"], r["reorder_point"], r["target_stock"], r["proposed_order_qty"], r.get("moq"), r.get("package_size"), r.get("pallets"), r.get("cost_price"), r.get("order_value_eur"), r.get("pull_forward_reason") or "", ]) # Summary row ws.append([]) ws.append(["TOTAL", "", "", "", "", "", "", "", "", "", "", "", "", proposal["total_pallets"], "", proposal["total_order_value_eur"], ""]) if proposal.get("abc_truck"): t = proposal["abc_truck"] ws.append([]) ws.append([f"ABC TRUCK: {t['proposed_pallets']:.1f} pallets " f"(target {t['target_pallets']}, status {t['fill_status']})"]) import io buf = io.BytesIO() wb.save(buf) return buf.getvalue()