package com.jjt.order.utils;

import com.jjt.biz.vo.GramMassDetail;
import com.jjt.calc.domain.TwinCalcDay;
import com.jjt.common.utils.DateUtils;
import com.jjt.common.utils.StringUtils;
import com.jjt.order.domain.TwinOrder;
import com.jjt.order.domain.TwinOrderDetail;
import com.jjt.order.domain.VmsStock;
import com.jjt.order.vo.BomVO;
import com.jjt.order.vo.SkuVO;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xddf.usermodel.chart.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTDLbls;

import java.math.BigDecimal;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

/**
 * OrderExportUtils$
 *
 * @author wukai
 * @date 2025/2/12 02:27
 */
public class OrderGsmExportUtil {
    private CellStyle p2;
    private CellStyle percentStyle;

    public void init(XSSFWorkbook wb) {
        CreationHelper creationHelper = wb.getCreationHelper();
        this.percentStyle = wb.createCellStyle();
        percentStyle.setDataFormat(creationHelper.createDataFormat().getFormat("0.00%"));
        percentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        this.p2 = wb.createCellStyle();
        p2.setDataFormat(wb.createDataFormat().getFormat("0.00"));
        p2.setVerticalAlignment(VerticalAlignment.CENTER);
    }

    public void process(XSSFWorkbook wb, String sheetTitle, List<TwinOrder> orderList, List<GramMassDetail> infos, Map<Long, TwinOrder> orderMap, Map<Long, TwinCalcDay> dayMap, List<VmsStock> stocks, Integer days) {
        init(wb);
        infos.sort(Comparator.comparing(GramMassDetail::getFormula_data_2));
        List<TwinOrderDetail> all = new ArrayList<>();
        orderList.forEach(to -> all.addAll(to.getTwinOrderDetailList().stream().filter(tod -> tod.getBomSpec() != null).collect(Collectors.toList())));
        //按规格分组
        Map<String, List<TwinOrderDetail>> allMap = all.stream().collect(Collectors.groupingBy(TwinOrderDetail::getBomSpec, LinkedHashMap::new, Collectors.toList()));
        List<BomVO> bomList = new ArrayList<>();
        List<SkuVO> skuList = new ArrayList<>();
        for (String spec : allMap.keySet()) {
            List<TwinOrderDetail> todList = allMap.get(spec);
            //匹配白坯布库存
            Float kc = 0f;
            VmsStock stock = matchStock(spec, stocks);
            if (stock != null) {
                stock.setGsm();
                kc = stock.getQty();
            }
            //匹配生产设备
            List<GramMassDetail> matchFormula = matchFormula(spec, infos);
            BigDecimal cl = BigDecimal.ZERO;
            String deviceIds = "";
            for (GramMassDetail match : matchFormula) {
                if (StringUtils.isNotEmpty(deviceIds)) {
                    deviceIds += ",";
                }
                deviceIds += match.getFormula_data_2();
                TwinCalcDay day = dayMap.get(match.getFormula_data_2());
                if (day != null) {
                    cl = cl.add(day.getWeight());
                }
            }

            cl = cl.multiply(BigDecimal.valueOf(1000));

            BomVO bom = new BomVO();
            bom.setDeviceIds(deviceIds);
            bom.setLast(cl.doubleValue());
            //统计订单数量
            long orders = todList.stream().map(TwinOrderDetail::getOderId).distinct().count();
            double totalWeight = todList.stream().mapToDouble(o -> o.getWeight().doubleValue()).sum();
            TwinOrderDetail tod = todList.get(0);
            bom.setBomCode(tod.getBomCode());
            bom.setBomName(tod.getBomName());
            bom.setBomSpec(spec);
            bom.setGsm(OrderExportUtil.calcGsm(spec));

            bom.setXql(totalWeight);
            bom.setKcl(kc);
            bom.setOrders(orders);
            double ycl = cl.multiply(BigDecimal.valueOf(days)).doubleValue();
            double cz = totalWeight - kc - ycl;
            bom.setYcl(ycl);
            bom.setCz(cz);
            bom.setCl(cz / totalWeight);

            SkuVO sku = new SkuVO();
            sku.setBomCode(tod.getBomCode());
            sku.setBomName(tod.getBomName());
            sku.setBomSpec(spec);
            if (stock != null) {
                sku.setSku(stock.getSku());
                sku.setDesc(stock.getDesc());
                sku.setNum(stock.getNum());
                sku.setQty(stock.getQty());
                sku.setGsm(stock.getGsm());
            } else {
                sku.setSku("");
                sku.setDesc("");
                sku.setNum(0);
                sku.setQty(0f);
                sku.setGsm(BigDecimal.ZERO);
            }
            sku.setXql(bom.getXql());
            List<TwinOrder> skuOrder = new ArrayList<>();
            //按订单分组
            Map<Long, List<TwinOrderDetail>> map = todList.stream().collect(Collectors.groupingBy(TwinOrderDetail::getOderId, LinkedHashMap::new, Collectors.toList()));
            for (Long oid : map.keySet()) {
                TwinOrder to = orderMap.get(oid);
                to.setTwinOrderDetailList(map.get(oid));
                skuOrder.add(to);
            }
            sku.setOrderList(skuOrder);
            bom.setOrderList(skuOrder);
            bomList.add(bom);
            skuList.add(sku);
        }
        XSSFSheet sheet0 = wb.getSheetAt(0);
        Cell title = sheet0.getRow(0).getCell(0);
        title.setCellValue(sheetTitle);

        sheet0(sheet0, bomList);
        sheet1(wb.getSheetAt(1), stocks);
        sheet2(wb.getSheetAt(2), skuList);
        sheet3(wb.getSheetAt(3), bomList);
        sheet4(wb.getSheetAt(4), infos);
    }


