package com.bankaudit.rest.process.service;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
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.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import com.bankaudit.constants.BankAuditConstant;
import com.bankaudit.dto.DataTableResponse;
import com.bankaudit.dto.process.AuditReportDynamicTableDto;
import com.bankaudit.dto.process.AuditReportDynamicTablePredefinedDataListDto;
import com.bankaudit.process.model.AuditLevelDocumentDetails;
import com.bankaudit.process.model.AuditReportDynamicTable;
import com.bankaudit.process.model.AuditReportDynamicTableDetails;
import com.bankaudit.process.model.AuditSchedule;
import com.bankaudit.rest.process.dao.AuditReportDynamicTableDao;
import com.bankaudit.util.BankAuditUtil;

@Service("auditReportDynamicTableService")
@Transactional("transactionManager")
public class AuditReportDynamicTableServiceImpl implements AuditReportDynamicTableService{

	@Autowired
	AuditReportDynamicTableDao auditReportDynamicTableDao; 
	
	static final Logger logger = Logger.getLogger(AuditReportDynamicTableServiceImpl.class);
	
	@Override
	public DataTableResponse getDynamicTables(Integer legalEntityCode, String userId, String role, String search, Integer orderColumn,	String orderDirection, Integer page, Integer size,
			LocalDate financialYear, String auditTypeCode) {
		List<LocalDate>  financialYearStrtEndList=null;
		if(financialYear!=null) financialYearStrtEndList= BankAuditUtil.getFinancialStartEndDatesNxtFrom(financialYear);
		return auditReportDynamicTableDao.getDynamicTables(legalEntityCode, userId, role, search, orderColumn,orderDirection, page, size, 
				financialYear, auditTypeCode, financialYearStrtEndList); 
	}
	
	@Override
	public AuditReportDynamicTableDto getDynamicTablesByAuditId(int legalEntityCode, String auditTypeCode, String auditId){
		return auditReportDynamicTableDao.getDynamicTablesByAuditId(legalEntityCode, auditTypeCode, auditId);
	}
	
	@Override
	public List<AuditReportDynamicTable> getDynamicTablesByAuditIdForAnnexure(int legalEntityCode, String auditTypeCode, String auditId){
		return auditReportDynamicTableDao.getDynamicTablesByAuditIdForAnnexure(legalEntityCode, auditTypeCode, auditId);
	}
	
	@Override
	public AuditReportDynamicTableDto getDynamicTablesById(Integer legalEntityCode, String auditTypeCode, String auditId, Integer id) {
		AuditReportDynamicTable dynamicTable = auditReportDynamicTableDao.getDynamicTableById(legalEntityCode, auditTypeCode, auditId, id);
		if(dynamicTable!=null){
			dynamicTable.getAuditReportDynamicTableDetailList().size();
		}
		AuditReportDynamicTableDto ardTableDto = new AuditReportDynamicTableDto(); 
		ardTableDto.setArdTable(dynamicTable); 		
		return ardTableDto; 
	}
	
	@Override
	public void createDynamicTables(AuditReportDynamicTableDto auditReportDynamicDtls) {
		logger.info("Inside createDynamicTables .. " +auditReportDynamicDtls);
		auditReportDynamicTableDao.save(auditReportDynamicDtls.getArdTable()); 
	}
	
	@Override
	public void updateDynamicTables(AuditReportDynamicTableDto auditReportDynamicDtls) {
		logger.info("Inside updateDynamicTables .. " +auditReportDynamicDtls);
		try { 
			AuditReportDynamicTable ardTable = auditReportDynamicDtls.getArdTable();
			List<AuditReportDynamicTableDetails> ardTableDtlsLst = ardTable.getAuditReportDynamicTableDetailList();
			if(ardTableDtlsLst!=null && ardTableDtlsLst.size()>0){
				List<Integer> dynamicTableDetailIds = ardTableDtlsLst.stream().filter(e -> e.getId()!=null && BankAuditConstant.STATUS_DEL.equalsIgnoreCase(e.getRowStatus())).map(AuditReportDynamicTableDetails::getId).collect(Collectors.toList());
	 			if(dynamicTableDetailIds!=null && dynamicTableDetailIds.size()>0){
					auditReportDynamicTableDao.deleteAuditReportDynamicTableDetailsByTableId(ardTable.getLegalEntityCode(), ardTable.getId(), 
							ardTable.getAuditTypeCode(), ardTable.getAuditId(), dynamicTableDetailIds);
					auditReportDynamicTableDao.flushSession();
	 			} 
 			}
			auditReportDynamicTableDao.saveOrUpdate(ardTable);
			
		}catch(Exception e ) {
			e.printStackTrace(System.out);
		}
	}
	 	
