package com.mx.dla.dda.excelMapper.bos;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.annotation.PostConstruct;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.mx.dla.dda.excelMapper.constants.ExcelMapperDataType;
import com.mx.dla.dda.excelMapper.context.ExcelMapperContext;
import com.mx.dla.dda.excelMapper.dtos.ExcelEntityResult;
import com.mx.dla.dda.excelMapper.dtos.ExcelMapper;
import com.mx.dla.dda.excelMapper.dtos.ExcelRule;
import com.mx.dla.dda.excelMapper.exceptions.ExcelMapperException;

@Component
public class ExcelMapperTransform {

	private Logger logger = LoggerFactory.getLogger("excelMapper");
	private static final String CELL_SEPARATOR= ",";
	
	@Autowired
	private ExcelMapperContext excelMapperContext;

	private BeanPopulator beanPopulator;

	@PostConstruct
	public void init() {
		beanPopulator = excelMapperContext.getBeanPopulator();
	}

	public <T> ExcelEntityResult<T> readExcelToObject(
			File stream, Class<T> c) throws ExcelMapperException {
		return readExcelToObject(stream, c.getName());
	}

	public <T> ExcelEntityResult<T> readExcelToObject(
			File stream, String idParse) throws ExcelMapperException {
		try {
			return readExcelToObject(new FileInputStream(stream), idParse);
		} catch (FileNotFoundException e) {
			logger.error("Error", e);
			throw new ExcelMapperException(
					ExcelMapperException.ERROR_READING_EXCEL_FILE);
		}
	}
	
	@SuppressWarnings("unchecked")
	public <T> ExcelEntityResult<T> readExcelToObject(
			InputStream stream, String idParse) throws ExcelMapperException {
		ExcelMapper mapper = excelMapperContext.getExcelMapper(idParse);
		List<T> list = new ArrayList<T>();
		List<String> errors = new ArrayList<String>();
		Set<Integer> rowsWithErrors = new HashSet<Integer>();
		ExcelEntityResult<T> result = new ExcelEntityResult<T>();
		try {
			Class<T> clzz = (Class<T>) Class.forName(mapper.getClassName());
			Workbook wb = WorkbookFactory.create(stream);
			Sheet sh = wb.getSheetAt(0);
			int init =  mapper.getFirstRow()< 0 ? 0: mapper.getFirstRow()-1;
			T zero = beanPopulator.initObject(clzz);
			for (int i = init; i <= sh.getLastRowNum(); i++) {
				Row row = sh.getRow(i);
				T target = beanPopulator.initObject(clzz);
				for (ExcelRule exRule : mapper.getRules()) {
					logger.trace("To rule [{}]",exRule);
					CellReference ref = new CellReference(exRule.getColumn());
					Cell cell = row.getCell(ref.getCol());
					try {
						if(cell != null &&  cell.getCellType() != Cell.CELL_TYPE_BLANK)
						{
							asignaValorObject(exRule, cell, target);
						}
					} catch (ExcelMapperException e) {
						rowsWithErrors.add(i);
						errors.add("In Row[" + (i-1) + "]:" + e.getMessage());
					}

				}
				if(!zero.equals(target))
				{
					list.add(target);
				}
			}
			
			result.setErrorRows(calculateRowWithErrors(sh,rowsWithErrors));
		} catch (InvalidFormatException e) {
			logger.error("Error", e);
			throw new ExcelMapperException(
					ExcelMapperException.ERROR_READING_EXCEL_FILE);
		} catch (IOException e) {
			logger.error("Error", e);
			throw new ExcelMapperException(
					ExcelMapperException.ERROR_READING_EXCEL_FILE);
		} catch (ClassNotFoundException e) {
			logger.error("Error ",e);
			throw new ExcelMapperException(
					ExcelMapperException.ERROR_READING_EXCEL_FILE
							+ mapper.getClassName());
		}
		result.setResult(list);
		result.setErrors(errors);
		return result;
	}
	
