package org.ohdsi.webapi.service;

import static org.ohdsi.webapi.service.cscompare.ConceptSetCompareService.CONCEPT_SET_COMPARISON_ROW_MAPPER;
import static org.ohdsi.webapi.util.SecurityUtils.whitelist;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import javax.cache.CacheManager;
import javax.cache.configuration.MutableConfiguration;

import javax.ws.rs.Consumes;
import javax.ws.rs.DefaultValue;
import javax.ws.rs.ForbiddenException;
import javax.ws.rs.GET;
import javax.ws.rs.NotFoundException;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.PathParam;
import javax.ws.rs.QueryParam;
import javax.ws.rs.Produces;
import javax.ws.rs.WebApplicationException;
import javax.ws.rs.core.MediaType;
import javax.ws.rs.core.Response;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.ohdsi.analysis.Utils;
import org.ohdsi.circe.cohortdefinition.ConceptSet;
import org.ohdsi.circe.helper.ResourceHelper;
import org.ohdsi.circe.vocabulary.ConceptSetExpression;
import org.ohdsi.circe.vocabulary.ConceptSetExpressionQueryBuilder;
import org.ohdsi.sql.SqlRender;
import org.ohdsi.sql.SqlTranslate;
import org.ohdsi.vocabulary.Concept;
import org.ohdsi.vocabulary.SearchProviderConfig;
import org.ohdsi.webapi.activity.Activity.ActivityType;
import org.ohdsi.webapi.activity.Tracker;
import org.ohdsi.webapi.conceptset.ConceptSetComparison;
import org.ohdsi.webapi.conceptset.ConceptSetExport;
import org.ohdsi.webapi.conceptset.ConceptSetOptimizationResult;
import org.ohdsi.webapi.service.cscompare.CompareArbitraryDto;
import org.ohdsi.webapi.service.cscompare.ConceptSetCompareService;
import org.ohdsi.webapi.service.cscompare.ExpressionFileUtils;
import org.ohdsi.webapi.service.vocabulary.ConceptSetStrategy;
import org.ohdsi.webapi.source.Source;
import org.ohdsi.webapi.source.SourceService;
import org.ohdsi.webapi.source.SourceDaimon;
import org.ohdsi.webapi.source.SourceInfo;
import org.ohdsi.webapi.util.CacheHelper;
import org.ohdsi.webapi.util.PreparedSqlRender;
import org.ohdsi.webapi.util.PreparedStatementRenderer;
import org.ohdsi.webapi.vocabulary.ConceptRecommendedNotInstalledException;
import org.ohdsi.webapi.vocabulary.ConceptRelationship;
import org.ohdsi.webapi.vocabulary.ConceptSearch;
import org.ohdsi.webapi.vocabulary.ConceptSetCondenser;
import org.ohdsi.webapi.vocabulary.DescendentOfAncestorSearch;
import org.ohdsi.webapi.vocabulary.Domain;
import org.ohdsi.webapi.vocabulary.RecommendedConcept;
import org.ohdsi.webapi.vocabulary.RelatedConcept;
import org.ohdsi.webapi.vocabulary.RelatedConceptSearch;
import org.ohdsi.webapi.vocabulary.Vocabulary;
import org.ohdsi.webapi.vocabulary.VocabularyInfo;
import org.ohdsi.webapi.vocabulary.VocabularySearchService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.cache.JCacheManagerCustomizer;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.cache.annotation.Caching;
import org.springframework.core.convert.support.GenericConversionService;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
import org.ohdsi.webapi.vocabulary.MappedRelatedConcept;

 /**
  * Provides REST services for working with
  * the OMOP standardized vocabularies
  * 
  * @summary Vocabulary
  */
@Path("vocabulary/")
@Component
public class VocabularyService extends AbstractDaoService {

	//create cache
	@Component
	public static class CachingSetup implements JCacheManagerCustomizer {

		public static final String CONCEPT_DETAIL_CACHE = "conceptDetail";
		public static final String CONCEPT_RELATED_CACHE = "conceptRelated";
		public static final String CONCEPT_HIERARCHY_CACHE = "conceptHierarchy";

