package com.bankaudit.template.rest.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.dao.AbstractDao;
import com.bankaudit.dto.DataTableResponse;
import com.bankaudit.template.model.UploadStatus;
import com.bankaudit.util.BankAuditUtil;

import java.util.ArrayList;

@Repository("uploadStatusDao")
public class UploadStatusDaoImpl extends AbstractDao implements UploadStatusDao{
	
	static final Logger logger = Logger.getLogger(UploadStatusDaoImpl.class);
	
	@Override
	public DataTableResponse getAllUploadStatus(Integer legalEntityCode, String search, Integer orderColumn,
			String orderDirection, Integer page, Integer size, String uploadType) {

		logger.info(" Inside getAllUploadStatus .."+ legalEntityCode+ " :: "+ uploadType);
		Session session = getSession();
		session.setDefaultReadOnly(true);
		List<UploadStatus> uploadStatuses = null;

		DataTableResponse dataTableResponse=new DataTableResponse();

		StringBuilder queryString=new StringBuilder(
				    " SELECT  us as uploadStatus, "
				  + "  (Select concat(u.userId , '-' , u.firstName, ' ',u.middleName,' ',u.lastName) from  User u where u.legalEntityCode =us.legalEntityCode and u.userId=us.maker) as maker "
				  + " from UploadStatus us where legalEntityCode =:legalEntityCode "
				);
		if(uploadType!=null && !BankAuditUtil.isEmptyString(uploadType))
			queryString.append(" AND type=:type ");
		
		if(!BankAuditUtil.isEmptyString(search)){
			queryString=queryString.append(" and ( us.fileName like :search "
					+ " or DATE_FORMAT(us.uploadDate, '%d %b %Y') LIKE :search "
					+ " or us.maker IN (SELECT u.userId FROM User u WHERE u.legalEntityCode =us.legalEntityCode and (u.userId Like :search or u.firstName LIKE :search or u.lastName Like :search) ) "
					+ " or us.totalRecords like :search "
					+ " or us.successRecords like :search "
					+ " or us.failedRecords like :search "
					+ " )"
					);
		}
		String[] columns={
				"us.uploadDate",
				"us.maker",
				"us.fileName",
				"us.uploadStatus",	
				"us.totalRecords",
				"us.successRecords", "us.failedRecords"};

		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 makerTimestamp desc "); // 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(columns[orderColumn]).append(" ").append(orderDirection);
		}

		Query query=session.createQuery(queryString.toString())
				.setResultTransformer(new ResultTransformer() {

					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						UploadStatus uploadStatus=null;
						if(tuple!=null && tuple[0]!=null){
							uploadStatus=(UploadStatus)tuple[0];
							if(tuple[1]!=null)	uploadStatus.setMaker(tuple[1].toString());								
						}
						return uploadStatus;
					}

					@Override
					public List transformList(List collection) {
						return collection;
					}
				})
				.setParameter("legalEntityCode", legalEntityCode);
				

		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}
		if(uploadType!=null && !BankAuditUtil.isEmptyString(uploadType))
			query.setParameter("type", uploadType);


		ScrollableResults resultScroll = query.scroll(ScrollMode.SCROLL_INSENSITIVE);
		if(resultScroll.first()&&resultScroll.scroll(page*size)){

			uploadStatuses= new ArrayList<>();
			int i = 0;
			while (size > i++) {
				uploadStatuses.add((UploadStatus) resultScroll.get(0));
				if (!resultScroll.next())
					break;
			}
			dataTableResponse.setData(uploadStatuses);
			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;
	}

	@SuppressWarnings("unchecked")
	@Override
	public UploadStatus getUploadStatusById(Integer legalEntityCode, Integer id){
		Session session =getSession();
		session.setDefaultReadOnly(true);
		logger.info("Inside getUploadStatusById legalEntityCode.. "+legalEntityCode+"id .."+ id);
		
		StringBuilder str =new StringBuilder(" from UploadStatus where legalEntityCode=:legalEntityCode AND id=:id ");
		return (UploadStatus)(session.createQuery(str.toString()).setParameter("legalEntityCode", legalEntityCode).setParameter("id", id).uniqueResult());
	}
	
	
}