	@SuppressWarnings("unchecked")
	public <T> ExcelEntityResult<T> readExcelToObject(String archivo, ExcelMapper mapper )throws ExcelMapperException {		
		
		ExcelEntityResult<T> result = new ExcelEntityResult<T>();
		
		try {
			FileInputStream file = new FileInputStream(new File(archivo));
			
			List<T> list                = new ArrayList<T>();
			List<String> errors         = new ArrayList<String>();
			Set<Integer> rowsWithErrors = new HashSet<Integer>();						
		
			Class<T> claz =  (Class<T>) Class.forName(mapper.getClassName());			
			Workbook wb   =  WorkbookFactory.create(file);
			Sheet sh      =  wb.getSheetAt(0);
			int init      =  (mapper.getFirstRow() < 0) ? 0: mapper.getFirstRow()-1;
				
			
			for (int i = init; i <= sh.getLastRowNum(); i++) 
			{
				Row row = sh.getRow(i);
				T zero  =  beanPopulator.initObject(claz);
				
                Iterator<Cell> cellIterator = row.cellIterator();
                List    <Cell> celdas       = new ArrayList<Cell>();
                
                //while (cellIterator.hasNext()) 
                //	   celdas.add(cellIterator.next());
                
                for(int cn=0; cn<row.getLastCellNum(); cn++) 
                {
                    Cell cell = row.getCell(cn, Row.CREATE_NULL_AS_BLANK);
                    celdas.add(cell);
                }
                                    																
				asignaValorArregloObject(mapper.getRules().get(0), celdas, zero);
				list.add(zero);																																		
			}
			
			result.setErrorRows(calculateRowWithErrors(sh,rowsWithErrors));
			
			file.close();
			result.setResult(list);
			result.setErrors(errors);
			
		} catch (FileNotFoundException e) {
			logger.error("Error", e);
			throw new ExcelMapperException(ExcelMapperException.ERROR_READING_EXCEL_FILE);
		} catch (InvalidFormatException e) {
			logger.error("Error", e);
			throw new ExcelMapperException(ExcelMapperException.ERROR_READING_EXCEL_FILE);		
		} catch (IOException e) {
			logger.error("Error", e);
			throw new ExcelMapperException(ExcelMapperException.ERROR_READING_EXCEL_FILE);
		} catch (ClassNotFoundException e) {
			logger.error("Error ",e);
			throw new ExcelMapperException(ExcelMapperException.ERROR_READING_EXCEL_FILE+ mapper.getClassName());
		}		
		
		return result;
	}

	private List<String> calculateRowWithErrors (Sheet sh , Set<Integer> errorIndexes)
	{
		List<String> rowsWithErrors= new ArrayList<String>();
		for(Integer i : errorIndexes)
		{
			Row row = sh.getRow(i);
			rowsWithErrors.add(rowToString(row));
		}
		return rowsWithErrors;
	}
	
