import uuid
from datetime import datetime, timedelta, timezone
from typing import Optional
from fastapi import HTTPException
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, func, delete, update
from sqlalchemy.orm import selectinload

from app.models.sales_models import CustomerMaster, ClientMaster
from app.models.yard_models import VehicleModelMaster, ChassisMaster
from app.models.dispatch_models import DispatchThermalChecksheet, DispatchThermalChecksheetItem
from app.models.dealer_models import (
    DealerPortalLog, DealerPortalActionLog,
    BodyTypeMaster, ModelBodyTypeMap, CustomerSegmentMaster, ModelSegmentMap, RtoMaster,
    SpecialFeatureMaster, SpecialFeatureOptionMaster,
    DealerOrder, DealerOrderVehicle, DealerOrderVehicleChassis, DealerOrderVehicleFeature,
    DealerOrderActivityLog, DealerRequest, DealerVehicleTemplate, DealerVehicleTemplateFeature,
    FeatureSegmentMap
)
from app.core.security import verify_password, create_access_token
from app.modules.dealer_portal.schemas import DealerLoginRequest, DealerLoginResponse, ClientCreate, OrderCreate
from app.modules.notifications import service as notifications_service
from . import schemas


async def authenticate_dealer(db: AsyncSession, req: DealerLoginRequest, ip: str, user_agent: str) -> DealerLoginResponse:
    username = (req.portal_username or "").strip()
    result = await db.execute(
        select(CustomerMaster)
        .where(func.lower(func.trim(CustomerMaster.portal_username)) == username.lower())
        .where(CustomerMaster.portal_enabled == True)
        .where(CustomerMaster.is_active == True)
    )
    dealer = result.scalar_one_or_none()
    if not dealer or not dealer.portal_password_hash:
        raise HTTPException(status_code=401, detail="Invalid credentials or portal not enabled")

    if not verify_password(req.portal_password, dealer.portal_password_hash):
        raise HTTPException(status_code=401, detail="Invalid credentials")

    log_entry = DealerPortalLog(
        dealer_id=dealer.customer_id,
        session_token=uuid.uuid4().hex,
        ip_address=ip,
        user_agent=user_agent,
        expires_at=datetime.now(timezone.utc) + timedelta(days=30),
    )
    db.add(log_entry)
    await db.commit()
    await db.refresh(log_entry)

    access_token = create_access_token({
        "sub": dealer.customer_id,
        "sub_type": "dealer",
        "session_id": str(log_entry.log_id),
    })

    return DealerLoginResponse(
        access_token=access_token, 
        dealer_id=dealer.customer_id, 
        dealer_name=dealer.name,
        allow_chassis_tracking=dealer.allow_chassis_tracking,
    )


async def logout_dealer(db: AsyncSession, session_id: uuid.UUID):
    log_entry = await db.get(DealerPortalLog, session_id)
    if log_entry:
        log_entry.invalidated_at = datetime.now(timezone.utc)
        await db.commit()


async def log_dealer_action(
    db: AsyncSession, dealer_id: str, action_type: str,
    entity_type: str | None = None, entity_id: str | None = None,
    details: dict | None = None, ip: str | None = None,
):
    db.add(DealerPortalActionLog(
        dealer_id=dealer_id, action_type=action_type, entity_type=entity_type,
        entity_id=entity_id, action_details=details, ip_address=ip,
    ))
    await db.commit()


def _generate_client_id() -> str:
    return f"CLI-{uuid.uuid4().hex[:6].upper()}"


async def search_clients(db: AsyncSession, dealer_id: str, q: str, limit: int = 10):
    from app.models.sales_models import ClientMaster, DealerClientMap
    stmt = (
        select(ClientMaster)
        .join(DealerClientMap, ClientMaster.client_id == DealerClientMap.client_id)
        .where(DealerClientMap.dealer_id == dealer_id, ClientMaster.is_active == True)
    )
    if q:
        stmt = stmt.where(ClientMaster.name.ilike(f"%{q}%"))
    stmt = stmt.order_by(ClientMaster.name).limit(limit)
    return (await db.execute(stmt)).scalars().all()


async def list_clients(db: AsyncSession, dealer_id: str):
    from app.models.sales_models import ClientMaster, DealerClientMap
    stmt = (
        select(ClientMaster)
        .join(DealerClientMap, ClientMaster.client_id == DealerClientMap.client_id)
        .where(DealerClientMap.dealer_id == dealer_id)
        .where(ClientMaster.is_active == True)
        .order_by(ClientMaster.name)
    )
    clients = (await db.execute(stmt)).scalars().all()
    for c in clients:
        c.dealer_id = dealer_id
    return clients


async def get_client(db: AsyncSession, dealer_id: str, client_id: str) -> ClientMaster:
    from app.models.sales_models import ClientMaster, DealerClientMap
    stmt = (
        select(ClientMaster)
        .join(DealerClientMap, ClientMaster.client_id == DealerClientMap.client_id)
        .where(DealerClientMap.dealer_id == dealer_id, ClientMaster.client_id == client_id)
    )
    client = (await db.execute(stmt)).scalars().first()
    if not client:
        raise HTTPException(status_code=404, detail="Client not found")
    client.dealer_id = dealer_id
    return client


async def get_client_for_staff(db: AsyncSession, client_id: str) -> ClientMaster:
    from app.models.sales_models import ClientMaster, DealerClientMap
    client = await db.get(ClientMaster, client_id)
    if not client:
        raise HTTPException(status_code=404, detail="Client not found")
        
    stmt = select(DealerClientMap.dealer_id).where(DealerClientMap.client_id == client_id)
    dealer_id = (await db.execute(stmt)).scalars().first()
    
    client.dealer_id = dealer_id
    if dealer_id:
        dealer = await db.get(CustomerMaster, dealer_id)
        client.dealer_name = dealer.name if dealer else None
    else:
        client.dealer_name = None
        
    return client


async def search_clients_across_dealers(db: AsyncSession, q: str, limit: int = 20):
    from app.models.sales_models import ClientMaster, DealerClientMap
    stmt = (
        select(ClientMaster, DealerClientMap.dealer_id, CustomerMaster.name.label('dealer_name'))
        .outerjoin(DealerClientMap, ClientMaster.client_id == DealerClientMap.client_id)
        .outerjoin(CustomerMaster, DealerClientMap.dealer_id == CustomerMaster.customer_id)
        .where(ClientMaster.is_active == True)
    )
    if q:
        stmt = stmt.where(ClientMaster.name.ilike(f"%{q}%"))
    stmt = stmt.order_by(ClientMaster.name).limit(limit)
    rows = (await db.execute(stmt)).all()
    results = []
    for client, dealer_id, dealer_name in rows:
        client.dealer_id = dealer_id
        client.dealer_name = dealer_name
        results.append(client)
    return results


async def create_client(
    db: AsyncSession, dealer_id: str, payload: ClientCreate,
    created_by_dealer: bool, created_by_user_id: Optional[uuid.UUID] = None,
) -> ClientMaster:
    from app.models.sales_models import ClientMaster, DealerClientMap
    existing = (await db.execute(
        select(ClientMaster)
        .join(DealerClientMap, ClientMaster.client_id == DealerClientMap.client_id)
        .where(
            DealerClientMap.dealer_id == dealer_id,
            ClientMaster.is_active == True,
            ClientMaster.name.ilike(payload.name.strip()),
        )
    )).scalar_one_or_none()
    if existing:
        raise HTTPException(status_code=400, detail=f"A client named '{existing.name}' already exists for this dealer")

    client = ClientMaster(
        client_id=_generate_client_id(),
        created_by_user_id=created_by_user_id,
        **payload.model_dump(),
    )
    db.add(client)
    await db.flush()
    
    db.add(DealerClientMap(dealer_id=dealer_id, client_id=client.client_id))
    
    await db.commit()
    await db.refresh(client)
    return client


