/*
 * Copyright 2015 Observational Health Data Sciences and Informatics [OHDSI.org].
 *
 * 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.ohdsi.webapi.service;

import com.fasterxml.jackson.annotation.JsonProperty;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.commons.lang3.StringUtils;
import org.commonmark.Extension;
import org.commonmark.ext.gfm.tables.TablesExtension;
import org.commonmark.node.*;
import org.commonmark.parser.Parser;
import org.commonmark.renderer.html.HtmlRenderer;
import org.ohdsi.analysis.Utils;
import org.ohdsi.analysis.cohortcharacterization.design.StandardFeatureAnalysisType;
import org.ohdsi.circe.check.Checker;
import org.ohdsi.circe.cohortdefinition.CohortExpression;
import org.ohdsi.circe.cohortdefinition.CohortExpressionQueryBuilder;
import org.ohdsi.circe.cohortdefinition.ConceptSet;
import org.ohdsi.circe.cohortdefinition.printfriendly.MarkdownRender;
import org.ohdsi.circe.helper.ResourceHelper;
import org.ohdsi.featureExtraction.FeatureExtraction;
import org.ohdsi.sql.SqlRender;
import org.ohdsi.sql.SqlTranslate;
import org.ohdsi.webapi.Constants;
import org.ohdsi.webapi.check.CheckResult;
import org.ohdsi.webapi.check.checker.cohort.CohortChecker;
import org.ohdsi.webapi.check.warning.Warning;
import org.ohdsi.webapi.check.warning.WarningUtils;
import org.ohdsi.webapi.cohortcharacterization.dto.CcDistributionStat;
import org.ohdsi.webapi.cohortcharacterization.dto.CcPrevalenceStat;
import org.ohdsi.webapi.cohortcharacterization.dto.CcResult;
import org.ohdsi.webapi.cohortcharacterization.dto.ExecutionResultRequest;
import org.ohdsi.webapi.cohortcharacterization.report.AnalysisItem;
import org.ohdsi.webapi.cohortcharacterization.report.AnalysisResultItem;
import org.ohdsi.webapi.cohortcharacterization.report.Report;
import org.ohdsi.webapi.cohortdefinition.CleanupCohortTasklet;
import org.ohdsi.webapi.cohortdefinition.CohortDefinition;
import org.ohdsi.webapi.cohortdefinition.CohortDefinitionDetails;
import org.ohdsi.webapi.cohortdefinition.CohortDefinitionRepository;
import org.ohdsi.webapi.cohortdefinition.CohortGenerationInfo;
import org.ohdsi.webapi.cohortdefinition.InclusionRuleReport;
import org.ohdsi.webapi.cohortdefinition.dto.CohortDTO;
import org.ohdsi.webapi.cohortdefinition.dto.CohortGenerationInfoDTO;
import org.ohdsi.webapi.cohortdefinition.dto.CohortMetadataDTO;
import org.ohdsi.webapi.cohortdefinition.dto.CohortMetadataImplDTO;
import org.ohdsi.webapi.cohortdefinition.dto.CohortVersionFullDTO;
import org.ohdsi.webapi.cohortsample.CleanupCohortSamplesTasklet;
import org.ohdsi.webapi.cohortsample.CohortSamplingService;
import org.ohdsi.webapi.cohortdefinition.dto.CohortRawDTO;
import org.ohdsi.webapi.cohortdefinition.event.CohortDefinitionChangedEvent;
import org.ohdsi.webapi.common.SourceMapKey;
import org.ohdsi.webapi.common.generation.GenerateSqlResult;
import org.ohdsi.webapi.common.sensitiveinfo.CohortGenerationSensitiveInfoService;
import org.ohdsi.webapi.conceptset.ConceptSetExport;
import org.ohdsi.webapi.feanalysis.domain.FeAnalysisEntity;
import org.ohdsi.webapi.feanalysis.repository.FeAnalysisEntityRepository;
import org.ohdsi.webapi.job.JobExecutionResource;
import org.ohdsi.webapi.job.JobTemplate;
import org.ohdsi.webapi.security.PermissionService;
import org.ohdsi.webapi.service.dto.CheckResultDTO;
import org.ohdsi.webapi.shiro.Entities.UserEntity;
import org.ohdsi.webapi.shiro.management.datasource.SourceIdAccessor;
import org.ohdsi.webapi.source.Source;
import org.ohdsi.webapi.source.SourceDaimon;
import org.ohdsi.webapi.source.SourceInfo;
import org.ohdsi.webapi.tag.domain.HasTags;
import org.ohdsi.webapi.tag.dto.TagNameListRequestDTO;
import org.ohdsi.webapi.util.*;
import org.ohdsi.webapi.versioning.domain.CohortVersion;
import org.ohdsi.webapi.versioning.domain.Version;
import org.ohdsi.webapi.versioning.domain.VersionBase;
import org.ohdsi.webapi.versioning.domain.VersionType;
import org.ohdsi.webapi.versioning.dto.VersionDTO;
import org.ohdsi.webapi.versioning.dto.VersionUpdateDTO;
import org.ohdsi.webapi.versioning.service.VersionService;
import org.springframework.batch.core.Job;
import org.springframework.batch.core.JobParameters;
import org.springframework.batch.core.JobParametersBuilder;
import org.springframework.batch.core.Step;
import org.springframework.batch.core.configuration.annotation.JobBuilderFactory;
import org.springframework.batch.core.configuration.annotation.StepBuilderFactory;
import org.springframework.batch.core.job.builder.SimpleJobBuilder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.ApplicationEventPublisher;
import org.springframework.core.convert.ConversionService;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.servlet.ServletContext;
import javax.transaction.Transactional;
import javax.ws.rs.Consumes;
import javax.ws.rs.DELETE;
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.PUT;
import javax.ws.rs.Path;
import javax.ws.rs.PathParam;
import javax.ws.rs.Produces;
import javax.ws.rs.QueryParam;
import javax.ws.rs.core.Context;
import javax.ws.rs.core.MediaType;
import javax.ws.rs.core.Response;
import java.io.ByteArrayOutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Objects;
import java.util.Optional;
import java.util.Set;
import java.util.function.Function;
import java.util.stream.Collectors;
import javax.cache.CacheManager;
import javax.cache.configuration.MutableConfiguration;
import javax.ws.rs.core.Response.ResponseBuilder;

import static org.ohdsi.analysis.cohortcharacterization.design.CcResultType.DISTRIBUTION;
import static org.ohdsi.analysis.cohortcharacterization.design.CcResultType.PREVALENCE;
import static org.ohdsi.webapi.Constants.Params.COHORT_DEFINITION_ID;
import static org.ohdsi.webapi.Constants.Params.JOB_NAME;
import static org.ohdsi.webapi.Constants.Params.SOURCE_ID;
import org.ohdsi.webapi.source.SourceService;
import org.ohdsi.webapi.sqlrender.SourceAwareSqlRender;

import static org.ohdsi.webapi.util.SecurityUtils.whitelist;
import org.springframework.boot.autoconfigure.cache.JCacheManagerCustomizer;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.Cacheable;

/**
 * Provides REST services for working with cohort definitions.
 *
 * @summary Provides REST services for working with cohort definitions.
 * @author cknoll1
 */
