package com.bankaudit.rest.process.dao;

import java.sql.Timestamp;
import java.util.List;

import org.hibernate.Session;
import org.hibernate.transform.ResultTransformer;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.bankaudit.dao.AbstractDao;
import com.bankaudit.process.model.AuditAdhocDocument;
import com.bankaudit.process.model.AuditAdhocDocumentHst;
import com.bankaudit.process.model.AuditAdhocReport;
import com.bankaudit.process.model.AuditAdhocReportHst;
import com.bankaudit.service.BusinessDateService;

@Repository("auditAdhocReportDao")
public class AuditAdhocReportDaoImpl extends AbstractDao implements AuditAdhocReportDao {

	@Autowired 
	BusinessDateService businessDateService;
	
	@SuppressWarnings("unchecked")
	@Override
	public void deleteauditAdhocDocument(Integer id,Integer legalEntityCode) {
		
		Session session=getSession();
	
		List<AuditAdhocReport>  auditAdhocReportsDb=
				session.createQuery(" from  AuditAdhocReport aar  "
						+ " where "
						+ " aar.documentId =:id "
						+ " and aar.status !='D' ")
				.setParameter("id", id)
				.list();
		
		if(auditAdhocReportsDb!=null){
			AuditAdhocReportHst auditAdhocReportHst=null;
			for (AuditAdhocReport auditAdhocReport : auditAdhocReportsDb) {
				auditAdhocReportHst=new AuditAdhocReportHst();
				BeanUtils.copyProperties(auditAdhocReport, auditAdhocReportHst);
				save(auditAdhocReportHst);
			}
		}
		
		AuditAdhocDocumentHst auditAdhocDocumentHst=null;
		AuditAdhocDocument auditAdhocDocumentDb=(AuditAdhocDocument)get(AuditAdhocDocument.class, id);
		if(auditAdhocDocumentDb!=null){
			auditAdhocDocumentHst=new AuditAdhocDocumentHst();
			BeanUtils.copyProperties(auditAdhocDocumentDb, auditAdhocDocumentHst);
			save(auditAdhocDocumentHst);
		}
		
		session.createQuery(" update AuditAdhocReport aar set "
				+ " aar.status='D' , "
				+ " aar.businessdateTimestamp =:businessdateTimestamp , "
				+ " aar.sysdateTimestamp =:sysdateTimestamp "
				+ " where "
				+ " aar.documentId =:id ")
		.setParameter("businessdateTimestamp", businessDateService.getBusinessDateTimestampByLegalEntityCode(legalEntityCode))
		.setParameter("sysdateTimestamp", new Timestamp(System.currentTimeMillis()))
		.setParameter("id", id)
		.executeUpdate();
		
		session.createQuery(" update AuditAdhocDocument aad set "
				+ " aad.status='D' "
				+ " where "
				+ " aad.id =:id ")
		.setParameter("id", id)
		.executeUpdate();
		
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<AuditAdhocDocument> getAuditAdhocDocument(Integer legalEntityCode, String auditTypeCode, String planId,
			String auditId, String reportedBy) {
		
		Session session = getSession();
		return session.createQuery("from AuditAdhocDocument where "
				+ " legalEntityCode =:legalEntityCode "
				+ " and auditTypeCode =:auditTypeCode "
				+ " and planId =:planId "
				+ " and auditId =:auditId "
				+ " and reportedBy =:reportedBy "
				+ " and status !='D'")
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("auditTypeCode", auditTypeCode)
		.setParameter("planId", planId)
		.setParameter("auditId", auditId)
		.setParameter("reportedBy", reportedBy).list()
		;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<AuditAdhocReport> getAuditAdhocReports(int legalEntityCode, String auditTypeCode, String planId,
			String auditId, String reportedBy) {
		Session session = getSession();
		session.setDefaultReadOnly(true);
		return session.createQuery(" select aar , "
				+ " (select concat(u.userId,'-',u.firstName,' ', u.lastName) as targetUser "
				+ " from User u where u.legalEntityCode=aar.legalEntityCode "
				+ " and u.userId=aar.targetUser) as targetUser , "
				
				+ " (select concat(u.userId,'-',u.firstName,' ', u.lastName) as reportedBy "
				+ " from User u where u.legalEntityCode=aar.legalEntityCode "
				+ " and u.userId=aar.auditAdhocDocument.reportedBy) as reportedBy  "
				
				+ " from AuditAdhocReport aar where "
				+ " aar.auditAdhocDocument.legalEntityCode =:legalEntityCode "
				+ " and aar.auditAdhocDocument.auditTypeCode =:auditTypeCode "
				+ " and aar.auditAdhocDocument.planId =:planId "
				+ " and aar.auditAdhocDocument.auditId =:auditId "
				+ " and aar.auditAdhocDocument.reportedBy =:reportedBy "
				+ " and aar.status !='D'")
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("auditTypeCode", auditTypeCode)
		.setParameter("planId", planId)
		.setParameter("auditId", auditId)
		.setParameter("reportedBy", reportedBy)
		.setResultTransformer(new ResultTransformer() {
			
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				AuditAdhocReport auditAdhocReport=null;
				
				if(tuple[0]!=null) {
					auditAdhocReport=(AuditAdhocReport)tuple[0];
				}
				
				
				if(auditAdhocReport!=null){
					
					if(tuple[1]!=null) {
						auditAdhocReport.setTargetUser(tuple[1].toString());
							
					}
					if(tuple[2]!=null) {
						auditAdhocReport.getAuditAdhocDocument().setReportedBy(tuple[2].toString());
							
					}
					
				
				}
				return auditAdhocReport;
			}
			@Override
			public List transformList(List collection) {
				return collection;
			}
		})
		.list()
		;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<AuditAdhocReport> getAuditAdhocReportsTargetUser(int legalEntityCode, String auditTypeCode,
			String planId, String auditId, String targetUser) {
		Session session = getSession();
		session.setDefaultReadOnly(true);
		return session.createQuery(" select aar , "
				+ " (select concat(u.userId,'-',u.firstName,' ', u.lastName) as targetUser "
				+ " from User u where u.legalEntityCode=aar.legalEntityCode "
				+ " and u.userId=aar.targetUser) as targetUser , "
				
				+ " (select concat(u.userId,'-',u.firstName,' ', u.lastName) as reportedBy "
				+ " from User u where u.legalEntityCode=aar.legalEntityCode "
				+ " and u.userId=aar.auditAdhocDocument.reportedBy) as reportedBy  "
				
				+ " from AuditAdhocReport aar where "
				+ " aar.auditAdhocDocument.legalEntityCode =:legalEntityCode "
				+ " and aar.auditAdhocDocument.auditTypeCode =:auditTypeCode "
				+ " and aar.auditAdhocDocument.planId =:planId "
				+ " and aar.auditAdhocDocument.auditId =:auditId "
				+ " and aar.targetUser =:targetUser "
				+ " and aar.status !='D'")
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("auditTypeCode", auditTypeCode)
		.setParameter("planId", planId)
		.setParameter("auditId", auditId)
		.setParameter("targetUser", targetUser)
		.setResultTransformer(new ResultTransformer() {
			
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				AuditAdhocReport auditAdhocReport=(AuditAdhocReport)tuple[0];
				
				if(auditAdhocReport!=null){
					auditAdhocReport.setTargetUser(tuple[1].toString());
					auditAdhocReport.getAuditAdhocDocument().setReportedBy(tuple[2].toString());
				}
				return auditAdhocReport;
			}
			@Override
			public List transformList(List collection) {
				return collection;
			}
		})
		.list()
		;
	}

}