async def update_client(
    db: AsyncSession, dealer_id: str, client_id: str, payload: schemas.ClientUpdate
) -> ClientMaster:
    from app.models.sales_models import ClientMaster, DealerClientMap
    client = await get_client(db, dealer_id, client_id)
    
    if payload.name is not None and payload.name.strip() != client.name:
        existing = (await db.execute(
            select(ClientMaster)
            .join(DealerClientMap, ClientMaster.client_id == DealerClientMap.client_id)
            .where(
                DealerClientMap.dealer_id == dealer_id,
                ClientMaster.is_active == True,
                ClientMaster.name.ilike(payload.name.strip()),
                ClientMaster.client_id != client_id
            )
        )).scalar_one_or_none()
        if existing:
            raise HTTPException(status_code=400, detail=f"A client named '{existing.name}' already exists for this dealer")

    update_data = payload.model_dump(exclude_unset=True)
    for key, value in update_data.items():
        setattr(client, key, value)
        
    await db.commit()
    await db.refresh(client)
    return client


async def get_order_masters(db: AsyncSession) -> dict:
    models = (await db.execute(
        select(VehicleModelMaster).where(VehicleModelMaster.is_active == True).order_by(VehicleModelMaster.model_number)
    )).scalars().all()
    body_types = (await db.execute(
        select(BodyTypeMaster).where(BodyTypeMaster.is_active == True).order_by(BodyTypeMaster.name)
    )).scalars().all()
    segments = (await db.execute(
        select(CustomerSegmentMaster).where(CustomerSegmentMaster.is_active == True).order_by(CustomerSegmentMaster.name)
    )).scalars().all()
    rto_list = (await db.execute(
        select(RtoMaster).where(RtoMaster.is_active == True).order_by(RtoMaster.name)
    )).scalars().all()

    feature_rows = (await db.execute(
        select(SpecialFeatureMaster).where(SpecialFeatureMaster.is_active == True).order_by(SpecialFeatureMaster.sequence_no)
    )).scalars().all()
    option_rows = (await db.execute(
        select(SpecialFeatureOptionMaster).where(SpecialFeatureOptionMaster.is_active == True).order_by(SpecialFeatureOptionMaster.sequence_no)
    )).scalars().all()
    options_by_feature = {}
    for o in option_rows:
        options_by_feature.setdefault(o.feature_id, []).append(o)
    for f in feature_rows:
        f.options = options_by_feature.get(f.feature_id, [])

    mbt_rows = (await db.execute(select(ModelBodyTypeMap))).scalars().all()
    ms_rows = (await db.execute(select(ModelSegmentMap))).scalars().all()
    fs_rows = (await db.execute(select(FeatureSegmentMap))).scalars().all()

    from app.models.dealer_models import SeatModelConfiguration
    seat_configs = (await db.execute(select(SeatModelConfiguration).where(SeatModelConfiguration.is_active == True))).scalars().all()

    return {
        "models": models, "body_types": body_types, "segments": segments, "rto_list": rto_list,
        "features": feature_rows,
        "model_body_type_pairs": [{"model_id": r.model_id, "related_id": r.body_type_id} for r in mbt_rows],
        "model_segment_pairs": [{"model_id": r.model_id, "related_id": r.segment_id} for r in ms_rows],
        "feature_segment_pairs": [{"feature_id": r.feature_id, "segment_id": r.segment_id} for r in fs_rows],
        "seat_configs": seat_configs,
    }


def _generate_order_no() -> str:
    return f"DLO-{uuid.uuid4().hex[:6].upper()}"


async def create_order(db: AsyncSession, dealer_id: str, payload: OrderCreate) -> DealerOrder:
    from app.models.sales_models import DealerClientMap
    map_record = (await db.execute(
        select(DealerClientMap).where(DealerClientMap.client_id == payload.client_id, DealerClientMap.dealer_id == dealer_id)
    )).scalar_one_or_none()
    if not map_record:
        raise HTTPException(status_code=404, detail="Client not found or not mapped to this dealer")

    # Validate the model/body-type/segment relationships server-side — never trust
    # that the frontend's cascading dropdowns were the only path used to build this payload.
    await _validate_vehicles(db, payload.vehicles)

    if not payload.is_draft:
        if not payload.rep_name or not payload.rep_contact:
            raise HTTPException(status_code=400, detail="Representative name and contact are required for submission")

    order = DealerOrder(
        order_id=uuid.uuid4(),
        order_no=_generate_order_no(),
        dealer_id=dealer_id,
        client_id=payload.client_id,
        status='DRAFT' if payload.is_draft else 'SUBMITTED',
        remarks=payload.remarks,
        rep_name=payload.rep_name,
        rep_contact=payload.rep_contact,
        billing_by=None,
        submitted_at=None if payload.is_draft else datetime.utcnow(),
    )
    db.add(order)
    await db.flush()

    await _write_vehicles(db, order.order_id, payload.vehicles)

    db.add(DealerOrderActivityLog(
        id=uuid.uuid4(), order_id=order.order_id, action='DRAFT_SAVED' if payload.is_draft else 'SUBMITTED', performed_by_dealer=True,
    ))

    await db.commit()
    await db.refresh(order)

    if not payload.is_draft:
        await notifications_service.notify_order_submitted(db, order, is_resubmission=False)

    return order


async def list_orders(db: AsyncSession, dealer_id: str, client_id: str | None = None):
    stmt = (
        select(DealerOrder, ClientMaster.name.label('client_name'))
        .join(ClientMaster, DealerOrder.client_id == ClientMaster.client_id)
        .where(DealerOrder.dealer_id == dealer_id)
        .order_by(DealerOrder.created_at.desc())
    )
    if client_id:
        stmt = stmt.where(DealerOrder.client_id == client_id)
    rows = (await db.execute(stmt)).all()

    order_ids = [order.order_id for order, _ in rows]
    chassis_by_order = {}
    qty_by_order = {}
    if order_ids:
        vehs = (await db.execute(
            select(DealerOrderVehicle.order_id, DealerOrderVehicle.order_vehicle_id, DealerOrderVehicle.qty)
            .where(DealerOrderVehicle.order_id.in_(order_ids))
        )).all()
        ov_ids = [v.order_vehicle_id for v in vehs]
        for v in vehs:
            qty_by_order[v.order_id] = qty_by_order.get(v.order_id, 0) + (v.qty or 0)

        if ov_ids:
            c_rows = (await db.execute(
                select(DealerOrderVehicle.order_id, DealerOrderVehicleChassis.chassis_no)
                .join(DealerOrderVehicleChassis, DealerOrderVehicle.order_vehicle_id == DealerOrderVehicleChassis.order_vehicle_id)
                .where(DealerOrderVehicle.order_vehicle_id.in_(ov_ids), DealerOrderVehicleChassis.chassis_no.isnot(None))
                .order_by(DealerOrderVehicleChassis.seq_no)
            )).all()
            for o_id, c_no in c_rows:
                if c_no and c_no.strip():
                    chassis_by_order.setdefault(o_id, []).append(c_no.strip())

    orders = []
    for order, client_name in rows:
        order.client_name = client_name
        c_list = chassis_by_order.get(order.order_id, [])
        order.chassis_nos = c_list
        order.total_qty = qty_by_order.get(order.order_id, 0)
        order.allocated_qty = len(c_list)
        orders.append(order)
    return orders


