package com.bankaudit.rest.process.service;

import java.io.ByteArrayOutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jboss.logging.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.bankaudit.dto.process.AuditRatingComplianceObsScoreReportDto;
import com.bankaudit.dto.process.AuditRatingComplianceScoreDto;
import com.bankaudit.process.model.AuditRatingComplianceObsScore;
import com.bankaudit.rest.process.dao.AuditRatingComplianceScoreDao;
import com.bankaudit.rest.process.dao.MyAuditsDao;
import com.bankaudit.service.BusinessDateService;
import com.bankaudit.util.BankAuditUtil;

@Service("auditRatingComplianceScoreService")
@Transactional("transactionManager")
public class AuditRatingComplianceScoreServiceImpl implements AuditRatingComplianceScoreService{

	@Lazy
	@Autowired(required=true)
	MyAuditsDao myAuditsDao;
	
	@Autowired(required=true)
	AuditRatingComplianceScoreDao auditRatingComplianceScoreDao;
	
	@Autowired
	BusinessDateService businessDateService;
		
	static final Logger logger = Logger.getLogger(AuditRatingComplianceScoreServiceImpl.class);

	@Override
	public List<AuditRatingComplianceObsScore> insertAuditComplianceObsScore(int legalEntityCode,String auditId, String unitId, String auditTypeCode, String userId) {
		List<AuditRatingComplianceObsScore> compObsScrLst=null;
		boolean isDataPresent= auditRatingComplianceScoreDao.verifyDataPresentForComplianceObsScore(legalEntityCode, auditId); 
		//if(!isDataPresent) {
			boolean success=auditRatingComplianceScoreDao.createAndGetAuditRatingComplianceScore(legalEntityCode, auditId, unitId, auditTypeCode, userId);
		    if(success) compObsScrLst= auditRatingComplianceScoreDao.getComplianceObsScoreByAuditId(legalEntityCode,auditId,auditTypeCode,"PIO");
		//}
		return compObsScrLst;
	}
	
	@Override
	public List<AuditRatingComplianceObsScore> getComplianceObsScoreByAuditId(int legalEntityCode,String auditId, String auditTypeCode, String unitId, String userType, String userId, String compRatingStatus) {
		/*if(compRatingStatus!=null) {}
		else {*/
			boolean isDataPresent= auditRatingComplianceScoreDao.verifyDataPresentForComplianceObsScore(legalEntityCode, auditId);
			if(!isDataPresent)auditRatingComplianceScoreDao.createAndGetAuditRatingComplianceScore(legalEntityCode, auditId, auditTypeCode, unitId, userId);
		// }
		return auditRatingComplianceScoreDao.getComplianceObsScoreByAuditId(legalEntityCode,auditId,auditTypeCode, userType);		 
	}
	
	@Override
	public void updateAuditComplianceObsScores(List<AuditRatingComplianceObsScore> auditRatingComplianceObsScores, int legalEntityCode,String auditId, String userType) {
		auditRatingComplianceScoreDao.updateAuditComplianceObsScores(auditRatingComplianceObsScores, legalEntityCode, auditId, userType); 
	}
	
		@Override
	public AuditRatingComplianceScoreDto getAuditRatingComplianceScores(int legalEntityCode, String auditId, String ratingType, String userType) {
		return auditRatingComplianceScoreDao.getAuditRatingComplianceScores(legalEntityCode,auditId,ratingType, userType);  
	}
		
	@Override
	public List<AuditRatingComplianceObsScore> getComplianceObsScoreReportByAuditId(int legalEntityCode,String auditId, String userType, String compRatingStatus) {
		return auditRatingComplianceScoreDao.getComplianceObsScoreReportByAuditId(legalEntityCode, auditId, userType, compRatingStatus);		 
	}
	