@Path("/cohortdefinition")
@Component
public class CohortDefinitionService extends AbstractDaoService implements HasTags<Integer> {

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

		public static final String COHORT_DEFINITION_LIST_CACHE = "cohortDefinitionList";

		@Override
		public void customize(CacheManager cacheManager) {
			// Evict when a cohort definition is created or updated, or permissions, or tags
			if (!CacheHelper.getCacheNames(cacheManager).contains(COHORT_DEFINITION_LIST_CACHE)) {
				cacheManager.createCache(COHORT_DEFINITION_LIST_CACHE, new MutableConfiguration<String, List<CohortMetadataDTO>>()
					.setTypes(String.class, (Class<List<CohortMetadataDTO>>) (Class<?>) List.class)
					.setStoreByValue(false)
					.setStatisticsEnabled(true));
			}
		}
	}
	
	private static final CohortExpressionQueryBuilder queryBuilder = new CohortExpressionQueryBuilder();
    private static final int DEMOGRAPHIC_MODE = 2;
    private static final String DEMOGRAPHIC_DOMAIN = "DEMOGRAPHICS";
    private static final String[] PARAMETERS_RESULTS_FILTERED = { "cohort_characterization_generation_id",
                    "threshold_level", "analysis_ids", "cohort_ids", "vocabulary_schema" };
    private final List<String[]> executionPrevalenceHeaderLines = new ArrayList<String[]>() {
        {
            add(new String[] { "Analysis ID", "Analysis name", "Strata ID", "Strata name", "Cohort ID", "Cohort name",
                            "Covariate ID", "Covariate name", "Covariate short name", "Count", "Percent" });
        }
    };
    private final List<String[]> executionDistributionHeaderLines = new ArrayList<String[]>() {
        {
            add(new String[] { "Analysis ID", "Analysis name", "Strata ID", "Strata name", "Cohort ID", "Cohort name",
                            "Covariate ID", "Covariate name", "Covariate short name", "Value field",
                            "Missing Means Zero", "Count", "Avg", "StdDev", "Min", "P10", "P25", "Median", "P75", "P90",
                            "Max" });
        }
    };
    private final List<String[]> executionComparativeHeaderLines = new ArrayList<String[]>() {
        {
            add(new String[] { "Analysis ID", "Analysis name", "Strata ID", "Strata name", "Target cohort ID",
                            "Target cohort name", "Comparator cohort ID", "Comparator cohort name", "Covariate ID",
                            "Covariate name", "Covariate short name", "Target count", "Target percent",
                            "Comparator count", "Comparator percent", "Std. Diff Of Mean" });
        }
    };
    private Map<String, FeatureExtraction.PrespecAnalysis> prespecAnalysisMap = FeatureExtraction
            .getNameToPrespecAnalysis();
    private final String QUERY_RESULTS = ResourceHelper
            .GetResourceAsString("/resources/cohortcharacterizations/sql/queryResults.sql");

	@Autowired
	private CohortDefinitionRepository cohortDefinitionRepository;

	@Autowired
	private JobBuilderFactory jobBuilders;

	@Autowired
	private StepBuilderFactory stepBuilders;

	@Autowired
	private JobTemplate jobTemplate;

	@Autowired
	private CohortGenerationService cohortGenerationService;

	@Autowired
	private JobService jobService;

	@Autowired
	private CohortGenerationSensitiveInfoService sensitiveInfoService;

	@Autowired
	private SourceIdAccessor sourceIdAccessor;

	@Autowired
	ConversionService conversionService;

	@Autowired
	private ObjectMapper objectMapper;
  
	@Autowired
	private CohortSamplingService samplingService;

	@Autowired
	private ApplicationEventPublisher eventPublisher;

	@Autowired
	private SourceService sourceService;

	@Autowired
	private VocabularyService vocabularyService;

	@Autowired
	private PermissionService permissionService;

	@PersistenceContext
	protected EntityManager entityManager;

	@Autowired
	private CohortChecker cohortChecker;

	@Autowired
	private VersionService<CohortVersion> versionService;

  @Autowired
  private FeAnalysisEntityRepository feAnalysisRepository;

  @Autowired
  private SourceAwareSqlRender sourceAwareSqlRender;

  @Value("${security.defaultGlobalReadPermissions}")
	private boolean defaultGlobalReadPermissions;

	private final MarkdownRender markdownPF = new MarkdownRender();

	private final List<Extension> extensions = Arrays.asList(TablesExtension.create());

	private final RowMapper<InclusionRuleReport.Summary> summaryMapper = (rs, rowNum) -> {
		InclusionRuleReport.Summary summary = new InclusionRuleReport.Summary();
		summary.baseCount = rs.getLong("base_count");
		summary.finalCount = rs.getLong("final_count");
		summary.lostCount = rs.getLong("lost_count");

		double matchRatio = (summary.baseCount > 0) ? ((double) summary.finalCount / (double) summary.baseCount) : 0.0;
		summary.percentMatched = new BigDecimal(matchRatio * 100.0).setScale(2, RoundingMode.HALF_UP).toPlainString() + "%";
		return summary;
	};

	private final RowMapper<InclusionRuleReport.InclusionRuleStatistic> inclusionRuleStatisticMapper = new RowMapper<InclusionRuleReport.InclusionRuleStatistic>() {

		@Override
		public InclusionRuleReport.InclusionRuleStatistic mapRow(ResultSet rs, int rowNum) throws SQLException {
			InclusionRuleReport.InclusionRuleStatistic statistic = new InclusionRuleReport.InclusionRuleStatistic();
			statistic.id = rs.getInt("rule_sequence");
			statistic.name = rs.getString("name");
			statistic.countSatisfying = rs.getLong("person_count");
			long personTotal = rs.getLong("person_total");

			long gainCount = rs.getLong("gain_count");
			double excludeRatio = personTotal > 0 ? (double) gainCount / (double) personTotal : 0.0;
			String percentExcluded = new BigDecimal(excludeRatio * 100.0).setScale(2, RoundingMode.HALF_UP).toPlainString();
			statistic.percentExcluded = percentExcluded + "%";

			long satisfyCount = rs.getLong("person_count");
			double satisfyRatio = personTotal > 0 ? (double) satisfyCount / (double) personTotal : 0.0;
			String percentSatisfying = new BigDecimal(satisfyRatio * 100.0).setScale(2, RoundingMode.HALF_UP).toPlainString();
			statistic.percentSatisfying = percentSatisfying + "%";
			return statistic;
		}
	};

	private final RowMapper<Long[]> inclusionRuleResultItemMapper = new RowMapper<Long[]>() {

		@Override
		public Long[] mapRow(ResultSet rs, int rowNum) throws SQLException {
			Long[] resultItem = new Long[2];
			resultItem[0] = rs.getLong("inclusion_rule_mask");
			resultItem[1] = rs.getLong("person_count");
			return resultItem;
		}
	};

	private CohortGenerationInfo findBySourceId(Set<CohortGenerationInfo> infoList, Integer sourceId) {
		for (CohortGenerationInfo info : infoList) {
			if (info.getId().getSourceId().equals(sourceId)) {
				return info;
			}
		}
		return null;
	}

	private InclusionRuleReport.Summary getInclusionRuleReportSummary(int id, Source source, int modeId) {

		String sql = "select cs.base_count, cs.final_count, cc.lost_count from @tableQualifier.cohort_summary_stats cs left join @tableQualifier.cohort_censor_stats cc "
						+ "on cc.cohort_definition_id = cs.cohort_definition_id where cs.cohort_definition_id = @id and cs.mode_id = @modeId";
		String tqName = "tableQualifier";
		String tqValue = source.getTableQualifier(SourceDaimon.DaimonType.Results);
		String[] varNames = {"id", "modeId"};
		Object[] varValues = {whitelist(id), whitelist(modeId)};
		PreparedStatementRenderer psr = new PreparedStatementRenderer(source, sql, tqName, tqValue, varNames, varValues, SessionUtils.sessionId());
		List<InclusionRuleReport.Summary> result = getSourceJdbcTemplate(source).query(psr.getSql(), psr.getSetter(), summaryMapper);
		return result.isEmpty() ? new InclusionRuleReport.Summary() : result.get(0);
	}

	private List<InclusionRuleReport.InclusionRuleStatistic> getInclusionRuleStatistics(int id, Source source, int modeId) {

		String sql = "select i.rule_sequence, i.name, s.person_count, s.gain_count, s.person_total"
						+ " from @tableQualifier.cohort_inclusion i join @tableQualifier.cohort_inclusion_stats s on i.cohort_definition_id = s.cohort_definition_id"
						+ " and i.rule_sequence = s.rule_sequence"
						+ " where i.cohort_definition_id = @id and mode_id = @modeId ORDER BY i.rule_sequence";
		String tqName = "tableQualifier";
		String tqValue = source.getTableQualifier(SourceDaimon.DaimonType.Results);
		String[] varNames = {"id", "modeId"};
		Object[] varValues = {whitelist(id), whitelist(modeId)};
		PreparedStatementRenderer psr = new PreparedStatementRenderer(source, sql, tqName, tqValue, varNames, varValues, SessionUtils.sessionId());
		return getSourceJdbcTemplate(source).query(psr.getSql(), psr.getSetter(), inclusionRuleStatisticMapper);
	}

    private List<Report> getDemographicStatistics(int id, Source source,
            int modeId, long ccGenerateId) {
        ExecutionResultRequest params = new ExecutionResultRequest();

        // Get FE Analysis Demographic (Gender, Age, Race,)
        Set<FeAnalysisEntity> featureAnalyses = feAnalysisRepository.findByListIds(Arrays.asList(70, 72, 74, 77));

        params.setCohortIds(Arrays.asList(id));
        params.setAnalysisIds(featureAnalyses.stream().map(this::mapFeatureAnalysisId).collect(Collectors.toList()));
        params.setDomainIds(Arrays.asList(DEMOGRAPHIC_DOMAIN));

        List<CcResult> ccResults = findResults(ccGenerateId, params, source);
        Map<Integer, AnalysisItem> analysisMap = new HashMap<>();
        ccResults.stream().peek(cc -> {
            if (StandardFeatureAnalysisType.PRESET.toString().equals(cc.getFaType())) {
                featureAnalyses.stream().filter(fa -> Objects.equals(fa.getDesign(), cc.getAnalysisName())).findFirst()
                        .ifPresent(v -> cc.setAnalysisId(v.getId()));
            }
        }).forEach(ccResult -> {
            if (ccResult instanceof CcPrevalenceStat) {
                analysisMap.putIfAbsent(ccResult.getAnalysisId(), new AnalysisItem());
                AnalysisItem analysisItem = analysisMap.get(ccResult.getAnalysisId());
                analysisItem.setType(ccResult.getResultType());
                analysisItem.setName(ccResult.getAnalysisName());
                analysisItem.setFaType(ccResult.getFaType());
                List<CcResult> results = analysisItem.getOrCreateCovariateItem(
                        ((CcPrevalenceStat) ccResult).getCovariateId(), ccResult.getStrataId());
                results.add(ccResult);
            }
        });

        CohortDefinition cohortDefinition = cohortDefinitionRepository.findOne(id);
        List<Report> reports = prepareReportData(analysisMap,
                new HashSet<CohortDefinition>(Arrays.asList(cohortDefinition)), featureAnalyses);

        return reports;
    }

    private List<Report> prepareReportData(Map<Integer, AnalysisItem> analysisMap, Set<CohortDefinition> cohortDefs,
            Set<FeAnalysisEntity> featureAnalyses) {
        // Create map to get cohort name by its id
        final Map<Integer, CohortDefinition> definitionMap = cohortDefs.stream()
                .collect(Collectors.toMap(CohortDefinition::getId, Function.identity()));
        // Create map to get feature analyses by its name
        final Map<String, String> feAnalysisMap = featureAnalyses.stream()
                .collect(Collectors.toMap(this::mapFeatureName, entity -> entity.getDomain().toString()));

        List<Report> reports = new ArrayList<>();
        try {
            // list to accumulate results from simple reports
            List<AnalysisResultItem> simpleResultSummary = new ArrayList<>();
            // list to accumulate results from comparative reports
            List<AnalysisResultItem> comparativeResultSummary = new ArrayList<>();
            // do not create summary reports when only one analyses is present
            boolean ignoreSummary = analysisMap.keySet().size() == 1;
            for (Integer analysisId : analysisMap.keySet()) {
                analysisMap.putIfAbsent(analysisId, new AnalysisItem());
                AnalysisItem analysisItem = analysisMap.get(analysisId);
                AnalysisResultItem resultItem = analysisItem.getSimpleItems(definitionMap, feAnalysisMap);
                Report simpleReport = new Report(analysisItem.getName(), analysisId, resultItem);
                simpleReport.faType = analysisItem.getFaType();
                simpleReport.domainId = feAnalysisMap.get(analysisItem.getName());

                if (PREVALENCE.equals(analysisItem.getType())) {
                    simpleReport.header = executionPrevalenceHeaderLines;
                    simpleReport.resultType = PREVALENCE;
                    // Summary comparative reports are only available for
                    // prevalence type
                    simpleResultSummary.add(resultItem);
                } else if (DISTRIBUTION.equals(analysisItem.getType())) {
                    simpleReport.header = executionDistributionHeaderLines;
                    simpleReport.resultType = DISTRIBUTION;
                }
                reports.add(simpleReport);

                // comparative mode
                if (definitionMap.size() == 2) {
                    Iterator<CohortDefinition> iter = definitionMap.values().iterator();
                    CohortDefinition firstCohortDef = iter.next();
                    CohortDefinition secondCohortDef = iter.next();
                    AnalysisResultItem comparativeResultItem = analysisItem.getComparativeItems(firstCohortDef,
                            secondCohortDef, feAnalysisMap);
                    Report comparativeReport = new Report(analysisItem.getName(), analysisId, comparativeResultItem);
                    comparativeReport.header = executionComparativeHeaderLines;
                    comparativeReport.isComparative = true;
                    comparativeReport.faType = analysisItem.getFaType();
                    comparativeReport.domainId = feAnalysisMap.get(analysisItem.getName());
                    if (PREVALENCE.equals(analysisItem.getType())) {
                        comparativeReport.resultType = PREVALENCE;
                        // Summary comparative reports are only available for
                        // prevalence type
                        comparativeResultSummary.add(comparativeResultItem);
                    } else if (DISTRIBUTION.equals(analysisItem.getType())) {
                        comparativeReport.resultType = DISTRIBUTION;
                    }
                    reports.add(comparativeReport);
                }
            }
            if (!ignoreSummary) {
                // summary comparative reports are only available for prevalence
                // type
                if (!simpleResultSummary.isEmpty()) {
                    Report simpleSummaryData = new Report("All prevalence covariates", simpleResultSummary);
                    simpleSummaryData.header = executionPrevalenceHeaderLines;
                    simpleSummaryData.isSummary = true;
                    simpleSummaryData.resultType = PREVALENCE;
                    reports.add(simpleSummaryData);
                }
                // comparative mode
                if (!comparativeResultSummary.isEmpty()) {
                    Report comparativeSummaryData = new Report("All prevalence covariates", comparativeResultSummary);
                    comparativeSummaryData.header = executionComparativeHeaderLines;
                    comparativeSummaryData.isSummary = true;
                    comparativeSummaryData.isComparative = true;
                    comparativeSummaryData.resultType = PREVALENCE;
                    reports.add(comparativeSummaryData);
                }
            }

            return reports;
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        }
    }

    private String mapFeatureName(FeAnalysisEntity entity) {

        if (StandardFeatureAnalysisType.PRESET == entity.getType()) {
            return entity.getDesign().toString();
        }
        return entity.getName();
    }

    private List<CcResult> findResults(final Long generationId, ExecutionResultRequest params, Source source) {
        return executeFindResults(generationId, params, QUERY_RESULTS, getGenerationResults(source), source);
    }

    private <T> List<T> executeFindResults(final Long generationId, ExecutionResultRequest params, String query,
            RowMapper<T> rowMapper, Source source) {
        String analysis = params.getAnalysisIds().stream().map(String::valueOf).collect(Collectors.joining(","));
        String cohorts = params.getCohortIds().stream().map(String::valueOf).collect(Collectors.joining(","));
        String generationResults = sourceAwareSqlRender.renderSql(source.getSourceId(), query,
                PARAMETERS_RESULTS_FILTERED,
                new String[] { String.valueOf(generationId), String.valueOf(params.getThresholdValuePct()), analysis,
                                cohorts, SourceUtils.getVocabularyQualifier(source) });
        final String tempSchema = SourceUtils.getTempQualifier(source);
        String translatedSql = SqlTranslate.translateSql(generationResults, source.getSourceDialect(),
                SessionUtils.sessionId(), tempSchema);
        return this.getSourceJdbcTemplate(source).query(translatedSql, rowMapper);
    }

    private RowMapper<CcResult> getGenerationResults(Source source) {
        return (rs, rowNum) -> {
            final String type = rs.getString("type");
            if (StringUtils.equals(type, DISTRIBUTION.toString())) {
                final CcDistributionStat distributionStat = new CcDistributionStat();
                gatherForPrevalence(distributionStat, rs, source);
                gatherForDistribution(distributionStat, rs);
                return distributionStat;
            } else if (StringUtils.equals(type, PREVALENCE.toString())) {
                final CcPrevalenceStat prevalenceStat = new CcPrevalenceStat();
                gatherForPrevalence(prevalenceStat, rs, source);
                return prevalenceStat;
            }
            return null;
        };
    }

    private void gatherForPrevalence(final CcPrevalenceStat stat, final ResultSet rs, Source source)
            throws SQLException {
        stat.setFaType(rs.getString("fa_type"));
        stat.setSourceKey(source.getSourceKey());
        stat.setCohortId(rs.getInt("cohort_definition_id"));
        stat.setAnalysisId(rs.getInt("analysis_id"));
        stat.setAnalysisName(rs.getString("analysis_name"));
        stat.setResultType(PREVALENCE);
        stat.setCovariateId(rs.getLong("covariate_id"));
        stat.setCovariateName(rs.getString("covariate_name"));
        stat.setConceptName(rs.getString("concept_name"));
        stat.setConceptId(rs.getLong("concept_id"));
        stat.setAvg(rs.getDouble("avg_value"));
        stat.setCount(rs.getLong("count_value"));
        stat.setStrataId(rs.getLong("strata_id"));
        stat.setStrataName(rs.getString("strata_name"));
    }

    private void gatherForDistribution(final CcDistributionStat stat, final ResultSet rs) throws SQLException {
        stat.setResultType(DISTRIBUTION);
        stat.setAvg(rs.getDouble("avg_value"));
        stat.setStdDev(rs.getDouble("stdev_value"));
        stat.setMin(rs.getDouble("min_value"));
        stat.setP10(rs.getDouble("p10_value"));
        stat.setP25(rs.getDouble("p25_value"));
        stat.setMedian(rs.getDouble("median_value"));
        stat.setP75(rs.getDouble("p75_value"));
        stat.setP90(rs.getDouble("p90_value"));
        stat.setMax(rs.getDouble("max_value"));
        stat.setAggregateId(rs.getInt("aggregate_id"));
        stat.setAggregateName(rs.getString("aggregate_name"));
        stat.setMissingMeansZero(rs.getInt("missing_means_zero") == 1);
    }

    private Integer mapFeatureAnalysisId(FeAnalysisEntity feAnalysis) {

        if (feAnalysis.isPreset()) {
            return prespecAnalysisMap.values().stream()
                    .filter(p -> Objects.equals(p.analysisName, feAnalysis.getDesign())).findFirst()
                    .orElseThrow(() -> new IllegalArgumentException(
                            String.format("Preset analysis with id=%s does not exist", feAnalysis.getId()))).analysisId;
        } else {
            return feAnalysis.getId();
        }
    }

	private int countSetBits(long n) {
		int count = 0;
		while (n > 0) {
			n &= (n - 1);
			count++;
		}
		return count;
	}

	private String formatBitMask(Long n, int size) {
		return StringUtils.reverse(StringUtils.leftPad(Long.toBinaryString(n), size, "0"));
	}

	private String getInclusionRuleTreemapData(int id, int inclusionRuleCount, Source source, int modeId) {

		String sql = "select inclusion_rule_mask, person_count from @tableQualifier.cohort_inclusion_result where cohort_definition_id = @id and mode_id = @modeId";
		String tqName = "tableQualifier";
		String tqValue = source.getTableQualifier(SourceDaimon.DaimonType.Results);
		String[] varNames = {"id", "modeId"};
		Object[] varValues = {whitelist(id), whitelist(modeId)};
		PreparedStatementRenderer psr = new PreparedStatementRenderer(source, sql, tqName, tqValue, varNames, varValues, SessionUtils.sessionId());

		// [0] is the inclusion rule bitmask, [1] is the count of the match
		List<Long[]> items = this.getSourceJdbcTemplate(source).query(psr.getSql(), psr.getSetter(), inclusionRuleResultItemMapper);
		Map<Integer, List<Long[]>> groups = new HashMap<>();
		for (Long[] item : items) {
			int bitsSet = countSetBits(item[0]);
			if (!groups.containsKey(bitsSet)) {
				groups.put(bitsSet, new ArrayList<Long[]>());
			}
			groups.get(bitsSet).add(item);
		}

		StringBuilder treemapData = new StringBuilder("{\"name\" : \"Everyone\", \"children\" : [");

		List<Integer> groupKeys = new ArrayList<>(groups.keySet());
		Collections.sort(groupKeys);
		Collections.reverse(groupKeys);

		int groupCount = 0;
		// create a nested treemap data where more matches (more bits set in string) appear higher in the hierarchy)
		for (Integer groupKey : groupKeys) {
			if (groupCount > 0) {
				treemapData.append(",");
			}

			treemapData.append(String.format("{\"name\" : \"Group %d\", \"children\" : [", groupKey));

			int groupItemCount = 0;
			for (Long[] groupItem : groups.get(groupKey)) {
				if (groupItemCount > 0) {
					treemapData.append(",");
				}

				//sb_treemap.Append("{\"name\": \"" + cohort_identifer + "\", \"size\": " + cohorts[cohort_identifer].ToString() + "}");
				treemapData.append(String.format("{\"name\": \"%s\", \"size\": %d}", formatBitMask(groupItem[0], inclusionRuleCount), groupItem[1]));
				groupItemCount++;
			}
			groupCount++;
		}

		treemapData.append(StringUtils.repeat("]}", groupCount + 1));

		return treemapData.toString();
	}

	public static class GenerateSqlRequest {

		public GenerateSqlRequest() {
		}

		@JsonProperty("expression")
		public CohortExpression expression;

		@JsonProperty("options")
		public CohortExpressionQueryBuilder.BuildExpressionQueryOptions options;

	}
	@Context
	ServletContext context;

	/**
	 * Returns OHDSI template SQL for a given cohort definition 
	 *
	 * @summary Generate Sql
	 * @param request A GenerateSqlRequest containing the cohort expression and options.
	 * @return The OHDSI template SQL needed to generate the input cohort definition as a character string
	 */
	@Path("sql")
	@POST
	@Produces(MediaType.APPLICATION_JSON)
	@Consumes(MediaType.APPLICATION_JSON)
	public GenerateSqlResult generateSql(GenerateSqlRequest request) {
		CohortExpressionQueryBuilder.BuildExpressionQueryOptions options = request.options;
		GenerateSqlResult result = new GenerateSqlResult();
		if (options == null) {
			options = new CohortExpressionQueryBuilder.BuildExpressionQueryOptions();
		}
		String expressionSql = queryBuilder.buildExpressionQuery(request.expression, options);
		result.templateSql = SqlRender.renderSql(expressionSql, null, null);

		return result;
	}

	/**
	 * Returns metadata about all cohort definitions in the WebAPI database
	 *
	 * @summary List Cohort Definitions
	 * @return List of metadata about all cohort definitions in WebAPI
	 * @see org.ohdsi.webapi.cohortdefinition.CohortMetadataDTO
	 */
	@GET
	@Path("/")
	@Produces(MediaType.APPLICATION_JSON)
	@Transactional
	@Cacheable(cacheNames = CachingSetup.COHORT_DEFINITION_LIST_CACHE, key = "@permissionService.getSubjectCacheKey()")
	public List<CohortMetadataDTO> getCohortDefinitionList() {
		List<CohortDefinition> definitions = cohortDefinitionRepository.list();
		return definitions.stream()
						.filter(!defaultGlobalReadPermissions ? entity -> permissionService.hasReadAccess(entity) : entity -> true)
						.map(def -> {
							CohortMetadataDTO dto = conversionService.convert(def, CohortMetadataImplDTO.class);
							permissionService.fillWriteAccess(def, dto);
							permissionService.fillReadAccess(def, dto);
