package com.ots.project.tool.report.LAQ.chart;

import com.alibaba.fastjson.JSON;
import com.ots.common.enums.CapabilityEnum;
import com.ots.common.enums.LangTypeEnum;
import com.ots.common.enums.ReportTypeEnum;
import com.ots.common.utils.DateUtils;
import com.ots.common.utils.MapDataUtil;
import com.ots.common.utils.StringUtils;
import com.ots.common.utils.poi.ExcelUtil;
import com.ots.common.utils.poi.WordUtil;
import com.ots.framework.config.EssConfig;
import com.ots.project.exam.domain.TReportTemplate;
import com.ots.project.tool.PdfUtil;
import com.ots.project.tool.report.LAQ.LAQReport;
import com.ots.project.tool.report.LAQ.LAQTemplate;
import com.ots.project.tool.report.MAQ.base.RowData;
import com.ots.project.tool.report.MAQ.base.SeriesData;
import com.ots.project.tool.report.MAQNEW.MAQReport_V2;
import com.ots.project.tool.report.ReportResultData;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ooxml.POIXMLDocument;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.XWPFChart;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.openxmlformats.schemas.drawingml.x2006.chart.*;

import java.io.*;
import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @description: LAQ图表
 * @author: zhijie
 * @create: 2023-10-10 15:01
 **/
@Slf4j
public class LAQChart {


    public static void main(String[] args) throws Exception {
        /*String dataPath = "D:\\测评系统\\需求\\LAQ\\report\\LAQ表头数据文件.xlsx";
        // 获取导入数据
        File file = new File(dataPath);
        InputStream in = new FileInputStream(file);
        ExcelUtil<LAQTemplate> util = new ExcelUtil<>(LAQTemplate.class);
        List<LAQTemplate> laqTemplateList = util.importExcel(in);
        // 生成文件集合
        List<String> fileNameList = new ArrayList<>();
        List<String> deleteStrList = new ArrayList<>();
        for (int i = 0; i < laqTemplateList.size(); i++) {
            LAQTemplate laqTemplate = laqTemplateList.get(i);
            //组装报告所用数据
            Map<String, Object> textMap = new HashMap<>();
//            textMap.put("sendEmailFileName",laqTemplate.getName());
//            textMap.put("nowDate", DateUtils.getDate());
//            MapDataUtil.objectToMap(laqTemplate,textMap);
            textMap.put("olas","50");
            textMap.put("ca","32");
            textMap.put("bla","34");
            textMap.put("nr","3");
            textMap.put("ar","45");
            textMap.put("open","67");
            textMap.put("cons","12");
            textMap.put("extra","48");
            textMap.put("agree","90");
            textMap.put("emosta","23");
            textMap.put("perfomance","80");


            LAQReport report = new LAQReport();
//            ReportResultData resultData = report.getTemplateParameters(textMap, ReportTypeEnum.LAQ, LangTypeEnum.codeOf("English"));
//            TReportTemplate template = null;
//            String zipFileName = WordUtil.makeReportFile("D:\\测评系统\\需求\\LAQ\\report\\LAQ英文报告-20230926.docx", template, textMap, new HashMap<>(), deleteStrList);
//            fileNameList.add(zipFileName);
            ZipSecureFile.setMinInflateRatio(0.001);
            try (OutputStream os = new FileOutputStream("D:\\测评系统\\需求\\LAQ\\report\\LAQ英文模板-wzp-修改版-1.docx");XWPFDocument document = new XWPFDocument(POIXMLDocument.openPackage("D:\\测评系统\\需求\\LAQ\\report\\LAQ英文模板-wzp-修改版.docx"))){
                Map<Integer,List<SeriesData>> seriesDatas = initData(textMap);
                changeChart(document,seriesDatas,textMap);
                document.write(os);
            }catch (Exception e){
                e.printStackTrace();
            }

            log.info("删除的文件名:{}", JSON.toJSONString(deleteStrList));
            WordUtil.deleteFileByStr(deleteStrList);
        }*/
    }

    /**
     * 替换LAQ图表数据
     * @param document 文档对象
     * @param textMap 数据源
     */
    public static void changeChart(XWPFDocument document, Map<String, Object> textMap) {
        Map<Integer,List<SeriesData>> seriesDatas = initData(textMap);
        changeChart(document,seriesDatas,textMap);
    }

