package com.samsung.embo.formula.controller;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.Resource;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.samsung.authentication.Account;
import com.samsung.embo.formula.FormulaService;
import com.samsung.embo.formula.entity.Formula;
import com.samsung.resource.FileManagerService;
import com.samsung.resource.entity.AttachFile;
import com.samsung.user.entity.User;

import io.swagger.v3.oas.annotations.Operation;
import lombok.extern.log4j.Log4j2;

@RestController
@RequestMapping("/embo/formula/")
@Log4j2
public class FormulaController {

	private final FormulaService formulaService;
	private final FileManagerService fileManagerService;

	public FormulaController(FormulaService formulaService, FileManagerService fileManagerService) {

		this.formulaService = formulaService;
		this.fileManagerService = fileManagerService;
	}

	@Operation(summary = "page 초기정보.")
	@PostMapping(value = "list/main.do", params = "method=page")
	public Map<String, Object> page(@RequestBody Map<String, Object> params) throws Exception {

		User user = Account.currentUser();

		Map<String, Object> result = new HashMap<String, Object>();
		result.put("user", user);
		result.put("item1", "메일 템플릿 항목1");
		return result;
	}

	@Operation(summary = "formula 조회")
	@PostMapping(value = "list/main.do", params = "method=search")
	public Map<String, Object> list_search(@RequestBody Map<String, Object> params) throws Exception {

		User user = Account.currentUser();

		Map<String, Object> result = new HashMap<String, Object>();
		result.put("user", user);
		result.put("result", formulaService.selectFormula(params));
		return result;
	}

	@Operation(summary = "Formula 상세조회")
	@PostMapping(value = "item/main.do", params = "method=getFormula")
	public Map<String, Object> item_getFormula(@RequestBody Map<String, Object> params) throws Exception {
		Map<String, Object> result = new HashMap<String, Object>();

		result.put("result", formulaService.getFormula(params));

		List<AttachFile> attachFiles = fileManagerService.getAttachFileList(params.get("fmId").toString(), "FORMULA");

		result.put("attachments", attachFiles);

		if (attachFiles.size() > 0) {
			AttachFile attachFile = attachFiles.get(0);
			Resource resource = fileManagerService.getResource(attachFile.getFileExtensionName(),
					attachFile.getFilePathName());

			OPCPackage pkg = OPCPackage.open(resource.getInputStream());
			XSSFWorkbook wb = new XSSFWorkbook(pkg);
			XSSFSheet sheet = wb.getSheetAt(0);

			DataFormatter formatter = new DataFormatter();
			String measurePos = "A22";
			String[] measureIds = { "MEASURE", "SCOPE1", "SCOPE2", "SCOPE3", "TARGET1", "TARGET2", "TARGET3", "VALUE" };
			int measureSize = 10;

			CellReference cellRefMeasure = new CellReference(measurePos);

			List<Map<String, String>> measureList = IntStream.range(0, measureSize).boxed().map(rowIdx -> IntStream
					.range(0, measureIds.length).boxed()
					.collect(Collectors.toMap(colIdx -> measureIds[colIdx], colIdx -> formatter
							.formatCellValue(sheet.getRow(cellRefMeasure.getRow() + rowIdx).getCell(1 + colIdx))))

			).collect(Collectors.toList());

			result.put("measureInfo", measureList);

			String mboPos = "A7";
			String[] mboIds = { "MBO_ITEM", "TYPE", "TARGET", "FORMULA", "GRADE" };
			int mboSize = 10;
			CellReference cellRefMbo = new CellReference(mboPos);

			List<Map<String, String>> mboList = IntStream.range(0, mboSize).boxed()
					.map(rowIdx -> IntStream.range(0, mboIds.length).boxed()
							.collect(Collectors.toMap(colIdx -> mboIds[colIdx], colIdx -> formatter
									.formatCellValue(sheet.getRow(cellRefMbo.getRow() + rowIdx).getCell(1 + colIdx))))

					).collect(Collectors.toList());

			result.put("mboInfo", mboList);

			wb.close();
			pkg.close();
		}

		return result;
	}

	@Operation(summary = "formula 등록")
	@PostMapping(value = "item/main.do", params = "method=regist")
	public String item_regist(@RequestBody Formula formula) throws Exception {
		User user = Account.currentUser();
		formula.setUserId(user.getUserId());
		return formulaService.insertFormula(formula);
	}

