SearchServiceImpl.java

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

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.TimeUnit;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import lombok.extern.slf4j.Slf4j;
import org.antlr.v4.runtime.CharStreams;
import org.antlr.v4.runtime.CommonTokenStream;
import org.antlr.v4.runtime.TokenStream;
import org.antlr.v4.runtime.TokenStreamRewriter;
import org.antlr.v4.runtime.tree.ErrorNode;
import org.antlr.v4.runtime.tree.ParseTreeWalker;
import org.apache.commons.lang3.StringUtils;
import org.gringlobal.api.exception.InvalidApiUsageException;
import org.gringlobal.compatibility.parser.SeQueryLexer;
import org.gringlobal.compatibility.parser.SeQueryParser;
import org.gringlobal.compatibility.parser.SeQueryParser.CriterionContext;
import org.gringlobal.compatibility.parser.SeQueryParserBaseListener;
import org.gringlobal.compatibility.parser.SeQueryParserBaseVisitor;
import org.gringlobal.compatibility.service.DataviewService;
import org.gringlobal.compatibility.service.SearchService;
import org.gringlobal.model.QAccession;
import org.gringlobal.model.QInventory;
import org.gringlobal.soap.Datatable;
import org.gringlobal.soap.Datatable.HasChanges;
import org.gringlobal.soap.model.Search;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.google.common.cache.Cache;
import com.google.common.cache.CacheBuilder;
import com.querydsl.core.types.ExpressionUtils;
import com.querydsl.core.types.Predicate;
import com.querydsl.jpa.impl.JPAQuery;
import com.querydsl.jpa.impl.JPAQueryFactory;

@Service
@Transactional(readOnly = true)
@Slf4j
public class SearchServiceImpl implements SearchService {

	public static final Pattern QBE = Pattern.compile(
		"(AND|OR)?\\s*@([^\\.]+)\\.([^\\s=<>]+)\\s*(?:([<>=]+|LIKE|IN)\\s*('([^']+)'|\"([^\"])\"|(\\d+(\\.\\d+)?)|\\(([^\\)]*)\\))|(IS NOT NULL|IS NULL))\\s*",
		Pattern.CASE_INSENSITIVE);

	public static void main(String[] args) {
//		final Matcher matcher = QBE.matcher("@geography.country_code = 'MEX' AND @geography.adm1_type_code IS NULL");
//		final Matcher matcher = QBE.matcher("@accession.accession_id IN ()");
//
//		while (matcher.find()) {
//			LOG.debug("Match: {}", matcher.group());
//			for (var i = 0; i <= matcher.groupCount(); i++) {
//				LOG.debug("group[{}]: {}", i, matcher.group(i));
//			}
//		}
		var resolverName = "foobar";
		var operator = "AND";
		var params = new ArrayList<>();
		Set<String> joinTables = new HashSet<>();
		joinTables.add(resolverName); // this is our main table

//		var query = "@accession.owned_by IN (48,830,831,832,833,854) AND ((@web_order_request_item.status_code='NEW'))";
		var query = "@accession.owned_by IN (  ) AND ((@web_order_request_item.status_code='NEW'))";
//		var query = "@geography.country_code = 'MEX' AND @geography.adm1_type_code IS NULL";

		// lets analyze the query with ANTLR parser
		log.debug("QUERY: {}", query);
		var stream = CharStreams.fromString(query);
		var lexer = new SeQueryLexer(stream);
		var tokens = new CommonTokenStream(lexer);
		SeQueryParser parser = new SeQueryParser(tokens);
		parser.setBuildParseTree(true);
		var tree = parser.mixedQuery();
		ParseTreeWalker walker = new ParseTreeWalker(); // create standard walker

		// Use a listener to modify the formated criteria into usable SQL
		// RewritingListener listen = new RewritingListener(_sd, _dm, sjm, tokens, pKeyType, defaultOperator);
		RewritingListener listen = new RewritingListener(tokens, resolverName, operator, params, joinTables);
		walker.walk(listen, tree); // initiate walk of tree with listener

		// If a formatted block of criteria was seen, extract the rewritten SQL 
		var where = listen.rewriter.getText();
		log.debug("Parsed SQL: {}", where);
		for (var param : params) {
			log.debug("? = {}", param);
		}
		log.debug("formattedPresent: {}", listen.formattedPresent);
		log.debug("freeformPresent: {}", listen.freeformPresent);
	}

	@Autowired
	private DataviewService dataviewService;

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Autowired
	private JPAQueryFactory jpaQueryFactory;

	@PersistenceContext
	private EntityManager entityManager;

	@Value("${db.dataviewSql}")
	private String sqlEngine;

	@Override
	@PreAuthorize("isAuthenticated()")
	public Datatable search(Search request) throws SQLException {

		String operator = "AND";
		if (request.getOptions()!= null && request.getOptions().matches("OrMultipleLines\\s*=\\s*true")) {
			operator = "LIST";
		} else {
			operator = request.isAndTermsTogether() ? "AND" : "OR";
		}

		log.debug("Search of {} operator={} query: {}", request.getResolverName(), operator, request.getQuery());
		final Set<Long> pKeys = findPKeys(request.getResolverName(), request.getQuery(), operator, request.getOffset() + request.getLimit());

		{
			final Datatable dtIds = new Datatable("SearchResult", List.of(
				new Datatable.Column("ID", Long.class)
			));
			pKeys.forEach(pk -> {
				dtIds.addRow(HasChanges.original, pk);
			});
			return dtIds;
		}
	}

	private final Cache<String, TableMatrix> cacheSysMatrix = CacheBuilder.newBuilder().maximumSize(5).expireAfterWrite(10, TimeUnit.MINUTES).build();

	private TableMatrix getTableMatrix() {
		try {
			return cacheSysMatrix.get("sys_matrix_input", () -> {
				log.info("Getting sys table matrix");

				Datatable dataTable = dataviewService.getData("sys_matrix_input", Map.of(), 0, 1000, null);
				log.debug("Got sys_matrix_input:\n {}", dataTable);
				return TableMatrix.from(dataTable.getRows());
			});
		} catch (ExecutionException e1) {
			log.error("Failed to load sys_matrix_input", e1);
			return null;
		}
	}

