package pwc.taxtech.atms.common;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.Optional;

public class POIUtil {
    private static final Logger logger = LoggerFactory.getLogger(POIUtil.class);
    public static final String XLSX = ".xlsx";
    public static final String XLS = ".xls";

    public static Optional<Workbook> cloneNewSheet(Sheet sheet, String fileName) {
        Workbook workbook;
        try {
            if (StringUtils.endsWith(fileName, XLSX)) {
                workbook = new XSSFWorkbook();
            } else if (StringUtils.endsWith(fileName, XLS)) {
                workbook = new HSSFWorkbook();
            } else {
                return Optional.empty();
            }
            Sheet tmpSheet = workbook.createSheet(sheet.getSheetName());
            cloneSheet(sheet, tmpSheet);
            return Optional.of(workbook);
        } catch (Exception e) {
            logger.error("cloneNewSheet error.", e);
        }
        return Optional.empty();
    }

    public static void cloneSheet(Sheet sheet, Sheet targetSheet) {
        for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            Row targetRow = targetSheet.createRow(r);
            for (int c = row.getFirstCellNum(); c <= row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                if (null == cell) {
                    continue;
                }
                Cell targetCell = targetRow.createCell(c);
                targetCell.setCellType(cell.getCellTypeEnum());
                switch (cell.getCellTypeEnum()) {
                    case STRING:
                        targetCell.setCellValue(cell.getStringCellValue());
                        break;
                    case NUMERIC:
                        targetCell.setCellValue(cell.getNumericCellValue());
                        break;
                    case BOOLEAN:
                        targetCell.setCellValue(cell.getBooleanCellValue());
                        break;
                    case FORMULA:
                        targetCell.setCellFormula(cell.getCellFormula());
                        break;
//                    case ERROR:
////                    case BLANK:
////                    case _NONE:
                    default:
                        break;
                }
                if (null != cell.getCellComment()) {
                    targetCell.setCellComment(cell.getCellComment());
                }
                if (null != cell.getCellStyle()) {
                    targetCell.getCellStyle().cloneStyleFrom(cell.getCellStyle());
                }
                if (null != cell.getHyperlink()) {
                    targetCell.setHyperlink(cell.getHyperlink());
                }
            }
        }
    }

    public static String getCellFormulaString(Cell cell) {
        switch (cell.getCellTypeEnum()) {
            case STRING:
                return cell.getStringCellValue();
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return StringUtils.EMPTY;
        }
    }

    public static Optional<String> getFileSuffix(String fileName) {
        if (StringUtils.endsWith(fileName, XLSX)) {
            return Optional.of(XLSX);
        } else if (StringUtils.endsWith(fileName, XLS)) {
            return Optional.of(XLS);
        } else {
            return Optional.empty();
        }
    }
}