EDIT

/**

* Copyright (C) 2009 EDIT

* European Distributed Institute of Taxonomy

* http://www.e-taxonomy.eu

*

* The contents of this file are subject to the Mozilla Public License Version 1.1

* See LICENSE.TXT at the top of this package for the full license terms.

*/

package eu.etaxonomy.cdm.io.pesi.out;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Types;

import java.util.ArrayList;

import java.util.EnumSet;

import java.util.HashMap;

import java.util.Iterator;

import java.util.List;

import java.util.Map;

import java.util.Set;

import java.util.UUID;

import java.util.regex.Matcher;

import java.util.regex.Pattern;

import org.apache.commons.lang.StringUtils;

import org.apache.log4j.Logger;

import org.joda.time.DateTime;

import org.joda.time.format.DateTimeFormat;

import org.joda.time.format.DateTimeFormatter;

import org.springframework.stereotype.Component;

import org.springframework.transaction.TransactionStatus;

import eu.etaxonomy.cdm.api.service.TaxonServiceImpl;

import eu.etaxonomy.cdm.common.CdmUtils;

import eu.etaxonomy.cdm.io.berlinModel.BerlinModelTransformer;

import eu.etaxonomy.cdm.io.common.Source;

import eu.etaxonomy.cdm.io.common.mapping.UndefinedTransformerMethodException;

import eu.etaxonomy.cdm.io.common.mapping.out.DbConstantMapper;

import eu.etaxonomy.cdm.io.common.mapping.out.DbExtensionMapper;

import eu.etaxonomy.cdm.io.common.mapping.out.DbLastActionMapper;

import eu.etaxonomy.cdm.io.common.mapping.out.DbObjectMapper;

import eu.etaxonomy.cdm.io.common.mapping.out.DbStringMapper;

import eu.etaxonomy.cdm.io.common.mapping.out.IdMapper;

import eu.etaxonomy.cdm.io.common.mapping.out.MethodMapper;

import eu.etaxonomy.cdm.io.common.mapping.out.ObjectChangeMapper;

import eu.etaxonomy.cdm.io.pesi.erms.ErmsTransformer;

import eu.etaxonomy.cdm.model.common.AnnotationType;

import eu.etaxonomy.cdm.model.common.CdmBase;

import eu.etaxonomy.cdm.model.common.Extension;

import eu.etaxonomy.cdm.model.common.ExtensionType;

import eu.etaxonomy.cdm.model.common.IdentifiableEntity;

import eu.etaxonomy.cdm.model.common.IdentifiableSource;

import eu.etaxonomy.cdm.model.common.Marker;

import eu.etaxonomy.cdm.model.common.MarkerType;

import eu.etaxonomy.cdm.model.common.RelationshipBase;

import eu.etaxonomy.cdm.model.name.NameTypeDesignation;

import eu.etaxonomy.cdm.model.name.NameTypeDesignationStatus;

import eu.etaxonomy.cdm.model.name.NomenclaturalCode;

import eu.etaxonomy.cdm.model.name.NomenclaturalStatus;

import eu.etaxonomy.cdm.model.name.Rank;

import eu.etaxonomy.cdm.model.name.TaxonName;

import eu.etaxonomy.cdm.model.reference.INomenclaturalReference;

import eu.etaxonomy.cdm.model.reference.Reference;

import eu.etaxonomy.cdm.model.taxon.Classification;

import eu.etaxonomy.cdm.model.taxon.Synonym;

import eu.etaxonomy.cdm.model.taxon.SynonymType;

import eu.etaxonomy.cdm.model.taxon.Taxon;

import eu.etaxonomy.cdm.model.taxon.TaxonBase;

import eu.etaxonomy.cdm.model.taxon.TaxonNode;

import eu.etaxonomy.cdm.model.taxon.TaxonRelationship;

import eu.etaxonomy.cdm.model.taxon.TaxonRelationshipType;

import eu.etaxonomy.cdm.strategy.cache.HTMLTagRules;

import eu.etaxonomy.cdm.strategy.cache.TagEnum;

import eu.etaxonomy.cdm.strategy.cache.name.INonViralNameCacheStrategy;

import eu.etaxonomy.cdm.strategy.cache.name.TaxonNameDefaultCacheStrategy;

import eu.etaxonomy.cdm.strategy.cache.name.ZooNameNoMarkerCacheStrategy;

