package com.genpt.nsight.v3.service.impl;

import com.datastax.driver.core.*;
import com.genpt.nsight.NSightCache;
import com.genpt.nsight.config.DeliveryTypes;
import com.genpt.nsight.config.NSightProperties;
import com.genpt.nsight.v1.model.*;
import com.genpt.nsight.v2.SplunkLogger;
import com.genpt.nsight.v3.service.AvailabilityServiceContextV3;
import com.genpt.nsight.v3.service.Site2SiteDeliveryEstimateServiceV3;
import com.google.common.util.concurrent.Futures;
import com.google.common.util.concurrent.ListenableFuture;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.PostConstruct;
import java.util.*;
import java.util.stream.Collectors;

@Service("Site2SiteDeliveryEstimateServiceV3")
public class Site2SiteDeliveryEstimateServiceV3Impl implements Site2SiteDeliveryEstimateServiceV3 {
    private String[] dayOfWeekColNames = new String[]{"monday_cutoff_time", "tuesday_cutoff_time", "wednesday_cutoff_time", "thursday_cutoff_time", "friday_cutoff_time", "saturday_cutoff_time", "sunday_cutoff_time"};
    private String[] leadTimesDayOfWeekColNames = new String[]{"Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"};

    private static final Logger LOGGER = LoggerFactory.getLogger(Site2SiteDeliveryEstimateServiceV3Impl.class);

    @Autowired
    DeliveryTypes deliveryTypes;

    @Autowired
    private Session session;

    @Autowired
    NSightCache nSightCache;

    @Autowired
    NSightProperties nSightProperties;

    PreparedStatement deliveryEstimatePS;

    @PostConstruct
    public void init() {
        deliveryEstimatePS = session.prepare("SELECT ship_to_site_identifier, delivery_service_type, ship_from_site_identifier, monday_cutoff_time, tuesday_cutoff_time, wednesday_cutoff_time, thursday_cutoff_time, friday_cutoff_time, saturday_cutoff_time, sunday_cutoff_time, service_duration_designator, service_duration_max_value, service_duration_min_value FROM site_to_site_delivery_matrix where ship_to_site_identifier = ? and delivery_service_type = ? and ship_from_site_identifier in ?;");
        deliveryEstimatePS.setConsistencyLevel(ConsistencyLevel.LOCAL_QUORUM);
    }



    @Override
    public ResultSetFuture getDeliveryEstimates(UUID toSiteId, String deliveryType, List<UUID> fromSiteList) {
        BoundStatement boundStatement = deliveryEstimatePS.bind(toSiteId,deliveryType,fromSiteList);
        return session.executeAsync(boundStatement);
    }

    @Override
    public void setDeliveryEstimateFutures(AvailabilityServiceContextV3 availabilityServiceContextV3) {

        //if requested only primary DC , just add that
        if (availabilityServiceContextV3.getRequest().getDetailRequest().getInventoryRelationshipsRequested().isAllOtherDCs())
            availabilityServiceContextV3.getFromSiteIds().addAll(nSightCache.getDcSet());
        else if (availabilityServiceContextV3.getRequest().getDetailRequest().getInventoryRelationshipsRequested().isServingDC())
            availabilityServiceContextV3.getFromSiteIds().add(availabilityServiceContextV3.getPrimaryDC().getSiteId());

        if (availabilityServiceContextV3.getRequest().getDetailRequest().getInventoryRelationshipsRequested().isAllSuppliers() )
            availabilityServiceContextV3.getFromSiteIds().addAll(nSightCache.getSupplierIdSet());

        List<UUID> fromSiteIdList = new ArrayList<>(availabilityServiceContextV3.getFromSiteIds().size());
        availabilityServiceContextV3.getFromSiteIds().forEach(siteId -> fromSiteIdList.add(UUID.fromString(siteId)));

        UUID fromSiteId = UUID.fromString(availabilityServiceContextV3.getRequestingSite().getSiteIdentifier());
        //ad to map
        String requestorKey= availabilityServiceContextV3.getRequest().getHeaderRequest().getRequestor().getRequestorIdentifier().getType()+"|"+ availabilityServiceContextV3.getRequest().getHeaderRequest().getRequestor().getRequestorIdentifier().getValue();
        availabilityServiceContextV3.getReverseShipToSitesMap().put(availabilityServiceContextV3.getRequestingSite().getSiteIdentifier(),requestorKey);

        List<ResultSetFuture> deliveryTimeFutureResultSetList = new ArrayList<>();

        //if there are entries in additional ship to sites, add those
        // it should at least have the requesting store
        for (Map.Entry<String,String> entry : availabilityServiceContextV3.getShipToSitesMap().entrySet()){
            String aliasName = entry.getValue();
            String aliasValue = entry.getKey();
            String fullAliasKey= aliasName + "|" + aliasValue;
            String shipToSiteID = nSightCache.getAliasToSiteIdMap().get(fullAliasKey);
            if (shipToSiteID!=null && shipToSiteID.trim().length()>0) {
                //need the following on the response path
                //add to the map
                availabilityServiceContextV3.getReverseShipToSitesMap().put(shipToSiteID, fullAliasKey);
                deliveryTypes.getDeliveryTypes().stream().map(deliveryType -> {
                    return getDeliveryEstimates(UUID.fromString(shipToSiteID), deliveryType, fromSiteIdList);
                }).forEach(deliveryTimeFutureResultSetList::add);
            }
        }

        if (availabilityServiceContextV3.getDeliveryEstimateFutures()==null){
            availabilityServiceContextV3.setDeliveryEstimateFutures(new ArrayList<>());
            availabilityServiceContextV3.getDeliveryEstimateFutures().addAll(Futures.inCompletionOrder(deliveryTimeFutureResultSetList));
        }else
            availabilityServiceContextV3.getDeliveryEstimateFutures().addAll(Futures.inCompletionOrder(deliveryTimeFutureResultSetList));

        //availabilityServiceContextV2.setDeliveryEstimateFutures(Futures.inCompletionOrder(deliveryTimeFutureResultSetList));
    }

    @Override
    public void setDeliveryBySite(AvailabilityServiceContextV3 availabilityServiceContextV3) {
        SplunkLogger splunkLogger = new SplunkLogger(availabilityServiceContextV3.getRequest().getHeaderRequest());

        Map<String, List<RawDelivery>> quickestDeliveryBySite = new HashMap<>();
        Map<String, List<RawDelivery>> freeDeliveryBySite = new HashMap<>();
        Map<String, List<RawDelivery>> paidDeliveryBySite = new HashMap<>();

        if (Boolean.TRUE.equals(availabilityServiceContextV3.getRequest().getActionRequest().getAction().getNeedQuickestDelivery())){
            setOneDeliveryBySite(availabilityServiceContextV3);
        }else {
            //create a set where we store all the 5 Digit deliveries
            Set<String> fromSitesToZip = new HashSet<>();

            Map<String, List<RawDelivery>> deliveryBySite = availabilityServiceContextV3.getDeliveryEstimateFutures().stream().flatMap(resultSetListenableFuture -> {
                ResultSet resultSet = null;
                List<RawDelivery> rawDeliveryList = new ArrayList<>();
                try {
                    resultSet = resultSetListenableFuture.get();
                    while (!resultSet.isExhausted()) {

                        Row row = resultSet.one();
                        String serviceDurationDesignator = row.getString("service_duration_designator");
                        RawDelivery rawDelivery = createDelivery(serviceDurationDesignator,row, availabilityServiceContextV3, rawDeliveryList,fromSitesToZip);
                        if (Boolean.TRUE.equals(availabilityServiceContextV3.isFreePaid())) {
                            createFreePaidDelivery(serviceDurationDesignator, row, quickestDeliveryBySite, availabilityServiceContextV3, freeDeliveryBySite, paidDeliveryBySite);
                        }
                    }
                } catch (Exception ex) {
                    ex.printStackTrace();
                    LOGGER.error(splunkLogger.toString() + " , Error in setDeliveryBySite, Message :  {}, StackTrace: {} ", ex.getMessage(), ex.getStackTrace());
                }
                return rawDeliveryList.stream();
            }).collect(Collectors.groupingBy(delivery -> delivery.getSiteId()));

                availabilityServiceContextV3.setDeliveryBySite(deliveryBySite);

                if (Boolean.TRUE.equals(availabilityServiceContextV3.isFreePaid())) {
                    availabilityServiceContextV3.setQuickestFreePaidDeliveryBySite(quickestDeliveryBySite);
                    availabilityServiceContextV3.setFreeDeliveryBySite(freeDeliveryBySite);
                    availabilityServiceContextV3.setPaidDeliveryBySite(paidDeliveryBySite);
                }
            }
        }


