# app/modules/chassis_tracker/order_spec_service.py import uuid from typing import Optional, List, Dict from datetime import datetime, date from sqlalchemy import select, or_, and_, func from sqlalchemy.ext.asyncio import AsyncSession from fastapi import HTTPException from app.models.yard_models import ChassisMaster, VehicleModelMaster from app.models.sales_models import CustomerMaster, ClientMaster from app.models.dealer_models import ( DealerOrder, DealerOrderVehicle, DealerOrderVehicleChassis, DealerOrderVehicleFeature, SpecialFeatureMaster, SpecialFeatureOptionMaster, BodyTypeMaster, CustomerSegmentMaster, RtoMaster ) from app.models.rm_models import StationMaster from app.models.bom_models import ProductMaster, BomMaster, BomDetail from app.models.rm_models import RmMaster from .schemas import ( ChassisOrderSpecOut, SpecialFeatureItemOut, SpecialFeatureSectionOut, BomMaterialItemOut ) async def resolve_chassis_order_spec(db: AsyncSession, chassis: ChassisMaster) -> ChassisOrderSpecOut: """ Given a ChassisMaster instance, resolves complete order details, manufacturing specifications, dynamic special features checklist, and bill of materials recipe. """ if isinstance(chassis, str): return await get_chassis_order_spec_by_identifier(db, chassis) # 1. Resolve Model model_number = None if chassis.model_id: m = (await db.execute( select(VehicleModelMaster.model_number).where(VehicleModelMaster.model_id == chassis.model_id) )).scalar_one_or_none() if m: model_number = m # 2. Resolve Station current_station_name = None if chassis.current_station_id: st = (await db.execute( select(StationMaster.station_name).where(StationMaster.station_id == chassis.current_station_id) )).scalar_one_or_none() if st: current_station_name = st # 3. Resolve Direct Dealer & Client on Chassis chassis_dealer_name = None chassis_dealer_city = None chassis_dealer_phone = None if chassis.dealer_id: dl = (await db.execute( select(CustomerMaster).where(CustomerMaster.customer_id == chassis.dealer_id) )).scalar_one_or_none() if dl: chassis_dealer_name = dl.name chassis_dealer_city = getattr(dl, 'city', None) chassis_dealer_phone = getattr(dl, 'phone', None) chassis_client_name = None chassis_client_city = None chassis_client_phone = None chassis_client_address = None if chassis.client_id: cl = (await db.execute( select(ClientMaster).where(ClientMaster.client_id == chassis.client_id) )).scalar_one_or_none() if cl: chassis_client_name = cl.name chassis_client_city = getattr(cl, 'city', None) chassis_client_phone = getattr(cl, 'phone', None) chassis_client_address = getattr(cl, 'address', None) # 4. Find DealerOrderVehicle (dov) dov: Optional[DealerOrderVehicle] = None expected_delivery: Optional[date] = None if chassis.dealer_order_vehicle_id: dov = (await db.execute( select(DealerOrderVehicle).where(DealerOrderVehicle.order_vehicle_id == chassis.dealer_order_vehicle_id) )).scalar_one_or_none() if not dov and chassis.chassis_no: dovc = (await db.execute( select(DealerOrderVehicleChassis).where(func.lower(DealerOrderVehicleChassis.chassis_no) == chassis.chassis_no.lower().strip()) )).scalar_one_or_none() if dovc: expected_delivery = dovc.expected_delivery_date dov = (await db.execute( select(DealerOrderVehicle).where(DealerOrderVehicle.order_vehicle_id == dovc.order_vehicle_id) )).scalar_one_or_none() # 5. If DOV exists, load deep order attributes & special features has_order = False order_id = None order_no = None order_date = None confirmed_date = None order_remarks = None vehicle_notes = None rep_name = None rep_contact = None billing_by = None order_status = None body_type_name = None segment_name = None rto_name = None rto_description = None order_qty = 1 dealer_name = chassis_dealer_name dealer_city = chassis_dealer_city dealer_phone = chassis_dealer_phone client_name = chassis_client_name client_city = chassis_client_city client_phone = chassis_client_phone client_address = chassis_client_address special_sections: List[SpecialFeatureSectionOut] = [] total_features_count = 0 if dov: has_order = True vehicle_notes = dov.notes order_qty = dov.qty or 1 # Body type if dov.body_type_id: bt = (await db.execute( select(BodyTypeMaster.name).where(BodyTypeMaster.body_type_id == dov.body_type_id) )).scalar_one_or_none() if bt: body_type_name = bt # Customer segment if dov.segment_id: sg = (await db.execute( select(CustomerSegmentMaster.name).where(CustomerSegmentMaster.segment_id == dov.segment_id) )).scalar_one_or_none() if sg: segment_name = sg # RTO if dov.rto_id: rt = (await db.execute( select(RtoMaster).where(RtoMaster.rto_id == dov.rto_id) )).scalar_one_or_none() if rt: rto_name = rt.name rto_description = rt.description # Model override if DOV model differs if not model_number and dov.model_id: ov_m = (await db.execute( select(VehicleModelMaster.model_number).where(VehicleModelMaster.model_id == dov.model_id) )).scalar_one_or_none() if ov_m: model_number = ov_m # Parent DealerOrder do = (await db.execute( select(DealerOrder).where(DealerOrder.order_id == dov.order_id) )).scalar_one_or_none() if do: order_id = do.order_id order_no = do.order_no order_date = do.created_at or do.submitted_at confirmed_date = do.confirmed_at order_remarks = do.remarks rep_name = do.rep_name rep_contact = do.rep_contact billing_by = do.billing_by order_status = do.status if do.dealer_id and not dealer_name: dl = (await db.execute( select(CustomerMaster).where(CustomerMaster.customer_id == do.dealer_id) )).scalar_one_or_none() if dl: dealer_name = dl.name dealer_city = getattr(dl, 'city', None) dealer_phone = getattr(dl, 'phone', None) if do.client_id and not client_name: cl = (await db.execute( select(ClientMaster).where(ClientMaster.client_id == do.client_id) )).scalar_one_or_none() if cl: client_name = cl.name client_city = getattr(cl, 'city', None) client_phone = getattr(cl, 'phone', None) client_address = getattr(cl, 'address', None) # Features feat_stmt = ( select( SpecialFeatureMaster.feature_id, SpecialFeatureMaster.section, SpecialFeatureMaster.feature_name, SpecialFeatureMaster.input_type, SpecialFeatureMaster.sequence_no, SpecialFeatureOptionMaster.option_id, SpecialFeatureOptionMaster.option_value, DealerOrderVehicleFeature.bool_value, DealerOrderVehicleFeature.text_value, SpecialFeatureOptionMaster.image_url, ) .join(SpecialFeatureMaster, DealerOrderVehicleFeature.feature_id == SpecialFeatureMaster.feature_id) .outerjoin(SpecialFeatureOptionMaster, DealerOrderVehicleFeature.feature_option_id == SpecialFeatureOptionMaster.option_id) .where(DealerOrderVehicleFeature.order_vehicle_id == dov.order_vehicle_id) .order_by(SpecialFeatureMaster.section, SpecialFeatureMaster.sequence_no) ) features_rows = (await db.execute(feat_stmt)).all() section_map: Dict[str, List[SpecialFeatureItemOut]] = {} for row in features_rows: f_id, sec, f_name, in_type, seq_no, opt_id, opt_val, b_val, t_val, img_url = row if in_type == 'DROPDOWN': display_val = opt_val or "Specified Option" elif in_type == 'BOOLEAN': display_val = "Yes / Included" if b_val else "No / Standard" elif in_type == 'TEXT': display_val = t_val or "Custom Note" else: display_val = opt_val or t_val or ("Yes" if b_val else "Standard") item = SpecialFeatureItemOut( feature_id=f_id, feature_name=f_name, section=sec or "General Features", input_type=in_type or "DROPDOWN", sequence_no=seq_no or 0, option_id=opt_id, option_value=opt_val, bool_value=b_val, text_value=t_val, display_value=display_val, image_url=img_url, ) section_map.setdefault(sec or "General Features", []).append(item) total_features_count += 1 for sec_name, items in section_map.items(): special_sections.append( SpecialFeatureSectionOut( section=sec_name, count=len(items), items=items, ) ) # 6. Bill of Materials Recipe Resolution bom_number = None product_name = None bom_materials: List[BomMaterialItemOut] = [] if body_type_name and segment_name and model_number: cand1 = f"{body_type_name.strip()} {segment_name.strip()} {model_number.strip()}".upper() prod = (await db.execute( select(ProductMaster).where( or_( func.upper(ProductMaster.name) == cand1, ProductMaster.name.ilike(f"%{model_number.strip()}%"), ) ).order_by(ProductMaster.created_at.desc()) )).scalars().first() if prod: product_name = prod.name bom = (await db.execute( select(BomMaster).where(BomMaster.product_id == prod.product_id, BomMaster.is_active == True) )).scalars().first() if bom: bom_number = bom.bom_number mat_stmt = ( select( BomDetail.rm_id, RmMaster.part_no, RmMaster.name.label('material_name'), BomDetail.quantity, BomDetail.uom ) .join(RmMaster, BomDetail.rm_id == RmMaster.rm_id) .where(BomDetail.bom_id == bom.bom_id) .order_by(RmMaster.name) ) mat_rows = (await db.execute(mat_stmt)).all() for rm_id, code, m_name, qty, uom in mat_rows: bom_materials.append( BomMaterialItemOut( rm_id=rm_id, item_code=code, material_name=m_name, quantity=float(qty) if qty is not None else 1.0, uom=uom or "NOS", ) ) return ChassisOrderSpecOut( has_order=has_order, chassis_id=chassis.chassis_id, chassis_no=chassis.chassis_no, order_id=order_id, order_no=order_no, order_date=order_date, confirmed_date=confirmed_date, expected_delivery_date=expected_delivery, dealer_id=chassis.dealer_id, dealer_name=dealer_name, dealer_city=dealer_city, dealer_phone=dealer_phone, client_id=chassis.client_id, client_name=client_name, client_phone=client_phone, client_city=client_city, client_address=client_address, rep_name=rep_name, rep_contact=rep_contact, billing_by=billing_by, order_status=order_status, order_remarks=order_remarks, vehicle_notes=vehicle_notes, order_quantity=order_qty, model_number=model_number, body_type_name=body_type_name, segment_name=segment_name, rto_name=rto_name, rto_description=rto_description, fuel_type=chassis.fuel_type or 'DIESEL', voltage=chassis.voltage or '12V', light_type=chassis.light_type or 'SQUARE_LIGHT', fuel_receiving_qty=float(chassis.fuel_receiving_qty) if chassis.fuel_receiving_qty is not None else None, yard_location=chassis.yard_location or '34 Acre Yard', source_type=chassis.source_type or 'SALES_ORDER', current_station_name=current_station_name, special_features_sections=special_sections, total_special_features_count=total_features_count, bom_number=bom_number, product_name=product_name, bom_materials=bom_materials, ) async def get_chassis_order_spec_by_identifier(db: AsyncSession, identifier: str) -> ChassisOrderSpecOut: ident = (identifier or '').strip() if not ident: raise HTTPException(status_code=400, detail="Chassis number or identifier required.") # Try UUID chassis = None try: uid = uuid.UUID(ident) chassis = await db.get(ChassisMaster, uid) except Exception: pass if not chassis: chassis = (await db.execute( select(ChassisMaster).where(ChassisMaster.chassis_no.ilike(ident)) )).scalar_one_or_none() if not chassis: raise HTTPException(status_code=404, detail=f"Chassis '{ident}' not found.") return await resolve_chassis_order_spec(db, chassis)