	private String rowToString(Row row)
	{
		StringBuilder sb = new StringBuilder();
		Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext())
        {
        	Cell cell = cellIterator.next();
        	cell.setCellType(Cell.CELL_TYPE_STRING);
        	sb.append(cell.getStringCellValue());
        	sb.append(CELL_SEPARATOR);
        }
		return sb.toString();
	}
	
	private void asignaValorObject(ExcelRule exRule, Cell cell, Object target)
			throws ExcelMapperException {
		ExcelMapperDataType type = exRule.getType() == null ? ExcelMapperDataType.string
				: exRule.getType();
		switch (type) {
		case date:
			try {
				beanPopulator.setProperty(target, exRule.getAttribute(),
						cell.getDateCellValue());
			} catch (Exception e) {
				cell.setCellType(Cell.CELL_TYPE_STRING);
				beanPopulator.setProperty(target, exRule.getAttribute(),
						cell.getStringCellValue().trim());
			}
			break;
		case numeric:
			try {
				beanPopulator.setProperty(target, exRule.getAttribute(),
						cell.getNumericCellValue());
			} catch (Exception e) {
				cell.setCellType(Cell.CELL_TYPE_STRING);
				beanPopulator.setProperty(target, exRule.getAttribute(),
						cell.getStringCellValue().trim());
			}
			break;
		case bool:
			try {
				beanPopulator.setProperty(target, exRule.getAttribute(),
						cell.getBooleanCellValue());
			} catch (Exception e) {
				cell.setCellType(Cell.CELL_TYPE_STRING);
				beanPopulator.setProperty(target, exRule.getAttribute(),
						cell.getStringCellValue().trim());
			}
			break;
		case string:
		default:
			cell.setCellType(Cell.CELL_TYPE_STRING);
			beanPopulator.setProperty(target, exRule.getAttribute(),
					cell.getStringCellValue().trim());
			break;
		}
	}
	
	private void asignaValorArregloObject(ExcelRule exRule, List<Cell> cells, Object target) throws ExcelMapperException {
		
		ExcelMapperDataType type = exRule.getType() == null ? ExcelMapperDataType.string : exRule.getType();
		
		switch (type) 
		{		
			case numeric:
				try {
					List<Double> numeros = new ArrayList<Double>();
					for(Cell c : cells)
						numeros.add(c.getNumericCellValue());
					
					beanPopulator.setProperty(target, exRule.getAttribute(), numeros);
										
				} catch (Exception e) {
					List<String> str = new ArrayList<String>();					
					for(Cell c : cells)
					{
						c.setCellType(Cell.CELL_TYPE_STRING);
						str.add(c.getStringCellValue().trim());
					}					
				}
				break;			
		
			case string:
				try {
					List<String> cadenas = new ArrayList<String>();
					logger.debug(cells.toString());
					for(Cell c : cells)
					{
						c.setCellType(Cell.CELL_TYPE_STRING);
						cadenas.add(c.getStringCellValue().trim());
					}						
					
					beanPopulator.setProperty(target, exRule.getAttribute(), cadenas);
										
				} catch (Exception e) 
				{
					logger.error("Error {}", e);
				}					
		}
	}
	
	public <T> byte[] transformObjectToExcel(List<T> elements ,Class<T> clase) throws ExcelMapperException, IOException {
		return transformObjectToExcel(elements, clase.getName(), clase);
	}
	
	public <T> byte[] transformObjectToExcel(List<T> elements,String idParse ,Class<T> clase) throws ExcelMapperException, IOException {
		logger.debug("idParse [{}] class [{}]",idParse,clase.getName());
		ExcelMapper mapper = excelMapperContext.getExcelMapper(idParse);
		return transformObjectToExcel(elements,mapper,clase);
	}
	
	public <T> byte[] transformObjectToExcel(List<T> elements, ExcelMapper mapper ,Class<T> clase) throws ExcelMapperException, IOException {
		logger.debug("mapper class [{}]  mapper id[{}]",mapper.getClassName(),mapper.getId() );
		if(!mapper.getClassName().equals(clase.getName()))
		{
			throw new ExcelMapperException(ExcelMapperException.ERROR_MAPPER_CLASS, mapper.getClassName(),clase.getName() );
		}
		
		int size = elements.size()/3 < 1 ? elements.size() : elements.size()/3; 
		SXSSFWorkbook workbook = new SXSSFWorkbook(size);
		Sheet sheet = workbook.createSheet();
		
		//Blank workbook
		//XSSFWorkbook workbook = new XSSFWorkbook();
        //Create a blank sheet
        //XSSFSheet sheet = workbook.createSheet();
        
        int r = 0;
        Row row = sheet.createRow(r++);        
        for(ExcelRule exRule: mapper.getRules())
        {        
                Cell cell = row.createCell( CellReference.convertColStringToIndex(exRule.getColumn())  );
                cell.setCellValue(exRule.getHeader());
        }
        for(T element : elements)
        {
        	Row rowE = sheet.createRow(r++);
	        for(ExcelRule exRule: mapper.getRules())
	        {
	        	try{
	        		Cell cell = rowE.createCell(CellReference.convertColStringToIndex(exRule.getColumn()));
	        		cell = asignaValorCell(exRule, cell, element);
	        		cell =  asignaStyleCell(exRule, cell, workbook);
	        	}
	        	catch(Exception e)
	        	{
	        		logger.error("Error",e);
	        	}
	        }
        }
        
        sheet.autoSizeColumn(100);
        //for(ExcelRule exRule: mapper.getRules())
        //{
        	//CellReference ref = new CellReference(exRule.getColumn());        	
        //}
                
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
		return baos.toByteArray();
	}
	

	private Cell  asignaValorCell(ExcelRule exRule,Cell cell, Object target)
			throws ExcelMapperException {
		ExcelMapperDataType type = exRule.getType() == null ? ExcelMapperDataType.string
				: exRule.getType();
		Object temp = beanPopulator.getProperty(target, exRule.getAttribute());
		logger.info("Setting [{}] type [{}] value [{}]",new Object[]{exRule.getAttribute(),type,temp});

		if(temp == null)
		{
			//cell.setCellValue("");
		}
		else
		{
			switch (type) {
			case date:
				try {
					if(temp !=null)
						cell.setCellValue((Date)  temp);
					//else
						//cell.setCellValue("");
				} catch (Exception e) {
					cell.setCellValue( temp.toString()   );
				}
				break;
			case numeric:
				try {
					if (temp != null) {
						double d = Double.parseDouble(temp.toString());
						cell.setCellValue(d);
					}
					else
						cell.setCellValue(0);
				} catch (Exception e) {
					cell.setCellValue( temp.toString()   );
				}
				break;
			case bool:
				try {
					if(temp !=null)
						cell.setCellValue((boolean)  temp);
					else
						cell.setCellValue("");
				} catch (Exception e) {
					cell.setCellValue( temp.toString()   );
				}
				break;
			case string:
			default:
				cell.setCellValue( temp.toString()   );
				break;
			}
		}
		return cell;
	}
	
	private Cell  asignaStyleCell(ExcelRule exRule,Cell cell,SXSSFWorkbook workbook ) throws ExcelMapperException {
		
		ExcelMapperDataType type = exRule.getType() == null ? ExcelMapperDataType.string : exRule.getType();
		//XSSFDataFormat df = workbook.createDataFormat();
		
		switch (type) {
		case date:
			CellStyle dateStyle = workbook.createCellStyle();
			DataFormat df = workbook.createDataFormat();
			String dateFormat = excelMapperContext.getDateFormat();
			dateFormat = exRule.getDateFormat() != null ? exRule.getDateFormat() : dateFormat;
			dateStyle.setDataFormat(df.getFormat(dateFormat));
			cell.setCellStyle(dateStyle);
			break;
		case numeric:
			
			break;
		case bool:
			
			break;
		case string:
		default:
			break;
		}
		return cell;
	}
	
	
	public byte[] transformMapToExcel(List<HashMap> elements) throws ExcelMapperException, IOException {

		byte[] stream = null;

		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet sheet = workbook.createSheet();

		if (elements != null && !elements.isEmpty()) {

			int r = 0;

			Row row = sheet.createRow(r);

			HashMap<String, Object> header = elements.get(0);
			int hc = 0;

			for (String key : header.keySet()) {

				Cell cell = row.createCell(hc);
				cell.setCellValue(key);

				hc++;

			}

			r++;

			for (HashMap<String, Object> body : elements) {

				row = sheet.createRow(r);

				int bc = 0;

				for (Map.Entry<String, Object> entry : body.entrySet()) {

					Cell cell = row.createCell(bc);
					cell.setCellValue(entry.getValue().toString());

					bc++;
				}

				r++;
			}

			for (int i = 0; i < header.size(); i++)
				sheet.autoSizeColumn(i);

		}

		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		workbook.write(baos);

		stream = baos.toByteArray();

		return stream;
	}

	public byte[] transformMapToExcel2(List<List<String>> datos, List<String> encabezados) throws ExcelMapperException, IOException {

		byte[] stream = null;

		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet sheet = workbook.createSheet();
		    
		if (datos != null && !datos.isEmpty()) 
		{
			Row rowE =sheet.createRow(0);
			for(int j=0; j<encabezados.size(); j++)												
				rowE.createCell(j).setCellValue(encabezados.get(j));								
			
			for(int i=0; i<datos.size(); i++)
			{				
				rowE = sheet.createRow(i+1);
				for(int j=0; j<datos.get(i).size(); j++)
				{									
					rowE.createCell(j).setCellValue(datos.get(i).get(j));					
				}	
			}			
		}

		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		workbook.write(baos);

		stream = baos.toByteArray();

		return stream;
	}

	public boolean validateHeader(File stream, String idParse) throws ExcelMapperException{
		boolean respuesta = true;
		
		try {
	
			ExcelMapper mapper = excelMapperContext.getExcelMapper(idParse);
			List<ExcelRule> rules = mapper.getRules();
				
			Workbook wb = WorkbookFactory.create(new FileInputStream(stream));
			Sheet sh = wb.getSheetAt(0);
			Row row = sh.getRow(0);
			
			logger.debug("No. Cells : [{}] No. Rules : [{}]", row.getLastCellNum(), rules.size());
			
			if(row.getLastCellNum() == rules.size()) {
				
				for(int i = 0; i < row.getLastCellNum(); i++){
					Cell cell = row.getCell(i);
					String value = cell.getStringCellValue();
					String header = rules.get(i).getHeader();
					//logger.debug("rule : [{}], cell : [{}] repuesta : " + value.equals(header) ,header , value);
					if(!value.equals(header))
						respuesta = false;
						
				}
				
			}else {
				respuesta = false;
			}
			
		} catch (InvalidFormatException e) {	
			logger.error("Error [{}]", e);
			throw new ExcelMapperException(ExcelMapperException.ERROR_READING_EXCEL_FILE);
		} catch (IOException e) {
			logger.error("Error [{}]", e);
			throw new ExcelMapperException(ExcelMapperException.ERROR_READING_EXCEL_FILE);
		}

		return respuesta;
	}
	
	public <T> byte[] transformObjectToExcel(T[][] elements, ExcelMapper mapper, Class<T> clase) throws ExcelMapperException, IOException {
		logger.info("mapper class [{}]  mapper id[{}]", mapper.getClassName(), mapper.getId());
		// if(!mapper.getClassName().equals(clase.getName()))
		// {
		// throw new ExcelMapperException(ExcelMapperException.ERROR_MAPPER_CLASS, mapper.getClassName(),clase.getName()
		// );
		// }

		int size = elements[0].length / 3 < 1 ? elements[0].length : elements[0].length / 3;
		SXSSFWorkbook workbook = new SXSSFWorkbook(size);
		Sheet sheet = workbook.createSheet();

		// Blank workbook
		// XSSFWorkbook workbook = new XSSFWorkbook();
		// Create a blank sheet
		// XSSFSheet sheet = workbook.createSheet();

		int r = 0;
		int c = 0;

		LinkedList<ExcelRule> auxiliar = new LinkedList<ExcelRule>(mapper.getRules());
		ExcelRule first = auxiliar.getFirst();

		for (int i = 0; i < first.getHeaders().size(); i++) {
			Row row = sheet.createRow(r++);
			for (ExcelRule exRule : mapper.getRules()) {

				Cell cell = row.createCell(CellReference.convertColStringToIndex(exRule.getColumn()));
				cell.setCellValue(exRule.getHeaders().get(i));

			}
		}
		for (T[] element : elements) {
			Row rowE = sheet.createRow(r++);
			for (ExcelRule exRule : mapper.getRules()) {
				try {

					ExcelMapperDataType type = exRule.getType() == null ? ExcelMapperDataType.string : exRule.getType();

					Cell cell = rowE.createCell(CellReference.convertColStringToIndex(exRule.getColumn()));
					// cell.setCellValue((String) element[c++] );

					Object temp = element[c++];

					if (temp == null)
						cell.setCellValue("");
					else {
						switch (type) {
							case date:
								try {
									if (temp != null)
										cell.setCellValue((Date) temp);
								}
								catch (Exception e) {
									cell.setCellValue(temp.toString());
								}
								break;
							case numeric:
								try {
									if (temp != null) {
										double d = Double.parseDouble(temp.toString());
										cell.setCellValue(d);
									}
								}
								catch (Exception e) {
									cell.setCellValue(temp.toString());
								}
								break;
							case bool:
								try {
									if (temp != null)
										cell.setCellValue((boolean) temp);
								}
								catch (Exception e) {
									cell.setCellValue(temp.toString());
								}
								break;
							case string:
							default:
								cell.setCellValue(temp.toString());
								break;
						}
					}

					cell = asignaStyleCell(exRule, cell, workbook);
				}
				catch (Exception e) {
					logger.error("Error", e);
				}
			}

			c = 0;
		}

		sheet.autoSizeColumn(100);
		// for(ExcelRule exRule: mapper.getRules())
		// {
		// CellReference ref = new CellReference(exRule.getColumn());
		// }

		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		workbook.write(baos);
		return baos.toByteArray();
	}
}
