package com.doumee.core.annotation.excel;

import com.doumee.core.constants.ResponseStatus;
import com.doumee.core.exception.BusinessException;
import com.doumee.core.utils.Constants;
import lombok.AllArgsConstructor;
import lombok.Data;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.core.annotation.AnnotationConfigurationException;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.nio.charset.Charset;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Excel导出实现
 * @author Eva.Caesar Liu
 * @date 2023/02/14 11:14
 */
@Data
public class ExcelExporter<T> {

    private static final String DEFAULT_SHEET_NAME = "Sheet1";

    private Class<T> modelClass;
    private static int maxrows = 50000;

    private ExcelExporter(){}

    /**
     * 构造器
     * @param modelClass 实体Class对象
     */
    public static <T> ExcelExporter<T> build(Class<T> modelClass) {
        ExcelExporter<T> excelExporter = new ExcelExporter<>();
        excelExporter.setModelClass(modelClass);
        return excelExporter;
    }
    /**
     * 导出到指定输出流
     * @param data 数据
     * @param sheetName Sheet名称
     */
    public void exportWithFirstAndEnd (List<T> data, String sheetName,String first,String end, OutputStream os) {
        SXSSFWorkbook sxssfWorkbook;
        try {
            sxssfWorkbook = new SXSSFWorkbook();
            Sheet sheet = sxssfWorkbook.createSheet(sheetName);
            // 创建列头
            sheet.createFreezePane(0, 2);
            sheet.addMergedRegion(new CellRangeAddress(0   ,0,0,this.getColumns().size()-1));
            Row title = sheet.createRow(0);
            title.setHeight((short) 1000);
            Cell c = title.createCell(0);
            c.setCellValue(first);
            configFirstCell(sxssfWorkbook,c);

            Row header = sheet.createRow(1);
            List<ColumnInfo> columns = this.getColumns();
            for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
                ColumnInfo column = columns.get(columnIndex);
                Cell cell = header.createCell(columnIndex);
                cell.setCellValue(column.columnConfig.name());
                // 列宽设置
                if (column.columnConfig.width() == -1) {
                    sheet.setColumnWidth(columnIndex, column.columnConfig.name().length() * 2 * 256);
                } else {
                    sheet.setColumnWidth(columnIndex, column.columnConfig.width() * 2 * 256);
                }
                // 设置列头单元格
                configHeaderCell(sxssfWorkbook, cell, column.columnConfig);
            }
            List<CellStyle> styleList = new ArrayList<>();
            for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
                ColumnInfo column = columns.get(columnIndex);
                styleList.add( configDataCell(sxssfWorkbook,   column.columnConfig));
            }
            // 创建数据记录
            for (int rowIndex = 0; rowIndex < data.size(); rowIndex++) {
                Row row = sheet.createRow(rowIndex + 2);
                for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
                    ColumnInfo column = columns.get(columnIndex);
                    Cell cell = row.createCell(columnIndex);
                    cell.setCellValue(getCellData(column, data.get(rowIndex)));
                    cell.setCellStyle(styleList.get(columnIndex));
                }
            }
            sheet.addMergedRegion(new CellRangeAddress(data.size()+2   ,data.size()+2,0,this.getColumns().size()-1));
            Row endRow = sheet.createRow(data.size()+2);