/**

 * The export class for {@link eu.etaxonomy.cdm.model.name.TaxonNameBase TaxonNames}.<p>

 * Inserts into DataWarehouse database table <code>Taxon</code>.

 * It is divided into four phases:<p><ul>

 * <li>Phase 1:	Export of all {@link eu.etaxonomy.cdm.model.name.TaxonName TaxonNames} except some data exported in the following phases.

 * <li>Phase 2:	Export of additional data: ParentTaxonFk and TreeIndex.

 * <li>Phase 3:	Export of additional data: Rank data, KingdomFk, TypeNameFk, expertFk and speciesExpertFk.

 * <li>Phase 4:	Export of Inferred Synonyms.</ul>

 *

 * @author e.-m.lee

 * @since 23.02.2010

 */

@Component

public class PesiTaxonExport extends PesiExportBase {

    private static final long serialVersionUID = -3412722058790200078L;

    private static final Logger logger = Logger.getLogger(PesiTaxonExport.class);

	private static final Class<? extends CdmBase> standardMethodParameter = TaxonBase.class;

	private static int modCount = 1000;

	private static final String dbTableName = "Taxon";

	private static final String dbTableNameSynRel = "RelTaxon";

	private static final String dbTableAdditionalSourceRel = "AdditionalTaxonSource";

	private static final String pluralString = "Taxa";

	private static final String parentPluralString = "Taxa";

	private PreparedStatement parentTaxonFk_TreeIndex_KingdomFkStmt;

	private PreparedStatement parentTaxonFkStmt;

	private PreparedStatement rankTypeExpertsUpdateStmt;

	private PreparedStatement rankUpdateStmt;

	private Integer kingdomFk;

	private AnnotationType treeIndexAnnotationType;

	private static ExtensionType lastActionExtensionType;

	private static ExtensionType lastActionDateExtensionType;

	private static ExtensionType expertNameExtensionType;

	private static ExtensionType speciesExpertNameExtensionType;

	private static ExtensionType cacheCitationExtensionType;

	public static TaxonNameDefaultCacheStrategy zooNameStrategy = ZooNameNoMarkerCacheStrategy.NewInstance();

	public static TaxonNameDefaultCacheStrategy nonViralNameStrategy = TaxonNameDefaultCacheStrategy.NewInstance();

	private static int currentTaxonId;

	protected AnnotationType getTreeIndexAnnotationType() {

		return treeIndexAnnotationType;

	}

	protected void setTreeIndexAnnotationType(AnnotationType treeIndexAnnotationType) {

		this.treeIndexAnnotationType = treeIndexAnnotationType;

	}

	enum NamePosition {

		beginning,

		end,

		between,

		alone,

		nowhere

	}

	public PesiTaxonExport() {

		super();

	}

	@Override

	public Class<? extends CdmBase> getStandardMethodParameter() {

		return standardMethodParameter;

	}

	@Override

	protected void doInvoke(PesiExportState state) {

		try {

			logger.info("*** Started Making " + pluralString + " ...");

			initPreparedStatements(state);

			// Stores whether this invoke was successful or not.

			boolean success = true;

			// PESI: Clear the database table Taxon.

			doDelete(state);

			// Get specific mappings: (CDM) Taxon -> (PESI) Taxon

			PesiExportMapping mapping = getMapping();

			PesiExportMapping synonymRelMapping = getSynRelMapping();

			PesiExportMapping additionalSourceMapping = getAdditionalSourceMapping(state);

			// Initialize the db mapper

			mapping.initialize(state);

			synonymRelMapping.initialize(state);

			additionalSourceMapping.initialize(state);

			// Find extensionTypes

			lastActionExtensionType = (ExtensionType)getTermService().find(PesiTransformer.uuidExtLastAction);

			lastActionDateExtensionType = (ExtensionType)getTermService().find(PesiTransformer.uuidExtLastActionDate);

			expertNameExtensionType = (ExtensionType)getTermService().find(PesiTransformer.uuidExtExpertName);

			speciesExpertNameExtensionType = (ExtensionType)getTermService().find(PesiTransformer.uuidExtSpeciesExpertName);

			cacheCitationExtensionType = (ExtensionType)getTermService().find(PesiTransformer.uuidExtCacheCitation);

			//Export Taxa..

			success &= doPhase01(state, mapping, additionalSourceMapping);

			//"PHASE 1b: Handle names without taxa ...

			success &= doNames(state, additionalSourceMapping);

			// 2nd Round: Add ParentTaxonFk to each taxon

			success &= doPhase02(state);

			//PHASE 3: Add Rank data, KingdomFk, TypeNameFk ...

			success &= doPhase03(state);

			// 4nd Round: Add TreeIndex to each taxon

			success &= doPhase04(state);

			//"PHASE 5: Creating Inferred Synonyms...

			success &= doPhase05(state, mapping, synonymRelMapping);

			logger.info("*** Finished Making " + pluralString + " ..." + getSuccessString(success));

			if (!success){

				state.getResult().addError("An error occurred in PesiTaxonExport.doInvoke. Success = false");

			}

			return;

		} catch (Exception e) {

			e.printStackTrace();

			logger.error(e.getMessage());

			state.getResult().addException(e);

		}

	}