    public void setOneDeliveryBySite(AvailabilityServiceContextV3 availabilityServiceContextV3) {
        //iterate over all the deliveries to the requesting site
        Map<String, List<RawDelivery>> deliveryBySite = new HashMap<>();
        SplunkLogger splunkLogger = new SplunkLogger(availabilityServiceContextV3.getRequest().getHeaderRequest());

        for (ListenableFuture<ResultSet> resultSetListenableFuture : availabilityServiceContextV3.getDeliveryEstimateFutures()) {

            ResultSet resultSet = null;
            String fromSiteID = null;

            try {
                resultSet = resultSetListenableFuture.get();

                //iterate results
                while (!resultSet.isExhausted()) {
                    Row row = resultSet.one();
                    String serviceDurationDesignator = row.getString("service_duration_designator");

                    //check if mandatory values are present
                    if (serviceDurationDesignator != null) {
                        List<RawDelivery> rawDeliveryList = new ArrayList<>();

                        fromSiteID = row.getUUID("ship_from_site_identifier").toString();
                        float shipFromTZ = 0;//set the timezone for the shipfrom site
                        if (nSightCache.getSiteIdToSiteMap().get(fromSiteID) != null && nSightCache.getSiteIdToSiteMap().get(fromSiteID).getTimeZone() != null && nSightCache.getSiteIdToSiteMap().get(fromSiteID).getTimeZone().trim() != "")
                            try {
                                shipFromTZ = Float.parseFloat(nSightCache.getSiteIdToSiteMap().get(fromSiteID).getTimeZone());
                            }catch(Exception ex){
                                ex.printStackTrace();
                                LOGGER.error(splunkLogger.toString() + " , Error in setOneDeliveryBySite, Message :  {}, StackTrace: {} ", ex.getMessage(), ex.getStackTrace());
                            }

                        if (deliveryBySite.containsKey(fromSiteID)) {// already exists
                            //get the existing rawDelivery
                            List<RawDelivery> existingRawDeliveryList = deliveryBySite.get(fromSiteID);
                            RawDelivery existingRawDelivery = existingRawDeliveryList.get(0);

                            //create the new rawDelivery
                            RawDelivery rawDelivery = new RawDelivery();
                            rawDelivery.setDeliveryType(row.getString("delivery_service_type"));
                            rawDelivery.setSiteId(fromSiteID);

                            //populate lead times
                            if (availabilityServiceContextV3.getLeadTimeBySite() !=null  && availabilityServiceContextV3.getLeadTimeBySite().containsKey(fromSiteID)){
                                List<LeadTimes> copy = availabilityServiceContextV3.getLeadTimeBySite().get(fromSiteID).stream()
                                        .filter(s -> s.getDeliveryServiceType().compareToIgnoreCase(row.getString("delivery_service_type")) == 0)
                                        .collect(Collectors.toList());
                                if (copy!=null & copy.size() > 0)
                                    rawDelivery.setLeadTimes(copy);
                            }

                            List<DeliveryCutOff> deliveryCutOffList = null;
                            for (int i = 0; i < 7; i++) {
                                //check if the time is a null value
                                boolean isNull = row.isNull(dayOfWeekColNames[(availabilityServiceContextV3.getDayOfWeek() + i) % 7]);
                                if (!isNull) {
                                    long deliveryCutOffTime = row.getTime(dayOfWeekColNames[(availabilityServiceContextV3.getDayOfWeek() + i) % 7]);
                                    //incorporate the lead times
                                    String dayOfWeek = leadTimesDayOfWeekColNames[(availabilityServiceContextV3.getDayOfWeek() + i) % 7];
                                    int sumLeadTimes = availabilityServiceContextV3.getLeadTimesForSite(dayOfWeek, fromSiteID, rawDelivery.getDeliveryType());

                                    boolean afterCutOff = false;
                                    if (availabilityServiceContextV3.getStoreOffSetTime() != null && shipFromTZ != 0) {
                                        if (!availabilityServiceContextV3.requestTimeBeforeCutOffTime(deliveryCutOffTime/1000000, shipFromTZ,row.getInt("service_duration_min_value")+sumLeadTimes))//expects millis
                                            afterCutOff=true;
                                    }else{
                                        long now = 1000000 * availabilityServiceContextV3.getTimeOfDay();
                                        if (deliveryCutOffTime < now)
                                            afterCutOff=true;
                                    }

                                    //if we have passed the cutoff time
                                    if (i == 0 ) {
                                        if (afterCutOff)
                                            continue;
                                        else{//before the cutoff time for today
                                            DeliveryCutOff deliveryCutOff = null;
                                            deliveryCutOff = new DeliveryCutOff((availabilityServiceContextV3.getDayOfWeek() + i) % 7, deliveryCutOffTime / 1000000L);
                                            if (deliveryCutOffList == null) {
                                                deliveryCutOffList = new ArrayList<>();
                                            }
                                            deliveryCutOffList.add(deliveryCutOff);
                                            rawDelivery.setDeliveryEstimate(new DeliveryEstimate());
                                            rawDelivery.getDeliveryEstimate().setDurationDesignator(serviceDurationDesignator);
                                            rawDelivery.getDeliveryEstimate().setDurationMinValue(row.getInt("service_duration_min_value") + i);//since i is 0
                                            rawDelivery.getDeliveryEstimate().setDurationMaxValue(row.getInt("service_duration_max_value") + i);
                                            rawDelivery.getDeliveryEstimate().setDurationMinValueWithLeadTime(row.getInt("service_duration_min_value") + i + sumLeadTimes);//since i is 0
                                            rawDelivery.getDeliveryEstimate().setDurationMaxValueWithLeadTime(row.getInt("service_duration_max_value") + i + sumLeadTimes);
                                            break;
                                        }
                                    } else {
                                        DeliveryCutOff deliveryCutOff = null;
                                        deliveryCutOff = new DeliveryCutOff((availabilityServiceContextV3.getDayOfWeek() + i) % 7, deliveryCutOffTime / 1000000L);
                                        if (deliveryCutOffList == null) {
                                            deliveryCutOffList = new ArrayList<>();
                                        }
                                        deliveryCutOffList.add(deliveryCutOff);
                                        rawDelivery.setDeliveryEstimate(new DeliveryEstimate());
                                        rawDelivery.getDeliveryEstimate().setDurationDesignator(serviceDurationDesignator);
                                        rawDelivery.getDeliveryEstimate().setDurationMinValue(row.getInt("service_duration_min_value") + i);
                                        rawDelivery.getDeliveryEstimate().setDurationMaxValue(row.getInt("service_duration_max_value") + i);
                                        rawDelivery.getDeliveryEstimate().setDurationMinValueWithLeadTime(row.getInt("service_duration_min_value") + i + sumLeadTimes);
                                        rawDelivery.getDeliveryEstimate().setDurationMaxValueWithLeadTime(row.getInt("service_duration_max_value") + i + sumLeadTimes);

                                        break;
                                    }
                                }
                            }
                            if (deliveryCutOffList != null) {
                                rawDelivery.setDeliveryCutOff(deliveryCutOffList);
                            }
                            if (rawDelivery.getDeliveryCutOff()!= null && rawDelivery.getDeliveryCutOff().size() > 0)
                                rawDeliveryList.add(rawDelivery);

                            //check which rawDelivery is quicker. if the new rawDelivery has valid values we are good
                            if (rawDelivery.getDeliveryCutOff() != null && rawDelivery.getDeliveryCutOff().size() > 0 && rawDelivery.getDeliveryEstimate() != null) {
                                //first check which has lower durations
                                if (rawDelivery.getDeliveryEstimate().getDurationMinValueWithLeadTime() < existingRawDelivery.getDeliveryEstimate().getDurationMinValueWithLeadTime()) { // new rawDelivery is quicker
                                    //use this rawDelivery.  the old one from the list
                                    existingRawDeliveryList = null;

                                } else if (rawDelivery.getDeliveryEstimate().getDurationMinValueWithLeadTime() == existingRawDelivery.getDeliveryEstimate().getDurationMinValueWithLeadTime()) { // durations are equal
                                    // then check which which is quicker
                                    if (rawDelivery.getDeliveryCutOff().get(0) != null && existingRawDelivery.getDeliveryCutOff().get(0) != null && rawDelivery.getDeliveryCutOff().get(0).getCutOffTime() > existingRawDelivery.getDeliveryCutOff().get(0).getCutOffTime()) {
                                        /// use the new one
                                        existingRawDeliveryList = null;
                                    } else {
                                        //use the existing one
                                        rawDeliveryList = existingRawDeliveryList;
                                    }
                                } else {///new rawDelivery is greater
                                    rawDeliveryList = existingRawDeliveryList;
                                }
                            }else {
                                rawDeliveryList = existingRawDeliveryList;
                            }
                        } else {
                            //found the first rawDelivery
                            RawDelivery rawDelivery = new RawDelivery();
                            rawDelivery.setDeliveryType(row.getString("delivery_service_type"));
                            rawDelivery.setSiteId(fromSiteID);
                            //populate lead times
                            if (availabilityServiceContextV3.getLeadTimeBySite() !=null  && availabilityServiceContextV3.getLeadTimeBySite().containsKey(fromSiteID)){
                                List<LeadTimes> copy = availabilityServiceContextV3.getLeadTimeBySite().get(fromSiteID).stream()
                                        .filter(s -> s.getDeliveryServiceType().compareToIgnoreCase(row.getString("delivery_service_type")) == 0)
                                        .collect(Collectors.toList());
                                if (copy!=null & copy.size() > 0)
                                    rawDelivery.setLeadTimes(copy);
                            }

                            List<DeliveryCutOff> deliveryCutOffList = null;

                            for (int i = 0; i < 7; i++) {
                                //check if the time is a null value
                                boolean isNull = row.isNull(dayOfWeekColNames[(availabilityServiceContextV3.getDayOfWeek() + i) % 7]);
                                if (!isNull) {
                                    long deliveryCutOffTime = row.getTime(dayOfWeekColNames[(availabilityServiceContextV3.getDayOfWeek() + i) % 7]);
                                    //incorporate the lead times
                                    String dayOfWeek = leadTimesDayOfWeekColNames[(availabilityServiceContextV3.getDayOfWeek() + i) % 7];
                                    int sumLeadTimes = availabilityServiceContextV3.getLeadTimesForSite(dayOfWeek, fromSiteID, rawDelivery.getDeliveryType());

                                    boolean afterCutOff = false;
                                    if (availabilityServiceContextV3.getStoreOffSetTime() != null && shipFromTZ != 0) {
                                        if (!availabilityServiceContextV3.requestTimeBeforeCutOffTime(deliveryCutOffTime/1000000, shipFromTZ,row.getInt("service_duration_min_value")+sumLeadTimes))//expects millis
                                            afterCutOff=true;
                                    }else{
                                        long now = 1000000 * availabilityServiceContextV3.getTimeOfDay();
                                        if (deliveryCutOffTime < now)
                                            afterCutOff=true;
                                    }

                                    //if we have passed the cutoff time
                                    if (i == 0 ) {
                                        if (afterCutOff) {
                                            continue;//passed the cutoff time for today
                                        }
                                        else{//found before the cutoff time for today
                                            DeliveryCutOff deliveryCutOff = null;
                                            deliveryCutOff = new DeliveryCutOff((availabilityServiceContextV3.getDayOfWeek() + i) % 7, deliveryCutOffTime / 1000000L);
                                            if (deliveryCutOffList == null) {
                                                deliveryCutOffList = new ArrayList<>();
                                            }
                                            deliveryCutOffList.add(deliveryCutOff);
                                            rawDelivery.setDeliveryEstimate(new DeliveryEstimate());
                                            rawDelivery.getDeliveryEstimate().setDurationDesignator(serviceDurationDesignator);
                                            rawDelivery.getDeliveryEstimate().setDurationMinValue(row.getInt("service_duration_min_value") + i);
                                            rawDelivery.getDeliveryEstimate().setDurationMaxValue(row.getInt("service_duration_max_value") + i);
                                            rawDelivery.getDeliveryEstimate().setDurationMinValueWithLeadTime(row.getInt("service_duration_min_value") + i + sumLeadTimes);
                                            rawDelivery.getDeliveryEstimate().setDurationMaxValueWithLeadTime(row.getInt("service_duration_max_value") + i + sumLeadTimes);

                                            break;
                                        }
                                    } else {
                                        DeliveryCutOff deliveryCutOff = null;
                                        deliveryCutOff = new DeliveryCutOff((availabilityServiceContextV3.getDayOfWeek() + i) % 7, deliveryCutOffTime / 1000000L);
                                        if (deliveryCutOffList == null) {
                                            deliveryCutOffList = new ArrayList<>();
                                        }
                                        deliveryCutOffList.add(deliveryCutOff);
                                        rawDelivery.setDeliveryEstimate(new DeliveryEstimate());
                                        rawDelivery.getDeliveryEstimate().setDurationDesignator(serviceDurationDesignator);
                                        rawDelivery.getDeliveryEstimate().setDurationMinValue(row.getInt("service_duration_min_value") + i);
                                        rawDelivery.getDeliveryEstimate().setDurationMaxValue(row.getInt("service_duration_max_value") + i);
                                        rawDelivery.getDeliveryEstimate().setDurationMinValueWithLeadTime(row.getInt("service_duration_min_value") + i + sumLeadTimes);
                                        rawDelivery.getDeliveryEstimate().setDurationMaxValueWithLeadTime(row.getInt("service_duration_max_value") + i + sumLeadTimes);

                                        break;
                                    }
                                }
                            }//loop cutoffs

                            if (deliveryCutOffList != null && deliveryCutOffList.size() > 0) {
                                rawDelivery.setDeliveryCutOff(deliveryCutOffList);
                            }
                            if (rawDelivery.getDeliveryCutOff()!= null && rawDelivery.getDeliveryCutOff().size() > 0)
                                rawDeliveryList.add(rawDelivery);
                            //finished adding the first found rawDelivery
                        }//end of else which is created the first rawDelivery
                        /// finally add it to the map, if the list is populated
                        if (rawDeliveryList !=null && rawDeliveryList.size() > 0)
                            deliveryBySite.put(fromSiteID, rawDeliveryList);
                    }
                }
            } catch (Exception ex) {
                ex.printStackTrace();
                LOGGER.error(splunkLogger.toString() + " , Error in setOneDeliveryBySite, Message :  {}, StackTrace: {} ", ex.getMessage(), ex.getStackTrace());
            }

        }
        availabilityServiceContextV3.setDeliveryBySite(deliveryBySite);
    }