async def _hydrate_order(db: AsyncSession, order: DealerOrder) -> DealerOrder:
    client = await db.get(ClientMaster, order.client_id)
    order.client_name = client.name if client else None

    dealer = await db.get(CustomerMaster, order.dealer_id)
    order.dealer_name = dealer.name if dealer else None

    vehicles = (await db.execute(
        select(DealerOrderVehicle)
        .options(selectinload(DealerOrderVehicle.features))
        .where(DealerOrderVehicle.order_id == order.order_id)
        .order_by(DealerOrderVehicle.sequence_no)
    )).scalars().all()

    model_map = {m.model_id: m.model_number for m in (await db.execute(select(VehicleModelMaster))).scalars().all()}
    body_type_map = {b.body_type_id: b.name for b in (await db.execute(select(BodyTypeMaster))).scalars().all()}
    segment_map = {s.segment_id: s.name for s in (await db.execute(select(CustomerSegmentMaster))).scalars().all()}
    rto_map = {r.rto_id: r.name for r in (await db.execute(select(RtoMaster))).scalars().all()}

    vehicle_ids = [v.order_vehicle_id for v in vehicles]
    chassis_by_vehicle = {}
    if vehicle_ids:
        all_chassis = (await db.execute(
            select(DealerOrderVehicleChassis)
            .where(DealerOrderVehicleChassis.order_vehicle_id.in_(vehicle_ids))
            .order_by(DealerOrderVehicleChassis.seq_no)
        )).scalars().all()
        for c in all_chassis:
            chassis_by_vehicle.setdefault(c.order_vehicle_id, []).append(c)

    for v in vehicles:
        v.model_name = model_map.get(v.model_id)
        v.body_type_name = body_type_map.get(v.body_type_id)
        v.segment_name = segment_map.get(v.segment_id)
        v.rto_name = rto_map.get(v.rto_id) if v.rto_id else None
        v.chassis = chassis_by_vehicle.get(v.order_vehicle_id, [])

    order.vehicles = vehicles
    order.activity = (await db.execute(
        select(DealerOrderActivityLog).where(DealerOrderActivityLog.order_id == order.order_id)
        .order_by(DealerOrderActivityLog.created_at)
    )).scalars().all()
    return order


async def _validate_vehicles(db: AsyncSession, vehicles, current_order_id: Optional[uuid.UUID] = None):
    valid_body_types = {
        (r.model_id, r.body_type_id) for r in (await db.execute(select(ModelBodyTypeMap))).scalars().all()
    }
    valid_segments = {
        (r.model_id, r.segment_id) for r in (await db.execute(select(ModelSegmentMap))).scalars().all()
    }
    submitted_chassis_nos = []
    for v in vehicles:
        if (v.model_id, v.body_type_id) not in valid_body_types:
            raise HTTPException(status_code=400, detail="Selected body type is not offered for this model")
        if (v.model_id, v.segment_id) not in valid_segments:
            raise HTTPException(status_code=400, detail="Selected segment is not offered for this model")
        if len(v.chassis) > v.qty:
            raise HTTPException(status_code=400, detail="Chassis count cannot exceed the quantity")
        for c in (v.chassis or []):
            if c.chassis_no and c.chassis_no.strip():
                submitted_chassis_nos.append(c.chassis_no.strip())

    if len(submitted_chassis_nos) != len(set(submitted_chassis_nos)):
        raise HTTPException(
            status_code=400,
            detail="The same chassis number cannot be specified multiple times in the same order."
        )

    if submitted_chassis_nos:
        conflict_stmt = (
            select(DealerOrderVehicleChassis.chassis_no, DealerOrder.order_no)
            .join(DealerOrderVehicle, DealerOrderVehicle.order_vehicle_id == DealerOrderVehicleChassis.order_vehicle_id)
            .join(DealerOrder, DealerOrder.order_id == DealerOrderVehicle.order_id)
            .where(
                DealerOrderVehicleChassis.chassis_no.in_(submitted_chassis_nos),
                DealerOrder.status != 'CANCELLED'
            )
        )
        if current_order_id:
            conflict_stmt = conflict_stmt.where(DealerOrder.order_id != current_order_id)
        conflict = (await db.execute(conflict_stmt)).first()
        if conflict:
            raise HTTPException(
                status_code=400,
                detail=f"Chassis {conflict[0]} is already allocated to active order {conflict[1]}."
            )


async def _write_vehicles(db: AsyncSession, order_id: uuid.UUID, vehicles):
    for i, v in enumerate(vehicles):
        vehicle = DealerOrderVehicle(
            order_vehicle_id=uuid.uuid4(),
            order_id=order_id,
            model_id=v.model_id,
            body_type_id=v.body_type_id,
            segment_id=v.segment_id,
            rto_id=v.rto_id,
            qty=v.qty,
            sequence_no=i,
            notes=v.notes,
            is_edited=getattr(v, 'is_edited', False),
            edited_fields=getattr(v, 'edited_fields', None)
        )
        db.add(vehicle)
        await db.flush()

        valid_chassis_nos = []
        for seq, c_input in enumerate(v.chassis, start=1):
            if (c_input.chassis_no and c_input.chassis_no.strip()) or c_input.expected_delivery_date:
                clean_no = c_input.chassis_no.strip() if c_input.chassis_no else None
                if clean_no:
                    valid_chassis_nos.append(clean_no)
                db.add(DealerOrderVehicleChassis(
                    id=uuid.uuid4(), order_vehicle_id=vehicle.order_vehicle_id,
                    seq_no=seq, 
                    chassis_no=clean_no,
                    expected_delivery_date=c_input.expected_delivery_date
                ))

        if valid_chassis_nos:
            await db.execute(
                update(ChassisMaster)
                .where(ChassisMaster.chassis_no.in_(valid_chassis_nos))
                .values(dealer_order_vehicle_id=vehicle.order_vehicle_id)
            )

        for feat in v.features:
            db.add(DealerOrderVehicleFeature(
                id=uuid.uuid4(), order_vehicle_id=vehicle.order_vehicle_id,
                feature_id=feat.feature_id, feature_option_id=feat.feature_option_id,
                bool_value=feat.bool_value, text_value=feat.text_value, image_url=feat.image_url,
            ))


async def get_order_detail(db: AsyncSession, dealer_id: str, order_id: uuid.UUID) -> DealerOrder:
    order = await db.get(DealerOrder, order_id)
    if not order or order.dealer_id != dealer_id:
        raise HTTPException(status_code=404, detail="Order not found")
    return await _hydrate_order(db, order)


async def acknowledge_order(db: AsyncSession, dealer_id: str, order_id: uuid.UUID) -> DealerOrder:
    order = await db.get(DealerOrder, order_id)
    if not order or order.dealer_id != dealer_id:
        raise HTTPException(status_code=404, detail="Order not found")
    if order.status != 'PENDING_DEALER_ACK':
        raise HTTPException(status_code=400, detail="Only orders awaiting your acknowledgement can be confirmed")

    order.status = 'PENDING_FINAL_CONFIRMATION'
    # order.confirmed_at = datetime.utcnow() # we do this on final confirm now
    db.add(DealerOrderActivityLog(
        id=uuid.uuid4(), order_id=order.order_id, action='DEALER_ACKNOWLEDGED', performed_by_dealer=True,
    ))
    await db.commit()
    await db.refresh(order)
    await notifications_service.notify_order_acknowledged_by_dealer(db, order)
    return await _hydrate_order(db, order)