		@Override
		public void customize(CacheManager cacheManager) {
			// due to unit tests causing application contexts to reload cache manager caches, we
			// have to check for the existance of a cache before creating it
			Set<String> cacheNames = CacheHelper.getCacheNames(cacheManager);
			// Evict when a cohort definition is created or updated, or permissions, or tags
			if (!cacheNames.contains(CONCEPT_DETAIL_CACHE)) {
				cacheManager.createCache(CONCEPT_DETAIL_CACHE, new MutableConfiguration<String, Concept>()
					.setTypes(String.class, Concept.class)
					.setStoreByValue(false)
					.setStatisticsEnabled(true));
			}
			if (!cacheNames.contains(CONCEPT_RELATED_CACHE)) {
				cacheManager.createCache(CONCEPT_RELATED_CACHE, new MutableConfiguration<String, Collection<RelatedConcept>>()
					.setTypes(String.class, (Class<Collection<RelatedConcept>>) (Class<?>) Collection.class)
					.setStoreByValue(false)
					.setStatisticsEnabled(true));
			}
			if (!cacheNames.contains(CONCEPT_HIERARCHY_CACHE)) {
				cacheManager.createCache(CONCEPT_HIERARCHY_CACHE, new MutableConfiguration<String, Collection<RelatedConcept>>()
					.setTypes(String.class, (Class<Collection<RelatedConcept>>) (Class<?>) Collection.class)
					.setStoreByValue(false)
					.setStatisticsEnabled(true));
			}
		}
	}
	
	private static Hashtable<String, VocabularyInfo> vocabularyInfoCache = null;
  public static final String DEFAULT_SEARCH_ROWS = "20000";

  @Autowired
  private SourceService sourceService;
  
  @Autowired
  private VocabularySearchService vocabSearchService;

  @Autowired
  protected GenericConversionService conversionService;

  @Autowired
  private ConceptSetCompareService conceptSetCompareService;

  @Autowired
  private ObjectMapper objectMapper;

  @Value("${datasource.driverClassName}")
  private String driver;

  private final RowMapper<Concept> rowMapper = (resultSet, arg1) -> {
    final Concept concept = new Concept();
    concept.conceptId = resultSet.getLong("CONCEPT_ID");
    concept.conceptCode = resultSet.getString("CONCEPT_CODE");
    concept.conceptName = resultSet.getString("CONCEPT_NAME");
    concept.standardConcept = resultSet.getString("STANDARD_CONCEPT");
    concept.invalidReason = resultSet.getString("INVALID_REASON");
    concept.conceptClassId = resultSet.getString("CONCEPT_CLASS_ID");
    concept.vocabularyId = resultSet.getString("VOCABULARY_ID");
    concept.domainId = resultSet.getString("DOMAIN_ID");
    concept.validStartDate = resultSet.getDate("VALID_START_DATE");
    concept.validEndDate = resultSet.getDate("VALID_END_DATE");
    return concept;
  };

  public RowMapper<Concept> getRowMapper() {
    return this.rowMapper;
  }

  private String getDefaultVocabularySourceKey() {
    Source vocabSource = sourceService.getPriorityVocabularySource();
    return Objects.nonNull(vocabSource) ? vocabSource.getSourceKey() : null;
  }

  public Source getPriorityVocabularySource() {

    Source source = sourceService.getPriorityVocabularySource();
    if (Objects.isNull(source)) {
      throw new ForbiddenException();
    }
    return source;
  }

  public ConceptSetExport exportConceptSet(ConceptSet conceptSet, SourceInfo vocabSource) {

    ConceptSetExport export = conversionService.convert(conceptSet, ConceptSetExport.class);
    // Lookup the identifiers
    export.identifierConcepts = executeIncludedConceptLookup(vocabSource.sourceKey, conceptSet.expression);
    // Lookup the mapped items
    export.mappedConcepts = executeMappedLookup(vocabSource.sourceKey, conceptSet.expression);
    return export;
  }
  