	@Override
	public void updateDynamicTablesSequencing(AuditReportDynamicTableDto auditReportDynamicSequence) {
		logger.info("Inside updateDynamicTablesSequencing .. " +auditReportDynamicSequence);
		try {
			List<AuditReportDynamicTable> ardTablesLst = auditReportDynamicSequence.getArdTableSequenceList();
			for(AuditReportDynamicTable ardTable: ardTablesLst) { 
				String strQuery = " Update AuditReportDynamicTable "
						+ " set sequence = "+ardTable.getSequence()
						+ " where legalEntityCode = '"+ardTable.getLegalEntityCode()+"' "
						+ " and auditTypeCode = '"+ardTable.getAuditTypeCode()+"' "
						+ " and auditId = '"+ardTable.getAuditId()+"' "
						+ " and id = "+ardTable.getId() ;
				auditReportDynamicTableDao.executeHQLQuery(strQuery);				 
			} 
			System.out.println("===================");
		}catch(Exception e ) {
			e.printStackTrace(System.out);
		}
	} 
	
	@Override
	public void deleteDynamicTablesById(Integer legalEntityCode, String auditTypeCode, String auditId, Integer id){
		logger.info("Inside deleteDynamicTablesById .. "+legalEntityCode +" :: " +id+" :: "+auditId +" :: "+id );
		try { 
			auditReportDynamicTableDao.deleteDynamicTablesById(legalEntityCode, auditTypeCode, auditId, id); // Parent table
			auditReportDynamicTableDao.flushSession();			
		}catch(Exception e ) {
			e.printStackTrace(System.out);
		}
	} 
	
	@Override
	public List<AuditSchedule> getInprogressAuditbyUserId(int legalEntityCode, String userId, String roleId) {
		List<AuditSchedule> auditSchLst = auditReportDynamicTableDao.getInprogressAuditbyUserId(legalEntityCode, userId, roleId);
		return auditSchLst; 
	} 
	
