package be.mentoringsystems.merke.importer.excel;

import be.mentoringsystems.merke.importer.VendorImporter;
import be.mentoringsystems.merke.model.db.Login;
import be.mentoringsystems.merke.model.db.Vendor;
import be.mentoringsystems.merke.model.db.Vendorstall;
import be.mentoringsystems.merke.service.LoginService;
import be.mentoringsystems.merke.service.VendorService;
import be.mentoringsystems.merke.service.VendorstallService;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import org.apache.commons.lang3.StringUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellReference;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

/**
 *
 * @author anthonyarents
 */
@Component
public class VendorImporterImpl implements VendorImporter {

    @Autowired
    private transient VendorService vendorService;
    @Autowired
    private transient VendorstallService vendorstallService;
    @Autowired
    private transient LoginService loginService;
    
    private static final Logger LOGGER = LogManager.getLogger(VendorImporterImpl.class);

    @Override
    public void importFile(File file, final UUID fileId) {
        try {
            //InputStream inp = new FileInputStream("workbook.xlsx");
            final Workbook wb = WorkbookFactory.create(file);
            final Sheet sheet = wb.getSheetAt(0); // only first sheet

            final List<String> columnHeaders = new ArrayList<String>();
            for (Row row : sheet) {
                if (columnHeaders.isEmpty()) {
                    readColumnHeaders(row, columnHeaders);
                } else {
                    mapRow(row, columnHeaders, fileId);
                }
            }

        } catch (IOException ex) {
            LOGGER.error(ex.getMessage(), ex);
        } catch (InvalidFormatException ex) {
            LOGGER.error(ex.getMessage(), ex);
        } catch (EncryptedDocumentException ex) {
            LOGGER.error(ex.getMessage(), ex);
        }
    }
    