async def edit_order(db: AsyncSession, dealer_id: str, order_id: uuid.UUID, payload) -> DealerOrder:
    order = await db.get(DealerOrder, order_id)
    if not order or order.dealer_id != dealer_id:
        raise HTTPException(status_code=404, detail="Order not found")
    if order.status == 'CONFIRMED':
        raise HTTPException(status_code=400, detail="This order is already confirmed and cannot be edited")

    await _validate_vehicles(db, payload.vehicles, current_order_id=order_id)

    if not payload.is_draft:
        if not payload.rep_name or not payload.rep_contact:
            raise HTTPException(status_code=400, detail="Representative name and contact are required for submission")

    old_vehicles = (await db.execute(
        select(DealerOrderVehicle)
        .options(selectinload(DealerOrderVehicle.features))
        .where(DealerOrderVehicle.order_id == order_id).order_by(DealerOrderVehicle.sequence_no)
    )).scalars().all()
    
    old_v_map = {v.sequence_no: v for v in old_vehicles}

    for seq, p_veh in enumerate(payload.vehicles):
        is_edited = False
        edited_fields = []
        old_v = old_v_map.get(seq)
        
        if not old_v:
            is_edited = True
            edited_fields = ["model", "body_type", "segment", "qty"] + [str(f.feature_id) for f in p_veh.features]
        else:
            edited_fields = old_v.edited_fields or []
            if str(old_v.model_id) != str(p_veh.model_id):
                is_edited = True
                if "model" not in edited_fields: edited_fields.append("model")
            if str(old_v.body_type_id) != str(p_veh.body_type_id):
                is_edited = True
                if "body_type" not in edited_fields: edited_fields.append("body_type")
            if str(old_v.segment_id) != str(p_veh.segment_id):
                is_edited = True
                if "segment" not in edited_fields: edited_fields.append("segment")
            if old_v.qty != p_veh.qty:
                is_edited = True
                if "qty" not in edited_fields: edited_fields.append("qty")

            old_f_map = {str(f.feature_id): f for f in old_v.features}
            for pf in p_veh.features:
                old_f = old_f_map.get(str(pf.feature_id))
                is_f_edited = False
                if not old_f:
                    is_f_edited = True
                elif str(old_f.feature_option_id or "") != str(pf.feature_option_id or "") or bool(old_f.bool_value) != bool(pf.bool_value) or str(old_f.text_value or "") != str(pf.text_value or "") or str(old_f.image_url or "") != str(pf.image_url or ""):
                    is_f_edited = True
                if is_f_edited:
                    is_edited = True
                    if str(pf.feature_id) not in edited_fields: edited_fields.append(str(pf.feature_id))
            
            # Note: We also track if features were removed
            if len(old_f_map) != len(p_veh.features) and not is_edited:
                is_edited = True

        p_veh.is_edited = is_edited
        p_veh.edited_fields = edited_fields
        if is_edited and order.status != 'DRAFT':
            edited_names = []
            feature_ids_to_fetch = []
            for field in edited_fields:
                if field == 'model': edited_names.append('Model')
                elif field == 'body_type': edited_names.append('Body Type')
                elif field == 'segment': edited_names.append('Segment')
                elif field == 'qty': edited_names.append('Quantity')
                else:
                    try:
                        feature_ids_to_fetch.append(uuid.UUID(field))
                    except:
                        pass
            
            if feature_ids_to_fetch:
                from app.models.dealer_models import SpecialFeatureMaster
                feats = (await db.execute(select(SpecialFeatureMaster).where(SpecialFeatureMaster.feature_id.in_(feature_ids_to_fetch)))).scalars().all()
                for f in feats:
                    edited_names.append(f.feature_name)
            
            edited_str = f" ({', '.join(edited_names)})" if edited_names else ""
            new_remark = f"Dealer edited Config {seq + 1}{edited_str}."
            if not payload.remarks:
                payload.remarks = new_remark
            elif new_remark not in payload.remarks:
                payload.remarks += f"\n{new_remark}"

    old_vehicle_ids = [v.order_vehicle_id for v in old_vehicles]
    if old_vehicle_ids:
        await db.execute(
            update(ChassisMaster)
            .where(ChassisMaster.dealer_order_vehicle_id.in_(old_vehicle_ids))
            .values(dealer_order_vehicle_id=None)
        )
    for vid in old_vehicle_ids:
        await db.execute(DealerOrderVehicleFeature.__table__.delete().where(DealerOrderVehicleFeature.order_vehicle_id == vid))
        await db.execute(DealerOrderVehicleChassis.__table__.delete().where(DealerOrderVehicleChassis.order_vehicle_id == vid))
    await db.execute(DealerOrderVehicle.__table__.delete().where(DealerOrderVehicle.order_id == order_id))

    await _write_vehicles(db, order_id, payload.vehicles)

    if payload.remarks is not None:
        order.remarks = payload.remarks
    if payload.rep_name is not None:
        order.rep_name = payload.rep_name
    if payload.rep_contact is not None:
        order.rep_contact = payload.rep_contact

    if payload.is_draft:
        order.status = 'DRAFT'
        action = 'DRAFT_SAVED'
    else:
        if order.status == 'DRAFT':
            order.status = 'SUBMITTED'
            order.submitted_at = datetime.utcnow()
            action = 'SUBMITTED'
        else:
            order.status = 'DEALER_EDITED'
            action = 'DEALER_EDITED'

    db.add(DealerOrderActivityLog(
        id=uuid.uuid4(), order_id=order.order_id, action=action, performed_by_dealer=True,
    ))
    await db.commit()
    await db.refresh(order)

    if not payload.is_draft:
        await notifications_service.notify_order_submitted(db, order, is_resubmission=(action == 'DEALER_EDITED'))

    return await _hydrate_order(db, order)


# ── Staff-side (sales) order review ──

async def list_orders_for_staff(db: AsyncSession, status: str | None = None, client_id: str | None = None, dealer_id: str | None = None):
    stmt = (
        select(DealerOrder, ClientMaster.name.label('client_name'), CustomerMaster.name.label('dealer_name'))
        .join(ClientMaster, DealerOrder.client_id == ClientMaster.client_id)
        .join(CustomerMaster, DealerOrder.dealer_id == CustomerMaster.customer_id)
        .order_by(DealerOrder.created_at.desc())
    )
    if status:
        stmt = stmt.where(DealerOrder.status == status)
    if client_id:
        stmt = stmt.where(DealerOrder.client_id == client_id)
    if dealer_id:
        stmt = stmt.where(DealerOrder.dealer_id == dealer_id)
    rows = (await db.execute(stmt)).all()

    order_ids = [order.order_id for order, *_ in rows]
    chassis_by_order = {}
    qty_by_order = {}
    if order_ids:
        vehs = (await db.execute(
            select(DealerOrderVehicle.order_id, DealerOrderVehicle.order_vehicle_id, DealerOrderVehicle.qty)
            .where(DealerOrderVehicle.order_id.in_(order_ids))
        )).all()
        ov_ids = [v.order_vehicle_id for v in vehs]
        for v in vehs:
            qty_by_order[v.order_id] = qty_by_order.get(v.order_id, 0) + (v.qty or 0)

        if ov_ids:
            c_rows = (await db.execute(
                select(DealerOrderVehicle.order_id, DealerOrderVehicleChassis.chassis_no)
                .join(DealerOrderVehicleChassis, DealerOrderVehicle.order_vehicle_id == DealerOrderVehicleChassis.order_vehicle_id)
                .where(DealerOrderVehicle.order_vehicle_id.in_(ov_ids), DealerOrderVehicleChassis.chassis_no.isnot(None))
                .order_by(DealerOrderVehicleChassis.seq_no)
            )).all()
            for o_id, c_no in c_rows:
                if c_no and c_no.strip():
                    chassis_by_order.setdefault(o_id, []).append(c_no.strip())

    orders = []
    for order, client_name, dealer_name in rows:
        order.client_name = client_name
        order.dealer_name = dealer_name
        c_list = chassis_by_order.get(order.order_id, [])
        order.chassis_nos = c_list
        order.total_qty = qty_by_order.get(order.order_id, 0)
        order.allocated_qty = len(c_list)
        orders.append(order)
    return orders