	private void initPreparedStatements(PesiExportState state) throws SQLException {

		initTreeIndexStatement(state);

		initRankExpertsUpdateStmt(state);

		initRankUpdateStatement(state);

		initParentFkStatement(state);

	}

	// Prepare TreeIndex-And-KingdomFk-Statement

	private void initTreeIndexStatement(PesiExportState state) throws SQLException {

		Connection connection = state.getConfig().getDestination().getConnection();

		String parentTaxonFk_TreeIndex_KingdomFkSql = "UPDATE Taxon SET ParentTaxonFk = ?, TreeIndex = ? WHERE TaxonId = ?";

		parentTaxonFk_TreeIndex_KingdomFkStmt = connection.prepareStatement(parentTaxonFk_TreeIndex_KingdomFkSql);

	}

	// Prepare TreeIndex-And-KingdomFk-Statement

	private void initParentFkStatement(PesiExportState state) throws SQLException {

		Connection connection = state.getConfig().getDestination().getConnection();

		String parentTaxonFkSql = "UPDATE Taxon SET ParentTaxonFk = ? WHERE TaxonId = ?";

		parentTaxonFkStmt = connection.prepareStatement(parentTaxonFkSql);

	}

	private void initRankUpdateStatement(PesiExportState state) throws SQLException {

		Connection connection = state.getConfig().getDestination().getConnection();

		String rankSql = "UPDATE Taxon SET RankFk = ?, RankCache = ?, KingdomFk = ? WHERE TaxonId = ?";

		rankUpdateStmt = connection.prepareStatement(rankSql);

	}

	private void initRankExpertsUpdateStmt(PesiExportState state) throws SQLException {

//		String sql_old = "UPDATE Taxon SET RankFk = ?, RankCache = ?, TypeNameFk = ?, KingdomFk = ?, " +

//				"ExpertFk = ?, SpeciesExpertFk = ? WHERE TaxonId = ?";

		//TODO handle experts GUIDs

		Connection connection = state.getConfig().getDestination().getConnection();

		String sql = "UPDATE Taxon SET RankFk = ?, RankCache = ?, TypeNameFk = ?, KingdomFk = ? " +

				" WHERE TaxonId = ?";

		rankTypeExpertsUpdateStmt = connection.prepareStatement(sql);

	}

	private boolean doPhase01(PesiExportState state, PesiExportMapping mapping, PesiExportMapping additionalSourceMapping){

		int count = 0;

		int pastCount = 0;

		boolean success = true;

		// Get the limit for objects to save within a single transaction.

		int limit = state.getConfig().getLimitSave();

		logger.info("PHASE 1: Export Taxa...limit is " + limit);

		// Start transaction

		TransactionStatus txStatus = startTransaction(true);

		if (logger.isDebugEnabled()) {

            logger.info("Started new transaction. Fetching some " + pluralString + " (max: " + limit + ") ...");

            logger.info("Taking snapshot at the beginning of phase 1 of taxonExport");

            //ProfilerController.memorySnapshot();

        }

		int partitionCount = 0;

		List<TaxonBase<?>> list;

		while ((list = getNextTaxonPartition(null, limit, partitionCount++, null)) != null   ) {

			logger.debug("Fetched " + list.size() + " " + pluralString + ". Exporting...");

			for (TaxonBase<?> taxon : list) {

				doCount(count++, modCount, pluralString);

				TaxonName taxonName = taxon.getName();

				TaxonName nvn = CdmBase.deproxy(taxonName);

				if (! nvn.isProtectedTitleCache()){

					nvn.setTitleCache(null, false);

				}

				if (! nvn.isProtectedNameCache()){

					nvn.setNameCache(null, false);

				}

				if (! nvn.isProtectedFullTitleCache()){

					nvn.setFullTitleCache(null, false);

				}

				if (! nvn.isProtectedAuthorshipCache()){

					nvn.setAuthorshipCache(null, false);

				}

				try{

    				if (nvn.getRank().equals(Rank.KINGDOM())){

    				    if(taxon.isInstanceOf(Taxon.class)){

    				        String treeIndex = ((Taxon)taxon).getTaxonNodes().iterator().next().treeIndex();

    				        Integer kingdomId = PesiTransformer.pesiKingdomMap.get(nvn.getGenusOrUninomial());

    				        state.getTreeIndexKingdomMap().put(treeIndex, kingdomId);

    				    }else{

    				        logger.warn("Kingdom taxon is not of class Taxon but " + taxon.getClass().getSimpleName() + ": " + nvn.getGenusOrUninomial());

    				    }

    				}

				}catch(NullPointerException e){

				    logger.error(nvn.getTitleCache() + " has no Rank!");

				    System.err.println(nvn.getTitleCache() + " has no Rank!");

				}

				//core mapping

				success &= mapping.invoke(taxon);

				//additional source

				if (nvn.getNomenclaturalReference() != null || StringUtils.isNotBlank(nvn.getNomenclaturalMicroReference() )){

					additionalSourceMapping.invoke(taxon);

				}

				//TODO switch on again, leads to some warnings in ERMS for taxa of not correctly handled kingdoms

				validatePhaseOne(taxon, nvn);

			}

			// Commit transaction

			commitTransaction(txStatus);

			logger.info("Exported " + (count - pastCount) + " " + pluralString + ". Total: " + count + " (Phase 01)");

			pastCount = count;

			// Start new transaction

			txStatus = startTransaction(true);

			if (logger.isDebugEnabled()) {

                logger.info("Started new transaction. Fetching some " + pluralString + " (max: " + limit + ") ...");

            }

		}

		logger.debug("No " + pluralString + " left to fetch.");

		// Commit transaction

		commitTransaction(txStatus);

		txStatus = null;

		return success;

	}

