/*******************************************************************************
 * Copyright 2014 Centre for Development of Advanced Computing(C-DAC), Pune
 *
 * 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 in.cdac.medinfo.csnotk.csnolib.db;

import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.CONCEPT;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.CONCEPT_ACTIVE;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.CONCEPT_DEFINITION_STATUS;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.CONCEPT_EFFECTIVE_TIME;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.CONCEPT_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.CONCEPT_MODULE_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.DESCRIPTION;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.DESCRIPTION_ACTIVE;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.DESCRIPTION_CASE_SIGNIFICANCE_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.DESCRIPTION_CONCEPT_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.DESCRIPTION_EFFECTIVE_TIME;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.DESCRIPTION_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.DESCRIPTION_LANGUAGE_CODE;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.DESCRIPTION_MODULE_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.DESCRIPTION_TERM;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.DESCRIPTION_TYPE_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.REFSET;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.REFSETLANG;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.REFSETLANG_ACCEPTABILITY_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.REFSETLANG_ACTIVE;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.REFSETLANG_REFERENCED_COMPONENT_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.REFSETLANG_REFSET_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.REFSET_REFERENCED_COMPONENT_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.REFSET_REFSET_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.RELATIONSHIP;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.RELATIONSHIP_ACTIVE;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.RELATIONSHIP_DESTINATION_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.RELATIONSHIP_SOURCE_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.RELATIONSHIP_TYPEID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.TRANSCLOSURE;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.TRANSCLOSURE_CHILD_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.TRANSCLOSURE_PARENT_ID;

import java.io.File;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Locale;
import java.util.Set;

import org.h2.tools.RunScript;

import in.cdac.medinfo.csnotk.csnolib.agents.SNOMEDAgent;
import in.cdac.medinfo.csnotk.csnolib.commons.Constants;
import in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping;
import in.cdac.medinfo.csnotk.csnolib.commons.EnumAcceptabilityId;
import in.cdac.medinfo.csnotk.csnolib.commons.EnumCaseSignificance;
import in.cdac.medinfo.csnotk.csnolib.commons.EnumCharacteristicType;
import in.cdac.medinfo.csnotk.csnolib.commons.EnumComponent;
import in.cdac.medinfo.csnotk.csnolib.commons.EnumDefStatus;
import in.cdac.medinfo.csnotk.csnolib.commons.EnumDescType;
import in.cdac.medinfo.csnotk.csnolib.commons.EnumDirection;
import in.cdac.medinfo.csnotk.csnolib.commons.EnumLangRefset;
import in.cdac.medinfo.csnotk.csnolib.commons.EnumModifier;
import in.cdac.medinfo.csnotk.csnolib.commons.EnumRelationshipType;
import in.cdac.medinfo.csnotk.csnolib.commons.EnumSemanticTag;
import in.cdac.medinfo.csnotk.csnolib.model.ComponentFactory;
import in.cdac.medinfo.csnotk.csnolib.model.CompositeDescription;
import in.cdac.medinfo.csnotk.csnolib.model.Concept;
import in.cdac.medinfo.csnotk.csnolib.model.Description;
import in.cdac.medinfo.csnotk.csnolib.model.Relationship;
import in.cdac.medinfo.csnotk.csnolib.util.SemanticTagBuilder;
//Logs
import org.apache.logging.log4j.Logger;
import org.apache.logging.log4j.LogManager;
import in.cdac.medinfo.csnotk.csnolib.util.LoggerUtility;
//INDIA_MODULE_ID
import static in.cdac.medinfo.csnotk.csnolib.commons.Constants.INDIA_MODULE_ID;


/**
 * Establishes a connection to fetch data from SNOMED CT database.
 *
 */
