AccessionServiceImpl.java

/*
 * Copyright 2018 Global Crop Diversity Trust
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.genesys.server.service.impl;

import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Optional;
import java.util.Set;
import java.util.UUID;
import java.util.function.Consumer;
import java.util.regex.Matcher;
import java.util.stream.Collectors;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.genesys.blocks.auditlog.service.AuditTrailService;
import org.genesys.filerepository.model.ImageGallery;
import org.genesys.filerepository.model.QImageGallery;
import org.genesys.filerepository.model.RepositoryFolder;
import org.genesys.filerepository.service.ImageGalleryService;
import org.genesys.server.api.Slice;
import org.genesys.server.component.aspect.DatasetAndSubsetCountAspect;
import org.genesys.server.exception.NotFoundElement;
import org.genesys.server.exception.SearchException;
import org.genesys.server.model.PublishState;
import org.genesys.server.model.dataset.Dataset;
import org.genesys.server.model.dataset.QDataset;
import org.genesys.server.model.genesys.Accession;
import org.genesys.server.model.genesys.AccessionData;
import org.genesys.server.model.genesys.AccessionId;
import org.genesys.server.model.genesys.QAccession;
import org.genesys.server.model.genesys.QAccessionId;
import org.genesys.server.model.genesys.Taxonomy2;
import org.genesys.server.model.impl.AccessionIdentifier3;
import org.genesys.server.model.impl.Country;
import org.genesys.server.model.impl.FaoInstitute;
import org.genesys.server.model.impl.QSubset;
import org.genesys.server.model.impl.Subset;
import org.genesys.server.persistence.AccessionIdRepository;
import org.genesys.server.persistence.AccessionRepository;
import org.genesys.server.persistence.SubsetRepository;
import org.genesys.server.persistence.dataset.DatasetRepository;
import org.genesys.server.service.AccessionService;
import org.genesys.server.service.DatasetService;
import org.genesys.server.service.ElasticsearchService;
import org.genesys.server.service.FilterConstants;
import org.genesys.server.service.GeoService;
import org.genesys.server.service.InstituteService;
import org.genesys.server.service.SubsetService;
import org.genesys.server.service.TaxonomyService;
import org.genesys.server.service.filter.AccessionFilter;
import org.genesys.server.service.filter.AppliedFiltersConverter;
import org.genesys.server.service.worker.AccessionProcessor;
import org.genesys.spring.TransactionHelper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.context.annotation.Lazy;
import org.springframework.core.task.TaskExecutor;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.ReflectionUtils;

import com.google.common.collect.Lists;
import com.google.common.collect.Sets;
import com.querydsl.core.BooleanBuilder;
import com.querydsl.core.Tuple;
import com.querydsl.core.types.EntityPath;
import com.querydsl.core.types.Expression;
import com.querydsl.core.types.Path;
import com.querydsl.core.types.dsl.BooleanExpression;
import com.querydsl.core.types.dsl.CollectionPathBase;
import com.querydsl.jpa.impl.JPAQuery;
import com.querydsl.jpa.impl.JPAQueryFactory;

/**
 * Accession services.
 */
@Service
@Transactional(readOnly = true)
public class AccessionServiceImpl implements AccessionService {

	public static final Logger LOG = LoggerFactory.getLogger(AccessionServiceImpl.class);

	private final Set<String> suggestions = Sets.newHashSet("aegis", "crop.shortName", "institute.code", "taxonomy.genus",
		"taxonomy.genusSpecies", "taxonomy.species", "countryOfOrigin.code3", "historic", "sampStat", "available", "mlsStatus", "sgsv",
		"storage", "imageCount", "curationType");

	/** The accession repository. */
	@Autowired
	private AccessionRepository accessionRepository;

	@Autowired(required = false)
	private ElasticsearchService elasticsearchService;

	@Autowired
	private JPAQueryFactory jpaQueryFactory;

	@Lazy
	@Autowired
	private DatasetService datasetService;

	@Autowired
	private SubsetService subsetService;

	@Autowired
	private ImageGalleryService imageGalleryService;

	@Autowired
	private AuditTrailService auditService;

	@Autowired
	private InstituteService instituteService;

	@Autowired
	private TaxonomyService taxonomyService;

	@Autowired
	private GeoService geoService;

	@Autowired
	private DatasetRepository datasetRepository;

	@Autowired
	private SubsetRepository subsetRepository;

	@Autowired
	private AccessionIdRepository accessionIdRepository;

	@Autowired
	private DatasetAndSubsetCountAspect datasetAndSubsetCountAspect;

	@Autowired
	private TaskExecutor taskExecutor;

	@Autowired @Lazy
	private AccessionProcessor accessionProcessor;

	private static final int LOAD_CHUNK_SIZE = 200;

	private static final String NOCROP = "NOCROP";

	private <T extends AccessionData> T lazyLoad(T accession) {
		if (accession != null) {
			AccessionId accessionId = accession.getAccessionId();

			if (accessionId.getStorage() != null)
				accessionId.getStorage().size();
			if (accessionId.getAliases() != null)
				accessionId.getAliases().size();
			if (accessionId.getBreederCode() != null)
				accessionId.getBreederCode().size();
			if (accessionId.getBreederName() != null)
				accessionId.getBreederName().size();
			if (accessionId.getDuplSite() != null)
				accessionId.getDuplSite().size();
			if (accessionId.getRemarks() != null)
				accessionId.getRemarks().size();

			if (accession.getCountryOfOrigin() != null)
				accession.getCountryOfOrigin().getId();
			if (accession.getCrop() != null)
				accession.getCrop().getId();

			if (accessionId.getColl() != null) {
				if (accessionId.getColl().getCollCode() != null)
					accessionId.getColl().getCollCode().size();
				if (accessionId.getColl().getCollInstAddress() != null)
					accessionId.getColl().getCollInstAddress().size();
				if (accessionId.getColl().getCollName() != null)
					accessionId.getColl().getCollName().size();
			}

			try {
				accessionId.getClimate().getTileIndex();
			} catch (Throwable e) {
				LOG.debug("Climate for tile does not exist. tileIndex=" + accessionId.getTileIndex());
				accessionId.setClimate(null);
			}

			if (accessionId.getPdci() != null)
				accessionId.getPdci().getId();
		}
		return accession;
	}

