/*******************************************************************************
 * Copyright 2016 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.icdmap;

import java.io.InputStreamReader;
import java.sql.Connection;
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.LinkedList;
import java.util.List;
import java.util.Locale;

import org.h2.tools.RunScript;

import in.cdac.medinfo.csnotk.csnolib.db.ConnectionManager;
import in.cdac.medinfo.csnotk.csnolib.db.DBUtil;
import in.cdac.medinfo.csnotk.csnolib.util.PropertyReader;
//Logs
import org.apache.logging.log4j.Logger;
import org.apache.logging.log4j.LogManager;
import in.cdac.medinfo.csnotk.csnolib.util.LoggerUtility;
/**
 * Establishes a connection to fetch data from SNOMED ICD-10 Mapping database.
 *
 */
class QueryManager {
	private long offset;
	private long numberOfRecords;
	private String[] columnArray = {MappingConstants.ID,MappingConstants.EFFECTIVE_TIME
			,MappingConstants.ICD_ACTIVE,MappingConstants.MODULE_ID,MappingConstants.REFSET_ID,
			MappingConstants.REFERENCED_COMPONENT_ID,MappingConstants.MAP_GROUP,MappingConstants.MAP_PRIORITY,
			MappingConstants.MAP_RULE,MappingConstants.MAP_ADVICE,MappingConstants.MAP_TARGET,MappingConstants.CORRELATION_ID,MappingConstants.MAPCATEGORY_ID};
	private ArrayList<MappingComponent> icdMappingComponentList;
	private static SimpleDateFormat simpleDateFormatObject=new SimpleDateFormat("yyyyMMdd",Locale.ENGLISH);
    private static final Logger logger = LogManager.getLogger(QueryManager.class);

	/**
	 * Gets the offset.
	 */
	long getOffset() {
		return offset;
	}
	/**
	 * Sets the offset.
	 * @param offset
	 * 				 offset value.
	 */
	void setOffset(long offset) {
		this.offset = offset;
	}
	/**
	 * Gets the number of records / returnLimit.
	 */
	long getNumberOfRecords() {
		return numberOfRecords;
	}
	/**
	 * Sets the number of records.
	 */
	void setNumberOfRecords(long numberOfRecords) {
		this.numberOfRecords = numberOfRecords;
	}


	/**
	 * Checks if a mapping exists for provided SNOMED id.
	 * @param id
	 * SNOMED concept id
	 * @return
	 * true/false
	 */
	boolean isMapExists(Long id)
	{
		boolean isMapExists = false;

		if( null == id)
			return isMapExists;

		try {
			Connection conn = ConnectionManager.getConnection();
			String query = "SELECT * FROM " + MappingConstants.ICD_MAP_TABLE + " where "+MappingConstants.REFERENCED_COMPONENT_ID+"="+id+" and "+MappingConstants.ICD_ACTIVE+"="+"1 ORDER BY mapGroup, mapPriority";
			Statement stmt =  conn.createStatement();
			ResultSet result = stmt.executeQuery(query);

			if (null != result && false != result.next())
				isMapExists = true;

			stmt.close();

		} catch (SQLException e) {
			// Log the Exception
            logger.error(LoggerUtility.MapICDException, LoggerUtility.createErrorMessage(e));

		}
		return isMapExists;
	}

	/**
	 * Fetches all the records for provided referencedID (SCTID) from the mapping database.
	 * @param id
	 * referencedID or SNOMED ID that need to be mapped
	 * @return
	 * list of all MappingComponents for mapping provided referencedID (SCTID)
	 */

	List<MappingComponent> getMappingSet(Long id)
	{
		if( null == id)
			return null;
		List<MappingComponent> listMappingComponents = new LinkedList<MappingComponent>();
		Connection conn = ConnectionManager.getConnection();
		String query = "SELECT * FROM " + MappingConstants.ICD_MAP_TABLE + " where referencedComponentId = "+id +" AND active=" + MappingConstants.ACTIVE + " AND (mapCategoryId=" + MappingConstants.CATAGORY_ID_MAP_SOURCE_PROPERLY_CLASSIFIED + " OR mapCategoryId=" + MappingConstants.CATAGORY_ID_MAP_SOURCE_CONTEXT_DEPENDENT + ") ORDER BY mapGroup, mapPriority";
		try(Statement stmt=conn.createStatement();ResultSet result= stmt.executeQuery(query)){
			if (null!=result)
				while(result.next()){
					MappingComponent mappingComponent = new MappingComponent();
					mappingComponent.setId(result.getString(1));
					mappingComponent.setEffectiveTime(simpleDateFormatObject.parse(result.getString(2)));
					mappingComponent.setActive(result.getByte(3));
					mappingComponent.setModuleId(result.getLong(4));
					mappingComponent.setRefsetId(result.getLong(5));
					mappingComponent.setReferencedComponentId(result.getLong(6));
					mappingComponent.setMapGroup(result.getInt(7));
					mappingComponent.setMapPriority(result.getInt(8));
					mappingComponent.setMapRule(result.getString(9));
					mappingComponent.setMapAdvice(result.getString(10));
					mappingComponent.setMapTarget(result.getString(11));
					mappingComponent.setCorrelationId(result.getLong(12));
					mappingComponent.setMapCategoryId(result.getLong(13));
					listMappingComponents.add(mappingComponent);
				}
		}catch (Exception e){
			// Log the Exception
            logger.error(LoggerUtility.MapICDException, LoggerUtility.createErrorMessage(e));
		}
		return listMappingComponents;
	}