async def get_order_detail_for_staff(db: AsyncSession, order_id: uuid.UUID) -> DealerOrder:
    order = await db.get(DealerOrder, order_id)
    if not order:
        raise HTTPException(status_code=404, detail="Order not found")
    return await _hydrate_order(db, order)


async def confirm_order(db: AsyncSession, admin_id: uuid.UUID, order_id: uuid.UUID) -> DealerOrder:
    order = await db.get(DealerOrder, order_id)
    if not order:
        raise HTTPException(status_code=404, detail="Order not found")
    if order.status not in ('SUBMITTED', 'DEALER_EDITED'):
        raise HTTPException(status_code=400, detail="Only newly submitted or dealer-edited orders can be confirmed")

    order.status = 'PENDING_DEALER_ACK'
    db.add(DealerOrderActivityLog(
        id=uuid.uuid4(), order_id=order.order_id, action='SALES_CONFIRMED',
        performed_by_dealer=False, performed_by_user_id=admin_id,
    ))
    await db.commit()
    await db.refresh(order)
    await notifications_service.notify_order_confirmed_by_sales(db, order, admin_id=admin_id)
    return await _hydrate_order(db, order)

async def create_dealer_request(db: AsyncSession, dealer_id: str, request_type: str, details: str, photo_url: str = None, photo_urls: list = None) -> DealerRequest:
    new_request = DealerRequest(
        request_id=uuid.uuid4(),
        dealer_id=dealer_id,
        request_type=request_type,
        details=details,
        photo_url=photo_url,
        photo_urls=photo_urls or [],
        status='PENDING'
    )
    db.add(new_request)
    await db.commit()
    await db.refresh(new_request)
    return new_request

async def list_dealer_requests(db: AsyncSession, dealer_id: str) -> list[DealerRequest]:
    result = await db.execute(
        select(DealerRequest)
        .where(DealerRequest.dealer_id == dealer_id)
        .order_by(DealerRequest.created_at.desc())
    )
    return list(result.scalars().all())


async def get_dealer_request(db: AsyncSession, dealer_id: str, request_id: uuid.UUID) -> DealerRequest:
    request = await db.get(DealerRequest, request_id)
    if not request or request.dealer_id != dealer_id:
        raise HTTPException(status_code=404, detail="Request not found")
    return request


async def get_dealer_chassis(
    db: AsyncSession,
    dealer_id: str,
    include_spares: bool = False,
    client_id: str | None = None,
    yard_only: bool = False,
) -> schemas.DealerChassisListResponse:
    from sqlalchemy import or_
    
    dealer = await db.get(CustomerMaster, dealer_id)

    allocated_chassis_subq = (
        select(DealerOrderVehicleChassis.chassis_no)
        .join(DealerOrderVehicle, DealerOrderVehicle.order_vehicle_id == DealerOrderVehicleChassis.order_vehicle_id)
        .join(DealerOrder, DealerOrder.order_id == DealerOrderVehicle.order_id)
        .where(DealerOrder.dealer_id == dealer_id)
        .where(DealerOrderVehicleChassis.chassis_no.is_not(None))
    )

    base_condition = ChassisMaster.dealer_id == dealer_id
    if client_id:
        base_condition = base_condition & ((ChassisMaster.client_id == client_id) | (ChassisMaster.client_id.is_(None)))

    where_clause = or_(
        base_condition,
        ChassisMaster.chassis_no.in_(allocated_chassis_subq)
    )

    if include_spares:
        where_clause = or_(
            where_clause,
            (ChassisMaster.dealer_id.is_(None)) & ((ChassisMaster.tracking_status == 'CHASSIS_IN') | (ChassisMaster.tracking_status.is_(None)))
        )

    if yard_only:
        where_clause = where_clause & (
            ChassisMaster.arrival_date.isnot(None)
            & (ChassisMaster.current_status != 'HANDED_TO_PRODUCTION')
            & (ChassisMaster.tracking_status.notin_(['WIP', 'READY_FOR_DISPATCH', 'DISPATCHED']))
        )

    stmt = (
        select(
            ChassisMaster,
            VehicleModelMaster.model_number.label('model_name'),
            ClientMaster.name.label('client_name')
        )
        .outerjoin(VehicleModelMaster, ChassisMaster.model_id == VehicleModelMaster.model_id)
        .outerjoin(ClientMaster, ChassisMaster.client_id == ClientMaster.client_id)
        .where(where_clause)
        .order_by(ChassisMaster.arrival_date.desc(), ChassisMaster.created_at.desc())
    )
    rows = (await db.execute(stmt)).all()
    
    # Map active orders for each chassis
    chassis_nos = [chassis.chassis_no.strip() for chassis, _, _ in rows if chassis.chassis_no]
    chassis_order_map: dict[str, dict] = {}
    if chassis_nos:
        order_chassis_stmt = (
            select(
                DealerOrderVehicleChassis.chassis_no,
                DealerOrder.order_id,
                DealerOrder.order_no,
                DealerOrder.status.label('order_status'),
            )
            .join(DealerOrderVehicle, DealerOrderVehicle.order_vehicle_id == DealerOrderVehicleChassis.order_vehicle_id)
            .join(DealerOrder, DealerOrder.order_id == DealerOrderVehicle.order_id)
            .where(DealerOrderVehicleChassis.chassis_no.in_(chassis_nos))
            .where(DealerOrder.status != 'CANCELLED')
        )
        for r in (await db.execute(order_chassis_stmt)).all():
            c_no_clean = r.chassis_no.strip() if r.chassis_no else ''
            chassis_order_map[c_no_clean] = {
                'order_id': r.order_id,
                'order_no': r.order_no,
                'order_status': r.order_status,
            }

        # Fallback from ChassisMaster.dealer_order_vehicle_id if not in DOVC
        for chassis, _, _ in rows:
            c_no_clean = chassis.chassis_no.strip() if chassis.chassis_no else ''
            if c_no_clean not in chassis_order_map and chassis.dealer_order_vehicle_id:
                ov_stmt = (
                    select(DealerOrder.order_id, DealerOrder.order_no, DealerOrder.status)
                    .join(DealerOrderVehicle, DealerOrderVehicle.order_id == DealerOrder.order_id)
                    .where(DealerOrderVehicle.order_vehicle_id == chassis.dealer_order_vehicle_id)
                    .where(DealerOrder.status != 'CANCELLED')
                )
                ov_row = (await db.execute(ov_stmt)).first()
                if ov_row:
                    chassis_order_map[c_no_clean] = {
                        'order_id': ov_row[0],
                        'order_no': ov_row[1],
                        'order_status': ov_row[2],
                    }

    # ── Fetch manufacturing defects from thermal checksheets ──────────────
    chassis_ids = [chassis.chassis_id for chassis, _, _ in rows]
    mfg_defects_map: dict = {}  # chassis_id -> list of defect items
    if chassis_ids:
        # Get all checksheet IDs for these chassis
        cs_stmt = select(
            DispatchThermalChecksheet.checksheet_id,
            DispatchThermalChecksheet.chassis_id
        ).where(DispatchThermalChecksheet.chassis_id.in_(chassis_ids))
        cs_rows = (await db.execute(cs_stmt)).all()
        checksheet_id_map = {r.checksheet_id: r.chassis_id for r in cs_rows}

        if checksheet_id_map:
            # Get all manufacturing defect items for these checksheets
            items_stmt = select(DispatchThermalChecksheetItem).where(
                DispatchThermalChecksheetItem.checksheet_id.in_(list(checksheet_id_map.keys())),
                DispatchThermalChecksheetItem.is_ok == False,
                DispatchThermalChecksheetItem.fault_type == 'MANUFACTURING'
            )
            defect_items = (await db.execute(items_stmt)).scalars().all()
            for di in defect_items:
                cid = checksheet_id_map.get(di.checksheet_id)
                if cid:
                    mfg_defects_map.setdefault(cid, []).append(
                        schemas.ManufacturingDefectItem(
                            item_no=di.item_no,
                            item_text=di.item_text or '',
                            authority_name=di.authority_name,
                            remarks=di.remarks,
                        )
                    )

    items = []
    chassis_in_count = 0
    wip_count = 0
    dispatched_count = 0
    
    for chassis, model_name, client_name in rows:
        status = chassis.tracking_status or 'CHASSIS_IN'
        if status == 'CHASSIS_IN':
            chassis_in_count += 1
        elif status == 'WIP':
            wip_count += 1
        elif status == 'DISPATCHED':
            dispatched_count += 1
            
        c_no_clean = chassis.chassis_no.strip() if chassis.chassis_no else ''
        order_info = chassis_order_map.get(c_no_clean, {})
        has_active_order = bool(order_info.get('order_no'))

        items.append(schemas.DealerChassisItem(
            chassis_id=chassis.chassis_id,
            chassis_no=chassis.chassis_no,
            dealer_id=chassis.dealer_id,
            client_name=client_name,
            model_id=chassis.model_id,
            model_name=model_name,
            fuel_type=chassis.fuel_type,
            voltage=chassis.voltage,
            arrival_date=chassis.arrival_date,
            da_lite_status=chassis.da_lite_status,
            da_lite_remarks=chassis.da_lite_remarks,
            da_lite_photo_url=chassis.da_lite_photo_url,
            da_lite_checked_at=chassis.da_lite_checked_at,
            current_status=chassis.current_status,
            tracking_status=status,
            dispatch_photo_url=chassis.dispatch_photo_url,
            dispatched_at=chassis.dispatched_at,
            created_at=chassis.created_at,
            order_id=order_info.get('order_id'),
            order_no=order_info.get('order_no'),
            order_status=order_info.get('order_status'),
            is_allocated=has_active_order,
            manufacturing_defects=mfg_defects_map.get(chassis.chassis_id, []),
        ))
        
    return schemas.DealerChassisListResponse(
        total=len(items),
        chassis_in_count=chassis_in_count,
        wip_count=wip_count,
        dispatched_count=dispatched_count,
        items=items,
        allow_chassis_tracking=dealer.allow_chassis_tracking,
    )

