package de.avatic.lcc.service.report;

import de.avatic.lcc.dto.generic.NodeDTO;
import de.avatic.lcc.dto.report.ReportDTO;
import de.avatic.lcc.dto.report.ReportDestinationDTO;
import de.avatic.lcc.dto.report.ReportEntryDTO;
import de.avatic.lcc.dto.report.ReportSectionDTO;
import de.avatic.lcc.model.bulk.header.ReportSummaryHeader;
import de.avatic.lcc.model.db.properties.SystemPropertyMappingId;
import de.avatic.lcc.repositories.MaterialRepository;
import de.avatic.lcc.repositories.properties.PropertyRepository;
import de.avatic.lcc.service.bulk.helper.CellStyleProvider;
import de.avatic.lcc.service.bulk.helper.HeaderGenerator;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.stereotype.Service;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.*;
import java.util.stream.Stream;

@Service
public class ExcelReportingService {


    private static final Map<Integer, RowInfo> rowSequence = new HashMap<>() {
        {
            put(0, new RowInfo(0, RowCaption.SUPPLIER, "Supplier", RowType.TITLE, true));
            put(1, new RowInfo(1, RowCaption.ADDRESS, "Address", RowType.SUB_TITLE, true));

            put(2, new RowInfo(2, RowCaption.SUMMARY, "Summary", RowType.HEADER, false));
            put(3, new RowInfo(3, RowCaption.SUMMARY_MEK_A, "MEK A", RowType.SPLIT_DATA, false));
            put(4, new RowInfo(4, RowCaption.SUMMARY_LOGISTICS_COST, "Logistics cost", RowType.SPLIT_DATA, false));
            put(5, new RowInfo(5, RowCaption.SUMMARY_MEK_B, "MEK B", RowType.SPLIT_DATA, false));

            put(6, new RowInfo(6, RowCaption.WEIGHTED_COST_BREAKDOWN, "Weighted cost breakdown", RowType.HEADER, false));
            put(7, new RowInfo(7, RowCaption.BREAKDOWN_MEK_A, "MEK A", RowType.SPLIT_DATA, false));
            put(8, new RowInfo(8, RowCaption.BREAKDOWN_TRANSPORT, "Transport", RowType.SPLIT_DATA, false));
            put(9, new RowInfo(9, RowCaption.BREAKDOWN_HANDLING, "Handling", RowType.SPLIT_DATA, false));
            put(10, new RowInfo(10, RowCaption.BREAKDOWN_STORAGE, "Storage", RowType.SPLIT_DATA, false));
            put(11, new RowInfo(11, RowCaption.BREAKDOWN_REPACKAGING, "Repackaging", RowType.SPLIT_DATA, false));
            put(12, new RowInfo(12, RowCaption.BREAKDOWN_DISPOSAL, "Disposal", RowType.SPLIT_DATA, false));
            put(13, new RowInfo(13, RowCaption.BREAKDOWN_CAPITAL, "Capital", RowType.SPLIT_DATA, false));
            put(14, new RowInfo(14, RowCaption.BREAKDOWN_CUSTOM, "Custom", RowType.SPLIT_DATA, false));
            put(15, new RowInfo(15, RowCaption.BREAKDOWN_FCA_FEES, "FCA fees", RowType.SPLIT_DATA, false));
            put(16, new RowInfo(16, RowCaption.BREAKDOWN_TOTAL, "Total", RowType.SPLIT_DATA, false));

            put(17, new RowInfo(17, RowCaption.FLUCTUATION, "Transport costs fluctuations", RowType.HEADER, false));
            put(18, new RowInfo(18, RowCaption.FLUCTUATION_CURRENT, "Current scenario", RowType.SPLIT_DATA, false));
            put(19, new RowInfo(19, RowCaption.FLUCTUATION_OPPORTUNITY, "Opportunity scenario", RowType.SPLIT_DATA, false));
            put(20, new RowInfo(20, RowCaption.FLUCTUATION_RISK, "Risk scenario", RowType.SPLIT_DATA, false));

            put(21, new RowInfo(21, RowCaption.ASSUMPTIONS, "Assumptions", RowType.HEADER, false));
            put(22, new RowInfo(22, RowCaption.ASSUMPTIONS_MATERIAL, "Material", RowType.SUB_HEADER, false));
            put(23, new RowInfo(23, RowCaption.ASSUMPTIONS_PART_NUMBER, "Part number", RowType.DATA, false));
            put(24, new RowInfo(24, RowCaption.ASSUMPTIONS_HS_CODE, "HS code", RowType.DATA, false));
            put(25, new RowInfo(25, RowCaption.ASSUMPTIONS_TARIFF_RATE, "Tariff rate", RowType.DATA, false));
            put(26, new RowInfo(26, RowCaption.ASSUMPTIONS_OVERSEA_SHARE, "Oversea share", RowType.DATA, false));
            put(27, new RowInfo(27, RowCaption.ASSUMPTIONS_AIR_FREIGHT_SHARE, "Airfreight share", RowType.DATA, false));
            put(28, new RowInfo(28, RowCaption.ASSUMPTIONS_SAFETY_STOCK, "Safety stock [working days]", RowType.DATA, false));

            put(29, new RowInfo(29, RowCaption.HANDLING_UNIT, "Handling unit", RowType.SUB_HEADER, false));
            put(30, new RowInfo(30, RowCaption.HANDLING_UNIT_LENGTH, "Length", RowType.DATA, false));
            put(31, new RowInfo(31, RowCaption.HANDLING_UNIT_WIDTH, "Width", RowType.DATA, false));
            put(32, new RowInfo(32, RowCaption.HANDLING_UNIT_HEIGHT, "Height", RowType.DATA, false));
            put(33, new RowInfo(33, RowCaption.HANDLING_UNIT_DIMENSION_UNIT, "Unit", RowType.DATA, false));
            put(34, new RowInfo(34, RowCaption.HANDLING_UNIT_WEIGHT, "Weight", RowType.DATA, false));
            put(35, new RowInfo(35, RowCaption.HANDLING_UNIT_DIMENSION_WEIGHT, "Unit", RowType.DATA, false));
            put(36, new RowInfo(36, RowCaption.HANDLING_UNIT_PIECES, "Pieces per HU", RowType.DATA, false));
            put(37, new RowInfo(37, RowCaption.HANDLING_UNIT_MIXED, "Mixed transport", RowType.DATA, false));

        }
    };
    private static final Map<Integer, RowInfo> destinationRowSequence = new HashMap<>() {
        {
            put(0, new RowInfo(0, RowCaption.DESTINATION, "Destination", RowType.HEADER, true));

            put(1, new RowInfo(1, RowCaption.DESTINATION_GENERAL, "General", RowType.SUB_HEADER, false));

            put(2, new RowInfo(2, RowCaption.DESTINATION_ROUTE, "Route", RowType.DATA, false));
            put(3, new RowInfo(3, RowCaption.DESTINATION_ANNUAL_QUANTITY, "Annual quantity", RowType.DATA, false));
            put(4, new RowInfo(4, RowCaption.DESTINATION_TRANSPORT_TIME, "Transit time [days]", RowType.DATA, false));

            put(5, new RowInfo(5, RowCaption.DESTINATION_CONTAINER, "Container calculation", RowType.SUB_HEADER, false));
            put(6, new RowInfo(6, RowCaption.DESTINATION_CONTAINER_LAYER, "Stacked layers", RowType.DATA, false));
            put(7, new RowInfo(7, RowCaption.DESTINATION_CONTAINER_UNIT_COUNT, "Container unit count", RowType.DATA, false));
            put(8, new RowInfo(8, RowCaption.DESTINATION_CONTAINER_TYPE, "Container type", RowType.DATA, false));
            put(9, new RowInfo(9, RowCaption.DESTINATION_CONTAINER_LIMIT, "Limiting factor", RowType.DATA, false));
        }
    };
    private final ReportingService reportingService;
    private final CellStyleProvider cellStyleProvider;
    private final HeaderGenerator headerGenerator;
    private final MaterialRepository materialRepository;
    private final PropertyRepository propertyRepository;