public class QueryManager
{
	private static final String CREATING_TABLE = "Creating {} table";
	private static final String DELETING_RECORDS_FROM_TABLE = "Deleting records from {} table";
	private static final String NULL_CHARSET_UTF_8_FIELD_DELIMITER_FIELD_SEPARATOR_CHR_9 = "',null,'charset=UTF-8 fieldDelimiter= fieldSeparator='||chr(9));";
	private static final String SORTED_SELECT_FROM_CSVREAD = " SORTED SELECT * FROM CSVREAD('";
	private static final String SELECT_FROM_CSVREAD = " SELECT * FROM CSVREAD('";
	private static final String DELETE_FROM = "delete from ";
	private static final String INSERT_INTO = "insert into ";
	private static final String MERGE_INTO = "merge into ";
	/**
	 * SNOMEDAgent object
	 */
	SNOMEDAgent snomedAgent=new SNOMEDAgent();
	private static SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyyMMdd",Locale.ENGLISH);
	private static final Logger logger = LogManager.getLogger(QueryManager.class);

	/**
	 * Retrieves the Semantic Tag for a particular Concept id.
	 * @param conceptId
	 * 					the id for particular Concept whose hierarchy is to be
	 * 					identified.
	 * @param typeId
	 * 					type of the description.
	 * @param term
	 * 					the description for that concept.
	 * @return {@link String} value corresponding to the hierarchy/Semantic Tag of
	 *  a particular concept.
	 * @throws SQLException
	 */
	public String getHierarchy(String conceptId, String typeId,
			String term) throws SQLException {
		String strSemanticTag = "";
		String strTypeID = "";
		if (typeId.equalsIgnoreCase(EnumDescType.FULLY_SPECIFIED_NAME.getValue()) && term.contains("(") && term.contains(")")) {
			String strST = SemanticTagBuilder.buildSemanticTagFromString(term);
			if (null == strST) {
				strSemanticTag = EnumSemanticTag.SITUATION_WITH_EXPLICIT_CONTEXT.getValue();
			}
		} else {
			Connection conn = ConnectionManager.getConnection();
			StringBuilder query = new StringBuilder();
			query.append("select "+DESCRIPTION_TERM+","+DESCRIPTION_TYPE_ID+" from "+DESCRIPTION+"  where " +DESCRIPTION_CONCEPT_ID+"="+conceptId+" and "+DESCRIPTION_TYPE_ID+"="+EnumDescType.FULLY_SPECIFIED_NAME.getValue()+" and "+DESCRIPTION_TERM+" like '%)%' and "+DESCRIPTION_ACTIVE+"=1;");
			Statement stmt = conn.createStatement();
			ResultSet result = stmt.executeQuery(query.toString());
			if (result != null) {
				while (result.next()) {
					term = result.getString(1);
					strTypeID = result.getString(2);

					if (strTypeID
							.equalsIgnoreCase(EnumDescType.FULLY_SPECIFIED_NAME
									.getValue())
							&& term.contains("(") && term.contains(")")) {
						String strST = SemanticTagBuilder
								.buildSemanticTagFromString(term);
						if (null == strST) {
							strSemanticTag = EnumSemanticTag.SITUATION_WITH_EXPLICIT_CONTEXT.getValue();
							break;
						}
					}
				}
			}
			stmt.close();
		}
		return strSemanticTag;
	}


	/**
	 * Returns a SNOMED CT {@link Concept} associated for a given concept identifier.
	 * @param id
	 * 				identifier of the concept.
	 * @param concept
	 * 				SNOMED CT {@link Concept} which is to be populated.
	 * @throws SQLException
	 * @throws java.text.ParseException
	 */
	public void populateConcept(String id, Concept concept) throws SQLException, java.text.ParseException
	{
		if( null == concept || !id.matches("[0-9]*"))
			return;
		Connection conn = ConnectionManager.getConnection();

		StringBuilder query = new StringBuilder();
		query.append("select "+DESCRIPTION_TERM+","+CONCEPT_ACTIVE+","+CONCEPT_DEFINITION_STATUS+","+CONCEPT_EFFECTIVE_TIME+","+CONCEPT_MODULE_ID+" from "+DESCRIPTION+","+CONCEPT+"  where "+ DESCRIPTION_CONCEPT_ID +"="+ CONCEPT_ID+" and " +DESCRIPTION_CONCEPT_ID+"="+id+" and "+DESCRIPTION_TYPE_ID+"="+EnumDescType.FULLY_SPECIFIED_NAME.getValue()+" and "+DESCRIPTION_ACTIVE +"=1");
		Statement stmt = conn.createStatement();
		ResultSet result = stmt.executeQuery(query.toString());
		if(null != result)
		{
			if(result.next())
			{
				concept.setId(id);
				concept.setFullySpecifiedName(result.getString(1));
				concept.setActiveStatus(result.getByte(2));
				concept.setDefinitionStatusId(EnumDefStatus.getEnum(result.getString(3)));
				concept.setTime(parseToDate(result.getString(4)));
				concept.setModuleId(result.getString(5));
			}
		}
		stmt.close();
	}

