"""/api/supply/* router. Thin layer: parse query params, call SupplyService, return. Health probe is kept so deployment smoke tests work without touching the DB. """ from __future__ import annotations from typing import Optional from fastapi import APIRouter, Body, Depends, File, HTTPException, Query, UploadFile from fastapi.responses import Response from sqlalchemy.orm import Session from backend.models.database import get_db from backend.api.middleware.auth import CurrentUser, require_ingest_or_section, require_section from backend.schemas.supply import ( AlertsResponse, CoverageResponse, CostsResponse, IncomingUploadResponse, InventoryHealthResponse, LogisticsResponse, LogisticsMasterResponse, MoqAuditResponse, MoqResponse, OrderProposalExportRequest, OrderProposalInput, OrderProposalResponse, OrderProposalRow, OrderProposalSaveResponse, OrderProposalSupplier, OrderSuggestionsResponse, PalletFlowResponse, ProjectionResponse, ScenarioExportRequest, ScenarioRequest, ScenarioResponse, SettingsResponse, SettingsUpdateItem, SettingsUpdateResponse, StoreOverstockResponse, SupplyDashboard, TruckPlanResponse, ) from backend.services import scenario_service from backend.services.logistics_service import LogisticsService from backend.services.order_proposal_service import OrderProposalService from backend.services.supply_service import SupplyService router = APIRouter(prefix="/supply", tags=["supply"]) @router.get("/health") def health() -> dict: return {"status": "ok"} @router.post("/upload-incoming", response_model=IncomingUploadResponse) async def upload_incoming( file: UploadFile = File(..., description="Incoming-PO matrix .xlsx (SKU × week)"), year: int = Query(..., description="Year to stamp on every week in the file"), db: Session = Depends(get_db), _: CurrentUser = Depends(require_ingest_or_section("upload_incoming")), ) -> IncomingUploadResponse: """Upload incoming POs as a SKU × week quantity matrix. The file fully REPLACES incoming_supply (Stock Projection / Coverage / Scenario all read from it). The file carries only week numbers, so `year` stamps them.""" name = file.filename or "incoming.xlsx" if not name.lower().endswith((".xlsx", ".xls")): raise HTTPException(status_code=400, detail=f"'{name}' is not an Excel workbook") blob = await file.read() if not blob: raise HTTPException(status_code=400, detail="File is empty") from backend.services.upload_po_service import process_incoming_po_upload try: result = process_incoming_po_upload(db, name, blob, year) except ValueError as exc: raise HTTPException(status_code=400, detail=str(exc)) return IncomingUploadResponse(**result) @router.get("/dashboard", response_model=SupplyDashboard) def get_supply_dashboard(db: Session = Depends(get_db)) -> SupplyDashboard: """Stock summary + 13-week demand/incoming roll-forward at company level. The horizon starts at max(year, week)+1 from v_sales_weekly_full so the projection picks up the week *after* the most recent actuals. """ return SupplyService(db).get_supply_dashboard() @router.get("/projection", response_model=ProjectionResponse) def get_stock_projection( category: list[str] = Query(default=[], description="Category name (exact, repeat for multi)"), tier: list[str] = Query(default=[], description="GOLD / SILVER / BRONZE (partial match)"), xyz: list[str] = Query(default=[], description="X / Y / Z exact match"), sku: list[str] = Query(default=[], description="Restrict to specific SKU codes"), exclude_kam: list[str] = Query(default=[], description="Person/KAM display names to exclude"), exclude_buyer: list[str] = Query(default=[], description="Buyer names to exclude"), db: Session = Depends(get_db), ) -> ProjectionResponse: """Per-SKU 13-week roll-forward with optional KAM/buyer exclusion of on-top demand. Filters are AND-combined; excluded_kam and excluded_buyer are OR-combined inside the SQL (a row is excluded if it matches EITHER list). """ return SupplyService(db).get_stock_projection_data( category=category or None, tier=tier or None, xyz=xyz or None, sku=sku or None, excluded_kams=exclude_kam or None, excluded_buyers=exclude_buyer or None, ) @router.get("/coverage", response_model=CoverageResponse) def get_coverage( category: list[str] = Query(default=[]), tier: list[str] = Query(default=[]), xyz: list[str] = Query(default=[]), sku: list[str] = Query(default=[]), db: Session = Depends(get_db), ) -> CoverageResponse: """Coverage workbook — per-SKU 13-week roll-forward shaped for the wide grid (stock/demand/incoming/closing/coverage rows per SKU).""" return SupplyService(db).get_coverage_data( category=category or None, tier=tier or None, xyz=xyz or None, sku=sku or None, ) @router.get("/coverage/export") def export_coverage( category: list[str] = Query(default=[]), tier: list[str] = Query(default=[]), xyz: list[str] = Query(default=[]), sku: list[str] = Query(default=[]), db: Session = Depends(get_db), ) -> Response: """Download the coverage workbook as an Excel file. Honors the same tier/category/XYZ filters as the grid; an optional `sku` list lets the UI export exactly the rows currently visible (after search / color narrowing).""" xlsx = SupplyService(db).build_coverage_xlsx( category=category or None, tier=tier or None, xyz=xyz or None, sku=sku or None, ) from datetime import datetime as _dt fname = f"coverage_{_dt.now().strftime('%Y%m%d_%H%M')}.xlsx" return Response( content=xlsx, media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", headers={"Content-Disposition": f'attachment; filename="{fname}"'}, ) @router.get("/alerts", response_model=AlertsResponse) def get_alerts( category: list[str] = Query(default=[]), tier: list[str] = Query(default=[]), xyz: list[str] = Query(default=[]), db: Session = Depends(get_db), ) -> AlertsResponse: """Categorized reorder alerts — Order Now / Order Soon / Pull in. Uses lead-time-aware coverage; falls back to LT=2w when supply_master is missing a row.""" return SupplyService(db).get_alerts( category=category or None, tier=tier or None, xyz=xyz or None, ) @router.get("/order-suggestions", response_model=OrderSuggestionsResponse) def get_order_suggestions( category: list[str] = Query(default=[]), tier: list[str] = Query(default=[]), xyz: list[str] = Query(default=[]), db: Session = Depends(get_db), ) -> OrderSuggestionsResponse: """Pre-filled order quantities for the Order Entry page. Returns only SKUs where suggested_qty > 0 — i.e. an action is recommended.""" return SupplyService(db).get_order_suggestions( category=category or None, tier=tier or None, xyz=xyz or None, ) @router.get("/inventory-health", response_model=InventoryHealthResponse) def get_inventory_health( tier: list[str] = Query(default=[]), category: list[str] = Query(default=[]), status: Optional[str] = Query(None, pattern="^(overstock|stockout_risk|balanced|all)$"), planned_only: bool = Query(False, description="Restrict to SKUs in sku_planning"), fa_global: float = Query(0.70, ge=0.30, le=0.95), fa_min_weeks: int = Query(6, ge=2, le=26), db: Session = Depends(get_db), ) -> InventoryHealthResponse: """Inventory health (reporting tool — covers planned + unplanned). Three statuses ONLY: overstock / stockout_risk / balanced. Matches Streamlit's `page_supply_inventory_health` formula verbatim. Demand signal is from forecasts.total for planned SKUs with a forecast row; falls back to a non-promo run rate for unplanned SKUs and for planned SKUs without forecast data.""" return SupplyService(db).get_inventory_health( tier=tier or None, category=category or None, status=status, planned_only=planned_only, fa_global=fa_global, fa_min_weeks=fa_min_weeks, ) @router.get("/store-overstock", response_model=StoreOverstockResponse) def get_store_overstock( tier: list[str] = Query(default=["GOLD", "SILVER", "BRONZE"]), country: str = Query("HR", pattern="^[A-Z]{2}$"), db: Session = Depends(get_db), ) -> StoreOverstockResponse: """HR per-store overstock for planned SKUs only. Matches `page_supply_store_overstock`: LT=1, safety_stock = Z×σ×√1, cycle_stock = avg_weekly, optimal = safety+cycle. σ from non-promo HR retail sales; pairs with < 4 non-promo weeks flagged.""" return SupplyService(db).get_store_overstock( tier=tier or None, country=country, ) @router.post("/orders", response_model=OrderProposalSaveResponse) def save_order_proposals( proposals: list[OrderProposalInput], db: Session = Depends(get_db), ) -> OrderProposalSaveResponse: """Bulk-insert order_proposals rows. Per-row outcomes (created / skipped / error) so the frontend can paint a confirmation table. Auth/audit: no user attribution yet — approved_by_id stays NULL until JWT-based auth is wired in.""" return SupplyService(db).save_order_proposals( [p.model_dump() for p in proposals] ) @router.get("/moq-analysis", response_model=MoqResponse) def get_moq_analysis( tier: list[str] = Query(default=[]), category: list[str] = Query(default=[]), db: Session = Depends(get_db), ) -> MoqResponse: """MOQ analysis for planned SKUs — supply_master joined with 13-week run rates. weeks_per_moq = moq / avg_weekly_demand shows how many weeks of demand one minimum order covers.""" return SupplyService(db).get_moq_analysis( tier=tier or None, category=category or None, ) @router.get("/logistics", response_model=LogisticsResponse) def get_logistics( future_only: bool = Query(True, description="Exclude past deliveries"), db: Session = Depends(get_db), ) -> LogisticsResponse: """Incoming supply timeline grouped by supplier. future_only=true (default) restricts to deliveries at or after the current planning horizon start week.""" return SupplyService(db).get_logistics(future_only=future_only) @router.get("/logistics/master", response_model=LogisticsMasterResponse) def get_logistics_master(db: Session = Depends(get_db)) -> LogisticsMasterResponse: """Per-SKU pallet master data from `data/logistic_master.csv` + coverage stats vs. all SKUs in the system. Includes category-average fallback table so callers know what ppp is used when direct data is missing.""" return LogisticsService(db).get_master() @router.get("/logistics/pallet-flow", response_model=PalletFlowResponse) def get_pallet_flow( n_weeks: int = Query(13, ge=1, le=26, description="Forward weeks to project"), db: Session = Depends(get_db), ) -> PalletFlowResponse: """Per-week pallet IN (ABC + non-ABC) vs OUT (forecast demand) for the next `n_weeks` weeks. Includes ABC truck count + overrun flag.""" return LogisticsService(db).get_pallet_flow(n_weeks=n_weeks) @router.get("/logistics/truck-plan", response_model=TruckPlanResponse) def get_truck_plan( n_weeks: int = Query(13, ge=1, le=26, description="Forward weeks for truck planning"), db: Session = Depends(get_db), ) -> TruckPlanResponse: """Pack open ABC POs into trucks (33 pallets each, full only, max 2/week). POs in the same truck share a delivery week.""" return LogisticsService(db).get_truck_plan(n_weeks=n_weeks) @router.get("/logistics/moq-audit", response_model=MoqAuditResponse) def get_moq_audit(db: Session = Depends(get_db)) -> MoqAuditResponse: """Compare every open ABC PO against pallet capacity + MOQ rules. Surfaces partial-pallet orders, below-MOQ violations, EOL re-orders, and a recommended rounded qty per PO.""" return LogisticsService(db).get_moq_audit() @router.get("/costs", response_model=CostsResponse) def get_costs( tier: list[str] = Query(default=[]), category: list[str] = Query(default=[]), planned_only: bool = Query(False), db: Session = Depends(get_db), ) -> CostsResponse: """Cost and margin analysis — erp_costs joined with erp_prices and current stock. planned_only restricts to Gold/Silver/Bronze SKUs.""" return SupplyService(db).get_costs( tier=tier or None, category=category or None, planned_only=planned_only, ) @router.get("/settings", response_model=SettingsResponse) def get_settings(db: Session = Depends(get_db)) -> SettingsResponse: """Read supply_master (lead time + MOQ) for all planned SKUs. SKUs without a supply_master row appear with null values so the planner can see what's missing.""" return SupplyService(db).get_settings() @router.put("/settings", response_model=SettingsUpdateResponse) def update_settings( updates: list[SettingsUpdateItem], db: Session = Depends(get_db), ) -> SettingsUpdateResponse: """Upsert lead_time_weeks and/or moq in supply_master for a list of SKUs. Inserts a new row when none exists; updates fields that are supplied (null fields are skipped). Returns per-row outcome counts.""" return SupplyService(db).update_settings( [u.model_dump() for u in updates] ) # ────────────────────────────────────────────────────────────────────── # Scenario Planner (Streamlit page_supply_scenarios parity) # ────────────────────────────────────────────────────────────────────── def _scenario_params_from(body: ScenarioRequest) -> scenario_service.ScenarioParams: overrides_map: dict[str, dict] = {} for ov in body.overrides: key = f"{ov.sku}|{ov.po_year}|{ov.po_week}" overrides_map[key] = {"action": ov.action, "new_cw": ov.new_cw} return scenario_service.ScenarioParams( suppliers=body.suppliers, window_start_cw=int(body.window_start_cw), window_end_cw=int(body.window_end_cw), stock_basis=body.stock_basis, postpone_trigger=float(body.policy.postpone_trigger), postpone_delay=int(body.policy.postpone_delay), cancel_on=bool(body.policy.cancel_on), cancel_threshold=float(body.policy.cancel_threshold), target_eur=float(body.policy.target_eur), excluded_buyers=list(body.excluded_buyers), overrides=overrides_map, ) @router.post("/scenarios", response_model=ScenarioResponse) def post_scenarios( body: ScenarioRequest, db: Session = Depends(get_db), ) -> ScenarioResponse: """Compute the scenario for the given filters + policy + overrides. Idempotent: same inputs → same response. Per-PO actions, KPI strip, baseline vs scenario cash projection, and the list of sanity-check flips are all included.""" data = scenario_service.load_scenario_data(db) params = _scenario_params_from(body) return scenario_service.compute_scenario(data, params) @router.post("/scenarios/inputs") def get_scenario_inputs(db: Session = Depends(get_db)) -> dict: """Lookup data for populating the page: supplier list, VP buyer list, current ISO week. Called once on page mount.""" data = scenario_service.load_scenario_data(db) return { "current_year": data.cur_year, "current_week": data.cur_week, "suppliers": data.suppliers, "vp_buyers": data.all_vp_buyers, "n_incoming_pos": len(data.incoming), } @router.post("/scenarios/export") def post_scenarios_export( body: ScenarioExportRequest, db: Session = Depends(get_db), ) -> Response: """Recompute the scenario and return an Excel blob with the requested action list (CANCEL or POSTPONE), supplier-ready format.""" action = body.action.upper() if action not in ("CANCEL", "POSTPONE"): raise HTTPException(status_code=400, detail="action must be CANCEL or POSTPONE") data = scenario_service.load_scenario_data(db) params = _scenario_params_from(body.scenario) result = scenario_service.compute_scenario(data, params) xlsx = scenario_service.build_supplier_xlsx(result["per_po"], action) if not xlsx: raise HTTPException(status_code=404, detail=f"No POs flagged {action}") from datetime import datetime as _dt fname = f"po_{action.lower()}_list_{_dt.now().strftime('%Y%m%d_%H%M')}.xlsx" return Response( content=xlsx, media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", headers={"Content-Disposition": f'attachment; filename="{fname}"'}, ) # ---------------------------------------------------------------------- # Order Proposal — supplier-scoped (s, S) ordering tool. Replaces the # old Reorder Alerts page. Role-gated on `order_proposal` (Admin + Nabava). # ---------------------------------------------------------------------- _REQ_ORDER_PROPOSAL = Depends(require_section("order_proposal")) @router.get("/order-proposal/suppliers", response_model=list[OrderProposalSupplier]) def get_order_proposal_suppliers( db: Session = Depends(get_db), _: CurrentUser = _REQ_ORDER_PROPOSAL, ) -> list[OrderProposalSupplier]: """Suppliers eligible for an order proposal — i.e. those with planned SKUs + a populated lead time. Used to populate the dropdown on /supply/order-proposal.""" return OrderProposalService(db).list_suppliers() @router.get("/order-proposal", response_model=OrderProposalResponse) def get_order_proposal( supplier_id: int = Query(..., description="dim_suppliers.id"), db: Session = Depends(get_db), _: CurrentUser = _REQ_ORDER_PROPOSAL, ) -> OrderProposalResponse: """Build the weekly order proposal for one supplier. Walks the projected stock forward over the supplier-specific lead time, flags SKUs whose projected stock at W+LT falls below their tier-aware reorder point, and proposes an order qty that lifts projected stock to the tier-aware target. Includes the ABC truck-fill summary when the supplier matches ABC Nutritional.""" return OrderProposalService(db).build_proposal(supplier_id=supplier_id) @router.post("/order-proposal/export") def post_order_proposal_export( body: OrderProposalExportRequest, db: Session = Depends(get_db), _: CurrentUser = _REQ_ORDER_PROPOSAL, ) -> Response: """Excel blob of the order proposal for the given supplier — server recomputes so the export reflects current stock + forecast + incoming.""" xlsx = OrderProposalService(db).build_xlsx(supplier_id=body.supplier_id) if not xlsx: raise HTTPException(status_code=404, detail="Empty proposal") from datetime import datetime as _dt fname = f"order_proposal_{body.supplier_id}_{_dt.now().strftime('%Y%m%d_%H%M')}.xlsx" return Response( content=xlsx, media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", headers={"Content-Disposition": f'attachment; filename="{fname}"'}, )