package de.avatic.lcc.service.excelMapper;

import de.avatic.lcc.excelmodel.ExcelNode;
import de.avatic.lcc.model.bulk.HiddenCountryHeader;
import de.avatic.lcc.model.bulk.HiddenTableType;
import de.avatic.lcc.model.bulk.NodeHeader;
import de.avatic.lcc.model.country.Country;
import de.avatic.lcc.model.country.IsoCode;
import de.avatic.lcc.model.nodes.Node;
import de.avatic.lcc.repositories.NodeRepository;
import de.avatic.lcc.repositories.country.CountryRepository;
import de.avatic.lcc.service.bulk.helper.ConstraintGenerator;
import de.avatic.lcc.service.bulk.helper.HeaderGenerator;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class NodeExcelMapper {


    private final HeaderGenerator headerGenerator;
    private final NodeRepository nodeRepository;
    private final CountryRepository countryRepository;
    private final ConstraintGenerator constraintGenerator;

    public NodeExcelMapper(HeaderGenerator headerGenerator, NodeRepository nodeRepository, CountryRepository countryRepository, ConstraintGenerator constraintGenerator) {
        this.headerGenerator = headerGenerator;
        this.nodeRepository = nodeRepository;
        this.countryRepository = countryRepository;
        this.constraintGenerator = constraintGenerator;
    }

    public void fillSheet(Sheet sheet, CellStyle headerStyle)
    {
        headerGenerator.generateHeader(sheet, NodeHeader.class, headerStyle);
        nodeRepository.listAllNodes(false).forEach(node -> mapToRow(node, sheet.createRow(sheet.getLastRowNum()+1)));
    }

    private void mapToRow(Node node, Row row) {
        row.createCell(NodeHeader.MAPPING_ID.ordinal()).setCellValue(node.getExternalMappingId());
        row.createCell(NodeHeader.NAME.ordinal()).setCellValue(node.getName());
        row.createCell(NodeHeader.ADDRESS.ordinal()).setCellValue(node.getAddress());
        row.createCell(NodeHeader.COUNTRY.ordinal()).setCellValue(countryRepository.getById(node.getCountryId()).orElseThrow().getIsoCode().getCode());
        row.createCell(NodeHeader.GEO_LATITUDE.ordinal()).setCellValue(node.getGeoLat().doubleValue());
        row.createCell(NodeHeader.GEO_LONGITUDE.ordinal()).setCellValue(node.getGeoLng().doubleValue());
        row.createCell(NodeHeader.IS_ORIGIN.ordinal()).setCellValue(node.getSource());
        row.createCell(NodeHeader.IS_INTERMEDIATE.ordinal()).setCellValue(node.getIntermediate());
        row.createCell(NodeHeader.IS_DESTINATION.ordinal()).setCellValue(node.getDestination());
        row.createCell(NodeHeader.OUTBOUND_COUNTRIES.ordinal()).setCellValue(mapOutboundCountriesToCell(node.getOutboundCountries()));
        row.createCell(NodeHeader.PREDECESSOR_NODES.ordinal()).setCellValue(mapChains(node.getNodePredecessors()));
        row.createCell(NodeHeader.IS_PREDECESSOR_MANDATORY.ordinal()).setCellValue(node.getPredecessorRequired());
    }

    private String mapChains(List<Map<Integer, Integer>> chains) {
        return chains.stream().map(this::mapChain).collect(Collectors.joining(", "));
    }

    private String mapChain(Map<Integer, Integer> chain) {
        var seq = new ArrayList<>(chain.keySet());
        Collections.sort(seq);
        return seq.stream().map(s -> nodeRepository.getById(chain.get(s)).orElseThrow().getExternalMappingId()).collect(Collectors.joining(", "));
    }

    private String mapOutboundCountriesToCell(Collection<Integer> outboundCountryIds) {
        return outboundCountryIds.stream().map(countryRepository::getById).filter(Optional::isPresent).map(Optional::get).map(Country::getIsoCode).map(IsoCode::getCode).collect(Collectors.joining(", "));
    }

    public void createConstraints(Workbook workbook, Sheet sheet) {
        var namedRange = constraintGenerator.createReference(workbook, HiddenCountryHeader.ISO_CODE.ordinal(), HiddenTableType.COUNTRY_HIDDEN_TABLE);

        constraintGenerator.createFormulaListConstraint(sheet, NodeHeader.COUNTRY.ordinal(), namedRange.getNameName());
        constraintGenerator.createDecimalConstraint(sheet, NodeHeader.GEO_LATITUDE.ordinal(), -90.0, 90.0);
        constraintGenerator.createDecimalConstraint(sheet, NodeHeader.GEO_LONGITUDE.ordinal(), -180.0, 180.0);
        constraintGenerator.createBooleanConstraint(sheet, NodeHeader.IS_ORIGIN.ordinal());
        constraintGenerator.createBooleanConstraint(sheet, NodeHeader.IS_INTERMEDIATE.ordinal());
        constraintGenerator.createBooleanConstraint(sheet, NodeHeader.IS_DESTINATION.ordinal());
        constraintGenerator.createBooleanConstraint(sheet, NodeHeader.IS_PREDECESSOR_MANDATORY.ordinal());
        constraintGenerator.createLengthConstraint(sheet, NodeHeader.ADDRESS.ordinal(), 1, 500);
        constraintGenerator.createLengthConstraint(sheet, NodeHeader.NAME.ordinal(), 1, 255);

    }

    public List<ExcelNode> extractSheet(Sheet sheet) {
        if(!headerGenerator.validateHeader(sheet, NodeHeader.class)) return null;

        var nodes = new ArrayList<ExcelNode>();
        sheet.forEach(row -> nodes.add(mapToEntity(row)));
        return nodes;
    }

    private ExcelNode mapToEntity(Row row) {
        ExcelNode entity = new ExcelNode();

        entity.setExternalMappingId(row.getCell(NodeHeader.MAPPING_ID.ordinal()).getStringCellValue());
        entity.setName(row.getCell(NodeHeader.NAME.ordinal()).getStringCellValue());
        entity.setAddress(row.getCell(NodeHeader.ADDRESS.ordinal()).getStringCellValue());
        entity.setCountryId(IsoCode.valueOf(row.getCell(NodeHeader.COUNTRY.ordinal()).getStringCellValue()));
        entity.setGeoLat(BigDecimal.valueOf(row.getCell(NodeHeader.GEO_LATITUDE.ordinal()).getNumericCellValue()));
        entity.setGeoLng(BigDecimal.valueOf(row.getCell(NodeHeader.GEO_LONGITUDE.ordinal()).getNumericCellValue()));
        entity.setSource(row.getCell(NodeHeader.IS_ORIGIN.ordinal()).getBooleanCellValue());
        entity.setIntermediate(row.getCell(NodeHeader.IS_INTERMEDIATE.ordinal()).getBooleanCellValue());
        entity.setDestination(row.getCell(NodeHeader.IS_DESTINATION.ordinal()).getBooleanCellValue());
        entity.setPredecessorRequired(row.getCell(NodeHeader.IS_PREDECESSOR_MANDATORY.ordinal()).getBooleanCellValue());
        entity.setNodePredecessors(mapChainsFromCell(row.getCell(NodeHeader.PREDECESSOR_NODES.ordinal()).getStringCellValue()));
        entity.setOutboundCountries(mapOutboundCountriesFromCell(row.getCell(NodeHeader.OUTBOUND_COUNTRIES.ordinal()).getStringCellValue()));

        return entity;
    }

    private List<String> mapOutboundCountriesFromCell(String outboundCountryIds) {
        if(outboundCountryIds == null || outboundCountryIds.isBlank()) return Collections.emptyList();
        return Arrays.stream(outboundCountryIds.split(",")).map(String::trim).toList();
    }

    private List<List<String>> mapChainsFromCell(String cell) {
        if(cell == null || cell.isBlank()) return Collections.emptyList();
        return Arrays.stream(cell.split(";")).map(String::trim).map(this::mapChainFromCell).toList();
    }

    private List<String> mapChainFromCell(String chain) {
        return Arrays.stream(chain.split(",")).map(String::trim).toList();
    }

}