	private Set<Long> findPKeys(final String resolverName, String query, final String operator, final int limit) {
		Set<Long> pKeys = new HashSet<>();

		log.debug("findPKeys resolver={} operator={} query={} limit={}", resolverName, operator, query, limit);

		if (operator.equals("LIST")) {
			pKeys.addAll(resolveItemList(resolverName, query, "ALL", limit));
		}

		query = extractFormattedFromSearch(query);
		if (StringUtils.isBlank(query)) {
			log.debug("No more items in query. Done");
			return pKeys;
		}

		log.debug("findPKeys of {} operator={} query: {}", resolverName, operator, query);

		Set<String> joinTables = new HashSet<>();
		joinTables.add(resolverName); // this is our main table
		TableMatrix tableMatrix = getTableMatrix();

		String where = "";
		List<Object> params = new ArrayList<>();

		// lets analyze the query with ANTLR parser
		var stream = CharStreams.fromString(query);
		var lexer = new SeQueryLexer(stream);
		var tokens = new CommonTokenStream(lexer);
		SeQueryParser parser = new SeQueryParser(tokens);
		parser.setBuildParseTree(true);
		var tree = parser.mixedQuery();
		ParseTreeWalker walker = new ParseTreeWalker(); // create standard walker

		// Use a listener to modify the formated criteria into usable SQL
		// RewritingListener listen = new RewritingListener(_sd, _dm, sjm, tokens, pKeyType, defaultOperator);
		RewritingListener listen = new RewritingListener(tokens, resolverName, operator, params, joinTables);
		walker.walk(listen, tree); // initiate walk of tree with listener

		// If a formatted block of criteria was seen, extract the rewritten SQL 
		if (listen.formattedPresent) {
			where = listen.rewriter.getText();
			log.debug("Parsed SQL: {}", where);

//            // Dump the prepared SQL as an error message if a keyword is used at beginning of query
//            if (searchText.ToUpper().Replace(" ", "").StartsWith("--DUMPSQL")) {
//                throw Library.CreateBusinessException(getDisplayMember("SEDumpSql", "The Search Engine prepared this SQL statement:\r\n\r\n{0}", sql));
//            }
//
			// double check that neither a single quote or semicolon got into the SQL
			if ((where.indexOf("'") >= 0) || (where.indexOf(";") >= 0)) {
				throw new InvalidApiUsageException("Found illegal char in prepared SQL");
//                throw Library.CreateBusinessException(getDisplayMember("SEbadChar", "Search Engine error: found illegal char in prepared SQL.\r\n" + sql));
			}
//            //Logger.LogText("ResolveQuery initial sql: \r\n" + sql); // KDebug
//
//            // If there are no freeform words to process, execute the SQL ane return the results
//            if (!listen.freeformPresent) return addSelectPKeys(_dm, pkeys, pKeyType, sql, listen.dps);
		}

		// if a "Google-like" freeform query block was seen process that seperately and combine if necessary
		if (listen.freeformPresent) {
			log.debug("Ignoring freeform part of query: {}", query);

//            // Use a parse tree visitor to process freeform tokens and get results
//            FreeformVisitor free = new FreeformVisitor(_sd, _dm, sjm, pKeyType, defaultOperator);
//            List<int> freeKeys = free.Visit(tree);
//            //Logger.LogText("ResolveQuery freeKeys.Count: " + freeKeys.Count); // KDebug
//            if (freeKeys.Count < 1 || !listen.formattedPresent) return freeKeys;
//
//            // add freeforms results to the SQL if not too many IDs, this speeds criteria checks that are too general
//            if (freeKeys.Count < 1000) {
//                sql += " INTERSECT SELECT " + pKeyType + " FROM " + pKeyType.Substring(0, pKeyType.Length - 3)
//                    + " WHERE " + pKeyType + " IN (" + idListToString(freeKeys) + ")";
//                //Logger.LogText("ResolveQuery extra sql: " + '"' + sql + '"'); // KDebug
//            }
//
//            //execute SQL and intersect with freeform results
//            pkeys = addSelectPKeys(_dm, pkeys, pKeyType, sql, listen.dps);
//            //Logger.LogText("ResolveQuery SQL pkeys.Count: " + pkeys.Count); // KDebug
//            pkeys = pkeys.Intersect(freeKeys).ToList();
//            //Logger.LogText("ResolveQuery both pkeys.Count: " + pkeys.Count); // KDebug
		}

//		final Matcher matcher = QBE.matcher(query);
//
//		while (matcher.find()) {
//			LOG.debug("Q: {}", matcher.group());
//			LOG.debug("Q2: {}", matcher.group(2));
//
//			final String tableName = matcher.group(2);
//			joinTables.add(tableName);
//		}

//		// Do.
//		matcher.reset();
//		while (matcher.find()) {
//			LOG.debug("Q: {}", matcher.group());
//			for (var i = 0; i <= matcher.groupCount(); i++) {
//				LOG.debug("Q{}: {}", i, matcher.group(i));
//			}
//
//			final String andOr = matcher.group(1);
//			final String tableName = matcher.group(2);
//			final String fieldName = matcher.group(3);
//			String op = matcher.group(4);
//			if (op == null) {
//				op = matcher.group(11);
//			}
//			var value = matcher.group(6);
//
//			if (value == null) {
//				value = matcher.group(7);
//			}
//			Double nvalue = null;
//			try {
//				nvalue = Double.parseDouble(matcher.group(8));
//			} catch (final Throwable e) {
//			}
//
//			if (StringUtils.isNotBlank(andOr)) {
//				where.append(" ").append(andOr).append(" ");
//			}
//
//			if (op.equals("=") || op.endsWith(">") || op.equals(">=") || op.equals("<") || op.equals("=<")) {
//				where.append(tableName).append(".").append(fieldName);
//				where.append(" ").append(op).append(" ?");
//				params.add(nvalue == null ? value : nvalue);
//			} else if (op.equalsIgnoreCase("LIKE")) {
//				where.append(tableName).append(".").append(fieldName);
//				where.append(" like ?");
//				params.add(value);
//			} else if (op.equalsIgnoreCase("IS NULL")) {
//				where.append(tableName).append(".").append(fieldName);
//				where.append(" is null");
//			} else if (op.equalsIgnoreCase("IS NOT NULL")) {
//				where.append(tableName).append(".").append(fieldName);
//				where.append(" is not null");
//			} else if (op.equalsIgnoreCase("IN")) {
//				String inValues = StringUtils.trimToNull(matcher.group(10));
//				if (inValues == null) {
//					where.append("1=2"); // Blank IN must result in no matches
//				} else {
//					where.append(tableName).append(".").append(fieldName);
//					where.append(" in (").append(inValues).append(")");
//				}
//
//			} else {
//				LOG.debug("Unhandled search engine query operation {}", op);
//				continue;
//			}
//		}

		var fromClause = tableMatrix.generateFromClause(resolverName, "INNER", joinTables);
		log.trace("\n\nSELECT {}_id {}", resolverName, fromClause);

		log.trace("\n\nWhere:\n{}", where);

		var sql = "SELECT DISTINCT " + resolverName + "." + resolverName + "_id" + fromClause
				+ (StringUtils.trimToNull(where) == null ? "" : " WHERE " + where)
				+ " ORDER BY " + resolverName + "_id";
		log.debug("Full statement: {}", sql);

		if (limit > 0) {
			// Apply limit (if > 0)
			sql = DataviewServiceImpl.applyLimit(sqlEngine, sql, limit, 0);
		}

		pKeys = executeSqlForKeys(sql, params);

		if (limit > 0 && pKeys.size() > limit) {
			log.debug("Returning {} of {} pKeys found", limit, pKeys.size());
			return new HashSet<>(new ArrayList<>(pKeys).subList(0, limit));
		}
		return pKeys;
	}

