package com.bankaudit.dao;

import java.util.Arrays;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.TemporalType;

import org.hibernate.Query;
import org.hibernate.ScrollMode;
import org.hibernate.ScrollableResults;
import org.hibernate.Session;
import org.hibernate.transform.ResultTransformer;
import org.jboss.logging.Logger;
import org.springframework.stereotype.Repository;

import com.bankaudit.constants.BankAuditConstant;
import com.bankaudit.dto.DataTableResponse;
import com.bankaudit.model.MaintEntity;
import com.bankaudit.model.MaintHoliday;
import com.bankaudit.util.BankAuditUtil;
import com.bankaudit.util.ServiceStatus;


import java.util.ArrayList;

@Repository("maintHolidayDao")
public class MaintHolidayDaoImpl  extends AbstractDao implements MaintHolidayDao {
	
	static final Logger logger = Logger.getLogger(MaintHolidayDaoImpl.class);

	@Override
	public List<MaintHoliday> getMaintHoliday(Integer legalEntityCode, String calendarYear, String holidayType,
			String countryAlpha3Code, String stateCode, Date holidayDate, String weekday, String status) {

		
		Session session=getSession();
		

		String queryStr1=" from  MaintHoliday  ";
		StringBuilder queryStr2=new StringBuilder("");
		
		Boolean begun=false; 
		
		if(legalEntityCode!=null){
			queryStr2.append("and legalEntityCode =:legalEntityCode ");
			begun=true;
		}
		
		if(!BankAuditUtil.isEmptyString(calendarYear)){
				queryStr2.append("and calendarYear =:calendarYear ");
				begun=true;
		}
		
		if(!BankAuditUtil.isEmptyString(holidayType)){
			queryStr2.append("and holidayType =:holidayType ");
			begun=true;
		}
		
		if(!BankAuditUtil.isEmptyString(countryAlpha3Code)){
			queryStr2.append("and countryAlpha3Code =:countryAlpha3Code ");
			begun=true;
		}
		
		if(!BankAuditUtil.isEmptyString(stateCode)){
			queryStr2.append("and stateCode =:stateCode ");
			begun=true;
		}
		
		if(!BankAuditUtil.isEmptyString(weekday)){
			queryStr2.append("and weekday =:weekday ");
			begun=true;
		}
		
		if(!BankAuditUtil.isEmptyString(status)){
			queryStr2.append("and status =:status ");
			begun=true;
		}
		

		if(holidayDate!=null){
			queryStr2.append("and holidayDate =:holidayDate ");
			begun=true;
		}
	
		if(begun){
			queryStr1=queryStr1+" where "+queryStr2.substring(4);
		}
		
		Query query=session.createQuery(queryStr1);
		
		
		if(legalEntityCode!=null){
			query.setParameter("legalEntityCode", legalEntityCode);
		}
		
		if(!BankAuditUtil.isEmptyString(calendarYear)){
			query.setParameter("calendarYear", calendarYear);
		}
		
		if(!BankAuditUtil.isEmptyString(holidayType)){
			query.setParameter("holidayType", holidayType);
		}
		
		if(!BankAuditUtil.isEmptyString(countryAlpha3Code)){
			query.setParameter("countryAlpha3Code", countryAlpha3Code);
		}
		
		if(!BankAuditUtil.isEmptyString(stateCode)){
			query.setParameter("stateCode", stateCode);
		}
		
		if(!BankAuditUtil.isEmptyString(weekday)){
			query.setParameter("weekday", weekday);
		}
		
		if(!BankAuditUtil.isEmptyString(status)){
			query.setParameter("status", status);
		}
		
		if(holidayDate!=null){
			query.setParameter("holidayDate", holidayDate);
		}
		
		return query.list();
		
		
	}

