package com.bankaudit.dao;

import java.util.Collections;
import java.util.Date;
import java.util.List;

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.UserLeavePlanner;
import com.bankaudit.util.BankAuditUtil;
import com.bankaudit.util.ServiceStatus;


import java.util.ArrayList;

@Repository("userLeavePlannerDao")
public class UserLeavePlannerDaoImpl extends AbstractDao implements UserLeavePlannerDao{
	
	static final Logger logger = Logger.getLogger(UserLeavePlannerDaoImpl.class);
	
	@Override
	public DataTableResponse getAllUserLeaves(Integer legalEntityCodeStr,String search, Integer orderColumn, String orderDirection, Integer page, Integer size) {

		Session session = getSession();
		session.setDefaultReadOnly(true);
		List<UserLeavePlanner> leavesList = null;

		DataTableResponse dataTableResponse=new DataTableResponse();

		StringBuilder queryString=new StringBuilder(
				  " SELECT "
						  +" legal_entity_code, "
						  + "(select concat(u.user_id , '-' ,u.first_name,' ',u.last_name )"
						  	+ " from user u where "
						  	+ " u.user_id=aa.user_id "
						  	+ " and u.legal_entity_code=aa.legal_entity_code ) as user_id,  " // to get the User Name and User Id
						  + " leave_type, "
						  + " start_date, end_date,"
						  + " reason, reference, auth_rej_remarks, "
						  							 
						  + " (select concat(gp.value , '-' , gp.description)"
				    		 + " from general_parameter gp where "
				    		 + " gp.key_1='STATUS' and gp.value=aa.status "
				    		 + " and gp.legal_entity_code=aa.legal_entity_code ) as status, "
						  
						 + " (select concat(u.user_id , '-' ,u.first_name,' ',u.last_name )"
						 + " from user u where "
						 + " u.user_id=aa.maker "
						 + " and u.legal_entity_code=aa.legal_entity_code ) as maker ,  "	 
						  
						  +" maker_timestamp, "
						  +" checker, "
						  +" checker_timestamp, "
						  +" businessdate_timestamp, "
						  +" sysdate_timestamp, "
						  +" id "
						  +"  FROM "
						  +"  ( SELECT * FROM user_leave_planner WHERE legal_entity_code =:legal_entity_code "
						  +" UNION ALL "
						  +" SELECT * FROM user_leave_planner_wrk WHERE legal_entity_code =:legal_entity_code "
						  +"  "
						  +" ) AS aa WHERE legal_entity_code =:legal_entity_code "
				);

		if(!BankAuditUtil.isEmptyString(search)){
			queryString=queryString.append(" and ( aa.leave_type like :search "
					+ " or  aa.reason like :search  "
					+ " or  aa.reference like :search  "
					+ " or  aa.maker like :search  "
					+ " or  aa.maker in ( "
						+ " select u.user_id "
						+ " from user u where "
						+ " (concat(u.first_name , ' ' , u.last_name) like :search  ) "
						+ " and u.legal_entity_code=aa.legal_entity_code ) "
					+ " or  aa.user_id in ( "
						+ " select u.user_id "
						+ " from user u where "
						+ " (concat(u.first_name , ' ' , u.last_name) like :search  ) "
						+ " and u.legal_entity_code=aa.legal_entity_code ) "	
					+ " or DATE_FORMAT(aa.start_date, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(aa.end_date, '%d %b %Y') LIKE :search "
											
					+ " or  aa.status IN (SELECT value FROM general_parameter  WHERE key_1='STATUS' and  description LIKE :search ) "
					+ " )"
					);
		}

		String[] columns={
				"aa.user_id",
				"aa.start_date",
				"aa.end_date",
				"aa.leave_type",
				"aa.maker",
				"aa.status"
				};
		
		if(orderColumn != null && orderColumn == -1 && !BankAuditUtil.isEmptyString(orderDirection)){
			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 FIELD(STATUS,'DF') DESC ,  ").append(columns[orderColumn]).append(" ").append(orderDirection);
		}

		Query query=session.createSQLQuery(queryString.toString())
				.setResultTransformer(new ResultTransformer() {

					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						UserLeavePlanner userLeavePlanner=null;
						if(tuple!=null){
							
							userLeavePlanner=new UserLeavePlanner();
							
							if(tuple[0]!=null)	userLeavePlanner.setLegalEntityCode(Integer.parseInt(tuple[0].toString()));
							if(tuple[1]!=null)	userLeavePlanner.setUserId(tuple[1].toString());
							if(tuple[2]!=null)	userLeavePlanner.setLeaveType(tuple[2].toString());
							if(tuple[3]!=null)	userLeavePlanner.setStartDate((Date)tuple[3]);
							if(tuple[4]!=null)	userLeavePlanner.setEndDate((Date)tuple[4]);
							if(tuple[5]!=null)	userLeavePlanner.setReason(tuple[5].toString());
							if(tuple[6]!=null)	userLeavePlanner.setReference(tuple[6].toString());
							if(tuple[7]!=null)	userLeavePlanner.setAuthRejRemarks(tuple[7].toString());
							if(tuple[8]!=null) {
								userLeavePlanner.setStatus(tuple[8].toString());
								userLeavePlanner.setPrevStatus(tuple[8].toString());
							}
							if(tuple[9]!=null)  userLeavePlanner.setMaker(tuple[9].toString());
							if(tuple[10]!=null)	userLeavePlanner.setMakerTimestamp((Date)tuple[10]);
							if(tuple[11]!=null) userLeavePlanner.setChecker(tuple[11].toString());
							if(tuple[12]!=null)	userLeavePlanner.setCheckerTimestamp((Date)tuple[12]);
							if(tuple[13]!=null)	userLeavePlanner.setBusinessdateTimestamp((Date)tuple[13]);
							if(tuple[14]!=null)	userLeavePlanner.setSysdateTimestamp((Date)tuple[14]);
							if(tuple[15]!=null)	userLeavePlanner.setId(Integer.parseInt(tuple[15].toString()));
							
						}
						
						return userLeavePlanner;
					}

					@Override
					public List transformList(List collection) {
						return collection;
					}
				})
				.setParameter("legal_entity_code", legalEntityCodeStr)
				/*.setParameter("user_id", userId)*/;

		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}


		ScrollableResults resultScroll = query.scroll(ScrollMode.SCROLL_INSENSITIVE);
		if(resultScroll.first()&&resultScroll.scroll(page*size)){

			leavesList= new ArrayList<>();
			int i = 0;
			while (size > i++) {
				leavesList.add((UserLeavePlanner) resultScroll.get(0));
				if (!resultScroll.next())
					break;
			}
			dataTableResponse.setData(leavesList);
			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 List<UserLeavePlanner> getByUserIdLeaveType (Integer legalEntityCodeStr,String userId,String leaveType){

		Session session=getSession();
		UserLeavePlanner userLeavePlanner = new UserLeavePlanner();
		logger.info("legalEntityCodeStr .."+legalEntityCodeStr +" userId.."+userId+" leaveType.."+leaveType);
		List<UserLeavePlanner> userLeavePlannerLst = null;
		try {
			String queryStr1=" from  UserLeavePlannerDao  "
					+" WHERE legalEntityCode =:legalEntityCode AND userId =:userId  ";
			if(!BankAuditUtil.isEmptyString(leaveType)){
				queryStr1 += " AND leaveType=:leaveType";
			}
			
			Query query=session.createQuery(queryStr1).setParameter("legalEntityCode", legalEntityCodeStr)
					.setParameter("userId", userId);
			if(!BankAuditUtil.isEmptyString(leaveType)){
				query.setParameter("leaveType", leaveType);
			}
			
			userLeavePlannerLst=query.list();
			logger.info("userLeavePlanner .."+userLeavePlanner);
		}catch(Exception e) {
			e.printStackTrace();
		}
		return userLeavePlannerLst;
	}
	
	@Override
	public ServiceStatus validateUserLeaves(UserLeavePlanner userLeavePlanner) {

		ServiceStatus serviceStatus = null;
		Session session=getSession();
		logger.info("Inside DAOImp validateUserLeaves.."+ userLeavePlanner.getLegalEntityCode() + " Id.."+ userLeavePlanner.getId() );
		logger.info("StartDate.. "+ userLeavePlanner.getStartDate()+" endDate.."+userLeavePlanner.getEndDate());
		Integer legalEntityCode = userLeavePlanner.getLegalEntityCode();
		try {
			serviceStatus =new ServiceStatus();
			serviceStatus.setStatus("success");
			StringBuilder queryStr1=new StringBuilder("");
			queryStr1.append(" SELECT a.MstCnt +a.WrkCnt  FROM ( ");
			queryStr1.append("		SELECT  ");
			queryStr1.append("		(SELECT COUNT(*) FROM user_leave_planner_wrk WHERE legal_entity_code=:legalEntityCode AND  user_id= :userId AND "); 
			if(userLeavePlanner.getId() != null) {
				queryStr1.append(" id != :id AND ");
			}
			queryStr1.append(" 			((:startDate between start_date AND end_date)   OR  (:endDate between start_date AND end_date) OR (start_date between :startDate and :endDate ) ) ");
			queryStr1.append(" 		) as WrkCnt,");
			queryStr1.append("		(SELECT COUNT(*) FROM user_leave_planner WHERE legal_entity_code= :legalEntityCode AND user_id= :userId AND"); 
			if(userLeavePlanner.getId() != null) {
				queryStr1.append(" id != :id AND ");
			}
			queryStr1.append(" 			((:startDate between start_date AND end_date)   OR  (:endDate between start_date AND end_date) OR (start_date between :startDate and :endDate) ) ");
			queryStr1.append(" ) as MstCnt ");
			queryStr1.append(" FROM dual )  a ");
			
			Query query=session.createSQLQuery(queryStr1.toString());
			query.setParameter("legalEntityCode", userLeavePlanner.getLegalEntityCode());
			query.setParameter("userId", userLeavePlanner.getUserId());
			if(userLeavePlanner.getId() != null) query.setParameter("id",userLeavePlanner.getId());
			query.setParameter("startDate", userLeavePlanner.getStartDate());
			query.setParameter("endDate", userLeavePlanner.getEndDate());
				
			int duplicate = Integer.parseInt(query.uniqueResult().toString());
			if(duplicate > 0) {
				serviceStatus.setStatus("failure");
				serviceStatus.setMessage("Please modify existing record instead of adding a new record as the dates are common.");
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
			
		return serviceStatus;
	}
	
	@Override
	public void deleteUserLeavePlanner(Integer legalEntityCode,Integer id, String statusUnauth) {
		
		Session session=getSession();
		logger.info("deleteUserLeavePlanner .."+ legalEntityCode +" Id .."+ id +" statusUnauth.."+statusUnauth);
		try {
			int del =0;	
			if(!BankAuditConstant.STATUS_AUTH.equals(statusUnauth)){
				del=session.createQuery("delete from UserLeavePlannerWrk where "
						+ " legalEntityCode =:legalEntityCodeStr  AND "
						+ " id =:id  ")
				.setParameter("legalEntityCodeStr", legalEntityCode)
				.setParameter("id", id).executeUpdate();
			}else {
				del=session.createQuery("delete from UserLeavePlanner where "
						+ " legalEntityCode =:legalEntityCodeStr  AND "
						+ " id =:id  ")
				.setParameter("legalEntityCodeStr", legalEntityCode)
				.setParameter("id", id).executeUpdate();
			}
			logger.info("The number of records deleted .."+ del);
		}catch(Exception e) {
			e.printStackTrace();
		}
	
		
	}
	

}
