package com.bankaudit.rest.process.dao;

import java.sql.Timestamp;
import java.time.LocalDate;
import java.util.Date;
import java.util.List;

import org.hibernate.Session;
import org.hibernate.query.Query;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.jboss.logging.Logger;
import org.springframework.stereotype.Repository;

import com.bankaudit.dao.AbstractDao;
import com.bankaudit.dto.process.AuditPlanDto;
import com.bankaudit.process.model.AuditActionItem;
import com.bankaudit.process.model.AuditPlan;
import com.bankaudit.process.model.AuditPlanWrk;
import com.bankaudit.process.model.AuditSchedule;
import com.bankaudit.process.model.AuditScheduleWrk;
import com.bankaudit.process.model.AuditTeam;
import com.bankaudit.process.model.AuditTeamWrk;
import com.bankaudit.util.BankAuditUtil;

@Repository("auditPlanDao")
public class AuditPlanDaoImpl extends AbstractDao implements AuditPlanDao {

	static final Logger logger = Logger.getLogger(AuditPlanDaoImpl.class);
	
	@Override
	public Boolean isAuditPlanWrk(Integer legalEntityCode, String auditTypeCode, Date planStartDate,String planType) {

		Session session =getSession();
		
	//	SimpleDateFormat solrFormate = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");*/
	/*	SimpleDateFormat currentFormate = new SimpleDateFormat("yyyy-MM-dd'T'00:00:00'Z'");
		String now=currentFormate.format(new Date());
		//Date oldDate=solrFormate.parse("2000-01-01T00:00:00Z");
		criteria=criteria.and(new Criteria("expiresOn").greaterThanEqual(currentFormate.parse(now)).or("expiresOn").is("2000-01-01T00:00:00Z"));
	*/	
		String hsql = "select count(*) from AuditPlanWrk "
					+ "  where legalEntityCode =:legalEntityCode and "
					+ " auditTypeCode =:auditTypeCode "
					+ " and status !='DF' "
					+ " and status !='D' ";
		       hsql+= " and STR_TO_DATE(:planStartDate, '%Y-%m-%d') between STR_TO_DATE(planStartDate, '%Y-%m-%d') and STR_TO_DATE(planEndDate, '%Y-%m-%d') ";
		
		if("Y".equals(planType)) {
			   hsql+= " and planType = 'Y' ";			   
		}
		
		//Long count=(Long)
				
		Query query=session.createQuery(hsql);
		
		query.setParameter("legalEntityCode", legalEntityCode);
		query.setParameter("auditTypeCode", auditTypeCode);
		query.setParameter("planStartDate", planStartDate);
		
		//System.out.println("planType "+(String)query.uniqueResult());
		
		Long count=null; //
		count=(Long)query.uniqueResult();
		if(count!=null && count>0){
			return true;
		}else{
			return false;
		}
	}