//            endRow.setHeight((short) 600);
            Cell c1 = endRow.createCell(0);
            c1.setCellValue(end);
            c1.setCellStyle(  configEndCell(sxssfWorkbook));
            sxssfWorkbook.write(os);
            os.close();
        } catch (Exception e) {
            throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
        } finally {
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 导出到指定输出流
     * @param data 数据
     * @param sheetName Sheet名称
     * @param os 输出流
     */
    public void export (List<T> data, String sheetName, OutputStream os) {
        SXSSFWorkbook sxssfWorkbook;
        try {
            sxssfWorkbook = new SXSSFWorkbook();
            int totalSheet =1;
            if( data!=null && data.size()>0){
                totalSheet = data.size()/maxrows;
                if(data.size()%maxrows !=0){
                    totalSheet += 1;
                }
            }
            for (int i = 0; i < totalSheet; i++) {
                List<T> list = null;
                if(data.size() < maxrows * (i+1)) {
                    list = data.subList(maxrows*i,data.size());
                }else{
                    list = data.subList(maxrows*i,maxrows*(i+1));
                }
                createSheetDataBiz(list,"【"+(i+1)+"】"+sheetName,sxssfWorkbook);
            }
            sxssfWorkbook.write(os);
            os.close();
        } catch (Exception e) {
            throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
        } finally {
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private void createSheetDataBiz(List<T> data, String sheetName, SXSSFWorkbook sxssfWorkbook) throws Exception{
        Sheet sheet = sxssfWorkbook.createSheet(sheetName);
        // 创建列头
        sheet.createFreezePane(0, 1);
        Row header = sheet.createRow(0);
        List<ColumnInfo> columns = this.getColumns();
        for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
            ColumnInfo column = columns.get(columnIndex);
            Cell cell = header.createCell(columnIndex);
            cell.setCellValue(column.columnConfig.name());
            // 列宽设置
            if (column.columnConfig.width() == -1) {
                sheet.setColumnWidth(columnIndex, column.columnConfig.name().length() * 2 * 256);
            } else {
                sheet.setColumnWidth(columnIndex, column.columnConfig.width() * 2 * 256);
            }
            // 设置列头单元格
            configHeaderCell(sxssfWorkbook, cell, column.columnConfig);
        }
        List<CellStyle> styleList = new ArrayList<>();
        for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
            ColumnInfo column = columns.get(columnIndex);
            styleList.add( configDataCell(sxssfWorkbook,   column.columnConfig));
        }
        // 创建数据记录
        for (int rowIndex = 0; rowIndex < data.size(); rowIndex++) {
            Row row = sheet.createRow(rowIndex + 1);
            for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
                ColumnInfo column = columns.get(columnIndex);
                Cell cell = row.createCell(columnIndex);
                cell.setCellValue(getCellData(column, data.get(rowIndex)));
                // 设置数据单元格样式
                cell.setCellStyle(styleList.get(columnIndex));
            }
        }
    }

    /**
     * 导出至响应流
     * @param data 数据
     * @param fileName Excel文件名
     * @param sheetName Sheet名称
     * @param response HttpServletResponse对象
     */
    public void export (List<T> data, String fileName, String sheetName, HttpServletResponse response) {
        try {
            String encodeFileName = URLEncoder.encode(fileName, Charset.forName("UTF-8").toString()) + ".xlsx";
            response.setHeader("Content-Disposition","attachment;filename=" + encodeFileName);
            response.setContentType("application/octet-stream");
            response.setHeader("eva-opera-type", "download");
            response.setHeader("eva-download-filename", encodeFileName);
            this.export(data, sheetName, response.getOutputStream());
        } catch (IOException e) {
            throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
        }
    }
    /**
     * 导出至响应流
     * @param data 数据
     * @param fileName Excel文件名
     * @param sheetName Sheet名称
     * @param response HttpServletResponse对象
     */
    public void exportWithFirstAndEnd (List<T> data, String fileName, String sheetName, String first,String end ,HttpServletResponse response) {
        try {
            String encodeFileName = URLEncoder.encode(fileName, Charset.forName("UTF-8").toString()) + ".xlsx";
            response.setHeader("Content-Disposition","attachment;filename=" + encodeFileName);
            response.setContentType("application/octet-stream");
            response.setHeader("eva-opera-type", "download");
            response.setHeader("eva-download-filename", encodeFileName);
            this.exportWithFirstAndEnd(data, sheetName,first,end, response.getOutputStream());
        } catch (IOException e) {
            throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
        }
    }

    /**
     * 导出至响应流
     * @param data 数据
     * @param fileName Excel文件名
     * @param response HttpServletResponse对象
     */
    public void export (List<T> data, String fileName, HttpServletResponse response) {

        this.export(data, fileName, DEFAULT_SHEET_NAME, response);
    }
    /**
     * 导出至响应流
     * @param data 数据
     * @param fileName Excel文件名
     * @param response HttpServletResponse对象
     */
    public void exportWithFirstAndEnd (List<T> data, String fileName,String first,String end, HttpServletResponse response) {
        this.exportWithFirstAndEnd(data, fileName, DEFAULT_SHEET_NAME, first,end,response);
    }

    /**O
     * 获取列集合
     */
    private List<ColumnInfo> getColumns () {
        Map<Integer, ColumnInfo> sortedFields = new TreeMap<>();
        Field[] fields = modelClass.getDeclaredFields();
        int index = 0;
        for (Field field : fields) {
            ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
            if (excelColumn == null) {
                continue;
            }
            if (sortedFields.get(excelColumn.index()) != null) {
                throw new AnnotationConfigurationException("Excel column contains the same index.");
            }
            sortedFields.put(excelColumn.index() == -1 ? index : excelColumn.index(), new ColumnInfo(excelColumn, field));
            index++;
        }
        return new ArrayList<>(sortedFields.values());
    }

    /**
     * 配置数据单元格
     */
    private CellStyle configDataCell (SXSSFWorkbook workbook, ExcelColumn columnConfig) {
            CellStyle  configDataCellStyle = workbook.createCellStyle();
            configDataCellStyle.setAlignment(columnConfig.align());
            configDataCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 设置背景
            configDataCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            configDataCellStyle.setFillForegroundColor(columnConfig.dataBackgroundColor().getIndex());
            // 字体
            Font font = workbook.createFont();
            font.setFontHeightInPoints(columnConfig.fontSize());
            // 字体颜色
            font.setColor(columnConfig.color().getIndex());
            // 粗体
            font.setBold(columnConfig.bold());
            // 斜体
            font.setItalic(columnConfig.italic());
            configDataCellStyle.setFont(font);
            // 边框
            configCellBorder(configDataCellStyle);
            configDataCellStyle.setWrapText(true);

        return configDataCellStyle;
//        cell.setCellStyle(configDataCellStyle);
    }

    /**
     * 配置列头单元格
     */
    private void configHeaderCell (SXSSFWorkbook workbook, Cell cell, ExcelColumn columnConfig) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(columnConfig.align());
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置背景
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFillForegroundColor(columnConfig.backgroundColor().getIndex());
        // 字体
        Font font = workbook.createFont();
        font.setFontHeightInPoints(columnConfig.fontSize());
        style.setFont(font);
        // 设置边框
        configCellBorder(style);
        cell.setCellStyle(style);
    }
    /**
     * 配置列头单元格
     */
    private void configFirstCell (SXSSFWorkbook workbook, Cell cell ) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置背景
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        // 字体
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short)18);
        style.setFont(font);
        // 设置边框
        configCellBorder(style);
        cell.setCellStyle(style);
    }