	private void validatePhaseOne(TaxonBase<?> taxon, TaxonName taxonName) {

	    // Check whether some rules are violated

		String genusOrUninomial = taxonName.getGenusOrUninomial();

		String specificEpithet = taxonName.getSpecificEpithet();

		String infraSpecificEpithet = taxonName.getInfraSpecificEpithet();

		String infraGenericEpithet = taxonName.getInfraGenericEpithet();

		Rank rank =  taxonName.getRank();

		//as kingdomFk can not be defined in Phase 01 the below code was switched to use the CDM rank. This may be changed if we move validation to Phase03 or later

//		Integer rankFk = getRankFk(taxonName, taxonName.getNameType());

//		if (rankFk == null) {

//			logger.error("Rank was not determined: " + taxon.getUuid() + " (" + taxon.getTitleCache() + ")");

//		} else {

			// Check whether infraGenericEpithet is set correctly

			// 1. Childs of an accepted taxon of rank subgenus that are accepted taxa of rank species have to have an infraGenericEpithet

			// 2. Grandchilds of an accepted taxon of rank subgenus that are accepted taxa of rank subspecies have to have an infraGenericEpithet

			int ancestorLevel = 0;

			if (taxonName.getRank().equals(Rank.SUBSPECIES())) {

				// The accepted taxon two rank levels above should be of rank subgenus

				ancestorLevel  = 2;

			}

			if (taxonName.getRank().equals(Rank.SPECIES())) {

				// The accepted taxon one rank level above should be of rank subgenus

				ancestorLevel = 1;

			}

			if (ancestorLevel > 0) {

				if (validateAncestorOfSpecificRank(taxon, ancestorLevel, Rank.SUBGENUS())) {

					// The child (species or subspecies) of this parent (subgenus) has to have an infraGenericEpithet

					if (infraGenericEpithet == null) {

						logger.warn("InfraGenericEpithet for (sub)species of infrageneric taxon does not exist even though it should (also valid for Botanical Names?) for: " + taxon.getUuid() + " (" + taxon.getTitleCache() + ")");

						// maybe the taxon could be named here

					}

				}

			}

			if (infraGenericEpithet == null && rank.isInfraGenericButNotSpeciesGroup()) {

				logger.warn("InfraGenericEpithet was not determined although it should exist for infra generic names: " + taxon.getUuid() + " (" + taxon.getTitleCache() + ")");

			}

			if (specificEpithet != null && (rank.isInfraGenericButNotSpeciesGroup()||rank.isGenus()||rank.isSupraGeneric())) {

				logger.warn("SpecificEpithet was determined for rank " + rank.getTitleCache() + " although it should only exist for species aggregates, species or infraspecific taxa: TaxonName " + taxonName.getUuid() + " (" + taxonName.getTitleCache() + ")");

			}

			if (infraSpecificEpithet != null && !rank.isInfraSpecific()) {

				String message = "InfraSpecificEpithet '" +infraSpecificEpithet + "' was determined for rank " + rank.getTitleCache() + " although it should only exist for rank species and higher: "  + taxonName.getUuid() + " (" + taxonName.getTitleCache() + ")";

				if (StringUtils.isNotBlank(infraSpecificEpithet)){

					logger.warn(message);

				}else{

					logger.warn(message);

				}

			}

//		}

		if (infraSpecificEpithet != null && specificEpithet == null) {

			logger.warn("An infraSpecificEpithet was determined, but a specificEpithet was not determined: "  + taxon.getUuid() + " (" + taxon.getTitleCache() + ")");

		}

		if (genusOrUninomial == null) {

			logger.warn("GenusOrUninomial was not determined: " + taxon.getUuid() + " (" + taxon.getTitleCache() + ")");

		}

	}

