package com.napa.pulse.service.impl;

import com.google.gson.Gson;
import com.napa.pulse.dao.interfaces.HubSpokeNetworkDAO;
import com.napa.pulse.dto.*;
import com.napa.pulse.entity.pulseui.HierarchyItem;
import com.napa.pulse.entity.security.User;
import com.napa.pulse.service.interfaces.HubSpokeNetworkService;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.*;

@Service
public class HubSpokeNetworkServiceImpl implements HubSpokeNetworkService {

    private final Gson gson = new Gson();

    @Autowired
    private HubSpokeNetworkDAO networkDAO;

    @Override
    public HubSpokeNetworkActionDTO createNewNetwork( HubSpokeNetworkDTO hubSpokeNetworkDTO, Integer userId) {
        return networkDAO.createNewNetwork(hubSpokeNetworkDTO,userId);
    }

    @Override
    public Map<Long,List<HubSpokeNetworkDTO>> createNewProduct( String productName, Integer hubId, Integer networkId, Integer userId, Integer roleId) {
        return networkDAO.createNewProduct(productName, hubId, networkId, userId, roleId);
    }

    @Override
    public ActionResult updateNetwork(HubSpokeCreateRequestDto createRequestDto,Integer userId) {
        return networkDAO.updateNetwork(createRequestDto,userId);
    }

    @Override
    public List<HubSpokeNetworkDTO> getNetwork(HubSpokeWidgetData hubSpokeWidgetData) {
        return networkDAO.getNetwork(hubSpokeWidgetData);
    }

    @Override
    public List<HubSpokeNetworkDTO> getNetworkById(Integer networkId, User user) {
        return networkDAO.getNetworkById(networkId, user);
    }

    @Override
    public ActionResult deleteNetwork(Integer networkId,Integer userId){
         return networkDAO.deleteNetwork(networkId,userId);
    }

    @Override
    public ActionResult renameNetwork(HubSpokeCreateRequestDto createRequestDto,Integer userId){
        return networkDAO.renameNetwork(createRequestDto,userId);
    }

    @Override
    public ProductHubDTO createHubSpoke(Integer networkID, Integer siteID){
        return networkDAO.createHubSpoke(networkID,siteID);
    }

    @Override
    public ActionResult createHubProduct(ProductHubDTO dto){
        return  networkDAO.createHubProduct(dto);
    }

    @Override
    public ActionResult updateHubProduct(ProductHubDTO hubDTO) {
        return networkDAO.updateHubProduct(hubDTO);
    }

    @Override
    public List<Integer> getHierarchyId(Integer hubSiteId, Integer productMapId) {
        return networkDAO.getHierarchyId(hubSiteId,productMapId);
    }

    @Override
    public Integer getThresholdValue(Integer hubSiteId, Integer productMapId) {
        return networkDAO.getThresholdValue(hubSiteId,productMapId);
    }

    @Override
    public List<Integer> getHierarchyIdForOtherSites(Integer networkID, String hubSiteId, Integer productMapId) {
        String[] hubSiteIdArray = null;
        if (StringUtils.isNotBlank(hubSiteId)) {
            hubSiteIdArray = gson.fromJson(hubSiteId, String[].class);
        }
        return networkDAO.getHierarchyIdForOtherSites(networkID,Arrays.asList(hubSiteIdArray), productMapId);
    }

    @Override
    public ActionResult updateHierarchyId(Integer networkId, Integer siteId, Integer hubSiteId,
                                          Integer productMapId, Integer supplierNo, Integer userId,
                                          String productIds, String newProductIds, Integer selectedThresholdValue, Boolean isDiffDemThre) {
        String[] pIdArray = null;
        if (StringUtils.isNotBlank(productIds)) {
            pIdArray = gson.fromJson(productIds, String[].class);
        }
        Integer[] newlyAddedIds = null;
        if (StringUtils.isNotBlank(newProductIds)) {
            newlyAddedIds = gson.fromJson(newProductIds, Integer[].class);
        }
        return networkDAO.updateHierarchyId(networkId,siteId, hubSiteId, productMapId, supplierNo, userId, Arrays.asList(pIdArray),
                Arrays.asList(newlyAddedIds), selectedThresholdValue, isDiffDemThre);
    }

    @Override
    public ActionResultItem<Map<Integer, HierarchyItem>> getSelectedProductTree(Integer networkHubId, Integer productMapId,  User user) {
        return new ActionResultItem<>(networkDAO.getSelectedProductTree(networkHubId, productMapId, user));
    }

    @Override
    public ActionResultItem<Map<Integer, HierarchyItem>> getSelectedProductTreeForOtherSites(Integer networkId, Integer productMapId, String hubSiteId, User user) {
        return new ActionResultItem<>(networkDAO.getSelectedProductTreeForOtherSites(networkId, productMapId, hubSiteId, user));
    }

    @Override
    public List<Integer> getThresholdValues(){
        return  networkDAO.getThresholdValues();
    }

    @Override
    public HubSliderDto getProductHierarchies(Integer siteId){
        return  networkDAO.getProductHierarchies(siteId);
    }

