package com.bankaudit.template.rest.service;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import com.bankaudit.template.rest.dao.FileUploadDao;
import com.bankaudit.util.BankAuditUtil;

	@Service("FileUploadReadTemplateService")
	@Transactional("transactionManager")
	public class FileUploadReadTemplateImpl implements FileUploadReadTemplateService {

		@Autowired(required=true) 
		FileUploadDao fileUploadDao;

		@Override
		@SuppressWarnings("rawtypes")
		public String readTemplate(CommonsMultipartFile file,String tableName,String maker,Date businessDate) throws Exception {

			String message="";
			String cellValue = "";

			
			Map<String,String> dataMap=null;
			List uploadList=null;

			long size = (((long)file.getSize())/(1024*1024));

			if(size>4.0)	{
				message="File size "+size+" is too big.</br>Please split the file into multiple files that are smaller than 4096kb/4MB each.";
			}else if("".equals(message)) {

				POIFSFileSystem myFileSystem = new POIFSFileSystem(file.getInputStream());
				HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);

				//Sheet1 starts here
				HSSFSheet mySheet = myWorkBook.getSheetAt(0);
				Iterator rowIter = mySheet.rowIterator();

				HSSFRow exlRow=null;

				double dt=0;
				Date d=null;
				SimpleDateFormat s=null;

				double d1=0.0;

				String cellText = "" ;
				String Text="";

				ArrayList<Map<String,String>> colNames = null;
				Map hm = null;

				//below 2-variable are created to manage empty spaces in excel sheet
				int errorColumnIndex = 0;
				String errorColumnName = "";

				//sheet one iterator
				while (rowIter.hasNext()) {

					exlRow = (HSSFRow) rowIter.next();						
					//System.out.println(" RowNumber ................... : "+exlRow.getRowNum());

					errorColumnIndex = 0;
					errorColumnName = "";

					//First row in excel file
					HSSFCell cell =null;

					//get values from excel sheet create map column index should be same
					//If Excel has 2nd Row					   
					if(exlRow.getRowNum() == 0){						   
						colNames = fileUploadDao.getTableColumns(tableName);
						
						if(colNames==null || colNames.size()==0) {
							message = "File Upload Error: File could not be uploaded, since selected template"+tableName+" is not maching with the upload template.";
							break;
						}
						else if(((int)exlRow.getLastCellNum())!=colNames.size()){
							message = "File Upload Error: File could not be uploaded, since selected template columns size:"+colNames.size()+" is not matching with the upload template columns size:"+exlRow.getLastCellNum()+".";
							break;
						}else if(colNames!=null && colNames.size()>0){							
							for(int i=0;i<colNames.size();i++){
								hm=(Map)colNames.get(i);
								//System.out.println("colun Name : "+(String)hm.get("column") +" cellType : "+exlRow.getCell(i).getCellType());
								if(!((String)hm.get("column")).equalsIgnoreCase(exlRow.getCell(i).getStringCellValue().trim())){
									message = "File Upload Error: File could not be uploaded, since selected template column:"+(String)hm.get("column")+" is not maching with the upload template column:"+exlRow.getCell(i).getCellType()+".";										
									break;  
								}
							}
							if(!"".equals(message)) break;
						}
					}

					if(exlRow.getRowNum()>0){

						dataMap=new HashMap<>();
						//System.out.println("Columns Length ::::  "+colNames.size());

						try{

							for(int index=0;index < colNames.size(); index++) {

								//System.out.println("_____________________________________");

								hm=(HashMap)colNames.get(index);	
								cell = exlRow.getCell(index,HSSFRow.CREATE_NULL_AS_BLANK);
								d1=0.0;		
								cellValue="";
								Text="";
								switch(cell.getCellType()) {
								case HSSFCell.CELL_TYPE_NUMERIC : {

									if (cell.getCellStyle().getDataFormatString().contains("%")) {
										// Detect Percent Values 
										Double value = cell.getNumericCellValue() * 100;
										Long longValue = /*new Long(value.longValue());*/  Long.valueOf(value.longValue());
										cellValue = new String(longValue.toString());								        
										//System.out.println("Percent value found = " + value.toString() +"%");
									} else {
										d1 = cell.getNumericCellValue();
										if(((String)hm.get("columnType")).equals("DATE")){
											if (this.isCellDateFormatted(cell)) {
												d = HSSFDateUtil.getJavaDate(d1);
												s = new SimpleDateFormat("dd/MM/yyyy");
												cellValue = s.format(d);
											} 
										}else{
											Double value = new Double(cell.getNumericCellValue());
											Long longValue = /*new Long(value.longValue());*/  Long.valueOf(value.longValue());
											cellValue = new String(longValue.toString());
										}

									}
									//System.out.print("HSSFCell.CELL_TYPE_NUMERIC .. Cell Value :"+cellValue);
									break;
								}
								case HSSFCell.CELL_TYPE_FORMULA  :
									cellValue = cell.getCellFormula()!=null?cell.getCellFormula().trim():""; 
									//System.out.println("HSSFCell.CELL_TYPE_FORMULA");
									break;  
								case HSSFCell.CELL_TYPE_BOOLEAN  : 
									cellValue = cell.getBooleanCellValue() + ""; 
									//System.out.println("HSSFCell.CELL_TYPE_BOOLEAN");
									break;
								case HSSFCell.CELL_TYPE_ERROR  : 
									cellValue = cell.getErrorCellValue() + "";
									//System.out.println("HSSFCell.CELL_TYPE_ERROR");
									break;
								case HSSFCell.CELL_TYPE_STRING : 
									cellValue = cell.getStringCellValue()!=null?cell.getStringCellValue().trim():"";
									//System.out.println("HSSFCell.CELL_TYPE_STRING");
									break ;
								case HSSFCell.CELL_TYPE_BLANK : 
									cellValue = "";
									//System.out.println("HSSFCell.CELL_TYPE_BLANK");
									break ;	
								default :
									cellValue = "";
									//System.out.println("default/unknown CellType : " +cell.getCellType());
									break ;
								} //switch 

								/*if("Open Date".equals((String)hm.get("column"))) {

								System.out.println("\t getColumnIndex : " +cell.getColumnIndex());
								System.out.print(" getCellType : " +cell.getCellType());								
								System.out.print(" column : " +(String)hm.get("column"));
								System.out.print(" columnType : " +(String)hm.get("columnType"));
								System.out.print(" cellValue : " +cellValue);
									
								}*/	

								cellValue = cellValue!=null?cellValue.trim():cellValue; 

								//working 
								//if any non empty value fond then checked for temp flags {that maintained for empty column value.}
								
								/*if(!"".equals(cellValue)){
									message = "File Upload Error: File could not be uploaded, since Template with column : "+errorColumnName+" at (R"+(exlRow.getRowNum()+1)+",Column"+(errorColumnIndex)+") should not be an empty value.";
									break;			
								}*/

								if(!"".equals(cellValue) && (cellValue.length() > Integer.parseInt((String)hm.get("columnLenght")) /*new Integer((String)hm.get("columnLenght"))*/)   ) {
									message = "File Upload Error: File could not be uploaded, since Template with column : "+(String)hm.get("column")+" at (R"+(exlRow.getRowNum()+1)+",Column"+(cell.getCellNum()+1)+") value is too large in size. "
											+ "Allowed size in DataBase is :"+ Integer.parseInt((String)hm.get("columnLenght")) /*new Integer((String)hm.get("columnLenght"))*/;
								}else if(((String)hm.get("columnType")).equalsIgnoreCase("NUMBER") || ((String)hm.get("columnType")).equalsIgnoreCase("INT")){
									try{
										Text=cellValue.equals("")?"":Integer.parseInt(cellValue)+"";
									}catch(Exception e){
										try{
											Text=cellValue.equals("")?"":Double.parseDouble(cellValue)+"";
										}catch(Exception ex){
											throw ex;
										}
									}
								}else if(((String)hm.get("columnType")).equalsIgnoreCase("DATE")){
									if(cellValue != null && !(cellValue.trim()).equals("")){	
										if(!BankAuditUtil.isThisDateValid(cellValue,"dd/MM/yyyy")){
											message = "File Upload Error: File could not be uploaded, since Template with column : "+(String)hm.get("column")+" at (R"+(exlRow.getRowNum()+1)+",Column"+(cell.getCellNum()+1)+") have Invalid Date Format.";
											break;	
										}
									}
									Text=cellValue;												
								}else if(((String)hm.get("columnType")).equalsIgnoreCase("timestamp")){
									Text=cellValue;									
								}else if(((String)hm.get("columnType")).equalsIgnoreCase("VARCHAR") || ((String)hm.get("columnType")).equalsIgnoreCase("VARCHAR2")){
									Text=cellValue;
								}
								//System.out.println("Text : " +Text);	
								dataMap.put((String)hm.get("column"), Text == null ?"":Text.trim());


							}//end for loop for cell values 
							//=============== Handle empty rows here ===============

							if(uploadList==null)
								uploadList=new ArrayList();
							
							if(dataMap!=null){
								uploadList.add(dataMap);
							}
							
							System.out.println("Read Record rom excel is :"+dataMap);

						}catch(Exception e){
							message = "Type miss match for column "+(String)hm.get("column")+" at (R"+exlRow.getRowNum()+",C"+cell.getCellNum()+") ";
							message +="Erorr : "+e.getMessage();
							break;
						} 

					}// row gt>0

				}//while loop
				// add row data to arraylist
			//	System.out.println("Read complete sheet data is : " +uploadList);	     
				if("".equals(message) && (uploadList!=null && uploadList.size()>0)) {
					//upload into database depends on the table.
					fileUploadDao.uploadExcelData(tableName,uploadList,maker,businessDate);
				}
			}//else
			return message;
		}//method

		/**
		 * check weather Cell Date Format
		 * @param cell
		 * @return
		 * @throws Exception
		 */
		public boolean isCellDateFormatted(HSSFCell cell) throws Exception{
			boolean bDate = false;
			double d = cell.getNumericCellValue();
			if( HSSFDateUtil.isValidExcelDate(d) ) {
				HSSFCellStyle style = cell.getCellStyle();
				int i = style.getDataFormat();
				switch(i) {
				case 0xe:
				case 0xf:
				case 0x10:
				case 0x11:
				case 0x12:
				case 0x13:
				case 0x14:
				case 0x15:
				case 0x16:
				case 0x2d:
				case 0x2e:
				case 0x2f:
					bDate = true;
					break;
				default:
					bDate = false;
					break;
				}
			}
			return bDate;
		}


}