  /**
   * Calculates the full set of ancestor and descendant concepts for a list of 
   * ancestor and descendant concepts specified. This is used by ATLAS when
   * navigating the list of included concepts in a concept set - the full list
   * of ancestors (as defined in the concept set) and the descendants (those
   * concepts included when resolving the concept set) are used to determine 
   * which descendant concepts share one or more ancestors.
   * 
   * @summary Calculates ancestors for a list of concepts
   * @param ids Concepts identifiers from concept set
   * @return A map of the form: {id -> List<ascendant id>}
   */
  @Path("{sourceKey}/lookup/identifiers/ancestors")
  @POST
  @Produces(MediaType.APPLICATION_JSON)
  @Consumes(MediaType.APPLICATION_JSON)
  public Map<Long, List<Long>> calculateAscendants(@PathParam("sourceKey") String sourceKey, Ids ids) {

    Source source = getSourceRepository().findBySourceKey(sourceKey);
    
    if (CollectionUtils.isEmpty(ids.ancestors) || CollectionUtils.isEmpty(ids.descendants)) { 
        return new HashMap<>();
    }

    final int limit = Math.floorDiv(PreparedSqlRender.getParameterLimit(source), 2);

    final List<Map.Entry<Long, Long>> result = new ArrayList<>();
    
    // Here we calculate cartesian product of batches
    for (final List<Long> ancestorsBatch : Lists.partition(ids.ancestors, limit)) {
      
        for (final List<Long> descendantsBatch : Lists.partition(ids.descendants, limit)) {
          
            final PreparedStatementRenderer psr = prepareAscendantsCalculating(
                    ancestorsBatch.toArray(new Long[0]),
                    descendantsBatch.toArray(new Long[0]), 
                    source
            );
            
            result.addAll(getSourceJdbcTemplate(source)
                    .query(
                            psr.getSql(),
                            psr.getSetter(),
                            (resultSet, arg1) -> Maps.immutableEntry(resultSet.getLong("ANCESTOR_ID"), resultSet.getLong("DESCENDANT_ID"))));
        }
    }

    return result
            .stream()
            .collect(
                    Collectors.groupingBy(
                            Map.Entry::getValue, 
                            Collectors.mapping(
                                    Map.Entry::getKey, 
                                    Collectors.toList()
                            )
                    )
            ); 
  }
  
  private static class Ids {
    public List<Long> ancestors;
    public List<Long> descendants;
  }

  protected PreparedStatementRenderer prepareAscendantsCalculating(Long[] identifiers, Long[] descendants, Source source) {

    String sqlPath = "/resources/vocabulary/sql/calculateAscendants.sql";
    String tqName = "CDM_schema";
    String tqValue = source.getTableQualifier(SourceDaimon.DaimonType.Vocabulary);

    return new PreparedStatementRenderer(source, sqlPath, tqName, tqValue, 
            new String[]{ "ancestors", "descendants" }, 
            new Object[]{ identifiers, descendants });
  }
  
  /**
   * Get concepts from concept identifiers (IDs) from a specific source
   * 
   * @summary Perform a lookup of an array of concept identifiers returning the
   * matching concepts with their detailed properties.
   * @param sourceKey path parameter specifying the source key identifying the
   * source to use for access to the set of vocabulary tables
   * @param identifiers an array of concept identifiers
   * @return A collection of concepts
   */
  @Path("{sourceKey}/lookup/identifiers")
  @POST
  @Produces(MediaType.APPLICATION_JSON)
  @Consumes(MediaType.APPLICATION_JSON)
  public Collection<Concept> executeIdentifierLookup(@PathParam("sourceKey") String sourceKey, long[] identifiers) {
    Source source = getSourceRepository().findBySourceKey(sourceKey);
    return executeIdentifierLookup(source, identifiers);
  }

  protected Collection<Concept> executeIdentifierLookup(Source source, long[] identifiers) {
    Collection<Concept> concepts = new ArrayList<>();
    if (identifiers.length == 0) {
      return concepts;
    } else {
      // Determine if we need to chunk up ther request based on the parameter
      // limit of the source RDBMS
      int parameterLimit = PreparedSqlRender.getParameterLimit(source);
      if (parameterLimit > 0 && identifiers.length > parameterLimit) {
        concepts = executeIdentifierLookup(source, Arrays.copyOfRange(identifiers, parameterLimit, identifiers.length));
        identifiers = Arrays.copyOfRange(identifiers, 0, parameterLimit);
      }

      PreparedStatementRenderer psr = prepareExecuteIdentifierLookup(identifiers, source);
      return concepts.addAll(getSourceJdbcTemplate(source).query(psr.getSql(), psr.getSetter(), this.rowMapper))
          ? concepts : new ArrayList<>();
    }
  }

