WorkLoadAllocationRepositoryImpl.java

package com.tradecloud.repository.workload.impl;

import com.tradecloud.domain.base.utils.DateUtils;
import com.tradecloud.domain.workload.*;
import com.tradecloud.dto.workload.WorkLoadAllocationResult;
import com.tradecloud.dto.workload.WorkLoadAllocationSearch;
import com.tradecloud.repository.SearchMetaParams;
import com.tradecloud.repository.base.impl.RepositoryBaseImpl;
import com.tradecloud.repository.workload.WorkLoadAllocationRepository;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.Query;
import javax.persistence.TypedQuery;
import java.math.BigInteger;
import java.time.LocalDateTime;
import java.util.*;

@Repository(value = "workLoadAllocationRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class WorkLoadAllocationRepositoryImpl extends RepositoryBaseImpl<WorkLoadAllocation, Object> implements WorkLoadAllocationRepository {

    private static final String WORKLOAD_ALLOCATION_QUERY = """
            WITH supplier_data AS (
                SELECT c.shipment_id, STRING_AGG(distinct(t.name), ', ') AS supplier
                FROM consignment c 
                JOIN orders co ON co.consignment_id = c.id 
                JOIN purchaseorder p ON p.id = co.id 
                JOIN organisationalunitsupplier tus ON tus.id = p.supplier_id 
                JOIN supplier t ON t.id = tus.supplier_id 
                GROUP BY c.shipment_id 
            ), 
            payment_terms_data AS ( 
                SELECT c.shipment_id, STRING_AGG(distinct(pt.name), ', ') AS paymentTerms 
                FROM consignment c 
                JOIN orders co ON co.consignment_id = c.id 
                JOIN purchaseorder p ON p.id = co.id 
                JOIN paymentterm pt on (pt.code=co.paymentterm_code)
                GROUP BY c.shipment_id 
            ) 
            SELECT
                w.id as id, 
                w.entityReference AS reference, 
                w.entityNumber AS number, 
                w.dueDate As dueDate,
                COALESCE(sif.name, sisf.name) AS freightForwarder, 
                sea.shippingVessel AS vesselName, 
                ct.name AS type, 
                COALESCE(si.shippingmode, sis.shippingmode) AS shippingMode, 
                COALESCE(si.multiModalShippingMode, sis.multiModalShippingMode) AS multiModalShippingMode, 
                COALESCE(osup.name, supplier_data.supplier) AS supplier, 
                COALESCE((select name from paymentterm where code= o.paymentterm_code), payment_terms_data.paymentTerms) AS paymentTerms, 
                COALESCE(s.scheduledDepartureDate, o.latestshipmentdate) AS scheduledDepartureDate, 
                COALESCE(s.estimatedarrivaldateatplaceofdischarge, o.arrivalatplaceofdischargedate) AS estimateArrivalPod, 
                w.comment_id AS comment, 
                u.username AS assignedUser, w.applyTo,
                COALESCE(w.orderId, w.shipmentId) AS entityId,
                w.textComment,
                w.priority as priority, ui.firstname||' '||ui.lastname as fullName,
                r.attribute as ruleAttribute,w.ruleDate,
               o.businessState,
               s.arrivaldateatplaceofdischarge AS actualArrivalPod,
               o.plannedshipmentdate as plannedShipmentDate,
               o.estimatedArrivalDate as estimatedArrivalDate
            FROM WorkLoadAllocation w 
            LEFT JOIN workload_rule r ON r.id = w.workloadrule_id 
            LEFT JOIN workload_categories ct ON ct.id = r.category_id 
            LEFT JOIN users u ON u.id = w.allocatedUser_id
            LEFT JOIN userinfo ui ON ui.id=u.userinfo_id 
            LEFT JOIN orders o ON o.id = w.orderId 
            LEFT JOIN shippinginformation si ON si.id = o.shippinginformation_id 
            LEFT JOIN serviceprovider sif ON sif.id = si.freightforwarder_id 
            LEFT JOIN purchaseorder po ON po.id = o.id 
            LEFT JOIN organisationalunitsupplier ous ON ous.id = po.supplier_id 
            LEFT JOIN supplier osup ON osup.id = ous.supplier_id 
            LEFT JOIN shipment s ON s.id = w.shipmentId 
            LEFT JOIN shipmentshippinginfo sis ON sis.id = s.shippinginfo_id 
            LEFT JOIN serviceprovider sisf ON sisf.id = sis.freightforwarder_id 
            LEFT JOIN seashipment sea ON sea.id = s.id 
            LEFT JOIN supplier_data ON supplier_data.shipment_id = s.id 
            LEFT JOIN payment_terms_data ON payment_terms_data.shipment_id = s.id 
            WHERE w.actionedBy IS NULL %s
            ORDER BY w.priority DESC,applyTo DESC, w.ruleDate asc
            """;

    private static final String WORKLOAD_ALLOCATION_QUERY_COUNT = """
                 select count(*) FROM WorkLoadAllocation  w where w.actionedBy IS NULL %s
            """;

    private static final Map<DateFilter, String> priorityDateFilterMappings = Map.of(
            DateFilter.EARLIEST_SHIPMENT_DATE, "date(s.earliestshipmentdate)",
            DateFilter.LATEST_SHIPMENT_DATE, "date(s.latestshipmentdate)",
            DateFilter.PLANNED_SHIPMENT_DATE, "date(s.plannedshipmentdate)",
            DateFilter.REQUIRED_ON_SITE_DATE, "date(s.requiredonsitedate)");

    @Override
    public long countBy(WorkLoadRule rule, Long entityId) {
        String querySql = "SELECT COUNT(w) FROM WorkLoadAllocation w WHERE w.workLoadRule = :workLoadRule " +
                "AND (w.orderId = :entityId or w.shipmentId = :entityId)" +
                "  and actionedDate is null";
        Query query = getSessionCustom().createQuery(querySql);
        query.setParameter("workLoadRule", rule);
        query.setParameter("entityId", entityId);
        return ((Number) query.getSingleResult()).longValue();
    }

    @Override
    public long countByRuleAttr(AttributeType rule, Long entityId) {
        String querySql = "SELECT COUNT(w) FROM WorkLoadAllocation w WHERE w.workLoadRule.attribute = :workLoadRule " +
                "AND (w.orderId = :entityId or w.shipmentId = :entityId)" +
                "  and actionedDate is null";
        Query query = getSessionCustom().createQuery(querySql);
        query.setParameter("workLoadRule", rule);
        query.setParameter("entityId", entityId);
        return ((Number) query.getSingleResult()).longValue();
    }

    @Override
    public List<WorkLoadAllocation> findAllNotActioned(WorkLoadRule rule, Date lastPriorityDate) {
        String nativeSql = "SELECT w.* FROM WorkLoadAllocation w " +
                "LEFT JOIN Shipment s ON s.id = w.shipmentId " +
                "LEFT JOIN Orders o ON o.id = w.orderId " +
                "WHERE w.workLoadRule_id = :workLoadRule " +
                "AND w.actionedDate IS NULL " +
                "AND (s.updated > :lastRunDate OR o.updated > :lastRunDate)";
        Date date = Optional.ofNullable(lastPriorityDate).orElse(DateUtils.addDays(new Date(), -100));
        Query query = getSessionCustom().createNativeQuery(nativeSql, WorkLoadAllocation.class);
        query.setParameter("workLoadRule", rule.getId());
        query.setParameter("lastRunDate", date);
        return query.getResultList();
    }

    @Override
    public List<Map<String, String>> findPriorityAttr(WorkLoadAllocation workLoadAllocation) {
        Objects.requireNonNull(workLoadAllocation, "WorkLoadAllocation must not be null");
        Objects.requireNonNull(workLoadAllocation.getApplyTo(), "RuleScope must not be null");

        final RuleProcessor ruleProcessor = switch (workLoadAllocation.getApplyTo()) {
            case ORDER -> new OrderWorkLoadRuleProcessing(getCurrentSession());
            case SHIPMENT -> new ShipmentWorkLoadRuleProcessing(getCurrentSession());
            default -> throw new IllegalArgumentException("Unsupported RuleScope: " + workLoadAllocation.getApplyTo());
        };
        return ruleProcessor.findPriorityAttr(workLoadAllocation);
    }

    @Override
    public List<WorkLoadAllocation> findBy(RuleScope scope, Long entityId) {
        String querySql = "SELECT w FROM WorkLoadAllocation w WHERE w.applyTo = :scope AND (w.orderId = :entityId OR w.shipmentId=:entityId) " +
                "and actionedBy is null";
        TypedQuery<WorkLoadAllocation> query = getSessionCustom().createQuery(querySql, WorkLoadAllocation.class);
        query.setParameter("scope", scope);
        query.setParameter("entityId", entityId);
        return query.getResultList();
    }

    @Override
    public List<WorkLoadAllocationResult> search(WorkLoadAllocationSearch search) {
        NativeQuery query = getSessionCustom().createNativeQuery(String.format(WORKLOAD_ALLOCATION_QUERY, ""));

        addScalar(query);
        SearchMetaParams searchMetaParams = search.getSearchMetaParams();
        if (searchMetaParams != null) {
            query.setFirstResult(searchMetaParams.getRowIndex());
            query.setMaxResults(searchMetaParams.getRowCount());
        }
        return query.getResultList();
    }

    @Override
    public long count(WorkLoadAllocationSearch search) {
        NativeQuery query = getSessionCustom().createNativeQuery(String.format(WORKLOAD_ALLOCATION_QUERY_COUNT, ""));
        return ((BigInteger) query.uniqueResult()).longValue();
    }

    @Override
    public List<WorkLoadAllocationResult> findUserAllocations(String username) {
        NativeQuery query = getSessionCustom().createNativeQuery(String.format(WORKLOAD_ALLOCATION_QUERY, " And u.username=:username "));
        addScalar(query);
        query.setParameter("username", username);
        return query.getResultList();
    }

    private void addScalar(NativeQuery query) {
        query.addScalar("id", StandardBasicTypes.LONG)
                .addScalar("reference", StandardBasicTypes.STRING)
                .addScalar("number", StandardBasicTypes.STRING)
                .addScalar("freightForwarder", StandardBasicTypes.STRING)
                .addScalar("vesselName", StandardBasicTypes.STRING)
                .addScalar("type", StandardBasicTypes.STRING)
                .addScalar("shippingMode", StandardBasicTypes.STRING)
                .addScalar("multiModalShippingMode", StandardBasicTypes.STRING)
                .addScalar("supplier", StandardBasicTypes.STRING)
                .addScalar("paymentTerms", StandardBasicTypes.STRING)
                .addScalar("scheduledDepartureDate", StandardBasicTypes.DATE)
                .addScalar("estimateArrivalPod", StandardBasicTypes.DATE)
                .addScalar("comment", StandardBasicTypes.STRING)
                .addScalar("assignedUser", StandardBasicTypes.STRING)
                .addScalar("applyTo", StandardBasicTypes.STRING)
                .addScalar("entityId", StandardBasicTypes.LONG)
                .addScalar("textComment", StandardBasicTypes.STRING)
                .addScalar("priority", StandardBasicTypes.SHORT)
                .addScalar("fullName", StandardBasicTypes.STRING)
                .addScalar("ruleAttribute", StandardBasicTypes.STRING)
                .addScalar("ruleDate", StandardBasicTypes.DATE)
                .addScalar("businessState", StandardBasicTypes.STRING)
                .addScalar("actualArrivalPod", StandardBasicTypes.DATE)
                .addScalar("plannedShipmentDate", StandardBasicTypes.DATE)
                .addScalar("estimatedArrivalDate", StandardBasicTypes.DATE)
                .addScalar("dueDate", StandardBasicTypes.DATE)
                .setResultTransformer(Transformers.aliasToBean(WorkLoadAllocationResult.class));
    }

    @Override
    public List<WorkLoadAllocation> findAll(WorkLoadRule workLoadRule) {
        String querySql = "SELECT w FROM WorkLoadAllocation w WHERE w.workLoadRule = :workLoadRule  ";
        TypedQuery<WorkLoadAllocation> query = getSessionCustom().createQuery(querySql, WorkLoadAllocation.class);
        query.setParameter("workLoadRule", workLoadRule);
        return query.getResultList();
    }

    @Override
    public void savePartial(long id, String fieldName, Object value) {
        if (id <= 0 || fieldName == null || fieldName.isBlank()) {
            throw new IllegalArgumentException("Valid ID and field name required");
        }

        String jpqlField = switch (fieldName) {
            case "textComment" -> "textComment";
            case "allocatedUser" -> "allocatedUser.id";
            case "workLoadComment" -> "comment.id";
            case "dueDate" -> "dueDate";
            default -> throw new IllegalArgumentException("Unsupported field: " + fieldName);
        };

        String jpql = "UPDATE WorkLoadAllocation w SET w." + jpqlField + " = :value, w.updated = :updated WHERE w.id = :id";

        org.hibernate.query.Query<?> query = getSessionCustom().createQuery(jpql);
        query.setParameter("value", value);
        query.setParameter("updated", new Date());
        query.setParameter("id", id);
        query.executeUpdate();
    }

    @Override
    public void removeComment(WorkLoadComment workLoadComment) {
        if (workLoadComment == null || workLoadComment.getId() == null) {
            throw new IllegalArgumentException("WorkLoadComment or its ID must not be null");
        }

        String jpql = """
                    UPDATE WorkLoadAllocation w 
                    SET w.comment = null 
                    WHERE w.comment.id = :commentId
                """;

        org.hibernate.query.Query<?> query = getSessionCustom().createQuery(jpql);
        query.setParameter("commentId", workLoadComment.getId());
        query.executeUpdate();
    }

    @Override
    public long findOrderSupplierId(Long orderId) {
        String nativeSql = """
                    select ous.supplier_id from orders o join purchaseorder p on ( p.id=o.id) 
                    join organisationalunitsupplier ous on(ous.id=p.supplier_id)  where o.id =:orderId
                """;
        NativeQuery query = getSessionCustom().createNativeQuery(nativeSql);
        query.setParameter("orderId", orderId);
        return ((BigInteger) query.uniqueResult()).longValue();
    }

    @Override
    public List<Long> findOrderWorkLoadAllocation(List<Long> supplierIds) {
        String sql = """
                    SELECT DISTINCT w.id
                    FROM WorkLoadAllocation w
                    JOIN workload_rule r on (r.id=w.workloadrule_id)
                    JOIN orders o ON o.id = w.orderId
                    JOIN purchaseorder p ON p.id = o.id
                    JOIN organisationalunitsupplier ous ON ous.id = p.supplier_id
                    WHERE r.enablesupplierautoallocation AND ous.supplier_id IN (:supplierIds)
                      AND w.actionedDate IS NULL
                      AND allocateduser_id IS NULL
                """;

        NativeQuery<Long> query = getSessionCustom().createNativeQuery(sql);
        query.setParameterList("supplierIds", supplierIds);
        List<?> results = query.getResultList();
        return results.stream()
                .map(result -> ((Number) result).longValue())
                .toList();
    }

    @Override
    public List<Long> findShipmentWorkLoadAllocation(List<Long> supplierIds) {
        String sql = """
                    SELECT DISTINCT w.id
                    FROM WorkLoadAllocation w
                    JOIN workload_rule r on (r.id=w.workloadrule_id)
                    JOIN shipment s ON s.id = w.shipmentid
                    JOIN consignment c ON c.shipment_id = s.id
                    JOIN orders o ON ( o.consignment_id=c.id)
                    JOIN purchaseorder p ON p.id = o.id
                    JOIN organisationalunitsupplier ous ON ous.id = p.supplier_id
                    WHERE r.enablesupplierautoallocation AND ous.supplier_id IN (:supplierIds)
                      AND w.actionedDate IS NULL
                      AND allocateduser_id IS NULL
                """;

        NativeQuery<Long> query = getSessionCustom().createNativeQuery(sql);
        query.setParameterList("supplierIds", supplierIds);
        List<?> results = query.getResultList();
        return results.stream()
                .map(result -> ((Number) result).longValue())
                .toList();
    }

    @Override
    public void deleteActionedAllocationsOlderThan(int days) {
        String sql = """
                    DELETE FROM WorkLoadAllocation
                    WHERE actionedBy IS NOT NULL
                      AND created < :cutoffDate
                """;

        // Compute cutoff date in Java
        LocalDateTime cutoff = LocalDateTime.now().minusDays(days);
        getSessionCustom()
                .createNativeQuery(sql)
                .setParameter("cutoffDate", cutoff)
                .executeUpdate();
    }

    @Override
    public List<Long> findAllNotActionedByAttr() {
        String sql = """
                    SELECT COALESCE(w.orderid, w.shipmentid) AS id 
                    FROM WorkLoadAllocation w
                    JOIN workload_rule r on (r.id=w.workloadrule_id)
                     WHERE w.actionedDate IS NULL
                      AND allocateduser_id IS NULL 
                """;

        NativeQuery<Long> query = getSessionCustom().createNativeQuery(sql);
        //query.setParameter("attribute", orderedAttr.name());
        List<?> results = query.getResultList();
        return results.stream()
                .map(result -> ((Number) result).longValue())
                .toList();

    }

    @Override
    public boolean hasAllocationNotActioned(RuleScope scope, long id) {
        String column = switch (scope) {
            case SHIPMENT -> "shipmentid";
            case ORDER -> "orderid";
            default -> throw new IllegalArgumentException("Unsupported scope: " + scope);
        };

        String sql = """
                SELECT EXISTS (
                    SELECT 1
                    FROM WorkLoadAllocation w
                    JOIN workload_rule r ON r.id = w.workloadrule_id
                    WHERE w.actionedDate IS NULL
                      AND w.allocateduser_id IS NULL
                      AND %s = :id
                )
                """.formatted(column);

        NativeQuery<Boolean> query = getSessionCustom()
                .createNativeQuery(sql)
                .setParameter("id", id);

        return Boolean.TRUE.equals(query.uniqueResult());
    }

}