	/**
	 * Create Snomed to ICD database from given international release files.<br>
	 * @param refsetICDMap - {@link String} file path upto snapshot from international release files.<br>
	 * @return {@link Boolean} value true/false.<br>
	 */
	boolean createICDMapdb(String refsetICDMap) {
		Connection conn = ConnectionManager.getConnection();
		try(Statement stm = conn.createStatement();){
		RunScript.execute(conn, new InputStreamReader(QueryManager.class.getClassLoader().getResourceAsStream("ICDMapH2Script.sql")));
		stm.execute("insert into "+MappingConstants.ICD_MAP_TABLE+" SELECT * FROM CSVREAD('"+refsetICDMap+"',null,'charset=UTF-8 fieldDelimiter= fieldSeparator='||chr(9))");
		logger.info(LoggerUtility.ConsoleWithLog, "Database created successfully.");
		}catch(SQLException e){
			// Log the Exception
            logger.error(LoggerUtility.MapICDException, LoggerUtility.createErrorMessage(e));
			return false;
		}
		return true;
	}


	/**
	 * Returns List of {@link MappingComponent}.<br>
	 * @param domainName - {@link String} Table name of ICD map.<br>
	 * @param offset - {@link Long} offset value for reading rows.<br>
	 * @param totalRecords - {@link Long} total number of rows.<br>
	 * @return List of {@link MappingComponent}.<br>
	 * @throws SQLException
	 * @throws ParseException
	 */
	List<MappingComponent> getDomain(String domainName, long offset,
			long totalRecords) throws SQLException, ParseException {
			PropertyReader.loadSystemProperties();
			Connection connection = ConnectionManager.getConnection();
			Statement statement = DBUtil.getStatement(connection);
			ResultSet resultSet = DBUtil.retrieveColumns(domainName,columnArray,statement, getOffset(), totalRecords,true);
			icdMappingComponentList = new ArrayList<MappingComponent>();
			if (domainName!=null)
				setMappingComponentList(resultSet);
			DBUtil.closeResultSet(resultSet);
			DBUtil.closeStatement(statement);
			return icdMappingComponentList;
	}

		/**
		 * Setting list of {@link MappingComponent}
		 * @param resultSet {@link ResultSet} pass the ResultSet of ICDMap table <br>
		 * @throws SQLException
		 * @throws ParseException
		 */
	private void setMappingComponentList(ResultSet resultSet) throws SQLException,ParseException{
		while (resultSet.next()){
			MappingComponent mappingComponentObj=new MappingComponent();
			mappingComponentObj.setId(resultSet.getString("ID"));
			mappingComponentObj.setEffectiveTime(simpleDateFormatObject.parse((resultSet.getString("EFFECTIVETIME"))));
			mappingComponentObj.setActive(Byte.parseByte(resultSet.getString("ACTIVE")));
			mappingComponentObj.setModuleId(Long.parseLong(resultSet.getString("MODULEID")));
			mappingComponentObj.setRefsetId(Long.parseLong(resultSet.getString("REFSETID")));
			mappingComponentObj.setReferencedComponentId(Long.parseLong(resultSet.getString("REFERENCEDCOMPONENTID")));
			mappingComponentObj.setMapGroup(Integer.parseInt(resultSet.getString("MAPGROUP")));
			mappingComponentObj.setMapPriority(Integer.parseInt(resultSet.getString("MAPPRIORITY")));
			mappingComponentObj.setMapRule(resultSet.getString("MAPRULE"));
			mappingComponentObj.setMapAdvice(resultSet.getString("MAPADVICE"));
			mappingComponentObj.setMapTarget(resultSet.getString("MAPTARGET"));
			mappingComponentObj.setCorrelationId(Long.parseLong(resultSet.getString("CORRELATIONID")));
			mappingComponentObj.setMapCategoryId(Long.parseLong(resultSet.getString("MAPCATEGORYID")));
			this.icdMappingComponentList.add(mappingComponentObj);
		}
	}
}