  protected PreparedStatementRenderer prepareExecuteIdentifierLookup(long[] identifiers, Source source) {

    String sqlPath = "/resources/vocabulary/sql/lookupIdentifiers.sql";
    String tqName = "CDM_schema";
    String tqValue = source.getTableQualifier(SourceDaimon.DaimonType.Vocabulary);

    return new PreparedStatementRenderer(source, sqlPath, tqName, tqValue, "identifiers", identifiers);
  }
  
  /**
   * Get concepts from concept identifiers (IDs) from the default vocabulary 
   * source
   * 
   * @summary Perform a lookup of an array of concept identifiers returning the
   * matching concepts with their detailed properties, using the default source.
   * @param identifiers an array of concept identifiers
   * @return A collection of concepts
   */
  @Path("lookup/identifiers")
  @POST
  @Produces(MediaType.APPLICATION_JSON)
  @Consumes(MediaType.APPLICATION_JSON)
  public Collection<Concept> executeIdentifierLookup(long[] identifiers) {
    String defaultSourceKey = getDefaultVocabularySourceKey();
    
    if (defaultSourceKey == null)
      throw new WebApplicationException(new Exception("No vocabulary or cdm daimon was found in configured sources.  Search failed."), Response.Status.SERVICE_UNAVAILABLE); // http 503      

    return executeIdentifierLookup(defaultSourceKey, identifiers);   
  }  
  
  public Collection<Concept> executeIncludedConceptLookup(String sourceKey, ConceptSetExpression conceptSetExpression) {
    Source source = getSourceRepository().findBySourceKey(sourceKey);
    String tqValue = source.getTableQualifier(SourceDaimon.DaimonType.Vocabulary);
    ConceptSetExpressionQueryBuilder builder = new ConceptSetExpressionQueryBuilder();
    String query = builder.buildExpressionQuery(conceptSetExpression);
    PreparedStatementRenderer psr = new PreparedStatementRenderer(source, query, "vocabulary_database_schema", tqValue);
    String sqlPath = "/resources/vocabulary/sql/lookupIdentifiers.sql";
    String[] searches = new String[]{"identifiers", "CDM_schema"};
    String[] replacements = new String[]{psr.getSql(), tqValue};
    psr = new PreparedStatementRenderer(source, sqlPath, searches, replacements, (String[]) null, null);
    return getSourceJdbcTemplate(source).query(psr.getSql(), psr.getSetter(), this.rowMapper);
  }
  

  /**
   * Get concepts from source codes from a specific source
   * 
   * @summary Lookup source codes from the concept CONCEPT_CODE field
   * in the specified vocabulary
   * @param sourceKey path parameter specifying the source key identifying the
   * source to use for access to the set of vocabulary tables
   * @param sourcecodes array of source codes
   * @return A collection of concepts
   */
  @Path("{sourceKey}/lookup/sourcecodes")
  @POST
  @Produces(MediaType.APPLICATION_JSON)
  @Consumes(MediaType.APPLICATION_JSON)
  public Collection<Concept> executeSourcecodeLookup(@PathParam("sourceKey") String sourceKey, String[] sourcecodes) {
    if (sourcecodes.length == 0) {
      return new ArrayList<>();
    }

    Source source = getSourceRepository().findBySourceKey(sourceKey);
    PreparedStatementRenderer psr = prepareExecuteSourcecodeLookup(sourcecodes, source);
    return getSourceJdbcTemplate(source).query(psr.getSql(), psr.getSetter(), this.rowMapper);
  }

  protected PreparedStatementRenderer prepareExecuteSourcecodeLookup(String[] sourcecodes, Source source) {

    String sqlPath = "/resources/vocabulary/sql/lookupSourcecodes.sql";
    String tqName = "CDM_schema";
    String tqValue = source.getTableQualifier(SourceDaimon.DaimonType.Vocabulary);
    PreparedStatementRenderer psr = new PreparedStatementRenderer(source, sqlPath, tqName, tqValue, "sourcecodes", sourcecodes);
    return psr;
  }