	/**
	 * Returns list of {@link Description}s  associated for
	 * a given concept identifier and which is other than FSN.
	 * @param conceptId
	 * 					SNOMED CT {@link Concept} identifier.
	 * @return List of {@link Description}s .
	 * @throws SQLException if any error occurs while accessing database.
	 * @throws ParseException
	 */
	public List<Description> getDescriptions(String conceptId) throws SQLException, ParseException
	{
		Connection conn = ConnectionManager.getConnection();
		List<Description> descriptions = new ArrayList<>();

		String query = "select "+DESCRIPTION_ID+","+DESCRIPTION_CONCEPT_ID+","+DESCRIPTION_TERM+"" +
				","+DESCRIPTION_TYPE_ID+","+DESCRIPTION_ACTIVE+","+DESCRIPTION_LANGUAGE_CODE+"," +
				""+DESCRIPTION_EFFECTIVE_TIME+","+DESCRIPTION_MODULE_ID+"," +
				""+DESCRIPTION_CASE_SIGNIFICANCE_ID+" from " +
				""+DESCRIPTION+" where "+DESCRIPTION_CONCEPT_ID+"="+conceptId+"" +
				" and "+DESCRIPTION_TYPE_ID+"="+EnumDescType.SYNONYM.getValue();
		Statement stmt = conn.createStatement();
		ResultSet result = stmt.executeQuery(query);
		if(null !=  result)
		{
			while(result.next()){
				ComponentFactory componentFactory=ComponentFactory.getInstance();
				Description description = (Description)componentFactory.getComponent(EnumComponent.DESCRIPTION, null);
				description.setId(result.getString(1));
				description.setConceptId(result.getString(2));
				description.setTerm(result.getString(3));
				description.setType(EnumDescType.getEnum(result.getString(4)));
				description.setActiveStatus(result.getByte(5));
				description.setLangCode(result.getString(6));
				description.setTime(parseToDate(result.getString(7)));
				description.setModuleId(result.getString(8));
				description.setCaseSId(EnumCaseSignificance.getEnum(result.getString(9)));
				descriptions.add(description);
			}
		}
		stmt.close();
		return descriptions;
	}



	/**
	 * Searches for concepts in the SNOMED CT repository for the
	 *  given search term.
	 * @param searchTerm
	 * 					{@link String} term/text to be searched .
	 * @return Set of {@link Concept}s.
	 * @throws SQLException if any error occurs while accessing database.
	 */
	public Set<Concept> searchConcepts(String searchTerm) throws SQLException
	{
		Connection conn = ConnectionManager.getConnection();
		Set<Concept> concepts = new HashSet<>();

		String query = "select * from "+CONCEPT+" where "+CONCEPT_ID+" in (select "+DESCRIPTION_CONCEPT_ID+" from "+DESCRIPTION+" where "+DESCRIPTION_TERM+" like '%"+searchTerm+"%') and "+CONCEPT_ACTIVE+"=1";
		Statement stmt = conn.createStatement();
		ResultSet result = stmt.executeQuery(query);
		if(null != result){
			while(result.next())
			{
				ComponentFactory componentFactory=ComponentFactory.getInstance();
				Concept concept = (Concept)componentFactory.getComponent(EnumComponent.CONCEPT, null);
				concept.setId(result.getString(1));
				concepts.add(concept);
			}
		}
		stmt.close();
		return concepts;
	}


