GenesysImageManagerApiServiceImpl.java

/*
 * Copyright 2025 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.gringlobal.api.v2.facade.impl;

import com.google.common.cache.Cache;
import com.google.common.cache.CacheBuilder;
import com.google.common.collect.Iterators;
import com.opencsv.CSVParserBuilder;
import com.opencsv.CSVReader;
import com.opencsv.CSVReaderBuilder;
import com.opencsv.CSVWriter;
import com.opencsv.bean.ColumnPositionMappingStrategy;
import com.opencsv.bean.ColumnPositionMappingStrategyBuilder;
import com.opencsv.bean.CsvToBean;
import com.opencsv.bean.CsvToBeanBuilder;
import com.opencsv.bean.MappingStrategy;
import com.opencsv.bean.StatefulBeanToCsv;
import com.opencsv.bean.StatefulBeanToCsvBuilder;
import com.querydsl.core.BooleanBuilder;
import com.querydsl.core.Tuple;
import com.querydsl.core.types.ExpressionUtils;
import com.querydsl.core.types.Predicate;
import com.querydsl.core.types.Projections;
import com.querydsl.core.types.dsl.BooleanExpression;
import com.querydsl.jpa.impl.JPAQueryFactory;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NonNull;
import lombok.extern.slf4j.Slf4j;

import org.apache.commons.lang3.Strings;
import org.genesys.blocks.util.TransactionHelper;
import org.genesys.filerepository.model.QRepositoryFile;
import org.genesys.filerepository.model.RepositoryImage;
import org.genesys.filerepository.service.RepositoryService;
import org.gringlobal.api.exception.InvalidApiUsageException;
import org.gringlobal.api.exception.NotFoundElement;
import org.gringlobal.api.model.integration.GenesysAttachmentDTO;
import org.gringlobal.api.model.integration.RepositoryFileDTO;
import org.gringlobal.api.model.integration.RepositoryImageDTO;
import org.gringlobal.api.v2.facade.GenesysImageManagerApiService;
import org.gringlobal.api.v2.facade.GenesysImageManagerApiService.SyncListProcessStatus.ListProcessStatus;
import org.gringlobal.api.v2.facade.GenesysImageManagerApiService.SyncProcessStatus.ProcessStatus;
import org.gringlobal.api.v2.mapper.MapstructMapper;
import org.gringlobal.application.config.IntegrationConfig;
import org.gringlobal.model.AccessionInvAttach;
import org.gringlobal.model.Inventory;
import org.gringlobal.model.QAccession;
import org.gringlobal.model.QAccessionInvAttach;
import org.gringlobal.model.QInventory;
import org.gringlobal.model.QSite;
import org.gringlobal.model.Site;
import org.gringlobal.model.integration.GenesysAttachment;
import org.gringlobal.model.integration.QGenesysAttachment;
import org.gringlobal.model.integration.GenesysAttachment.ImageSyncList;
import org.gringlobal.model.integration.GenesysAttachment.ImageSyncStatus;
import org.gringlobal.persistence.GenesysAttachmentRepository;
import org.gringlobal.persistence.SiteRepository;
import org.gringlobal.service.AppSettingsService;
import org.gringlobal.service.InventoryAttachmentService;
import org.gringlobal.util.LoggerHelper;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.core.io.InputStreamResource;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.util.Pair;
import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpMethod;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.client.HttpComponentsClientHttpRequestFactory;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.LinkedMultiValueMap;
import org.springframework.util.MultiValueMap;
import org.springframework.util.StreamUtils;
import org.springframework.web.client.RestTemplate;
import org.springframework.web.util.UriComponentsBuilder;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.nio.file.Files;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;
import java.util.TreeMap;
import java.util.UUID;
import java.util.concurrent.CancellationException;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.Future;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.TimeoutException;
import java.util.concurrent.atomic.AtomicReference;
import java.util.function.BiFunction;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import javax.persistence.EntityManager;

import static org.gringlobal.application.config.IntegrationConfig.GenesysClientFactory.APPSETTING_GENESYS_URL;
import static org.gringlobal.application.config.IntegrationConfig.GenesysClientFactory.APPSETTING_ORIGIN;
import static org.gringlobal.application.config.IntegrationConfig.GenesysClientFactory.SETTINGS_GENESYS;
import static org.gringlobal.model.community.CommunityCodeValues.ATTACH_CATEGORY_IMAGE;
import static org.gringlobal.model.integration.GenesysAttachment.ImageSyncList.imageDownload;
import static org.gringlobal.model.integration.GenesysAttachment.ImageSyncList.imageUpdate;
import static org.gringlobal.model.integration.GenesysAttachment.ImageSyncList.imageUpload;
import static org.gringlobal.model.integration.GenesysAttachment.ImageSyncList.metadataUpdate;
import static org.gringlobal.model.integration.GenesysAttachment.ImageSyncList.uploaded;

@Service
@Slf4j
@PreAuthorize("hasAuthority('GROUP_ADMINS')")
public class GenesysImageManagerApiServiceImpl implements GenesysImageManagerApiService, InitializingBean {

	private static final String DEFAULT_GENESYS_API = "https://api.genesys-pgr.org";
	private static final String ENCODING = "UTF-8";
	private static final Character SEPARATOR = '\t';
	private static final Character QUOTE_CHAR = '"';
	private static final Character ESCAPE_CHAR = '\\';
	private static final String LINE_END = "\n";
	private static final String[] HEADERS = new String[] {
		"uuid", "version", "contentType", "path", "extension", "originalFilename",
		"title", "subject", "description", "creator", "created", "rightsHolder",
		"accessRights", "license", "bibliographicCitation", "md5"
	};

	public MappingStrategy<GenesysAttachmentDTO> mappingStrategy;

	@Autowired
	private MapstructMapper mapper;

	@Autowired
	private InventoryAttachmentService attachmentService;

	@Autowired
	private SiteRepository siteRepository;

	@Autowired
	private ThreadPoolTaskExecutor executor;

	@Autowired
	private AppSettingsService appSettingsService;

	@Autowired
	private RepositoryService fileRepositoryService;

	@Autowired
	private JPAQueryFactory jpaQueryFactory;

	@Autowired
	private EntityManager entityManager;

	@Autowired
	private TransactionHelper transactionHelper;

	@Autowired
	private GenesysAttachmentRepository genesysAttachmentRepository;

	@Autowired(required = false)
	private IntegrationConfig.GenesysClientFactory genesysClientFactory;

	private final AtomicReference<SyncProcessStatus> syncProcessStatus = new AtomicReference<>();
	private final AtomicReference<SyncListProcessStatus> syncImageUploadStatus = new AtomicReference<>();
	private final AtomicReference<SyncListProcessStatus> syncImageDownloadStatus = new AtomicReference<>();
	private final AtomicReference<SyncListProcessStatus> syncImageUpdateStatus = new AtomicReference<>();
	private final AtomicReference<SyncListProcessStatus> syncMetadataUpdateStatus = new AtomicReference<>();

	private RestTemplate restTemplate;

	@Override
	public void afterPropertiesSet() throws Exception {
		genesysAttachmentRepository.deleteAllInBatch();

		HttpComponentsClientHttpRequestFactory httpRequestFactory = new HttpComponentsClientHttpRequestFactory();
		httpRequestFactory.setConnectTimeout(5 * 1000);
		httpRequestFactory.setReadTimeout(180 * 1000);
		this.restTemplate = new RestTemplate(httpRequestFactory);

		mappingStrategy = new ColumnPositionMappingStrategyBuilder<GenesysAttachmentDTO>().build();
		mappingStrategy.setType(GenesysAttachmentDTO.class);
		((ColumnPositionMappingStrategy<GenesysAttachmentDTO>)mappingStrategy).setColumnMapping(HEADERS);
	}

	@Override
	public void cancelSyncProcess() {
		syncProcessStatus.set(null);
		syncImageUploadStatus.set(null);
		syncImageDownloadStatus.set(null);
		syncImageUpdateStatus.set(null);
		syncMetadataUpdateStatus.set(null);
		genesysAttachmentRepository.deleteAllInBatch();
	}

	@Override
	public void pauseSyncProcess() {
		syncImageUploadStatus.getAndUpdate(status -> {
			if (status != null) status.status = ListProcessStatus.STOPPED;
			return status;
		});
		syncImageDownloadStatus.getAndUpdate(status -> {
			if (status != null) status.status = ListProcessStatus.STOPPED;
			return status;
		});
		syncImageUpdateStatus.getAndUpdate(status -> {
			if (status != null) status.status = ListProcessStatus.STOPPED;
			return status;
		});
		syncMetadataUpdateStatus.getAndUpdate(status -> {
			if (status != null) status.status = ListProcessStatus.STOPPED;
			return status;
		});
	}

	@Override
	public SyncProcessStatus getSyncProcessStatus() {
		return syncProcessStatus.get();
	}

	@Override
	public SyncListProcessStatus getSyncListStatus(ImageSyncList list) {
		switch (list) {
			case imageUpload:
				return syncImageUploadStatus.get();
			case imageDownload:
				return syncImageDownloadStatus.get();
			case imageUpdate:
				return syncImageUpdateStatus.get();
			case metadataUpdate:
				return syncMetadataUpdateStatus.get();
			default:
				throw new NotFoundElement("No list found by name: " + list);
		}
	}

	@Override
	@Transactional(readOnly = true)
	public Page<GenesysAttachmentDTO> getUploadList(Pageable page) {
		return mapper.map(listAttachments(QGenesysAttachment.genesysAttachment.type.in(imageUpload, imageUpdate, metadataUpdate), page), mapper::mapGenesys);
	}

	@Override
	@Transactional(readOnly = true)
	public Page<GenesysAttachmentDTO> getDownloadList(Pageable page) {
		return mapper.map(listAttachments(QGenesysAttachment.genesysAttachment.type.in(imageDownload), page), mapper::mapGenesys);
	}

	@Override
	@Transactional(readOnly = true)
	public Page<GenesysAttachmentDTO> getUploadedList(Pageable page) {
		return mapper.map(listAttachments(QGenesysAttachment.genesysAttachment.type.in(uploaded), page), mapper::mapGenesys);
	}

	private Page<GenesysAttachment> listAttachments(BooleanExpression expression, Pageable page) {
		var processStatus = syncProcessStatus.get();
		if (processStatus == null || processStatus.status != ProcessStatus.READY) {
			throw new InvalidApiUsageException("List is not ready.");
		}
		return genesysAttachmentRepository.findAll(expression, page);
	}

	@Override
	public SyncProcessStatus startSyncProcess(Set<String> instCodes) {

		if (syncProcessStatus.get() != null && syncProcessStatus.get().status != ProcessStatus.FAILED && syncProcessStatus.get().status != ProcessStatus.READY) {
			throw new InvalidApiUsageException("Cannot start a new process while another is still running. Please finish the current process first.");
		}

		syncProcessStatus.set(new SyncProcessStatus(ProcessStatus.STARTED));
		transactionHelper.executeInTransaction(false, () -> {
			genesysAttachmentRepository.deleteAllInBatch();
			return true;
		});

		// Execute in background
		var currentUserAuth = SecurityContextHolder.getContext().getAuthentication();
		executor.submit(() -> transactionHelper.asUser(currentUserAuth, () -> {
			var pendingTasks = new LinkedList<Future<ProcessStatus>>();
			for (var instCode : instCodes) {
				var sites = (List<Site>) siteRepository.findAll(QSite.site.faoInstituteNumber.eq(instCode));
				if (sites.isEmpty()) {
					log.warn("No sites found for {}. Ignoring", instCode);
					continue;
				}

				pendingTasks.add(executor.submit(() -> {
					File tempGenesysImageMetadata = null;
					// Download Genesys images metadata csv file to temp file
					try (var tempImagesMetadataOutput = new FileOutputStream(tempGenesysImageMetadata = File.createTempFile("temp_genesys_images_metadata", ".csv"))) {

						syncProcessStatus.set(new SyncProcessStatus(ProcessStatus.DOWNLOADING));
						String httpOrigin = appSettingsService.getSetting(SETTINGS_GENESYS, APPSETTING_ORIGIN, String.class).get();
						String genesysApiUrl = appSettingsService.getSetting(SETTINGS_GENESYS, APPSETTING_GENESYS_URL, String.class).orElse(DEFAULT_GENESYS_API);
						HttpHeaders headers = new HttpHeaders();
						headers.set("Origin", httpOrigin);
						headers.set("Referer", httpOrigin);
						headers.set("Authorization", "Bearer " + genesysClientFactory.fetchGenesysToken());

						var urlTemplate = UriComponentsBuilder.fromHttpUrl(genesysApiUrl)
							.path("/api/v2/repository/download/folder-metadata")
							.pathSegment("wiews", instCode, "acn")
							.toUriString();

						restTemplate.execute(
							urlTemplate,
							HttpMethod.GET,
							request -> request.getHeaders().addAll(headers),
							response -> {
								log.warn("Genesys metadata response {} {}", response.getStatusCode(), response.getStatusText());
								if (response.getStatusCode() == HttpStatus.OK) {
									StreamUtils.copy(response.getBody(), tempImagesMetadataOutput);
									return true;
								} else {
									log.warn("Genesys metadata response not OK, but {} {}", response.getStatusCode(), response.getStatusText());
									response.getHeaders().forEach((a, b) -> {
										log.warn("Header {}: {}", a, b);
									});
									throw new RuntimeException("Failed to download metadata from Genesys for " + instCode + ". Status " + response.getStatusCode());
								}
							}
						);
						log.warn("Existing Genesys attachments written to {}", tempGenesysImageMetadata.getAbsolutePath());

					} catch (Throwable e) {
						deleteTemporaryFile(tempGenesysImageMetadata);
						throw e;
					}

					long numberOfRecords = -1;
					try {
						numberOfRecords = Files.lines(tempGenesysImageMetadata.toPath()).count() - 1;
					} catch (Throwable e) {
						log.error("Could not get number of lines in {}: {}", tempGenesysImageMetadata.getAbsolutePath(), e.getMessage());
					}
					// Save Genesys attachment metadata to database as REMOTE
					try (var inputStream = new FileInputStream(tempGenesysImageMetadata)) {
						populateFromGenesysAttachments(numberOfRecords, inputStream);
					} finally {
						deleteTemporaryFile(tempGenesysImageMetadata);
					}

					// Save GGCE attafchments metadata to database as LOCAL
					populateFromSiteAttachments(sites);
					return ProcessStatus.READY;
				}));
			}

			var errorFound = new AtomicReference<Throwable>(null);
			while (pendingTasks.size() > 0) {
				log.trace("Have {} pending tasks", pendingTasks.size());
				// Cancel tasks if any errors were encountered
				if (errorFound.get() != null) {
					pendingTasks.forEach(task -> {
						log.warn("Error found, canceling task {}", task);
						task.cancel(true);
					});
				}

				// Wait for tasks to wrap up
				pendingTasks.removeIf(task -> {
					if (task.isDone()) {
						log.warn("Task {} is done.", task);
						return true;
					}
					try {
						var result = task.get(250, TimeUnit.MILLISECONDS);
						log.warn("Have {}", result);
						return true;
					} catch (TimeoutException e) {
						// Task is still running
						// log.trace("Task {} is still running...", task);
						return false;
					} catch (CancellationException e) {
						log.error("Sync was cancelled: {}", e.getMessage());
						return true;
					} catch (ExecutionException e) {
						log.error("Error executing sync: {}", e.getCause().getMessage(), e.getCause());
						errorFound.set(e.getCause());
						return true;
					} catch (Throwable e) {
						log.error("Other error while executing sync: {}", e.getMessage(), e);
						return true;
					}
				});
			}

			if (errorFound.get() != null) {
				log.warn("Canceling because an error was encountered during sync: {}", errorFound.get().getMessage(), errorFound.get());
				cancelSyncProcess();
				syncProcessStatus.set(new SyncProcessStatus(ProcessStatus.FAILED, errorFound.get().getMessage()));
				return Void.TYPE;
			}

			log.warn("No errors, comparing images now...");
			// Compare
			syncProcessStatus.set(new SyncProcessStatus(ProcessStatus.COMPARING));
			try {
				compareImages();
				syncProcessStatus.getAndUpdate(status -> status.status(ProcessStatus.READY));
				log.warn("Done comparing images, {}", syncProcessStatus.get());
			} catch (Throwable e) {
				log.warn("Error comparing images: {}", e.getMessage(), e);
				syncProcessStatus.set(new SyncProcessStatus(ProcessStatus.FAILED, e.getMessage()));
			} finally {
				syncImageUploadStatus.set(null);
				syncImageDownloadStatus.set(null);
				syncImageUpdateStatus.set(null);
				syncMetadataUpdateStatus.set(null);
			}
			return Void.TYPE;
		}));

		return syncProcessStatus.get();
	}

	@Override
	public void syncUploadList() {
		syncList(imageUpload);
		syncList(imageUpdate);
		syncList(metadataUpdate);
	}

	@Override
	public void syncDownloadList() {
		syncList(imageDownload);
	}

	private void syncList(ImageSyncList list) {

		AtomicReference<SyncListProcessStatus> status;
		switch (list) {
			case imageUpload:
				status = syncImageUploadStatus;
				break;
			case imageDownload:
				status = syncImageDownloadStatus;
				break;
			case imageUpdate:
				status = syncImageUpdateStatus;
				break;
			case metadataUpdate:
				status = syncMetadataUpdateStatus;
				break;
			default:
				throw new NotFoundElement(list + " list is not found");
		}
		if (syncProcessStatus.get() == null || syncProcessStatus.get().status != ProcessStatus.READY) {
			status.set(null);
			throw new InvalidApiUsageException(list + " list is not ready for synchronization");

		} else if (status.get() != null &&
			(status.get().status == ListProcessStatus.IN_PROGRESS || status.get().status == ListProcessStatus.SYNCHRONIZED)) {
			status.get();
			return;
		}

		String genesysApiUrl = appSettingsService.getSetting(SETTINGS_GENESYS, APPSETTING_GENESYS_URL, String.class).orElse(DEFAULT_GENESYS_API);
		String httpOrigin = appSettingsService.getSetting(SETTINGS_GENESYS, APPSETTING_ORIGIN, String.class).get();
		HttpHeaders headers = new HttpHeaders();
		headers.set("Origin", httpOrigin);
		headers.set("Referer", httpOrigin);
		headers.set("Authorization", "Bearer " + genesysClientFactory.fetchGenesysToken());

		var currentStatus = new SyncListProcessStatus(ListProcessStatus.IN_PROGRESS);
		status.set(currentStatus);
		// As current user
		var currentUserAuth = SecurityContextHolder.getContext().getAuthentication();
		executor.submit(() -> transactionHelper.asUser(currentUserAuth, () -> {
			switch (list) {
				case imageUpload:
					syncImageUpload(genesysApiUrl, headers);
					break;
				case imageDownload:
					syncImageDownload(genesysApiUrl, headers);
					break;
				case imageUpdate:
					syncImageUpdate(genesysApiUrl, headers);
					break;
				case metadataUpdate:
					syncMetadataUpdate(genesysApiUrl, headers);
					break;
				default:
					throw new NotFoundElement(list + " list is not found");
			}
			return true;
		}));
	}

	/** Fetch first X unsynchronized records from {@code list} ordered by id */
	private Page<GenesysAttachment> getAttachmentsForSync(ImageSyncList list, Pageable page) {
		assert(list != null);
		assert(page != null);
		return transactionHelper.executeInTransaction(true, () -> 
			genesysAttachmentRepository.findAll(
				QGenesysAttachment.genesysAttachment.type.eq(list)
				.and(QGenesysAttachment.genesysAttachment.status.eq(ImageSyncStatus.PENDING)) // Only pending items
				, page
		));
	}

	private void syncImageUpload(String apiUrl, HttpHeaders headers) {
		var status = syncImageUploadStatus.get(); // Get current status
		assert(status != null);
		var page = PageRequest.of(0, 50, Sort.by("id"));
		var toUploadItems = getAttachmentsForSync(imageUpload, page);
		status.totalElements = toUploadItems.getTotalElements();
		status.successCounter = 0;
		status.errorCounter = 0;

		while (status.errorCounter < 10 && !toUploadItems.isEmpty()) {
			for (GenesysAttachment toUploadItem : toUploadItems) {
				status = syncImageUploadStatus.get(); // Get current status
				if (status == null || status.status != ListProcessStatus.IN_PROGRESS) {
					log.warn("Stopping upload, status is {}", status);
					return;
				}

				try {
					log.warn("For upload: {}", toUploadItem);
					// metadata part
					var repositoryFile = fileRepositoryService.getFile(toUploadItem.getUuid());
					log.warn("Local metadata: {}", repositoryFile);
					var metadata = mapper.mapGenesys(repositoryFile);
					log.warn("Uploading metadata: {}", metadata);
					metadata.setUuid(null);
					HttpHeaders metadataHeaders = new HttpHeaders();
					metadataHeaders.setContentType(MediaType.APPLICATION_JSON);
					HttpEntity<RepositoryFileDTO> metadataPart = new HttpEntity<>(metadata, metadataHeaders);

					// file bytes part
					var fileBytes = fileRepositoryService.getFileBytes(repositoryFile);
					log.warn("Size of upload is {}b = {}kb = {}mb", fileBytes.length, fileBytes.length / 1024, fileBytes.length / 1024 / 1024);

					// Multipart request
					headers.setContentType(MediaType.MULTIPART_FORM_DATA);
					MultiValueMap<String, Object> multipartRequest = new LinkedMultiValueMap<>();
					multipartRequest.add("repositoryFile", metadataPart);
					multipartRequest.add("file", new ByteArrayResource(fileBytes) {
						@Override
						public String getFilename() {
							return repositoryFile.getOriginalFilename();
						}
					});
					HttpEntity<MultiValueMap<String, Object>> requestEntity = new HttpEntity<>(multipartRequest, headers);

					var attachPath = QAccessionInvAttach.accessionInvAttach;
					var instCodeAcceNumb = jpaQueryFactory.select(attachPath.inventory().site().faoInstituteNumber, attachPath.inventory().accession().accessionNumber)
						.from(attachPath)
						.where(attachPath.repositoryFile().eq(repositoryFile))
						.fetchOne();
					if (instCodeAcceNumb == null) {
						status.errorCounter++;
						toUploadItem.setStatus(ImageSyncStatus.FAILED);
						toUploadItem.setErrorMessage("Attachment " + repositoryFile.getOriginalFilename() + " not found");
						continue;
					}

					var url = apiUrl + "/api/v2/repository/upload" + mapper.getGenesysFolder(instCodeAcceNumb.get(attachPath.inventory().site().faoInstituteNumber), instCodeAcceNumb.get(attachPath.inventory().accession().accessionNumber));

					log.warn("Uploading to {}", url);
					var response = restTemplate.postForEntity(url, requestEntity, RepositoryFileDTO.class);
					if (response.getStatusCode().is2xxSuccessful()) {
						log.warn("Upload was successful {}: {}", response.getStatusCode(), response.getBody());
						status.successCounter++;
						toUploadItem.setStatus(ImageSyncStatus.SYNCHRONIZED);
						toUploadItem.setErrorMessage(null);
					} else {
						log.warn("Genesys responded with code {}", response.getStatusCode());
						status.errorCounter++;
						toUploadItem.setStatus(ImageSyncStatus.FAILED);
						toUploadItem.setErrorMessage(response.getStatusCode().toString());
					}
				} catch (Throwable e) {
					status.errorCounter++;
					toUploadItem.setStatus(ImageSyncStatus.FAILED);
					toUploadItem.setErrorMessage(e.getMessage());
				} finally {
					// Update record status
					transactionHelper.executeInTransaction(false, () -> genesysAttachmentRepository.saveAndFlush(toUploadItem));
				}

				if (status.errorCounter >= 10) {
					log.warn("Stopping after too many (10) errors.");
					break;
				}
			}

			// Get next batch
			toUploadItems = getAttachmentsForSync(imageUpload, page);
		}

		// Update current status
		if (status.errorCounter > 0) {
			status.status = ListProcessStatus.FAILED;
			status.errorMessage = "Some attachments failed to upload.";
		} else {
			status.status = ListProcessStatus.SYNCHRONIZED;
			status.errorMessage = null;
		}
	}

	private void syncImageDownload(String apiUrl, HttpHeaders headers) {
		var status = syncImageDownloadStatus.get(); // Get current status
		assert(status != null);
		var page = PageRequest.of(0, 50, Sort.by("id"));
		var toDownloadItems = getAttachmentsForSync(imageDownload, page);
		status.totalElements = toDownloadItems.getTotalElements();
		status.successCounter = 0;
		status.errorCounter = 0;

		while (status.errorCounter < 10 && !toDownloadItems.isEmpty()) {
			for (GenesysAttachment toDownloadItem : toDownloadItems) {
				status = syncImageDownloadStatus.get(); // Get current status
				if (status == null || status.status != ListProcessStatus.IN_PROGRESS) {
					log.warn("Stopping download, status is {}", status);
					return;
				}

				try {
					var inventory = toDownloadItem.getInventory();
					var attachExists = jpaQueryFactory
						.selectOne().from(QAccessionInvAttach.accessionInvAttach)
						.where(
							QAccessionInvAttach.accessionInvAttach.inventory().eq(inventory)
								.and(QAccessionInvAttach.accessionInvAttach.repositoryFile().originalFilename.eq(toDownloadItem.getOriginalFilename()))
						)
						.fetchFirst() != null;
					if (attachExists) {
						toDownloadItem.setStatus(ImageSyncStatus.SYNCHRONIZED);
						status.successCounter++;
						continue;
					}

					var genesysFileUuid = toDownloadItem.getUuid();
					var getFileUrl = apiUrl + "/api/v2/repository/file/" + genesysFileUuid;
					var getFileResponse = restTemplate.exchange(
						getFileUrl,
						HttpMethod.GET,
						new HttpEntity<>(headers),
						RepositoryImageDTO.class
					);

					var genesysImageDto = getFileResponse.getBody();

					RepositoryImage image = mapper.mapGenesys(genesysImageDto);

					var itemStatus = status;
					restTemplate.execute(
						apiUrl + "/api/v2/repository/download/" + genesysFileUuid,
						HttpMethod.GET,
						request -> request.getHeaders().addAll(headers),
						response -> {
							if (response.getStatusCode().is2xxSuccessful()) {
								try {
									var attachRequest = new InventoryAttachmentService.InventoryAttachmentRequest();
									attachRequest.fileMetadata = image;
									attachRequest.attachMetadata = new AccessionInvAttach();
									attachRequest.attachMetadata.setCategoryCode(ATTACH_CATEGORY_IMAGE.value);
									attachRequest.attachMetadata.setIsWebVisible("Y");
									attachmentService.uploadFile(inventory, image.getOriginalFilename(), image.getContentType(), response.getBody(), attachRequest);
									toDownloadItem.setStatus(ImageSyncStatus.SYNCHRONIZED);
									toDownloadItem.setErrorMessage(null);
									itemStatus.successCounter++;
								} catch (Throwable e) {
									itemStatus.errorCounter++;
									toDownloadItem.setStatus(ImageSyncStatus.FAILED);
									toDownloadItem.setErrorMessage(e.getMessage());
									return null;
								}
							} else {
								log.warn("Genesys responded with code {}", response.getStatusCode());
								itemStatus.errorCounter++;
								toDownloadItem.setStatus(ImageSyncStatus.FAILED);
								toDownloadItem.setErrorMessage(response.getStatusText());
							}
							return null;
						}
					);
				} catch (Throwable e) {
					status.errorCounter++;
					toDownloadItem.setStatus(ImageSyncStatus.FAILED);
					toDownloadItem.setErrorMessage(e.getMessage());
				} finally {
					// Update record status
					transactionHelper.executeInTransaction(false, () -> genesysAttachmentRepository.save(toDownloadItem));
				}

				if (status.errorCounter >= 10) {
					log.warn("Stopping after too many (10) errors.");
					break;
				}
			}

			// Get next batch
			toDownloadItems = getAttachmentsForSync(imageDownload, page);
		}

		// Update current status
		if (status.errorCounter > 0) {
			status.status = ListProcessStatus.FAILED;
			status.errorMessage = "Some attachments failed to download.";
		} else {
			status.status = ListProcessStatus.SYNCHRONIZED;
			status.errorMessage = null;
		}
	}

	private void syncImageUpdate(String apiUrl, HttpHeaders headers) {
		var status = syncImageUpdateStatus.get(); // Get current status
		assert(status != null);
		var page = PageRequest.of(0, 50, Sort.by("id"));
		var toUpdateItems = getAttachmentsForSync(imageUpdate, page);
		status.totalElements = toUpdateItems.getTotalElements();
		status.successCounter = 0;
		status.errorCounter = 0;

		while (status.errorCounter < 10 && !toUpdateItems.isEmpty()) {
			for (GenesysAttachment toUpdateItem : toUpdateItems) {
				status = syncImageUpdateStatus.get(); // Get current status
				if (status == null || status.status != ListProcessStatus.IN_PROGRESS) {
					log.warn("Stopping update, status is {}", status);
					break;
				}

				try {
					var imagePath = toUpdateItem.getPath();
					var imagePathParts = imagePath.split("/");
					if (imagePathParts.length < 5) {
						// Wrong path
						status.errorCounter++;
						toUpdateItem.setStatus(ImageSyncStatus.FAILED);
						toUpdateItem.setErrorMessage("Invalid path: " + imagePath);
						continue;
					}
					var instCode = imagePathParts[2];
					var acceNumb = imagePathParts[4];

					var inventory = jpaQueryFactory.select(QInventory.inventory).from(QInventory.inventory)
						.where(QInventory.inventory.accession().site().faoInstituteNumber.eq(instCode)
							.and(QInventory.inventory.accession().accessionNumber.eq(acceNumb))
						).fetchOne();
					if (inventory == null) {
						// Skip image if accession is missing ?
						status.errorCounter++;
						toUpdateItem.setStatus(ImageSyncStatus.FAILED);
						toUpdateItem.setErrorMessage("Inventory not found by inst code: " + instCode + " and accession number: " + acceNumb);
						continue;
					}

					var fileUuid = jpaQueryFactory.select(QAccessionInvAttach.accessionInvAttach.repositoryFile().uuid).from(QAccessionInvAttach.accessionInvAttach)
						.where(QAccessionInvAttach.accessionInvAttach.inventory().eq(inventory)
							.and(QAccessionInvAttach.accessionInvAttach.repositoryFile().originalFilename.eq(toUpdateItem.getOriginalFilename()))
							.and(QAccessionInvAttach.accessionInvAttach.repositoryFile().folder().path.eq(attachmentService.createRepositoryPath(inventory).toString()))
						)
						.fetchOne();

					if (fileUuid == null) {
						status.errorCounter++;
						toUpdateItem.setStatus(ImageSyncStatus.FAILED);
						toUpdateItem.setErrorMessage("RepositoryFile(" + toUpdateItem.getOriginalFilename() + ") not found");
						continue;
					}

					var file = fileRepositoryService.getFile(fileUuid);

					// metadata part
					var metadata = mapper.mapGenesys((RepositoryImage) file);
					metadata.setUuid(toUpdateItem.getUuid());
					HttpHeaders metadataHeaders = new HttpHeaders();
					metadataHeaders.setContentType(MediaType.APPLICATION_JSON);
					HttpEntity<RepositoryImageDTO> metadataPart = new HttpEntity<>(metadata, metadataHeaders);

					// file bytes part
					var fileBytes = fileRepositoryService.getFileBytes(file);

					// Multipart request
					headers.setContentType(MediaType.MULTIPART_FORM_DATA);
					MultiValueMap<String, Object> multipartRequest = new LinkedMultiValueMap<>();
					multipartRequest.add("metadata", metadataPart);
					multipartRequest.add("file", new ByteArrayResource(fileBytes) {
						@Override
						public String getFilename() {
							return toUpdateItem.getOriginalFilename();
						}
					});
					HttpEntity<MultiValueMap<String, Object>> requestEntity = new HttpEntity<>(multipartRequest, headers);

					var response = restTemplate.postForEntity(apiUrl + "/api/v2/repository/{fileUuid}/update", requestEntity, RepositoryFileDTO.class, metadata.getUuid());
					if (response.getStatusCode().is2xxSuccessful()) {
						toUpdateItem.setStatus(ImageSyncStatus.SYNCHRONIZED);
						status.successCounter++;
					} else {
						log.warn("Genesys responded with code {}", response.getStatusCodeValue());
						status.errorCounter++;
						toUpdateItem.setStatus(ImageSyncStatus.FAILED);
						toUpdateItem.setErrorMessage(response.getStatusCode().getReasonPhrase());
					}
				} catch (Throwable e) {
					status.errorCounter++;
					toUpdateItem.setStatus(ImageSyncStatus.FAILED);
					toUpdateItem.setErrorMessage(e.getMessage());
				} finally {
					// Update record status
					transactionHelper.executeInTransaction(false, () -> genesysAttachmentRepository.save(toUpdateItem));
				}

				if (status.errorCounter >= 10) {
					log.warn("Stopping after too many (10) errors.");
					break;
				}
			}

			// Get next batch
			toUpdateItems = getAttachmentsForSync(imageUpdate, page);
		}

		// Update current status
		if (status.errorCounter > 0) {
			status.status = ListProcessStatus.FAILED;
			status.errorMessage = "Some attachments failed to upload.";
		} else {
			status.status = ListProcessStatus.SYNCHRONIZED;
			status.errorMessage = null;
		}
	}

	private void syncMetadataUpdate(String apiUrl, HttpHeaders headers) throws IOException {
		var attachmentList = metadataUpdate;
		var status = syncMetadataUpdateStatus.get(); // Get current status
		assert(status != null);

		var tempMetadataFile = File.createTempFile("temp_metadata_update", ".csv");
		
			if (status == null || status.status != ListProcessStatus.IN_PROGRESS) {
				log.warn("Stopping upload, status is {}", status);
				return;
			}
			var page = PageRequest.of(0, 1000, Sort.by("id"));
			Page<GenesysAttachment> lastImages = null;

			do {
				try (
					var tempFileOutput = new FileOutputStream(tempMetadataFile, false);
					CSVWriter metadataUpdateWriter = new CSVWriter(new BufferedWriter(new OutputStreamWriter(tempFileOutput, ENCODING)), SEPARATOR, QUOTE_CHAR, ESCAPE_CHAR, LINE_END);
				) {
					// Upload metadata file
					metadataUpdateWriter.writeNext(HEADERS);
					metadataUpdateWriter.flush();
					var beanWriter = createImageWriter(metadataUpdateWriter);
					var pageToFetch = page;
					var images = transactionHelper.executeInTransaction(true, () -> {
						var pageImages = genesysAttachmentRepository.findAll(
								QGenesysAttachment.genesysAttachment.type.eq(attachmentList)
								.and(QGenesysAttachment.genesysAttachment.status.eq(ImageSyncStatus.PENDING)) // Only pending items
							, pageToFetch);

						beanWriter.write(mapper.map(pageImages.getContent(), mapper::mapGenesys));
						return pageImages;
					});
					metadataUpdateWriter.flush();
					status.totalElements = images.getTotalElements();
					lastImages = images;

					if (!images.isEmpty()) {
						InputStreamResource fileResource = new InputStreamResource(new FileInputStream(tempMetadataFile)) {
							@Override
							public String getFilename() {
								return "metadata.csv";
							}

							@Override
							public long contentLength() {
								return tempMetadataFile.length();
							}
						};

						MultiValueMap<String, Object> body = new LinkedMultiValueMap<>();
						body.add("file", fileResource);

						HttpHeaders multipartHeaders = new HttpHeaders();
						multipartHeaders.setContentType(MediaType.MULTIPART_FORM_DATA);
						multipartHeaders.addAll(headers);

						HttpEntity<MultiValueMap<String, Object>> requestEntity = new HttpEntity<>(body, multipartHeaders);

						log.warn("Sending metadata for {} attachments", images.getNumberOfElements());
						var response = restTemplate.postForEntity(apiUrl + "/api/v2/repository/upload/folder-metadata/", requestEntity, TreeMap.class);
						Map<String, String> errors = response.getBody();
						// Errors -> uuid + errorMessage or globalExceptionName + errorMessage
						if (errors != null && !errors.isEmpty()) {
							errors.forEach((k, v) -> log.warn("Error: {} {}", k, v));
							var imageUuidMap = images.stream().collect(Collectors.toMap(GenesysAttachment::getUuid, i -> i));
							status.errorCounter += errors.size();
							status.successCounter += Math.max(0, images.getNumberOfElements() - errors.size());
							for(var entry: errors.entrySet()) {
								GenesysAttachment failedMetadata = null;
								try {
									var metadataUuid = UUID.fromString(entry.getKey());
									failedMetadata = imageUuidMap.remove(metadataUuid);
								} catch (Throwable e) {
									// All image updates failed
									imageUuidMap.forEach((k, v) -> {
										v.setStatus(ImageSyncStatus.FAILED);
										v.setErrorMessage(entry.getValue());
									});
									imageUuidMap.clear();
									break;
								}
								if (failedMetadata != null) {
									failedMetadata.setStatus(ImageSyncStatus.FAILED);
									failedMetadata.setErrorMessage(entry.getValue());
								}
							}
							imageUuidMap.forEach((k,v) -> v.setStatus(ImageSyncStatus.SYNCHRONIZED));
						} else {
							status.successCounter += images.getNumberOfElements();
							images.forEach(i -> i.setStatus(ImageSyncStatus.SYNCHRONIZED));
						}
						List<GenesysAttachment> toSave = images.getContent();
						transactionHelper.executeInTransaction(false, () -> genesysAttachmentRepository.saveAll(toSave));
					}

					// Advance page 
					page = page.next();
				} catch (Throwable e) {
					log.warn("Metadata update error: {}", e.getMessage(), e);
					status.status = ListProcessStatus.FAILED;
					status.errorMessage = e.getMessage();
					return; // Bail out!
				}
			} while (lastImages != null && !lastImages.isLast());

			log.warn("Metadata successfully sent to Genesys");
			status.status = ListProcessStatus.SYNCHRONIZED;
	}

	private StatefulBeanToCsv<GenesysAttachmentDTO> createImageWriter(CSVWriter writer) {
		return new StatefulBeanToCsvBuilder<GenesysAttachmentDTO>(writer)
			.withMappingStrategy(mappingStrategy)
			.withApplyQuotesToAll(true)
			.build();
	}

	/**
	 * Save all public accession attachments to LOCAL list.
	 *
	 * @param sites List of sites to consider
	 */
	private void populateFromSiteAttachments(List<Site> sites) {

		var qAiA = QAccessionInvAttach.accessionInvAttach;
		var qI = new QInventory("i");
		var qA = new QAccession("a");
		var qS = new QSite("asite");
		var qRF = new QRepositoryFile("rf");
		var predicate = qS.in(sites)
			.and(qA.isWebVisible.eq("Y")) // Only flagged as public
			.and(qI.formTypeCode.eq(Inventory.SYSTEM_INVENTORY_FTC)) // Accession attachments only
			.and(qAiA.isWebVisible.eq("Y")) // Only flagged as public
			.and(qRF.isNotNull()) // Must have a file in the repository
			.and(qRF.contentType.startsWithIgnoreCase("image/"))
		;

		// Use one big read-only transaction to paginate through all records
		transactionHelper.executeInTransaction(true, () -> {

			var totalCount = jpaQueryFactory.from(qAiA)
				.join(qAiA.inventory(), qI)
				.join(qI.accession(), qA)
				.join(qA.site(), qS)
				.join(qAiA.repositoryFile(), qRF)
				.select(qAiA.count())
				.where(predicate)
				.fetchOne();

			var localsQuery = jpaQueryFactory.from(qAiA)
				.join(qAiA.inventory(), qI).fetchJoin()
				.leftJoin(qI.extra()).fetchJoin()
				.join(qI.accession(), qA).fetchJoin()
				.join(qA.site(), qS).fetchJoin()
				.leftJoin(qA.accessionPedigree()).fetchJoin()
				.join(qAiA.repositoryFile(), qRF).fetchJoin()
				.select(qAiA)
				.where(predicate);

			var page = PageRequest.of(0, 1000, Sort.by("id"));
			var locals = localsQuery
				.limit(page.getPageSize())
				.offset(page.getOffset())
				.fetch();

			var listStatus = new SyncListProcessStatus(ListProcessStatus.IN_PROGRESS).totalElements(totalCount);
			var status = syncProcessStatus.get(); // Get current status
			if (status != null) {
				status.listStatus(listStatus);
			}

			while (locals.size() > 0) {
				var batch = locals.stream().map(acceInvAtt -> {
					entityManager.detach(acceInvAtt); // Detach record, should keep memory use down
					var attach = mapper.mapGenesys(acceInvAtt);
					attach.setType(ImageSyncList.LOCAL);
					attach.setStatus(ImageSyncStatus.FAILED); // Use failed so it doesn't get used for sync
					return attach;
				}).collect(Collectors.toList());

				// A small transaction to save the batch
				transactionHelper.executeInTransaction(false, () -> {
					log.warn("Saving {} LOCAL attachments", batch.size());
					listStatus.successCounter += batch.size();
					return genesysAttachmentRepository.saveAllAndFlush(batch);
				});

				listStatus.successCounter += batch.size(); // Increment progress
				page = page.next();
				locals = localsQuery
					.limit(page.getPageSize())
					.offset(page.getOffset())
					.fetch();
			}
			listStatus.status(ListProcessStatus.SYNCHRONIZED);
			return Void.TYPE;
		});
	}

	private final static Pattern PATTERN_DASH = Pattern.compile("-");
	private final static Cache<String, List<String>> ALTACCENUMB_CACHE = CacheBuilder.newBuilder().maximumSize(500).expireAfterWrite(1, TimeUnit.MINUTES).build();

	/**
	 * Find all matching accessions; it could be that accession ACCENUMBs contain / which we replace with -
	 */
	private static List<String> getAccessionNumberAlternatives(String acceNumb) {
		try {
			return ALTACCENUMB_CACHE.get(acceNumb, () -> {
				var slasher = PATTERN_DASH.matcher(acceNumb);

				// List of possible alternative ACCENUMB with each - replaced by /
				var acceNumbCombinationsCharArr = new LinkedList<char[]>();
				acceNumbCombinationsCharArr.add(acceNumb.toCharArray());

				// Populate alternative ACCENUMB with all combinations of - replaced with /
				slasher.results().forEach((match) -> {
					// For each existing alt name, add a new alt name with this slash replaced by -
					for (var i = acceNumbCombinationsCharArr.size() - 1; i >= 0; i--) {
						var another = Arrays.copyOf(acceNumbCombinationsCharArr.get(i), acceNumbCombinationsCharArr.get(i).length);
						another[match.start()] = '/';
						acceNumbCombinationsCharArr.add(another);
						// log.warn("Added {}", new String(another));
					}
				});

				var acceNumbCombinations = acceNumbCombinationsCharArr.stream().map(String::new).collect(Collectors.toList());
				return acceNumbCombinations;
			});
		} catch (ExecutionException e) {
			log.error("Failed to calculate accession number alternatives: {}", e.getMessage());
			throw new RuntimeException(e);
		}
	}

	/**
	 * Extract {instCode, acceNumb} from imagePath
	 * @return Pair of {@code instCode, acceNumb} <b>OR</b> {@code null} if the path doesn't match the expected format
	 */
	private static Pair<String, String> parsePathToInstCodeAcceNumb(String imagePath) {
		var genesysImagePathParts = imagePath.split("/");
		if (genesysImagePathParts.length < 5) {
			// Wrong path
			log.debug("Path \"{}\" does not match expected format", imagePath);
			return null;
		}
		var instCode = genesysImagePathParts[2];
		var acceNumb = genesysImagePathParts[4];
		log.trace("Genesys attachment is for INSTCODE={} ACCENUMB={}", instCode, acceNumb);
		return Pair.of(instCode, acceNumb);
	}

	@Getter
	@AllArgsConstructor
	public static final class CandidateInventory {
		private String faoInstituteNumber;
		private String accessionNumber;
		private String isWebVisible;
		private Long inventoryId;
		private String inventoryNumber;
		private String formTypeCode;
	}

	/**
	 * Find candidate accession's system inventories (formTypeCode=**) for a batch of attachments.
	 * @param genesysAttachments
	 */
	private List<CandidateInventory> loadCandidateInventories(@NonNull List<GenesysAttachmentDTO> genesysAttachments) {

		var instCodeAndAlternativeAcceNumbs = genesysAttachments.stream()
		.map(genesysAttach -> parsePathToInstCodeAcceNumb(genesysAttach.getPath()))
		.filter(Objects::nonNull) // Remove nulls
		.map(instCodeAndAcceNumb -> { // Find all alternatives for acceNumb
			var instCode = instCodeAndAcceNumb.getFirst();
			var acceNumb = instCodeAndAcceNumb.getSecond();
			return Pair.of(instCode, getAccessionNumberAlternatives(acceNumb));
		})
		.collect(Collectors.toList());

		log.info("Got {} combinations for {} attachments", instCodeAndAlternativeAcceNumbs.size(), genesysAttachments.size());
		if (instCodeAndAlternativeAcceNumbs.size() == 0) return List.of(); // Nothing to search for
		log.debug("They are: {}", instCodeAndAlternativeAcceNumbs);

		// Build one big beautiful OR query
		var qI = QInventory.inventory;
		var qA = new QAccession("acce");
		var qS = new QSite("asite");

		Predicate allTheOrExpressions = null;
		for (var instCodeAndListOfAcceNumb : instCodeAndAlternativeAcceNumbs) {
			log.trace("Processing {}", instCodeAndListOfAcceNumb);
			if (instCodeAndListOfAcceNumb != null) {
				var thisCombo = qS.faoInstituteNumber.eq(instCodeAndListOfAcceNumb.getFirst()).and(qA.accessionNumber.in(instCodeAndListOfAcceNumb.getSecond()));
				allTheOrExpressions = allTheOrExpressions == null ? thisCombo : ExpressionUtils.or(allTheOrExpressions, thisCombo);
			}
		}
		if (allTheOrExpressions == null) return List.of(); // Nothing to search for

		var query = jpaQueryFactory
			.select(Projections.constructor(CandidateInventory.class, qS.faoInstituteNumber, qA.accessionNumber, qA.isWebVisible, qI.id, qI.inventoryNumber, qI.formTypeCode))
			.from(qI)
			.join(qI.accession(), qA)
			.join(qA.site(), qS)
			.where(qI.formTypeCode.eq(Inventory.SYSTEM_INVENTORY_FTC)
				.and(allTheOrExpressions)
			)
			.limit(1001); // Max 1000 matches!

		// Query the database for these pairs
		var allMatches = query.fetch();
		log.info("Found {} matches!", allMatches.size());
		// Try to avoid buggy queries that would load all inventories from the database
		if (allMatches.size() > 1000) throw new RuntimeException("Too many records fetched for " + genesysAttachments.size() + " attachments");
		return allMatches;
	}


	/**
	 * Read metadata stream and save to database as REMOTE
	 * @param numberOfRecords Number of records in the CSV file
	 * @param inputStream The CSV source stream
	 * @throws IOException 
	 */
	private void populateFromGenesysAttachments(long numberOfRecords, final InputStream inputStream) throws IOException {

		var listStatus = new SyncListProcessStatus(ListProcessStatus.IN_PROGRESS).totalElements(numberOfRecords);
		{ // Set current list status
			var status = syncProcessStatus.get(); // Get current status
			if (status != null) {
				status.listStatus(listStatus);
			}
		}

		try (CSVReader reader = new CSVReaderBuilder(new BufferedReader(new InputStreamReader(inputStream, ENCODING)))
			.withCSVParser(new CSVParserBuilder()
			.withSeparator(SEPARATOR)
			.withQuoteChar(QUOTE_CHAR)
			.withEscapeChar(ESCAPE_CHAR)
			.withStrictQuotes(false)
			.withIgnoreQuotations(true)
			.build()).build()) {

			CsvToBean<GenesysAttachmentDTO> csvToBean = new CsvToBeanBuilder<GenesysAttachmentDTO>(reader)
				.withType(GenesysAttachmentDTO.class)
				.withIgnoreLeadingWhiteSpace(true)
				.build();

			// Keep partition size low, database queries might get intense and hit query parameter limits
			// size=100: For 40,000 images that is 400 queries
			Iterators.partition(
				csvToBean.stream()
					.takeWhile(attach -> { // Stop reading CSV if operation canceled
						var status = syncProcessStatus.get(); // Get current status
						if (status == null || status.status != ProcessStatus.DOWNLOADING) {
							log.warn("Stopping read of CSV, status is {}", status);
							listStatus.status(ListProcessStatus.STOPPED).errorMessage("Processing was stopped");
							return false;
						} else {
							listStatus.successCounter++; // Increment counter
						}
						return true;
					})
					.filter(attach -> Strings.CI.startsWith(attach.getContentType(), "image/")).iterator(),
				100)
				.forEachRemaining(genesysAttachments -> {

					 // Find candidate inventories for everything in this batch
					var candidateInventories = transactionHelper.executeInTransaction(true, () -> LoggerHelper.withSqlLogging(() -> loadCandidateInventories(genesysAttachments)));
					// var candidateInventories = transactionHelper.executeInTransaction(true, () -> loadCandidateInventories(genesysAttachments));

					log.info("Found {} candidate inventories for {} attachments", candidateInventories.size(), genesysAttachments.size());

					var getInventoryForPath = (BiFunction<String, String, CandidateInventory>) (instCode, acceNumb) -> {
						var acceNumbs = getAccessionNumberAlternatives(acceNumb);
						var candidates = acceNumbs.stream().flatMap(altNumb -> candidateInventories.stream().filter(i -> Strings.CI.equals(i.getFaoInstituteNumber(), instCode) && Strings.CI.equals(i.getAccessionNumber(), altNumb))).collect(Collectors.toList());
						if (candidates.size() > 1) {
							log.debug("Multiple inventories resolved to the same accession {}: {}", acceNumb, candidates.stream().map(CandidateInventory::getInventoryNumber).collect(Collectors.joining(", ")));
							return null;
						} else if (candidates.size() == 1) {
							return candidates.get(0);
						} else {
							return null; // No matches
						}
					};

					var batch = new LinkedList<GenesysAttachment>();
					genesysAttachments.forEach(attach -> {
						attach.setType(ImageSyncList.REMOTE);
						attach.setStatus(ImageSyncStatus.FAILED); // So it doesn't get synced by mistake
						var genesysAttach = mapper.mapGenesys(attach);
						var instCodeAndAcceNumb = parsePathToInstCodeAcceNumb(genesysAttach.getPath());
						var instCode = instCodeAndAcceNumb.getFirst();
						var acceNumb = instCodeAndAcceNumb.getSecond();

						try {
							var inventory = getInventoryForPath.apply(instCode, acceNumb);
							if (inventory == null || Objects.equals(inventory.getIsWebVisible(), "N")) {
								log.error("No such local accession INSTCODE={} ACCENUMB={} or accession is not web visible", instCode, acceNumb);
								// Skip image if accession is missing or is not web visible
							} else {
								genesysAttach.setInventory(new Inventory(inventory.getInventoryId()));
								batch.add(genesysAttach);
							}
						} catch (Throwable e) {
							// An error may be thrown if we cannot resolve to a single inventory
							log.warn("Skipping INSTCODE={} ACCENUMB={}: {}", instCode, acceNumb, e.getMessage());
						}
					});
					transactionHelper.executeInTransaction(false, () -> {
						log.debug("Saving {} REMOTE attachments", batch.size());
						return genesysAttachmentRepository.saveAllAndFlush(batch);
					});
				});
		}

		listStatus.status(ListProcessStatus.SYNCHRONIZED);
	}

	private void compareImages() {

		SyncListProcessStatus listStatus = new SyncListProcessStatus(ListProcessStatus.IN_PROGRESS);
		{
			var status = syncProcessStatus.get(); // Get current status
			if (status != null && status.status() == ProcessStatus.COMPARING) {
				status.listStatus(listStatus);
			} else {
				return; // Just stop
			}
		}
		
		QGenesysAttachment qAttach = QGenesysAttachment.genesysAttachment;

		// Pairs (path, originalFilename)
		var pathOriginalFilenamePairs = transactionHelper.executeInTransaction(true, () -> {
			return jpaQueryFactory
				.select(qAttach.path, qAttach.originalFilename, qAttach.count())
				.from(qAttach)
				.groupBy(qAttach.path, qAttach.originalFilename)
				.having(qAttach.count().gt(1))
				.fetch();
		});

		int pageSize = 100;
		int totalPairs = pathOriginalFilenamePairs.size();
		int pageCount = (int) Math.ceil((double) totalPairs / pageSize);
		listStatus.totalElements(totalPairs);

		for (int page = 0; page < pageCount; page++) {
			{
				var status = syncProcessStatus.get(); // Get current status
				if (status == null || status.status() != ProcessStatus.COMPARING) {
					log.warn("Comparing local and remote attachments was stopped!");
					listStatus.errorCounter++;
					listStatus.errorMessage("Comparing local and remote attachments was stopped");
					return;
				}
			}
			int fromIndex = page * pageSize;
			int toIndex = Math.min(fromIndex + pageSize, totalPairs);
			log.warn("Comparing page {} of {}, rows {}-{}", page + 1, pageCount, fromIndex, toIndex);

			var pairsPage = pathOriginalFilenamePairs.subList(fromIndex, toIndex);

			BooleanBuilder predicate = new BooleanBuilder();
			for (Tuple pair : pairsPage) {
				String path = pair.get(qAttach.path);
				String originalFilename = pair.get(qAttach.originalFilename);

				predicate.or(
					qAttach.path.eq(path)
						.and(qAttach.originalFilename.eq(originalFilename))
				);
			}

			transactionHelper.executeInTransaction(false, () -> {
				List<GenesysAttachment> attachments = jpaQueryFactory
					.selectFrom(qAttach)
					.where(predicate.and(qAttach.type.in(ImageSyncList.LOCAL, ImageSyncList.REMOTE)))
					.orderBy(qAttach.path.asc(), qAttach.originalFilename.asc(), qAttach.type.asc())	// Pair (LOCAL -> REMOTE)
					.fetch();

				List<GenesysAttachment> toUpdate = new LinkedList<>();
				List<GenesysAttachment> toRemove = new LinkedList<>();
				for (int i = 0; i < attachments.size(); i += 2) {
					var local = attachments.get(i);
					var remote = attachments.get(i + 1);
					if (!Objects.equals(local.getPath(), remote.getPath()) || !Strings.CI.equals(local.getOriginalFilename(), remote.getOriginalFilename())) {
						log.error("Invalid LOCAL-REMOTE combination: {} {} != {} {}", local.getPath(), local.getOriginalFilename(), remote.getPath(), remote.getOriginalFilename());
						listStatus.errorCounter++;
						listStatus.errorMessage("Invalid LOCAL-REMOTE combination, path+originalFilename do not match");
						throw new RuntimeException("Invalid LOCAL-REMOTE combination, path+originalFilename do not match");
					}
					listStatus.successCounter++; // Increment progress
					if (!Objects.equals(local.getMd5(), remote.getMd5())) {
						local.setStatus(ImageSyncStatus.PENDING);
						local.setType(imageUpdate);
						local.setUuid(remote.getUuid());
						toUpdate.add(local);
					} else if (local.equalTo(remote)) {
						local.setStatus(ImageSyncStatus.SYNCHRONIZED);
						local.setType(uploaded);
						local.setUuid(remote.getUuid());
						toUpdate.add(local);
					} else {
						local.setStatus(ImageSyncStatus.PENDING);
						local.setType(metadataUpdate);
						local.setUuid(remote.getUuid());
						toUpdate.add(local);
					}
					toRemove.add(remote);
				}
				genesysAttachmentRepository.saveAllAndFlush(toUpdate);
				genesysAttachmentRepository.deleteAll(toRemove);
				log.warn("Updated {}, removed {}", toUpdate.size(), toRemove.size());
				return true;
			});
		}

		// Finalize
		transactionHelper.executeInTransaction(false, () -> {
			jpaQueryFactory.update(qAttach)
				.set(qAttach.status, ImageSyncStatus.PENDING)
				.set(qAttach.type, imageDownload)
				.where(qAttach.type.eq(ImageSyncList.REMOTE))
				.execute();
			jpaQueryFactory.update(qAttach)
				.set(qAttach.status, ImageSyncStatus.PENDING)
				.set(qAttach.type, imageUpload)
				.where(qAttach.type.eq(ImageSyncList.LOCAL))
				.execute();
			return true;
		});

		log.warn("Done writing results.");
		listStatus.status(ListProcessStatus.SYNCHRONIZED);
	}

	private void deleteTemporaryFile(File sourceFile) {
		try {
			if (sourceFile != null && sourceFile.exists()) {
				log.warn("Deleting temporary file {}", sourceFile.getAbsolutePath());
				if (!sourceFile.delete()) {
					log.warn("Temporary file {} was not deleted", sourceFile.getAbsolutePath());
				}
			}
		} catch (Throwable e) {
			log.warn("Temporary file {} was not deleted", sourceFile.getAbsolutePath());
		}
	}

}