package com.bankaudit.dao;

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

import javax.persistence.Column;
import javax.persistence.Id;

import org.hibernate.Query;
import org.hibernate.ScrollMode;
import org.hibernate.ScrollableResults;
import org.hibernate.Session;
import org.hibernate.transform.ResultTransformer;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.bankaudit.constants.BankAuditConstant;
import com.bankaudit.dto.DataTableResponse;
import com.bankaudit.model.RiskScoreAuditSubgrp;
import com.bankaudit.model.RiskScorePercentage;
import com.bankaudit.util.BankAuditUtil;

import java.util.ArrayList;

@Repository("riskScorePercentageDao")
public class RiskScorePercentageDaoImpl extends AbstractDao implements RiskScorePercentageDao {

	@SuppressWarnings("unchecked")
	@Override
	public List<RiskScorePercentage> getRiskScorePercentagesExceptThis(Integer legalEntityCode, String auditTypeCode,
			String riskCategoryCode) {
		Session session=getSession();
		return session.createQuery("from RiskScorePercentage where "
				+ " legalEntityCode !=:legalEntityCode and "
				+ " auditTypeCode !=:auditTypeCode and "
				+ " riskCategoryCode !=:riskCategoryCode")
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("auditTypeCode", auditTypeCode)
		.setParameter("riskCategoryCode", riskCategoryCode).list();
		

	}