	@Operation(summary = "formula 수정")
	@PostMapping(value = "item/main.do", params = "method=update")
	public String item_update(@RequestBody Formula formula) throws Exception {
		User user = Account.currentUser();
		formula.setUserId(user.getUserId());
		return formulaService.updateFormula(formula);
	}

	@Operation(summary = "formula 삭제")
	@PostMapping(value = "item/main.do", params = "method=delete")
	public String item_delete(@RequestBody Map<String, Object> params) throws Exception {
		User user = Account.currentUser();
		// todo : admin & execute 권한 check
		params.put("userId", user.getUserId());
		int ret = formulaService.deleteFormula(params);
		return "success";
	}

	@SuppressWarnings("unchecked")
	@Operation(summary = "formula calc")
	@PostMapping(value = "item/main.do", params = "method=calc")
	public Map<String, Object> item_calc(@RequestBody Map<String, Object> params) throws Exception {
		User user = Account.currentUser();
		params.put("userId", user.getUserId());

		Map<String, Object> result = new HashMap<String, Object>();

		List<Map<String, String>> measureList = (List<Map<String, String>>) params.get("rows");

		log.info(measureList);
		List<AttachFile> attachFiles = fileManagerService.getAttachFileList(params.get("fmId").toString(), "FORMULA");
		if (attachFiles.size() > 0) {
			AttachFile attachFile = attachFiles.get(0);
			Resource resource = fileManagerService.getResource(attachFile.getFileExtensionName(),
					attachFile.getFilePathName());

			OPCPackage pkg = OPCPackage.open(resource.getInputStream());
			XSSFWorkbook wb = new XSSFWorkbook(pkg);
			XSSFSheet sheet = wb.getSheetAt(0);
			String valuePos = "I22";
			int measureSize = 10;

			// set measure value
			CellReference cellReference = new CellReference(valuePos);
			IntStream.range(0, measureSize).boxed().forEach(rowIdx -> {
				XSSFRow rowSrc = sheet.getRow(cellReference.getRow() + rowIdx);
				XSSFCell cellSrc = rowSrc.getCell(cellReference.getCol());
				if ("".equals(measureList.get(rowIdx).get("VALUE"))) {
					cellSrc.setCellValue("");
				} else {
					cellSrc.setCellValue(Double.parseDouble(measureList.get(rowIdx).get("VALUE")));
				}

			});

			wb.getCreationHelper().createFormulaEvaluator().evaluateAll();

			String mboPos = "A7";
			String[] mboIds = { "MBO_ITEM", "TYPE", "TARGET", "FORMULA", "GRADE" };
			int mboSize = 10;
			CellReference cellRefMbo = new CellReference(mboPos);
			DataFormatter formatter = new DataFormatter();
			List<Map<String, String>> mboList = IntStream.range(0, mboSize).boxed().map(rowIdx ->

			IntStream.range(0, mboIds.length).boxed().collect(Collectors.toMap(colIdx -> mboIds[colIdx], colIdx -> {
				String cellValue = "";
				XSSFCell cell = sheet.getRow(cellRefMbo.getRow() + rowIdx).getCell(1 + colIdx);
				if (cell.getCellType() == CellType.NUMERIC) {
					cellValue = String.valueOf(cell.getNumericCellValue());
				} else if (cell.getCellType() == CellType.FORMULA) {
					switch (cell.getCachedFormulaResultType()) {
					case NUMERIC:
						cellValue = String.valueOf(cell.getNumericCellValue());
						break;
					case STRING:
						cellValue = cell.getStringCellValue();
						break;
					default:
						break;
					}
				} else {
					cellValue = cell.getStringCellValue();
				}
				return cellValue;
			}))

			).collect(Collectors.toList());

			result.put("mboInfo", mboList);

			wb.close();
			pkg.close();

		}

		return result;
	}

