package com.bankaudit.rest.process.dao;

import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.ResultTransformer;
import org.jboss.logging.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.bankaudit.dao.AbstractDao;
import com.bankaudit.dto.DataTableResponse;
import com.bankaudit.model.User;
import com.bankaudit.process.model.AuditSchedule;
import com.bankaudit.process.model.AuditTeam;
import com.bankaudit.service.BusinessDateService;
import com.bankaudit.util.BankAuditUtil;

@Repository("auditOffsetDao")
public class AuditOffsetDaoImpl extends AbstractDao implements AuditOffsetDao{
	
	@Autowired
	BusinessDateService businessDateService;
	
	static final Logger logger = Logger.getLogger(MyAuditsDaoImpl.class);
	
	@SuppressWarnings("unchecked")
	@Override
	public DataTableResponse getAuditsforOffset(int legalEntityCode,String userId, String search, Integer orderColumn,
			String orderDirection, Integer page, Integer size) {

		Session session = getSession();
		session.setDefaultReadOnly(true);

		/********************** This block is to retrieve the User details --> UserId and UserName **************************/
		StringBuilder queryString0=new StringBuilder(// " Select concat(u.firstName , ' ' , u.lastName) as firstName, u.userId "
				 " from User u  "
				+ " where "
				+ " u.legalEntityCode =:legalEntityCode "
				);
		
		Query query1=session.createQuery(queryString0.toString())
				.setParameter("legalEntityCode", legalEntityCode);
		
		List lst = query1.list();
		HashMap<String, String> userDtls = new HashMap<String, String>();
		for ( Iterator iter = lst.iterator(); iter.hasNext(); ) {
			User usr = (User) iter.next();
			if(usr.getUserId().equalsIgnoreCase(userId))
				userDtls.put(usr.getUserId(), "Me"); // for the same User we are stamping as "You"
			else userDtls.put(usr.getUserId(), usr.getFirstName()+" "+usr.getMiddleName()+" "+usr.getLastName());	
		}
		System.out.println("userId is .... "+ userId+" serach:: "+search +" legal entity code:: "+legalEntityCode);
		System.out.println("User details val is .... "+ userDtls);
		/******************** User Retrieval logic, Ends here ********************************/
		
		/***************** Logic to retrieve duration and No. of Audits from general parameter table and  ****
		 ***************** 	comparing both to find out the minimum, Starts here 	      ********************/
		Date businessDate =businessDateService.getBusinessDateTimestampByLegalEntityCode(legalEntityCode);
		System.out.println("businessDate .... "+ businessDate);
		//userId="4468"; // for temp purpose, need to be commented
		StringBuilder queryString2=new StringBuilder( " select nd.auditsInInterval,nads.noAudits from " 
				+ " ( select count(*) as auditsInInterval from audit_schedule ash, audit_team atm " 
				+ "	where ash.plan_id=atm.plan_id and ash.audit_id = atm.audit_id and ash.legal_entity_code=atm.legal_entity_code and atm.user_id=:userId " 
				+ "          and (ash.schedule_start_date between :businessDate and " 
				+ "				ADDDATE(:businessDate, INTERVAL (select g.value from general_parameter g where key_1='AUDITOR_VISIBILITY' AND key_2='DAYS' AND g.legal_entity_code=ash.legal_entity_code ) DAY))  ) as nd, " 
				+ " ( select g.value as noAudits from general_parameter g where g.key_1='AUDITOR_VISIBILITY' AND g.key_2='NO_OF_AUDITS' AND g.legal_entity_code=:legalEntityCode) as nads; "  
				); 
		//(select g.value from GeneralParameter g where g.key1='AUDITOR_VISIBILITY' AND g.key2='DAYS' AND g.legalEntityCode=:legalEntityCode) 
		
		Query query2lst=session.createSQLQuery(queryString2.toString())
				.setParameter("legalEntityCode", legalEntityCode).setParameter("userId", userId)
				.setParameter("businessDate", businessDate);
		
		Object obj = query2lst.uniqueResult();
		int auidtsDisplay = 100;
		try{
			if(obj != null  && ((Object[])obj).length > 0)
			{
				int auditsInInterval  =Integer.parseInt( ((Object[])obj)[0].toString());
				int  noOfAudit=Integer.parseInt( ((Object[])obj)[1].toString());
				System.out.println("auditsInInterval .... "+ auditsInInterval +" noOfAudit.... "+noOfAudit);
				if(auditsInInterval > noOfAudit) auidtsDisplay=noOfAudit;
				else auidtsDisplay = auditsInInterval;
			}
		}catch(Exception e) {
			System.out.println("Exception occured ....");
		}
		System.out.println("auidtsDisplay .... "+auidtsDisplay);
		/******************** Logic for no of Audits, Ends here ********************************/
			
		DataTableResponse dataTableResponse=new DataTableResponse();
		try {
		//StringBuilder queryString=new StringBuilder(" from AuditSchedule where legalEntityCode =:legalEntityCode ");
		StringBuilder queryString=new StringBuilder("select sch as auditSchedule, " 
				+ " legalEntityCode,"
				+ "(select concat(auditTypeCode , '-' , auditTypeDesc) from MaintAuditTypeDesc at where at.auditTypeCode=sch.auditTypeCode and at.legalEntityCode= sch.legalEntityCode ) as auditTypeCode,"
				
				+ "planId,auditId, "
				+ " (Select concat(unitCode , '-' , unitName) from  MaintEntity e where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode ) as unitId,  "
				
				+ " (Select g.description from  MaintEntity e,GeneralParameter g where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode and e.legalEntityCode =g.legalEntityCode and g.key1='UNIT_TYPE' and g.key2='UNIT_TYPE' and g.value=e.unitType) as unitType, "
				
				+ " (Select c.criticalityDesc from  MaintEntity e,MaintCriticality c where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode and e.legalEntityCode =c.legalEntityCode and e.uCriticality=c.criticalityCode and c.criticalityOfType='Entity') as uCriticality, "
				
				//+ " (Select concat(u.firstName , ' ' , u.lastName) from  User u,AuditTeam t where u.userId =t.userId and t.auditId=sch.auditId and t.legalEntityCode =u.legalEntityCode and t.legalEntityCode =sch.legalEntityCode and t.userType='L') as teamLead, "
				+ " (Select t.userId from  AuditTeam t where t.auditId=sch.auditId and t.legalEntityCode =sch.legalEntityCode and t.userType='L') as teamLead, "
				
				+" duration," //Code to comment //+ " (Select p.duration from  MaintEntity e,MaintAuditParameter p where e.unitCode =sch.unitId and e.unitType=p.unitType and e.uCriticality=p.uCriticality and sch.auditTypeCode=p.auditTypeCode and e.legalEntityCode =sch.legalEntityCode and e.legalEntityCode =p.legalEntityCode ) as duration, "
				+" frequency," //Code to comment //+ " (Select p.frequency from  MaintEntity e,MaintAuditParameter p where e.unitCode =sch.unitId and e.unitType=p.unitType and e.uCriticality=p.uCriticality and sch.auditTypeCode=p.auditTypeCode and e.legalEntityCode =sch.legalEntityCode and e.legalEntityCode =p.legalEntityCode ) as frequency, "
				//+ " (select tm.auditTypeCode,GROUP_CONCAT(tm.name) from (Select t.auditTypeCode,t.planId, t.auditId, concat(u.firstName , ' ' , u.lastName) as name from  User u,AuditTeam t where u.userId =t.userId and t.auditId=sch.auditId and t.legalEntityCode =u.legalEntityCode and t.legalEntityCode =sch.legalEntityCode and t.userType='T') tm where  GROUP BY tm.auditTypeCode  ) as teamMember "
				+ " (Select concat(sch.auditStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus) as auditStatus, "
				+ " (Select concat(u.userId , '-' , u.firstName, ' ',u.middleName,' ',u.lastName) from  User u where u.legalEntityCode =sch.legalEntityCode and u.userId=sch.maker) as maker "
				+ ", (Select count(*) from  AuditObservation o where o.legalEntityCode =sch.legalEntityCode and o.auditId=sch.auditId and o.complianceCategory='N' and o.reviewStatus='R' ) as responseReject "
				+ ", (Select count(*) from  AuditObservation o where o.legalEntityCode =sch.legalEntityCode and o.auditId=sch.auditId and o.complianceCategory='C' and o.submissionStatus='A') as complianceCount "
				+ ", (Select count(*) from  AuditObservation o where o.legalEntityCode =sch.legalEntityCode and o.auditId=sch.auditId and o.complianceCategory='NA' and o.submissionStatus='A') as notApplicableCount "
				//+ " , auditStatus "
				+ ", (Select g.value from GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='AUDIT_EARLY_START' and g.key2='EARLY_START_ALLOWED') as earlyStartAllowed "
				+ ", (Select g.value from GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='AUDIT_OFFSET_DAYS' and g.key2='AUDIT_OFFSET_DAYS') as auditOffsetDays " 
				+ " from AuditSchedule sch where legalEntityCode =:legalEntityCode "
				//+ " and sch.legalEntityCode=t.legalEntityCode and t.auditId=sch.auditId and t.userId=:userId"
				+ " and   IFNULL(auditStatus,'NotNull') not in ('AD','AU','DRP','UP') "
				// + " and  sch.auditId IN (SELECT t.auditId FROM AuditTeam t WHERE t.auditId=sch.auditId and t.legalEntityCode =sch.legalEntityCode and t.userId=:userId  ) " // to provide all the Audits irrespective of User 
				);
		System.out.println("Before the qry....");
		
		if(!BankAuditUtil.isEmptyString(search)){
			queryString=queryString.append(" and ( "
					+ " sch.auditTypeCode IN (SELECT a.auditTypeCode FROM MaintAuditTypeDesc a  WHERE a.legalEntityCode=sch.legalEntityCode AND a.auditTypeDesc LIKE :search) "
					+ " or planId like :search "
					+ " or auditId like :search "
					//+ " or unitId like :search "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m WHERE m.legalEntityCode=sch.legalEntityCode AND (m.unitName LIKE :search or m.unitCode LIKE :search )) "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m,GeneralParameter g WHERE sch.unitId=m.unitCode and m.legalEntityCode=sch.legalEntityCode AND m.legalEntityCode =g.legalEntityCode and g.key1='UNIT_TYPE' and g.key2='UNIT_TYPE' and g.value=m.unitType and g.description LIKE :search) "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m,MaintCriticality c WHERE sch.unitId=m.unitCode and m.legalEntityCode=sch.legalEntityCode AND m.legalEntityCode =c.legalEntityCode and m.uCriticality=c.criticalityCode and c.criticalityOfType='Entity' and  c.criticalityDesc LIKE :search) "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m,MaintAuditParameter p WHERE sch.unitId=m.unitCode and m.legalEntityCode=sch.legalEntityCode AND m.legalEntityCode =p.legalEntityCode and m.uCriticality=p.uCriticality and sch.auditTypeCode=p.auditTypeCode and  p.duration LIKE :search) "
					+ " or sch.auditId IN (SELECT t.auditId FROM User u,AuditTeam t WHERE t.auditId=sch.auditId and t.legalEntityCode =sch.legalEntityCode and t.legalEntityCode =u.legalEntityCode and t.userId=u.userId and (t.userId Like :search or u.firstName LIKE :search or u.lastName Like :search) ) "
					+ " or DATE_FORMAT(sch.scheduleStartDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.scheduleEndDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.actualStartDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.actualEndDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.lastAuditDate, '%d %b %Y') LIKE :search "
					+ " or sch.maker IN (SELECT u.userId FROM User u WHERE u.legalEntityCode =sch.legalEntityCode and (u.userId Like :search or u.firstName LIKE :search or u.lastName Like :search) ) "
					+ " or sch.auditStatus IN (SELECT g.value FROM GeneralParameter g WHERE sch.legalEntityCode =g.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus and g.description LIKE :search) "
					+ " ) "
					// + " )"
					);
		}
		
		/*queryString=queryString.append("  ORDER BY FIELD(auditStatus, 'AI','P','AP','AS','AC','AU') asc ");*/
		String[] columns={
				"auditTypeCode",
				"(auditId *1 ), (planId *1 )", //"CAST(planId AS Integer),CAST(auditId AS Integer) "
				"unitType",
				"(unitId * 1) ",  //"unitId",
				"uCriticality",
				"duration",
				"lastAuditDate",//last audit date actually 
				"sch.scheduleStartDate",
				"sch.scheduleEndDate",
				"sch.actualStartDate",
				"sch.actualEndDate",
				"teamLead", // Team Lead
				// "sch.maker", // Team Member
				"sch.maker",
				"sch.auditStatus"
		};

		/*if(orderColumn!=null
				&& !BankAuditUtil.isEmptyString(orderDirection)){
			queryString=queryString.append(" order by ").append(columns[orderColumn]).append(" ").append(orderDirection);
		}*/
		System.out.println("orderColumn **.... "+ orderColumn +"  orderDirection ...."+ orderDirection );
		if(orderColumn!=null && !BankAuditUtil.isEmptyString(orderDirection)){
			if(orderColumn==0) {
				queryString=queryString.append(" ORDER BY  ").append(columns[orderColumn]).append(" ").append(orderDirection).append(" , FIELD(auditStatus, 'AI','AP','AS','AR','AC') asc ,(auditId *1 ) asc  ");
			}else {
				queryString=queryString.append(" ORDER BY  ").append(columns[orderColumn]).append(" ").append(orderDirection);
			}
		}else {
			queryString=queryString.append(" ORDER BY FIELD(auditStatus, 'AI','AP','AS','AR','AC') asc  ");
		}
		
		System.out.println("queryString .... "+ queryString.toString());
		
		Query query=(Query) session.createQuery(queryString.toString())				
				.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						AuditSchedule auditSchedule=null;
						//System.out.println("tuple[0] ...."+tuple[0]);
						if(tuple[0]!=null){
							auditSchedule=(AuditSchedule)tuple[0];
							/* session.evict(maintEntity);*/
							
							if(tuple[1]!=null){
								auditSchedule.setLegalEntityCode(Integer.parseInt(tuple[1].toString()));								
							}
							if(tuple[2]!=null){
								auditSchedule.setAuditTypeCode(tuple[2].toString());
							}
							
							if(tuple[3]!=null){
								auditSchedule.setPlanId(tuple[3].toString());
							}
							
							if(tuple[4]!=null){
								auditSchedule.setAuditId(tuple[4].toString());
							}
							if(tuple[5]!=null){
								auditSchedule.setUnitId(tuple[5].toString());
							}
							if(tuple[6]!=null){
								auditSchedule.setUnitType(tuple[6].toString());
							}
							if(tuple[7]!=null){
								auditSchedule.setuCriticality(tuple[7].toString());
							}
							if(tuple[8]!=null){
								//auditSchedule.setTeamLead(tuple[8].toString()+"-"+userDtls.get(tuple[8].toString()));
								auditSchedule.setTeamLead(userDtls.get(tuple[8].toString()));
							}
							if(tuple[9]!=null){
								auditSchedule.setDuration(tuple[9].toString());
							}
							if(tuple[10]!=null){
								auditSchedule.setFrequency(tuple[10].toString());
							}
							if(tuple[11]!=null){
								if("P".equalsIgnoreCase(tuple[11].toString()))auditSchedule.setAuditStatus("AP");
								else auditSchedule.setAuditStatus(tuple[11].toString());
							}
							if(tuple[12]!=null){
								auditSchedule.setMaker(tuple[12].toString());
							}
							if(tuple[13]!=null && auditSchedule.getRespondedFindings() != null){
								auditSchedule.setResponseReject(Integer.parseInt(tuple[13].toString()));
								auditSchedule.setRespondedFindings(auditSchedule.getRespondedFindings()- (Integer.parseInt(tuple[13].toString()))); // Included to find out the Responded Observations
							}else auditSchedule.setResponseReject(0);
							
							if(tuple[14]!=null){
								auditSchedule.setComplianceCount(Integer.parseInt(tuple[14].toString()));
							}else auditSchedule.setComplianceCount(0);
							
							if(tuple[15]!=null){
								auditSchedule.setNotApplicableCount(Integer.parseInt(tuple[15].toString()));
							}else auditSchedule.setNotApplicableCount(0);
							
							if(tuple[16]!=null){
								auditSchedule.setEarlyStartAllowed(tuple[16].toString());
							}else auditSchedule.setEarlyStartAllowed("Y");
							
							if(tuple[17]!=null){
								auditSchedule.setAuditOffsetDays(Integer.parseInt(tuple[17].toString()));
							}else auditSchedule.setAuditOffsetDays(0);
							
							int tmSize =auditSchedule.getAuditTeams().size()-1;
							String teamMember="";
							System.out.println("Audit Id .... "+ auditSchedule.getAuditId()+" tmSize .."+tmSize);
							for( AuditTeam adtTm: auditSchedule.getAuditTeams() ) {
								
								adtTm.setUserId(adtTm.getUserId()+"-"+userDtls.get(adtTm.getUserId()));
								
								if(!"L".equalsIgnoreCase(adtTm.getUserType())) teamMember +=adtTm.getUserId() + ",";
								System.out.println("teamMember .... "+ teamMember);
							}if(tmSize >0 ) auditSchedule.setTeamMember(teamMember.substring(0,teamMember.lastIndexOf(",")));
							System.out.println("Team member .... "+auditSchedule.getTeamMember());
							//System.out.println("Team size ...."+auditSchedule.getAuditTeams().size());
							
						}
						return auditSchedule;
					}
			@Override
			public List transformList(List collection) {
				return collection;
			}
		})		
				.setParameter("legalEntityCode",legalEntityCode); //.setParameter("userId",userId);

		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}
		
		System.out.println("page :: "+page +"size :: "+ size);
		List<AuditSchedule> tmpAuditSchedule =query.list(); 
		Long totalRecords=null;
		try {
			totalRecords = (long)tmpAuditSchedule.size();
		}catch(Exception e) {
			totalRecords = (long)0;
		}
		List<AuditSchedule> auditSchedule=query.setFirstResult((page*size)).setMaxResults(size).list();
		
		System.out.println("Bfr size tmpAuditSchedule is .... "+tmpAuditSchedule.size()+" totalRecords:: "+ totalRecords);
		
		/*int i=0;
		for(AuditSchedule adSch:auditSchedule){
			System.out.println("Inside adSch ...");
			if("A2".equalsIgnoreCase(adSch.getAuditId()));
			else 	tmpAuditSchedule.add(adSch);
			System.out.println("after the call");
			
		}
		System.out.println("size tmpAuditSchedule .... "+tmpAuditSchedule);*/
		
		
		dataTableResponse.setData(auditSchedule);
		dataTableResponse.setRecordsTotal(totalRecords);
		dataTableResponse.setRecordsFiltered(totalRecords);
		
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return dataTableResponse;
	
	}
}