	/**

	 * 2nd Round: Add ParentTaxonFk to each taxon and add Biota if not exists

	 */

	private boolean doPhase02(PesiExportState state) {

		int count = 0;

		int pastCount = 0;

		boolean success = true;

		if (! state.getConfig().isDoParentAndBiota()){

			logger.info ("Ignore PHASE 2: Make ParentFk and Biota...");

			return success;

		}

		// Get the limit for objects to save within a single transaction.

		int limit = state.getConfig().getLimitSave();

		insertBiota(state);

		logger.info("PHASE 2: Make ParentFk and Biota ... limit is " + limit);

		// Start transaction

		TransactionStatus txStatus = startTransaction(true);

		int partitionCount = 0;

//		ProfilerController.memorySnapshot();

		List<Taxon> list;

		while ((list = getNextTaxonPartition(Taxon.class, limit, partitionCount++, null)) != null   ) {

			if(logger.isDebugEnabled()) {

                logger.info("Fetched " + list.size() + " " + pluralString + ". Exporting...");

            }

			for (Taxon taxon : list) {

				for (TaxonNode node : taxon.getTaxonNodes()){

					doCount(count++, modCount, pluralString);

					TaxonNode parentNode = node.getParent();

					if (parentNode != null && parentNode.getTaxon() != null){  //new root node handling requires has root taxon with taxon == null

						int childId = state.getDbId( taxon);

						int parentId = state.getDbId(parentNode.getTaxon());

						success &= invokeParentTaxonFk(parentId, childId);

					}

				}

			}

			// Commit transaction

			commitTransaction(txStatus);

			logger.info("Exported " + (count - pastCount) + " " + pluralString + ". Total: " + count + " (Phase 2)");

			pastCount = count;

			// Start transaction

			txStatus = startTransaction(true);

			if (logger.isDebugEnabled()){

			    logger.info("Started new transaction. Fetching some " + pluralString + " (max: " + limit + ") ...");

			}

		}

		logger.debug("No " + pluralString + " left to fetch.");

		// Commit transaction

		commitTransaction(txStatus);

		return success;

	}

	/**

	 * Inserts the Biota Taxon if not yet exists.

	 */

	private void insertBiota(PesiExportState state) {

		try {

			ResultSet rs = state.getConfig().getDestination().getResultSet("SELECT * FROM Taxon WHERE GenusOrUninomial = 'Biota' ");

			if (rs.next() == false){

				int biotaId = state.getConfig().getNameIdStart() -1 ;

				String sqlInsertBiota = "INSERT INTO Taxon (TaxonId, KingdomFk, RankFk, RankCache, GenusOrUninomial, WebSearchName, WebShowName, FullName, DisplayName, TaxonStatusFk, TaxonStatusCache) " +

									       " VALUES (" + biotaId + ",    0,    0,   'Superdomain',   'Biota',          'Biota',  '<i>Biota</i>',   'Biota', '<i>Biota</i>',  1 ,      'accepted')";

				state.getConfig().getDestination().update(sqlInsertBiota);

			}

			rs = null;

		} catch (SQLException e) {

			logger.warn ("Biota could not be requested or inserted");

		}

	}

	//PHASE 3: Add Rank data, KingdomFk, TypeNameFk, expertFk and speciesExpertFk...