	@Override
	public DataTableResponse getRiskScorePercentages(Integer legalEntityCode, String search, Integer orderColumn,
			String orderDirection, Integer page, Integer size) {
		
		
	    Session session = getSession();
		List<RiskScorePercentage> riskScorePercentages = null;
		
		DataTableResponse dataTableResponse=new DataTableResponse();

		StringBuilder queryString=new StringBuilder(
				
				
				        " SELECT "
						+" legal_entity_code , "
						
						+ "(select concat(atd.audit_type_code , '-' , atd.audit_type_desc)"
						+ " from maint_audit_type_desc atd where "
						+ " atd.audit_type_code=rsp.audit_type_code "
						+ " and atd.legal_entity_code=rsp.legal_entity_code ) as audit_type_code , "
					
						+" minimun_per  , "
						+" maximum_per , "
						+" risk_category_code   , "
						+" risk_category_desc , "
						+" 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=rsp.entity_status "
				        + " and gp.legal_entity_code=rsp.legal_entity_code ) as entity_status ,  "
				         
						+ " (select concat(gp.value , '-' , gp.description)"
						+ " from general_parameter gp where "
						+ " gp.key_1='STATUS' and gp.value=rsp.status "
						+ " and gp.legal_entity_code=rsp.legal_entity_code ) as status, "
 
						+ "(select concat(u.user_id , '-' ,u.first_name,' ',u.last_name )"
						+ " from user u where "
						+ " u.user_id=rsp.maker "
						+ " and u.legal_entity_code=rsp.legal_entity_code ) as maker ,  "
	 
						+" maker_timestamp    , "
						+" checker , "
						+" checker_timestamp  , "
						+" businessdate_timestamp  , "
						+" sysdate_timestamp  "
						+" FROM (   "
						+" SELECT * FROM risk_score_percentage  "
						+" WHERE legal_entity_code =:legal_entity_code and status !='D' "
						+" UNION ALL "
						+" SELECT * FROM risk_score_percentage_wrk WHERE legal_entity_code =:legal_entity_code "
						+ " and status !='D'  "
						+"  )  rsp  WHERE legal_entity_code=:legal_entity_code "
				
				);
		
		
		if(!BankAuditUtil.isEmptyString(search)){
			queryString=queryString.append(" and ( "
			
					+ " rsp.audit_type_code IN ( "
					+ " select atd.audit_type_code "
					+ " from maint_audit_type_desc atd where "
					+ " atd.audit_type_desc LIKE :search "
					+ " and atd.legal_entity_code=rsp.legal_entity_code  "
					+ " ) "
					+ " or  rsp.audit_type_code like :search  "
					+ " or  rsp.minimun_per like :search  "
					+ " or  rsp.maximum_per like :search  "
					+ " or  rsp.risk_category_code like :search  "
					+ " or  rsp.risk_category_desc like :search  "
					+ " or  rsp.maker like :search  "
	                + " or  rsp.status IN (SELECT value FROM general_parameter  WHERE key_1='STATUS' and  description LIKE :search ) "
                    + " or  rsp.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 ) "
                    + " or  rsp.entity_status IN (SELECT value FROM general_parameter  WHERE key_1='ENTITY_STATUS' and key_2='ENTITY_STATUS' and description LIKE :search ) "
					+ " ) ");
		}
/*		audit_type_code     
		minimun_per         
		maximum_per         
		risk_category_code  
		risk_category_desc  
		status              
		maker               */
		String[] columns={
				"rsp.audit_type_code",
				"rsp.risk_category_code",
				"rsp.risk_category_desc",
				"rsp.minimun_per",
				"rsp.maximum_per",
				"rsp.maker",
				"rsp.entity_status",
				"rsp.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())
				.setResultTransformer(new ResultTransformer() {
					
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						RiskScorePercentage  riskScorePercentage=null;
						if(tuple!=null){
							riskScorePercentage=new RiskScorePercentage();
							
							if(tuple[0]!=null){ riskScorePercentage.setLegalEntityCode(Integer.parseInt(tuple[0].toString())); }
							if(tuple[1]!=null){ riskScorePercentage.setAuditTypeCode(tuple[1].toString()); }
							if(tuple[2]!=null){ riskScorePercentage.setMinimunPer(Integer.parseInt(tuple[2].toString())); }
							if(tuple[3]!=null){ riskScorePercentage.setMaximumPer(Integer.parseInt(tuple[3].toString())); }
							if(tuple[4]!=null){ riskScorePercentage.setRiskCategoryCode(tuple[4].toString()); }
							if(tuple[5]!=null){ riskScorePercentage.setRiskCategoryDesc(tuple[5].toString()); }
							if(tuple[6]!=null){ riskScorePercentage.setAuthRejRemarks(tuple[6].toString()); }
							if(tuple[7]!=null){ riskScorePercentage.setEntityStatus(tuple[7].toString()); }
							if(tuple[8]!=null){ riskScorePercentage.setStatus(tuple[8].toString()); }
							if(tuple[9]!=null){ riskScorePercentage.setMaker(tuple[9].toString()); }
							if(tuple[10]!=null){ riskScorePercentage.setMakerTimestamp((Date)tuple[10]); }
							if(tuple[11]!=null){ riskScorePercentage.setChecker(tuple[11].toString()); }
							if(tuple[12]!=null){ riskScorePercentage.setCheckerTimestamp((Date)tuple[12]); }
							if(tuple[13]!=null){ riskScorePercentage.setBusinessdateTimestamp((Date)tuple[13]); }
							if(tuple[14]!=null){ riskScorePercentage.setSysdateTimestamp((Date)tuple[14]); }
						}
						return riskScorePercentage;
					}
					
					@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)){
			
			riskScorePercentages= new ArrayList<>();
			int i = 0;
			while (size > i++) {
				riskScorePercentages.add((RiskScorePercentage) resultScroll.get(0));
			    if (!resultScroll.next())
			        break;
			}
			dataTableResponse.setData(riskScorePercentages);
			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 Boolean isRiskScorePercentage(Integer legalEntityCode, String auditTypeCode) {

		
Session session=getSession();
		
		Long count=(Long)session.createQuery(
	
				
				" select count(*) "
				+ " from RiskScorePercentage   "
				+ " where "
				+ " legalEntityCode =:legalEntityCode "
				+ " and auditTypeCode =:auditTypeCode  ")
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("auditTypeCode", auditTypeCode).uniqueResult();
		
		Long count1=(Long)session.createQuery(
		
				
				" select count(*) "
				+ " from RiskScorePercentageWrk   "
				+ " where "
				+ " legalEntityCode =:legalEntityCode "
				+ " and auditTypeCode =:auditTypeCode  ")
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("auditTypeCode", auditTypeCode).uniqueResult();
		Boolean flag;
		if((count1!=null && count1 >0)||(count!=null && count >0)){
			flag= true;
		}else {
			flag= false;
		}
	return flag;
	}

	@Override
	public void deleteRiskScorePercentages(Integer legalEntityCode, String auditTypeCode, String statusUnauth) {

		
		Session session=getSession();
		if(!BankAuditConstant.STATUS_AUTH.equals(statusUnauth)){
			session.createQuery("delete from RiskScorePercentageWrk where legalEntityCode =:legalEntityCode   "
			
					+ " and auditTypeCode =:auditTypeCode  ")
			.setParameter("auditTypeCode", auditTypeCode)
			.setParameter("legalEntityCode", legalEntityCode).executeUpdate();
			
			
		}else {
			session.createQuery("delete from RiskScorePercentage where legalEntityCode =:legalEntityCode   "
					+ " and auditTypeCode =:auditTypeCode  ")
			.setParameter("auditTypeCode", auditTypeCode)
			.setParameter("legalEntityCode", legalEntityCode).executeUpdate();
		
		}
	
	}
	
	@Override
	public RiskScorePercentage getRiskCategoryOnPercentage(Integer legalEntityCode, String auditTypeCode, Integer riskScore) {

		Session session=getSession();
		RiskScorePercentage rskScrPcn =new RiskScorePercentage();
		System.out.println("LE.. "+legalEntityCode +" AT.. "+auditTypeCode +" RS.. "+riskScore);
		try{
			rskScrPcn=(RiskScorePercentage)session.createQuery(
				 " from RiskScorePercentage  rp "
				+ " where "
				+ " rp.legalEntityCode =:legalEntityCode "
				+ " and rp.auditTypeCode =:auditTypeCode  "
				+ " and (:riskScore > rp.minimunPer and :riskScore <= rp.maximumPer)")
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("riskScore", riskScore).uniqueResult();
			System.out.println("rskScrPcn .... "+ rskScrPcn);
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}return rskScrPcn;
	}

	@Override
	public Boolean isRiskCategoryCode(Integer legalEntityCode, String auditTypeCode, String riskCategoryCode) {


		
Session session=getSession();
		
		Long count=(Long)session.createQuery(
	
				
				" select count(*) "
				+ " from RiskScorePercentage   "
				+ " where "
				+ " legalEntityCode =:legalEntityCode "
				+ " and auditTypeCode =:auditTypeCode "
				+ " and riskCategoryCode =:riskCategoryCode")
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("auditTypeCode", auditTypeCode)
		.setParameter("riskCategoryCode", riskCategoryCode)
		.uniqueResult();
		
		Long count1=(Long)session.createQuery(
		
				
				" select count(*) "
				+ " from RiskScorePercentageWrk   "
				+ " where "
				+ " legalEntityCode =:legalEntityCode "
				+ " and auditTypeCode =:auditTypeCode "
				+ " and riskCategoryCode =:riskCategoryCode")
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("auditTypeCode", auditTypeCode)
		.setParameter("riskCategoryCode", riskCategoryCode).uniqueResult();
		Boolean flag;
		if((count1!=null && count1 >0)||(count!=null && count >0)){
			flag= true;
		}else {
			flag= false;
		}
	return flag;
	
	}


}