    /**
     * 总览
     *
     * @param sheet   sheet
     * @param bomList 物料列表
     */
    public void sheet0(XSSFSheet sheet, List<BomVO> bomList) {
        AtomicInteger num = new AtomicInteger(3);
        BomVO bom = new BomVO();
        bom.init();
        bomList.forEach(obj -> {
            Row row = sheet.createRow(num.getAndIncrement());
            Cell[] cells = new Cell[11];
            for (int i = 0; i < cells.length; i++) {
                cells[i] = row.createCell(i);
            }
            cells[0].setCellValue(obj.getBomCode());
            cells[1].setCellValue(obj.getBomName());
            cells[2].setCellValue(obj.getBomSpec());
            cells[3].setCellValue(obj.getGsm().doubleValue());
            cells[4].setCellValue(obj.getOrders());
            cells[5].setCellValue(obj.getXql());
            cells[6].setCellValue(obj.getKcl());
            cells[8].setCellValue(obj.getYcl());
            cells[9].setCellValue(obj.getCz());
            cells[10].setCellValue(obj.getCl());
            cells[10].setCellStyle(percentStyle);
            bom.setOrders(bom.getOrders() + obj.getOrders());
            bom.setXql(bom.getXql() + obj.getXql());
            bom.setKcl(bom.getKcl() + obj.getKcl());
            bom.setYcl(bom.getYcl() + obj.getYcl());
            bom.setCz(bom.getCz() + obj.getCz());
            bom.setCl(bom.getCl() + obj.getCl());
        });
        Row row = sheet.createRow(num.getAndIncrement());
        Cell[] cells = new Cell[11];
        for (int i = 0; i < cells.length; i++) {
            cells[i] = row.createCell(i);
        }
        cells[0].setCellValue("合计");
//        cells[3].setCellValue(bom.getOrders());
        cells[5].setCellValue(bom.getXql());
        cells[6].setCellValue(bom.getKcl());
        cells[8].setCellValue(bom.getYcl());
        cells[9].setCellValue(bom.getCz());
        for (int i = 4; i < 9; i++) {
            cells[i].setCellStyle(p2);
        }
        cells[10].setCellValue(bom.getCl() / bomList.size());
        cells[10].setCellStyle(percentStyle);
        // 创建一个画布
        XSSFDrawing drawing = sheet.createDrawingPatriarch();
        // 前四个默认0，[0,4]：从0列4行开始;[7,20]:到7列20行结束
        // 默认宽度(14-8)*12
        XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 12, 3, 18, 20);
        // 创建一个chart对象
        XSSFChart chart = drawing.createChart(anchor);
        // 标题
        chart.setTitleText("需求重量占比图");
        // 标题是否覆盖图表
        chart.setTitleOverlay(false);

