import uuid from datetime import datetime, timezone from decimal import Decimal from collections import defaultdict from sqlalchemy.ext.asyncio import AsyncSession from sqlalchemy import select, func from sqlalchemy.orm import selectinload from fastapi import HTTPException from app.models.yard_models import ChassisMaster, VehicleModelMaster from app.models.rm_models import RmInventory, RmMaster, StationMaster, RmConsumptionLog, RmVendorMapping, VendorMaster, RmPurchaseOrder, RmPoDetail from app.models.procurement_models import RmPurchaseRequest, RmPurchaseRequestDetail, ChassisBomSnapshot from app.models.dealer_models import DealerOrderVehicle, DealerOrderVehicleFeature from app.models.bom_models import ProductMaster, BomMaster, SpecialFeatureBom now_utc = lambda: datetime.now(timezone.utc) class ProcurementEngineService: def __init__(self, db: AsyncSession): self.db = db async def move_to_check(self, chassis_ids: list[uuid.UUID]): """Move chassis to PROCUREMENT_PENDING status.""" for chassis_id in chassis_ids: chassis = await self.db.get(ChassisMaster, chassis_id) if not chassis: raise HTTPException(404, 'Chassis not found') if not chassis.dealer_order_vehicle_id: raise HTTPException(400, f'Chassis {chassis.chassis_no} has no Sales Order. Cannot proceed to procurement.') chassis.procurement_status = 'PROCUREMENT_PENDING' await self.db.flush() async def get_queue(self): """Get chassis that are waiting for procurement check or on hold.""" stmt = ( select(ChassisMaster) .where(ChassisMaster.procurement_status.in_(['PROCUREMENT_PENDING', 'SHORTAGE_HOLD', 'AWAITING_INBOUND_PO'])) .options( selectinload(ChassisMaster.dealer_order_vehicle).selectinload(DealerOrderVehicle.model), selectinload(ChassisMaster.dealer_order_vehicle).selectinload(DealerOrderVehicle.body_type), selectinload(ChassisMaster.dealer_order_vehicle).selectinload(DealerOrderVehicle.segment), selectinload(ChassisMaster.model) ) ) result = await self.db.execute(stmt) return result.scalars().all() async def get_confirmed_queue(self): """Get chassis that have been confirmed but NOT yet inducted to production.""" stmt = ( select(ChassisMaster) .where( ChassisMaster.procurement_status == 'PROCUREMENT_CHECKED', ChassisMaster.tracking_status == 'CHASSIS_IN' ) .options( selectinload(ChassisMaster.dealer_order_vehicle).selectinload(DealerOrderVehicle.model), selectinload(ChassisMaster.dealer_order_vehicle).selectinload(DealerOrderVehicle.body_type), selectinload(ChassisMaster.dealer_order_vehicle).selectinload(DealerOrderVehicle.segment), selectinload(ChassisMaster.model) ) ) result = await self.db.execute(stmt) return result.scalars().all() async def get_backflush_queue(self): """Get chassis that are confirmed and currently in production (WIP).""" stmt = ( select(ChassisMaster) .where( ChassisMaster.procurement_status == 'PROCUREMENT_CHECKED', ChassisMaster.tracking_status == 'WIP' ) .options( selectinload(ChassisMaster.dealer_order_vehicle).selectinload(DealerOrderVehicle.model), selectinload(ChassisMaster.dealer_order_vehicle).selectinload(DealerOrderVehicle.body_type), selectinload(ChassisMaster.dealer_order_vehicle).selectinload(DealerOrderVehicle.segment), selectinload(ChassisMaster.model) ) ) result = await self.db.execute(stmt) return result.scalars().all() async def get_chassis_bom(self, chassis_id: uuid.UUID): """Get the confirmed BOM snapshot for a chassis, grouped by station.""" chassis = await self.db.get(ChassisMaster, chassis_id) stmt = select(ChassisBomSnapshot, RmMaster, StationMaster).join( RmMaster, ChassisBomSnapshot.rm_id == RmMaster.rm_id ).outerjoin( StationMaster, RmMaster.station_id == StationMaster.station_id ).where(ChassisBomSnapshot.chassis_id == chassis_id) result = await self.db.execute(stmt) items = [] for snapshot, rm, station in result.all(): items.append({ "rm_id": snapshot.rm_id, "rm_name": rm.name, "rm_code": rm.part_no, "quantity": float(snapshot.quantity), "reserved_qty": float(snapshot.reserved_qty), "consumed_qty": float(snapshot.consumed_qty), "category": snapshot.category or "Base BOM", "station_name": station.station_name if station else "Unassigned", "sequence_no": station.sequence_no if station else 999 }) return { "chassis_id": str(chassis_id), "chassis_no": chassis.chassis_no if chassis else str(chassis_id), "bom_items": items } async def _get_aggregated_inventory(self, rm_ids: list) -> dict: """ BUG-02 FIX: Aggregate inventory across ALL stores per RM. Returns { rm_id: (total_current_qty, total_reserved_qty) } """ if not rm_ids: return {} agg_stmt = ( select( RmInventory.rm_id, func.sum(RmInventory.current_qty).label('total_current'), func.sum(RmInventory.reserved_qty).label('total_reserved'), func.sum(RmInventory.in_transit_qty).label('total_in_transit'), ) .where(RmInventory.rm_id.in_(rm_ids)) .group_by(RmInventory.rm_id) ) res = await self.db.execute(agg_stmt) return { row.rm_id: { 'current_qty': row.total_current or Decimal('0'), 'reserved_qty': row.total_reserved or Decimal('0'), 'in_transit_qty': row.total_in_transit or Decimal('0'), } for row in res.all() } async def _get_first_inventory_row(self, rm_id: uuid.UUID): """Get the first inventory row for an RM to use for locking/modifying reserved_qty.""" inv_stmt = select(RmInventory).where(RmInventory.rm_id == rm_id).with_for_update().limit(1) inv_res = await self.db.execute(inv_stmt) return inv_res.scalar_one_or_none() async def simulate_check(self, chassis_id: uuid.UUID): """Simulate the BOM check against available stock based on Sales Order.""" chassis = await self.db.get(ChassisMaster, chassis_id) if not chassis or not chassis.dealer_order_vehicle_id: raise HTTPException(404, 'Chassis or Sales Order not found') dov = await self.db.get(DealerOrderVehicle, chassis.dealer_order_vehicle_id) if not dov: raise HTTPException(404, 'Dealer Order Vehicle not found') from app.models.dealer_models import BodyTypeMaster, CustomerSegmentMaster model = await self.db.get(VehicleModelMaster, dov.model_id) body_type = await self.db.get(BodyTypeMaster, dov.body_type_id) if dov.body_type_id else None segment = await self.db.get(CustomerSegmentMaster, dov.segment_id) if dov.segment_id else None # Dictionary to aggregate requirements by (rm_id, category) consolidated = {} # 1. Base BOM — spec §5 Step 1: fuzzy match on model + body_type + segment if model: prod_stmt = select(ProductMaster).where(ProductMaster.name.ilike(f"%{model.model_number}%")) if body_type: prod_stmt = prod_stmt.where(ProductMaster.name.ilike(f"%{body_type.name}%")) if segment: prod_stmt = prod_stmt.where(ProductMaster.name.ilike(f"%{segment.name}%")) prod_res = await self.db.execute(prod_stmt) product = prod_res.scalars().first() if not product: raise HTTPException(400, f"No Product Match found for {body_type.name if body_type else 'N/A'} - {segment.name if segment else 'N/A'} - {model.model_number}. Update ProductMaster.") bom_stmt = select(BomMaster).where(BomMaster.product_id == product.product_id).options(selectinload(BomMaster.details)) bom_res = await self.db.execute(bom_stmt) base_bom = bom_res.scalars().first() if not base_bom: raise HTTPException(400, f"No BOM defined for product {product.name}. Update BOM Master.") for det in base_bom.details: key = (det.rm_id, "Base BOM") consolidated[key] = consolidated.get(key, Decimal('0')) + det.quantity # 2. Additive Feature BOM — spec §5 Steps 3 feat_stmt = select(DealerOrderVehicleFeature).where(DealerOrderVehicleFeature.order_vehicle_id == dov.order_vehicle_id) feat_res = await self.db.execute(feat_stmt) features = feat_res.scalars().all() for feat in features: if feat.feature_option_id: # Case A: Dropdown feature from app.models.dealer_models import SpecialFeatureOptionMaster, SpecialFeatureMaster option = await self.db.get(SpecialFeatureOptionMaster, feat.feature_option_id) feature_master = await self.db.get(SpecialFeatureMaster, feat.feature_id) if feat.feature_id else None feature_name = getattr(feature_master, 'feature_name', getattr(feature_master, 'name', 'Feature')) if feature_master else "Feature" cat_name = f"Feature: {feature_name} ({option.option_value})" if option else "Special Feature" sf_stmt = select(SpecialFeatureBom).where(SpecialFeatureBom.option_id == feat.feature_option_id) sf_res = await self.db.execute(sf_stmt) for sf in sf_res.scalars().all(): key = (sf.rm_id, cat_name) consolidated[key] = consolidated.get(key, Decimal('0')) + sf.quantity elif getattr(feat, 'bool_value', None): # Case B: Boolean feature from app.models.dealer_models import SpecialFeatureMaster, SpecialFeatureOptionMaster feature_master = await self.db.get(SpecialFeatureMaster, feat.feature_id) if feat.feature_id else None feature_name = getattr(feature_master, 'feature_name', getattr(feature_master, 'name', 'Special Feature')) if feature_master else "Special Feature" cat_name = f"Feature: {feature_name}" opt_ids = (await self.db.execute( select(SpecialFeatureOptionMaster.option_id) .where(SpecialFeatureOptionMaster.feature_id == feat.feature_id) .where(SpecialFeatureOptionMaster.option_value.in_(['Required', 'Yes', 'True', '1'])) )).scalars().all() if opt_ids: sf_stmt = select(SpecialFeatureBom).where(SpecialFeatureBom.option_id.in_(opt_ids)) sf_res = await self.db.execute(sf_stmt) for sf in sf_res.scalars().all(): key = (sf.rm_id, cat_name) consolidated[key] = consolidated.get(key, Decimal('0')) + sf.quantity if not consolidated: return { "chassis_id": chassis.chassis_id, "chassis_number": chassis.chassis_no, "chassis_no": chassis.chassis_no, "bom_items": [], "can_proceed": True, "has_covered_shortages": False } simulation = [] rm_ids = list(set([k[0] for k in consolidated.keys()])) if rm_ids: # Bulk fetch RmMaster rm_stmt = select(RmMaster).where(RmMaster.rm_id.in_(rm_ids)) rm_res = await self.db.execute(rm_stmt) rms = {rm.rm_id: rm for rm in rm_res.scalars().all()} # BUG-02 FIX: Aggregate inventory across all stores inventories = await self._get_aggregated_inventory(rm_ids) # Bulk fetch Stations station_ids = {rm.station_id for rm in rms.values() if rm.station_id} stations = {} if station_ids: st_stmt = select(StationMaster).where(StationMaster.station_id.in_(station_ids)) st_res = await self.db.execute(st_stmt) stations = {st.station_id: st for st in st_res.scalars().all()} # ── PO Receipts Tracking & Supplier Resolution ── po_stmt = ( select( RmPoDetail.rm_id, VendorMaster.name.label("supplier_name"), RmPoDetail.received_qty, RmPoDetail.order_qty, RmPurchaseOrder.expected_delivery_date ) .join(RmPurchaseOrder, RmPurchaseOrder.po_id == RmPoDetail.po_id) .join(VendorMaster, VendorMaster.vendor_id == RmPurchaseOrder.vendor_id) .where( RmPoDetail.rm_id.in_(rm_ids), RmPoDetail.line_status != 'CANCELLED' ) .order_by(RmPurchaseOrder.created_at.desc()) ) po_res = await self.db.execute(po_stmt) po_rows = po_res.all() rm_po_receipts = defaultdict(Decimal) rm_pending_po = defaultdict(Decimal) rm_po_suppliers = {} rm_delivery_dates = {} for row in po_rows: rm_po_receipts[row.rm_id] += row.received_qty or Decimal('0') open_qty = max(Decimal('0'), (row.order_qty or Decimal('0')) - (row.received_qty or Decimal('0'))) rm_pending_po[row.rm_id] += open_qty if row.rm_id not in rm_po_suppliers and row.supplier_name: rm_po_suppliers[row.rm_id] = row.supplier_name if row.expected_delivery_date: if row.rm_id not in rm_delivery_dates or row.expected_delivery_date < rm_delivery_dates[row.rm_id]: rm_delivery_dates[row.rm_id] = row.expected_delivery_date # Fallback vendor mappings for RMs without active POs map_stmt = ( select(RmVendorMapping.rm_id, VendorMaster.name) .join(VendorMaster, VendorMaster.vendor_id == RmVendorMapping.vendor_id) .where(RmVendorMapping.rm_id.in_(rm_ids), RmVendorMapping.is_active == True) ) map_res = await self.db.execute(map_stmt) for rm_id_m, v_name in map_res.all(): if rm_id_m not in rm_po_suppliers: rm_po_suppliers[rm_id_m] = v_name for (rm_id, category), required_qty in consolidated.items(): rm = rms.get(rm_id) if not rm: continue inv = inventories.get(rm_id, {}) station = stations.get(rm.station_id) if rm.station_id else None current = inv.get('current_qty', Decimal('0')) reserved = inv.get('reserved_qty', Decimal('0')) in_transit = inv.get('in_transit_qty', Decimal('0')) available = max(Decimal('0'), current - reserved) required = required_qty shortage = max(Decimal('0'), required - available) # Spec §5 Shortage Classification & PO Tracking pending_po = float(rm_pending_po.get(rm_id, Decimal('0'))) or float(in_transit) received_qty = float(rm_po_receipts.get(rm_id, Decimal('0'))) supplier_name = rm_po_suppliers.get(rm_id, "—") exp_date = rm_delivery_dates.get(rm_id) simulation.append({ "rm_id": rm_id, "rm_name": rm.name, "rm_code": rm.part_no, "station_name": station.station_name if station else "Unassigned", "unit_of_measure": rm.unit_of_measurement or "NOS", "required_qty": float(required), "available_qty": float(available), "shortage_qty": float(shortage), "supplier_name": supplier_name, "received_rm_qty": received_qty, "pending_po_qty": pending_po, "expected_delivery_date": exp_date.isoformat() if exp_date else None, "category": category }) has_uncovered = any( item["shortage_qty"] > 0 and item["pending_po_qty"] < item["shortage_qty"] for item in simulation ) has_covered = any( item["shortage_qty"] > 0 and item["pending_po_qty"] >= item["shortage_qty"] for item in simulation ) return { "chassis_id": chassis.chassis_id, "chassis_number": chassis.chassis_no, "chassis_no": chassis.chassis_no, "bom_items": simulation, "can_proceed": not has_uncovered, "has_covered_shortages": has_covered } async def confirm_reservation(self, chassis_id: uuid.UUID, final_bom: list, user_id: uuid.UUID): """Confirm reservation, save snapshot, handle shortages and indents. FLAW-01 FIX: db.commit() is now called in the route, not here. FLAW-03 FIX: Re-confirm uses delta logic — adjusts existing snapshot instead of delete+recreate. """ chassis = await self.db.get(ChassisMaster, chassis_id) if not chassis: raise HTTPException(404, 'Chassis not found') # 1. Fetch existing BOM snapshot for delta logic (FLAW-03 FIX) old_snap_res = await self.db.execute( select(ChassisBomSnapshot).where(ChassisBomSnapshot.chassis_id == chassis_id) ) existing_map = {snap.rm_id: snap for snap in old_snap_res.scalars().all()} has_shortage = False has_covered_shortage = False shortages_to_indent = [] new_rm_ids = set() # 2. Process each item in final_bom with incremental delta logic for item in final_bom: rm_id = item.rm_id new_rm_ids.add(rm_id) new_qty = Decimal(str(item.qty)) # Lock all inventory rows for this RM (Full BUG-02 fix for write path) inv_stmt = select(RmInventory).where(RmInventory.rm_id == rm_id).order_by(RmInventory.current_qty.desc()).with_for_update() inv_res = await self.db.execute(inv_stmt) inventories = inv_res.scalars().all() if not inventories: # Graceful fallback per spec §11 rule 7 from app.models.rm_models import StoreMaster default_store_res = await self.db.execute(select(RmInventory.store_id).limit(1)) default_store_id = default_store_res.scalar_one_or_none() or uuid.UUID('00000000-0000-0000-0000-000000000001') inventory = RmInventory( rm_id=rm_id, store_id=default_store_id, current_qty=Decimal('0'), reserved_qty=Decimal('0'), in_transit_qty=Decimal('0') ) self.db.add(inventory) await self.db.flush() inventories = [inventory] old_snap = existing_map.get(rm_id) already_reserved = old_snap.reserved_qty if old_snap else Decimal('0') if new_qty > already_reserved: # Need to reserve more delta = new_qty - already_reserved total_reserved = already_reserved for inv in inventories: if delta <= Decimal('0'): break available = max(Decimal('0'), (inv.current_qty or Decimal('0')) - (inv.reserved_qty or Decimal('0'))) reserving_now = min(delta, available) inv.reserved_qty = (inv.reserved_qty or Decimal('0')) + reserving_now total_reserved += reserving_now delta -= reserving_now elif new_qty < already_reserved: # Release excess release = already_reserved - new_qty total_reserved = already_reserved for inv in sorted(inventories, key=lambda x: x.reserved_qty or Decimal('0'), reverse=True): if release <= Decimal('0'): break can_release = min(release, inv.reserved_qty or Decimal('0')) inv.reserved_qty = (inv.reserved_qty or Decimal('0')) - can_release total_reserved -= can_release release -= can_release else: total_reserved = already_reserved shortage = max(Decimal('0'), new_qty - total_reserved) in_transit = sum(inv.in_transit_qty or Decimal('0') for inv in inventories) # Upsert snapshot if old_snap: old_snap.quantity = new_qty old_snap.reserved_qty = total_reserved else: snap = ChassisBomSnapshot( chassis_id=chassis_id, rm_id=rm_id, quantity=new_qty, reserved_qty=total_reserved, consumed_qty=Decimal('0'), ) self.db.add(snap) if shortage > Decimal('0'): if in_transit >= shortage: has_covered_shortage = True else: has_shortage = True shortages_to_indent.append({ "rm_id": rm_id, "shortage_qty": float(shortage) }) # 3. Clean up removed items — release reserved_qty and delete snapshot (spec §6.4 step 3) removed_rm_ids = set(existing_map.keys()) - new_rm_ids for rm_id in removed_rm_ids: old_snap = existing_map[rm_id] if old_snap.reserved_qty and old_snap.reserved_qty > 0: inv_stmt = select(RmInventory).where(RmInventory.rm_id == rm_id).with_for_update().limit(1) inv_res = await self.db.execute(inv_stmt) inventory = inv_res.scalar_one_or_none() if inventory: inventory.reserved_qty = max(Decimal('0'), (inventory.reserved_qty or Decimal('0')) - old_snap.reserved_qty) await self.db.delete(old_snap) # 4. Classify chassis per spec §6.4 step 4 if has_shortage: chassis.procurement_status = 'SHORTAGE_HOLD' elif has_covered_shortage: chassis.procurement_status = 'AWAITING_INBOUND_PO' else: chassis.procurement_status = 'PROCUREMENT_CHECKED' # 5. Generate Indents for uncovered shortages only (spec §6.4 step 5) # FLAW-03 FIX: Remove previous shortage contribution for this chassis before re-adding if shortages_to_indent: rm_ids_with_shortage = [s['rm_id'] for s in shortages_to_indent] chassis_no = str(chassis.chassis_no) # DESIGN-04 FIX: Only active vendor mappings map_stmt = ( select(RmVendorMapping) .where(RmVendorMapping.rm_id.in_(rm_ids_with_shortage)) .where(RmVendorMapping.is_active == True) ) map_res = await self.db.execute(map_stmt) mappings = map_res.scalars().all() rm_vendors = defaultdict(list) for m in mappings: rm_vendors[m.rm_id].append(m.vendor_id) vendor_groups = defaultdict(list) for short in shortages_to_indent: rm_id = short['rm_id'] v_list = rm_vendors.get(rm_id, []) if len(v_list) == 1: vendor_groups[v_list[0]].append(short) else: vendor_groups[None].append(short) for vendor_id, group_shortages in vendor_groups.items(): stmt = select(RmPurchaseRequest).options(selectinload(RmPurchaseRequest.details)) if vendor_id: stmt = stmt.where(RmPurchaseRequest.status == 'PENDING', RmPurchaseRequest.vendor_id == vendor_id) else: stmt = stmt.where(RmPurchaseRequest.status == 'PENDING', RmPurchaseRequest.vendor_id == None) stmt = stmt.order_by(RmPurchaseRequest.created_at.desc()).limit(1) pr_res = await self.db.execute(stmt) indent = pr_res.scalar_one_or_none() is_new = not indent if is_new: indent = RmPurchaseRequest( request_number=f"PR-{uuid.uuid4().hex[:8].upper()}", requested_by=user_id, vendor_id=vendor_id ) self.db.add(indent) await self.db.flush() existing_details = {d.rm_id: d for d in indent.details} if not is_new else {} for short in group_shortages: rm_id = short['rm_id'] qty = Decimal(str(short['shortage_qty'])) if rm_id in existing_details: detail = existing_details[rm_id] detail.shortage_qty += qty if not detail.affected_chassis: detail.affected_chassis = [] # FLAW-03 FIX: deduplicate chassis in affected_chassis list if chassis_no not in detail.affected_chassis: detail.affected_chassis = detail.affected_chassis + [chassis_no] else: detail = RmPurchaseRequestDetail( request_id=indent.request_id, rm_id=rm_id, shortage_qty=qty, affected_chassis=[chassis_no] ) self.db.add(detail) await self.db.flush() async def assign_vendor_to_pr_detail(self, request_id: uuid.UUID, detail_id: uuid.UUID, vendor_id: uuid.UUID, user_id: uuid.UUID): stmt = select(RmPurchaseRequest).options(selectinload(RmPurchaseRequest.details)).where(RmPurchaseRequest.request_id == request_id) pr_res = await self.db.execute(stmt) orig_pr = pr_res.scalar_one_or_none() if not orig_pr: raise HTTPException(404, "Original PR not found") detail = next((d for d in orig_pr.details if d.request_detail_id == detail_id), None) if not detail: raise HTTPException(404, "PR Detail not found") v_stmt = select(RmPurchaseRequest).options(selectinload(RmPurchaseRequest.details)).where( RmPurchaseRequest.status == 'PENDING', RmPurchaseRequest.vendor_id == vendor_id ).order_by(RmPurchaseRequest.created_at.desc()).limit(1) v_res = await self.db.execute(v_stmt) vendor_pr = v_res.scalar_one_or_none() is_new = not vendor_pr if is_new: vendor_pr = RmPurchaseRequest( request_number=f"PR-{uuid.uuid4().hex[:8].upper()}", requested_by=user_id, vendor_id=vendor_id ) self.db.add(vendor_pr) await self.db.flush() vendor_details = [] if is_new else vendor_pr.details existing_vendor_detail = next((d for d in vendor_details if d.rm_id == detail.rm_id), None) if existing_vendor_detail: existing_vendor_detail.shortage_qty += detail.shortage_qty if detail.affected_chassis: if not existing_vendor_detail.affected_chassis: existing_vendor_detail.affected_chassis = [] new_c = [c for c in detail.affected_chassis if c not in existing_vendor_detail.affected_chassis] if new_c: existing_vendor_detail.affected_chassis = existing_vendor_detail.affected_chassis + new_c await self.db.delete(detail) if detail in orig_pr.details: orig_pr.details.remove(detail) else: if detail in orig_pr.details: orig_pr.details.remove(detail) if vendor_pr.details is None: vendor_pr.details = [] vendor_pr.details.append(detail) await self.db.flush() count_stmt = select(RmPurchaseRequestDetail).where(RmPurchaseRequestDetail.request_id == request_id) c_res = await self.db.execute(count_stmt) if len(c_res.scalars().all()) == 0: await self.db.delete(orig_pr) await self.db.flush() async def issue_adhoc_rm(self, chassis_id: uuid.UUID, rm_id: uuid.UUID, qty: Decimal, user_id: uuid.UUID, station_id: uuid.UUID = None, category: str = "Ad-hoc"): """Issue ad-hoc raw material to a confirmed chassis.""" chassis = await self.db.get(ChassisMaster, chassis_id) if not chassis: raise HTTPException(404, 'Chassis not found') # BUG-02: Use aggregate to check availability, but deduct from first row inv_agg = await self._get_aggregated_inventory([rm_id]) agg = inv_agg.get(rm_id, {}) available = max(Decimal('0'), agg.get('current_qty', Decimal('0')) - agg.get('reserved_qty', Decimal('0'))) if available < qty: raise HTTPException(400, f'Insufficient stock for ad-hoc issue. Available: {float(available)}, Requested: {float(qty)}') # Deduct from first inventory row inv_stmt = select(RmInventory).where(RmInventory.rm_id == rm_id).with_for_update().limit(1) inv_res = await self.db.execute(inv_stmt) inventory = inv_res.scalar_one_or_none() if not inventory: raise HTTPException(400, 'No inventory record found for this material') # Deduct from stock directly (spec §6.11 step 3) inventory.current_qty -= qty # Log consumption (spec §6.11 step 4) log = RmConsumptionLog( rm_id=rm_id, store_id=inventory.store_id, qty_used=qty, consumption_type='MANUAL', consumed_date=now_utc().date(), description=f"Ad-hoc issue for chassis {chassis.chassis_no}", remarks="Ad-hoc RM request" ) self.db.add(log) # Add BOM snapshot entry (spec §6.11 step 5) snap = ChassisBomSnapshot( chassis_id=chassis_id, rm_id=rm_id, quantity=qty, reserved_qty=qty, consumed_qty=qty, station_id=station_id, ) self.db.add(snap) await self.db.flush() return {"status": "success", "issued_qty": float(qty)}