    /**
     * 初始化报告数据
     * @param textMap
     * @return
     */
    public static Map<Integer,List<SeriesData>> initData(Map<String, Object> textMap){
        Map<Integer,List<SeriesData>> seriesDataMap = new HashMap<>();
        //获取命中数值
        Double olas = Double.valueOf((String)textMap.get("olas"));
        Double ca = Double.valueOf((String)textMap.get("ca"));
        Double bla = Double.valueOf((String)textMap.get("bla"));
        Double nr = Double.valueOf((String)textMap.get("nr"));
        Double ar = Double.valueOf((String)textMap.get("ar"));
        Double open = Double.valueOf((String)textMap.get("open"));
        Double cons = Double.valueOf((String)textMap.get("cons"));
        Double extra = Double.valueOf((String)textMap.get("extra"));
        Double agree = Double.valueOf((String)textMap.get("agree"));
        Double emosta = Double.valueOf((String)textMap.get("emosta"));
        Double perfomance = Double.valueOf((String)textMap.get("perfomance"));

        // 图表1
        List<SeriesData> seriesDatas = Arrays.asList(
                new SeriesData(" ", Arrays.asList(
                        new RowData(" ", 0,"类别 1")
                )),
                new SeriesData("系列 1", Arrays.asList(
                        new RowData("系列 1", olas.doubleValue())
                ))
        );
        seriesDataMap.put(1,seriesDatas);

        // 图表2
        seriesDatas = Arrays.asList(
                new SeriesData(" ", Arrays.asList(
                        new RowData(" ", 0,"Behavioral Leadership Alignment"),
                        new RowData(" ", 0,"Cognitive Ability")
                )),
                new SeriesData("系列 1", Arrays.asList(
                        new RowData("系列 1", bla.doubleValue()),
                        new RowData("系列 1", ca.doubleValue())
                ))
        );
        seriesDataMap.put(2,seriesDatas);

        // 图表3
        seriesDatas = Arrays.asList(
                new SeriesData(" ", Arrays.asList(
                        new RowData(" ", 0,"Abstract Reasoning"),
                        new RowData(" ", 0,"Numeric Reasoning")
                )),
                new SeriesData("系列 1", Arrays.asList(
                        new RowData("系列 1", ar.doubleValue()),
                        new RowData("系列 1", nr.doubleValue())
                ))
        );
        seriesDataMap.put(3,seriesDatas);

        // 图表4
        seriesDatas = Arrays.asList(
                new SeriesData(" ", Arrays.asList(
                        new RowData(" ", 0,"类别 1")
                )),
                new SeriesData("系列 1", Arrays.asList(
                        new RowData("系列 1", bla.doubleValue())
                ))
        );
        seriesDataMap.put(4,seriesDatas);

        // 图表5
        seriesDatas = Arrays.asList(
                new SeriesData(" ", Arrays.asList(
                        new RowData(" ", 0,"Emotional Stability"),
                        new RowData(" ", 0,"Agreeableness"),
                        new RowData(" ", 0,"Extraversion"),
                        new RowData(" ", 0,"Conscientiousness"),
                        new RowData(" ", 0,"Openness to Experience")
                )),
                new SeriesData("系列 1", Arrays.asList(
                        new RowData("系列 1", emosta.doubleValue()),
                        new RowData("系列 1", agree.doubleValue()),
                        new RowData("系列 1", extra.doubleValue()),
                        new RowData("系列 1", cons.doubleValue()),
                        new RowData("系列 1", open.doubleValue())
                ))
        );
        seriesDataMap.put(5,seriesDatas);

        // 图表6
        seriesDatas = Arrays.asList(
                new SeriesData(" ", Arrays.asList(
                        new RowData(" ", 0,"Emotional Stability"),
                        new RowData(" ", 0,"Agreeableness"),
                        new RowData(" ", 0,"Extraversion"),
                        new RowData(" ", 0,"Conscientiousness"),
                        new RowData(" ", 0,"Openness to Experience")
                )),
                new SeriesData("系列 1", Arrays.asList(
                        new RowData("系列 1", emosta.doubleValue()),
                        new RowData("系列 1", agree.doubleValue()),
                        new RowData("系列 1", extra.doubleValue()),
                        new RowData("系列 1", cons.doubleValue()),
                        new RowData("系列 1", open.doubleValue())
                ))
        );
        seriesDataMap.put(6,seriesDatas);

        // 图表7
        seriesDatas = Arrays.asList(
                new SeriesData(" ", Arrays.asList(
                        new RowData(" ", 0,"Emotional Stability"),
                        new RowData(" ", 0,"Agreeableness"),
                        new RowData(" ", 0,"Extraversion"),
                        new RowData(" ", 0,"Conscientiousness"),
                        new RowData(" ", 0,"Openness to Experience")
                )),
                new SeriesData("系列 1", Arrays.asList(
                        new RowData("系列 1", emosta.doubleValue()),
                        new RowData("系列 1", agree.doubleValue()),
                        new RowData("系列 1", extra.doubleValue()),
                        new RowData("系列 1", cons.doubleValue()),
                        new RowData("系列 1", open.doubleValue())
                ))
        );
        seriesDataMap.put(7,seriesDatas);

        // 图表8
        seriesDatas = Arrays.asList(
                new SeriesData(" ", Arrays.asList(
                        new RowData(" ", 0,"Emotional Stability"),
                        new RowData(" ", 0,"Agreeableness"),
                        new RowData(" ", 0,"Extraversion"),
                        new RowData(" ", 0,"Conscientiousness"),
                        new RowData(" ", 0,"Openness to Experience")
                )),
                new SeriesData("系列 1", Arrays.asList(
                        new RowData("系列 1", emosta.doubleValue()),
                        new RowData("系列 1", agree.doubleValue()),
                        new RowData("系列 1", extra.doubleValue()),
                        new RowData("系列 1", cons.doubleValue()),
                        new RowData("系列 1", open.doubleValue())
                ))
        );
        seriesDataMap.put(8,seriesDatas);

        // 图表9
        seriesDatas = Arrays.asList(
                new SeriesData(" ", Arrays.asList(
                        new RowData(" ", 0,"Emotional Stability"),
                        new RowData(" ", 0,"Agreeableness"),
                        new RowData(" ", 0,"Extraversion"),
                        new RowData(" ", 0,"Conscientiousness"),
                        new RowData(" ", 0,"Openness to Experience")
                )),
                new SeriesData("系列 1", Arrays.asList(
                        new RowData("系列 1", emosta.doubleValue()),
                        new RowData("系列 1", agree.doubleValue()),
                        new RowData("系列 1", extra.doubleValue()),
                        new RowData("系列 1", cons.doubleValue()),
                        new RowData("系列 1", open.doubleValue())
                ))
        );
        seriesDataMap.put(9,seriesDatas);

        // 图表10
        seriesDatas = Arrays.asList(
                new SeriesData("X 值", Arrays.asList(
                        new RowData("X 值", olas.doubleValue())
                )),
                new SeriesData("Y 值", Arrays.asList(
                        new RowData("Y 值", 44)
                ))
        );
        seriesDataMap.put(10,seriesDatas);

        // 图表11
        seriesDatas = Arrays.asList(
                new SeriesData("X 值", Arrays.asList(
                        new RowData("X 值", olas.doubleValue())
                )),
                new SeriesData("Y 值", Arrays.asList(
                        new RowData("Y 值", perfomance.doubleValue())
                ))
        );
        seriesDataMap.put(11,seriesDatas);

        return seriesDataMap;
    }