	/**
	 * Returns unique Inward/Outward latest relationships with active status.
	 * @param ConceptId
	 * 					identifier of the {@link Concept}.
	 * @param enumRelationshipType
	 * 					specifies the type of relationship.
	 * @param direction
	 * 					specifies the direction(source / destination) {@link EnumDirection}.
	 * @return Set of {@link Relationship}s .
	 * @throws SQLException if any error occurs while accessing database.
	 * @throws ParseException
	 */
	public List<Relationship> getRelationships(String ConceptId, String relationshipType, EnumDirection direction) throws SQLException, ParseException
	{
		Connection conn = ConnectionManager.getConnection();
		List<Relationship> relationships = new ArrayList<>();
		String query;

		if(relationshipType.toLowerCase().equals(EnumRelationshipType.ALL.getValue()))
		{
			query="select * from "+RELATIONSHIP+" where "+RELATIONSHIP_SOURCE_ID+"="+ConceptId
					+" AND "+ RELATIONSHIP_ACTIVE + "=1";
		}

		else if(direction==EnumDirection.DESTINATION)
		{
			query="select * from "+RELATIONSHIP+" where "+RELATIONSHIP_TYPEID+"="+relationshipType+" AND "+RELATIONSHIP_DESTINATION_ID+"="+ConceptId
					+" AND "+ RELATIONSHIP_ACTIVE + "=1";
		}
		else
		{
			query="select * from "+RELATIONSHIP+" where "+RELATIONSHIP_TYPEID+"="+relationshipType+" AND "+RELATIONSHIP_SOURCE_ID+"="+ConceptId
					+" AND "+ RELATIONSHIP_ACTIVE + "=1";
		}
		Statement stmt = conn.createStatement();
		ResultSet result = stmt.executeQuery(query);
		if(null != result){
			while(result.next()){
				ComponentFactory componentFactory=ComponentFactory.getInstance();
				Relationship relationship = (Relationship)componentFactory.getComponent(EnumComponent.RELATIONSHIP, null);
				relationship.setSourceConcept(snomedAgent.getConcept(result.getString(5).trim()));
				Concept destinationConcept=snomedAgent.getConcept(result.getString(6).trim());
				relationship.setDestinationConcept(destinationConcept);
				relationship.setType(snomedAgent.getConcept(result.getString(8).trim()));
				relationship.setCharacteristicTypeId(EnumCharacteristicType.getEnum(result.getString(9)));
				relationship.setModifierId(EnumModifier.getEnum(result.getString(10)));
				relationship.setActiveStatus(result.getByte(3));
				relationship.setTime(parseToDate(result.getString(2)));
				relationship.setModuleId(result.getString(4));
				relationship.setRelationshipGroup(result.getInt(7));


				relationships.add(relationship);
			}
		}
		stmt.close();
		return relationships;
	}


	/**
	 * Returns list of preferred names in US refset associated with
	 * corresponding {@link Concept} identifier.
	 * @param enumLangRefset specifies the Language Refset {@link EnumLangRefset} }.
	 * @param conceptId identifier of the {@link Concept}.
	 * @return Preferred name of {@link Concept}.
	 * @throws SQLException
	 */
	public String getPreferredName(String conceptId, EnumLangRefset enumLangRefset) throws SQLException
	{

		String query = "select "+ DESCRIPTION_TERM +" from ( "+ DESCRIPTION+" join "+ REFSETLANG +" on ("+ DESCRIPTION_ID +" = "+
				REFSETLANG_REFERENCED_COMPONENT_ID +" )) where (( " + DESCRIPTION_ACTIVE+ " = 1) and ( "+ DESCRIPTION_TYPE_ID+ "= "+EnumDescType.SYNONYM.getValue()+") and ( "+
				DESCRIPTION_CONCEPT_ID + "= " + conceptId + " and "+ REFSETLANG_REFSET_ID +"='"+enumLangRefset.getId()+"') and ( "+REFSETLANG_ACTIVE +" = 1) and ( "+
				REFSETLANG_ACCEPTABILITY_ID +" ="+EnumAcceptabilityId.PREFERRED.getValue()+"))";

		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = conn.prepareStatement(query);
		ResultSet result =	ps.executeQuery();
		result.next();
		String prefName =	result.getString(1);
		ps.close();
		return prefName;
	}


	/**
	 * This function loads the closure table in the database.
	 * @param ct_file
	 * 					File associated with the closure table.
	 * @throws SQLException
	 * @throws IOException
	 */

