SecurityEngineUtils.java

/*******************************************************************************
 * Copyright 2015 Defense Health Agency (DHA)
 *
 * If your use of this software does not include any GPLv2 components:
 * 	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.
 * ----------------------------------------------------------------------------
 * If your use of this software includes any GPLv2 components:
 * 	This program is free software; you can redistribute it and/or
 * 	modify it under the terms of the GNU General Public License
 * 	as published by the Free Software Foundation; either version 2
 * 	of the License, or (at your option) any later version.
 *
 * 	This program is distributed in the hope that it will be useful,
 * 	but WITHOUT ANY WARRANTY; without even the implied warranty of
 * 	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * 	GNU General Public License for more details.
 *******************************************************************************/
package prerna.auth.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.UUID;
import java.util.stream.Collectors;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.javatuples.Pair;

import prerna.auth.AccessPermissionEnum;
import prerna.auth.AccessToken;
import prerna.auth.AuthProvider;
import prerna.auth.User;
import prerna.auth.access.ModelUsageLimit;
import prerna.auth.access.ModelUsageLimitKey;
import prerna.auth.access.PermissionChange;
import prerna.auth.access.PermissionErrorRules;
import prerna.auth.access.PermissionGrant;
import prerna.auth.access.PermissionProblem;
import prerna.auth.access.PermissionSubject;
import prerna.auth.access.PermissionTargetResource;
import prerna.engine.api.IEngine;
import prerna.engine.api.IRDBMSEngine;
import prerna.engine.api.IRawSelectWrapper;
import prerna.engine.impl.SmssUtilities;
import prerna.notifications.NotificationDbUtils;
import prerna.query.querystruct.SelectQueryStruct;
import prerna.query.querystruct.filters.AndQueryFilter;
import prerna.query.querystruct.filters.IQueryFilter;
import prerna.query.querystruct.filters.OrQueryFilter;
import prerna.query.querystruct.filters.SimpleQueryFilter;
import prerna.query.querystruct.joins.IRelation;
import prerna.query.querystruct.joins.SubqueryRelationship;
import prerna.query.querystruct.selectors.QueryColumnOrderBySelector;
import prerna.query.querystruct.selectors.QueryColumnSelector;
import prerna.query.querystruct.selectors.QueryFunctionHelper;
import prerna.query.querystruct.selectors.QueryFunctionSelector;
import prerna.query.querystruct.selectors.QueryIfSelector;
import prerna.rdf.engine.wrappers.WrapperManager;
import prerna.sablecc2.om.PixelDataType;
import prerna.sablecc2.om.execptions.SemossPixelException;
import prerna.usertracking.AuditEventType;
import prerna.usertracking.AuditTargetType;
import prerna.usertracking.UserAuditTrailUtils;
import prerna.util.Constants;
import prerna.util.DIHelper;
import prerna.util.EmailUtility;
import prerna.util.NotificationConstants;
import prerna.util.QueryExecutionUtility;
import prerna.util.SystemEngineRegistry;
import prerna.util.Utility;
import prerna.util.sql.AbstractSqlQueryUtil;

public class SecurityEngineUtils extends AbstractSecurityUtils {

	private static final Logger classLogger = LogManager.getLogger(SecurityEngineUtils.class);

	private static final String INSUFFICIENT_PRIVILEGES = "Insufficient privileges to modify this engine's permissions.";
	private static final String NOT_HIGH_ENOUGH = "The user doesn't have the high enough permissions to modify this users engine permission.";
	private static final String CANNOT_GIVE_OWNER = "Cannot give owner level access to this engine since you are not currently an owner.";
	private static final String INSERT_ENGINE = "INSERT INTO ENGINE (ENGINEID, ENGINENAME, ENGINETYPE, ENGINESUBTYPE, "
			+ "COST, GLOBAL, DISCOVERABLE, CREATEDBY, CREATEDBYTYPE, DATECREATED, ENGINEDISPLAYNAME) "
			+ "VALUES (?,?,?,?,?,?,?,?,?,?,?)";

	/**
	 * Add an entire database into the security db
	 * 
	 * @param engineId
	 * 
	 *                 PLEASE DEFINE GLOBAL
	 *                 {@link #addEngine(String, boolean, User)}
	 */
	@Deprecated
	public static void addEngine(String engineId, User user) {
		if (ignoreDatabase(engineId)) {
			// dont add local master or security db to security db
			return;
		}
		// default engine is not global
		addEngine(engineId, false, user);
	}

	/**
	 * Add an entire database into the security db
	 * 
	 * @param engineId
	 */
	public static void addEngine(String engineId, boolean global, User user) {
		if (ignoreDatabase(engineId)) {
			// dont add local master or security db to security db
			return;
		}
		String smssFile = DIHelper.getInstance().getEngineProperty(engineId + "_" + Constants.STORE) + "";
		Properties prop = Utility.loadProperties(smssFile);

		String engineName = prop.getProperty(Constants.ENGINE_ALIAS);
		if (engineName == null) {
			engineName = engineId;
		}

		String displayName = prop.getProperty(Constants.ENGINE_DISPLAY_NAME);
		if (displayName == null || displayName.trim().isEmpty()) {
			displayName = engineName;
		}

		Object[] typeAndCost = getEngineTypeAndSubTypeAndCost(prop);
		IEngine.CATALOG_TYPE engineType = (IEngine.CATALOG_TYPE) typeAndCost[0];
		boolean engineExists = containsEngineId(engineId);
		if (engineExists) {
			updateEngineTypeAndSubType(engineId, engineType, (String) typeAndCost[1]);
			classLogger.info("Security database already contains engine of type {} with unique id = {}", typeAndCost[0],
					Utility.cleanLogString(SmssUtilities.getUniqueName(prop)));
		} else {
			addEngine(engineId, engineName, displayName, engineType, (String) typeAndCost[1], (String) typeAndCost[2],
					global, user);
		}

		if (engineType == IEngine.CATALOG_TYPE.MODEL) {
			try {
				SecurityModelMetadataUtils.upsertModelMetadata(engineId, prop);
			} catch (Exception e) {
				classLogger.error("Failed to save model metadata for engine {}. The engine is cataloged without it",
						Utility.cleanLogString(engineId), e);
			}
		}

		classLogger.info("Finished adding engine = {}", Utility.cleanLogString(engineId));
	}

	/**
	 * Utility method to get the engine type, subtype, and cost This returns
	 * ENGINETYPE as the enum IEngine.CATALOG_TYPE and not the String format it is
	 * stored in
	 * 
	 * @param prop
	 * @return
	 */
	public static Object[] getEngineTypeAndSubTypeAndCost(Properties smssProp) {
		IEngine.CATALOG_TYPE engineType = null;
		String engineSubType = null;
		String engineCost = "$";

		String rawType = smssProp.get(Constants.ENGINE_TYPE).toString();
		try {
			IEngine emptyClass = (IEngine) Class.forName(rawType).getConstructor().newInstance();
			engineType = emptyClass.getCatalogType();
			engineSubType = emptyClass.getCatalogSubType(smssProp);
		} catch (Exception e) {
			classLogger.warn("Unknown class name = {}", rawType);
		}

		return new Object[] { engineType, engineSubType, engineCost };
	}