	private Set<Long> resolveItemList(final String resolverName, final String query, final String operator, final int limit) {
		final Set<Long> pKeys = new HashSet<>();
		log.debug("resolveItemList of {} operator={} query: {}", resolverName, operator, query);

		if (StringUtils.isBlank(query)) {
			return pKeys;
		}

		// Original code from .Net follows

		// // if (searchType != "PN")
		// long number;
		// switch (tokens.length) {
		// case 1: {
		// try {
		// // Assume this is an order_request_id
		// number = Long.parseLong(tokens[0]);
		//
//				/*@formatter:off*/
//				// if (int.TryParse(tokens[0], out number) && searchType == "ALL") {
//				//    sql = "SELECT order_request_id FROM order_request WHERE order_request.order_request_id = :order_number";
//				//    orids = addSelectPKeys(dm, orids, "order_request_id", sql, new DataParameters(":order_number", number));
//				// }
//				/*@formatter:on*/
		// orderRequestIds.add(number);
		//
		// } catch (NumberFormatException e) {
		// // Try as a one part accession or inventory ID
		//
		// // int beforeCount = pKeys.size();
//				/*@formatter:off*/
//				// sql = "SELECT accession_id FROM accession WHERE accession.accession_number_part1= :prefix AND accession.accession_number_part2 IS NULL";
//				// acids = addSelectPKeys(dm, acids, "accession_id", sql, new DataParameters(":prefix", tokens[0]));
//
//				// if (acids.Count == beforeCount) {
//				//    sql = "SELECT inventory_id FROM inventory WHERE inventory.inventory_number_part1= :prefix AND inventory.inventory_number_part2 IS NULL";
//				//    ivids = addSelectPKeys(dm, ivids, "inventory_id", sql, new DataParameters(":prefix", tokens[0]));
//				// }
//				/*@formatter:on*/
		// numberPart1.add(tokens[0]);
		// }
		// break;
		// }
		// case 2:
		// // Assume this is a xxx_part1 and xxx_part2 item...
		// number = 0;
		//
//			/*@formatter:off*/
//			// if (!int.TryParse(tokens[0], out number) && int.TryParse(tokens[1], out number)) {
//			// }
//			/*@formatter:on*/
		// try {
		// number = Long.parseLong(tokens[1]);
		// // int beforeCount = acids.Count;
		// // sql = "SELECT accession_id FROM accession WHERE
		// accession.accession_number_part1= :prefix AND
		// accession.accession_number_part2= :number";
		// // acids = addSelectPKeys(dm, acids, "accession_id", sql, new
		// DataParameters(":prefix", tokens[0], ":number", number));
		// //
		// // if (acids.Count == beforeCount) {
		// // sql = "SELECT inventory_id FROM inventory WHERE
		// inventory.inventory_number_part1= :prefix AND
		// inventory.inventory_number_part2= :number";
		// // ivids = addSelectPKeys(dm, ivids, "inventory_id", sql, new
		// DataParameters(":prefix", tokens[0], ":number", number));
		// // }
		// numberPart2.add(new Object[] { tokens[0], number });
		// } catch (NumberFormatException e) {
		//
		// }
		// break;
		// case 3:
		// // Assume this is a xxx_part1, xxx_part2 and xxx_part3 item...
		// number = 0;
//			/*@formatter:off*/
//			// if (!int.TryParse(tokens[0], out number) && int.TryParse(tokens[1], out number)) {
//			// }
//			/*@formatter:on*/
		// try {
		// number = Long.parseLong(tokens[1]);
		// // sql = "SELECT accession_id FROM accession WHERE
		// accession.accession_number_part1= :prefix AND
		// accession.accession_number_part2= :number AND
		// accession.accession_number_part3= :suffix";
		// // acids = addSelectPKeys(dm, acids, "accession_id", sql, new
		// DataParameters(":prefix", tokens[0], ":number", number, ":suffix",
		// tokens[2]));
		// //
		// // sql = "SELECT inventory_id FROM inventory WHERE
		// inventory.inventory_number_part1= :prefix AND
		// inventory.inventory_number_part2= :number AND
		// inventory.inventory_number_part3= :suffix";
		// // ivids = addSelectPKeys(dm, ivids, "inventory_id", sql, new
		// DataParameters(":prefix", tokens[0], ":number", number, ":suffix",
		// tokens[2]));
		// numberPart3.add(new Object[] { tokens[0], number, tokens[2] });
		// } catch (NumberFormatException e) {
		//
		// }
		// break;
		// case 4:
		// // Assume this is a xxx_part1, xxx_part2, xxx_part3 and xxx_part4 item (which
		// // can only be an inventory)...
		// number = 0;
//			/*@formatter:off*/
//			// if (!int.TryParse(tokens[0], out number) && int.TryParse(tokens[1], out number)) {
//			//    sql = "SELECT inventory_id FROM inventory WHERE inventory.inventory_number_part1 = :prefix AND inventory.inventory_number_part2 = :number"
//			//        + " AND inventory.inventory_number_part3 = :suffix AND inventory.form_type_code = :form";
//			//    ivids = addSelectPKeys(dm, ivids, "inventory_id", sql, new DataParameters(":prefix", tokens[0], ":number", number, ":suffix", tokens[2], ":form", tokens[3]));
//			// }
//			/*@formatter:on*/
		// numberPart4.add(new Object[] { tokens[0], number, tokens[2], tokens[3] });
		// break;
		// default:
		// // Ignore this item...
		// break;
		// }
//		/*@formatter:off*/
//		// int postCaseCount = acids.Count + ivids.Count + orids.Count;
//		// if (postCaseCount == preCaseCount && searchType != "ID") {
//		//    // If nothing else worked maybe the entire line is a plant name
//		//    sql = "SELECT accession_inv_name_id FROM accession_inv_name WHERE plant_name = :plant_name_identifier";
//		//    pnids = addSelectPKeys(dm, pnids, "accession_inv_name_id", sql, new DataParameters(":plant_name_identifier", item));
//		// }
//		/*@formatter:on*/
		// }

		final Set<Long> orderRequestIds = new HashSet<>();
		final Set<String> numberPart1 = new HashSet<>();
		final List<Object[]> numberPart2 = new ArrayList<>();
		final List<Object[]> numberPart3 = new ArrayList<>();
		final List<Object[]> numberPart4 = new ArrayList<>();

		for (final String item : query.split("\n")) {
			if (item.length() == 0) {
				continue;
			}
			final String[] tokens = item.strip().split("\\s+");
			log.debug("Query item: {} --> tokens: {}", item, tokens);

			if (tokens.length == 1) {
				try {
					// Assume this is an order_request_id
					final long number = Long.parseLong(tokens[0]);
					orderRequestIds.add(number);

				} catch (final NumberFormatException e) {
					// Try as a one part accession or inventory ID
					numberPart1.add(tokens[0]);
				}
			} else if (tokens.length > 1) {
				try {
					// Assume this is a xxx_part1, xxx_part2 item...
					final long number = Long.parseLong(tokens[1]);

					if (tokens.length == 2) {
						numberPart2.add(new Object[] { tokens[0], number });
					} else if (tokens.length == 3) {
						numberPart3.add(new Object[] { tokens[0], number, tokens[2] });
					} else {
						numberPart4.add(new Object[] { tokens[0], number, tokens[2], tokens[3] });
					}

				} catch (final NumberFormatException e) {
					log.warn("Can't parse long: {}", tokens[1]);
				}
			}
		}

		final Set<Long> accessionIds = new HashSet<>();
		final Set<Long> inventoryIds = new HashSet<>();

		if (orderRequestIds.size() > 0) {
			// jpaQueryFactory.from(QOrderRequest.orderRequest).select(QOrderRequest.orderRequest.id).where(QOrderRequest.orderRequest.id.in(orderRequestIds));
		}
		if (numberPart1.size() > 0) {
			{
				// // sql = "SELECT accession_id FROM accession WHERE
				// accession.accession_number_part1= :prefix AND
				// accession.accession_number_part2 IS NULL";
				// // acids = addSelectPKeys(dm, acids, "accession_id", sql, new
				// DataParameters(":prefix", tokens[0]));

				final JPAQuery<Long> q = jpaQueryFactory.from(QAccession.accession).select(QAccession.accession.id).distinct();
				// Filters
				q.where(QAccession.accession.accessionNumberPart2.isNull().and(QAccession.accession.accessionNumberPart1.in(numberPart1)));
				accessionIds.addAll(q.fetch());
			}
			{
				// // sql = "SELECT inventory_id FROM inventory WHERE
				// inventory.inventory_number_part1= :prefix AND
				// inventory.inventory_number_part2= :number";
				// // ivids = addSelectPKeys(dm, ivids, "inventory_id", sql, new
				// DataParameters(":prefix", tokens[0], ":number", number));

				final JPAQuery<Long> q = jpaQueryFactory.from(QInventory.inventory).select(QInventory.inventory.id).distinct();
				// Filters
				q.where(QInventory.inventory.inventoryNumberPart2.isNull().and(QInventory.inventory.inventoryNumberPart1.in(numberPart1)));
				inventoryIds.addAll(q.fetch());
			}
		}

		if (numberPart2.size() > 0) {
			{
				// // sql = "SELECT accession_id FROM accession WHERE
				// accession.accession_number_part1= :prefix AND
				// accession.accession_number_part2= :number";
				final JPAQuery<Long> q = jpaQueryFactory.from(QAccession.accession).select(QAccession.accession.id).distinct();
				// Filters
				final List<Predicate> exprs = new ArrayList<>();
				for (final Object[] token : numberPart2) {
					exprs.add(QAccession.accession.accessionNumberPart1.eq((String) token[0]).and(QAccession.accession.accessionNumberPart2.eq((Long) token[1])));
				}
				q.where(ExpressionUtils.anyOf(exprs));
				accessionIds.addAll(q.fetch());
			}
			{
				// // sql = "SELECT inventory_id FROM inventory WHERE
				// inventory.inventory_number_part1= :prefix AND
				// inventory.inventory_number_part2= :number";
				final JPAQuery<Long> q = jpaQueryFactory.from(QInventory.inventory).select(QInventory.inventory.id).distinct();
				// Filters
				final List<Predicate> exprs = new ArrayList<>();
				for (final Object[] token : numberPart2) {
					exprs.add(QInventory.inventory.inventoryNumberPart1.eq((String) token[0]).and(QInventory.inventory.inventoryNumberPart2.eq((Long) token[1])));
				}
				q.where(ExpressionUtils.anyOf(exprs));
				inventoryIds.addAll(q.fetch());
			}
		}

		if (numberPart3.size() > 0) {
			{
				// // sql = "SELECT accession_id FROM accession WHERE
				// accession.accession_number_part1= :prefix AND
				// accession.accession_number_part2= :number AND
				// accession.accession_number_part3= :suffix";
				final JPAQuery<Long> q = jpaQueryFactory.from(QAccession.accession).select(QAccession.accession.id).distinct();
				// Filters
				final List<Predicate> exprs = new ArrayList<>();
				for (final Object[] token : numberPart3) {
					exprs.add(QAccession.accession.accessionNumberPart1.eq((String) token[0]).and(QAccession.accession.accessionNumberPart2.eq((Long) token[1]).and(
						QAccession.accession.accessionNumberPart3.eq((String) token[2]))));
				}
				q.where(ExpressionUtils.anyOf(exprs));
				accessionIds.addAll(q.fetch());
			}
			{
				// // sql = "SELECT inventory_id FROM inventory WHERE
				// inventory.inventory_number_part1= :prefix AND
				// inventory.inventory_number_part2= :number AND
				// inventory.inventory_number_part3= :suffix";
				final JPAQuery<Long> q = jpaQueryFactory.from(QInventory.inventory).select(QInventory.inventory.id).distinct();
				// Filters
				final List<Predicate> exprs = new ArrayList<>();
				for (final Object[] token : numberPart3) {
					exprs.add(QInventory.inventory.inventoryNumberPart1.eq((String) token[0]).and(QInventory.inventory.inventoryNumberPart2.eq((Long) token[1]).and(
						QInventory.inventory.inventoryNumberPart3.eq((String) token[2]))));
				}
				q.where(ExpressionUtils.anyOf(exprs));
				inventoryIds.addAll(q.fetch());
			}
		}

		if (numberPart4.size() > 0) {
			// // sql = "SELECT inventory_id FROM inventory WHERE
			// inventory.inventory_number_part1 = :prefix AND
			// inventory.inventory_number_part2 = :number"
			// // + " AND inventory.inventory_number_part3 = :suffix AND
			// inventory.form_type_code = :form";
			final JPAQuery<Long> q = jpaQueryFactory.from(QInventory.inventory).select(QInventory.inventory.id).distinct();
			// Filters
			final List<Predicate> exprs = new ArrayList<>();
			for (final Object[] token : numberPart4) {
				exprs.add(QInventory.inventory.inventoryNumberPart1.eq((String) token[0]).and(QInventory.inventory.inventoryNumberPart2.eq((Long) token[1]).and(
					QInventory.inventory.inventoryNumberPart3.eq((String) token[2]))).and(QInventory.inventory.formTypeCode.eq((String) token[3])));
			}
			q.where(ExpressionUtils.anyOf(exprs));
			inventoryIds.addAll(q.fetch());
		}

		pKeys.addAll(convertIdList(accessionIds, "accession_id", resolverName + "_id"));
		pKeys.addAll(convertIdList(inventoryIds, "inventory_id", resolverName + "_id"));

		// pKeys.addAll(orderRequestIds);
		return pKeys;
	}