	public void loadClosureTable(File ct_file) throws SQLException, IOException
	{

		String query=("insert into sct2_relationships_tc SELECT * FROM CSVREAD('"+ct_file.getCanonicalPath().replace("\\","/")+NULL_CHARSET_UTF_8_FIELD_DELIMITER_FIELD_SEPARATOR_CHR_9);

		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = conn.prepareStatement(query);

		ps.executeUpdate();
		ps.close();
	}
	/**
	 * Returns acceptability Id for specified description id.
	 * acceptability id indicates whether particular description is Preferred Term or not.
	 * @param descriptionId
	 * 						{@link String} identifier of the {@link Description}.
	 * @return {@link String} value that specifies the acceptability id.
	 * @throws SQLException
	 */
	public String getAcceptabilityId(String descriptionId) throws SQLException
	{
		//	SELECT acceptabilityId   FROM  der2_refset_lang
		//	WHERE referencedComponentId = 1480803010 ;


		String query = "select "+ REFSETLANG_ACCEPTABILITY_ID +" from  "+ REFSETLANG  +"  where "  + REFSETLANG_REFERENCED_COMPONENT_ID + "= " + descriptionId ;

		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = conn.prepareStatement(query);
		ResultSet result =	ps.executeQuery();
		result.next();
		String acceptabilityId=result.getString(1);

		ps.close();
		return acceptabilityId;
	}

	/**
	 * This method will create SNOMED CT database
	 * @param conceptFile file path for Concept File
	 * @param descriptionFile file path for Description File
	 * @param relationshipFile file path for Relationship File
	 * @param refsetLangFile file path for Language Refset File
	 * @throws SQLException
	 */


	public void createSNOMEDdb(String conceptFile, String descriptionFile, String relationshipFile, String refsetLangFile,String textdefinitionFile,String concreteRelationshipFile) throws SQLException{
		Connection conn =null;
		Statement stm = null;
		try{
			conn = ConnectionManager.getConnection();
			stm = conn.createStatement();

			RunScript.execute(conn, new InputStreamReader(QueryManager.class.getClassLoader().getResourceAsStream("SNOMEDDBH2Script.sql")));
			logger.info(LoggerUtility.ConsoleWithLog, CREATING_TABLE, DBSchemaMapping.CONCEPT);
			stm.execute(INSERT_INTO+DBSchemaMapping.CONCEPT+SORTED_SELECT_FROM_CSVREAD+conceptFile+NULL_CHARSET_UTF_8_FIELD_DELIMITER_FIELD_SEPARATOR_CHR_9);

			logger.info(LoggerUtility.ConsoleWithLog, CREATING_TABLE, DBSchemaMapping.DESCRIPTION);

			stm.execute(INSERT_INTO+DBSchemaMapping.DESCRIPTION+SORTED_SELECT_FROM_CSVREAD+descriptionFile+NULL_CHARSET_UTF_8_FIELD_DELIMITER_FIELD_SEPARATOR_CHR_9);

			logger.info(LoggerUtility.ConsoleWithLog, CREATING_TABLE, DBSchemaMapping.RELATIONSHIP);
			stm.execute(INSERT_INTO+DBSchemaMapping.RELATIONSHIP+SORTED_SELECT_FROM_CSVREAD+relationshipFile+NULL_CHARSET_UTF_8_FIELD_DELIMITER_FIELD_SEPARATOR_CHR_9);

			if(concreteRelationshipFile!=null) {
				logger.info(LoggerUtility.ConsoleWithLog, CREATING_TABLE, DBSchemaMapping.CONCRETE_RELATIONSHIP);
				stm.execute(INSERT_INTO+DBSchemaMapping.CONCRETE_RELATIONSHIP+SORTED_SELECT_FROM_CSVREAD+concreteRelationshipFile+NULL_CHARSET_UTF_8_FIELD_DELIMITER_FIELD_SEPARATOR_CHR_9);
			}

			logger.info(LoggerUtility.ConsoleWithLog, CREATING_TABLE, DBSchemaMapping.REFSETLANG);

			stm.execute(INSERT_INTO+DBSchemaMapping.REFSETLANG+SORTED_SELECT_FROM_CSVREAD+refsetLangFile+NULL_CHARSET_UTF_8_FIELD_DELIMITER_FIELD_SEPARATOR_CHR_9);

			logger.info(LoggerUtility.ConsoleWithLog, CREATING_TABLE, DBSchemaMapping.TEXTDEFINITION);

			stm.execute(INSERT_INTO+DBSchemaMapping.TEXTDEFINITION+SORTED_SELECT_FROM_CSVREAD+textdefinitionFile+NULL_CHARSET_UTF_8_FIELD_DELIMITER_FIELD_SEPARATOR_CHR_9);
		}finally{
			if(stm!=null)
				stm.close();
			if(conn!=null)
				conn.close();
		}

	}