	@Override
	public Accession getByUuid(UUID uuid) {
		return lazyLoad(accessionRepository.findOneByUuid(uuid));
	}

	@Override
	public Accession getByDoi(String doi) {
		return lazyLoad(accessionRepository.findByDoi(doi));
	}

	@Override
	public AccessionDetails getAccessionDetails(Accession accession) {
		if (accession == null) {
			throw new NotFoundElement();
		}
		accession = lazyLoad(accessionRepository.findOneByUuid(accession.getUuid()));

		List<Dataset> datasets = datasetService.listByAccession(accession);
		List<Subset> subsets = subsetService.listByAccession(accession);

		AccessionDetails accessionDetails = new AccessionDetails();
		accessionDetails.details = accession;
		if (accession.getAccessionId().getPdci() != null) accession.getAccessionId().getPdci().getId(); // init
		accessionDetails.pdci = accession.getAccessionId().getPdci();
//		if (accessionDetails.pdci != null) accessionDetails.pdci.getId(); // initialize
		accessionDetails.datasets = datasets;
		accessionDetails.subsets = subsets;

		RepositoryFolder folder = accession.getAccessionId().getRepositoryFolder();
		if (folder != null) {
			accessionDetails.files = folder.getFiles().stream().filter((file) -> !file.getContentType().contains("image/")).collect(Collectors.toList());

			ImageGallery imageGallery = imageGalleryService.getImageGallery(folder);
			if (imageGallery != null) {
				if (imageGallery.getImages() != null) {
					imageGallery.getImages().size();
				}
				accessionDetails.imageGallery = imageGallery;
			}
		}

		return accessionDetails;
	}

	@Override
	@PreAuthorize("isAuthenticated()")
	public AccessionAuditLog getAuditLog(Accession accession) {
		AccessionAuditLog auditLog = new AccessionAuditLog();

		auditLog.auditAccession = auditService.auditLogs(accession);
		auditLog.auditAccessionId = auditService.auditLogs(accession.getAccessionId());
		auditLog.auditAccessionCollect = auditService.auditLogs(accession.getAccessionId().getColl());
		// TODO Update the UI
		//auditLog.auditAccessionGeo = auditService.auditLogs(accession.getAccessionId().getGeo());

		return auditLog;
	}

	@Override
	public Map<UUID, AccessionIdentifier3> toUUID(List<? extends AccessionIdentifier3> identifiers) {

		if (CollectionUtils.isEmpty(identifiers)) {
			return Collections.emptyMap();
		}

		Map<UUID, AccessionIdentifier3> res = new HashMap<>();

		final int chunkSize = 200;

		for (int fromIndex = 0; fromIndex < identifiers.size(); fromIndex += chunkSize) {
			List<Accession> foundAccessions = accessionRepository.find(true, identifiers.subList(fromIndex, Math.min(identifiers.size(), fromIndex + chunkSize)));
			for (Accession accession : foundAccessions) {
				Optional<? extends AccessionIdentifier3> toPut = identifiers.stream().filter(id -> id.getAccessionNumber().equals(accession.getAccessionNumber()) && id.getGenus()
					.equals(accession.getGenus()) && id.getHoldingInstitute().equals(accession.getInstCode())).findFirst();

				toPut.ifPresent(accessionIdentifier3 -> res.put(accession.getUuid(), accessionIdentifier3));
			}
		}

		return res;
	}

	@Override
	public List<Accession> forUuids(Set<UUID> uuids) {

		if (CollectionUtils.isEmpty(uuids)) {
			return Collections.emptyList();
		}

		List<UUID> uuidList = new ArrayList<>(uuids);
		List<Accession> accessions = new ArrayList<>(uuids.size());

		for (int fromIndex = 0; fromIndex < uuids.size(); fromIndex+= LOAD_CHUNK_SIZE){
			List<UUID> chunk = uuidList.subList(fromIndex, Math.min(uuids.size(), fromIndex+LOAD_CHUNK_SIZE));
			accessions.addAll((List<Accession>) accessionRepository.findAll(QAccession.accession.accessionId().uuid.in(chunk)));
		}

		accessions.forEach(this::lazyLoad);

		return accessions;
	}

	@Override
	// @Cacheable(value = "accessionCount", keyGenerator = "shortFilterKeyGenerator")
	@Cacheable(cacheNames = "apiResponses.accession.count", unless = "#result < 10000")
	public long countAccessions(AccessionFilter filter) throws SearchException {
		long total;

		if (elasticsearchService != null) {
			total = elasticsearchService.count(Accession.class, filter);
			if (total < 10000 && ! filter.isFulltextQuery()) {
				// If total is below 10K and no full-text query, use actual count
				total = accessionRepository.count(filter);
			}
		} else {
			total = accessionRepository.count(filter);
		}

		return total;
	}

	@Override
	public UUID uuidFromId(long id) {
		var uuid = jpaQueryFactory.select(QAccession.accession.accessionId().uuid)
			.from(QAccession.accession).where(QAccession.accession.id.eq(id)).fetchOne();

		if (uuid == null) {
			throw new NotFoundElement("No accession with id " + id);
		}

		return uuid;
	}

	/**
	 * Converts list of accession IDs to respective accession UUIDs in batches of
	 * 200.
	 *
	 * @see org.genesys.server.service.AccessionService#uuidsFromIds(java.util.List)
	 */
	@Override
	public List<UUID> uuidsFromIds(List<Long> ids) {

		List<UUID> uuids = new ArrayList<>();
		for (int fromIndex = 0; fromIndex < ids.size(); fromIndex += LOAD_CHUNK_SIZE) {
			var idsSubList = ids.subList(fromIndex, Math.min(fromIndex + LOAD_CHUNK_SIZE, ids.size()));
			uuids.addAll(jpaQueryFactory.select(QAccession.accession.accessionId().uuid)
				.from(QAccession.accession).where(QAccession.accession.id.in(idsSubList)).fetch());
		}
		return uuids;
	}