	@Override
	public AuditRatingComplianceObsScoreReportDto getComplianceObsScoreReportByAuditIdAndRatingType(int legalEntityCode,String auditId, String ratingType, String userType, String compRatingStatus, String recordsWithNA) {
		
		AuditRatingComplianceObsScoreReportDto scrDto = new AuditRatingComplianceObsScoreReportDto();
		logger.info("getComplianceObsScoreReportByAuditIdAndRatingType .. "+ auditId+ " :: "+ ratingType+" :: "+userType+" :: "+compRatingStatus+" :: "+ recordsWithNA );
		try{
			// To get the Entity Data
			scrDto.setEntityAuditData(myAuditsDao.getEntityAuditData(legalEntityCode, null, auditId, null));
			
			// To get the Scores with or without NA
			List<AuditRatingComplianceObsScore> scores=auditRatingComplianceScoreDao.getComplianceObsScoreReportByAuditId(legalEntityCode, auditId, userType, compRatingStatus);
			if(scores !=null) {
				if("N".equalsIgnoreCase(recordsWithNA))	{ // To get Records with out NA
					if("D".equalsIgnoreCase(ratingType))
						scrDto.setAuditRatingComplianceObsScoreLst(scores.stream().filter(p -> !"NA".equals(p.getDeskEffectiveScore())).collect(Collectors.toList())); // To discard the NA scores for Desk Rating
					else
						scrDto.setAuditRatingComplianceObsScoreLst(scores.stream().filter(p -> !"NA".equals(p.getFieldEffectiveScore())).collect(Collectors.toList())); // To discard the NA scores for Field Rating			
				}else {
					scrDto.setAuditRatingComplianceObsScoreLst(scores);
				}
			}else scrDto.setAuditRatingComplianceObsScoreLst(new ArrayList<AuditRatingComplianceObsScore>());
			
			// To get the Count of Obs, Maximum Marks, Total Marks, Rating% and Score
			scrDto.setAuditRatingComplianceScore(auditRatingComplianceScoreDao.getAuditRatingComplianceScores(legalEntityCode,auditId,ratingType, userType));
			
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return scrDto; 		 
	}		
	
	@Override
	public ByteArrayOutputStream downloadAuditRatingComplianceObsScore(Integer legalEntityCode, String auditTypeCode, String auditId, String role, 
			String ratingComplianceStatus){
		  
		List<String> rolesListInspector = Arrays.asList(("G2,G8").split(","));  //Inspection
		List<String> rolesListReviewer = Arrays.asList(("G8,G12").split(",")); //Reviewer
		List<String> rolesListScrutinizer = Arrays.asList(("G4,G7").split(",")); // Scrutinizer/review team
		
		if(!rolesListInspector.contains(role) && !rolesListReviewer.contains(role) && !rolesListScrutinizer.contains(role)){
			return null;
		}
		try(XSSFWorkbook workbook = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream();) { 
		
			List<AuditRatingComplianceObsScore> auditRatingComplianceObsScoreList = 
					auditRatingComplianceScoreDao.downloadAuditRatingComplianceObsScore(legalEntityCode, auditTypeCode, auditId);
			
			int rowNumber=0;
			XSSFSheet sheet = workbook.createSheet(legalEntityCode+"_"+auditId+"_"+auditTypeCode); 

			sheet.protectSheet(System.nanoTime()+"legalEntityAuditIdAuditType_"+legalEntityCode+"_"+auditId+"_"+auditTypeCode); 
			
			CellStyle cellStyleEditable = workbook.createCellStyle();
			cellStyleEditable.setLocked(false);	 
			cellStyleEditable.setWrapText(true);
			cellStyleEditable.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);
			
			CellStyle cellStyleReadOnly = workbook.createCellStyle();			
			cellStyleReadOnly.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);  
			cellStyleReadOnly.setWrapText(true);
			cellStyleReadOnly.setLocked(true);
			
			CellStyle cellStyleReadOnlyCenter = workbook.createCellStyle(); 			
			cellStyleReadOnly.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);
			cellStyleReadOnlyCenter.setAlignment(CellStyle.ALIGN_CENTER);
			cellStyleReadOnly.setWrapText(true);
			cellStyleReadOnly.setLocked(true);	 
						