	private static String extractFormattedFromSearch(final String searchText) {
		final StringBuilder nonIdText = new StringBuilder();
		boolean endOfIds = false;

		final String[] lines = searchText.split("\\n");
		for (final String line : lines) {
			if (endOfIds || line.equals("WHERE") || line.contains("@")) {
				nonIdText.append(line).append("\n");
				endOfIds = true;
			}
		}
		return nonIdText.toString().strip();
	}

	private Set<Long> convertIdList(final Set<Long> pKeys, final String fromKeyType, final String toKeyType) {
		/*@formatter:off*/
		// private static List<int> convertIdList(SecureData sd, GrinGlobal.Core.DataManager dm, JoinMatrix sjm, List<int> pkeys, string fromKeyType, string toKeyType ) {
		//    List<int> resultPKeys = new List<int>();
		//    DataParameters dps = new DataParameters();
		//    string fromKeyTable = fromKeyType.Replace("_id", "");
		//    string toKeyTable = toKeyType.Replace("_id", "");
		//    string sql = "SELECT " + toKeyTable + "." + toKeyType;
		//    List<string> neededTables = new List<string>();
		//    neededTables.Add(fromKeyTable);
		//    sql += sjm.generateFromClause(toKeyTable, "\r\nINNER ", neededTables);
		//    sql += " WHERE " + fromKeyTable + "." + fromKeyType + " IN (" + idListToString(pkeys) + ")";
		//    //Logger.LogText("convertIdList sql: " + sql); // KDebug
		//    resultPKeys = addSelectPKeys(dm, resultPKeys, toKeyType, sql, dps);
		//    return resultPKeys;
		// }
		/*@formatter:on*/
		if (fromKeyType.equals(toKeyType) || pKeys.size() < 1) {
			// Don't bother
			return pKeys;
		}

		TableMatrix tableMatrix = getTableMatrix();

		// DataParameters dps = new DataParameters();
		String fromKeyTable = fromKeyType.replace("_id", "");
		String toKeyTable = toKeyType.replace("_id", "");

		StringBuilder sql = new StringBuilder(200);
		sql.append("SELECT ").append(toKeyTable).append(".").append(toKeyType);
		List<String> neededTables = new ArrayList<String>();
		neededTables.add(fromKeyTable);
		sql.append(tableMatrix.generateFromClause(toKeyTable, "\r\nINNER ", neededTables));
		sql.append(" WHERE ").append(fromKeyTable).append(".").append(fromKeyType).append(" IN ( ").append(pKeys.stream().map(Object::toString).collect(Collectors.joining(",")))
			.append(" )");
		sql.append(" ORDER BY ").append(toKeyTable).append(".").append(toKeyType);
		// Logger.LogText("convertIdList sql: " + sql); // KDebug

		log.debug("Generated SQL:\n{}\n", sql);

		try {
			final Set<Long> resultPKeys = executeSqlForKeys(sql.toString(), null);
			return resultPKeys;
		} catch (BadSqlGrammarException e) {
			log.error("TableMatrix incomplete: {}", e.getMessage());
			return Set.of();
		}
		//
		// if (fromKeyType.equals("accession_id")) {
		// if (toKeyType.equals("inventory_id")) {
		// return
		// jpaQueryFactory.from(QInventory.inventory).select(QInventory.inventory.id).where(QInventory.inventory.accession.id.in(pKeys)).distinct().fetch();
		// }
		// } else if (fromKeyType.equals("inventory_id")) {
		// if (toKeyType.equals("accession_id")) {
		// return
		// jpaQueryFactory.from(QInventory.inventory).select(QInventory.inventory.accession.id).where(QInventory.inventory.id.in(pKeys)).distinct().fetch();
		// }
		// } else if (fromKeyType.equals("taxonomy_genus_id")) {
		// if (toKeyType.equals("taxonomy_species_id")) {
		// return
		// jpaQueryFactory.from(QTaxonomySpecies.taxonomySpecies).select(QTaxonomySpecies.taxonomySpecies.id).where(QTaxonomySpecies.taxonomySpecies.taxonomyGenus.id
		// .in(pKeys)).distinct().fetch();
		// }
		// } else if (fromKeyType.equals("accession_source_id")) {
		// if (toKeyType.equals("accession_id")) {
		// return
		// jpaQueryFactory.from(QAccessionSource.accessionSource).select(QAccessionSource.accessionSource.accession.id).where(QAccessionSource.accessionSource.id.in(
		// pKeys)).distinct().fetch();
		// }
		// } else if (fromKeyType.equals("web_order_request_item_id")) {
		// if (toKeyType.endsWith("web_order_request_id")) {
		// return
		// jpaQueryFactory.from(QWebOrderRequestItem.webOrderRequestItem).select(QWebOrderRequestItem.webOrderRequestItem.webOrderRequest.id).where(
		// QWebOrderRequestItem.webOrderRequestItem.id.in(pKeys)).distinct().fetch();
		// }
		// }
		//
		// LOG.warn("No ID conversion from {} to {}", fromKeyType, toKeyType);
		//
		// return resultPKeys;
	}