    public ExcelReportingService(ReportingService reportingService, CellStyleProvider cellStyleProvider, HeaderGenerator headerGenerator, MaterialRepository materialRepository, PropertyRepository propertyRepository) {

        this.reportingService = reportingService;
        this.cellStyleProvider = cellStyleProvider;
        this.headerGenerator = headerGenerator;
        this.materialRepository = materialRepository;
        this.propertyRepository = propertyRepository;
    }

    public ByteArrayResource generateExcelReport(List<Integer> materialIds, List<Integer> nodeIds, List<Integer> userNodeIds) {
        var reportingProperty = propertyRepository.getPropertyByMappingId(SystemPropertyMappingId.REPORTING).orElseThrow();
        boolean includeAirfreight = reportingProperty.getCurrentValue().equals("MEK_C");

        try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            Workbook workbook = new XSSFWorkbook();
            CellStyle headerStyle = cellStyleProvider.createHeaderCellStyle(workbook);
            var styles = cellStyleProvider.createCellStyles(workbook);


            fillSummarySheet(workbook, headerStyle, materialIds, nodeIds, userNodeIds, styles);
            materialIds.forEach(id -> fillExcelSheet(workbook, headerStyle, id, nodeIds, userNodeIds, styles, includeAirfreight));

            // Return the Excel file as an InputStreamSource
            workbook.write(outputStream);
            return new ByteArrayResource(outputStream.toByteArray());
        } catch (IOException e) {
            throw new RuntimeException("Failed to generate template", e);
        }
    }

    private void fillSummarySheet(Workbook workbook, CellStyle headerStyle, List<Integer> materialIds, List<Integer> nodeIds, List<Integer> userNodeIds, Map<CellStyleProvider.TextFormat, Map<CellStyleProvider.PredefinedColors, CellStyle>> styles) {
        Sheet sheet = workbook.createSheet("Summary");

        int row = 1;

        for (Integer materialId : materialIds) {
            for (var mapper : reportingService.getReport(materialId, nodeIds, userNodeIds).stream().map(report -> new SummaryMapper(report, styles)).toList()) {
                mapper.map(sheet, row++);
            }
        }

        headerGenerator.generateHeader(sheet, ReportSummaryHeader.class, headerStyle);
        headerGenerator.fixWidth(sheet, ReportSummaryHeader.class);

    }

    private void fillExcelSheet(Workbook workbook, CellStyle headerStyle, Integer materialId, List<Integer> nodeIds, List<Integer> userNodeIds, Map<CellStyleProvider.TextFormat, Map<CellStyleProvider.PredefinedColors, CellStyle>> styles, boolean includeAirfreight) {
        var material = materialRepository.getById(materialId).orElseThrow();
        var reports = reportingService.getReport(materialId, nodeIds, userNodeIds);

        Sheet sheet = workbook.createSheet(material.getPartNumber());

        ArrayList<String> headers = new ArrayList<>();
        headers.add("");
        headers.addAll(reports.stream().flatMap(report -> Stream.of(report.getSupplier().getName(), null)).toList());

        headerGenerator.generateHeader(sheet, headers.toArray(String[]::new), headerStyle, true);
        List<NodeDTO> destinations = collectDestinations(reports);

        var row = 1;
        var mapper = new ReportRowMapper(reports.stream().map(report -> new ReportMapper(report, destinations, styles, includeAirfreight)).toList(), destinations, styles, rowSequence, destinationRowSequence);

        boolean finished = false;
        while (!finished) {
            finished = !mapper.map(sheet, row++);
        }

        headerGenerator.fixWidth(sheet, headers.toArray(String[]::new), true);

    }

    private List<NodeDTO> collectDestinations(List<ReportDTO> reports) {
        Map<Integer, NodeDTO> destinations = new HashMap<>();

        reports.forEach(r -> r.getDestinations().forEach(d -> {
            if (!destinations.containsKey(d.getDestination().getId()))
                destinations.put(d.getDestination().getId(), d.getDestination());
        }));

        return destinations.values().stream().toList();
    }

    private enum RowCaption {
        SUPPLIER, ADDRESS, SUMMARY, SUMMARY_MEK_A, SUMMARY_LOGISTICS_COST, SUMMARY_MEK_B, WEIGHTED_COST_BREAKDOWN, BREAKDOWN_MEK_A, BREAKDOWN_TRANSPORT, BREAKDOWN_HANDLING, BREAKDOWN_STORAGE, BREAKDOWN_REPACKAGING, BREAKDOWN_DISPOSAL, BREAKDOWN_CAPITAL, BREAKDOWN_CUSTOM, BREAKDOWN_FCA_FEES, BREAKDOWN_TOTAL, FLUCTUATION, FLUCTUATION_CURRENT, FLUCTUATION_OPPORTUNITY, FLUCTUATION_RISK, ASSUMPTIONS, ASSUMPTIONS_MATERIAL, ASSUMPTIONS_PART_NUMBER, ASSUMPTIONS_HS_CODE, ASSUMPTIONS_TARIFF_RATE, ASSUMPTIONS_OVERSEA_SHARE, ASSUMPTIONS_AIR_FREIGHT_SHARE, ASSUMPTIONS_SAFETY_STOCK, HANDLING_UNIT, HANDLING_UNIT_LENGTH, HANDLING_UNIT_WIDTH, HANDLING_UNIT_HEIGHT, HANDLING_UNIT_DIMENSION_UNIT, HANDLING_UNIT_WEIGHT, HANDLING_UNIT_DIMENSION_WEIGHT, HANDLING_UNIT_PIECES, HANDLING_UNIT_MIXED, DESTINATION, DESTINATION_ROUTE, DESTINATION_ANNUAL_QUANTITY, DESTINATION_TRANSPORT_TIME, DESTINATION_CONTAINER, DESTINATION_CONTAINER_LAYER, DESTINATION_CONTAINER_UNIT_COUNT, DESTINATION_CONTAINER_TYPE, DESTINATION_CONTAINER_LIMIT, DESTINATION_GENERAL;
    }


    private enum RowType {
        TITLE, SUB_TITLE, HEADER, SUB_HEADER, DATA, SPLIT_DATA
    }

    private record RowInfo(int rowIdx, RowCaption captionType, String caption, RowType type, boolean hideCaption) {
    }

    private record ReportRowMapper(List<ReportMapper> mapper, List<NodeDTO> destinations,
                                   Map<CellStyleProvider.TextFormat, Map<CellStyleProvider.PredefinedColors, CellStyle>> styles,
                                   Map<Integer, RowInfo> rowSequence, Map<Integer, RowInfo> destinationRowSequence) {


        public boolean map(Sheet sheet, int rowIndex) {

            RowInfo info = getRowInfo(rowIndex);
            int destinationIdx = getDestinationIdx(rowIndex);

            Row row = sheet.createRow(rowIndex);

            Cell cell = row.createCell(0);

            if (info.captionType == RowCaption.DESTINATION) {
                cell.setCellValue(destinations.get(destinationIdx).getName());
            } else {
                cell.setCellValue(info.hideCaption() ? "" : info.caption());
            }

            cell.setCellStyle(getStyle(info));

            int columnIdx = 1;
            for (var curMapper : mapper) {
                curMapper.map(sheet, row, rowIndex, columnIdx, info, destinationIdx);
                columnIdx += 2;
            }

            return rowIndex < getLastIndex();
        }

        private int getLastIndex() {
            return rowSequence.size() + (destinations.size() * destinationRowSequence.size()) - 1;
        }

        private CellStyle getStyle(RowInfo info) {
            return switch (info.type) {
                case TITLE ->
                        this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.TITLE);
                case SUB_TITLE, DATA, SPLIT_DATA ->
                        this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                case HEADER ->
                        this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.HEADER);
                case SUB_HEADER ->
                        this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.SUB_HEADER);
            };
        }

        private int getDestinationIdx(int rowIndex) {

            if (rowIndex < rowSequence.size()) return -1;

            return (rowIndex - rowSequence.size()) / destinationRowSequence.size();
        }

        private RowInfo getRowInfo(int rowIndex) {
            if (rowIndex < rowSequence.size()) return rowSequence.get(rowIndex);

            return destinationRowSequence.get((rowIndex - rowSequence.size()) % destinationRowSequence.size());
        }
    }


    private record ReportMapper(ReportDTO report, List<NodeDTO> destinations,
                                Map<CellStyleProvider.TextFormat, Map<CellStyleProvider.PredefinedColors, CellStyle>> styles,
                                boolean includeAirfreight) {

        public void map(Sheet sheet, Row row, int rowIdx, int columnIdx, RowInfo info, int destinationIdx) {


            boolean mergeCells = false;
            CellStyle selectedStyle = null;

            /* **********************************
             * Cell 1
             * **********************************/

            Cell cell = row.createCell(columnIdx);

            switch (info.type) {
                case TITLE -> {
                    cell.setCellValue(report.getSupplier().getName());
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.TITLE);
                    mergeCells = true;
                }
                case SUB_TITLE -> {
                    cell.setCellValue(report.getSupplier().getAddress());
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    mergeCells = true;
                }
                case HEADER -> {
                    cell.setCellValue("");
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.HEADER);
                    mergeCells = true;
                }
                case SUB_HEADER -> {
                    cell.setCellValue("");
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.SUB_HEADER);
                    mergeCells = true;
                }
                case DATA -> {
                    selectedStyle = setCellValue(cell, info, destinationIdx, false);
                    mergeCells = true;
                }
                case SPLIT_DATA -> {
                    selectedStyle = setCellValue(cell, info, destinationIdx, false);
                }

            }

            if (selectedStyle != null) {
                cell.setCellStyle(selectedStyle);
            }

            /* **********************************
             * Cell 2
             * **********************************/

            if (mergeCells) {
                var merged = new CellRangeAddress(rowIdx, rowIdx, columnIdx, columnIdx + 1);

                if (selectedStyle != null) {
                    RegionUtil.setBorderBottom(selectedStyle.getBorderBottom(), merged, sheet);
                    RegionUtil.setBorderTop(selectedStyle.getBorderTop(), merged, sheet);
                    RegionUtil.setBorderLeft(selectedStyle.getBorderLeft(), merged, sheet);
                    RegionUtil.setBorderRight(selectedStyle.getBorderRight(), merged, sheet);
                    RegionUtil.setBottomBorderColor(selectedStyle.getBottomBorderColor(), merged, sheet);
                    RegionUtil.setTopBorderColor(selectedStyle.getTopBorderColor(), merged, sheet);
                    RegionUtil.setLeftBorderColor(selectedStyle.getLeftBorderColor(), merged, sheet);
                    RegionUtil.setRightBorderColor(selectedStyle.getRightBorderColor(), merged, sheet);
                }
                sheet.addMergedRegion(merged);
            } else {
                Cell cell2nd = row.createCell(columnIdx + 1);
                var selectedStyle2nCell = setCellValue(cell2nd, info, destinationIdx, true);
                if (selectedStyle2nCell != null) {
                    cell2nd.setCellStyle(selectedStyle2nCell);
                }
            }

        }

        private CellStyle setCellValue(Cell cell, RowInfo info, int destinationIdx, boolean secondColumn) {

            CellStyle selectedStyle = null;

            switch (info.captionType) {
                case SUPPLIER -> {
                    cell.setCellValue(report.getSupplier().getName());
                }
                case ADDRESS -> {
                    cell.setCellValue(report.getSupplier().getAddress());
                }
                case SUMMARY, WEIGHTED_COST_BREAKDOWN, FLUCTUATION, ASSUMPTIONS, ASSUMPTIONS_MATERIAL, HANDLING_UNIT,
                     DESTINATION, DESTINATION_CONTAINER, DESTINATION_GENERAL -> {
                    cell.setCellValue("");
                }
                case SUMMARY_MEK_A -> {
                    if (!secondColumn) {
                        cell.setCellValue(report.getOverview().get("mek_a").getTotal().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.CURRENCY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    } else {
                        cell.setCellValue(report.getOverview().get("mek_a").getPercentage().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.PERCENTAGE).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    }
                }
                case SUMMARY_LOGISTICS_COST -> {
                    if (!secondColumn) {
                        cell.setCellValue(report.getOverview().get("logistics").getTotal().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.CURRENCY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    } else {
                        cell.setCellValue(report.getOverview().get("logistics").getPercentage().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.PERCENTAGE).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    }
                }
                case SUMMARY_MEK_B, FLUCTUATION_CURRENT -> {
                    if (!secondColumn) {
                        cell.setCellValue(report.getOverview().get("mek_b").getTotal().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.CURRENCY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    } else {
                        cell.setCellValue(report.getOverview().get("mek_b").getPercentage().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.PERCENTAGE).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    }
                }
                case BREAKDOWN_MEK_A -> {
                    if (!secondColumn) {
                        cell.setCellValue(report.getCost().get("mek_a").getTotal().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.CURRENCY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    } else {
                        cell.setCellValue(report.getCost().get("mek_a").getPercentage().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.PERCENTAGE).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    }
                }
                case BREAKDOWN_TRANSPORT -> {
                    if (!secondColumn) {
                        cell.setCellValue(report.getCost().get("transport").getTotal().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.CURRENCY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    } else {
                        cell.setCellValue(report.getCost().get("transport").getPercentage().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.PERCENTAGE).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    }
                }
                case BREAKDOWN_HANDLING -> {
                    if (!secondColumn) {
                        cell.setCellValue(report.getCost().get("handling").getTotal().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.CURRENCY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    } else {
                        cell.setCellValue(report.getCost().get("handling").getPercentage().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.PERCENTAGE).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    }
                }
                case BREAKDOWN_STORAGE -> {
                    if (!secondColumn) {
                        cell.setCellValue(report.getCost().get("storage").getTotal().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.CURRENCY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    } else {
                        cell.setCellValue(report.getCost().get("storage").getPercentage().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.PERCENTAGE).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    }
                }
                case BREAKDOWN_REPACKAGING -> {
                    if (!secondColumn) {
                        cell.setCellValue(report.getCost().get("repacking").getTotal().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.CURRENCY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    } else {
                        cell.setCellValue(report.getCost().get("repacking").getPercentage().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.PERCENTAGE).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    }
                }
                case BREAKDOWN_DISPOSAL -> {
                    if (!secondColumn) {
                        cell.setCellValue(report.getCost().get("disposal").getTotal().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.CURRENCY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    } else {
                        cell.setCellValue(report.getCost().get("disposal").getPercentage().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.PERCENTAGE).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    }
                }
                case BREAKDOWN_CAPITAL -> {
                    if (!secondColumn) {
                        cell.setCellValue(report.getCost().get("capital").getTotal().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.CURRENCY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    } else {
                        cell.setCellValue(report.getCost().get("capital").getPercentage().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.PERCENTAGE).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    }
                }
                case BREAKDOWN_CUSTOM -> {
                    if (!secondColumn) {
                        cell.setCellValue(report.getCost().get("custom").getTotal().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.CURRENCY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    } else {
                        cell.setCellValue(report.getCost().get("custom").getPercentage().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.PERCENTAGE).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    }
                }
                case BREAKDOWN_FCA_FEES -> {
                    if (!secondColumn) {
                        cell.setCellValue(report.getCost().get("fca_fees").getTotal().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.CURRENCY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    } else {
                        cell.setCellValue(report.getCost().get("fca_fees").getPercentage().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.PERCENTAGE).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    }
                }
                case BREAKDOWN_TOTAL -> {
                    if (!secondColumn) {
                        cell.setCellValue(report.getCost().get("total").getTotal().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.CURRENCY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    } else {
                        cell.setCellValue(report.getCost().get("total").getPercentage().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.PERCENTAGE).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    }
                }
                case FLUCTUATION_OPPORTUNITY -> {
                    if (!secondColumn) {
                        cell.setCellValue(report.getOverview().get("opportunity_scenario").getTotal().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.CURRENCY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    } else {
                        cell.setCellValue(report.getOverview().get("opportunity_scenario").getPercentage().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.PERCENTAGE).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    }
                }
                case FLUCTUATION_RISK -> {
                    if (!secondColumn) {
                        cell.setCellValue(report.getOverview().get("risk_scenario").getTotal().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.CURRENCY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    } else {
                        cell.setCellValue(report.getOverview().get("risk_scenario").getPercentage().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.PERCENTAGE).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    }
                }
                case ASSUMPTIONS_PART_NUMBER -> {
                    cell.setCellValue(report.getMaterial().getPartNumber());
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                }
                case ASSUMPTIONS_HS_CODE -> {
                    cell.setCellValue(report.getPremises().getHsCode());
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                }
                case ASSUMPTIONS_TARIFF_RATE -> {
                    cell.setCellValue(report.getPremises().getTariffRate().doubleValue());
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.PERCENTAGE).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                }
                case ASSUMPTIONS_OVERSEA_SHARE -> {
                    cell.setCellValue(report.getPremises().getOverseaShare().doubleValue());
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.PERCENTAGE).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                }
                case ASSUMPTIONS_AIR_FREIGHT_SHARE -> {
                    if (includeAirfreight) {
                        cell.setCellValue(report.getPremises().getAirFreightShare().doubleValue());
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.PERCENTAGE).get(CellStyleProvider.PredefinedColors.NEUTRAL);

                    } else {
                        cell.setCellValue("-");
                        selectedStyle = this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                    }
                }
                case ASSUMPTIONS_SAFETY_STOCK -> {
                    cell.setCellValue(report.getPremises().getSafetyStock().doubleValue());
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                }
                case HANDLING_UNIT_LENGTH -> {
                    cell.setCellValue(report.getPremises().getLength());
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                }
                case HANDLING_UNIT_WIDTH -> {
                    cell.setCellValue(report.getPremises().getWidth());
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                }
                case HANDLING_UNIT_HEIGHT -> {
                    cell.setCellValue(report.getPremises().getHeight());
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                }
                case HANDLING_UNIT_DIMENSION_UNIT -> {
                    cell.setCellValue(report.getPremises().getDimensionUnit().getDisplayedName());
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                }
                case HANDLING_UNIT_WEIGHT -> {
                    cell.setCellValue(report.getPremises().getWeight());
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                }
                case HANDLING_UNIT_DIMENSION_WEIGHT -> {
                    cell.setCellValue(report.getPremises().getWeightUnit().getDisplayedName());
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                }
                case HANDLING_UNIT_PIECES -> {
                    cell.setCellValue(report.getPremises().getHuUnitCount());
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                }
                case HANDLING_UNIT_MIXED -> {
                    cell.setCellValue(report.getPremises().getMixable() ? "Yes" : "No");
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                }
                case DESTINATION_ROUTE -> {
                    var dest = getDestination(destinationIdx);

                    if (dest == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(getRoute(dest.getSections()));
                    }
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                }
                case DESTINATION_ANNUAL_QUANTITY -> {
                    var dest = getDestination(destinationIdx);

                    if (dest == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(dest.getAnnualQuantity());
                    }
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.NEUTRAL);

                }
                case DESTINATION_TRANSPORT_TIME -> {
                    var dest = getDestination(destinationIdx);

                    if (dest == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(dest.getTransportTime());
                    }
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.NEUTRAL);
                }
                case DESTINATION_CONTAINER_LAYER -> {
                    var dest = getDestination(destinationIdx);

                    if (dest == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(dest.getLayer());
                    }
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.NEUTRAL);

                }
                case DESTINATION_CONTAINER_UNIT_COUNT -> {
                    var dest = getDestination(destinationIdx);

                    if (dest == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(dest.getUnitCount().intValue() * report.getPremises().getHuUnitCount());
                    }
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.NEUTRAL);

                }
                case DESTINATION_CONTAINER_TYPE -> {
                    var dest = getDestination(destinationIdx);

                    if (dest == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(dest.getType().getDescription());
                    }
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.NEUTRAL);

                }
                case DESTINATION_CONTAINER_LIMIT -> {
                    var dest = getDestination(destinationIdx);

                    if (dest == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(dest.getWeightExceeded() ? "Weight" : "Volume");
                    }
                    selectedStyle = this.styles.get(CellStyleProvider.TextFormat.TEXT_ONLY).get(CellStyleProvider.PredefinedColors.NEUTRAL);

                }
            }

            return selectedStyle;
        }

        private String getRoute(List<ReportSectionDTO> sections) {

            if (sections == null || sections.isEmpty())
                return "";

            var firstNode = sections.getFirst().getFromNode();

            StringBuilder route = new StringBuilder(firstNode.getExternalMappingId() == null ? shortened(firstNode.getName()) : firstNode.getExternalMappingId());

            sections.forEach(s -> {
                route.append(" > ");
                route.append(s.getToNode().getExternalMappingId());
            });

            return route.toString();
        }

        private String shortened(String name) {
            if (name == null || name.length() < 10)
                return name;

            return name.substring(0, 7) + "...";
        }

        private ReportDestinationDTO getDestination(int destinationIdx) {
            if (destinationIdx == -1 || destinations.size() <= destinationIdx)
                return null;

            var node = destinations.get(destinationIdx);

            if (node == null) return null;

            return report.getDestinations().stream().filter(d -> Objects.equals(d.getDestination().getId(), node.getId())).findFirst().orElse(null);

        }

    }

    private record SummaryMapper(ReportDTO report,
                                 Map<CellStyleProvider.TextFormat, Map<CellStyleProvider.PredefinedColors, CellStyle>> styles) {

        public void map(Sheet sheet, int rowIdx) {
            Row row = sheet.createRow(rowIdx);

            createStringCell(row, ReportSummaryHeader.MATERIAL, report.getMaterial().getPartNumber());
            createStringCell(row, ReportSummaryHeader.SUPPLIER, report.getSupplier().getName());

            var currencyStyle = styles.get(CellStyleProvider.TextFormat.CURRENCY);
            var percentStyle = styles.get(CellStyleProvider.TextFormat.PERCENTAGE);

            createSplitCell(row, ReportSummaryHeader.MEK_A, report.getOverview().get("mek_a"), currencyStyle.get(CellStyleProvider.PredefinedColors.NEUTRAL), percentStyle.get(CellStyleProvider.PredefinedColors.NEUTRAL));
            createSplitCell(row, ReportSummaryHeader.LOGISTICS_COST, report.getOverview().get("logistics"), currencyStyle.get(CellStyleProvider.PredefinedColors.LIGHT_BLUE_1), percentStyle.get(CellStyleProvider.PredefinedColors.LIGHT_BLUE_2));
            createSplitCell(row, ReportSummaryHeader.MEK_B, report.getOverview().get("mek_b"), currencyStyle.get(CellStyleProvider.PredefinedColors.GREEN_1), percentStyle.get(CellStyleProvider.PredefinedColors.GREEN_2));

            createNumberCell(row, ReportSummaryHeader.TRANSPORT, report.getCost().get("transport").getTotal(), currencyStyle.get(CellStyleProvider.PredefinedColors.NEUTRAL));
            createNumberCell(row, ReportSummaryHeader.HANDLING, report.getCost().get("handling").getTotal(), currencyStyle.get(CellStyleProvider.PredefinedColors.NEUTRAL));
            createNumberCell(row, ReportSummaryHeader.STORAGE, report.getCost().get("storage").getTotal(), currencyStyle.get(CellStyleProvider.PredefinedColors.NEUTRAL));
            createNumberCell(row, ReportSummaryHeader.REPACKAGING, report.getCost().get("repacking").getTotal(), currencyStyle.get(CellStyleProvider.PredefinedColors.NEUTRAL));
            createNumberCell(row, ReportSummaryHeader.DISPOSAL, report.getCost().get("disposal").getTotal(), currencyStyle.get(CellStyleProvider.PredefinedColors.NEUTRAL));
            createNumberCell(row, ReportSummaryHeader.CAPITAL, report.getCost().get("capital").getTotal(), currencyStyle.get(CellStyleProvider.PredefinedColors.NEUTRAL));
            createNumberCell(row, ReportSummaryHeader.CUSTOM, report.getCost().get("custom").getTotal(), currencyStyle.get(CellStyleProvider.PredefinedColors.NEUTRAL));
            createNumberCell(row, ReportSummaryHeader.FCA_FEE, report.getCost().get("fca_fees").getTotal(), currencyStyle.get(CellStyleProvider.PredefinedColors.NEUTRAL));

            if (report.getCost().containsKey("air_freight_cost"))
                createNumberCell(row, ReportSummaryHeader.AIR_FREIGHT, report.getCost().get("air_freight_cost").getTotal(), currencyStyle.get(CellStyleProvider.PredefinedColors.NEUTRAL));
            else createStringCell(row, ReportSummaryHeader.AIR_FREIGHT, "-");

        }

        private void createSplitCell(Row row, ReportSummaryHeader header, ReportEntryDTO value, CellStyle cellStyleHighlight1, CellStyle cellStyleHighlight2) {
            Cell cell = row.createCell(header.getColumn());
            cell.setCellValue(value.getTotal().doubleValue());
            cell.setCellStyle(cellStyleHighlight1);

            Cell percentageCell = row.createCell(header.getColumn() + 1);
            percentageCell.setCellValue(value.getPercentage().doubleValue());
            percentageCell.setCellStyle(cellStyleHighlight2);
        }

        private void createStringCell(Row row, ReportSummaryHeader header, String value) {
            Cell cell = row.createCell(header.getColumn());
            cell.setCellValue(value);
        }

        private void createNumberCell(Row row, ReportSummaryHeader header, Number value, CellStyle style) {
            Cell cell = row.createCell(header.getColumn());
            cell.setCellValue(value.doubleValue());
            cell.setCellStyle(style);
        }


    }
}