    void createQuickestDeliveryBySite(String serviceDurationDesignator, AvailabilityServiceContextV3 availabilityServiceContextV3, Row row ){
        SplunkLogger splunkLogger = new SplunkLogger(availabilityServiceContextV3.getRequest().getHeaderRequest());

        if (serviceDurationDesignator != null) {
            String shipFromSiteId = row.getUUID("ship_from_site_identifier").toString();

            float shipFromTZ = 0;//set the timezone for the shipfrom site
            if (nSightCache.getSiteIdToSiteMap().get(shipFromSiteId) != null && nSightCache.getSiteIdToSiteMap().get(shipFromSiteId).getTimeZone() != null && nSightCache.getSiteIdToSiteMap().get(shipFromSiteId).getTimeZone().trim() != "")
                try {
                    shipFromTZ = Float.parseFloat(nSightCache.getSiteIdToSiteMap().get(shipFromSiteId).getTimeZone());
                }catch(Exception ex){
                    ex.printStackTrace();
                    LOGGER.error(splunkLogger.toString() + " , Error in setOneDeliveryBySite, Message :  {}, StackTrace: {} ", ex.getMessage(), ex.getStackTrace());
                }

            if (availabilityServiceContextV3.getSiteWithPartsAvailable().contains(shipFromSiteId)) {
                //going to set the quickest rawDelivery from this site.
                QuickestDelivery quickestDelivery = availabilityServiceContextV3.getQuickestDeliveryBySite().get(shipFromSiteId);
                //include lead times in calculation
                String dayOfWeek = leadTimesDayOfWeekColNames[(availabilityServiceContextV3.getDayOfWeek()) % 7];//get todays laed time if exists
                int sumLeadTimes = availabilityServiceContextV3.getLeadTimesForSite(dayOfWeek, shipFromSiteId, row.getString("delivery_service_type"));

                int durationMinValue = row.getInt("service_duration_min_value");

                if (quickestDelivery == null || quickestDelivery.getDurationMinValueWithLeadTime() >= durationMinValue+sumLeadTimes) {
                    for (int i = 0; i < 7; i++) {
                        //check if the time is a null value
                        boolean isNull = row.isNull(dayOfWeekColNames[(availabilityServiceContextV3.getDayOfWeek() + i) % 7]);

                        if (!isNull) {
                            long deliveryCutOffTime = row.getTime(dayOfWeekColNames[(availabilityServiceContextV3.getDayOfWeek() + i) % 7]);
                            deliveryCutOffTime = deliveryCutOffTime / 1000000L;//convert from nanos to millis

                            //include lead times in calculation
                            dayOfWeek = leadTimesDayOfWeekColNames[(availabilityServiceContextV3.getDayOfWeek() + i) % 7];
                            sumLeadTimes=0;
                            sumLeadTimes = availabilityServiceContextV3.getLeadTimesForSite(dayOfWeek, shipFromSiteId, row.getString("delivery_service_type"));

                            //if we have timezone data avaialble then use that
                            if (availabilityServiceContextV3.getStoreOffSetTime() != null && shipFromTZ != 0) {
                                //have time zone data for calling store and ship from store
                                //if the cutoff time has passed the current requesting time
                                if (i == 0 && !availabilityServiceContextV3.requestTimeBeforeCutOffTime(deliveryCutOffTime, shipFromTZ,row.getInt("service_duration_min_value")+sumLeadTimes)) {
                                    continue;
                                }
                            } else {
                                if (i == 0 && deliveryCutOffTime < availabilityServiceContextV3.getTimeOfDay()) {
                                    continue;
                                }
                            }

//                                        if (quickestDelivery == null || (quickestDelivery.getMinDeliveryPeriod() == (durationMinValue + i) && quickestDelivery.getMaxDeliveryCutOff() < deliveryCutOffTime) || quickestDelivery.getMinDeliveryPeriod() > (durationMinValue + i)) {
                            if (quickestDelivery == null ||
                                    (quickestDelivery.getDurationMinValueWithLeadTime() == (durationMinValue + i +sumLeadTimes) && availabilityServiceContextV3.cutOffTimeAfterMax(deliveryCutOffTime, quickestDelivery.getMaxDeliveryCutOff(), shipFromTZ, shipFromTZ))
                                    || quickestDelivery.getDurationMinValueWithLeadTime() > (durationMinValue + i +sumLeadTimes)) {
                                if (quickestDelivery == null) {
                                    quickestDelivery = new QuickestDelivery();
                                    availabilityServiceContextV3.getQuickestDeliveryBySite().put(shipFromSiteId, quickestDelivery);
                                }

                                quickestDelivery.setMaxDeliveryCutOff(deliveryCutOffTime);
                                quickestDelivery.setMinDeliveryPeriod(durationMinValue + i);
                                quickestDelivery.setDurationMinValueWithLeadTime(durationMinValue + i + sumLeadTimes);
                                quickestDelivery.setDeliveryCutoffDay(i);
                                if (shipFromTZ != 0)//set the timezone
                                    quickestDelivery.setTimeZone(shipFromTZ);

                                break;
                            } else {
                                break;
                            }
                        }
                    }
                }
            }
        }

    }