//    public static CellStyle configEndCellStyle =null;
    /**
     * 配置列头单元格
     */
    private CellStyle configEndCell (SXSSFWorkbook workbook ) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.RIGHT);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置背景
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        // 字体
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short)14);
        style.setFont(font);
        // 设置边框
        configCellBorder(style);
        return style;

    }

    /**
     * 配置单元格边框
     */
    private static void configCellBorder(CellStyle style) {
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    }

    /**
     * 处理单元格数据
     */
    private String getCellData (ColumnInfo columnInfo, T row) throws Exception {
        columnInfo.field.setAccessible(Boolean.TRUE);
        Object value = columnInfo.field.get(row);
        columnInfo.field.setAccessible(Boolean.FALSE);
        if (value == null) {
            return "";
        }
        String stringValue = value.toString();
        // 存在自定义数据处理器
        if (!columnInfo.columnConfig.handler().equals(ExcelDataHandlerAdapter.class)) {
            try {
                Object instance = columnInfo.columnConfig.handler().newInstance();
                Method formatMethod = columnInfo.columnConfig.handler().getMethod("format", Object[].class);
                List<Object> args = new ArrayList<>();
                args.add(value);
                for (String arg : columnInfo.columnConfig.args()) {
                    args.add(arg);
                }
                value = formatMethod.invoke(instance, new Object[]{args.toArray()});
                stringValue = value.toString();
            } catch (Exception e) {
                throw new IllegalStateException("Can not format data by " + columnInfo.columnConfig.handler(), e);
            }
        }
        // 日期处理
        if (!"".equals(columnInfo.columnConfig.dateFormat()) && value instanceof Date) {
            SimpleDateFormat sdf = new SimpleDateFormat(columnInfo.columnConfig.dateFormat());
            stringValue = sdf.format((Date) value);
        }
        // 值映射
        if (!"".equals(columnInfo.columnConfig.valueMapping())) {
            String[] segs = columnInfo.columnConfig.valueMapping().split(";");
            for (String seg : segs) {
                String[] mapping = seg.split("=");
                if (value.toString().equals(mapping[0].trim())) {
                    stringValue = mapping[1].trim();
                }
            }
        }
        // 前缀处理
        stringValue = columnInfo.columnConfig.prefix() + stringValue;
        // 后缀处理
        stringValue = stringValue + columnInfo.columnConfig.suffix();
        return stringValue;
    }

    @Data
    @AllArgsConstructor
    private static class ColumnInfo {

        private ExcelColumn columnConfig;

        private Field field;
    }

    /**
     * 导出到指定输出流
     * @param os 输出流
     */
    public static void exportList (List<List<String>> dataList ,String sheetName, OutputStream os) {
        SXSSFWorkbook sxssfWorkbook;
        try {
            sxssfWorkbook = new SXSSFWorkbook();
            Sheet sheet = sxssfWorkbook.createSheet(sheetName);
            // 创建列头
            sheet.createFreezePane(0, 1);
            Row header = sheet.createRow(0);
            CellStyle hstyle = configHeaderCellStatic(sxssfWorkbook);
            for (int i = 0; i < dataList.size(); i++) {
                Cell cell = header.createCell(i);
                cell.setCellValue(dataList.get(i).get(Constants.ZERO));
                // 列宽设置
                sheet.setColumnWidth(i, dataList.get(i).get(Constants.ZERO).length() * 2 * 256);
                // 设置列头单元格
                cell.setCellStyle(hstyle);
            }

            //总行数
            Integer rowSize = dataList.get(Constants.ZERO).size();
            //总列数
            Integer columnSize = dataList.size();
            for (int i = 1; i < rowSize; i++) {
                Row row = sheet.createRow(i);
                for (int j = 0; j < columnSize; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellValue(dataList.get(j).get(i));
                }
            }
            sxssfWorkbook.write(os);
            os.close();
        } catch (Exception e) {
            throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
        } finally {
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private static CellStyle configHeaderCellStatic (SXSSFWorkbook workbook)  {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置背景
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        // 字体
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);
        // 设置边框
        configCellBorder(style);
        return style;
    }




}