OrganisationalUnitRepositoryImpl.java

package com.tradecloud.repository.impl;

import com.tradecloud.authentication.User;
import com.tradecloud.common.base.PersistenceBase;
import com.tradecloud.common.externalreference.ExternalReference;
import com.tradecloud.domain.base.utils.ObjectUtil;
import com.tradecloud.domain.common.Currency;
import com.tradecloud.domain.common.Percentage;
import com.tradecloud.domain.event.EventGroup;
import com.tradecloud.domain.exception.EntityAccessException;
import com.tradecloud.domain.infrastructure.persistence.CriteriaBuilder;
import com.tradecloud.domain.model.events.EventOrganisationalUnit;
import com.tradecloud.domain.model.organisationalunit.*;
import com.tradecloud.domain.party.Employee;
import com.tradecloud.domain.rate.RateSourceType;
import com.tradecloud.domain.search.SearchParams;
import com.tradecloud.dto.base.StaticDataSearch;
import com.tradecloud.dto.organisationalUnit.OrganisationalUnitSearch;
import com.tradecloud.repository.OrganisationalUnitRepository;
import com.tradecloud.repository.Refreshable;
import com.tradecloud.repository.SearchMetaParams;
import com.tradecloud.repository.base.impl.RepositoryBaseImpl;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.hibernate.query.NativeQuery;
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.criteria.JoinType;
import javax.persistence.criteria.Root;
import java.io.Serializable;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.*;
import java.util.stream.Collectors;

/**
 * Repository Implementation for Organisational Unit. This is the only class in the system that should be calling Hibernate with an Organisational
 * Unit
 */