	@Override
	public List<UUID> uuidsFromAcceNumbers(String instCode, List<String> acceNumbers) {

		QAccession accession = QAccession.accession;

		List<UUID> uuids = new ArrayList<>();
		for (int fromIndex = 0; fromIndex < acceNumbers.size(); fromIndex += LOAD_CHUNK_SIZE) {
			BooleanBuilder predicate = new BooleanBuilder();
			if (instCode != null) {
				predicate.and(accession.instituteCode.eq(instCode));
			}
			predicate.and(accession.accessionNumber.in(acceNumbers.subList(fromIndex, Math.min(fromIndex + LOAD_CHUNK_SIZE, acceNumbers.size()))));

			List<Accession> accessions = Lists.newArrayList(accessionRepository.findAll(predicate));

			uuids.addAll(accessions.stream().map(AccessionData::getAccessionId).map(AccessionId::getUuid).collect(Collectors.toList()));
		}
		return uuids;
	}

	@Override
	public UUID uuidFromAcceNumber(String instCode, String acceNumber) {
		Accession accession = instCode != null ?
				accessionRepository.findByInstituteCodeAndAccessionNumber(instCode, acceNumber)
				: accessionRepository.findOne(QAccession.accession.accessionNumber.eq(acceNumber)).orElse(null);

		if (accession == null) {
			throw new NotFoundElement("No accession with instCode " + instCode + " and acceNumb: " + acceNumber);
		}

		return accession.getUuid();
	}

	@Override
	public Set<Long> filterAvailableForDistributionByUuid(Set<UUID> accessionUuids) {
		if (accessionUuids == null || accessionUuids.size() == 0) {
			return Collections.emptySet();
		}

		QAccession accession = QAccession.accession;

		BooleanBuilder predicate = new BooleanBuilder();
		predicate.and(accession.accessionId().uuid.in(accessionUuids));
		predicate.and(accession.historic.isFalse());
		predicate.and(accession.available.isNull().or(accession.available.isTrue()));
		predicate.and(accession.institute().allowMaterialRequests.isTrue());

		JPAQuery<Long> query = jpaQueryFactory.select(accession.id)
				.from(accession)
				.where(predicate);

		return new HashSet<>(query.fetch());
	}

	@Override
	public Set<Long> filterAvailableById(Set<Long> accessionIds) {
		if (accessionIds == null || accessionIds.size() == 0) {
			return Collections.emptySet();
		}

		QAccession accession = QAccession.accession;

		BooleanBuilder predicate = new BooleanBuilder();
		predicate.and(accession.historic.isFalse());
		predicate.and(accession.available.isNull().or(accession.available.isTrue()));
		predicate.and(accession.id.in(accessionIds));

		JPAQuery<Long> query = jpaQueryFactory.select(accession.id)
			.from(accession)
			.where(predicate);

		return new HashSet<>(query.fetch());
	}

	/*
	 * (non-Javadoc)
	 * @see
	 * org.genesys.server.service.AccessionService#list(org.genesys.server.service
	 * .filter.AccessionFilter, org.springframework.data.domain.Pageable)
	 */
	@Override
	// @Cacheable(value = "apiResponses.accessionApi1.list", unless = "#result == null", keyGenerator = "shortFilterKeyGenerator")
	public Page<Accession> list(AccessionFilter filter, Pageable page) throws SearchException {
		LOG.debug("Filtering for: {}", filter);
		Page<Accession> result = findAll(filter, page);
		result.getContent().forEach(accession -> lazyLoad(accession));
		return result;
	}

	@Override
	// @Cacheable(value = "apiResponses.accessionApi1.list", unless = "#result == null", keyGenerator = "shortFilterKeyGenerator")
	public Page<Accession> listFast(AccessionFilter filter, Pageable page) throws SearchException {
		LOG.debug("Filtering for: {}", filter);
		Page<Accession> result = findAll(filter, page);
		return result;
	}

	private class QueryContext {
		private final Set<String> BANNED_PARTS = Set.of("wait", "hashCode", "_super", "accessionId");

		private QAccession a = QAccession.accession;
		private QAccessionId aId;
		private JPAQuery<Tuple> query = null;
		private Expression<?> root = QAccession.accession;
		private List<Expression<?>> exprs = new ArrayList<>();
		private Map<EntityPath<?>, Path<?>> leftJoins = new HashMap<>();
		private Map<String, Expression<?>> expressionsByFieldName = new LinkedHashMap<>();
		private Map<Expression<?>, QueryContext> listStuff = new LinkedHashMap<>();
		private Map<String, String> aliasByFieldName = new HashMap<>();

		public QueryContext() {
			this.query = jpaQueryFactory.from(a).select(a.id, a.id); // The select is to generate a tuple
			this.aId = new QAccessionId("AId");
			this.query.innerJoin(a.accessionId(), this.aId);
			this.exprs.add(a.id);
		}

		public QueryContext(QueryContext parentContext) {
			this.query = jpaQueryFactory.from(a).select(a.id, a.id); // The select is to generate a tuple
			this.aliasByFieldName = parentContext.aliasByFieldName; // Use the same map!
			this.aId = new QAccessionId("AId"); // Join AccessionId firstly (it can be used in left joins)
			this.query.innerJoin(a.accessionId(), this.aId);
			// Need to add joins
			parentContext.leftJoins.forEach((entity, path) -> {
				var alias = this.leftJoins.computeIfAbsent(entity, join -> aliasJoin(this.query, (EntityPath) entity, entity.getClass(), join.getMetadata().getName()));
				LOG.warn("Copied left-join {} {}", alias.getClass(), alias.getMetadata().getName());
			});
			this.exprs.add(a.id);
		}