	private Set<Long> executeSqlForKeys(String sql, List<Object> params) {
		log.info("executeSqlForKeys: {}\n\t{}", sql, params);
		final Set<Long> resultPKeys = jdbcTemplate.execute(sql, new PreparedStatementCallback<Set<Long>>() {
			@Override
			public Set<Long> doInPreparedStatement(final PreparedStatement ps) throws SQLException, DataAccessException {
				if (params != null) {
					int pos = 0;
					for (final var param : params) {
						pos++;
						log.debug("Setting ?{} <- {}", pos, param);
						ps.setObject(pos, param);
					}
				}
				try (final ResultSet rs = ps.executeQuery()) {
					Set<Long> resultPKeys = new HashSet<>();
					while (rs.next()) {
						resultPKeys.add(rs.getLong(1));
					}
					rs.close();
					return resultPKeys;
				}
			}
		});
		return resultPKeys;
	}

	static class RewritingListener extends SeQueryParserBaseListener {
//        SecureData sd;
//        GrinGlobal.Core.DataManager dm;
//        JoinMatrix sjm; // Schema Join Matrix
		public TokenStreamRewriter rewriter;
		String pKeyType;
		String defaultOperator;
//		public DataParameters dps;
		Set<String> neededTables;
		public boolean freeformPresent;
		public boolean formattedPresent;
		public String joiningOperator;
		public String joinType;
		int subSelectLevel;
		private List<Object> params;