			Font font = workbook.createFont();
			font.setBold(true);
			CellStyle cellStyleHeader = workbook.createCellStyle();  
			cellStyleHeader.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);
			cellStyleHeader.setAlignment(CellStyle.ALIGN_CENTER);
			cellStyleHeader.setFont(font); 
			cellStyleHeader.setWrapText(true);
			cellStyleHeader.setLocked(true);
			
			//Create information Header row
			XSSFRow rowheadInfoHead = sheet.createRow(rowNumber++);			
			BankAuditUtil.createCell(sheet, rowheadInfoHead, 0, "Inspection Id", cellStyleHeader, 15*256);
			BankAuditUtil.createCell(sheet, rowheadInfoHead, 1, "Inspection Type", cellStyleHeader, 1);
			BankAuditUtil.createCell(sheet, rowheadInfoHead, 2, "Inspection Type", cellStyleHeader, 55*256);
		
			XSSFRow rowheadInfoValues = sheet.createRow(rowNumber++);			
			BankAuditUtil.createCell(sheet, rowheadInfoValues, 0, auditId, cellStyleReadOnlyCenter, 0);
			BankAuditUtil.createCell(sheet, rowheadInfoValues, 1, auditTypeCode, cellStyleReadOnlyCenter, 1);
			BankAuditUtil.createCell(sheet, rowheadInfoValues, 2, auditTypeCode, cellStyleReadOnlyCenter, 0);
			  			
			//Create Header of Excel			
			XSSFRow rowhead = sheet.createRow(rowNumber++); 
			BankAuditUtil.createCell(sheet, rowhead, 0, "Observation Id", cellStyleHeader, 0); 		
			BankAuditUtil.createCell(sheet, rowhead, 1, "Criticality", cellStyleHeader, 1);			
			BankAuditUtil.createCell(sheet, rowhead, 2, "Observation", cellStyleHeader, 0);
			BankAuditUtil.createCell(sheet, rowhead, 3, "Compliance", cellStyleHeader, 55*256);
			
			if(rolesListScrutinizer.contains(role) && 
					(BankAuditUtil.isEmptyString(ratingComplianceStatus) || ratingComplianceStatus.equalsIgnoreCase("RCDDF"))){
				BankAuditUtil.createCell(sheet, rowhead, 4, "Reference score", cellStyleHeader, 12*256);
				BankAuditUtil.createCell(sheet, rowhead, 5, "Desk assessed score", cellStyleHeader, 12*256);
				BankAuditUtil.createCell(sheet, rowhead, 6, "Reference Remark", cellStyleHeader, 45*256);
				BankAuditUtil.createCell(sheet, rowhead, 7, "Updated Remark", cellStyleHeader, 45*256);
			}else if(rolesListScrutinizer.contains(role) && 
					(ratingComplianceStatus.equalsIgnoreCase("RCDU") || ratingComplianceStatus.equalsIgnoreCase("RCDM"))){
				BankAuditUtil.createCell(sheet, rowhead, 4, "Reference score", cellStyleHeader, 12*256);
				BankAuditUtil.createCell(sheet, rowhead, 5, "Desk effective score", cellStyleHeader, 12*256);
				BankAuditUtil.createCell(sheet, rowhead, 6, "Reference Remark", cellStyleHeader, 45*256);
				BankAuditUtil.createCell(sheet, rowhead, 7, "Updated Remark", cellStyleHeader, 45*256);
			}else if(rolesListInspector.contains(role) && 
					(BankAuditUtil.isEmptyString(ratingComplianceStatus) || ratingComplianceStatus.equalsIgnoreCase("RCDS") || ratingComplianceStatus.equalsIgnoreCase("RCFDF"))){
				BankAuditUtil.createCell(sheet, rowhead, 4, "Reference score", cellStyleHeader, 12*256);
				BankAuditUtil.createCell(sheet, rowhead, 5, "Field assessed score", cellStyleHeader, 12*256);
				BankAuditUtil.createCell(sheet, rowhead, 6, "Reference Remark", cellStyleHeader, 45*256);
				BankAuditUtil.createCell(sheet, rowhead, 7, "Updated Remark", cellStyleHeader, 45*256);
			}else if(rolesListReviewer.contains(role) &&
					(ratingComplianceStatus.equalsIgnoreCase("RCFU") ||ratingComplianceStatus.equalsIgnoreCase("RCFM"))){
				BankAuditUtil.createCell(sheet, rowhead, 4, "Reference score", cellStyleHeader, 12*256);
				BankAuditUtil.createCell(sheet, rowhead, 5, "Field effective score", cellStyleHeader, 12*256);
				BankAuditUtil.createCell(sheet, rowhead, 6, "Reference Remark", cellStyleHeader, 45*256);
				BankAuditUtil.createCell(sheet, rowhead, 7, "Updated Remark", cellStyleHeader, 45*256);
			}
			
			for (AuditRatingComplianceObsScore auditRatingComplianceObsScore : auditRatingComplianceObsScoreList) {
				XSSFRow rowComplRating = sheet.createRow(rowNumber);	
				
				BankAuditUtil.createCell(sheet, rowComplRating, 0, auditRatingComplianceObsScore.getObservationId(), cellStyleReadOnlyCenter, 0);
				BankAuditUtil.createCell(sheet, rowComplRating, 1, auditRatingComplianceObsScore.getObservationCriticality(), cellStyleReadOnlyCenter, 1);
				BankAuditUtil.createCell(sheet, rowComplRating, 2, BankAuditUtil.isEmptyString(auditRatingComplianceObsScore.getObservation())?"":auditRatingComplianceObsScore.getObservation().replaceAll("\\<.*?\\>", ""), cellStyleReadOnly, 0);
				BankAuditUtil.createCell(sheet, rowComplRating, 3, BankAuditUtil.isEmptyString(auditRatingComplianceObsScore.getCompliance())?"":auditRatingComplianceObsScore.getCompliance().replaceAll("\\<.*?\\>", ""), cellStyleReadOnly, 0);
			 	
				if(rolesListScrutinizer.contains(role) && 
						(BankAuditUtil.isEmptyString(ratingComplianceStatus) || ratingComplianceStatus.equalsIgnoreCase("RCDDF"))){
					BankAuditUtil.createCell(sheet, rowComplRating, 4, auditRatingComplianceObsScore.getDeskAssessedScore(), cellStyleReadOnlyCenter, 0);
					BankAuditUtil.createCell(sheet, rowComplRating, 5, "", cellStyleEditable, 0);
					BankAuditUtil.createCell(sheet, rowComplRating, 6, auditRatingComplianceObsScore.getReviewerMakerRemarks(), cellStyleReadOnly, 0);
					BankAuditUtil.createCell(sheet, rowComplRating, 7, "", cellStyleEditable, 0);
				}else if(rolesListScrutinizer.contains(role) && 
						(ratingComplianceStatus.equalsIgnoreCase("RCDU") || ratingComplianceStatus.equalsIgnoreCase("RCDM"))){
					BankAuditUtil.createCell(sheet, rowComplRating, 4, auditRatingComplianceObsScore.getDeskEffectiveScore(), cellStyleReadOnlyCenter, 0);
					BankAuditUtil.createCell(sheet, rowComplRating, 5, "", cellStyleEditable, 0);
					BankAuditUtil.createCell(sheet, rowComplRating, 6, auditRatingComplianceObsScore.getReviewerCheckerRemarks(), cellStyleReadOnly, 0);
					BankAuditUtil.createCell(sheet, rowComplRating, 7, "", cellStyleEditable, 0);
				}else if(rolesListInspector.contains(role) && 
						(BankAuditUtil.isEmptyString(ratingComplianceStatus) || ratingComplianceStatus.equalsIgnoreCase("RCDS") || ratingComplianceStatus.equalsIgnoreCase("RCFDF"))){
					BankAuditUtil.createCell(sheet, rowComplRating, 4, auditRatingComplianceObsScore.getFieldAssessedScore(), cellStyleReadOnlyCenter, 0);
					BankAuditUtil.createCell(sheet, rowComplRating, 5, "", cellStyleEditable, 0);
					BankAuditUtil.createCell(sheet, rowComplRating, 6, auditRatingComplianceObsScore.getAuditorMakerRemarks(), cellStyleReadOnly, 0);
					BankAuditUtil.createCell(sheet, rowComplRating, 7, "", cellStyleEditable, 0);
				}else if(rolesListReviewer.contains(role) &&
						(ratingComplianceStatus.equalsIgnoreCase("RCFU") ||ratingComplianceStatus.equalsIgnoreCase("RCFM"))){
					BankAuditUtil.createCell(sheet, rowComplRating, 4, auditRatingComplianceObsScore.getFieldEffectiveScore(), cellStyleReadOnlyCenter, 0);
					BankAuditUtil.createCell(sheet, rowComplRating, 5, "", cellStyleEditable, 0);
					BankAuditUtil.createCell(sheet, rowComplRating, 6, auditRatingComplianceObsScore.getAuditorCheckerRemarks(), cellStyleReadOnly, 0);
					BankAuditUtil.createCell(sheet, rowComplRating, 7, "", cellStyleEditable, 0);
				}
				
				rowNumber++;
			}
			
			DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper(); 
            DataValidationConstraint constraint = dataValidationHelper.createExplicitListConstraint(new String[] {"0", "1", "2", "3", "4", "5", "NA" }) ;             
            XSSFDataValidation validation = (XSSFDataValidation) dataValidationHelper.createValidation(constraint,
            		new CellRangeAddressList(3, rowNumber, 5, 5));	         
            validation.createErrorBox("Error", "Please enter score between 0 to 5 OR NA");
            validation.setSuppressDropDownArrow(true);
            validation.setShowErrorBox(true);
            sheet.addValidationData(validation);
            
            workbook.write(out);
            out.toByteArray();
            return out; 
		 
		}catch ( Exception ex ) {
			logger.error(ex);
			ex.printStackTrace(System.out);
        }		
		return null;		
	}
	 
}