		private QueryContext buildQueryContext(List<String> select) {
			var context = this;

			select.forEach(fieldName -> {
				LOG.info("Selected field accession.{}", fieldName);
				var fieldWithAlias = fieldName.split("\\s+");
				var field = fieldWithAlias[0];
				if (fieldWithAlias.length > 1) {
					context.aliasByFieldName.put(fieldName, fieldWithAlias[1]);
				}
				var parts = field.split("\\.");

				var currentContext = context;
				Expression<?> root = currentContext.root;
				Expression<?> expr = null;
				String aliasName = "";
				for (int i = 0; i < parts.length; i++) {
					var part = parts[i];
					aliasName += part;

					LOG.debug("Looking for part {}", part);
					if (BANNED_PARTS.contains(part)) {
						expr = null; // Reset to null
						LOG.warn("Path not allowed {}", part);
						break;
					}
					try {
						var property = ReflectionUtils.findField(root.getClass(), part);
						if (property != null && property.canAccess(root) && Expression.class.isAssignableFrom(property.getType())) {
							var x = property.get(root);
							if (x instanceof CollectionPathBase) {
								var setPath = (CollectionPathBase<?, ?, ?>) x;
								expr = null;
								LOG.debug("{}: Need a subcontext for {} {} {}", fieldName, part, x, x.getClass());
								var thisContext = currentContext;
								currentContext = thisContext.listStuff.computeIfAbsent(setPath, listRoot -> new QueryContext(thisContext));
								expr = setPath.any();
								root = setPath.any();
								continue;
							} else if (x instanceof Expression) {
								expr = (Expression<?>) x;
								root = expr;
								LOG.debug("{}: For {} found path {} {}", fieldName, part, x, x.getClass());
								continue;
							} else {
								LOG.debug("Skipping: {} {}", x, x.getClass());
								LOG.debug("{}: For {} no property on {}", fieldName, part, root);
							}
						}
						var accessor = ReflectionUtils.findMethod(root.getClass(), part);
						if (accessor != null && accessor.canAccess(root) && Expression.class.isAssignableFrom(accessor.getReturnType())) {
							var x = accessor.invoke(root);
							if (x instanceof CollectionPathBase) {
								var setPath = (CollectionPathBase<?, ?, ?>) x;
								expr = null;
								LOG.debug("{}: Need a subcontext for {} {} {}", fieldName, part, x, x.getClass());
								var thisContext = currentContext;
								currentContext = thisContext.listStuff.computeIfAbsent(setPath, listRoot -> new QueryContext(thisContext));
								expr = setPath.any();
								root = setPath.any();
								continue;
							} else if (x instanceof EntityPath) {
								var entityPath = (EntityPath) x;
								var joinName = aliasName;
								var thisContext = currentContext;
								var alias = thisContext.leftJoins.computeIfAbsent(entityPath, join -> aliasJoin(thisContext.query, (EntityPath) join, join.getClass(), joinName));
								expr = null;
								root = alias;
								LOG.debug("{}: For {} found left-joined entity {} {}", fieldName, part, x, x.getClass());
								continue;
							} else if (x instanceof Expression) {
								expr = (Expression<?>) x;
								root = expr;
								LOG.debug("{}: For {} found target {} {}", fieldName, part, x, x.getClass());
								continue;
							} else {
								LOG.debug("Skipping: {}", x);
								LOG.debug("{}: For {} no accessor on {}", fieldName, part, root);
							}
						}
						if (i == 0 && ! Objects.equals(root, currentContext.aId)) {
							LOG.debug("Checking accessionId!");
							// Check accessionId
							root = currentContext.aId;
							i--; // Repeat
							aliasName = "";
							continue;
						} else {
							LOG.warn("Didn't find property or accessor for {} in {}", part, root);
							break;
						}
					} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
						LOG.warn("Error looking for {} in {}: {}", part, root, e.getMessage(), e);
						throw new RuntimeException("Error looking for " + part + " in " + root, e);
					}
				}

				if (expr != null) {
					LOG.debug("For {} found {}", fieldName, expr);
					if (null == currentContext.expressionsByFieldName.putIfAbsent(fieldName, expr)) {
						currentContext.exprs.add(expr);
					} else {
						LOG.warn("Duplicate expression found for {}", fieldName);
					}
				}
			});

			context.query = context.query.select(context.exprs.toArray(new Expression<?>[] {}));
			context.listStuff.forEach((root, subContext) -> {
				subContext.query = subContext.query.select(subContext.exprs.toArray(new Expression<?>[] {}));
			});