  /**
   * Get concepts from source codes from the default vocabulary source
   * 
   * @summary Lookup source codes from the concept CONCEPT_CODE field
   * in the specified vocabulary
   * @param sourcecodes array of source codes
   * @return A collection of concepts
   */
  @Path("lookup/sourcecodes")
  @POST
  @Produces(MediaType.APPLICATION_JSON)
  @Consumes(MediaType.APPLICATION_JSON)
  public Collection<Concept> executeSourcecodeLookup(String[] sourcecodes) {
    String defaultSourceKey = getDefaultVocabularySourceKey();
    
    if (defaultSourceKey == null)
      throw new WebApplicationException(new Exception("No vocabulary or cdm daimon was found in configured sources.  Search failed."), Response.Status.SERVICE_UNAVAILABLE); // http 503      

    return executeSourcecodeLookup(defaultSourceKey, sourcecodes);
  }
  
  /**
   * Get concepts mapped to the selected concept identifiers from a 
   * specific source. Find all concepts mapped to the concept identifiers 
   * provided. This end-point will check the CONCEPT, CONCEPT_RELATIONSHIP and
   * SOURCE_TO_CONCEPT_MAP tables.
   * 
   * @summary Concepts mapped to other concepts
   * @param sourceKey path parameter specifying the source key identifying the
   * source to use for access to the set of vocabulary tables
   * @param identifiers an array of concept identifiers
   * @return A collection of concepts
   */
  @Path("{sourceKey}/lookup/mapped")
  @POST
  @Produces(MediaType.APPLICATION_JSON)
  @Consumes(MediaType.APPLICATION_JSON)
  public Collection<Concept> executeMappedLookup(@PathParam("sourceKey") String sourceKey, long[] identifiers) {
    Source source = getSourceRepository().findBySourceKey(sourceKey);
    return executeMappedLookup(source, identifiers);
  }

	protected Collection<Concept> executeMappedLookup(Source source, long[] identifiers) {
    Collection<Concept> concepts = new HashSet<>();
    if (identifiers.length == 0) {
      return concepts;
    } else {
      // Determine if we need to chunk up the request based on the parameter
      // limit of the source RDBMS
      int parameterLimit = PreparedSqlRender.getParameterLimit(source);
      // Next take into account the fact that the identifiers are used in 3
      // places in the query so the parameter limit will need to be divided
      parameterLimit = Math.floorDiv(parameterLimit, 3);
      if (parameterLimit > 0 && identifiers.length > parameterLimit) {
        concepts = executeMappedLookup(source, Arrays.copyOfRange(identifiers, parameterLimit, identifiers.length));
        identifiers = Arrays.copyOfRange(identifiers, 0, parameterLimit);
      }
      PreparedStatementRenderer psr = prepareExecuteMappedLookup(identifiers, source);
      return concepts.addAll(getSourceJdbcTemplate(source).query(psr.getSql(), psr.getSetter(), this.rowMapper))
          ? concepts : new HashSet<>();
    }
  }

  protected PreparedStatementRenderer prepareExecuteMappedLookup(long[] identifiers, Source source) {

    String tqName = "CDM_schema";
    String tqValue = source.getTableQualifier(SourceDaimon.DaimonType.Vocabulary);
    String resourcePath = "/resources/vocabulary/sql/getMappedSourcecodes.sql";
    return new PreparedStatementRenderer(source, resourcePath, tqName, tqValue, "identifiers", identifiers);
  }

  /**
   * Get concepts mapped to the selected concept identifiers from a 
   * specific source. Find all concepts mapped to the concept identifiers 
   * provided. This end-point will check the CONCEPT, CONCEPT_RELATIONSHIP and
   * SOURCE_TO_CONCEPT_MAP tables.
   * 
   * @summary Concepts mapped to other concepts
   * @param identifiers an array of concept identifiers
   * @return A collection of concepts
   */
  @Path("lookup/mapped")
  @POST
  @Produces(MediaType.APPLICATION_JSON)
  @Consumes(MediaType.APPLICATION_JSON)
  public Collection<Concept> executeMappedLookup(long[] identifiers) {
    String defaultSourceKey = getDefaultVocabularySourceKey();
    
    if (defaultSourceKey == null)
      throw new WebApplicationException(new Exception("No vocabulary or cdm daimon was found in configured sources.  Search failed."), Response.Status.SERVICE_UNAVAILABLE); // http 503      

    return executeMappedLookup(defaultSourceKey, identifiers);
  }
  
