package de.avatic.lcc.service.bulk.helper;

import de.avatic.lcc.model.bulk.HiddenTableType;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.stereotype.Service;

import java.util.EnumSet;

@Service
public class ConstraintGenerator {


    public void createBooleanConstraint(Sheet sheet, Integer columnIdx) {
        createConstraint(sheet, columnIdx, new String[]{"true", "false"});
    }

    public <T extends Enum<T>> void createEnumConstraint(Sheet sheet, Integer columnIdx, Class<T> values) {
        createConstraint(sheet, columnIdx, EnumSet.allOf(values).stream().map(Enum::name).toArray(String[]::new));
    }

    private void createConstraint(Sheet sheet, Integer columnIdx, String[] values) {
        var helper = sheet.getDataValidationHelper();
        var constraint = helper.createExplicitListConstraint(values);

        var validation = helper.createValidation(constraint, new CellRangeAddressList(1, SpreadsheetVersion.EXCEL2007.getMaxRows(), columnIdx, columnIdx));
        validation.createErrorBox("Invalid Value", values.length > 8 ? "Please check dropdown for allowed values." : String.format("Allowed values are: %s.", String.join(", ", values)));
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);
    }

    public void createFormulaListConstraint(Sheet sheet, Integer columnIdx, String namedReference) {
        var helper = sheet.getDataValidationHelper();
        var constraint = helper.createFormulaListConstraint(namedReference);

        var validation = helper.createValidation(constraint, new CellRangeAddressList(1, SpreadsheetVersion.EXCEL2007.getMaxRows(), columnIdx, columnIdx));
        validation.createErrorBox("Invalid Value", "Please check dropdown for allowed values.");
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);
    }

    public void createLengthConstraint(Sheet sheet, Integer columnIdx, Integer min, Integer max) {
        var helper = sheet.getDataValidationHelper();
        var constraint = helper.createTextLengthConstraint(DataValidationConstraint.OperatorType.BETWEEN, String.valueOf(min), String.valueOf(max));

        var validation = helper.createValidation(constraint, new CellRangeAddressList(1, SpreadsheetVersion.EXCEL2007.getMaxRows(), columnIdx, columnIdx));
        validation.createErrorBox("Invalid Value", String.format("Allowed length is between %d and %d characters.", min, max));
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);
    }

    public void createDecimalConstraint(Sheet sheet, Integer columnIdx, Number min, Number max) {
        var helper = sheet.getDataValidationHelper();
        var constraint = helper.createDecimalConstraint(DataValidationConstraint.OperatorType.BETWEEN, String.valueOf(min), String.valueOf(max));

        var validation = helper.createValidation(constraint, new CellRangeAddressList(1, SpreadsheetVersion.EXCEL2007.getMaxRows(), columnIdx, columnIdx));
        validation.createErrorBox("Invalid Value", String.format("Allowed value is between %f and %f.", min.floatValue(), max.floatValue()));
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);
    }

    public void createIntegerConstraint(Sheet sheet, Integer columnIdx, Number min, Number max) {
        var helper = sheet.getDataValidationHelper();
        var constraint = helper.createIntegerConstraint(DataValidationConstraint.OperatorType.BETWEEN, String.valueOf(min), String.valueOf(max));

        var validation = helper.createValidation(constraint, new CellRangeAddressList(1, SpreadsheetVersion.EXCEL2007.getMaxRows(), columnIdx, columnIdx));
        validation.createErrorBox("Invalid Value", String.format("Allowed value is between %f and %f.", min.floatValue(), max.floatValue()));
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);
    }

    public Name createReference(Workbook workbook, Integer columnIdx, HiddenTableType hiddenTableType ) {
        Name namedRange = workbook.createName();
        namedRange.setNameName(hiddenTableType.getReferenceName());
        String reference = hiddenTableType.getSheetName()+ "!$"+toExcelLetter(columnIdx)+"$1:$"+toExcelLetter(columnIdx)+"$" + SpreadsheetVersion.EXCEL2007.getMaxRows();
        namedRange.setRefersToFormula(reference);
        return namedRange;
    }

    private String toExcelLetter(int columnIdx) {
        int digit = columnIdx % 26;
        int quotient = columnIdx / 26;
        return (quotient==0 ? "" : toExcelLetter(quotient)) + (char) ('A' + digit);
    }
}