@Repository(value = "orgUnitRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class OrganisationalUnitRepositoryImpl extends RepositoryBaseImpl<OrganisationalUnit, StaticDataSearch>
        implements OrganisationalUnitRepository, Refreshable {

    public static String PLACE_ORDERS_RULE = "placeOrders";
    public static String HAS_ITEMS_RULE = "hasItems";
    public static String HAS_INVOICE_RULE = "hasInvoice";

    private static final long serialVersionUID = 1L;

    private static final Logger log = Logger.getLogger(OrganisationalUnitRepositoryImpl.class);

    private final Map<String, List<?>> cache = new HashMap<String, List<?>>();

    /**
     * Cache namespaces. Each of these will be prefixed to the client's name in order to create a cache key e.g. divisions.tfg, currencies.woo,
     * banknames.atz.
     */
    private static final String DIVISION_NS = "divisions.";
    private static final String CURRENCIES_NS = "currencies.";
    private static final String BANK_NAMES_NS = "banknames.";

    private static final String COLUMN_CODE = "code";

    private static final String QUERY_FIND_BY_CODE = "from OrganisationalUnit as o where o.code = :code";

    private static final String QUERY_PARENT_NULL = "select o from OrganisationalUnit o where o.parent is null";

    private static final String QUERY_PARENT_NULL_COUNT = "select o from OrganisationalUnit o where o.parent is null";

    private static final String QUERY_PARENT_NOT_NULL = "select o from OrganisationalUnit o where o.parent is not null";

    private static final String QUERY_PARENT_NOT_NULL_BY_NAME = "select o from OrganisationalUnit o where o.parent is not null AND o.name = :name";

    private static final String QUERY_CHILDREN_WITH_PLACE_ORDERS =
            "select o from OrganisationalUnit o where o.parent = :parent AND o.placeOrders = true and type=:type";

    private static final String QUERY_CHILDREN_WITH_HAS_ITEMS =
            "select o from OrganisationalUnit o where o.parent = :parent AND o.hasItems = true and type=:type order by o.name";

    @Override
    public OrganisationalUnit retrieve(String code) {
        List<OrganisationalUnit> list = (List<OrganisationalUnit>) findByNamedParam(QUERY_FIND_BY_CODE, COLUMN_CODE, code);

        return list.isEmpty() ? null : list.iterator().next();
    }

    @Override
    public OrganisationalUnit findByName(String name) {
        List<OrganisationalUnit> list = (List<OrganisationalUnit>) findByNamedQueryAndNamedParam("orgUnit.byName", "name",
                name.trim().toLowerCase());
        return list.isEmpty() ? null : list.get(0);
    }

    @Override
    public OrganisationalUnit findByNameAllowSuppliers(String name) {
        List<OrganisationalUnit> list = (List<OrganisationalUnit>) findByNamedQueryAndNamedParam("orgUnit.byNameAllowSupplier", "name",
                name.trim().toLowerCase());
        return list.isEmpty() ? null : list.get(0);
    }

    @Override
    public OrganisationalUnit findByCode(String code) {
        List<OrganisationalUnit> list = null;
        if (code != null) {
            list = (List<OrganisationalUnit>) findByNamedQueryAndNamedParam("orgUnit.byCode",
                    "code", code.trim().toLowerCase());
        }
        return list == null || list.isEmpty() ? null : list.get(0);
    }

    @Override
    public Collection<OrganisationalUnit> findAllWithRule(String ruleCode) {
        // log.debug("Find all with rule " + ruleCode);
        List<OrganisationalUnit> matchingOrgUnits = new ArrayList<OrganisationalUnit>();
        for (OrganisationalUnit orgUnit : findAll()) {
            if (orgUnit.isPlaceOrders() && orgUnit.isFullType()) {
                matchingOrgUnits.add(orgUnit);
            }
        }
        return matchingOrgUnits;
    }

    /**
     * Find the parent matching the orgCode Scan through it's child elements.
     * Check all the Rules for a match on the rule code. If a match is Found add
     * the child organisational unit to the return list
     *
     * TODO - this only scans to a depth of one. Might be nice to add some
     * recursive methods to the org unit for getting all it's rules and it's
     * children's rules.
     */
    @Override
    public Collection<OrganisationalUnit> findDescendentsWithRule(String orgCode, String ruleCode) {
        // log.debug("Find descendents of orgUnit " + orgCode + " with rule " + ruleCode);
        List<OrganisationalUnit> matchingOrgUnits = new ArrayList<OrganisationalUnit>();
        OrganisationalUnit orgUnit = retrieve(orgCode);
        for (OrganisationalUnit orgUnitChild : orgUnit.getChildren()) {
            if (hasRuleWithCode(orgUnitChild, ruleCode) && orgUnit.isFullType()) {
                matchingOrgUnits.add(orgUnitChild);
            }
        }
        return matchingOrgUnits;
    }

    /**
     * Finds all the children of the organisational unit with the supplied code.
     */
    @SuppressWarnings("unchecked")
    @Override
    public Collection<OrganisationalUnit> findAllOwnedBy(String code) {
        // log.debug("Find root node...find the org unit where the parent is null...");
        List<OrganisationalUnit> matchingOrgUnits = new ArrayList<OrganisationalUnit>();
        List<OrganisationalUnit> orgUnits = (List<OrganisationalUnit>) find(QUERY_PARENT_NOT_NULL);

        for (OrganisationalUnit orgUnit : orgUnits) {
            if (orgUnit.getParent().getCode() != null
                    && orgUnit.getParent().getCode().equals(code) && orgUnit.getType() == OrganisationalUnit.Type.FULL) {
                matchingOrgUnits.add(orgUnit);
            }
        }
        return matchingOrgUnits;
    }

    /**
     * Finds the only Org Unit without a parent.
     *
     * @return
     */
    @Override
    public OrganisationalUnit findRootNode() {
        List<OrganisationalUnit> orgUnits = (List<OrganisationalUnit>) find(QUERY_PARENT_NULL);
        return ObjectUtil.first(orgUnits);
    }

    @Override
    public List<OrganisationalUnit> allRootNode() {
        return getCurrentSession().createQuery(QUERY_PARENT_NULL_COUNT).list();
    }

    /**
     * TODO - change this to be a named query. Not even sure if its needed yet. Just adding a possible
     * alternative for the search by label that seems to be used by the WOO team.
     */
    @Override
    public List<OrganisationalUnit> findAllByTier(SearchParams searchParams, String tierName) {
        if (tierName == null) {
            throw new IllegalArgumentException("tierName cannot be null.");
        }

        // log.debug("findAllByTier invoked with tier " + tierName);
        List<OrganisationalUnit> matchedOrgUnits = new ArrayList<>();
        List<OrganisationalUnit> list = findAll();
        for (OrganisationalUnit organisationalUnit : list) {
            if (organisationalUnit.getType() == OrganisationalUnit.Type.FULL && organisationalUnit.getTier() != null
                    && tierName.equalsIgnoreCase(organisationalUnit.getTier().getName())) {
                // log.debug("Adding organisationalUnit " + organisationalUnit.getName());
                matchedOrgUnits.add(organisationalUnit);
            }
        }
        // log.debug("Returning matched org units of size " + matchedOrgUnits.size());
        return matchedOrgUnits;
    }

    @Override
    public List<OrganisationalUnit> findAllByTier(OrganisationalUnitTier tier, Boolean export) {
        String hql = "from OrganisationalUnit where tier = :tier and exportUnit = :export and type=:type order by name";

        Query query = getSession().createQuery(hql);
        query.setParameter("tier", tier);
        query.setParameter("type", OrganisationalUnit.Type.FULL);
        if (export != null) {
            query.setParameter("export", export);
        }

        return (List<OrganisationalUnit>) query.list();
    }

    @Override
    public OrganisationalUnit findParentByName(String name) {

        List<OrganisationalUnit> orgUnits = (List<OrganisationalUnit>) findByNamedQueryAndNamedParam(QUERY_PARENT_NOT_NULL_BY_NAME, "name", name);
        return orgUnits.get(0);
    }

    /**
     * Deletes 'em all!
     */
    @Override
    public void deleteAll() {
        // log.debug("In delete all");

        List<OrganisationalUnit> orgUnits = findAll();

        // log.debug("Found " + orgUnits.size() + " org units");
        deleteAll(orgUnits);
    }

    @Override
    public OrganisationalUnit findByNameAndTier(String name, OrganisationalUnitTier tier) {
        DetachedCriteria criteria = DetachedCriteria.forClass(OrganisationalUnit.class);

        CriteriaBuilder.addEqRestriction(criteria, "tier", tier);
        CriteriaBuilder.addEqRestriction(criteria, "name", name);
        criteria.add(Restrictions.eq("type", OrganisationalUnit.Type.FULL));
        List<OrganisationalUnit> results = getExecutableCriteriaList(criteria, null);

        return results.isEmpty() ? null : results.get(0);
    }

    @Override
    public List<OrganisationalUnit> findAllWithPlaceOrdersAndTier(OrganisationalUnitTier tier) {
        DetachedCriteria criteria = DetachedCriteria.forClass(OrganisationalUnit.class);

        CriteriaBuilder.addEqRestriction(criteria, "tier", tier);
        CriteriaBuilder.addEqRestriction(criteria, "placeOrders", true);
        criteria.add(Restrictions.eq("type", OrganisationalUnit.Type.FULL));
        List<OrganisationalUnit> results = getExecutableCriteriaList(criteria, null);

        return results;
    }

    @Override
    public List<OrganisationalUnit> findAllWithHasInvoiceAndTier(OrganisationalUnitTier tier) {
        DetachedCriteria criteria = DetachedCriteria.forClass(OrganisationalUnit.class);

        CriteriaBuilder.addEqRestriction(criteria, "tier", tier);
        CriteriaBuilder.addEqRestriction(criteria, "hasInvoice", true);
        criteria.add(Restrictions.eq("type", OrganisationalUnit.Type.FULL));
        List<OrganisationalUnit> results = getExecutableCriteriaList(criteria, null);

        return results;
    }

    @Override
    public List<OrganisationalUnit> findChildrenWithPlaceOrders(OrganisationalUnit parent) {
        String[] references = new String[]{"parent", "type"};
        Object[] values = new Object[]{parent, OrganisationalUnit.Type.FULL};
        List<OrganisationalUnit> orgUnits =
                (List<OrganisationalUnit>) findByNamedParam(QUERY_CHILDREN_WITH_PLACE_ORDERS, references, values);

        return orgUnits;
    }

    @Override
    public List<OrganisationalUnit> findAllWithHasItemsAndTier(OrganisationalUnitTier tier) {
        DetachedCriteria criteria = DetachedCriteria.forClass(OrganisationalUnit.class);

        CriteriaBuilder.addEqRestriction(criteria, "tier", tier);
        CriteriaBuilder.addEqRestriction(criteria, "hasItems", true);
        criteria.add(Restrictions.eq("type", OrganisationalUnit.Type.FULL));
        List<OrganisationalUnit> results = getExecutableCriteriaList(criteria, null);
        return results;
    }

    @Override
    public List<OrganisationalUnit> findChildrenWithHasItems(OrganisationalUnit parent) {
        String[] references = new String[]{"parent", "type"};
        Object[] values = new Object[]{parent, OrganisationalUnit.Type.FULL};
        List<OrganisationalUnit> orgUnits =
                (List<OrganisationalUnit>) findByNamedParam(QUERY_CHILDREN_WITH_HAS_ITEMS, references, values);

        return orgUnits;
    }

    @Override
    public List<OrganisationalUnit> findAllByTier(OrganisationalUnitTier tier, SearchParams searchParams) {
        log.debug("Finding org units. Tier '" + tier + "'.");
        OrganisationalUnit.Type type = OrganisationalUnit.Type.FULL;

        DetachedCriteria criteria = DetachedCriteria.forClass(OrganisationalUnit.class);
        CriteriaBuilder.addEqRestriction(criteria, "tier", tier);
        if (searchParams.isFilteredByUserOrg()) {
            addFilteredByOrg(searchParams, criteria);
        }
        if (!searchParams.isIncludeElc()) {
            criteria.add(Restrictions.eq("type", type));
        }
        return getExecutableCriteriaList(criteria, null);

//        } else {
//            String[] references = new String[]{"tier", "type"};
//            Object[] values = new Object[]{tier, type};
//            return (List<OrganisationalUnit>) findByNamedQueryAndNamedParam("orgUnit.byTier", references, values);
//        }
    }

    @Override
    public List<OrganisationalUnit> findAllByTierAllowSupplier(OrganisationalUnitTier tier) {
        log.debug("Finding org units that allow suppliers. Tier '" + tier + "'.");
        String[] references = new String[]{"tier", "type"};
        Object[] values = new Object[]{tier, OrganisationalUnit.Type.FULL};
        List<OrganisationalUnit> list = (List<OrganisationalUnit>) findByNamedQueryAndNamedParam("orgUnit.byTierAllowSupplier", references, values);
        return list;
    }

    public List<OrganisationalUnitTier> findOrganisationalUnitTier(String name, Long level) {
        Criteria criteria = getSession().createCriteria(OrganisationalUnitTier.class);

        if (name != null) {
            criteria.add(Restrictions.eq("name", name));
        }

        if (level != null) {
            criteria.add(Restrictions.eq("level_", level.intValue()));
        }

        return (List<OrganisationalUnitTier>) criteria.list();
    }

    /**
     * Finds the set of divisions that pertain to a certain organisationalUnit. Only divisions that are in an division orgunit with no parent can see
     * all divisions.
     */
    @Override
    public List<OrganisationalUnit> findAllOrganisationalUnitsForUser(String organisationalUnitName, OrganisationalUnitTier tier) {
        OrganisationalUnit parentOrgUnit = findRootNode();

        if (parentOrgUnit != null && parentOrgUnit.getName().equals(organisationalUnitName)) {
            return findAllByTier(tier, SearchParams.orderByName());
        } else {
            List<OrganisationalUnit> divisionList = new ArrayList<OrganisationalUnit>();
            OrganisationalUnit organisationalUnit = findByName(organisationalUnitName);
            convert(organisationalUnit);
            divisionList.add(organisationalUnit);

            return divisionList;
        }
    }

    @Override
    public List<OrganisationalUnit> findAllOrganisationalUnitsForUserAllowSuppliers(String organisationalUnitName, OrganisationalUnitTier tier) {
        OrganisationalUnit parentOrgUnit = findRootNode();

        if (parentOrgUnit != null && parentOrgUnit.getName().equals(organisationalUnitName)) {
            List<OrganisationalUnit> all = findAllByTierAllowSupplier(tier);
            return all;
        } else {
            List<OrganisationalUnit> divisionList = new ArrayList<>();
            OrganisationalUnit organisationalUnit = findByNameAllowSuppliers(organisationalUnitName);
            convert(organisationalUnit);
            divisionList.add(organisationalUnit);

            return divisionList;
        }
    }

    /**
     * Copied from findAllOrganisationalUnitsForUser but using org-unit.code instead.
     */
    @Override
    public List<OrganisationalUnit> findOrganisationalUnitsByCodeAndTier(String organisationalUnitCode, OrganisationalUnitTier tier) {
        OrganisationalUnit parentOrgUnit = findRootNode();

        if (organisationalUnitCode == null) {
            throw new IllegalStateException("Org Unit Code is required. Ensure That current active user has Org Unit.");
        }

        if (parentOrgUnit != null && parentOrgUnit.getCode().equals(organisationalUnitCode)) {
            return findAllByTier(tier, SearchParams.orderByName());
        } else {
            List<OrganisationalUnit> organisationalUnits = new ArrayList<OrganisationalUnit>();
            OrganisationalUnit organisationalUnit = findByCode(organisationalUnitCode);
            convert(organisationalUnit);

            organisationalUnits.add(organisationalUnit);

            return organisationalUnits;
        }
    }

    @Override
    public List<Currency> findCurrencies() {
        OrganisationalUnit client = findRootNode();
        @SuppressWarnings("unchecked")
        List<Currency> collection = (List<Currency>) cache.get(CURRENCIES_NS + client.getName());
        if (collection != null && !collection.isEmpty()) {
            log.debug("Returning cached currencies for " + client.getName());
            return collection;
        }
        return loadCurrencies(client);
    }

    @Override
    public List<String> findBankNames() {
        OrganisationalUnit client = findRootNode();
        @SuppressWarnings("unchecked")
        List<String> collection = (List<String>) cache.get(BANK_NAMES_NS + client.getName());
        if (collection != null && !collection.isEmpty()) {
            log.debug("Returning cached bank names for " + client.getName());
            return collection;
        }
        return loadBankNames(client);
    }

    /**
     * Return all Org Units that have the Org Unit Attribute "placeOrders" set to true.
     *
     * @return
     */
    @Override
    @SuppressWarnings("unchecked")
    public List<OrganisationalUnit> findAllPlaceOrderOrgUnits() {
        String[] references = new String[]{"type"};
        Object[] values = new Object[]{OrganisationalUnit.Type.FULL};
        List<OrganisationalUnit> allPlaceOrderOrgUnits = (List<OrganisationalUnit>)
                getNamedQueryAndNamedParam("orgUnit.placeOrders", references, values);
        log.debug("Found allPlaceOrderOrgUnits list of size " + allPlaceOrderOrgUnits.size() + ".");
        return allPlaceOrderOrgUnits;
    }

    @Override
    public List<OrganisationalUnit> findAllHasInvoiceOrgUnits() {
        String[] references = new String[]{"type"};
        Object[] values = new Object[]{OrganisationalUnit.Type.FULL};
        List<OrganisationalUnit> allHasInvoiceOrgUnits = (List<OrganisationalUnit>)
                getNamedQueryAndNamedParam("orgUnit.hasInvoice", references, values);
        log.debug("Found allHasInvoiceOrgUnits list of size " + allHasInvoiceOrgUnits.size() + ".");
        return allHasInvoiceOrgUnits;
    }

    @Override
    public List<OrganisationalUnit> findAllStockLevels() {
        String[] references = new String[]{"type"};
        Object[] values = new Object[]{OrganisationalUnit.Type.FULL};
        List<OrganisationalUnit> allOrgUnits = (List<OrganisationalUnit>)
                getNamedQueryAndNamedParam("orgUnit.stockLevel", references, values);
        log.debug("Found stockLevel list of size " + allOrgUnits.size() + ".");
        return allOrgUnits;
    }

    /**
     * Return all Org Units that have the Org Unit Attribute "hasItems" set to true.
     *
     * @param searchParams
     * @return
     */
    @Override
    @SuppressWarnings("unchecked")
    public List<OrganisationalUnit> findAllHasItemsOrgUnits(SearchParams searchParams) {
        if (searchParams.isFilteredByUserOrg()) {
            DetachedCriteria criteria = DetachedCriteria.forClass(OrganisationalUnit.class);
            CriteriaBuilder.addEqRestriction(criteria, "hasItems", true);
            addFilteredByOrg(searchParams, criteria);
            return getExecutableCriteriaList(criteria, null);
        } else {
            List<OrganisationalUnit> allHasItemsOrgUnits = (List<OrganisationalUnit>) getNamedQuery("orgUnit.hasItems");
            log.debug("Found allHasItemsOrgUnits list of size " + allHasItemsOrgUnits.size() + ".");
            return allHasItemsOrgUnits;
        }
    }

    @Override
    public void refresh() {
        OrganisationalUnit client = findRootNode();
        log.debug("Refreshing caches for " + client.getName());
        cache.remove(DIVISION_NS + client.getName());
        loadDivisions(client);
        cache.remove(CURRENCIES_NS + client.getName());
        loadCurrencies(client);
        cache.remove(BANK_NAMES_NS + client.getName());
        loadBankNames(client);
    }

    private List<String> loadBankNames(OrganisationalUnit client) {
        log.debug("loadBankNames for " + client.getName());
        RuleAttribute bankNamesAttr = getRuleAttributeForClient("config", "banknames", client);
        List<String> list = new ArrayList<>();
        Collections.addAll(list, bankNamesAttr.getValue().split(","));
        cache.put(BANK_NAMES_NS + client.getName(), list);
        return list;
    }

    private List<Currency> loadCurrencies(OrganisationalUnit client) {
        log.debug("loadCurrencies for " + client.getName());
        RuleAttribute currencyAttr = getRuleAttributeForClient("config", "currencies", client);
        log.debug("Got rule attribute: " + currencyAttr);
        List<Currency> list = new ArrayList<>();
        for (String currencyCode : currencyAttr.getValue().split(",")) {
            list.add(new Currency(currencyCode));
        }
        log.debug("Adding currencies to cache for client " + client.getName());
        cache.put(CURRENCIES_NS + client.getName(), list);
        return Collections.unmodifiableList(list);
    }

    private List<OrganisationalUnit> loadDivisions(OrganisationalUnit client) {
        if (log.isDebugEnabled()) {
            log.debug("Going to retrieve all org units from the database");
        }
        try {
            List<OrganisationalUnit> collection = convert(findAll());
            if (log.isDebugEnabled()) {
                for (OrganisationalUnit organisationalUnit : collection) {
                    log.debug("\nFound Division: " + organisationalUnit.toString());
                }
            }
            cache.put(DIVISION_NS + client.getName(), collection);
            return collection;
        } catch (Exception e) {
            log.error("FATAL:Division Cache Load Failure", e);
        }
        throw new EntityAccessException("No divisions found!");
    }

    /**
     * Get the {@code RuleAttribute} associated with the supplied {@code ruleName}, {@code attributeName} and {@code client}. This method delegates to
     * {@link #getRuleWithCode(String, OrganisationalUnit)} and {@link #getAttributeWithCode(String, Rule)} but unlike those methods will throw an
     * exception if no rule or attribute is found.
     *
     * @param ruleName      The name of the {@code Rule} to lookup.
     * @param attributeName The name of the {@code RuleAttribute} to lookup.
     * @param client        The {@code OrganisationalUnit} containing the rule. Defaults to the logged in client if null.
     * @return The {@link RuleAttribute} associated with the {@code ruleName}, {@code attributeName} and {@code client}.
     * @throws IllegalStateException if no {@code Rule} or {@code RuleAttribute} is configured for the supplied data.
     * @see #getRuleWithCode(String, OrganisationalUnit)
     * @see #getAttributeWithCode(String, Rule)
     */
    private RuleAttribute getRuleAttributeForClient(String ruleName, String attributeName, OrganisationalUnit client) {
        client = client == null ? findRootNode() : client;
        Rule rule = getRuleWithCode(ruleName, client);
        if (rule == null) {
            throw new IllegalStateException("The rule '" + ruleName + "' must be configured for the client " + client.getName());
        }
        RuleAttribute attribute = getAttributeWithCode(attributeName, rule);
        if (attribute == null) {
            throw new IllegalStateException("The rule attribute '" + attributeName + "' must be configured for the client " + client.getName());
        }
        return attribute;
    }

    private boolean hasRuleWithCode(OrganisationalUnit orgUnit, String ruleCode) {
        return hasRuleWithCode(orgUnit, Collections.singleton(ruleCode));
    }

    private boolean hasRuleWithCode(OrganisationalUnit orgUnit, Collection<String> ruleCodes) {
        for (Rule rule : orgUnit.getRules()) {
            if (ruleCodes.contains(rule.getCode())) {
                return true;
            }
        }
        return false;
    }

    /**
     * For now convert the org units into divisions.
     *
     * The division data is populated from a flexible rule structure in the org
     * unit...absolute overkill?? Some might say ;)
     */
    private List<OrganisationalUnit> convert(List<OrganisationalUnit> orgUnits) {
        List<OrganisationalUnit> divisions = new ArrayList<>();
        for (OrganisationalUnit organisationalUnit : orgUnits) {
            if (isTreasuryDivision(organisationalUnit)) {
                // Add stuff to it
                convert(organisationalUnit);
                divisions.add(organisationalUnit);
            }
        }
        return divisions;
    }

    /**
     * check for a rule called 'treasury'. If not then ignore that particular orgUnit as we don't want to process it here.
     */
    private boolean isTreasuryDivision(OrganisationalUnit organisationalUnit) {
        Rule rule = getRuleWithCode("treasury", organisationalUnit);
        return rule != null;
    }

    /**
     * Converts an org unit to a organisationalUnit. TODO - we need to remove Division and just use org unit TODO - do we really need the generic rule
     * concept?
     */
    private void convert(OrganisationalUnit organisationalUnit) {
        if (organisationalUnit != null) {
            organisationalUnit.setForwardRateMargin(getRateMargin("forward_rate_margin", organisationalUnit));
            organisationalUnit.setSpotRateMargin(getRateMargin("spot_rate_margin", organisationalUnit));
            organisationalUnit.setRatesSource(getRatesFeed(organisationalUnit));
            organisationalUnit.setDaysFinance(0);
        }
    }

    private Percentage getRateMargin(String marginType, OrganisationalUnit organisationalUnit) {
        return new Percentage(new BigDecimal(getValue(marginType, "has_rate_margin", organisationalUnit)));
    }

    private RateSourceType getRatesFeed(OrganisationalUnit organisationalUnit) {
        return Enum.valueOf(RateSourceType.class, getValue("rate_source", "has_rate_source", organisationalUnit));
    }

    /**
     * Generic method to get an attribute given the attribute code and also the enclosing rule code.
     */
    private String getValue(String attributeCode, String ruleCode, OrganisationalUnit organisationalUnit) {
        Rule rule = getRuleWithCode(ruleCode, organisationalUnit);
        RuleAttribute attribute = getAttributeWithCode(attributeCode, rule);
        return attribute.getValue();
    }

    /**
     * Gets a rule matching a code.
     */
    private Rule getRuleWithCode(String code, OrganisationalUnit organisationalUnit) {
        if (organisationalUnit != null) {
            log.debug("getRuleWithCode invoked for code " + code + ", client is " + organisationalUnit.getName());
            for (Rule rule : organisationalUnit.getRules()) {
                if (rule.getCode().equals(code)) {
                    return rule;
                }
            }
            return getRuleWithCode(code, organisationalUnit.getParent());
        }
        return null;
    }

    /**
     * Gets an attribute from a rule.
     */
    private RuleAttribute getAttributeWithCode(String code, Rule rule) {
        for (RuleAttribute attribute : rule.getAttributes()) {
            log.debug("Attribute is " + attribute.getCode());
            if (attribute.getCode().equals(code)) {
                return attribute;
            }
        }
        return null;
    }

    @Override
    public void delete(String orgUnitCode) {
        OrganisationalUnit orgUnit = retrieve(orgUnitCode);
        delete(orgUnit);
    }

    @Override
    public List<OrganisationalUnit> findAllByEmployee(Employee employee, boolean onlyActive) {
        String hql = "select distinct s from OrganisationalUnit s join s.employees as employees where employees.id = :employeeId";
        if (onlyActive) {
            hql += " and s.active = true";
        }
        Query query = getSessionCustom().createQuery(hql);
        query.setParameter("employeeId", employee.getId());
        return query.list();
    }

    @Override
    public List<OrganisationalUnit> findExporters() {
        return (List<OrganisationalUnit>) getNamedQuery("orgUnit.exporter");
    }

    @Override
    public void updateOrganisationalUnitImage(Long id, byte[] array) {
        String hql = "update OrganisationalUnit set image = :image" +
                " where id = :id";

        //InputStream inputStream = new ByteArrayInputStream(array);

        Query query = getSessionCustom().createQuery(hql);
        query.setBinary("image", array);
        //query.setParameter("image", inputStream);
        query.setParameter("id", id);

        int i = query.executeUpdate();

        log.debug("Affected rows:  " + i);
    }

    @Override
    public OrganisationalUnit findByExternalReference(ExternalReference externalReference) {
        //obtain the OrganisationalUnit id
        String sql = "select ou.id from organisationalunit ou " +
                "join organisationalunit_externalreference ouer on ouer.organisationalunit_id = ou.id " +
                "join externalreference er on er.id = ouer.externalreferences_id " +
                "join integratedsystem isys on isys.id = er.integratedsystem_id " +
                "where er.referencevalue = '" + externalReference.getReferenceValue() + "' " +
                "and isys.code = '" + externalReference.getIntegratedSystem().getCode() + "'";

        SQLQuery sqlQuery = getSession().createSQLQuery(sql);

        BigInteger orgUnitId = (BigInteger) sqlQuery.uniqueResult();

        if (orgUnitId == null) {
            return null;
        }

        //obtain the OrganisationalUnit
        String hql = "select o from OrganisationalUnit o  where o.id = :orgUnitId";

        Query query = getSession().createQuery(hql);
        Long orgId = orgUnitId.longValue();
        query.setParameter("orgUnitId", orgId);

        return (OrganisationalUnit) query.list().get(0);
    }

    @Override
    public List<OrganisationalUnit> findAllActiveForEmployee(boolean onlyActive) {
        String hql = "select distinct s from OrganisationalUnit s join s.employees as employees";
        if (onlyActive) {
            hql += " where s.active = true";
        }
        Query query = getSessionCustom().createQuery(hql);
        return query.list();
    }

    @Override
    public List<OrganisationalUnit> findAllActiveCompany(boolean isCompany) {
        String hql = "select distinct s from OrganisationalUnit s WHERE s.isLegalEntity = true " +
                " AND s.active = true ";
        Query query = getSessionCustom().createQuery(hql);
        return query.list();
    }

    @Override
    public List<OrganisationalUnit> findAllCompany() {
        String hql = "select distinct s from OrganisationalUnit s WHERE s.isLegalEntity = true order by s.name" ;
        Query query = getSessionCustom().createQuery(hql);
        return query.list();
    }

    @Override
    public List<OrganisationalUnit> search(StaticDataSearch search) {
        DetachedCriteria criteria = getDetachedCriteria(search);
        SearchMetaParams searchMetaParams = search.getSearchMetaParams();
        if (searchMetaParams != null) {
            if (StringUtils.isEmpty(search.getSearchMetaParams().getOrderBy())) {
                searchMetaParams.setOrderBy("code");
            }
        }
        return getExecutableCriteriaList(criteria, search.getSearchMetaParams());
    }

    private DetachedCriteria getDetachedCriteria(StaticDataSearch search) {
        DetachedCriteria criteria = DetachedCriteria.forClass(OrganisationalUnit.class);

        if (search.getName() != null) {
            criteria.add(Restrictions.ilike("name", LIKE + search.getName() + LIKE));
        }
        Set<OrganisationalUnit> organisationalUnits = new HashSet<>();
        if (search.isFilteredByUserOrg()) {
            organisationalUnits = getUserOrganisationalUnits();
        } else if (!CollectionUtils.isEmpty(search.getOrganisationalUnitList())) {
            organisationalUnits = new HashSet<>(search.getOrganisationalUnitList());
        } else if (search.getOrganisationalUnit() != null) {
            OrganisationalUnit organisationalUnit = findByCode(search.getOrganisationalUnit().getCode());
            organisationalUnits = new HashSet<>();
            OrgUnitTraversal.getAllOrgUnitsDown(organisationalUnit, organisationalUnits);
        }

        if (!organisationalUnits.isEmpty()) {
            List<Long> collect = organisationalUnits.stream()
                    .map(PersistenceBase::getId).collect(Collectors.toList());
            criteria.add(Restrictions.in("id", collect));
        }
        if (search instanceof OrganisationalUnitSearch organisationalUnitSearch) {
            if (organisationalUnitSearch.getVatRegistrationNumber() != null) {
                criteria.add(Restrictions.ilike("vatRegistrationNumber", LIKE + organisationalUnitSearch.getVatRegistrationNumber()
                        + LIKE));
            }
            if (organisationalUnitSearch.getCustomsCode() != null) {
                criteria.add(Restrictions.ilike("customsCode", LIKE + organisationalUnitSearch.getCustomsCode() + LIKE));
            }
            if (organisationalUnitSearch.getHasItems() != null) {
                criteria.add(Restrictions.eq("hasItems", organisationalUnitSearch.getHasItems().booleanValue()));
            }
            if (organisationalUnitSearch.getPlaceOrders() != null) {
                criteria.add(Restrictions.eq("placeOrders", organisationalUnitSearch.getPlaceOrders().booleanValue()));
            }
        }
        if (search.getCode() != null ) {

            criteria.add(Restrictions.ilike("code", LIKE + search.getCode() + LIKE));
        }
        if (search.getType() != null) {
            criteria.add(Restrictions.eq("type", search.getType()));
        }

        return criteria;
    }

    @Override
    public long count(StaticDataSearch search) {
        DetachedCriteria criteria = getDetachedCriteria(search);
        return getExecutableCriteriaCount(criteria);
    }

    @Override
    public void save(OrganisationalUnit orgUnit) {
        //This method will be called from OrgUnitExternalKeyValidator
//        validatePBExternalReferences(orgUnit.getClass(), orgUnit.getExternalReferences(), null);
        super.save2(orgUnit);
    }

    @Override
    public Serializable save2(OrganisationalUnit orgUnit) {
        //This method will be called from OrgUnitExternalKeyValidator
//        validatePBExternalReferences(orgUnit.getClass(), orgUnit.getExternalReferences(), null);
        return super.save2(orgUnit);
    }

    @Override
    public void update(OrganisationalUnit orgUnit) {
//        This method will be called from OrgUnitExternalKeyValidator
//        validatePBExternalReferences(orgUnit.getClass(), orgUnit.getExternalReferences(), orgUnit.getId());
        super.update(orgUnit);
    }

    public void validate(OrganisationalUnit orgUnit) {
        validatePBExternalReferences(orgUnit.getClass(), orgUnit.getExternalReferences(), orgUnit.getId());
    }

    @Override
    @Transactional(readOnly = true)
    public List<OrganisationalUnit> findAll(SearchParams searchParams) {
        DetachedCriteria criteria = DetachedCriteria.forClass(OrganisationalUnit.class);
        addFilteredByOrg(searchParams, criteria);
        criteria.add(Restrictions.eq("type", OrganisationalUnit.Type.FULL));
        CriteriaBuilder.applySearchParams(criteria, searchParams);
        @SuppressWarnings("unchecked")
        List<OrganisationalUnit> results = criteria.getExecutableCriteria(getSessionCustom()).list();
        return results;
    }

    private void addFilteredByOrg(SearchParams searchParams, DetachedCriteria criteria) {
        if (searchParams.isFilteredByUserOrg()) {
            List<Long> collect = getUserOrganisationalUnits().stream()
                    .map(PersistenceBase::getId).collect(Collectors.toList());
            criteria.add(Restrictions.in("id", collect));
        }
    }

    @Override
    public List<OrganisationalUnit> getOrganisationalUnitsForEvents() {
        javax.persistence.criteria.CriteriaBuilder builder = getSessionCustom().getCriteriaBuilder();
        javax.persistence.criteria.CriteriaQuery criteria = builder.createQuery(OrganisationalUnit.class);
        Root root = criteria.from(OrganisationalUnit.class);
        criteria.select(root);
        criteria.where(builder.equal(root.get("useOnIntegrationEvents"), true));
        org.hibernate.query.Query query = getSessionCustom().createQuery(criteria);

        return query.getResultList();
    }

    @Override
    public EventOrganisationalUnit getOrganisationalUnitsForEventGroup(EventGroup eventGroup, User integratedUser) {
        javax.persistence.criteria.CriteriaBuilder builder = getSessionCustom().getCriteriaBuilder();
        javax.persistence.criteria.CriteriaQuery criteria = builder.createQuery(EventOrganisationalUnit.class);
        Root root = criteria.from(EventOrganisationalUnit.class);
        root.fetch("integratedUser", JoinType.LEFT);
        root.fetch("organisationalUnits", JoinType.LEFT);
        criteria.select(root);
        criteria.where(builder.equal(root.get("eventGroup"), eventGroup), builder.equal(root.get("integratedUser"), integratedUser));
        org.hibernate.query.Query query = getSessionCustom().createQuery(criteria);

        return (EventOrganisationalUnit) query.uniqueResult();
    }

    @Override
    public List<OrganisationalUnit> getImporterOrganisationalUnits() {
        javax.persistence.criteria.CriteriaBuilder builder = getSessionCustom().getCriteriaBuilder();
        javax.persistence.criteria.CriteriaQuery criteria = builder.createQuery(OrganisationalUnit.class);
        Root root = criteria.from(OrganisationalUnit.class);
        criteria.select(root);
        criteria.where(builder.isNotNull(root.get("vatRegistrationNumber")), builder.isNotNull(root.get("customsCode")));
        org.hibernate.query.Query query = getSessionCustom().createQuery(criteria);

        List resultList = query.getResultList();
        return resultList;
    }

    @Override
    public List<OrganisationalUnit> getOrganisationalUnits(List<Long> ids) {
        javax.persistence.criteria.CriteriaBuilder builder = getSessionCustom().getCriteriaBuilder();
        javax.persistence.criteria.CriteriaQuery criteria = builder.createQuery(OrganisationalUnit.class);
        Root root = criteria.from(OrganisationalUnit.class);
        criteria.select(root);
        criteria.where(root.get("id").in(ids));
        org.hibernate.query.Query query = getSessionCustom().createQuery(criteria);

        List resultList = query.getResultList();
        return resultList;
    }

    @Override
    public String getNamesByIds(List<Long> ids) {
        String sql = "SELECT STRING_AGG(ou.name, ',') FROM organisationalunit ou WHERE ou.id IN (:ids)";
            NativeQuery<String> query = getCurrentSession().createNativeQuery(sql);
            query.setParameterList("ids", ids);
            return  query.uniqueResult();
    }

    @Override
    public Optional<OrganisationalUnit> findWithRules(Long id) {
        return getCurrentSession()
                .createQuery(
                        """
                        SELECT ou
                        FROM OrganisationalUnit ou
                        LEFT JOIN FETCH ou.rules
                        WHERE ou.id = :id
                        """,
                        OrganisationalUnit.class
                )
                .setParameter("id", id)
                .uniqueResultOptional();
    }

    @Override
    public Optional<Long> findOrgUnitIdWithRuleInHierarchy(Long startId, String ruleCode) {

        String sql = """
        WITH RECURSIVE ou_tree AS (
            SELECT id, parent_id, 0 AS depth
            FROM organisationalunit
            WHERE id = :startId

            UNION ALL

            SELECT p.id, p.parent_id, ot.depth + 1
            FROM organisationalunit p
            JOIN ou_tree ot ON p.id = ot.parent_id
        )
        SELECT r.orgunit_id
        FROM organisationrule r
        JOIN ou_tree ot ON r.orgunit_id = ot.id
        WHERE r.code = :ruleCode
        ORDER BY ot.depth
        LIMIT 1
        """;

        Object result = getCurrentSession()
                .createNativeQuery(sql)
                .setParameter("startId", startId)
                .setParameter("ruleCode", ruleCode)
                .uniqueResult();

        if (result == null) {
            return Optional.empty();
        }

        // Convert to Long safely
        return Optional.of(((Number) result).longValue());
    }

    @Override
    public Optional<String> findRuleAtrributeInHierarchy(Long orgUnitStartId, String ruleCode, String ruleAtrribute) {

        String sql = """
        WITH RECURSIVE ou_tree AS (
            SELECT id, parent_id, 0 AS depth
            FROM organisationalunit
            WHERE id = :startId

            UNION ALL

            SELECT p.id, p.parent_id, ot.depth + 1
            FROM organisationalunit p
            JOIN ou_tree ot ON p.id = ot.parent_id
        )
                SELECT a.value FROM organisationrule r join ruleattribute a on (a.rule_id=r.id) 
                       JOIN ou_tree ot ON r.orgunit_id = ot.id
                               WHERE r.code = :ruleCode and a.code=:attributeCode
                               ORDER BY ot.depth
                               LIMIT 1
        """;

        Object result = getCurrentSession()
                .createNativeQuery(sql)
                .setParameter("startId", orgUnitStartId)
                .setParameter("ruleCode", ruleCode)
                .setParameter("attributeCode", ruleAtrribute)
                .uniqueResult();

        if (result == null) {
            return Optional.empty();
        }

        // Convert to Long safely
        return Optional.of(result.toString());
    }

    @Override
    public Map<Long, String> getTierForOrgUnits(Set<Long> orgUnitIds, String targetTierCode) {

        String sql = "WITH RECURSIVE ou_tree AS (" +
                "  SELECT id, parent_id, id AS original_id, 0 AS depth " +
                "  FROM organisationalunit " +
                "  WHERE id IN (:ids) " +
                "  UNION ALL " +
                "  SELECT p.id, p.parent_id, ot.original_id, ot.depth + 1 " +
                "  FROM organisationalunit p " +
                "  JOIN ou_tree ot ON p.id = ot.parent_id " +
                "  WHERE ot.depth < 10 " +
                ") " +
                "SELECT DISTINCT ON (ot.original_id) " +
                "  ot.original_id, u.name " +
                "FROM ou_tree ot " +
                "JOIN organisationalunit u ON u.id = ot.id " +
                "JOIN organisationalunittier r ON u.tier_code = r.code " +
                "WHERE r.code = :tierCode " + // Dynamic tier (e.g., 'BusinessUnit')
                "ORDER BY ot.original_id, ot.depth ASC";

        Query query = getCurrentSession().createNativeQuery(sql);
        query.setParameter("ids", orgUnitIds);
        query.setParameter("tierCode", targetTierCode);

        @SuppressWarnings("unchecked")
        List<Object[]> results = query.getResultList();

        return results.stream().collect(Collectors.toMap(
                row -> ((Number) row[0]).longValue(), // source_ou_id
                row -> (String) row[1]               // business_unit_name
        ));
    }

}