	@Override
	public DataTableResponse getMaintHolidays(Integer legalEntityCodeStr, String search, Integer orderColumn, String orderDirection, Integer page,
			Integer size) {

	    Session session = getSession();
	    session.setDefaultReadOnly(true);
		List<MaintHoliday> maintHolidays = null;
		
		DataTableResponse dataTableResponse=new DataTableResponse();
		StringBuilder queryString=new StringBuilder(
				
				
				          " SELECT "
						+ " legal_entity_code , "
						+ " id , "
						
						+ " (select concat(gp.value , '-' , gp.description) "
						+ " from general_parameter gp where "
						+ " gp.key_1='HOLIDAY_TYPE' and gp.value=h.holiday_type "
						+ " and gp.legal_entity_code=h.legal_entity_code ) as holiday_type , "
						
						+ " calendar_year, "
						
			            + " (select distinct concat(u.country_alpha3_code , '-' ,u.country_name ) "
			            + " from maint_address u where "
			            + " u.country_alpha3_code=h.country_alpha3_code "
			            + " and u.legal_entity_code=h.legal_entity_code ) as country_alpha3_code , "
                        
			            + " (select distinct concat(u.state_code , '-' ,u.state_name ) "
			            + " from maint_address u where "
			            + " u.state_code=h.state_code "
			            + " and u.country_alpha3_code=h.country_alpha3_code "
			            + " and u.legal_entity_code=h.legal_entity_code ) as state_code , "
						
						+ " holiday_date  , "
						+ " auth_rej_remarks  , "
						+ " description , "
					
						 + "(select concat(gp.value , '-' , gp.description)"
						 + " from general_parameter gp where "
						 + " gp.key_1='WEEK_DAY' and gp.value=h.weekday "
						 + " and gp.legal_entity_code=h.legal_entity_code ) as weekday ,  "
						
						 + " (select concat(gp.value , '-' , gp.description) "
						 + " from general_parameter gp where "
						 + " gp.key_1='ENTITY_STATUS' and gp.value=h.entity_status "
						 + " and gp.legal_entity_code=h.legal_entity_code ) as entity_status , "
						 
						 + " (select concat(gp.value , '-' , gp.description) "
						 + " from general_parameter gp where "
						 + " gp.key_1='STATUS' and gp.value=h.status "
						 + " and gp.legal_entity_code=h.legal_entity_code ) as status , "
						 
						
						   + " (select concat(u.user_id , '-' ,u.first_name,' ',u.last_name )"
						   + " from user u where "
						   + " u.user_id=h.maker "
						   + " and u.legal_entity_code=h.legal_entity_code ) as maker ,  "
						
						+ " maker_timestamp  , "
						+ " checker , "
						+ " checker_timestamp , "
						+ " businessdate_timestamp , "
						+ " sysdate_timestamp "
						+ " FROM "
						+ " ( "
						+ " SELECT * FROM maint_holiday WHERE legal_entity_code=:legal_entity_code "
						+ " UNION ALL "
						+ " SELECT * FROM maint_holiday_wrk WHERE legal_entity_code=:legal_entity_code "
						+ " ) h WHERE legal_entity_code=:legal_entity_code "
			);
			
		if(!BankAuditUtil.isEmptyString(search)){
			queryString=queryString.append(" and ( "
					+ "    h.holiday_type IN (SELECT value FROM general_parameter  WHERE key_1='HOLIDAY_TYPE' and  description LIKE :search ) "
					+ " or h.calendar_year like :search"
					+ " or h.holiday_date like :search"
					+ " or DATE_FORMAT(h.holiday_date, '%d %b %Y') LIKE :search "
					+ " or h.description like :search"
					+ " or h.maker like :search"
					+ " or h.maker in ( select u.user_id from user u where (concat(u.first_name , ' ' , u.last_name) like :search ) and u.legal_entity_code=h.legal_entity_code ) "
					+ " or h.status IN (SELECT value FROM general_parameter  WHERE key_1='STATUS' and  description LIKE :search ) "
					+ " or h.entity_status IN (SELECT value FROM general_parameter  WHERE key_1='ENTITY_STATUS' and key_2='ENTITY_STATUS' and description LIKE :search ) "
					
					/*+ " or  h.country_alpha3_code IN (SELECT country_alpha3_code FROM maint_address WHERE legal_entity_code=h.legal_entity_code AND country_name LIKE :search ) "
					+ " or  h.state_code IN (SELECT state_code FROM maint_address WHERE legal_entity_code=h.legal_entity_code "
					+ " 					AND country_alpha3_code =h.country_alpha3_code AND state_name LIKE :search ) "
					+ " or  h.weekday IN (SELECT value FROM general_parameter  WHERE key_1='WEEK_DAY' and  description LIKE :search ) " */
					+ ") ");
		}
	
		String[] columns={
				"h.holiday_type",
				"h.calendar_year",
				"h.holiday_date",
				"h.description",
				"h.maker",
				"h.entity_status",
				"h.status"
				};
		
		if(orderColumn != null && orderColumn == -1 && !BankAuditUtil.isEmptyString(orderDirection)){ // -1 send for the first time from the UI during landing page.
			queryString=queryString.append(" order by FIELD(STATUS,'DF','R','RM','U','M','A') ASC  "); // The code modified to get the records in the order of Status on landing page, the sequence is  Draft, Reject, Unauth, Modify, Auth
		}else if(orderColumn!=null && !BankAuditUtil.isEmptyString(orderDirection)){
			queryString=queryString.append(" order by ").append(columns[orderColumn]).append(" ").append(orderDirection);
		}
		
		Query query=session.createSQLQuery(queryString.toString()).setParameter("legal_entity_code", legalEntityCodeStr)
				.setResultTransformer(new ResultTransformer() {
					
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						MaintHoliday maintHoliday=null;
						
						if(tuple!=null){
							maintHoliday=new MaintHoliday();
							
							if(tuple[0]!=null){ maintHoliday.setLegalEntityCode(Integer.parseInt(tuple[0].toString())); }
							if(tuple[1]!=null){ maintHoliday.setId(Integer.parseInt(tuple[1].toString())); }
							if(tuple[2]!=null){ maintHoliday.setHolidayType(tuple[2].toString()); }
							if(tuple[3]!=null){ maintHoliday.setCalendarYear(tuple[3].toString()); }
							if(tuple[4]!=null){ maintHoliday.setCountryAlpha3Code(tuple[4].toString()); }
							if(tuple[5]!=null){ maintHoliday.setStateCode(tuple[5].toString()); }
							if(tuple[6]!=null){ maintHoliday.setHolidayDate((Date)tuple[6]); }
							if(tuple[7]!=null){ maintHoliday.setAuthRejRemarks(tuple[7].toString()); }
							if(tuple[8]!=null){ maintHoliday.setDescription(tuple[8].toString()); }
							if(tuple[9]!=null){ maintHoliday.setWeekday(tuple[9].toString()); }
							if(tuple[10]!=null){ maintHoliday.setEntityStatus(tuple[10].toString()); }
							if(tuple[11]!=null){ maintHoliday.setStatus(tuple[11].toString()); }
							if(tuple[12]!=null){ maintHoliday.setMaker(tuple[12].toString()); }
							if(tuple[13]!=null){ maintHoliday.setMakerTimestamp((Date)tuple[13]); }
							if(tuple[14]!=null){ maintHoliday.setChecker(tuple[14].toString()); }
							if(tuple[15]!=null){ maintHoliday.setCheckerTimestamp((Date)tuple[15]); }
							if(tuple[16]!=null){ maintHoliday.setBusinessdateTimestamp((Date)tuple[16]); }
							if(tuple[17]!=null){ maintHoliday.setSysdateTimestamp((Date)tuple[17]); }
							
						}
						return maintHoliday;
					}
					
					@Override
					public List transformList(List collection) {
						return collection;
					}
				});
		
		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}
		
		
		ScrollableResults resultScroll = query.scroll(ScrollMode.SCROLL_INSENSITIVE);
		if(resultScroll.first()&&resultScroll.scroll(page*size)){
			
			maintHolidays= new ArrayList<>();
			int i = 0;
			while (size > i++) {
				maintHolidays.add((MaintHoliday) resultScroll.get(0));
			    if (!resultScroll.next())
			        break;
			}
			dataTableResponse.setData(maintHolidays);
			resultScroll.last();
			if(size!=0){
				dataTableResponse.setRecordsFiltered((resultScroll.getRowNumber()+1l/size)+1);
				dataTableResponse.setRecordsTotal((resultScroll.getRowNumber()+1l/size)+1);
			}else{
				dataTableResponse.setError("page size zero");
				dataTableResponse.setData(Collections.EMPTY_LIST);
				dataTableResponse.setRecordsTotal(0l);
				dataTableResponse.setRecordsFiltered(0l);
			}
			
		}else{
			dataTableResponse.setError(null);
			dataTableResponse.setData(Collections.EMPTY_LIST);
			dataTableResponse.setRecordsTotal(0l);
			dataTableResponse.setRecordsFiltered(0l);
		}
		return dataTableResponse;
}
	
	@Override
	public ServiceStatus validateHoliday(MaintHoliday maintHoliday) {

		ServiceStatus serviceStatus = null;
		Session session=getSession();
		String holidayType = null;
		String holidayDateCond = "";
		String stateCodeCond = "";
		String updateCondition ="";
		StringBuilder queryStateTmp= new StringBuilder();
		

		serviceStatus =new ServiceStatus();
		serviceStatus.setStatus("success");
		holidayType = maintHoliday.getHolidayType();

		System.out.println("Inside validateHoliday() ...."+holidayType);
		
		if(("N").equalsIgnoreCase(holidayType)) {
			holidayDateCond=" and STR_TO_DATE(holiday_date, '%Y-%m-%d') =STR_TO_DATE(:holidayDate, '%Y-%m-%d') "; // This condition is for the date 
		
			//This code is to get the State Codes and verify whether the same date for the selected states is present or not
			String[] stateCodeSplit = maintHoliday.getStateCode().split(",");
			for(int i=0; i<stateCodeSplit.length;i++) {
				if(i ==0) queryStateTmp.append( " and (  ");
				queryStateTmp.append("  FIND_IN_SET ("+stateCodeSplit[i]+", state_code) != 0 ");
				if(i < stateCodeSplit.length-1) queryStateTmp.append( " or" );
				else queryStateTmp.append( " )" );
			}// end here, State code
		}
		logger.info("queryStateTmp ...."+queryStateTmp);
		
		//check for update Status		 
		if(maintHoliday.getId() !=null && maintHoliday.getId()>0){
			updateCondition =" and id !=:updateId";
		}
		
		
		StringBuilder queryStr1=new StringBuilder("");
		queryStr1.append(" select a.MstCnt +a.WrkCnt  from ( ");
		queryStr1.append("		select  ");
		queryStr1.append("		(select count(*) as wrk_cnt from maint_holiday_wrk where legal_entity_code=:legalEntityCode and holiday_type=:holidayType and country_alpha3_code=:countryAlpha3Code  ");
		queryStr1.append(holidayDateCond);
		queryStr1.append(updateCondition);
		queryStr1.append(queryStateTmp);
		queryStr1.append(" ) as WrkCnt,");
		queryStr1.append("		(select count(*) as mst_cnt from maint_holiday where legal_entity_code=:legalEntityCode and holiday_type=:holidayType and country_alpha3_code=:countryAlpha3Code  ");
		queryStr1.append(holidayDateCond);
		queryStr1.append(updateCondition);
		queryStr1.append(queryStateTmp);
		queryStr1.append(" ) as MstCnt ");
		queryStr1.append("		from dual )  a ");
		
		Query query=session.createSQLQuery(queryStr1.toString());
		query.setParameter("legalEntityCode", maintHoliday.getLegalEntityCode());
		query.setParameter("holidayType",holidayType);
		query.setParameter("countryAlpha3Code", maintHoliday.getCountryAlpha3Code());
		if(("N").equalsIgnoreCase(holidayType)) query.setParameter("holidayDate",maintHoliday.getHolidayDate());
		if(maintHoliday.getId() !=null && maintHoliday.getId()>0){ query.setParameter("updateId",maintHoliday.getId());  	}
		
		int duplicate = Integer.parseInt(query.uniqueResult().toString());
		if(duplicate > 0) {
			serviceStatus.setStatus("failure");
			if(maintHoliday.getId() !=null && maintHoliday.getId()>0){
				serviceStatus.setMessage("The date already marked as Holiday for "+maintHoliday.getDescription());
			}
			else if("N".equalsIgnoreCase(holidayType)) {
				serviceStatus.setMessage("The date already marked as Holiday");
			}else
				serviceStatus.setMessage("The "+maintHoliday.getDescription() +" holiday's already defined in the system");
		}else {   // This is to validate the Days selected in Weekly Off and Fortnightly Off, not to have same days
			if(!("N").equalsIgnoreCase(holidayType)) {
				String othHolidayType = (holidayType.equalsIgnoreCase("W")?"B":"W");  // If Type is Weekly off, then need to compare with Fortnightly off.
				/*StringBuilder queryStr2=new StringBuilder(" select count(*) from  MaintHoliday  ");
				String[] weekDay = maintHoliday.getWeekday().split(",");
				queryStr2.append("where holidayType =:holidayType "
						+ " and legalEntityCode =:legalEntityCode ");
				for(int i=0; i<weekDay.length;i++) {
					if(i ==0) queryStr2.append( " and (  ");
					queryStr2.append(" weekday like ('%"+weekDay[i]+"%') ");
					if(i < weekDay.length-1) queryStr2.append( " or" );
					else queryStr2.append( " )" );
				}
				Query query1=session.createQuery(queryStr2.toString());*/
				
				StringBuilder queryTmp= new StringBuilder();
				String[] weekDay = maintHoliday.getWeekday().split(",");
				for(int i=0; i<weekDay.length;i++) {
					if(i ==0) queryTmp.append( " and (  ");
					queryTmp.append(" weekday like ('%"+weekDay[i]+"%') ");
					if(i < weekDay.length-1) queryTmp.append( " or" );
					else queryTmp.append( " )" );
				}
				
				logger.info("queryTmp :: "+ queryTmp);
				
				StringBuilder queryStr2=new StringBuilder("select a.MstCnt +a.WrkCnt  from (" );
				queryStr2.append("  select ");
				queryStr2.append(" (select count(*) as MstCnt from  maint_holiday where legal_entity_code=:legalEntityCode and holiday_type=:holidayType ");
				queryStr2.append( queryTmp );
				queryStr2.append(updateCondition);
				queryStr2.append(" ) as MstCnt,");
				queryStr2.append(" (select count(*) as WrkCnt from  maint_holiday_wrk where legal_entity_code=:legalEntityCode and holiday_type=:holidayType ");
				queryStr2.append( queryTmp );
				queryStr2.append(updateCondition);
				queryStr2.append(" ) as WrkCnt ");
				queryStr2.append("		from dual )  a ");
						
				logger.info("queryStr2 :: "+ queryStr2);
				
				Query query1=session.createSQLQuery(queryStr2.toString());
				query1.setParameter("holidayType",othHolidayType);
				query1.setParameter("legalEntityCode",maintHoliday.getLegalEntityCode());
				if(maintHoliday.getId()!=null && maintHoliday.getId()>0){ query1.setParameter("updateId",maintHoliday.getId());  	}

				int duplicateday = Integer.parseInt(query1.uniqueResult().toString());
				if(duplicateday > 0) {
					serviceStatus.setStatus("failure");					
					serviceStatus.setMessage("The days already marked holiday for "+(holidayType.equalsIgnoreCase("B")?"Weekly off":"Fortnightly off"));
				}
			}
		}
		return serviceStatus;
	}

	@Override
	public void deleteMaintHoliday(Integer id, String statusUnauth) {
		
		Session session=getSession();
			
		if(!BankAuditConstant.STATUS_AUTH.equals(statusUnauth)){
			session.createQuery("delete from MaintHolidayWrk where "
					+ " id =:id   "
					+ " ")
			.setParameter("id", id).executeUpdate();
		}else {

			session.createQuery("delete from MaintHoliday where "
					+ "  id =:id  ")
			.setParameter("id", id).executeUpdate();
		}	
		
	}

	@Override
	public boolean isMaintHoliday(Integer id, String statusUnauth) {
		Session session=getSession();
		Long count;
		
		if(BankAuditConstant.STATUS_AUTH.equalsIgnoreCase(statusUnauth)){
			 count=(Long) session.createQuery(" select count(*) from MaintHoliday "
						+ " where id =:id ").setParameter("id", id).uniqueResult();
		}else{
			
			 count=(Long) session.createQuery(" select count(*) from MaintHolidayWrk "
						+ " where id =:id ").setParameter("id", id).uniqueResult();
		}
		Boolean flag=false;
		if(count!=null && count>0){
			flag= true;
		}else{
			flag= false;
		}
		return flag;
	}
	
	/************** 
	 ***** Start -- Get holidays -> add the number of holidays between two date *****
	**************/

	@Override
	public Map<String,List<String>> getWeeklyAndFortnightlyHoliday(Integer legalEntityCode,String unitCode){
		 
		Map<String,List<String>> weeklyAndFortnightlyHoliday =  new HashMap<String, List<String>>();
		try { 
			Session session=getSession(); 
			session.setDefaultReadOnly(true);
			String hql="  Select me from MaintEntity me "
					+ " Where me.legalEntityCode = :legalEntityCode "
					+ " and me.unitCode = :unitCode " ;
			MaintEntity maintEntity = (MaintEntity) session.createQuery(hql)
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("unitCode", unitCode).uniqueResult();
			
			String weeklyHoliday = maintEntity.getWeeklyHoliday();
			String fortnightlyHoliday = maintEntity.getFortnightlyHoliday();
					
			weeklyHoliday = weeklyHoliday==null || weeklyHoliday.trim().equals("") ?null:weeklyHoliday;
			fortnightlyHoliday = fortnightlyHoliday==null ||  fortnightlyHoliday.trim().equals("") ?null:fortnightlyHoliday;
			
			if(weeklyHoliday==null && fortnightlyHoliday==null){
				return weeklyAndFortnightlyHoliday;
			} 		
			
			if(weeklyHoliday!=null){
				weeklyAndFortnightlyHoliday.put("W", Arrays.asList(weeklyHoliday.split(",")));
			}if(fortnightlyHoliday!=null){
				weeklyAndFortnightlyHoliday.put("F", Arrays.asList(fortnightlyHoliday.split(",")));
			}			
			
		}catch(Exception e) {
			logger.error(e.getMessage(),e);			 
		} 
		
		return weeklyAndFortnightlyHoliday;
	}
	
	@Override
	public List<Date> getNormalHolidaysCount(Integer legalEntityCode,String unitCode, Date startDate,Date endDate){
		try { 
			Session session=getSession();
			session.setDefaultReadOnly(true);
			String sql="  Select distinct holiday_date from audit_holiday_dates_v holiday "
					+ " Where holiday.legal_entity_code = :legalEntityCode "
					+ " and holiday.unit_code = :unitCode "
					+ " and holiday_date between :startDate and :endDate "
					+ " order by holiday_date ASC" ;
			Query qry =  session.createNativeQuery(sql)
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("unitCode", unitCode)
			.setParameter("startDate", startDate, TemporalType.DATE)
			.setParameter("endDate", endDate, TemporalType.DATE)
			.setResultTransformer(new ResultTransformer() {				
				@Override
				public Object transformTuple(Object[] tuple, String[] aliases) {
					Date date = null;
					if(tuple[0] !=null){
						date = (Date) tuple[0];
					}
					return date;
				}
				
				@Override
				public List transformList(List collection) { 
					return collection;
				}
			}); 
			 								 			
			return  qry.list();
			
		}catch(Exception e) {
			logger.error(e.getMessage(),e);			 
		} 
		
		return null;
	}
		
	/************** 
	 ***** End -- Get holidays -> add the number of holidays between two date *****
	**************/
	 
}