	@Override
	public void deleteAuditPlanWrk(AuditPlanWrk auditPlanWrkDb) {
	
		Session session=getSession();
		
		
		
		for (AuditScheduleWrk auditScheduleWrk  : auditPlanWrkDb.getAuditScheduleWrks()) {
			int a;
			
			if(auditScheduleWrk.getAuditTeamWrks()!=null){
				for (AuditTeamWrk auditTeamWrk : auditScheduleWrk.getAuditTeamWrks()) {
					 a=session.createSQLQuery("delete from  audit_team_wrk where  "
							+ " legal_entity_code =:legalEntityCode "
							+ " and audit_type_code =:auditTypeCode"
							+ " and plan_id =:planId "
							+ " and audit_id =:auditId "
							/*+ " and user_id =:userId "*/
							+ "  ")
					.setParameter("legalEntityCode", auditTeamWrk.getLegalEntityCode())
					.setParameter("auditTypeCode", auditTeamWrk.getAuditTypeCode())
					.setParameter("planId", auditTeamWrk.getPlanId())
					.setParameter("auditId", auditTeamWrk.getAuditId())
					/*.setParameter("userId", auditTeamWrk.getUserId())*/.executeUpdate();
					 System.out.println(a);
				}
			}
			session.flush();
			session.clear();
			session.createQuery("delete from  AuditScheduleWrk where  "
					+ " legalEntityCode =:legalEntityCode "
					+ " and auditTypeCode =:auditTypeCode"
					+ " and planId =:planId "
					+ " and auditId =:auditId "
			
					+ "  ")
			.setParameter("legalEntityCode", auditScheduleWrk.getLegalEntityCode())
			.setParameter("auditTypeCode", auditScheduleWrk.getAuditTypeCode())
			.setParameter("planId", auditScheduleWrk.getPlanId())
			.setParameter("auditId", auditScheduleWrk.getAuditId())
			.executeUpdate();
			
		}
		
		session.createQuery("delete from  AuditPlanWrk where  "
				+ " legalEntityCode =:legalEntityCode "
				+ " and auditTypeCode =:auditTypeCode"
				+ " and planId =:planId "
		
				+ "  ")
		.setParameter("legalEntityCode", auditPlanWrkDb.getLegalEntityCode())
		.setParameter("auditTypeCode", auditPlanWrkDb.getAuditTypeCode())
		.setParameter("planId", auditPlanWrkDb.getPlanId())
		.executeUpdate();
		
		
	}

	@Override
	public void flushSession() {
		
		Session session=getSession();
		session.flush();
		session.clear();
		
		
	}

	@Override
	public void deleteAuditPlan(AuditPlan auditPlan) {

		
		Session session=getSession();
		
		
		
		for (AuditSchedule auditScheduleWrk  : auditPlan.getAuditSchedules()) {
			
			
			for (AuditTeam auditTeamWrk : auditScheduleWrk.getAuditTeams()) {
				session.createQuery("delete AuditTeam where  "
						+ " legalEntityCode =:legalEntityCode "
						+ " and auditTypeCode =:auditTypeCode"
						+ " and planId =:planId "
						+ " and auditId =:auditId "
						+ " and userId =:userId "
						+ "  ")
				.setParameter("legalEntityCode", auditTeamWrk.getLegalEntityCode())
				.setParameter("auditTypeCode", auditTeamWrk.getAuditTypeCode())
				.setParameter("planId", auditTeamWrk.getPlanId())
				.setParameter("auditId", auditTeamWrk.getAuditId())
				.setParameter("userId", auditTeamWrk.getUserId()).executeUpdate();
			}
			
			session.createQuery("delete AuditSchedule where  "
					+ " legalEntityCode =:legalEntityCode "
					+ " and auditTypeCode =:auditTypeCode"
					+ " and planId =:planId "
					+ " and auditId =:auditId "
			
					+ "  ")
			.setParameter("legalEntityCode", auditScheduleWrk.getLegalEntityCode())
			.setParameter("auditTypeCode", auditScheduleWrk.getAuditTypeCode())
			.setParameter("planId", auditScheduleWrk.getPlanId())
			.setParameter("auditId", auditScheduleWrk.getAuditId())
			.executeUpdate();
			
		}
		
		session.createQuery("delete AuditPlan where  "
				+ " legalEntityCode =:legalEntityCode "
				+ " and auditTypeCode =:auditTypeCode"
				+ " and planId =:planId "
		
				+ "  ")
		.setParameter("legalEntityCode", auditPlan.getLegalEntityCode())
		.setParameter("auditTypeCode", auditPlan.getAuditTypeCode())
		.setParameter("planId", auditPlan.getPlanId())
		.executeUpdate();
			
	}
	