	private boolean doPhase03(PesiExportState state) {

		int count = 0;

		int pastCount = 0;

		boolean success = true;

		if (! state.getConfig().isDoTreeIndex()){

			logger.info ("Ignore PHASE 3: Add Rank data, KingdomFk, TypeNameFk, expertFk and speciesExpertFk...");

			return success;

		}

		// Get the limit for objects to save within a single transaction.

		int limit = state.getConfig().getLimitSave();

		logger.info("PHASE 3: Add Rank data, KingdomFk, TypeNameFk, expertFk and speciesExpertFk...");

		// Be sure to add rank information, KingdomFk, TypeNameFk, expertFk and speciesExpertFk to every taxonName

		// Start transaction

		TransactionStatus txStatus = startTransaction(true);

		if (logger.isDebugEnabled()) {

            logger.info("Started new transaction for rank, kingdom, typeName, expertFk and speciesExpertFK. Fetching some " + pluralString + " (max: " + limit + ") ...");

        }

		int partitionCount = 0;

		List<TaxonBase> list;

		while ((list = getNextTaxonPartition(TaxonBase.class, limit, partitionCount++, null)) != null) {

			if (logger.isDebugEnabled()) {

                logger.debug("Fetched " + list.size() + " " + pluralString + ". Exporting...");

            }

			for (TaxonBase<?> taxon : list) {

				TaxonName taxonName = CdmBase.deproxy(taxon.getName());

				// Determine expertFk

//				Integer expertFk = makeExpertFk(state, taxonName);

//

//				// Determine speciesExpertFk

//				Integer speciesExpertFk = makeSpeciesExpertFk(state, taxonName);

				doCount(count++, modCount, pluralString);

				Integer typeNameFk = getTypeNameFk(taxonName, state);

				Integer kingdomFk = findKingdomIdFromTreeIndex(taxon, state);

			    invokeRankDataAndTypeNameFkAndKingdomFk(taxonName, state.getDbId(taxon),

						typeNameFk, kingdomFk, state);

			}

			// Commit transaction

			commitTransaction(txStatus);

			if (logger.isDebugEnabled()){logger.debug("Committed transaction.");}

			logger.info("Exported " + (count - pastCount) + " " + pluralString + ". Total: " + count + " (Phase 3)");

			pastCount = count;

			// Start transaction

			txStatus = startTransaction(true);

			if (logger.isDebugEnabled()) {

                logger.info("Started new transaction for rank, kingdom, typeName, expertFk and speciesExpertFK. Fetching some " + pluralString + " (max: " + limit + ") ...");

            }

		}

		logger.debug("No " + pluralString + " left to fetch.");

		// Commit transaction

		commitTransaction(txStatus);

		if (logger.isDebugEnabled()){

		    logger.debug("Committed transaction.");

		    logger.debug("Try to take snapshot at the end of phase 3 of taxonExport, number of partitions: " + partitionCount);

		    //ProfilerController.memorySnapshot();

		}

		return success;

	}

    // 4th round: Add TreeIndex to each taxon

    private boolean doPhase04(PesiExportState state) {

        boolean success = true;

        logger.info("PHASE 4: Make TreeIndex ... ");

        //TODO test if possible to move to phase 02

        String sql = " UPDATE Taxon SET ParentTaxonFk = (SELECT TaxonId FROM Taxon WHERE RankFk = 0) " +

                " WHERE (RankFk = 10) and TaxonStatusFk = 1 ";

        state.getConfig().getDestination().update(sql);

        state.getConfig().getDestination().update("EXEC dbo.recalculateallstoredpaths");

        logger.info("PHASE 4: Make TreeIndex DONE");

        return success;

    }

    private static Integer findKingdomIdFromTreeIndex(TaxonBase<?> taxonBase,PesiExportState state) {

        Taxon taxon;

        if (taxonBase instanceof Synonym){

            taxon = ((Synonym) taxonBase).getAcceptedTaxon();

        }else{

            taxon = checkPseudoOrRelatedTaxon((Taxon)taxonBase);

        }

        if (taxon == null){

            NomenclaturalCode nomenclaturalCode = taxonBase.getName().getNameType();

            logger.warn("Taxon is synonym with no accepted taxon attached: " + taxonBase.getTitleCache() + ". The kingdom is taken from the nomenclatural code: " + PesiTransformer.nomenclaturalCode2Kingdom(nomenclaturalCode) );

            return PesiTransformer.nomenclaturalCode2Kingdom(nomenclaturalCode);

        } else{

            Set<TaxonNode> nodes = taxon.getTaxonNodes();

            if (nodes.isEmpty()){

                NomenclaturalCode nomenclaturalCode = taxon.getName().getNameType();

                logger.warn("The taxon has no nodes: " + taxon.getTitleCache() + ". The kingdom is taken from the nomenclatural code: " + PesiTransformer.nomenclaturalCode2Kingdom(nomenclaturalCode));

                return PesiTransformer.nomenclaturalCode2Kingdom(nomenclaturalCode);

            } else {

                if (nodes.size()>1){

                    logger.warn("The taxon has more then 1 taxon node: " + taxon.getTitleCache() + ". Take arbitrary one.");

                }

                String treeIndex = nodes.iterator().next().treeIndex();

                Pattern pattern = Pattern.compile("#t[0-9]+#([0-9]+#){3}");

                Matcher matcher = pattern.matcher(treeIndex);

                Integer kingdomID = null;

                if(matcher.find()) {

                    String treeIndexKingdom = matcher.group(0);

                    kingdomID = state.getTreeIndexKingdomMap().get(treeIndexKingdom);

                }else{

                    pattern = Pattern.compile("#t[0-9]+#([0-9]+#){2}");

                    matcher = pattern.matcher(treeIndex);

                    if(matcher.find()) {

                        String treeIndexKingdom = matcher.group(0);

                        kingdomID = state.getTreeIndexKingdomMap().get(treeIndexKingdom);

                    }

                }

                if(Rank.DOMAIN().equals(taxon.getName().getRank())){

                    return 0;

                }

                if(kingdomID == null){

                    logger.warn("Kingdom could not be defined for treeindex " + treeIndex);

                }

                return kingdomID;

            }

        }

    }