    /**
     * 替换LAQ图表数据
     * @param document 文档对象
     * @param seriesDataMap
     * @param textMap 数据源
     */
    public static void changeChart(XWPFDocument document, Map<Integer,List<SeriesData>> seriesDataMap, Map<String, Object> textMap) {
        if(StringUtils.isEmpty(textMap)){
            return;
        }
        try {
            List<POIXMLDocumentPart> relations = document.getRelations();
            int barIndex = 1;
            for (POIXMLDocumentPart part : relations) {
                if (part instanceof XWPFChart) {
                    // 图表元素
                    XWPFChart chart = (XWPFChart) part;
                    // 查看里面的图表数据，才能知道是什么图表
                    CTPlotArea plot = chart.getCTChart().getPlotArea();
                    getChartType(chart);
                    // excel内置表格
                    XSSFWorkbook workbook = chart.getWorkbook();
                    XSSFSheet sheet = workbook.getSheetAt(0);

                    //柱形图
                    if (!plot.getBarChartList().isEmpty()) {
                        List<SeriesData> seriesDatas = seriesDataMap.get(barIndex);
                        CTBarChart barChart = plot.getBarChartArray(0);
                        //System.out.println("柱形图{"+plot.getScatterChartList().size()+"}");
                        //刷新内置excel数据
                        refreshExcel(seriesDatas, sheet);
                        workbook.write(chart.getPackagePart().getOutputStream());

                        //柱形图数据源
//                        List<SeriesData> barDatas = seriesDatas.subList(1,4);
                        //因为柱形图CTBarSer没按excel顺序走 调换数据位置保持对应
                        Collections.swap(seriesDatas, 0, 1);
                        int j = 0;
                        for (CTBarSer ser : barChart.getSerList()) {
                            //更新柱形图数据缓存
                            updateBarChart(seriesDatas.get(j), ser.getVal());
                            ++j;
                        }
                        barIndex++;
                    }

                    //散点图
                    if (!plot.getScatterChartList().isEmpty()) {
                        List<SeriesData> seriesDatas = seriesDataMap.get(barIndex);
                        CTScatterChart scatterChart = plot.getScatterChartArray(0);
                        //System.out.println("散点图{"+plot.getScatterChartList().size()+"}");
                        //刷新内置excel数据
                        refreshExcel(seriesDatas, sheet);
                        workbook.write(chart.getPackagePart().getOutputStream());

/*                        //散点图数据源 XY坐标
                        List<SeriesData> scatterDatas = new ArrayList<SeriesData>();
                        scatterDatas.add(seriesDatas.get(0));
                        scatterDatas.add(seriesDatas.get(4));*/
                        int i = 0;
                        for (CTScatterSer ser : scatterChart.getSerList()) {
                            //更新散点图缓存
                            updateScatterChart(seriesDatas, ser.getXVal(), ser.getYVal());
                            ++i;
                        }
                        barIndex++;
                    }
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
            log.error("MAQTR替换word图表 散点图图形失败：{}",e);
        }
    }



    /**
     * 更新 柱形图 的缓存数据
     *
     * @param seriesDatas   数据
     * @param numDataSource 数据的缓存
     */
    protected static void updateBarChart(SeriesData seriesDatas, CTNumDataSource numDataSource) {
        //获取柱形图数据数量
        long ptNumCnt = numDataSource.getNumRef().getNumCache().getPtCount().getVal();
        int dataSize = seriesDatas.value.size();
        for (int i = 0; i < dataSize; i++) {
            RowData cellValu = seriesDatas.value.get(i);
            CTNumVal val = ptNumCnt > i ? numDataSource.getNumRef().getNumCache().getPtArray(i)
                    : numDataSource.getNumRef().getNumCache().addNewPt();
            val.setIdx(i);
            val.setV(String.format("%.0f", cellValu.value));

        }

        // 更新对应excel的范围
        numDataSource.getNumRef().setF(
                replaceRowEnd(numDataSource.getNumRef().getF(),
                        ptNumCnt,
                        dataSize));

        // 删除多的
        if (ptNumCnt > dataSize) {
            for (int idx = dataSize; idx < ptNumCnt; idx++) {
                numDataSource.getNumRef().getNumCache().removePt(dataSize);
            }
        }
        // 更新个数
        numDataSource.getNumRef().getNumCache().getPtCount().setVal(dataSize);
    }


    /**
     * 更新图表的关联 excel
     *
     * @param seriesDatas
     * @param sheet
     */
    protected static void refreshExcel(List<SeriesData> seriesDatas, XSSFSheet sheet) {
        XSSFRow title = sheet.getRow(0);
        //遍历数据数组进行excel赋值
        for (int i = 0; i < seriesDatas.size(); i++) {
            SeriesData data = seriesDatas.get(i);
            if (data.name != null && !data.name.isEmpty()) {
                // 第一行标题
                XSSFCell cell = title.getCell(i);
                if (cell == null) {
                    cell = title.createCell(i + 1);
                }
                cell.setCellValue(data.name);
            }
            int size = data.value.size();
            //遍历数据进行赋值
            for (int j = 0; j < size; j++) {
                //从第二行开始赋值
                XSSFRow row = sheet.getRow(j + 1);
                if (row == null) {
                    row = sheet.createRow(j + 1);
                }
                RowData cellValu = data.value.get(j);

                //第一列开始赋值
                XSSFCell cell = row.getCell(i);
                if (cell == null) {
                    cell = row.createCell(i);
                }
                //优先使用字符串值
                if(StringUtils.isNotEmpty(cellValu.valueStr)){
                    cell.setCellValue(cellValu.valueStr);
                }else{
                    cell.setCellValue(cellValu.value);
                }

            }
            //删除多余行数
            int lastRowNum = sheet.getLastRowNum();
            //log.error("lastRowNum:{},size:{}",lastRowNum,size);
            if (lastRowNum > size) {
                for (int idx = lastRowNum; idx > size; idx--) {
                    if(sheet.getRow(idx) == null){
                        continue;
                    }
                    sheet.removeRow(sheet.getRow(idx));
                }
            }
        }
    }


    /**
     * 更新 散点图 缓存数据
     *
     * @param seriesDatas 数据
     * @param xDataSource X坐标数据缓存
     * @param yDataSource Y坐标数据缓存
     */
    protected static void updateScatterChart(List<SeriesData> seriesDatas, CTAxDataSource xDataSource,
                                             CTNumDataSource yDataSource) {

        //获取xy坐标数据条数
        long xNumCnt = xDataSource.getNumRef().getNumCache().getPtCount().getVal();
        long yNumCnt = yDataSource.getNumRef().getNumCache().getPtCount().getVal();

        //获取XY最新数据
        List<RowData> xRowDatas = seriesDatas.get(0).getValue();
        int xRowDataSize = xRowDatas.size();
        List<RowData> yRowDatas = seriesDatas.get(1).getValue();
        int yRowDataSize = yRowDatas.size();

        //更新X坐标缓存
        for (int i = 0; i < xRowDatas.size(); i++) {
            RowData cellValu = xRowDatas.get(i);
            CTNumVal val = xNumCnt > i ? xDataSource.getNumRef().getNumCache().getPtArray(i)
                    : xDataSource.getNumRef().getNumCache().addNewPt();
            val.setIdx(i);
            val.setV(String.format("%.0f", cellValu.value));
        }

        //更新Y坐标缓存
        for (int i = 0; i < yRowDatas.size(); i++) {
            RowData cellValu = yRowDatas.get(i);
            CTNumVal val = yNumCnt > i ? yDataSource.getNumRef().getNumCache().getPtArray(i)
                    : yDataSource.getNumRef().getNumCache().addNewPt();
            val.setIdx(i);
            val.setV(String.format("%.0f", cellValu.value));
        }

        // 更新对应excel的范围
        xDataSource.getNumRef().setF(
                replaceRowEnd(xDataSource.getNumRef().getF(),
                        xNumCnt,
                        xRowDataSize));
        yDataSource.getNumRef().setF(
                replaceRowEnd(yDataSource.getNumRef().getF(),
                        yNumCnt,
                        yRowDataSize));

        // 删除多的
        if (xNumCnt > xRowDataSize) {
            for (int idx = xRowDataSize; idx < xNumCnt; idx++) {
                xDataSource.getNumRef().getNumCache().removePt(xRowDataSize);
            }
        }
        if (yNumCnt > yRowDataSize) {
            for (int idx = yRowDataSize; idx < yNumCnt; idx++) {
                yDataSource.getNumRef().getNumCache().removePt(yRowDataSize);
            }
        }
        // 更新个数
        xDataSource.getNumRef().getNumCache().getPtCount().setVal(xRowDataSize);
        // 更新个数
        yDataSource.getNumRef().getNumCache().getPtCount().setVal(yRowDataSize);
    }


    /**
     * 替换 形如： Sheet1!$A$2:$A$4 的字符
     *
     * @param range
     * @return
     */
    public static String replaceRowEnd(String range, long oldSize, long newSize) {
        Pattern pattern = Pattern.compile("(:\\$[A-Z]+\\$)(\\d+)");
        Matcher matcher = pattern.matcher(range);
        if (matcher.find()) {
            long old = Long.parseLong(matcher.group(2));
            return range.replaceAll("(:\\$[A-Z]+\\$)(\\d+)", "$1" + Long.toString(old - oldSize + newSize));
        }
        return range;
    }

    /**
     * 获取图表有哪些类型
     * @param part
     */
    public static void getChartType(POIXMLDocumentPart part){
        XWPFChart chart = (XWPFChart) part;
        //根据属性第一列名称切换数据类型
        CTChart ctChart = chart.getCTChart();
        CTPlotArea plotArea = ctChart.getPlotArea();

        if (!plotArea.getBarChartList().isEmpty()) {
            System.out.println("柱状图{"+plotArea.getBarChartList().size()+"}");
        }
        if (!plotArea.getPieChartList().isEmpty()) {
            System.out.println("饼图{"+plotArea.getPieChartList().size()+"}");
        }
        if (!plotArea.getLineChartList().isEmpty()) {
            System.out.println("线形图{"+plotArea.getLineChartList().size()+"}");
        }
        if (!plotArea.getBar3DChartList().isEmpty()) {
            System.out.println("柱状图3D{"+plotArea.getBar3DChartList().size()+"}");
        }
        if (!plotArea.getScatterChartList().isEmpty()) {
            System.out.println("散点图{"+plotArea.getScatterChartList().size()+"}");
        }
    }

}