		// public RewritingListener(SecureData sd, GrinGlobal.Core.DataManager dm,
		// JoinMatrix sjm, ITokenStream tokens, string pKeyType, string defaultOperator)
		// {
		public RewritingListener(TokenStream tokens, String pKeyType, String defaultOperator, List<Object> params, Set<String> joinTables) {
			// this.sd = sd;
			// this.dm = dm;
			// this.sjm = sjm;
			rewriter = new TokenStreamRewriter(tokens);
			this.pKeyType = pKeyType;
			this.defaultOperator = defaultOperator;
			freeformPresent = false;
			formattedPresent = false;
			joiningOperator = "DEFAULT";
			this.params = params;
			this.neededTables = joinTables;
		}

		@Override
		public void exitFreeUnit(SeQueryParser.FreeUnitContext ctx) {
			freeformPresent = true;
		}

		@Override
		public void enterFormattedBlock(SeQueryParser.FormattedBlockContext ctx) {
//			dps = new DataParameters();
			// erase everything before the formatted block
			int tokenIndex = ctx.start.getTokenIndex();
			if (tokenIndex > 0) {
				rewriter.delete(0, tokenIndex - 1);
			}
		}

		// Ignored since sub-selects are not supported
//		@Override
//		public void enterSingleSelect(SeQueryParser.SingleSelectContext ctx) {
//			neededTables = new HashSet<String>();
//			subSelectLevel = 0;
//			joinType = "\r\nINNER ";
//		}

		@Override
		public void enterField(SeQueryParser.FieldContext ctx) {
			// Hide optional starting @
			if (ctx.start.getText().equals("@")) {
				rewriter.delete(ctx.start);
			}
		}