        // 图例位置
        XDDFChartLegend legend = chart.getOrAddLegend();
        legend.setPosition(LegendPosition.TOP_RIGHT);

        XDDFCategoryDataSource countries = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(3, num.get() - 2, 2, 2));
        // 数据1，单元格范围位置[1, 0]到[1, 6]
        XDDFNumericalDataSource<Double> area = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(3, num.get() - 2, 4, 4));
//         XDDFChartData data = chart.createData(ChartTypes.PIE3D, null, null);
        XDDFChartData data = chart.createData(ChartTypes.PIE, null, null);
        // 设置为可变颜色
        data.setVaryColors(true);
        // 图表加载数据
        data.addSeries(countries, area);

        // 绘制
        chart.plot(data);
        CTDLbls dLbls = chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).addNewDLbls();
        dLbls.addNewShowVal().setVal(false);
        dLbls.addNewShowLegendKey().setVal(false);
        dLbls.addNewShowCatName().setVal(true);
        // 类别名称
        dLbls.addNewShowSerName().setVal(false);
        dLbls.addNewShowPercent().setVal(true);
        // 百分比
        dLbls.addNewShowLeaderLines().setVal(true);
        // 引导线
        dLbls.setSeparator("\n");
        // 分隔符为分行符

    }

    /**
     * 白坯布全量
     *
     * @param sheet  sheet
     * @param stocks 库存
     */
    public void sheet1(Sheet sheet, List<VmsStock> stocks) {
        AtomicInteger num = new AtomicInteger(3);
        stocks.forEach(stock -> {
            Row row = sheet.createRow(num.getAndIncrement());
            Cell[] cells = new Cell[9];
            for (int i = 0; i < cells.length; i++) {
                cells[i] = row.createCell(i);
            }
            cells[0].setCellValue(stock.getSku());
            cells[1].setCellValue(stock.getDesc());
            cells[2].setCellValue(stock.getNum());
            cells[3].setCellValue(stock.getQty());
            cells[3].setCellStyle(p2);
            cells[4].setCellValue(stock.getWeight());
            cells[5].setCellValue(stock.getLength());
            cells[6].setCellValue(stock.getMick());
            cells[7].setCellValue(stock.getGsm().doubleValue());
            cells[7].setCellStyle(p2);
//            cells[5].setCellValue();
        });
    }

    /**
     * 白坯库存匹配
     *
     * @param sheet   sheet
     * @param skuList 库存
     */
    public void sheet2(Sheet sheet, List<SkuVO> skuList) {
        AtomicInteger num = new AtomicInteger(3);
        skuList.forEach(sku -> {
            int sr = num.get();
            Row row = sheet.createRow(num.getAndIncrement());
            Cell[] cells = new Cell[20];
            for (int i = 0; i < cells.length; i++) {
                cells[i] = row.createCell(i);
            }
            cells[0].setCellValue(sku.getSku());
            cells[1].setCellValue(sku.getDesc());
            cells[2].setCellValue(sku.getGsm().doubleValue());
            cells[3].setCellValue(sku.getNum());
            cells[4].setCellValue(sku.getQty());
            cells[4].setCellStyle(p2);
            cells[5].setCellValue(sku.getXql());
            cells[5].setCellStyle(p2);
            cells[6].setCellValue(sku.getBomCode());
            cells[7].setCellValue(sku.getBomName());
            cells[8].setCellValue(sku.getBomSpec());
            cells[9].setCellValue(CalcUtil.calcGsm(sku.getBomSpec()).doubleValue());
            boolean out = false;
            for (TwinOrder order : sku.getOrderList()) {
                if (out) {
                    row = sheet.createRow(num.getAndIncrement());
                    cells = new Cell[20];
                    for (int i = 0; i < cells.length; i++) {
                        cells[i] = row.createCell(i);
                    }
                }
                cells[10].setCellValue(DateUtils.parseDateToStr(order.getOrderDate()));
                cells[11].setCellValue(order.getOrderCode());
                cells[12].setCellValue(order.getCustomerCode());
                cells[13].setCellValue(order.getCustomerName());
                boolean in = false;
                int ssr = num.get() - 1;
                for (TwinOrderDetail detail : order.getTwinOrderDetailList()) {
                    if (in) {
                        row = sheet.createRow(num.getAndIncrement());
                        cells = new Cell[20];
                        for (int i = 0; i < cells.length; i++) {
                            cells[i] = row.createCell(i);
                        }
                    }
                    cells[14].setCellValue(detail.getColor());
                    cells[15].setCellValue(detail.getPrice().doubleValue());
                    cells[16].setCellValue(detail.getTotalPrice().doubleValue());
                    cells[17].setCellValue(detail.getUnit());
                    cells[18].setCellValue(detail.getNum().doubleValue());
                    cells[19].setCellValue(detail.getWeight().doubleValue());
                    in = true;
                }
                out = true;
                int eer = num.get() - 1;
                if (eer - ssr > 1) {
                    for (int i = 10; i < 14; i++) {
                        sheet.addMergedRegion(new CellRangeAddress(ssr, eer, i, i));
                    }
                }
            }
            int er = num.get() - 1;
            if (er - sr > 1) {
                for (int i = 0; i < 10; i++) {
                    sheet.addMergedRegion(new CellRangeAddress(sr, er, i, i));
                }
            }
        });
    }

    /**
     * 经编机匹配明细
     *
     * @param sheet   sheet
     * @param bomList 库存
     */
    public void sheet3(Sheet sheet, List<BomVO> bomList) {
        AtomicInteger num = new AtomicInteger(3);
        bomList.forEach(obj -> {
            int sr = num.get();
            Row row = sheet.createRow(num.getAndIncrement());
            Cell[] cells = new Cell[19];
            for (int i = 0; i < cells.length; i++) {
                cells[i] = row.createCell(i);
            }
            cells[0].setCellValue(obj.getBomCode());
            cells[1].setCellValue(obj.getBomName());
            cells[2].setCellValue(obj.getBomSpec());
            cells[3].setCellValue(obj.getGsm().doubleValue());
            cells[4].setCellValue(obj.getXql());
            cells[5].setCellValue(obj.getDeviceIds());
            cells[6].setCellValue(obj.getLast());
            cells[7].setCellValue(obj.getYcl());

            boolean out = false;
            for (TwinOrder order : obj.getOrderList()) {
                if (out) {
                    row = sheet.createRow(num.getAndIncrement());
                    cells = new Cell[19];
                    for (int i = 0; i < cells.length; i++) {
                        cells[i] = row.createCell(i);
                    }
                }
                Cell xql = cells[8];
                cells[9].setCellValue(DateUtils.parseDateToStr(order.getOrderDate()));
                cells[10].setCellValue(order.getOrderCode());
                cells[11].setCellValue(order.getCustomerCode());
                cells[12].setCellValue(order.getCustomerName());
                boolean in = false;
                int ssr = num.get() - 1;
                BigDecimal ow = BigDecimal.ZERO;
                for (TwinOrderDetail detail : order.getTwinOrderDetailList()) {
                    if (in) {
                        row = sheet.createRow(num.getAndIncrement());
                        cells = new Cell[19];
                        for (int i = 0; i < cells.length; i++) {
                            cells[i] = row.createCell(i);
                        }
                    }
                    cells[13].setCellValue(detail.getColor());
                    cells[14].setCellValue(detail.getPrice().doubleValue());
                    cells[15].setCellValue(detail.getTotalPrice().doubleValue());
                    cells[16].setCellValue(detail.getUnit());
                    cells[17].setCellValue(detail.getNum().doubleValue());
                    cells[18].setCellValue(detail.getWeight().doubleValue());
                    ow = ow.add(detail.getWeight());
                    in = true;
                }
                xql.setCellValue(ow.doubleValue());
                xql.setCellStyle(p2);
                out = true;
                int eer = num.get() - 1;
                if (eer - ssr > 1) {
                    for (int i = 8; i < 12; i++) {
                        sheet.addMergedRegion(new CellRangeAddress(ssr, eer, i, i));
                    }
                }
            }
            int er = num.get() - 1;
            if (er - sr > 1) {
                for (int i = 0; i < 7; i++) {
                    sheet.addMergedRegion(new CellRangeAddress(sr, er, i, i));
                }
            }
        });
    }


    /**
     * 经编规格分布
     *
     * @param sheet sheet
     * @param infos 库存
     */
    public void sheet4(Sheet sheet, List<GramMassDetail> infos) {
        AtomicInteger num = new AtomicInteger(3);
//        infos.sort(Comparator.comparing(TwinFormulaInfo::getFd18).reversed().thenComparing(TwinFormulaInfo::getFd16).thenComparing(TwinFormulaInfo::getFd17).thenComparing(TwinFormulaInfo::getFd3));
        infos.forEach(info -> {
            Row row = sheet.createRow(num.getAndIncrement());
            Cell[] cells = new Cell[36];
            for (int i = 0; i < cells.length; i++) {
                cells[i] = row.createCell(i);
            }
            cells[0].setCellValue(info.getFormula_data_2());
            cells[1].setCellValue(info.getFormula_data_18());
            cells[2].setCellValue(info.getFormula_data_16());
            cells[3].setCellValue(info.getFormula_data_17());
            cells[4].setCellValue(info.getGramMass());
            cells[5].setCellValue(info.getFormula_data_3());
            cells[6].setCellValue(info.getFormula_data_15());
            cells[7].setCellValue(info.getFormula_data_4());
            cells[8].setCellValue(info.getFormula_data_5());
            cells[9].setCellValue(info.getFormula_data_6());
            cells[10].setCellValue(info.getFormula_data_1());
            cells[11].setCellValue(info.getFormula_data_13());
            cells[12].setCellValue(info.getFormula_data_14());
            cells[13].setCellValue(info.getFormula_data_24());
            if (info.getFormula_data_25() != null) {
                cells[14].setCellValue(info.getFormula_data_25());
            }

//            cells[15].setCellValue(info.getCd10());
//            cells[16].setCellValue(info.getCd11());
//            cells[17].setCellValue(info.getCd12());
//            cells[18].setCellValue(info.getCd13());
//            cells[19].setCellValue(info.getCd14());
//            cells[20].setCellValue(info.getCd15());
//            cells[21].setCellValue(info.getCd16());
//            cells[22].setCellValue(info.getCd17());
//            cells[23].setCellValue(info.getCd18());
//            cells[24].setCellValue(info.getCd19());
//
//            cells[25].setCellValue(info.getFd19());
//            cells[26].setCellValue(info.getFd20());
//            cells[27].setCellValue(info.getFd21());
//            cells[28].setCellValue(info.getFd22());
//            cells[29].setCellValue(info.getFd23());

            cells[15].setCellValue(info.getFormula_data_7());
            cells[16].setCellValue(info.getFormula_data_8());
            cells[17].setCellValue(info.getFormula_data_9());
            cells[18].setCellValue(info.getFormula_data_10());
            cells[19].setCellValue(info.getFormula_data_11());
            cells[20].setCellValue(info.getFormula_data_12());
        });
    }

    /**
     * 匹配库存,新匹配方式，以幅宽和平方米克重来匹配
     * 订单中计算得到毛毯平方米克重要求，根据长宽规格，先以产品长度匹配白胚库中的幅宽规格。
     * 如没有或不足，以宽匹配，但此时要做标注，以准备网框的工艺切换。
     * 白胚库的规格，只以平方米克重和幅宽为规格单位。
     *
     * @param spec   规格
     * @param stocks 库存
     * @return 结果
     */
    private VmsStock matchStock(String spec, List<VmsStock> stocks) {
        //按平方米克重分组
        Map<BigDecimal, List<VmsStock>> stockMap = stocks.stream().collect(Collectors.groupingBy(VmsStock::getGsm, LinkedHashMap::new, Collectors.toList()));
        if (spec != null) {
            String[] tmp = spec.split("\\*");
            if (tmp.length == 3) {
                //长
                float length = Float.parseFloat(tmp[0]);
                //宽
                float width = Float.parseFloat(tmp[1]);
                BigDecimal gsm = CalcUtil.calcGsm(spec);
                List<VmsStock> tempList = new ArrayList<>();
                for (BigDecimal key : stockMap.keySet()) {
                    // 计算 gsm 和 key 的差值
                    BigDecimal difference = gsm.subtract(key).abs();
                    // 如果差值在正负10之间
                    if (difference.compareTo(new BigDecimal(10)) <= 0) {
                        // 将对应的 VmsStock 列表添加到 tempList 中
                        tempList.addAll(stockMap.get(key));
                    }
                }
                //计算差值，匹配差值最小的
                float xx = 1000f;
                VmsStock result = null;
                for (VmsStock stock : tempList) {
                    float yyy = stock.getLength().floatValue() - length;
                    if (yyy < xx) {
                        result = stock;
                    }
                }
                if (result == null) {
                    for (VmsStock stock : tempList) {
                        float yyy = stock.getLength().floatValue() - width;
                        if (yyy < xx) {
                            result = stock;
                        }
                    }
                }
                return result;

            }
        }
        return null;
    }


    /**
     * 匹配产线
     *
     * @param spec 规格
     * @param list 产线
     * @return 结果
     */
    private List<GramMassDetail> matchFormula(String spec, List<GramMassDetail> list) {
        List<GramMassDetail> result = new ArrayList<>();
        //按平方米克重分组
        Map<Float, List<GramMassDetail>> formulaMap = list.stream().collect(Collectors.groupingBy(GramMassDetail::getGramMass, LinkedHashMap::new, Collectors.toList()));
        if (spec != null) {
            String[] tmp = spec.split("\\*");
            if (tmp.length == 3) {
                Float length = Float.parseFloat(tmp[0]);
                Float width = Float.parseFloat(tmp[1]);
                BigDecimal gsm = CalcUtil.calcGsm(spec);
                List<GramMassDetail> tempList = new ArrayList<>();
                for (Float key : formulaMap.keySet()) {
                    // 计算 gsm 和 key 的差值
                    BigDecimal difference = gsm.subtract(BigDecimal.valueOf(key)).abs();
                    // 如果差值在正负10之间
                    if (difference.compareTo(new BigDecimal(10)) <= 0) {
                        // 将对应的 VmsStock 列表添加到 tempList 中
                        tempList.addAll(formulaMap.get(key));
                    }
                }
                for (GramMassDetail info : tempList) {
                    if (info.getFormula_data_16() >= length && info.getFormula_data_17() >= width ||
                            info.getFormula_data_17() >= length && info.getFormula_data_16() >= width) {
                        result.add(info);
                    }
                }
                return result;
            }
        }
        return result;
    }
}
