package org.ohdsi.webapi.cohortresults;

import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.ohdsi.circe.helper.ResourceHelper;
import org.ohdsi.sql.SqlRender;
import org.ohdsi.webapi.cohortanalysis.CohortAnalysisTask;
import org.ohdsi.webapi.cohortresults.mapper.*;
import org.ohdsi.webapi.source.Source;
import org.ohdsi.webapi.source.SourceDaimon;
import org.ohdsi.webapi.util.PreparedStatementRenderer;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import static org.ohdsi.webapi.util.SecurityUtils.whitelist;

public class CohortResultsAnalysisRunner {

	public static final String COHORT_SPECIFIC = "cohort_specific";
	public static final String COHORT_SPECIFIC_CONDITION_DRILLDOWN = "cohort_specific_condition_drilldown";
	public static final String COHORT_SPECIFIC_DRUG_DRILLDOWN = "cohort_specific_drug_drilldown";
	public static final String COHORT_SPECIFIC_PROCEDURE_DRILLDOWN = "cohort_specific_procedure_drilldown";
	public static final String COHORT_SPECIFIC_TREEMAP = "cohort_specific_treemap";
	public static final String CONDITION = "condition";
	public static final String CONDITION_DRILLDOWN = "condition_drilldown";
	public static final String CONDITION_ERA = "condition_era";
	public static final String CONDITION_ERA_DRILLDOWN = "condition_era_drilldown";
	public static final String DASHBOARD = "dashboard";
	public static final String DATA_DENSITY = "data_density";
	public static final String DEATH = "death";
	public static final String DEFAULT = "default";
	public static final String DRUG = "drug";
	public static final String DRUG_DRILLDOWN = "drug_drilldown";
	public static final String DRUG_ERA = "drug_era";
	public static final String DRUG_ERA_DRILLDOWN = "drug_era_drilldown";
	public static final String DRUG_EXPOSURE = "drug_exposure";
	public static final String HERACLES_HEEL = "heracles_heel";
	public static final String MEASUREMENT = "measurement";
	public static final String MEASUREMENT_DRILLDOWN = "measurement_drilldown";
	public static final String OBSERVATION = "observation";
	public static final String OBSERVATION_DRILLDOWN = "observation_drilldown";
	public static final String OBSERVATION_PERIOD = "observation_period";
	public static final String OBSERVATION_PERIODS = "observation_periods";
	public static final String PERSON = "person";
	public static final String PROCEDURE = "procedure";
	public static final String PROCEDURE_DRILLDOWN = "procedure_drilldown";
	public static final String VISIT = "visit";
	public static final String VISIT_DRILLDOWN = "visit_drilldown";
	
	private static final Logger log = LoggerFactory.getLogger(CohortResultsAnalysisRunner.class);
	
	public static final String BASE_SQL_PATH = "/resources/cohortresults/sql";

	private static final String[] STANDARD_COLUMNS = new String[]{"cdm_database_schema",
		"ohdsi_database_schema", "cohortDefinitionId",
		"minCovariatePersonCount", "minIntervalPersonCount"};

	private static final String[] DRILLDOWN_COLUMNS = new String[]{"cdm_database_schema",
		"ohdsi_database_schema", "cohortDefinitionId",
		"minCovariatePersonCount", "minIntervalPersonCount", "conceptId"};

	public static final Integer MIN_COVARIATE_PERSON_COUNT = 10;
	public static final Integer MIN_INTERVAL_PERSON_COUNT = 10;

	private ObjectMapper mapper;
	private String sourceDialect;
	private VisualizationDataRepository visualizationDataRepository;

	public CohortResultsAnalysisRunner(String sourceDialect, VisualizationDataRepository visualizationDataRepository, ObjectMapper objectMapper) {
		this.sourceDialect = sourceDialect;
		this.visualizationDataRepository = visualizationDataRepository;
		mapper = objectMapper;
	}

	public List<TornadoRecord> getTornadoRecords(JdbcTemplate jdbcTemplate, final int id, Source source) {
		final String sqlPath = BASE_SQL_PATH + "/tornado/getTornadoData.sql";
		String resultsTableQualifier = source.getTableQualifier(SourceDaimon.DaimonType.Results);

		String[] search = new String[]{"tableQualifier"};
		String[] replace = new String[]{resultsTableQualifier};

		String[] cols = new String[]{"cohortDefinitionId"};
		Object[] colValues = new Object[]{id};

		final PreparedStatementRenderer psr =  new PreparedStatementRenderer(source, sqlPath, search, replace, cols, colValues);
		return jdbcTemplate.query(psr.getSql(), psr.getSetter(), new TornadoMapper());
	}