  public Collection<Concept> executeMappedLookup(String sourceKey, ConceptSetExpression conceptSetExpression) {
    Source source = getSourceRepository().findBySourceKey(sourceKey);
    String tableQualifier = source.getTableQualifier(SourceDaimon.DaimonType.Vocabulary);

    ConceptSetExpressionQueryBuilder builder = new ConceptSetExpressionQueryBuilder();
    String query = builder.buildExpressionQuery(conceptSetExpression);
    PreparedStatementRenderer psr = new PreparedStatementRenderer(source, query, "vocabulary_database_schema", tableQualifier);
    String sqlPath = "/resources/vocabulary/sql/getMappedSourcecodes.sql";
    String[] search = new String[]{"identifiers", "CDM_schema"};
    String[] replace = new String[]{psr.getSql(), tableQualifier};
    psr = new PreparedStatementRenderer(source, sqlPath, search, replace, (String[]) null, null);
    return getSourceJdbcTemplate(source).query(psr.getSql(), this.rowMapper);
  }

  /**
   * Search for a concept on the selected source.
   * 
   * @summary Search for a concept on the selected source
   * @param sourceKey The source key for the concept search
   * @param search The ConceptSearch parameters
   * @return A collection of concepts
   */
  @Path("{sourceKey}/search")
  @POST
  @Produces(MediaType.APPLICATION_JSON)
  @Consumes(MediaType.APPLICATION_JSON)
  public Collection<Concept> executeSearch(@PathParam("sourceKey") String sourceKey, ConceptSearch search) {
    Source source = getSourceRepository().findBySourceKey(sourceKey);

    PreparedStatementRenderer psr = prepareExecuteSearch(search, source);
    return getSourceJdbcTemplate(source).query(psr.getSql(), psr.getSetter(), rowMapper);
  }