			return context;
		}
	}

	@Override
	public void query(AccessionFilter filter, List<String> select, Pageable page, boolean useMcpdStyle, Consumer<Map<String, ?>> resultHandler) throws Exception {
		if (CollectionUtils.isEmpty(select)) {
			return;
		}

		LOG.info("Querying fields {} using: {}", select, filter);

		var context = new QueryContext().buildQueryContext(select);

		if (CollectionUtils.isEmpty(context.exprs) && context.listStuff.isEmpty()) {
			return;
		}

		queryRun(context, select, filter, page, useMcpdStyle, resultHandler);
	}

	@Override
	public Page<Map<String, ?>> query(AccessionFilter filter, List<String> select, Pageable page, boolean useMcpdStyle) throws Exception {
		if (CollectionUtils.isEmpty(select)) {
			return Page.empty(page);
		}

		LOG.info("Querying fields {} using: {}", select, filter);

		var context = new QueryContext().buildQueryContext(select);

		if (CollectionUtils.isEmpty(context.exprs) && context.listStuff.isEmpty()) {
			return Page.empty(page);
		}

		var mapped = new LinkedList<Map<String, ?>>();
		queryRun(context, select, filter, page, useMcpdStyle, mapped::add);

		long total;
		if (filter.isFulltextQuery()) {
			total = elasticsearchService.count(Accession.class, filter);
		} else {
			var countQuery = jpaQueryFactory.select(QAccession.accession.id).from(QAccession.accession).where(filter.buildPredicate());
//			total = LoggerHelper.withSqlLogging(countQuery::fetchCount);
			total = countQuery.fetchCount();
		}
		LOG.info("Count is {}", total);

		return new PageImpl<>(mapped, page, total);
	}

	private <T> Path<?> aliasJoin(JPAQuery<?> query, EntityPath<T> expr, Class<T> type, String part) {
		try {
			var alias = (EntityPath<T>) type.getDeclaredConstructor(String.class).newInstance(part);
			LOG.debug("Created alias for left-join {} type {}", part, alias.getClass());
			query.leftJoin(expr, alias);
			return alias;
		} catch (Throwable e) {
			return null;
		}
	}

	private void queryRun(QueryContext context, List<String> select, AccessionFilter filter, Pageable page, boolean useMcpdStyle, Consumer<Map<String, ?>> resultHandler) throws Exception {
		accessionProcessor.processIds(filter, page, (batchOfAccessionIds) -> {

			LOG.info("Executing query with {} and {}", context.exprs, context.expressionsByFieldName);

			context.query.getMetadata().clearWhere(); // Clear previous where predicates!
			context.query.where(context.a.id.in(batchOfAccessionIds)); // For the accession ids in this batch

			List<Tuple> res;
			try {
//				res = LoggerHelper.withSqlLogging(context.query::fetch);
				res = context.query.fetch();
			} catch (Exception e) {
				throw new RuntimeException(e);
			}
	
			var mappedById = new LinkedHashMap<Long, Map<String, Object>>(); // Lookup by ID
			res.forEach(tuple -> {
				LOG.trace("Making map from {} {}", tuple.size(), Arrays.toString(tuple.toArray()));
				var o = new LinkedHashMap<String, Object>(20);
				select.forEach(selectedField -> {
					var expr = context.expressionsByFieldName.get(selectedField);
					var alias = context.aliasByFieldName.getOrDefault(selectedField, selectedField);
					if (expr != null) {
						o.putIfAbsent(alias, tuple.get(expr));
					} else {
						o.putIfAbsent(alias, null);
					}
				});
				mappedById.put(tuple.get(0, Long.class), o);
			});

			LOG.info("Have {} accessionIds", mappedById.size());

			context.listStuff.forEach((root, subContext) -> {
				LOG.info("Have subcontext on {} with fields {}", root, subContext.expressionsByFieldName.values());
				LOG.info("Executing query with {} and {}", subContext.exprs, subContext.expressionsByFieldName);

				subContext.query.getMetadata().clearWhere(); // Clear previous where predicates!
				subContext.query.where(subContext.a.id.in(batchOfAccessionIds)); // For accession IDs in this batch

				List<Tuple> contextRes;
				try {
//					contextRes = LoggerHelper.withSqlLogging(subContext.query::fetch);
					contextRes = subContext.query.fetch();
				} catch (Exception e) {
					throw new RuntimeException(e);
				}

				contextRes.forEach(tuple -> {
					LOG.trace("Making array from {}", Arrays.toString(tuple.toArray()));
					var o = mappedById.get(tuple.get(0, Long.class));
					if (o == null) {
						LOG.debug("This should not happen!");
					} else {
						subContext.expressionsByFieldName.keySet().forEach(fieldName -> {
							var expr = subContext.expressionsByFieldName.get(fieldName);
							var val = (Object) tuple.get(expr);
							LOG.trace("Sub: Have {}: {}", fieldName, val);
							if (val == null) return; // Skip
							var alias = subContext.aliasByFieldName.getOrDefault(fieldName, fieldName);
							if (useMcpdStyle) {
								o.compute(alias, (k, v) -> v == null ? Objects.toString(val) : v + ";" + Objects.toString(val));
							} else {
								var arrayList = o.computeIfAbsent(alias, n -> new ArrayList<Object>(10));
								if (arrayList instanceof List) {
									((List<Object>) arrayList).add((Object) tuple.get(expr));
								} else {
									LOG.error("{} is not a List, but {}!", fieldName, arrayList == null ? "" : arrayList.getClass());
								}
							}
						});
					}
				});
			});

			mappedById.values().forEach(resultHandler); // This may throw a RuntimeException and we'll stop processing
		});
	}

	@Override
	public Page<AccessionDetails> withImages(AccessionFilter filter, Pageable page) throws SearchException {
		filter.images(true);

		Page<Accession> paged = findAll(filter, page);
		paged.forEach(accession -> accession.getTaxonomy().getId());

		List<AccessionDetails> contentWithImages = new ArrayList<>(paged .getNumberOfElements());

		for (Accession accession : paged ) {
			AccessionDetails details = new AccessionDetails();
			details.details = accession;

			RepositoryFolder folder = accession.getAccessionId().getRepositoryFolder();
			ImageGallery imageGallery = imageGalleryService.getImageGallery(folder);
			if (imageGallery != null) {
				if (imageGallery.getImages() != null) {
					imageGallery.getImages().size();
				}
				details.imageGallery = imageGallery;
			}
			contentWithImages.add(details);
		}

		return new PageImpl<>(contentWithImages, page, paged .getTotalElements());
	}

	@Override
	public Slice<AccessionDetails> withImagesSlice(AccessionFilter filter, Pageable page) throws SearchException {
		filter.images(true);

		Page<Accession> paged = findAll(filter, page);
		paged.forEach(accession -> accession.getTaxonomy().getId());

		List<AccessionDetails> contentWithImages = new ArrayList<>(paged .getNumberOfElements());

		int imageCount = 0;
		for (Accession accession : paged ) {
			AccessionDetails details = new AccessionDetails();
			details.details = accession;

			RepositoryFolder folder = accession.getAccessionId().getRepositoryFolder();
			ImageGallery imageGallery = imageGalleryService.getImageGallery(folder);
			if (imageGallery != null) {
				if (imageGallery.getImages() != null) {
					imageGallery.getImages().size();
					imageCount += imageGallery.getImages().size();
				}
				details.imageGallery = imageGallery;
			}
			contentWithImages.add(details);
			if (imageCount > page.getPageSize()) {
				break;
			}
		}

		return new Slice<>(contentWithImages, page.getOffset(), paged.getTotalElements(), paged.getSort());
	}

	@Override
	@Cacheable(cacheNames = "apiResponses.accessionImages.count")
	public int countAccessionsImages(AccessionFilter filter) throws SearchException, Exception {
		var imagesFilter = filter.copy(AccessionFilter.class);
		imagesFilter.images(true);

		List<Long> folderIds = null;

		if (filter.isFulltextQuery()) {
			Set<Long> accessionIds = new HashSet<>();
			elasticsearchService.processById(Accession.class, imagesFilter, accessionIds::addAll, Pageable.unpaged());
			folderIds = jpaQueryFactory.from(QAccession.accession)
				.select(QAccession.accession.accessionId().repositoryFolder().id)
				.where(QAccession.accession.id.in(accessionIds))
				.fetch();
		} else {
			folderIds = jpaQueryFactory.from(QAccession.accession)
				.select(QAccession.accession.accessionId().repositoryFolder().id)
				.where(imagesFilter.buildPredicate())
				.fetch();
		}

		var imageCount = jpaQueryFactory.from(QImageGallery.imageGallery)
			.select(QImageGallery.imageGallery.images.size().sum())
			.where(QImageGallery.imageGallery.folder.id.in(folderIds))
			.fetchOne();

		return imageCount == null ? 0 : imageCount;
	}

	// Utility method that uses ES as required
	private Page<Accession> findAll(AccessionFilter filter, Pageable page) throws SearchException {
		if (filter.isFulltextQuery()) {
			return elasticsearchService.findAll(Accession.class, filter, page);
		}

		List<Accession> content = accessionRepository.findAll(filter, page);
		long total = countAccessions(filter);
		return new PageImpl<>(content, page, total);
	}

	@Override
	public List<LabelValue<String>> autocomplete(AccessionFilter filter, String field, String text) throws IOException {
		AccessionFilter suggestionFilter = filter.copy(AccessionFilter.class);
		try {
			suggestionFilter.clearFilter(field);
		} catch (NoSuchFieldException | IllegalAccessException e) {
			LOG.error("Error while clearing filter: ", e.getMessage());
		}

		final List<LabelValue<String>> completed = new ArrayList<>();
		ElasticsearchService.TermResult termResult = null;
		if (AppliedFiltersConverter.convertTerm(FilterConstants.INSTCODE).equalsIgnoreCase(field)) {
			final List<FaoInstitute> faoInst = instituteService.autocomplete(text);
			try {
				termResult = elasticsearchService.termStatisticsAuto(Accession.class, suggestionFilter, 20000, AppliedFiltersConverter.convertTerm(FilterConstants.INSTCODE));
			} catch (SearchException e) {
				LOG.error("Error occurred during search", e);
			}

			if (termResult != null && !termResult.getTerms().isEmpty()) {
				for (FaoInstitute inst : faoInst) {
					String label = inst.getCode() + ", " + inst.getFullName();
					LabelValue<String> suggestion = getSuggestion(termResult.getTerms(), label, inst.getCode());
					if (suggestion != null) {
						completed.add(suggestion);
					}
				}
			}
		} else if (AppliedFiltersConverter.convertTerm(FilterConstants.TAXONOMY_GENUS).equalsIgnoreCase(field)) {
			final List<String> genusList = taxonomyService.autocompleteGenus(text);
			try {
				termResult = elasticsearchService.termStatisticsAuto(Accession.class, suggestionFilter, 20000,AppliedFiltersConverter.convertTerm(FilterConstants.TAXONOMY_GENUS));
			} catch (SearchException e) {
				LOG.error("Error occurred during search", e);
			}

			if (termResult != null && !termResult.getTerms().isEmpty()) {
				var termsForSuggestion = termResult.getTerms().stream()
					.filter(term -> StringUtils.startsWithIgnoreCase(term.getTerm(), text) && term.getCount() > 0)
					.sorted(Comparator.comparingLong(ElasticsearchService.Term::getCount).reversed())
					.collect(Collectors.toList());
				for (var term : termsForSuggestion) {
					LabelValue<String> suggestion = getSuggestion(genusList, term);
					if (suggestion != null) {
						completed.add(suggestion);
						if (completed.size() == 10) {
							break;
						}
					}
				}
			}
		} else if (AppliedFiltersConverter.convertTerm(FilterConstants.ORGCTY_ISO3).equalsIgnoreCase(field)) {
			final List<Country> countries = geoService.autocomplete(text);
			try {
				termResult = elasticsearchService.termStatisticsAuto(Accession.class, suggestionFilter, 20000, AppliedFiltersConverter.convertTerm(FilterConstants.ORGCTY_ISO3));
			} catch (SearchException e) {
				LOG.error("Error occurred during search", e);
			}

			completed.addAll(getCountrySuggestions(termResult, countries));
		} else if (AppliedFiltersConverter.convertTerm(FilterConstants.INSTITUTE_COUNTRY_ISO3).equalsIgnoreCase(field)) {
			final List<Country> countries = geoService.autocomplete(text);
			try {
				termResult = elasticsearchService.termStatisticsAuto(Accession.class, suggestionFilter, 20000, AppliedFiltersConverter.convertTerm(FilterConstants.INSTITUTE_COUNTRY_ISO3));
			} catch (SearchException e) {
				LOG.error("Error occurred during search", e);
			}

			completed.addAll(getCountrySuggestions(termResult, countries));
		}
		return completed;
	}

	private List<LabelValue<String>> getCountrySuggestions(ElasticsearchService.TermResult termResult, List<Country> countries) {
		final List<LabelValue<String>> result = new ArrayList<>();
		if (termResult != null && !termResult.getTerms().isEmpty()) {
			for (Country c : countries) {
				String label = c.getCode3() + ", " + c.getName();
				LabelValue<String> suggestion = getSuggestion(termResult.getTerms(), label, c.getCode3());
				if (suggestion != null) {
					result.add(suggestion);
				}
			}
		}
		return result;
	}

	private LabelValue<String> getSuggestion(List<ElasticsearchService.Term> terms, String label, String value) {
		for (ElasticsearchService.Term term : terms) {
			if (term.getTerm().equalsIgnoreCase(value) && term.getCount() > 0) {
				label = label.concat(" (" + term.getCount() + ")");
				return new LabelValue<>(value, label);
			}
		}
		return null;
	}

	private LabelValue<String> getSuggestion(List<String> values, ElasticsearchService.Term term) {
		for (String value : values) {
			if (term.getTerm().equalsIgnoreCase(value) && term.getCount() > 0) {
				String label = value.concat(" (" + term.getCount() + ")");
				return new LabelValue<>(value, label);
			}
		}
		return null;
	}

	@Override
	@Cacheable(cacheNames = "apiResponses.accession.suggestions", sync = true)
	public Map<String, ElasticsearchService.TermResult> getSuggestions(AccessionFilter filter) throws SearchException, IOException {

		if (elasticsearchService == null)
			return Map.of();

		// All suggestions for specified filters
		Map<String, ElasticsearchService.TermResult> allTerms = elasticsearchService.termStatisticsAuto(Accession.class, filter, 100, suggestions.toArray(ArrayUtils.EMPTY_STRING_ARRAY));

		// Adapted suggestions
		Map<String, ElasticsearchService.TermResult> suggestionRes = new HashMap<>(suggestions.size());

		for (String suggestionKey: suggestions) {
			AccessionFilter suggestionFilter = filter.copy(AccessionFilter.class);
			try {
				if ("imageCount".equals(suggestionKey)) {
					suggestionFilter.clearFilter("images");
				} else {
					suggestionFilter.clearFilter(suggestionKey);
				}
			} catch (NoSuchFieldException e) {
				LOG.debug("No field {} while clearing filter: {}", suggestionKey, e.getMessage());
			} catch (Throwable e) {
				LOG.error("Error while clearing filter {}: {}", suggestionKey, e.getMessage());
			}

			ElasticsearchService.TermResult suggestion = null;
			if (suggestionFilter.toString().equals(filter.toString())) {
				// If suggestionKey is not in the filter
				suggestion = allTerms.get(suggestionKey);
			} else {
				// suggestionKey is filtered, get terms without it
				suggestion = elasticsearchService.termStatisticsAuto(Accession.class, suggestionFilter, 100, suggestionKey);
			}

			if ("imageCount".equals(suggestionKey)) {
				// falseTerm: has "0" as imageCount 
				ElasticsearchService.Term falseTerm = suggestion.getTerms().stream().filter((term) -> "0".equals(term.getTerm())).findFirst().map((term) -> new ElasticsearchService.Term("false", term.getCount())).orElse(new ElasticsearchService.Term("false", 0));
				// trueTerm: total - false
				ElasticsearchService.Term trueTerm = new ElasticsearchService.Term("true", suggestion.getTotalCount() - falseTerm.getCount());

				List<ElasticsearchService.Term> terms = Lists.newArrayList();
				if(falseTerm.getCount() > 0) {
					terms.add(falseTerm);
				}
				if (trueTerm.getCount() > 0) {
					terms.add(trueTerm);
				}

				ElasticsearchService.TermResult imageCountSuggestion = new ElasticsearchService.TermResult("images", suggestion.getTotal(), terms, 0);
				suggestionRes.put("images", imageCountSuggestion);
				continue;
			}
			suggestionRes.put(suggestionKey, suggestion);
		}

		return suggestionRes;
	}

	@Override
	@Cacheable(cacheNames = "apiResponses.accession.geobounds", sync = true)
	public Number[][] getGeoBounds(AccessionFilter filter) {

		if (filter.isFulltextQuery()) {
			try {
				Number[][] geoBounds = null;
				if (elasticsearchService != null) {
					geoBounds = elasticsearchService.getAccessionGeoBounds(filter);
				}
				if (geoBounds == null || geoBounds.length == 0) {
					return AccessionService.DEFAULT_GEOBOUNDS;
				} else {
					return geoBounds;
				}
			} catch (SearchException e) {
				LOG.error("Exception in getting geo bounds", e);
				return AccessionService.DEFAULT_GEOBOUNDS;
			}
		}

		final QAccession accession = QAccession.accession;
		QAccessionId qAid = accession.accessionId();

		JPAQuery<Accession> query = jpaQueryFactory.selectFrom(accession);
		filter.buildJpaQuery(query, accession);

		Object[] results = query.select(qAid.latitude.min(), qAid.longitude.max(),
			qAid.latitude.max(), qAid.longitude.min()).where(qAid.latitude.between(-90, 90).and(qAid.longitude.between(-180, 180))).fetchOne().toArray();

		return new Number[][] { new Number[] { (Number) results[0], (Number) results[1] }, new Number[] { (Number) results[2], (Number) results[3] } };
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRES_NEW, noRollbackFor = Throwable.class)
	public List<Accession> processAccessions(List<Long> accessionIds, IAccessionBatchAction action) {
		var qa = QAccession.accession;
		List<Accession> accessions = jpaQueryFactory.from(qa).innerJoin(qa.accessionId()).fetchJoin().innerJoin(qa.taxonomy()).fetchJoin().select(qa).where(qa.id.in(accessionIds)).fetch();
		LOG.debug("Processing {} accessions of {} IDs provided", accessions.size(), accessionIds.size());
		try {
			action.apply(accessions);
		} catch (Exception e) {
			LOG.info("Error processing accession: {}", e.getMessage());
		}
		return accessions;
	}


	@Override
	public Accession findMatchingAccession(RepositoryFolder repositoryFolder) {
		Accession accession = null;
		Matcher matcher = ACCESSION_IMAGE_PATTERN.matcher(repositoryFolder.getPath());
		if (matcher.matches()) {
			LOG.debug("Path {} is within institute accession files prefix instCode={} acceNumb={}", repositoryFolder.getPath(), matcher.group(1), matcher.group(2));
			accession = accessionRepository.findByInstituteCodeAndAccessionNumber(matcher.group(1), matcher.group(2));
		}
		return accession;
	}

	@Override
	@PreAuthorize("hasRole('ADMINISTRATOR')")
	public void scanForPublishedDatasets() {
		BooleanExpression expression = QDataset.dataset.state.eq(PublishState.PUBLISHED);
		datasetRepository.findAll(expression).forEach(dataset -> {
			taskExecutor.execute(() -> TransactionHelper.executeInTransaction(false, () -> {
				datasetAndSubsetCountAspect.afterDatasetPersist(dataset);
				return true;
			}));
		});
	}

	@Override
	@PreAuthorize("hasRole('ADMINISTRATOR')")
	public void scanForPublishedSubsets() {
		BooleanExpression expression = QSubset.subset.state.eq(PublishState.PUBLISHED);
		subsetRepository.findAll(expression).forEach(subset -> {
			taskExecutor.execute(() -> TransactionHelper.executeInTransaction(false, () -> {
				datasetAndSubsetCountAspect.afterSubsetPersist(subset);
				return true;
			}));
		});
	}

	@Override
	@Transactional
	@PreAuthorize("hasRole('ADMINISTRATOR')")
	public void resetSubsetAndDatasetCounters() {
		accessionIdRepository.resetSubsetAndDatasetCounters();
	}

	@Override
	public List<Taxonomy2> listSpecies(AccessionFilter filter) {
		return accessionRepository.listSpecies(filter);
	}

	@Override
	@Transactional(propagation = Propagation.MANDATORY) // Need to be part of an existing transaction!
	@PreAuthorize("hasRole('ADMINISTRATOR')")
	public long changeInstitute(FaoInstitute currentInstitute, FaoInstitute newInstitute) {
		var qAccession = QAccession.accession;
		if (jpaQueryFactory.from(qAccession).select(qAccession.id.count()).where(qAccession.institute().eq(newInstitute)).fetchOne() > 0) {
			throw new RuntimeException("Institute " + newInstitute.getCode() + " already has accessions in Genesys.");
		}
		LOG.warn("Migrating accessions from {} to {}", currentInstitute.getCode(), newInstitute.getCode());

		return jpaQueryFactory.update(qAccession)
			// Set instCode
			.set(qAccession.instituteCode, newInstitute.getCode())
			// Set institute
			.set(qAccession.institute(), newInstitute)
			// WHERE
			.where(qAccession.institute().eq(currentInstitute))
			// execute and get the number of updated records
			.execute();
	}

	@Override
	public Set<Integer> listTileIndex3min(AccessionFilter filter) throws SearchException, Exception {
		QAccession qAccession = QAccession.accession;
		var qAid = QAccessionId.accessionId;

		if (filter.isFulltextQuery()) {
			var tileIndexes = new HashSet<Integer>();
			elasticsearchService.processById(Accession.class, filter, (ids) -> {
				var loaded = jpaQueryFactory.from(qAid).select(qAid.tileIndex3min).distinct().where(qAid.id.in(ids).and(qAid.tileIndex3min.isNotNull())).fetch();
				tileIndexes.addAll(loaded);
			}, Pageable.unpaged());
			return tileIndexes;

		} else {
			// Build sub-select for accession ids matching the filter
			var accessionIdSelect = jpaQueryFactory.from(qAccession).select(qAccession.id);
			filter.buildJpaQuery(accessionIdSelect, qAccession);

			// fetch distinct list of tile indexes with subselect
			var tileIndexes3d = jpaQueryFactory.from(qAid).select(qAid.tileIndex3min).distinct().where(qAid.id.in(accessionIdSelect).and(qAid.tileIndex3min.isNotNull())).fetch();
			return new HashSet<>(tileIndexes3d);
		}
	}

	@Override
	public Map<String, Set<Integer>> listTileIndex3minByCrop(AccessionFilter filter) throws SearchException, Exception {
		// Check if filter includes crop selection
		if (CollectionUtils.isEmpty(filter.crop)) {
			// No crop selected, return under "NOCROP"
			return Map.of(NOCROP, listTileIndex3min(filter));

		} else {
			// Crops are selected, handle each crop separately
			var tileIndexes = new HashMap<String, Set<Integer>>();
			for (var cropCode : filter.crop) {
				var filterForCrop = filter.copy(AccessionFilter.class);
				filterForCrop.crop = Set.of(cropCode);
				tileIndexes.put(cropCode, listTileIndex3min(filterForCrop));
			}
			return tileIndexes;
		}
	}

	@Override
	@Cacheable(cacheNames = "accessionsInDatasets")
	public List<UUID> getAccessionUuids(AccessionFilter filters) {
		LOG.debug("Loading accession UUIDs for: {}", filters);
		if (filters.isEmpty()) {
			throw new RuntimeException("Accession filter must not be empty");
		}

//		if (filters.isFulltextQuery()) {
//			try {
//				var accessionIds = new LinkedHashSet<Long>(1000);
//				elasticsearchService.processById(Accession.class, filters, accessionIds::addAll, null);
//				var idFilter = new AccessionFilter();
//				idFilter.id(accessionIds);
//				return Collections.unmodifiableList(accessionRepository.getUUIDs(idFilter));
//			} catch (Exception e) {
//				throw new RuntimeException("Could not query accessions " + e.getMessage(), e);
//			}
//		}

		return Collections.unmodifiableList(accessionRepository.getUUIDs(filters));
	}

}