async def final_confirm_order(db: AsyncSession, admin_id: uuid.UUID, order_id: uuid.UUID) -> DealerOrder:
    order = await db.get(DealerOrder, order_id)
    if not order:
        raise HTTPException(status_code=404, detail="Order not found")
    if order.status != 'PENDING_FINAL_CONFIRMATION':
        raise HTTPException(status_code=400, detail="Order is not awaiting final confirmation")

    order.status = 'CONFIRMED'
    order.confirmed_at = datetime.utcnow()
    db.add(DealerOrderActivityLog(
        id=uuid.uuid4(), order_id=order.order_id, action='SALES_FINAL_CONFIRMED',
        performed_by_dealer=False, performed_by_user_id=admin_id,
    ))
    await db.commit()
    await db.refresh(order)
    await notifications_service.notify_order_final_confirmed(db, order)
    return await _hydrate_order(db, order)

async def allocate_order_chassis(
    db: AsyncSession,
    user_id: uuid.UUID | None,
    order_id: uuid.UUID,
    allocations: dict,
    is_dealer: bool = False,
    dealer_id: str | None = None,
) -> DealerOrder:
    order = await db.get(DealerOrder, order_id)
    if not order:
        raise HTTPException(status_code=404, detail="Order not found")

    if is_dealer:
        if order.dealer_id != dealer_id:
            raise HTTPException(status_code=403, detail="Not authorized to allocate chassis for this order")
        if order.status != 'CONFIRMED':
            raise HTTPException(status_code=400, detail="Chassis can only be allocated to confirmed orders")

    from app.models.yard_models import ChassisMaster
    from sqlalchemy import update

    # 1. Collect all non-empty chassis numbers
    all_alloc_chassis_nos = []
    for ov_id_str, chassis_list in allocations.items():
        for ch_no in chassis_list:
            if ch_no and ch_no.strip():
                all_alloc_chassis_nos.append(ch_no.strip())

    # Check for duplicate allocations within the current submission
    if len(all_alloc_chassis_nos) != len(set(all_alloc_chassis_nos)):
        raise HTTPException(status_code=400, detail="The same chassis number cannot be allocated multiple times")

    # Check if any requested chassis is already allocated to another active order
    if all_alloc_chassis_nos:
        conflict_stmt = (
            select(DealerOrderVehicleChassis.chassis_no, DealerOrder.order_no)
            .join(DealerOrderVehicle, DealerOrderVehicle.order_vehicle_id == DealerOrderVehicleChassis.order_vehicle_id)
            .join(DealerOrder, DealerOrder.order_id == DealerOrderVehicle.order_id)
            .where(
                DealerOrderVehicleChassis.chassis_no.in_(all_alloc_chassis_nos),
                DealerOrder.order_id != order.order_id,
                DealerOrder.status != 'CANCELLED'
            )
        )
        conflict = (await db.execute(conflict_stmt)).first()
        if conflict:
            raise HTTPException(
                status_code=400,
                detail=f"Chassis {conflict[0]} is already allocated to active order {conflict[1]}."
            )

    # Load all targeted ChassisMaster records
    chassis_records: dict[str, ChassisMaster] = {}
    if all_alloc_chassis_nos:
        ch_rows = (await db.execute(
            select(ChassisMaster).where(ChassisMaster.chassis_no.in_(all_alloc_chassis_nos))
        )).scalars().all()
        for ch in ch_rows:
            if ch.chassis_no:
                chassis_records[ch.chassis_no.strip()] = ch

    # Validate each allocation against vehicle line and yard status rules
    for ov_id_str, chassis_list in allocations.items():
        ov_id = uuid.UUID(ov_id_str)
        ov = await db.get(DealerOrderVehicle, ov_id)
        if not ov or ov.order_id != order.order_id:
            raise HTTPException(status_code=400, detail=f"Invalid vehicle configuration {ov_id_str}")

        valid_count = sum(1 for c in chassis_list if c and c.strip())
        if valid_count > ov.qty:
            raise HTTPException(status_code=400, detail=f"Cannot allocate more chassis ({valid_count}) than vehicle quantity ({ov.qty})")

        for ch_no in chassis_list:
            if not ch_no or not ch_no.strip():
                continue
            clean_no = ch_no.strip()
            ch = chassis_records.get(clean_no)
            if not ch:
                raise HTTPException(status_code=400, detail=f"Chassis {clean_no} not found in inventory")

            # Must be in yard (arrival_date is set)
            if not ch.arrival_date:
                raise HTTPException(
                    status_code=400,
                    detail=f"Chassis {clean_no} has not arrived in the yard yet. Only chassis currently in the yard can be allocated."
                )

            # Must NOT be handed to production
            if ch.current_status == 'HANDED_TO_PRODUCTION':
                raise HTTPException(
                    status_code=400,
                    detail=f"Chassis {clean_no} has already been handed over to production and cannot be allocated."
                )

            # Must NOT be in production, WIP, ready for dispatch, or dispatched
            if ch.tracking_status in ('WIP', 'READY_FOR_DISPATCH', 'DISPATCHED'):
                raise HTTPException(
                    status_code=400,
                    detail=f"Chassis {clean_no} is currently in production or dispatched (status: {ch.tracking_status}) and cannot be allocated. Only chassis currently available in the yard can be selected."
                )

            # Dealer check: if allocated by dealer, must belong to this dealer or be a spare (dealer_id is null)
            if is_dealer and ch.dealer_id and ch.dealer_id != order.dealer_id:
                raise HTTPException(
                    status_code=400,
                    detail=f"Chassis {clean_no} belongs to another dealer."
                )

            # Model check: must match vehicle model
            if ch.model_id and ov.model_id and ch.model_id != ov.model_id:
                raise HTTPException(
                    status_code=400,
                    detail=f"Chassis {clean_no} model does not match the vehicle model specification."
                )

    # All validations passed! Perform the allocations:
    for ov_id_str, chassis_list in allocations.items():
        ov_id = uuid.UUID(ov_id_str)

        # 1. Clear old ChassisMaster linkages
        old_chassis_nos = (await db.execute(select(DealerOrderVehicleChassis.chassis_no).where(DealerOrderVehicleChassis.order_vehicle_id == ov_id))).scalars().all()
        if old_chassis_nos:
            await db.execute(update(ChassisMaster).where(ChassisMaster.chassis_no.in_(old_chassis_nos)).values(dealer_order_vehicle_id=None))

        # 2. Delete from cross-reference table
        await db.execute(DealerOrderVehicleChassis.__table__.delete().where(DealerOrderVehicleChassis.order_vehicle_id == ov_id))

        valid_chassis_nos = []
        for i, chassis_no in enumerate(chassis_list):
            if not chassis_no or not chassis_no.strip():
                continue
            clean_no = chassis_no.strip()
            valid_chassis_nos.append(clean_no)
            db.add(DealerOrderVehicleChassis(
                id=uuid.uuid4(),
                order_vehicle_id=ov_id,
                seq_no=i+1,
                chassis_no=clean_no
            ))

        # 3. Set new linkages on ChassisMaster
        if valid_chassis_nos:
            await db.execute(update(ChassisMaster).where(ChassisMaster.chassis_no.in_(valid_chassis_nos)).values(dealer_order_vehicle_id=ov_id))

    db.add(DealerOrderActivityLog(
        id=uuid.uuid4(),
        order_id=order.order_id,
        action='CHASSIS_ALLOCATED',
        performed_by_dealer=is_dealer,
        performed_by_user_id=user_id if not is_dealer else None,
        notes=f"Allocated {len(all_alloc_chassis_nos)} chassis by {'dealer' if is_dealer else 'sales'}"
    ))
    await db.commit()
    await db.refresh(order)
    return await _hydrate_order(db, order)