  protected PreparedStatementRenderer prepareExecuteSearch(ConceptSearch search, Source source) {
    // escape for bracket
    search.query = search.query.replace("[", "[[]");

    String resourcePath = search.isLexical ? "/resources/vocabulary/sql/searchLexical.sql" : "/resources/vocabulary/sql/search.sql";
    String searchSql = ResourceHelper.GetResourceAsString(resourcePath);
    String tqName = "CDM_schema";
    String tqValue = source.getTableQualifier(SourceDaimon.DaimonType.Vocabulary);
    List<String> searchNamesList = new ArrayList<>();
    List<Object> replacementNamesList = new ArrayList<>();
    List<String> variableNameList = new ArrayList<>();
    List<Object> variableValueList = new ArrayList<>();

    searchNamesList.add(tqName);
    replacementNamesList.add(tqValue);
    
    String filters = "";
    if (search.domainId != null && search.domainId.length > 0) {
      // lexical search domain filters work slightly differeant than non-lexical
      if (!search.isLexical) {
        // use domain_ids as-is
        filters += " AND DOMAIN_ID IN (@domainId)";
        variableNameList.add("domainId");
        variableValueList.add(search.domainId);
      } else {
        // MEASUREMENT domain is a special case where we want to ensure concept class is 'lab test' or 'procedure'
        ArrayList<String> domainClauses = new ArrayList<>();
        String[] nonMeasurementDomains = Stream.of(search.domainId).filter(s -> !"Measurement".equals(s)).collect(Collectors.toList()).toArray(new String[0]);
        if (nonMeasurementDomains.length > 0) {
          domainClauses.add("DOMAIN_ID IN (@domainId)");
          variableNameList.add("domainId");
          variableValueList.add(nonMeasurementDomains);
        }
        if (Arrays.asList(search.domainId).contains("Measurement")) {
          domainClauses.add("(DOMAIN_ID = 'Measurement' and LOWER(concept_class_id) in ('lab test', 'procedure'))");
        }
        if (!domainClauses.isEmpty()) {
          filters += String.format(" AND (%s)", StringUtils.join(domainClauses, " OR "));
        }
      }
    }

    if (search.vocabularyId != null && search.vocabularyId.length > 0) {
      filters += " AND VOCABULARY_ID IN (@vocabularyId)";
      variableNameList.add("vocabularyId");
      variableValueList.add(search.vocabularyId);
    }

    if (search.conceptClassId != null && search.conceptClassId.length > 0) {
      filters += " AND CONCEPT_CLASS_ID IN (@conceptClassId)";
      variableNameList.add("conceptClassId");
      variableValueList.add(search.conceptClassId);

    }

    if (search.invalidReason != null && !search.invalidReason.trim().isEmpty()) {
      if (search.invalidReason.equals("V")) {
        filters += " AND INVALID_REASON IS NULL ";
      } else {
        filters += " AND INVALID_REASON = @invalidReason";
        variableNameList.add("invalidReason");
        variableValueList.add(search.invalidReason.trim());
      }
    }

    if (search.standardConcept != null) {
      if (search.standardConcept.equals("N")) {
        filters += " AND STANDARD_CONCEPT IS NULL ";
      } else {
        filters += " AND STANDARD_CONCEPT = @standardConcept";
        variableNameList.add("standardConcept");
        variableValueList.add(search.standardConcept.trim());
      }
    }
    
    if (search.isLexical) {
      // 1. Create term variables for the expressions including truncated terms (terms >=8 are truncated to 6 letters
      List<String> searchTerms = Arrays.asList(StringUtils.split(search.query.toLowerCase(), " "));
      List<String> allTerms = Stream.concat(
          searchTerms.stream(), 
          searchTerms.stream().filter(i -> i.length() >= 8).map(i -> StringUtils.left(i,6))
      ).sorted((a,b) -> b.length() - a.length()).collect(Collectors.toList());
      LinkedHashMap<String, String> termMap = new LinkedHashMap<>();
      for (int i=0;i<allTerms.size();i++) {
        termMap.put(String.format("term_%d",i+1), allTerms.get(i));
      }
      // 2. Create REPLACE expressions to caluclate the match ratio
      String replaceExpression = termMap.keySet().stream()
          .reduce("", (acc, element) -> {
            return "".equals(acc) ? 
                String.format("REPLACE(lower(concept_name), '@%s','')",element) // the first iteration
                : String.format("REPLACE(%s, '@%s','')", acc, element); // the subsequent iterations
          });
      searchNamesList.add("replace_expression");
      replacementNamesList.add(replaceExpression);
      
      // 3. Create the set of 'like' expressions for concept name from the terms that are < 8 chars
      List<String> nameFilterList = termMap.keySet().stream()
          .filter(k -> termMap.get(k).length() < 8)
          .map(k -> String.format("lower(concept_name) like '%%@%s%%'",k))
          .collect(Collectors.toList());
      searchNamesList.add("name_filters");
      replacementNamesList.add(StringUtils.join(nameFilterList, " AND "));
      
      // 4. Create the set of 'like' expressions for concept synonyms
      List<String> synonymFilterList = termMap.keySet().stream()
          .filter(k -> termMap.get(k).length() < 8)
          .map(k -> String.format("lower(concept_synonym_name) like '%%@%s%%'",k))
          .collect(Collectors.toList());
      searchNamesList.add("synonym_filters");
      replacementNamesList.add(StringUtils.join(synonymFilterList, " AND "));
      
     // 5. Create name-value pairs for each term paramater
     for (Map.Entry<String,String> entry : termMap.entrySet()) {
       variableNameList.add(entry.getKey());
       variableValueList.add(entry.getValue());
     }
    } else {
      if (!search.query.isEmpty()) {
        String queryFilter = "LOWER(CONCEPT_NAME) LIKE '%@query%' or LOWER(CONCEPT_CODE) LIKE '%@query%'";
        if (StringUtils.isNumeric(search.query)) {
          queryFilter += " or CONCEPT_ID = CAST(@query as int)";
        }
        filters += " AND (" + queryFilter + ")";
        variableNameList.add("query");
        variableValueList.add(search.query.toLowerCase());
      }
    }
    searchSql = StringUtils.replace(searchSql, "@filters", filters);

    String[] searchNames = searchNamesList.toArray(new String[0]);
    String[] replacementNames = replacementNamesList.toArray(new String[0]);

    String[] variableNames = variableNameList.toArray(new String[variableNameList.size()]);
    Object[] variableValues = variableValueList.toArray(new Object[variableValueList.size()]);

    PreparedStatementRenderer renderer = new PreparedStatementRenderer(source, searchSql, searchNames, replacementNames, variableNames, variableValues);
    String debugSql = renderer.generateDebugSql(searchSql, searchNames, replacementNames, variableNames, variableValues);
    return renderer;

  }