    @Override
    public void setQuickestDeliveryBySite(AvailabilityServiceContextV3 availabilityServiceContextV3) {
        SplunkLogger splunkLogger = new SplunkLogger(availabilityServiceContextV3.getRequest().getHeaderRequest());

        availabilityServiceContextV3.setQuickestDeliveryBySite(new HashMap<>());
        availabilityServiceContextV3.getDeliveryEstimateFutures().forEach(future -> {
            ResultSet resultSet = null;
            try {
                resultSet = future.get();
                while (!resultSet.isExhausted()) {
                    Row row = resultSet.one();
                    String serviceDurationDesignator = row.getString("service_duration_designator");
                    createQuickestDeliveryBySite(serviceDurationDesignator, availabilityServiceContextV3, row);
                }
            } catch (Exception ex) {
                ex.printStackTrace();
                LOGGER.error(splunkLogger.toString() + " , Error in setQuickestDeliveryBySite, Message :  {}, StackTrace: {} ", ex.getMessage(), ex.getStackTrace());

            }
        });
    }

    public void setFreePaidDeliveryBySite(AvailabilityServiceContextV3 availabilityServiceContextV3) {
        //iterate over all the deliveries to the requesting site
        Map<String, List<RawDelivery>> quickestDeliveryBySite = new HashMap<>();
        Map<String, List<RawDelivery>> freeDeliveryBySite = new HashMap<>();
        Map<String, List<RawDelivery>> paidDeliveryBySite = new HashMap<>();

        SplunkLogger splunkLogger = new SplunkLogger(availabilityServiceContextV3.getRequest().getHeaderRequest());

        for (ListenableFuture<ResultSet> resultSetListenableFuture : availabilityServiceContextV3.getDeliveryEstimateFutures()) {
            ResultSet resultSet = null;
            String fromSiteID = null;

            try {
                resultSet = resultSetListenableFuture.get();
                //iterate results
                while (!resultSet.isExhausted()) {
                    Row row = resultSet.one();
                    String serviceDurationDesignator = row.getString("service_duration_designator");
                    createFreePaidDelivery(serviceDurationDesignator,row,quickestDeliveryBySite, availabilityServiceContextV3,freeDeliveryBySite,paidDeliveryBySite);
                }
            } catch (Exception ex) {
                ex.printStackTrace();
                LOGGER.error(splunkLogger.toString() + " , Error in setOneDeliveryBySite, Message :  {}, StackTrace: {} ", ex.getMessage(), ex.getStackTrace());
            }

        }
        availabilityServiceContextV3.setQuickestFreePaidDeliveryBySite(quickestDeliveryBySite);
        availabilityServiceContextV3.setFreeDeliveryBySite(freeDeliveryBySite);
        availabilityServiceContextV3.setPaidDeliveryBySite(paidDeliveryBySite);
    }

