import uuid from decimal import Decimal from collections import defaultdict from sqlalchemy.ext.asyncio import AsyncSession from sqlalchemy import select 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, RmVendorMapping, StationMaster, RmPurchaseOrder, RmPoDetail, VendorMaster 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 from app.modules.procurement.bulk_engine_schemas import BulkEngineRunRequest class BulkProcurementEngineService: def __init__(self, db: AsyncSession): self.db = db async def get_chassis_bom_requirements(self, chassis_id: uuid.UUID) -> dict: """Explodes the BOM for a single chassis. Returns a dict of (rm_id, category) -> Decimal quantity.""" chassis = await self.db.get(ChassisMaster, chassis_id) if not chassis or not chassis.dealer_order_vehicle_id: raise HTTPException(400, f"Chassis {chassis.chassis_no if chassis else chassis_id} is not linked to a Dealer Order Vehicle.") dov = await self.db.get(DealerOrderVehicle, chassis.dealer_order_vehicle_id) if not dov: raise HTTPException(400, f"Dealer Order Vehicle missing for Chassis {chassis.chassis_no}.") 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) segment = await self.db.get(CustomerSegmentMaster, dov.segment_id) if not (model and body_type and segment): raise HTTPException(400, f"Incomplete master data for Chassis {chassis.chassis_no} (Model, Body Type, or Segment missing).") required_boms = {} prod_stmt = ( select(ProductMaster) .where(ProductMaster.name.ilike(f"%{body_type.name}%")) .where(ProductMaster.name.ilike(f"%{segment.name}%")) .where(ProductMaster.name.ilike(f"%{model.model_number}%")) ) 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} - {segment.name} - {model.model_number} on Chassis {chassis.chassis_no}.") 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} (Chassis {chassis.chassis_no}).") for det in base_bom.details: key = (det.rm_id, "Base BOM") required_boms[key] = required_boms.get(key, Decimal('0')) + det.quantity feat_stmt = select(DealerOrderVehicleFeature).where(DealerOrderVehicleFeature.order_vehicle_id == dov.order_vehicle_id) feat_res = await self.db.execute(feat_stmt) for feat in feat_res.scalars().all(): if feat.feature_option_id: from app.models.dealer_models import SpecialFeatureOptionMaster option = await self.db.get(SpecialFeatureOptionMaster, feat.feature_option_id) cat_name = f"Feature: {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) required_boms[key] = required_boms.get(key, Decimal('0')) + sf.quantity elif getattr(feat, 'bool_value', None): 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) required_boms[key] = required_boms.get(key, Decimal('0')) + sf.quantity return required_boms async def simulate_bulk_check(self, chassis_ids: list[uuid.UUID]) -> dict: """Simulate the BOM check against available stock for multiple chassis.""" if not chassis_ids: return {"bom_items": [], "can_proceed": True} # Pre-fetch chassis numbers and metadata chassis_stmt = ( select(ChassisMaster) .where(ChassisMaster.chassis_id.in_(chassis_ids)) .options( selectinload(ChassisMaster.dealer_order_vehicle).selectinload(DealerOrderVehicle.body_type), selectinload(ChassisMaster.dealer_order_vehicle).selectinload(DealerOrderVehicle.segment) ) ) chassis_res = await self.db.execute(chassis_stmt) chassis_map = {} chassis_labels = {} for c in chassis_res.scalars().all(): chassis_map[c.chassis_id] = c.chassis_no bt = c.dealer_order_vehicle.body_type.name if c.dealer_order_vehicle and c.dealer_order_vehicle.body_type else "" seg = c.dealer_order_vehicle.segment.name if c.dealer_order_vehicle and c.dealer_order_vehicle.segment else "" if bt or seg: chassis_labels[c.chassis_no] = f"{bt} • {seg}" consolidated = defaultdict(Decimal) breakdown = defaultdict(dict) for chassis_id in chassis_ids: chassis_no = chassis_map.get(chassis_id, str(chassis_id)) reqs = await self.get_chassis_bom_requirements(chassis_id) for (rm_id, category), qty in reqs.items(): consolidated[(rm_id, category)] += qty breakdown[(rm_id, category)][chassis_no] = float(qty) if not consolidated: return {"bom_items": [], "can_proceed": True} simulation = [] rm_ids = list(set([k[0] for k in consolidated.keys()])) # Bulk fetch RmMaster and Inventory 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()} inv_stmt = select(RmInventory).where(RmInventory.rm_id.in_(rm_ids)) inv_res = await self.db.execute(inv_stmt) inventories = {inv.rm_id: inv for inv in inv_res.scalars().all()} # 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 inventory = inventories.get(rm_id) station = stations.get(rm.station_id) if rm.station_id else None available = float((inventory.current_qty or Decimal('0')) - (inventory.reserved_qty or Decimal('0'))) if inventory else 0.0 required = float(required_qty) shortage = required - available if available < required else 0.0 in_transit_inv = float(inventory.in_transit_qty or Decimal('0')) if inventory else 0.0 pending_po = float(rm_pending_po.get(rm_id, Decimal('0'))) or in_transit_inv 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": required, "available_qty": available, "shortage_qty": 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, "chassis_breakdown": breakdown.get((rm_id, category), {}) }) has_shortages = any(item["shortage_qty"] > 0 for item in simulation) return { "bom_items": simulation, "chassis_labels": chassis_labels, "can_proceed": not has_shortages } async def run_bulk_engine(self, request: BulkEngineRunRequest, user_id: uuid.UUID): # 1. Gather all required BOMs per chassis global_required = defaultdict(Decimal) chassis_required_map = defaultdict(lambda: defaultdict(Decimal)) # chassis_id -> {(rm_id, category): qty} for chassis_id in request.chassis_ids: reqs = await self.get_chassis_bom_requirements(chassis_id) for (rm_id, category), qty in reqs.items(): chassis_required_map[chassis_id][(rm_id, category)] += qty global_required[(rm_id, category)] += qty if not global_required and not getattr(request, 'final_bom', None): raise HTTPException(400, "No BOM requirements found for the selected chassis.") # If final_bom is provided (user edited the bulk preview), we apply changes if getattr(request, 'final_bom', None): new_chassis_required_map = defaultdict(lambda: defaultdict(Decimal)) num_chassis = Decimal(str(len(request.chassis_ids))) c_stmt = select(ChassisMaster).where(ChassisMaster.chassis_id.in_(request.chassis_ids)) c_res = await self.db.execute(c_stmt) c_map = {c.chassis_no: c.chassis_id for c in c_res.scalars().all()} for item in request.final_bom: rm_id = item.rm_id category = item.category if getattr(item, 'chassis_breakdown', None): for c_no, b_qty in item.chassis_breakdown.items(): c_id = c_map.get(c_no) if c_id: new_chassis_required_map[c_id][(rm_id, category)] += Decimal(str(b_qty)) else: new_qty = Decimal(str(item.qty)) old_qty = global_required.get((rm_id, category), Decimal('0')) if old_qty == Decimal('0'): # Completely new ad-hoc part added in bulk preview. Distribute evenly. per_chassis = new_qty / num_chassis if num_chassis > 0 else Decimal('0') for cid in request.chassis_ids: new_chassis_required_map[cid][(rm_id, category)] += per_chassis else: # DESIGN-05 FIX: Use remainder method to avoid rounding drift chassis_list = [c for c in request.chassis_ids if chassis_required_map[c].get((rm_id, category), Decimal('0')) > 0] if chassis_list: distributed = Decimal('0') for i, cid in enumerate(chassis_list): orig_chassis_qty = chassis_required_map[cid].get((rm_id, category), Decimal('0')) if i == len(chassis_list) - 1: # Last chassis gets the remainder to avoid rounding errors adjusted_qty = new_qty - distributed else: adjusted_qty = (orig_chassis_qty * new_qty / old_qty).quantize(Decimal('0.001')) distributed += adjusted_qty new_chassis_required_map[cid][(rm_id, category)] += adjusted_qty chassis_required_map = new_chassis_required_map # Re-calculate global requirements for inventory checking (ignoring categories now) flat_global_required = defaultdict(Decimal) flat_chassis_required_map = defaultdict(lambda: defaultdict(Decimal)) for cid, reqs in chassis_required_map.items(): for (rm_id, category), qty in reqs.items(): flat_global_required[rm_id] += qty flat_chassis_required_map[cid][rm_id] += qty # 2. Check and lock Inventory for Reservation global_shortage = defaultdict(Decimal) rm_ids = list(flat_global_required.keys()) inv_stmt = select(RmInventory).where(RmInventory.rm_id.in_(rm_ids)).with_for_update() inv_res = await self.db.execute(inv_stmt) inventories = {inv.rm_id: inv for inv in inv_res.scalars().all()} rm_stmt = select(RmMaster).where(RmMaster.rm_id.in_(rm_ids)) rm_res = await self.db.execute(rm_stmt) rm_masters = {rm.rm_id: rm for rm in rm_res.scalars().all()} map_stmt = ( select(RmVendorMapping) .where(RmVendorMapping.rm_id.in_(rm_ids)) .where(RmVendorMapping.is_active == True) # DESIGN-04 FIX: exclude inactive vendors ) map_res = await self.db.execute(map_stmt) vendor_maps = {} for vm in map_res.scalars().all(): if vm.rm_id not in vendor_maps: vendor_maps[vm.rm_id] = vm.vendor_id for chassis_id in request.chassis_ids: reqs = flat_chassis_required_map[chassis_id] chassis_has_shortage = False chassis_shortage_map = {} # rm_id -> shortage qty for this chassis for rm_id, qty in reqs.items(): inventory = inventories.get(rm_id) available = (inventory.current_qty or Decimal('0')) - (inventory.reserved_qty or Decimal('0')) if inventory else Decimal('0') available = max(Decimal('0'), available) reserving_qty = min(qty, available) if inventory: inventory.reserved_qty = (inventory.reserved_qty or Decimal('0')) + reserving_qty rm = rm_masters.get(rm_id) snapshot = ChassisBomSnapshot( chassis_id=chassis_id, rm_id=rm_id, quantity=qty, reserved_qty=reserving_qty, consumed_qty=Decimal('0'), station_id=rm.station_id if rm else None ) self.db.add(snapshot) shortage = qty - reserving_qty if shortage > 0: global_shortage[rm_id] += shortage chassis_has_shortage = True chassis_shortage_map[rm_id] = chassis_shortage_map.get(rm_id, Decimal('0')) + shortage chassis = await self.db.get(ChassisMaster, chassis_id) if chassis: # Spec §7.3 Step 4: per-chassis status using in_transit for AWAITING_INBOUND_PO if chassis_has_shortage: # Check if shortages are covered by in_transit stock all_covered = True for rm_id, short_qty in chassis_shortage_map.items(): inv = inventories.get(rm_id) in_transit = (inv.in_transit_qty or Decimal('0')) if inv else Decimal('0') if in_transit < short_qty: all_covered = False break chassis.procurement_status = 'AWAITING_INBOUND_PO' if all_covered else 'SHORTAGE_HOLD' else: chassis.procurement_status = 'PROCUREMENT_CHECKED' # 3. Apply Buffer and Create or Append to Purchase Request buffer_multiplier = Decimal(str(request.buffer_percentage)) / Decimal('100.0') affected_chassis_list = [str(c) for c in request.chassis_ids] if global_shortage: rm_ids = list(global_shortage.keys()) map_stmt = ( select(RmVendorMapping) .where(RmVendorMapping.rm_id.in_(rm_ids)) .where(RmVendorMapping.is_active == True) # DESIGN-04 FIX ) 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 rm_id, shortage_qty in global_shortage.items(): v_list = rm_vendors.get(rm_id, []) if len(v_list) == 1: vendor_groups[v_list[0]].append((rm_id, shortage_qty)) else: vendor_groups[None].append((rm_id, shortage_qty)) 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-BULK-{uuid.uuid4().hex[:6].upper()}", status='PENDING', 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 rm_id, shortage_qty in group_shortages: buffered_shortage = shortage_qty + (shortage_qty * buffer_multiplier) if rm_id in existing_details: detail = existing_details[rm_id] detail.shortage_qty += buffered_shortage if not detail.affected_chassis: detail.affected_chassis = [] new_chassis = [c for c in affected_chassis_list if c not in detail.affected_chassis] if new_chassis: detail.affected_chassis = detail.affected_chassis + new_chassis else: detail = RmPurchaseRequestDetail( request_id=indent.request_id, rm_id=rm_id, shortage_qty=buffered_shortage, affected_chassis=affected_chassis_list, reason=f"Bulk Engine Auto-Indent for {len(request.chassis_ids)} chassis" ) self.db.add(detail) await self.db.flush() return {"status": "success", "vendors_notified": 0}