package de.avatic.lcc.service.excelMapper;

import de.avatic.lcc.dto.generic.TransportType;
import de.avatic.lcc.model.bulk.ContainerRateHeader;
import de.avatic.lcc.model.bulk.HiddenNodeHeader;
import de.avatic.lcc.model.bulk.HiddenTableType;
import de.avatic.lcc.model.rates.ContainerRate;
import de.avatic.lcc.repositories.NodeRepository;
import de.avatic.lcc.repositories.rates.ContainerRateRepository;
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.ArrayList;
import java.util.List;

@Service
public class ContainerRateExcelMapper {

    private final HeaderGenerator headerGenerator;
    private final ContainerRateRepository containerRateRepository;
    private final NodeRepository nodeRepository;
    private final ConstraintGenerator constraintGenerator;

    public ContainerRateExcelMapper(HeaderGenerator headerGenerator, ContainerRateRepository containerRateRepository, NodeRepository nodeRepository, ConstraintGenerator constraintGenerator) {
        this.headerGenerator = headerGenerator;
        this.containerRateRepository = containerRateRepository;
        this.nodeRepository = nodeRepository;
        this.constraintGenerator = constraintGenerator;
    }

    public void fillSheet(Sheet sheet, CellStyle headerStyle, Integer periodId) {
        headerGenerator.generateHeader(sheet, ContainerRateHeader.class, headerStyle);
        containerRateRepository.listAllRatesByPeriodId(periodId).forEach(rate -> mapToRow(rate, sheet.createRow(sheet.getLastRowNum()+1)));
    }

    private void mapToRow(ContainerRate rate, Row row) {
        row.createCell(ContainerRateHeader.FROM_NODE.ordinal()).setCellValue(nodeRepository.getById(rate.getFromNodeId()).orElseThrow().getExternalMappingId());
        row.createCell(ContainerRateHeader.TO_NODE.ordinal()).setCellValue(nodeRepository.getById(rate.getToNodeId()).orElseThrow().getExternalMappingId());
        row.createCell(ContainerRateHeader.CONTAINER_RATE_TYPE.ordinal()).setCellValue(rate.getType().name());
        row.createCell(ContainerRateHeader.RATE_FEU.ordinal()).setCellValue(rate.getRateFeu().doubleValue());
        row.createCell(ContainerRateHeader.RATE_TEU.ordinal()).setCellValue(rate.getRateTeu().doubleValue());
        row.createCell(ContainerRateHeader.RATE_HC.ordinal()).setCellValue(rate.getRateHc().doubleValue());
        row.createCell(ContainerRateHeader.LEAD_TIME.ordinal()).setCellValue(rate.getLeadTime());
    }

    public void createConstraints(Workbook workbook, Sheet sheet) {

        var namedRange = constraintGenerator.createReference(workbook, HiddenNodeHeader.MAPPING_ID.ordinal(),HiddenTableType.NODE_HIDDEN_TABLE);

        constraintGenerator.createFormulaListConstraint(sheet, ContainerRateHeader.FROM_NODE.ordinal(), namedRange.getNameName());
        constraintGenerator.createFormulaListConstraint(sheet, ContainerRateHeader.TO_NODE.ordinal(), namedRange.getNameName());

        constraintGenerator.createEnumConstraint(sheet, ContainerRateHeader.CONTAINER_RATE_TYPE.ordinal(), TransportType.class);
        constraintGenerator.createDecimalConstraint(sheet, ContainerRateHeader.RATE_FEU.ordinal(), 0.0, 1000000.0);
        constraintGenerator.createDecimalConstraint(sheet, ContainerRateHeader.RATE_TEU.ordinal(),0.0, 1000000.0);
        constraintGenerator.createDecimalConstraint(sheet, ContainerRateHeader.RATE_HC.ordinal(),0.0, 1000000.0);

        constraintGenerator.createIntegerConstraint(sheet, ContainerRateHeader.LEAD_TIME.ordinal(), 0, 365);

    }

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

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

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

        entity.setFromNodeId(nodeRepository.getByExternalMappingId(row.getCell(ContainerRateHeader.FROM_NODE.ordinal()).getStringCellValue()).orElseThrow().getId());
        entity.setFromNodeId(nodeRepository.getByExternalMappingId(row.getCell(ContainerRateHeader.TO_NODE.ordinal()).getStringCellValue()).orElseThrow().getId());
        entity.setType(TransportType.valueOf(row.getCell(ContainerRateHeader.CONTAINER_RATE_TYPE.ordinal()).getStringCellValue()));
        entity.setLeadTime(Double.valueOf(row.getCell(ContainerRateHeader.LEAD_TIME.ordinal()).getNumericCellValue()).intValue());
        entity.setType(TransportType.valueOf(row.getCell(ContainerRateHeader.CONTAINER_RATE_TYPE.ordinal()).getStringCellValue()));
        entity.setRateFeu(BigDecimal.valueOf(row.getCell(ContainerRateHeader.RATE_FEU.ordinal()).getNumericCellValue()));
        entity.setRateTeu(BigDecimal.valueOf(row.getCell(ContainerRateHeader.RATE_TEU.ordinal()).getNumericCellValue()));
        entity.setRateHc(BigDecimal.valueOf(row.getCell(ContainerRateHeader.RATE_HC.ordinal()).getNumericCellValue()));

        return entity;
    }
}