    private void compareAndSetQuickestDeliveryList(AvailabilityServiceContextV3 availabilityServiceContextV3, Row row, List<RawDelivery> existingRawDeliveryList, String deliveryServiceType, String fromSiteID, String serviceDurationDesignator,
                                                     float shipFromTZ, boolean isPaidDelivery, List<RawDelivery> existingFreeRawDeliveryList, List<RawDelivery> existingPaidRawDeliveryList){

        RawDelivery existingRawDelivery = existingRawDeliveryList.get(0);
        RawDelivery existingFreeRawDelivery = existingFreeRawDeliveryList.size() > 0 ? existingFreeRawDeliveryList.get(0) : null;
        RawDelivery existingPaidRawDelivery = existingPaidRawDeliveryList.size() > 0 ? existingPaidRawDeliveryList.get(0): null;

        //create the new rawDelivery
        RawDelivery rawDelivery = new RawDelivery();
        rawDelivery.setDeliveryType(deliveryServiceType);
        rawDelivery.setSiteId(fromSiteID);
        //populate lead times
        if (availabilityServiceContextV3.getLeadTimeBySite() !=null  && availabilityServiceContextV3.getLeadTimeBySite().containsKey(fromSiteID)){
            List<LeadTimes> copy = availabilityServiceContextV3.getLeadTimeBySite().get(fromSiteID).stream()
                    .filter(s -> s.getDeliveryServiceType().compareToIgnoreCase(deliveryServiceType) == 0)
                    .collect(Collectors.toList());
            if (copy!=null & copy.size() > 0)
                rawDelivery.setLeadTimes(copy);
        }

        List<DeliveryCutOff> deliveryCutOffList = null;
        for (int i = 0; i < 7; i++) {
            //check if the time is a null value
            boolean isNull = row.isNull(dayOfWeekColNames[(availabilityServiceContextV3.getDayOfWeek() + i) % 7]);
            if (!isNull) {
                long deliveryCutOffTime = row.getTime(dayOfWeekColNames[(availabilityServiceContextV3.getDayOfWeek() + i) % 7]);
                //include lead times in calculation
                String dayOfWeek = leadTimesDayOfWeekColNames[(availabilityServiceContextV3.getDayOfWeek() + i) % 7];
                int sumLeadTimes = availabilityServiceContextV3.getLeadTimesForSite(dayOfWeek, fromSiteID, deliveryServiceType);

                boolean afterCutOff = false;
                if (availabilityServiceContextV3.getStoreOffSetTime() != null && shipFromTZ != 0) {
                    if (!availabilityServiceContextV3.requestTimeBeforeCutOffTime(deliveryCutOffTime/1000000, shipFromTZ,row.getInt("service_duration_min_value")+sumLeadTimes))//expects millis
                        afterCutOff=true;
                }else{
                    long now = 1000000 * availabilityServiceContextV3.getTimeOfDay();
                    if (deliveryCutOffTime < now)
                        afterCutOff=true;
                }

                //if we have passed the cutoff time
                if (i == 0 ) {
                    if (afterCutOff)
                        continue;
                    else{//before the cutoff time for today
                        DeliveryCutOff deliveryCutOff = null;
                        deliveryCutOff = new DeliveryCutOff((availabilityServiceContextV3.getDayOfWeek() + i) % 7, deliveryCutOffTime / 1000000L);
                        if (deliveryCutOffList == null) {
                            deliveryCutOffList = new ArrayList<>();
                        }
                        deliveryCutOffList.add(deliveryCutOff);
                        rawDelivery.setDeliveryEstimate(new DeliveryEstimate());
                        rawDelivery.getDeliveryEstimate().setDurationDesignator(serviceDurationDesignator);
                        rawDelivery.getDeliveryEstimate().setDurationMinValue(row.getInt("service_duration_min_value") + i);//since i is 0
                        rawDelivery.getDeliveryEstimate().setDurationMaxValue(row.getInt("service_duration_max_value") + i);
                        rawDelivery.getDeliveryEstimate().setDurationMinValueWithLeadTime(row.getInt("service_duration_min_value") + i + sumLeadTimes);
                        rawDelivery.getDeliveryEstimate().setDurationMaxValueWithLeadTime(row.getInt("service_duration_max_value") + i + sumLeadTimes);
                        break;
                    }
                } else {
                    DeliveryCutOff deliveryCutOff = null;
                    deliveryCutOff = new DeliveryCutOff((availabilityServiceContextV3.getDayOfWeek() + i) % 7, deliveryCutOffTime / 1000000L);
                    if (deliveryCutOffList == null) {
                        deliveryCutOffList = new ArrayList<>();
                    }
                    deliveryCutOffList.add(deliveryCutOff);
                    rawDelivery.setDeliveryEstimate(new DeliveryEstimate());
                    rawDelivery.getDeliveryEstimate().setDurationDesignator(serviceDurationDesignator);
                    rawDelivery.getDeliveryEstimate().setDurationMinValue(row.getInt("service_duration_min_value") + i);
                    rawDelivery.getDeliveryEstimate().setDurationMaxValue(row.getInt("service_duration_max_value") + i);
                    rawDelivery.getDeliveryEstimate().setDurationMinValueWithLeadTime(row.getInt("service_duration_min_value") + i + sumLeadTimes);
                    rawDelivery.getDeliveryEstimate().setDurationMaxValueWithLeadTime(row.getInt("service_duration_max_value") + i + sumLeadTimes);
                    break;
                }
            }
        }
        if (deliveryCutOffList != null)
            rawDelivery.setDeliveryCutOff(deliveryCutOffList);
//        if (rawDelivery.getDeliveryCutOff()!= null && rawDelivery.getDeliveryCutOff().size() > 0)
//            existingRawDeliveryList.add(rawDelivery);

        //now that we are done creating the new RawDelivery Object
        //check which rawDelivery is quicker. if the new rawDelivery has valid values we are good
        if (rawDelivery.getDeliveryCutOff() != null && rawDelivery.getDeliveryCutOff().size() > 0 && rawDelivery.getDeliveryEstimate() != null) {
            //first check which has lower durations
            if (rawDelivery.getDeliveryEstimate().getDurationMinValueWithLeadTime() < existingRawDelivery.getDeliveryEstimate().getDurationMinValueWithLeadTime()) { // new rawDelivery is quicker
                //use this new rawDelivery
                existingRawDeliveryList.remove(0);
                RawDelivery newRawDelivery = (RawDelivery) JavaDeepClone.deepClone(rawDelivery);
                existingRawDeliveryList.add(newRawDelivery);
            } else if (rawDelivery.getDeliveryEstimate().getDurationMinValueWithLeadTime() == existingRawDelivery.getDeliveryEstimate().getDurationMinValueWithLeadTime()) { // durations are equal
                // then check which which is quicker
                //if (rawDelivery.getDeliveryCutOff().get(0) != null && existingRawDelivery.getDeliveryCutOff().get(0) != null && rawDelivery.getDeliveryCutOff().get(0).getCutOffTime() > existingRawDelivery.getDeliveryCutOff().get(0).getCutOffTime()) {
                if (rawDelivery.getDeliveryCutOff().get(0) != null &&
                        existingRawDelivery.getDeliveryCutOff().get(0) != null &&
                        availabilityServiceContextV3.cutOffTimeAfterMax(rawDelivery.getDeliveryCutOff().get(0).getCutOffTime(), existingRawDelivery.getDeliveryCutOff().get(0).getCutOffTime(), shipFromTZ, shipFromTZ)
                        ) {
                    /// use the new one
                    existingRawDeliveryList.remove(0);
                    RawDelivery newRawDelivery = (RawDelivery) JavaDeepClone.deepClone(rawDelivery);
                    existingRawDeliveryList.add(newRawDelivery);
                }
                else {//use the existing rawDelivery
//                    RawDelivery quickestDelivery = (RawDelivery) JavaDeepClone.deepClone(existingRawDeliveryList.get(0));
//                    deliveryList.add(quickestDelivery);
                }
            }
            else {
//                RawDelivery quickestDelivery = (RawDelivery) JavaDeepClone.deepClone(existingRawDeliveryList.get(0));
//                deliveryList.add(quickestDelivery);
            }
        }else {
//            RawDelivery quickestDelivery = (RawDelivery) JavaDeepClone.deepClone(existingRawDeliveryList.get(0));
//            deliveryList.add(quickestDelivery);
        }

        //at this point we have the quickest rawDelivery across paid and free for this site

        if (existingPaidRawDelivery != null && isPaidDelivery){
            if (rawDelivery.getDeliveryCutOff() != null && rawDelivery.getDeliveryCutOff().size() > 0 && rawDelivery.getDeliveryEstimate() != null) {
                //first check which has lower durations
                if (rawDelivery.getDeliveryEstimate().getDurationMinValueWithLeadTime() < existingPaidRawDelivery.getDeliveryEstimate().getDurationMinValueWithLeadTime()) { // new rawDelivery is quicker
                    //use this rawDelivery.  the old one from the list
                    existingPaidRawDeliveryList.remove(0);
                    RawDelivery paidRawDelivery = (RawDelivery) JavaDeepClone.deepClone(rawDelivery);
                    existingPaidRawDeliveryList.add(paidRawDelivery);
                } else if (rawDelivery.getDeliveryEstimate().getDurationMinValueWithLeadTime() == existingPaidRawDelivery.getDeliveryEstimate().getDurationMinValueWithLeadTime()) { // durations are equal
                    // then check which which is quicker
                    if (rawDelivery.getDeliveryCutOff().get(0) != null
                            && existingPaidRawDelivery.getDeliveryCutOff().get(0) != null
                            && availabilityServiceContextV3.cutOffTimeAfterMax(rawDelivery.getDeliveryCutOff().get(0).getCutOffTime(), existingPaidRawDelivery.getDeliveryCutOff().get(0).getCutOffTime(), shipFromTZ, shipFromTZ)
                            ) {
                        /// use the new one
                        existingPaidRawDeliveryList.remove(0);
                        RawDelivery paidRawDelivery = (RawDelivery) JavaDeepClone.deepClone(rawDelivery);
                        existingPaidRawDeliveryList.add(paidRawDelivery);
                    }
                }
            }
        }// now we have the quickest paid rawDelivery

        if (existingFreeRawDelivery != null && !isPaidDelivery){
            if (rawDelivery.getDeliveryCutOff() != null && rawDelivery.getDeliveryCutOff().size() > 0 && rawDelivery.getDeliveryEstimate() != null) {
                //first check which has lower durations
                if (rawDelivery.getDeliveryEstimate().getDurationMinValueWithLeadTime() < existingFreeRawDelivery.getDeliveryEstimate().getDurationMinValueWithLeadTime()) { // new rawDelivery is quicker
                    //use this new rawDelivery.
                    existingFreeRawDeliveryList.remove(0);
                    RawDelivery freeRawDelivery = (RawDelivery) JavaDeepClone.deepClone(rawDelivery);

                    existingFreeRawDeliveryList.add(freeRawDelivery);
                } else if (rawDelivery.getDeliveryEstimate().getDurationMinValueWithLeadTime() == existingFreeRawDelivery.getDeliveryEstimate().getDurationMinValueWithLeadTime()) { // durations are equal
                    // then check which which is quicker
                    if (rawDelivery.getDeliveryCutOff().get(0) != null
                            && existingFreeRawDelivery.getDeliveryCutOff().get(0) != null
                            && availabilityServiceContextV3.cutOffTimeAfterMax(rawDelivery.getDeliveryCutOff().get(0).getCutOffTime(), existingFreeRawDelivery.getDeliveryCutOff().get(0).getCutOffTime(), shipFromTZ, shipFromTZ)
                            ) {
                        /// use the new one
                        existingFreeRawDeliveryList.remove(0);
                        RawDelivery freeRawDelivery = (RawDelivery) JavaDeepClone.deepClone(rawDelivery);
                        existingFreeRawDeliveryList.add(freeRawDelivery);
                    }
                }
            }
        }// now we have the quickest free rawDelivery

        if (isPaidDelivery){
            if (existingPaidRawDeliveryList.size() == 0 && rawDelivery.getDeliveryCutOff()!= null && rawDelivery.getDeliveryCutOff().size() > 0 ){
                RawDelivery paidRawDelivery = (RawDelivery) JavaDeepClone.deepClone(rawDelivery);
                existingPaidRawDeliveryList.add(paidRawDelivery);
            }
        }else{
            if (existingFreeRawDeliveryList.size() == 0 && rawDelivery.getDeliveryCutOff()!= null && rawDelivery.getDeliveryCutOff().size() > 0){
                RawDelivery freeRawDelivery = (RawDelivery) JavaDeepClone.deepClone(rawDelivery);
                existingFreeRawDeliveryList.add(freeRawDelivery);
            }
        }

    }

