from fastapi import APIRouter, Depends, HTTPException, Query, status, File, UploadFile from sqlalchemy import func, extract, update, distinct import os import shutil from sqlalchemy.ext.asyncio import AsyncSession from sqlalchemy.future import select from sqlalchemy.orm import selectinload from typing import List, Optional from uuid import UUID from datetime import date, datetime from app.core.security import require_permission from app.models.core_models import LoginUsers from app.core.database import get_db from app.models.maintenance_models import ( MaintenanceAsset, MaintenanceChecklistItem, MaintenanceRecord, MaintenanceRecordAction, MaintenancePMSchedule, IncomingMaterial, MonthlyStock, EquipmentPurchase, EquipmentRental, ) from . import schemas router = APIRouter() # ─── ASSETS ─────────────────────────────────────────────────────────────────── @router.get("/assets", response_model=List[schemas.MaintenanceAssetOut]) async def list_assets( machine_type: Optional[str] = Query(None), is_active: Optional[bool] = Query(None), db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'view')) ): stmt = select(MaintenanceAsset).order_by(MaintenanceAsset.machine_type, MaintenanceAsset.machine_number) if machine_type: stmt = stmt.where(MaintenanceAsset.machine_type == machine_type) if is_active is not None: stmt = stmt.where(MaintenanceAsset.is_active == is_active) result = await db.execute(stmt) return result.scalars().all() @router.post("/assets", response_model=schemas.MaintenanceAssetOut, status_code=status.HTTP_201_CREATED) async def create_asset(payload: schemas.MaintenanceAssetCreate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'create'))): asset = MaintenanceAsset(**payload.model_dump()) db.add(asset) await db.commit() await db.refresh(asset) return asset @router.get("/assets/{asset_id}", response_model=schemas.MaintenanceAssetOut) async def get_asset(asset_id: UUID, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'view'))): asset = await db.get(MaintenanceAsset, asset_id) if not asset: raise HTTPException(status_code=404, detail="Asset not found") return asset @router.put("/assets/{asset_id}", response_model=schemas.MaintenanceAssetOut) async def update_asset(asset_id: UUID, payload: schemas.MaintenanceAssetUpdate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'edit'))): asset = await db.get(MaintenanceAsset, asset_id) if not asset: raise HTTPException(status_code=404, detail="Asset not found") for key, value in payload.model_dump(exclude_unset=True).items(): setattr(asset, key, value) await db.commit() await db.refresh(asset) return asset @router.delete("/assets/{asset_id}", status_code=status.HTTP_204_NO_CONTENT) async def delete_asset(asset_id: UUID, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'delete'))): asset = await db.get(MaintenanceAsset, asset_id) if not asset: raise HTTPException(status_code=404, detail="Asset not found") await db.delete(asset) await db.commit() # ─── CHECKLIST ──────────────────────────────────────────────────────────────── @router.get("/checklist", response_model=List[schemas.ChecklistItemOut]) async def list_checklist( machine_type: Optional[str] = Query(None), db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'view')) ): stmt = select(MaintenanceChecklistItem).order_by( MaintenanceChecklistItem.machine_type, MaintenanceChecklistItem.sr_no ) if machine_type: stmt = stmt.where(MaintenanceChecklistItem.machine_type == machine_type) result = await db.execute(stmt) return result.scalars().all() @router.post("/checklist", response_model=schemas.ChecklistItemOut, status_code=status.HTTP_201_CREATED) async def create_checklist_item(payload: schemas.ChecklistItemCreate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'create'))): item = MaintenanceChecklistItem(**payload.model_dump()) db.add(item) await db.commit() await db.refresh(item) return item @router.put("/checklist/{item_id}", response_model=schemas.ChecklistItemOut) async def update_checklist_item(item_id: UUID, payload: schemas.ChecklistItemUpdate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'update'))): item = await db.get(MaintenanceChecklistItem, item_id) if not item: raise HTTPException(status_code=404, detail="Checklist item not found") update_data = payload.model_dump(exclude_unset=True) for key, value in update_data.items(): setattr(item, key, value) await db.commit() await db.refresh(item) return item @router.delete("/checklist/{item_id}", status_code=status.HTTP_204_NO_CONTENT) async def delete_checklist_item(item_id: UUID, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'delete'))): item = await db.get(MaintenanceChecklistItem, item_id) if not item: raise HTTPException(status_code=404, detail="Checklist item not found") await db.delete(item) await db.commit() # ─── MAINTENANCE RECORDS ────────────────────────────────────────────────────── @router.post("/upload-photo") async def upload_maintenance_photo( file: UploadFile = File(...), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'create')) ): import uuid upload_dir = "uploads/maintenance" os.makedirs(upload_dir, exist_ok=True) # Generate unique filename ext = file.filename.split('.')[-1] if '.' in file.filename else 'jpg' filename = f"{uuid.uuid4().hex}.{ext}" filepath = os.path.join(upload_dir, filename) with open(filepath, "wb") as buffer: shutil.copyfileobj(file.file, buffer) return {"url": f"/uploads/maintenance/{filename}"} @router.get("/records", response_model=List[schemas.MaintenanceRecordListOut]) async def list_records( asset_id: Optional[UUID] = Query(None), maintenance_type: Optional[str] = Query(None), status: Optional[str] = Query(None), priority: Optional[str] = Query(None), date_from: Optional[date] = Query(None), date_to: Optional[date] = Query(None), skip: int = Query(0, ge=0), limit: int = Query(200, ge=1, le=1000), db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'view')) ): stmt = select(MaintenanceRecord).order_by(MaintenanceRecord.record_date.desc()) if asset_id: stmt = stmt.where(MaintenanceRecord.asset_id == asset_id) if maintenance_type: stmt = stmt.where(MaintenanceRecord.maintenance_type == maintenance_type) if status: stmt = stmt.where(MaintenanceRecord.status == status) if priority: stmt = stmt.where(MaintenanceRecord.priority == priority) if date_from: stmt = stmt.where(MaintenanceRecord.record_date >= date_from) if date_to: stmt = stmt.where(MaintenanceRecord.record_date <= date_to) stmt = stmt.offset(skip).limit(limit) result = await db.execute(stmt) return result.scalars().all() @router.post("/records", response_model=schemas.MaintenanceRecordOut, status_code=status.HTTP_201_CREATED) async def create_record(payload: schemas.MaintenanceRecordCreate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'create'))): # Verify asset exists asset = await db.get(MaintenanceAsset, payload.asset_id) if not asset: raise HTTPException(status_code=404, detail="Asset not found") actions_data = payload.actions record_data = payload.model_dump(exclude={'actions'}) record = MaintenanceRecord(**record_data) db.add(record) await db.flush() # Get the record ID # Auto-generate WO number: WO-YYYYMMDD-NNN date_str = record.record_date.strftime('%Y%m%d') # Count existing records for this date to get sequential number count_stmt = select(func.count()).select_from(MaintenanceRecord).where( MaintenanceRecord.record_date == record.record_date ) count_result = await db.execute(count_stmt) seq = (count_result.scalar() or 0) record.wo_number = f"WO-{date_str}-{seq:03d}" for i, act in enumerate(actions_data): action = MaintenanceRecordAction( record_id=record.id, sr_no=act.sr_no if act.sr_no is not None else i + 1, action_taken=act.action_taken ) db.add(action) await db.commit() # Auto-update PM Schedule if this is a completed PM record if record.maintenance_type == "PM" and record.status == "COMPLETED": pm_stmt = select(MaintenancePMSchedule).where( MaintenancePMSchedule.asset_id == record.asset_id, MaintenancePMSchedule.year == record.record_date.year, MaintenancePMSchedule.month == record.record_date.month ) pm_result = await db.execute(pm_stmt) pm_entry = pm_result.scalar_one_or_none() if pm_entry: pm_entry.status = "DONE" pm_entry.completed_date = record.record_date await db.commit() # Reload with relationships stmt = select(MaintenanceRecord).options(selectinload(MaintenanceRecord.actions)).where(MaintenanceRecord.id == record.id) result = await db.execute(stmt) return result.scalar_one() @router.get("/records/{record_id}", response_model=schemas.MaintenanceRecordOut) async def get_record(record_id: UUID, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'view'))): stmt = select(MaintenanceRecord).options( selectinload(MaintenanceRecord.actions) ).where(MaintenanceRecord.id == record_id) result = await db.execute(stmt) record = result.scalar_one_or_none() if not record: raise HTTPException(status_code=404, detail="Record not found") return record @router.put("/records/{record_id}", response_model=schemas.MaintenanceRecordOut) async def update_record(record_id: UUID, payload: schemas.MaintenanceRecordUpdate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'edit'))): stmt = select(MaintenanceRecord).options(selectinload(MaintenanceRecord.actions)).where(MaintenanceRecord.id == record_id) result = await db.execute(stmt) record = result.scalar_one_or_none() if not record: raise HTTPException(status_code=404, detail="Record not found") update_data = payload.model_dump(exclude_unset=True) actions_data = update_data.pop('actions', None) for key, value in update_data.items(): setattr(record, key, value) if actions_data is not None: # Replace all actions for old_action in record.actions: await db.delete(old_action) await db.flush() for i, act in enumerate(actions_data): action = MaintenanceRecordAction( record_id=record.id, sr_no=act.get('sr_no') if isinstance(act, dict) else act.sr_no, action_taken=act.get('action_taken') if isinstance(act, dict) else act.action_taken ) db.add(action) await db.commit() # Auto-update PM Schedule if this is a completed PM record if record.maintenance_type == "PM" and record.status == "COMPLETED": pm_stmt = select(MaintenancePMSchedule).where( MaintenancePMSchedule.asset_id == record.asset_id, MaintenancePMSchedule.year == record.record_date.year, MaintenancePMSchedule.month == record.record_date.month ) pm_result = await db.execute(pm_stmt) pm_entry = pm_result.scalar_one_or_none() if pm_entry: pm_entry.status = "DONE" pm_entry.completed_date = record.record_date await db.commit() stmt = select(MaintenanceRecord).options(selectinload(MaintenanceRecord.actions)).where(MaintenanceRecord.id == record_id) result = await db.execute(stmt) return result.scalar_one() @router.delete("/records/{record_id}", status_code=status.HTTP_204_NO_CONTENT) async def delete_record(record_id: UUID, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'delete'))): record = await db.get(MaintenanceRecord, record_id) if not record: raise HTTPException(status_code=404, detail="Record not found") await db.delete(record) await db.commit() # ─── PM SCHEDULE ───────────────────────────────────────────────────────────── @router.get("/schedule", response_model=List[schemas.PMScheduleOut]) async def list_schedule( year: int = Query(...), month: int = Query(...), db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'view')) ): stmt = ( select(MaintenancePMSchedule) .options(selectinload(MaintenancePMSchedule.asset)) .where( MaintenancePMSchedule.year == year, MaintenancePMSchedule.month == month ) .order_by(MaintenancePMSchedule.scheduled_date) ) result = await db.execute(stmt) return result.scalars().all() @router.post("/schedule", response_model=schemas.PMScheduleOut, status_code=status.HTTP_201_CREATED) async def create_schedule_entry(payload: schemas.PMScheduleCreate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'create'))): entry = MaintenancePMSchedule(**payload.model_dump()) db.add(entry) await db.commit() stmt = select(MaintenancePMSchedule).options(selectinload(MaintenancePMSchedule.asset)).where(MaintenancePMSchedule.id == entry.id) result = await db.execute(stmt) return result.scalar_one() @router.put("/schedule/{entry_id}", response_model=schemas.PMScheduleOut) async def update_schedule_entry(entry_id: UUID, payload: schemas.PMScheduleUpdate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'edit'))): entry = await db.get(MaintenancePMSchedule, entry_id) if not entry: raise HTTPException(status_code=404, detail="Schedule entry not found") for key, value in payload.model_dump(exclude_unset=True).items(): setattr(entry, key, value) await db.commit() stmt = select(MaintenancePMSchedule).options(selectinload(MaintenancePMSchedule.asset)).where(MaintenancePMSchedule.id == entry_id) result = await db.execute(stmt) return result.scalar_one() @router.delete("/schedule/{entry_id}", status_code=status.HTTP_204_NO_CONTENT) async def delete_schedule_entry(entry_id: UUID, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'delete'))): entry = await db.get(MaintenancePMSchedule, entry_id) if not entry: raise HTTPException(status_code=404, detail="Schedule entry not found") await db.delete(entry) await db.commit() # ─── DASHBOARD ──────────────────────────────────────────────────────────────── @router.get("/dashboard", response_model=schemas.MaintenanceDashboardStats) async def get_dashboard(db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'view'))): now = datetime.utcnow() year, month = now.year, now.month today = now.date() total_assets = (await db.execute(select(func.count()).select_from(MaintenanceAsset))).scalar_one() active_assets = (await db.execute(select(func.count()).select_from(MaintenanceAsset).where(MaintenanceAsset.is_active == True))).scalar_one() pm_this_month = (await db.execute( select(func.count()).select_from(MaintenanceRecord).where( MaintenanceRecord.maintenance_type == 'PM', extract('year', MaintenanceRecord.record_date) == year, extract('month', MaintenanceRecord.record_date) == month ) )).scalar_one() bd_this_month = (await db.execute( select(func.count()).select_from(MaintenanceRecord).where( MaintenanceRecord.maintenance_type == 'BD', extract('year', MaintenanceRecord.record_date) == year, extract('month', MaintenanceRecord.record_date) == month ) )).scalar_one() total_records = (await db.execute(select(func.count()).select_from(MaintenanceRecord))).scalar_one() planned_this_month = (await db.execute( select(func.count()).select_from(MaintenancePMSchedule).where( MaintenancePMSchedule.year == year, MaintenancePMSchedule.month == month, MaintenancePMSchedule.status == 'PLANNED' ) )).scalar_one() done_this_month = (await db.execute( select(func.count()).select_from(MaintenancePMSchedule).where( MaintenancePMSchedule.year == year, MaintenancePMSchedule.month == month, MaintenancePMSchedule.status == 'DONE' ) )).scalar_one() overdue_count = (await db.execute( select(func.count()).select_from(MaintenancePMSchedule).where( MaintenancePMSchedule.status == 'PLANNED', MaintenancePMSchedule.scheduled_date < today ) )).scalar_one() incoming_material_entries = (await db.execute(select(func.count()).select_from(IncomingMaterial))).scalar_one() monthly_stock_entries = (await db.execute( select(func.count()).select_from(MonthlyStock).where( MonthlyStock.stock_year == year, MonthlyStock.stock_month == month ) )).scalar_one() equipment_purchase_entries = (await db.execute( select(func.count()).select_from(EquipmentPurchase).where( EquipmentPurchase.purchase_year == year, EquipmentPurchase.purchase_month == month ) )).scalar_one() equipment_rental_entries = (await db.execute( select(func.count()).select_from(EquipmentRental).where( extract('year', EquipmentRental.rental_date) == year, extract('month', EquipmentRental.rental_date) == month ) )).scalar_one() return schemas.MaintenanceDashboardStats( total_assets=total_assets, active_assets=active_assets, pm_this_month=pm_this_month, bd_this_month=bd_this_month, total_records=total_records, planned_this_month=planned_this_month, done_this_month=done_this_month, overdue_count=overdue_count, incoming_material_entries=incoming_material_entries, monthly_stock_entries=monthly_stock_entries, equipment_purchase_entries=equipment_purchase_entries, equipment_rental_entries=equipment_rental_entries, ) from app.models.maintenance_models import DieselRecord, LPGGasRecord, SolarMaintenanceRecord # ─── DIESEL RECORD ──────────────────────────────────────────────────────────── @router.get("/diesel-records", response_model=List[schemas.DieselRecordOut]) async def list_diesel_records( db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'view')), ): stmt = select(DieselRecord).order_by(DieselRecord.entry_date.desc()) result = await db.execute(stmt) return result.scalars().all() @router.post("/diesel-records", response_model=schemas.DieselRecordOut, status_code=status.HTTP_201_CREATED) async def create_diesel_record(payload: schemas.DieselRecordCreate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'create'))): item = DieselRecord(**payload.model_dump()) db.add(item) await db.commit() await db.refresh(item) return item @router.delete("/diesel-records/{item_id}", status_code=status.HTTP_204_NO_CONTENT) async def delete_diesel_record(item_id: UUID, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'delete'))): item = await db.get(DieselRecord, item_id) if not item: raise HTTPException(status_code=404, detail="Item not found") await db.delete(item) await db.commit() @router.put("/diesel-records/{item_id}", response_model=schemas.DieselRecordOut) async def update_diesel_record(item_id: UUID, payload: schemas.DieselRecordUpdate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'edit'))): item = await db.get(DieselRecord, item_id) if not item: raise HTTPException(status_code=404, detail="Item not found") for k, v in payload.model_dump(exclude_unset=True).items(): setattr(item, k, v) await db.commit() await db.refresh(item) return item # ─── LPG GAS RECORD ─────────────────────────────────────────────────────────── @router.get("/lpg-gas-records", response_model=List[schemas.LPGGasRecordOut]) async def list_lpg_gas_records( db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'view')), ): stmt = select(LPGGasRecord).order_by(LPGGasRecord.entry_date.desc()) result = await db.execute(stmt) return result.scalars().all() @router.post("/lpg-gas-records", response_model=schemas.LPGGasRecordOut, status_code=status.HTTP_201_CREATED) async def create_lpg_gas_record(payload: schemas.LPGGasRecordCreate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'create'))): total = (payload.kg * payload.price) if payload.price else None item = LPGGasRecord(**payload.model_dump(), total_amount=total) db.add(item) await db.commit() await db.refresh(item) return item @router.delete("/lpg-gas-records/{item_id}", status_code=status.HTTP_204_NO_CONTENT) async def delete_lpg_gas_record(item_id: UUID, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'delete'))): item = await db.get(LPGGasRecord, item_id) if not item: raise HTTPException(status_code=404, detail="Item not found") await db.delete(item) await db.commit() @router.put("/lpg-gas-records/{item_id}", response_model=schemas.LPGGasRecordOut) async def update_lpg_gas_record(item_id: UUID, payload: schemas.LPGGasRecordUpdate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'edit'))): item = await db.get(LPGGasRecord, item_id) if not item: raise HTTPException(status_code=404, detail="Item not found") data = payload.model_dump(exclude_unset=True) # Recalculate total_amount if kg or price changed kg = data.get('kg', item.kg) price = data.get('price', item.price) if price: data['total_amount'] = (data.get('quantity', item.quantity) or item.quantity) * kg * price for k, v in data.items(): setattr(item, k, v) await db.commit() await db.refresh(item) return item # ─── SOLAR MAINTENANCE ──────────────────────────────────────────────────────── @router.get("/solar-maintenance", response_model=List[schemas.SolarMaintenanceOut]) async def list_solar_maintenance( db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'view')), ): stmt = select(SolarMaintenanceRecord).order_by(SolarMaintenanceRecord.entry_date.desc()) result = await db.execute(stmt) return result.scalars().all() @router.post("/solar-maintenance", response_model=schemas.SolarMaintenanceOut, status_code=status.HTTP_201_CREATED) async def create_solar_maintenance(payload: schemas.SolarMaintenanceCreate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'create'))): total = payload.quantity * payload.price item = SolarMaintenanceRecord(**payload.model_dump(), total_amount=total) db.add(item) await db.commit() await db.refresh(item) return item @router.delete("/solar-maintenance/{item_id}", status_code=status.HTTP_204_NO_CONTENT) async def delete_solar_maintenance(item_id: UUID, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'delete'))): item = await db.get(SolarMaintenanceRecord, item_id) if not item: raise HTTPException(status_code=404, detail="Item not found") await db.delete(item) await db.commit() @router.put("/solar-maintenance/{item_id}", response_model=schemas.SolarMaintenanceOut) async def update_solar_maintenance(item_id: UUID, payload: schemas.SolarMaintenanceUpdate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'edit'))): item = await db.get(SolarMaintenanceRecord, item_id) if not item: raise HTTPException(status_code=404, detail="Item not found") data = payload.model_dump(exclude_unset=True) # Recalculate total_amount if quantity or price changed qty = data.get('quantity', item.quantity) price = data.get('price', item.price) data['total_amount'] = qty * price for k, v in data.items(): setattr(item, k, v) await db.commit() await db.refresh(item) return item # ─── INCOMING MATERIAL ──────────────────────────────────────────────────────── @router.get("/incoming-material", response_model=List[schemas.IncomingMaterialOut]) async def list_incoming_material( vendor: Optional[str] = Query(None), category: Optional[str] = Query(None), date_from: Optional[date] = Query(None), date_to: Optional[date] = Query(None), db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'view')), ): stmt = select(IncomingMaterial).order_by(IncomingMaterial.entry_date.desc()) if vendor: stmt = stmt.where(IncomingMaterial.vendor.ilike(f"%{vendor}%")) if category: stmt = stmt.where(IncomingMaterial.category == category) if date_from: stmt = stmt.where(IncomingMaterial.entry_date >= date_from) if date_to: stmt = stmt.where(IncomingMaterial.entry_date <= date_to) result = await db.execute(stmt) return result.scalars().all() @router.post("/incoming-material", response_model=schemas.IncomingMaterialOut, status_code=status.HTTP_201_CREATED) async def create_incoming_material(payload: schemas.IncomingMaterialCreate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'create'))): total = None if payload.rate: total = payload.quantity * payload.rate if payload.discount: total -= payload.discount item = IncomingMaterial(**payload.model_dump(), total_amount=total) db.add(item) await db.commit() await db.refresh(item) return item @router.put("/incoming-material/{item_id}", response_model=schemas.IncomingMaterialOut) async def update_incoming_material(item_id: UUID, payload: schemas.IncomingMaterialUpdate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'edit'))): item = await db.get(IncomingMaterial, item_id) if not item: raise HTTPException(status_code=404, detail="Item not found") data = payload.model_dump(exclude_unset=True) for k, v in data.items(): setattr(item, k, v) # Recalculate total if item.rate: item.total_amount = item.quantity * item.rate if item.discount: item.total_amount -= item.discount else: item.total_amount = None await db.commit() await db.refresh(item) return item @router.delete("/incoming-material/{item_id}", status_code=status.HTTP_204_NO_CONTENT) async def delete_incoming_material(item_id: UUID, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'delete'))): item = await db.get(IncomingMaterial, item_id) if not item: raise HTTPException(status_code=404, detail="Item not found") await db.delete(item) await db.commit() @router.get("/incoming-material/vendors", response_model=List[str]) async def list_incoming_vendors(db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'view'))): """Return all distinct vendor names from incoming material records, sorted.""" result = await db.execute( select(distinct(IncomingMaterial.vendor)) .where(IncomingMaterial.vendor.isnot(None)) .order_by(IncomingMaterial.vendor) ) return [row[0] for row in result.all()] @router.post("/incoming-material/merge-vendor", response_model=dict) async def merge_incoming_vendor( from_vendor: str = Query(..., description="The vendor name to rename/merge FROM"), to_vendor: str = Query(..., description="The canonical vendor name to rename/merge TO"), db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'edit')), ): """ Bulk-rename all IncomingMaterial records where vendor == from_vendor to to_vendor. Use this to fix spelling variants like 'Sri Ram' → 'Shri Ram'. """ if not from_vendor.strip() or not to_vendor.strip(): raise HTTPException(status_code=400, detail="Both from_vendor and to_vendor are required.") if from_vendor.strip().lower() == to_vendor.strip().lower(): raise HTTPException(status_code=400, detail="from_vendor and to_vendor are the same.") stmt = ( update(IncomingMaterial) .where(IncomingMaterial.vendor == from_vendor.strip()) .values(vendor=to_vendor.strip()) ) result = await db.execute(stmt) await db.commit() updated = result.rowcount return {"updated": updated, "from_vendor": from_vendor.strip(), "to_vendor": to_vendor.strip()} # ─── MONTHLY STOCK ──────────────────────────────────────────────────────────── @router.get("/monthly-stock", response_model=List[schemas.MonthlyStockOut]) async def list_monthly_stock( stock_year: Optional[int] = Query(None), stock_month: Optional[int] = Query(None), db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'view')), ): stmt = select(MonthlyStock).order_by(MonthlyStock.category_order, MonthlyStock.created_at, MonthlyStock.id) if stock_year: stmt = stmt.where(MonthlyStock.stock_year == stock_year) if stock_month: stmt = stmt.where(MonthlyStock.stock_month == stock_month) result = await db.execute(stmt) return result.scalars().all() @router.post("/monthly-stock/copy-previous", response_model=dict) async def copy_monthly_stock( source_month: int = Query(...), source_year: int = Query(...), target_month: int = Query(...), target_year: int = Query(...), db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'create')), ): # Fetch source data stmt = select(MonthlyStock).where(MonthlyStock.stock_month == source_month, MonthlyStock.stock_year == source_year) result = await db.execute(stmt) source_items = result.scalars().all() if not source_items: raise HTTPException(status_code=400, detail="No data found in the source month to copy.") # Check if target data already exists stmt_target = select(MonthlyStock).where(MonthlyStock.stock_month == target_month, MonthlyStock.stock_year == target_year) result_target = await db.execute(stmt_target) if result_target.scalars().first(): raise HTTPException(status_code=400, detail="Data already exists for the target month.") new_entries = [] for item in source_items: opening_qty = item.closing_qty opening_amt = opening_qty * item.cost # New entry starts with 0 incoming and consumption total_qty = opening_qty total_amt = opening_amt closing_qty = opening_qty closing_amt = opening_amt entry = MonthlyStock( stock_month=target_month, stock_year=target_year, category=item.category, category_order=item.category_order, material_name=item.material_name, uom=item.uom, cost=item.cost, opening_qty=opening_qty, opening_amount=opening_amt, incoming_qty=0, incoming_amount=0, total_qty=total_qty, total_amount=total_amt, consumption_qty=0, consumption_amount=0, closing_qty=closing_qty, closing_amount=closing_amt, ) new_entries.append(entry) db.add(entry) await db.commit() return {"message": f"Successfully copied {len(new_entries)} items to {target_month}/{target_year}"} @router.post("/monthly-stock", response_model=schemas.MonthlyStockOut, status_code=status.HTTP_201_CREATED) async def create_monthly_stock(payload: schemas.MonthlyStockCreate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'create'))): # Auto-calculate amounts opening_amt = payload.opening_qty * payload.cost incoming_amt = payload.incoming_qty * payload.cost total_qty = payload.opening_qty + payload.incoming_qty total_amt = total_qty * payload.cost consumption_amt = payload.consumption_qty * payload.cost closing_qty = total_qty - payload.consumption_qty closing_amt = closing_qty * payload.cost entry = MonthlyStock( stock_month=payload.stock_month, stock_year=payload.stock_year, category=payload.category, material_name=payload.material_name, uom=payload.uom, cost=payload.cost, opening_qty=payload.opening_qty, opening_amount=opening_amt, incoming_qty=payload.incoming_qty, incoming_amount=incoming_amt, total_qty=total_qty, total_amount=total_amt, consumption_qty=payload.consumption_qty, consumption_amount=consumption_amt, closing_qty=closing_qty, closing_amount=closing_amt, ) db.add(entry) await db.commit() await db.refresh(entry) return entry @router.put("/monthly-stock/{entry_id}", response_model=schemas.MonthlyStockOut) async def update_monthly_stock(entry_id: UUID, payload: schemas.MonthlyStockUpdate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'edit'))): entry = await db.get(MonthlyStock, entry_id) if not entry: raise HTTPException(status_code=404, detail="Entry not found") data = payload.model_dump(exclude_unset=True) for k, v in data.items(): setattr(entry, k, v) # Re-calculate entry.opening_amount = entry.opening_qty * entry.cost entry.incoming_amount = entry.incoming_qty * entry.cost entry.total_qty = entry.opening_qty + entry.incoming_qty entry.total_amount = entry.total_qty * entry.cost entry.consumption_amount = entry.consumption_qty * entry.cost entry.closing_qty = entry.total_qty - entry.consumption_qty entry.closing_amount = entry.closing_qty * entry.cost await db.commit() await db.refresh(entry) return entry @router.delete("/monthly-stock/{entry_id}", status_code=status.HTTP_204_NO_CONTENT) async def delete_monthly_stock(entry_id: UUID, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'delete'))): entry = await db.get(MonthlyStock, entry_id) if not entry: raise HTTPException(status_code=404, detail="Entry not found") await db.delete(entry) await db.commit() # ─── EQUIPMENT PURCHASE ─────────────────────────────────────────────────────── @router.get("/equipment-purchase", response_model=List[schemas.EquipmentPurchaseOut]) async def list_equipment_purchase( month: Optional[int] = Query(None), year: Optional[int] = Query(None), db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'view')), ): stmt = select(EquipmentPurchase).order_by( EquipmentPurchase.purchase_year.desc(), EquipmentPurchase.purchase_month.desc() ) if month: stmt = stmt.where(EquipmentPurchase.purchase_month == month) if year: stmt = stmt.where(EquipmentPurchase.purchase_year == year) result = await db.execute(stmt) return result.scalars().all() @router.post("/equipment-purchase", response_model=schemas.EquipmentPurchaseOut, status_code=status.HTTP_201_CREATED) async def create_equipment_purchase(payload: schemas.EquipmentPurchaseCreate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'create'))): total = payload.quantity * payload.unit_price item = EquipmentPurchase(**payload.model_dump(), total_amount=total) db.add(item) await db.commit() await db.refresh(item) return item @router.put("/equipment-purchase/{item_id}", response_model=schemas.EquipmentPurchaseOut) async def update_equipment_purchase(item_id: UUID, payload: schemas.EquipmentPurchaseUpdate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'edit'))): item = await db.get(EquipmentPurchase, item_id) if not item: raise HTTPException(status_code=404, detail="Item not found") for k, v in payload.model_dump(exclude_unset=True).items(): setattr(item, k, v) item.total_amount = item.quantity * item.unit_price await db.commit() await db.refresh(item) return item @router.delete("/equipment-purchase/{item_id}", status_code=status.HTTP_204_NO_CONTENT) async def delete_equipment_purchase(item_id: UUID, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'delete'))): item = await db.get(EquipmentPurchase, item_id) if not item: raise HTTPException(status_code=404, detail="Item not found") await db.delete(item) await db.commit() # ─── EQUIPMENT RENTAL ──────────────────────────────────────────────────────── @router.get("/equipment-rental", response_model=List[schemas.EquipmentRentalOut]) async def list_equipment_rental( vendor: Optional[str] = Query(None), date_from: Optional[date] = Query(None), date_to: Optional[date] = Query(None), db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'view')), ): stmt = select(EquipmentRental).order_by(EquipmentRental.rental_date.desc()) if vendor: stmt = stmt.where(EquipmentRental.vendor.ilike(f"%{vendor}%")) if date_from: stmt = stmt.where(EquipmentRental.rental_date >= date_from) if date_to: stmt = stmt.where(EquipmentRental.rental_date <= date_to) result = await db.execute(stmt) return result.scalars().all() @router.post("/equipment-rental", response_model=schemas.EquipmentRentalOut, status_code=status.HTTP_201_CREATED) async def create_equipment_rental(payload: schemas.EquipmentRentalCreate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'create'))): item = EquipmentRental(**payload.model_dump()) db.add(item) await db.commit() await db.refresh(item) return item @router.put("/equipment-rental/{item_id}", response_model=schemas.EquipmentRentalOut) async def update_equipment_rental(item_id: UUID, payload: schemas.EquipmentRentalUpdate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'edit'))): item = await db.get(EquipmentRental, item_id) if not item: raise HTTPException(status_code=404, detail="Item not found") for k, v in payload.model_dump(exclude_unset=True).items(): setattr(item, k, v) await db.commit() await db.refresh(item) return item @router.delete("/equipment-rental/{item_id}", status_code=status.HTTP_204_NO_CONTENT) async def delete_equipment_rental(item_id: UUID, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'delete'))): item = await db.get(EquipmentRental, item_id) if not item: raise HTTPException(status_code=404, detail="Item not found") await db.delete(item) await db.commit() # ─── MACHINE CONSUMPTION COST ──────────────────────────────────────────────── from app.models.maintenance_models import MachineConsumptionCost @router.get("/machine-cost", response_model=List[schemas.MachineCostOut]) async def list_machine_cost( year: Optional[int] = Query(None), db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'view')), ): stmt = select(MachineConsumptionCost).order_by( MachineConsumptionCost.display_order, MachineConsumptionCost.created_at ) if year: stmt = stmt.where(MachineConsumptionCost.year == year) result = await db.execute(stmt) return result.scalars().all() @router.post("/machine-cost", response_model=schemas.MachineCostOut, status_code=status.HTTP_201_CREATED) async def create_machine_cost(payload: schemas.MachineCostCreate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'create'))): item = MachineConsumptionCost(**payload.model_dump()) db.add(item) await db.commit() await db.refresh(item) return item @router.put("/machine-cost/{item_id}", response_model=schemas.MachineCostOut) async def update_machine_cost(item_id: UUID, payload: schemas.MachineCostUpdate, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'edit'))): item = await db.get(MachineConsumptionCost, item_id) if not item: raise HTTPException(status_code=404, detail="Item not found") for k, v in payload.model_dump(exclude_unset=True).items(): setattr(item, k, v) await db.commit() await db.refresh(item) return item @router.delete("/machine-cost/{item_id}", status_code=status.HTTP_204_NO_CONTENT) async def delete_machine_cost(item_id: UUID, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'delete'))): item = await db.get(MachineConsumptionCost, item_id) if not item: raise HTTPException(status_code=404, detail="Item not found") await db.delete(item) await db.commit() # ─── DAILY CONSUMABLES (GAS & WIRE) ────────────────────────────────────────── from app.models.maintenance_models import DailyConsumableStock @router.get("/consumables/daily", response_model=List[schemas.DailyConsumableStockOut]) async def list_daily_consumables( year: int = Query(...), month: int = Query(...), consumable_type: str = Query(...), db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'view')), ): stmt = select(DailyConsumableStock).where( extract('year', DailyConsumableStock.record_date) == year, extract('month', DailyConsumableStock.record_date) == month, DailyConsumableStock.consumable_type == consumable_type ).order_by(DailyConsumableStock.record_date) result = await db.execute(stmt) return result.scalars().all() @router.post("/consumables/daily/batch", response_model=List[schemas.DailyConsumableStockOut]) async def batch_upsert_daily_consumables( payload: schemas.DailyConsumableBatchUpdateReq, db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'create')), ): # Fetch existing stmt = select(DailyConsumableStock).where( extract('year', DailyConsumableStock.record_date) == payload.year, extract('month', DailyConsumableStock.record_date) == payload.month, DailyConsumableStock.consumable_type == payload.consumable_type ) result = await db.execute(stmt) existing_map = {item.record_date: item for item in result.scalars().all()} # Compute ripple current_opening = payload.initial_opening_stock processed = [] for d in sorted(payload.days_data, key=lambda x: x.record_date): total = current_opening + d.received_stock closing = total - d.consumed_stock if d.record_date in existing_map: item = existing_map[d.record_date] item.opening_stock = current_opening item.received_stock = d.received_stock item.total_stock = total item.consumed_stock = d.consumed_stock item.closing_stock = closing item.remark = d.remark else: item = DailyConsumableStock( record_date=d.record_date, consumable_type=d.consumable_type, opening_stock=current_opening, received_stock=d.received_stock, total_stock=total, consumed_stock=d.consumed_stock, closing_stock=closing, remark=d.remark ) db.add(item) processed.append(item) current_opening = closing await db.commit() # Refetch result = await db.execute(stmt.order_by(DailyConsumableStock.record_date)) return result.scalars().all() # ─── COST VS PRODUCTION ANALYTICS ──────────────────────────────────────────── from decimal import Decimal as D MONTH_NAMES = ['', 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'] MONTH_FIELD_MAP = { 1: 'jan_cost', 2: 'feb_cost', 3: 'mar_cost', 4: 'apr_cost', 5: 'may_cost', 6: 'jun_cost', 7: 'jul_cost', 8: 'aug_cost', 9: 'sep_cost', 10: 'oct_cost', 11: 'nov_cost', 12: 'dec_cost', } # Alert thresholds — cost per bus (₹) ALERT_THRESHOLD = 3000 # ₹3,000/bus → ALERT (red) WATCH_THRESHOLD = 1500 # ₹1,500/bus → WATCH (amber) def _get_alert(cost_per_bus: Optional[D], buses: int) -> tuple[str, Optional[str]]: """Return (alert_level, alert_message) for a machine row.""" if buses == 0: if cost_per_bus is None: return "OK", None return "WATCH", f"₹{cost_per_bus:,.0f} spent with 0 buses produced — verify production data" if cost_per_bus is None: return "OK", None if cost_per_bus >= ALERT_THRESHOLD: return "ALERT", f"₹{cost_per_bus:,.0f}/bus is very high — investigate machine aging or faulty spares" if cost_per_bus >= WATCH_THRESHOLD: return "WATCH", f"₹{cost_per_bus:,.0f}/bus — monitor this machine closely" return "OK", None @router.get("/cost-vs-production", response_model=schemas.CostVsProductionOut) async def cost_vs_production( year: int = Query(..., description="Year (e.g. 2026)"), month: int = Query(..., ge=1, le=12, description="Month number 1-12"), db: AsyncSession = Depends(get_db), current_user: LoginUsers = Depends(require_permission('MAINTENANCE', 'view')) ): """ Analytics: For each machine, compare monthly maintenance cost against the number of buses dispatched in that period. Calculates cost/bus and flags machines where cost is unjustifiably high relative to production. Bus count is auto-derived from chassis_master.dispatched_at. """ from app.models.maintenance_models import MachineConsumptionCost month_field = MONTH_FIELD_MAP[month] # 1. Get buses dispatched from the manual entry 'Total bus' in machine_consumption_cost total_bus_stmt = select(MachineConsumptionCost).where( MachineConsumptionCost.year == year, MachineConsumptionCost.machine_name.ilike('%Total bus%') ) total_bus_result = await db.execute(total_bus_stmt) total_bus_row = total_bus_result.scalars().first() buses_produced = 0 if total_bus_row: val = getattr(total_bus_row, month_field) try: buses_produced = int(float(val)) if val else 0 except (ValueError, TypeError): buses_produced = 0 # 2. Get machine cost rows for this year (exclude summary rows like 'Total bus', '%/bus') cost_stmt = select(MachineConsumptionCost).where( MachineConsumptionCost.year == year, ~func.lower(MachineConsumptionCost.machine_name).in_(['total cost', 'total bus', '%/ bus', '% / bus']) ).order_by(MachineConsumptionCost.display_order, MachineConsumptionCost.created_at) cost_result = await db.execute(cost_stmt) machine_rows = cost_result.scalars().all() # 3. Build analytics rows machine_items = [] total_cost = D('0') for row in machine_rows: month_cost = D(str(getattr(row, month_field) or 0)) total_cost += month_cost if buses_produced > 0 and month_cost > 0: cost_per_bus = month_cost / buses_produced elif month_cost == 0: cost_per_bus = D('0') else: cost_per_bus = None # cost but no buses alert_level, alert_message = _get_alert(cost_per_bus, buses_produced) machine_items.append(schemas.MachineCostVsProductionItem( machine_name=row.machine_name, month_cost=month_cost, buses_produced=buses_produced, cost_per_bus=round(cost_per_bus, 2) if cost_per_bus is not None else None, alert_level=alert_level, alert_message=alert_message, )) # 4. Overall cost per bus if buses_produced > 0: overall_cost_per_bus = round(total_cost / buses_produced, 2) elif total_cost == 0: overall_cost_per_bus = D('0') else: overall_cost_per_bus = None return schemas.CostVsProductionOut( year=year, month=month, month_name=MONTH_NAMES[month], total_buses_produced=buses_produced, total_maintenance_cost=total_cost, overall_cost_per_bus=overall_cost_per_bus, machines=machine_items, )