	@Override
	public String uploadDynamicTablesSheet(AuditReportDynamicTable ardTable, MultipartFile[] files){		
		
		Integer ardTableId = ardTable.getId();
		if(ardTableId != null && ardTableId>0){
			auditReportDynamicTableDao.deleteDynamicTablesById(ardTable.getLegalEntityCode(), ardTable.getAuditTypeCode(), ardTable.getAuditId(), ardTableId); // Parent table 
		}
		
		String fileName=ardTable.getLegalEntityCode().toString()+ardTable.getAuditTypeCode()+ardTable.getAuditId()+ardTable.getRole()+ardTable.getDocumentType();				   
		AuditLevelDocumentDetails auditLevelDocumentDetails =  new AuditLevelDocumentDetails();
		auditLevelDocumentDetails.setLegalEntityCode(ardTable.getLegalEntityCode());
		auditLevelDocumentDetails.setAuditTypeCode(ardTable.getAuditTypeCode()); 
		auditLevelDocumentDetails.setAuditId(ardTable.getAuditId());
		auditLevelDocumentDetails.setPlanId(ardTable.getPlanId());
		auditLevelDocumentDetails.setDocumentType(ardTable.getDocumentType());  
		auditLevelDocumentDetails.setDocumentName(ardTable.getDocumentName());					
		auditLevelDocumentDetails.setDocumentSubType(ardTable.getDocumentSubType());  
		auditLevelDocumentDetails.setMaker(ardTable.getMaker());  			 
		auditLevelDocumentDetails.setMakerTimestamp(new Timestamp(System.currentTimeMillis()));
		auditLevelDocumentDetails.setUserRoleId(ardTable.getRole());			 		
		auditLevelDocumentDetails.setUnitCode(ardTable.getUnitId());
		auditLevelDocumentDetails.setSeqId(ardTable.getSequence()+""); 
		auditLevelDocumentDetails.setUploadStatus(BankAuditConstant.STATUS_INACTIVE);
		
		for (MultipartFile multipartFile : files) {
			 			
			try{   
				String pattern = "yyyyMMddHHmmssSSS"; 
				SimpleDateFormat simpleDateFormat = new SimpleDateFormat(pattern);  
				String referencePath =  BankAuditUtil.uploadMaterial(multipartFile, BankAuditConstant.DIR_DOC_UPLOAD, simpleDateFormat.format(new Date())+"_"+fileName);
				auditLevelDocumentDetails.setReferencePath(referencePath);  
				auditLevelDocumentDetails.setFileName(multipartFile.getOriginalFilename()); 
			}catch (Exception e) {
				logger.error("Error .." + e.getMessage()+" :: "+e.getCause());  
				return BankAuditConstant.FAILED;
			}
			 
			ardTable.setSequence(Integer.parseInt(auditLevelDocumentDetails.getSeqId())); 
			if(BankAuditConstant.FOR_FLEXIBLE_TABLE.equalsIgnoreCase(ardTable.getStatementType())
					|| BankAuditConstant.FOR_PREDEFINED_OR_EXTERNAL_TABLE.equalsIgnoreCase(ardTable.getStatementType())
					|| BankAuditConstant.FOR_ANNEXURE.equalsIgnoreCase(ardTable.getStatementType())){
				/* ****** read excel Start  ******* */
				try(XSSFWorkbook uploadExcel = new XSSFWorkbook(multipartFile.getInputStream())){ 
					XSSFSheet sheet = uploadExcel.getSheetAt(0);			
					 				  				
					Row headerRow = sheet.getRow(0); 
					int numberOfColumns = headerRow==null?0:getColumnCount(headerRow); 
					if(headerRow==null || isRowEmpty(headerRow, numberOfColumns)){
						return "Number of rows is zero";
					}
					
					if(ardTable.getNoOfColumns()!=null && ardTable.getNoOfColumns()!=0 && numberOfColumns!=ardTable.getNoOfColumns()){
						return "Number of columns are not matched with selected template";
					}else if(numberOfColumns>9 ){  
						return "Number of columns is more than 9";
					}else if(numberOfColumns==0){
						return "Number of rows is zero";
					}
					 
					int cellNum=0;
					while(cellNum<numberOfColumns){ 					
						Cell cell =   headerRow.getCell(cellNum);
						if(cell==null){
							return "Header is empty at column "+(cellNum+1);
						}
						cell.setCellType(Cell.CELL_TYPE_STRING); 
						String cellValue=cell.getStringCellValue();
						if(!BankAuditUtil.isEmptyString(cellValue)){ 
							if(cellValue.length()>50){
								return "Max allowed lenght for header is 50";
							}						 				
							String header = "header"+String.format("%02d", (cellNum+1) );							
							org.apache.commons.beanutils.PropertyUtils.setSimpleProperty(ardTable, header, cellValue); 					
						}else{
							return "Header is empty at cell "+(cellNum+1);
						}
						cellNum++;
					}					
					
					Row secondRow = sheet.getRow(1); 
					if(secondRow==null || isRowEmpty(secondRow, numberOfColumns)){
						return "Number of rows is zero";
		        	}  
					
					sheet.removeRow(sheet.getRow(0)); 
					ardTable.setNoOfColumns(numberOfColumns);
					List<AuditReportDynamicTableDetails> ardTableDtlsLst = new ArrayList<AuditReportDynamicTableDetails>();
					//Iterator<Row> rowIterator = sheet.iterator();//rowIterator(); 				
			        //while (rowIterator.hasNext()) {
			        for( Row row : sheet){	 	        	
			        	if(row == null || isRowEmpty(row, numberOfColumns)){ 
			        		//break;
			        		continue;
			        	}  	        	
			        
			        	AuditReportDynamicTableDetails ardTableDtls =  new AuditReportDynamicTableDetails();
			        	ardTableDtls.setAuditId(auditLevelDocumentDetails.getAuditId());
			        	ardTableDtls.setAuditTypeCode(auditLevelDocumentDetails.getAuditTypeCode());
			        	ardTableDtls.setLegalEntityCode(auditLevelDocumentDetails.getLegalEntityCode());
			        	ardTableDtls.setSequenceRow(row.getRowNum()+1);		        	
			        	 
			        	cellNum = 0;
			        	while(cellNum<numberOfColumns){ 					
							Cell cell =   row.getCell(cellNum);
							if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK 
									|| (cell.getCellType() == Cell.CELL_TYPE_STRING && cell.getStringCellValue().trim().isEmpty())){
								return "Column is empty at row "+ardTableDtls.getSequenceRow()+", col "+(cellNum+1) ; 
							}
							  
							String cellValue= BankAuditUtil.readCellValue(cell);						
							if(!BankAuditUtil.isEmptyString(cellValue)){ 							 
								if(cellValue.length()>1000){
									return "Max allowed lenght for column is 1000, please check at row "+ardTableDtls.getSequenceRow()+", col "+(cellNum+1);
								}							 
								String content = "content"+String.format("%02d",(cellNum+1) );								
								org.apache.commons.beanutils.PropertyUtils.setSimpleProperty(ardTableDtls, content, cellValue); 							
							}else{ 
								return "Column is empty at row"+ardTableDtls.getSequenceRow()+", col "+(cellNum+1) ; 							  
							} 
							cellNum++;
						}             
						ardTableDtlsLst.add(ardTableDtls);					
			        }	         
			        
			        auditLevelDocumentDetails.setUploadStatus(BankAuditConstant.SUCCESS);
			        auditReportDynamicTableDao.save(auditLevelDocumentDetails);
			        ardTable.setDocId(auditLevelDocumentDetails.getId());
			        
			        AuditReportDynamicTableDto auditReportDynamicDtls = new AuditReportDynamicTableDto();
			        ardTable.setAuditReportDynamicTableDetailList(ardTableDtlsLst);
			        auditReportDynamicDtls.setArdTable(ardTable); 
			        createDynamicTables(auditReportDynamicDtls);
			        
				}catch (Exception e) {
					logger.error("Error .." + e.getMessage()+" :: "+e.getCause());  
					return BankAuditConstant.FAILED;
				}	
			}else{
				 auditLevelDocumentDetails.setUploadStatus(BankAuditConstant.SUCCESS);
				 auditReportDynamicTableDao.save(auditLevelDocumentDetails);
		         ardTable.setDocId(auditLevelDocumentDetails.getId());
		         auditReportDynamicTableDao.save(ardTable);  
			}
		}
		return BankAuditConstant.SUCCESS;
	}		

	@Override
	public List<AuditReportDynamicTablePredefinedDataListDto> getPredefinedStatementDetail(int legalEntityCode,
			String auditTypeCode, String auditId, String auditGroupCode, String auditSubGroupCode, String activityCode,
			String processCode, String statementType, String all) { 
		return auditReportDynamicTableDao.getPredefinedStatementDetail(legalEntityCode, auditTypeCode, auditId, auditGroupCode, auditSubGroupCode, activityCode,
				processCode, statementType, all);
	}
	
	@Override
	public AuditReportDynamicTable  getSavedStatementDetail(int legalEntityCode, String auditTypeCode, String auditId, String auditGroupCode,
			String auditSubGroupCode,  String activityCode,  String processCode, String statementType, String statementId){
		AuditReportDynamicTable dynamicTable = auditReportDynamicTableDao.getSavedStatementDetail(legalEntityCode, auditTypeCode, auditId, auditGroupCode, auditSubGroupCode, activityCode,
				processCode, statementType, statementId);		 
		if(dynamicTable!=null){
			dynamicTable.getAuditReportDynamicTableDetailList().size();
		} 		
		return dynamicTable; 
	}
	

	private static boolean isRowEmpty(Row row, int columnCount) {
		boolean isEmpty = true;	
    	for( int cellNum=0 ; cellNum<columnCount && isEmpty; cellNum++){
			Cell cell =   row.getCell(cellNum);
			if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK 
					|| (cell.getCellType() == Cell.CELL_TYPE_STRING && cell.getStringCellValue().trim().isEmpty())){ 
				isEmpty = true; 
			}else{isEmpty = false;}
    	} 
		return isEmpty;
	}
	
	private static int getColumnCount(Row row) { 
		int columnCount  = 0; 
    	for( Cell cell: row){ 
			if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK 
					|| (cell.getCellType() == Cell.CELL_TYPE_STRING && cell.getStringCellValue().trim().isEmpty())){  
			}else{
				columnCount++;
			}
    	} 
		return columnCount;
	}
	  
}