# ── Templates ──

async def _verify_model_body_segment(db: AsyncSession, model_id: uuid.UUID, body_type_id: uuid.UUID, segment_id: uuid.UUID):
    m = await db.get(VehicleModelMaster, model_id)
    b = await db.get(BodyTypeMaster, body_type_id)
    s = await db.get(CustomerSegmentMaster, segment_id)
    if not m or not b or not s:
        raise HTTPException(status_code=400, detail="Invalid model, body type, or segment")


async def list_vehicle_templates(db: AsyncSession, dealer_id: str):
    stmt = (
        select(DealerVehicleTemplate)
        .where(DealerVehicleTemplate.dealer_id == dealer_id)
        .order_by(DealerVehicleTemplate.created_at.desc())
    )
    rows = (await db.execute(stmt)).scalars().all()
    templates = []
    for row in rows:
        feat_stmt = select(DealerVehicleTemplateFeature).where(DealerVehicleTemplateFeature.template_id == row.template_id)
        features = (await db.execute(feat_stmt)).scalars().all()
        row.features = features
        templates.append(row)
    return templates

async def create_vehicle_template(db: AsyncSession, dealer_id: str, payload: schemas.VehicleTemplateCreate) -> schemas.VehicleTemplateOut:
    tpl_id = uuid.uuid4()
    # verify model/body/segment
    await _verify_model_body_segment(db, payload.model_id, payload.body_type_id, payload.segment_id)

    row = DealerVehicleTemplate(
        template_id=tpl_id,
        dealer_id=dealer_id,
        name=payload.name,
        model_id=payload.model_id,
        body_type_id=payload.body_type_id,
        segment_id=payload.segment_id
    )
    db.add(row)

    features = []
    for f in payload.features:
        f_row = DealerVehicleTemplateFeature(
            id=uuid.uuid4(),
            template_id=tpl_id,
            feature_id=f.feature_id,
            feature_option_id=f.feature_option_id,
            bool_value=f.bool_value,
            text_value=f.text_value,
            image_url=f.image_url
        )
        db.add(f_row)
        features.append(f_row)

    await db.commit()
    await db.refresh(row)
    row.features = features
    return row


async def update_vehicle_template(db: AsyncSession, dealer_id: str, template_id: uuid.UUID, payload: schemas.VehicleTemplateCreate) -> schemas.VehicleTemplateOut:
    # check exists
    tpl = await db.scalar(select(DealerVehicleTemplate).where(
        DealerVehicleTemplate.template_id == template_id,
        DealerVehicleTemplate.dealer_id == dealer_id
    ))
    if not tpl:
        raise HTTPException(status_code=404, detail="Template not found")

    await _verify_model_body_segment(db, payload.model_id, payload.body_type_id, payload.segment_id)

    tpl.name = payload.name
    tpl.model_id = payload.model_id
    tpl.body_type_id = payload.body_type_id
    tpl.segment_id = payload.segment_id

    # delete old features
    await db.execute(delete(DealerVehicleTemplateFeature).where(
        DealerVehicleTemplateFeature.template_id == template_id
    ))

    # add new features
    features = []
    for f in payload.features:
        f_row = DealerVehicleTemplateFeature(
            id=uuid.uuid4(),
            template_id=template_id,
            feature_id=f.feature_id,
            feature_option_id=f.feature_option_id,
            bool_value=f.bool_value,
            text_value=f.text_value,
            image_url=f.image_url
        )
        db.add(f_row)
        features.append(f_row)

    await db.commit()
    await db.refresh(tpl)
    tpl.features = features
    return tpl


async def delete_vehicle_template(db: AsyncSession, dealer_id: str, template_id: uuid.UUID):
    template = await db.get(DealerVehicleTemplate, template_id)
    if not template or template.dealer_id != dealer_id:
        raise HTTPException(status_code=404, detail="Template not found")
    
    await db.execute(DealerVehicleTemplateFeature.__table__.delete().where(DealerVehicleTemplateFeature.template_id == template_id))
    await db.delete(template)
    await db.commit()
    return {"message": "Deleted"}

