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

import com.ots.common.utils.StringUtils;
import com.ots.common.utils.poi.ExcelUtil;
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 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.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.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 全景图图表渲染
 */
@Slf4j
public class LAQAllChart {

    /**
     * 测试模板
     * @param args
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {
        System.out.println("启动测试..");
        String dataPath = "D:\\桌面文件\\LAQ全景图报告\\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);


        ZipSecureFile.setMinInflateRatio(0.001);
        try (OutputStream os = new FileOutputStream("D:\\桌面文件\\可删除\\LAQ英文报告"+UUID.randomUUID().toString()+".docx");
             XWPFDocument document = new XWPFDocument(POIXMLDocument.openPackage("D:\\桌面文件\\LAQ英文报告-叠加版本.docx"))) {

            Map<String, Object> textMap = new HashMap<>();
            textMap.put("allList",laqTemplateList);
            changeChart(document,textMap);
            document.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        }


        System.out.println("处理完成...");
    }


    /**
     * 替换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);
        System.out.println("进入全景图渲染模式");
        try{
            List<LAQTemplate> laqTemplateList = (List<LAQTemplate>)textMap.get("allList");
            changeChartNew(document,laqTemplateList);
            System.out.println("全景图渲染完成");
        }
        catch (Exception ex)
        {
            log.error("LAQ全景图异常："+ex.getMessage());
        }

    }


    public static void changeChartNew(XWPFDocument document,List<LAQTemplate> list) {
        if(list.size()<=0){
            return;
        }
        try {
            //获取数据
            Map<Integer,List<SeriesData>> seriesDataMapTop = getSeriesDataByType(list,1);
            Map<Integer,List<SeriesData>> seriesDataMapMiddle = getSeriesDataByType(list,2);
            Map<Integer,List<SeriesData>> seriesDataMapBottom = getSeriesDataByType(list,3);

            //获取标签
            Map<Integer,String> seriesDataLablesTop = getSeriesDataLablesByType(list,1);
            Map<Integer,String> seriesDataLablesMiddle = getSeriesDataLablesByType(list,2);
            Map<Integer,String> seriesDataLablesBottom = getSeriesDataLablesByType(list,3);

            List<POIXMLDocumentPart> relations = document.getRelations();
            int barIndex = 1;

            //三个图表的个数
            int topIndex =1;
            int middleIndex =1;
            int bottomIndex =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.getScatterChartList().isEmpty()) {
                        CTScatterChart scatterChart = plot.getScatterChartArray(0);
                        //第一个散点图1-90，第二个91-180，第三个181-270
                        System.out.println("第"+barIndex+"个散点图");

                        String lables = "";//散点图的显示标签
                        List<SeriesData> seriesDatas = new ArrayList<>();

                        // 获取图表的值轴,并把最小值设置为-0，
                        // chart.getAxes().get(1) 获取的纵坐标，chart.getAxes().get(0) 获取的是横坐标
                        chart.getAxes().get(1).setMinimum(0);
                        Double maximum = chart.getAxes().get(0).getMaximum();

                        Double minimum = chart.getAxes().get(0).getMinimum();
//                        System.out.println("X轴最小值是："+minimum+",最大值是："+maximum);

                        //判断图表所在位置
                        if(minimum>=67)
                        {
                            seriesDatas = seriesDataMapTop.get(topIndex);
                            lables = seriesDataLablesTop.get(topIndex);
                            topIndex++;
//                            System.out.println("第一页图表个数："+topIndex+"，标签："+lables+";"+"X轴最小值是："+minimum+",最大值是："+maximum);
                        }
                        else if(minimum>=33)
                        {
                            seriesDatas = seriesDataMapMiddle.get(middleIndex);
                            lables = seriesDataLablesMiddle.get(middleIndex);
//                            System.out.println("第二页图表个数："+middleIndex);

                            middleIndex++;
                        }
                        else
                        {
                            seriesDatas = seriesDataMapBottom.get(bottomIndex);
                            lables = seriesDataLablesBottom.get(bottomIndex);
                            bottomIndex++;
//                            System.out.println("第三页图表个数："+bottomIndex);
                        }


                        barIndex++;


                        if(seriesDatas==null)
                        {
                            for (CTScatterSer ser : scatterChart.getSerList()) {
                                ser.getTx().getStrRef().getStrCache().getPtArray(0).setV("");
                            }
                            continue;
                        }

                        //刷新内置excel数据
                        refreshExcel(seriesDatas, sheet);
                        workbook.write(chart.getPackagePart().getOutputStream());
                        int i = 0;
                        for (CTScatterSer ser : scatterChart.getSerList()) {
                            //更新散点图缓存
                            updateScatterChart(seriesDatas, ser.getXVal(), ser.getYVal());
                            ser.getTx().getStrRef().getStrCache().getPtArray(0).setV(lables);//设置标签
                            ++i;
                        }

                    }



                }
            }


            System.out.println("第一页图表个数："+(topIndex-1));
            System.out.println("第二页图表个数："+(middleIndex-1));
            System.out.println("第三页图表个数："+(bottomIndex-1));

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

    /**
     * 更新 散点图 缓存数据
     *
     * @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();


        System.out.println("[有数据]条数  x:"+xNumCnt+" y:"+yNumCnt);

        //获取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);
            if(cellValu.value == -1){
                val.setV(null);
            }else {
                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);
            if(cellValu.value == -1){
                val.setV(null);
            }else {
                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;
    }

    /**
     * 更新图表里 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{
                    if(cellValu.value != -1){
                        cell.setCellValue(cellValu.value);
                    }else{
                        cell.setCellValue((String) null);
                    }
                }

            }
            //删除多余行数
            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));
                }
            }
        }
    }

    /**
     * 获取指定类型的SeriesData。
     * @param list
     * @param type
     * @return
     */
    public static Map<Integer,List<SeriesData>> getSeriesDataByType(List<LAQTemplate> list,Integer type)
    {
        Map<Integer,List<SeriesData>> seriesDataMap = new HashMap<>();
        int dataIndex =1;
        for (int i = 0; i < list.size(); i++)
        {
            LAQTemplate laqTemplate = list.get(i);
            Double olas = Double.valueOf(laqTemplate.getOlas().toString());
            Double perfomance;
            if(laqTemplate.getPerfomance()!= null)
            {
                perfomance = Double.valueOf(laqTemplate.getPerfomance().toString());
            }
            else {
                perfomance = 0.0;
            }





            //如果y轴小于1，要设置成3，往上移动一下
            if(perfomance<=1.2)
            {
                perfomance=perfomance+1.2;
            }
            //如果y轴大于97，设置成97，往下移动一点，好看
            if(perfomance>=99)
            {
                perfomance=99.0;
            }

            if(type==1)
            {
                //x轴要往右边移动
                if(olas>67.0&olas<=70.0)
                {
                    olas=olas+1.5;
                }

                if(olas>=99.0)
                {
                    olas = olas-2.0;
                }
            }

            if(type==2)
            {
                //x轴要往右边移动
                if(olas>=33.0&olas<=35.0)
                {
                    olas= olas+1;
                }
            }

            if(type==3)
            {
                //x轴要往右边移动
                if(olas>0.0 && olas<=3.0)
                {
                    olas= olas + 1;
                }

            }

            List<SeriesData> seriesDatas = new ArrayList<>();
            seriesDatas = Arrays.asList(
                    new SeriesData("X 值", Arrays.asList(
                            new RowData("X 值", olas)
                    )),
                    new SeriesData("Y 值", Arrays.asList(
                            new RowData("Y 值", perfomance)
                    ))
            );

            if(type==1)
            {
                if(olas>67)
                {
                    seriesDataMap.put(dataIndex,seriesDatas);
                    dataIndex++;
                }
            }

            if(type==2)
            {
                if(olas<=67 && olas>33)
                {
                    seriesDataMap.put(dataIndex,seriesDatas);
                    dataIndex++;
                }
            }

            if(type==3)
            {
                if(olas<=33)
                {
                    seriesDataMap.put(dataIndex,seriesDatas);
                    dataIndex++;
                }
            }

        }

        return  seriesDataMap;
    }

