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

import java.util.ArrayList;

@Repository("maintControlDesignEffectivenessDao")
public class MaintControlDesignEffectivenessDaoImpl extends AbstractDao implements MaintControlDesignEffectivenessDao{

	static final Logger logger = Logger.getLogger(MaintControlDesignEffectivenessDaoImpl.class);
	
	@Override
	public DataTableResponse getAllMaintControlDesignEffectiveness(String search, Integer orderColumn, String orderDirection,
			Integer page, Integer size,Integer legalEntityCode) {
		
		
	    Session session = getSession();
	    session.setDefaultReadOnly(true);
		List<MaintControlDesignEffectiveness> maintControlDesignEffectivenesss = null;
		
		DataTableResponse dataTableResponse=new DataTableResponse();
		StringBuilder queryString=new StringBuilder(
				
				         " SELECT "
						 + "  "
						 + " legal_entity_code, "
						 + " control_design_eff_code, "
						 + " control_design_eff_type,  "
						 + " control_design_eff_desc, "
						 + " control_design_eff_score, "
						 + " auth_rej_remarks, "

				         + " (select concat(gp.value , '-' , gp.description) from general_parameter gp where"
				         + " gp.key_1='ENTITY_STATUS' and gp.key_2='ENTITY_STATUS' and gp.value=c.entity_status "
				         + " and gp.legal_entity_code=c.legal_entity_code ) as entity_status ,  "
						
						 + " (select concat(gp.value , '-' , gp.description)"
						 + " from general_parameter gp where "
						 + " gp.key_1='STATUS' and gp.value=c.status "
						 + " and gp.legal_entity_code=c.legal_entity_code ) as status, "
						 
						 + "( select concat(u.user_id , '-' ,u.first_name,' ',u.last_name )"
						 + " from user u where "
						 + " u.user_id=c.maker "
						 + " and u.legal_entity_code=c.legal_entity_code ) as maker ,  "
						 
						 + " maker_timestamp, "
						 + " checker, "
						 + " checker_timestamp, "
						 + " businessdate_timestamp, "
						 + " sysdate_timestamp "
						 + "       "
						 + "     FROM "
						 + "         ( SELECT "
						 + "             *  "
						 + "         FROM "
						 + "             maint_control_design_eff "
						 + "         WHERE "
						 + "             legal_entity_code =:legal_entity_code "
						 + "         UNION ALL "
						 + "         SELECT "
						 + "             *  "
						 + "         FROM "
						 + "            maint_control_design_eff_wrk  "
						 + "         WHERE "
						 + "             legal_entity_code =:legal_entity_code "
						 + "              "
						 + "     ) AS c  "
						 + " WHERE "
						 + "    legal_entity_code =:legal_entity_code ");
		
		if(!BankAuditUtil.isEmptyString(search)){
			queryString=queryString.append(" and ( c.control_design_eff_code like :search "
					+ " or  c.control_design_eff_type like :search  "
					+ " or  c.control_design_eff_desc like :search  "
					+ " or  c.maker like :search  "
					+ " or  c.status IN (SELECT value FROM general_parameter WHERE key_1='STATUS' and  description LIKE :search ) "
					+ " or  c.control_design_eff_score  like :search  "
					+ " or  c.entity_status IN (SELECT value FROM general_parameter  WHERE key_1='ENTITY_STATUS' and key_2='ENTITY_STATUS' and description LIKE :search ) "
                    + " or  c.maker IN ( "
					+ " select u.user_id "
						 + " from user u where "
						 + " (concat(u.first_name , ' ' , u.last_name) like :search  ) "
						 + " and u.legal_entity_code =:legal_entity_code ) "
					+ " ) "
                    
					);
		}
		
	
		String[] columns={
				"c.control_design_eff_code",
				"c.control_design_eff_type",
				"c.control_design_eff_desc",
				"c.control_design_eff_score",
				"c.maker",
				"c.status",
				"c.entity_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, control_design_eff_score 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())
				.setResultTransformer(new ResultTransformer() {
					
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						
						MaintControlDesignEffectiveness maintControlDesignEffectiveness=null;
						
						if(tuple!=null){
							
							maintControlDesignEffectiveness=new MaintControlDesignEffectiveness();
							
							if(tuple[0]!=null){
								maintControlDesignEffectiveness.setLegalEntityCode(Integer.parseInt(tuple[0].toString()));
							}
							if(tuple[1]!=null){
								maintControlDesignEffectiveness.setControlDesignEffCode(tuple[1].toString());
							}
							if(tuple[2]!=null){
								maintControlDesignEffectiveness.setControlDesignEffType(tuple[2].toString());
							}
							if(tuple[3]!=null){
								maintControlDesignEffectiveness.setControlDesignEffDesc(tuple[3].toString());
							}
							if(tuple[4]!=null){
								maintControlDesignEffectiveness.setControlDesignEffScore(tuple[4].toString());
							}
							if(tuple[5]!=null){
								maintControlDesignEffectiveness.setAuthRejRemarks(tuple[5].toString());
							}
							if(tuple[6]!=null){
								maintControlDesignEffectiveness.setEntityStatus(tuple[6].toString());
							}
							if(tuple[7]!=null){
								maintControlDesignEffectiveness.setStatus(tuple[7].toString());
							}
							if(tuple[8]!=null){
								maintControlDesignEffectiveness.setMaker(tuple[8].toString());
							}
							if(tuple[9]!=null){
								maintControlDesignEffectiveness.setMakerTimestamp((Date)tuple[9]);
							}
							if(tuple[10]!=null){
								maintControlDesignEffectiveness.setChecker(tuple[10].toString());
							}
							if(tuple[11]!=null){
								maintControlDesignEffectiveness.setCheckerTimestamp((Date)tuple[11]);
							}
							if(tuple[12]!=null){
								maintControlDesignEffectiveness.setBusinessdateTimestamp((Date)tuple[12]);
							}
							if(tuple[13]!=null){
								maintControlDesignEffectiveness.setSysdateTimestamp((Date)tuple[13]);
							}
							
						}
						
						return maintControlDesignEffectiveness;
					}
					
					@Override
					public List transformList(List collection) {
						return collection;
					}
				})
				.setParameter("legal_entity_code", legalEntityCode);
		
	
		
		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}
		
		
		ScrollableResults resultScroll = query.scroll(ScrollMode.SCROLL_INSENSITIVE);
		if(resultScroll.first()&&resultScroll.scroll(page*size)){
			
			maintControlDesignEffectivenesss= new ArrayList<>();
			int i = 0;
			while (size > i++) {
				maintControlDesignEffectivenesss.add((MaintControlDesignEffectiveness) resultScroll.get(0));
			    if (!resultScroll.next())
			        break;
			}
			dataTableResponse.setData(maintControlDesignEffectivenesss);
			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 void deleteMaintControlDesignEffectiveness(Integer legalEntityCode, String controlDesignEffCode, String controlDesignEffType,
				String statusAuth) {

			
			Session session=getSession();
				
			if(!BankAuditConstant.STATUS_AUTH.equals(statusAuth)){
				session.createQuery("delete from MaintControlDesignEffectivenessWrk where legalEntityCode =:legalEntityCode  and "
						+ " controlDesignEffCode =:controlDesignEffCode "
						+ " and controlDesignEffType =:controlDesignEffType  ")
				.setParameter("controlDesignEffType", controlDesignEffType)
				.setParameter("controlDesignEffCode", controlDesignEffCode)
				.setParameter("legalEntityCode", legalEntityCode).executeUpdate();
				
			}else {

				session.createQuery("delete from MaintControlDesignEffectiveness where legalEntityCode =:legalEntityCode  and "
						+ " controlDesignEffCode =:controlDesignEffCode "
						+ " and controlDesignEffType =:controlDesignEffType  ")
				.setParameter("controlDesignEffType", controlDesignEffType)
				.setParameter("controlDesignEffCode", controlDesignEffCode)
				.setParameter("legalEntityCode", legalEntityCode).executeUpdate();
			}
			
		}

		@Override
		public Boolean isMaintControlDesignEffectiveness(Integer legalEntityCode, String controlDesignEffCode, String controlDesignEffType) {
			Session session=getSession();
			Long count=(Long)session.createQuery(
					" select count(*) "
					+ " from MaintControlDesignEffectiveness   "
					+ " where "
					+ " legalEntityCode =:legalEntityCode  "
					+ " and controlDesignEffType =:controlDesignEffType "
					+ " and controlDesignEffCode =:controlDesignEffCode  ")
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("controlDesignEffType", controlDesignEffType)
			.setParameter("controlDesignEffCode", controlDesignEffCode).uniqueResult();
			
			Long count1=(Long)session.createQuery(
			
					
					" select count(*) "
					+ " from MaintControlDesignEffectivenessWrk   "
					+ " where "
					+ " legalEntityCode =:legalEntityCode "
					+ " and controlDesignEffType =:controlDesignEffType "
					+ " and controlDesignEffCode =:controlDesignEffCode  ")
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("controlDesignEffType", controlDesignEffType)
			.setParameter("controlDesignEffCode", controlDesignEffCode).uniqueResult();
			Boolean flag=false;
			if((count1!=null && count1 >0)||(count!=null && count >0)){
				flag= true;
			}else {
				flag= false;
			}
			return flag;
		}
		
		@Override
		public Boolean validateScore(MaintControlDesignEffectiveness maintControlDesignEffectiveness) {
			Session session=getSession();
			logger.info("LE::"+maintControlDesignEffectiveness.getLegalEntityCode()+" ControlType::"+maintControlDesignEffectiveness.getControlDesignEffType()+" Score::"+maintControlDesignEffectiveness.getControlDesignEffScore()+" CriticalityCode::"+maintControlDesignEffectiveness.getControlDesignEffCode());
			Long scoreMstCnt=(Long)session.createQuery(  
					" select count(*) "
					+ " from MaintControlDesignEffectiveness   "
					+ " where "
					+ " legalEntityCode =:legalEntityCode  "
					+ " and controlDesignEffType =:controlDesignEffType "
					+ " and controlDesignEffCode <>:controlDesignEffCode "  // Condition added to ignore the score check for the same Criticality Code
					+ " and controlDesignEffScore =:controlDesignEffScore  ")
			.setParameter("legalEntityCode", maintControlDesignEffectiveness.getLegalEntityCode())
			.setParameter("controlDesignEffType", maintControlDesignEffectiveness.getControlDesignEffType())
			.setParameter("controlDesignEffCode", maintControlDesignEffectiveness.getControlDesignEffCode())
			.setParameter("controlDesignEffScore", maintControlDesignEffectiveness.getControlDesignEffScore()).uniqueResult();
			
			Long ScoreWrkCnt=(Long)session.createQuery(
					" select count(*) "
					+ " from MaintControlDesignEffectivenessWrk   "
					+ " where "
					+ " legalEntityCode =:legalEntityCode "
					+ " and controlDesignEffType =:controlDesignEffType "
					+ " and controlDesignEffCode <>:controlDesignEffCode " // Condition added to ignore the score check for the same Criticality Code
					+ " and controlDesignEffScore =:controlDesignEffScore  ")
					.setParameter("legalEntityCode", maintControlDesignEffectiveness.getLegalEntityCode())
					.setParameter("controlDesignEffType", maintControlDesignEffectiveness.getControlDesignEffType())
					.setParameter("controlDesignEffCode", maintControlDesignEffectiveness.getControlDesignEffCode())
					.setParameter("controlDesignEffScore", maintControlDesignEffectiveness.getControlDesignEffScore()).uniqueResult();
			
			logger.info("scoreMstCnt::"+scoreMstCnt +" ScoreWrkCnt::" +ScoreWrkCnt);
			Boolean flag=false;
			if((ScoreWrkCnt!=null && ScoreWrkCnt >0)||(scoreMstCnt!=null && scoreMstCnt >0)){
				flag= true;
			}
			return flag;
		}
}