    @Override
    public ActionResult updateThresholdValues(DemandthresholdDTO dto){
        return networkDAO.updateThresholdValues(dto);
    }

    @Override
    public List<VendorDataDTO> getVendorSupplierDesc()
    {
        return networkDAO.distinctVendorList();
    }

    @Override
    public List<Long> getProductIdsList(Integer supplierNumber, Integer siteId) {
        return networkDAO.productIdsList(supplierNumber, siteId);
    }

    @Override
    public  HubSpokeSitesDTO getHubSpokeFlags(Integer sessionId,Integer productId, Integer userId){
        return networkDAO.getHubSpokeFlags(sessionId,productId, userId);
    }

    @Override
    public void hubSiteDataExportXL(Long networkId, HttpServletResponse res) throws IOException {
        SqlRowSet rS = null;

        final String[] columns = {"Hub Site", "Product Map", "Vendor", "Created By", "Created On", "L1","L2", "L3", "L4", "L4 Description", "Demand Threshold"};

        HashMap<String, SqlRowSet> map = networkDAO.hubSiteDataExport(networkId);

        res.setHeader("Content-Disposition", "attachment;filename=\"HubSiteData.xlsx\"");
        res.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

        SXSSFWorkbook workbook = new SXSSFWorkbook();
        for(Map.Entry<String, SqlRowSet> m : map.entrySet()){
            rS = m.getValue();

            SXSSFSheet sheet = workbook.createSheet(m.getKey());
            sheet.trackAllColumnsForAutoSizing();
            CreationHelper creationHelper = workbook.getCreationHelper();

            Font headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerFont.setFontHeightInPoints((short) 14);

            CellStyle headerCellStyle = workbook.createCellStyle();
            headerCellStyle.setFont(headerFont);
            headerCellStyle.setWrapText(true);
            headerCellStyle.setAlignment(HorizontalAlignment.CENTER);

            CellStyle dateFormat = workbook.createCellStyle();
            dateFormat.setDataFormat(creationHelper.createDataFormat().getFormat("dd-mmm-yyyy"));

            Row headerRow = sheet.createRow(0);

            for(int i = 0; i < columns.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(columns[i]);
                cell.setCellStyle(headerCellStyle);
            }
            sheet.setAutoFilter(new CellRangeAddress(0, 0, 0, columns.length - 1));
            int rowNum = 1;
            boolean hubSite=true;
            while (rS.next())
            {
                Row row = sheet.createRow(rowNum++);
                Cell cell;
                while(hubSite) {
                    row.createCell(0).setCellValue(m.getKey());
                    hubSite=false;
                }
                row.createCell(1).setCellValue(rS.getString("productMap"));
                row.createCell(2).setCellValue(rS.getString("vendor"));
                row.createCell(3).setCellValue(rS.getString("createdBy"));
                cell = row.createCell(4);
                cell.setCellValue(rS.getDate("createdDate"));
                cell.setCellStyle(dateFormat);
                row.createCell(5).setCellValue(rS.getString("l1"));
                row.createCell(6).setCellValue(rS.getString("l2"));
                row.createCell(7).setCellValue(rS.getString("l3"));
                row.createCell(8).setCellValue(rS.getString("l4"));
                row.createCell(9).setCellValue(rS.getString("l4des"));
                row.createCell(10).setCellValue(rS.getString("demandThreshold"));
            }
            for(int i = 0; i < columns.length; i++) {
                sheet.autoSizeColumn(i);
            }
        }
        workbook.write(res.getOutputStream());
        res.getOutputStream().flush();
        workbook.close();
        workbook.dispose();


    }

    @Override
    public List<HubSpokeNetworkDTO> deleteProductMap(Integer networkId, Integer hubSiteId, Integer productMapId, Integer siteId, Integer userId, Integer roleId) {
        return networkDAO.deleteProductMap(networkId, hubSiteId, productMapId, siteId, userId, roleId);
    }

    @Override
    public List<HubSpokeNetworkDTO> renameProductMap(Integer networkId, Integer hubSiteId, Integer productMapId, String newProductMapName, Integer userId, Integer roleId) {
        return networkDAO.renameProductMap(networkId, hubSiteId, productMapId, newProductMapName, userId, roleId);
    }

    @Override
    public  List<Integer> getAllNetworkSites(){
        return networkDAO.getAllNetworkSites();
    }

    @Override
    public ActionResult updateReturnToHub ( ReturntoHubDTO dto ) {
        return networkDAO.updateReturnToHub(dto);
    }

    @Override
    public List<Integer> getReturnToHubValues ( ) {
        return  networkDAO.getReturnToHubValues();
    }

    @Override
    public List<String> getAllNetworkNames ( ) {
        return networkDAO.getAllNetworkNames();
    }

    @Override
    public List<HashMap<Integer , List<Integer>>> getSpokeSiteIds ( List<Integer> hubSiteId ) {
        return networkDAO.getSpokeSiteIds(hubSiteId);

    }
}