    @SuppressWarnings("deprecation")
    private Object getCellContent(final Cell cell) {
        // Alternatively, get the value and format it yourself
        switch (cell.getCellTypeEnum()) {
            case STRING:
                return cell.getRichStringCellValue().getString();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                } else {
                    return cell.getNumericCellValue();
                }
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            default:
                return null;
        }
    }
    
    private String getAsString(final Object obj) {
        if(obj == null) return null;
        if(obj instanceof String) return (String) obj;
        return obj.toString();
    }
    
    private void mapVendorAndVendorstall(final List<String> columnHeaders, final List<Object> values, final UUID fileId) {
        LOGGER.info("--- mapping to vendor and vendorstall");
        final Vendor vendor = new Vendor();
        final Vendorstall vendorstall = new Vendorstall();
        final Login login = new Login();
        
        vendor.setTitle((String) values.get(0));
        vendor.setTitle2((String) values.get(1));
        vendor.setFirstname((String) values.get(2));
        vendor.setLastname((String) values.get(3));
        vendor.setCompanyName((String) values.get(4));
        vendor.setStreet((String) values.get(5));
        Object zipcodeObj = values.get(6);
        if(zipcodeObj instanceof String) {
            vendor.setPostalcode((String) zipcodeObj);
        } else {
            Double zipcode = (Double) values.get(6);
            vendor.setPostalcode(Integer.toString(zipcode.intValue()));
        }
        vendor.setCity((String) values.get(7));
        vendor.setTelephonenumber((String) values.get(8));
        vendor.setEmail((String) values.get(9));
        vendor.setImportlistId(fileId);
        
        vendorstall.setVendor(vendor);
        
        StringBuilder unabletoimport = new StringBuilder();
        
        unabletoimport.append(columnHeaders.get(10)).append(": ").append(values.get(10)).append(",\n"); // 10 : koopwaar
        unabletoimport.append(columnHeaders.get(11)).append(": ").append(values.get(11)).append(",\n"); // 11 : koopwaar beschrijving
        unabletoimport.append(columnHeaders.get(12)).append(": ").append(values.get(12)).append(",\n"); // 12 : koopwaar beschrijving cbs
        unabletoimport.append(columnHeaders.get(13)).append(": ").append(values.get(13)).append(",\n"); // 13 : locatie (plaats op markt)
        unabletoimport.append(columnHeaders.get(14)).append(": ").append(values.get(14)); // 14 : stand nr
        
        vendorstall.setStalllength(getAsString(values.get(15)));
        vendorstall.setStalldepth(getAsString(values.get(16)));
        
        String vehicle = getAsString(values.get(17));
        if(!StringUtils.isEmpty(vehicle) && !"-".equals(vehicle) && !"x".equals(vehicle)) {
            vendorstall.setVehicle(true);
            unabletoimport.append(",\n").append(columnHeaders.get(17)).append(": ").append(vehicle); // 17 : marktwagen
        }
        String disselordoor = getAsString(values.get(18));
        if(!StringUtils.isEmpty(disselordoor) && !"-".equals(disselordoor) && !"x".equals(disselordoor)) {
            vendorstall.setAdze(disselordoor.contains("dss:"));
            vendorstall.setDoor(disselordoor.contains("dr:"));
            if(vendorstall.isDoor()) {
                vendorstall.setDoorlocation(disselordoor);
            }
            if(vendorstall.isAdze()) {
                vendorstall.setAdzelocation(disselordoor);
            }
        }
        
        String luifel = getAsString(values.get(19));
        if(!StringUtils.isEmpty(luifel) && !"-".equals(luifel) && !"x".equals(luifel)) {
            unabletoimport.append(",\n").append(columnHeaders.get(19)).append(": ").append(luifel); // 19 : luifel
        }
        String parasolortent = getAsString(values.get(20));
        if(!StringUtils.isEmpty(parasolortent) && !"-".equals(parasolortent) && !"x".equals(parasolortent)) {
            unabletoimport.append(",\n").append(columnHeaders.get(20)).append(": ").append(parasolortent); // 20 : parasol / tent
        }
        String wattage = getAsString(values.get(21));
        if(!StringUtils.isEmpty(wattage) && !"-".equals(wattage) && !"x".equals(wattage)) {
            vendorstall.setElectricity(true);
            vendorstall.setElectricitypower(wattage);
        }
        vendor.setCompanyNumber(getAsString(values.get(22)));
        vendor.setPermitNumber(getAsString(values.get(23)));
        
        String favv = getAsString(values.get(24));
        if(!StringUtils.isEmpty(favv) && !"-".equals(favv) && !"x".equals(favv)) {
            unabletoimport.append(",\n").append(columnHeaders.get(24)).append(": ").append(favv); // 20 : parasol / tent
        }
        vendor.setIdnrNationalRegister((String) values.get(25));
        if(StringUtils.isEmpty(vendor.getCompanyName())) {
            vendor.setCompanyName((String) values.get(26));
        }
        vendor.setBankaccount(getAsString(values.get(27)));
        // 28 : bedrag calculatie (formule)
        String kolom1 = getAsString(values.get(29));
        if(!StringUtils.isEmpty(kolom1) && !"-".equals(kolom1) && !"x".equals(kolom1)) {
            unabletoimport.append(",\n").append(columnHeaders.get(29)).append(": ").append(kolom1); // 20 : parasol / tent
        }
        String kolom2 = getAsString(values.get(30));
        if(!StringUtils.isEmpty(kolom2) && !"-".equals(kolom2) && !"x".equals(kolom2)) {
            unabletoimport.append(",\n").append(columnHeaders.get(30)).append(": ").append(kolom2); // 20 : parasol / tent
        }
        String remarks = getAsString(values.get(31));
        if(!StringUtils.isEmpty(remarks) && !"-".equals(remarks) && !"x".equals(remarks)) {
            unabletoimport.append(",\n").append(columnHeaders.get(31)).append(": ").append(remarks); // 20 : parasol / tent
        }
        
        vendorstall.setVendor(vendor);
        vendorstall.setExtra(unabletoimport.toString());
        
        login.setFirstname(vendor.getFirstname());
        login.setLastname(vendor.getLastname());
        login.setUsername(vendor.getEmail());
        if(StringUtils.isEmpty(login.getUsername())) {
            final StringBuilder sb = new StringBuilder(login.getFirstname());
            sb.append(".").append(login.getLastname());
            login.setUsername(sb.toString());
        }
        login.setPassword("temp");
        login.setActive(false);
        login.setEmail(vendor.getEmail());
        login.setGroup(2);
        loginService.save(login);
        
        vendor.setLoginId(login.getId());
        vendorService.save(vendor);
        vendorstallService.save(vendorstall);
        
        
        LOGGER.info("--- done mapping to vendor and vendorstall");
    }

    private void mapRow(final Row row, final List<String> columnHeaders, final UUID fileId) {
        Map<String, Object> result = new HashMap<String, Object>();
        final List<Object> values = new ArrayList<Object>();
        LOGGER.info("--- reading row");
        for (int cn = 0; cn < columnHeaders.size(); cn++) {
            Cell c = row.getCell(cn, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
            CellReference cr = new CellReference(row.getRowNum(), cn);
            if (c == null) {
                // The spreadsheet is empty in this cell
                values.add(null);
                LOGGER.info("{}({}) does not have a value", columnHeaders.get(cn), cr.formatAsString());
            } else {
                final Object value = getCellContent(c);
                result.put(columnHeaders.get(cn), value);
                values.add(value);
                LOGGER.info("{}({}) : {}", columnHeaders.get(cn), cr.formatAsString(), value);
            }
        }
        LOGGER.info("--- done reading row");
        
        if(values.get(0) == null) {
            LOGGER.info("--- unable to map row");
        } else {
            mapVendorAndVendorstall(columnHeaders, values, fileId);
        }
    }

    private void readColumnHeaders(Row row, List<String> columnHeaders) {
        for (Cell cell : row) {
            columnHeaders.add(cell.getStringCellValue());
        }
    }

}