	@SuppressWarnings({ "deprecation" })
	public String getAdhocPlanConflicts(Integer legalEntityCode,String auditTypeCode,String unitCode,String sheduleStartDate,String sheduleEndDate){
		
		String hql ="";		
		hql+="select get_adhoc_plan_conflicts(:legalEntityCode,:auditTypeCcode,:unitCode,:sheduleStartDate,:sheduleEndDate) as planIds FROM DUAL ";
		Session session=getSession();
		String str = (String) session.createSQLQuery(hql)
				.addScalar("planIds", StandardBasicTypes.STRING)
				.setParameter("legalEntityCode", new Integer(legalEntityCode).intValue())
			    .setParameter("auditTypeCcode", auditTypeCode)
			    .setParameter("unitCode", unitCode)
			    .setParameter("sheduleStartDate", sheduleStartDate)
			    .setParameter("sheduleEndDate", sheduleEndDate).uniqueResult();
		return str;
	}
	
	@Override
	public List<AuditPlan> getAuditPlans(Integer legalEntityCode, String auditTypeCode){
		Session session = getSession();
		logger.info("Inside getAuditPlans .. "+ legalEntityCode +" :: "+auditTypeCode);
			
		return session.createQuery(" From AuditPlan ap WHERE ap.legalEntityCode=:legalEntityCode ").setParameter("legalEntityCode", legalEntityCode).list();
	}
	
	@Override
	public AuditPlan getLatestPlan(Integer legalEntityCode, String auditTypeCode, LocalDate fYStartDt, LocalDate fYEndDt){
		Session session = getSession();
		logger.info("Inside getLatestPlan .. "+ legalEntityCode +" :: "+auditTypeCode+" :: "+ fYStartDt+" :: "+fYEndDt);
		String planId=null;
		
		/*return (AuditPlan)session.createQuery(" From AuditPlan ap WHERE ap.legalEntityCode=:legalEntityCode  "
				+ " AND ap.auditTypeCode=:auditTypeCode AND ap.planStartDate >=:fYStartDt AND ap.planEndDate <=:fYEndDt "
				+ "  ORDER BY planEndDate limit 1").setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode)
				.setParameter("fYStartDt", fYStartDt).setParameter("fYEndDt", fYEndDt).uniqueResult(); */
		Query auditPlanDtlsQry=session.createNativeQuery(" SELECT audit_type_code, plan_id  "
				+ "   From audit_plan ap WHERE ap.legal_entity_code=:legalEntityCode AND "
				+ "        ap.audit_type_code=:auditTypeCode AND ap.plan_start_date >=:fYStartDt AND ap.plan_end_date <=:fYEndDt "
				+ "  ORDER BY plan_end_date limit 1").setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode)
				.setParameter("fYStartDt", fYStartDt).setParameter("fYEndDt", fYEndDt);
		
		logger.info("Query before .. "+ auditPlanDtlsQry );
		try {
			Object[] obj = (Object[])auditPlanDtlsQry.getSingleResult();
			if(obj !=null) {
				planId=obj[1].toString();		
			}
			return (AuditPlan)session.createQuery(" From AuditPlan ap WHERE ap.legalEntityCode=:legalEntityCode AND ap.auditTypeCode=:auditTypeCode "
					+ " AND ap.planId=:planId ").setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode)
					.setParameter("planId", planId).uniqueResult();
		}catch(Exception e) {
			System.out.println("No plan with in the period.");
			return null;
		}
	}	
	
	@Override
	public AuditPlan getPlanDetailsByPlanId(Integer legalEntityCode, String auditTypeCode, String planId){
		Session session = getSession();
		logger.info("Inside getPlanDetailsByPlanId .. "+ legalEntityCode +" :: "+auditTypeCode+" :: "+ planId);
		return (AuditPlan)session.createQuery(" From AuditPlan ap WHERE ap.legalEntityCode=:legalEntityCode AND ap.auditTypeCode=:auditTypeCode "
					+ " AND ap.planId=:planId ").setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode)
					.setParameter("planId", planId).uniqueResult();
	}
	
}