	public List<ProfileSampleRecord> getProfileSampleRecords(JdbcTemplate jdbcTemplate, final int id, Source source) {
		final String sqlPath = BASE_SQL_PATH + "/tornado/getProfileSamples.sql";
		String resultsTableQualifier = source.getTableQualifier(SourceDaimon.DaimonType.Results);

		String[] search = new String[]{"tableQualifier"};
		String[] replace = new String[]{resultsTableQualifier};

		String[] cols = new String[]{"cohortDefinitionId"};
		Object[] colValues = new Object[]{id};

		final PreparedStatementRenderer psr =  new PreparedStatementRenderer(source, sqlPath, search, replace, cols, colValues);
		return jdbcTemplate.query(psr.getSql(), psr.getSetter(), new ProfileSampleMapper());
	}

	public List<ScatterplotRecord> getCohortConditionDrilldown(JdbcTemplate jdbcTemplate,
			final int id, 
			final int conceptId,
			final Integer minCovariatePersonCountParam,
			final Integer minIntervalPersonCountParam,
			Source source, 
			boolean save) {

		final String key = COHORT_SPECIFIC_CONDITION_DRILLDOWN;
		List<ScatterplotRecord> records = new ArrayList<>();

		final PreparedStatementRenderer psr = prepareDrillDownCohortSql("firstConditionRelativeToIndex", "cohortSpecific", id,
				conceptId, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (psr != null) {
			records = jdbcTemplate.query(psr.getSql(), psr.getSetter(), new ScatterplotMapper());
			if (save) {
				this.saveEntityDrilldown(id, source.getSourceId(), key, conceptId, records);
			}
		}

		return records;
	}

	public CohortDataDensity getCohortDataDensity(JdbcTemplate jdbcTemplate,
			final int id,
			final Integer minCovariatePersonCountParam,
			final Integer minIntervalPersonCountParam,
			Source source,
			boolean save) {

		CohortDataDensity data = new CohortDataDensity();
		final String key = DATA_DENSITY;
		boolean empty = true;

		PreparedStatementRenderer recordsPerPersonSql = this.renderTranslateCohortSql(BASE_SQL_PATH + "/datadensity/recordsperperson.sql", id,
				minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (recordsPerPersonSql != null) {
			data.setRecordsPerPerson(jdbcTemplate.query(recordsPerPersonSql.getSql(), recordsPerPersonSql.getSetter(), new SeriesPerPersonMapper()));
		}
		PreparedStatementRenderer totalRecordsSql = renderTranslateCohortSql(BASE_SQL_PATH + "/datadensity/totalrecords.sql", id,
				minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (totalRecordsSql != null) {
			data.setTotalRecords(jdbcTemplate.query(totalRecordsSql.getSql(), totalRecordsSql.getSetter(), new SeriesPerPersonMapper()));
		}
		PreparedStatementRenderer conceptsPerPersonSql = renderTranslateCohortSql(BASE_SQL_PATH + "/datadensity/conceptsperperson.sql", id,
				minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (conceptsPerPersonSql != null) {
			data.setConceptsPerPerson(jdbcTemplate.query(conceptsPerPersonSql.getSql(), conceptsPerPersonSql.getSetter(), new ConceptQuartileMapper()));
		}
		
		if (CollectionUtils.isNotEmpty(data.getRecordsPerPerson())
				|| CollectionUtils.isNotEmpty(data.getTotalRecords())
				|| CollectionUtils.isNotEmpty(data.getConceptsPerPerson())) {
			empty = false;
		}

		if (!empty && save) {
			saveEntity(id, source.getSourceId(), key, data);
		}

		return data;
	}

	public CohortDeathData getCohortDeathData(JdbcTemplate jdbcTemplate,
			final int id,
			final Integer minCovariatePersonCountParam,
			final Integer minIntervalPersonCountParam,
			Source source,
			boolean save) {
		CohortDeathData data = new CohortDeathData();
		final String key = DEATH;
		boolean empty = true;

		List<ConceptQuartileRecord> age = null;
		PreparedStatementRenderer ageSql = renderTranslateCohortSql(BASE_SQL_PATH + "/death/sqlAgeAtDeath.sql", id,
				minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (ageSql != null) {
			age = jdbcTemplate.query(ageSql.getSql(), ageSql.getSetter(), new ConceptQuartileMapper());
		}
		data.setAgetAtDeath(age);

		List<ConceptCountRecord> byType = null;
		PreparedStatementRenderer byTypeSql = renderTranslateCohortSql(BASE_SQL_PATH + "/death/sqlDeathByType.sql", id,
				minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (byTypeSql != null) {
			byType = jdbcTemplate.query(byTypeSql.getSql(), byTypeSql.getSetter(), new ConceptCountMapper());
		}
		data.setDeathByType(byType);

		List<ConceptDecileRecord> prevalenceByGenderAgeYear = null;
		PreparedStatementRenderer prevalenceGenderAgeSql = renderTranslateCohortSql(BASE_SQL_PATH + "/death/sqlPrevalenceByGenderAgeYear.sql", id,
				minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (prevalenceGenderAgeSql != null) {
			prevalenceByGenderAgeYear = jdbcTemplate.query(prevalenceGenderAgeSql.getSql(), prevalenceGenderAgeSql.getSetter(), new ConceptDecileCountsMapper());
		}
		data.setPrevalenceByGenderAgeYear(prevalenceByGenderAgeYear);

		List<PrevalenceRecord> prevalenceByMonth = null;
		PreparedStatementRenderer prevalanceMonthSql = renderTranslateCohortSql(BASE_SQL_PATH + "/death/sqlPrevalenceByMonth.sql", id,
				minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (prevalanceMonthSql != null) {
			prevalenceByMonth = jdbcTemplate.query(prevalanceMonthSql.getSql(), prevalanceMonthSql.getSetter(), new PrevalanceConceptMapper());
		}
		data.setPrevalenceByMonth(prevalenceByMonth);

		if (CollectionUtils.isNotEmpty(data.getAgetAtDeath())
				|| CollectionUtils.isNotEmpty(data.getDeathByType())
				|| CollectionUtils.isNotEmpty(data.getPrevalenceByGenderAgeYear())
				|| CollectionUtils.isNotEmpty(data.getPrevalenceByMonth())) {
			empty = false;
		}
		if (!empty && save) {
			this.saveEntity(id, source.getSourceId(), key, data);
		}

		return data;
	}

	public List<ScatterplotRecord> getCohortDrugDrilldown(JdbcTemplate jdbcTemplate,
			final int id, 
			final int conceptId,
			final Integer minCovariatePersonCountParam,
			final Integer minIntervalPersonCountParam,
			Source source,
			boolean save) {

		List<ScatterplotRecord> records = new ArrayList<>();
		final String key = COHORT_SPECIFIC_DRUG_DRILLDOWN;

		final PreparedStatementRenderer sql = prepareDrillDownCohortSql("drugOccursRelativeToIndex", "cohortSpecific", id,
				conceptId, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (sql != null) {
			records = jdbcTemplate.query(sql.getSql(), sql.getSetter(), new ScatterplotMapper());
			if (save) {
				saveEntityDrilldown(id, source.getSourceId(), key, conceptId, records);
			}
		}

		return records;
	}

	public List<HierarchicalConceptRecord> getCohortMeasurementResults(JdbcTemplate jdbcTemplate,
			final int id,
			final Integer minCovariatePersonCountParam,
			final Integer minIntervalPersonCountParam,
			Source source,
			boolean save) {
		List<HierarchicalConceptRecord> res = null;
		final String key = MEASUREMENT;

		PreparedStatementRenderer sql = renderTranslateCohortSql(BASE_SQL_PATH + "/measurement/sqlMeasurementTreemap.sql",
				id, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (sql != null) {
			res = jdbcTemplate.query(sql.getSql(), sql.getSetter(), new HierarchicalConceptMapper());
			if (save) {
				saveEntity(id, source.getSourceId(), key, res);
			}
		}
		return res;
	}

	public CohortMeasurementDrilldown getCohortMeasurementResultsDrilldown(JdbcTemplate jdbcTemplate,
			final int id, 
			final int conceptId,
			final Integer minCovariatePersonCountParam,
			final Integer minIntervalPersonCountParam,
			Source source,
			boolean save) {
		CohortMeasurementDrilldown drilldown = new CohortMeasurementDrilldown();
		final String key = MEASUREMENT_DRILLDOWN;
		boolean empty = true;

		PreparedStatementRenderer ageAtFirstOccurrenceSql = prepareDrillDownCohortSql("sqlAgeAtFirstOccurrence", MEASUREMENT, id,
				conceptId, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (ageAtFirstOccurrenceSql != null) {
			drilldown.setAgeAtFirstOccurrence(jdbcTemplate.query(ageAtFirstOccurrenceSql.getSql(), ageAtFirstOccurrenceSql.getSetter(), new ConceptQuartileMapper()));
		}

		PreparedStatementRenderer sqlLowerLimitDistribution = prepareDrillDownCohortSql("sqlLowerLimitDistribution", MEASUREMENT, id,
				conceptId, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (sqlLowerLimitDistribution != null) {
			drilldown.setLowerLimitDistribution(jdbcTemplate.query(sqlLowerLimitDistribution.getSql(), sqlLowerLimitDistribution.getSetter(), new ConceptQuartileMapper()));
		}

		PreparedStatementRenderer sqlMeasurementValueDistribution = prepareDrillDownCohortSql("sqlMeasurementValueDistribution", MEASUREMENT, id,
				conceptId, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (sqlMeasurementValueDistribution != null) {
			drilldown.setMeasurementValueDistribution(jdbcTemplate.query(sqlMeasurementValueDistribution.getSql(), sqlMeasurementValueDistribution.getSetter(), new ConceptQuartileMapper()));
		}

		PreparedStatementRenderer sqlUpperLimitDistribution = prepareDrillDownCohortSql("sqlUpperLimitDistribution", MEASUREMENT, id,
				conceptId, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (sqlUpperLimitDistribution != null) {
			drilldown.setUpperLimitDistribution(jdbcTemplate.query(sqlUpperLimitDistribution.getSql(), sqlUpperLimitDistribution.getSetter(), new ConceptQuartileMapper()));
		}

		PreparedStatementRenderer sqlMeasurementsByType = prepareDrillDownCohortSql("sqlMeasurementsByType", MEASUREMENT, id,
				conceptId, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (sqlMeasurementsByType != null) {
			drilldown.setMeasurementsByType(jdbcTemplate.query(sqlMeasurementsByType.getSql(), sqlMeasurementsByType.getSetter(), new ConceptObservationCountMapper()));
		}

		PreparedStatementRenderer sqlRecordsByUnit = prepareDrillDownCohortSql("sqlRecordsByUnit", MEASUREMENT, id,
				conceptId, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (sqlRecordsByUnit != null) {
			drilldown.setRecordsByUnit(jdbcTemplate.query(sqlRecordsByUnit.getSql(), sqlRecordsByUnit.getSetter(), new ConceptObservationCountMapper()));
		}

		PreparedStatementRenderer sqlValuesRelativeToNorm = prepareDrillDownCohortSql("sqlValuesRelativeToNorm", MEASUREMENT, id,
				conceptId, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (sqlValuesRelativeToNorm != null) {
			drilldown.setValuesRelativeToNorm(jdbcTemplate.query(sqlValuesRelativeToNorm.getSql(), sqlValuesRelativeToNorm.getSetter(), new ConceptObservationCountMapper()));
		}

		PreparedStatementRenderer sqlPrevalenceByGenderAgeYear = prepareDrillDownCohortSql("sqlPrevalenceByGenderAgeYear", MEASUREMENT, id,
				conceptId, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (sqlPrevalenceByGenderAgeYear != null) {
			drilldown.setPrevalenceByGenderAgeYear(jdbcTemplate.query(sqlPrevalenceByGenderAgeYear.getSql(), sqlPrevalenceByGenderAgeYear.getSetter(), new ConceptDecileMapper()));
		}

		List<PrevalenceRecord> prevalenceByMonth = null;
		PreparedStatementRenderer prevalanceMonthSql = prepareDrillDownCohortSql("sqlPrevalenceByMonth", MEASUREMENT, id,
				conceptId, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (prevalanceMonthSql != null) {
			prevalenceByMonth = jdbcTemplate.query(prevalanceMonthSql.getSql(), prevalanceMonthSql.getSetter(), new PrevalanceConceptNameMapper());
		}
		drilldown.setPrevalenceByMonth(prevalenceByMonth);

		if (CollectionUtils.isNotEmpty(drilldown.getAgeAtFirstOccurrence())
				|| CollectionUtils.isNotEmpty(drilldown.getLowerLimitDistribution())
				|| CollectionUtils.isNotEmpty(drilldown.getMeasurementsByType())
				|| CollectionUtils.isNotEmpty(drilldown.getMeasurementValueDistribution())
				|| CollectionUtils.isNotEmpty(drilldown.getPrevalenceByGenderAgeYear())
				|| CollectionUtils.isNotEmpty(drilldown.getPrevalenceByMonth())
				|| CollectionUtils.isNotEmpty(drilldown.getRecordsByUnit())
				|| CollectionUtils.isNotEmpty(drilldown.getUpperLimitDistribution())
				|| CollectionUtils.isNotEmpty(drilldown.getValuesRelativeToNorm()))  {
			empty = false;
		}
		
		if (!empty && save) {
			this.saveEntityDrilldown(id, source.getSourceId(), key, conceptId, drilldown);
		}

		return drilldown;
	}

	public CohortObservationPeriod getCohortObservationPeriod(final JdbcTemplate jdbcTemplate,
			final int id,
			final Integer minCovariatePersonCountParam,
			final Integer minIntervalPersonCountParam,
			Source source,
			boolean save) {
		CohortObservationPeriod obsPeriod = new CohortObservationPeriod();
		final String key = OBSERVATION_PERIOD;
		boolean empty = true;

		PreparedStatementRenderer ageAtFirstSql = renderTranslateCohortSql(BASE_SQL_PATH + "/observationperiod/ageatfirst.sql",
				id, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (ageAtFirstSql != null) {
			obsPeriod.setAgeAtFirst(jdbcTemplate.query(ageAtFirstSql.getSql(), ageAtFirstSql.getSetter(), new ConceptDistributionMapper()));
		}

		PreparedStatementRenderer obsLengthSql = renderTranslateCohortSql(BASE_SQL_PATH + "/observationperiod/observationlength_data.sql",
				id, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (obsLengthSql != null) {
			obsPeriod.setObservationLength(jdbcTemplate.query(obsLengthSql.getSql(), obsLengthSql.getSetter(), new ConceptDistributionMapper()));
		}

		PreparedStatementRenderer obsLengthStatsSql = renderTranslateCohortSql(BASE_SQL_PATH + "/observationperiod/observationlength_stats.sql", id, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (obsLengthStatsSql != null) {
			obsPeriod.setObservationLengthStats(jdbcTemplate.query(obsLengthStatsSql.getSql(), obsLengthStatsSql.getSetter(), new CohortStatsMapper()));
		}

		PreparedStatementRenderer obsYearStatsSql = renderTranslateCohortSql(BASE_SQL_PATH + "/observationperiod/observedbyyear_stats.sql", id, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (obsYearStatsSql != null) {
			obsPeriod.setPersonsWithContinuousObservationsByYearStats(jdbcTemplate.query(obsYearStatsSql.getSql(), obsYearStatsSql.getSetter(), new CohortStatsMapper()));
		}

		PreparedStatementRenderer personsWithContObsSql = renderTranslateCohortSql(BASE_SQL_PATH + "/observationperiod/observedbyyear_data.sql",
				id, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (personsWithContObsSql != null) {
			obsPeriod.setPersonsWithContinuousObservationsByYear(jdbcTemplate.query(personsWithContObsSql.getSql(), personsWithContObsSql.getSetter(), new ConceptDistributionMapper()));
		}

		PreparedStatementRenderer ageByGenderSql = renderTranslateCohortSql(BASE_SQL_PATH + "/observationperiod/agebygender.sql",
				id, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (ageByGenderSql != null) {
			obsPeriod.setAgeByGender(jdbcTemplate.query(ageByGenderSql.getSql(), ageByGenderSql.getSetter(), new ConceptQuartileMapper()));
		}

		PreparedStatementRenderer durationByGenderSql = renderTranslateCohortSql(BASE_SQL_PATH + "/observationperiod/observationlengthbygender.sql",
				id, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (durationByGenderSql != null) {
			obsPeriod.setDurationByGender(jdbcTemplate.query(durationByGenderSql.getSql(), durationByGenderSql.getSetter(), new ConceptQuartileMapper()));
		}

		PreparedStatementRenderer durationByAgeSql = renderTranslateCohortSql(BASE_SQL_PATH + "/observationperiod/observationlengthbyage.sql",
				id, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (durationByAgeSql != null) {
			obsPeriod.setDurationByAgeDecile(jdbcTemplate.query(durationByAgeSql.getSql(), durationByAgeSql.getSetter(), new ConceptQuartileMapper()));
		}

		PreparedStatementRenderer cumulObsSql = renderTranslateCohortSql(BASE_SQL_PATH + "/observationperiod/cumulativeduration.sql", id,
				minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (cumulObsSql != null) {
			obsPeriod.setCumulativeObservation(jdbcTemplate.query(cumulObsSql.getSql(), cumulObsSql.getSetter(), new CumulativeObservationMapper()));
		}

		PreparedStatementRenderer obsByMonthSql = renderTranslateCohortSql(BASE_SQL_PATH + "/observationperiod/observedbymonth.sql", id,
				minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (obsByMonthSql != null) {
			obsPeriod.setObservedByMonth(jdbcTemplate.query(obsByMonthSql.getSql(), obsByMonthSql.getSetter(), new MonthObservationMapper()));
		}

		PreparedStatementRenderer obsPeriodsPerPersonSql = renderTranslateCohortSql(BASE_SQL_PATH + "/observationperiod/periodsperperson.sql", id, minCovariatePersonCountParam,
				minIntervalPersonCountParam, source);
		if (obsPeriodsPerPersonSql != null) {
			obsPeriod.setObservationPeriodsPerPerson(jdbcTemplate.query(obsPeriodsPerPersonSql.getSql(), obsPeriodsPerPersonSql.getSetter(), new ConceptCountMapper()));
		}
		
		if (CollectionUtils.isNotEmpty(obsPeriod.getAgeAtFirst())
				|| CollectionUtils.isNotEmpty(obsPeriod.getAgeByGender())
				|| CollectionUtils.isNotEmpty(obsPeriod.getCumulativeObservation())
				|| CollectionUtils.isNotEmpty(obsPeriod.getDurationByAgeDecile())
				|| CollectionUtils.isNotEmpty(obsPeriod.getDurationByGender())
				|| CollectionUtils.isNotEmpty(obsPeriod.getObservationLength())
				|| CollectionUtils.isNotEmpty(obsPeriod.getObservationLengthStats())
				|| CollectionUtils.isNotEmpty(obsPeriod.getObservationPeriodsPerPerson())
				|| CollectionUtils.isNotEmpty(obsPeriod.getObservedByMonth())
				|| CollectionUtils.isNotEmpty(obsPeriod.getPersonsWithContinuousObservationsByYear())
				|| CollectionUtils.isNotEmpty(obsPeriod.getPersonsWithContinuousObservationsByYearStats())) {
			empty = false;
		}

		if (!empty && save) {
			this.saveEntity(id, source.getSourceId(), key, obsPeriod);
		}

		return obsPeriod;
	}

	public List<HierarchicalConceptRecord> getCohortObservationResults(JdbcTemplate jdbcTemplate,
			final int id,
			final Integer minCovariatePersonCountParam,
			final Integer minIntervalPersonCountParam,
			Source source,
			boolean save) {

		List<HierarchicalConceptRecord> res = null;

		final String key = OBSERVATION;
		PreparedStatementRenderer sql = renderTranslateCohortSql(BASE_SQL_PATH + "/observation/sqlObservationTreemap.sql",
				id, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (sql != null) {
			res = jdbcTemplate.query(sql.getSql(), sql.getSetter(), new HierarchicalConceptMapper());
			if (save) {
				saveEntity(id, source.getSourceId(), key, res);
			}
		}

		return res;
	}

	public CohortObservationDrilldown getCohortObservationResultsDrilldown(JdbcTemplate jdbcTemplate,
			final int id, 
			final int conceptId,
			final Integer minCovariatePersonCountParam,
			final Integer minIntervalPersonCountParam,
			Source source,
			boolean save) {
		CohortObservationDrilldown drilldown = new CohortObservationDrilldown();
		final String key = OBSERVATION_DRILLDOWN;
		boolean empty = true;

		PreparedStatementRenderer ageAtFirstOccurrenceSql = prepareDrillDownCohortSql("sqlAgeAtFirstOccurrence", OBSERVATION, id,
				conceptId, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (ageAtFirstOccurrenceSql != null) {
			drilldown.setAgeAtFirstOccurrence(jdbcTemplate.query(ageAtFirstOccurrenceSql.getSql(), ageAtFirstOccurrenceSql.getSetter(), new ConceptQuartileMapper()));
		}

		PreparedStatementRenderer sqlObservationValueDistribution = prepareDrillDownCohortSql("sqlObservationValueDistribution", OBSERVATION, id,
				conceptId, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (sqlObservationValueDistribution != null) {
			drilldown.setObservationValueDistribution(jdbcTemplate.query(sqlObservationValueDistribution.getSql(), sqlObservationValueDistribution.getSetter(), new ConceptQuartileMapper()));
		}

		PreparedStatementRenderer sqlObservationsByType = prepareDrillDownCohortSql("sqlObservationsByType", OBSERVATION, id,
				conceptId, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (sqlObservationsByType != null) {
			drilldown.setObservationsByType(jdbcTemplate.query(sqlObservationsByType.getSql(), sqlObservationsByType.getSetter(), new ConceptObservationCountMapper()));
		}

		PreparedStatementRenderer sqlRecordsByUnit = prepareDrillDownCohortSql("sqlRecordsByUnit", OBSERVATION, id,
				conceptId, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (sqlRecordsByUnit != null) {
			drilldown.setRecordsByUnit(jdbcTemplate.query(sqlRecordsByUnit.getSql(), sqlRecordsByUnit.getSetter(), new ConceptObservationCountMapper()));
		}

		PreparedStatementRenderer sqlPrevalenceByGenderAgeYear = prepareDrillDownCohortSql("sqlPrevalenceByGenderAgeYear", OBSERVATION, id,
				conceptId, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (sqlPrevalenceByGenderAgeYear != null) {
			drilldown.setPrevalenceByGenderAgeYear(jdbcTemplate.query(sqlPrevalenceByGenderAgeYear.getSql(), sqlPrevalenceByGenderAgeYear.getSetter(), new ConceptDecileMapper()));
		}

		List<PrevalenceRecord> prevalenceByMonth = null;
		PreparedStatementRenderer prevalanceMonthSql = prepareDrillDownCohortSql("sqlPrevalenceByMonth", OBSERVATION, id,
				conceptId, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (prevalanceMonthSql != null) {
			prevalenceByMonth = jdbcTemplate.query(prevalanceMonthSql.getSql(), prevalanceMonthSql.getSetter(), new PrevalanceConceptNameMapper());
		}
		drilldown.setPrevalenceByMonth(prevalenceByMonth);
		
		if (CollectionUtils.isNotEmpty(drilldown.getAgeAtFirstOccurrence())
				|| CollectionUtils.isNotEmpty(drilldown.getLowerLimitDistribution())
				|| CollectionUtils.isNotEmpty(drilldown.getObservationsByType())
				|| CollectionUtils.isNotEmpty(drilldown.getObservationValueDistribution())
				|| CollectionUtils.isNotEmpty(drilldown.getPrevalenceByGenderAgeYear())
				|| CollectionUtils.isNotEmpty(drilldown.getPrevalenceByMonth())
				|| CollectionUtils.isNotEmpty(drilldown.getRecordsByUnit())
				|| CollectionUtils.isNotEmpty(drilldown.getUpperLimitDistribution())
				|| CollectionUtils.isNotEmpty(drilldown.getValuesRelativeToNorm())) {
			empty = false;
		}
		
		if (!empty && save) {
			this.saveEntityDrilldown(id, source.getSourceId(), key, conceptId, drilldown);
		}
		return drilldown;

	}

	public List<ScatterplotRecord> getCohortProcedureDrilldown(JdbcTemplate jdbcTemplate,
			final int id, 
			final int conceptId,
			final Integer minCovariatePersonCountParam,
			final Integer minIntervalPersonCountParam,
			Source source,
			boolean save) {

		List<ScatterplotRecord> records = new ArrayList<>();
		final String key = COHORT_SPECIFIC_PROCEDURE_DRILLDOWN;

		final PreparedStatementRenderer sql = prepareDrillDownCohortSql("procedureOccursRelativeToIndex", "cohortSpecific", id,
				conceptId, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (sql != null) {
			records = jdbcTemplate.query(sql.getSql(), sql.getSetter(), new ScatterplotMapper());
			if (save) {
				saveEntityDrilldown(id, source.getSourceId(), key, conceptId, records);
			}
		}
		return records;
	}

	public CohortProceduresDrillDown getCohortProceduresDrilldown(JdbcTemplate jdbcTemplate,
			final int id,
			final int conceptId,
			final Integer minCovariatePersonCountParam,
			final Integer minIntervalPersonCountParam,
			Source source,
			boolean save) {
		CohortProceduresDrillDown drilldown = new CohortProceduresDrillDown();
		final String key = PROCEDURE_DRILLDOWN;
		boolean empty = true;
		
		List<ConceptQuartileRecord> ageAtFirst = null;
		PreparedStatementRenderer ageAtFirstSql = prepareDrillDownCohortSql("sqlAgeAtFirstOccurrence", PROCEDURE, id,
				conceptId, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (ageAtFirstSql != null) {
			ageAtFirst = jdbcTemplate.query(ageAtFirstSql.getSql(), ageAtFirstSql.getSetter(), new ConceptQuartileMapper());
		}
		drilldown.setAgeAtFirstOccurrence(ageAtFirst);

		List<ConceptCountRecord> byType = null;
		PreparedStatementRenderer byTypeSql = prepareDrillDownCohortSql("sqlProceduresByType", PROCEDURE, id,
				conceptId, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (byTypeSql != null) {
			byType = jdbcTemplate.query(byTypeSql.getSql(), byTypeSql.getSetter(), new ConceptCountMapper());
		}
		drilldown.setProceduresByType(byType);

		List<ConceptDecileRecord> prevalenceByGenderAgeYear = null;
		PreparedStatementRenderer prevalenceGenderAgeSql = prepareDrillDownCohortSql("sqlPrevalenceByGenderAgeYear", PROCEDURE, id,
				conceptId, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (prevalenceGenderAgeSql != null) {
			prevalenceByGenderAgeYear = jdbcTemplate.query(prevalenceGenderAgeSql.getSql(), prevalenceGenderAgeSql.getSetter(), new ConceptDecileMapper());
		}
		drilldown.setPrevalenceByGenderAgeYear(prevalenceByGenderAgeYear);

		List<PrevalenceRecord> prevalenceByMonth = null;
		PreparedStatementRenderer prevalanceMonthSql = prepareDrillDownCohortSql("sqlPrevalenceByMonth", PROCEDURE, id,
				conceptId, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (prevalanceMonthSql != null) {
			prevalenceByMonth = jdbcTemplate.query(prevalanceMonthSql.getSql(), prevalanceMonthSql.getSetter(), new PrevalanceConceptMapper());
		}
		drilldown.setPrevalenceByMonth(prevalenceByMonth);
		
		if (CollectionUtils.isNotEmpty(drilldown.getAgeAtFirstOccurrence())
				|| CollectionUtils.isNotEmpty(drilldown.getPrevalenceByGenderAgeYear())
				|| CollectionUtils.isNotEmpty(drilldown.getPrevalenceByMonth())
				|| CollectionUtils.isNotEmpty(drilldown.getProceduresByType())) {
			empty = false;
		}

		if (!empty && save) {
			saveEntityDrilldown(id, source.getSourceId(), key, conceptId, drilldown);
		}

		return drilldown;
	}

	public CohortSpecificSummary getCohortSpecificSummary(JdbcTemplate jdbcTemplate,
			final int id,
			final Integer minCovariatePersonCountParam,
			final Integer minIntervalPersonCountParam,
			Source source, 
			boolean save) {
		final String key = COHORT_SPECIFIC;
		CohortSpecificSummary summary = new CohortSpecificSummary();
		boolean empty = true;
		
		// 1805, 1806
		PreparedStatementRenderer personsByDurationSql = renderTranslateCohortSql(BASE_SQL_PATH + "/cohortSpecific/observationPeriodTimeRelativeToIndex.sql", id, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (personsByDurationSql != null) {
			summary.setPersonsByDurationFromStartToEnd(jdbcTemplate.query(personsByDurationSql.getSql(), personsByDurationSql.getSetter(), new ObservationPeriodMapper()));
		}

		// 1815
		PreparedStatementRenderer monthPrevalenceSql = renderTranslateCohortSql(BASE_SQL_PATH + "/cohortSpecific/prevalenceByMonth.sql", id, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (monthPrevalenceSql != null) {
			summary.setPrevalenceByMonth(jdbcTemplate.query(monthPrevalenceSql.getSql(), monthPrevalenceSql.getSetter(), new PrevalanceMapper()));
		}

		// 1814
		List<ConceptDecileRecord> prevalenceByGenderAgeYear = null;
		PreparedStatementRenderer prevalenceGenderAgeSql = renderTranslateCohortSql(BASE_SQL_PATH + "/cohortSpecific/prevalenceByYearGenderSex.sql", id, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (prevalenceGenderAgeSql != null) {
			prevalenceByGenderAgeYear = jdbcTemplate.query(prevalenceGenderAgeSql.getSql(), prevalenceGenderAgeSql.getSetter(), new ConceptDecileCountsMapper());
		}
		summary.setNumPersonsByCohortStartByGenderByAge(prevalenceByGenderAgeYear);

		// 1801
		List<ConceptQuartileRecord> ageAtIndex = null;
		PreparedStatementRenderer ageAtIndexSql = renderTranslateCohortSql(BASE_SQL_PATH + "/cohortSpecific/ageAtIndexDistribution.sql", id, minCovariatePersonCountParam, minIntervalPersonCountParam, source);
		if (ageAtIndexSql != null) {