    private void createFreePaidDelivery(String serviceDurationDesignator,Row row,  Map<String, List<RawDelivery>> quickestFreePaidDeliveryBySite, AvailabilityServiceContextV3 availabilityServiceContextV3, Map<String, List<RawDelivery>> freeDeliveryBySite,   Map<String, List<RawDelivery>> paidDeliveryBySite ){
        SplunkLogger splunkLogger = new SplunkLogger(availabilityServiceContextV3.getRequest().getHeaderRequest());

        if (serviceDurationDesignator != null) {
            List<RawDelivery> existingFreeRawDeliveryList = null;
            List<RawDelivery> existingPaidRawDeliveryList =null ;
            List<RawDelivery> existingRawDeliveryList = null;

            boolean isDeliveryPaid = false;
           // List<RawDelivery> deliveryList = new ArrayList<>();
            String deliveryServiceType = row.getString("delivery_service_type");
            if (nSightProperties.getPaidMOTS().contains(deliveryServiceType))
                isDeliveryPaid=true;

                String fromSiteID = row.getUUID("ship_from_site_identifier").toString();
            float shipFromTZ = 0;//set the timezone for the shipfrom site
            if (nSightCache.getSiteIdToSiteMap().get(fromSiteID) != null && nSightCache.getSiteIdToSiteMap().get(fromSiteID).getTimeZone() != null && nSightCache.getSiteIdToSiteMap().get(fromSiteID).getTimeZone().trim() != "")
                try {
                    shipFromTZ = Float.parseFloat(nSightCache.getSiteIdToSiteMap().get(fromSiteID).getTimeZone());
                }catch (Exception ex){
                    LOGGER.error(splunkLogger.toString() + " , Error in createFreePaidDelivery, Message :  {}, StackTrace: {} ", ex.getMessage(), ex.getStackTrace());
                }

            boolean noCutoffTimes = false;
            if (quickestFreePaidDeliveryBySite.containsKey(fromSiteID)) {// entry for this site exists in map
                //get the existing rawDelivery to compare with the new rawDelivery
                existingRawDeliveryList = quickestFreePaidDeliveryBySite.get(fromSiteID);
                existingFreeRawDeliveryList = freeDeliveryBySite.get(fromSiteID);
                existingPaidRawDeliveryList = paidDeliveryBySite.get(fromSiteID);
                if (existingFreeRawDeliveryList == null)
                    existingFreeRawDeliveryList =  new ArrayList<>();
                if (existingPaidRawDeliveryList == null)
                    existingPaidRawDeliveryList =  new ArrayList<>();

                compareAndSetQuickestDeliveryList(availabilityServiceContextV3,row, existingRawDeliveryList,
                        deliveryServiceType,fromSiteID,serviceDurationDesignator,
                        shipFromTZ, isDeliveryPaid, existingFreeRawDeliveryList, existingPaidRawDeliveryList);
                //at this point free, paid and quickest lists would be populated for this site
            } else {
                existingRawDeliveryList = new ArrayList<>();

                //found the first rawDelivery
                RawDelivery rawDelivery = new RawDelivery();
                rawDelivery.setDeliveryType(deliveryServiceType);
                rawDelivery.setSiteId(fromSiteID);

                //populate lead times
                if (availabilityServiceContextV3.getLeadTimeBySite() !=null  && availabilityServiceContextV3.getLeadTimeBySite().containsKey(fromSiteID)){
                    List<LeadTimes> copy = availabilityServiceContextV3.getLeadTimeBySite().get(fromSiteID).stream()
                            .filter(s -> s.getDeliveryServiceType().compareToIgnoreCase(deliveryServiceType) == 0)
                            .collect(Collectors.toList());
                    if (copy!=null & copy.size() > 0)
                        rawDelivery.setLeadTimes(copy);
                }

                List<DeliveryCutOff> deliveryCutOffList = null;

                for (int i = 0; i < 7; i++) {
                    //check if the time is a null value
                    boolean isNull = row.isNull(dayOfWeekColNames[(availabilityServiceContextV3.getDayOfWeek() + i) % 7]);
                    //include lead times in calculation
                    String dayOfWeek = leadTimesDayOfWeekColNames[(availabilityServiceContextV3.getDayOfWeek() + i) % 7];
                    int sumLeadTimes = availabilityServiceContextV3.getLeadTimesForSite(dayOfWeek, fromSiteID, deliveryServiceType);

                    if (!isNull) {
                        long deliveryCutOffTime = row.getTime(dayOfWeekColNames[(availabilityServiceContextV3.getDayOfWeek() + i) % 7]);
                        boolean afterCutOff = false;
                        if (availabilityServiceContextV3.getStoreOffSetTime() != null && shipFromTZ != 0) {
                            if (!availabilityServiceContextV3.requestTimeBeforeCutOffTime(deliveryCutOffTime/1000000, shipFromTZ,row.getInt("service_duration_min_value")+sumLeadTimes))//expects millis
                                afterCutOff=true;
                        }else{
                            long now = 1000000 * availabilityServiceContextV3.getTimeOfDay();
                            if (deliveryCutOffTime < now)
                                afterCutOff=true;
                        }

                        //if we have passed the cutoff time
                        if (i == 0 ) {
                            if (afterCutOff) {
                                continue;//passed the cutoff time for today
                            }
                            else{//found before the cutoff time for today
                                DeliveryCutOff deliveryCutOff = null;
                                deliveryCutOff = new DeliveryCutOff((availabilityServiceContextV3.getDayOfWeek() + i) % 7, deliveryCutOffTime / 1000000L);
                                if (deliveryCutOffList == null) {
                                    deliveryCutOffList = new ArrayList<>();
                                }
                                deliveryCutOffList.add(deliveryCutOff);
                                rawDelivery.setDeliveryEstimate(new DeliveryEstimate());
                                rawDelivery.getDeliveryEstimate().setDurationDesignator(serviceDurationDesignator);
                                rawDelivery.getDeliveryEstimate().setDurationMinValue(row.getInt("service_duration_min_value") + i);
                                rawDelivery.getDeliveryEstimate().setDurationMaxValue(row.getInt("service_duration_max_value") + i);
                                rawDelivery.getDeliveryEstimate().setDurationMinValueWithLeadTime(row.getInt("service_duration_min_value") + i + sumLeadTimes);
                                rawDelivery.getDeliveryEstimate().setDurationMaxValueWithLeadTime(row.getInt("service_duration_max_value") + i + sumLeadTimes);
                                break;
                            }
                        } else {
                            DeliveryCutOff deliveryCutOff = null;
                            deliveryCutOff = new DeliveryCutOff((availabilityServiceContextV3.getDayOfWeek() + i) % 7, deliveryCutOffTime / 1000000L);
                            if (deliveryCutOffList == null) {
                                deliveryCutOffList = new ArrayList<>();
                            }
                            deliveryCutOffList.add(deliveryCutOff);
                            rawDelivery.setDeliveryEstimate(new DeliveryEstimate());
                            rawDelivery.getDeliveryEstimate().setDurationDesignator(serviceDurationDesignator);
                            rawDelivery.getDeliveryEstimate().setDurationMinValue(row.getInt("service_duration_min_value") + i);
                            rawDelivery.getDeliveryEstimate().setDurationMaxValue(row.getInt("service_duration_max_value") + i);
                            rawDelivery.getDeliveryEstimate().setDurationMinValueWithLeadTime(row.getInt("service_duration_min_value") + i + sumLeadTimes);
                            rawDelivery.getDeliveryEstimate().setDurationMaxValueWithLeadTime(row.getInt("service_duration_max_value") + i + sumLeadTimes);
                            break;
                        }
                    }
                }//loop cutoffs

                if (deliveryCutOffList != null && deliveryCutOffList.size() > 0)
                    rawDelivery.setDeliveryCutOff(deliveryCutOffList);

                if (rawDelivery.getDeliveryCutOff()!= null && rawDelivery.getDeliveryCutOff().size() > 0) {
                    existingRawDeliveryList.add(rawDelivery);

                    if (isDeliveryPaid ) {
                        existingPaidRawDeliveryList = new ArrayList<>();
                        RawDelivery paidRawDelivery = (RawDelivery) JavaDeepClone.deepClone(rawDelivery);
                        existingPaidRawDeliveryList.add(paidRawDelivery);
                    }
                    else {
                        existingFreeRawDeliveryList = new ArrayList<>();
                        RawDelivery freeRawDelivery = (RawDelivery) JavaDeepClone.deepClone(rawDelivery);
                        existingFreeRawDeliveryList.add(freeRawDelivery);
                    }
                }

                //finished adding the first found rawDelivery
            }//end of else already have rawDelivery list for this site

            /// finally add it to the map, if the list is populated
            if (existingRawDeliveryList !=null && existingRawDeliveryList.size() > 0)
                quickestFreePaidDeliveryBySite.put(fromSiteID, existingRawDeliveryList);

            if (isDeliveryPaid) {
                if (existingPaidRawDeliveryList != null) {
                    paidDeliveryBySite.put(fromSiteID, existingPaidRawDeliveryList);
                }
            }
            else {
                if (existingFreeRawDeliveryList != null) {
                    freeDeliveryBySite.put(fromSiteID, existingFreeRawDeliveryList);
                }
            }

        }//end of check service duration designator
    }

