# app/modules/procurement/routes.py from fastapi import APIRouter, Depends, HTTPException from sqlalchemy.ext.asyncio import AsyncSession from sqlalchemy import select, func, and_ from sqlalchemy.orm import selectinload from app.core.database import get_db from app.core.security import require_permission from app.core.numbering import next_document_number from datetime import datetime, date from app.models.rm_models import ( RmPurchaseOrder, RmPoDetail, RmReceivingLog, GrnDetail, PoStatusMaster, MonthlyProcurementPlan, MonthlyProcurementPlanLine ) from app.modules.inventory.service import InventoryService from .schemas import ( RmPurchaseOrderCreate, RmPurchaseOrderResponse, RmPurchaseOrderStatusUpdate, RmReceivingLogCreate, RmReceivingLogResponse, MonthlyPlanCreate, MonthlyPlanUpdate, MonthlyPlanResponse, MonthlyPlanLineResponse ) from uuid import UUID from typing import List, Optional from decimal import Decimal router = APIRouter() @router.get('/po-statuses') async def list_po_statuses(db: AsyncSession = Depends(get_db), _=Depends(require_permission('PROCUREMENT', 'view'))): """ Fetch all available PO statuses (e.g., OPEN, APPROVED, CLOSED). Purpose: Used by the frontend dropdowns to display valid status transitions for Purchase Orders within the procurement module. """ result = await db.execute(select(PoStatusMaster).order_by(PoStatusMaster.code.asc())) return list(result.scalars().all()) # ── PO ROUTES ──────────────────────────────────────── @router.get('/purchase-orders', response_model=List[RmPurchaseOrderResponse]) async def list_purchase_orders( skip: int = 0, limit: int = 100, db: AsyncSession = Depends(get_db), _=Depends(require_permission('PROCUREMENT', 'view')) ): """ Fetch a paginated list of Purchase Orders along with their associated details. Purpose: Serves the primary data grid on the PO Management page. Uses `selectinload` to efficiently fetch child line items (details) avoiding N+1 query problems. """ stmt = ( select(RmPurchaseOrder) .options(selectinload(RmPurchaseOrder.details)) .offset(skip) .limit(limit) ) result = await db.execute(stmt) return list(result.scalars().all()) @router.get('/purchase-orders/next-number') async def get_next_po_number( db: AsyncSession = Depends(get_db), _=Depends(require_permission('PROCUREMENT', 'view')) ): """ Generate or preview the next sequential Purchase Order Number (e.g. PO-2026-00001). """ next_no = await next_document_number(db, RmPurchaseOrder.po_number, 'PO') return {"po_number": next_no} @router.post('/purchase-orders', response_model=RmPurchaseOrderResponse, status_code=201) async def create_purchase_order( data: RmPurchaseOrderCreate, db: AsyncSession = Depends(get_db), user=Depends(require_permission('PROCUREMENT', 'create')) ): """ Create a new Purchase Order (Header) along with its line items (Details). Purpose: This is the core entry point for ordering Raw Materials from vendors. It automatically calculates line amounts including GST, aggregates the total PO amount, and persists the hierarchical data (header + children) together. """ total_amount = Decimal('0.0') po_details = [] for detail_in in data.details: gst = Decimal(str(detail_in.gst_percent or 0.0)) qty = Decimal(str(detail_in.order_qty)) price = Decimal(str(detail_in.unit_price)) line_amount = qty * price * (1 + gst / 100) total_amount += line_amount detail = RmPoDetail( rm_id=detail_in.rm_id, order_qty=qty, received_qty=Decimal('0.0'), unit_price=price, gst_percent=gst, line_amount=line_amount, line_status='OPEN' ) po_details.append(detail) final_po_number = data.po_number.strip() if data.po_number and data.po_number.strip() else None if final_po_number: existing = (await db.execute(select(RmPurchaseOrder.po_id).where(RmPurchaseOrder.po_number == final_po_number))).scalar_one_or_none() if existing: final_po_number = await next_document_number(db, RmPurchaseOrder.po_number, 'PO') else: final_po_number = await next_document_number(db, RmPurchaseOrder.po_number, 'PO') po = RmPurchaseOrder( po_number=final_po_number, vendor_id=data.vendor_id, order_date=data.order_date, expected_delivery_date=data.expected_delivery_date, status_id=data.status_id, notes=data.notes, created_by=user.user_id, total_amount=total_amount, details=po_details ) db.add(po) await db.flush() from app.models.rm_models import RmPoHistory history = RmPoHistory( po_id=po.po_id, action='CREATED', description='Purchase Order created', created_by_name=user.name if user else 'System' ) db.add(history) stmt = ( select(RmPurchaseOrder) .options(selectinload(RmPurchaseOrder.details)) .where(RmPurchaseOrder.po_id == po.po_id) ) result = await db.execute(stmt) po = result.scalar_one() return po @router.get('/purchase-orders/{po_id}', response_model=RmPurchaseOrderResponse) async def get_purchase_order( po_id: UUID, db: AsyncSession = Depends(get_db), _=Depends(require_permission('PROCUREMENT', 'view')) ): """ Fetch a single Purchase Order by its ID. """ stmt = ( select(RmPurchaseOrder) .options(selectinload(RmPurchaseOrder.details)) .where(RmPurchaseOrder.po_id == po_id) ) result = await db.execute(stmt) po = result.scalar_one_or_none() if not po: raise HTTPException(404, 'Purchase Order not found') return po @router.get('/purchase-orders/{po_id}/history') async def get_po_history( po_id: UUID, db: AsyncSession = Depends(get_db), _=Depends(require_permission('PROCUREMENT', 'view')) ): from app.models.rm_models import RmPoHistory stmt = select(RmPoHistory).where(RmPoHistory.po_id == po_id).order_by(RmPoHistory.created_at.desc()) result = await db.execute(stmt) history = result.scalars().all() return [ { "history_id": h.history_id, "action": h.action, "description": h.description, "created_by_name": h.created_by_name, "created_at": h.created_at } for h in history ] @router.put('/purchase-orders/{po_id}', response_model=RmPurchaseOrderResponse) async def update_purchase_order( po_id: UUID, data: RmPurchaseOrderCreate, db: AsyncSession = Depends(get_db), user=Depends(require_permission('PROCUREMENT', 'edit')) ): """ Update a Purchase Order by its ID. Replaces existing line items with the provided ones. """ stmt = ( select(RmPurchaseOrder) .options(selectinload(RmPurchaseOrder.details)) .where(RmPurchaseOrder.po_id == po_id) ) result = await db.execute(stmt) po = result.scalar_one_or_none() if not po: raise HTTPException(404, 'Purchase Order not found') po.po_number = data.po_number po.vendor_id = data.vendor_id po.order_date = data.order_date po.expected_delivery_date = data.expected_delivery_date po.status_id = data.status_id po.notes = data.notes existing_details = {d.rm_id: d for d in po.details} new_details = [] total_amount = Decimal('0.0') for detail_in in data.details: qty = Decimal(str(detail_in.order_qty)) price = Decimal(str(detail_in.unit_price)) gst = Decimal(str(detail_in.gst_percent or 0)) line_amount = qty * price * (1 + gst / 100) total_amount += line_amount if detail_in.rm_id in existing_details: detail = existing_details[detail_in.rm_id] detail.order_qty = qty detail.unit_price = price detail.gst_percent = gst detail.line_amount = line_amount new_details.append(detail) del existing_details[detail_in.rm_id] else: detail = RmPoDetail( rm_id=detail_in.rm_id, order_qty=qty, received_qty=Decimal('0.0'), unit_price=price, gst_percent=gst, line_amount=line_amount, line_status='OPEN' ) new_details.append(detail) po.details = new_details po.total_amount = total_amount try: await db.flush() from app.models.rm_models import RmPoHistory history = RmPoHistory( po_id=po.po_id, action='UPDATED', description='Purchase Order details updated', created_by_name=user.name if user else 'System' ) db.add(history) await db.flush() except Exception as e: raise HTTPException(400, f'Failed to update PO: {str(e)}') await db.refresh(po) return po @router.put('/purchase-orders/{po_id}/status', response_model=RmPurchaseOrderResponse) async def update_po_status( po_id: UUID, status_data: RmPurchaseOrderStatusUpdate, db: AsyncSession = Depends(get_db), user=Depends(require_permission('PROCUREMENT', 'edit')) ): """ Update the lifecycle status of a PO (e.g., from DRAFT to APPROVED). """ status_exists = await db.get(PoStatusMaster, status_data.status_id) if not status_exists: raise HTTPException(400, 'Invalid status ID') if status_exists.code == 'APPROVED' and user.role_code not in ['PROCUREMENT_MGR', 'ADMIN']: raise HTTPException(403, 'Only managers can approve purchase orders') stmt = ( select(RmPurchaseOrder) .options(selectinload(RmPurchaseOrder.details)) .where(RmPurchaseOrder.po_id == po_id) ) result = await db.execute(stmt) po = result.scalar_one_or_none() if not po: raise HTTPException(404, 'Purchase Order not found') po.status_id = status_data.status_id if status_exists.code == 'APPROVED': po.approved_by = user.user_id po.approved_at = datetime.utcnow() await db.flush() from app.models.rm_models import RmPoHistory history = RmPoHistory( po_id=po.po_id, action='STATUS_CHANGED', description=f"Status changed to {status_exists.name}", created_by_name=user.name if user else 'System' ) db.add(history) await db.flush() await db.refresh(po) return po @router.post('/purchase-orders/{po_id}/approve', response_model=RmPurchaseOrderResponse) async def approve_purchase_order( po_id: UUID, db: AsyncSession = Depends(get_db), user=Depends(require_permission('PROCUREMENT', 'approve')) ): """ Approve a Purchase Order. Requires PROCUREMENT_MGR or ADMIN. """ stmt = ( select(RmPurchaseOrder) .options(selectinload(RmPurchaseOrder.details)) .where(RmPurchaseOrder.po_id == po_id) ) result = await db.execute(stmt) po = result.scalar_one_or_none() if not po: raise HTTPException(404, 'Purchase Order not found') # Check status using PoStatusMaster name status_stmt = select(PoStatusMaster).where(PoStatusMaster.id == po.status_id) status_res = await db.execute(status_stmt) status_obj = status_res.scalar_one_or_none() if status_obj and status_obj.code == 'APPROVED': raise HTTPException(400, 'Purchase Order is already approved') if po.created_by == user.user_id and user.role_code != 'ADMIN': raise HTTPException(403, 'Self-approval is not allowed') # Get the APPROVED status id approved_status_stmt = select(PoStatusMaster).where(PoStatusMaster.code == 'APPROVED') approved_status_res = await db.execute(approved_status_stmt) approved_status_obj = approved_status_res.scalar_one_or_none() if approved_status_obj: po.status_id = approved_status_obj.id po.approved_by = user.user_id po.approved_at = datetime.utcnow() await db.flush() from app.models.rm_models import RmPoHistory history = RmPoHistory( po_id=po.po_id, action='APPROVED', description="Purchase Order approved", created_by_name=user.name if user else 'System' ) db.add(history) await db.flush() await db.refresh(po) return po @router.delete('/purchase-orders/{po_id}', status_code=204) async def delete_purchase_order( po_id: UUID, db: AsyncSession = Depends(get_db), _=Depends(require_permission('PROCUREMENT', 'delete')) ): """ Delete a Purchase Order permanently. Purpose: Allows admin to remove a PO record. """ stmt = ( select(RmPurchaseOrder) .options(selectinload(RmPurchaseOrder.details)) .where(RmPurchaseOrder.po_id == po_id) ) result = await db.execute(stmt) po = result.scalar_one_or_none() if not po: raise HTTPException(404, 'Purchase Order not found') await db.delete(po) await db.flush() from app.models.vendor_portal import ASNLog @router.get('/purchase-orders/{po_id}/asns') async def get_po_asns( po_id: UUID, db: AsyncSession = Depends(get_db), _=Depends(require_permission('PROCUREMENT', 'view')) ): """ Fetch all ASNs for a given Purchase Order. """ stmt = ( select(ASNLog) .options(selectinload(ASNLog.details)) .where(ASNLog.po_id == po_id) .order_by(ASNLog.created_at.desc()) ) result = await db.execute(stmt) return list(result.scalars().all()) @router.get('/purchase-orders/{po_id}/grns', response_model=List[RmReceivingLogResponse]) async def get_po_grns( po_id: UUID, db: AsyncSession = Depends(get_db), _=Depends(require_permission('PROCUREMENT', 'view')) ): """ Fetch all GRNs for a given Purchase Order. """ stmt = ( select(RmReceivingLog) .options(selectinload(RmReceivingLog.details)) .where(RmReceivingLog.po_id == po_id) .order_by(RmReceivingLog.created_at.desc()) ) result = await db.execute(stmt) return list(result.scalars().all()) # ── GRN ROUTES ──────────────────────────────────────── @router.get('/grn', response_model=List[RmReceivingLogResponse]) async def list_grn( skip: int = 0, limit: int = 100, db: AsyncSession = Depends(get_db), _=Depends(require_permission('PROCUREMENT', 'view')) ): """ Fetch a list of Goods Receipt Notes (GRN). """ stmt = ( select(RmReceivingLog) .options(selectinload(RmReceivingLog.details)) .offset(skip) .limit(limit) ) result = await db.execute(stmt) return list(result.scalars().all()) @router.get('/grn/{grn_id}', response_model=RmReceivingLogResponse) async def get_grn( grn_id: UUID, db: AsyncSession = Depends(get_db), _=Depends(require_permission('PROCUREMENT', 'view')) ): """ Fetch a single Goods Receipt Note (GRN) by its ID. """ stmt = ( select(RmReceivingLog) .options(selectinload(RmReceivingLog.details)) .where(RmReceivingLog.grn_id == grn_id) ) result = await db.execute(stmt) grn = result.scalar_one_or_none() if not grn: raise HTTPException(404, 'GRN not found') return grn @router.post('/grn', response_model=RmReceivingLogResponse, status_code=201) async def create_grn( data: RmReceivingLogCreate, db: AsyncSession = Depends(get_db), user=Depends(require_permission('PROCUREMENT', 'create')) ): """ Create a new Goods Receipt Note (GRN) when a delivery arrives. Cross-module: updates PO line statuses and posts stock to inventory. """ grn = RmReceivingLog( grn_number=data.grn_number or await next_document_number(db, RmReceivingLog.grn_number, 'GRN'), po_id=data.po_id, vendor_id=data.vendor_id, asn_id=data.asn_id, received_date=data.received_date, vehicle_number=data.vehicle_number, dc_number=data.dc_number, grn_status=data.grn_status or 'PENDING_QA', remarks=data.remarks, created_by=user.user_id ) db.add(grn) if data.asn_id: asn = await db.get(ASNLog, data.asn_id) if asn: asn.status = 'RECEIVED' await db.flush() InventoryService(db) grn_details = [] for detail_in in data.details: rec_qty = Decimal(str(detail_in.received_qty)) acc_qty = Decimal(str(detail_in.accepted_qty)) if detail_in.accepted_qty is not None else rec_qty rej_qty = Decimal(str(detail_in.rejected_qty or 0.0)) detail = GrnDetail( grn_id=grn.grn_id, po_detail_id=detail_in.po_detail_id, rm_id=detail_in.rm_id, received_qty=rec_qty, accepted_qty=acc_qty, rejected_qty=rej_qty, rejection_reason=detail_in.rejection_reason, store_id=detail_in.store_id ) db.add(detail) grn_details.append(detail) await db.flush() stmt = ( select(RmReceivingLog) .options(selectinload(RmReceivingLog.details)) .where(RmReceivingLog.grn_id == grn.grn_id) ) result = await db.execute(stmt) grn = result.scalar_one() return grn @router.post('/grn/{grn_id}/verify', response_model=RmReceivingLogResponse) async def verify_grn( grn_id: UUID, db: AsyncSession = Depends(get_db), user=Depends(require_permission('PROCUREMENT', 'edit')) ): stmt = ( select(RmReceivingLog) .options(selectinload(RmReceivingLog.details)) .where(RmReceivingLog.grn_id == grn_id) ) result = await db.execute(stmt) grn = result.scalar_one_or_none() if not grn: raise HTTPException(404, 'GRN not found') grn.grn_status = 'VERIFIED' await db.flush() await db.refresh(grn) return grn @router.post('/grn/{grn_id}/approve', response_model=RmReceivingLogResponse) async def approve_grn( grn_id: UUID, db: AsyncSession = Depends(get_db), user=Depends(require_permission('PROCUREMENT', 'approve')) ): stmt = ( select(RmReceivingLog) .options(selectinload(RmReceivingLog.details)) .where(RmReceivingLog.grn_id == grn_id) ) result = await db.execute(stmt) grn = result.scalar_one_or_none() if not grn: raise HTTPException(404, 'GRN not found') if grn.grn_status == 'APPROVED': raise HTTPException(400, 'GRN is already approved') if grn.created_by == user.user_id and user.role_code != 'ADMIN': raise HTTPException(403, 'Self-approval is not allowed') inv_service = InventoryService(db) po_id = grn.po_id for detail in grn.details: if detail.accepted_qty and detail.accepted_qty > 0: await inv_service.grn_post( rm_id=detail.rm_id, store_id=detail.store_id, accepted_qty=detail.accepted_qty, grn_id=grn.grn_id ) po_detail = await db.get(RmPoDetail, detail.po_detail_id) if po_detail: po_detail.received_qty = (po_detail.received_qty or Decimal('0.0')) + detail.accepted_qty if po_detail.received_qty >= po_detail.order_qty: po_detail.line_status = 'COMPLETED' else: po_detail.line_status = 'PARTIALLY_RECEIVED' grn.grn_status = 'APPROVED' grn.approved_by = user.user_id grn.approved_at = datetime.utcnow() await db.flush() # Auto-close PO if all lines are completed if po_id: po = await db.get(RmPurchaseOrder, po_id) if po: po_stmt = select(RmPoDetail).where(RmPoDetail.po_id == po_id) po_res = await db.execute(po_stmt) po_lines = po_res.scalars().all() all_completed = all(line.received_qty and line.received_qty >= line.order_qty for line in po_lines) if all_completed: status_stmt = select(PoStatusMaster).where(PoStatusMaster.code == 'COMPLETED') status_res = await db.execute(status_stmt) completed_status = status_res.scalar_one_or_none() if completed_status: po.status_id = completed_status.id await db.flush() await db.refresh(grn) return grn @router.put('/grn/{grn_id}', response_model=RmReceivingLogResponse) async def update_grn( grn_id: UUID, data: RmReceivingLogCreate, db: AsyncSession = Depends(get_db), _=Depends(require_permission('PROCUREMENT', 'edit')) ): """ Update an existing Goods Receipt Note (GRN). Adjusts inventory and PO line statuses based on delta quantities. """ stmt = ( select(RmReceivingLog) .options(selectinload(RmReceivingLog.details)) .where(RmReceivingLog.grn_id == grn_id) ) result = await db.execute(stmt) grn = result.scalar_one_or_none() if not grn: raise HTTPException(404, 'GRN not found') grn.grn_number = data.grn_number grn.po_id = data.po_id grn.vendor_id = data.vendor_id grn.received_date = data.received_date grn.vehicle_number = data.vehicle_number grn.dc_number = data.dc_number grn.grn_status = data.grn_status or 'PENDING_QA' grn.remarks = data.remarks inv_service = InventoryService(db) old_details_map = {d.po_detail_id: d for d in grn.details} for detail_in in data.details: rec_qty = Decimal(str(detail_in.received_qty)) acc_qty = Decimal(str(detail_in.accepted_qty)) if detail_in.accepted_qty is not None else rec_qty rej_qty = Decimal(str(detail_in.rejected_qty or 0.0)) old_detail = old_details_map.get(detail_in.po_detail_id) if old_detail: diff_acc = acc_qty - (old_detail.accepted_qty or Decimal('0.0')) old_detail.received_qty = rec_qty old_detail.accepted_qty = acc_qty old_detail.rejected_qty = rej_qty old_detail.rejection_reason = detail_in.rejection_reason old_detail.store_id = detail_in.store_id if diff_acc != 0: await inv_service.post_transaction( rm_id=detail_in.rm_id, store_id=detail_in.store_id, qty=diff_acc, transaction_type='GRN_ADJUSTMENT', reference_type='GRN', reference_id=grn.grn_id ) po_detail = await db.get(RmPoDetail, detail_in.po_detail_id) if po_detail: po_detail.received_qty = (po_detail.received_qty or Decimal('0.0')) + diff_acc if po_detail.received_qty >= po_detail.order_qty: po_detail.line_status = 'COMPLETED' else: po_detail.line_status = 'PARTIALLY_RECEIVED' else: detail = GrnDetail( grn_id=grn.grn_id, po_detail_id=detail_in.po_detail_id, rm_id=detail_in.rm_id, received_qty=rec_qty, accepted_qty=acc_qty, rejected_qty=rej_qty, rejection_reason=detail_in.rejection_reason, store_id=detail_in.store_id ) db.add(detail) if acc_qty > 0: await inv_service.grn_post( rm_id=detail_in.rm_id, store_id=detail_in.store_id, accepted_qty=acc_qty, grn_id=grn.grn_id ) po_detail = await db.get(RmPoDetail, detail_in.po_detail_id) if po_detail: po_detail.received_qty = (po_detail.received_qty or Decimal('0.0')) + acc_qty if po_detail.received_qty >= po_detail.order_qty: po_detail.line_status = 'COMPLETED' else: po_detail.line_status = 'PARTIALLY_RECEIVED' try: await db.flush() except Exception as e: raise HTTPException(400, f'Failed to update GRN: {str(e)}') await db.refresh(grn) return grn @router.delete('/grn/{grn_id}', status_code=204) async def delete_grn( grn_id: UUID, db: AsyncSession = Depends(get_db), _=Depends(require_permission('PROCUREMENT', 'delete')) ): """ Delete a GRN record permanently. Purpose: Allows admin to remove a GRN entry. """ stmt = ( select(RmReceivingLog) .options(selectinload(RmReceivingLog.details)) .where(RmReceivingLog.grn_id == grn_id) ) result = await db.execute(stmt) grn = result.scalar_one_or_none() if not grn: raise HTTPException(404, 'GRN not found') await db.delete(grn) await db.flush() from app.models.vendor_portal import DebitNote from .schemas import DebitNoteCreate, DebitNoteResponse # ── DEBIT NOTES ─────────────────────────────────────── @router.post('/debit-notes', response_model=DebitNoteResponse, status_code=201) async def create_debit_note( data: DebitNoteCreate, db: AsyncSession = Depends(get_db), user=Depends(require_permission('PROCUREMENT', 'create')) ): """ Create a new Debit Note to settle discrepancies (e.g. shortages or rejected items). """ dn = DebitNote( vendor_id=data.vendor_id, rejection_id=data.rejection_id, dn_number=data.dn_number or await next_document_number(db, DebitNote.dn_number, 'DN'), amount=data.amount, reason=data.reason, status='DRAFT', created_by=user.user_id ) db.add(dn) await db.flush() await db.refresh(dn) return dn @router.post('/debit-notes/{dn_id}/approve', response_model=DebitNoteResponse) async def approve_debit_note( dn_id: UUID, db: AsyncSession = Depends(get_db), user=Depends(require_permission('PROCUREMENT', 'approve')) ): stmt = select(DebitNote).where(DebitNote.debit_note_id == dn_id) result = await db.execute(stmt) dn = result.scalar_one_or_none() if not dn: raise HTTPException(404, 'Debit Note not found') if dn.status == 'APPROVED': raise HTTPException(400, 'Debit Note is already approved') if dn.created_by == user.user_id and user.role_code != 'ADMIN': raise HTTPException(403, 'Self-approval is not allowed') dn.status = 'APPROVED' dn.approved_by = user.user_id dn.approved_at = datetime.utcnow() await db.flush() await db.refresh(dn) return dn @router.get('/debit-notes', response_model=List[DebitNoteResponse]) async def get_all_debit_notes( db: AsyncSession = Depends(get_db), _=Depends(require_permission('PROCUREMENT', 'view')) ): """ Fetch all Debit Notes for the ERP view. """ result = await db.execute( select(DebitNote) .order_by(DebitNote.created_at.desc()) ) return result.scalars().all() # ── MONTHLY PROCUREMENT PLAN ROUTES ────────────────── async def _enrich_plan_with_ordered_qty(db: AsyncSession, plan: MonthlyProcurementPlan) -> MonthlyPlanResponse: """ Helper: attaches live-computed PO and GRN quantities to every plan line. """ # Build a map: rm_id -> ordered_qty for this vendor+month first_day = date(plan.plan_year, plan.plan_month, 1) if plan.plan_month == 12: last_day = date(plan.plan_year + 1, 1, 1) else: last_day = date(plan.plan_year, plan.plan_month + 1, 1) ordered_result = await db.execute( select( RmPoDetail.rm_id, func.coalesce(func.sum(RmPoDetail.order_qty), 0).label('total_ordered') ) .join(RmPurchaseOrder, RmPoDetail.po_id == RmPurchaseOrder.po_id) .join(PoStatusMaster, RmPurchaseOrder.status_id == PoStatusMaster.id) .where( and_( RmPurchaseOrder.vendor_id == plan.vendor_id, RmPurchaseOrder.order_date >= first_day, RmPurchaseOrder.order_date < last_day, PoStatusMaster.code.notin_(['DRAFT', 'CANCELLED']) ) ) .group_by(RmPoDetail.rm_id) ) ordered_map = {str(row.rm_id): float(row.total_ordered) for row in ordered_result.all()} received_result = await db.execute( select( GrnDetail.rm_id, func.coalesce(func.sum(GrnDetail.received_qty), 0).label('total_received') ) .join(RmReceivingLog, GrnDetail.grn_id == RmReceivingLog.grn_id) .join(RmPurchaseOrder, RmReceivingLog.po_id == RmPurchaseOrder.po_id) .where( and_( RmPurchaseOrder.vendor_id == plan.vendor_id, RmReceivingLog.received_date >= first_day, RmReceivingLog.received_date < last_day, ) ) .group_by(GrnDetail.rm_id) ) received_map = {str(row.rm_id): float(row.total_received) for row in received_result.all()} enriched_lines = [ MonthlyPlanLineResponse( line_id=line.line_id, plan_id=line.plan_id, rm_id=line.rm_id, planned_qty=float(line.planned_qty), ordered_qty=ordered_map.get(str(line.rm_id), 0.0), received_qty=received_map.get(str(line.rm_id), 0.0), created_at=line.created_at, ) for line in plan.lines ] return MonthlyPlanResponse( plan_id=plan.plan_id, vendor_id=plan.vendor_id, plan_year=plan.plan_year, plan_month=plan.plan_month, notes=plan.notes, created_by=plan.created_by, created_at=plan.created_at, updated_at=plan.updated_at, lines=enriched_lines, ) @router.get('/monthly-plans', response_model=List[MonthlyPlanResponse]) async def list_monthly_plans( vendor_id: Optional[UUID] = None, year: Optional[int] = None, month: Optional[int] = None, db: AsyncSession = Depends(get_db), _=Depends(require_permission('PROCUREMENT', 'view')) ): """ List all Monthly Procurement Plans. Supports optional filtering by vendor_id, year, and month. Each line includes computed ordered_qty from POs. """ stmt = ( select(MonthlyProcurementPlan) .options(selectinload(MonthlyProcurementPlan.lines)) .order_by(MonthlyProcurementPlan.plan_year.desc(), MonthlyProcurementPlan.plan_month.desc()) ) if vendor_id: stmt = stmt.where(MonthlyProcurementPlan.vendor_id == vendor_id) if year: stmt = stmt.where(MonthlyProcurementPlan.plan_year == year) if month: stmt = stmt.where(MonthlyProcurementPlan.plan_month == month) result = await db.execute(stmt) plans = result.scalars().all() return [await _enrich_plan_with_ordered_qty(db, p) for p in plans] @router.post('/monthly-plans', response_model=MonthlyPlanResponse, status_code=201) async def create_monthly_plan( data: MonthlyPlanCreate, db: AsyncSession = Depends(get_db), user=Depends(require_permission('PROCUREMENT', 'create')) ): """ Create a new Monthly Procurement Plan for a vendor + month. Raises 409 if a plan already exists for that vendor/year/month. """ existing = await db.execute( select(MonthlyProcurementPlan).where( and_( MonthlyProcurementPlan.vendor_id == data.vendor_id, MonthlyProcurementPlan.plan_year == data.plan_year, MonthlyProcurementPlan.plan_month == data.plan_month, ) ) ) if existing.scalar_one_or_none(): raise HTTPException(409, 'A plan already exists for this vendor and month. Edit the existing plan.') plan = MonthlyProcurementPlan( vendor_id=data.vendor_id, plan_year=data.plan_year, plan_month=data.plan_month, notes=data.notes, created_by=getattr(user, 'user_id', None), ) db.add(plan) await db.flush() # get plan_id for line_in in data.lines: db.add(MonthlyProcurementPlanLine( plan_id=plan.plan_id, rm_id=line_in.rm_id, planned_qty=Decimal(str(line_in.planned_qty)), )) await db.commit() await db.refresh(plan) # reload with lines result = await db.execute( select(MonthlyProcurementPlan) .options(selectinload(MonthlyProcurementPlan.lines)) .where(MonthlyProcurementPlan.plan_id == plan.plan_id) ) plan = result.scalar_one() return await _enrich_plan_with_ordered_qty(db, plan) @router.get('/monthly-plans/{plan_id}', response_model=MonthlyPlanResponse) async def get_monthly_plan( plan_id: UUID, db: AsyncSession = Depends(get_db), _=Depends(require_permission('PROCUREMENT', 'view')) ): """Fetch a single Monthly Plan by ID with live ordered_qty.""" result = await db.execute( select(MonthlyProcurementPlan) .options(selectinload(MonthlyProcurementPlan.lines)) .where(MonthlyProcurementPlan.plan_id == plan_id) ) plan = result.scalar_one_or_none() if not plan: raise HTTPException(404, 'Monthly plan not found') return await _enrich_plan_with_ordered_qty(db, plan) @router.put('/monthly-plans/{plan_id}', response_model=MonthlyPlanResponse) async def update_monthly_plan( plan_id: UUID, data: MonthlyPlanUpdate, db: AsyncSession = Depends(get_db), _=Depends(require_permission('PROCUREMENT', 'edit')) ): """ Update a Monthly Plan — replaces all lines and updates notes. Plans remain fully editable even after POs have been raised (ordered_qty is always live-computed). """ result = await db.execute( select(MonthlyProcurementPlan) .options(selectinload(MonthlyProcurementPlan.lines)) .where(MonthlyProcurementPlan.plan_id == plan_id) ) plan = result.scalar_one_or_none() if not plan: raise HTTPException(404, 'Monthly plan not found') if data.notes is not None: plan.notes = data.notes # Delete all existing lines and replace for line in list(plan.lines): await db.delete(line) await db.flush() for line_in in data.lines: db.add(MonthlyProcurementPlanLine( plan_id=plan.plan_id, rm_id=line_in.rm_id, planned_qty=Decimal(str(line_in.planned_qty)), )) await db.commit() result = await db.execute( select(MonthlyProcurementPlan) .options(selectinload(MonthlyProcurementPlan.lines)) .where(MonthlyProcurementPlan.plan_id == plan_id) ) plan = result.scalar_one() return await _enrich_plan_with_ordered_qty(db, plan) @router.delete('/monthly-plans/{plan_id}', status_code=204) async def delete_monthly_plan( plan_id: UUID, db: AsyncSession = Depends(get_db), _=Depends(require_permission('PROCUREMENT', 'delete')) ): """Delete a Monthly Plan and all its lines.""" result = await db.execute( select(MonthlyProcurementPlan).where(MonthlyProcurementPlan.plan_id == plan_id) ) plan = result.scalar_one_or_none() if not plan: raise HTTPException(404, 'Monthly plan not found') await db.delete(plan) await db.commit()