	@Operation(summary = "formula merge")
	@PostMapping(value = "list/main.do", params = "method=merge")
	public Map<String, Object> list_merge(@RequestBody Map<String, Object> params) throws Exception {

		// https://gist.github.com/davidsommer/7805574
		User user = Account.currentUser();

		Map<String, Object> result = new HashMap<String, Object>();
		result.put("user", user);
		result.put("result", formulaService.selectFormula(params));

		List<FileInputStream> filePaths = new ArrayList<FileInputStream>();

		List<Map<String, Object>> fmList = formulaService.selectFormula(params);

		for (Map<String, Object> fmInfo : fmList) {

			List<AttachFile> attachFiles = fileManagerService.getAttachFileList(fmInfo.get("fmId").toString(),
					"FORMULA");

			if (attachFiles.size() > 0) {
				AttachFile attachFile = attachFiles.get(0);
				Resource resource = fileManagerService.getResource(attachFile.getFileExtensionName(),
						attachFile.getFilePathName());
				filePaths.add(new FileInputStream(resource.getFile()));
			} else {
				log.info("error");
			}
		}

		this.mergeExcelFiles(new File("D:\\temp\\all.xlsx"), filePaths);

		System.out.println("Done!");
		return result;
	}

	public void mergeExcelFiles(File file, List<FileInputStream> list) throws IOException {
		XSSFWorkbook book = new XSSFWorkbook();
		XSSFSheet sheet = book.createSheet(file.getName());

		for (FileInputStream fin : list) {
			XSSFWorkbook b = new XSSFWorkbook(fin);
			copySheets(book, sheet, b.getSheetAt(0));
			// for (int i = 0; i < b.getNumberOfSheets(); i++) {copySheets(book, sheet,
			// b.getSheetAt(i));}
		}

		try {
			writeFile(book, file);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	protected static void writeFile(XSSFWorkbook book, File file) throws Exception {
		FileOutputStream out = new FileOutputStream(file);
		book.write(out);
		out.close();
	}

	private static void copySheets(XSSFWorkbook newWorkbook, XSSFSheet newSheet, XSSFSheet sheet) {
		copySheets(newWorkbook, newSheet, sheet, true);
	}

	private static void copySheets(XSSFWorkbook newWorkbook, XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle) {
		int newRownumber = (int) (Math.floor((newSheet.getLastRowNum() + 100) / 100) * 100);
		int maxColumnNum = 0;
		Map<Integer, XSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, XSSFCellStyle>() : null;

		for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
			XSSFRow srcRow = sheet.getRow(i);
			XSSFRow destRow = newSheet.createRow(i + newRownumber);
			if (srcRow != null) {
				copyRow(newWorkbook, sheet, newSheet, srcRow, destRow, styleMap);
				if (srcRow.getLastCellNum() > maxColumnNum) {
					maxColumnNum = srcRow.getLastCellNum();
				}
			}
		}
		for (int i = 0; i <= maxColumnNum; i++) {
			newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
		}
	}

	public static void copyRow(XSSFWorkbook newWorkbook, XSSFSheet srcSheet, XSSFSheet destSheet, XSSFRow srcRow,
			XSSFRow destRow, Map<Integer, XSSFCellStyle> styleMap) {
		destRow.setHeight(srcRow.getHeight());
		for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
			XSSFCell oldCell = srcRow.getCell(j);
			XSSFCell newCell = destRow.getCell(j);
			if (oldCell != null) {
				if (newCell == null) {
					newCell = destRow.createCell(j);
				}
				copyCell(newWorkbook, oldCell, newCell, styleMap);
			}
		}
	}

	public static void copyCell(XSSFWorkbook newWorkbook, XSSFCell oldCell, XSSFCell newCell,
			Map<Integer, XSSFCellStyle> styleMap) {
		if (styleMap != null) {
			int stHashCode = oldCell.getCellStyle().hashCode();
			XSSFCellStyle newCellStyle = styleMap.get(stHashCode);
			if (newCellStyle == null) {
				newCellStyle = newWorkbook.createCellStyle();
				newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
				styleMap.put(stHashCode, newCellStyle);
			}
			newCell.setCellStyle(newCellStyle);
		}
		switch (oldCell.getCellType()) {
		case STRING:
			newCell.setCellValue(oldCell.getRichStringCellValue());
			break;
		case NUMERIC:
			newCell.setCellValue(oldCell.getNumericCellValue());
			break;
		case BLANK:
			newCell.setCellType(CellType.BLANK);
			break;
		case BOOLEAN:
			newCell.setCellValue(oldCell.getBooleanCellValue());
			break;
		case ERROR:
			newCell.setCellErrorValue(oldCell.getErrorCellValue());
			break;
		case FORMULA:
			newCell.setCellFormula(oldCell.getCellFormula());
			break;
		default:
			break;
		}
	}
}
