package com.tievd.jyz.plugin;

import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.tievd.cube.commons.annotations.Dict;
import com.tievd.cube.commons.easyexcel.EasyExcel;
import com.tievd.cube.commons.easyexcel.annotations.Excel;
import com.tievd.cube.commons.easyexcel.dict.IDictTranslator;
import com.tievd.cube.commons.easyexcel.model.ImportExcel;
import lombok.SneakyThrows;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFCreationHelper;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbookFactory;
import org.springframework.stereotype.Component;

import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.*;
import java.util.stream.Collectors;

/**
 * Author: wqy
 * Date: 2022/10/14 11:13
 */
@Component
public class ReformEasyExcel implements EasyExcel {
    
    @Override
    @SneakyThrows
    public <T> List<T> read(Class<T> cls, ImportExcel importExcel, IDictTranslator dictTranslator) {
        List<T> data = new ArrayList<>();
        Workbook workbook = WorkbookFactory.create(importExcel.getInputStream());
        Sheet sheet = workbook.getSheetAt(importExcel.getSheetIndex());
        int lastRowNum = sheet.getLastRowNum() + 1;
        // 得到标题行
        Row titleCellRangeRow = sheet.getRow(importExcel.getStartRow() - 1);
        // 得到标题索引
        Map<String, Integer> titleIndex = new HashMap<>();
        for (int i = 0; i < titleCellRangeRow.getLastCellNum(); i++) {
            titleIndex.put(titleCellRangeRow.getCell(i).getStringCellValue(), i);
        }
        Field[] fields = cls.getDeclaredFields();
        // 排除static字段
        List<Field> unStaticFieldList = ListUtil.toList(fields).stream()
                .filter(field -> !Modifier.isStatic(field.getModifiers()) && field.getAnnotation(Excel.class)!=null)
                .collect(Collectors.toList());
        // 遍历数据
        for (int i = importExcel.getStartRow(); i < lastRowNum; i++) {
            Row row = sheet.getRow(i);
            T obj = cls.newInstance();
            for (Field field : unStaticFieldList) {
                String name = field.getName();
                Excel excel = field.getAnnotation(Excel.class);
                name = excel.value();
                Integer idx = titleIndex.get(name);
                if (idx == null) {
                    continue;
                }
                String strValue;
                Cell cell = row.getCell(idx);
                CellType cellType = cell.getCellType();
                // 单元格未填写的情况下是null
                if (cellType == null) {
                    continue;
                }
                if (CellType.NUMERIC.equals(cellType)) {
                    strValue = NumberToTextConverter.toText(cell.getNumericCellValue());
                } else {
                    strValue = row.getCell(idx).getStringCellValue();
                }
                if (StrUtil.isBlank(strValue)) {
                    continue;
                }
                String fieldTypeName = field.getType().getSimpleName().toLowerCase();
                // 字典转换
                Dict dict = field.getAnnotation(Dict.class);
                if (dictTranslator != null && dict != null) {
                    strValue = dictTranslator.valueToId(strValue, dict);
                }
                Object value;
                if (fieldTypeName.contains("int")) {
                    value = Integer.valueOf(strValue);
                } else if (fieldTypeName.contains("byte")) {
                    value = Byte.valueOf(strValue);
                } else if (fieldTypeName.contains("long")) {
                    value = Long.valueOf(strValue);
                } else if (fieldTypeName.contains("bool")) {
                    value = Boolean.valueOf(strValue);
                } else if (fieldTypeName.contains("date")) {
                    DateTime dateTime = DateUtil.parse(strValue);
                    value = dateTime.toJdkDate();
                } else if (fieldTypeName.contains("double")) {
                    value = Double.valueOf(strValue);
                } else if (field.getType().isEnum()) {
                    Class fieldClass = field.getType();
                    value = Enum.valueOf(fieldClass, strValue);
                } else {
                    value = strValue;
                }
                field.setAccessible(true);
                field.set(obj, value);
            }
            data.add(obj);
        }
        return data;
    }
    
    @Override
    @SneakyThrows
    public String readToJson(Class<?> cls, ImportExcel excel, IDictTranslator dictHandler) {
        List<?> list = read(cls, excel, dictHandler);
        return JSONUtil.toJsonStr(list);
    }
    