	/**
	 * This method will create SNOMED CT extension database
	 * @param conceptFile file path for Concept File
	 * @param descriptionFile file path for Description File
	 * @param relationshipFile file path for Relationship File
	 * @param refsetLangFile file path for Language Refset File
	 * @param Set of moduleId
	 * @throws SQLException
	 */
	public void createExtensiondb(Set<String> moduleIdSet, String conceptFile, String descriptionFile, String relationshipFile, String refsetLangFile, String concreteRelationshipFile) throws SQLException{
		Connection conn =null;
		Statement stm = null;
		try{
			conn = ConnectionManager.getConnection();
			stm = conn.createStatement();

			ResultSet rst= stm.executeQuery("select count(*) from "+DBSchemaMapping.CONCEPT +" Where "+DBSchemaMapping.CONCEPT_ID+ " like '%00_'");
			if(!(rst.next() && rst.getInt(1)>0))
				return;
			//Delete module id based data
			//Except INDIA NRC module
			String moduleId="";
			for(String element: moduleIdSet){
				if( !INDIA_MODULE_ID.equalsIgnoreCase(element) ) {
					if(moduleId=="")
						moduleId=element;
					else
						moduleId=moduleId+","+element;
				}
			}

			//this should be done firstly
			//Deleting / truncating old TC table
			stm.execute("TRUNCATE TABLE " + DBSchemaMapping.TRANSCLOSURE);
			logger.info(LoggerUtility.ConsoleWithLog, "Truncated old {} table" ,DBSchemaMapping.TRANSCLOSURE);
			//Delete module id based data
			stm.execute(DELETE_FROM+DBSchemaMapping.CONCEPT+" where "+DBSchemaMapping.CONCEPT_MODULE_ID+" in ("+moduleId+")");
			logger.info(LoggerUtility.ConsoleWithLog, DELETING_RECORDS_FROM_TABLE ,DBSchemaMapping.CONCEPT);
			stm.execute(DELETE_FROM+DBSchemaMapping.DESCRIPTION+" where "+DBSchemaMapping.DESCRIPTION_MODULE_ID+" in ("+moduleId+")");
			logger.info(LoggerUtility.ConsoleWithLog, DELETING_RECORDS_FROM_TABLE ,DBSchemaMapping.DESCRIPTION);
			stm.execute(DELETE_FROM+DBSchemaMapping.RELATIONSHIP+" where "+DBSchemaMapping.RELATIONSHIP_MODULE_ID+" in ("+moduleId+")");
			logger.info(LoggerUtility.ConsoleWithLog, DELETING_RECORDS_FROM_TABLE ,DBSchemaMapping.RELATIONSHIP);
			if(concreteRelationshipFile!=null) {
				stm.execute(DELETE_FROM+DBSchemaMapping.CONCRETE_RELATIONSHIP+" where "+DBSchemaMapping.CONCRETE_RELATIONSHIP_MODULE_ID+" in ("+moduleId+")");
				logger.info(LoggerUtility.ConsoleWithLog, DELETING_RECORDS_FROM_TABLE ,DBSchemaMapping.CONCRETE_RELATIONSHIP);
			}
			stm.execute(DELETE_FROM+DBSchemaMapping.REFSETLANG+" where "+DBSchemaMapping.REFSETLANG_MODULE_ID+" in ("+moduleId+")");
			logger.info(LoggerUtility.ConsoleWithLog, DELETING_RECORDS_FROM_TABLE ,DBSchemaMapping.REFSETLANG);

			logger.info(LoggerUtility.ConsoleWithLog, CREATING_TABLE, DBSchemaMapping.CONCEPT);
			stm.execute(MERGE_INTO+DBSchemaMapping.CONCEPT+SELECT_FROM_CSVREAD+conceptFile+NULL_CHARSET_UTF_8_FIELD_DELIMITER_FIELD_SEPARATOR_CHR_9);

			logger.info(LoggerUtility.ConsoleWithLog, CREATING_TABLE, DBSchemaMapping.DESCRIPTION);
			stm.executeUpdate(MERGE_INTO+DBSchemaMapping.DESCRIPTION+SELECT_FROM_CSVREAD+descriptionFile+NULL_CHARSET_UTF_8_FIELD_DELIMITER_FIELD_SEPARATOR_CHR_9);

			logger.info(LoggerUtility.ConsoleWithLog, CREATING_TABLE, DBSchemaMapping.RELATIONSHIP);
			Constants.RELATIONSHIP_EXTENSION_RECORDS_COUNT=stm.executeUpdate(MERGE_INTO+DBSchemaMapping.RELATIONSHIP+SELECT_FROM_CSVREAD+relationshipFile+NULL_CHARSET_UTF_8_FIELD_DELIMITER_FIELD_SEPARATOR_CHR_9);

			if(concreteRelationshipFile!=null) {
				logger.info(LoggerUtility.ConsoleWithLog, CREATING_TABLE, DBSchemaMapping.CONCRETE_RELATIONSHIP);
				Constants.CONCRETE_RELATIONSHIP_EXTENSION_RECORDS_COUNT=stm.executeUpdate(INSERT_INTO+DBSchemaMapping.CONCRETE_RELATIONSHIP+SELECT_FROM_CSVREAD+concreteRelationshipFile+NULL_CHARSET_UTF_8_FIELD_DELIMITER_FIELD_SEPARATOR_CHR_9);
			}
			logger.info(LoggerUtility.ConsoleWithLog, CREATING_TABLE, DBSchemaMapping.REFSETLANG);
			stm.executeUpdate(MERGE_INTO+DBSchemaMapping.REFSETLANG+SELECT_FROM_CSVREAD+refsetLangFile+NULL_CHARSET_UTF_8_FIELD_DELIMITER_FIELD_SEPARATOR_CHR_9);

		}finally{
			if(stm!=null)
				stm.close();
			if(conn!=null)
				conn.close();
		}

	}

