VirtualDataviewServiceImpl.java

/*
 * Copyright 2022 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.compatibility.service.impl;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;
import java.util.Objects;
import java.util.Optional;
import java.util.Set;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

import javax.annotation.Resource;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.genesys.blocks.model.BasicModel;
import org.genesys.blocks.model.EmptyModel;
import org.gringlobal.api.exception.InvalidApiUsageException;
import org.gringlobal.compatibility.component.SysTableComponent;
import org.gringlobal.compatibility.service.VirtualDataviewService;
import org.gringlobal.model.AuditedModel;
import org.gringlobal.model.SysTable;
import org.gringlobal.model.SysTableField;
import org.gringlobal.soap.Datatable;
import org.gringlobal.soap.Datatable.Column;
import org.gringlobal.soap.Datatable.HasChanges;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.convert.ConversionService;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.ReflectionUtils;

import com.querydsl.core.types.ExpressionUtils;
import com.querydsl.core.types.Order;
import com.querydsl.core.types.OrderSpecifier;
import com.querydsl.core.types.Predicate;
import com.querydsl.core.types.dsl.PathBuilder;
import com.querydsl.jpa.impl.JPAQueryFactory;

@Service
@Slf4j
public class VirtualDataviewServiceImpl implements VirtualDataviewService {

	public static final String GGCE_PREFIX = "gget_";

	// Local VM cache of Dataview's primary key parameter names
	private Map<Long, String> sysTablePrimaryKeyDataviewParameters = new HashMap<>(200);

	@Resource
	private Set<Class<?>> entityClassSet;

	@Autowired
	private SysTableComponent sysTableComponent;

	@Autowired
	protected JPAQueryFactory jpaQueryFactory;

	@Autowired
	private ConversionService conversionService;

	@Value("${dataview.max.rows}")
	private int maxRowsLimit;

	@Override
	public void addVirtualDataviews(Datatable datatable) {
		var autoId = new AtomicInteger(datatable.getRows().size() == 0 ? 0 : ((Number) datatable.getRows().get(datatable.getRows().size() - 1).getValue(0)).intValue());

		entityClassSet.forEach(entity -> {
			SysTable sysTable = sysTableComponent.getSysTable(entity);
			if (Objects.isNull(sysTable)) {
				return;
			}
			Integer dataviewId = autoId.incrementAndGet();
			String dataviewName = GGCE_PREFIX.concat(entity.getSimpleName());
			String title = "*" + entity.getSimpleName();
			String description = "GGCE " + entity.getSimpleName();
			String isEnabled = sysTable.getIsEnabled();
			String isReadonly = sysTable.getIsReadonly();
			String categoryCode = "Client";
			String databaseAreaCode = "GGCE"; // sysTable.getDatabaseAreaCode();
			Integer databaseAreaCodeSortOrder = 0;

			Optional<SysTableField> primaryKeyField = sysTable.getFields().stream()
				.filter(field -> Objects.equals(field.getIsPrimaryKey(), "Y"))
				.findFirst();
			String primaryKey = primaryKeyField.map(SysTableField::getFieldName).orElse(null);

			log.debug("Injecting dataview id={} name={}", dataviewId, dataviewName);
			datatable.addRow(HasChanges.original, dataviewId, dataviewName, title, description, isEnabled, isReadonly, categoryCode, databaseAreaCode, databaseAreaCodeSortOrder, primaryKey);
		});
	}

	@Override
	public void addVirtualDataviewParameters(Datatable datatable, String dataview) {
		String entityName = StringUtils.removeStart(dataview, GGCE_PREFIX);
		Optional<Class<?>> entityClass = entityClassSet.stream().filter(entity -> StringUtils.equalsIgnoreCase(entity.getSimpleName(), entityName)).findFirst();
		if (entityClass.isPresent()) {
			SysTable sysTable = sysTableComponent.getSysTable(entityClass.get());
			if (Objects.nonNull(sysTable)) {
				String primaryKeyParameter = getPrimaryKeyDataviewParameter(sysTable);
				log.debug("Injecting dataview parameter {} INTEGERCOLLECTION sortOrder={}", primaryKeyParameter, 0);
				datatable.addRow(HasChanges.original, primaryKeyParameter, "INTEGERCOLLECTION", 0);
				// Add all @ManyToOne
				sysTable.getFields().stream().filter(f -> Objects.equals("Y", f.getIsForeignKey())).forEach(foreignKey -> {
					log.debug("Virtual DV {} has FK {}", dataview, foreignKey.getFieldName());
					datatable.addRow(HasChanges.original, getPKParameterForTable(foreignKey.getForeignKeyTableField().getTable().getTableName()), "INTEGERCOLLECTION", 0);
				});
			} else {
				log.warn("No sysTable class {} for {}", entityName, dataview);
			}
		} else {
			log.warn("No entity class {} for {}", entityName, dataview);
		}
	}

	/**
	 * Virtual dataviews only support :idlist (or parameter name for its primary key field).
	 *
	 * Other parameters (e.g. by ManyToOne fields) are not supported, so we can't filter
	 * Inventory directly by accession_id.
	 */
	@Override
	@Transactional(readOnly = true)
	@PreAuthorize("isAuthenticated()")
	public Datatable getData(String dataviewName, Map<String, String> parameters, final int offset, final int limit, final String options) throws Exception {

		log.info("Dataview {} offset={} limit={}", dataviewName, offset, limit);
		log.debug("Client parameters: {}", parameters);
		log.debug("Client options: {}", options);

		if (limit > maxRowsLimit) {
			throw new InvalidApiUsageException("The server is configured with a limit of " + maxRowsLimit + " rows. You requested " + limit);
		}

		String entityName = StringUtils.removeStartIgnoreCase(dataviewName, GGCE_PREFIX);

		Optional<Class<?>> optionalEntityClass = entityClassSet.stream().filter(entity -> StringUtils.equalsIgnoreCase(entity.getSimpleName(), entityName)).findFirst();
		if (! optionalEntityClass.isPresent()) {
			throw new InvalidApiUsageException("Dataview entity not found for " + entityName);
		}

		Class<?> entityClass = optionalEntityClass.get();
		SysTable sysTable = sysTableComponent.getSysTable(entityClass);
		if (Objects.isNull(sysTable)) {
			throw new InvalidApiUsageException("Dataview sysTable not found for " + entityClass);
		}


		var foreignKeyLookups = new HashMap<String, String>(); // Foreign key param to entity property

		// Add datatable properties
		var datatable = new Datatable(dataviewName);
		datatable.setProp("is_readonly", sysTable.getIsReadonly());
		datatable.setProp("title", dataviewName);
		datatable.setProp("description", "GGCE virtual dataview for table " + sysTable .getTableName());

		// Define datatable columns
		var entityFields = new ArrayList<Field>(sysTable.getFields().size());
		for (var sysTableField : sysTable.getFields()) {
			Field propertyField = ReflectionUtils.findField(entityClass, sysTableField.getPropertyName());
			entityFields.add(propertyField);
			ReflectionUtils.makeAccessible(propertyField);

			Column column;
			// If propertyField is an entity, then use Long
			if (AuditedModel.class.isAssignableFrom(propertyField.getType())) {
				column = datatable.addColumn(sysTableField.getFieldName(), Long.class);
			} else if (EmptyModel.class.isAssignableFrom(propertyField.getType())) {
				column = datatable.addColumn(sysTableField.getFieldName(), Long.class);
			} else {
				column = datatable.addColumn(sysTableField.getFieldName(), propertyField.getType());
			}
			column.setData("Caption", sysTableField.getFieldName());
			column.setProp("title", prettyLabel(sysTableField.getFieldName()));
			column.setProp("description", sysTableField.getFieldName()); //todo

			column.setProp("dataview_name", datatable.getName());
			column.setProp("dataview_field_name", sysTableField.getFieldName());

			column.setProp("table_name", sysTable.getTableName());
			column.setProp("table_field_name", sysTableField.getFieldName());
			column.setProp("table_field_data_type_string", sysTableField.getFieldType());
			if ("DATETIME".equals(sysTableField.getFieldType())) {
				// msdata:DateTimeMode="Unspecified"
				column.setData("DateTimeMode", "Unspecified");
			}

			column.setReadonly(sysTableField.getIsReadonly().equals("Y"));
			column.setProp("is_readonly", sysTableField.getIsReadonly());
			column.setProp("is_visible", "Y"); // No corresponding field?
			column.setProp("is_primary_key", sysTableField.getIsPrimaryKey());
			column.setProp("is_autoincrement", sysTableField.getIsAutoincrement());
			column.setProp("is_foreign_key", sysTableField.getIsForeignKey());
			column.setProp("is_nullable", sysTableField.getIsNullable());
			column.setProp("default_value", sysTableField.getDefaultValue());

			column.setProp("group_name", sysTableField.getGroupName()); // code value
			column.setProp("gui_hint", sysTableField.getGuiHint());
			column.setProp("max_length", Integer.toString(sysTableField.getMaxLength()));

			// Enable Lookups
			column.setProp("foreign_key_dataview_name", sysTableField.getForeignKeyDataviewName());
			if (StringUtils.isNotBlank(sysTableField.getForeignKeyDataviewName())) {
				// msprop:foreign_key_dataview_param=":createddate=__createddate__;:modifieddate=__modifieddate__;:valuemember=__valuemember__;:startpkey=__startpkey__;:stoppkey=__stoppkey__"
				column.setProp("foreign_key_dataview_param", ":createddate=__createddate__;:modifieddate=__modifieddate__;:valuemember=__valuemember__;:startpkey=__startpkey__;:stoppkey=__stoppkey__");

				// Register lookup

				if (StringUtils.startsWith(sysTableField.getForeignKeyDataviewName(), VirtualDataviewServiceImpl.GGCE_PREFIX)
					&& StringUtils.endsWith(sysTableField.getForeignKeyDataviewName(), VirtualLookupServiceImpl.LOOKUP_SUFFIX)) {

					String foreignEntity = StringUtils.removeEndIgnoreCase(StringUtils.removeStartIgnoreCase(sysTableField.getForeignKeyDataviewName(), VirtualDataviewServiceImpl.GGCE_PREFIX), VirtualLookupServiceImpl.LOOKUP_SUFFIX);
					foreignKeyLookups.put(getPKParameterForTable(foreignEntity), sysTableField.getPropertyName());
					log.debug("VDV Parameter {} -> {}", getPKParameterForTable(foreignEntity), sysTableField.getPropertyName());
				}
			}

			if (sysTableField.getForeignKeyTableField() != null) {
//				column.setProp("gui_hint", GuiHint.INTEGER_CONTROL.name()); // Disable lookups

				// msprop:foreign_key_field_name=""
				column.setProp("foreign_key_field_name", sysTableField.getForeignKeyTableField().getFieldName());
				// msprop:foreign_key_table_field_name="taxonomy_species_id"
				column.setProp("foreign_key_table_field_name", sysTableField.getForeignKeyTableField().getFieldName());

			}
		}

		// get id value from given parameters map
		final String primaryKeyParameter = getPrimaryKeyDataviewParameter(sysTable);

		// Map of entity propertyNames to incoming parameter values
		Map<String, Set<Long>> entityFieldFilters = new HashMap<>();

		foreignKeyLookups.entrySet().forEach(p -> log.debug("VDV foreign key lookups {} -> {}", p.getKey(), p.getValue()));
		parameters.entrySet().forEach(parameter -> {
			if (StringUtils.isBlank(parameter.getValue())) {
				return; // Skip blank parameter
			}
			String propertyName = null;
			if (StringUtils.equals(parameter.getKey(), primaryKeyParameter) || StringUtils.equals(parameter.getKey(), ":idlist")) {
				propertyName = "id";
			} else {
				propertyName = foreignKeyLookups.get(parameter.getKey());
				log.debug("VDV foreign key lookup for {} is {}", parameter.getKey(), propertyName);
				if (propertyName == null) {
					return; // Skipping undefined parameter
				}
			}

			// This is usually a list of values
			Set<Long> idValues = Arrays.stream(parameters.get(parameter.getKey()).split(","))
				// To Long
				.map((v) -> conversionService.convert(v, Long.class))
				// Cleanup
				.filter((v) -> v != null).collect(Collectors.toSet());

			if (! idValues.isEmpty()) {
				log.info("VDV Parameter {} for property {}: {}", parameter.getKey(), propertyName, idValues);
				entityFieldFilters.put(propertyName, idValues);
			}
		});

		if (entityFieldFilters.isEmpty()) {
			return datatable;
		}
		if (log.isDebugEnabled()) {
			entityFieldFilters.entrySet().forEach(p -> log.debug("VDV Parameter {} = {}", p.getKey(), p.getValue()));
		}

		var root = new PathBuilder<>(entityClass, "t");
		PathBuilder<Long> pkField = root.get("id", Long.class);

		var q = jpaQueryFactory.selectFrom(root);

		Collection<Predicate> filters = new ArrayList<>();
		entityFieldFilters.entrySet().forEach(parameter -> {
			log.debug("Applying VDV Parameter {} = {}", parameter.getKey(), parameter.getValue());
			if (StringUtils.equals(parameter.getKey(), "id")) {
				var property = root.get(parameter.getKey());
				filters.add(property.in(parameter.getValue()));
			} else {
				var property = root.get(parameter.getKey()).get("id");
				filters.add(property.in(parameter.getValue()));
			}
		});

		q.where(ExpressionUtils.anyOf(filters));

		// always order by id
		q.orderBy(new OrderSpecifier<>(Order.ASC, pkField));

		// Apply limits

		if (limit > 0) {
			q.limit(limit);
		}
		if (offset > 1) {
			q.offset(offset);
		}

		// Get entities
		var rows = q.fetch();

		// Add rows to datatable
		var rowData = new ArrayList<>(sysTable.getFields().size());
		rows.forEach(row -> {
			log.trace("Adding row for {}", row);
			rowData.clear();
			for (var entityField : entityFields) {
				var d = ReflectionUtils.getField(entityField, row);
				if (d == null) {
					rowData.add(null);
				} else if (AuditedModel.class.isAssignableFrom(entityField.getType())) {
					rowData.add(((AuditedModel)d).getId());
				} else if (BasicModel.class.isAssignableFrom(entityField.getType())) {
					rowData.add(((BasicModel)d).getId());
				} else if (EmptyModel.class.isAssignableFrom(entityField.getType())) {
					rowData.add(((EmptyModel)d).getId());
				} else {
					rowData.add(d);
				}
			}
			datatable.addRow(HasChanges.original, rowData.toArray());
		});

//		datatable.acceptChanges(); // All rows are HasChanges.original
		return datatable;
	}

	private String prettyLabel(String fieldName) {
		return StringUtils.capitalize(fieldName.replace("_id", " ID").replaceAll("_", " "));
	}

	private String getPrimaryKeyDataviewParameter(SysTable sysTable) {
		// Use local VM cache
		return sysTablePrimaryKeyDataviewParameters.computeIfAbsent(sysTable.getId(), st -> {
			var pkField = sysTable.getFields().stream()
				.filter(sysTableField -> Objects.equals("Y", sysTableField.getIsPrimaryKey()))
				.findFirst().orElse(null);
			if (pkField != null) {
				return ":".concat(pkField.getFieldName().replaceAll("_", ""));
			} else {
				log.warn("Using primary key parameter name based on table name for {}", sysTable.getTableName());
				return getPKParameterForTable(sysTable.getTableName());
			}
		});
	}

	private String getPKParameterForTable(String tableName) {
		return ":".concat(tableName.toLowerCase().replaceAll("_", "")).concat("id");
	}

}