    private RawDelivery createDelivery(String serviceDurationDesignator,Row row, AvailabilityServiceContextV3 availabilityServiceContextV3, List<RawDelivery> rawDeliveryList, Set<String> fromSitesToZip){
        RawDelivery rawDelivery =null;

        if (serviceDurationDesignator != null) {
            rawDelivery = new RawDelivery();

            rawDelivery.setDeliveryType(row.getString("delivery_service_type"));
            String shipFrom = null;
            final String deliveryType = rawDelivery.getDeliveryType();
            rawDelivery.setSiteId(row.getUUID("ship_from_site_identifier").toString());
            shipFrom = row.getUUID("ship_from_site_identifier").toString();
            String shipTo = null;
            if (row.getColumnDefinitions().contains("ship_to_site_identifier"))
                shipTo = row.getUUID("ship_to_site_identifier").toString();
            String shipToZip = null;
            if (row.getColumnDefinitions().contains("ship_to_zip_code"))
                shipToZip = row.getString("ship_to_zip_code");

            List<DeliveryCutOff> deliveryCutOffList = null;
            for (int i = 0; i < dayOfWeekColNames.length; i++) {
                boolean isNull = row.isNull(dayOfWeekColNames[i]);
                if (!isNull) {
                    long deliveryCutOffTime = row.getTime(dayOfWeekColNames[i]);
                    DeliveryCutOff deliveryCutOff = null;
                    //if (deliveryCutOffTime != 0L)
                    {
                        deliveryCutOff = new DeliveryCutOff(i, deliveryCutOffTime / 1000000L);
                        if (deliveryCutOffList == null) {
                            deliveryCutOffList = new ArrayList<>();
                        }
                        deliveryCutOffList.add(deliveryCutOff);
                    }
                }
            }
            //populate lead times
            if (availabilityServiceContextV3.getLeadTimeBySite() !=null  && availabilityServiceContextV3.getLeadTimeBySite().containsKey(shipFrom)){

//                if (rawDelivery.getLeadTimes()!=null && rawDelivery.getLeadTimes().size()==0)
                {
                    List<LeadTimes> copy = availabilityServiceContextV3.getLeadTimeBySite().get(shipFrom).stream()
                            .filter(s -> s.getDeliveryServiceType().compareToIgnoreCase(deliveryType) == 0)
                            .collect(Collectors.toList());
                    if (copy!=null & copy.size() > 0)
                        rawDelivery.setLeadTimes(copy);
                    //rawDelivery.getLeadTimes().removeIf(n -> n.getDeliveryServiceType().compareToIgnoreCase(deliveryType)!=0);
                }
            }

            if (deliveryCutOffList != null) {
                rawDelivery.setDeliveryCutOff(deliveryCutOffList);
            }

            rawDelivery.setDeliveryEstimate(new DeliveryEstimate());
            rawDelivery.getDeliveryEstimate().setDurationDesignator(serviceDurationDesignator);
            rawDelivery.getDeliveryEstimate().setDurationMinValue(row.getInt("service_duration_min_value"));
            rawDelivery.getDeliveryEstimate().setDurationMaxValue(row.getInt("service_duration_max_value"));

            DeliveryDestination deliveryDestination = new DeliveryDestination();
            if (shipTo != null) {//site. need to get alias and value
                String aliasFullName = availabilityServiceContextV3.getReverseShipToSitesMap().get(shipTo);
                if (aliasFullName != null) {
                    String arr[] = aliasFullName.split("\\|");
                    if (arr.length > 1) {
                        deliveryDestination.setValue(arr[1]);
                        deliveryDestination.setName(arr[0]);
                        rawDelivery.setDeliveryDestination(deliveryDestination);
                        rawDeliveryList.add(rawDelivery);//add it to the list
                    }//valid alias names for SiteID
                }
            }
            if (shipToZip != null && shipToZip.length() == 5) {//zip, that means they had requested a zipcode
                //zipcode  5 digit
                deliveryDestination.setName("Zip Code");
                deliveryDestination.setValue(shipToZip);
                rawDelivery.setDeliveryDestination(deliveryDestination);
                rawDeliveryList.add(rawDelivery);//add it to the list
                //also add it to the set which stores 5 digit deliveris
                fromSitesToZip.add(rawDelivery.getDeliveryType()+shipFrom+shipToZip.substring(0,3));
                System.out.println("Added zip: " + rawDelivery.getDeliveryType()+shipFrom+shipToZip.substring(0,3));
            }

            if (shipToZip != null && shipToZip.length() == 3) {//3 digit zipcode
                //check if the 5 digit for this 3 digit is already present
                //if its present ignore the 3 digit
                if (fromSitesToZip.contains(rawDelivery.getDeliveryType() + shipFrom + shipToZip)) {
                    //ignore this zipcode
                    System.out.println("Ignoring ZIP: "+ rawDelivery.getDeliveryType() + shipFrom + shipToZip);
                } else {
                    //loop through the requested 5 digit zips
                    for (String zipcode : availabilityServiceContextV3.getZipCodesSet()) {
                        if (zipcode.contains(shipToZip)) {
                            //create a new deliuvery section for this zip
                            RawDelivery altRawDelivery = (RawDelivery) JavaDeepClone.deepClone(rawDelivery);
                            DeliveryDestination deliveryAltDestination = new DeliveryDestination();
                            deliveryAltDestination.setName("Zip Code");
                            deliveryAltDestination.setValue(zipcode);
                            altRawDelivery.setDeliveryDestination(deliveryAltDestination);
                            if (!rawDeliveryList.contains(altRawDelivery))
                                rawDeliveryList.add(altRawDelivery);//add it to the list
                        }
                    }
                }
            }
        }

        return rawDelivery;
    }
}