	/**
	 * This method will create database for Refset
	 * @param refsetId
	 * @param refFilePath
	 * @throws SQLException
	 */
	/**
	 * This method will create database for Refset
	 * @param refsetId
	 * @param refFilePath
	 * @throws SQLException
	 */
	public void createRefsetdb(String refsetId, String refFilePath, Set<String> moduleIdSet) throws SQLException{
		Connection conn =null;
		Statement stm = null;
		try{
			conn = ConnectionManager.getConnection();
			stm = conn.createStatement();
			logger.info(LoggerUtility.ConsoleWithLog, CREATING_TABLE,refsetId);

			for(String moduleid:moduleIdSet)
				stm.execute(DELETE_FROM+DBSchemaMapping.REFSET+" where "+DBSchemaMapping.REFSET_REFSET_ID+" = '"+refsetId+"' and "+DBSchemaMapping.REFSET_MODULE_ID+" = '"+moduleid+"' ;");
			stm.execute(INSERT_INTO+DBSchemaMapping.REFSET+SELECT_FROM_CSVREAD+refFilePath+NULL_CHARSET_UTF_8_FIELD_DELIMITER_FIELD_SEPARATOR_CHR_9);

		}finally{
			if(stm!=null)
				stm.close();
			if(conn!=null)
				conn.close();
		}


	}