	/**
	 * This returns ENGINETYPE as the enum IEngine.CATALOG_TYPE and not the String
	 * format it is stored in
	 * 
	 * @param engineId
	 * @return
	 */
	public static IEngine.CATALOG_TYPE getEngineType(String engineId) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINETYPE"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ENGINEID", "==", engineId));
		List<Object[]> results = QueryExecutionUtility.flushRsToListOfObjArray(securityDb, qs);
		if (results == null || results.isEmpty()) {
			throw new IllegalArgumentException("Could not find engine with id " + engineId);
		}
		Object[] result = results.get(0);
		return IEngine.CATALOG_TYPE.valueOf(result[0] + "");
	}

	/**
	 * This returns ENGINETYPE as the enum IEngine.CATALOG_TYPE and not the String
	 * format it is stored in
	 * 
	 * @param engineId
	 * @return
	 */
	public static Object[] getEngineTypeAndSubtype(String engineId) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINETYPE"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINESUBTYPE"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ENGINEID", "==", engineId));
		List<Object[]> results = QueryExecutionUtility.flushRsToListOfObjArray(securityDb, qs);
		if (results == null || results.isEmpty()) {
			throw new IllegalArgumentException("Could not find engine with id " + engineId);
		}
		Object[] result = results.get(0);
		result[0] = IEngine.CATALOG_TYPE.valueOf(result[0] + "");
		return results.get(0);
	}

	/**
	 * Adds an engine to the security database with its name as its display name.
	 * The engine is not discoverable, and the user, when given, is recorded as its
	 * creator.
	 * 
	 * @param engineId
	 * @param engineName
	 * @param engineType
	 * @param engineSubType
	 * @param engineCost
	 * @param global
	 * @param user
	 */
	public static void addEngine(String engineId, String engineName, IEngine.CATALOG_TYPE engineType,
			String engineSubType, String engineCost, boolean global, User user) {
		addEngine(engineId, engineName, engineName, engineType, engineSubType, engineCost, global, user);
	}

	public static void addEngine(String engineId, String engineName, String engineDisplayName,
			IEngine.CATALOG_TYPE engineType, String engineSubType, String engineCost, boolean global, User user) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		try {
			QueryExecutionUtility.write(securityDb, connection -> {
				insertEngine(connection, securityDb.getQueryUtil(), engineId, engineName, engineDisplayName, engineType,
						engineSubType, engineCost, global, user);
				return null;
			});
		} catch (RuntimeException e) {
			throw e;
		} catch (Exception e) {
			classLogger.error("Failed to create the engine record in the security database", e);
		}
	}

	/**
	 * Inserts an engine's ENGINE row on the caller's connection, in its
	 * transaction. The engine is not discoverable, it was created now, and the
	 * user, when given, is recorded as its creator. A blank display name gives the
	 * engine's name as its display name.
	 *
	 * @param connection the security database connection, in the caller's
	 *                   transaction
	 * @param queryUtil  the security database's query util, for its binders
	 */
	private static void insertEngine(Connection connection, AbstractSqlQueryUtil queryUtil, String engineId,
			String engineName, String engineDisplayName, IEngine.CATALOG_TYPE engineType, String engineSubType,
			String engineCost, boolean global, User user) throws Exception {
		QueryExecutionUtility.executeUpdate(connection, INSERT_ENGINE, ps -> {
			int parameterIndex = 1;
			ps.setString(parameterIndex++, engineId);
			queryUtil.setNullableString(ps, parameterIndex++, engineName);
			ps.setString(parameterIndex++, engineType.toString());
			queryUtil.setNullableString(ps, parameterIndex++, engineSubType);
			queryUtil.setNullableString(ps, parameterIndex++, engineCost);
			ps.setBoolean(parameterIndex++, global);
			ps.setBoolean(parameterIndex++, false);
			if (user != null) {
				AuthProvider ap = user.getPrimaryLogin();
				AccessToken token = user.getAccessToken(ap);
				ps.setString(parameterIndex++, token.getId());
				ps.setString(parameterIndex++, ap.toString());
			} else {
				ps.setNull(parameterIndex++, java.sql.Types.VARCHAR);
				ps.setNull(parameterIndex++, java.sql.Types.VARCHAR);
			}
			ps.setTimestamp(parameterIndex++, Utility.getCurrentSqlTimestampUTC());
			if (engineDisplayName == null || engineDisplayName.trim().isEmpty()) {
				queryUtil.setNullableString(ps, parameterIndex++, engineName);
			} else {
				ps.setString(parameterIndex++, engineDisplayName);
			}
		});
	}

	public static void updateEngineTypeAndSubType(String engineId, IEngine.CATALOG_TYPE engineType,
			String engineSubType) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		String query = "UPDATE ENGINE SET ENGINETYPE=?, ENGINESUBTYPE=? WHERE ENGINEID=?";

		try {
			QueryExecutionUtility.executeUpdate(securityDb, query, ps -> {
				int parameterIndex = 1;
				ps.setString(parameterIndex++, engineType.toString());
				ps.setString(parameterIndex++, engineSubType);
				ps.setString(parameterIndex++, engineId);
			});
		} catch (RuntimeException e) {
			throw e;
		} catch (Exception e) {
			classLogger.error("Failed to update engine type and subtype", e);
		}
	}

	public static void addEngineOwner(String engineId, String userId) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		// make sure user doesn't already exist for this database
		if (getUserEnginePermission(userId, engineId) != null) {
			// that means there is already a value
			throw new IllegalArgumentException("This user already has access to this engine");
		}

		String query = "INSERT INTO ENGINEPERMISSION (USERID, PERMISSION, ENGINEID, DATEADDED) VALUES (?,?,?,?)";

		try {
			QueryExecutionUtility.executeUpdate(securityDb, query, ps -> {
				int parameterIndex = 1;
				ps.setString(parameterIndex++, userId);
				ps.setInt(parameterIndex++, AccessPermissionEnum.OWNER.getId());
				ps.setString(parameterIndex++, engineId);
				ps.setTimestamp(parameterIndex++, Utility.getCurrentSqlTimestampUTC());
			});
		} catch (RuntimeException e) {
			throw e;
		} catch (Exception e) {
			classLogger.error("Failed to add engine owner", e);
		}
	}

	/**
	 * Get the database alias for a id
	 * 
	 * @return
	 */
	public static String getEngineAliasForId(String id) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		// String query = "SELECT ENGINENAME FROM ENGINE WHERE ENGINEID='" + id + "'";
		// IRawSelectWrapper wrapper =
		// WrapperManager.getInstance().getRawWrapper(securityDb, query);

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ENGINEID", "==", id));
		List<String> results = QueryExecutionUtility.flushToListString(securityDb, qs);
		if (results.isEmpty()) {
			return null;
		}
		return results.get(0);
	}

	/**
	 * Get the database alias for a id
	 * 
	 * @return
	 */
	public static Map<Object, Object> getEngineAliasForIds(Collection<String> ids) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEID"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ENGINEID", "==", ids));
		return QueryExecutionUtility.flushRsToKeyValueMap(securityDb, qs);
	}

	/**
	 * Get the display name for an engine id. Falls back to canonical name
	 * (ENGINENAME) if display name is null or blank.
	 *
	 * @param id
	 * @return
	 */
	public static String getEngineDisplayNameForId(String id) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEDISPLAYNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ENGINEID", "==", id));
		List<Object[]> results = QueryExecutionUtility.flushRsToListOfObjArray(securityDb, qs);
		if (results.isEmpty()) {
			return null;
		}
		Object[] row = results.get(0);
		String displayName = row[0] != null ? row[0].toString() : null;
		String canonicalName = row[1] != null ? row[1].toString() : null;
		if (displayName == null || displayName.trim().isEmpty()) {
			return canonicalName;
		}
		return displayName;
	}

	/**
	 * Set the display name for an engine. Only the engine owner can perform this
	 * action.
	 *
	 * @param user
	 * @param engineId
	 * @param newDisplayName
	 * @return
	 * @throws IllegalAccessException
	 */
	public static boolean setEngineDisplayName(User user, String engineId, String newDisplayName)
			throws IllegalAccessException {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		if (!SecurityUserEngineUtils.userIsOwner(user, engineId)) {
			throw new IllegalAccessException(
					"The user doesn't have the permission to change the engine display name. Only the owner can perform this action.");
		}
		if (newDisplayName == null || newDisplayName.trim().isEmpty()) {
			throw new IllegalArgumentException("Display name cannot be null or blank.");
		}

		try {
			QueryExecutionUtility.executeUpdate(securityDb, "UPDATE ENGINE SET ENGINEDISPLAYNAME=? WHERE ENGINEID=?",
					ps -> {
						int parameterIndex = 1;
						ps.setString(parameterIndex++, newDisplayName);
						ps.setString(parameterIndex++, engineId);
					});
		} catch (Exception e) {
			classLogger.error("Failed to update engine display name", e);
			throw new IllegalArgumentException("An error occurred updating the engine display name");
		}

		UserAuditTrailUtils.recordEngineLifecycle(user, AuditEventType.ENGINE_DISPLAY_NAME_UPDATE,
				getAuditTargetType(engineId), engineId, newDisplayName,
				Map.of("field", "displayName", "displayName", newDisplayName));
		return true;
	}

	/**
	 * Get what permission the user has for a given engine
	 * 
	 * @param userId
	 * @param engineId
	 * @return
	 */
	public static String getActualUserEnginePermission(User user, String engineId) {
		String userPermission = SecurityUserEngineUtils.getActualUserEnginePermission(user, engineId);
		List<String> groupUserPermissions = SecurityUserEngineUtils.getActualGroupUserEnginePermission(user, engineId);
		return SecurityUserEngineUtils.getHighestEnginePermission(userPermission, groupUserPermissions);
	}

	/**
	 * Lists the ids of every engine on the server, of any type.
	 * 
	 * @return
	 */
	public static List<String> getAllEngineIds() {
		return getAllEngineIds(null);
	}

	/**
	 * Get a list of the database ids
	 * 
	 * @return
	 */
	public static List<String> getAllEngineIds(List<String> engineTypes) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		// String query = "SELECT DISTINCT ENGINEID FROM ENGINE";
		// IRawSelectWrapper wrapper =
		// WrapperManager.getInstance().getRawWrapper(securityDb, query);

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEID"));
		if (engineTypes != null && !engineTypes.isEmpty()) {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ENGINETYPE", "==", engineTypes));
		}
		return QueryExecutionUtility.flushToListString(securityDb, qs);
	}

	/**
	 * Get markdown for a given engine
	 * 
	 * @param user
	 * @param engineId
	 * @return
	 */
	public static String getEngineMarkdown(User user, String engineId) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINEMETA__METAVALUE"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEMETA__METAKEY", "==", Constants.MARKDOWN));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEMETA__ENGINEID", "==", engineId));
		{
			SelectQueryStruct qs1 = new SelectQueryStruct();
			qs1.addSelector(new QueryColumnSelector("ENGINE__ENGINEID"));
			{
				OrQueryFilter orFilter = new OrQueryFilter();
				orFilter.addFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__DISCOVERABLE", "==",
						Arrays.asList(true, null), PixelDataType.BOOLEAN));
				orFilter.addFilter(
						SimpleQueryFilter.makeColToValFilter("ENGINEPERMISSION__USERID", "==", getUserFiltersQs(user)));
				qs1.addExplicitFilter(orFilter);
			}
			qs1.addRelation("ENGINE", "ENGINEPERMISSION", "join");
			IRelation subQuery = new SubqueryRelationship(qs1, "ENGINE", "join",
					new String[] { "ENGINE__ENGINEID", "ENGINEMETA__ENGINEID", "=" });
			qs.addRelation(subQuery);
		}
		return QueryExecutionUtility.flushToString(securityDb, qs);
	}

	/**
	 * Initialize an engine's markdown metadata without replacing existing content.
	 *
	 * @param engineId        engine whose metadata should be initialized
	 * @param defaultMarkdown markdown supplied by the engine implementation
	 */
	public static void setDefaultEngineMarkdown(String engineId, String defaultMarkdown) {
		if (defaultMarkdown == null || defaultMarkdown.trim().isEmpty()) {
			return;
		}

		Map<String, Object> metadata = getAggregateEngineMetadata(engineId, List.of(Constants.MARKDOWN), false);
		if (hasMetadataValue(metadata.get(Constants.MARKDOWN))) {
			return;
		}

		Map<String, Object> defaultMetadata = new HashMap<>();
		defaultMetadata.put(Constants.MARKDOWN, defaultMarkdown);
		updateEngineMetadata(engineId, defaultMetadata);
	}

	private static boolean hasMetadataValue(Object value) {
		if (value == null) {
			return false;
		}
		if (value instanceof Collection) {
			for (Object item : (Collection<?>) value) {
				if (hasMetadataValue(item)) {
					return true;
				}
			}
			return false;
		}
		return !value.toString().trim().isEmpty();
	}

	/**
	 * Get the engine permissions for a specific user
	 * 
	 * @param singleUserId
	 * @param engineId
	 * @return
	 */
	public static Integer getUserEnginePermission(String singleUserId, String engineId) {
		return SecurityUserEngineUtils.getUserEnginePermission(singleUserId, engineId);
	}

	/**
	 * Get the request pending database permission for a specific user
	 * 
	 * @param singleUserId
	 * @param databaseId
	 * @return
	 */
	public static Integer getUserAccessRequestEnginePermission(String userId, String databaseId) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINEACCESSREQUEST__PERMISSION"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEACCESSREQUEST__REQUEST_USERID", "==", userId));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEACCESSREQUEST__ENGINEID", "==", databaseId));
		qs.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINEACCESSREQUEST__APPROVER_DECISION", "==", null));
		return QueryExecutionUtility.flushToInteger(securityDb, qs);
	}

	/**
	 * Approves access requests, as a permission batch: each person gets the level
	 * they asked for, and the engine's own requests are marked approved in the same
	 * transaction, so either the access and the approvals are all saved or none of
	 * them is. The engine's editors can; only its owners give or change Author
	 * access.
	 *
	 * <p>
	 * Once the save has committed, each requester is sent a notification and an
	 * email. Each is sent on its own, and a failure is logged and does not undo the
	 * save; nothing is sent when the save fails.
	 * </p>
	 *
	 * @param user     the user approving
	 * @param engineId the engine
	 * @param requests the requests, each with {@code requestid}, {@code userid},
	 *                 {@code type} and {@code permission}
	 * @param endDate  when the access ends, or null
	 *
	 * @throws IllegalAccessException when the user cannot change people's access
	 */
	public static void approveEngineUserAccessRequests(User user, String engineId, List<Map<String, String>> requests,
			String endDate) throws IllegalAccessException {
		PermissionTargetResource target = PermissionTargetResource.engine(engineId);
		PermissionBatchUtils.saveChanges(user, target, PermissionBatchUtils.approvalChanges(target, requests, endDate),
				PermissionBatchOptions.userMode().approving(target, requests),
				PermissionErrorRules.forApprovals(endDate).illegalAccess(PermissionProblem.ErrorCode.NO_PERMISSION,
						"Insufficient privileges to modify this engine's permissions."));

		// notifications and emails go out after the save, and a failure does not
		// undo it
		if (Utility.isNotificationDatabaseEnabled()) {
			String engineType = readEngineTypeForNotifications(engineId);
			if (engineType == null) {
				return;
			}
			for (Map<String, String> request : requests) {
				try {
					NotificationDbUtils.createNotification(user, request.get("userid"), request.get("type"), engineId,
							NotificationConstants.Type.REQUEST_APPROVAL, engineType,
							NotificationConstants.Priority.MEDIUM, null, request.get("permission"),
							NotificationConstants.DisplaySurface.BELL);
					EmailUtility.sendAccessRequestApprovalEmailNotification(user, request.get("userid"), engineId,
							request.get("permission"), EmailUtility.RESOURCE_TYPE.ENGINE);
				} catch (Exception e) {
					classLogger.warn("Could not tell {} their request for engine {} was approved",
							request.get("userid"), engineId, e);
				}
			}
		}
	}

	/**
	 * Denies access requests to an engine: each of the engine's own requests is
	 * marked denied, by the user, and its requester is read back, in one
	 * transaction. Only the engine's owners can, directly or through a team.
	 *
	 * <p>
	 * Once the denials have committed, each requester is sent a notification, from
	 * the requesters read in that transaction. Each is sent on its own, and a
	 * failure is logged and does not undo the denials; nothing is sent when the
	 * denials fail.
	 * </p>
	 *
	 * @param user       the user denying
	 * @param engineId   the engine
	 * @param requestIds the requests' ids
	 * @throws IllegalAccessException when the user is not an editor of the engine
	 */
	public static void denyEngineUserAccessRequests(User user, String engineId, List<String> requestIds)
			throws IllegalAccessException {
		List<Map<String, Object>> denied = PermissionBatchUtils.denyRequests(user,
				PermissionTargetResource.engine(engineId), requestIds, PermissionBatchOptions.userMode(),
				new PermissionErrorRules()
						.illegalAccess(PermissionProblem.ErrorCode.NO_PERMISSION, INSUFFICIENT_PRIVILEGES)
						.illegalArgument(PermissionProblem.ErrorCode.OWNERS_ONLY,
								"Insufficient privileges to deny user access requests.")
						.illegalArgument(PermissionProblem.ErrorCode.WRITE_FAILED,
								"An error occurred while updating user access request"));

		// notifications go out after the save, and a failure does not undo it
		if (Utility.isNotificationDatabaseEnabled()) {
			String engineType = readEngineTypeForNotifications(engineId);
			if (engineType == null) {
				return;
			}
			for (Map<String, Object> request : denied) {
				Integer permission = (Integer) request.get("permission");
				if (permission == null) {
					// the notification names the level asked for, so a request with none
					// gets no notification
					continue;
				}
				try {
					NotificationDbUtils.createNotification(user, (String) request.get("userId"),
							(String) request.get("type"), engineId, NotificationConstants.Type.REQUEST_DENIAL,
							engineType, NotificationConstants.Priority.MEDIUM, null,
							AccessPermissionEnum.getPermissionValueById(permission),
							NotificationConstants.DisplaySurface.BELL);
				} catch (Exception e) {
					classLogger.warn("Could not tell the requester that request {} for engine {} was denied",
							request.get("requestId"), engineId, e);
				}
			}
		}
	}

	/**
	 * Reads an engine's type for the notifications sent after a save, in lower
	 * case. The save has committed by then, so a failure to read it is logged, and
	 * no notification is sent, rather than thrown.
	 *
	 * @param engineId the engine
	 * @return the engine's type in lower case, or null when it cannot be read
	 */
	private static String readEngineTypeForNotifications(String engineId) {
		try {
			return String.valueOf(getEngineType(engineId)).toLowerCase();
		} catch (RuntimeException e) {
			classLogger.warn("Could not read the type of engine {} for notifications", engineId, e);
			return null;
		}
	}

	/**
	 * Get the request pending engine permission for a specific user
	 * 
	 * @param singleUserId
	 * @param engineId
	 * @return
	 */
	public static List<Map<String, Object>> getUserAccessRequestsByEngine(String engineId) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINEACCESSREQUEST__ID"));
		qs.addSelector(new QueryColumnSelector("ENGINEACCESSREQUEST__REQUEST_USERID"));
		qs.addSelector(new QueryColumnSelector("ENGINEACCESSREQUEST__REQUEST_TYPE"));
		qs.addSelector(new QueryColumnSelector("SMSS_USER__NAME"));
		qs.addSelector(new QueryColumnSelector("SMSS_USER__EMAIL"));
		qs.addSelector(new QueryColumnSelector("SMSS_USER__USERNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINEACCESSREQUEST__REQUEST_TIMESTAMP"));
		qs.addSelector(new QueryColumnSelector("ENGINEACCESSREQUEST__ENGINEID"));
		qs.addSelector(new QueryColumnSelector("ENGINEACCESSREQUEST__PERMISSION"));
		qs.addSelector(new QueryColumnSelector("ENGINEACCESSREQUEST__APPROVER_USERID"));
		qs.addSelector(new QueryColumnSelector("ENGINEACCESSREQUEST__APPROVER_TYPE"));
		qs.addSelector(new QueryColumnSelector("ENGINEACCESSREQUEST__APPROVER_DECISION"));
		qs.addSelector(new QueryColumnSelector("ENGINEACCESSREQUEST__APPROVER_TIMESTAMP"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEACCESSREQUEST__ENGINEID", "==", engineId));
		qs.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINEACCESSREQUEST__APPROVER_DECISION", "==", "NEW_REQUEST"));
		qs.addRelation("ENGINEACCESSREQUEST__REQUEST_USERID", "SMSS_USER__ID", "inner.join");
		qs.addRelation("ENGINEACCESSREQUEST__REQUEST_TYPE", "SMSS_USER__TYPE", "inner.join");
		return QueryExecutionUtility.flushRsToMap(securityDb, qs);
	}

	/**
	 * Validate if the given engineId exists in the database.
	 * 
	 * @param engineId
	 * @return
	 */
	public static boolean engineExists(String engineId) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEID"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ENGINEID", "==", engineId));
		boolean exists = false;
		try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(securityDb, qs)) {
			exists = wrapper.hasNext();
		} catch (Exception e) {
			classLogger.error("Failed to check whether engine exists", e);
		}
		return exists;
	}

	/**
	 * See if specific engine is global
	 * 
	 * @return
	 */
	public static boolean engineIsGlobal(String engineId) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		// String query = "SELECT ENGINEID FROM ENGINE WHERE GLOBAL=TRUE and ENGINEID='"
		// + databaseId + "'";
		// IRawSelectWrapper wrapper =
		// WrapperManager.getInstance().getRawWrapper(securityDb, query);

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEID"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__GLOBAL", "==", true, PixelDataType.BOOLEAN));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ENGINEID", "==", engineId));
		boolean global = false;
		try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(securityDb, qs)) {
			global = wrapper.hasNext();
		} catch (Exception e) {
			classLogger.error("Failed to determine whether engine is global", e);
		}
		return global;
	}

	/**
	 * Whether the user is an owner of the engine, which the UI calls Author. Grants
	 * that have ended do not count.
	 *
	 * @return whether the user is an owner of the engine now, directly or through a
	 *         team; see {@link CurrentPermissionLevel}
	 */
	public static boolean userIsOwner(User user, String databaseId) {
		return CurrentPermissionLevel.isOwner(user, PermissionTargetResource.engine(databaseId));
	}

	/**
	 * Whether the user can edit the engine, as an editor or an owner. Grants that
	 * have ended do not count.
	 *
	 * @return whether the user is an editor or an owner of the engine now, directly
	 *         or through a team; see {@link CurrentPermissionLevel}
	 */
	public static boolean userCanEditEngine(User user, String databaseId) {
		return CurrentPermissionLevel.canEdit(user, PermissionTargetResource.engine(databaseId));
	}

	/**
	 * Determine if a user can view a engine
	 * 
	 * @param user
	 * @param engineId
	 * @return
	 */
	public static boolean userCanViewEngine(User user, String engineId) {
		return SecurityUserEngineUtils.userCanViewEngine(user, engineId)
				|| SecurityGroupEngineUtils.userGroupCanViewEngine(user, engineId);
	}

	/**
	 * The user's access level on the engine now, for a request that checks several
	 * things against one reading of it. It is the highest level among the grants in
	 * effect, given to the user directly or to a team they are in, as
	 * {@link #userCanEditEngine} reads it, or read-only on a public engine they
	 * have no grant on, as {@link #userCanViewEngine} allows. A grant that has
	 * ended does not count, and is not removed.
	 *
	 * @param user     the user
	 * @param engineId the engine
	 * @return the level, or null when the user has no access to the engine
	 */
	public static AccessPermissionEnum getCurrentUserEngineLevel(User user, String engineId) {
		if (user == null) {
			return null;
		}
		Integer levelId = CurrentPermissionLevel.ofOrNull(user, PermissionTargetResource.engine(engineId));
		if (levelId != null) {
			for (AccessPermissionEnum level : AccessPermissionEnum.values()) {
				if (level.getId() == levelId) {
					return level;
				}
			}
		}
		return engineIsGlobal(engineId) ? AccessPermissionEnum.READ_ONLY : null;
	}

	/**
	 * Determine if the user can edit the engine
	 * 
	 * @param userId
	 * @param engineId
	 * @return
	 */
	static int getMaxUserEnginePermission(User user, String engineId) {
		return SecurityUserEngineUtils.getMaxUserEnginePermission(user, engineId);
	}

	///////////////////////////////////////////////////////////////////////////////////
	///////////////////////////////////////////////////////////////////////////////////
	///////////////////////////////////////////////////////////////////////////////////

	/*
	 * Query for database users
	 */

	/**
	 * The people to show with the engine: for a global engine, its owners and
	 * editors plus a PUBLIC DATABASE row at Read-Only; otherwise everyone with
	 * access to it.
	 * 
	 * @param databaseId
	 * @return
	 */
	public static List<Map<String, Object>> getDisplayDatabaseOwnersAndEditors(String databaseId) {
		return SecurityUserEngineUtils.getDisplayEngineOwnersAndEditors(databaseId);
	}

	/**
	 * Retrieve the list of users for a given database
	 * 
	 * @param user
	 * @param engineId
	 * @param searchParam
	 * @param permission
	 * @param limit
	 * @param offset
	 * @return
	 * @throws IllegalAccessException
	 */
	public static List<Map<String, Object>> getEngineUsers(User user, String engineId, String searchParam,
			String permission, long limit, long offset) throws IllegalAccessException {
		if (!userCanViewEngine(user, engineId)) {
			throw new IllegalAccessException("The user does not have access to view this engine");
		}
		return SecurityUserEngineUtils.getEngineUsers(engineId, searchParam, permission, limit, offset);
	}

	/**
	 * Counts the users who hold a level on the engine, for paging its user list.
	 * The search matches user id, name, username or email, and the permission keeps
	 * only users at that level. The user must be able to view the engine.
	 * 
	 * @param user
	 * @param engineId
	 * @param userId
	 * @param permission
	 * @return
	 * @throws IllegalAccessException
	 */
	public static long getEngineUsersCount(User user, String engineId, String searchParam, String permission)
			throws IllegalAccessException {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		if (!userCanViewEngine(user, engineId)) {
			throw new IllegalAccessException("The user does not have access to view this engine");
		}
		boolean hasSearchParam = searchParam != null && !(searchParam = searchParam.trim()).isEmpty();
		boolean hasPermission = permission != null && !(permission = permission.trim()).isEmpty();
		SelectQueryStruct qs = new SelectQueryStruct();
		QueryFunctionSelector fSelector = new QueryFunctionSelector();
		fSelector.setAlias("count");
		fSelector.setFunction(QueryFunctionHelper.COUNT);
		fSelector.addInnerSelector(new QueryColumnSelector("SMSS_USER__ID"));
		qs.addSelector(fSelector);
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEPERMISSION__ENGINEID", "==", engineId));
		if (hasSearchParam) {
			OrQueryFilter or = new OrQueryFilter();
			or.addFilter(SimpleQueryFilter.makeColToValFilter("ENGINEPERMISSION__USERID", "?like", searchParam));
			or.addFilter(SimpleQueryFilter.makeColToValFilter("SMSS_USER__NAME", "?like", searchParam));
			or.addFilter(SimpleQueryFilter.makeColToValFilter("SMSS_USER__USERNAME", "?like", searchParam));
			or.addFilter(SimpleQueryFilter.makeColToValFilter("SMSS_USER__EMAIL", "?like", searchParam));
			qs.addExplicitFilter(or);
		}
		if (hasPermission) {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEPERMISSION__PERMISSION", "==",
					AccessPermissionEnum.getIdByPermission(permission)));
		}
		qs.addRelation("SMSS_USER", "ENGINEPERMISSION", "inner.join");
		qs.addRelation("ENGINEPERMISSION", "PERMISSION", "inner.join");
		return QueryExecutionUtility.flushToLong(securityDb, qs);
	}

	/**
	 * Gives a person access to an engine, as a permission batch. The engine's
	 * editors can; only its owners give Author access, and only its owners give a
	 * usage limit.
	 *
	 * @param user       the user giving access
	 * @param newUserId  the person
	 * @param engineId   the engine
	 * @param permission the level, by name; anything else is Read-Only
	 * @param endDate    when the access ends, or null
	 * @param usageLimit the person's model usage limit on a model engine, or null
	 *                   for no limit defined
	 * @throws IllegalAccessException when the user cannot give the access, or is
	 *                                not an owner and gives a usage limit
	 */
	public static void addEngineUser(User user, String newUserId, String engineId, String permission, String endDate,
			ModelUsageLimit usageLimit) throws IllegalAccessException {
		PermissionBatchUtils.saveChanges(user, PermissionTargetResource.engine(engineId),
				List.of(PermissionChange.add(PermissionSubject.ofUser(newUserId, null),
						new PermissionGrant(permission, endDate).withUsageLimit(usageLimit))),
				PermissionBatchOptions.userMode(),
				new PermissionErrorRules(endDate)
						.illegalAccess(PermissionProblem.ErrorCode.NO_PERMISSION, INSUFFICIENT_PRIVILEGES)
						.illegalArgument(PermissionProblem.ErrorCode.ALREADY_HAS_ACCESS,
								"This user already has access to this engine. Please edit the existing permission level.")
						.illegalAccess(PermissionProblem.ErrorCode.GIVES_AUTHOR, CANNOT_GIVE_OWNER)
						.illegalAccess(PermissionProblem.ErrorCode.OWNERS_ONLY, PermissionBatchUtils.OWNERS_SET_LIMITS)
						.illegalArgument(PermissionProblem.ErrorCode.WRITE_FAILED,
								"An error occurred adding the user permissions for this engine"));
	}

	/**
	 * Gives many people access to an engine together, as a permission batch, and
	 * tells them. Each entry has {@code userid}, {@code permission}, and optionally
	 * {@code type}, {@code endDate} and, on a model, {@code usageLimit}, which only
	 * the engine's owners give; an entry without it gives no limit.
	 *
	 * @param user       the user giving access
	 * @param engineId   the engine
	 * @param permission the people, their levels, end dates and limits
	 * @throws IllegalAccessException when the user cannot give access
	 */
	public static void addEngineUserPermissions(User user, String engineId, List<Map<String, Object>> permission)
			throws IllegalAccessException {
		PermissionBatchUtils.saveChanges(user, PermissionTargetResource.engine(engineId),
				PermissionBatchUtils.engineChanges(PermissionChange.Action.ADD, permission),
				PermissionBatchOptions.userMode().notifying(true, false),
				new PermissionErrorRules()
						.illegalAccess(PermissionProblem.ErrorCode.NO_PERMISSION, INSUFFICIENT_PRIVILEGES)
						.illegalArgumentWithIds(PermissionProblem.ErrorCode.ALREADY_HAS_ACCESS,
								"The following users already have access to this engine. Please edit the existing permission level: ")
						.illegalArgument(PermissionProblem.ErrorCode.GIVES_AUTHOR,
								"As a non-owner, you cannot add owner user access.")
						.illegalArgument(PermissionProblem.ErrorCode.OWNERS_ONLY,
								PermissionBatchUtils.OWNERS_SET_LIMITS)
						.illegalArgument(PermissionProblem.ErrorCode.WRITE_FAILED,
								"An error occurred adding the user permissions for this engine"));
	}

	/**
	 * Changes a person's access to an engine, as a permission batch, and tells
	 * them. The engine's editors can; only its owners change an Author's access or
	 * give Author access, and only its owners change a usage limit.
	 *
	 * @param user             the user changing the access
	 * @param existingUserId   the person
	 * @param existingUserType the person's login type, for the notification
	 * @param engineId         the engine
	 * @param newPermission    the level, by name; anything else is Read-Only
	 * @param endDate          when the access ends, or null for never
	 * @param usageLimit       the person's model usage limit on a model engine, or
	 *                         null for no limit defined
	 * @throws IllegalAccessException when the user cannot change the access, or is
	 *                                not an owner and changes the usage limit
	 */
	public static void editEngineUserPermission(User user, String existingUserId, String existingUserType,
			String engineId, String newPermission, String endDate, ModelUsageLimit usageLimit)
			throws IllegalAccessException {
		saveEngineUserChange(user, existingUserId, existingUserType, engineId,
				new PermissionGrant(newPermission, endDate).withUsageLimit(usageLimit),
				PermissionBatchOptions.userMode().notifying(false, true), endDate);
	}

	/**
	 * Changes a person's level and end date on an engine, as a permission batch,
	 * and tells them. Their model usage limit stays as it is. The engine's editors
	 * can; only its owners change an Author's access or give Author access.
	 *
	 * @param user             the user changing the access
	 * @param existingUserId   the person
	 * @param existingUserType the person's login type, for the notification
	 * @param engineId         the engine
	 * @param newPermission    the level, by name; anything else is Read-Only
	 * @param endDate          when the access ends, or null for never
	 * @throws IllegalAccessException when the user cannot change the access
	 */
	public static void editEngineUserPermission(User user, String existingUserId, String existingUserType,
			String engineId, String newPermission, String endDate) throws IllegalAccessException {
		saveEngineUserChange(user, existingUserId, existingUserType, engineId,
				new PermissionGrant(newPermission, endDate),
				PermissionBatchOptions.userMode().notifying(false, true).keepingModelLimits(), endDate);
	}

	private static void saveEngineUserChange(User user, String existingUserId, String existingUserType, String engineId,
			PermissionGrant grant, PermissionBatchOptions options, String endDate) throws IllegalAccessException {
		PermissionBatchUtils.saveChanges(user, PermissionTargetResource.engine(engineId),
				List.of(PermissionChange.change(PermissionSubject.ofUser(existingUserId, existingUserType), grant)),
				options,
				new PermissionErrorRules(endDate)
						.illegalAccess(PermissionProblem.ErrorCode.NO_PERMISSION, INSUFFICIENT_PRIVILEGES)
						.illegalArgument(PermissionProblem.ErrorCode.NO_ACCESS,
								"Attempting to modify engine permission for a user who does not currently have access to the engine")
						.illegalAccess(PermissionProblem.ErrorCode.CHANGES_AUTHOR, NOT_HIGH_ENOUGH)
						.illegalAccess(PermissionProblem.ErrorCode.GIVES_AUTHOR, CANNOT_GIVE_OWNER)
						.illegalAccess(PermissionProblem.ErrorCode.OWNERS_ONLY, PermissionBatchUtils.OWNERS_SET_LIMITS)
						.illegalArgument(PermissionProblem.ErrorCode.WRITE_FAILED,
								"An error occurred updating the user permissions for this engine"));
	}

	/**
	 * Changes many people's access to an engine together, as a permission batch,
	 * and tells them. Each entry has {@code userid}, {@code permission}, and
	 * optionally {@code type}, {@code endDate} and, on a model, {@code usageLimit},
	 * which only the engine's owners change. An entry without {@code usageLimit}
	 * keeps the person's current limit, and one whose {@code usageLimit} is null
	 * removes it.
	 *
	 * @param user       the user changing the access
	 * @param engineId   the engine
	 * @param permission the changes
	 * @throws IllegalAccessException when the user cannot change people's access
	 */
	public static void editEngineUserPermissions(User user, String engineId, List<Map<String, Object>> permission)
			throws IllegalAccessException {
		PermissionBatchUtils.saveChanges(user, PermissionTargetResource.engine(engineId),
				PermissionBatchUtils.engineChanges(PermissionChange.Action.CHANGE, permission),
				PermissionBatchOptions.userMode().notifying(false, true).keepingModelLimits(),
				new PermissionErrorRules()
						.illegalAccess(PermissionProblem.ErrorCode.NO_PERMISSION, INSUFFICIENT_PRIVILEGES)
						.illegalArgumentWithIds(PermissionProblem.ErrorCode.NO_ACCESS,
								"Attempting to modify user permission for the following users who do not currently have access to the engine: ")
						.illegalArgument(PermissionProblem.ErrorCode.CHANGES_AUTHOR,
								"As a non-owner, you cannot edit access of an owner.")
						.illegalArgument(PermissionProblem.ErrorCode.GIVES_AUTHOR,
								"As a non-owner, you cannot give a user access as an owner.")
						.illegalArgument(PermissionProblem.ErrorCode.OWNERS_ONLY,
								PermissionBatchUtils.OWNERS_SET_LIMITS)
						.illegalArgument(PermissionProblem.ErrorCode.ENDS_LIMITED_ACCESS,
								PermissionBatchUtils.OWNERS_END_LIMITED_ACCESS)
						.illegalArgument(PermissionProblem.ErrorCode.WRITE_FAILED,
								"An error occurred updating the user permissions for this engine"));
	}

	/**
	 * Delete all values
	 * 
	 * @param engineId
	 */
	public static void deleteEngine(String engineId) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		List<String> deletes = new ArrayList<>();
		deletes.add("DELETE FROM ENGINE WHERE ENGINEID=?");
		deletes.add("DELETE FROM ENGINEPERMISSION WHERE ENGINEID=?");
		deletes.add("DELETE FROM USERENGINEPREFERENCE WHERE ENGINEID=?");
		deletes.add("DELETE FROM ENGINEMETA WHERE ENGINEID=?");
		deletes.add("DELETE FROM MODELMETADATA WHERE ENGINEID=?");
		deletes.add("DELETE FROM ENGINEACCESSREQUEST WHERE ENGINEID=?");

		for (String deleteQuery : deletes) {
			try {
				QueryExecutionUtility.executeUpdate(securityDb, deleteQuery, ps -> ps.setString(1, engineId));
			} catch (RuntimeException e) {
				throw e;
			} catch (Exception e) {
				classLogger.error("Failed to delete engine", e);
			}
		}
		// every model usage limit on the engine: people's, teams', public use and its
		// total
		try {
			QueryExecutionUtility.write(securityDb, connection -> {
				SecurityModelUsageLimitUtils.deleteEngineLimits(connection, engineId);
				return null;
			});
		} catch (RuntimeException e) {
			throw e;
		} catch (Exception e) {
			classLogger.error("Failed to delete the engine's model usage limits", e);
		} finally {
			// the grants are gone even when the limits are not, so the cached limits are
			// read again
			SecurityModelUsageLimitUtils.markChanged();
		}
	}

	/**
	 * Takes a person's access to an engine away, as a permission batch. The
	 * engine's editors can, and anyone can leave; only its owners take away an
	 * Author's access.
	 *
	 * @param user           the user taking the access away
	 * @param existingUserId the person
	 * @param engineId       the engine
	 * @throws IllegalAccessException when the user cannot take the access away
	 */
	public static void removeEngineUser(User user, String existingUserId, String engineId)
			throws IllegalAccessException {
		PermissionBatchUtils.saveChanges(user, PermissionTargetResource.engine(engineId),
				List.of(PermissionChange.remove(PermissionSubject.ofUser(existingUserId, null))),
				PermissionBatchOptions.userMode().allowingSelfRemoval(),
				new PermissionErrorRules()
						.illegalAccess(PermissionProblem.ErrorCode.NO_PERMISSION, INSUFFICIENT_PRIVILEGES)
						.illegalArgument(PermissionProblem.ErrorCode.NO_ACCESS,
								"Attempting to modify engine permission for a user who does not currently have access to the engine")
						.illegalAccess(PermissionProblem.ErrorCode.CHANGES_AUTHOR, NOT_HIGH_ENOUGH)
						.illegalAccess(PermissionProblem.ErrorCode.ENDS_LIMITED_ACCESS,
								PermissionBatchUtils.OWNERS_END_LIMITED_ACCESS)
						.illegalArgument(PermissionProblem.ErrorCode.WRITE_FAILED,
								"An error occurred removing the user permissions for this engine"));
	}

	/**
	 * Takes many people's access to an engine away together, as a permission batch.
	 *
	 * @param user            the user taking the access away
	 * @param existingUserIds the people
	 * @param engineId        the engine
	 * @throws IllegalAccessException when the user cannot change people's access
	 */
	public static void removeEngineUsers(User user, List<String> existingUserIds, String engineId)
			throws IllegalAccessException {
		PermissionBatchUtils.saveChanges(user, PermissionTargetResource.engine(engineId),
				PermissionChange.forPeople(PermissionChange.Action.REMOVE, existingUserIds, null),
				PermissionBatchOptions.userMode(),
				new PermissionErrorRules()
						.illegalAccess(PermissionProblem.ErrorCode.NO_PERMISSION, INSUFFICIENT_PRIVILEGES)
						.illegalArgumentWithIds(PermissionProblem.ErrorCode.NO_ACCESS,
								"Attempting to modify engine permission for the following users who do not currently have access to the engine: ")
						.illegalAccess(PermissionProblem.ErrorCode.CHANGES_AUTHOR,
								"As a non-owner, you cannot remove access of an owner.")
						.illegalAccess(PermissionProblem.ErrorCode.ENDS_LIMITED_ACCESS,
								PermissionBatchUtils.OWNERS_END_LIMITED_ACCESS)
						.illegalArgument(PermissionProblem.ErrorCode.WRITE_FAILED,
								"An error occurred removing the users from this engine"));
	}

	/**
	 * Removes a person's access to an engine once it has ended, with the model
	 * usage limit on it.
	 *
	 * @param userId   the person
	 * @param engineId the engine
	 */
	public static void removeExpiredEngineUser(String userId, String engineId) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		String deleteQuery = "DELETE FROM ENGINEPERMISSION WHERE USERID=? AND ENGINEID=?";
		// read before the removal, so the audit trail records the limits removed with
		// it
		Map<ModelUsageLimitKey, ModelUsageLimit> removedLimits = directLimitsOf(engineId, userId);
		try {
			QueryExecutionUtility.write(securityDb, connection -> {
				QueryExecutionUtility.executeUpdate(connection, deleteQuery, ps -> {
					int parameterIndex = 1;
					ps.setString(parameterIndex++, userId);
					ps.setString(parameterIndex++, engineId);
				});
				SecurityModelUsageLimitUtils.deleteDirectLimitsWithoutGrant(connection, userId);
				return null;
			});
			SecurityModelUsageLimitUtils.markChanged();
		} catch (RuntimeException e) {
			throw e;
		} catch (Exception e) {
			classLogger.error("Failed to remove expired engine user", e);
			throw new IllegalArgumentException("An error occurred removing the user permissions for this engine");
		}
		UserAuditTrailUtils.recordExpiredPermissionRemoval(getAuditTargetType(engineId), engineId, null, engineId, null,
				userId);
		ModelUsageLimitAudit.recordRemovedWithExpiredGrant(engineId, removedLimits);
	}

	/**
	 * Reads a person's Direct Limits on a model, on every login type, for the audit
	 * trail of a removal. A read that fails gives none, so it never stops the
	 * removal.
	 *
	 * @return the person's Direct Limits on the model, by key
	 */
	private static Map<ModelUsageLimitKey, ModelUsageLimit> directLimitsOf(String engineId, String userId) {
		Map<ModelUsageLimitKey, ModelUsageLimit> direct = new LinkedHashMap<>();
		try {
			for (Map.Entry<ModelUsageLimitKey, ModelUsageLimit> entry : SecurityModelUsageLimitUtils
					.getEngineLimits(engineId).entrySet()) {
				if (entry.getKey().isDirect() && userId.equals(entry.getKey().subjectId())) {
					direct.put(entry.getKey(), entry.getValue());
				}
			}
		} catch (RuntimeException e) {
			classLogger.warn("Unable to read the usage limits of an expired grant for the audit trail", e);
		}
		return direct;
	}

	/**
	 * Set if the engine is public to all users on this instance
	 * 
	 * @param user
	 * @param engineId
	 * @param global
	 * @return
	 * @throws IllegalAccessException
	 */
	public static boolean setEngineGlobal(User user, String engineId, boolean global) throws IllegalAccessException {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		if (!SecurityUserEngineUtils.userIsOwner(user, engineId)) {
			throw new IllegalAccessException(
					"The user doesn't have the permission to set this engine as global. Only the owner can perform this action.");
		}

		String updateQ = "UPDATE ENGINE SET GLOBAL=? WHERE ENGINEID=? AND (GLOBAL IS NULL OR GLOBAL<>?)";
		try {
			int updated = QueryExecutionUtility.executeUpdate(securityDb, updateQ, ps -> {
				ps.setBoolean(1, global);
				ps.setString(2, engineId);
				ps.setBoolean(3, global);
			});
			if (updated == 0
					&& QueryExecutionUtility.queryOne(securityDb, "SELECT ENGINEID FROM ENGINE WHERE ENGINEID=?",
							ps -> ps.setString(1, engineId), rs -> rs.getString(1)) != null) {
				return true;
			}
			if (updated != 1) {
				throw new IllegalArgumentException("Engine not found");
			}
			// public use applies only on a public model
			SecurityModelUsageLimitUtils.markChanged();
		} catch (Exception e) {
			classLogger.error("Failed to update engine global visibility setting", e);
			UserAuditTrailUtils.recordFailure(user, AuditEventType.ENGINE_GLOBAL_UPDATE, AuditTargetType.ENGINE,
					engineId, new IllegalStateException("Unable to update engine global visibility"), null);
			throw new IllegalStateException("Unable to update engine global visibility", e);
		}
		UserAuditTrailUtils.recordEngineLifecycle(user, AuditEventType.ENGINE_GLOBAL_UPDATE,
				getAuditTargetType(engineId), engineId, getEngineDisplayNameForId(engineId),
				Map.of("field", "global", "global", global));
		return true;
	}

	/**
	 * Set a engine to be global
	 * 
	 * @param engineId
	 */
	public static void setEngineCompletelyGlobal(String engineId) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		{
			String update1 = "UPDATE ENGINE SET GLOBAL=? WHERE ENGINEID=?";
			try {
				QueryExecutionUtility.executeUpdate(securityDb, update1, ps -> {
					int parameterIndex = 1;
					ps.setBoolean(parameterIndex++, true);
					ps.setString(parameterIndex++, engineId);
				});
				// public use applies only on a public model
				SecurityModelUsageLimitUtils.markChanged();
			} catch (RuntimeException e) {
				throw e;
			} catch (Exception e) {
				classLogger.error("Failed to set engine visibility to globally accessible", e);
			}
		}
	}

	/**
	 * Set if the engine is discoverable to all users on this instance
	 * 
	 * @param user
	 * @param engineId
	 * @param discoverable
	 * @return
	 * @throws IllegalAccessException
	 */
	public static boolean setEngineDiscoverable(User user, String engineId, boolean discoverable)
			throws IllegalAccessException {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		if (!SecurityUserEngineUtils.userIsOwner(user, engineId)) {
			throw new IllegalAccessException(
					"The user doesn't have the permission to set this engine as discoverable. Only the owner or an admin can perform this action.");
		}

		String updateQ = "UPDATE ENGINE SET DISCOVERABLE=? WHERE ENGINEID=? AND (DISCOVERABLE IS NULL OR DISCOVERABLE<>?)";
		try {
			int updated = QueryExecutionUtility.executeUpdate(securityDb, updateQ, ps -> {
				ps.setBoolean(1, discoverable);
				ps.setString(2, engineId);
				ps.setBoolean(3, discoverable);
			});
			if (updated == 0
					&& QueryExecutionUtility.queryOne(securityDb, "SELECT ENGINEID FROM ENGINE WHERE ENGINEID=?",
							ps -> ps.setString(1, engineId), rs -> rs.getString(1)) != null) {
				return true;
			}
			if (updated != 1) {
				throw new IllegalArgumentException("Engine not found");
			}
		} catch (Exception e) {
			classLogger.error("Failed to update engine discoverability setting", e);
			UserAuditTrailUtils.recordFailure(user, AuditEventType.ENGINE_DISCOVERABLE_UPDATE, AuditTargetType.ENGINE,
					engineId, new IllegalStateException("Unable to update engine discoverability"), null);
			throw new IllegalStateException("Unable to update engine discoverability", e);
		}
		UserAuditTrailUtils.recordEngineLifecycle(user, AuditEventType.ENGINE_DISCOVERABLE_UPDATE,
				getAuditTargetType(engineId), engineId, getEngineDisplayNameForId(engineId),
				Map.of("field", "discoverable", "discoverable", discoverable));
		return true;
	}

	/**
	 * Show or hide an engine in the user's lists, for each of their logins. The
	 * visibility is saved in USERENGINEPREFERENCE, apart from the user's
	 * permissions, and changes only this engine.
	 *
	 * @param user       the user
	 * @param engineId   the engine
	 * @param visibility true to show the engine, false to hide it
	 * @throws IllegalAccessException when the user cannot view the engine
	 * @throws Exception              when the visibility cannot be saved
	 */
	public static void setEngineVisibility(User user, String engineId, boolean visibility) throws Exception {
		if (!userCanViewEngine(user, engineId)) {
			throw new IllegalAccessException(
					"The user doesn't have the permission to modify his visibility of this engine");
		}
		setUserPreference(user, "USERENGINEPREFERENCE", List.of(Pair.with("ENGINEID", engineId)), "VISIBILITY",
				visibility);
		UserAuditTrailUtils.recordEngineLifecycle(user, AuditEventType.ENGINE_VISIBILITY_UPDATE,
				getAuditTargetType(engineId), engineId, getEngineDisplayNameForId(engineId),
				Map.of("field", "visibility", "visibility", visibility));
	}

	/**
	 * Favorite or unfavorite an engine for each of the user's logins. The favorite
	 * is saved in USERENGINEPREFERENCE, apart from the user's permissions, so a
	 * user can favorite a public engine they have no direct access to.
	 *
	 * @param user       the user
	 * @param engineId   the engine
	 * @param isFavorite true to favorite the engine, false to unfavorite it
	 * @throws IllegalAccessException when the engine is not public and the user
	 *                                cannot view it
	 * @throws Exception              when the favorite cannot be saved
	 */
	public static void setEngineFavorite(User user, String engineId, boolean isFavorite) throws Exception {
		if (!engineIsGlobal(engineId) && !userCanViewEngine(user, engineId)) {
			throw new IllegalAccessException(
					"The user doesn't have the permission to modify his visibility of this engine");
		}
		setUserPreference(user, "USERENGINEPREFERENCE", List.of(Pair.with("ENGINEID", engineId)), "FAVORITE",
				isFavorite);
	}

	/**
	 * update the database name
	 * 
	 * @param user
	 * @param engineId
	 * @param isPublic
	 * @return
	 * @throws IllegalAccessException
	 */
	public static boolean setEngineName(User user, String engineId, String newEngineName)
			throws IllegalAccessException {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		if (!SecurityUserEngineUtils.userIsOwner(user, engineId)) {
			throw new IllegalAccessException(
					"The user doesn't have the permission to change the engine name. Only the owner can perform this action.");
		}

		try {
			QueryExecutionUtility.executeUpdate(securityDb, "UPDATE ENGINE SET ENGINENAME=? WHERE ENGINEID=?", ps -> {
				int parameterIndex = 1;
				// SET
				ps.setString(parameterIndex++, newEngineName);
				// WHERE
				ps.setString(parameterIndex++, engineId);
			});
		} catch (Exception e) {
			classLogger.error("Failed to update engine name", e);
			throw new IllegalArgumentException("An error occurred updating the engine name");
		}
		UserAuditTrailUtils.recordEngineLifecycle(user, AuditEventType.ENGINE_RENAME, getAuditTargetType(engineId),
				engineId, newEngineName, Map.of("field", "name", "name", newEngineName));
		return true;
	}

	//////////////////////////////////////////////////////////////////////////////
	//////////////////////////////////////////////////////////////////////////////
	//////////////////////////////////////////////////////////////////////////////

	/*
	 * Database Metadata
	 */

	/**
	 * Lists every metadata key defined for engines.
	 * 
	 * @return
	 */
	public static List<String> getAllMetakeys() {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINEMETAKEYS__METAKEY"));
		List<String> metakeys = QueryExecutionUtility.flushToListString(securityDb, qs);
		return metakeys;
	}

	/**
	 * Update the engine metadata Will delete existing values and then perform a
	 * bulk insert
	 * 
	 * @param engineId
	 * @param insightId
	 * @param tags
	 */
	public static void updateEngineMetadata(String engineId, Map<String, Object> metadata) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		try {
			QueryExecutionUtility.write(securityDb, connection -> {
				// first do a delete
				String deleteQ = "DELETE FROM ENGINEMETA WHERE METAKEY=? AND ENGINEID=?";

				QueryExecutionUtility.executeBatch(connection, deleteQ, metadata.keySet(), (deletePs, field) -> {
					int parameterIndex = 1;
					deletePs.setString(parameterIndex++, field);
					deletePs.setString(parameterIndex++, engineId);
				});

				// now we do the new insert with the order of the tags
				String query = securityDb.getQueryUtil().createInsertPreparedStatementString("ENGINEMETA",
						new String[] { "ENGINEID", "METAKEY", "METAVALUE", "METAORDER" });

				QueryExecutionUtility.executeBatch(connection, query, ps -> {
					for (String field : metadata.keySet()) {
						Object val = metadata.get(field);
						List<Object> values = new ArrayList<>();
						if (val instanceof List) {
							values = (List<Object>) val;
						} else if (val instanceof Collection) {
							values.addAll((Collection<Object>) val);
						} else {
							values.add(val);
						}

						for (int i = 0; i < values.size(); i++) {
							int parameterIndex = 1;
							Object fieldVal = values.get(i);

							ps.setString(parameterIndex++, engineId);
							ps.setString(parameterIndex++, field);
							ps.setString(parameterIndex++, fieldVal + "");
							ps.setInt(parameterIndex++, i);
							ps.addBatch();
						}
					}
				});
				return null;
			});
		} catch (Exception e) {
			classLogger.error("Failed to update engine metadata", e);
		}
	}

	/**
	 * Get the wrapper for additional database metadata
	 * 
	 * @param engineIds
	 * @param metaKeys
	 * @param ignoreMarkdown
	 * @return
	 * @throws Exception
	 */
	public static IRawSelectWrapper getEngineMetadataWrapper(Collection<String> engineIds, List<String> metaKeys,
			boolean ignoreMarkdown) throws Exception {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		// selectors
		qs.addSelector(new QueryColumnSelector("ENGINEMETA__ENGINEID"));
		qs.addSelector(new QueryColumnSelector("ENGINEMETA__METAKEY"));
		qs.addSelector(new QueryColumnSelector("ENGINEMETA__METAVALUE"));
		qs.addSelector(new QueryColumnSelector("ENGINEMETA__METAORDER"));
		// filters
		if (engineIds != null && !engineIds.isEmpty()) {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEMETA__ENGINEID", "==", engineIds));
		}
		if (metaKeys != null && !metaKeys.isEmpty()) {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEMETA__METAKEY", "==", metaKeys));
		}
		// exclude markdown metadata due to potential large data size
		if (ignoreMarkdown) {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEMETA__METAKEY", "!=", Constants.MARKDOWN));
		}
		// order
		qs.addOrderBy("ENGINEMETA__METAORDER");
		IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(securityDb, qs);
		return wrapper;
	}

	/**
	 * Get the metadata for a specific database
	 * 
	 * @param engineId
	 * @param metaKeys
	 * @param ignoreMarkdown
	 * @return
	 */
	public static Map<String, Object> getAggregateEngineMetadata(String engineId, List<String> metaKeys,
			boolean ignoreMarkdown) {
		Map<String, Object> retMap = new HashMap<String, Object>();

		List<String> engineIds = new ArrayList<>();
		engineIds.add(engineId);

		try (IRawSelectWrapper wrapper = getEngineMetadataWrapper(engineIds, metaKeys, ignoreMarkdown)) {
			while (wrapper.hasNext()) {
				Object[] data = wrapper.next().getValues();
				String metaKey = (String) data[1];
				String metaValue = (String) data[2];

				// always send as array
				// if multi, send as array
				if (retMap.containsKey(metaKey)) {
					Object obj = retMap.get(metaKey);
					if (obj instanceof List) {
						((List) obj).add(metaValue);
					} else {
						List<Object> newList = new ArrayList<>();
						newList.add(obj);
						newList.add(metaValue);
						retMap.put(metaKey, newList);
					}
				} else {
					retMap.put(metaKey, metaValue);
				}
			}
		} catch (Exception e) {
			classLogger.error("Failed to aggregate engine metadata values", e);
		}

		return retMap;
	}

	/**
	 * Check if the user has access to the engine
	 * 
	 * @param engineId
	 * @param userId
	 * @return
	 * @throws Exception
	 */
	public static boolean checkUserHasAccessToDatabase(String engineId, String userId) throws Exception {
		return SecurityUserEngineUtils.checkUserHasAccessToEngine(engineId, userId);
	}

	/////////////////////////////////////////////////////////////////////////////////////
	/////////////////////////////////////////////////////////////////////////////////////
	/////////////////////////////////////////////////////////////////////////////////////

	/**
	 * Returns List of users that have no access credentials to a given engine
	 * 
	 * @param engineId
	 * @return
	 */
	public static List<Map<String, Object>> getEngineUsersNoCredentials(User user, String engineId, String searchTerm,
			long limit, long offset) throws IllegalAccessException {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		if (!userCanViewEngine(user, engineId)) {
			throw new IllegalAccessException("The user does not have access to view this engine");
		}

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("SMSS_USER__ID", "id"));
		qs.addSelector(new QueryColumnSelector("SMSS_USER__TYPE", "type"));
		qs.addSelector(new QueryColumnSelector("SMSS_USER__USERNAME", "username"));
		qs.addSelector(new QueryColumnSelector("SMSS_USER__NAME", "name"));
		qs.addSelector(new QueryColumnSelector("SMSS_USER__EMAIL", "email"));
		// filter for sub-query
		{
			SelectQueryStruct subQs = new SelectQueryStruct();
			qs.addExplicitFilter(SimpleQueryFilter.makeColToSubQuery("SMSS_USER__ID", "!=", subQs));
			// Sub-query itself
			subQs.addSelector(new QueryColumnSelector("ENGINEPERMISSION__USERID"));
			subQs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEPERMISSION__ENGINEID", "==", engineId));
			subQs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEPERMISSION__PERMISSION", "!=", null,
					PixelDataType.NULL_VALUE));
		}
		if (searchTerm != null && !(searchTerm = searchTerm.trim()).isEmpty()) {
			OrQueryFilter or = new OrQueryFilter();
			or.addFilter(SimpleQueryFilter.makeColToValFilter("SMSS_USER__ID", "?like", searchTerm));
			or.addFilter(SimpleQueryFilter.makeColToValFilter("SMSS_USER__NAME", "?like", searchTerm));
			or.addFilter(SimpleQueryFilter.makeColToValFilter("SMSS_USER__USERNAME", "?like", searchTerm));
			or.addFilter(SimpleQueryFilter.makeColToValFilter("SMSS_USER__EMAIL", "?like", searchTerm));
			qs.addExplicitFilter(or);
		}
		qs.addOrderBy(new QueryColumnOrderBySelector("SMSS_USER__NAME"));
		qs.addOrderBy(new QueryColumnOrderBySelector("SMSS_USER__EMAIL"));
		qs.addOrderBy(new QueryColumnOrderBySelector("SMSS_USER__ID"));
		if (limit > 0) {
			qs.setLimit(limit);
		}
		if (offset > 0) {
			qs.setOffSet(offset);
		}

		return QueryExecutionUtility.flushRsToMap(securityDb, qs);
	}

	/**
	 * Return the engines the user has explicit access to
	 * 
	 * @param singleUserId
	 * @return
	 */
	public static Set<String> getEngineUserHasExplicitAccess(User user) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEID"));
		OrQueryFilter orFilter = new OrQueryFilter();
		orFilter.addFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__GLOBAL", "==", true, PixelDataType.BOOLEAN));
		orFilter.addFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINEPERMISSION__USERID", "==", getUserFiltersQs(user)));
		qs.addExplicitFilter(orFilter);
		qs.addRelation("ENGINE", "ENGINEPERMISSION", "left.outer.join");
		return QueryExecutionUtility.flushToSetString(securityDb, qs, false);
	}

	/**
	 * Return if user has explicit permissions to this engine
	 * 
	 * @param user
	 * @param engineId
	 * @return
	 */
	public static boolean userHasExplicitAccess(User user, String engineId) {
		return SecurityUserEngineUtils.getUserEnginePermission(user, engineId) != null;
	}

	/**
	 * Determine if a user can request a engine
	 * 
	 * @param engineId
	 * @return
	 */
	public static boolean engineIsDiscoverable(String engineId) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEID"));
		qs.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINE__DISCOVERABLE", "==", true, PixelDataType.BOOLEAN));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ENGINEID", "==", engineId));
		boolean discoverable = false;
		try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(securityDb, qs)) {
			// if there is a row, you can request
			discoverable = wrapper.hasNext();
		} catch (Exception e) {
			classLogger.error("Failed to determine whether engine is discoverable", e);
		}
		return discoverable;
	}

	/**
	 * set user access request
	 * 
	 * @param userId
	 * @param userType
	 * @param engineId
	 * @param requestReasonComment
	 * @param permission
	 * @param user
	 */
	public static void setUserAccessRequest(String userId, String userType, String engineId,
			String requestReasonComment, int permission, User user) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		try {
			QueryExecutionUtility.write(securityDb, connection -> {
				// first mark previously undecided requests as old
				String updateQ = "UPDATE ENGINEACCESSREQUEST SET APPROVER_DECISION = 'OLD' WHERE REQUEST_USERID=? AND REQUEST_TYPE=? AND ENGINEID=? AND APPROVER_DECISION='NEW_REQUEST'";
				AbstractSqlQueryUtil securityQueryUtil = securityDb.getQueryUtil();
				try {
					int index = 1;
					try (PreparedStatement updatePs = connection.prepareStatement(updateQ)) {
						updatePs.setString(index++, userId);
						updatePs.setString(index++, userType);
						updatePs.setString(index++, engineId);
						updatePs.execute();

					}
				} catch (Exception e) {
					classLogger.error("Failed to update the user access request record", e);
					throw new IllegalArgumentException(
							"An error occurred while marking old user access request with detailed message = "
									+ e.getMessage());
				}

				// grab user info who is submitting request
				Pair<String, String> requesterDetails = User.getPrimaryUserIdAndTypePair(user);

				// now we do the new insert
				String insertQ = "INSERT INTO ENGINEACCESSREQUEST "
						+ "(ID, REQUEST_USERID, REQUEST_TYPE, REQUEST_TIMESTAMP, REQUEST_REASON, ENGINEID, PERMISSION, SUBMITTED_BY_USERID, SUBMITTED_BY_TYPE, APPROVER_DECISION) "
						+ "VALUES (?,?,?,?,?,?,?,?,?, 'NEW_REQUEST')";
				try {
					java.sql.Timestamp timestamp = Utility.getCurrentSqlTimestampUTC();

					int index = 1;
					try (PreparedStatement insertPs = connection.prepareStatement(insertQ)) {
						insertPs.setString(index++, UUID.randomUUID().toString());
						insertPs.setString(index++, userId);
						insertPs.setString(index++, userType);
						insertPs.setTimestamp(index++, timestamp);
						securityQueryUtil.setNullableLargeText(insertPs, index++, requestReasonComment);
						insertPs.setString(index++, engineId);
						insertPs.setInt(index++, permission);
						insertPs.setString(index++, requesterDetails.getValue0());
						insertPs.setString(index++, requesterDetails.getValue1());
						insertPs.execute();

					}
				} catch (Exception e) {
					classLogger.error("Failed to update the user access request record", e);
					throw new IllegalArgumentException(
							"An error occurred while adding user access request detailed message = " + e.getMessage());
				}
				return null;
			});
		} catch (RuntimeException e) {
			throw e;
		} catch (Exception e) {
			classLogger.error("Failed to update the user access request record", e);
			throw new IllegalArgumentException(
					"An error occurred while marking old user access request with detailed message = "
							+ e.getMessage());
		}
	}

	/**
	 * The level the user asked for in their pending access request on the engine,
	 * from their primary login's newest request that is not approved, denied or
	 * old, or -1 when there is none.
	 * 
	 * @param user
	 * @param engineId
	 * @return
	 */
	public static int getUserPendingAccessRequest(User user, String engineId) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		// grab user info who is submitting request
		Pair<String, String> requesterDetails = User.getPrimaryUserIdAndTypePair(user);

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINEACCESSREQUEST__APPROVER_DECISION"));
		qs.addSelector(new QueryColumnSelector("ENGINEACCESSREQUEST__PERMISSION"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEACCESSREQUEST__REQUEST_USERID", "==",
				requesterDetails.getValue0()));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEACCESSREQUEST__REQUEST_TYPE", "==",
				requesterDetails.getValue1()));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEACCESSREQUEST__ENGINEID", "==", engineId));
		qs.addOrderBy("ENGINEACCESSREQUEST__REQUEST_TIMESTAMP", "desc");
		int pendingPermission = -1;
		try (IRawSelectWrapper it = WrapperManager.getInstance().getRawWrapper(securityDb, qs)) {
			while (it.hasNext()) {
				Object[] values = it.next().getValues();
				String mostRecentAction = (String) values[0];
				if (!mostRecentAction.equals("APPROVED") && !mostRecentAction.equals("DENIED")
						&& !mostRecentAction.equals("OLD")) {
					pendingPermission = ((Number) values[1]).intValue();
					break;
				}
			}
		} catch (Exception e) {
			classLogger.error("Failed to retrieve pending user access requests", e);
		}

		return pendingPermission;
	}

	/**
	 * Get the list of engines the user does not have access to but can request
	 * 
	 * @param allUserEngines
	 * @throws Exception
	 */
	public static List<Map<String, Object>> getUserRequestableEngines(Collection<String> allUserEngines) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEID"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEDISPLAYNAME"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ENGINEID", "!=", allUserEngines));
		qs.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINE__DISCOVERABLE", "==", true, PixelDataType.BOOLEAN));
		return QueryExecutionUtility.flushRsToMap(securityDb, qs);
	}

	public static List<Map<String, Object>> getEngineInfo(Collection<String> engineFilter) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEID"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEDISPLAYNAME"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ENGINEID", "==", engineFilter));
		return QueryExecutionUtility.flushRsToMap(securityDb, qs);
	}

	/**
	 * Retrieve the engine owner
	 * 
	 * @param user
	 * @param engineId
	 * @param insightId
	 * @return
	 * @throws IllegalAccessException
	 */
	public static List<String> getEngineOwners(String engineId) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("SMSS_USER__EMAIL", "email"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEPERMISSION__ENGINEID", "==", engineId));
		qs.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("PERMISSION__ID", "==", AccessPermissionEnum.OWNER.getId()));
		qs.addRelation("SMSS_USER", "ENGINEPERMISSION", "inner.join");
		qs.addRelation("ENGINEPERMISSION", "PERMISSION", "inner.join");
		qs.addOrderBy(new QueryColumnOrderBySelector("SMSS_USER__ID"));
		return QueryExecutionUtility.flushToListString(securityDb, qs);
	}

	/**
	 * Get global engines
	 * 
	 * @return
	 */
	public static Set<String> getGlobalEngineIds() {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEID"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__GLOBAL", "==", true, PixelDataType.BOOLEAN));
		return QueryExecutionUtility.flushToSetString(securityDb, qs, false);
	}

	public static List<Map<String, Object>> getUserEngineList(User user, List<String> engineTypes,
			List<String> engineIdFilters, boolean favoritesOnly, Map<String, Object> engineMetadataFilter,
			List<Integer> permissionFilters, String searchTerm, String limit, String offset) {
		return getUserEngineList(user, engineTypes, engineIdFilters, favoritesOnly, engineMetadataFilter,
				permissionFilters, searchTerm, limit, offset, null);
	}

	/**
	 * Get the list of the database information that the user has access to
	 * 
	 * @param user
	 * @param engineTypes
	 * @param engineIdFilters
	 * @param favoritesOnly
	 * @param engineMetadataFilter
	 * @param permissionFilters
	 * @param searchTerm
	 * @param limit
	 * @param offset
	 * @param sortFields
	 * @return
	 */
	public static List<Map<String, Object>> getUserEngineList(User user, List<String> engineTypes,
			List<String> engineIdFilters, boolean favoritesOnly, Map<String, Object> engineMetadataFilter,
			List<Integer> permissionFilters, String searchTerm, String limit, String offset,
			Map<String, String> sortFields) {
		return getUserEngineList(user, engineTypes, engineIdFilters, favoritesOnly, engineMetadataFilter,
				permissionFilters, searchTerm, limit, offset, sortFields, null, null);
	}

	/**
	 * Get the engines the user can see: the global ones, and those the user or one
	 * of their groups holds a grant on, leaving out any the user has hidden.
	 *
	 * @param user                 the user; their ids come from every login in the
	 *                             session
	 * @param engineTypes          keep only the engines of these types; null or
	 *                             empty for every type
	 * @param engineIdFilters      keep only these engine ids; null or empty for
	 *                             every engine
	 * @param favoritesOnly        keep only the user's favorites
	 * @param engineMetadataFilter keep only the engines whose metadata holds each
	 *                             of these key and value pairs; null or empty for
	 *                             no filter
	 * @param permissionFilters    keep only the engines on which the user's own
	 *                             grant is one of these
	 *                             {@link prerna.auth.AccessPermissionEnum} ids,
	 *                             ignoring group grants; null or empty for no
	 *                             filter
	 * @param searchTerm           keep only the engines whose id, name, or display
	 *                             name matches; null or blank for no search
	 * @param limit                the most engines to return; null or empty for no
	 *                             limit
	 * @param offset               how many engines to skip; null or empty for none
	 * @param sortFields           sort keys and directions: {@code ENGINENAME} or
	 *                             {@code DATECREATED} mapped to {@code ASC} or
	 *                             {@code DESC}; null or empty to sort by name
	 * @param effectivePermissions keep only the engines whose effective permission
	 *                             is one of these
	 *                             {@link prerna.auth.AccessPermissionEnum} ids: the
	 *                             better of the user's own grant and their groups'
	 *                             grant, with a global engine the user holds no
	 *                             grant on counting as read only; null or empty for
	 *                             no filter
	 * @param createdBy            keep only the engines created by one of these
	 *                             (login id, login type) pairs, as
	 *                             {@link User#getUserIdAndType(User)} returns them;
	 *                             null or empty for no filter
	 * @return one map per engine, with its details, the user's and their groups'
	 *         grants, and the effective {@code permission}
	 * @throws IllegalArgumentException when a level in {@code effectivePermissions}
	 *                                  is not an
	 *                                  {@link prerna.auth.AccessPermissionEnum} id
	 */
	public static List<Map<String, Object>> getUserEngineList(User user, List<String> engineTypes,
			List<String> engineIdFilters, boolean favoritesOnly, Map<String, Object> engineMetadataFilter,
			List<Integer> permissionFilters, String searchTerm, String limit, String offset,
			Map<String, String> sortFields, Collection<Integer> effectivePermissions,
			Collection<Pair<String, String>> createdBy) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();

		String enginePrefix = "ENGINE__";
		String groupEnginePermission = "GROUPENGINEPERMISSION__";

		Collection<String> userIds = getUserFiltersQs(user);
		boolean hasSearchTerm = searchTerm != null && !(searchTerm = searchTerm.trim()).isEmpty();

		SelectQueryStruct qs1 = new SelectQueryStruct();
		// preferred selectors
		qs1.addSelector(new QueryColumnSelector("ENGINE__ENGINEID", "engine_id"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME", "engine_name"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__ENGINEDISPLAYNAME", "engine_display_name"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__ENGINETYPE", "engine_type"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__ENGINESUBTYPE", "engine_subtype"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__COST", "engine_cost"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__DISCOVERABLE", "engine_discoverable"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__GLOBAL", "engine_global"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__TOOL_APP", "engine_tool_app"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__CREATEDBY", "engine_created_by"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__CREATEDBYTYPE", "engine_created_by_type"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__DATECREATED", "engine_date_created"));
		qs1.addSelector(QueryFunctionSelector.makeFunctionSelector(QueryFunctionHelper.LOWER, QueryFunctionSelector
				.makeCoalesceSelector("ENGINE__ENGINEDISPLAYNAME", "ENGINE__ENGINENAME", "display_name"),
				"low_engine_name"));
		qs1.addSelector(new QueryColumnSelector("USER_PERMISSIONS__PERMISSION", "engine_user_permission"));
		qs1.addSelector(new QueryColumnSelector("GROUP_PERMISSIONS__PERMISSION", "engine_group_permission"));
		qs1.addSelector(
				QueryFunctionSelector.makeCol2ValCoalesceSelector("USER_PREFERENCES__FAVORITE", 0, "engine_favorite"));

		// Legacy aliases, every one of which repeats a column already selected above
		// under its engine_* name. The commented entries are unused by either client.

		// legacy aliases (app_*): kept for backwards compatibility
		qs1.addSelector(new QueryColumnSelector("ENGINE__ENGINEID", "app_id"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME", "app_name"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__ENGINETYPE", "app_type"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__ENGINESUBTYPE", "app_subtype"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__COST", "app_cost"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__ENGINEID", "database_id"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME", "database_name"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__ENGINETYPE", "database_type"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__DISCOVERABLE", "database_discoverable"));

		// description from engine metadata
		qs1.addSelector(new QueryColumnSelector("ENGINE_DESCRIPTION__DESCRIPTION", "engine_description"));

		// this block is for max permissions
		// If both null - return null
		// if either not null - return the permission value that is not null
		// if both not null - return the max permissions (I.E lowest number)
		{
			AndQueryFilter and = new AndQueryFilter();
			and.addFilter(SimpleQueryFilter.makeColToValFilter("GROUP_PERMISSIONS__PERMISSION", "==", null,
					PixelDataType.CONST_INT));
			and.addFilter(SimpleQueryFilter.makeColToValFilter("USER_PERMISSIONS__PERMISSION", "==", null,
					PixelDataType.CONST_INT));

			AndQueryFilter and1 = new AndQueryFilter();
			and1.addFilter(SimpleQueryFilter.makeColToValFilter("GROUP_PERMISSIONS__PERMISSION", "!=", null,
					PixelDataType.CONST_INT));
			and1.addFilter(SimpleQueryFilter.makeColToValFilter("USER_PERMISSIONS__PERMISSION", "==", null,
					PixelDataType.CONST_INT));

			AndQueryFilter and2 = new AndQueryFilter();
			and2.addFilter(SimpleQueryFilter.makeColToValFilter("GROUP_PERMISSIONS__PERMISSION", "==", null,
					PixelDataType.CONST_INT));
			and2.addFilter(SimpleQueryFilter.makeColToValFilter("USER_PERMISSIONS__PERMISSION", "!=", null,
					PixelDataType.CONST_INT));

			SimpleQueryFilter maxPermFilter = SimpleQueryFilter.makeColToColFilter("USER_PERMISSIONS__PERMISSION", "<",
					"GROUP_PERMISSIONS__PERMISSION");

			QueryIfSelector qis3 = QueryIfSelector.makeQueryIfSelector(maxPermFilter,
					new QueryColumnSelector("USER_PERMISSIONS__PERMISSION"),
					new QueryColumnSelector("GROUP_PERMISSIONS__PERMISSION"), "permission");

			QueryIfSelector qis2 = QueryIfSelector.makeQueryIfSelector(and2,
					new QueryColumnSelector("USER_PERMISSIONS__PERMISSION"), qis3, "permission");

			QueryIfSelector qis1 = QueryIfSelector.makeQueryIfSelector(and1,
					new QueryColumnSelector("GROUP_PERMISSIONS__PERMISSION"), qis2, "permission");

			QueryIfSelector qis = QueryIfSelector.makeQueryIfSelector(and,
					new QueryColumnSelector("USER_PERMISSIONS__PERMISSION"), qis1, "permission");

			qs1.addSelector(qis);
		}

		// add a join to get the user permission level
		{
			SelectQueryStruct qs2 = new SelectQueryStruct();
			qs2.addSelector(new QueryColumnSelector("ENGINEPERMISSION__ENGINEID", "ENGINEID"));
			qs2.addSelector(QueryFunctionSelector.makeFunctionSelector(QueryFunctionHelper.MIN,
					"ENGINEPERMISSION__PERMISSION", "PERMISSION"));
			qs2.addGroupBy(new QueryColumnSelector("ENGINEPERMISSION__ENGINEID", "ENGINEID"));
			qs2.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEPERMISSION__USERID", "==", userIds));
			IRelation subQuery = new SubqueryRelationship(qs2, "USER_PERMISSIONS", "left.outer.join",
					new String[] { "USER_PERMISSIONS__ENGINEID", "ENGINE__ENGINEID", "=" });
			qs1.addRelation(subQuery);
		}

		// add a join to get if favorite and the visibility
		{
			SelectQueryStruct prefQs = new SelectQueryStruct();
			prefQs.addSelector(new QueryColumnSelector("USERENGINEPREFERENCE__ENGINEID", "ENGINEID"));

			QueryFunctionSelector castFavorite = QueryFunctionSelector.makeFunctionSelector(QueryFunctionHelper.CAST,
					"USERENGINEPREFERENCE__FAVORITE", "castFavorite");
			castFavorite.setDataType(securityDb.getQueryUtil().getIntegerDataTypeName());
			prefQs.addSelector(
					QueryFunctionSelector.makeFunctionSelector(QueryFunctionHelper.MAX, castFavorite, "FAVORITE"));
			QueryFunctionSelector castVisibility = QueryFunctionSelector.makeFunctionSelector(QueryFunctionHelper.CAST,
					"USERENGINEPREFERENCE__VISIBILITY", "castVisibility");
			castVisibility.setDataType(securityDb.getQueryUtil().getIntegerDataTypeName());
			prefQs.addSelector(
					QueryFunctionSelector.makeFunctionSelector(QueryFunctionHelper.MAX, castVisibility, "VISIBILITY"));
			prefQs.addGroupBy(new QueryColumnSelector("USERENGINEPREFERENCE__ENGINEID", "ENGINEID"));
			prefQs.addExplicitFilter(
					getUserIdAndTypeFilter(user, "USERENGINEPREFERENCE__USERID", "USERENGINEPREFERENCE__TYPE"));
			IRelation subQuery = new SubqueryRelationship(prefQs, "USER_PREFERENCES", "left.outer.join",
					new String[] { "USER_PREFERENCES__ENGINEID", "ENGINE__ENGINEID", "=" });
			qs1.addRelation(subQuery);
		}

		// add a join to get the group permission level
		{
			SelectQueryStruct qs3 = new SelectQueryStruct();
			qs3.addSelector(new QueryColumnSelector(groupEnginePermission + "ENGINEID", "ENGINEID"));
			qs3.addSelector(QueryFunctionSelector.makeFunctionSelector(QueryFunctionHelper.MIN,
					groupEnginePermission + "PERMISSION", "PERMISSION"));
			qs3.addGroupBy(new QueryColumnSelector(groupEnginePermission + "ENGINEID", "ENGINEID"));

			qs3.addExplicitFilter(getUserGroupPermissionFilter(user, groupEnginePermission));

			IRelation subQuery = new SubqueryRelationship(qs3, "GROUP_PERMISSIONS", "left.outer.join",
					new String[] { "GROUP_PERMISSIONS__ENGINEID", "ENGINE__ENGINEID", "=" });
			qs1.addRelation(subQuery);
		}

		// add a join to get the engine description from metadata
		{
			SelectQueryStruct descQs = new SelectQueryStruct();
			descQs.addSelector(new QueryColumnSelector("ENGINEMETA__ENGINEID", "ENGINEID"));
			descQs.addSelector(new QueryColumnSelector("ENGINEMETA__METAVALUE", "DESCRIPTION"));
			descQs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEMETA__METAKEY", "==", "description"));
			IRelation descSubQuery = new SubqueryRelationship(descQs, "ENGINE_DESCRIPTION", "left.outer.join",
					new String[] { "ENGINE_DESCRIPTION__ENGINEID", "ENGINE__ENGINEID", "=" });
			qs1.addRelation(descSubQuery);
		}

		// filters
		if (engineIdFilters != null && !engineIdFilters.isEmpty()) {
			qs1.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ENGINEID", "==", engineIdFilters));
		}
		if (engineTypes != null && !engineTypes.isEmpty()) {
			qs1.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ENGINETYPE", "==", engineTypes));
		}

		// filter based on permission filters
		if (permissionFilters != null && !permissionFilters.isEmpty()) {
			qs1.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("USER_PERMISSIONS__PERMISSION", "==",
					permissionFilters, PixelDataType.CONST_INT));
		}
		if (effectivePermissions != null && !effectivePermissions.isEmpty()) {
			qs1.addExplicitFilter(getEffectivePermissionFilter("USER_PERMISSIONS__PERMISSION",
					"GROUP_PERMISSIONS__PERMISSION", enginePrefix + "GLOBAL", effectivePermissions));
		}
		if (createdBy != null && !createdBy.isEmpty()) {
			qs1.addExplicitFilter(
					getCreatedByFilter(enginePrefix + "CREATEDBY", enginePrefix + "CREATEDBYTYPE", createdBy));
		}

		OrQueryFilter orFilter = new OrQueryFilter();
		{
			orFilter.addFilter(
					SimpleQueryFilter.makeColToValFilter("ENGINE__GLOBAL", "==", true, PixelDataType.BOOLEAN));
			orFilter.addFilter(SimpleQueryFilter.makeColToValFilter("USER_PERMISSIONS__PERMISSION", "!=", null,
					PixelDataType.CONST_INT));
			orFilter.addFilter(SimpleQueryFilter.makeColToValFilter("GROUP_PERMISSIONS__PERMISSION", "!=", null,
					PixelDataType.CONST_INT));
			qs1.addExplicitFilter(orFilter);
		}
		// only show those that are visible
		// remember, user preferences cast this to int
		qs1.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("USER_PREFERENCES__VISIBILITY", "==",
				Arrays.asList(new Object[] { 1, null }), PixelDataType.CONST_INT));
		// favorites only
		// remember, user preferences cast this to int
		if (favoritesOnly) {
			qs1.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("USER_PREFERENCES__FAVORITE", "==", 1,
					PixelDataType.CONST_INT));
		}
		// optional word filter on the engine name
		if (hasSearchTerm) {
			OrQueryFilter searchFilter = new OrQueryFilter();
			searchFilter.addFilter(securityDb.getQueryUtil().getSearchRegexFilter("ENGINE__ENGINENAME", searchTerm));
			searchFilter.addFilter(securityDb.getQueryUtil().getSearchRegexFilter("ENGINE__ENGINEID", searchTerm));
			searchFilter
					.addFilter(securityDb.getQueryUtil().getSearchRegexFilter("ENGINE__ENGINEDISPLAYNAME", searchTerm));
			qs1.addExplicitFilter(searchFilter);
		}
		// filtering by enginemeta key-value pairs (i.e. <tag>:value): for each pair,
		// add in-filter against engineids from subquery
		if (engineMetadataFilter != null && !engineMetadataFilter.isEmpty()) {
			for (String k : engineMetadataFilter.keySet()) {
				SelectQueryStruct subQs = new SelectQueryStruct();
				subQs.addSelector(new QueryColumnSelector("ENGINEMETA__ENGINEID"));
				subQs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEMETA__METAKEY", "==", k));
				subQs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEMETA__METAVALUE", "==",
						engineMetadataFilter.get(k)));
				qs1.addExplicitFilter(SimpleQueryFilter.makeColToSubQuery("ENGINE__ENGINEID", "==", subQs));
			}
		}

		if (sortFields == null || sortFields.isEmpty()) {
			// Default Sorting
			qs1.addOrderBy(new QueryColumnOrderBySelector("low_engine_name"));
		} else {
			Set<String> sortKeys = sortFields.keySet();
			Set<String> validSorts = new HashSet<>(Arrays.asList("ENGINENAME", "DATECREATED"));
			if (sortFields.containsKey(null) || sortFields.containsValue(null)) {
				throw new SemossPixelException("Sort parameters cannot contain null keys or values");
			}
			if (!validSorts.containsAll(sortKeys)) {
				throw new SemossPixelException(
						"Invalid Sort Parameters passed: Only \"ENGINENAME\" and \"DATECREATED\" are supported");
			}
			for (String s : sortKeys) {
				if ("ENGINENAME".equals(s)) {
					qs1.addOrderBy("low_engine_name", sortFields.getOrDefault(s, "ASC"));
				} else {
					qs1.addOrderBy("ENGINE__" + s, sortFields.getOrDefault(s, "ASC"));
				}
			}
		}
		// always add a secondary sort by engine_id
		qs1.addOrderBy(new QueryColumnOrderBySelector("engine_id"));

		Long long_limit = -1L;
		Long long_offset = -1L;
		if (limit != null && !limit.trim().isEmpty()) {
			long_limit = ((Number) Double.parseDouble(limit)).longValue();
		}
		if (offset != null && !offset.trim().isEmpty()) {
			long_offset = ((Number) Double.parseDouble(offset)).longValue();
		}
		qs1.setLimit(long_limit);
		qs1.setOffSet(long_offset);

		return QueryExecutionUtility.flushRsToMap(securityDb, qs1);
	}

	/**
	 * Get the list of the database ids that the user has access to
	 * 
	 * @param user
	 * @param includeGlobal
	 * @param includeDiscoverable
	 * @param includeExistingAccess
	 * @return
	 */
	public static List<String> getUserEngineIdList(User user, List<String> engineTypes, boolean includeGlobal,
			boolean includeDiscoverable, boolean includeExistingAccess) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		String enginePrefix = "ENGINE__";
		String enginePermissionPrefix = "ENGINEPERMISSION__";
		String groupEnginePermissionPrefix = "GROUPENGINEPERMISSION__";

		Collection<String> userIds = getUserFiltersQs(user);

		SelectQueryStruct qs1 = new SelectQueryStruct();
		// selectors
		qs1.addSelector(new QueryColumnSelector(enginePrefix + "ENGINEID", "database_id"));
		// filters
		OrQueryFilter orFilter = new OrQueryFilter();
		if (includeGlobal) {
			orFilter.addFilter(
					SimpleQueryFilter.makeColToValFilter(enginePrefix + "GLOBAL", "==", true, PixelDataType.BOOLEAN));
		}
		if (includeDiscoverable) {
			orFilter.addFilter(SimpleQueryFilter.makeColToValFilter(enginePrefix + "DISCOVERABLE", "==", true,
					PixelDataType.BOOLEAN));
		}
		if (engineTypes != null && !engineTypes.isEmpty()) {
			qs1.addExplicitFilter(SimpleQueryFilter.makeColToValFilter(enginePrefix + "ENGINETYPE", "==", engineTypes));
		}
		String existingAccessComparator = "==";
		if (!includeExistingAccess) {
			existingAccessComparator = "!=";
		}
		if (!includeExistingAccess && !includeDiscoverable) {
			throw new IllegalArgumentException(
					"Fitler combinations can result in ids that the user does not have access to. Please adjust your parameters");
		}
		{
			// user access
			SelectQueryStruct qs2 = new SelectQueryStruct();
			qs2.addSelector(new QueryColumnSelector(enginePermissionPrefix + "ENGINEID", "ENGINEID"));
			qs2.addExplicitFilter(
					SimpleQueryFilter.makeColToValFilter(enginePermissionPrefix + "USERID", "==", userIds));
			qs2.addExplicitFilter(SimpleQueryFilter.makeColToValFilter(enginePermissionPrefix + "PERMISSION", "!=",
					null, PixelDataType.CONST_INT));
			IQueryFilter directAccess = SimpleQueryFilter.makeColToSubQuery(enginePrefix + "ENGINEID",
					existingAccessComparator, qs2);
			if (includeExistingAccess) {
				orFilter.addFilter(directAccess);
			} else {
				qs1.addExplicitFilter(directAccess);
			}
		}
		{
			SelectQueryStruct qs3 = new SelectQueryStruct();
			qs3.addSelector(new QueryColumnSelector(groupEnginePermissionPrefix + "ENGINEID", "ENGINEID"));
			qs3.addExplicitFilter(getUserGroupPermissionFilter(user, groupEnginePermissionPrefix));
			IQueryFilter groupAccess = SimpleQueryFilter.makeColToSubQuery(enginePrefix + "ENGINEID",
					existingAccessComparator, qs3);
			if (includeExistingAccess) {
				orFilter.addFilter(groupAccess);
			} else {
				qs1.addExplicitFilter(groupAccess);
			}
		}

		qs1.addExplicitFilter(orFilter);

		return QueryExecutionUtility.flushToListString(securityDb, qs1);
	}

	/**
	 * Get all the available engine metadata and their counts for given keys
	 * 
	 * @param engineFilters
	 * @param metaKey
	 * @return
	 */
	public static List<Map<String, Object>> getAvailableMetaValues(List<String> engineFilters, List<String> metaKeys) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		// selectors
		qs.addSelector(new QueryColumnSelector("ENGINEMETA__METAKEY"));
		qs.addSelector(new QueryColumnSelector("ENGINEMETA__METAVALUE"));
		QueryFunctionSelector fSelector = new QueryFunctionSelector();
		fSelector.setAlias("count");
		fSelector.setFunction(QueryFunctionHelper.COUNT);
		fSelector.addInnerSelector(new QueryColumnSelector("ENGINEMETA__METAVALUE"));
		qs.addSelector(fSelector);
		// filters
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEMETA__METAKEY", "==", metaKeys));
		if (engineFilters != null && !engineFilters.isEmpty()) {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEMETA__ENGINEID", "==", engineFilters));
		}
		// group
		qs.addGroupBy(new QueryColumnSelector("ENGINEMETA__METAKEY"));
		qs.addGroupBy(new QueryColumnSelector("ENGINEMETA__METAVALUE"));

		return QueryExecutionUtility.flushRsToMap(securityDb, qs);
	}

	/**
	 * Get all user database and database ids regardless of it being hidden or not
	 * 
	 * @param userId
	 * @return
	 */
	public static List<Map<String, Object>> getAllUserDatabaseList(User user) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();

		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEID", "app_id"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME", "app_name"));
		// qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEDISPLAYNAME",
		// "app_display_name"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINETYPE", "app_type"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINESUBTYPE", "app_subtype"));
		qs.addSelector(new QueryColumnSelector("ENGINE__COST", "app_cost"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__GLOBAL", "==", true, PixelDataType.BOOLEAN));
		List<Map<String, Object>> allGlobalEnginesMap = QueryExecutionUtility.flushRsToMap(securityDb, qs);

		SelectQueryStruct qs2 = new SelectQueryStruct();
		qs2.addSelector(new QueryColumnSelector("ENGINE__ENGINEID", "app_id"));
		qs2.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME", "app_name"));
		// qs2.addSelector(new QueryColumnSelector("ENGINE__ENGINEDISPLAYNAME",
		// "app_display_name"));
		qs2.addSelector(new QueryColumnSelector("ENGINE__ENGINETYPE", "app_type"));
		qs2.addSelector(new QueryColumnSelector("ENGINE__ENGINESUBTYPE", "app_subtype"));
		qs2.addSelector(new QueryColumnSelector("ENGINE__COST", "app_cost"));
		qs2.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINEPERMISSION__USERID", "==", getUserFiltersQs(user)));
		qs2.addRelation("ENGINE", "ENGINEPERMISSION", "inner.join");

		List<Map<String, Object>> databaseMap = QueryExecutionUtility.flushRsToMap(securityDb, qs2);
		databaseMap.addAll(allGlobalEnginesMap);
		return databaseMap;
	}

	/**
	 * Get the list of the engine information that the user has access to
	 * 
	 * @param userId
	 * @return
	 */
	public static List<Map<String, Object>> getUserEngineList(User user, String engineFilter,
			List<String> engineTypeFilter) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEDISPLAYNAME", "engine_display_name"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEID", "engine_id"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME", "engine_name"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINETYPE", "engine_type"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINESUBTYPE", "engine_subtype"));
		qs.addSelector(new QueryColumnSelector("ENGINE__TOOL_APP", "engine_tool_app"));
		qs.addSelector(new QueryColumnSelector("ENGINE__COST", "engine_cost"));
		qs.addSelector(new QueryColumnSelector("ENGINE__DISCOVERABLE", "engine_discoverable"));
		qs.addSelector(new QueryColumnSelector("ENGINE__GLOBAL", "engine_global"));
		qs.addSelector(new QueryColumnSelector("ENGINE__CREATEDBY", "engine_created_by"));
		qs.addSelector(new QueryColumnSelector("ENGINE__CREATEDBYTYPE", "engine_created_by_type"));
		qs.addSelector(new QueryColumnSelector("ENGINE__DATECREATED", "engine_date_created"));

		// legacy aliases (database_*): kept for backwards compatibility
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEID", "database_id"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME", "database_name"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINETYPE", "database_type"));
		qs.addSelector(new QueryColumnSelector("ENGINE__DISCOVERABLE", "database_discoverable"));

		// for sorting
		qs.addSelector(QueryFunctionSelector.makeFunctionSelector(QueryFunctionHelper.LOWER, QueryFunctionSelector
				.makeCoalesceSelector("ENGINE__ENGINEDISPLAYNAME", "ENGINE__ENGINENAME", "display_name"),
				"low_engine_name"));
		if (engineFilter != null && !engineFilter.isEmpty()) {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ENGINEID", "==", engineFilter));
		}
		if (engineTypeFilter != null && !engineTypeFilter.isEmpty()) {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ENGINETYPE", "==", engineTypeFilter));
		}
		{
			OrQueryFilter orFilter = new OrQueryFilter();
			orFilter.addFilter(
					SimpleQueryFilter.makeColToValFilter("ENGINE__GLOBAL", "==", true, PixelDataType.BOOLEAN));
			orFilter.addFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__DISCOVERABLE", "==",
					Arrays.asList(true, null), PixelDataType.BOOLEAN));
			AndQueryFilter directAccess = new AndQueryFilter();
			directAccess.addFilter(
					SimpleQueryFilter.makeColToValFilter("ENGINEPERMISSION__USERID", "==", getUserFiltersQs(user)));
			directAccess.addFilter(SimpleQueryFilter.makeColToValFilter("ENGINEPERMISSION__PERMISSION", "!=", null,
					PixelDataType.CONST_INT));
			orFilter.addFilter(directAccess);
			SelectQueryStruct groupAccess = new SelectQueryStruct();
			groupAccess.addSelector(new QueryColumnSelector("GROUPENGINEPERMISSION__ENGINEID"));
			groupAccess.addExplicitFilter(getUserGroupPermissionFilter(user, "GROUPENGINEPERMISSION__"));
			orFilter.addFilter(SimpleQueryFilter.makeColToSubQuery("ENGINE__ENGINEID", "==", groupAccess));
			qs.addExplicitFilter(orFilter);
		}
		qs.addRelation("ENGINE", "ENGINEPERMISSION", "left.outer.join");
		qs.addOrderBy(new QueryColumnOrderBySelector("low_engine_name"));

		return QueryExecutionUtility.flushRsToMap(securityDb, qs);
	}

	/**
	 * Get the list of the database information that the user has access to
	 * 
	 * @param user
	 * @param engineTypeFilter
	 * @return
	 */
	public static List<Map<String, Object>> getUserEngineList(User user, List<String> engineTypeFilter, Integer limit,
			Integer offset) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		Collection<String> userIds = getUserFiltersQs(user);

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEID", "app_id"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME", "app_name"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINETYPE", "app_type"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINESUBTYPE", "app_subtype"));
		qs.addSelector(new QueryColumnSelector("ENGINE__COST", "app_cost"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEID", "database_id"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME", "database_name"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINETYPE", "database_type"));
		// qs.addSelector(new QueryColumnSelector("ENGINE__ENGINESUBTYPE",
		// "database_subtype"));
		// qs.addSelector(new QueryColumnSelector("ENGINE__COST", "database_cost"));
		QueryFunctionSelector fun = new QueryFunctionSelector();
		fun.setFunction(QueryFunctionHelper.LOWER);
		fun.addInnerSelector(new QueryColumnSelector("ENGINE__ENGINENAME"));
		fun.setAlias("low_database_name");
		qs.addSelector(fun);
		if (engineTypeFilter != null && !engineTypeFilter.isEmpty()) {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ENGINETYPE", "==", engineTypeFilter));
		}
		{
			OrQueryFilter orFilter = new OrQueryFilter();
			orFilter.addFilter(
					SimpleQueryFilter.makeColToValFilter("ENGINE__GLOBAL", "==", true, PixelDataType.BOOLEAN));
			orFilter.addFilter(SimpleQueryFilter.makeColToValFilter("ENGINEPERMISSION__USERID", "==", userIds));
			qs.addExplicitFilter(orFilter);
		}
		// remove the engines the user has hidden
		qs.addExplicitFilter(SimpleQueryFilter.makeColToSubQuery("ENGINE__ENGINEID", "!=",
				getHiddenIdsQs(user, "USERENGINEPREFERENCE", "ENGINEID")));
		// joins
		qs.addRelation("ENGINE", "ENGINEPERMISSION", "left.outer.join");
		qs.addOrderBy(new QueryColumnOrderBySelector("low_database_name"));

		// add the limit and offset
		if (limit != null && limit > 0) {
			qs.setLimit(limit);
		}
		if (offset != null && offset > 0) {
			qs.setOffSet(offset);
		}

		return QueryExecutionUtility.flushRsToMap(securityDb, qs);
	}

	/**
	 * Get the list of the engine information that the user has access to
	 * 
	 * @param userId
	 * @return
	 */
	public static List<Map<String, Object>> getDiscoverableEngineList(String engineFilter,
			List<String> engineTypeFilter) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEDISPLAYNAME", "engine_display_name"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEID", "engine_id"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME", "engine_name"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINETYPE", "engine_type"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINESUBTYPE", "engine_subtype"));
		qs.addSelector(new QueryColumnSelector("ENGINE__COST", "engine_cost"));
		qs.addSelector(new QueryColumnSelector("ENGINE__CREATEDBY", "engine_created_by"));
		qs.addSelector(new QueryColumnSelector("ENGINE__CREATEDBYTYPE", "engine_created_by_type"));
		qs.addSelector(new QueryColumnSelector("ENGINE__DATECREATED", "engine_date_created"));

		// legacy aliases (database_*): kept for backwards compatibility
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEID", "database_id"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME", "database_name"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINETYPE", "database_type"));

		// for sorting
		qs.addSelector(QueryFunctionSelector.makeFunctionSelector(QueryFunctionHelper.LOWER, QueryFunctionSelector
				.makeCoalesceSelector("ENGINE__ENGINEDISPLAYNAME", "ENGINE__ENGINENAME", "display_name"),
				"low_engine_name"));
		if (engineFilter != null && !engineFilter.isEmpty()) {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ENGINEID", "==", engineFilter));
		}
		if (engineTypeFilter != null && !engineTypeFilter.isEmpty()) {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ENGINETYPE", "==", engineTypeFilter));
		}
		qs.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINE__DISCOVERABLE", "==", true, PixelDataType.BOOLEAN));
		qs.addOrderBy(new QueryColumnOrderBySelector("low_engine_name"));
		return QueryExecutionUtility.flushRsToMap(securityDb, qs);
	}

	/**
	 * Get the list of the database information that the user does not have access
	 * to, but is discoverable
	 * 
	 * @param user
	 * @param engineTypes
	 * @param engineFilters
	 * @param engineMetadataFilter
	 * @param searchTerm
	 * @param limit
	 * @param offset
	 * @return
	 */
	public static List<Map<String, Object>> getUserDiscoverableEngineList(User user, List<String> engineTypes,
			List<String> engineFilters, Map<String, Object> engineMetadataFilter, String searchTerm, String limit,
			String offset) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		Collection<String> userIds = getUserFiltersQs(user);

		boolean hasSearchTerm = searchTerm != null && !(searchTerm = searchTerm.trim()).isEmpty();

		SelectQueryStruct qs1 = new SelectQueryStruct();
		// selectors
		qs1.addSelector(new QueryColumnSelector("ENGINE__ENGINEID", "engine_id"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME", "engine_name"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__ENGINEDISPLAYNAME", "engine_display_name"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__ENGINETYPE", "engine_type"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__ENGINESUBTYPE", "engine_subtype"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__COST", "engine_cost"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__DISCOVERABLE", "engine_discoverable"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__GLOBAL", "engine_global"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__CREATEDBY", "engine_created_by"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__CREATEDBYTYPE", "engine_created_by_type"));
		qs1.addSelector(new QueryColumnSelector("ENGINE__DATECREATED", "engine_date_created"));
		qs1.addSelector(QueryFunctionSelector.makeFunctionSelector(QueryFunctionHelper.LOWER, "ENGINE__ENGINENAME",
				"low_engine_name"));
		// only care about discoverable engines
		qs1.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINE__DISCOVERABLE", "==", true, PixelDataType.BOOLEAN));
		qs1.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINE__GLOBAL", "==", false, PixelDataType.BOOLEAN));
		// remove user permission access
		{
			SelectQueryStruct subQsUser = new SelectQueryStruct();
			subQsUser.addSelector(new QueryColumnSelector("ENGINEPERMISSION__ENGINEID"));
			subQsUser
					.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEPERMISSION__USERID", "==", userIds));
			qs1.addExplicitFilter(SimpleQueryFilter.makeColToSubQuery("ENGINE__ENGINEID", "!=", subQsUser));
		}
		{
			// remove group permission access
			SelectQueryStruct subQsGroup = new SelectQueryStruct();
			subQsGroup.addSelector(new QueryColumnSelector("GROUPENGINEPERMISSION__ENGINEID"));
			OrQueryFilter orFilter = new OrQueryFilter();
			List<AuthProvider> logins = user.getLogins();
			for (AuthProvider login : logins) {
				if (user.getAccessToken(login).getUserGroups().isEmpty()) {
					continue;
				}
				AndQueryFilter andFilter = new AndQueryFilter();
				andFilter.addFilter(SimpleQueryFilter.makeColToValFilter("GROUPENGINEPERMISSION__TYPE", "==",
						user.getAccessToken(login).getUserGroupType()));
				andFilter.addFilter(SimpleQueryFilter.makeColToValFilter("GROUPENGINEPERMISSION__ID", "==",
						user.getAccessToken(login).getUserGroups()));
				orFilter.addFilter(andFilter);
			}
			if (!orFilter.isEmpty()) {
				subQsGroup.addExplicitFilter(orFilter);
				qs1.addExplicitFilter(SimpleQueryFilter.makeColToSubQuery("ENGINE__ENGINEID", "!=", subQsGroup));
			}
		}
		// filters
		if (engineFilters != null && !engineFilters.isEmpty()) {
			qs1.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ENGINEID", "==", engineFilters));
		}
		if (engineTypes != null && !engineTypes.isEmpty()) {
			qs1.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ENGINETYPE", "==", engineTypes));
		}

		// optional word filter on the engine name
		if (hasSearchTerm) {
			OrQueryFilter searchFilter = new OrQueryFilter();
			searchFilter.addFilter(securityDb.getQueryUtil().getSearchRegexFilter("ENGINE__ENGINENAME", searchTerm));
			searchFilter.addFilter(securityDb.getQueryUtil().getSearchRegexFilter("ENGINE__ENGINEID", searchTerm));
			searchFilter
					.addFilter(securityDb.getQueryUtil().getSearchRegexFilter("ENGINE__ENGINEDISPLAYNAME", searchTerm));
			qs1.addExplicitFilter(searchFilter);
		}
		// filtering by enginemeta key-value pairs (i.e. <tag>:value): for each pair,
		// add in-filter against engineids from subquery
		if (engineMetadataFilter != null && !engineMetadataFilter.isEmpty()) {
			for (String k : engineMetadataFilter.keySet()) {
				SelectQueryStruct subQs = new SelectQueryStruct();
				subQs.addSelector(new QueryColumnSelector("ENGINEMETA__ENGINEID"));
				subQs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEMETA__METAKEY", "==", k));
				subQs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEMETA__METAVALUE", "==",
						engineMetadataFilter.get(k)));
				qs1.addExplicitFilter(SimpleQueryFilter.makeColToSubQuery("ENGINE__ENGINEID", "==", subQs));
			}
		}

		// add the sort
		qs1.addOrderBy(new QueryColumnOrderBySelector("low_engine_name"));

		Long long_limit = -1L;
		Long long_offset = -1L;
		if (limit != null && !limit.trim().isEmpty()) {
			long_limit = ((Number) Double.parseDouble(limit)).longValue();
		}
		if (offset != null && !offset.trim().isEmpty()) {
			long_offset = ((Number) Double.parseDouble(offset)).longValue();
		}
		qs1.setLimit(long_limit);
		qs1.setOffSet(long_offset);

		return QueryExecutionUtility.flushRsToMap(securityDb, qs1);
	}

	/**
	 * Get user engines + global engines
	 * 
	 * @param userId
	 * @return
	 */
	public static List<String> getFullUserEngineIds(User user) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINEPERMISSION__ENGINEID"));
		qs.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINEPERMISSION__USERID", "==", getUserFiltersQs(user)));
		List<String> databaseList = QueryExecutionUtility.flushToListString(securityDb, qs);
		databaseList.addAll(SecurityEngineUtils.getGlobalEngineIds());
		return databaseList.stream().distinct().sorted().collect(Collectors.toList());
	}

	/**
	 * Get the visual user databases
	 * 
	 * @param userId
	 * @return
	 */
	public static List<String> getVisibleUserDatabaseIds(User user) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		Collection<String> userIds = getUserFiltersQs(user);

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEID"));
		{
			OrQueryFilter orFilter = new OrQueryFilter();
			orFilter.addFilter(
					SimpleQueryFilter.makeColToValFilter("ENGINE__GLOBAL", "==", true, PixelDataType.BOOLEAN));
			orFilter.addFilter(SimpleQueryFilter.makeColToValFilter("ENGINEPERMISSION__USERID", "==", userIds));
			qs.addExplicitFilter(orFilter);
		}
		// remove the engines the user has hidden
		qs.addExplicitFilter(SimpleQueryFilter.makeColToSubQuery("ENGINE__ENGINEID", "!=",
				getHiddenIdsQs(user, "USERENGINEPREFERENCE", "ENGINEID")));
		// joins
		qs.addRelation("ENGINE", "ENGINEPERMISSION", "left.outer.join");
		return QueryExecutionUtility.flushToListString(securityDb, qs);
	}

	/**
	 * Reads the settings of the engine metadata keys: whether each takes one value
	 * or many, its display order and options, and its default values. Reads only
	 * the given key when one is passed, or every key otherwise.
	 * 
	 * @param metakey
	 * @return
	 */
	public static List<Map<String, Object>> getMetakeyOptions(String metakey) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINEMETAKEYS__METAKEY", "metakey"));
		qs.addSelector(new QueryColumnSelector("ENGINEMETAKEYS__SINGLEMULTI", "single_multi"));
		qs.addSelector(new QueryColumnSelector("ENGINEMETAKEYS__DISPLAYORDER", "display_order"));
		qs.addSelector(new QueryColumnSelector("ENGINEMETAKEYS__DISPLAYOPTIONS", "display_options"));
		qs.addSelector(new QueryColumnSelector("ENGINEMETAKEYS__DEFAULTVALUES", "display_values"));
		if (metakey != null && !metakey.isEmpty()) {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEMETAKEYS__METAKEY", "==", metakey));
		}
		return QueryExecutionUtility.flushRsToMap(securityDb, qs);
	}

	/**
	 * Replaces every engine metadata key definition with the given ones, each with
	 * its key, single or multi setting, display order and display options, in one
	 * write.
	 * 
	 * @param metaoptions
	 * @return
	 */
	public static boolean updateMetakeyOptions(List<Map<String, Object>> metaoptions) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		String tableName = "ENGINEMETAKEYS";
		try {
			return QueryExecutionUtility.write(securityDb, connection -> {
				// first truncate table clean
				String truncateSql = "DELETE FROM " + tableName + " WHERE 1=1";
				QueryExecutionUtility.executeUpdate(connection, truncateSql, QueryExecutionUtility.NO_PARAMETERS);
				try (PreparedStatement insertPs = connection.prepareStatement(securityDb.getQueryUtil()
						.createInsertPreparedStatementString(tableName, new String[] { Constants.METAKEY,
								Constants.SINGLE_MULTI, Constants.DISPLAY_ORDER, Constants.DISPLAY_OPTIONS }))) {
					// then insert latest options
					for (int i = 0; i < metaoptions.size(); i++) {
						insertPs.setString(1, (String) metaoptions.get(i).get("metakey"));
						insertPs.setString(2, (String) metaoptions.get(i).get("singlemulti"));
						insertPs.setInt(3, ((Number) metaoptions.get(i).get("order")).intValue());
						insertPs.setString(4, (String) metaoptions.get(i).get("displayoptions"));
						insertPs.addBatch();
					}
					insertPs.executeBatch();

					return true;
				}
			});
		} catch (Exception e) {
			classLogger.error("Failed to update metadata key options", e);
		}
		return false;
	}

	/**
	 * Replaces a person's engine permissions with the ones given, such as their
	 * login provider's, in one transaction: the engines not yet registered are
	 * added, the person's grants are replaced, and the model usage limits on the
	 * grants no longer given are removed, all saved together or not at all. The
	 * limits on the grants kept stay as they are.
	 *
	 * @param user              the person
	 * @param enginePermissions each engine's {@code engineId}, {@code engineName},
	 *                          {@code engineType}, {@code engineSubType} and
	 *                          {@code permission}
	 * @return the permissions whose engines were added
	 * @throws IllegalArgumentException when the permissions cannot be saved;
	 *                                  nothing is saved, the engines it would add
	 *                                  included
	 */
	public static List<Map<String, Object>> updateEngineUserPermissions(User user,
			List<Map<String, Object>> enginePermissions) throws Exception {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		AbstractSqlQueryUtil queryUtil = securityDb.getQueryUtil();
		List<Map<String, Object>> permissions = enginePermissions == null ? List.of() : enginePermissions;
		String userId = User.getPrimaryUserIdAndTypePair(user).getValue0();
		String deleteQuery = "DELETE FROM ENGINEPERMISSION WHERE USERID = ?";
		String insertQuery = "INSERT INTO ENGINEPERMISSION (USERID, PERMISSION, ENGINEID, DATEADDED) "
				+ "VALUES (?, ?, ?, ?)";
		Timestamp currentTimestamp = Utility.getCurrentSqlTimestampUTC();
		List<Map<String, Object>> newEngines;
		try {
			newEngines = QueryExecutionUtility.write(securityDb, connection -> {
				// engines not yet registered are added in the same transaction, so a grant
				// is never saved without its engine
				List<Map<String, Object>> added = new ArrayList<>();
				for (Map<String, Object> permissionMap : permissions) {
					String engineId = (String) permissionMap.get("engineId");
					if (QueryExecutionUtility.queryOne(connection, "SELECT ENGINEID FROM ENGINE WHERE ENGINEID=?",
							ps -> ps.setString(1, engineId), rs -> rs.getString(1)) == null) {
						added.add(permissionMap);
						String engineName = (String) permissionMap.get("engineName");
						insertEngine(connection, queryUtil, engineId, engineName, engineName,
								(IEngine.CATALOG_TYPE) permissionMap.get("engineType"),
								(String) permissionMap.get("engineSubType"), "", false, null);
					}
				}

				QueryExecutionUtility.executeUpdate(connection, deleteQuery, ps -> ps.setString(1, userId));
				if (!permissions.isEmpty()) {
					QueryExecutionUtility.executeBatch(connection, insertQuery, permissions, (ps, permissionMap) -> {
						String permission = (String) permissionMap.get("permission");
						ps.setString(1, userId);
						ps.setInt(2, AccessPermissionEnum.getIdByPermission(permission));
						ps.setString(3, (String) permissionMap.get("engineId"));
						ps.setTimestamp(4, currentTimestamp);
					});
				}
				SecurityModelUsageLimitUtils.deleteDirectLimitsWithoutGrant(connection, userId);
				return added;
			});
		} catch (Exception e) {
			classLogger.error("Failed to update engine user permissions", e);
			throw new IllegalArgumentException("An error occurred while updating the user engine permissions in db ");
		}
		// the grants decide which limits apply, so the cached limits are read again
		SecurityModelUsageLimitUtils.markChanged();
		return newEngines;
	}

	public static void updateEngineToolApp(String engineId, String projectId) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		try {
			QueryExecutionUtility.write(securityDb, connection -> {
				String query = "UPDATE ENGINE SET TOOL_APP = ? WHERE ENGINEID = ?";

				QueryExecutionUtility.executeUpdate(connection, query, ps -> {
					ps.setString(1, projectId);
					ps.setString(2, engineId);
				});
				return null;
			});
		} catch (RuntimeException e) {
			throw e;
		} catch (Exception e) {
			classLogger.error("Failed to update engine legacy tool_app alias", e);
		}
	}

	/**
	 * Get userDetails by using user's engine access request
	 * 
	 * @param requestId
	 * @return List of user details
	 */
	public static List<Map<String, Object>> getUserDetailsFromEngineAccessRequest(String engineRequestId) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINEACCESSREQUEST__REQUEST_USERID", "userId"));
		qs.addSelector(new QueryColumnSelector("ENGINEACCESSREQUEST__REQUEST_TYPE", "type"));
		qs.addSelector(new QueryColumnSelector("PERMISSION__NAME", "permission"));
		qs.addSelector(new QueryColumnSelector("ENGINEACCESSREQUEST__PERMISSION", "permission"));
		qs.addRelation("ENGINEACCESSREQUEST__PERMISSION", "PERMISSION__ID", "inner.join");
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEACCESSREQUEST__ID", "==", engineRequestId));
		return QueryExecutionUtility.flushRsToMap(securityDb, qs);
	}

	/**
	 * Get all authors for a specific engine (for engine-related notifications)
	 * 
	 * @param engineId
	 * @return
	 */
	public static List<Map<String, Object>> getEngineAuthors(String engineId) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("SMSS_USER__ID", "userId"));
		qs.addSelector(new QueryColumnSelector("SMSS_USER__TYPE", "userType"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEPERMISSION__ENGINEID", "==", engineId));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINEPERMISSION__PERMISSION", "==", 1));
		qs.addRelation("SMSS_USER", "ENGINEPERMISSION", "inner.join");

		return QueryExecutionUtility.flushRsToMap(securityDb, qs);
	}

	/**
	 * Maps each of the given engine ids to its engine name. Ids with no engine are
	 * left out, and no ids give an empty map.
	 * 
	 * @param engineIds
	 * @return
	 */
	public static Map<String, String> getEngineNamesByIds(Collection<String> engineIds) {
		IRDBMSEngine securityDb = SystemEngineRegistry.getSecurityDb();
		Map<String, String> engineMap = new HashMap<>();
		if (engineIds == null || engineIds.isEmpty()) {
			return engineMap;
		}

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINEID", "id"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME", "name"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ENGINEID", "==", engineIds));

		List<Map<String, Object>> resultList = QueryExecutionUtility.flushRsToMap(securityDb, qs);
		if (resultList != null) {
			for (Map<String, Object> row : resultList) {
				engineMap.put(String.valueOf(row.get("id")), String.valueOf(row.get("name")));
			}
		}
		return engineMap;
	}

	/**
	 * What an engine is, as the audit trail names it: its kind, such as a model.
	 *
	 * @param engineId the engine
	 * @return its target type, or {@link AuditTargetType#ENGINE} when its kind is
	 *         not known or the catalog does not have it
	 */
	static AuditTargetType getAuditTargetType(String engineId) {
		try {
			return AuditTargetType.ofEngine(getEngineType(engineId));
		} catch (RuntimeException e) {
			// an engine the catalog does not have is recorded as an engine, so the audit
			// never stops the change
			return AuditTargetType.ENGINE;
		}
	}

}