		/**
		 * If we see any ORs outside sub-selects we should do left joins
		 */
		@Override
		public void enterBoolOp(SeQueryParser.BoolOpContext ctx) {
			if (subSelectLevel < 1 && ctx.getText().toUpperCase().equals("OR")) {
				joinType = "\r\nLEFT ";
			}
		}

		@Override
		public void enterSubSelect(SeQueryParser.SubSelectContext ctx) {
//			subSelectLevel += 1;
			throw new InvalidApiUsageException("Subselects in search query are not supported");
		}

		@Override
		public void exitSubSelect(SeQueryParser.SubSelectContext ctx) {
			subSelectLevel -= 1;
		}

		@Override
		public void enterTable(SeQueryParser.TableContext ctx) {
			if (subSelectLevel < 1) {
				String table = ctx.getText();
				neededTables.add(table);
			}
		}


		@Override
		public void enterFunction(SeQueryParser.FunctionContext ctx) {
			// review use of functions
			String name = ctx.getText().toLowerCase();
			if (name.equals("exec")) {
				rewriter.replace(ctx.start, "NULL");
			}
		}

		@Override
		public void enterString(SeQueryParser.StringContext ctx) {
			// Replace literal strings with parameters to avoid SQL injection
//			int pNo = params.size() + 1;
//			String pName = ":param" + pNo;
			String pName = "?";
			// String pText = ctx.Start.Text.TrimStart('\'').TrimEnd('\'').Replace("''",
			// "'");
			String pText = StringUtils.unwrap(ctx.start.getText(), '\'').replace("''", "'");
//			params.add(new DataParameter(pName, pText, DbType.String));
			params.add(pText);
			rewriter.replace(ctx.start, pName);
		}

		@Override
		public void exitCriterion(CriterionContext ctx) {
			log.debug("Exit criterion: {}", ctx.K_IN());
			if (ctx.K_IN() != null) {
				var parenList = ctx.parenList().getText();
				var list = parenList.substring(1, parenList.length() -1);
				log.debug("IN parenList: {}", list);
				if (StringUtils.isBlank(list)) {
					// Drop entire criterion if list is blank
					rewriter.replace(ctx.start, ctx.stop, "1=2");
				}
			}
		}

		/**
		 * Not supported
		 */
		@Override
		public void enterEmbeddedFreeform(SeQueryParser.EmbeddedFreeformContext ctx) {
			throw new InvalidApiUsageException("Embedded free-form search is not supported.");
//            // replace freeform token with parametized SQL
//            string token = ctx.GetText(); int pNo = dps.Count + 1;
//            string pName = ":param" + pNo.ToString();
//            DataParameter dp;
//            DataParameters tokendps = new DataParameters();
//            dp = new DataParameter(pName+"str", unquoteForSQL(token));
//            dps.Add(dp);
//            tokendps.Add(dp);
//
//            int intToken;
//            bool wasInt = false;
//            if (int.TryParse(token, out intToken)) {
//                dp = new DataParameter(pName+"int", intToken);
//                dps.Add(dp);
//                tokendps.Add(dp);
//                wasInt = true;
//            }
//
//            Decimal decimalToken;
//            bool wasDec = false;
//            if (Decimal.TryParse(token, out decimalToken)) {
//                dp = new DataParameter(pName+"dec", decimalToken);
//                dps.Add(dp);
//                tokendps.Add(dp);
//                wasDec = true;
//            }
//
//            string oper = " = ";
//            if (token.Contains("%")) oper = " LIKE ";
//            int hitcnt = 0;
//            string pKeyTable = pKeyType.Replace("_id", "");
//            string result = pKeyTable + "." + pKeyType + " IN ( ";
//            DataSet autoFields = sd.GetData("get_search_autofields", "", 0, 0);
//            foreach (DataRow dr in autoFields.Tables["get_search_autofields"].Rows) {
//                string afTable = dr["table_name"].ToString().ToLower();
//                string afField = dr["field_name"].ToString().ToLower();
//                string afType = dr["field_type"].ToString().ToUpper();
//
//                string sqlStart = "SELECT COUNT(*) FROM " + afTable + " WHERE";
//                string whereClause = " " + afTable + "." + afField + oper;
//
//                if (afType == "STRING") {
//                    whereClause += pName + "str";
//                } else if (afType == "INTEGER") {
//                    if (!wasInt) continue;
//                    whereClause += pName + "int";
//                } else if (afType == "DECIMAL") {
//                    if (!wasDec) continue;
//                    whereClause += pName + "dec";
//                } else {
//                    continue;   // don't know how to handle field type 
//                }
//
//                int rowcnt = Toolkit.ToInt32(dm.ReadValue(sqlStart + whereClause, tokendps), -1);
//                if (rowcnt > 0) {
//                    if (++hitcnt > 1) result += "\r\nUNION ";
//                    result += "SELECT DISTINCT " + pKeyTable + "." + pKeyType;
//                    string sqlFrom = sjm.generateFromClause(pKeyTable, "\r\nINNER ", new List<string> { afTable });
//                    sqlFrom = parameterize(sqlFrom);
//                    result += sqlFrom + "\r\n  WHERE" + whereClause;
//                }
//            }
//
//            //if (hitcnt > 1) result = " (" + result + " ) ";
//            result += " ) ";
//            if (hitcnt > 0) {
//                rewriter.Replace(ctx.Start, result);
//            } else {
//                rewriter.Replace(ctx.Start, " 1=2 ");
//            }
		}

		/**
		 * Replace missing boolean operator with default
		 */
		@Override
		public void exitSearchCondition(SeQueryParser.SearchConditionContext ctx) {
			if (ctx.getChildCount() == 2 && ctx.searchCondition(1) != null) {
				rewriter.insertAfter(ctx.searchCondition(0).stop, " " + defaultOperator + " ");
			}
		}