    private static Taxon checkPseudoOrRelatedTaxon(Taxon taxon) {

        if (!taxon.getTaxonNodes().isEmpty()){

            return taxon;

        }else if(hasPseudoTaxonRelationship(taxon)){

            return acceptedPseudoTaxon(taxon);

        }else if(isMisappliedNameOrProParteSynonym(taxon)){

            return acceptedTaxonConcept(taxon);

        }else{

            return taxon;

        }

    }

    private static Taxon acceptedPseudoTaxon(Taxon taxon) {

        for (TaxonRelationship rel : taxon.getRelationsFromThisTaxon()){

            if (TaxonRelationshipType.pseudoTaxonUuids().contains(rel.getType().getUuid())){

                return rel.getToTaxon();

            }

        }

        return taxon;

    }

    private static Taxon acceptedTaxonConcept(Taxon taxon) {

       for (TaxonRelationship rel : taxon.getRelationsFromThisTaxon()){

            if (TaxonRelationshipType.misappliedNameUuids().contains(rel.getType().getUuid())||

                    TaxonRelationshipType.proParteOrPartialSynonymUuids().contains(rel.getType().getUuid())){

                return rel.getToTaxon();

            }

        }

        return taxon;

    }

    private static boolean hasPseudoTaxonRelationship(Taxon taxon) {

        for (TaxonRelationship rel : taxon.getRelationsFromThisTaxon()){

            if (TaxonRelationshipType.pseudoTaxonUuids().contains(rel.getType().getUuid())){

                return true;

            }

        }

        return false;

    }

    private static boolean isMisappliedNameOrProParteSynonym(Taxon taxon) {

        for (TaxonRelationship rel : taxon.getRelationsFromThisTaxon()){

            if (TaxonRelationshipType.misappliedNameUuids().contains(rel.getType().getUuid())||

                    TaxonRelationshipType.proParteOrPartialSynonymUuids().contains(rel.getType().getUuid())){

                return true;

            }

        }

        return false;

    }

    //	"PHASE 5: Creating Inferred Synonyms..."

	private boolean doPhase05(PesiExportState state, PesiExportMapping mapping, PesiExportMapping synRelMapping) {

		int count;

		int pastCount;

		boolean success = true;

		// Get the limit for objects to save within a single transaction.

		if (! state.getConfig().isDoInferredSynonyms()){

			logger.info ("Ignore PHASE 5: Creating Inferred Synonyms...");

			return success;

		}

		int limit = state.getConfig().getLimitSave();

		// Create inferred synonyms for accepted taxa

		logger.info("PHASE 5: Creating Inferred Synonyms...");

		// Determine the count of elements in data warehouse database table Taxon

		currentTaxonId = determineTaxonCount(state);

		currentTaxonId++;

		count = 0;

		pastCount = 0;

		int pageSize = limit/10;

		int pageNumber = 1;

		String inferredSynonymPluralString = "Inferred Synonyms";

		// Start transaction

		TransactionStatus txStatus = startTransaction(true);

		if (logger.isDebugEnabled()) {

            logger.info("Started new transaction. Fetching some " + parentPluralString + " first (max: " + limit + ") ...");

        }

		List<TaxonBase> taxonList = null;

		while ((taxonList  = getTaxonService().listTaxaByName(Taxon.class, "*", "*", "*", "*", "*", Rank.SPECIES(), pageSize, pageNumber)).size() > 0) {

		    Map<Integer, TaxonName> inferredSynonymsDataToBeSaved = new HashMap<>();

			if (logger.isDebugEnabled()) {

                logger.info("Fetched " + taxonList.size() + " " + parentPluralString + ". Exporting...");

            }

			inferredSynonymsDataToBeSaved.putAll(createInferredSynonymsForTaxonList(state, mapping,

					synRelMapping, taxonList));

			doCount(count += taxonList.size(), modCount, inferredSynonymPluralString);

			// Commit transaction

			commitTransaction(txStatus);

			if (logger.isDebugEnabled()){logger.debug("Committed transaction.");}

			logger.info("Exported " + (taxonList.size()) + " " + inferredSynonymPluralString + ". Total: " + count);

			//pastCount = count;

			// Save Rank Data and KingdomFk for inferred synonyms

			for (Integer taxonFk : inferredSynonymsDataToBeSaved.keySet()) {

			    TaxonName taxonName = inferredSynonymsDataToBeSaved.get(taxonFk);

                invokeRankDataAndKingdomFk(inferredSynonymsDataToBeSaved.get(taxonFk), taxonFk, kingdomFk, state);

			}

			// Start transaction

			txStatus = startTransaction(true);

			if (logger.isDebugEnabled()) {

                logger.info("Started new transaction. Fetching some " + parentPluralString + " first (max: " + limit + ") ...");

            }

			// Increment pageNumber

			pageNumber++;

		}

		taxonList = null;

		while ((taxonList  = getTaxonService().listTaxaByName(Taxon.class, "*", "*", "*", "*", "*", Rank.SUBSPECIES(), pageSize, pageNumber)).size() > 0) {

			Map<Integer, TaxonName> inferredSynonymsDataToBeSaved = new HashMap<>();

			logger.info("Fetched " + taxonList.size() + " " + parentPluralString + ". Exporting...");

			inferredSynonymsDataToBeSaved.putAll(createInferredSynonymsForTaxonList(state, mapping,

					synRelMapping, taxonList));

			doCount(count += taxonList.size(), modCount, inferredSynonymPluralString);

			// Commit transaction

			commitTransaction(txStatus);

			logger.debug("Committed transaction.");

			logger.info("Exported " + taxonList.size()+ " " + inferredSynonymPluralString + ". Total: " + count);

			//pastCount = count;

			// Save Rank Data and KingdomFk for inferred synonyms

			for (Integer taxonFk : inferredSynonymsDataToBeSaved.keySet()) {

			    TaxonName taxonName = inferredSynonymsDataToBeSaved.get(taxonFk);

			    invokeRankDataAndKingdomFk(taxonName, taxonFk, kingdomFk, state);

			}

			// Start transaction

			txStatus = startTransaction(true);

			logger.info("Started new transaction. Fetching some " + parentPluralString + " first (max: " + limit + ") ...");

			// Increment pageNumber

			pageNumber++;

			inferredSynonymsDataToBeSaved = null;

		}

		if (taxonList.size() == 0) {

			logger.info("No " + parentPluralString + " left to fetch.");

		}

		taxonList = null;

//		logger.warn("Taking snapshot at the end of phase 5 of taxonExport");

//		ProfilerController.memorySnapshot();

		// Commit transaction

		commitTransaction(txStatus);

		System.gc();

		logger.debug("Taking snapshot at the end of phase 5 after gc() of taxonExport");

		//ProfilerController.memorySnapshot();

		logger.debug("Committed transaction.");

		return success;

	}