  /**
   * Search for a concept on the default vocabulary source.
   * 
   * @summary Search for a concept (default vocabulary source)
   * @param search The ConceptSearch parameters
   * @return A collection of concepts
   */
  @Path("search")
  @POST
  @Produces(MediaType.APPLICATION_JSON)
  @Consumes(MediaType.APPLICATION_JSON)
  public Collection<Concept> executeSearch(ConceptSearch search) {
    String defaultSourceKey = getDefaultVocabularySourceKey();
    
    if (defaultSourceKey == null)
      throw new WebApplicationException(new Exception("No vocabulary or cdm daimon was found in configured sources.  Search failed."), Response.Status.SERVICE_UNAVAILABLE); // http 503      

    return executeSearch(defaultSourceKey, search);    
  }
 
  /**
   * Search for a concept based on a query using the selected vocabulary source.
   * 
   * @summary Search for a concept using a query
   * @param sourceKey The source key holding the OMOP vocabulary
   * @param query The query to use to search for concepts
   * @return A collection of concepts
   */
  @Path("{sourceKey}/search/{query}")
  @GET
  @Produces(MediaType.APPLICATION_JSON)
  public Collection<Concept> executeSearch(@PathParam("sourceKey") String sourceKey, @PathParam("query") String query) {
    return this.executeSearch(sourceKey, query, DEFAULT_SEARCH_ROWS);
  }
  
  /**
   * Search for a concept based on a query using the default vocabulary source.
   * NOTE: This method uses the query as part of the URL query string
   * 
   * @summary Search for a concept using a query (default vocabulary)
   * @param sourceKey The source key holding the OMOP vocabulary
   * @param query The query to use to search for concepts
   * @param rows The number of rows to return.
   * @return A collection of concepts
   */
  @Path("{sourceKey}/search")
  @GET
  @Produces(MediaType.APPLICATION_JSON)
  public Collection<Concept> executeSearch(@PathParam("sourceKey") String sourceKey, @QueryParam("query") String query, @DefaultValue(DEFAULT_SEARCH_ROWS) @QueryParam("rows") String rows) {
    // Verify that the rows parameter contains an integer and is > 0
    try {
        Integer r = Integer.parseInt(rows);
        if (r <= 0) {
            throw new NumberFormatException("The rows parameter must be greater than 0");
        }
    } catch (NumberFormatException nfe) {
        throw nfe;
    }
    
    Collection<Concept> concepts = new ArrayList<>();
    try {
        Source source = getSourceRepository().findBySourceKey(sourceKey);
        VocabularyInfo vocabularyInfo = getInfo(sourceKey);
        String versionKey = vocabularyInfo.version.replace(' ', '_');
        SearchProviderConfig searchConfig = new SearchProviderConfig(source.getSourceKey(), versionKey);
        concepts = vocabSearchService.getSearchProvider(searchConfig).executeSearch(searchConfig, query, rows);
    } catch (Exception ex) {
        log.error("An error occurred during the vocabulary search", ex);
    }
    return concepts;
  }

  public PreparedStatementRenderer prepareExecuteSearchWithQuery(String query, Source source) {

    ConceptSearch search = new ConceptSearch();
    search.query = query;
    return this.prepareExecuteSearch(search, source);
  }

  /**
   * Search for a concept based on a query using the default vocabulary source.
   * NOTE: This method uses the query as part of the URL and not the 
   * query string
   * 