    /**
     * 获取散点图的所有标签
     * @param list
     * @param type
     * @return
     */
    public static Map<Integer,String> getSeriesDataLablesByType(List<LAQTemplate> list,Integer type)
    {
        Map<Integer,String> seriesDataMapLables = new HashMap<>();
        int dataIndex =1;
        for (int i = 0; i < list.size(); i++)
        {
            LAQTemplate laqTemplate = list.get(i);
            Double olas = Double.valueOf(laqTemplate.getOlas().toString());
            Double perfomance;
            if(laqTemplate.getPerfomance()!= null)
            {
                perfomance = Double.valueOf(laqTemplate.getPerfomance().toString());
            }
            else {
                perfomance = 0.0;
            }


            String olasStr = olas.toString();
            String perfomanceStr = perfomance.toString();

            // 判断小数部分是否为0
            if (Math.abs(olas - Math.floor(olas)) < 1e-9) {
                // 如果小数部分接近于0，则取整数部分
                olasStr = String.valueOf(olas.intValue());
            }

            if (Math.abs(perfomance - Math.floor(perfomance)) < 1e-9) {
                // 如果小数部分接近于0，则取整数部分
                perfomanceStr = String.valueOf(perfomance.intValue());
            }

            if(type==1)
            {
                if(olas>67)
                {
                    seriesDataMapLables.put(dataIndex,laqTemplate.getName()+"\n("+olasStr+"-"+perfomanceStr+")");
                    dataIndex++;
                }
            }

            if(type==2)
            {
                if(olas<=67 && olas>33)
                {
                    seriesDataMapLables.put(dataIndex,laqTemplate.getName()+"\n("+olasStr+"-"+perfomanceStr+")");
                    dataIndex++;
                }
            }

            if(type==3)
            {
                if(olas<=33)
                {
                    seriesDataMapLables.put(dataIndex,laqTemplate.getName()+"\n("+olasStr+"-"+perfomanceStr+")");

                    dataIndex++;
                }
            }
        }

        return  seriesDataMapLables;
    }


    /**
     * 获取图表有哪些类型
     * @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()+"}");
        }
    }
}