	private Map<Integer, TaxonName> createInferredSynonymsForTaxonList(PesiExportState state,

			PesiExportMapping mapping, PesiExportMapping synRelMapping,	 List<TaxonBase> taxonList) {

		Taxon acceptedTaxon;

		Classification classification = null;

		List<Synonym> inferredSynonyms = null;

		boolean localSuccess = true;

		Map<Integer, TaxonName> inferredSynonymsDataToBeSaved = new HashMap<>();

		for (TaxonBase<?> taxonBase : taxonList) {

			if (taxonBase.isInstanceOf(Taxon.class)) { // this should always be the case since we should have fetched accepted taxon only, but you never know...

				acceptedTaxon = CdmBase.deproxy(taxonBase, Taxon.class);

				TaxonName taxonName = acceptedTaxon.getName();

				if (taxonName.isZoological()) {

					kingdomFk = findKingdomIdFromTreeIndex(taxonBase, state);

					Set<TaxonNode> taxonNodes = acceptedTaxon.getTaxonNodes();

					TaxonNode singleNode = null;

					if (taxonNodes.size() > 0) {

						// Determine the classification of the current TaxonNode

						singleNode = taxonNodes.iterator().next();

						if (singleNode != null) {

							classification = singleNode.getClassification();

						} else {

							logger.error("A TaxonNode belonging to this accepted Taxon is NULL: " + acceptedTaxon.getUuid() + " (" + acceptedTaxon.getTitleCache() +")");

						}

					} else {

						// Classification could not be determined directly from this TaxonNode

						// The stored classification from another TaxonNode is used. It's a simple, but not a failsafe fallback solution.

						if (taxonNodes.size() == 0) {

							//logger.error("Classification could not be determined directly from this Taxon: " + acceptedTaxon.getUuid() + " is misapplication? "+acceptedTaxon.isMisapplication()+ "). The classification of the last taxon is used");

						}

					}

					if (classification != null) {

						try{

						    TaxonName name = acceptedTaxon.getName();

							//if (name.isSpecies() || name.isInfraSpecific()){

								inferredSynonyms  = getTaxonService().createAllInferredSynonyms(acceptedTaxon, classification, true);

							//}

//								inferredSynonyms = getTaxonService().createInferredSynonyms(classification, acceptedTaxon, SynonymType.INFERRED_GENUS_OF());

							if (inferredSynonyms != null) {