    @Override
    @SneakyThrows
    public String readToJson(ImportExcel excel) {
        JSONArray data = JSONUtil.createArray();
        Workbook workbook = WorkbookFactory.create(excel.getInputStream());
        Sheet sheet = workbook.getSheetAt(excel.getSheetIndex());
        // 得到所有行
        int lastRowNum = sheet.getLastRowNum();
        // 得到标题行
        Row titleCellRangeRow = sheet.getRow(excel.getStartRow() - 1);
        List<String> titles = new ArrayList<>();
        for (int i = 0; i < titleCellRangeRow.getLastCellNum(); i++) {
            titles.add(titleCellRangeRow.getCell(i).getStringCellValue());
        }
        // 遍历数据
        for (int i = excel.getStartRow(); i < lastRowNum; i++) {
            JSONObject obj = JSONUtil.createObj();
            Row row = sheet.getRow(i);
            int lastCellNum = row.getLastCellNum();
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);
                CellType cellType = cell.getCellType();
                if (CellType.NUMERIC.equals(cellType)) {
                    obj.set(titles.get(j), row.getCell(j).getNumericCellValue());
                } else {
                    obj.set(titles.get(j), row.getCell(j).getStringCellValue());
                }
            }
            data.add(obj);
        }
        return data.toString();
    }
    
    @Override
    @SneakyThrows
    public void export(List<?> list, OutputStream outputStream, IDictTranslator dictTranslator) {
        int dataSize = list.size();
        if (dataSize > 0) {
            Field[] fields = list.get(0).getClass().getDeclaredFields();
            try (XSSFWorkbook workbook = XSSFWorkbookFactory.createWorkbook()) {
                XSSFCreationHelper creationHelper = new XSSFCreationHelper(workbook);
                Sheet sheet = workbook.createSheet("Data");
                Font font = workbook.createFont();
                font.setBold(true);
                font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
                font.setFontHeightInPoints((short) 14);
                CellStyle cellStyle = workbook.createCellStyle();
                cellStyle.setFont(font);
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                cellStyle.setAlignment(HorizontalAlignment.CENTER);
                // 排除static字段
                List<Field> unStaticFieldList = ListUtil.toList(fields).stream()
                        .filter(field -> !Modifier.isStatic(field.getModifiers()) && field.getAnnotation(Excel.class)!=null)
                        .collect(Collectors.toList());
                for (int i = 0; i < unStaticFieldList.size(); i++) {
                    Field field = unStaticFieldList.get(i);
                    String name = field.getName();
                    Excel excel = field.getAnnotation(Excel.class);
                    name = excel.value();
                    // 写Header
                    Row header = sheet.getRow(0);
                    if (header == null) {
                        header = sheet.createRow(0);
                    }
                    Cell headerCell = header.getCell(i);
                    if (headerCell == null) {
                        headerCell = header.createCell(i);
                    }
                    headerCell.setCellStyle(cellStyle);
                    headerCell.setCellValue(name);
                    // 字典转换
                    Dict dict = field.getAnnotation(Dict.class);
                    // 按列写数据
                    for (int j = 0; j < dataSize; j++) {
                        Row row = sheet.getRow(1 + j);
                        if (row == null) {
                            row = sheet.createRow(1 + j);
                        }
                        Cell cell = row.getCell(i);
                        if (cell == null) {
                            cell = row.createCell(i);
                        }
                        field.setAccessible(true);
                        String cellData = " ";
                        Object value = field.get(list.get(j));
                        if (ObjectUtil.isNotEmpty(value)) {
                            if (value instanceof Date) {
                                cellData = DateUtil.format((Date) value, "yyyy-MM-dd HH:mm:ss");
                            } else if (value instanceof List) {
                                cellData = JSONUtil.toJsonStr(value);
                            } else {
                                cellData = String.valueOf(value);
                            }
                            if (dictTranslator != null && dict != null && StrUtil.isNotEmpty(cellData)) {
                                cellData = dictTranslator.idToValue(cellData, dict);
                            }
                        }
                        cell.setCellValue(cellData);
                        //增加超连接
                        if (field.getAnnotation(ExcelHyperLink.class) != null && StrUtil.isNotBlank(cellData)) {
                            Hyperlink link = creationHelper.createHyperlink(HyperlinkType.FILE);
                            link.setAddress(cellData);
                            cell.setHyperlink(link);
                        }
                    }
                    sheet.autoSizeColumn(i);
                    //自动宽度依然会遮住内容，宽度放大到1.5倍
                    int width = sheet.getColumnWidth(i) * 15 / 10;
                    sheet.setColumnWidth(i, width);
                }
                // 写到输出流
                workbook.write(outputStream);
            }
        }
    }
    
    @Override
    public boolean supportXlsx() {
        return true;
    }
    
    @Override
    public String getExtension() {
        return ".xlsx";
    }
}