""" QA Checksheet service — template CRUD, station mapping, inspection flow, inventory deduction on QA pass, shortage tracking. """ import uuid import logging from datetime import datetime, timezone from decimal import Decimal from typing import Optional, List from uuid import UUID logger = logging.getLogger(__name__) from sqlalchemy import select, func, and_, or_, case, text from sqlalchemy.ext.asyncio import AsyncSession from fastapi import HTTPException from app.models.qa_models import ( QaChecksheetTemplate, QaChecksheetSection, QaCheckpointItem, QaChecksheetInstance, QaCheckpointResponse, QaChecksheetSignoff, QaStationTemplateMap, QaShortageLog, ) from app.models.rm_models import StationMaster, RmMaster, RmInventory, RmInventoryLog from app.models.station_work_models import QaResponsePhoto, QaSectionRemark, StationUserAssignment from app.modules.station_work import flow from app.models.yard_models import ChassisMaster, VehicleModelMaster from app.models.dealer_models import BodyTypeMaster, DealerOrderVehicle, DealerOrderVehicleChassis from app.models.procurement_models import ChassisBomSnapshot from app.models.production_models import ChassisStationLog from app.models.core_models import LoginUsers from app.modules.station_work.routes import assigned_station_ids from . import schemas def _now(): return datetime.now(timezone.utc) # ═══════════════════════════════════════════════════════════ # TEMPLATE CRUD # ═══════════════════════════════════════════════════════════ async def list_templates(db: AsyncSession) -> List[schemas.TemplateOut]: result = await db.execute( select(QaChecksheetTemplate).order_by(QaChecksheetTemplate.template_code) ) templates = result.scalars().all() out = [] for t in templates: station_name = None if t.station_id: st = await db.get(StationMaster, t.station_id) if st: station_name = st.station_name out.append(schemas.TemplateOut( template_id=t.template_id, template_code=t.template_code, template_name=t.template_name, station_id=t.station_id, station_name=station_name, format_no=t.format_no, revision=t.revision, revision_date=t.revision_date, description=t.description, is_active=t.is_active, )) return out async def create_template(db: AsyncSession, req: schemas.TemplateCreate) -> schemas.TemplateOut: t = QaChecksheetTemplate( template_id=uuid.uuid4(), template_code=req.template_code, template_name=req.template_name, station_id=req.station_id, format_no=req.format_no, revision=req.revision, revision_date=req.revision_date, description=req.description, ) db.add(t) await db.commit() await db.refresh(t) return schemas.TemplateOut( template_id=t.template_id, template_code=t.template_code, template_name=t.template_name, station_id=t.station_id, format_no=t.format_no, revision=t.revision, revision_date=t.revision_date, description=t.description, is_active=t.is_active, ) async def update_template(db: AsyncSession, template_id: UUID, req: schemas.TemplateUpdate) -> schemas.TemplateOut: t = await db.get(QaChecksheetTemplate, template_id) if not t: raise HTTPException(404, "Template not found") for field, val in req.model_dump(exclude_unset=True).items(): setattr(t, field, val) await db.commit() await db.refresh(t) return schemas.TemplateOut( template_id=t.template_id, template_code=t.template_code, template_name=t.template_name, station_id=t.station_id, format_no=t.format_no, revision=t.revision, revision_date=t.revision_date, description=t.description, is_active=t.is_active, ) async def delete_template(db: AsyncSession, template_id: UUID) -> None: t = await db.get(QaChecksheetTemplate, template_id) if not t: raise HTTPException(404, "Template not found") t.is_active = False await db.commit() async def get_template_full(db: AsyncSession, template_id: UUID) -> schemas.TemplateOut: t = await db.get(QaChecksheetTemplate, template_id) if not t: raise HTTPException(404, "Template not found") sections_result = await db.execute( select(QaChecksheetSection).where( QaChecksheetSection.template_id == template_id, QaChecksheetSection.is_active == True, ).order_by(QaChecksheetSection.sort_order) ) sections = sections_result.scalars().all() section_list = [] for sec in sections: items_result = await db.execute( select(QaCheckpointItem).where( QaCheckpointItem.section_id == sec.section_id, QaCheckpointItem.is_active == True, ).order_by(QaCheckpointItem.sort_order) ) items = items_result.scalars().all() section_list.append(schemas.SectionOut( section_id=sec.section_id, template_id=sec.template_id, section_no=sec.section_no, section_name=sec.section_name, description=sec.description, sort_order=sec.sort_order, items=[schemas.ItemOut.model_validate(i) for i in items], )) station_name = None if t.station_id: st = await db.get(StationMaster, t.station_id) if st: station_name = st.station_name return schemas.TemplateOut( template_id=t.template_id, template_code=t.template_code, template_name=t.template_name, station_id=t.station_id, station_name=station_name, format_no=t.format_no, revision=t.revision, revision_date=t.revision_date, description=t.description, is_active=t.is_active, sections=section_list, ) # ── Section CRUD ────────────────────────────────────────── async def add_section(db: AsyncSession, template_id: UUID, req: schemas.SectionCreate) -> schemas.SectionOut: t = await db.get(QaChecksheetTemplate, template_id) if not t: raise HTTPException(404, "Template not found") sec = QaChecksheetSection( section_id=uuid.uuid4(), template_id=template_id, section_no=req.section_no, section_name=req.section_name, description=req.description, sort_order=req.sort_order, ) db.add(sec) await db.commit() await db.refresh(sec) return schemas.SectionOut.model_validate(sec) async def update_section(db: AsyncSession, template_id: UUID, section_id: UUID, req: schemas.SectionUpdate) -> schemas.SectionOut: sec = await db.get(QaChecksheetSection, section_id) if not sec or sec.template_id != template_id: raise HTTPException(404, "Section not found") for field, val in req.model_dump(exclude_unset=True).items(): setattr(sec, field, val) await db.commit() await db.refresh(sec) return schemas.SectionOut.model_validate(sec) async def delete_section(db: AsyncSession, template_id: UUID, section_id: UUID) -> None: sec = await db.get(QaChecksheetSection, section_id) if not sec or sec.template_id != template_id: raise HTTPException(404, "Section not found") sec.is_active = False await db.commit() # ── Item CRUD ───────────────────────────────────────────── async def add_item(db: AsyncSession, template_id: UUID, section_id: UUID, req: schemas.ItemCreate) -> schemas.ItemOut: sec = await db.get(QaChecksheetSection, section_id) if not sec or sec.template_id != template_id: raise HTTPException(404, "Section not found") item = QaCheckpointItem( item_id=uuid.uuid4(), section_id=section_id, item_no=req.item_no, area_desc=req.area_desc, checkpoint_desc=req.checkpoint_desc, checkpoint_class=req.checkpoint_class, verification_method=req.verification_method, standard_specification=req.standard_specification, requires_measurement=req.requires_measurement, min_value=req.min_value, max_value=req.max_value, unit_of_measure=req.unit_of_measure, special_feature_flag=req.special_feature_flag, frequency=req.frequency, model_specs=req.model_specs or {}, sub_points=req.sub_points or [], reference_diagram_url=req.reference_diagram_url, sort_order=req.sort_order, ) db.add(item) await db.commit() await db.refresh(item) return schemas.ItemOut.model_validate(item) async def update_item(db: AsyncSession, template_id: UUID, section_id: UUID, item_id: UUID, req: schemas.ItemUpdate) -> schemas.ItemOut: item = await db.get(QaCheckpointItem, item_id) if not item or item.section_id != section_id: raise HTTPException(404, "Item not found") for field, val in req.model_dump(exclude_unset=True).items(): setattr(item, field, val) await db.commit() await db.refresh(item) return schemas.ItemOut.model_validate(item) async def delete_item(db: AsyncSession, template_id: UUID, section_id: UUID, item_id: UUID) -> None: item = await db.get(QaCheckpointItem, item_id) if not item or item.section_id != section_id: raise HTTPException(404, "Item not found") item.is_active = False await db.commit() # ═══════════════════════════════════════════════════════════ # STATION ↔ TEMPLATE MAPPING # ═══════════════════════════════════════════════════════════ async def _build_station_mapping_out(m: QaStationTemplateMap, st: StationMaster, t: QaChecksheetTemplate, c_count: int, model_map: dict, bt_map: dict) -> schemas.StationMappingOut: m_model_ids = [] if m.model_ids and isinstance(m.model_ids, list): for x in m.model_ids: try: m_model_ids.append(UUID(str(x))) except Exception: pass elif m.model_id: m_model_ids = [m.model_id] m_model_numbers = [model_map[str(uid)] for uid in m_model_ids if str(uid) in model_map] m_bt_ids = [] if m.body_type_ids and isinstance(m.body_type_ids, list): for x in m.body_type_ids: try: m_bt_ids.append(UUID(str(x))) except Exception: pass elif m.body_type_id: m_bt_ids = [m.body_type_id] m_bt_names = [bt_map[str(uid)] for uid in m_bt_ids if str(uid) in bt_map] return schemas.StationMappingOut( map_id=m.map_id, station_id=m.station_id, station_name=st.station_name if st else None, template_id=m.template_id, template_name=t.template_name if t else None, template_code=t.template_code if t else None, format_no=t.format_no if t else None, revision=t.revision if t else None, model_id=m_model_ids[0] if len(m_model_ids) == 1 else None, model_number=", ".join(m_model_numbers) if m_model_numbers else None, model_ids=m_model_ids, model_numbers=m_model_numbers, body_type_id=m_bt_ids[0] if len(m_bt_ids) == 1 else None, body_type_name=", ".join(m_bt_names) if m_bt_names else None, body_type_ids=m_bt_ids, body_type_names=m_bt_names, checkpoint_count=c_count, sort_order=m.sort_order, is_mandatory=m.is_mandatory, ) async def list_station_mappings(db: AsyncSession) -> List[schemas.StationMappingOut]: counts_result = await db.execute( select(QaChecksheetSection.template_id, func.count(QaCheckpointItem.item_id)) .join(QaCheckpointItem, QaCheckpointItem.section_id == QaChecksheetSection.section_id) .where(QaChecksheetSection.is_active == True, QaCheckpointItem.is_active == True) .group_by(QaChecksheetSection.template_id) ) counts_map = dict(counts_result.all()) models_res = await db.execute(select(VehicleModelMaster)) model_map = {str(vm.model_id): vm.model_number for vm in models_res.scalars().all()} bts_res = await db.execute(select(BodyTypeMaster)) bt_map = {str(bt.body_type_id): bt.name for bt in bts_res.scalars().all()} result = await db.execute( select(QaStationTemplateMap, StationMaster, QaChecksheetTemplate) .join(StationMaster, StationMaster.station_id == QaStationTemplateMap.station_id) .join(QaChecksheetTemplate, QaChecksheetTemplate.template_id == QaStationTemplateMap.template_id) .order_by(StationMaster.sequence_no, QaStationTemplateMap.sort_order) ) out = [] for m, st, t in result.all(): out.append(await _build_station_mapping_out(m, st, t, counts_map.get(t.template_id, 0), model_map, bt_map)) return out async def create_station_mapping(db: AsyncSession, req: schemas.StationMappingCreate) -> schemas.StationMappingOut: req_model_ids = [str(x) for x in (req.model_ids or [])] if not req_model_ids and req.model_id: req_model_ids = [str(req.model_id)] primary_model_id = UUID(req_model_ids[0]) if len(req_model_ids) == 1 else None req_bt_ids = [str(x) for x in (req.body_type_ids or [])] if not req_bt_ids and req.body_type_id: req_bt_ids = [str(req.body_type_id)] primary_bt_id = UUID(req_bt_ids[0]) if len(req_bt_ids) == 1 else None m = QaStationTemplateMap( map_id=uuid.uuid4(), station_id=req.station_id, template_id=req.template_id, model_id=primary_model_id, model_ids=req_model_ids, body_type_id=primary_bt_id, body_type_ids=req_bt_ids, sort_order=req.sort_order, is_mandatory=req.is_mandatory, ) db.add(m) await db.commit() st = await db.get(StationMaster, req.station_id) t = await db.get(QaChecksheetTemplate, req.template_id) count_result = await db.execute( select(func.count(QaCheckpointItem.item_id)) .join(QaChecksheetSection, QaChecksheetSection.section_id == QaCheckpointItem.section_id) .where(QaChecksheetSection.template_id == req.template_id, QaChecksheetSection.is_active == True, QaCheckpointItem.is_active == True) ) c_count = count_result.scalar() or 0 models_res = await db.execute(select(VehicleModelMaster)) model_map = {str(vm.model_id): vm.model_number for vm in models_res.scalars().all()} bts_res = await db.execute(select(BodyTypeMaster)) bt_map = {str(bt.body_type_id): bt.name for bt in bts_res.scalars().all()} return await _build_station_mapping_out(m, st, t, c_count, model_map, bt_map) async def update_station_mapping(db: AsyncSession, map_id: UUID, req: schemas.StationMappingUpdate) -> schemas.StationMappingOut: m = await db.get(QaStationTemplateMap, map_id) if not m: raise HTTPException(404, "Station mapping not found") if req.model_ids is not None: req_model_ids = [str(x) for x in req.model_ids] m.model_ids = req_model_ids m.model_id = UUID(req_model_ids[0]) if len(req_model_ids) == 1 else None elif req.model_id is not None: m.model_id = req.model_id m.model_ids = [str(req.model_id)] if req.body_type_ids is not None: req_bt_ids = [str(x) for x in req.body_type_ids] m.body_type_ids = req_bt_ids m.body_type_id = UUID(req_bt_ids[0]) if len(req_bt_ids) == 1 else None elif req.body_type_id is not None: m.body_type_id = req.body_type_id m.body_type_ids = [str(req.body_type_id)] if req.sort_order is not None: m.sort_order = req.sort_order if req.is_mandatory is not None: m.is_mandatory = req.is_mandatory await db.commit() await db.refresh(m) st = await db.get(StationMaster, m.station_id) t = await db.get(QaChecksheetTemplate, m.template_id) count_result = await db.execute( select(func.count(QaCheckpointItem.item_id)) .join(QaChecksheetSection, QaChecksheetSection.section_id == QaCheckpointItem.section_id) .where(QaChecksheetSection.template_id == m.template_id, QaChecksheetSection.is_active == True, QaCheckpointItem.is_active == True) ) c_count = count_result.scalar() or 0 models_res = await db.execute(select(VehicleModelMaster)) model_map = {str(vm.model_id): vm.model_number for vm in models_res.scalars().all()} bts_res = await db.execute(select(BodyTypeMaster)) bt_map = {str(bt.body_type_id): bt.name for bt in bts_res.scalars().all()} return await _build_station_mapping_out(m, st, t, c_count, model_map, bt_map) async def delete_station_mapping(db: AsyncSession, map_id: UUID) -> None: m = await db.get(QaStationTemplateMap, map_id) if not m: raise HTTPException(404, "Mapping not found") await db.delete(m) await db.commit() async def get_station_templates(db: AsyncSession, station_id: UUID) -> List[schemas.StationMappingOut]: counts_result = await db.execute( select(QaChecksheetSection.template_id, func.count(QaCheckpointItem.item_id)) .join(QaCheckpointItem, QaCheckpointItem.section_id == QaChecksheetSection.section_id) .where(QaChecksheetSection.is_active == True, QaCheckpointItem.is_active == True) .group_by(QaChecksheetSection.template_id) ) counts_map = dict(counts_result.all()) models_res = await db.execute(select(VehicleModelMaster)) model_map = {str(vm.model_id): vm.model_number for vm in models_res.scalars().all()} bts_res = await db.execute(select(BodyTypeMaster)) bt_map = {str(bt.body_type_id): bt.name for bt in bts_res.scalars().all()} result = await db.execute( select(QaStationTemplateMap, StationMaster, QaChecksheetTemplate) .join(StationMaster, StationMaster.station_id == QaStationTemplateMap.station_id) .join(QaChecksheetTemplate, QaChecksheetTemplate.template_id == QaStationTemplateMap.template_id) .where(QaStationTemplateMap.station_id == station_id) .order_by(QaStationTemplateMap.sort_order) ) out = [] for m, st, t in result.all(): out.append(await _build_station_mapping_out(m, st, t, counts_map.get(t.template_id, 0), model_map, bt_map)) return out # ═══════════════════════════════════════════════════════════ # INSPECTION FLOW # ═══════════════════════════════════════════════════════════ async def _resolve_chassis_specs( db: AsyncSession, chassis: Optional[ChassisMaster] ) -> tuple[Optional[UUID], Optional[str], Optional[UUID], Optional[str]]: """ Resolve (model_id, model_number, body_type_id, body_type_name) for a given chassis. 1. Direct model_id on chassis -> vehicle_model_master 2. dealer_order_vehicle / dealer_order_vehicle_chassis -> model_id & body_type_id """ if not chassis: return None, None, None, None model_id = chassis.model_id model_number = None dov = None if chassis.dealer_order_vehicle_id: dov = await db.get(DealerOrderVehicle, chassis.dealer_order_vehicle_id) if not dov: dov_chassis = (await db.execute( select(DealerOrderVehicleChassis) .where(DealerOrderVehicleChassis.chassis_no == chassis.chassis_no) )).scalars().first() if dov_chassis and dov_chassis.order_vehicle_id: dov = await db.get(DealerOrderVehicle, dov_chassis.order_vehicle_id) if not model_id and dov and dov.model_id: model_id = dov.model_id if model_id: vm = await db.get(VehicleModelMaster, model_id) if vm: model_number = vm.model_number body_type_id = None body_type_name = None if dov and dov.body_type_id: body_type_id = dov.body_type_id bt = await db.get(BodyTypeMaster, dov.body_type_id) if bt: body_type_name = bt.name return model_id, model_number, body_type_id, body_type_name async def _resolve_chassis_body_type( db: AsyncSession, chassis: Optional[ChassisMaster] ) -> tuple[Optional[UUID], Optional[str]]: _, _, body_type_id, body_type_name = await _resolve_chassis_specs(db, chassis) return body_type_id, body_type_name async def start_inspection( db: AsyncSession, req: schemas.InspectStartRequest, user_id: UUID ) -> schemas.InspectStartResponse: """Scan chassis at station → get/create instances for all mapped templates matching station, body type, and model.""" chassis = (await db.execute( select(ChassisMaster).where(ChassisMaster.chassis_no == req.chassis_no) )).scalars().first() if not chassis: raise HTTPException(404, "Chassis not found") station = await db.get(StationMaster, req.station_id) if not station: raise HTTPException(404, "Station not found") # ── VALIDATION 1: Inspector Station Authorization ── user = await db.get(LoginUsers, user_id) if user: if user.role_code == 'QA_INSPECTOR': assigned = await assigned_station_ids(db, user.user_id, 'QA') if req.station_id not in assigned: raise HTTPException(400, "Chassis has not arrived at your station.") elif user.role_code not in ('ADMIN', 'QA_SUPERVISOR'): raise HTTPException(400, "Chassis has not arrived at your station.") # ── VALIDATION 2: Station Arrival Gating ('Chassis In' by Production Supervisor) ── is_at_station = ( chassis.current_station_id == req.station_id and chassis.station_arrived_at is not None and chassis.station_completed_at is None ) if not is_at_station: active_log = (await db.execute( select(ChassisStationLog).where( ChassisStationLog.chassis_id == chassis.chassis_id, ChassisStationLog.station_id == req.station_id, ChassisStationLog.event_type == 'ARRIVAL', ChassisStationLog.status == 'AT_STATION', ) )).scalars().first() if not active_log: raise HTTPException(400, "Chassis has not arrived at your station.") # Two taps (or a retry on a slow network) must not create the same inspection twice: # serialise starts for this chassis + station until this transaction commits. await db.execute(text("SELECT pg_advisory_xact_lock(hashtext(:k))"), {"k": f"qa-start:{chassis.chassis_id}:{req.station_id}"}) model_id, model_number, body_type_id, body_type_name = await _resolve_chassis_specs(db, chassis) # Get templates mapped to this station (universal OR matching body type AND universal OR matching model) mapping_query = ( select(QaStationTemplateMap, QaChecksheetTemplate) .join(QaChecksheetTemplate, QaChecksheetTemplate.template_id == QaStationTemplateMap.template_id) .where(QaStationTemplateMap.station_id == req.station_id) ) if body_type_id: bt_str = str(body_type_id) mapping_query = mapping_query.where( or_( and_( func.coalesce(func.jsonb_array_length(QaStationTemplateMap.body_type_ids), 0) == 0, QaStationTemplateMap.body_type_id == None, ), QaStationTemplateMap.body_type_id == body_type_id, QaStationTemplateMap.body_type_ids.contains([bt_str]), ) ) else: mapping_query = mapping_query.where( and_( func.coalesce(func.jsonb_array_length(QaStationTemplateMap.body_type_ids), 0) == 0, QaStationTemplateMap.body_type_id == None, ) ) if model_id: model_str = str(model_id) mapping_query = mapping_query.where( or_( and_( func.coalesce(func.jsonb_array_length(QaStationTemplateMap.model_ids), 0) == 0, QaStationTemplateMap.model_id == None, ), QaStationTemplateMap.model_id == model_id, QaStationTemplateMap.model_ids.contains([model_str]), ) ) else: mapping_query = mapping_query.where( and_( func.coalesce(func.jsonb_array_length(QaStationTemplateMap.model_ids), 0) == 0, QaStationTemplateMap.model_id == None, ) ) mapping_query = mapping_query.order_by(QaStationTemplateMap.sort_order) mappings = (await db.execute(mapping_query)).all() if not mappings: extra_parts = [] if model_number: extra_parts.append(f"Model: {model_number}") if body_type_name: extra_parts.append(f"Body Type: {body_type_name}") extra_info = f" ({', '.join(extra_parts)})" if extra_parts else "" raise HTTPException(400, f"No QA checksheets mapped to station '{station.station_name}'{extra_info}") instances = [] seen_templates = set() for mapping, template in mappings: if template.template_id in seen_templates: continue # same checksheet mapped more than once (e.g. per body type) seen_templates.add(template.template_id) # Find or create instance (if duplicates exist from before, use the signed-off one first) existing = (await db.execute( select(QaChecksheetInstance).where( QaChecksheetInstance.template_id == template.template_id, QaChecksheetInstance.chassis_id == chassis.chassis_id, QaChecksheetInstance.station_id == req.station_id, ).order_by( case((QaChecksheetInstance.overall_result.in_(("PASSED", "FAILED")), 0), else_=1), QaChecksheetInstance.created_at, ) )).scalars().first() if existing: inst = existing else: inst = QaChecksheetInstance( instance_id=uuid.uuid4(), template_id=template.template_id, chassis_id=chassis.chassis_id, chassis_no=chassis.chassis_no, station_id=req.station_id, inspector_id=user_id, inspection_date=_now(), status="PENDING", overall_result="PENDING", station_qa_status="IN_PROGRESS", ) db.add(inst) # Count items and responses total_items = (await db.execute( select(func.count()).select_from(QaCheckpointItem) .join(QaChecksheetSection, QaChecksheetSection.section_id == QaCheckpointItem.section_id) .where( QaChecksheetSection.template_id == template.template_id, QaCheckpointItem.is_active == True, ) )).scalar() or 0 responded_items = 0 if existing: responded_items = (await db.execute( select(func.count()).select_from(QaCheckpointResponse) .where(QaCheckpointResponse.instance_id == inst.instance_id) )).scalar() or 0 instances.append(schemas.InstanceSummary( instance_id=inst.instance_id, template_id=template.template_id, template_name=template.template_name, template_code=template.template_code, station_qa_status=inst.station_qa_status or "PENDING", total_items=total_items, responded_items=responded_items, )) await db.commit() order_spec_data = None try: from app.modules.chassis_tracker.order_spec_service import resolve_chassis_order_spec order_spec_data = (await resolve_chassis_order_spec(db, chassis)).model_dump() except Exception as e: logger.warning(f"Failed to resolve order spec in start_inspection: {e}") return schemas.InspectStartResponse( chassis_id=chassis.chassis_id, chassis_no=chassis.chassis_no, station_name=station.station_name, model_id=model_id, model_number=model_number, body_type_id=body_type_id, body_type_name=body_type_name, instances=instances, order_spec=order_spec_data, ) async def get_instance_detail(db: AsyncSession, instance_id: UUID) -> schemas.InstanceDetailOut: inst = await db.get(QaChecksheetInstance, instance_id) if not inst: raise HTTPException(404, f"QA checksheet instance '{instance_id}' not found.") template = await db.get(QaChecksheetTemplate, inst.template_id) if inst.template_id else None chassis = await db.get(ChassisMaster, inst.chassis_id) if inst.chassis_id else None station = await db.get(StationMaster, inst.station_id) if inst.station_id else None model_id, model_number, body_type_id, body_type_name = await _resolve_chassis_specs(db, chassis) sections = [] if inst.template_id: sections_result = await db.execute( select(QaChecksheetSection).where( QaChecksheetSection.template_id == inst.template_id, QaChecksheetSection.is_active == True, ).order_by(QaChecksheetSection.sort_order) ) sections = sections_result.scalars().all() photo_rows = [] try: photo_rows = (await db.execute( select(QaResponsePhoto).where(QaResponsePhoto.instance_id == instance_id).order_by(QaResponsePhoto.uploaded_at) )).scalars().all() except Exception as e: logger.warning(f"Failed to fetch photo rows for instance {instance_id}: {e}") photos_by_item = {} for ph in photo_rows: photos_by_item.setdefault(ph.item_id, []).append( schemas.ResponsePhotoOut(photo_id=ph.photo_id, photo_url=ph.photo_url, uploaded_at=ph.uploaded_at) ) section_remarks = {} try: section_remarks = { r.section_id: r.remark for r in (await db.execute( select(QaSectionRemark).where(QaSectionRemark.instance_id == instance_id) )).scalars().all() } except Exception as e: logger.warning(f"Failed to fetch section remarks for instance {instance_id}: {e}") section_list = [] for sec in sections: items_result = await db.execute( select(QaCheckpointItem).where( QaCheckpointItem.section_id == sec.section_id, QaCheckpointItem.is_active == True, ).order_by(QaCheckpointItem.sort_order) ) items = items_result.scalars().all() items_with_response = [] for item in items: resp = (await db.execute( select(QaCheckpointResponse).where( QaCheckpointResponse.instance_id == instance_id, QaCheckpointResponse.item_id == item.item_id, ) )).scalars().first() items_with_response.append(schemas.ItemWithResponse( item_id=item.item_id, item_no=item.item_no if item.item_no is not None else 1, area_desc=item.area_desc, checkpoint_desc=item.checkpoint_desc or "", checkpoint_class=item.checkpoint_class or "MAJOR", verification_method=item.verification_method, standard_specification=item.standard_specification, requires_measurement=bool(item.requires_measurement), min_value=item.min_value, max_value=item.max_value, unit_of_measure=item.unit_of_measure, special_feature_flag=item.special_feature_flag, frequency=item.frequency, model_specs=item.model_specs or {}, sub_points=item.sub_points or [], reference_diagram_url=item.reference_diagram_url, response_id=resp.response_id if resp else None, status=resp.status if resp else None, measured_value=resp.measured_value if resp else None, sub_point_values=resp.sub_point_values if (resp and isinstance(resp.sub_point_values, dict)) else {}, observation=resp.observation if resp else None, rework_status=resp.rework_status if resp else None, after_rework_observation=resp.after_rework_observation if resp else None, photos=photos_by_item.get(item.item_id, []), )) section_list.append(schemas.SectionWithResponses( section_id=sec.section_id, section_name=sec.section_name or "", section_no=sec.section_no if sec.section_no is not None else 0, remark=section_remarks.get(sec.section_id), items=items_with_response, )) # Carried-forward defects from upstream stations (fault-tolerant) carried_forward_items = [] if station and station.sequence_no is not None and inst.chassis_id: try: upstream_stmt = ( select(QaCheckpointResponse, QaCheckpointItem, StationMaster.station_name) .join(QaCheckpointItem, QaCheckpointItem.item_id == QaCheckpointResponse.item_id) .join(QaChecksheetInstance, QaChecksheetInstance.instance_id == QaCheckpointResponse.instance_id) .join(StationMaster, StationMaster.station_id == QaChecksheetInstance.station_id) .where( QaChecksheetInstance.chassis_id == inst.chassis_id, StationMaster.sequence_no < station.sequence_no, QaCheckpointResponse.status.in_(('NOT_OK', 'FAIL')), or_( QaCheckpointResponse.rework_status == None, QaCheckpointResponse.rework_status != 'DONE' ) ) .order_by(StationMaster.sequence_no, QaCheckpointItem.sort_order) ) upstream_failures = (await db.execute(upstream_stmt)).all() for u_resp, u_item, u_st_name in upstream_failures: u_photos = [] try: u_photo_rows = (await db.execute( select(QaResponsePhoto).where( QaResponsePhoto.instance_id == u_resp.instance_id, QaResponsePhoto.item_id == u_item.item_id, ).order_by(QaResponsePhoto.uploaded_at) )).scalars().all() u_photos = [ schemas.ResponsePhotoOut(photo_id=ph.photo_id, photo_url=ph.photo_url, uploaded_at=ph.uploaded_at) for ph in u_photo_rows ] except Exception as ph_err: logger.warning(f"Failed to fetch carried-forward photos: {ph_err}") carried_forward_items.append(schemas.ItemWithResponse( item_id=u_item.item_id, item_no=f"CF-{u_item.item_no or 1}", area_desc=f"[Carried from {u_st_name}] {u_item.area_desc or ''}".strip(), checkpoint_desc=u_item.checkpoint_desc or "", checkpoint_class=u_item.checkpoint_class or "MAJOR", verification_method=u_item.verification_method, standard_specification=u_item.standard_specification, requires_measurement=bool(u_item.requires_measurement), min_value=u_item.min_value, max_value=u_item.max_value, unit_of_measure=u_item.unit_of_measure, special_feature_flag=u_item.special_feature_flag, frequency=u_item.frequency, model_specs=u_item.model_specs or {}, sub_points=u_item.sub_points or [], reference_diagram_url=u_item.reference_diagram_url, response_id=u_resp.response_id, status=u_resp.status, measured_value=u_resp.measured_value, sub_point_values=u_resp.sub_point_values if isinstance(u_resp.sub_point_values, dict) else {}, observation=u_resp.observation, rework_status=u_resp.rework_status or "PENDING", after_rework_observation=u_resp.after_rework_observation, is_carried_forward=True, carried_from_station=u_st_name, original_response_id=u_resp.response_id, photos=u_photos, )) if carried_forward_items: cf_sec_id = uuid.uuid5(uuid.NAMESPACE_DNS, f"cf-sec:{inst.instance_id}") section_list.insert(0, schemas.SectionWithResponses( section_id=cf_sec_id, section_name="⚠️ Carried-Forward Defects (Rework from Upstream Stations)", section_no="CF", description="Checkpoints that failed at upstream stations and require rework and verification.", is_carried_forward=True, items=carried_forward_items, )) except Exception as cf_err: logger.warning(f"Failed to resolve upstream carried-forward defects: {cf_err}") # Check QA clearance for Chassis Out status is_cleared = False cleared_at = None if inst.station_id and inst.chassis_id: try: cleared_flag, _ = await flow.is_qa_cleared_at_station(db, inst.chassis_id, inst.station_id) is_cleared = cleared_flag h = await flow.open_handoff_from(db, inst.chassis_id, inst.station_id) if h and h.status in ('QA_PASSED', 'RELEASED'): cleared_at = h.released_at or h.completed_at elif getattr(inst, 'completed_at', None): cleared_at = inst.completed_at except Exception as cl_err: logger.warning(f"Failed to check QA clearance status: {cl_err}") can_clear = False if not is_cleared and inst.station_id: native_items = [it for sec in section_list if not getattr(sec, 'is_carried_forward', False) for it in (sec.items or [])] if native_items and all(it.status is not None for it in native_items): can_clear = True order_spec_data = None if chassis: try: from app.modules.chassis_tracker.order_spec_service import resolve_chassis_order_spec order_spec_data = (await resolve_chassis_order_spec(db, chassis)).model_dump() except Exception as e: logger.warning(f"Failed to resolve order spec in get_instance_detail: {e}") return schemas.InstanceDetailOut( instance_id=inst.instance_id, template_id=inst.template_id, template_name=template.template_name if (template and template.template_name) else "", chassis_no=chassis.chassis_no if chassis else (inst.chassis_no or None), station_name=station.station_name if station else None, model_number=model_number, body_type_name=body_type_name, format_no=template.format_no if template else None, revision=template.revision if template else None, overall_result=inst.overall_result or "PENDING", station_qa_status=inst.station_qa_status or "PENDING", remarks=inst.remarks, can_clear_chassis_out=can_clear, is_chassis_out_cleared=is_cleared, chassis_out_cleared_at=cleared_at, sections=section_list, order_spec=order_spec_data, ) async def submit_responses_bulk( db: AsyncSession, instance_id: UUID, req: schemas.ResponseBulkRequest, user_id: UUID ) -> dict: inst = await db.get(QaChecksheetInstance, instance_id) if not inst: raise HTTPException(404, "Instance not found") for item_resp in req.responses: if item_resp.original_response_id: orig = await db.get(QaCheckpointResponse, item_resp.original_response_id) if orig: if item_resp.status in ('OK', 'PASS'): orig.status = 'OK' orig.rework_status = 'DONE' elif item_resp.rework_status: orig.rework_status = item_resp.rework_status if item_resp.after_rework_observation: orig.after_rework_observation = item_resp.after_rework_observation elif item_resp.observation and not orig.after_rework_observation: orig.after_rework_observation = item_resp.observation continue # Mandatory validation: If checkpoint requires numerical measurement and value is outside tolerance, flag as NOT_OK item_obj = await db.get(QaCheckpointItem, item_resp.item_id) if item_obj and item_obj.requires_measurement and item_resp.measured_value is not None: try: num_v = float(item_resp.measured_value) is_outside = False if item_obj.min_value is not None and num_v < float(item_obj.min_value): is_outside = True if item_obj.max_value is not None and num_v > float(item_obj.max_value): is_outside = True if is_outside and item_resp.status in ('OK', 'PASS'): item_resp.status = 'NOT_OK' if not item_resp.observation: item_resp.observation = f"Out of tolerance: Measured value ({num_v}) is outside allowable limits ({item_obj.min_value} to {item_obj.max_value})." except (ValueError, TypeError): pass existing = (await db.execute( select(QaCheckpointResponse).where( QaCheckpointResponse.instance_id == instance_id, QaCheckpointResponse.item_id == item_resp.item_id, ) )).scalars().first() if existing: existing.status = item_resp.status existing.measured_value = item_resp.measured_value if item_resp.sub_point_values is not None: existing.sub_point_values = item_resp.sub_point_values existing.observation = item_resp.observation existing.na_reason = item_resp.na_reason if item_resp.rework_status: existing.rework_status = item_resp.rework_status if item_resp.after_rework_observation: existing.after_rework_observation = item_resp.after_rework_observation else: resp = QaCheckpointResponse( response_id=uuid.uuid4(), instance_id=instance_id, item_id=item_resp.item_id, status=item_resp.status, measured_value=item_resp.measured_value, sub_point_values=item_resp.sub_point_values or {}, observation=item_resp.observation, na_reason=item_resp.na_reason, rework_status=item_resp.rework_status, after_rework_observation=item_resp.after_rework_observation, ) db.add(resp) inst.station_qa_status = "IN_PROGRESS" await db.commit() return {"message": f"Saved {len(req.responses)} responses", "instance_id": str(instance_id)} async def signoff_instance( db: AsyncSession, instance_id: UUID, req: schemas.SignoffRequest, user_id: UUID ) -> schemas.SignoffResponse: inst = await db.get(QaChecksheetInstance, instance_id) if not inst: raise HTTPException(404, "Instance not found") inst.overall_result = req.result inst.station_qa_status = req.result inst.completed_at = _now() inst.passed_by = user_id if req.rework_notes: inst.rework_notes = req.rework_notes if req.remarks: inst.remarks = req.remarks # Add signoff record user = await db.get(LoginUsers, user_id) signer_name = user.name if user and user.name else "QA Inspector" signoff = QaChecksheetSignoff( signoff_id=uuid.uuid4(), instance_id=instance_id, signoff_type="INSPECTOR", signed_by_user_id=user_id, signed_by_name=signer_name, signed_by=user_id, role="INSPECTOR", remarks=req.remarks, signed_at=_now(), ) db.add(signoff) inventory_deducted = False # Check if all mandatory templates at this station are now PASSED if req.result == "PASSED" and inst.station_id: chassis = await db.get(ChassisMaster, inst.chassis_id) model_id, _, chassis_body_type_id, _ = await _resolve_chassis_specs(db, chassis) mandatory_query = select(QaStationTemplateMap).where( QaStationTemplateMap.station_id == inst.station_id, QaStationTemplateMap.is_mandatory == True, ) if chassis_body_type_id: bt_str = str(chassis_body_type_id) mandatory_query = mandatory_query.where( or_( and_( func.coalesce(func.jsonb_array_length(QaStationTemplateMap.body_type_ids), 0) == 0, QaStationTemplateMap.body_type_id == None, ), QaStationTemplateMap.body_type_id == chassis_body_type_id, QaStationTemplateMap.body_type_ids.contains([bt_str]), ) ) else: mandatory_query = mandatory_query.where( and_( func.coalesce(func.jsonb_array_length(QaStationTemplateMap.body_type_ids), 0) == 0, QaStationTemplateMap.body_type_id == None, ) ) if model_id: model_str = str(model_id) mandatory_query = mandatory_query.where( or_( and_( func.coalesce(func.jsonb_array_length(QaStationTemplateMap.model_ids), 0) == 0, QaStationTemplateMap.model_id == None, ), QaStationTemplateMap.model_id == model_id, QaStationTemplateMap.model_ids.contains([model_str]), ) ) else: mandatory_query = mandatory_query.where( and_( func.coalesce(func.jsonb_array_length(QaStationTemplateMap.model_ids), 0) == 0, QaStationTemplateMap.model_id == None, ) ) mandatory_mappings = (await db.execute(mandatory_query)).scalars().all() all_passed = True for m in mandatory_mappings: m_inst = (await db.execute( select(QaChecksheetInstance).where( QaChecksheetInstance.template_id == m.template_id, QaChecksheetInstance.chassis_id == inst.chassis_id, QaChecksheetInstance.station_id == inst.station_id, QaChecksheetInstance.overall_result == "PASSED", ) )).scalars().first() if not m_inst: all_passed = False break if all_passed: # Trigger inventory deduction inventory_deducted = await _deduct_inventory_on_qa_pass( db, inst.chassis_id, inst.station_id, instance_id ) else: all_passed = False # Station hand-over: a pass of everything mandatory lets QA release the chassis. handoff = await flow.set_qa_outcome(db, inst.chassis_id, inst.station_id, all_passed) if inst.station_id else None next_name = await flow.station_name(db, handoff.to_station_id) if handoff else None await db.commit() return schemas.SignoffResponse( instance_id=inst.instance_id, overall_result=inst.overall_result, station_qa_status=inst.station_qa_status, inventory_deducted=inventory_deducted, all_mandatory_passed=all_passed, can_release=bool(handoff and all_passed), next_station_name=next_name, message=f"Checksheet signed off as {req.result}" + ( ". Inventory deducted for station BOM parts." if inventory_deducted else "" ), ) async def grant_qa_clearance_for_chassis_out( db: AsyncSession, station_id: UUID, chassis_id: UUID, user_id: UUID, remarks: Optional[str] = None ) -> schemas.ClearanceForChassisOutResponse: station = await db.get(StationMaster, station_id) if not station: raise HTTPException(404, "Station not found") chassis = await db.get(ChassisMaster, chassis_id) if not chassis: raise HTTPException(404, "Chassis not found") user = await db.get(LoginUsers, user_id) if user: if user.role_code == 'QA_INSPECTOR': assigned = await assigned_station_ids(db, user.user_id, 'QA') if station_id not in assigned: raise HTTPException(403, "You are not authorized to grant clearance for this station.") elif user.role_code not in ('ADMIN', 'QA_SUPERVISOR'): raise HTTPException(403, "Not authorized to grant QA clearance.") instances = (await db.execute( select(QaChecksheetInstance).where( QaChecksheetInstance.chassis_id == chassis_id, QaChecksheetInstance.station_id == station_id, ) )).scalars().all() if not instances: raise HTTPException(400, "No QA inspection records found for this chassis at this station.") for inst in instances: total_items = (await db.execute( select(func.count()).select_from(QaCheckpointItem) .join(QaChecksheetSection, QaChecksheetSection.section_id == QaCheckpointItem.section_id) .where( QaChecksheetSection.template_id == inst.template_id, QaCheckpointItem.is_active == True, ) )).scalar() or 0 responded_items = (await db.execute( select(func.count()).select_from(QaCheckpointResponse) .where( QaCheckpointResponse.instance_id == inst.instance_id, QaCheckpointResponse.status.isnot(None), ) )).scalar() or 0 if responded_items < total_items: raise HTTPException( 400, f"Cannot grant clearance: Only {responded_items}/{total_items} checklist items completed for checksheet." ) now = datetime.now(timezone.utc) for inst in instances: inst.station_qa_status = 'PASSED' if not inst.overall_result or inst.overall_result == 'PENDING': inst.overall_result = 'PASSED' inst.completed_at = now inst.passed_by = user_id handoff, next_st_name = await flow.release( db, chassis_id, station_id, user_id, remarks=remarks or "QA Clearance granted for Chassis Out" ) await db.commit() user_name = (user.name if user and user.name else (user.username if user else "QA Inspector")) return schemas.ClearanceForChassisOutResponse( status="CLEARED", message=f"QA clearance granted for Chassis Out at {station.station_name}.", station_name=station.station_name, cleared_at=now, cleared_by=user_name, ) async def _deduct_inventory_on_qa_pass( db: AsyncSession, chassis_id: UUID, station_id: UUID, instance_id: UUID ) -> bool: """Deduct inventory for BOM parts at this station when all QA passes.""" bom_rows = (await db.execute( select(ChassisBomSnapshot).where( ChassisBomSnapshot.chassis_id == chassis_id, ChassisBomSnapshot.station_id == station_id, ChassisBomSnapshot.consumed_qty < ChassisBomSnapshot.quantity, ) )).scalars().all() if not bom_rows: return False deducted = False for snap in bom_rows: deduct_qty = snap.quantity - snap.consumed_qty if deduct_qty <= 0: continue # Update inventory inv = (await db.execute( select(RmInventory).where(RmInventory.rm_id == snap.rm_id) )).scalars().first() if inv: inv.current_qty = max(Decimal("0"), inv.current_qty - deduct_qty) # Log the transaction log = RmInventoryLog( log_id=uuid.uuid4(), rm_id=snap.rm_id, transaction_type="CONSUMPTION", quantity=deduct_qty, reference_type="QA_STATION_PASS", reference_id=instance_id, balance_after=inv.current_qty, created_at=_now(), ) db.add(log) # Mark BOM snapshot as consumed snap.consumed_qty = snap.quantity deducted = True return deducted # ═══════════════════════════════════════════════════════════ # SHORTAGE TRACKING # ═══════════════════════════════════════════════════════════ async def get_shortages( db: AsyncSession, chassis_no: str, station_id: UUID ) -> List[schemas.ShortageOut]: chassis = (await db.execute( select(ChassisMaster).where(ChassisMaster.chassis_no == chassis_no) )).scalars().first() if not chassis: raise HTTPException(404, "Chassis not found") result = await db.execute( select(QaShortageLog, RmMaster) .join(RmMaster, RmMaster.rm_id == QaShortageLog.rm_id) .where( QaShortageLog.chassis_id == chassis.chassis_id, QaShortageLog.station_id == station_id, ) .order_by(QaShortageLog.noted_at.desc()) ) return [ schemas.ShortageOut( shortage_id=s.shortage_id, chassis_id=s.chassis_id, station_id=s.station_id, rm_id=s.rm_id, rm_name=rm.name, rm_code=rm.part_no, required_qty=s.required_qty, fitted_qty=s.fitted_qty, short_qty=s.required_qty - s.fitted_qty, status=s.status, remarks=s.remarks, noted_at=s.noted_at, resolved_at=s.resolved_at, ) for s, rm in result.all() ] async def create_shortage( db: AsyncSession, req: schemas.ShortageCreate, user_id: UUID ) -> schemas.ShortageOut: chassis = (await db.execute( select(ChassisMaster).where(ChassisMaster.chassis_no == req.chassis_no) )).scalars().first() if not chassis: raise HTTPException(404, "Chassis not found") rm = await db.get(RmMaster, req.rm_id) if not rm: raise HTTPException(404, "RM not found") s = QaShortageLog( shortage_id=uuid.uuid4(), chassis_id=chassis.chassis_id, station_id=req.station_id, rm_id=req.rm_id, required_qty=req.required_qty, fitted_qty=req.fitted_qty, noted_by=user_id, noted_at=_now(), remarks=req.remarks, ) db.add(s) await db.commit() await db.refresh(s) return schemas.ShortageOut( shortage_id=s.shortage_id, chassis_id=s.chassis_id, station_id=s.station_id, rm_id=s.rm_id, rm_name=rm.name, rm_code=rm.part_no, required_qty=s.required_qty, fitted_qty=s.fitted_qty, short_qty=s.required_qty - s.fitted_qty, status=s.status, remarks=s.remarks, noted_at=s.noted_at, ) async def update_shortage( db: AsyncSession, shortage_id: UUID, req: schemas.ShortageUpdate ) -> schemas.ShortageOut: s = await db.get(QaShortageLog, shortage_id) if not s: raise HTTPException(404, "Shortage not found") if req.fitted_qty is not None: s.fitted_qty = req.fitted_qty if req.remarks is not None: s.remarks = req.remarks await db.commit() await db.refresh(s) rm = await db.get(RmMaster, s.rm_id) return schemas.ShortageOut( shortage_id=s.shortage_id, chassis_id=s.chassis_id, station_id=s.station_id, rm_id=s.rm_id, rm_name=rm.name if rm else None, rm_code=rm.part_no if rm else None, required_qty=s.required_qty, fitted_qty=s.fitted_qty, short_qty=s.required_qty - s.fitted_qty, status=s.status, remarks=s.remarks, noted_at=s.noted_at, resolved_at=s.resolved_at, ) async def resolve_shortage( db: AsyncSession, shortage_id: UUID, user_id: UUID ) -> schemas.ShortageOut: s = await db.get(QaShortageLog, shortage_id) if not s: raise HTTPException(404, "Shortage not found") s.status = "RESOLVED" s.resolved_by = user_id s.resolved_at = _now() s.fitted_qty = s.required_qty await db.commit() await db.refresh(s) rm = await db.get(RmMaster, s.rm_id) return schemas.ShortageOut( shortage_id=s.shortage_id, chassis_id=s.chassis_id, station_id=s.station_id, rm_id=s.rm_id, rm_name=rm.name if rm else None, rm_code=rm.part_no if rm else None, required_qty=s.required_qty, fitted_qty=s.fitted_qty, short_qty=Decimal("0"), status=s.status, remarks=s.remarks, noted_at=s.noted_at, resolved_at=s.resolved_at, ) # ═══════════════════════════════════════════════════════════ # QA DASHBOARD # ═══════════════════════════════════════════════════════════ async def get_qa_dashboard(db: AsyncSession) -> schemas.QADashboardResponse: stations = (await db.execute( select(StationMaster).where(StationMaster.is_active == True, StationMaster.requires_qa == True) .order_by(StationMaster.sequence_no) )).scalars().all() station_summaries = [] total_pending = total_passed = total_failed = 0 for st in stations: pending = (await db.execute( select(func.count()).select_from(QaChecksheetInstance) .where(QaChecksheetInstance.station_id == st.station_id, QaChecksheetInstance.station_qa_status == "PENDING") )).scalar() or 0 in_progress = (await db.execute( select(func.count()).select_from(QaChecksheetInstance) .where(QaChecksheetInstance.station_id == st.station_id, QaChecksheetInstance.station_qa_status == "IN_PROGRESS") )).scalar() or 0 passed = (await db.execute( select(func.count()).select_from(QaChecksheetInstance) .where(QaChecksheetInstance.station_id == st.station_id, QaChecksheetInstance.station_qa_status == "PASSED") )).scalar() or 0 failed = (await db.execute( select(func.count()).select_from(QaChecksheetInstance) .where(QaChecksheetInstance.station_id == st.station_id, QaChecksheetInstance.station_qa_status == "FAILED") )).scalar() or 0 total_pending += pending + in_progress total_passed += passed total_failed += failed station_summaries.append(schemas.QAStationSummary( station_id=st.station_id, station_name=st.station_name, pending=pending, in_progress=in_progress, passed=passed, failed=failed, )) return schemas.QADashboardResponse( total_pending=total_pending, total_passed=total_passed, total_failed=total_failed, stations=station_summaries, ) # ═══════════════════════════════════════════════════════════ # CHASSIS QA HISTORY # ═══════════════════════════════════════════════════════════ async def get_chassis_qa_history(db: AsyncSession, chassis_no: str) -> List[schemas.ChassisQAHistoryItem]: chassis = (await db.execute( select(ChassisMaster).where(ChassisMaster.chassis_no == chassis_no) )).scalars().first() if not chassis: raise HTTPException(404, f"Chassis '{chassis_no}' not found") instances = (await db.execute( select(QaChecksheetInstance) .where(QaChecksheetInstance.chassis_id == chassis.chassis_id) .order_by(QaChecksheetInstance.created_at.desc()) )).scalars().all() out = [] for inst in instances: tpl = await db.get(QaChecksheetTemplate, inst.template_id) st = await db.get(StationMaster, inst.station_id) responses = (await db.execute( select(QaCheckpointResponse).where(QaCheckpointResponse.instance_id == inst.instance_id) )).scalars().all() ok_count = sum(1 for r in responses if r.status == "OK") nok_count = sum(1 for r in responses if r.status == "NOT_OK") na_count = sum(1 for r in responses if r.status == "NA") out.append(schemas.ChassisQAHistoryItem( instance_id=inst.instance_id, template_id=inst.template_id, template_name=tpl.template_name if tpl else "Unknown", template_code=tpl.template_code if tpl else "", station_id=inst.station_id, station_name=st.station_name if st else "Unknown", inspection_date=inst.inspection_date, overall_result=inst.overall_result, station_qa_status=inst.station_qa_status, total_items=len(responses), ok_items=ok_count, not_ok_items=nok_count, na_items=na_count, completed_at=inst.completed_at, )) return out async def save_instance_remarks( db: AsyncSession, instance_id: UUID, req: schemas.InstanceRemarksIn, user_id: UUID ) -> dict: """Save the checksheet remark and per-section remarks (empty text removes a section remark).""" inst = await db.get(QaChecksheetInstance, instance_id) if not inst: raise HTTPException(404, "Instance not found") inst.remarks = (req.remarks or '').strip() or None for sr in req.sections: text = (sr.remark or '').strip() row = (await db.execute(select(QaSectionRemark).where( QaSectionRemark.instance_id == instance_id, QaSectionRemark.section_id == sr.section_id, ))).scalars().first() if text and row: row.remark, row.updated_by = text, user_id elif text: db.add(QaSectionRemark(instance_id=instance_id, section_id=sr.section_id, remark=text, updated_by=user_id)) elif row: await db.delete(row) await db.commit() return {"saved": True} # ═══════════════════════════════════════════════════════════ # COMPREHENSIVE QA SUPERVISOR DASHBOARD # ═══════════════════════════════════════════════════════════ async def get_supervisor_dashboard(db: AsyncSession) -> schemas.SupervisorDashboardResponse: # 1. Fetch active QA stations stations = (await db.execute( select(StationMaster) .where(StationMaster.is_active == True, StationMaster.requires_qa == True) .order_by(StationMaster.sequence_no) )).scalars().all() # 2. Build station allocations and inspector assignments station_allocations = [] monitored_stations = len(stations) active_inspectors_set = set() for st in stations: # Assigned inspectors for this station assigned_inspectors_rows = (await db.execute( select(LoginUsers) .join(StationUserAssignment, StationUserAssignment.user_id == LoginUsers.user_id) .where( StationUserAssignment.station_id == st.station_id, StationUserAssignment.role == 'QA', StationUserAssignment.is_active == True, ) )).scalars().all() assigned_list = [] for u in assigned_inspectors_rows: active_inspectors_set.add(u.user_id) assigned_list.append(schemas.AssignedInspectorBrief( user_id=u.user_id, name=u.name, employee_code=u.employee_code, email=u.email, )) # Station metrics passed_cnt = (await db.execute( select(func.count()).select_from(QaChecksheetInstance) .where(QaChecksheetInstance.station_id == st.station_id, QaChecksheetInstance.station_qa_status == "PASSED") )).scalar() or 0 failed_cnt = (await db.execute( select(func.count()).select_from(QaChecksheetInstance) .where(QaChecksheetInstance.station_id == st.station_id, QaChecksheetInstance.station_qa_status == "FAILED") )).scalar() or 0 pending_cnt = (await db.execute( select(func.count()).select_from(QaChecksheetInstance) .where(QaChecksheetInstance.station_id == st.station_id, QaChecksheetInstance.station_qa_status.in_(["PENDING", "IN_PROGRESS"])) )).scalar() or 0 # Active chassis list at this station active_chassis_nos = (await db.execute( select(ChassisMaster.chassis_no) .where(ChassisMaster.current_station_id == st.station_id) .limit(10) )).scalars().all() station_allocations.append(schemas.StationAllocationItem( station_id=st.station_id, station_name=st.station_name, station_code=st.station_code, sequence_no=st.sequence_no, requires_qa=st.requires_qa, assigned_inspectors=assigned_list, active_chassis_count=len(active_chassis_nos), active_chassis_list=list(active_chassis_nos), passed_count=passed_cnt, failed_count=failed_cnt, pending_count=pending_cnt, )) # 3. Real-time Chassis QA Statuses recent_instances = (await db.execute( select(QaChecksheetInstance, ChassisMaster, StationMaster, LoginUsers) .join(ChassisMaster, ChassisMaster.chassis_id == QaChecksheetInstance.chassis_id) .join(StationMaster, StationMaster.station_id == QaChecksheetInstance.station_id) .outerjoin(LoginUsers, LoginUsers.user_id == QaChecksheetInstance.inspector_id) .order_by(QaChecksheetInstance.updated_at.desc()) .limit(100) )).all() chassis_statuses = [] seen_chassis_stations = set() total_active_chassis_set = set() total_passed = 0 total_failed = 0 total_pending = 0 cleared_for_chassis_out = 0 for inst, ch, st, u in recent_instances: total_active_chassis_set.add(ch.chassis_id) if inst.station_qa_status == 'PASSED': total_passed += 1 elif inst.station_qa_status == 'FAILED': total_failed += 1 else: total_pending += 1 # Avoid duplicate cards for same chassis+station in live view key = (ch.chassis_id, st.station_id) if key in seen_chassis_stations: continue seen_chassis_stations.add(key) # Clearance check cleared_flag = False try: cleared_flag, _ = await flow.is_qa_cleared_at_station(db, ch.chassis_id, st.station_id) except Exception: cleared_flag = (inst.station_qa_status == 'PASSED') if cleared_flag: cleared_for_chassis_out += 1 # Checkpoints counts total_items = (await db.execute( select(func.count()).select_from(QaCheckpointItem) .join(QaChecksheetSection, QaChecksheetSection.section_id == QaCheckpointItem.section_id) .where(QaChecksheetSection.template_id == inst.template_id, QaCheckpointItem.is_active == True) )).scalar() or 0 responses_cnt = (await db.execute( select(func.count()).select_from(QaCheckpointResponse) .where(QaCheckpointResponse.instance_id == inst.instance_id) )).scalar() or 0 defects_cnt = (await db.execute( select(func.count()).select_from(QaCheckpointResponse) .where(QaCheckpointResponse.instance_id == inst.instance_id, QaCheckpointResponse.status == 'NOT_OK') )).scalar() or 0 # Model name & Body type model_name = None if ch.model_id: mdl = await db.get(VehicleModelMaster, ch.model_id) if mdl: model_name = mdl.model_number # Station & Current Bay resolution station_name_map = {s.station_id: s.station_name for s in stations} is_curr = (ch.current_station_id == st.station_id) if ch.current_station_id else False curr_station_name = station_name_map.get(ch.current_station_id) if ch.current_station_id else None chassis_statuses.append(schemas.ChassisLiveQaStatus( instance_id=inst.instance_id, chassis_id=ch.chassis_id, chassis_no=ch.chassis_no, model_name=model_name or ch.light_type, body_type=None, customer_name=ch.dealer_id or ch.client_id, station_id=st.station_id, station_name=st.station_name, station_code=st.station_code, station_sequence=st.sequence_no, overall_qa_status=inst.station_qa_status or inst.overall_result, is_cleared=cleared_flag, is_current_station=is_curr, current_station_name=curr_station_name, inspector_name=u.name if u else None, inspector_code=u.employee_code if u else None, checkpoints_total=total_items, checkpoints_completed=responses_cnt, defects_count=defects_cnt, updated_at=inst.updated_at or inst.created_at, )) # 4. Live Defect Feed defect_rows = (await db.execute( select(QaCheckpointResponse, QaCheckpointItem, QaChecksheetInstance, ChassisMaster, StationMaster, LoginUsers) .join(QaCheckpointItem, QaCheckpointItem.item_id == QaCheckpointResponse.item_id) .join(QaChecksheetInstance, QaChecksheetInstance.instance_id == QaCheckpointResponse.instance_id) .join(ChassisMaster, ChassisMaster.chassis_id == QaChecksheetInstance.chassis_id) .join(StationMaster, StationMaster.station_id == QaChecksheetInstance.station_id) .outerjoin(LoginUsers, LoginUsers.user_id == QaChecksheetInstance.inspector_id) .where(or_(QaCheckpointResponse.status == 'NOT_OK', QaCheckpointResponse.is_flagged == True)) .order_by(QaCheckpointResponse.created_at.desc()) .limit(100) )).all() defects_list = [] for resp, it, inst, ch, st, u in defect_rows: photo_urls = (await db.execute( select(QaResponsePhoto.photo_url).where( QaResponsePhoto.instance_id == resp.instance_id, QaResponsePhoto.item_id == resp.item_id ) )).scalars().all() defects_list.append(schemas.DefectDetailItem( response_id=resp.response_id, instance_id=resp.instance_id, chassis_no=ch.chassis_no, station_name=st.station_name, checkpoint_desc=it.checkpoint_desc, checkpoint_class=it.checkpoint_class or "MAJOR", status=resp.status, measured_value=float(resp.measured_value) if resp.measured_value is not None else None, observation=resp.observation, inspector_name=u.name if u else None, inspector_code=u.employee_code if u else None, photo_urls=list(photo_urls), rework_status=resp.rework_status, created_at=resp.created_at, )) kpis = schemas.SupervisorDashboardKpis( total_active_chassis=len(total_active_chassis_set), total_passed=total_passed, total_failed=total_failed, total_pending=total_pending, total_defects=len(defects_list), cleared_for_chassis_out=cleared_for_chassis_out, active_inspectors=len(active_inspectors_set), monitored_stations=monitored_stations, ) return schemas.SupervisorDashboardResponse( kpis=kpis, chassis_statuses=chassis_statuses, defects=defects_list, station_allocations=station_allocations, ) # ═══════════════════════════════════════════════════════════ # INDIVIDUAL QA INSPECTOR ANALYTICS # ═══════════════════════════════════════════════════════════ async def get_inspector_analytics(db: AsyncSession) -> schemas.InspectorAnalyticsResponse: # Fetch all inspectors / supervisors or users with QA assignments inspectors_rows = (await db.execute( select(LoginUsers).where( or_( LoginUsers.role_code.in_(['QA_INSPECTOR', 'QA_SUPERVISOR']), LoginUsers.user_id.in_(select(StationUserAssignment.user_id).where(StationUserAssignment.role == 'QA')), LoginUsers.user_id.in_(select(QaChecksheetInstance.inspector_id).where(QaChecksheetInstance.inspector_id.isnot(None))), ) ).order_by(LoginUsers.name) )).scalars().all() inspector_metrics = [] total_plant_inspections = 0 total_plant_passed = 0 total_defects_caught = 0 crit_cnt = maj_cnt = min_cnt = 0 for u in inspectors_rows: # Inspections where this user was the inspector user_inst_clause = or_(QaChecksheetInstance.inspector_id == u.user_id, QaChecksheetInstance.passed_by == u.user_id) total_inspected = (await db.execute( select(func.count()).select_from(QaChecksheetInstance).where(user_inst_clause) )).scalar() or 0 passed_inspected = (await db.execute( select(func.count()).select_from(QaChecksheetInstance).where(user_inst_clause, QaChecksheetInstance.overall_result == 'PASSED') )).scalar() or 0 failed_inspected = (await db.execute( select(func.count()).select_from(QaChecksheetInstance).where(user_inst_clause, QaChecksheetInstance.overall_result == 'FAILED') )).scalar() or 0 total_checkpoints = (await db.execute( select(func.count()).select_from(QaCheckpointResponse) .join(QaChecksheetInstance, QaChecksheetInstance.instance_id == QaCheckpointResponse.instance_id) .where(user_inst_clause) )).scalar() or 0 # Defects logged user_defects = (await db.execute( select(QaCheckpointItem.checkpoint_class, func.count()) .select_from(QaCheckpointResponse) .join(QaChecksheetInstance, QaChecksheetInstance.instance_id == QaCheckpointResponse.instance_id) .join(QaCheckpointItem, QaCheckpointItem.item_id == QaCheckpointResponse.item_id) .where(user_inst_clause, QaCheckpointResponse.status == 'NOT_OK') .group_by(QaCheckpointItem.checkpoint_class) )).all() u_crit = sum(c for cls, c in user_defects if (cls or '').upper() == 'CRITICAL') u_maj = sum(c for cls, c in user_defects if (cls or '').upper() == 'MAJOR') u_min = sum(c for cls, c in user_defects if (cls or '').upper() == 'MINOR') u_total_defects = u_crit + u_maj + u_min # Assigned station names station_names = (await db.execute( select(StationMaster.station_name) .join(StationUserAssignment, StationUserAssignment.station_id == StationMaster.station_id) .where(StationUserAssignment.user_id == u.user_id, StationUserAssignment.role == 'QA', StationUserAssignment.is_active == True) )).scalars().all() # Last active last_inst_time = (await db.execute( select(func.max(QaChecksheetInstance.updated_at)).where(user_inst_clause) )).scalar() pass_rate = round((passed_inspected / total_inspected * 100), 1) if total_inspected > 0 else 100.0 total_plant_inspections += total_inspected total_plant_passed += passed_inspected total_defects_caught += u_total_defects crit_cnt += u_crit maj_cnt += u_maj min_cnt += u_min inspector_metrics.append(schemas.InspectorMetricItem( user_id=u.user_id, name=u.name, employee_code=u.employee_code, role_code=u.role_code, email=u.email, assigned_stations=list(station_names), total_inspected=total_inspected, total_passed=passed_inspected, total_failed=failed_inspected, pass_rate=pass_rate, total_checkpoints=total_checkpoints, total_defects=u_total_defects, critical_defects=u_crit, major_defects=u_maj, minor_defects=u_min, last_active=last_inst_time or u.last_login_at, )) plant_pass_rate = round((total_plant_passed / total_plant_inspections * 100), 1) if total_plant_inspections > 0 else 100.0 summary = schemas.InspectorAnalyticsSummary( total_inspectors=len(inspectors_rows), total_inspections=total_plant_inspections, plant_pass_rate=plant_pass_rate, total_defects_caught=total_defects_caught, critical_defects=crit_cnt, major_defects=maj_cnt, minor_defects=min_cnt, ) return schemas.InspectorAnalyticsResponse( summary=summary, inspectors=inspector_metrics, )