		@Override
		public void exitSingleSelect(SeQueryParser.SingleSelectContext ctx) {
			// We use ANTLR only for SQL WHERE statement generation, not the entire query.

			/* @formatter:off */
			//			// Insert in the whole SELECT ... FROM ... WHERE bit
			//			String pKeyTable = pKeyType.replace("_id", "");
			//			String sqlStart = "SELECT DISTINCT " + pKeyTable + "." + pKeyType; // +" FROM " + pKeyTable;
			//		
			//			String sqlFrom = "";
			//			if (neededTables.size() < 1) {
			//				sqlFrom = " FROM " + pKeyTable;
			//			} else { // going to need to add some joins for the criteria tables
			//				sqlFrom = sjm.generateFromClause(pKeyTable, joinType, neededTables);
			//				sqlFrom = parameterize(sqlFrom);
			//			}
			//			sqlStart += sqlFrom;
			//		
			//			sqlStart += "\r\nWHERE\r\n";
			//			rewriter.insertBefore(ctx.start, sqlStart);
			/* @formatter:on */
		}

		/* @formatter:off */
		//		// replaces single quoted strings with parameters, does not handle escaped quotes in string
		//		public string parameterize(string sql) {
		//			int first;
		//			while ((first = sql.IndexOf("'")) > -1) {
		//				int next = sql.Substring(first + 1).IndexOf("'") + first + 1;
		//				if (first == next) return sql; // no next quote, unbalanced
		//				int pNo = dps.Count + 1;
		//				string pName = ":param" + pNo.ToString();
		//				string quote = sql.Substring(first, next+1 - first);
		//				string pText = quote.TrimStart('\'').TrimEnd('\'').Replace("''", "'");
		//				sql = sql.Replace(quote, pName);
		//				dps.Add(new DataParameter(pName, pText, DbType.String));
		//			}
		//			return sql;
		//		}
		/* @formatter:on */

		@Override
		public void exitFormattedBlock(SeQueryParser.FormattedBlockContext ctx) {
			formattedPresent = true;
		}

		@Override
		public void exitMixedQuery(SeQueryParser.MixedQueryContext ctx) {
			if (ctx.mixedBool() != null) {
				// note boolean operator and then hide it
				joiningOperator = ctx.mixedBool().start.getText();
				rewriter.delete(ctx.mixedBool().start);
			}
		}

		@Override
		public void visitErrorNode(ErrorNode node) {
			String etext = node.getText();
			throw new RuntimeException("Search engine error parsing query: " + etext);
		}
	}

	// parse tree visitor to evaluate the results of the freeform query section
	static class FreeformVisitor extends SeQueryParserBaseVisitor<List<Integer>> {
//        JoinMatrix sjm;
//        string pKeyType;
//        string defaultOperator;
//        SecureData sd;
//        GrinGlobal.Core.DataManager dm;
//
//        public FreeformVisitor(SecureData sd, GrinGlobal.Core.DataManager dm, JoinMatrix sjm, string pKeyType, string defaultOperator) {
//            this.sd = sd;
//            this.dm = dm;
//            this.sjm = sjm;
//            this.pKeyType = pKeyType;
//            this.defaultOperator = defaultOperator.ToUpper();
//         }
//
//
//        public override List<int> VisitMixedQuery(SeQueryParser.MixedQueryContext ctx) {
//            if (ctx.freeExpr() == null) {
//                return new List<int>();
//            } else {
//                return Visit(ctx.freeExpr());
//            }
//        }
//
//        //public override List<int> VisitFreeformBlock(SeQueryParser.FreeformBlockContext ctx) {
//        //    return Visit(ctx.freeExpr());
//        //}
//
//        public override List<int> VisitFreeAnd(SeQueryParser.FreeAndContext ctx) {
//            List<int> list1 = Visit(ctx.freeExpr(0));
//            List<int> list2 = Visit(ctx.freeExpr(1));
//            return list2.Intersect(list1).ToList();
//        }
//
//        public override List<int> VisitFreeOr(SeQueryParser.FreeOrContext ctx) {
//            List<int> list1 = Visit(ctx.freeExpr(0));
//            List<int> list2 = Visit(ctx.freeExpr(1));
//            return list2.Union(list1).ToList();
//        }
//
//        public override List<int> VisitFreeNot(SeQueryParser.FreeNotContext ctx) {
//            List<int> list1 = Visit(ctx.freeExpr(0));
//            List<int> list2 = Visit(ctx.freeExpr(1));
//            return list1.Except(list2).ToList();
//        }
//
//        public override List<int> VisitFreeWhat(SeQueryParser.FreeWhatContext ctx) {
//            // try shortcut of putting consectutive free tokens together and testing for id match
//            string composite = ctx.freeExpr(0).GetText();
//            IParseTree subtree = ctx.GetChild(1);
//            while (subtree.ChildCount > 1) {
//                composite += " " + subtree.GetChild(0).GetText();
//                subtree = subtree.GetChild(1);
//            }
//            composite += " " + subtree.GetText();
//            //Logger.LogText("VisitFreeWhat composite: " + composite);
//            List<int> pkeys = ResolveItemList(sd, dm, sjm, composite, pKeyType, "ID");
//            if (pkeys.Count > 0) return pkeys;
//
//            // otherwise process the tokens seperately
//            List<int> list1 = Visit(ctx.freeExpr(0));
//            List<int> list2 = Visit(ctx.freeExpr(1));
//            if (defaultOperator == "OR") {
//                return list2.Union(list1).ToList();
//            } else {
//                return list2.Intersect(list1).ToList();
//            }
//        }
//
//        public override List<int> VisitFreeParen(SeQueryParser.FreeParenContext ctx) {
//            return Visit(ctx.freeExpr());
//        }
//
//        public override List<int> VisitFreeUnit(SeQueryParser.FreeUnitContext ctx) {
//            int value = ctx.GetText().Length;
//            List<int> manyi = new List<int>();
//            manyi.Add(value);
//            //return manyi;
//            return ResolveTokenByAutoFields(sd, dm, pKeyType, ctx.GetText(), sjm);
//        }
    }
}