	/**
	 * This method will create database for cRefset
	 * @param refsetId
	 * @param refFilePath
	 * @param moduleIdSet
	 * @throws SQLException
	 */
	public void createcRefsetdb(String refsetId, String refFilePath, Set<String> moduleIdSet) throws SQLException{
		Connection	conn=ConnectionManager.getConnection();
		Statement	stm=conn.createStatement();
		for(String moduleid:moduleIdSet){
			logger.info(LoggerUtility.ConsoleWithLog, DELETING_RECORDS_FROM_TABLE ,DBSchemaMapping.cREFSET);
			stm.execute(DELETE_FROM+DBSchemaMapping.cREFSET+" where "+DBSchemaMapping.cREFSET_REFSET_ID+" = '"+refsetId+"' and "+DBSchemaMapping.cREFSET_MODULE_ID+ " = '"+moduleid+"';");
		}

		logger.info(LoggerUtility.ConsoleWithLog, CREATING_TABLE, DBSchemaMapping.cREFSET);
		stm.execute(MERGE_INTO+DBSchemaMapping.cREFSET+SELECT_FROM_CSVREAD+refFilePath+NULL_CHARSET_UTF_8_FIELD_DELIMITER_FIELD_SEPARATOR_CHR_9);
	}
	/**
	 * This method will create database for simpleMap
	 * @param moduleId
	 * @param simpleMapFilePath
	 * @throws SQLException
	 */
	public boolean createSimpleMapdb(String moduleId, String simpleMapFilePath) throws SQLException{
		Connection conn =null;
		Statement stm = null;
		try{
			conn = ConnectionManager.getConnection();
			stm = conn.createStatement();
			logger.info(LoggerUtility.ConsoleWithLog, "Creating simple map table");
			ResultSet resultSet=stm.executeQuery("SELECT * FROM "+DBSchemaMapping.CONCEPT+" where "+DBSchemaMapping.CONCEPT_ID+" = '"+moduleId+"';");
			if(resultSet.next()==false)
			{
				return false;
			}
			stm.execute(DELETE_FROM+DBSchemaMapping.SIMPLEMAP+" where "+ DBSchemaMapping.SIMPLEMAP_MODULE_ID+ "= '"+moduleId+"';");
			Constants.SIMPLEMAP_RECORDS_COUNT=stm.executeUpdate(INSERT_INTO+DBSchemaMapping.SIMPLEMAP+SELECT_FROM_CSVREAD+simpleMapFilePath+NULL_CHARSET_UTF_8_FIELD_DELIMITER_FIELD_SEPARATOR_CHR_9);
			return true;
		}finally{
			if(stm!=null)
				stm.close();
			if(conn!=null)
				conn.close();
		}
	}

	/**
	 * This method will export relationship table into a tsv file (for TC table creation)
	 * @throws SQLException
	 * @throws IOException
	 */
	public String exportRelationshipTableAndDeleteTCTable() throws SQLException , IOException{
		Connection conn =null;
		Statement stm = null;
		String filePath = "";
		try{
			conn = ConnectionManager.getConnection();
			stm = conn.createStatement();
			File relationshipTemp = File.createTempFile("relationship_rf2_", ".txt");
			filePath = relationshipTemp.getAbsolutePath();
			ResultSet resultSet=stm.executeQuery("CALL CSVWRITE('"+filePath+"', 'SELECT * FROM "+DBSchemaMapping.RELATIONSHIP+"', 'charset=UTF-8 fieldSeparator=' || CHAR(9));");
			if(resultSet.next()==false)
			{
				return "Error";
			}
			logger.info(LoggerUtility.ConsoleWithLog, "Exported {} table to temporary file ({} records)", DBSchemaMapping.RELATIONSHIP, resultSet.getLong(1));
			return filePath;
		}finally{
			if(stm!=null)
				stm.close();
			if(conn!=null)
				conn.close();
		}
	}

	/**
	 * This method will delete temporary relationship file created for TC table generation
	 */
	public void deleteRelationshipTempFile(String filePath) {
		File relationshipTemp = new File(filePath);
		boolean deleteStatus = relationshipTemp.delete();
		if( deleteStatus )
			logger.info(LoggerUtility.ConsoleWithLog, "Deleted temporary relationship file");
	}

	private Date parseToDate(String strDate) throws java.text.ParseException{
		synchronized(this){
			return simpleDateFormat.parse(strDate);
		}
	}
}