async def add_order_remark(db, user_id, order_id, remark: str):
    order = await db.get(DealerOrder, order_id)
    if not order:
        raise HTTPException(status_code=404, detail="Order not found")
    
    order.remarks = remark
    
    db.add(DealerOrderActivityLog(
        id=uuid.uuid4(),
        order_id=order.order_id,
        action="REMARK",
        performed_by_dealer=False,
        performed_by_user_id=user_id,
        notes=remark
    ))
    
    await db.commit()
    return await get_order_detail_for_staff(db, order_id)


async def set_billing_by(db, user_id, order_id, billing_by: str):
    """Set billing_by on an order. Locked once the order is CONFIRMED."""
    if billing_by not in ('DEALER', 'CLIENT'):
        raise HTTPException(status_code=400, detail="billing_by must be 'DEALER' or 'CLIENT'")
    order = await db.get(DealerOrder, order_id)
    if not order:
        raise HTTPException(status_code=404, detail="Order not found")
    if order.status == 'CONFIRMED':
        raise HTTPException(status_code=400, detail="Billing By cannot be changed after the order is confirmed")
    order.billing_by = billing_by
    db.add(DealerOrderActivityLog(
        id=uuid.uuid4(),
        order_id=order.order_id,
        action="BILLING_BY_SET",
        performed_by_dealer=False,
        performed_by_user_id=user_id,
        notes=f"Billing by set to {billing_by}"
    ))
    await db.commit()
    return await get_order_detail_for_staff(db, order_id)


async def get_dealer_dashboard(db: AsyncSession, dealer_id: str, start: Optional[object] = None, end: Optional[object] = None) -> dict:
    from app.models.sales_models import ClientMaster, DealerClientMap
    from app.models.yard_models import ChassisMaster, VehicleModelMaster
    from datetime import date as d_date, time as d_time

    end_d = end or d_date.today()
    start_d = start or (end_d - timedelta(days=30))
    if start_d > end_d:
        raise HTTPException(status_code=400, detail="Start date must be on or before end date")

    period_from = datetime.combine(start_d, d_time.min, tzinfo=timezone.utc)
    period_to = datetime.combine(end_d, d_time.max, tzinfo=timezone.utc)

    # 1. Orders
    orders_res = await db.execute(
        select(DealerOrder, ClientMaster.name.label('client_name'))
        .join(ClientMaster, DealerOrder.client_id == ClientMaster.client_id)
        .where(DealerOrder.dealer_id == dealer_id)
        .order_by(DealerOrder.created_at.desc())
    )
    order_rows = orders_res.all()
    
    total_orders = len(order_rows)
    confirmed_orders = sum(1 for o, _ in order_rows if o.status == 'CONFIRMED')
    awaiting_ack = sum(1 for o, _ in order_rows if o.status == 'PENDING_DEALER_ACK')
    submitted_orders = sum(1 for o, _ in order_rows if o.status == 'SUBMITTED')

    # 2. Chassis KPIs for this dealer
    in_yard = (await db.execute(
        select(func.count()).select_from(ChassisMaster)
        .where(ChassisMaster.dealer_id == dealer_id, ChassisMaster.tracking_status == 'CHASSIS_IN')
    )).scalar() or 0

    in_production = (await db.execute(
        select(func.count()).select_from(ChassisMaster)
        .where(ChassisMaster.dealer_id == dealer_id, ChassisMaster.tracking_status == 'WIP')
    )).scalar() or 0

    ready_for_dispatch = (await db.execute(
        select(func.count()).select_from(ChassisMaster)
        .where(ChassisMaster.dealer_id == dealer_id, ChassisMaster.tracking_status == 'READY_FOR_DISPATCH')
    )).scalar() or 0

    arrived_in_period = (await db.execute(
        select(func.count()).select_from(ChassisMaster)
        .where(ChassisMaster.dealer_id == dealer_id, ChassisMaster.arrival_date.between(start_d, end_d))
    )).scalar() or 0

    dispatched_in_period = (await db.execute(
        select(func.count()).select_from(ChassisMaster)
        .where(ChassisMaster.dealer_id == dealer_id, ChassisMaster.dispatched_at.between(period_from, period_to))
    )).scalar() or 0

    total_clients = (await db.execute(
        select(func.count()).select_from(DealerClientMap)
        .where(DealerClientMap.dealer_id == dealer_id)
    )).scalar() or 0

    kpis = {
        'total_orders': total_orders,
        'confirmed_orders': confirmed_orders,
        'awaiting_ack': awaiting_ack,
        'submitted_orders': submitted_orders,
        'in_yard': in_yard,
        'in_production': in_production,
        'ready_for_dispatch': ready_for_dispatch,
        'arrived_in_period': arrived_in_period,
        'dispatched_in_period': dispatched_in_period,
        'total_clients': total_clients,
    }

    # 3. Model mix for dealer's chassis
    mix_rows = (await db.execute(
        select(func.coalesce(VehicleModelMaster.model_number, 'Model not set').label('model'), func.count().label('n'))
        .select_from(ChassisMaster)
        .outerjoin(VehicleModelMaster, VehicleModelMaster.model_id == ChassisMaster.model_id)
        .where(ChassisMaster.dealer_id == dealer_id)
        .group_by('model')
        .order_by(func.count().desc())
    )).all()
    mix_total = sum(r.n for r in mix_rows)
    model_mix = {
        'total': mix_total,
        'items': [{'model': r.model, 'count': r.n, 'pct': round((r.n / mix_total * 100), 1) if mix_total else 0} for r in mix_rows]
    }

    # 4. Recent orders with vehicle / chassis summaries
    recent_orders = []
    top_orders = order_rows[:6]
    if top_orders:
        order_ids = [o.order_id for o, _ in top_orders]
        vehicles_res = await db.execute(
            select(DealerOrderVehicle).where(DealerOrderVehicle.order_id.in_(order_ids))
        )
        all_vehs = vehicles_res.scalars().all()
        veh_ids = [v.order_vehicle_id for v in all_vehs]
        
        chassis_by_order = {}
        if veh_ids:
            c_rows = (await db.execute(
                select(DealerOrderVehicle.order_id, DealerOrderVehicleChassis.chassis_no)
                .join(DealerOrderVehicleChassis, DealerOrderVehicle.order_vehicle_id == DealerOrderVehicleChassis.order_vehicle_id)
                .where(DealerOrderVehicle.order_vehicle_id.in_(veh_ids), DealerOrderVehicleChassis.chassis_no.isnot(None))
                .order_by(DealerOrderVehicleChassis.seq_no)
            )).all()
            for o_id, c_no in c_rows:
                if c_no and c_no.strip():
                    chassis_by_order.setdefault(o_id, []).append(c_no.strip())

        vehs_by_order = {}
        for v in all_vehs:
            vehs_by_order.setdefault(v.order_id, []).append(v)

        for o, client_name in top_orders:
            o_vehs = vehs_by_order.get(o.order_id, [])
            total_qty = sum(v.qty for v in o_vehs)
            c_list = chassis_by_order.get(o.order_id, [])
            allocated_qty = len(c_list)
            recent_orders.append({
                'order_id': str(o.order_id),
                'order_no': o.order_no,
                'chassis_nos': c_list,
                'client_name': client_name or '—',
                'status': o.status,
                'total_qty': total_qty,
                'allocated_qty': allocated_qty,
                'submitted_at': o.submitted_at.isoformat() if o.submitted_at else None,
            })

    # 5. Dealer Profile / Name
    dealer = await db.get(CustomerMaster, dealer_id)
    dealer_name = dealer.name if dealer else "Dealer"

    return {
        'dealer_name': dealer_name,
        'kpis': kpis,
        'model_mix': model_mix,
        'recent_orders': recent_orders,
    }