Subversion Repositories SmartDukaan

package com.smartdukaan.cron.scheduled;

import com.google.auth.oauth2.GoogleCredentials;
import com.google.common.collect.Lists;
import com.google.gson.Gson;
import com.razorpay.Payment;
import com.smartdukaan.cron.itelImeiActivation.ItelImeiActivationNewService;
import com.smartdukaan.cron.itelImeiActivation.ItelImeiActivationService;
import com.smartdukaan.cron.itelImeiActivation.TecnoImeiActivation;
import com.smartdukaan.cron.migrations.RunOnceTasks;
import com.smartdukaan.cron.scheduled.ambreports.AbmReportSender;
import com.smartdukaan.cron.scheduled.runnables.PurchaseMigration;
import com.spice.profitmandi.common.enumuration.*;
import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;
import com.spice.profitmandi.common.model.*;
import com.spice.profitmandi.common.services.ReporticoService;
import com.spice.profitmandi.common.services.mandii.MandiiService;
import com.spice.profitmandi.common.util.FileUtil;
import com.spice.profitmandi.common.util.FormattingUtils;
import com.spice.profitmandi.common.util.PdfUtils;
import com.spice.profitmandi.common.util.Utils;
import com.spice.profitmandi.common.util.Utils.Attachment;
import com.spice.profitmandi.common.web.client.RestClient;
import com.spice.profitmandi.dao.Interface.Campaign;
import com.spice.profitmandi.dao.cart.CartService;
import com.spice.profitmandi.dao.cart.SmartCartService;
import com.spice.profitmandi.dao.entity.auth.AuthUser;
import com.spice.profitmandi.dao.entity.auth.PartnerCollectionRemark;
import com.spice.profitmandi.dao.entity.catalog.*;
import com.spice.profitmandi.dao.entity.catalog.Offer;
import com.spice.profitmandi.dao.entity.cs.NonSdBuying;
import com.spice.profitmandi.dao.entity.cs.Position;
import com.spice.profitmandi.dao.entity.cs.Ticket;
import com.spice.profitmandi.dao.entity.dtr.*;
import com.spice.profitmandi.dao.entity.dtr.User;
import com.spice.profitmandi.dao.entity.fofo.*;
import com.spice.profitmandi.dao.entity.inventory.*;
import com.spice.profitmandi.dao.entity.logistics.AST;
import com.spice.profitmandi.dao.entity.logistics.ASTRepository;
import com.spice.profitmandi.dao.entity.transaction.*;
import com.spice.profitmandi.dao.entity.user.*;
import com.spice.profitmandi.dao.entity.warehouse.WarehouseInventoryItem;
import com.spice.profitmandi.dao.entity.warehouse.WarehouseScan;
import com.spice.profitmandi.dao.enumuration.auth.CollectionRemark;
import com.spice.profitmandi.dao.enumuration.catalog.StockTransactionType;
import com.spice.profitmandi.dao.enumuration.cs.EscalationType;
import com.spice.profitmandi.dao.enumuration.dtr.RefferalStatus;
import com.spice.profitmandi.dao.enumuration.fofo.Gateway;
import com.spice.profitmandi.dao.enumuration.fofo.PaymentStatus;
import com.spice.profitmandi.dao.enumuration.fofo.ReturnType;
import com.spice.profitmandi.dao.enumuration.fofo.ScanType;
import com.spice.profitmandi.dao.enumuration.inventory.CatalogMovingEnum;
import com.spice.profitmandi.dao.enumuration.inventory.VendorCatalogPricingStatus;
import com.spice.profitmandi.dao.enumuration.transaction.CreditRisk;
import com.spice.profitmandi.dao.enumuration.transaction.LoanReferenceType;
import com.spice.profitmandi.dao.enumuration.transaction.PriceDropImeiStatus;
import com.spice.profitmandi.dao.enumuration.transaction.SchemePayoutStatus;
import com.spice.profitmandi.dao.model.*;
import com.spice.profitmandi.dao.repository.GenericRepository;
import com.spice.profitmandi.dao.repository.auth.AuthRepository;
import com.spice.profitmandi.dao.repository.auth.NonSdBuyingRepository;
import com.spice.profitmandi.dao.repository.auth.PartnerCollectionRemarkRepository;
import com.spice.profitmandi.dao.repository.catalog.*;
import com.spice.profitmandi.dao.repository.cs.CsService;
import com.spice.profitmandi.dao.repository.cs.PartnerRegionRepository;
import com.spice.profitmandi.dao.repository.cs.PositionRepository;
import com.spice.profitmandi.dao.repository.cs.TicketRepository;
import com.spice.profitmandi.dao.repository.dtr.*;
import com.spice.profitmandi.dao.repository.fofo.*;
import com.spice.profitmandi.dao.repository.inventory.*;
import com.spice.profitmandi.dao.repository.logistics.WarehouseProviderRepository;
import com.spice.profitmandi.dao.repository.transaction.*;
import com.spice.profitmandi.dao.repository.trialOnboarding.TrialFormRepository;
import com.spice.profitmandi.dao.repository.user.AddressRepository;
import com.spice.profitmandi.dao.repository.user.LoiFormRepository;
import com.spice.profitmandi.dao.repository.user.UserRepository;
import com.spice.profitmandi.dao.repository.warehouse.*;
import com.spice.profitmandi.dao.service.LiquidationService;
import com.spice.profitmandi.dao.service.PurSaleService;
import com.spice.profitmandi.dao.service.SidbiService;
import com.spice.profitmandi.dao.service.biuedart.BluedartAuthService;
import com.spice.profitmandi.dao.service.biuedart.BluedartService;
import com.spice.profitmandi.dao.service.solr.FofoSolr;
import com.spice.profitmandi.service.*;
import com.spice.profitmandi.service.integrations.CCAvenuePaymentService;
import com.spice.profitmandi.service.integrations.RazorpayPaymentService;
import com.spice.profitmandi.service.integrations.bharti.model.BAGInsuranceModel;
import com.spice.profitmandi.service.integrations.gstpro.GstProAuthService;
import com.spice.profitmandi.service.integrations.gstpro.GstProService;
import com.spice.profitmandi.service.integrations.pinelabs.dto.*;
import com.spice.profitmandi.service.integrations.psismart.PsiSmartService;
import com.spice.profitmandi.service.integrations.smartping.SmartPingService;
import com.spice.profitmandi.service.integrations.toffee.ToffeeService;
import com.spice.profitmandi.service.inventory.*;
import com.spice.profitmandi.service.offers.OfferService;
import com.spice.profitmandi.service.order.OrderService;
import com.spice.profitmandi.service.pricecircular.PriceCircularService;
import com.spice.profitmandi.service.pricing.PriceDropService;
import com.spice.profitmandi.service.recharge.provider.OxigenRechargeProviderService;
import com.spice.profitmandi.service.recharge.provider.ThinkWalnutDigitalRechargeProviderService;
import com.spice.profitmandi.service.scheme.SchemeService;
import com.spice.profitmandi.service.serviceConfig.ServiceConfigService;
import com.spice.profitmandi.service.transaction.CreditBlockedExceptionPartnersRepository;
import com.spice.profitmandi.service.transaction.CreditNoteService;
import com.spice.profitmandi.service.transaction.SDCreditService;
import com.spice.profitmandi.service.transaction.TransactionService;
import com.spice.profitmandi.service.transaction.invoicing.InvoiceService;
import com.spice.profitmandi.service.user.RetailerService;
import com.spice.profitmandi.service.user.StoreTimelineTatService;
import com.spice.profitmandi.service.wallet.CommonPaymentService;
import com.spice.profitmandi.service.wallet.WalletService;
import com.spice.profitmandi.service.warehouse.PurchaseOrderService;
import com.spice.profitmandi.service.warehouse.WarehouseInventoryService;
import com.spice.profitmandi.service.warehouse.WarehouseService;
import in.shop2020.model.v1.order.OrderStatus;
import in.shop2020.model.v1.order.WalletReferenceType;
import org.apache.commons.io.output.ByteArrayOutputStream;
import org.apache.commons.lang.StringUtils;
import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.conn.HttpHostConnectException;
import org.apache.http.entity.StringEntity;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.hibernate.SessionFactory;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.core.io.InputStreamSource;
import org.springframework.mail.javamail.JavaMailSender;
import org.springframework.mail.javamail.MimeMessageHelper;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import javax.mail.MessagingException;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;
import java.io.*;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.time.*;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoField;
import java.time.temporal.ChronoUnit;
import java.util.*;
import java.util.Map.Entry;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import static java.util.stream.Collectors.*;


@Component
@Transactional(rollbackFor = {Throwable.class, ProfitMandiBusinessException.class})
public class ScheduledTasks {

    @Value("${oxigen.recharge.enquiry.url}")
    private String oxigenRechargeEnquiryUrl;

    @Autowired
    PurchaseMigration purchaseMigration;

    @Autowired
    SmartPingService smartPingService;

    @Autowired
    private ASTRepository astRepository;

    @Autowired
    private ServiceConfigService serviceConfigService;

    @Autowired
    private TecnoImeiActivation tecnoImeiActivation;

    private static final DateTimeFormatter leadTimeFormatter = DateTimeFormatter.ofPattern("d LLL, hh:mm a");

    @Autowired
    private PartnerRegionRepository partnerRegionRepository;

    @Autowired
    private EmployeeAttendanceRepository employeeAttendanceRepository;

    @Autowired
    VivoImeiActivationService vivoImeiActivationService;

    @Autowired
    ItelImeiActivationService itelImeiActivationService;

    @Autowired
    private PartnerTypeChangeService partnerTypeChangeService;


    @Autowired
    private LeadRepository leadRepository;

    @Autowired
    private AuthRepository authRepository;

    @Autowired
    private PriceDropService priceDropService;

    @Autowired
    private FranchiseeVisitRepository franchiseeVisitRepository;

    @Autowired
    private FranchiseeActivityRepository franchiseeActivityRepository;

    @Autowired
    private HdfcPaymentRepository hdfcPaymentRepository;

    @Autowired
    private CsService csService;

    @Autowired
    private SaholicInventoryService saholicInventoryService;

    @Autowired
    private InsurancePolicyRepository insurancePolicyRepository;

    @Autowired
    private ToffeeService toffeeService;

    @Value("${oxigen.recharge.auth.key}")
    private String oxigenRechargeAuthKey;

    @Value("${oxigen.recharge.validation.url}")
    private String oxigenRechargeValidationUrl;

    @Value("${oxigen.recharge.validation.auth.key}")
    private String oxigenRechargeValidationAuthKey;

    @Value("${think.walnut.digital.recharge.transaction.mobile.url}")
    private String thinkWalnutDigitalRechargeTransactionMobileUrl;

    @Value("${think.walnut.digital.recharge.transaction.dth.url}")
    private String thinkWalnutDigitalRechargeTransactionDthUrl;

    @Value("${think.walnut.digital.recharge.enquiry.url}")
    private String thinkWalnutDigitalRechargeEnquiryUrl;

    @Value("${think.walnut.digital.recharge.balance.url}")
    private String thinkWalnutDigitalRechargeBalanceUrl;

    @Value("${think.walnut.digital.recharge.username}")
    private String thinkWalnutDigitalRechargeUserName;

    @Value("${think.walnut.digital.recharge.password}")
    private String thinkWalnutDigitalRechargePassword;

    @Value("${think.walnut.digital.recharge.auth.key}")
    private String thinkWalnutDigitalRechargeAuthKey;

    @Autowired
    private PurchaseRepository purchaseRepository;

    @Autowired
    private PriceDropIMEIRepository priceDropIMEIRepository;

    @Autowired
    PriceDropRepository priceDropRepository;

    @Autowired
    private PartnerTypeChangeRepository partnerTypeChangeRepository;

    @Autowired
    private SchemeService schemeService;

    @Autowired
    private ServiceConfigRepository serviceConfigRepository;

    @Autowired
    private RetailerBlockBrandsRepository retailerBlockBrandsRepository;

    @Autowired
    private LiquidationService liquidationService;

    private static final String[] STOCK_AGEING_MAIL_LIST = new String[]{"uday.singh@smartudkaan.com", "mohinder.mutreja@smartdukaan.com", "ankit.bhatia@smartdukaan.com", "tarun.verma@smartdukaan.com", "kuldeep.kumar@smartdukaan.com", "manish.gupta1@smartdukaan.com"};

    private static final String[] ITEMWISE_PENDING_INDENT_MAIL_LIST = new String[]{"kamini.sharma@smartdukaan.com", "tarun.verma@smartdukaan.com", "uday.singh@smartdukaan.com", "kuldeep.kumar@smartdukaan.com", "niranjan.kala@smartdukaan.com"};

    private static final String[] INDENT_TERTIARY_MAIL_LIST = new String[]{"uday.singh@smartdukaan.com", "kuldeep.kumar@smartdukaan.com", "nishant.ohri@smartdukaan.com", "vinay.p@smartdukaan.com", "deena.nath@smartdukaan.com", "shobhit.tandon@smartdukaan.com", "ritesh.chauhan1@smartdukaan.com"};

    private static final String[] EMPLOYEE_ATTENDANCE_MAIL_LIST = new String[]{"sm@smartdukaan.com", "sm@smartdukaan.com"};

    private List<OrderStatus> orderStatusList = Arrays.asList(OrderStatus.SUBMITTED_FOR_PROCESSING);

    @Autowired
    private ReporticoService reporticoService;

    @Autowired
    private PartnerInvestmentService partnerInvestmentService;

    @Autowired
    private PositionRepository positionRepository;

    @Autowired
    private FofoOrderItemRepository fofoOrderItemRepository;

    @Autowired
    private NotificationService notificationService;

    @Autowired
    private PartnerDailyInvestmentRepository partnerDailyInvestmentRepository;

    @Autowired
    private SchemeInOutRepository schemeInOutRepository;

    @Autowired
    private RechargeTransactionRepository rechargeTransactionRepository;

    @Autowired
    private CustomerAddressRepository customerAddressRepository;

    @Autowired
    private RechargeProviderCreditWalletHistoryRepository rechargeProviderCreditWalletHistoryRepository;

    @Autowired
    private FofoLineItemRepository fofoLineItemRepository;

    @Autowired
    private FofoOrderRepository fofoOrderRepository;

    @Autowired
    private UserWalletHistoryRepository userWalletHistoryRepository;

    @Autowired
    private UserWalletRepository userWalletRepository;

    @Autowired
    private InventoryItemRepository inventoryItemRepository;

    @Autowired
    private WalletService walletService;

    @Autowired
    private ThinkWalnutDigitalRechargeProviderService thinkWalnutDigitalRechargeProviderService;

    @Autowired
    private OxigenRechargeProviderService oxigenRechargeProviderService;

    @Autowired
    private RechargeProviderRepository rechargeProviderRepository;

    @Autowired
    private ScanRecordRepository scanRecordRepository;

    @Autowired
    private DailyRechargeRepository dailyRechargeRepository;

    @Autowired
    private FofoStoreRepository fofoStoreRepository;

    @Value("${prod}")
    private boolean prod;

    @Autowired
    private StateGstRateRepository stateGstRateRepository;

    @Autowired
    private RetailerService retailerService;

    @Autowired
    private BrandsRepository brandsRepository;

    @Autowired
    private TransactionService transactionService;

    @Autowired
    private ItemRepository itemRepository;

    @Autowired
    private OrderRepository orderRepository;

    @Autowired
    private OrderService orderService;

    @Autowired
    private SchemeRepository schemeRepository;

    @Autowired
    private SchemeItemRepository schemeItemRepository;

    @Autowired
    private JavaMailSender mailSender;

    @Autowired
    private PartnerTargetRepository partnerTargetRepository;

    @Autowired
    @Qualifier(value = "googleMailSender")
    private JavaMailSender googleMailSender;

    @Autowired
    private InventoryService inventoryService;

    @Autowired
    private AddressRepository addressRepository;

    @Autowired
    private RetailerRegisteredAddressRepository retailerRegisteredAddressRepository;

    @Autowired
    private Mongo mongoClient;

    @Autowired
    private DeviceRepository deviceRepository;

    @Autowired
    private PushNotificationRepository pushNotificationRepository;

    @Autowired
    private NotificationCampaignRepository notificationCampaignRepository;

    @Autowired
    private CurrentInventorySnapshotRepository currentInventorySnapshotRepository;

    @Autowired
    private FocusedModelRepository focusedModelRepository;

    @Autowired
    private UserAccountRepository userAccountRepository;

    @Autowired
    private UserRepository userUserRepository;

    @Autowired
    private com.spice.profitmandi.dao.repository.dtr.UserRepository dtrUserRepository;

    @Autowired
    private UserCampaignRepository userCampaignRepository;

    @Autowired
    private Gson gson;

    @Autowired
    private TicketRepository ticketRepository;

    @Autowired
    private RefferalRepository refferalRepository;

    @Autowired
    private PartnerProblemRepository partnerProblemRepository;

    @Autowired
    private PendingOrderRepository pendingOrderRepository;

    @Autowired
    private PendingOrderItemRepository pendingOrderItemRepository;

    @Value("${razorpay.account.keyId}")
    private String keyId;

    @Value("${razorpay.account.keySecret}")
    private String keySecret;

    @Autowired
    private RazorpayPaymentService razorPaymentService;

    @Autowired
    private RazorPayRepository razorPayRepository;

    @Autowired
    private PendingOrderService pendingOrderService;

    @Autowired
    private CustomerRepository customerRepository;

    @Autowired
    private RestClient restClient;

    @Autowired
    private MandiiService mandiiService;

    @Autowired
    CreditAccountRepository creditAccountRepository;

    @Autowired
    CustomerOfferRepository customerOfferRepository;

    @Autowired
    CustomerOfferItemRepository customerOfferItemRepository;
    @Autowired
    private FofoSolr fofoSolr;


    private static final Logger LOGGER = LogManager.getLogger(ScheduledTasks.class);

    private String FCM_URL = "https://fcm.googleapis.com/v1/projects/api-project-309385327230/messages:send";
    private String FCM_SCOPED = "https://www.googleapis.com/auth/cloud-platform";

    @Autowired
    private TagListingRepository tagListingRepository;
    @Autowired
    private RunOnceTasks runOnceTasks;

    public void generateDailyRecharge() {
        List<RechargeProviderCreditWalletHistory> allCreditHistory = rechargeProviderCreditWalletHistoryRepository.selectAll(0, 2000);
        List<RechargeProvider> rechargeProviders = rechargeProviderRepository.selectAll();
        rechargeProviders.stream().forEach(x -> x.setAmount(0));

        rechargeProviders.stream().forEach(x -> {
            Map<LocalDate, List<RechargeProviderCreditWalletHistory>> dateWiseProviderCreditsMap = allCreditHistory.stream().filter(z -> z.getProviderId() == x.getId()).collect(groupingBy(x1 -> x1.getReceiveTimestamp().toLocalDate()));

            LOGGER.info("dateWiseProviderCreditsMap -{}", dateWiseProviderCreditsMap);
            LocalDate endDate = LocalDate.now().plusDays(1);
            float previousDayClosing = 0;
            LocalDate date = LocalDate.of(2018, 4, 6);
            while (date.isBefore(endDate)) {
                List<RechargeTransaction> dateWiseRechargeTransactions = rechargeTransactionRepository.selectAllBetweenTimestamp(Arrays.asList(RechargeStatus.values()), date.atStartOfDay(), date.plusDays(1).atStartOfDay());

                List<RechargeTransaction> successfulTransactions = dateWiseRechargeTransactions.stream().filter(y -> y.getStatus().equals(RechargeStatus.SUCCESS)).collect(Collectors.toList());

                float dailyAmount = 0;
                float totalCommission = 0;
                for (RechargeTransaction rechargeTransaction : successfulTransactions) {
                    if (rechargeTransaction.getProviderId() == x.getId()) {
                        dailyAmount += rechargeTransaction.getAmount();
                        totalCommission += rechargeTransaction.getCommission();
                    }
                }

                List<RechargeProviderCreditWalletHistory> rechargeHistoryList = dateWiseProviderCreditsMap.get(date);
                float dailyWalletRecharge = 0;
                if (rechargeHistoryList != null) {
                    for (RechargeProviderCreditWalletHistory rechargeProviderCreditWalletHistory : rechargeHistoryList) {
                        if (rechargeProviderCreditWalletHistory.getProviderId() == x.getId()) {
                            dailyWalletRecharge += rechargeProviderCreditWalletHistory.getAmount();
                        }
                    }
                }
                if (dailyAmount > 0 || dailyWalletRecharge > 0) {
                    DailyRecharge dailyRecharge = null;
                    try {
                        dailyRecharge = dailyRechargeRepository.selectByProviderIdAndCreateDate(x.getId(), date);
                    } catch (Exception e) {
                        LOGGER.info("Could not find Recharge entry");
                    }
                    if (dailyRecharge == null) {
                        dailyRecharge = new DailyRecharge();
                        dailyRecharge.setCreateDate(date);
                    }
                    dailyRecharge.setOpeningBalance(previousDayClosing);
                    dailyRecharge.setProviderId(x.getId());
                    dailyRecharge.setWalletRechargeAmount(dailyWalletRecharge);
                    dailyRecharge.setTotalAmount(dailyAmount);
                    dailyRecharge.setTotalCommission(totalCommission);
                    float closingBalance = dailyRecharge.getOpeningBalance() + dailyWalletRecharge - dailyAmount;
                    dailyRecharge.setClosingBalance(closingBalance);
                    dailyRechargeRepository.persist(dailyRecharge);
                    x.setAmount(x.getAmount() + dailyRecharge.getClosingBalance() - dailyRecharge.getOpeningBalance());
                    previousDayClosing = dailyRecharge.getClosingBalance();
                }
                date = date.plusDays(1);
            }
            rechargeProviderRepository.persist(x);
        });
        LOGGER.info("finished generating daily recharge");
    }

    public void reconcileRecharge() throws Exception {
        LocalDateTime fromDate = LocalDateTime.now().truncatedTo(ChronoUnit.DAYS).minusDays(30);
        LocalDateTime toDate = LocalDateTime.now().truncatedTo(ChronoUnit.DAYS);
        List<RechargeStatus> nonSuccessRechargeStatuses = new ArrayList<>(Arrays.asList(RechargeStatus.values()));
        LOGGER.info("nonSuccessRechargeStatuses {} ", nonSuccessRechargeStatuses);
        nonSuccessRechargeStatuses.remove(RechargeStatus.SUCCESS);
        nonSuccessRechargeStatuses.remove(RechargeStatus.FAILED);
        RechargeCredential thinkWalnutDigitalRechargeEnquiryCredential = new RechargeCredential();
        thinkWalnutDigitalRechargeEnquiryCredential.setRechargeUrl(thinkWalnutDigitalRechargeEnquiryUrl);
        thinkWalnutDigitalRechargeEnquiryCredential.setRechargeUserName(thinkWalnutDigitalRechargeUserName);
        thinkWalnutDigitalRechargeEnquiryCredential.setRechargePassword(thinkWalnutDigitalRechargePassword);
        thinkWalnutDigitalRechargeEnquiryCredential.setRechargeAuthKey(thinkWalnutDigitalRechargeAuthKey);
        Map<String, RechargeStatus> requestRechargeStatusChanged = new HashMap<>();
        List<RechargeTransaction> rechargeTransactions = rechargeTransactionRepository.selectAllBetweenTimestamp(nonSuccessRechargeStatuses, fromDate, toDate);
        for (RechargeTransaction rechargeTransaction : rechargeTransactions) {
            try {
                int providerId = rechargeTransaction.getProviderId();
                if (providerId == 1) {
                    oxigenRechargeProviderService.doCheckStatusRequest(oxigenRechargeEnquiryUrl, oxigenRechargeAuthKey, rechargeTransaction);
                } else if (providerId == 2) {
                    thinkWalnutDigitalRechargeProviderService.doCheckStatusRequest(thinkWalnutDigitalRechargeEnquiryCredential, rechargeTransaction);
                }
                if (rechargeTransaction.getStatus().equals(RechargeStatus.SUCCESS) || rechargeTransaction.getStatus().equals(RechargeStatus.FAILED)) {
                    requestRechargeStatusChanged.put(rechargeTransaction.getRequestId(), rechargeTransaction.getStatus());
                }
            } catch (Exception e) {
                LOGGER.info("Could not check status for Request {}", rechargeTransaction.getRequestId());
            }
        }
        LOGGER.info("Reconcile recharge ran successfully");
    }

    public void processActivation() throws Exception {
        schemeService.processActivation();
    }

    // TemporaryMethod
    public void migrateInvoice() {
        List<FofoOrder> fofoOrders = fofoOrderRepository.selectFromSaleDate(LocalDateTime.now().minusDays(3));
        Map<Integer, List<FofoOrder>> partnerOrdersMap = new HashMap<>();
        partnerOrdersMap = fofoOrders.stream().collect(groupingBy(FofoOrder::getFofoId, Collectors.toList()));
        for (List<FofoOrder> orderList : partnerOrdersMap.values()) {
            int sequence = 0;
            String prefix = "";
            List<FofoOrder> sortedList = orderList.stream().sorted((x1, x2) -> x1.getId() - x2.getId()).collect(Collectors.toList());
            for (FofoOrder order : sortedList) {

                LOGGER.info("Order Id is {}, partner Id is {}", order.getId(), order.getFofoId());
                if (!order.getInvoiceNumber().contains("SEC")) {
                    sequence = Integer.parseInt(order.getInvoiceNumber().split("/")[1]);
                    prefix = order.getInvoiceNumber().split("/")[0];
                } else {
                    sequence += 1;
                    String invoiceNumber = prefix + "/" + sequence;
                    order.setInvoiceNumber(invoiceNumber);
                    fofoOrderRepository.persist(order);
                }
            }

        }
    }

    @Autowired
    private ReporticoCacheTableRepository reporticoCacheTableRepository;

    public void processSchemeOut(List<String> invoiceNumbers) throws Exception {
        for (String invoiceNumber : invoiceNumbers) {
            System.out.println("Invoice Number - " + invoiceNumber);
            FofoOrder fofoOrder = fofoOrderRepository.selectByInvoiceNumber(invoiceNumber);
            //orderService.reverseScheme(fofoOrder);
            schemeService.processSchemeOut(fofoOrder.getId(), fofoOrder.getFofoId());
        }
    }

    public void processSchemeIn(List<String> invoiceNumbers) throws Exception {
        for (String invoiceNumber : invoiceNumbers) {
            System.out.println("Invoice Number - " + invoiceNumber);
            Purchase purchase = purchaseRepository.selectByPurchaseReference(invoiceNumber);
            schemeService.processSchemeIn(purchase.getId(), purchase.getFofoId());
        }
    }

    public void processScheme(LocalDateTime startDate, LocalDateTime endDate, boolean dryRun) throws Exception {
        LOGGER.info("Started execution at {}", LocalDateTime.now());
        System.out.println(
                "InventoryId\tSerialNumber\tItem Id\tScheme Id\tScheme Name\tScheme Type\tAmount Type\tDP\tTaxable\tScheme Amount\tAmount Paid");
        try {
            List<Purchase> purchases = purchaseRepository.selectAllBetweenPurchaseDate(startDate, endDate);
            for (Purchase purchase : purchases) {
                schemeService.processSchemeIn(purchase.getId(), purchase.getFofoId());
            }

            List<FofoOrder> fofoOrders = fofoOrderRepository.selectBetweenSaleDate(startDate, endDate);
            for (FofoOrder fofoOrder : fofoOrders) {
                schemeService.processSchemeOut(fofoOrder.getId(), fofoOrder.getFofoId());
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
        List<UserWalletHistory> uwhs = userWalletHistoryRepository.selectAllByDateType(LocalDate.now().atStartOfDay(), endDate, Arrays.asList(WalletReferenceType.SCHEME_IN, WalletReferenceType.SCHEME_OUT));
        System.out.println("Amount\tReference\tReferenceType\tTimestamp\tDescription");
        for (UserWalletHistory uwh : uwhs) {
            System.out.println(String.format("%d\t%d\t%s\t%s\t%s", uwh.getAmount(), uwh.getReference(), uwh.getReferenceType(), uwh.getTimestamp().toString(), uwh.getDescription()));
        }
        LOGGER.info("Schemes processed successfully.");
        if (dryRun) {
            throw new Exception();
        }
    }

    public void processRechargeCashback() throws Throwable {
        LocalDateTime cashbackTime = LocalDateTime.now();
        int referenceId = (int) Timestamp.valueOf(cashbackTime).getTime() / 1000;
        List<RechargeTransaction> pendingTransactions = rechargeTransactionRepository.getPendingCashBackRehargeTransactions();
        Map<Object, Double> totalRetailerCashbacks = pendingTransactions.stream().collect(
                groupingBy(x -> x.getRetailerId(), Collectors.summingDouble(x -> x.getCommission())));
        for (Entry<Object, Double> totalRetailerCashback : totalRetailerCashbacks.entrySet()) {
            int retailerId = (Integer) totalRetailerCashback.getKey();
            float amount = totalRetailerCashback.getValue().floatValue();
            if (Math.round(amount) > 0) {
                walletService.addAmountToWallet(retailerId, referenceId, WalletReferenceType.CASHBACK, "Recharge Cashback", Math.round(amount), LocalDateTime.now());
            }
        }
        for (RechargeTransaction rt : pendingTransactions) {
            rt.setCashbackTimestamp(cashbackTime);
            rt.setCashbackReference(referenceId);
            rechargeTransactionRepository.persist(rt);
        }
        LOGGER.info("Cashbacks for Recharge processed Successfully");
    }

    public void rollOutUpgardedMarginsNextMonth(List<Integer> fofoIds) throws Exception {
        LocalDate startOfPreviousMonth = LocalDate.now().with(ChronoField.DAY_OF_MONTH, 1).minusMonths(1);
        List<FofoStore> stores;
        if (fofoIds == null || fofoIds.isEmpty()) {
            stores = fofoStoreRepository.selectActiveStores();
        } else {
            stores = fofoStoreRepository.selectByRetailerIds(fofoIds);
        }
        for (FofoStore store : stores) {
            this.rolloutMarginForStoreOnMonth(store, startOfPreviousMonth);
        }
    }

    public void rolloutMarginForStoreOnMonth(FofoStore store, LocalDate startOfMonth) throws ProfitMandiBusinessException {
        int fofoId = store.getId();

        // Check if there's a manual partner type change on day 1 of the month
        PartnerTypeChange day1Change = partnerTypeChangeRepository.selectPartnerType(fofoId, startOfMonth);
        if (day1Change != null && day1Change.getSource() == PartnerTypeSource.MANUAL) {
            LOGGER.info("Manual partner type upgrade detected for code {} to {}", store.getCode(), day1Change.getPartnerType());
            List<FofoOrder> fofoOrders = fofoOrderRepository.selectByFofoId(fofoId, startOfMonth.atStartOfDay(), startOfMonth.plusMonths(1).atStartOfDay(), 0, 0);
            for (FofoOrder fofoOrder : fofoOrders) {
                schemeService.processSchemeOut(fofoOrder.getId(), fofoId);
            }
            return;
        }

        // Existing logic for calculated upgrades
        PartnerType startOfPreviousMonthPartnerType = partnerTypeChangeService.getTypeOnDate(fofoId, startOfMonth);
        PartnerType todayPartnerType = partnerTypeChangeService.getTypeOnMonth(fofoId, YearMonth.from(startOfMonth));
        if (!startOfPreviousMonthPartnerType.nextPartnerTypes().contains(todayPartnerType)) {
            return;
        }
        if (!startOfPreviousMonthPartnerType.equals(todayPartnerType)) {
            LOGGER.info("Partner Type has been changed for code {} from {} to {}", store.getCode(), startOfPreviousMonthPartnerType, todayPartnerType);
            List<FofoOrder> fofoOrders = fofoOrderRepository.selectByFofoId(fofoId, startOfMonth.atStartOfDay(), startOfMonth.plusMonths(1).atStartOfDay(), 0, 0);
            for (FofoOrder fofoOrder : fofoOrders) {
                schemeService.processSchemeOut(fofoOrder.getId(), fofoId);
            }
        }
    }


    public void rollOutUpgardedMargins(List<Integer> fofoIds) throws Exception {
        LocalDate today = LocalDate.now();
        LocalDate yesterday = today.minusDays(1);
        int upgradedCount = 0;
        List<FofoStore> stores;
        if (fofoIds == null || fofoIds.isEmpty()) {
            stores = fofoStoreRepository.selectActiveStores();
        } else {
            stores = fofoStoreRepository.selectByRetailerIds(fofoIds);
        }

        // Bulk fetch partner types to avoid N+1 queries
        Map<Integer, PartnerType> yesterdayTypeMap = partnerTypeChangeService.getTypesForFofoIds(fofoIds, yesterday);
        Map<Integer, PartnerType> todayTypeMap = partnerTypeChangeService.getTypesForFofoIds(fofoIds, today);

        StringBuilder sb = new StringBuilder();
        for (FofoStore store : stores) {
            int fofoId = store.getId();

            PartnerType yesterdayPartnerType = yesterdayTypeMap.get(fofoId);
            PartnerType todayPartnerType = todayTypeMap.get(fofoId);
            if (yesterdayPartnerType == null || todayPartnerType == null) {
                continue;
            }
            if (!yesterdayPartnerType.nextPartnerTypes().contains(todayPartnerType)) {
                continue;
            }
            if (!yesterdayPartnerType.equals(todayPartnerType)) {
                upgradedCount++;
                sb.append(String.format("Partner Type has been changed for code %s from %s to %s", store.getCode(), yesterdayPartnerType.toString(), todayPartnerType.toString()));
                sb.append("<br>");
                LOGGER.info("Partner Type has been changed for code {} from {} to {}", store.getCode(), yesterdayPartnerType, todayPartnerType);
                List<FofoOrder> fofoOrders = fofoOrderRepository.selectByFofoId(fofoId, yesterday.withDayOfMonth(1).atStartOfDay(), yesterday.atStartOfDay(), 0, 0);
                for (FofoOrder fofoOrder : fofoOrders) {
                    schemeService.processSchemeOut(fofoOrder.getId(), fofoId);
                }

            }
        }
        if (upgradedCount > 0) {
            sendMailOfHtmlFormat(googleMailSender, new String[]{"amit.gupta@smartdukaan.com", "tarun.verma@smartdukaan.com", "neeraj.gupta@smartdukaan.com"}, sb.toString(), null, "Few Partners Category have been Upgraded");
        } else {
            sendMailOfHtmlFormat(googleMailSender, new String[]{"amit.gupta@smartdukaan.com", "tarun.verma@smartdukaan.com", "neeraj.gupta@smartdukaan.com"}, sb.toString(), null, "No partners Category have been upgraded today");
        }
    }

    @Autowired
    private PartnerStatsService partnerStatsService;

// Temporary Method

    public void fetchParnterStats() throws Exception {
        Map<Integer, PartnerDetailModel> partnerStats = partnerStatsService.getAllPartnerStats();
        ObjectOutputStream oos = null;
        FileOutputStream fout = null;
        try {
//            fout = new FileOutputStream("/tmp/partnerStat.tmp", false);
            fout = new FileOutputStream("/var/www/partner_stats/partnerStat.tmp", false);
            oos = new ObjectOutputStream(fout);
            oos.writeObject(partnerStats);

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (oos != null) {
                oos.close();
            }
        }
        ReporticoCacheTable reporticoCacheTable = reporticoCacheTableRepository.selectByTableName("partnerStat");
        if (reporticoCacheTable == null) {
            reporticoCacheTable = new ReporticoCacheTable();
            reporticoCacheTable.setTableName("partnerStat");
            reporticoCacheTable.setLastCreatedTimestamp(LocalDateTime.now());
            reporticoCacheTableRepository.persist(reporticoCacheTable);
        } else {
            reporticoCacheTable.setLastCreatedTimestamp(LocalDateTime.now());
        }
    }

    public void processPriceDrop() throws Exception {
        List<PriceDrop> priceDrops = priceDropRepository.selectAllByDatesBetween(LocalDateTime.now(), LocalDateTime.now().minusYears(1));
        for (PriceDrop priceDrop : priceDrops) {
            if (priceDrop.getDropAmount() > 0) {
                priceDropService.processPriceDrop(priceDrop.getId(), true);
            }
        }
    }

    @Autowired
    CreditNoteService creditNoteService;

    public void sendCreditNote(YearMonth yearMonth) throws Exception {
        creditNoteService.issueMonthlyMarginsCN(yearMonth);
    }

    @Autowired
    BrandAgeingLimitRepository brandAgeingLimitRepository;

    @Autowired
    AgeingService ageingService;

    @Autowired
    RbmTargetService rbmTargetService;

    public void sendMailForAgeingAlert() throws Exception {
        //brandAgeingLimitRepository.selectAll();
        Map<String, AgeingModel> ageingModelMap = ageingService.getAllAgeingForSecondary();

        System.out.println(ageingModelMap.size());
        List<PartnerAgeingModel> partnerAgeingModels = ageingService.getAllAgeingForTertiary();


        ByteArrayOutputStream byteArrayOutputStream = FileUtil.getCSVByteStream(Arrays.asList("InventoryItemId", "Serial Number", "Item Id", "Brand", "Model Name", "ModelNumber", "Color", "Exceed Days", "Last ScanType", "Supplier Name", "Current Warehouse", "First Warehouse", "Invoice Number", "Invoice Date"), ageingModelMap.values().stream().map(x -> Arrays.asList(x.getInventoryItemId(), x.getSerialNumber(), x.getItemId(), x.getBrand(), x.getModelName(), x.getModelNumber(), x.getColor(), x.getExceedDays(), x.getLastScanType(), x.getSupplierName(), x.getCurrentWarehouse(), x.getFirstWarehouse(), x.getInvoiceNumber(), FormattingUtils.formatDate(x.getInvoiceDate()))).collect(Collectors.toList()));

        ByteArrayOutputStream byteArrayOutputStream2 = FileUtil.getCSVByteStream(Arrays.asList("InventoryItemId", "Serial Number", "Item Id", "Brand", "Model Name", "ModelNumber", "Color", "Partner Id", "Partner Code", "Partner Name", "Activation Type", "City", "State", "Billing Date", "Exceed Days", "Max Ageing", "Activation Date"), partnerAgeingModels.stream().map(x -> Arrays.asList(x.getInventoryItemId(), x.getSerialNumber(), x.getItemId(), x.getBrand(), x.getModelName(), x.getModelNumber(), x.getColor(), x.getFofoId(), x.getFofoCode(), x.getFofoName(), x.getActivationType(), x.getFofoCity(), x.getFofoState(), FormattingUtils.formatDate(x.getBillingDate()), x.getExceedDays(), x.getMaxAgeingDays(), x.getActivationDate())).collect(Collectors.toList()));
        Attachment attachment1 = new Attachment("secondary-alert.csv", new ByteArrayResource(byteArrayOutputStream.toByteArray()));
        Attachment attachment2 = new Attachment("tertiary-alert.csv", new ByteArrayResource(byteArrayOutputStream2.toByteArray()));

        String mailSubject = "Stock ageing Alert";
        Utils.sendMailWithAttachments(mailSender, new String[]{"deena.nath@smartdukaan.com"}, null, mailSubject, "PFA", attachment1, attachment2);
    }

    public void rejectPriceDropsOfApprovedImeis() throws ProfitMandiBusinessException {
        priceDropService.rejectPriceDropsOfApprovedImeis();
    }

    public void processDeductions() {
        sidbiService.processDeductions();
    }

    public void resetHardLimit() throws ProfitMandiBusinessException {
        sdCreditService.resetHardLimit();
    }

    public void testInvoiceFormat() throws ProfitMandiBusinessException {
        InvoicePdfModel invoicePdfModel = orderService.getInvoicePdfModel(560280);
        //System.out.println(InvoiceFormatter.buildInvoiceLines(invoicePdfModel, InvoiceFormatter.WIDTH_80MM));
    }

    public void sendTrialExpiry() {
        List<FofoStore> stores = fofoStoreRepository.selectAllTrial();
        List<FofoStore> twoDaysToExpiry = stores.stream().filter(x -> x.getTrialEnd().minusDays(2).equals(LocalDate.now())).collect(Collectors.toList());

        List<FofoStore> fiveDaysToExpiry = stores.stream().filter(x -> x.getTrialEnd().minusDays(5).equals(LocalDate.now())).collect(Collectors.toList());
        List<FofoStore> expiredToday = stores.stream().filter(x -> x.getTrialEnd().equals(LocalDate.now())).collect(Collectors.toList());
        List<FofoStore> expired = stores.stream().filter(x -> x.getTrialEnd().isAfter(LocalDate.now())).collect(Collectors.toList());
    }

    /*public void processCashDiscount() throws ProfitMandiBusinessException {
        System.out.println("processing cash discount");
        List<Loan> loansSettledYesterday = loanRepository.selectLoansSettledOn(LocalDate.now().minusDays(1));
        loansSettledYesterday.addAll(loanRepository.selectLoansSettledOn(LocalDate.now()));
        Map<Integer, List<Loan>> partnerSettledLoans = loansSettledYesterday.stream().collect(Collectors.groupingBy(x -> x.getFofoId()));
        for (Entry<Integer, List<Loan>> partnerLoans : partnerSettledLoans.entrySet()) {
            List<Loan> fullCdLoans = partnerLoans.getValue().stream().filter(x -> x.getInvoiceNumber() != null && x.getDays() <= ProfitMandiConstants.LOAN_PARTIAL_CREDIT_DAYS).collect(toList());
            List<Loan> partialCdLoans = partnerLoans.getValue().stream().filter(x -> x.getInvoiceNumber() != null && x.getDays() > ProfitMandiConstants.LOAN_PARTIAL_CREDIT_DAYS &&
                    x.getDays() <= ProfitMandiConstants.LOAN_FULL_CREDIT_DAYS).collect(toList());
            //List<Loan> noCdLoans = loanRepository.selectLoansDueByDays()
            //
            for (Loan fullCdLoan : fullCdLoans) {
                LOGGER.info("FullCdLoan - {}", fullCdLoan);
                schemeService.processFullCD(fullCdLoan.getInvoiceNumber());
            }

            for (Loan partialCdLoan : partialCdLoans) {
                schemeService.processPartialCD(partialCdLoan.getInvoiceNumber());
            }

            *//*for (Loan noCdLoan : noCdLoans) {
                schemeService.processNoCD(noCdLoan.getInvoiceNumber());
            }*//*
        }

    }*/

    private class SaleRoles {

        private List<String> l1;
        private List<String> l2;

        public SaleRoles() {
            l1 = new ArrayList<>();
            l2 = new ArrayList<>();
        }

        public List<String> getL1() {
            return l1;
        }

        public List<String> getL2() {
            return l2;
        }

    }

    private class FofoReportingModel {
        private String code;
        private int fofoId;
        private String businessName;
        private String territoryManager;
        private String regionalManager;
        private String businessManager;

        public String getCode() {
            return code;
        }

        public void setCode(String code) {
            this.code = code;
        }

        public String getBusinessName() {
            return businessName;
        }

        public void setBusinessName(String businessName) {
            this.businessName = businessName;
        }

        public String getTerritoryManager() {
            return territoryManager;
        }

        public void setTerritoryManager(String territoryManager) {
            this.territoryManager = territoryManager;
        }

        public String getRegionalManager() {
            return regionalManager;
        }

        public void setRegionalManager(String regionalManager) {
            this.regionalManager = regionalManager;
        }

        private ScheduledTasks getOuterType() {
            return ScheduledTasks.this;
        }

        @Override
        public String toString() {
            return "FofoReportingModel{" + "code='" + code + '\'' + ", fofoId=" + fofoId + ", businessName='" + businessName + '\'' + ", territoryManager='" + territoryManager + '\'' + ", regionalManager='" + regionalManager + '\'' + ", businessManager='" + businessManager + '\'' + '}';
        }

        @Override
        public boolean equals(Object o) {
            if (this == o) return true;
            if (o == null || getClass() != o.getClass()) return false;
            FofoReportingModel that = (FofoReportingModel) o;
            return fofoId == that.fofoId && Objects.equals(code, that.code) && Objects.equals(businessName, that.businessName) && Objects.equals(territoryManager, that.territoryManager) && Objects.equals(regionalManager, that.regionalManager) && Objects.equals(businessManager, that.businessManager);
        }

        @Override
        public int hashCode() {
            return Objects.hash(code, fofoId, businessName, territoryManager, regionalManager, businessManager);
        }

        public String getBusinessManager() {
            return businessManager;
        }

        public void setBusinessManager(String businessManager) {
            this.businessManager = businessManager;
        }

        public int getFofoId() {
            return fofoId;
        }

        public void setFofoId(int fofoId) {
            this.fofoId = fofoId;
        }

    }

    @Autowired
    WarehouseInventoryItemRepository warehouseInventoryItemRepository;

    @Autowired
    OfferService offerService;

    @Autowired
    PurchaseReturnItemRepository purchaseReturnItemRepository;

    @Autowired
    BrandRegionMappingRepository brandRegionMappingRepository;

    @Autowired
    StandAlone standAlone;
    @Autowired
    ItelImeiActivationNewService itelImeiActivationNewService;

    public void checkItelImeiActivationNew(LocalDate date, Integer day) throws Exception {
        try {
            itelImeiActivationNewService.checkItelImeiActivationNew(date, day);
        } catch (Throwable e) {
            throw new Exception(e);
        }
    }

    @Autowired
    PurchaseReturnService purchaseReturnService;

    @Autowired
    JavaMailSender javaMailSender;

    @Autowired
    OfferPayoutRepository offerPayoutRepository;

    @Autowired
    OfferRepository offerRepository;

    @Autowired
    BluedartAuthService bluedartAuthService;
    @Autowired
    BluedartService bluedartService;

    @Autowired
    TransactionRelatedTasks transactionRelatedTasks;

    @Autowired
    PsiSmartService psiSmartService;

    private void migrateValentine() throws ProfitMandiBusinessException {
        List<FofoOrder> fofoOrders = fofoOrderRepository.selectBetweenSaleDate(LocalDate.now().atStartOfDay(), LocalDateTime.now());
        for (FofoOrder fofoOrder : fofoOrders) {
            orderService.processScratchOffer(fofoOrder);
        }
    }

    @Autowired
    InvoiceService invoiceService;

    @Autowired
    WarehouseService warehouseService;

    @Autowired
    GenericRepository genericRepository;

    @Autowired
    PriceCircularService priceCircularService;

    /*@Autowired
    RedisCacheManager redisFortnightlyCacheManage;*/

    @Autowired
    GstProAuthService gstProAuthService;

    @Autowired
    GstProService gstProService;

    @Autowired
    WarehouseProviderRepository warehouseProviderRepository;

    @Autowired
    LoiFormRepository loiFormRepository;
    @Autowired
    PartnerOnBoardingPanelRepository partnerOnBoardingPanelRepository;
    @Autowired
    StoreTimelineTatService storeTimelineTatService;
    @Autowired
    PartnerDealerRepository partnerDealerRepository;

    @Autowired
    PurSaleService purSaleService;

    @Autowired
    CatalogRepository catalogRepository;

    private void rolloutOppoPayout() throws Exception {
        //List<Integer> schemeIds = Arrays.asList(5540, 5542, 5544,5918, 5919, 5920, 5921, 5922);
        List<Integer> schemeIds = Arrays.asList(5540, 5542, 5544, 5545, 5550, 5555, 5556, 5557, 5564, 5565, 5566, 5567, 5569, 5570, 5582, 5590, 5592, 5593, 5594,
                5595, 5598, 5602, 5604, 5605, 5606, 5607, 5608, 5611, 5618, 5620, 5621, 5622, 5623, 5624, 5625, 5626, 5629, 5631,
                5633, 5634, 5635, 5637, 5644, 5649, 5655, 5659, 5662, 5668, 5672, 5674, 5679, 5683, 5684, 5685, 5688, 5689, 5690,
                5692, 5694, 5697, 5699, 5700, 5706, 5718, 5729, 5730, 5754, 5756, 5758, 5759, 5761, 5762, 5766, 5769, 5771, 5772,
                5773, 5774, 5775, 5776, 5781, 5782, 5783, 5786, 5788, 5790, 5795, 5800, 5803, 5804, 5807, 5809, 5815, 5816, 5819,
                5820, 5821, 5822, 5823, 5824, 5825, 5826, 5827, 5828, 5829, 5830, 5832, 5833, 5834, 5835, 5836, 5837, 5839, 5840,
                5841, 5842, 5843, 5844, 5845, 5846, 5847, 5848, 5849, 5850, 5851, 5852, 5853, 5854, 5855, 5857, 5859, 5860, 5863,
                5864, 5866, 5869, 5870, 5871, 5872, 5873, 5874, 5875, 5877, 5878, 5879, 5880, 5885, 5888, 5889, 5891, 5892, 5894,
                5899, 5901, 5903, 5905, 5908, 5910, 5913, 5916, 5917, 5918, 5919, 5920, 5921, 5922, 5923, 5924, 5925, 5926, 5927,
                5928, 5929, 5930, 5931, 5932, 5933, 5934, 5935, 5936, 5938, 5939, 5940, 5941, 5942, 5943, 5944, 5945, 5946, 5947,
                5948, 5949, 5950, 5951, 5952, 5953, 5954, 5955, 5956, 5957, 5958, 5959, 5960, 5961, 5962, 5963, 5964, 5965, 5966,
                5967, 5968, 5969, 5970, 5971, 5972, 5973, 5974, 5975, 5976, 5977, 5979, 5980, 5981, 5982, 5983, 5984, 5985, 5986,
                5987, 5989, 5990, 5991, 5992, 5996, 5997, 5998, 5999, 6000, 6001, 6002, 6003, 6004, 6005, 6006, 6007, 6008, 6010,
                6011, 6012, 6013, 6014, 6015, 6016, 6017, 6018, 6019, 6020, 6021, 6022, 6023, 6024, 6025, 6026, 6028, 6029, 6030,
                6031, 6032, 6033, 6034, 6035, 6036, 6037, 6038, 6039, 6040, 6041, 6042, 6043, 6044, 6045, 6046, 6047, 6048, 6049,
                6050, 6051, 6052, 6053, 6054, 6055, 6056, 6057, 6058, 6059, 6060, 6061, 6062, 6063, 6064, 6065, 6066, 6067, 6068,
                6069, 6070, 6071, 6072, 6073, 6078, 6079, 6080, 6081, 6082, 6083, 6084, 6085, 6086, 6087, 6088, 6089, 6091, 6092,
                6093, 6094, 6095, 6098, 6102);
        Map<Integer, List<SchemeInOut>> inventoryItemSiosMap = schemeInOutRepository.selectBySchemeIds(new HashSet<>(schemeIds)).stream().collect(Collectors.groupingBy(x -> x.getInventoryItemId()));
        Map<Integer, InventoryItem> inventoryItemMap = inventoryItemRepository.selectByIds(inventoryItemSiosMap.keySet()).stream().collect(Collectors.toMap(x -> x.getId(), x -> x));
        Set<Integer> itemIds = itemRepository.selectAllByBrand("Oppo", 10006).stream().map(x -> x.getId()).collect(toSet());
        List<InventoryItem> filteredInventoryItems = inventoryItemMap.values().stream().filter(x -> itemIds.contains(x.getItemId())).collect(Collectors.toList());

        Map<Integer, List<InventoryItem>> purchaseInventoryItemsMap = filteredInventoryItems.stream().filter(x -> x.getCreateTimestamp().isAfter(YearMonth.now().atDay(1).atStartOfDay())).collect(Collectors.groupingBy(x -> x.getPurchaseId()));
        List<Purchase> purchases = purchaseRepository.selectAllByIds(new ArrayList<>(purchaseInventoryItemsMap.keySet()));
        for (Purchase purchase : purchases) {
            String invoiceNumber = purchase.getPurchaseReference();
            LocalDateTime billingTime = orderRepository.selectByInvoiceNumber(invoiceNumber).get(0).getBillingTimestamp();
            if (billingTime.isAfter(YearMonth.now().atDay(1).atStartOfDay())) {
                schemeService.processSchemeIn(purchase.getId(), purchase.getFofoId());
            }
        }

        for (InventoryItem filteredInventoryItem : filteredInventoryItems) {
            //System.out.println("Serial Number is - " + filteredInventoryItem.getSerialNumber());
            List<ScanRecord> scanRecords = scanRecordRepository.selectByInventoryItemId(filteredInventoryItem.getId());
            ScanRecord scanRecord = scanRecords.stream().filter(x -> x.getType().equals(ScanType.SALE)).findFirst().orElse(null);
            if (scanRecord == null) {
                continue;
            }
            schemeService.processSchemeOut(scanRecord.getOrderId(), scanRecord.getFofoId());
        }
    }

    @Autowired
    AbmReportSender abmReportSender;

    public void sendOverDueReport() {
        List<Loan> loans = loanRepository.selectAllActiveLoan();
        Map<Integer, List<Loan>> partnerLoansMap = loans.stream().filter(x -> x.isOverdue()).collect(Collectors.groupingBy(x -> x.getFofoId()));
        //sdCreditService.getDueDateCrossLoan()
        partnerLoansMap.entrySet().stream().forEach(x -> {

            x.getKey();
            x.getValue().size();
        });

    }

    @Autowired
    OnBoardingRelatedSchelduleTask onBoardingRelatedSchelduleTask;

    @Autowired
    Migrations migrations;

    /*@Autowired
    BenowService benowService;*/

    @Autowired
    TrialFormRepository trialFormRepository;

    @Autowired
    CreditBlockedExceptionPartnersRepository creditBlockedExceptionPartnersRepository;

    @Autowired
    SDCreditService sdCreditService;

    @Autowired
    com.spice.profitmandi.service.integrations.pinelabs.PinelabsAffordabilityService pinelabsAffordabilityService;

    @Autowired
    com.spice.profitmandi.service.integrations.pinelabs.PinelabsAuthService pinelabsAuthService;

    public void testAffordability() {
        Money orderAmount = new Money("1000000", "INR");
        Money discountAmount = new Money("0", "INR");
        List<ProductDetails> productDetails = Arrays.asList(new ProductDetails(orderAmount, discountAmount, "Alpha_2"));
        OfferDiscoveryRequest offerDiscoveryRequest = new com.spice.profitmandi.service.integrations.pinelabs.dto.OfferDiscoveryRequest(orderAmount, productDetails, new AffordabilityIssuer("12345678"));
        OfferDiscoveryResponse offerDiscoveryResponse = pinelabsAffordabilityService.discoverOffers(offerDiscoveryRequest);
        System.out.println(offerDiscoveryResponse);
    }

    public void test() throws Exception {

        System.out.println(pinelabsAuthService.getAccessToken());
        testAffordability();

        //System.out.println(trialFormRepository.selectByEmailOrMobile("9990381568"));
        /*CreditBlockedExceptionPartner creditBlockedExceptionPartner = creditBlockedExceptionPartnersRepository.select(175139430);
        System.out.println(creditBlockedExceptionPartner);*/
        //this.updatePartnerLimit(175139595);
        //this.releaseBlockedLimit();
        //sdCreditService.resetHardLimit();
        //migrations.migrateSerialInvoiceMap();
        //runOnceTasks.checkRewardsEligibility();
        //hybridEncryptor.getJwt();
        /*System.out.printf("Date|Transaction Id|Serial Number|Sale Amount|Swipe Payout|Discount Payout%n");
        for(int i=0;i<10;i++){
            benowService.parseBenowTransaction(LocalDate.now().minusDays(i));
        }*/
        //onBoardingRelatedSchelduleTask.onboardingEventDelays();
        //runOnceTasks.migrateLimit();866009067657118
        //TODO -- Sales Return Pending
        //runOnceTasks.moveSaleReturnScanToDoa("866009067657118");
        //runOnceTasks.sendCreditExposureReport();
        //runOnceTasks.moveDoaScanToSaleReturn("868770072438608");
        //runOnceTasks.purchasePolicy();
        //purSaleService.moreThen10DaysEgedStockReport();
        //System.out.println(schemeService.testRedis(1, 2L));
        //System.out.println(schemeService.testRedis(1, null));
        // abmReportSender.sendAbmSmartCartReport();
        //this.sendOverDueReport();
        /*List<Integer> orderIds = Arrays.asList(1929879,1932372,1932377,1932386,1932396,1935221,1935563,1935564,1945144,1955256,1955271,1955294,1961255,1961256,1972821,1972823,1972824,1972825,1972826,1974250,1974252,1974253,1974254,1974255,1974256,1974242,1974244,1974245,1974246,1974247,1974248,1976610,1978095,1978136,1978130,1978174,1978156,1978158,1978167,1978169,1978160,1978179,1978175,1978180,1978201,1978200,1978202,1978192,1978198,1978207,1978204,1978457,1979781,1979783,1979784,1979787,1982412,1982413,1986531,1986532,1986533,1991006,1991628,1991629,2016001,2018921,2042948,2047031,2058500,2058522,2058519,2058520,2058546,2058559,2058568,2058575,2058574,2058598,2058594,2058618,2058633,2066491,2067110,2067113,2067118,2067120,2067123,2067132,2067125,2067127,2069974,2069970,2069976,2069985,2069987);
        for (Integer orderId : orderIds) {
            System.out.println("Refund Started - " + orderId);
            orderRepository.refundOrder(orderId, "vinay.p", "Long awaited cancellation", OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY);
            System.out.println("Refund Ended");
        }*/
        //System.out.println(warehouseSupplierInvoiceRepository.selectWarehouseItemAgeing(LocalDateTime.now().minusDays(10), LocalDateTime.now(), null, 0, null));
        /*Offer offer = offerRepository.selectById(6820);
        offerService.sendWhatsapp(offer, Arrays.asList(175140233),"https://images.smartdukaan.com/uploads/campaigns/image2025-09-24/offer-6820.png");*/
        /*List<String> invoices = Arrays.asList("NSDLNOI16527","NSDLNOI16366", "NSDLNOI16394", "NSDLNOI16460");
        List<Loan> loans = new ArrayList<>();
        for (String invoice : invoices) {
            loans.add(loanRepository.selectLoanByInvoice(invoice));
        }
        loans.stream().sorted(Comparator.comparing(x->x.getCreatedOn())).forEach(x-> {
            try {
                sdCreditService.settleLoan(x);
            } catch (ProfitMandiBusinessException e) {
                throw new RuntimeException(e);
            }
        });*/
        //Utils.sendMailWithAttachments(googleMailSender, new String[]{"amit.gupta@smartdukaan.com"}, null, "DTDC AWb", "PFA");*/
    }

    private void releaseBlockedLimit() throws ProfitMandiBusinessException {
        List<Loan> blockedLoans = loanRepository.selectAllBlockedLoans();
        for (Loan blockedLoan : blockedLoans) {
            LoanTransaction loanTransaction = loanTransactionRepository.selectByLoanId(blockedLoan.getId());
            int transactionId = loanTransaction.getTransactionId();
            List<UserWalletHistory> userWalletHistory = walletService.getAllByReference(blockedLoan.getFofoId(), transactionId, WalletReferenceType.PURCHASE);
            int amount = userWalletHistory.get(userWalletHistory.size() - 1).getAmount();
            int loanAmount = blockedLoan.getIntialAmount().intValue();
            if (loanAmount > -amount) {
                sdCreditService.settleBlockedLoan(transactionId, loanAmount + amount);
            }


        }
    }

    @Autowired
    LoanTransactionRepository loanTransactionRepository;

    private void fixBlockedCredit() throws ProfitMandiBusinessException {
        List<Loan> unsettledBlockedLoans = loanRepository.selectAllActiveLoan().stream().filter(x -> x.getFreeDays() >= 365).collect(toList());
        //unsettledBlockedLoans = unsettledBlockedLoans.stream().filter(x -> x.getId() == 52310).collect(toList());
        for (Loan unsettledBlockedLoan : unsettledBlockedLoans) {
            LoanTransaction loanTransaction = loanTransactionRepository.selectByLoanId(unsettledBlockedLoan.getId());
            if (loanTransaction == null) {
                System.out.println("Cant find Transaction Id for Loan - " + unsettledBlockedLoan.getId() + "-- Created On ->" + unsettledBlockedLoan.getCreatedOn());
                continue;
            }
            List<Order> processedOrders = orderRepository.selectAllByTransactionId(loanTransaction.getTransactionId()).stream()
                    .filter(x -> !x.getStatus().equals(OrderStatus.SUBMITTED_FOR_PROCESSING)).collect(toList());
            List<OrderStatus> processedOrderIds = processedOrders.stream().map(x -> x.getStatus()).collect(Collectors.toList());
            System.out.println("processedOrderIds " + processedOrderIds);
            //if(unsettledBlockedLoan !=null) continue;
            double limitToRelease = processedOrders.stream().collect(Collectors.summingDouble(x -> x.getWalletAmount()));
            sdCreditService.releaseBlockedLimit(unsettledBlockedLoan, limitToRelease);


        }
    }

    public static byte[] toByteArray(InputStream input) throws IOException {
        ByteArrayOutputStream buffer = new ByteArrayOutputStream();
        int nRead;
        byte[] data = new byte[16384];
        while ((nRead = input.read(data, 0, data.length)) != -1) {
            buffer.write(data, 0, nRead);
        }
        return buffer.toByteArray();
    }

    private void whatsappTest() throws Exception {
        //liquidationService.processBids(ProfitMandiConstants.BID_CRON_ENUM.TODAY);
        Utils.sendMailWithAttachments(googleMailSender, new String[]{"ranu.rajput@smartdukaan.com"}, null, "Test Mail", "Mail is working");
    }

    private void debitLoanAmountFromWallet() {
        List l1 = Arrays.asList(1234, 1420, 2694, 8129, 12182, 12880, 16289, 16462, 16551, 16625, 16819, 17606, 18902, 19152, 19663, 22409, 22410, 23924, 24058, 24206, 24215, 24220, 24459, 25977, 30585, 31590);
        l1.addAll(Arrays.asList(11849, 12702, 14554, 14643, 19166, 19638, 32824, 33467, 33954, 34017, 34514, 35128, 35229, 35355, 36007, 36351, 36435, 36586, 36608, 36612, 36665, 37076, 37495, 37678, 38519, 38563, 38638, 38690, 38815, 38840, 39015, 39084, 39142, 39379, 39384, 39586, 39636, 39657, 39755, 39788, 39908, 40054, 40220, 40534, 41731, 41962, 42692));
        l1.addAll(Arrays.asList());
    }

    public void test4() throws Exception {
        List<Integer> offerIds = Arrays.asList(2882, 2881, 2880, 2879, 2878, 2877, 2876, 2875, 2874, 2873, 2872, 2871, 2870, 2869, 2868, 2867, 2866, 2865, 2864, 2863, 2862, 2861, 2860, 2859, 2858, 2857, 2856, 2855, 2854, 2853, 2852, 2851, 2850, 2849, 2847, 2846, 2845, 2844, 2843, 2842, 2841, 2840, 2839, 2838, 2837, 2836, 2835, 2834, 2833, 2832, 2831, 2830, 2829, 2828, 2827, 2826, 2825, 2824, 2823, 2822, 2821, 2820, 2819, 2818, 2817, 2816, 2815, 2814, 2813, 2812, 2811, 2810, 2809, 2808, 2807, 2806, 2805, 2804, 2803, 2802, 2801, 2800, 2799, 2798, 2797, 2796, 2795, 2794, 2793, 2792, 2791, 2790, 2789, 2788, 2787, 2786, 2785, 2784, 2783, 2782, 2781, 2780, 2777, 2776, 2775, 2774, 2773, 2772, 2771, 2770, 2769, 2768, 2767, 2766, 2765, 2764, 2763, 2762, 2761, 2760, 2759, 2758, 2757, 2756, 2755, 2754, 2753, 2752, 2751, 2750, 2749, 2748, 2747, 2746, 2745, 2744, 2723, 2722, 2721, 2720, 2719, 2718, 2717, 2716, 2715, 2714, 2713, 2712, 2711, 2710, 2709, 2708, 2707, 2706, 2705, 2704, 2703, 2702, 2701, 2700, 2699, 2698, 2697, 2696, 2695, 2694, 2693, 2692, 2691, 2690, 2494, 2493, 2492, 2491, 2490, 2489, 2488, 2487, 2486, 2485, 2484, 2483, 2482, 2481, 2480, 2479, 2478, 2477, 2476, 2475, 2474, 2473, 2472, 2471, 2470, 2469, 2466, 2465, 2464, 2463, 2462, 2461, 2460, 2459, 2458, 2457, 2456, 2455, 2454, 2453, 2452, 2451, 2450, 2449, 2448, 2447, 2446, 2445, 2444, 2443, 2442, 2441, 2440, 2439, 2407, 2406, 2405, 2404, 2403, 2402, 2401, 2400, 2399, 2398, 2397, 2396, 2395, 2394, 2393, 2392, 2391, 2390, 2389, 2388, 2387, 2386, 2385, 2384, 2383, 2382, 2381, 2117, 2116, 2115, 2114, 2113, 2112, 2111, 2110, 2109, 2108, 2107, 2106, 2105, 2104, 2103, 2102, 2101, 2100, 2099, 2098, 2077);
        for (Integer offerId : offerIds) {
            Offer fromOffer = offerRepository.selectById(offerId);
            CreateOfferRequest createOfferRequest = offerService.getCreateOfferRequest(fromOffer);
            System.out.println("Processing offer Id " + fromOffer);
            offerService.processActivationtOffer(createOfferRequest);
        }

        this.processSidbiDeductions();
    }

    public void test3() throws Exception {
        List<Long> payoutIds = Arrays.asList();
        List<OfferPayout> offerPayouts = offerPayoutRepository.selectAllByIds(payoutIds);
        Map<Integer, List<OfferPayout>> offerPayoutMap = offerPayouts.stream().collect(groupingBy(x -> (int) x.getOfferId()));
        for (Entry<Integer, List<OfferPayout>> offerPayoutEntry : offerPayoutMap.entrySet()) {
            int offerId = offerPayoutEntry.getKey();
            List<OfferPayout> offerPayoutList = offerPayoutEntry.getValue();
            Map<Integer, List<OfferPayout>> partnerOfferPayoutMap = offerPayoutList.stream().collect(groupingBy(x -> (int) x.getFofoId()));
            for (Entry<Integer, List<OfferPayout>> partnerOfferPayoutEntry : partnerOfferPayoutMap.entrySet()) {
                int fofoId = partnerOfferPayoutEntry.getKey();
                List<OfferPayout> partnerPayouts = partnerOfferPayoutEntry.getValue();
                float amount = 0;
                for (OfferPayout offerPayout : partnerPayouts) {
                    amount += offerPayout.getAmount();
                    offerPayout.setStatus(SchemePayoutStatus.REJECTED);
                    offerPayout.setRejectTimestamp(LocalDateTime.now());
                }
                walletService.rollbackAmountFromWallet(fofoId, amount, offerId,
                        WalletReferenceType.ADDITIONAL_SCHEME, "Imeis returned - " + partnerPayouts.size() + "pc(s)", LocalDateTime.now());
            }
        }
    }

    public void test1() throws Exception {
        System.out.println("Hello from test");
        //this.calculateInterestAccured();
        //sendMailOfHtmlFormat(javaMailSender, new String[]{"amit.gupta@smartdukaan.com"}, "hello", null, "Hello");
        //priceDropService.rejectPriceDropsOfApprovedImeis();
        //Create return
        //this.updatePartnerLimit();
        //creditNoteService.sendCnMails(YearMonth.now().minusMonths(2));
        //orderService.createMissingScratchOffers();
        LocalDateTime debitNoteDate = LocalDate.of(2024, Month.APRIL, 30).atStartOfDay();
        Map<Integer, Set<String>> fofoIdImeiReturnMap = new LinkedHashMap<>();
        /*fofoIdImeiReturnMap.put(175139690,    new HashSet<>(Arrays.asList("862054078463472","863862077158219","866202075810858","866202077654494","866335072023651","866518078002718")));
        fofoIdImeiReturnMap.put(175139414,      new HashSet<>(Arrays.asList("356494471684303","356494471857164","356494472062590","861753065078075","863511068904178","863511069732933","864179068109203","864179068112504","864259078789978","866202076254130","866202076254775","866657078259165","866657078272846","867492065809718","867907071107955","868565073021423","868565073022223","868565073386347")));
        fofoIdImeiReturnMap.put(175139603,      new HashSet<>(Arrays.asList("350886063151961","356412387787135","357594162824701","357594162874771","860600078193972","863862077025350","863862077875796","863967075815072","866335073794995","866335074287874","869116067546048")));
        fofoIdImeiReturnMap.put(175139657,      new HashSet<>(Arrays.asList("350933415734746","354324787135850","354324788766356","354990352512960","354990352580181","358142881241184","866611064207332","869116067600464")));
        fofoIdImeiReturnMap.put(175139223,      new HashSet<>(Arrays.asList("350210770983048","350210771169027","350210771179844","350210771180784","863975067090596","866611060326797","868565075758196","869452074016992","869452074609515","869452074609556","869452074976674","869452074978654")));
        fofoIdImeiReturnMap.put(175139557,      new HashSet<>(Arrays.asList("861977073551795","861977073552678","861977074486819","862046062571245","862046067278226","862046067734921","862046067756866","864468070394741","864468071225506","864468071668341","864468073604740","864468074854625","866993074734475")));
        fofoIdImeiReturnMap.put(175139079,      new HashSet<>(Arrays.asList("862046068861921","862282079169299","865388071283496","866335072007753","866335073946256","866518073601712","866518077425993","866518078760471","868938062675394","868938062690799","868938064895552")));
        fofoIdImeiReturnMap.put(175139711,      new HashSet<>(Arrays.asList("861753065266498","861888066009099","864468073703369","866993071640154","869116062800465")));
        fofoIdImeiReturnMap.put(175139488,      new HashSet<>(Arrays.asList("862054076866114","862054076866650","866335071879079","866335077875998","866335077876095","866518078758939","868938062692878")));
        fofoIdImeiReturnMap.put(175138934,      new HashSet<>(Arrays.asList("862054072810231","862054072823150","862054076867674","862054078659590","863862075768456","863862077158193","863975064472490","863975064473233","865388071160033","865610079408035","866335072117370","866335073899471","866335074306153","866335075664477","868938062669876","868938062677895","868938062685997")));
        fofoIdImeiReturnMap.put(4149607  ,   new HashSet<>(Arrays.asList("864468073415741","864468075548523","864468075549927","864468079499426","866657074106022","868938062686656","868938062696978","868938062699352","868938064895198","868938066363799")));
        fofoIdImeiReturnMap.put(175139138,      new HashSet<>(Arrays.asList("862286066217631","864259076533691","864468073358842","864487061610014","864487061675918","866083066080028","866518078760950","866518078768979","866657075343848","868544061108518","868544061138432","869116065510848","869116065519781","869452074116891","869452074972939")));
        fofoIdImeiReturnMap.put(175139608,      new HashSet<>(Arrays.asList("350201087552063","350201087559944","350201087635660","350201087649760","350201087843140","350201088470224","350201088696687","350201088697180","354324783756923","354324786101366","355472822024008","355472822025625","860388068055779","860388068262219","866518078762337","866518078767534","868544061141493","868544061141774","868544061141816","868938064895719","868938066360910")));
        fofoIdImeiReturnMap.put(175139731,      new HashSet<>(Arrays.asList("860600079214413","861753063502217","861753065084172","861753065084636","863975064472094","863975064473076","863975064477218","864468074456462","865883068726994","866657074641481","866657078016946","868938062699832","868938062700754")));
        fofoIdImeiReturnMap.put(175138958,      new HashSet<>(Arrays.asList("863862075766393","864179064784108","866202075811732","866202076252654","866202078005571","868544060277439")));
        fofoIdImeiReturnMap.put(175139612,      new HashSet<>(Arrays.asList("864468073474946","864468073477808","864468075031082","864468075280689","864468075745723","864468079481747","864468079483123","866657074538125","866657074538141","866657074573106","867467071856465","867467071875465","867467071891223","867467071913845","867467072729125","867467072760088")));
        fofoIdImeiReturnMap.put(175139479,      new HashSet<>(Arrays.asList("863975062106595","863975064472672","863975064472870","863975064472953","863975064482333","863975064483570","863975064484719","863975064662694","863975064666455","863975064668931","863975067090257","863975067090810","863975067230218","863975067232230","866186062852892","866186062853973","866186062855051","866186062855077","866186063250773","866186063251094","866186063251219","866186063251318")));
        fofoIdImeiReturnMap.put(175139245,      new HashSet<>(Arrays.asList("354324787133962","354324787569397","354324787602750","863816078139719","863816078448318","866335070534139")));
        fofoIdImeiReturnMap.put(175139324,      new HashSet<>(Arrays.asList("863718060410316","863862075060755","865610074109257","866518078844457","866518079735159")));
        fofoIdImeiReturnMap.put(175139576,      new HashSet<>(Arrays.asList("354324789013261","861128071288044","862736064686138","863718062236859","863816074642674","863816074646691","864259078939631","867467071870367","867467072760625","868565073047048")));
        fofoIdImeiReturnMap.put(175139150,      new HashSet<>(Arrays.asList("860492060436217","861977073052257","862054073026738","862054073052056","863862077158938","866993074147710","867907071407074","868938060257450","868956064835055")));
        fofoIdImeiReturnMap.put(175139463,      new HashSet<>(Arrays.asList("862181066909552","863816077757719","863816078448516","864214068977273","866335070447936","866335071699212","866335071699592","866335071700291")));
        fofoIdImeiReturnMap.put(175139722,      new HashSet<>(Arrays.asList("358250293364682","358250294683627","358250295102007","861858064421273","861888066009537","864214068983198","864259078491112","864259078787733","865883068445694","866335072878930","866518078371014","868492068014968","868565072254660","868565073401468","868565073465661")));
        fofoIdImeiReturnMap.put(168311083,      new HashSet<>(Arrays.asList("863718064506176","864468070873488","865883068175572","866335071204435","866335072093738","866518077064313","866657077386209","866657077862282","868938061427359")));
        fofoIdImeiReturnMap.put(175139691,      new HashSet<>(Arrays.asList("863718061338714","863718061339258")));
        fofoIdImeiReturnMap.put(175139395,      new HashSet<>(Arrays.asList("860492060436910","861220079009850","862054077288730","863511068660374","863718060300574","863718060617936","866335072874111","869452070894897")));
        fofoIdImeiReturnMap.put(175139621,      new HashSet<>(Arrays.asList("351240553452191","351240553483527","351240553502920","354990353485570","860492062979750","860492062980998","864214068585274","864214068784398","864214068795378","864214068946534","864214068946757","864214068982075","868938061418754","868938061427391","868938062623139")));
        fofoIdImeiReturnMap.put(175139638,      new HashSet<>(Arrays.asList("863862077159191","866518078780438","866518079750471","868428060238049","868938061532836","868938062619517")));
        fofoIdImeiReturnMap.put(175139313,      new HashSet<>(Arrays.asList("350837023070346","350837023075022","350837023075568","350837023075782","352256988699643","862054076944275","863862075060359","863862075520055","864468075575948","865388072943635","868938060248574","868938061422830","868938062630894","869116061122184","869452070893717","869452073157912","869452074054373","869452075220890")));
        fofoIdImeiReturnMap.put(175139513,      new HashSet<>(Arrays.asList("861977073670355","863718060618439","864468075204986","868024064295935","868024065473754","868024065567811","868024065763972","868956063947893")));
        fofoIdImeiReturnMap.put(175139524,      new HashSet<>(Arrays.asList("350933416006946","350933416007944","351115165469199","351115165471237","351115167775437","351115167778431","351115167784413","351688420101625","355300592141142","358250295005267","358250295284607","359197386833520","359197386864947","862054073974671","862054073979597","862054074261730","862054074425954","862054074426036","864468073609384","866518078777392","868544060023239","868938060253772","868938060258516","868938061419018")));
        fofoIdImeiReturnMap.put(175139359,      new HashSet<>(Arrays.asList("358250293060942","358250293061387","863718063044252","863862077160991","866335072536793","866335072536975","866335072537130","866335072726691","866335072726733","866335073628532","866335073925250","866335073925292","866335073926233","866335075267156")));
        fofoIdImeiReturnMap.put(175139397,      new HashSet<>(Arrays.asList("861977073659010","863511069721050","864468071611101","864468074432844","864468074435284")));
        fofoIdImeiReturnMap.put(175139677,      new HashSet<>(Arrays.asList("351115165472839","351115165476095","351115165476251","351115165477739","351115165483430","351115165485377","351115165486516","351115165487191","351115166897992","351115166898438","351115166900416","351115166900879","351115166902438","351115166903816","351115166903857","351115166903915","351115166904475","351115166905753","351115166906454","351115166906595","351115166907015","351115166907759","351115166907957","351115166908872","351115166909813","864259078771372","864468075692644","864468079730101","864468079736744","866657076858687","868565073398649","869116061118042","869116061261081")));
        fofoIdImeiReturnMap.put(175139460,      new HashSet<>(Arrays.asList("860492061918494","862054079983072","862054079983171","866335070533032","866518078995119","866518079014993","868938060095017")));
        fofoIdImeiReturnMap.put(175139585,      new HashSet<>(Arrays.asList("358250293614706","358250293614904","358250294602908","358250294603120","358250295002181","861977074487718","863718061173699","863816078050254","865883068445777","866335070453439","866335070526812","866335071204351","866518078640293","869116067596803")));
        fofoIdImeiReturnMap.put(175139726,      new HashSet<>(Arrays.asList("861858064489411","863718063927654","864468073702221","864468073928784","864468074193149","865883068445819","866657078378742","866657078498904","867949078179850","867949078180494","868544060023197")));
        fofoIdImeiReturnMap.put(175139532,      new HashSet<>(Arrays.asList("863718060615096","863718060615617","863718060727537","863816077797897","866335073405139","868565072217287","868565072264560","868956065132510")));
        fofoIdImeiReturnMap.put(175139462,      new HashSet<>(Arrays.asList("865610074119819","865610074120536","866335072878658","866335072882098","866335073617030")));
        fofoIdImeiReturnMap.put(175139259,      new HashSet<>(Arrays.asList("350210771180644","355300593052348","860492061918213","861858064422651","864259078402572","864259078924310","866335070769719","866335071700556","866335071742558","866657073553208","867949078178530","867949078692472","868024065518616","868024065619091","869116067492540")));
        fofoIdImeiReturnMap.put(175139344,      new HashSet<>(Arrays.asList("350115571135781","354990352587723","358250293362843","358250293363148","862736064732510","865610070849617","865610070851290","866518078899998","868938062623378","868956065234613")));
        fofoIdImeiReturnMap.put(175139602,      new HashSet<>(Arrays.asList("861128070090482","868565072242160","868565072243044","868565072261004","868565073051644")));
        fofoIdImeiReturnMap.put(175139605,      new HashSet<>(Arrays.asList("861515075217922","861515076369466","863816074206397","863816075080072","864259078661755","864468075627368","867467071860541","868565072177903","868565073075726","868565073401625","868938060248012","868938066709652","869452074088611","869452074609234")));
        fofoIdImeiReturnMap.put(175139640,      new HashSet<>(Arrays.asList("358250293363429","358250293363684","861753065277792","863511069733998","864214068943713","864259078496574","864259078518773","864487060042870","864487060050352","865883069366170","866518079782391","867949078692639","867949078692852","868273068551617","868938066464571","869452073150115")));
        fofoIdImeiReturnMap.put(175139346,      new HashSet<>(Arrays.asList("356494471614953","860600076022215","861765072074616","861977070097412","864468074885181","864468076046105","865610073263139","865883065558994","866335075002538","866657079093605","867907070983216")));
        fofoIdImeiReturnMap.put(175139489,      new HashSet<>(Arrays.asList("354324789015209","354324789081797","861515075189824","861515075575501","861977070268997","864468072790144","864468072983921","864468075233225","866657077951960","869452071082492","869452071982378","869452074617070")));
        fofoIdImeiReturnMap.put(175139508,      new HashSet<>(Arrays.asList("861515075367024","864468072781283","864468074453386","864468075200448","864468076050669","866083062293849")));
        fofoIdImeiReturnMap.put(175139716,      new HashSet<>(Arrays.asList("350832440575209","358250295571425","860600076019492","861753063494373","861753063501151","864468073696100","864468074855689","865883065542311","865883067956030","866202075825450","866202077663297","866335073632591","866657078023769")));
        fofoIdImeiReturnMap.put(175139727,      new HashSet<>(Arrays.asList("355300592353325","355300593005387","861765072344258","862046060196128","864468073908604","864468073910121","864468073911269","867437074118411","868024064300099","868024065464977","868024065468218","869116065414389")));
        fofoIdImeiReturnMap.put(175139521,      new HashSet<>(Arrays.asList("860600071402594","860600071416719","861888064397496","861977073658616","864468070509587","864468073866406","866335074999056","866657077949642","866657078406964","866993072229072","867437070891698")));
        fofoIdImeiReturnMap.put(175139523,      new HashSet<>(Arrays.asList("354324785409950","354324788632830","354990352587939","358250295736085","358250295805187","358311601935629","863967076359575","866657074163502","867907070986318","869452071963378","869452074611750")));
        fofoIdImeiReturnMap.put(175139697,      new HashSet<>(Arrays.asList("862282078030112","862282078867117","863975067709930","863975068443810","864468072828860","864468073705786","864468075276489","864468075277685","865610073830739","866335073264577","866518079802116","866657074518903","866657077949980","868565073058664","868938060985639","869116065613949")));
        fofoIdImeiReturnMap.put(175139622,      new HashSet<>(Arrays.asList("358250293381926","358250293382304","358250293382601","358250294076046","358250294078109","358250294078463","358250294602064","358250294602429","358250294603062","358250294603203")));
        fofoIdImeiReturnMap.put(175139699,      new HashSet<>(Arrays.asList("863718064441317","865610074942897","866202077653173","866202077653553","866335072262671","866335073632211","866335074805519")));
        fofoIdImeiReturnMap.put(175139741,      new HashSet<>(Arrays.asList("861515071386101","861515074123402","861515074124186","861977073047752","861977073658574","864468075082804","864468076049869","865883065542139","865883067957111","866518070599679","866518079800672","866657078018587","866657078270162","866657079236220")));
        fofoIdImeiReturnMap.put(175139114,      new HashSet<>(Arrays.asList("861888066708856","861977074487692","866335070051217","866335070517274","866993074145631")));
        fofoIdImeiReturnMap.put(175139531,      new HashSet<>(Arrays.asList("863816077976194","863816079685710","866335070997336","866335074729354","866335075514698")));
        fofoIdImeiReturnMap.put(175139386,      new HashSet<>(Arrays.asList("350210771192805","358250295053846","860388068563434","863862077333374","864468075193221","864562077193757","864718076084310","866202077464092","866202077742190","866518077001513","868565079682020","868956065252516")));
        fofoIdImeiReturnMap.put(175139452,      new HashSet<>(Arrays.asList("350835033581484","350835034344999","350835035973283","350886063052599","351115165490559","351115165492290","351115165492811","354324788947428","354996630411503","354996631256261","355480685142494","355480686969416","359197386860549","861977073020494","862046067791525","862054077982977","866335072889499","867467072712048","868938062127511")));
        fofoIdImeiReturnMap.put(175139306,      new HashSet<>(Arrays.asList("863718064547394","864718076075516","864718076083478","866202077463631","866202077470057")));
        fofoIdImeiReturnMap.put(175139733,      new HashSet<>(Arrays.asList("350210771180008","861515071845668","861888067164877","866335070489094","866518076378276","868565072242269")));
        fofoIdImeiReturnMap.put(175138824,      new HashSet<>(Arrays.asList("862054078573478","863862074697490","863862075442375","864468074485545","866335070216315","866657078016169","866657078449402","869116065542247")));
        fofoIdImeiReturnMap.put(175139511,      new HashSet<>(Arrays.asList("862054075642557","862054075643530","862054076379357","862054077978934","863816078158016","868938062108974")));
        fofoIdImeiReturnMap.put(175139701,      new HashSet<>(Arrays.asList("863862078258331","866611061131790")));
        fofoIdImeiReturnMap.put(175139552,      new HashSet<>(Arrays.asList("861753065084958","866202075814439","866202075825633","866202075864194","866202076138754","866202076252811","866202077646870","866202077651094","866202077653876","866202077659956","866202077660293","866202077663339","866202078006397","866202078006611","866202078008914","866657078023280")));
        fofoIdImeiReturnMap.put(175139499,      new HashSet<>(Arrays.asList("351115165467359","351115166905951","355358277809813","355358277814052","356049795822532","358250290682060","863862075504950","864468073944906","864468076048721","866657079234365")));
        fofoIdImeiReturnMap.put(175139630,      new HashSet<>(Arrays.asList("357349229650288","358250290499184","358250294552947","358250294590301","860946078570556","860946078571059","861977073552215","861977073657790","861977073657832","861977073658731","861977074660850","862054075627517","862054075632756","862054075642979","862054076374879","863967071220830")));
        fofoIdImeiReturnMap.put(175139720,      new HashSet<>(Arrays.asList("865388072300075","866335073071691","866335073073697","866335073390398")));
        fofoIdImeiReturnMap.put(175138854,      new HashSet<>(Arrays.asList("50115570082950","351688420279827","351688420575703","354324788647382","357594162666201","359333606194530","863862075760198")));
        fofoIdImeiReturnMap.put(175139569,      new HashSet<>(Arrays.asList("350201087565545","350201087948386","350201087949087","350210771180081","351088890316094","351088890318454","351088896121530","351088896127776","351088896127958","351088896133410","351088896133832","352476512881224","352476512979242","354448512964985","357888147926932","357888147928292","357888147928557","357888147929050","357888147929795","357888147933094","861128070182420","861753065277115","863511069713933","866657077333680","868273068567092","868565072301768","869116062743905","869452071985157","869452073161393")));
        fofoIdImeiReturnMap.put(175139610,      new HashSet<>(Arrays.asList("862736063981738","863718063044617","866335072870176","866335072870598")));
        fofoIdImeiReturnMap.put(175139366,      new HashSet<>(Arrays.asList("354324783144864","354324788632608","354889661849990","354990353239423","863967070558271","864718076129396","866657077789543","866657078285368","866657078508066","869116060977828","869116061054429","869116061208546")));
        fofoIdImeiReturnMap.put(175139412,      new HashSet<>(Arrays.asList("351115165488595","351115165491318","351115165491359","351115165491458","351115165492332","355358278026573","355358278081610","861888063460717","861977073052018","863816078633919","865883069477035","866335074538870","866993071639594")));
        fofoIdImeiReturnMap.put(175139504,      new HashSet<>(Arrays.asList("862054072660792","862054075971873","862054076312374","862054077948630","863816075363171","863862075442334","864718075894230","868938062122017","868938065268932","869452071940632","869452073034897","869452073158233","869452073158498","869452074159735")));
        fofoIdImeiReturnMap.put(175139201,      new HashSet<>(Arrays.asList("863718061956671","863862075497734","863862075596915","864718076078510","864718076134834","866335070211639","866611060437032","866611061133812")));
        fofoIdImeiReturnMap.put(175139362,      new HashSet<>(Arrays.asList("350201087545646","350201087547048","350201087547709","350201087548764","350201087597043","350201087597167","350201087597464","350201087597787","350201087598165","350201087600789","863718062929271","865883069477191","865883069477530","866335071174133","866518078023870","866518078243072","868938061053494")));
        fofoIdImeiReturnMap.put(175139661,      new HashSet<>(Arrays.asList("860946077691031","861977074661155","862054073218855","866335070523694","867940069973476","868938062656477")));*/
        fofoIdImeiReturnMap.put(175139556, new HashSet<>(Arrays.asList("863718063534013", "866335072895173", "866335073390810")));
        fofoIdImeiReturnMap.put(175139721, new HashSet<>(Arrays.asList("358250294549067", "865883067508138", "866993072210619")));
        /*fofoIdImeiReturnMap.put(175139455,    new HashSet<>(Arrays.asList("358293804087913","866518078020694","866518078021650","866518078243676","866518078571936")));
        fofoIdImeiReturnMap.put(175138674,      new HashSet<>(Arrays.asList("351115165482275","359197386868583","862054074428875","862054077945693","862054078556879","863967070559535","863975068705671","863975068705770","864468076196124","866186064235971","866186066185117","866186066186917","866518073607370","866518079641514","868938060476456","868938060501535","868938060717990","868938062656931")));
        fofoIdImeiReturnMap.put(175139559,      new HashSet<>(Arrays.asList("863718069016890","863816078879256","864468075283881","866335073245436","866335073403571","866657074268327","868024065473853","868956065899993","869452070362895","869452074030274")));
        fofoIdImeiReturnMap.put(175139575,      new HashSet<>(Arrays.asList("354324786659199","860388067452035","861220078009919","863816078878134","863862075841154","865388070857498","867907071407116")));
        fofoIdImeiReturnMap.put(175139686,      new HashSet<>(Arrays.asList("866657078019106","866657078032869")));*/

        //this.processOrdersToRebill(fofoIdImeiReturnMap, debitNoteDate);
        purchaseReturnService.fixReturns(fofoIdImeiReturnMap, debitNoteDate);

    }


    private void processOrdersToRebill(Map<Integer, Set<String>> fofoIdImeiReturnMap, LocalDateTime debitNoteDate) throws Exception {


        for (Entry<Integer, Set<String>> fofoIdImeiReturnEntry : fofoIdImeiReturnMap.entrySet()) {
            int fofoId = fofoIdImeiReturnEntry.getKey();
            Set<String> imeis = fofoIdImeiReturnEntry.getValue();
            List<InventoryItem> inventoryItems = inventoryItemRepository.selectByFofoIdSerialNumbers(fofoId, imeis, true);
            for (InventoryItem inventoryItem : inventoryItems) {
                //inventoryItem.getId();
                purchaseReturnService.returnInventoryItem(fofoId, true, inventoryItem.getId(), ReturnType.GOOD);
                //Approve return
                PurchaseReturnItem purchaseReturnItem = purchaseReturnItemRepository.selectByInventoryItemId(inventoryItem.getId());
                purchaseReturnItem.setStatus(PurchaseReturnStatus.APPROVED);
                purchaseReturnItem.setApproveTimestamp(LocalDateTime.now());
            }
            Map<Purchase, List<InventoryItem>> purchaseInventoryItemsMap = purchaseReturnService.getPurchaseMapForApprovedReturns(fofoId);
            int walletAmount = walletService.getWalletAmount(fofoId);
            List<DebitNote> debitNotes = purchaseReturnService.generateDebitNotes(purchaseInventoryItemsMap);
            int walletDiff = walletAmount - walletService.getWalletAmount(fofoId);
            if (walletDiff > 0) {
                int reference = walletService.getManualReference(WalletReferenceType.OTHERS);
                walletService.addAmountToWallet(fofoId, reference, WalletReferenceType.ADVANCE_AMOUNT, "Sytematically added for internal PO", (float) walletDiff + 5000, LocalDateTime.now());
            }
            List<CartItem> cartItems = new ArrayList<>();
            for (DebitNote debitNote : debitNotes) {
                debitNote.setCreateTimestamp(debitNoteDate);
                //Try to refund amount per debit note
                cartItems.addAll(this.refundDnAmount(debitNote));
            }
            //If order created
            Map<Integer, List<CartItem>> warehouseCartItemsMap = cartItems.stream().collect(groupingBy(x -> x.getWarehouseId()));
            for (Entry<Integer, List<CartItem>> warehouseCartItemsEntry : warehouseCartItemsMap.entrySet()) {

                List<CartItem> warehouseCartItems = warehouseCartItemsEntry.getValue();

                List<CartItem> mergedCartItems = warehouseCartItems.stream()
                        .collect(Collectors.collectingAndThen(
                                groupingBy(x -> x.getItemId(), Collectors.collectingAndThen(
                                        Collectors.reducing((a, b) -> {
                                            a.setQuantity(a.getQuantity() + b.getQuantity());
                                            return a;
                                        }), Optional::get)),
                                m -> new ArrayList<>(m.values())));

                double walletTotalAmount = mergedCartItems.stream().mapToDouble(x -> x.getQuantity() * x.getSellingPrice()).sum();

                com.spice.profitmandi.dao.entity.user.User user = userRepository.selectById(fofoId);
                cartService.clearCart(user.getActiveCartId());
                cartService.addItemsToCart(user.getActiveCartId(), mergedCartItems);

                UserCart userCart = new UserCart();
                userCart.setCartId(user.getActiveCartId());
                userCart.setUserId(user.getId());

                int transactionId = transactionService.createTransactionInternally(userCart, walletTotalAmount, warehouseCartItemsEntry.getKey());
                commonPaymentService.payThroughWallet(transactionId);
                transactionService.processTransaction(transactionId, 0);
            }
        }
    }

    @Autowired
    CartService cartService;
    @Autowired
    UserRepository userRepository;

    @Autowired
    WarehouseScanRepository warehouseScanRepository;

    @Autowired
    WarehouseInventoryService warehouseInventoryService;

    @Autowired
    CommonPaymentService commonPaymentService;

    @Autowired
    LineItemImeisRepository lineItemImeisRepository;


    //Also create order
    private List<CartItem> refundDnAmount(DebitNote debitNote) throws ProfitMandiBusinessException {
        List<Integer> inventoryItemIds = purchaseReturnItemRepository.selectAllByDebitNoteId(debitNote.getId()).stream().map(x -> x.getInventoryItemId()).collect(Collectors.toList());
        Map<Integer, Long> inventoryItemCountMap = inventoryItemIds.stream().collect(groupingBy(x -> x, Collectors.counting()));
        List<InventoryItem> inventoryItems = inventoryItemRepository.selectAllByIds(new ArrayList<>(inventoryItemCountMap.keySet()));
        Purchase purchase = purchaseRepository.selectById(inventoryItems.get(0).getPurchaseId());
        String invoice = purchase.getPurchaseReference();
        List<Order> orders = orderRepository.selectByInvoiceNumber(invoice);
        Set<Integer> orderIds = orders.stream().map(x -> x.getId()).collect(Collectors.toSet());
        Order order = orders.get(0);
        float totalAmount = 0;
        int totalQty = 0;
        Map<Integer, CartItem> itemIdCartItemMap = new HashMap<>();

        // Batch fetch all TagListings by itemIds to avoid N+1 queries
        Set<Integer> allItemIds = inventoryItems.stream()
                .map(InventoryItem::getItemId)
                .collect(Collectors.toSet());
        List<TagListing> allTagListings = tagListingRepository.selectByItemIdsAndTagIds(allItemIds, null);
        Map<Integer, TagListing> itemIdTagListingMap = allTagListings.stream()
                .collect(Collectors.toMap(TagListing::getItemId, t -> t, (u, v) -> u));

        List<CartItem> cartItems = new ArrayList<>();
        for (InventoryItem inventoryItem : inventoryItems) {
            TagListing tagListing = itemIdTagListingMap.get(inventoryItem.getItemId());
            long quantity = inventoryItemCountMap.get(inventoryItem.getId());
            totalAmount += inventoryItem.getUnitPrice() * quantity;
            totalQty += quantity;
            if (!itemIdCartItemMap.containsKey(inventoryItem.getItemId())) {
                CartItem cartItem = new CartItem();
                cartItem.setItemId(inventoryItem.getItemId());
                cartItem.setSellingPrice(tagListing.getSellingPrice());
                cartItem.setWarehouseId(order.getWarehouseId());
                cartItems.add(cartItem);
                itemIdCartItemMap.put(inventoryItem.getItemId(), cartItem);
            }
            CartItem cartItem = itemIdCartItemMap.get(inventoryItem.getItemId());
            cartItem.setQuantity((int) quantity + cartItem.getQuantity());
        }
        String rollbackDescription = String.format("Refunded against DN %s, %d pc(s)", debitNote.getDebitNoteNumber(), totalQty);
        walletService.addAmountToWallet(debitNote.getFofoId(), order.getTransactionId(), WalletReferenceType.PURCHASE, rollbackDescription, totalAmount, LocalDateTime.now());
        //Before creating order lets try to in the stock

        //All scans pertaining to specific Invoices
        List<WarehouseScan> warehouseScans = warehouseScanRepository.selectAllByOrderIds(new ArrayList<>(orderIds));
        Map<Integer, Integer> inventoryItemIdOrderIdMap = new HashMap<>();
        try {
            inventoryItemIdOrderIdMap = warehouseScans.stream().collect(Collectors.toMap(x -> x.getInventoryItemId(), x -> x.getQuantity()));
        } catch (Exception e) {
            LOGGER.info("Could not process for fofoId - {}, {}", debitNote.getFofoId(), warehouseScans);
        }
        List<Integer> warehouseInventoryItemIds = warehouseScans.stream().filter(x -> !(x.getType().equals(in.shop2020.warehouse.ScanType.SALE_RET) || x.getType().equals(in.shop2020.warehouse.ScanType.SALE_RET_UNUSABLE)))
                .map(x -> x.getInventoryItemId()).collect(Collectors.toList());
        List<WarehouseInventoryItem> warehouseInventoryItems = warehouseInventoryItemRepository.selectAllByIds(warehouseInventoryItemIds);

        Map<String, WarehouseInventoryItem> serialNumberWarehouseInventoryItemMap = warehouseInventoryItems.stream().filter(x -> StringUtils.isNotBlank(x.getSerialNumber())).collect(Collectors.toMap(x -> x.getSerialNumber(), x -> x));

        for (InventoryItem inventoryItem : inventoryItems) {
            if (StringUtils.isBlank(inventoryItem.getSerialNumber())) {
                //As of now this should be happening as  dn are specifically for serialized devices
                /*int returnQty = inventoryItemCountMap.get(inventoryItem.getId()).intValue();
                List<WarehouseInventoryItem> warehouseInventoryItemList = itemIdWarehouseInventoryItemsMap.get(inventoryItem.getItemId());
                warehouseInventoryService.returnNonSerializedQty(warehouseInventoryItem)*/
            } else if (StringUtils.isNotBlank(inventoryItem.getSerialNumber())) {
                String serialNumber = inventoryItem.getSerialNumber();
                WarehouseInventoryItem warehouseInventoryItem = serialNumberWarehouseInventoryItemMap.get(serialNumber);
                warehouseInventoryItem.setLastScanType(in.shop2020.warehouse.ScanType.SALE_RET);
                warehouseInventoryService.addQuantity(warehouseInventoryItem.getId(), 1);
                WarehouseScan warehouseScan = new WarehouseScan();
                warehouseScan.setInventoryItemId(warehouseInventoryItem.getId());
                warehouseScan.setQuantity(1);
                warehouseScan.setOrderId(inventoryItemIdOrderIdMap.get(warehouseInventoryItem.getItemId()));
                warehouseScan.setScannedAt(LocalDateTime.now());
                warehouseScan.setType(in.shop2020.warehouse.ScanType.SALE_RET);
                warehouseScanRepository.persist(warehouseScan);

            }
        }

        //Create Order as well
        return cartItems;
    }

    void addToCartAndCreateOrder() {

    }

    @Autowired
    WarehouseDebitNoteRepository warehouseDebitNoteRepository;
    @Autowired
    WarehouseDebitNoteLineRepository warehouseDebitNoteLineRepository;

    @Autowired
    WarehouseInvoiceItemRepository warehouseInvoiceItemRepository;
    @Autowired
    WarehouseSupplierInvoiceRepository warehouseSupplierInvoiceRepository;

    @Autowired
    PurchaseOrderService purchaseOrderService;

    @Autowired
    VendorCatalogPricingLogRepository vendorCatalogPricingLogRepository;

//    private void checkItelImeiActivationNew(LocalDate now, int i) {
//    }

    @Autowired
    SessionFactory sessionFactory;


    public void sendMailForSamsungRebilling() throws Exception {
        List<BilledImeiModel> samsungModels = warehouseInventoryItemRepository.findByBillingDateBrand(LocalDate.now(), "Samsung");
        LOGGER.info("SamsungModels {}", samsungModels);
        samsungModels = samsungModels.stream().sorted(Comparator.comparing(x -> x.getPcmDate() == null ? -1 : 1)).collect(Collectors.toList());
        if (samsungModels.size() > 0) {
            List<String> headers = Arrays.asList("Partner Id", "Code", "Name", "City", "Brand", "Model Name", "Model Number", "Color", "Serial Number", "Remarks");
            List<List<?>> rows = new ArrayList<>();
            for (BilledImeiModel billedImeiModel : samsungModels) {
                List<Serializable> row = Arrays.asList(billedImeiModel.getFofoId(), billedImeiModel.getStoreCode(), billedImeiModel.getStoreName(), billedImeiModel.getStoreCity(), billedImeiModel.getBrand(), billedImeiModel.getModelName(), billedImeiModel.getModelNumber(), billedImeiModel.getColor(), billedImeiModel.getSerialNumber(), billedImeiModel.getPcmDate() == null ? "PCM date Missing" : "Rebill Imeis");
                rows.add(row);
            }
            ByteArrayOutputStream baos = FileUtil.getCSVByteStream(headers, rows);
            String[] sendToArray = new String[]{"kamini.sharma@smartdukaan.com", "praveen.sharma@smartdukaan.com"};
            String fileName = "Imeis need Rebilling -" + FormattingUtils.formatDate(LocalDateTime.now()) + ".csv";
            Utils.sendMailWithAttachment(googleMailSender, sendToArray, new String[]{"tarun.verma@smartdukaan.com"}, "Samsung Rebilling IMEI Summary", "PFA", fileName, new ByteArrayResource(baos.toByteArray()));
        }
    }

    public void sendPartnerInvestmentDetails(List<String> sendTo) throws Exception {
        LocalDate yesterDay = LocalDate.now().minusDays(1);
        List<FofoStore> fofoStores = fofoStoreRepository.selectActiveStores();
        Map<Integer, CustomRetailer> customRetailerMap = retailerService.getFofoRetailers(fofoStores.stream().map(x -> x.getId()).collect(Collectors.toList()));

        List<String> headers = Arrays.asList("Code", "Outlet name", "State Manager", "Territory/Team Lead", "Wallet Amount", "In Stock Amount", "Activated Stock", "Return In Transit Stock", "Unbilled Amount", "Grn Pending Amount", "Min Investment", "Investment Amount", "Investment Short", "Unbilled Qty", "Short Days");
        List<List<?>> rows = new ArrayList<>();
        Map<Integer, List<?>> partnerRowsMap = new HashMap<>();

        Map<Integer, FofoReportingModel> partnerIdSalesHeaderMap = this.getPartnerIdSalesHeaders();

        Map<Integer, Integer> shortDaysMap = partnerDailyInvestmentRepository.selectAll(LocalDate.now().withDayOfMonth(1), LocalDate.now()).stream().collect(groupingBy(
                x -> x.getFofoId(), Collectors.summingInt(x -> x.getShortPercentage() > 10 ? 1 : 0)));

        for (FofoStore fofoStore : fofoStores) {
            LOGGER.info("Fofo Store {},  {}", fofoStore.getId(), fofoStore.getCode());
            int fofoId = fofoStore.getId();
            PartnerDailyInvestment partnerDailyInvestment = partnerInvestmentService.getInvestment(fofoId, 1);
            partnerDailyInvestment.setDate(yesterDay);

            try {
                partnerDailyInvestmentRepository.persist(partnerDailyInvestment);
                shortDaysMap.put(fofoId, shortDaysMap.get(fofoId) + (partnerDailyInvestment.getShortPercentage() > 10 ? 1 : 0));
            } catch (Exception e) {
                // ignore the exceptions during persist
            }

            CustomRetailer retailer = customRetailerMap.get(fofoStore.getId());
            if (retailer == null || partnerIdSalesHeaderMap.get(fofoStore.getId()) == null) {
                LOGGER.info("Could not find retailer with retailer Id {}", fofoStore.getId());
                continue;
            }
            FofoReportingModel reportingModel = partnerIdSalesHeaderMap.get(fofoStore.getId());
            List<Serializable> row = new ArrayList<>();
            row.addAll(Arrays.asList(reportingModel.getCode(), reportingModel.getBusinessName(), reportingModel.getRegionalManager(), reportingModel.getTerritoryManager()));
            row.addAll(
                    Arrays.asList(partnerDailyInvestment.getWalletAmount(), partnerDailyInvestment.getInStockAmount(), partnerDailyInvestment.getActivatedStockAmount() == 0 ? "-" : "(" + partnerDailyInvestment.getActivatedStockAmount() + ")", 0, partnerDailyInvestment.getUnbilledAmount(), partnerDailyInvestment.getGrnPendingAmount(), partnerDailyInvestment.getMinInvestment(), partnerDailyInvestment.getTotalInvestment(), partnerDailyInvestment.getShortInvestment(), partnerDailyInvestment.getUnbilledQty(), shortDaysMap.get(fofoId)));
            partnerRowsMap.put(fofoStore.getId(), row);
            rows.add(row);

        }

        String fileName = "InvestmentSummary-" + FormattingUtils.formatDate(LocalDateTime.now()) + ".csv";

        if (sendTo == null) {
            for (Entry<String, Set<Integer>> storeGuyEntry : csService.getAuthUserPartnerIdMapping().entrySet()) {
                List<List<?>> filteredRows = storeGuyEntry.getValue().stream().map(x -> partnerRowsMap.get(x)).filter(x -> x != null).collect(Collectors.toList());
                ByteArrayOutputStream baos = FileUtil.getCSVByteStream(headers, filteredRows);
                String[] sendToArray = new String[]{storeGuyEntry.getKey()};
                Utils.sendMailWithAttachment(googleMailSender, sendToArray, null, "Franchise Investment Summary", "PFA", fileName, new ByteArrayResource(baos.toByteArray()));
            }
            sendTo = Arrays.asList("tarun.verma@smartdukaan.com", "kamini.sharma@smartdukaan.com", "neeraj.gupta@smartdukaan.com", "amit.gupta@shop2020.in", "manish.gupta1@smartdukaan.com", "niranjan.kala@smartdukaan.com");
        }

        ByteArrayOutputStream baos = FileUtil.getCSVByteStream(headers, rows);
        String[] sendToArray = sendTo.toArray(new String[sendTo.size()]);
        Utils.sendMailWithAttachment(googleMailSender, sendToArray, null, "Franchise Investment Summary", "PFA", fileName, new ByteArrayResource(baos.toByteArray()));

    }

    private Map<Integer, FofoReportingModel> getPartnerIdSalesHeaders() throws ProfitMandiBusinessException {
        Map<String, SaleRoles> partnerEmailSalesMap = new HashMap<>();
        Map<String, SaleRoles> partnerEmailRBMMap = new HashMap<>();
        Map<String, SaleRoles> partnerEmailABMMap = new HashMap<>();

        List<Position> positions = positionRepository.selectPositionByCategoryIds(Arrays.asList(ProfitMandiConstants.TICKET_CATEGORY_SALES, ProfitMandiConstants.TICKET_CATEGORY_RBM, ProfitMandiConstants.TICKET_CATEGORY_ABM));
        Map<Integer, AuthUser> authUsersMap = authRepository.selectAllActiveUser().stream().collect(Collectors.toMap(x -> x.getId(), x -> x));
        Map<Integer, List<CustomRetailer>> positionIdRetailerMap = csService.getPositionCustomRetailerMap(positions);
        for (Position position : positions) {
            List<CustomRetailer> crList = positionIdRetailerMap.get(position.getId());
            if (crList == null)
                continue;
            if (position.getCategoryId() == ProfitMandiConstants.TICKET_CATEGORY_SALES) {
                for (CustomRetailer cr : crList) {
                    if (!partnerEmailSalesMap.containsKey(cr.getEmail())) {
                        partnerEmailSalesMap.put(cr.getEmail(), new SaleRoles());
                    }
                    SaleRoles saleRoles = partnerEmailSalesMap.get(cr.getEmail());
                    AuthUser authUser = authUsersMap.get(position.getAuthUserId());
                    if (authUser == null) {
                        continue;
                    }
                    String name = authUser.getFirstName() + " " + authUser.getLastName();
                    if (position.getEscalationType().equals(EscalationType.L1)) {
                        saleRoles.getL1().add(name);
                    } else if (position.getEscalationType().equals(EscalationType.L2)) {
                        saleRoles.getL2().add(name);
                    }
                }
            }
            if (position.getCategoryId() == ProfitMandiConstants.TICKET_CATEGORY_RBM) {
                for (CustomRetailer cr : crList) {
                    if (!partnerEmailRBMMap.containsKey(cr.getEmail())) {
                        partnerEmailRBMMap.put(cr.getEmail(), new SaleRoles());
                    }
                    SaleRoles saleRoles = partnerEmailRBMMap.get(cr.getEmail());
                    AuthUser authUser = authUsersMap.get(position.getAuthUserId());
                    if (authUser == null) {
                        continue;
                    }
                    String name = authUser.getFirstName() + " " + authUser.getLastName();
                    if (position.getEscalationType().equals(EscalationType.L1) || position.getEscalationType().equals(EscalationType.L1)) {
                        saleRoles.getL1().add(name);
                    }
                    /*else if (position.getEscalationType().equals(EscalationType.L2)) {
                        saleRoles.getL2().add(name);
                    }*/
                }
            }

            if (position.getCategoryId() == ProfitMandiConstants.TICKET_CATEGORY_ABM) {
                for (CustomRetailer cr : crList) {
                    if (!partnerEmailABMMap.containsKey(cr.getEmail())) {
                        partnerEmailABMMap.put(cr.getEmail(), new SaleRoles());
                    }
                    SaleRoles saleRoles = partnerEmailABMMap.get(cr.getEmail());
                    AuthUser authUser = authUsersMap.get(position.getAuthUserId());
                    if (authUser == null) {
                        continue;
                    }
                    String name = authUser.getFirstName() + " " + authUser.getLastName();
                    if (position.getEscalationType().equals(EscalationType.L1) || position.getEscalationType().equals(EscalationType.L1)) {
                        saleRoles.getL1().add(name);
                    } else if (position.getEscalationType().equals(EscalationType.L2)) {
                        saleRoles.getL2().add(name);
                    }
                }
            }

        }

        Set<CustomRetailer> allCrList = new HashSet<>();
        for (List<CustomRetailer> cr : positionIdRetailerMap.values()) {
            allCrList.addAll(cr);
        }

        Map<Integer, FofoStore> fofoStoresMap = fofoStoreRepository.selectActiveStores().stream().collect(Collectors.toMap(x -> x.getId(), x -> x));

        Map<Integer, FofoReportingModel> partnerIdSalesHeadersMap = new HashMap<>();

        for (CustomRetailer cr : allCrList) {
            FofoStore fofoStore = fofoStoresMap.get(cr.getPartnerId());
            if (fofoStore == null) {
                LOGGER.info("Could not find Store {} in active Store", cr.getBusinessName());
                continue;
            }
            String code = fofoStore.getCode();
            // String storeName = "SmartDukaan-" +
            // fofoStore.getCode().replaceAll("[a-zA-Z]", "");
            String businessName = cr.getBusinessName();
            try {
                String stateManager = StringUtils.join(partnerEmailSalesMap.get(cr.getEmail()).getL2(), ", ");
                String territoryManager = StringUtils.join(partnerEmailSalesMap.get(cr.getEmail()).getL1(), ", ");
                String bdm = StringUtils.join(partnerEmailRBMMap.get(cr.getEmail()).getL1(), ", ");
                FofoReportingModel reportingModel = new FofoReportingModel();
                reportingModel.setBusinessName(businessName);
                reportingModel.setCode(code);
                reportingModel.setFofoId(fofoStore.getId());
                reportingModel.setRegionalManager(stateManager);
                reportingModel.setTerritoryManager(territoryManager);
                reportingModel.setBusinessManager(bdm);
                partnerIdSalesHeadersMap.put(fofoStore.getId(), reportingModel);
            } catch (Exception e) {
                LOGGER.warn("Could not find partner with email - {}", cr.getEmail());
            }
        }
        return partnerIdSalesHeadersMap;

    }

    public void persistRbmTodayTargets() throws Exception {
        rbmTargetService.setMovementWiseRbmTargets();
    }

    public void persistRbmTodayAchievements() throws Exception {
        rbmTargetService.setMovementWiseRbmAchievement();
    }

    public void sendPartnerInvestmentDetails() throws Exception {
        this.sendPartnerInvestmentDetails(null);
    }

    public void sendAgeingReport(String... sendTo) throws Exception {

        InputStreamSource isr = reporticoService.getReportInputStreamSource(ReporticoProject.WAREHOUSENEW, "itemstockageing.xml");
        InputStreamSource isr1 = reporticoService.getReportInputStreamSource(ReporticoProject.FOCO, "ItemwiseOverallPendingIndent.xml");
        Attachment attachment = new Attachment(
                "ageing-report-" + FormattingUtils.formatDate(LocalDateTime.now().minusDays(1)) + ".csv", isr);
        Attachment attachment1 = new Attachment(
                "pending-indent-" + FormattingUtils.formatDate(LocalDateTime.now().minusDays(1)) + ".csv", isr1);

        Utils.sendMailWithAttachments(googleMailSender, STOCK_AGEING_MAIL_LIST, null, "Stock Ageing Report", "PFA", attachment);
        Utils.sendMailWithAttachments(googleMailSender, ITEMWISE_PENDING_INDENT_MAIL_LIST, null, "Itemwise Pending indent", "PFA", attachment1);

        // Reports to be sent to mapped partners
        Map<String, Set<String>> storeGuysMap = csService.getAuthUserPartnerEmailMapping();

        for (Entry<String, Set<String>> storeGuyEntry : storeGuysMap.entrySet()) {
            Map<String, String> params = new HashMap<>();
            if (storeGuyEntry.getValue().size() == 0)
                continue;
            params.put("MANUAL_email", String.join(",", storeGuyEntry.getValue()));
            InputStreamSource isr3 = reporticoService.getReportInputStreamSource(ReporticoProject.FOCO, "focostockreport.xml", params);
            Attachment attache = new Attachment(
                    "Franchise-stock-report" + FormattingUtils.formatDate(LocalDateTime.now()) + ".csv", isr3);
            System.out.println(storeGuyEntry.getValue());
            Utils.sendMailWithAttachments(googleMailSender, new String[]{storeGuyEntry.getKey()}, null, "Franchise Stock Report", "PFA", attache);
        }

    }

    public void sendIndentTertiary() throws Exception {

        InputStreamSource isr = reporticoService.getReportInputStreamSource(ReporticoProject.FOCO, "indentandtertiary.xml");
        Attachment attachment = new Attachment(
                "indentandtertiary-report-" + FormattingUtils.formatDate(LocalDateTime.now()) + ".csv", isr);
        Utils.sendMailWithAttachments(googleMailSender, INDENT_TERTIARY_MAIL_LIST, null, "Indent Tertiary Report", "PFA", attachment);

    }

    public void sendAttendanceMorningAlert() throws Exception {
        LocalDateTime moriningTime = LocalDate.now().atTime(10, 31);
        List<AuthUser> authUsers = authRepository.selectAllActiveUser();
        Map<String, AuthUser> authUserEmailMap = authUsers.stream().filter(x -> x.isActive()).collect(Collectors.toMap(x -> x.getEmailId(), x -> x));

        List<User> users = dtrUserRepository.selectAllByEmailIds(new ArrayList<>(authUserEmailMap.keySet()));
        Map<String, User> userMap = users.stream().collect(Collectors.toMap(x -> x.getEmailId(), x -> x));

        List<EmployeeAttendance> employeeAttendances = employeeAttendanceRepository.selectAllByDatesBetween(LocalDate.now().atStartOfDay(), LocalDateTime.now());

        Map<Integer, Optional<EmployeeAttendance>> employeeMorningAttendance = employeeAttendances.stream().collect(groupingBy(EmployeeAttendance::getUserId, Collectors.minBy(Comparator.comparing(EmployeeAttendance::getCreateTimestamp))));
        for (AuthUser authUser : authUsers) {
            User user = userMap.get(authUser.getEmailId());
            Optional<EmployeeAttendance> employeeAttendanceOptional = employeeMorningAttendance.get(user.getId());
            LOGGER.info("AuthUser - {}, employeeAttendanceOptional {}", authUser.getName(), employeeAttendanceOptional);
            if (employeeAttendanceOptional != null) {
                LOGGER.info("employeeAttendanceOptional.orElse {}", employeeAttendanceOptional.orElse(null));
                if (employeeAttendanceOptional.orElse(null) != null) {
                    LOGGER.info("employeeAttendanceOptional.get().getCreateTimestamp() {}", employeeAttendanceOptional.get().getCreateTimestamp());
                }
            }
            if (employeeAttendanceOptional == null || employeeAttendanceOptional.orElse(null) == null || employeeAttendanceOptional.get().getCreateTimestamp().isAfter(moriningTime)) {
                LOGGER.info("Will Send Email to {}", authUser.getFullName());
                String body = String.format(
                        "Dear %s,\n Pls note that you haven't punched your attendance by 10:30am%s. You have been marked absent for half the day.\n\nRegards\nHR Team", authUser.getFullName(), (employeeAttendanceOptional == null || employeeAttendanceOptional.orElse(null) == null) ? "" : "(Punched at " + FormattingUtils.format(employeeAttendanceOptional.get().getCreateTimestamp()) + ")");

                Utils.sendMailWithAttachments(googleMailSender, new String[]{authUser.getEmailId()}, new String[]{"kangan.monga@smartdukaan.com"}, "Attendance Alert", body);

            }
        }

    }

    public void sendAttendanceEveningAlert() throws Exception {
        List<AuthUser> authUsers = authRepository.selectAllActiveUser();
        Map<String, AuthUser> authUserEmailMap = authUsers.stream().filter(x -> x.isActive()).collect(Collectors.toMap(x -> x.getEmailId(), x -> x));

        List<User> users = dtrUserRepository.selectAllByEmailIds(new ArrayList<>(authUserEmailMap.keySet()));
        Map<String, User> userMap = users.stream().collect(Collectors.toMap(x -> x.getEmailId(), x -> x));

        Map<Integer, List<EmployeeAttendance>> employeeAttendancesMap = employeeAttendanceRepository.selectAllByDatesBetween(LocalDate.now().atStartOfDay(), LocalDateTime.now()).stream().collect(groupingBy(x -> x.getUserId()));

        for (AuthUser authUser : authUsers) {
            User user = userMap.get(authUser.getEmailId());
            String body = null;
            List<EmployeeAttendance> employeeAttendances = employeeAttendancesMap.get(user.getId());
            if (employeeAttendances == null) {
                body = String.format(
                        "Dear %s,\n No attendance has been registered by you today. You have been marked absent for the day.\n\nRegards\nHR Team", authUser.getFullName());
            } else {
                List<LocalDateTime> punchTimes = employeeAttendances.stream().sorted(Comparator.comparing(EmployeeAttendance::getCreateTimestamp)).map(x -> x.getCreateTimestamp()).collect(Collectors.toList());
                if (punchTimes.size() == 1) {
                    // body = String.format("Dear %s,\n Pls note that you haven't punched out yet.
                    // You have been marked absent for half the day. You may contact your manager
                    // and get it regularise.\n\nRegards\nHR Team", authUser.getFullName());
                } else {
                    LocalDateTime firstPunch = punchTimes.get(0);
                    LocalDateTime lastPunch = punchTimes.get(punchTimes.size() - 1);
                    Duration duration = Duration.between(firstPunch, lastPunch);
                    boolean hoursCompleted = lastPunch.isAfter(firstPunch.plusHours(8).plusMinutes(30));
                    if (!hoursCompleted) {
                        body = String.format(
                                "Dear %s,\n Pls note that you haven't completed 8.30 Hrs (%d.%d Hrs). You have been marked absent for half the day.\n\nRegards\nHR Team", authUser.getFullName(), duration.toHours(), duration.toMinutes() - duration.toHours() * 60);
                    }
                }

            }
            if (body != null) {
                Utils.sendMailWithAttachments(googleMailSender, new String[]{authUser.getEmailId()}, new String[]{"kangan.monga@smartdukaan.com"}, "Attendance Alert", body);
            }
        }

        this.sendMailToHR();

    }

    private void sendMailToHR() throws Exception {
        Map<String, String> map = new HashMap<>();
        String reporticoDate = FormattingUtils.formatReporitcoDate(LocalDateTime.now());
        map.put("MANUAL_datesBetween_FROMDATE", reporticoDate);
        map.put("MANUAL_datesBetween_FROMDATE", reporticoDate);
        InputStreamSource isr = reporticoService.getReportInputStreamSource(ReporticoProject.FOCO, "employeeattendance.xml");
        Attachment attachment = new Attachment("attendance-" + FormattingUtils.formatDate(LocalDateTime.now()) + ".csv", isr);
        Utils.sendMailWithAttachments(googleMailSender, EMPLOYEE_ATTENDANCE_MAIL_LIST, null, "Attendance - " + FormattingUtils.formatDate(LocalDateTime.now()), "PFA Attendance", attachment);
    }

    public void checkPartnerActiveStore() throws Exception {

        List<FofoStore> fofoStores = fofoStoreRepository.selectByStatus(true);

        LocalDateTime currentDate = LocalDate.now().atStartOfDay();
        if (!fofoStores.isEmpty()) {
            for (FofoStore fofoStore : fofoStores) {

                if (currentDate.isBefore(fofoStore.getActiveTimeStamp())) {

                    fofoStore.setActive(true);
                    fofoStoreRepository.persist(fofoStore);
                    LOGGER.info("inserted into InActiveFofoStore successfully");

                } else {
                    fofoStore.setActive(false);
                    fofoStore.setActiveTimeStamp(null);
                    fofoStoreRepository.persist(fofoStore);
                    LOGGER.info("inserted into InActiveFofoStore successfully");
                }

            }
        }

    }

    public void sendAgeingReport() throws Exception {
        sendAgeingReport("kamini.sharma@smartdukaan.com", "tarun.verma@smartdukaan.com", "niranjan.kala@smartdukaan.com", "kuldeep.kumar@smartdukaan.com");
    }

    public void moveImeisToPriceDropImeis() throws Exception {
        List<PriceDrop> priceDrops = priceDropRepository.selectAll();
        for (PriceDrop priceDrop : priceDrops) {
            priceDropService.priceDropStatus(priceDrop.getId());
        }
    }

    public void walletmismatch() throws Exception {
        LocalDate curDate = LocalDate.now();
        List<PartnerDailyInvestment> pdis = partnerDailyInvestmentRepository.selectAll(curDate.minusDays(2));
        System.out.println(pdis.size());
        for (PartnerDailyInvestment pdi : pdis) {
            int fofoId = pdi.getFofoId();
            for (PartnerDailyInvestment investment : Lists.reverse(partnerDailyInvestmentRepository.selectAll(fofoId, null, null))) {
                float statementAmount = walletService.getOpeningTill(fofoId, investment.getDate().plusDays(1).atTime(LocalTime.of(4, 0)));
                CustomRetailer retailer = retailerService.getFofoRetailer(fofoId);
                LOGGER.info("{}\t{}\t{}\t{}\t{}", fofoId, retailer.getBusinessName(), retailer.getMobileNumber(), investment.getDate().toString(), investment.getWalletAmount(), statementAmount);

            }
        }

    }

    @Autowired
    StateRepository stateRepository;

    public void gst() throws Exception {
        List<FofoOrder> fofoOrders = fofoOrderRepository.selectBetweenSaleDate(LocalDate.of(2021, 8, 16).atStartOfDay(), LocalDateTime.now());
        for (FofoOrder fofoOrder : fofoOrders) {
            int retailerAddressId = retailerRegisteredAddressRepository.selectAddressIdByRetailerId(fofoOrder.getFofoId());

            Address retailerAddress = addressRepository.selectById(retailerAddressId);
            CustomerAddress customerAddress = customerAddressRepository.selectById(fofoOrder.getCustomerAddressId());
            Integer stateId = null;
            if (customerAddress.getState().equals(retailerAddress.getState())) {
                try {
                    stateId = stateRepository.selectByName(customerAddress.getState()).getId();
                } catch (Exception e) {
                    LOGGER.error("Cannot found state named {}", customerAddress.getState());
                    continue;
                }
            }
            Map<Integer, GstRate> itemIdStateTaxRateMap = null;

            List<FofoOrderItem> fofoOrderItems = fofoOrderItemRepository.selectByOrderId(fofoOrder.getId());
            List<Integer> itemIds = fofoOrderItems.stream().map(x -> x.getItemId()).collect(Collectors.toList());
            if (stateId != null) {
                itemIdStateTaxRateMap = stateGstRateRepository.getStateTaxRate(itemIds, stateId);
            } else {
                itemIdStateTaxRateMap = stateGstRateRepository.getIgstTaxRate(itemIds);
            }

            for (FofoOrderItem foi : fofoOrderItems) {
                float sgstRate = foi.getSgstRate();
                float cgstRate = foi.getCgstRate();
                float igstRate = foi.getIgstRate();
                foi.setCgstRate(itemIdStateTaxRateMap.get(foi.getItemId()).getCgstRate());
                foi.setSgstRate(itemIdStateTaxRateMap.get(foi.getItemId()).getSgstRate());
                foi.setIgstRate(itemIdStateTaxRateMap.get(foi.getItemId()).getIgstRate());

                LOGGER.info("Invoice {}, Date {}", fofoOrder.getInvoiceNumber(), fofoOrder.getCreateTimestamp());
                LOGGER.info("customerAddress.getState() {}, retailerAddress.getState() {}", customerAddress.getState(), retailerAddress.getState());
                LOGGER.info("Rates getIgstRate() {}", itemIdStateTaxRateMap.get(foi.getItemId()).getIgstRate());
                LOGGER.info("Rates getCgstRate() {}, getSgstRate() {}", itemIdStateTaxRateMap.get(foi.getItemId()).getCgstRate(), itemIdStateTaxRateMap.get(foi.getItemId()).getSgstRate());


            }
        }

    }

    public void schemewalletmismatch() throws ProfitMandiBusinessException {
        LocalDate dateToReconcile = LocalDate.of(2018, 4, 1);
        while (dateToReconcile.isBefore(LocalDate.now())) {
            reconcileSchemes(dateToReconcile);
            // reconcileOrders(dateTime);
            // reconcileRecharges(dateTime);
            dateToReconcile = dateToReconcile.plusDays(1);
        }
    }

    private void reconcileSchemes(LocalDate date) throws ProfitMandiBusinessException {
        LocalDateTime startDate = date.atStartOfDay();
        LocalDateTime endDate = startDate.plusDays(1);
        List<SchemeInOut> siosCreated = schemeInOutRepository.selectAllByCreateDate(startDate, endDate);
        List<SchemeInOut> siosRefunded = schemeInOutRepository.selectAllByRefundDate(startDate, endDate);
        double totalSchemeDisbursed = siosCreated.stream().mapToDouble(x -> x.getAmount()).sum();
        double totalSchemeRolledback = siosRefunded.stream().mapToDouble(x -> x.getAmount()).sum();
        double netSchemeDisbursed = totalSchemeDisbursed - totalSchemeRolledback;
        List<WalletReferenceType> walletReferenceTypes = Arrays.asList(WalletReferenceType.SCHEME_IN, WalletReferenceType.SCHEME_OUT);
        List<UserWalletHistory> history = userWalletHistoryRepository.selectAllByDateType(startDate, endDate, walletReferenceTypes);
        double schemeAmountWalletTotal = history.stream().mapToDouble(x -> x.getAmount()).sum();
        if (Math.abs(netSchemeDisbursed - schemeAmountWalletTotal) > 10d) {
            LOGGER.info("Scheme Amount mismatched for Date {}", date);

            Map<Integer, Double> inventoryItemSchemeIO = siosCreated.stream().collect(groupingBy(x -> x.getInventoryItemId(), Collectors.summingDouble(SchemeInOut::getAmount)));

            Map<Integer, Double> userSchemeMap = inventoryItemRepository.selectByIds(inventoryItemSchemeIO.keySet()).stream().collect(groupingBy(x -> x.getFofoId(), Collectors.summingDouble(x -> inventoryItemSchemeIO.get(x.getId()))));

            Map<Integer, Double> inventoryItemSchemeIORefunded = siosRefunded.stream().collect(groupingBy(x -> x.getInventoryItemId(), Collectors.summingDouble(SchemeInOut::getAmount)));

            Map<Integer, Double> userSchemeRefundedMap = inventoryItemRepository.selectByIds(inventoryItemSchemeIORefunded.keySet()).stream().collect(groupingBy(x -> x.getFofoId(), Collectors.summingDouble(x -> inventoryItemSchemeIORefunded.get(x.getId()))));

            Map<Integer, Double> finalUserSchemeAmountMap = new HashMap<>();

            for (Entry<Integer, Double> schemeAmount : userSchemeRefundedMap.entrySet()) {
                if (!finalUserSchemeAmountMap.containsKey(schemeAmount.getKey())) {
                    finalUserSchemeAmountMap.put(schemeAmount.getKey(), schemeAmount.getValue());
                } else {
                    finalUserSchemeAmountMap.put(schemeAmount.getKey(), finalUserSchemeAmountMap.get(schemeAmount.getKey()) + schemeAmount.getValue());
                }
            }
            Map<Integer, Integer> userWalletMap = userWalletRepository.selectByRetailerIds(finalUserSchemeAmountMap.keySet()).stream().collect(Collectors.toMap(UserWallet::getUserId, UserWallet::getId));

            Map<Integer, Double> walletAmountMap = history.stream().collect(groupingBy(
                    UserWalletHistory::getWalletId, Collectors.summingDouble((UserWalletHistory::getAmount))));
            for (Entry<Integer, Double> userAmount : walletAmountMap.entrySet()) {
                double diff = Math.abs(finalUserSchemeAmountMap.get(userAmount.getKey()) - userAmount.getValue());
                if (diff > 5) {
                    LOGGER.info("Partner scheme mismatched for Userid {}", userWalletMap.get(userAmount.getKey()));
                }
            }
        }

    }

    public void sendDailySalesNotificationToPartner(Integer fofoIdInt) throws Exception {

        LocalDateTime now = LocalDateTime.now();
        LocalDateTime from = now.with(LocalTime.MIN);
        String timeString = "Today %s";
        // Send yesterday's report
        /*
         * if (now.getHour() < 13) { timeString = "Yesterday %s"; from =
         * now.minusDays(1).; now = from.with(LocalTime.MAX);
         *
         * }
         */

        List<Integer> fofoIds = null;
        Map<Integer, FofoStore> fofoStoreMap = fofoStoreRepository.selectAll().stream().filter(x -> x.isActive()).collect(Collectors.toMap(x -> x.getId(), x -> x));


        if (fofoIdInt == null) {
            fofoIds = new ArrayList<>(fofoStoreMap.keySet());
        } else {
            fofoIds = Arrays.asList(fofoIdInt);
        }
        DateTimeFormatter timeFormatter = DateTimeFormatter.ofPattern("h:m a");

        Map<Integer, Float> partnerPolicyAmountMap = insurancePolicyRepository.selectAmountSumGroupByRetailerId(now, null);
        Map<Integer, Long> partnerPolicyQtyMap = insurancePolicyRepository.selectQtyGroupByRetailerId(now, null);

        Map<Integer, Double> spPartnerOrderValMap = fofoOrderItemRepository.selectSumAmountGroupByRetailer(from, now, 0, true);

        Map<Integer, Double> spPartner3DaysOrderValMap = fofoOrderItemRepository.selectSumAmountGroupByRetailer(from.minusDays(3), now, 0, true);
        Map<Integer, Long> spPartnerOrderQtyMap = fofoOrderItemRepository.selectQtyGroupByRetailer(from, now, 0, true);

        Map<Integer, Double> partnerOrderValMap = fofoOrderItemRepository.selectSumAmountGroupByRetailer(from, now, 0, false);
        Map<Integer, Long> partnerOrderQtyMap = fofoOrderItemRepository.selectQtyGroupByRetailer(from, now, 0, false);

        //4 days + current day running
        Map<Integer, Double> partnerBilledValueMap = orderRepository.selectBillingDatesBetweenSumGroupByRetailerId(from.minusDays(4), now);

        Map<Integer, SaleTargetReportModel> saleTargetReportModelMap = new HashMap<>();
        for (int fofoId : fofoIds) {
            SaleTargetReportModel model = new SaleTargetReportModel();
            model.setInsuranceSale(
                    partnerPolicyAmountMap.containsKey(fofoId) ? partnerPolicyAmountMap.get(fofoId).doubleValue() : 0);
            model.setInsruanceQty(partnerPolicyQtyMap.containsKey(fofoId) ? partnerPolicyQtyMap.get(fofoId) : 0);
            model.setSecondary(partnerBilledValueMap.containsKey(fofoId) ? partnerBilledValueMap.get(fofoId) : 0);
            model.setSmartphoneSale(spPartnerOrderValMap.containsKey(fofoId) ? spPartnerOrderValMap.get(fofoId) : 0);
            model.setSmartphoneQty(spPartnerOrderQtyMap.containsKey(fofoId) ? spPartnerOrderQtyMap.get(fofoId) : 0);
            model.setTotalSale(partnerOrderValMap.containsKey(fofoId) ? partnerOrderValMap.get(fofoId) : 0);
            model.setTotalQty(partnerOrderQtyMap.containsKey(fofoId) ? partnerOrderQtyMap.get(fofoId) : 0);
            model.setPast3daysSale(
                    spPartner3DaysOrderValMap.containsKey(fofoId) ? spPartner3DaysOrderValMap.get(fofoId) : 0);
            model.setFofoId(fofoId);
            model.setCode(fofoStoreMap.get(fofoId).getCode());
            model.setActivationType(fofoStoreMap.get(fofoId).getActivationType());
            saleTargetReportModelMap.put(fofoId, model);
        }

        Map<Integer, FofoReportingModel> partnerSalesHeadersMap = this.getPartnerIdSalesHeaders();
        for (Integer fofoId : fofoIds) {
            SaleTargetReportModel model = saleTargetReportModelMap.get(fofoId);

            // com.spice.profitmandi.dao.entity.user.User user =
            // userUserRepository.selectById(fofoId);

            // Address address = addressRepository.selectById(user.getAddressId());

            String title = "Sale Update";
            String messageTemplate = String.format("Smartphones Rs.%.0f, Insurance Rs.%.0f, Total Rs.%.0f till %s.", model.getSmartphoneSale(), model.getInsuranceSale(), model.getTotalSale(), String.format(timeString, now.format(timeFormatter)));
            SendNotificationModel sendNotificationModel = new SendNotificationModel();
            sendNotificationModel.setCampaignName("Sales update alert");
            sendNotificationModel.setTitle(title);
            sendNotificationModel.setMessage(messageTemplate);
            sendNotificationModel.setType("url");
            sendNotificationModel.setUrl("https://app.smartdukaan.com/pages/home/notifications");
            sendNotificationModel.setExpiresat(LocalDateTime.now().plusDays(1));
            sendNotificationModel.setMessageType(MessageType.notification);
            int userId = userAccountRepository.selectUserIdByRetailerId(fofoId);
            sendNotificationModel.setUserIds(Arrays.asList(userId));
            notificationService.sendNotification(sendNotificationModel);
            String whatsappMessageTemplate = String.format(
                    "Dear Partner, Your sale update is Smartphones Rs.%.0f, Insurance Rs.%.0f, Total Rs.%.0f till %s.", model.getSmartphoneSale(), model.getInsuranceSale(), model.getTotalSale(), String.format(timeString, now.format(timeFormatter)));
            // notificationService.sendWhatsappMessage(whatsappMessageTemplate, title,
            // address.getPhoneNumber());

        }
        // String saleReport = this.getDailySalesReportHtml(partnerSalesHeadersMap,
        // saleTargetReportModelMap);
        this.getStateWiseSales(saleTargetReportModelMap, partnerSalesHeadersMap);
    }

    public void checkRazorPayPaymentStatus() throws Exception {
        List<PendingOrder> pendingOrder = pendingOrderRepository.selectAllByStatus(com.spice.profitmandi.dao.enumuration.transaction.OrderStatus.PENDING);

        for (PendingOrder po : pendingOrder) {
            RazorPay razorPay = razorPayRepository.selectByOrdeId(po.getId());
            List<PendingOrderItem> poItems = pendingOrderItemRepository.selectByOrderId(po.getId());

            LOGGER.info("razorPay" + razorPay);
            if (razorPay != null) {
                List<Payment> payments = razorPaymentService.fetchOrderForPayment(razorPay.getRazorOrderId());

                if (!payments.isEmpty()) {
                    List<String> statusList = new ArrayList<>();
                    for (Payment payment : payments) {

                        JSONObject jsonObj = new JSONObject(payment.toString());

                        String status = jsonObj.getString("status");

                        statusList.add(status);

                    }
                    LOGGER.info("statusList" + statusList);

                    if (statusList.contains("authorized") || statusList.contains("captured")) {
                        po.setStatus(com.spice.profitmandi.dao.enumuration.transaction.OrderStatus.PROCESSING);
                        po.setPaidAmount(po.getTotalAmount());
                        for (PendingOrderItem poi : poItems) {
                            poi.setStatus(com.spice.profitmandi.dao.enumuration.transaction.OrderStatus.PROCESSING);
                        }
                        Map<String, Object> emailModel = pendingOrderService.sendCreateOrderMail(po);

                        CustomRetailer customRetailer = retailerService.getFofoRetailer(po.getFofoId());
                        Customer customer = customerRepository.selectById(po.getCustomerId());
                        String[] customerEmail = null;
                        if (customer.getEmailId() != null) {
                            customerEmail = new String[]{customer.getEmailId()};
                        }
                        List<String> bccTo = Arrays.asList("kamini.sharma@smartdukaan.com", "tarun.verma@smartdukaan.com", "niranjan.kala@smartdukaan.com", "sm@smartdukaan.com", "tejbeer.kaur@shop2020.in", customRetailer.getEmail());
                        List<String> authUserEmails = csService.getAuthUserByPartnerId(customRetailer.getPartnerId());
                        if (authUserEmails != null) {
                            authUserEmails = new ArrayList<>();
                        }
                        authUserEmails.addAll(bccTo);

                        // emailService.sendMailWithAttachments("Order Created with SmartDukaan",
                        // "order-confirm.vm",
                        // emailModel, customerEmail, null, authUserEmails.toArray(new String[0]));

                    } else if (statusList.contains("refunded") || statusList.contains("failed")) {
                        for (PendingOrderItem poi : poItems) {
                            poi.setStatus(com.spice.profitmandi.dao.enumuration.transaction.OrderStatus.FAILED);
                        }

                        po.setStatus(com.spice.profitmandi.dao.enumuration.transaction.OrderStatus.FAILED);
                    }

                }
                // LOGGER.info("payment" + payments);

            }
        }
    }

    public static class SaleTargetReportModel {
        private double totalSale;
        private long totalQty;
        private double past3daysSale;
        private int fofoId;
        private String code;
        private ActivationType activationType;


        @Override
        public boolean equals(Object o) {
            if (this == o) return true;
            if (o == null || getClass() != o.getClass()) return false;
            SaleTargetReportModel that = (SaleTargetReportModel) o;
            return Double.compare(that.totalSale, totalSale) == 0 && totalQty == that.totalQty && Double.compare(that.past3daysSale, past3daysSale) == 0 && fofoId == that.fofoId && Double.compare(that.secondary, secondary) == 0 && Double.compare(that.smartphoneSale, smartphoneSale) == 0 && smartphoneQty == that.smartphoneQty && Double.compare(that.insuranceSale, insuranceSale) == 0 && insruanceQty == that.insruanceQty && Objects.equals(code, that.code) && activationType == that.activationType;
        }

        @Override
        public int hashCode() {
            return Objects.hash(totalSale, totalQty, past3daysSale, fofoId, code, activationType, secondary, smartphoneSale, smartphoneQty, insuranceSale, insruanceQty);
        }

        public ActivationType getActivationType() {
            return activationType;
        }

        public void setActivationType(ActivationType activationType) {
            this.activationType = activationType;
        }

        public double getSecondary() {
            return secondary;
        }

        public void setSecondary(double secondary) {
            this.secondary = secondary;
        }

        private double secondary;

        public int getFofoId() {
            return fofoId;
        }

        @Override
        public String toString() {
            return "SaleTargetReportModel{" + "totalSale=" + totalSale + ", totalQty=" + totalQty + ", past3daysSale=" + past3daysSale + ", fofoId=" + fofoId + ", code='" + code + '\'' + ", secondary=" + secondary + ", smartphoneSale=" + smartphoneSale + ", smartphoneQty=" + smartphoneQty + ", insuranceSale=" + insuranceSale + ", insruanceQty=" + insruanceQty + '}';
        }

        public String getCode() {
            return code;
        }

        public void setCode(String code) {
            this.code = code;
        }

        public void setFofoId(int fofoId) {
            this.fofoId = fofoId;
        }

        private double smartphoneSale;
        private long smartphoneQty;
        private double insuranceSale;
        private long insruanceQty;

        public long getTotalQty() {
            return totalQty;
        }

        public void setTotalQty(long totalQty) {
            this.totalQty = totalQty;
        }

        public double getPast3daysSale() {
            return past3daysSale;
        }

        public void setPast3daysSale(double past3daysSale) {
            this.past3daysSale = past3daysSale;
        }

        public double getTotalSale() {
            return totalSale;
        }

        public void setTotalSale(double totalSale) {
            this.totalSale = totalSale;
        }

        public double getSmartphoneSale() {
            return smartphoneSale;
        }

        public void setSmartphoneSale(double smartphoneSale) {
            this.smartphoneSale = smartphoneSale;
        }

        public long getSmartphoneQty() {
            return smartphoneQty;
        }

        public void setSmartphoneQty(long smartphoneQty) {
            this.smartphoneQty = smartphoneQty;
        }

        public double getInsuranceSale() {
            return insuranceSale;
        }

        public void setInsuranceSale(double insuranceSale) {
            this.insuranceSale = insuranceSale;
        }

        public long getInsruanceQty() {
            return insruanceQty;
        }

        public void setInsruanceQty(long insruanceQty) {
            this.insruanceQty = insruanceQty;
        }

    }

    private void getStateWiseSales
            (Map<Integer, SaleTargetReportModel> saleTargetReportModelMap, Map<Integer, FofoReportingModel> partnerSalesHeadersMap) throws
            Exception {
        String timeString = "Today %s";
        LocalDateTime now = LocalDateTime.now();

        DateTimeFormatter timeFormatter = DateTimeFormatter.ofPattern("h:m a");
        List<Integer> categoryIds = Arrays.asList(ProfitMandiConstants.TICKET_CATEGORY_LOGISTICS, ProfitMandiConstants.TICKET_CATEGORY_FINANCIAL_SERVICES, ProfitMandiConstants.TICKET_CATEGORY_CATEGORY, ProfitMandiConstants.TICKET_CATEGORY_RBM, ProfitMandiConstants.TICKET_CATEGORY_SALES, ProfitMandiConstants.TICKET_CATEGORY_ABM, ProfitMandiConstants.TICKET_CATEGORY_MARKETING, ProfitMandiConstants.TICKET_CATEGORY_ACCOUNTS, ProfitMandiConstants.TICKET_CATEGORY_BUSINESSINTELLIGENT, ProfitMandiConstants.TICKET_CATEGORY_TECHNOLOGY);

        Map<String, Set<Integer>> storeGuyMap = csService.getAuthUserPartnerIdMappingByCategoryIds(categoryIds, false);

        for (Entry<String, Set<Integer>> storeGuyEntry : storeGuyMap.entrySet()) {
            String email = storeGuyEntry.getKey();
            Set<Integer> fofoIds = storeGuyEntry.getValue();
            LOGGER.info("fofoIds {}", fofoIds);

            if (!fofoIds.isEmpty()) {
                List<FofoStore> stores = fofoStoreRepository.selectActivePartnersByRetailerIds(new ArrayList<>(fofoIds));

                Map<String, List<Integer>> stateMap = stores.stream().collect(groupingBy(
                        x -> x.getCode().substring(0, 2), mapping(x -> x.getId(), Collectors.toList())));
                List<List<Serializable>> stateWiseSales = new ArrayList<>();
                for (Entry<String, List<Integer>> stateMapEntry : stateMap.entrySet()) {
                    long totalQty = stateMapEntry.getValue().stream().collect(Collectors.summingLong(x -> saleTargetReportModelMap.get(x).getTotalQty()));
                    double totalSale = stateMapEntry.getValue().stream().collect(Collectors.summingDouble(x -> saleTargetReportModelMap.get(x).getTotalSale()));
                    long smartPhoneQty = stateMapEntry.getValue().stream().collect(Collectors.summingLong(x -> saleTargetReportModelMap.get(x).getSmartphoneQty()));
                    double smartPhoneSale = stateMapEntry.getValue().stream().collect(
                            Collectors.summingDouble(x -> saleTargetReportModelMap.get(x).getSmartphoneSale()));
                    stateWiseSales.add(
                            Arrays.asList(stateMapEntry.getKey(), smartPhoneQty, smartPhoneSale, totalQty, totalSale));
                }
                StringBuilder sb = new StringBuilder();
                sb.append("<html><body>");
                sb.append("<p>Statewise Sale Report:</p><br/><table style=\"border-collapse: collapse;\">");
                sb.append("<tbody>\n" + "           <tr>" + "                                           <th style='border:1px solid black;padding: 5px'>State</th>" + "                                         <th style='border:1px solid black;padding: 5px'>SmartPhone Qty</th>" + "                                                <th style='border:1px solid black;padding: 5px'>SmartPhone Value</th>" + "                                              <th style='border:1px solid black;padding: 5px'>Total Qty</th>" + "                                             <th style='border:1px solid black;padding: 5px'>Total Value</th>" + "                                   </tr>");
                for (List<Serializable> stateSale : stateWiseSales) {
                    sb.append("<tr>");
                    sb.append("<td style='border:1px solid black;padding: 5px'>" + stateSale.get(0) + "</td>");
                    sb.append("<td style='border:1px solid black;padding: 5px'>" + stateSale.get(1) + "</td>");
                    sb.append("<td style='border:1px solid black;padding: 5px'>" + stateSale.get(2) + "</td>");
                    sb.append("<td style='border:1px solid black;padding: 5px'>" + stateSale.get(3) + "</td>");
                    sb.append("<td style='border:1px solid black;padding: 5px'>" + stateSale.get(4) + "</td>");
                    sb.append("</tr>");
                }
                sb.append("</tbody></table><br><br>");

                sb.append("<p>Partnerwise Report:</p><br/><table style=\"border-collapse: collapse;\">");
                sb.append("<tbody>\n" + "                                       <tr>\n" + "                                             <th style='border:1px solid black;padding: 5px'>Code</th>" + "                                          <th style='border:1px solid black;padding: 5px'>Business Name</th>" + "                                         <th style='border:1px solid black;padding: 5px'>BDM Name</th>" + "                                              <th style='border:1px solid black;padding: 5px'>Regional Manager</th>" + "                                              <th style='border:1px solid black;padding: 5px'>Territory Manager</th>" + "                                             <th style='border:1px solid black;padding: 5px'>Billed(4 days)</th>" + "                                                <th style='border:1px solid black;padding: 5px'>Sale</th>" + "                                          <th style='border:1px solid black;padding: 5px'>Smartphone Sale</th>" + "                                               <th style='border:1px solid black;padding: 5px'>SmartPhone Qty</th>" + "                                        </tr>");


                List<Integer> sortedSaleTargetReport = saleTargetReportModelMap.values().stream().filter(x -> fofoIds.contains(x.getFofoId())).sorted(Comparator.comparing(SaleTargetReportModel::getCode).thenComparing(SaleTargetReportModel::getSecondary)).map(SaleTargetReportModel::getFofoId).collect(Collectors.toList());

                String subject = String.format("Sale till %s", String.format(timeString, now.format(timeFormatter)));

                List<String> headers = Arrays.asList("Store Id", "Store Code", "Store Name", "Activation Type", "BDM Name", "Regional Manager", "Territory Manager", "Secondary(4 days)", "Sale", "Smartphone Value", "Smartphone Qty");
                List<List<?>> rows = new ArrayList<>();
                for (Integer fofoId : sortedSaleTargetReport) {
                    FofoReportingModel fofoReportingModel = partnerSalesHeadersMap.get(fofoId);
                    rows.add(Arrays.asList(fofoId, fofoReportingModel.getCode(), fofoReportingModel.getBusinessName(), saleTargetReportModelMap.get(fofoId).getActivationType(), fofoReportingModel.getBusinessManager(), fofoReportingModel.getRegionalManager(), fofoReportingModel.getTerritoryManager(), saleTargetReportModelMap.get(fofoId).getSecondary(), saleTargetReportModelMap.get(fofoId).getTotalSale(), saleTargetReportModelMap.get(fofoId).getSmartphoneSale(), saleTargetReportModelMap.get(fofoId).getSmartphoneQty()
                    ));

                    //Now try to populate html rows
                    if (!saleTargetReportModelMap.get(fofoId).getActivationType().equals(ActivationType.ACTIVE)) {
                        continue;
                    }
                    if (saleTargetReportModelMap.get(fofoId).getSecondary() == 0) {
                        sb.append("<tr style='background-color:#D21F3C;color:white'>");
                    } else {
                        sb.append("<tr>");
                    }
                    sb.append("<td style='border:1px solid black;padding: 5px'>" + partnerSalesHeadersMap.get(fofoId).getCode() + "</td>");
                    sb.append("<td style='border:1px solid black;padding: 5px'>" + partnerSalesHeadersMap.get(fofoId).getBusinessName() + "</td>");
                    sb.append("<td style='border:1px solid black;padding: 5px'>" + partnerSalesHeadersMap.get(fofoId).getBusinessManager() + "</td>");
                    sb.append("<td style='border:1px solid black;padding: 5px'>" + partnerSalesHeadersMap.get(fofoId).getRegionalManager() + "</td>");
                    sb.append("<td style='border:1px solid black;padding: 5px'>" + partnerSalesHeadersMap.get(fofoId).getTerritoryManager() + "</td>");
                    sb.append("<td style='border:1px solid black;padding: 5px'>" + saleTargetReportModelMap.get(fofoId).getSecondary() + "</td>");
                    sb.append("<td style='border:1px solid black;padding: 5px'>" + saleTargetReportModelMap.get(fofoId).getTotalSale() + "</td>");
                    sb.append("<td style='border:1px solid black;padding: 5px'>" + saleTargetReportModelMap.get(fofoId).getSmartphoneSale() + "</td>");
                    sb.append("<td style='border:1px solid black;padding: 5px'>" + saleTargetReportModelMap.get(fofoId).getSmartphoneQty() + "</td>");
                    sb.append("</tr>");


                }
                sb.append("</tr>");
                sb.append("</body></html>");
                String statewiseSaleReport = sb.toString();


                ByteArrayOutputStream baos = null;
                try {
                    baos = FileUtil.getCSVByteStream(headers, rows);
                } catch (Exception e2) {
                    e2.printStackTrace();
                }
                Attachment attachment = new Attachment("PartnerSalePerformance.csv", new ByteArrayResource(baos.toByteArray()));
                Utils.sendHtmlMailWithAttachments(googleMailSender, new String[]{email}, null, "Statewise " + subject, statewiseSaleReport, attachment);
            }
        }
    }


    private void sendMailOfHtmlFormat(JavaMailSender sender, String[] email, String body, String cc[], String subject) throws
            MessagingException, ProfitMandiBusinessException, IOException {
        MimeMessage message = sender.createMimeMessage();
        MimeMessageHelper helper = new MimeMessageHelper(message);
        helper.setSubject(subject);
        helper.setText(body, true);
        helper.setTo(email);
        if (cc != null) {
            helper.setCc(cc);
        }
        InternetAddress senderAddress = new InternetAddress("noreply@smartdukaan.com", "Smart Dukaan");
        helper.setFrom(senderAddress);
        mailSender.send(message);
    }

    public void sendNotification() throws Exception {
        LOGGER.info("FCM sendNotification");
        List<PushNotifications> pushNotifications = pushNotificationRepository.selectAllPendingNotifications();
        if (!pushNotifications.isEmpty()) {
            InputStream serviceAccount = getClass().getClassLoader().getResourceAsStream("service-account.json");
            GoogleCredentials googleCredentials = GoogleCredentials.fromStream(serviceAccount).createScoped(FCM_SCOPED);
            googleCredentials.refreshIfExpired();
            String accessToken = googleCredentials.getAccessToken().getTokenValue();

            for (PushNotifications pushNotification : pushNotifications) {
                Device device = deviceRepository.selectById(pushNotification.getDeviceId());
                NotificationCampaign notificationCampaign = notificationCampaignRepository.selectById(pushNotification.getNotificationCampaignid());
                SimpleCampaignParams scp = gson.fromJson(notificationCampaign.getImplementationParams(), SimpleCampaignParams.class);
                Campaign campaign = new SimpleCampaign(scp);
                String result_url = campaign.getUrl() + "&user_id=" + device.getUser_id();

                JSONObject message = new JSONObject();
                message.put("token", device.getFcmId());

                JSONObject notification = new JSONObject();
                notification.put("title", campaign.getTitle());
                notification.put("body", campaign.getMessage());
                message.put("notification", notification);

                JSONObject data = new JSONObject();
                data.put("type", campaign.getType());
                data.put("url", result_url);
                data.put("time_to_live", campaign.getExpireTimestamp());
                data.put("image", campaign.getImageUrl());
                data.put("largeIcon", "large_icon");
                data.put("smallIcon", "small_icon");
                data.put("vibrate", "1");
                data.put("pid", String.valueOf(pushNotification.getId()));
                data.put("sound", "1");
                data.put("priority", "high");
                message.put("data", data);

                JSONObject payload = new JSONObject();
                payload.put("message", message);

                try {
                    CloseableHttpClient client = HttpClients.createDefault();
                    HttpPost httpPost = new HttpPost(FCM_URL);

                    httpPost.setHeader("Authorization", "Bearer " + accessToken);
                    httpPost.setHeader("Content-Type", "application/json");

                    StringEntity entity = new StringEntity(payload.toString());
                    httpPost.setEntity(entity);
                    CloseableHttpResponse response = client.execute(httpPost);
                    LOGGER.info("google FCM status code: {}", response.getStatusLine().getStatusCode());
                    String responseBody = new BufferedReader(new InputStreamReader(response.getEntity().getContent()))
                            .lines().collect(Collectors.joining("\n"));
                    if (response.getStatusLine().getStatusCode() == 200) {
                        pushNotification.setSentTimestamp(LocalDateTime.now());
                    } else {
                        pushNotification.setSentTimestamp(LocalDateTime.of(1970, 1, 1, 00, 00));
                        LOGGER.info("FCM Response message" + responseBody);
                        response.toString();
                    }

                } catch (Exception e) {
                    e.printStackTrace();
                    pushNotification.setSentTimestamp(LocalDateTime.of(1970, 1, 1, 00, 00));
                    LOGGER.info("message " + "not sent " + e.getMessage());
                }
            }
        }
    }

    public void grouping() throws Exception {
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("MM-dd-yyyy hh:mm");
        List<PriceDropIMEI> priceDropImeis = priceDropIMEIRepository.selectByStatus(PriceDropImeiStatus.APPROVED);
        System.out.println(String.join("\t", Arrays.asList("IMEI", "ItemId", "Brand", "Model Name", "Model Number", "Franchise Id", "Franchise Name", "Grn On", "Price Dropped On", "Approved On", "Returned On", "Price Drop Paid", "Is Doa")));
        Map<Integer, CustomRetailer> retailersMap = retailerService.getFofoRetailers(false);
        for (PriceDropIMEI priceDropIMEI : priceDropImeis) {
            if (priceDropIMEI.getPartnerId() == 0)
                continue;
            HashSet<String> imeis = new HashSet<>();
            PriceDrop priceDrop = priceDropRepository.selectById(priceDropIMEI.getPriceDropId());
            imeis.add(priceDropIMEI.getImei());
            List<InventoryItem> inventoryItems = inventoryItemRepository.selectByFofoIdSerialNumbers(priceDropIMEI.getPartnerId(), imeis, false);
            if (inventoryItems.size() == 0) {
                LOGGER.info("Need to investigate partnerId - {} imeis - {}", priceDropIMEI.getPartnerId(), imeis);
                continue;
            }
            InventoryItem inventoryItem = inventoryItems.get(0);
            CustomRetailer customRetailer = retailersMap.get(inventoryItem.getFofoId());
            if (inventoryItem.getLastScanType().equals(ScanType.DOA_OUT) || inventoryItem.getLastScanType().equals(ScanType.PURCHASE_RET)) {
                // check if pricedrop has been rolled out
                List<UserWalletHistory> uwh = walletService.getAllByReference(inventoryItem.getFofoId(), priceDropIMEI.getPriceDropId(), WalletReferenceType.PRICE_DROP);
                if (uwh.size() > 0) {
                    Item item = itemRepository.selectById(inventoryItem.getItemId());
                    System.out.println(String.join("\t", Arrays.asList(priceDropIMEI.getImei(), inventoryItem.getItemId() + "", item.getBrand(), item.getModelName(), item.getModelNumber(), inventoryItem.getFofoId() + "", customRetailer.getBusinessName(), inventoryItem.getCreateTimestamp().format(dtf), priceDrop.getAffectedOn().format(dtf), priceDropIMEI.getUpdateTimestamp().format(dtf), inventoryItem.getUpdateTimestamp().format(dtf), priceDrop.getAutoPartnerPayout(inventoryItem.getUpdateTimestamp()) + "", inventoryItem.getLastScanType().equals(ScanType.DOA_OUT) + "")));
                }
            }
        }
    }

    public void toffeeRollback() throws Exception {
        toffeeService.cancelPolicyCopy("110143521986");
        toffeeService.getOrderId("110143521986");
    }

    public void attachToffeeInvoices() throws Exception {
        List<InsurancePolicy> insurancePolicies = insurancePolicyRepository.selectAllByProviderId(3, Optional.of(false));
        for (InsurancePolicy insurancePolicy : insurancePolicies) {
            String invoiceNumber = insurancePolicy.getInvoiceNumber();
            FofoOrder fofoOrder = fofoOrderRepository.selectByInvoiceNumber(invoiceNumber);
            InvoicePdfModel pdfModel = orderService.getInvoicePdfModel(fofoOrder.getId());
            java.io.ByteArrayOutputStream byteArrayOutputStream = new java.io.ByteArrayOutputStream();
            PdfUtils.generateAndWrite(Arrays.asList(pdfModel), byteArrayOutputStream);
            String pdfInvoiceString = "data:application/pdf;base64," + Base64.getEncoder().encodeToString(byteArrayOutputStream.toByteArray());
            boolean attached = toffeeService.attachInvoice(insurancePolicy.getPolicyNumber().split("#")[1], pdfInvoiceString);
            if (attached) {
                insurancePolicy.setPosted(true);
            }
        }
    }

    public void sendBAGPendingPolicies() throws Exception {
        List<InsurancePolicy> insurancePolicies = insurancePolicyRepository.selectAllByProviderId(4, Optional.of(false));
        for (InsurancePolicy insurancePolicy : insurancePolicies) {
            String invoiceNumber = insurancePolicy.getInvoiceNumber();
            FofoOrder fofoOrder = fofoOrderRepository.selectByInvoiceNumber(invoiceNumber);
            FofoOrderItem fofoOrderItem = fofoOrderItemRepository.selectByOrderId(fofoOrder.getId()).get(0);
            CustomerAddress customerAddress = customerAddressRepository.selectById(fofoOrder.getCustomerAddressId());
            Customer customer = customerRepository.selectById(fofoOrder.getCustomerId());
            BAGInsuranceModel bagInsuranceModel = new BAGInsuranceModel();
            // bagInsuranceModel.setModelId();
            bagInsuranceModel.setAddressLine1(customerAddress.getLine1());
            bagInsuranceModel.setAddressLine2(customerAddress.getLine2());
            bagInsuranceModel.setCity(customerAddress.getCity());
            bagInsuranceModel.setBrandWarranty("1 Year");
            Item item = itemRepository.selectById(fofoOrderItem.getId());
            bagInsuranceModel.setModelName(item.getItemDescription());
            bagInsuranceModel.setDateOfPurchase(fofoOrder.getFormattedDate());
            bagInsuranceModel.setEmail(customer.getEmailId());
            bagInsuranceModel.setImei1(insurancePolicy.getSerialNumber());
            bagInsuranceModel.setFirstName(customer.getFirstName());
            bagInsuranceModel.setLastName(customer.getLastName());
            bagInsuranceModel.setMobileNumber(customer.getMobileNumber());
            bagInsuranceModel.setPlanId("");
            insurancePolicy.setPosted(true);
        }
    }

    public void schemeRollback(List<String> schemeIds) throws Exception {
        List<Integer> schemeIdsInt = schemeIds.stream().map(x -> Integer.parseInt(x)).collect(Collectors.toList());
        Map<Integer, Scheme> schemesMap = schemeRepository.selectBySchemeIds(schemeIdsInt, 0, schemeIds.size()).stream().collect(Collectors.toMap(x -> x.getId(), x -> x));
        List<SchemeInOut> schemeInOuts = schemeInOutRepository.selectBySchemeIds(new HashSet<>(schemeIdsInt));
        for (SchemeInOut sio : schemeInOuts) {
            Scheme scheme = schemesMap.get(sio.getSchemeId());
            if (scheme.getType().getTransactionType().equals(StockTransactionType.IN)) {

            } else if (scheme.getType().getTransactionType().equals(StockTransactionType.OUT)) {
                InventoryItem inventoryItem = inventoryItemRepository.selectById(sio.getInventoryItemId());
                List<ScanRecord> sr = scanRecordRepository.selectByInventoryItemId(sio.getInventoryItemId());
                ScanRecord scanRecord = sr.stream().filter(x -> x.getType().equals(ScanType.SALE)).max((x1, x2) -> x1.getCreateTimestamp().compareTo(x2.getCreateTimestamp())).get();
                if (scanRecord.getCreateTimestamp().isAfter(scheme.getEndDateTime()) || scanRecord.getCreateTimestamp().isBefore(scheme.getStartDateTime())) {
                    sio.setRolledBackTimestamp(LocalDateTime.now());
                    FofoOrder fofoOrder = fofoOrderRepository.selectByOrderId(scanRecord.getOrderId());
                    String rollbackReason = "Scheme reversed for " + itemRepository.selectById(inventoryItem.getItemId()).getItemDescription() + "/Inv - " + fofoOrder.getInvoiceNumber();
                    walletService.rollbackAmountFromWallet(scanRecord.getFofoId(), sio.getAmount(), scanRecord.getOrderId(), WalletReferenceType.SCHEME_OUT, rollbackReason, LocalDateTime.now());
                    System.out.printf("Amount %f,SchemeId %d,Reason %s\n", sio.getAmount(), sio.getSchemeId(), rollbackReason);
                }
            }
        }
        // throw new Exception();
    }

    public void checkfocusedModelInPartnerStock() throws Exception {

        // ========== PHASE 1: Batch fetch all data upfront ==========
        Map<Integer, FofoStore> fofoStoreMap = fofoStoreRepository.selectActiveStores().stream()
                .collect(Collectors.toMap(FofoStore::getId, x -> x));
        List<Integer> allPartnerIds = new ArrayList<>(fofoStoreMap.keySet());

        Map<Integer, Map<Integer, List<SaholicCIS>>> warehouseItemAvailabilityMap = saholicInventoryService.getSaholicStock();
        Map<Integer, String> warehouseMap = ProfitMandiConstants.WAREHOUSE_MAP;
        Map<Integer, FofoReportingModel> partnerIdSalesHeadersMap = this.getPartnerIdSalesHeaders();
        Map<Integer, List<FocusedModel>> regionFocusedModelsMap = focusedModelRepository.selectAll().stream()
                .collect(Collectors.groupingBy(FocusedModel::getRegionId));
        LOGGER.info("regionFocusedModelsMap - {}", regionFocusedModelsMap);
        Map<Integer, Map<Integer, List<SaholicPOItem>>> warehousePoItemAvailabilityMap = saholicInventoryService.getSaholicPOItems();

        // Batch fetch all customRetailers upfront
        Map<Integer, CustomRetailer> customRetailerMap = retailerService.getFofoRetailers(allPartnerIds);
        LOGGER.info("Fetched {} customRetailers", customRetailerMap.size());

        // Batch fetch all inventory snapshots upfront
        Map<Integer, List<CurrentInventorySnapshot>> allInventorySnapshotsMap = currentInventorySnapshotRepository
                .selectByFofoId(allPartnerIds).stream()
                .collect(Collectors.groupingBy(CurrentInventorySnapshot::getFofoId));
        LOGGER.info("Fetched inventory snapshots for {} partners", allInventorySnapshotsMap.size());

        // Batch fetch all pending GRN orders upfront
        Map<Integer, List<Order>> allGrnPendingOrdersMap = orderRepository.selectPendingGrnOrders(allPartnerIds).stream()
                .collect(Collectors.groupingBy(Order::getRetailerId));
        LOGGER.info("Fetched GRN pending orders for {} partners", allGrnPendingOrdersMap.size());

        // Batch fetch all processing orders upfront
        Map<Integer, List<Order>> allProcessingOrdersMap = orderRepository.selectOrders(allPartnerIds, orderStatusList).stream()
                .collect(Collectors.groupingBy(Order::getRetailerId));
        LOGGER.info("Fetched processing orders for {} partners", allProcessingOrdersMap.size());

        // Collect all focused catalog IDs
        Set<Integer> allFocusedCatalogIds = regionFocusedModelsMap.values().stream()
                .flatMap(List::stream)
                .map(FocusedModel::getCatalogId)
                .collect(Collectors.toSet());

        // Batch fetch all items by catalog IDs upfront
        List<Item> allItemsByCatalogId = itemRepository.selectAllByCatalogIds(allFocusedCatalogIds);
        Map<Integer, List<Item>> itemsByCatalogIdMap = allItemsByCatalogId.stream()
                .collect(Collectors.groupingBy(Item::getCatalogItemId));
        LOGGER.info("Fetched items for {} catalog IDs", itemsByCatalogIdMap.size());

        // Batch fetch all tagListings using pre-fetched items to avoid duplicate item fetch
        Map<Integer, TagListing> tagListingByCatalogIdMap = tagListingRepository
                .selectAllByItems(allItemsByCatalogId);
        LOGGER.info("Fetched {} tagListings", tagListingByCatalogIdMap.size());

        // Collect all item IDs from inventory, GRN orders, and processing orders for batch item lookup
        Set<Integer> allItemIds = new HashSet<>();
        allInventorySnapshotsMap.values().forEach(list ->
                list.forEach(cis -> allItemIds.add(cis.getItemId())));
        allGrnPendingOrdersMap.values().forEach(orders ->
                orders.forEach(o -> allItemIds.add(o.getLineItem().getItemId())));
        allProcessingOrdersMap.values().forEach(orders ->
                orders.forEach(o -> allItemIds.add(o.getLineItem().getItemId())));

        // Batch fetch all items and create itemId -> catalogId mapping
        Map<Integer, Integer> itemIdToCatalogIdMap = new HashMap<>();
        if (!allItemIds.isEmpty()) {
            itemRepository.selectByIds(allItemIds).forEach(item ->
                    itemIdToCatalogIdMap.put(item.getId(), item.getCatalogItemId()));
        }
        LOGGER.info("Built itemId->catalogId mapping for {} items", itemIdToCatalogIdMap.size());

        // ========== PHASE 2: Build partner region mappings ==========
        Map<Integer, Set<Integer>> partnerToRegions = partnerRegionRepository.selectAll().stream()
                .collect(Collectors.groupingBy(
                        pr -> pr.getFofoId(),
                        Collectors.mapping(pr -> pr.getRegionId(), Collectors.toSet())
                ));
        Map<Set<Integer>, List<Integer>> regionPartnerMap = partnerToRegions.entrySet().stream()
                .collect(Collectors.groupingBy(
                        Map.Entry::getValue,
                        Collectors.mapping(Map.Entry::getKey, Collectors.toList())
                ));
        LOGGER.info("regionPartnerMap - {}", regionPartnerMap.keySet());

        // ========== PHASE 3: Process partners using pre-fetched data ==========
        Map<Integer, List<FocusedModelShortageModel>> focusedModelShortageReportMap = new HashMap<>();

        for (Map.Entry<Set<Integer>, List<Integer>> regionPartnerEntry : regionPartnerMap.entrySet()) {
            Set<Integer> regionIds = regionPartnerEntry.getKey();
            List<Integer> partnerIds = new ArrayList<>(regionPartnerEntry.getValue());

            if (regionIds.stream().anyMatch(x -> !regionFocusedModelsMap.containsKey(x))) continue;

            LOGGER.info("Processing regionIds: {}", regionIds);
            if (regionIds == null || regionIds.isEmpty()) {
                LOGGER.info("No region found for partner {}", partnerIds);
                continue;
            }
            if (regionIds.contains(5)) {
                partnerIds.addAll(fofoStoreMap.keySet());
            }

            Map<Integer, Optional<Integer>> focusedCatalogIdAndQtyMap = regionIds.stream()
                    .map(regionFocusedModelsMap::get)
                    .flatMap(x -> x != null ? x.stream() : Stream.empty())
                    .collect(groupingBy(FocusedModel::getCatalogId,
                            mapping(FocusedModel::getObsMinimumQty, Collectors.maxBy(Integer::compareTo))));

            if (focusedCatalogIdAndQtyMap.isEmpty()) continue;

            for (int partnerId : partnerIds) {
                if (partnerId == 0) continue;
                FofoStore fofoStore = fofoStoreMap.get(partnerId);
                if (fofoStore == null) continue;

                CustomRetailer customRetailer = customRetailerMap.get(partnerId);
                if (customRetailer == null) continue;

                List<FocusedModelShortageModel> focusedModelShortageList = new ArrayList<>();
                focusedModelShortageReportMap.put(fofoStore.getId(), focusedModelShortageList);

                // Build catalogIdAndQtyMap from pre-fetched inventory snapshots
                Map<Integer, Integer> catalogIdAndQtyMap = new HashMap<>();
                List<CurrentInventorySnapshot> inventorySnapshots = allInventorySnapshotsMap.get(partnerId);
                if (inventorySnapshots != null) {
                    Map<Integer, Integer> itemQtyMap = inventorySnapshots.stream()
                            .collect(Collectors.toMap(CurrentInventorySnapshot::getItemId, CurrentInventorySnapshot::getAvailability));
                    for (Map.Entry<Integer, Integer> e : itemQtyMap.entrySet()) {
                        Integer catalogId = itemIdToCatalogIdMap.get(e.getKey());
                        if (catalogId != null) {
                            catalogIdAndQtyMap.merge(catalogId, e.getValue(), Integer::sum);
                        }
                    }
                }

                // Build grnPendingOrdersMap from pre-fetched orders
                Map<Integer, Integer> grnPendingOrdersMap = new HashMap<>();
                List<Order> grnOrders = allGrnPendingOrdersMap.get(partnerId);
                if (grnOrders != null) {
                    for (Order order : grnOrders) {
                        Integer catalogId = itemIdToCatalogIdMap.get(order.getLineItem().getItemId());
                        if (catalogId != null) {
                            grnPendingOrdersMap.merge(catalogId, order.getLineItem().getQuantity(), Integer::sum);
                        }
                    }
                }

                // Build processingOrderMap from pre-fetched orders
                Map<Integer, Integer> processingOrderMap = new HashMap<>();
                List<Order> processingOrders = allProcessingOrdersMap.get(partnerId);
                if (processingOrders != null) {
                    for (Order order : processingOrders) {
                        Integer catalogId = itemIdToCatalogIdMap.get(order.getLineItem().getItemId());
                        if (catalogId != null) {
                            processingOrderMap.merge(catalogId, order.getLineItem().getQuantity(), Integer::sum);
                        }
                    }
                }

                Map<Integer, List<SaholicCIS>> itemAvailabilityMap = warehouseItemAvailabilityMap.get(fofoStore.getWarehouseId());
                Map<Integer, List<SaholicPOItem>> poItemAvailabilityMap = warehousePoItemAvailabilityMap.get(fofoStore.getWarehouseId());

                for (Entry<Integer, Optional<Integer>> entry : focusedCatalogIdAndQtyMap.entrySet()) {
                    int catalogId = entry.getKey();
                    int minQty = entry.getValue().orElse(0);
                    int inStockQty = catalogIdAndQtyMap.getOrDefault(catalogId, 0);
                    int processingQty = processingOrderMap.getOrDefault(catalogId, 0);
                    int grnPendingQty = grnPendingOrdersMap.getOrDefault(catalogId, 0);

                    int grnStockQty = grnPendingQty + inStockQty;
                    int totalQty = processingQty + grnPendingQty + inStockQty;
                    int shortageQty = minQty - totalQty;

                    // Use pre-fetched items
                    List<Item> items = itemsByCatalogIdMap.get(catalogId);
                    if (items == null || items.isEmpty()) continue;

                    // Use pre-fetched tagListing
                    TagListing tagListing = tagListingByCatalogIdMap.get(catalogId);
                    if (tagListing == null) continue;

                    int allColorNetAvailability = 0;
                    int allColorPoAvailability = 0;

                    for (Item item : items) {
                        if (itemAvailabilityMap != null) {
                            List<SaholicCIS> currentAvailability = itemAvailabilityMap.get(item.getId());
                            if (currentAvailability != null) {
                                allColorNetAvailability += currentAvailability.stream()
                                        .mapToInt(SaholicCIS::getNetavailability).sum();
                            }
                        }
                        if (poItemAvailabilityMap != null) {
                            List<SaholicPOItem> poItemAvailability = poItemAvailabilityMap.get(item.getId());
                            if (poItemAvailability != null) {
                                allColorPoAvailability += poItemAvailability.stream()
                                        .mapToInt(SaholicPOItem::getUnfulfilledQty).sum();
                            }
                        }
                    }

                    FocusedModelShortageModel fm = new FocusedModelShortageModel();
                    fm.setFofoId(fofoStore.getId());
                    fm.setStoreCode(fofoStore.getCode());
                    fm.setStoreName(customRetailer.getBusinessName());
                    fm.setBrandName(items.get(0).getBrand());
                    fm.setModelName(items.get(0).getModelName());
                    fm.setModelNumber(items.get(0).getModelNumber());
                    fm.setGrnStockQty(grnStockQty);
                    fm.setPendingIndentQty(processingQty);
                    fm.setShortageQty(shortageQty);
                    fm.setPoAvailability(allColorPoAvailability);
                    fm.setDp(tagListing.getSellingPrice());
                    fm.setWarehouseName(warehouseMap.get(customRetailer.getWarehouseId()));

                    // FIX: Use fofoStore.getId() instead of fofoStore object as key
                    FofoReportingModel reportingModel = partnerIdSalesHeadersMap.get(fofoStore.getId());
                    if (reportingModel != null) {
                        fm.setStateManager(reportingModel.getRegionalManager());
                        fm.setTerritoryManager(reportingModel.getTerritoryManager());
                    } else {
                        fm.setStateManager("");
                        fm.setTerritoryManager("");
                    }

                    fm.setItemName(items.get(0).getBrand() + items.get(0).getModelNumber() + items.get(0).getModelName());
                    fm.setAvailability(allColorNetAvailability);

                    focusedModelShortageList.add(fm);
                }
            }
        }

        // ========== PHASE 4: Send emails ==========
        LOGGER.info("focusedModelShortageReportMap size: {}", focusedModelShortageReportMap.size());
        if (!focusedModelShortageReportMap.isEmpty()) {
            String fileName = "Stock Alert-" + FormattingUtils.formatDate(LocalDateTime.now()) + ".csv";
            Map<String, Set<Integer>> storeGuyMap = csService.getAuthUserPartnerIdMapping();
            Map<String, List<List<?>>> emailRowsMap = new HashMap<>();

            for (Map.Entry<Integer, List<FocusedModelShortageModel>> x : focusedModelShortageReportMap.entrySet()) {
                for (Map.Entry<String, Set<Integer>> y : storeGuyMap.entrySet()) {
                    if (y.getValue().contains(x.getKey())) {
                        emailRowsMap.computeIfAbsent(y.getKey(), k -> new ArrayList<>());
                        List<List<? extends Serializable>> fms = x.getValue().stream()
                                .map(r -> Arrays.asList(r.getStoreCode(), r.getStoreName(), r.getBrandName(),
                                        r.getModelName(), r.getModelNumber(), r.getDp(), r.getWarehouseName(),
                                        r.getStateManager(), r.getTerritoryManager(), r.getPendingIndentQty(),
                                        r.getGrnStockQty(), r.getShortageQty(), r.getAvailability()))
                                .collect(Collectors.toList());
                        emailRowsMap.get(y.getKey()).addAll(fms);
                    }
                }
            }

            List<String> headers = Arrays.asList("Store Code", "Store Name", "Brand", "Model Name", "Model Number",
                    "DP", "Warehouse Name", "State Manager", "Territory Manager", "Pending Indent", "InStock",
                    "Shortage Qty", "Availability");

            for (Map.Entry<String, List<List<?>>> entry : emailRowsMap.entrySet()) {
                try {
                    ByteArrayOutputStream baos = FileUtil.getCSVByteStream(headers, entry.getValue());
                    String[] sendToArray = new String[]{entry.getKey()};
                    Utils.sendMailWithAttachment(googleMailSender, sendToArray, null, "Stock Alert", "PFA",
                            fileName, new ByteArrayResource(baos.toByteArray()));
                } catch (Exception e) {
                    LOGGER.error("Failed to send email to {}", entry.getKey(), e);
                }
            }
        }
    }

    private String getNotificationMessage(List<FocusedModelShortageModel> focusedModelShortageModel) {
        StringBuilder sb = new StringBuilder();
        sb.append("Focused Model Shortage in Your Stock : \n");
        for (FocusedModelShortageModel entry : focusedModelShortageModel) {

            sb.append(entry.getItemName() + "-" + entry.getShortageQty());
            sb.append(String.format("%n", ""));
        }
        return sb.toString();
    }

    private void sendMailWithAttachments(String subject, String messageText, String email) throws Exception {
        MimeMessage message = mailSender.createMimeMessage();
        MimeMessageHelper helper = new MimeMessageHelper(message, true);

        helper.setSubject(subject);
        helper.setText(messageText, true);
        helper.setTo(email);
        InternetAddress senderAddress = new InternetAddress("noreply@smartdukaan.com", "Smartdukaan Alerts");
        helper.setFrom(senderAddress);
        mailSender.send(message);

    }

    private String getMessage(List<FocusedModelShortageModel> focusedModelShortageModel) {
        StringBuilder sb = new StringBuilder();
        sb.append("<html><body><p>Alert</p><p>Focused Model Shortage in Your Stock:-</p>" + "<br/><table style='border:1px solid black ;padding: 5px';>");
        sb.append("<tbody>\n" + "                                       <tr>\n" + "                                             <th style='border:1px solid black;padding: 5px'>Item</th>\n" + "                                                <th style='border:1px solid black;padding: 5px'>Shortage Qty</th>\n" + "                                        </tr>");
        for (FocusedModelShortageModel entry : focusedModelShortageModel) {

            sb.append("<tr>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + entry.getItemName() + "</td>");

            sb.append("<td style='border:1px solid black;padding: 5px'>" + entry.getShortageQty() + "</td>");

            sb.append("</tr>");

        }

        sb.append("</tbody></table></body></html>");

        return sb.toString();
    }

    public void notifyLead() throws Exception {
        List<Lead> leadsToNotify = leadRepository.selectLeadsScheduledBetweenDate(null, LocalDateTime.now().minusDays(15), LocalDateTime.now().plusHours(4));
        Map<Integer, String> authUserEmailMap = authRepository.selectAllActiveUser().stream().collect(Collectors.toMap(x -> x.getId(), x -> x.getEmailId()));
        LOGGER.info("authUserEmailMap {}", authUserEmailMap);
        Map<String, Integer> dtrEmailMap = dtrUserRepository.selectAllByEmailIds(new ArrayList<>(authUserEmailMap.values())).stream().collect(Collectors.toMap(x -> x.getEmailId(), x -> x.getId()));

        LOGGER.info("dtrEmailMap {}", dtrEmailMap);

        Map<Integer, Integer> authUserKeyMap = new HashMap<>();

        for (Entry<Integer, String> authUserEmail : authUserEmailMap.entrySet()) {
            int authId = authUserEmail.getKey();
            String email = authUserEmail.getValue();
            authUserKeyMap.put(authId, dtrEmailMap.get(email));
        }
        LOGGER.info("authUserKeyMap", authUserKeyMap);
        LOGGER.info("leadsToNotify {}", leadsToNotify);

        String templateMessage = "Lead followup for %s %s, %s, %s is due by %s";
        for (Lead lead : leadsToNotify) {
            if (authUserKeyMap.get(lead.getAssignTo()) == null) {
                LOGGER.info("Assignee no longer part of system {}", lead.getAssignTo());
                continue;
            }
            String title = "Leads followup Reminder";
            String notificationMessage = String.format(templateMessage, lead.getFirstName(), lead.getLastName(), lead.getAddress(), lead.getLeadMobile(), leadTimeFormatter.format(lead.getScheduledTimestamp()));
            String url = "https://app.smartdukaan.com/pages/home/leadUpdate?leadId=" + lead.getId();
            SendNotificationModel sendNotificationModel = new SendNotificationModel();
            sendNotificationModel.setCampaignName("Lead Reminder");
            sendNotificationModel.setTitle(title);
            sendNotificationModel.setMessage(notificationMessage);
            sendNotificationModel.setType("url");
            sendNotificationModel.setUrl(url);
            sendNotificationModel.setExpiresat(LocalDateTime.now().plusDays(2));
            sendNotificationModel.setMessageType(MessageType.reminder);
            sendNotificationModel.setUserIds(Arrays.asList(authUserKeyMap.get(lead.getAssignTo())));
            System.out.println(sendNotificationModel);
            notificationService.sendNotification(sendNotificationModel);
        }
    }

    @Autowired
    AuthService authService;

    public void sendUnscheduledFollowUpMail() throws Exception {
        List<Lead> missedScheduleLeads = leadRepository.selectLeadsScheduledBetweenDate(null, LocalDate.now().atStartOfDay().minusDays(30), LocalDateTime.now().toLocalDate().atStartOfDay());
        List<Lead> leadsScheduledForToday = leadRepository.selectLeadsScheduledBetweenDate(null, LocalDate.now().atStartOfDay(), LocalDate.now().atTime(LocalTime.MAX));
        List<Integer> authIds = missedScheduleLeads.stream().map(x -> x.getAssignTo()).distinct().collect(Collectors.toList());
        LOGGER.info(authIds.size());
        Map<Integer, AuthUser> authUserMap = authRepository.selectByIds(authIds).stream().collect(Collectors.toMap(x -> x.getId(), x -> x));
        LOGGER.info(authUserMap.size());
        //if(true) return;

        missedScheduleLeads = missedScheduleLeads.stream().sorted(Comparator.comparing(Lead::getState, String.CASE_INSENSITIVE_ORDER).thenComparing(Lead::getAssignTo).thenComparing(Lead::getScheduledTimestamp)).collect(Collectors.toList());
        //leadsScheduledForToday
        List<AuthUser> authUsers = authRepository.selectAllActiveUser();
        LOGGER.info("Auth Users Size - {}", authUsers.size());
        for (AuthUser authUser : authUsers) {
            LOGGER.info("Auth Name - {}", authUser.getFullName());
            List<Integer> reporteeAuthIds = authService.getAllReportees(authUser.getId());
            reporteeAuthIds.add(authUser.getId());
            StringBuilder sb = new StringBuilder();
            sb.append("<html><body>");
            List<Lead> authMissedScheduledLeads = missedScheduleLeads.stream().filter(x -> reporteeAuthIds.contains(x.getAssignTo())).collect(Collectors.toList());
            LOGGER.info("authMissedScheduledLeads {}", authMissedScheduledLeads.size());
            if (authMissedScheduledLeads.size() > 0) {
                addMissedScheduledLeadsTable(sb, authMissedScheduledLeads, authUserMap);
                sb.append("</body></html>");

                String subject = "Lead Updates Summary";
                String[] email = Arrays.asList(authUser.getEmailId()).toArray(new String[1]);
                //String[] email = Arrays.asList("amit.gupta@smartdukaan.com").toArray(new String[1]);
                this.sendMailOfHtmlFormat(googleMailSender, email, sb.toString(), null, subject);
                //break;
            }
        }

    }

    private void addMissedScheduledLeadsTable(StringBuilder
                                                      sb, List<Lead> missedScheduleLeads, Map<Integer, AuthUser> authUserMap) {
        sb.append("<h3>Leads That Missed the schedule</h3>");
        sb.append("<table style='border:1px solid black' cellspacing='0'>").append("<tr>").append("<th style='border:1px solid black;padding: 5px'>").append("Lead Id").append("</th>").append("<th style='border:1px solid black;padding: 5px'>").append("Lead Name").append("</th>").append("<th style='border:1px solid black;padding: 5px'>").append("Mobile No").append("</th>").append("<th style='border:1px solid black;padding: 5px'>").append("City").append("</th>").append("<th style='border:1px solid black;padding: 5px'>").append("State").append("</th>").append("<th style='border:1px solid black;padding: 5px'>").append("Created On").append("</th>").append("<th style='border:1px solid black;padding: 5px'>").append("Assigned To").append("</th>").append("<th style='border:1px solid black;padding: 5px'>").append("Scheduled On").append("</th>").append("</tr>");
        for (Lead lead : missedScheduleLeads) {
            sb.append("<tr style='background-color:").append(lead.getColor()).append("'>").append("<td style='border:1px solid black;padding: 5px'>").append(lead.getId()).append("</td>").append("<td style='border:1px solid black;padding: 5px'>").append(lead.getFullName()).append("</td>").append("<td style='border:1px solid black;padding: 5px'>").append(lead.getLeadMobile()).append("</td>").append("<td style='border:1px solid black;padding: 5px'>").append(lead.getCity()).append("</td>").append("<td style='border:1px solid black;padding: 5px'>").append(lead.getState()).append("</td>").append("<td style='border:1px solid black;padding: 5px'>").append(FormattingUtils.formatDate(lead.getCreatedTimestamp())).append("</td>").append("<td style='border:1px solid black;padding: 5px'>").append(
                    authUserMap.containsKey(lead.getAssignTo()) ? authUserMap.get(lead.getAssignTo()).getFullName() : lead.getAssignTo()).append("</td>").append("<td style='border:1px solid black;padding: 5px'>").append(FormattingUtils.formatDate(lead.getScheduledTimestamp())).append("</td>").append("</tr>");
        }
        sb.append("</table>");
    }

    public void notifyVisits() throws Exception {
        List<FranchiseeVisit> franchiseeVisits = franchiseeVisitRepository.selectVisitsScheduledBetweenDate(LocalDateTime.now().minusDays(15), LocalDateTime.now().plusHours(4));
        Map<Integer, String> authUserEmailMap = authRepository.selectAllActiveUser().stream().collect(Collectors.toMap(x -> x.getId(), x -> x.getEmailId()));
        Map<String, Integer> dtrEmailMap = dtrUserRepository.selectAllByEmailIds(new ArrayList<>(authUserEmailMap.values())).stream().collect(Collectors.toMap(x -> x.getEmailId(), x -> x.getId()));
        Map<Integer, Integer> authUserKeyMap = new HashMap<>();

        for (Entry<Integer, String> authUserEmail : authUserEmailMap.entrySet()) {
            int authId = authUserEmail.getKey();
            String email = authUserEmail.getValue();
            authUserKeyMap.put(authId, dtrEmailMap.get(email));
        }
        String visitTemplate = "Planned visit to franchisee %s is due by %s";
        String followupTemplate = "Lead followup for franchisee %s is due by %s";
        DateTimeFormatter timeFormatter = DateTimeFormatter.ofPattern("MMM 7, EEEE h:m a");
        for (FranchiseeVisit visit : franchiseeVisits) {
            if (authUserKeyMap.containsKey(visit.getAuthId())) {
                continue;
            }
            SendNotificationModel sendNotificationModel = new SendNotificationModel();
            String message = null;
            if (visit.getFranchiseActivityId() == 0) {
                message = String.format(visitTemplate, visit.getPartnerName(), timeFormatter.format(visit.getScheduleTimestamp()));
                sendNotificationModel.setCampaignName("Franchisee visit Reminder");
            } else {
                message = String.format(followupTemplate, visit.getPartnerName(), timeFormatter.format(visit.getScheduleTimestamp()));
                sendNotificationModel.setCampaignName("Franchisee followup Reminder");
            }
            sendNotificationModel.setMessage(message);
            sendNotificationModel.setType("url");
            sendNotificationModel.setUrl("https://app.smartdukaan.com/pages/home/notifications");
            sendNotificationModel.setExpiresat(LocalDateTime.now().plusDays(2));
            sendNotificationModel.setMessageType(MessageType.reminder);
            sendNotificationModel.setUserIds(Arrays.asList(authUserKeyMap.get(visit.getAuthId())));
            // notificationService.sendNotification(sendNotificationModel);
        }
    }

    public void ticketClosed() throws Exception {

        List<Ticket> tickets = ticketRepository.selectAllNotClosedTicketsWithStatus(ActivityType.RESOLVED);
        for (Ticket ticket : tickets) {
            if (ticket.getUpdateTimestamp().toLocalDate().isBefore(LocalDate.now().minusDays(7))) {
                ticket.setCloseTimestamp(LocalDateTime.now());
                ticket.setLastActivity(ActivityType.RESOLVED_ACCEPTED);
                ticket.setUpdateTimestamp(LocalDateTime.now());
                ticketRepository.persist(ticket);
            }
        }

    }

    public void checkValidateReferral() throws Exception {

        List<Refferal> referrals = refferalRepository.selectByStatus(RefferalStatus.pending);
        LOGGER.info("referrals" + referrals);
        if (!referrals.isEmpty()) {
            String subject = "Referral Request";
            String messageText = this.getMessageForReferral(referrals);

            MimeMessage message = mailSender.createMimeMessage();
            MimeMessageHelper helper = new MimeMessageHelper(message, true);
            String[] email = {"kamini.sharma@smartdukaan.com", "tarun.verma@smartdukaan.com"};
            helper.setSubject(subject);
            helper.setText(messageText, true);
            helper.setTo(email);
            InternetAddress senderAddress = new InternetAddress("noreply@smartdukaan.com", "Smartdukaan Alerts");
            helper.setFrom(senderAddress);
            mailSender.send(message);

        }
    }

    private String getMessageForReferral(List<Refferal> referrals) {
        StringBuilder sb = new StringBuilder();
        sb.append("<html><body><p>Alert</p><p>Pending Referrals:-</p>" + "<br/><table style='border:1px solid black ;padding: 5px';>");
        sb.append("<tbody>\n" + "                                       <tr>\n" + "                                             <th style='border:1px solid black;padding: 5px'>RefereeName</th>\n" + "                                         <th style='border:1px solid black;padding: 5px'>Referee Email</th>\n" + "                                               <th style='border:1px solid black;padding: 5px'>Referral Name</th>\n" + "                                               <th style='border:1px solid black;padding: 5px'>Refferal Mobile</th>\n" + "                                             <th style='border:1px solid black;padding: 5px'>city</th>\n" + "                                                <th style='border:1px solid black;padding: 5px'>state</th>\n" + "                                       </tr>");
        for (Refferal entry : referrals) {

            sb.append("<tr>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + entry.getRefereeName() + "</td>");

            sb.append("<td style='border:1px solid black;padding: 5px'>" + entry.getRefereeEmail() + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + entry.getFirstName() + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + entry.getMobile() + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + entry.getCity() + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + entry.getState() + "</td>");

            sb.append("</tr>");

        }

        sb.append("</tbody></table></body></html>");

        return sb.toString();
    }

    private String getMessageForRBMEscalatedPartners(List<PartnerCollectionRemark> pcrs) throws
            ProfitMandiBusinessException {
        StringBuilder sb = new StringBuilder();
        sb.append("<html><body><p>Alert</p><p>Escalated by RBM-L1:-</p>" + "<br/><table style='border:1px solid black ;padding: 5px';>");
        sb.append("<tbody>" +
                "<tr>" +
                "<th style='border:1px solid black;padding: 5px'>PartnerName</th>" +
                "<th style='border:1px solid black;padding: 5px'>Escalated Since</th>" +
                "<th style='border:1px solid black;padding: 5px'>Message</th>" +
                "</tr>");

        for (PartnerCollectionRemark entry : pcrs) {

            CustomRetailer customRetailer = retailerService.getFofoRetailer(entry.getFofoId());

            sb.append("<tr>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + customRetailer.getBusinessName() + "(" + customRetailer.getCode() + ")" + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + entry.getCreateTimestamp().format(DateTimeFormatter.ofPattern("dd/MM/yyyy")) + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + entry.getMessage() + "</td>");

            sb.append("</tr>");

        }

        sb.append("</tbody></table></body></html>");

        return sb.toString();
    }

    private String getMessageForSalesEscalatedPartners(List<PartnerCollectionRemark> pcrs) throws
            ProfitMandiBusinessException {
        StringBuilder sb = new StringBuilder();
        sb.append("<html><body><p>Alert</p><p>Escalated to Sales Team:-</p>" + "<br/>" +
                "<table style='border:1px solid black ;padding: 5px';>");
        sb.append("<tbody>" +
                "<tr>" +
                "<th style='border:1px solid black;padding: 5px'>PartnerName</th>" +
                "<th style='border:1px solid black;padding: 5px'>Escalated Since</th>" +
                "<th style='border:1px solid black;padding: 5px'>Message</th>" +
                "</tr>");
        for (PartnerCollectionRemark entry : pcrs) {
            CustomRetailer customRetailer = retailerService.getFofoRetailer(entry.getFofoId());
            sb.append("<tr>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + customRetailer.getBusinessName() + "(" + customRetailer.getCode() + ")" + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + entry.getCreateTimestamp().format(DateTimeFormatter.ofPattern("dd/MM/yyyy")) + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + entry.getMessage() + "</td>");
            sb.append("</tr>");
        }

        sb.append("</tbody></table></body></html>");

        return sb.toString();
    }

    private String getMessageForUnauthorizedBuying(List<NonSdBuying> nsb) throws
            ProfitMandiBusinessException {
        StringBuilder sb = new StringBuilder();
        sb.append("<html><body><p>Alert</p><p>Unauthorized Buying-</p>" + "<br/>" +
                "<table style='border:1px solid black ;padding: 5px';>");
        sb.append("<tbody>" +
                "<tr>" +
                "<th style='border:1px solid black;padding: 5px'>PartnerName</th>" +
                "<th style='border:1px solid black;padding: 5px'>Brand Name</th>" +
                "<th style='border:1px solid black;padding: 5px'>Brand Value</th>" +
                "<th style='border:1px solid black;padding: 5px'>Created At</th>" +
                "<th style='border:1px solid black;padding: 5px'>Reason</th>" +
                "</tr>");
        for (NonSdBuying entry : nsb) {
            CustomRetailer customRetailer = retailerService.getFofoRetailer(entry.getFofoId());
            List<Integer> brandIds = Collections.singletonList(entry.getBrandId());
            BrandCatalog brandsDisplay = brandsRepository.selectByIds(brandIds).get(0);
            sb.append("<tr>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + customRetailer.getBusinessName() + "(" + customRetailer.getCode() + ")" + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + brandsDisplay.getName() + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + entry.getBrandValue() + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + entry.getCreateTimestamp().format(DateTimeFormatter.ofPattern("dd/MM/yyyy")) + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + entry.getReason() + "</td>");
            sb.append("</tr>");
        }

        sb.append("</tbody></table></body></html>");

        return sb.toString();
    }

    public void reverseWallet() throws Exception {
        LocalDate localDate = LocalDate.of(2020, 10, 28);
        List<UserWalletHistory> uwhList = userWalletHistoryRepository.selectAllByDateType(localDate.atStartOfDay(), localDate.plusDays(1).atStartOfDay(), Arrays.asList(WalletReferenceType.SCHEME_IN, WalletReferenceType.SCHEME_OUT));
        for (UserWalletHistory uwh : uwhList) {
            if (uwh.getBusinessTimestamp().getMonth().equals(Month.SEPTEMBER)) {
                UserWallet uw = userWalletRepository.selectById(uwh.getWalletId());
                // LOGGER.info("UWH - {}", uwh);
                // amount += uwh.getAmount();
                // System.out.println("Amount is - " + amount);
                // walletService.rollbackAmountFromWallet(uw.getUserId(), uwh.getAmount(),
                // uwh.getReference(), uwh.getReferenceType(), "Margin reversal for error prone
                // run", uwh.getBusinessTimestamp());
                int orderId = uwh.getReference();
                FofoOrder fofoOrder = fofoOrderRepository.selectByOrderId(orderId);
                int inventoryItemId = 0;
                // fofoOrderRepository.delete(fofoOrder);
                List<FofoOrderItem> fofoOrderItems = fofoOrderItemRepository.selectByOrderId(fofoOrder.getId());
                /*
                 * List<PaymentOptionTransaction> paymentOptionTransactions =
                 * paymentOptionTransactionRepository.selectByReferenceIdAndType(fofoOrder.getId
                 * (), PaymentOptionReferenceType.ORDER); for(PaymentOptionTransaction
                 * paymentOptionTransaction : paymentOptionTransactions) {
                 * //paymentOptionTransactionRepository.delete(paymentOptionTransaction); }
                 */
                for (FofoOrderItem foi : fofoOrderItems) {
                    // fofoOrderItemRepository.delete(foi);
                    List<FofoLineItem> flis = fofoLineItemRepository.selectByFofoOrderItemId(foi.getId());
                    for (FofoLineItem fli : flis) {
                        // fofoLineItemRepository.delete(fli);
                        inventoryItemId = fli.getInventoryItemId();
                    }
                }
                List<SchemeInOut> schemeInOuts = schemeInOutRepository.selectByInventoryItemIds(
                        Stream.of(inventoryItemId).collect(Collectors.toSet()));
                for (SchemeInOut sio : schemeInOuts) {
                    if (sio.getCreateTimestamp().toLocalDate().equals(localDate)) {
                        LOGGER.info("SIO - {}", sio);
                        sio.setRolledBackTimestamp(LocalDateTime.now());
                    }
                }

            }
        }

    }

    public void partnerProblemAlert() throws Exception {
        LocalDateTime curDate = LocalDate.now().atStartOfDay();
        Map<Integer, Double> lmtdSale = fofoOrderItemRepository.selectSumMopGroupByRetailer(
                curDate.withDayOfMonth(1).minusMonths(1), curDate.with(LocalTime.MAX).minusMonths(1), 0, false);
        Map<Integer, Double> mtdSales = fofoOrderItemRepository.selectSumMopGroupByRetailer(curDate.withDayOfMonth(1), curDate.with(LocalTime.MAX), 0, false);
        Map<Integer, PartnerDailyInvestment> pdi = partnerDailyInvestmentRepository.selectAll(LocalDate.now()).stream().collect(Collectors.toMap(x -> x.getFofoId(), x -> x));

        for (Entry<Integer, Double> ls : lmtdSale.entrySet()) {

            double lmtdTwentyPercentSale = ls.getValue() * 0.2;

            LOGGER.info("lmtdTwentyPercentSale" + lmtdTwentyPercentSale);

            double mtdSale = mtdSales.get(ls.getKey()) == null ? 0 : mtdSales.get(ls.getKey());

            LOGGER.info("mtdSale" + mtdSale);

            double totalSixtyPercentInvestment = pdi.get(ls.getKey()) == null ? 0 : pdi.get(ls.getKey()).getTotalInvestment() * 0.6;

            LOGGER.info("totalSixtyPercentInvestment" + totalSixtyPercentInvestment);

            double stockInvestment = pdi.get(ls.getKey()) == null ? 0 : pdi.get(ls.getKey()).getInStockAmount();

            LOGGER.info("stockInvestment" + stockInvestment);

            boolean Investmentvalue = partnerInvestmentService.isInvestmentBelow(ls.getKey(), 25);

            PartnerProblem partnerProblem = partnerProblemRepository.selectByFofoId(ls.getKey());

            if (partnerProblem == null) {
                partnerProblem = new PartnerProblem();
                partnerProblem.setFofoId(ls.getKey());
                if (mtdSale < lmtdTwentyPercentSale) {
                    partnerProblem.setMtd(1);
                }
                if (Investmentvalue) {
                    partnerProblem.setInvestment(1);
                }

                if (stockInvestment < totalSixtyPercentInvestment) {
                    partnerProblem.setInvestment(1);
                }

                partnerProblemRepository.persist(partnerProblem);
            } else {
                if (mtdSale < lmtdTwentyPercentSale) {
                    partnerProblem.setMtd(partnerProblem.getMtd() + 1);

                } else {
                    partnerProblem.setMtd(0);

                }
                if (Investmentvalue) {
                    partnerProblem.setInvestment(partnerProblem.getInvestment() + 1);
                } else {
                    partnerProblem.setInvestment(0);

                }
                if (stockInvestment < totalSixtyPercentInvestment) {
                    partnerProblem.setStockInvestment(partnerProblem.getStockInvestment() + 1);
                } else {
                    partnerProblem.setStockInvestment(0);
                }

            }

        }

        List<PartnerProblem> partnerProblems = partnerProblemRepository.selectAll().stream().filter(x -> x.getMtd() + x.getInvestment() + x.getStockInvestment() > 0).collect(Collectors.toList());

        partnerProblems = partnerProblems.stream().sorted((x1, x2) -> {
            return x1.getMtd() + x1.getInvestment() + x1.getStockInvestment() < x2.getMtd() + x2.getInvestment() + x2.getStockInvestment() ? 1 : -1;
        }).collect(Collectors.toList());

        Map<Integer, PartnerProblem> partnerProblemMap = partnerProblems.stream().sorted((x1, x2) -> {
            return x1.getMtd() + x1.getInvestment() + x1.getStockInvestment() < x2.getMtd() + x2.getInvestment() + x2.getStockInvestment() ? 1 : -1;
        }).collect(Collectors.toMap(x -> x.getFofoId(), x -> x));

        Map<Integer, CustomRetailer> customRetailers = retailerService.getAllFofoRetailers();

        List<Integer> assignTo = Arrays.asList(15, 9, 54, 53);

        Map<Integer, AuthUser> assignAuthUserMap = authRepository.selectByIds(assignTo).stream().collect(Collectors.toMap(x -> x.getId(), x -> x));

        List<String> sendTo = Arrays.asList("kamini.sharma@smartdukaan.com", "tarun.verma@smartdukaan.com", "sm@smartdukaan.com");
        LOGGER.info("partnerProblem" + partnerProblems);
        StringBuilder sb = new StringBuilder();
        sb.append("<htl><body>");
        sb.append(
                "<p>Number against MTD or Investment are the count of days the MTD is low VS LMTD OR Investment is below 75%.</p><br/><p>These partners needs immediate attention.</p><br/><table style='border:1px solid black';cellspacing=0>");
        sb.append("<tbody>\n" + "           <tr>" + "                                           <th style='border:1px solid black;padding: 5px'>Partner Name</th>" + "                         <th style='border:1px solid black;padding: 5px'>Assign TO</th>" + "                                              <th style='border:1px solid black;padding: 5px'>Mtd</th>" + "                                           <th style='border:1px solid black;padding: 5px'>Investment</th>" + "                                            <th style='border:1px solid black;padding: 5px'>Stock</th>" + "                                         <th style='border:1px solid black;padding: 5px'>Manager</th>"

                + "                                     </tr>");

        for (PartnerProblem pp : partnerProblems) {
            int value = pp.getFofoId() % 4;

            Map<EscalationType, AuthUser> authUserMap = csService.getAuthUserAndEsclationByPartnerId(pp.getFofoId());
            LOGGER.info("authUserMap" + authUserMap);
            sb.append("<tr>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + customRetailers.get(pp.getFofoId()).getBusinessName() + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + assignAuthUserMap.get(assignTo.get(value)).getName() + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + pp.getMtd() + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + pp.getInvestment() + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + pp.getStockInvestment() + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + (authUserMap.get(EscalationType.L1) == null ? "N/A" : authUserMap.get(EscalationType.L1).getFirstName()) + " ," + (authUserMap.get(EscalationType.L2) == null ? "N/A" : authUserMap.get(EscalationType.L2).getFirstName()) + "</td>");

            sb.append("</tr>");
        }
        sb.append("</tbody></table><br><br>");
        String subject = "Partner Problem Alert!";
        String[] email = sendTo.toArray(new String[sendTo.size()]);

        this.sendMailOfHtmlFormat(googleMailSender, email, sb.toString(), null, subject);

        Map<Integer, List<Integer>> authUserPartnerMapping = csService.getAuthUserIdPartnerIdMapping();

        for (Entry<Integer, List<Integer>> authUserPartner : authUserPartnerMapping.entrySet()) {
            AuthUser authUser = authRepository.selectById(authUserPartner.getKey());
            for (Integer patnerId : authUserPartner.getValue()) {
                if (partnerProblemMap.get(patnerId) != null) {

                    StringBuilder sbbuilder = new StringBuilder();
                    sbbuilder.append("<hml><body>");
                    sb.append(
                            "<p>Number against MTD or Investment are the count of days the MTD is low VS LMTD OR Investment is below 75%.</p><br/><p>These partners needs immediate attention.</p><br/><table style='border:1px solid black';cellspacing=0>");
                    sbbuilder.append("<tbody>\n" + "        <tr>" + "                                           <th style='border:1px solid black;padding: 5px'>Partner Name</th>" + "                                          <th style='border:1px solid black;padding: 5px'>Mtd</th>" + "                                           <th style='border:1px solid black;padding: 5px'>Investment</th>" + "                                            <th style='border:1px solid black;padding: 5px'>Stock</th>"

                            + "                                 </tr>");

                    for (Integer partnerId : authUserPartner.getValue()) {
                        if (partnerProblemMap.get(partnerId) != null) {
                            PartnerProblem pp = partnerProblemMap.get(partnerId);
                            sbbuilder.append("<tr>");
                            sbbuilder.append("<td style='border:1px solid black;padding: 5px'>" + customRetailers.get(pp.getFofoId()).getBusinessName() + "</td>");
                            sbbuilder.append("<td style='border:1px solid black;padding: 5px'>" + pp.getMtd() + "</td>");
                            sbbuilder.append(
                                    "<td style='border:1px solid black;padding: 5px'>" + pp.getInvestment() + "</td>");
                            sbbuilder.append("<td style='border:1px solid black;padding: 5px'>" + pp.getStockInvestment() + "</td>");

                            sbbuilder.append("</tr>");
                        }

                    }
                    sbbuilder.append("</tbody></table><br><br>");

                    this.sendMailOfHtmlFormat(googleMailSender, new String[]{authUser.getEmailId()}, sbbuilder.toString(), null, subject);
                }

            }
        }
    }

    @Autowired
    WarehouseRepository warehouseRepository;

    public void getVendorWarehouses(int warehouseId) throws ProfitMandiBusinessException {
        LOGGER.info("Warehouses - {}", warehouseRepository.getVendorWarehouses().get(warehouseId));

    }

    public void checkImeiActivation() {

        try {
            vivoImeiActivationService.checkImeiActivation();
        } catch (ProfitMandiBusinessException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    public void checkItelImeiActivation(LocalDate date, Integer day) {
        LOGGER.info("Hello - {}", "hhh");

        try {
            itelImeiActivationService.checkItelImeiActivation(date, day);
        } catch (ProfitMandiBusinessException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    public void selectFinServiceFollowUpDateByCurrDate(LocalDate currentDate) throws
            MessagingException, ProfitMandiBusinessException, IOException {
        LOGGER.info("selectfinServiceFollow - {}", "selectfinServiceFollowUpDateByCurrDate");

        serviceConfigService.selectFinServicePartnerfollowUpDateByCurrentDate(currentDate);

    }

    public void checkTecnoImeiActivation(LocalDate date, Integer day) {
        LOGGER.info("Hello - {}", "hhh");

        try {
            tecnoImeiActivation.checkTecnoImeiActivation(date, day);
        } catch (ProfitMandiBusinessException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    public void checkCancellationMargin() throws Exception {
        LocalDateTime startDate = LocalDate.of(2021, 4, 1).atStartOfDay();
        List<FofoOrder> fofoOrders = fofoOrderRepository.selectCancelledBetweenSaleDate(startDate, LocalDateTime.now());
        LOGGER.info("Total Orders =  {}", fofoOrders.size());
        for (FofoOrder fofoOrder : fofoOrders) {
            List<UserWalletHistory> history = userWalletHistoryRepository.selectAllByreferenceIdandreferenceType(fofoOrder.getId(), WalletReferenceType.SCHEME_OUT);
            history.addAll(userWalletHistoryRepository.selectAllByreferenceIdandreferenceType(fofoOrder.getId(), WalletReferenceType.ACTIVATION_SCHEME));
            int walletSum = history.stream().mapToInt(x -> x.getAmount()).sum();
            if (Math.abs(walletSum) > 1) {
                // LOGGER.info("Cancelled invoice {}, Order Id = {}, = havent rolledback, Value
                // = {}", fofoOrder.getInvoiceNumber(), fofoOrder.getId(), walletSum);
                List<FofoOrderItem> fofoOrderItems = fofoOrderItemRepository.selectByOrderId(fofoOrder.getId());
                List<FofoLineItem> flis = fofoLineItemRepository.selectByFofoOrderItemId(fofoOrderItems.get(0).getId());
                FofoLineItem fli = flis.get(0);
                if (StringUtils.isEmpty(fli.getSerialNumber()))
                    continue;
                List<SchemeInOut> schemeInOuts = schemeInOutRepository.selectByInventoryItemIds(new HashSet<>(Arrays.asList(fli.getInventoryItemId())));
                InventoryItem inventoryItem = inventoryItemRepository.selectById(fli.getInventoryItemId());
                double schemeSum = schemeInOuts.stream().filter(x -> x.getRolledBackTimestamp() == null).mapToDouble(x -> x.getAmount()).sum();
                /*
                 * if(inventoryItem.getGoodQuantity()==1) {
                 * LOGGER.info("GoodQty, Serial Number {}, InventoryItem = {}, Billed on {}",
                 * inventoryItem.getSerialNumber(), inventoryItem.getId(),
                 * inventoryItem.getUpdateTimestamp()); } else
                 * if(inventoryItem.getBadQuantity()==1){
                 * LOGGER.info("Bad Qty, Serial Number {}, InventoryItem = {}, Billed on {}",
                 * inventoryItem.getSerialNumber(), inventoryItem.getId(),
                 * inventoryItem.getUpdateTimestamp()); } else if
                 * (inventoryItem.getLastScanType().equals(ScanType.SALE)) { LOGGER.
                 * info("Problem Sold, Serial Number {}, InventoryItem = {}, Billed on {}",
                 * inventoryItem.getSerialNumber(), inventoryItem.getId(),
                 * inventoryItem.getUpdateTimestamp()); }
                 */
                if (inventoryItem.getLastScanType().equals(ScanType.SALE) || inventoryItem.getLastScanType().equals(ScanType.SALE_RET)) {
                    Map<String, Double> map = schemeInOuts.stream().collect(
                            groupingBy(
                                    x -> DateTimeFormatter.ofPattern("yyyyMMddHH").format(x.getCreateTimestamp()) + "- " + x.getSchemeId(), Collectors.summingDouble(x -> x.getAmount())));
                    for (Entry<String, Double> entry : map.entrySet()) {
                        LOGGER.info("{} = {}", entry.getKey(), entry.getValue());
                    }
                    LOGGER.info("id- {}, imei- {}, lastScan = {}, Scheme sum - {}", inventoryItem.getId(), inventoryItem.getSerialNumber(), inventoryItem.getLastScanType(), schemeSum);
                }
            }
        }

    }

    public static String readFileAsString(String file) throws Exception {
        String data = "";
        data = new String(Files.readAllBytes(Paths.get(file)));
        return data;
    }

    @Autowired
    private PartnerCollectionRemarkRepository partnerCollectionRemarkRepository;

    //RBM L2 Escalations
    public void reviewUncontactablePartner() throws
            ProfitMandiBusinessException, MessagingException, UnsupportedEncodingException {

        Map<Integer, CustomRetailer> customRetailerMap = retailerService.getFofoRetailers(true);

        List<Integer> partnerLastCollectionRemarkIds = partnerCollectionRemarkRepository.selectMaxRemarkId(new ArrayList<>(customRetailerMap.keySet()));

        if (!partnerLastCollectionRemarkIds.isEmpty()) {
            List<PartnerCollectionRemark> partnerCollectionRemarks = partnerCollectionRemarkRepository.selectByIds(partnerLastCollectionRemarkIds);

            Map<Integer, PartnerCollectionRemark> rbmEscalatedRemarksMap = partnerCollectionRemarks.stream().filter(x -> x.getRemark().equals(CollectionRemark.RBM_L2_ESCALATION)).collect(Collectors.toMap(x -> x.getFofoId(), x -> x));
            Map<Integer, PartnerCollectionRemark> salesEscalatedRemarksMap = partnerCollectionRemarks.stream().filter(x -> x.getRemark().equals(CollectionRemark.SALES_ESCALATION)).collect(Collectors.toMap(x -> x.getFofoId(), x -> x));


            if (!rbmEscalatedRemarksMap.isEmpty()) {
                //
                for (Entry<String, Set<Integer>> storeGuyEntry : csService.getAuthUserPartnerIdMappingByCategoryIds(Arrays.asList(ProfitMandiConstants.TICKET_CATEGORY_RBM), true).entrySet()) {
                    List<PartnerCollectionRemark> filteredRows = storeGuyEntry.getValue().stream()
                            .map(x -> rbmEscalatedRemarksMap.get(x)).filter(x -> x != null).collect(Collectors.toList());
                    String subject = "Partners escalated to RBM-L2";
                    String messageText = this.getMessageForRBMEscalatedPartners(filteredRows);

                    MimeMessage message = mailSender.createMimeMessage();
                    MimeMessageHelper helper = new MimeMessageHelper(message, true);
                    String[] email = new String[]{storeGuyEntry.getKey()};

                    helper.setSubject(subject);
                    helper.setText(messageText, true);
                    helper.setTo(email);
                    InternetAddress senderAddress = new InternetAddress("noreply@smartdukaan.com", "Smartdukaan Alerts");
                    helper.setFrom(senderAddress);
                    mailSender.send(message);

                    LOGGER.info("filteredRows {}", filteredRows);

                }
            }
            if (!salesEscalatedRemarksMap.isEmpty()) {
                //
                for (Entry<String, Set<Integer>> storeGuyEntry : csService.getAuthUserPartnerIdMappingByCategoryIds(Arrays.asList(ProfitMandiConstants.TICKET_CATEGORY_SALES, ProfitMandiConstants.TICKET_CATEGORY_ABM, ProfitMandiConstants.TICKET_CATEGORY_RBM), true).entrySet()) {
                    List<PartnerCollectionRemark> filteredRows = storeGuyEntry.getValue().stream()
                            .map(x -> salesEscalatedRemarksMap.get(x)).filter(x -> x != null).collect(Collectors.toList());
                    String subject = "Partners escalated to Sales";
                    String messageText = this.getMessageForSalesEscalatedPartners(filteredRows);

                    MimeMessage message = mailSender.createMimeMessage();
                    MimeMessageHelper helper = new MimeMessageHelper(message, true);
                    String[] email = new String[]{storeGuyEntry.getKey()};

                    helper.setSubject(subject);
                    helper.setText(messageText, true);
                    helper.setTo(email);
                    InternetAddress senderAddress = new InternetAddress("noreply@smartdukaan.com", "Smartdukaan Alerts");
                    helper.setFrom(senderAddress);
                    mailSender.send(message);

                    LOGGER.info("filteredRows {}", filteredRows);

                }
            }

        }
    }


    @Autowired
    private NonSdBuyingRepository nonSdBuyingRepository;

    //Non Sd Buying
    public void reviewNonSdBuyingBrand() throws
            ProfitMandiBusinessException, MessagingException, UnsupportedEncodingException {
        List<NonSdBuying> nonSdByuingActiveList = nonSdBuyingRepository.selectAllActiveNonSDBuyingList(true);
        Map<Integer, NonSdBuying> unAuthorizedBuyingMap = nonSdByuingActiveList.stream().collect(Collectors.toMap(x -> x.getFofoId(), x -> x));

        if (!unAuthorizedBuyingMap.isEmpty()) {
            for (Entry<String, Set<Integer>> storeGuyEntry : csService.getAuthUserPartnerIdMappingByCategoryIds(Arrays.asList(ProfitMandiConstants.TICKET_CATEGORY_RBM, ProfitMandiConstants.TICKET_CATEGORY_SALES, ProfitMandiConstants.TICKET_CATEGORY_ABM), true).entrySet()) {
                List<NonSdBuying> unAuthorizedfilteredRows = storeGuyEntry.getValue().stream()
                        .map(x -> unAuthorizedBuyingMap.get(x)).filter(x -> x != null).collect(Collectors.toList());
                if (!unAuthorizedfilteredRows.isEmpty()) {
                    String subject = "Unauthorized Buying";
                    String messageText = this.getMessageForUnauthorizedBuying(unAuthorizedfilteredRows);

                    MimeMessage message = mailSender.createMimeMessage();
                    MimeMessageHelper helper = new MimeMessageHelper(message, true);
                    String[] email = new String[]{storeGuyEntry.getKey()};

                    helper.setSubject(subject);
                    helper.setText(messageText, true);
                    helper.setTo(email);
                    InternetAddress senderAddress = new InternetAddress("noreply@smartdukaan.com", "Smartdukaan Alerts");
                    helper.setFrom(senderAddress);
                    mailSender.send(message);
                    LOGGER.info("unAuthorizedfilteredRows {}", unAuthorizedfilteredRows);
                }


            }

        }
    }

    @Autowired
    private LoanRepository loanRepository;

    @Autowired
    private LoanStatementRepository loanStatementRepository;

    @Autowired
    private SDCreditRequirementRepository sdCreditRequirementRepository;

    @Autowired
    private CurrentPartnerDailyInvestmentRepository currentPartnerDailyInvestmentRepository;

    @Autowired
    private TransactionRepository transactionRepository;

    @Autowired
    private SanctionRequestRepository sanctionRequestRepository;

    public void calculateInterestAccured() throws ProfitMandiBusinessException {
        List<Loan> loans = loanRepository.selectAllActiveLoan();
        //List<Loan> loans = Arrays.asList(loanRepository.selectByLoanId(52669));

        for (Loan loan : loans) {
            List<LoanStatement> loanStatements = loanStatementRepository.selectByLoanId(loan.getId());
            Map<LoanReferenceType, Double> loanStatusAmount = loanStatements.stream().collect(groupingBy(
                    x -> x.getLoanReferenceType(), Collectors.summingDouble(x -> x.getAmount().doubleValue())));

            int freeDays = loan.getFreeDays();

            LocalDate freeDaysLimitDate = loan.getCreatedOn().plusDays(freeDays).toLocalDate();

            LOGGER.info("Loan id - {}, freeDaysLimitDate {}", loan.getId(), freeDaysLimitDate);
            if (LocalDate.now().isAfter(freeDaysLimitDate)) {
                int loanStatementId = loanStatementRepository.selectLatestLoanSatement(loan.getFofoId(), loan.getId());
                LOGGER.info("loanStatementId {}", loanStatementId);
                if (loanStatementId != 0) {
                    this.calculateInterest(loan, loanStatusAmount, loanStatementId);
                } else {
                    sdCreditService.addInterest(freeDaysLimitDate, LocalDate.now(), loan, loanStatusAmount);
                }
            }
        }
    }

    @Autowired
    FofoPaymentRepository fofoPaymentRepository;

    @Autowired
    CCAvenuePaymentService ccAvenuePaymentService;

    public void settleCCAvePayments() throws Exception {
        List<FofoPayment> fofoPayments = fofoPaymentRepository.selectAllByGatewayStatus(Gateway.CCAVENUE, PaymentStatus.INIT).stream().filter(x -> x.getCreateTimestamp().isBefore(LocalDateTime.now().minusMinutes(30l))).collect(Collectors.toList());
        for (FofoPayment fofoPayment : fofoPayments) {
            ccAvenuePaymentService.updatePayment(fofoPayment);
        }
    }

    /*
     * public void getCCAvenuePendingOrderStatus(int pendingOrderId) { PendingOrder
     * pendingOrder = pendingOrderRepository.selectById(pendingOrderId);
     * ccAvenuePaymentService.getPaymentStatusMap(pendingOrder); }
     */

    private void calculateInterest(Loan loan, Map<LoanReferenceType, Double> loanStatusAmount,
                                   int loanStatementId) throws ProfitMandiBusinessException {
        LoanStatement loanStatement = loanStatementRepository.selectById(loanStatementId);

        if (loanStatement.getLoanId() == loan.getId()) {
            sdCreditService.addInterest(loanStatement.getCreatedAt().toLocalDate().plusDays(1), LocalDate.now(), loan, loanStatusAmount);
        } else {
            throw new ProfitMandiBusinessException("loanstament", loanStatement.getLoanId(), "Invalid LoanId");
        }
    }

    public void loanSettlement() throws Exception {
        List<Loan> loans = loanRepository.selectAllActiveLoanForSettlement().stream().filter(x -> x.canBeSettled()).collect(toList());
        //TODO:Upto here
        if (!loans.isEmpty()) {

            for (Loan loan : loans) {
                LOGGER.info("Loan - {},  {}", loan.getId(), loan.canBeSettled());
                double settledAmount = sdCreditService.settleLoan(loan);

                List<SanctionRequest> sanctionRequests = sanctionRequestRepository.selectHoldSanctionByFofoId(loan.getFofoId());
                for (SanctionRequest sanctionRequest : sanctionRequests) {

                    List<Order> orders = orderRepository.selectAllByTransactionId(sanctionRequest.getTransactionId());
                    if (orders.size() == 0) {
                        LOGGER.info("Could not find orders - for Sanction Request {}", sanctionRequest);
                        continue;
                    }

                    if (settledAmount >= sanctionRequest.getPendingAmount().doubleValue()) {
                        settledAmount -= sanctionRequest.getPendingAmount().doubleValue();
                        sanctionRequest.setPendingAmount(BigDecimal.valueOf(0));
                        this.sendUnholdEmail(orders);
                    } else {
                        double pendinAmount = sanctionRequest.getPendingAmount().doubleValue() - settledAmount;
                        System.out.println("Pending Amount - " + pendinAmount);
                        sanctionRequest.setPendingAmount(BigDecimal.valueOf(pendinAmount));
                        break;

                    }

                }
            }

        }


    }


    private void sendUnholdEmail(List<Order> orders) throws Exception {


        orders.forEach(x -> x.setShipmentHold(false));
        orders = orders.stream().filter(x -> x.getRefundTimestamp() != null).collect(Collectors.toList());
        if (orders.size() > 0) {

            double totalAmount = orders.stream().collect(Collectors.summingDouble(x -> x.getTotalAmount()));

            List<String> authUserEmail = csService.getAuthUserIdByPartnerId(orders.get(0).getRetailerId()).stream().map(x -> x.getEmailId()).collect(Collectors.toList());
            authUserEmail.add("vinay.p@smartdukaan.com");
            authUserEmail.add("shivam.gupta@smartdukaan.com");

            String[] emailTo = authUserEmail.toArray(new String[authUserEmail.size()]);

            String[] ccTo = {"tarun.verma@smartdukaan.com", "kamini.sharma@smartdukaan.com"};

            String subject = "Dispatch held orders of - " + (orders.get(0).getRetailerName());
            String message = String.format("Dear Team, \n" + "kindly note the material for the " + orders.get(0).getRetailerName() + "of Rs." + totalAmount + "is unhold now and needs to be dispatched.");
            Utils.sendMailWithAttachments(mailSender, emailTo, ccTo, subject, message);
        }


    }

    public void dailyLoanAlert() throws Exception {
        List<Loan> loans = loanRepository.selectAllActiveLoan();

        Map<Integer, Double> partnerLoanAmount = new HashMap<>();

        if (!loans.isEmpty()) {

            for (Loan loan : loans) {

                List<LoanStatement> loanStatements = loanStatementRepository.selectByLoanId(loan.getId());

                double amount = loanStatements.stream().map(x -> x.getAmount()).collect(Collectors.summingDouble(x -> x.doubleValue()));
                if (partnerLoanAmount.get(loan.getFofoId()) != null) {
                    amount += partnerLoanAmount.get(loan.getFofoId());
                    partnerLoanAmount.put(loan.getFofoId(), amount);
                } else {
                    partnerLoanAmount.put(loan.getFofoId(), amount);
                }

            }

        }

        if (!partnerLoanAmount.isEmpty()) {

            for (Entry<Integer, Double> partnerLoanAmountEnrty : partnerLoanAmount.entrySet()) {

                int fofoId = partnerLoanAmountEnrty.getKey();

                com.spice.profitmandi.dao.entity.user.User user = userUserRepository.selectById(fofoId);

                Address address = addressRepository.selectById(user.getAddressId());

                String title = "Alert Credit Outstanding!";
                String url = "http://app.smartdukaan.com/pages/home/credit";
                String message = "Your total outstanding Credit Balance is Rs." + FormattingUtils.formatDecimal(Math.abs(partnerLoanAmountEnrty.getValue())) + ".";
                notificationService.sendNotification(fofoId, title, MessageType.notification, title, message, url);

                //notificationService.sendWhatsappMessage(message, title, address.getPhoneNumber());

            }

        }

    }

    public void processActivatedImeisForSchemes() throws ProfitMandiBusinessException {
        schemeService.processActivatedImeisForSchemes();
    }

    @Autowired
    SidbiService sidbiService;

    public void processSidbiDeductions() throws ProfitMandiBusinessException {
        //sidbiService.migrateSanctions();
        sidbiService.processDeductions();
    }

    public void updatePartnerLimit(int fofoId) throws ProfitMandiBusinessException {
        LocalDateTime firstBillingDate = transactionRepository.getFirstBillingDate(fofoId);

        CreditAccount creditAccount = creditAccountRepository.selectByFofoIdAndGateway(fofoId, Gateway.SDDIRECT);
        BigDecimal utilizationAmount = sdCreditService.getUtilizationAmount(fofoId);
        PartnerDailyInvestment partnerDailyInvestment = partnerInvestmentService.getInvestment(fofoId, 0);
        BigDecimal suggestedAmount = this.getSuggestedAmount(creditAccount, partnerDailyInvestment, utilizationAmount, null);
        SDCreditRequirement sdCreditRequirement = sdCreditRequirementRepository.selectByFofoId(fofoId);

        LOGGER.info("suggestedAmount {} ", suggestedAmount);

        if (sdCreditRequirement == null) {
            sdCreditRequirement = new SDCreditRequirement();
            sdCreditRequirement.setFofoId(fofoId);
            sdCreditRequirement.setCreditDays(15);
            sdCreditRequirement.setInterestRate(ProfitMandiConstants.NEW_INTEREST_RATE);
            sdCreditRequirement.setRisk(CreditRisk.HIGH_RISK);
            sdCreditRequirement.setUtilizedAmount(BigDecimal.ZERO);
            sdCreditRequirement.setCreateTimestamp(LocalDateTime.now());
            sdCreditRequirement.setUpdateTimestamp(LocalDateTime.now());
            sdCreditRequirementRepository.persist(sdCreditRequirement);
        }

        CreditRisk creditRisk = sdCreditService.getCurrentRisk(sdCreditRequirement, firstBillingDate);

        sdCreditRequirement.setRisk(creditRisk);
        LOGGER.info("Suggested Amount - {}", suggestedAmount);
        sdCreditRequirement.setSuggestedLimit(suggestedAmount);
        if (!sdCreditRequirement.isHardLimit()) {
            sdCreditRequirement.setLimit(suggestedAmount);
        }
        BigDecimal utilizedLimit = sdCreditService.getUtilizationAmount(fofoId);

        BigDecimal availableLimit = sdCreditRequirement.getLimit().subtract(utilizedLimit);

        LOGGER.info("utilizedLimit {} ", utilizedLimit);

        LOGGER.info("availableLimit {} ", availableLimit);

        sdCreditRequirement.setUtilizedAmount(utilizedLimit);

        sdCreditRequirement.setUpdateTimestamp(LocalDateTime.now());

        if (creditAccount != null) {

            creditAccount.setInterestRate(sdCreditRequirement.getInterestRate().floatValue());
            creditAccount.setSanctionedAmount(sdCreditRequirement.getLimit().floatValue());
            creditAccount.setAvailableAmount(availableLimit.floatValue());
            creditAccount.setFreeDays(sdCreditRequirement.getFreeDays());
            creditAccount.setUpdatedOn(LocalDateTime.now());
        }

    }

    public void updatePartnerLimit() throws ProfitMandiBusinessException {
        Map<Integer, CustomRetailer> customRetailerMap = retailerService.getFofoRetailers(true);
        Map<Integer, BigDecimal> fofoSidbiLimitMap = sidbiService.getSuggestedLimitMap();
        Map<Integer, SDCreditRequirement> sdCreditRequirementMap = sdCreditRequirementRepository.selectAll().stream().collect(Collectors.toMap(x -> x.getFofoId(), x -> x));
        Map<Integer, CreditAccount> creditAccountMap = creditAccountRepository.selectAllByGateways(Arrays.asList(Gateway.SIDBI, Gateway.SDDIRECT))
                .stream().filter(x -> x.isActive()).collect(Collectors.toMap(x -> x.getFofoId(), x -> x));
        for (Entry<Integer, CustomRetailer> customRetailerEntry : customRetailerMap.entrySet()) {
            int fofoId = customRetailerEntry.getKey();
            LocalDateTime firstBillingDate = transactionRepository.getFirstBillingDate(fofoId);

            CreditAccount creditAccount = creditAccountMap.get(fofoId);
            BigDecimal utilizationAmount = sdCreditService.getUtilizationAmount(fofoId);
            PartnerDailyInvestment partnerDailyInvestment = partnerInvestmentService.getInvestment(fofoId, 0);
            BigDecimal suggestedAmount = this.getSuggestedAmount(creditAccount, partnerDailyInvestment, utilizationAmount, fofoSidbiLimitMap.get(fofoId));
            SDCreditRequirement sdCreditRequirement = sdCreditRequirementMap.get(fofoId);

            LOGGER.info("suggestedAmount {} ", suggestedAmount);

            if (sdCreditRequirement == null) {
                sdCreditRequirement = new SDCreditRequirement();
                sdCreditRequirement.setFofoId(fofoId);
                sdCreditRequirement.setCreditDays(15);
                sdCreditRequirement.setInterestRate(ProfitMandiConstants.NEW_INTEREST_RATE);
                sdCreditRequirement.setRisk(CreditRisk.HIGH_RISK);
                sdCreditRequirement.setUtilizedAmount(BigDecimal.ZERO);
                sdCreditRequirement.setCreateTimestamp(LocalDateTime.now());
                sdCreditRequirement.setUpdateTimestamp(LocalDateTime.now());
                sdCreditRequirementRepository.persist(sdCreditRequirement);
            }

            CreditRisk creditRisk = sdCreditService.getCurrentRisk(sdCreditRequirement, firstBillingDate);

            sdCreditRequirement.setRisk(creditRisk);

            sdCreditRequirement.setSuggestedLimit(suggestedAmount);
            if (!sdCreditRequirement.isHardLimit()) {
                sdCreditRequirement.setLimit(suggestedAmount);
            }
            BigDecimal utilizedLimit = sdCreditService.getUtilizationAmount(fofoId);

            BigDecimal availableLimit = sdCreditRequirement.getLimit().subtract(utilizedLimit);

            LOGGER.info("utilizedLimit {} ", utilizedLimit);

            LOGGER.info("availableLimit {} ", availableLimit);

            sdCreditRequirement.setUtilizedAmount(utilizedLimit);

            sdCreditRequirement.setUpdateTimestamp(LocalDateTime.now());

            if (creditAccount != null) {

                creditAccount.setInterestRate(sdCreditRequirement.getInterestRate().floatValue());
                creditAccount.setSanctionedAmount(sdCreditRequirement.getLimit().floatValue());
                creditAccount.setAvailableAmount(availableLimit.floatValue());
                creditAccount.setFreeDays(sdCreditRequirement.getFreeDays());
                creditAccount.setUpdatedOn(LocalDateTime.now());
            }

        }

    }

    private static final NavigableMap<Double, Double> discountMap = new TreeMap<>();

    static {
        discountMap.put(4 * ProfitMandiConstants.ONE_LAC - 1, 0.2);    // Up to 3,99,999 -> 20%
        discountMap.put(10 * ProfitMandiConstants.ONE_LAC - 1, 0.25);  // 4,00,000 to 9,99,999 -> 25%
        discountMap.put(20 * ProfitMandiConstants.ONE_LAC - 1, 0.3);   // 10,00,000 to 19,99,999 -> 30%
        discountMap.put(Double.MAX_VALUE, 0.4);                        // 20,00,000 and above -> 40%
    }

    public BigDecimal getSuggestedLimit(double investmentValue) {
        double percentageValue = discountMap.ceilingEntry(investmentValue).getValue();
        return this.getSuggestedAmount(investmentValue, percentageValue);

    }

    private BigDecimal getSuggestedAmount(double investmentValue, double percentageValue) {
        return BigDecimal.valueOf(investmentValue * percentageValue);
    }

    /*private static List<Integer> hundredPercentLimitPartnerIds = Arrays.asList(175139630,
            175139815,
            175139844,
            175140101
    );*/

    private static List<Integer> hundredPercentLimitPartnerIds = Arrays.asList();

    private BigDecimal getSuggestedAmount(CreditAccount creditAccount, PartnerDailyInvestment
            partnerDailyInvestment, BigDecimal utilizationAmount, BigDecimal sidbiLimit) {
        BigDecimal suggestedAmount = BigDecimal.ZERO;
        double utilization = utilizationAmount != null ? utilizationAmount.doubleValue() : 0;
        //1 LAC
        if (creditAccount == null || creditAccount.getGateway().equals(Gateway.SDDIRECT)) {

            if (partnerDailyInvestment != null) {
                if (hundredPercentLimitPartnerIds.contains(partnerDailyInvestment.getFofoId())) {
                    suggestedAmount = getSuggestedAmount(partnerDailyInvestment.getTotalInvestment() - utilization, 1);
                    suggestedAmount = suggestedAmount.min(BigDecimal.valueOf(1500000));
                } else {
                    suggestedAmount = getSuggestedLimit(partnerDailyInvestment.getTotalInvestment() - utilization);
                }
            }
            if (suggestedAmount.doubleValue() < 0) {
                suggestedAmount = BigDecimal.ZERO;
            }
        } else if (creditAccount.getGateway().equals(Gateway.SIDBI) && sidbiLimit != null) {
            suggestedAmount = getSuggestedLimit(partnerDailyInvestment.getTotalInvestment() - utilization);
            suggestedAmount = suggestedAmount.max(sidbiLimit);


        }
        return suggestedAmount;
    }

    public void notifyDefaultLoans() throws ProfitMandiBusinessException, MessagingException, IOException {

        sdCreditService.updateRisk();
        List<Loan> defaultLoans = sdCreditService.getDefaultLoans();
        if (!defaultLoans.isEmpty()) {
            this.sendDefaultLoanAlert(defaultLoans);
        }

    }

    public void sendDefaultLoanAlert(List<Loan> defaultLoans) throws
            ProfitMandiBusinessException, MessagingException, IOException {
        String subject = "Default Partners";

        List<Integer> categoryIds = Arrays.asList(ProfitMandiConstants.TICKET_CATEGORY_CATEGORY, ProfitMandiConstants.TICKET_CATEGORY_RBM, ProfitMandiConstants.TICKET_CATEGORY_SALES, ProfitMandiConstants.TICKET_CATEGORY_ABM, ProfitMandiConstants.TICKET_CATEGORY_ACCOUNTS, ProfitMandiConstants.TICKET_CATEGORY_BUSINESSINTELLIGENT);

        for (Entry<String, Set<Integer>> storeGuyEntry : csService.getAuthUserPartnerIdMappingByCategoryIds(categoryIds, false).entrySet()) {
            List<Loan> filteredRows = new ArrayList<>();
            for (Loan loan : defaultLoans) {

                if (storeGuyEntry.getValue().contains(loan.getFofoId())) {

                    filteredRows.add(loan);
                }
            }
            if (!filteredRows.isEmpty()) {
                String[] email = new String[]{storeGuyEntry.getKey()};
                String messageText = this.getMessageForDueDateExtend(filteredRows);
                this.sendMailOfHtmlFormat(googleMailSender, email, messageText, null, subject);

            }

        }
        String messageText = this.getMessageForDueDateExtend(defaultLoans);
        this.sendMailOfHtmlFormat(googleMailSender, new String[]{"puneet.wahi@smartdukaan.com", "rahul.latwal@smartdukaan.com", "dilbag.singh@smartdukaan.com"}, messageText, null, subject);

        for (Loan defaultLoan : defaultLoans) {
            List<LoanStatement> loanStatements = loanStatementRepository.selectByLoanId(defaultLoan.getId());

            double amount = loanStatements.stream().map(x -> x.getAmount()).collect(Collectors.summingDouble(x -> x.doubleValue()));

            com.spice.profitmandi.dao.entity.user.User user = userUserRepository.selectById(defaultLoan.getFofoId());

            Address address = addressRepository.selectById(user.getAddressId());

            String title = "Outstanding Credit Payment Overdue!";
            String url = "http://app.smartdukaan.com/pages/home/credit";
            String message = "Your Outstanding Credit payment due date " + defaultLoan.getDueDate().toLocalDate().format(DateTimeFormatter.ofPattern("dd-MM-yyyy")) + " has been exceeded. Please note " + ProfitMandiConstants.NEW_DELAYED_INTEREST_RATE + " shall be levied on daily basis." + " Your total pending Loan amount is Rs." + FormattingUtils.formatDecimal(Math.abs(amount)) + ". !!Pay Now!!";
            notificationService.sendNotification(defaultLoan.getFofoId(), title, MessageType.notification, title, message, url);

            // notificationService.sendWhatsappMessage(message, title,
            // address.getPhoneNumber());

        }
    }

    private String getMessageForDueDateExtend(List<Loan> loans) throws ProfitMandiBusinessException {
        Map<Integer, com.spice.profitmandi.dao.model.FofoReportingModel> fofoReportingModelMap = csService.getPartnerIdSalesHeaders();
        Map<Integer, FofoRBMReportingModel> fofoRbmReportingModelMap = csService.getPartnerIdRBMHeaders();
        List<AST> asts = astRepository.selectAll();
        Map<Integer, AST> astMap = asts.stream()
                .collect(Collectors.toMap(AST::getId, ast -> ast));
        StringBuilder sb = new StringBuilder();
        sb.append(
                "<html><body><p>Alert</p><p>Default Partners :-</p>" + "<br/><p>EveryDay Rs.100 charged as Penalty</p>" + "<br/>" +
                        "<table style='border:1px solid black ;padding: 5px';>");
        sb.append("<tbody>\n" +
                "<tr>\n" +
                "<th style='border:1px solid black;padding: 5px'>PartnerName</th>\n" +
                "<th style='border:1px solid black;padding: 5px'>State Name</th>\n" +
                "<th style='border:1px solid black;padding: 5px'>Area</th>\n" +
                "<th style='border:1px solid black;padding: 5px'>Territory</th>\n" +
                "<th style='border:1px solid black;padding: 5px'>due date</th>\n" +
                "<th style='border:1px solid black;padding: 5px'>Days</th>\n" +
                "<th style='border:1px solid black;padding: 5px'>Pending Amount</th>\n" +
                "<th style='border:1px solid black;padding: 5px'>RBM L2</th>\n" +
                "<th style='border:1px solid black;padding: 5px'>RBM L1</th>\n" +
                "<th style='border:1px solid black;padding: 5px'>Sales L1</th>\n" +
                "<th style='border:1px solid black;padding: 5px'>Sales L2</th>\n" +
                "</tr>");
        for (Loan entry : loans) {

            List<LoanStatement> loanStatements = loanStatementRepository.selectByLoanId(entry.getId());

            double amount = loanStatements.stream().map(x -> x.getAmount()).collect(Collectors.summingDouble(x -> x.doubleValue()));

            long noOfdaysBetween = ChronoUnit.DAYS.between(entry.getDueDate().toLocalDate(), LocalDateTime.now());

            CustomRetailer customRetailer = retailerService.getFofoRetailer(entry.getFofoId());

            sb.append("<tr>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + customRetailer.getBusinessName() + "(" + customRetailer.getCode() + ")" + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + customRetailer.getAddress().getState() + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + astMap.get(customRetailer.getAstId()).getArea() + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + astMap.get(customRetailer.getAstId()).getTerritory() + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + entry.getDueDate().format(DateTimeFormatter.ofPattern("dd/MM/yyyy")) + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + noOfdaysBetween + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + new DecimalFormat("#.##").format(Math.abs(amount)) + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + fofoRbmReportingModelMap.get(entry.getFofoId()).getL1Manager() + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + fofoRbmReportingModelMap.get(entry.getFofoId()).getL2Manager() + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + fofoReportingModelMap.get(entry.getFofoId()).getTerritoryManager() + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + fofoReportingModelMap.get(entry.getFofoId()).getRegionalManager() + "</td>");


            sb.append("</tr>");

        }

        sb.append("</tbody></table></body></html>");

        return sb.toString();
    }

    private String getMessageForDueDateCross(List<Loan> dueDateCrossLoans) throws ProfitMandiBusinessException {
        Map<Integer, com.spice.profitmandi.dao.model.FofoReportingModel> fofoReportingModelMap = csService.getPartnerIdSalesHeaders();
        Map<Integer, FofoRBMReportingModel> fofoRbmReportingModelMap = csService.getPartnerIdRBMHeaders();
        List<AST> asts = astRepository.selectAll();
        Map<Integer, AST> astMap = asts.stream()
                .collect(Collectors.toMap(AST::getId, ast -> ast));

        StringBuilder sb = new StringBuilder();
        sb.append("<html><body><p>Alert</p><p>Partner Credit Report :-</p>" + "<br/><p>Additional penal interest of  shall be levied on daily basis.</p>" + "<br/>" +
                "<table style='border:1px solid black ;padding: 5px';>");
        sb.append("<tbody>\n" + "<tr>\n" +
                "<th style='border:1px solid black;padding: 5px'>PartnerName</th>\n" +
                "<th style='border:1px solid black;padding: 5px'>State Name</th>\n" +
                "<th style='border:1px solid black;padding: 5px'>Area</th>\n" +
                "<th style='border:1px solid black;padding: 5px'>Territory</th>\n" +
                "<th style='border:1px solid black;padding: 5px'>due date</th>\n" +
                "<th style='border:1px solid black;padding: 5px'>Days</th>\n" +
                "<th style='border:1px solid black;padding: 5px'>Pending Amount</th>\n" +
                "<th style='border:1px solid black;padding: 5px'>RBM L2</th>\n" +
                "<th style='border:1px solid black;padding: 5px'>RBM L1</th>\n" +
                "<th style='border:1px solid black;padding: 5px'>Sales L1</th>\n" +
                "<th style='border:1px solid black;padding: 5px'>Sales L2</th>\n" +
                "<th style='border:1px solid black;padding: 5px'>Limit Assigned</th>\n" +
                "</tr>");

        for (Loan entry : dueDateCrossLoans) {
            DecimalFormat df = new DecimalFormat("#.##");

            List<LoanStatement> loanStatements = loanStatementRepository.selectByLoanId(entry.getId());

            double amount = loanStatements.stream().map(x -> x.getAmount()).collect(Collectors.summingDouble(x -> x.doubleValue()));

            long noOfdaysBetween = ChronoUnit.DAYS.between(entry.getDueDate().toLocalDate(), LocalDateTime.now());

            CustomRetailer customRetailer = retailerService.getFofoRetailer(entry.getFofoId());

            SDCreditRequirement sdCreditRequirement = sdCreditRequirementRepository.selectByFofoId(customRetailer.getPartnerId());

            sb.append("<tr>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + customRetailer.getBusinessName() + "(" + customRetailer.getCode() + ")" + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + customRetailer.getAddress().getState() + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + astMap.get(customRetailer.getAstId()).getArea() + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + astMap.get(customRetailer.getAstId()).getTerritory() + "</td>");

            sb.append("<td style='border:1px solid black;padding: 5px'>" + entry.getDueDate().format(DateTimeFormatter.ofPattern("dd/MM/yyyy")) + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + noOfdaysBetween + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + new DecimalFormat("#.##").format(Math.abs(amount)) + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + fofoRbmReportingModelMap.get(entry.getFofoId()).getL1Manager() + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + fofoRbmReportingModelMap.get(entry.getFofoId()).getL2Manager() + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + fofoReportingModelMap.get(entry.getFofoId()).getTerritoryManager() + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + fofoReportingModelMap.get(entry.getFofoId()).getRegionalManager() + "</td>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + df.format(sdCreditRequirement.getLimit()) + "</td>");
            sb.append("</tr>");
        }

        sb.append("</tbody></table></body></html>");

        return sb.toString();
    }

    public void notifyLoanDueDateCross() throws Exception {

        List<Loan> dueDateCrossLoans = sdCreditService.getDueDateCrossLoan();
        List<Loan> allActiveLoans = sdCreditService.getAllActiveLoan();
        if (!dueDateCrossLoans.isEmpty()) {
            this.sendDueDateCrossLoanAlert(dueDateCrossLoans, allActiveLoans);
        }

    }

    private void sendDueDateCrossLoanAlert(List<Loan> dueDateCrossLoans, List<Loan> allActiveLoans) throws Exception {
        List<Integer> categoryIds = Arrays.asList(ProfitMandiConstants.TICKET_CATEGORY_CATEGORY, ProfitMandiConstants.TICKET_CATEGORY_RBM, ProfitMandiConstants.TICKET_CATEGORY_SALES, ProfitMandiConstants.TICKET_CATEGORY_ABM, ProfitMandiConstants.TICKET_CATEGORY_ACCOUNTS, ProfitMandiConstants.TICKET_CATEGORY_BUSINESSINTELLIGENT);

        for (Entry<String, Set<Integer>> storeGuyEntry : csService.getAuthUserPartnerIdMappingByCategoryIds(categoryIds, false).entrySet()) {
            List<Loan> filteredRows = new ArrayList<>();
            for (Loan loan : dueDateCrossLoans) {

                if (storeGuyEntry.getValue().contains(loan.getFofoId())) {

                    filteredRows.add(loan);
                }
            }

            List<Loan> filteredAllActiveLoans = new ArrayList<>();
            for (Loan loan : allActiveLoans) {

                if (storeGuyEntry.getValue().contains(loan.getFofoId())) {

                    filteredAllActiveLoans.add(loan);
                }
            }

            ByteArrayOutputStream byteArrayOutputStream = null;
            Utils.Attachment attachment1 = null;
            if (!filteredAllActiveLoans.isEmpty()) {
                byteArrayOutputStream = this.generateLoanCsv(filteredAllActiveLoans);
                attachment1 = new Utils.Attachment("partnner-credit.csv", new ByteArrayResource(byteArrayOutputStream.toByteArray()));

            }


            if (!filteredRows.isEmpty()) {
                String messageText = this.getMessageForDueDateCross(filteredRows);
                String[] email = new String[]{storeGuyEntry.getKey()};

                if (attachment1 != null) {
                    Utils.sendMailWithAttachments(googleMailSender, email, null, null, "Partner Credit Report", messageText, true, attachment1);
                } else {
                    this.sendMailOfHtmlFormat(googleMailSender, email, messageText, null, "Due Date Cross");

                }

            }

        }

        String messageText = this.getMessageForDueDateCross(dueDateCrossLoans);
        ByteArrayOutputStream byteArrayOutputStream = this.generateLoanCsv(allActiveLoans);
        Utils.Attachment attachment = null;
        if (byteArrayOutputStream != null) {
            attachment = new Utils.Attachment("partnner-credit.csv", new ByteArrayResource(byteArrayOutputStream.toByteArray()));

        }

        if (attachment != null) {
            Utils.sendMailWithAttachments(googleMailSender, new String[]{"sdtech@smartdukaan.com"}, null, null, "Partner Credit Report", messageText, true, attachment);
        } else {
            this.sendMailOfHtmlFormat(googleMailSender, new String[]{"sdtech@smartdukaan.com"}, messageText, null, "Due Date Cross");
        }


        if (!dueDateCrossLoans.isEmpty()) {
            for (Loan loan : dueDateCrossLoans) {

                long noOfdaysBetween = ChronoUnit.DAYS.between(loan.getDueDate().toLocalDate(), LocalDate.now());

                com.spice.profitmandi.dao.entity.user.User user = userUserRepository.selectById(loan.getFofoId());

                Address address = addressRepository.selectById(user.getAddressId());

                LOGGER.info("noOfdaysBetween {} ", noOfdaysBetween);

                List<LoanStatement> loanStatements = loanStatementRepository.selectByLoanId(loan.getId());

                double amount = loanStatements.stream().map(x -> x.getAmount()).collect(Collectors.summingDouble(x -> x.doubleValue()));

                String url = "http://app.smartdukaan.com/pages/home/credit";

                String title = "Alert!";
                String message = "Your Outstanding Credit Payment due date " + loan.getDueDate().toLocalDate().format(DateTimeFormatter.ofPattern("dd-MM-yyyy")) + " has been exceeded. Additional penal interest of " + loan.getInterestRate().setScale(2, RoundingMode.HALF_UP) + "% shall be levied on daily basis. Your total pending loan amount is Rs." + FormattingUtils.formatDecimal(Math.abs(amount)) + ". !!Pay Now!!";

                notificationService.sendNotification(loan.getFofoId(), title, MessageType.notification, title, message, url);

                //notificationService.sendWhatsappMessage(message, title, address.getPhoneNumber());

            }
        }

    }

    private ByteArrayOutputStream generateLoanCsv(List<Loan> loans) throws ProfitMandiBusinessException {
        org.apache.commons.io.output.ByteArrayOutputStream byteArrayOutputStream = null;


        if (!loans.isEmpty()) {
            Map<Integer, com.spice.profitmandi.dao.model.FofoReportingModel> fofoReportingModelMap = csService.getPartnerIdSalesHeaders();
            Map<Integer, FofoRBMReportingModel> fofoRbmReportingModelMap = csService.getPartnerIdRBMHeaders();
            List<AST> asts = astRepository.selectAll();
            Map<Integer, AST> astMap = asts.stream()
                    .collect(Collectors.toMap(AST::getId, ast -> ast));
            try {
                List<String> headers = Arrays.asList(
                        "PartnerName", "State Name", "Area", "Territory",
                        "Due Date", "Days", "Pending Amount",
                        "RBM L2", "RBM L1", "Sales L1", "Sales L2", "Limit Assigned"
                );

                List<List<?>> rows = new ArrayList<>();

                for (Loan entry : loans) {

                    List<LoanStatement> loanStatements = loanStatementRepository.selectByLoanId(entry.getId());
                    double amount = loanStatements.stream().map(x -> x.getAmount()).collect(Collectors.summingDouble(x -> x.doubleValue()));

//                    long noOfdaysBetween = ChronoUnit.DAYS.between(entry.getDueDate().toLocalDate(), LocalDateTime.now());
                    long noOfdaysBetween = ChronoUnit.DAYS.between(entry.getCreatedOn().toLocalDate(), LocalDateTime.now());

                    CustomRetailer customRetailer = retailerService.getFofoRetailer(entry.getFofoId());
                    SDCreditRequirement sdCreditRequirement = sdCreditRequirementRepository.selectByFofoId(customRetailer.getPartnerId());
                    DecimalFormat df = new DecimalFormat("#.##");
                    List<String> row = Arrays.asList(
                            customRetailer.getBusinessName() + "(" + customRetailer.getCode() + ")",
                            customRetailer.getAddress().getState(),
                            astMap.get(customRetailer.getAstId()).getArea(),
                            astMap.get(customRetailer.getAstId()).getTerritory(),
                            entry.getDueDate().format(DateTimeFormatter.ofPattern("dd/MM/yyyy")),
                            String.valueOf(noOfdaysBetween),
                            df.format(Math.abs(amount)),
                            fofoRbmReportingModelMap.get(entry.getFofoId()).getL1Manager(),
                            fofoRbmReportingModelMap.get(entry.getFofoId()).getL2Manager(),
                            fofoReportingModelMap.get(entry.getFofoId()).getTerritoryManager(),
                            fofoReportingModelMap.get(entry.getFofoId()).getRegionalManager(),
                            df.format(sdCreditRequirement.getLimit())
                    );

                    rows.add(row);
                }

                byteArrayOutputStream = FileUtil.getCSVByteStream(headers, rows);


            } catch (Exception e) {
                throw new RuntimeException(e);
            }

        }
        return byteArrayOutputStream;
    }


    public void alertForDueDate() throws Exception {

        List<Loan> loans = loanRepository.selectAllActiveLoan();

        if (!loans.isEmpty()) {
            for (Loan loan : loans) {

                com.spice.profitmandi.dao.entity.user.User user = userUserRepository.selectById(loan.getFofoId());

                Address address = addressRepository.selectById(user.getAddressId());

                long noOfdaysBetween = ChronoUnit.DAYS.between(LocalDate.now(), loan.getDueDate().toLocalDate());

                LOGGER.info("noOfdaysBetween {} ", noOfdaysBetween);

                if (noOfdaysBetween <= 4 && noOfdaysBetween >= 0) {
                    List<LoanStatement> loanStatements = loanStatementRepository.selectByLoanId(loan.getId());
                    double amount = loanStatements.stream().map(x -> x.getAmount()).collect(Collectors.summingDouble(x -> x.doubleValue()));

                    String title = "Alert!";
                    String url = "http://app.smartdukaan.com/pages/home/credit";
                    String message = null;
                    if (noOfdaysBetween == 0) {

                        message = "Your total outstanding Credit amount is Rs." + FormattingUtils.formatDecimal(Math.abs(amount)) + " is due for Today, Pay Now!!";

                    } else {

                        message = "Your total outstanding Credit amount is Rs." + FormattingUtils.formatDecimal(Math.abs(amount)) + " is due by " + loan.getDueDate().toLocalDate().format(DateTimeFormatter.ofPattern("dd-MM-yyyy")) + " , Pay Now!!";

                    }
                    notificationService.sendNotification(loan.getFofoId(), title, MessageType.notification, title, message, url);
                    //notificationService.sendWhatsappMessage(message, title, address.getPhoneNumber());

                }
            }
        }

    }

    public void userMobileNumberOptIn() throws Exception {

        List<FofoStore> fofoStores = fofoStoreRepository.selectActiveStores();

        List<com.spice.profitmandi.dao.entity.user.User> users = userUserRepository.selectByIds(fofoStores.stream().map(x -> x.getId()).collect(Collectors.toList()));

        List<Address> addresses = addressRepository.selectByIds(users.stream().map(x -> x.getAddressId()).collect(Collectors.toList()));

        LOGGER.info("addresses" + addresses);

        for (Address address : addresses) {
            notificationService.optIn(address.getPhoneNumber());

        }

    }

    public void authUserMobileNumberOptIn() throws HttpHostConnectException, ProfitMandiBusinessException {

        List<AuthUser> authUsers = authRepository.selectAllActiveUser();
        LOGGER.info("authUsers" + authUsers);

        for (AuthUser authUser : authUsers) {
            Map<String, String> requestheaders = new HashMap<>();
            requestheaders.put("Content-Type", "application/x-www-form-urlencoded");
            Map<String, String> requestParams = new HashMap<>();
            requestParams.put("userid", String.valueOf(2000215976));
            requestParams.put("password", "MFRd!BBL");
            requestParams.put("phone_number", authUser.getMobileNumber());
            requestParams.put("auth_scheme", "plain");
            requestParams.put("v", "1.1");
            requestParams.put("format", "json");

            requestParams.put("method", "OPT_IN");

            requestParams.put("channel", "WHATSAPP");
            String response = restClient.get("https://media.smsgupshup.com/GatewayAPI/rest", requestParams, requestheaders);
            LOGGER.info("response" + response);
        }

    }

    @Autowired
    private HygieneDataRepository hygieneDataRepository;

    public void hygineAlertForPartner() throws ProfitMandiBusinessException, MessagingException, IOException {

        List<String> remarks = new ArrayList<>();
        remarks.add("Out of Service");
        remarks.add("Duplicate number");
        remarks.add("Partner number");
        List<HygieneData> hygieneData = hygieneDataRepository.selectAllByDisposedDateAndRemark(LocalDate.now(), remarks);

        if (!hygieneData.isEmpty()) {

            Map<Integer, List<HygieneData>> partnerHygieneDataMap = hygieneData.stream().collect(groupingBy(x -> x.getFofoId()));

            for (Entry<Integer, List<HygieneData>> partnerHygieneDataMapEntry : partnerHygieneDataMap.entrySet()) {

                CustomRetailer customRetailer = retailerService.getFofoRetailer(partnerHygieneDataMapEntry.getKey());

                StringBuilder sb = new StringBuilder();
                sb.append("<html><body><p>Dear Partner," + "</p><br/><p>It has been observed in our calls to the respective customers that the contact no is either not correct or does not have incoming calls.\n"

                        + " </p>" + "<p>We would like to inform you that this will hinder all our marketing initiatives to bring this customer back to our smartdukaan or cross sell any product from your inventory." + "</p><p>Kindly ensure going forward that the details are correct so to avoid 1% margin loss.\n" + "</p><p>In case the data is found to be incorrect there is a loss of 1% margin on this sales under SmartDukaan hygiene guidelines.\n" + "                            " + "</p>"

                        + "<br/><table style='border:1px solid black ;padding: 5px';>");
                sb.append("<tbody>\n" + "       " + "                                   " + "<tr>\n");
                sb.append("<th style='border:1px solid black;padding: 5px'>Invoice Number</th>\n");
                sb.append("<th style='border:1px solid black;padding: 5px'>Customer Name</th>\n");
                sb.append("<th style='border:1px solid black;padding: 5px'>Customer Number</th>\n");
                sb.append("</tr>\n");

                List<HygieneData> hygienes = partnerHygieneDataMapEntry.getValue();

                List<Integer> orderIds = hygienes.stream().map(x -> x.getOrderId()).collect(Collectors.toList());

                List<FofoOrder> fofoOrders = fofoOrderRepository.selectAllByOrderIds(orderIds);

                for (FofoOrder fofoOrder : fofoOrders) {
                    Customer customer = customerRepository.selectById(fofoOrder.getCustomerId());
                    sb.append("<tr>");
                    sb.append("<td style='border:1px solid black;padding: 5px'>" + fofoOrder.getInvoiceNumber() + "</td>\n");
                    sb.append("<td style='border:1px solid black;padding: 5px'>" + customer.getFirstName() + "" + customer.getLastName() + "</td>\n");
                    sb.append("<td style='border:1px solid black;padding: 5px'>" + customer.getMobileNumber() + "</td>\n");

                    sb.append("</tr>\n");
                }

                sb.append("</tbody></table></body></html>");
                String subject = "Customer Info - Hygiene Alert";

                this.sendMailOfHtmlFormat(mailSender, new String[]{customRetailer.getEmail()}, sb.toString(), null, subject);

            }

        }

    }

    //
    public void setInventoryItemIdToPriceDropImei() {
        List<PriceDropIMEI> priceDropIMEIList = priceDropIMEIRepository.selectAllMissingInventoryItemIds();
        LOGGER.info("Problems with - {} entries", priceDropIMEIList.size());
        //priceDropIMEIList.stream().collect(C)
        for (PriceDropIMEI priceDropIMEI : priceDropIMEIList) {
            System.out.println(priceDropIMEI.getImei());
            List<InventoryItem> inventoryItems = inventoryItemRepository.selectAllBySerialNumberFofoId(priceDropIMEI.getImei(), priceDropIMEI.getPartnerId());
            if (inventoryItems.size() == 0) {
                LOGGER.info("Could not find Grn - FofoID {} , Imei {} ", priceDropIMEI);
            } else if (inventoryItems.size() > 1) {
                LOGGER.info("Found 2 such instance for - FofoID {} , Imei {} ", priceDropIMEI.getImei(), priceDropIMEI.getPartnerId());
            } else {
                priceDropIMEI.setInventoryItemId(inventoryItems.get(0).getId());
            }

        }
    }

    @Autowired
    SaholicReservationSnapshotRepository saholicReservationSnapshotRepository;

    public void removePartialBillingForTransaction(int transactionId) throws ProfitMandiBusinessException {
        // Fetch with pessimistic lock to prevent concurrent modifications
        List<Order> orderList = orderRepository.selectAllByTransactionIdForUpdate(transactionId);
        Map<Integer, Order> orderMap = orderList.stream().collect(Collectors.toMap(x -> x.getId(), x -> x));
        //Delete scanRecords
        List<WarehouseScan> warehouseScans = warehouseScanRepository.selectAllByOrderIds(new ArrayList<>(orderMap.keySet()));
        for (WarehouseScan warehouseScan : warehouseScans) {
            warehouseScanRepository.delete(warehouseScan);
            WarehouseInventoryItem warehouseInventoryItem = warehouseInventoryItemRepository.selectById(warehouseScan.getInventoryItemId());
            warehouseInventoryItem.setLastScanType(in.shop2020.warehouse.ScanType.PURCHASE);
            warehouseInventoryItem.setCurrentQuantity(warehouseInventoryItem.getCurrentQuantity() + warehouseScan.getQuantity());
        }

        for (Order order : orderList) {
            if (order.getBillingTimestamp() != null) {
                order.setStatus(OrderStatus.ACCEPTED);
                order.setStatusDescription("Submitted for Processing");
                order.setBillingTimestamp(null);
                order.setBilledBy(null);
                order.setSerialNumber(null);
                order.setJacketNumber(null);
                order.getLineItem().setSerialNumber(null);
                List<LineItemImei> lineItemImeis = lineItemImeisRepository.selectByLineItemId(order.getLineItem().getId());
                for (LineItemImei lineItemImei : lineItemImeis) {
                    lineItemImeisRepository.delete(lineItemImei);
                }
                SaholicReservationSnapshot saholicReservationSnapshot = saholicReservationSnapshotRepository
                        .selectByOrderId(order.getId());
                if (saholicReservationSnapshot == null) {
                    saholicInventoryService.addReservationCount(order.getLineItem().getItemId(),
                            order.getFulfilmentWarehouseId(), order.getLineItem().getQuantity(), order.getId());
                }
            }
        }
    }

    public void hygineAlertForInternalTeam() throws ProfitMandiBusinessException, MessagingException, IOException {

        List<String> remarks = new ArrayList<>();
        remarks.add("Out of Service");
        remarks.add("Duplicate number");
        remarks.add("Partner number");
        List<HygieneData> hygieneDataLastThreeMonth = hygieneDataRepository.selectDisposedDateAndRemarks(
                LocalDate.now().withDayOfMonth(1).minusMonths(3).atStartOfDay(), LocalDate.now().minusMonths(1).withDayOfMonth(LocalDate.now().minusMonths(1).lengthOfMonth()).atTime(LocalTime.MAX), remarks);

        List<HygieneData> hygieneDataRecentMonth = hygieneDataRepository.selectDisposedDateAndRemarks(LocalDate.now().withDayOfMonth(1), remarks);
        Map<String, Set<Integer>> storeGuyMap = csService.getAuthUserPartnerIdMapping();
        Map<Integer, List<HygieneData>> partnerHygieneLastThreeMonthMap = null;

        Map<Integer, List<HygieneData>> partnerHygieneDataRecentMonth = null;

        if (!hygieneDataLastThreeMonth.isEmpty()) {
            partnerHygieneLastThreeMonthMap = hygieneDataLastThreeMonth.stream().collect(groupingBy(x -> x.getFofoId()));

        }

        if (!hygieneDataRecentMonth.isEmpty()) {
            partnerHygieneDataRecentMonth = hygieneDataRecentMonth.stream().collect(groupingBy(x -> x.getFofoId()));
        }

        for (Entry<String, Set<Integer>> storeGuyEntry : storeGuyMap.entrySet()) {

            String email = storeGuyEntry.getKey();

            List<Integer> fofoIds = new ArrayList<>(storeGuyEntry.getValue());

            Map<Integer, Map<YearMonth, List<HygieneData>>> partnerYearMonthData = new HashMap<>();
            Map<Integer, Map<LocalDate, List<HygieneData>>> partnerDateWiseData = new HashMap<>();
            for (Integer fofoId : fofoIds) {

                partnerYearMonthData.put(fofoId, null);
                partnerDateWiseData.put(fofoId, null);
                if (!partnerHygieneLastThreeMonthMap.isEmpty()) {
                    List<HygieneData> hygienes = partnerHygieneLastThreeMonthMap.get(fofoId);

                    if (hygienes != null) {
                        Map<YearMonth, List<HygieneData>> yearMonthData = hygienes.stream().collect(groupingBy(x -> YearMonth.from(x.getDisposedTimestamp())));

                        partnerYearMonthData.put(fofoId, yearMonthData);

                    }
                    List<HygieneData> dateWiseHygienes = partnerHygieneDataRecentMonth.get(fofoId);

                    if (dateWiseHygienes != null) {

                        Map<LocalDate, List<HygieneData>> dateWiseData = hygienes.stream().collect(groupingBy(x -> (x.getDisposedTimestamp()).toLocalDate()));

                        partnerDateWiseData.put(fofoId, dateWiseData);

                    }

                }

            }

            String subject = "Customer Info - Hygiene Alert";

            LOGGER.info("hygieneData {}", partnerDateWiseData.values());

            boolean partnerDateWise = partnerDateWiseData.values().stream().allMatch(Objects::isNull);

            boolean partnerYearMonth = partnerYearMonthData.values().stream().allMatch(Objects::isNull);

            if (!partnerDateWise && !partnerYearMonth) {
                String sb = this.getMessageHygieneAlertForPartner(partnerYearMonthData, partnerDateWiseData);
                this.sendMailOfHtmlFormat(googleMailSender, new String[]{email}, sb, null, subject);
            }

        }

    }

    private String getMessageHygieneAlertForPartner(
            Map<Integer, Map<YearMonth, List<HygieneData>>> partnerYearMonthData, Map<Integer, Map<LocalDate, List<HygieneData>>> partnerDateWiseData)
            throws ProfitMandiBusinessException {

        Map<Integer, CustomRetailer> customeRetailerMap = retailerService.getAllFofoRetailers();

        LocalDateTime startDate = LocalDate.now().withDayOfMonth(1).atStartOfDay();
        LocalDateTime endDate = LocalDateTime.now();

        LocalDateTime startYearMonth = LocalDate.now().withDayOfMonth(1).minusMonths(3).atStartOfDay();
        LocalDateTime endYearMonth = LocalDate.now().minusMonths(1).withDayOfMonth(LocalDate.now().minusMonths(1).lengthOfMonth()).atTime(LocalTime.MAX);

        DateTimeFormatter dateYearMonthFormatter = DateTimeFormatter.ofPattern("MMM''uu");

        List<YearMonth> yearMonthRange = new ArrayList<>();
        yearMonthRange.add(YearMonth.from(startYearMonth));

        yearMonthRange.add(YearMonth.from(startYearMonth.plusMonths(1)));

        yearMonthRange.add(YearMonth.from(endYearMonth));

        long noOfDaysBetween = ChronoUnit.DAYS.between(startDate, endDate.plusDays(1));

        List<LocalDate> dateRange = Stream.iterate(startDate.toLocalDate(), date -> date.plusDays(1)).limit(noOfDaysBetween).collect(Collectors.toList());

        StringBuilder sb = new StringBuilder();
        sb.append("<html><body><p>Hi," + "</p><br/><p>Kindly advise below mentioned partner to input correct details in the system else this will hinder all our marketing initiatives to bring this customer back to our smartdukaan or cross sell any product from partner's inventory.\n" + " </p>" + "<p>Kindly ensure going forward that the details are correct so to avoid 1% margin loss.\n" + "</p><p>In case the data is found to be incorrect there is a loss of 1% margin on this sales under SmartDukaan hygiene guidelines.\n</p>"

                + "<table style='border:1px solid black ;padding: 5px';>");
        sb.append("<tbody>\n" + "       " + "                                   " + "<tr>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Partner Name</th>\n");

        for (YearMonth yearMonth : yearMonthRange) {
            sb.append("<th style='border:1px solid black;padding: 5px'>" + yearMonth.format(dateYearMonthFormatter) + "</th>\n");
        }

        for (LocalDate localDate : dateRange) {
            sb.append("<th style='border:1px solid black;padding: 5px'>" + localDate + "</th>\n");
        }

        sb.append("</tr>\n");

        for (Entry<Integer, Map<YearMonth, List<HygieneData>>> partnerYearMonthEntry : partnerYearMonthData.entrySet()) {

            int fofoId = partnerYearMonthEntry.getKey();

            Map<YearMonth, List<HygieneData>> yearMonthData = partnerYearMonthEntry.getValue();

            Map<LocalDate, List<HygieneData>> dateWiseData = partnerDateWiseData.get(fofoId);

            if (yearMonthData == null && dateWiseData == null) {
                continue;
            }

            sb.append("<tr>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + customeRetailerMap.get(fofoId).getBusinessName() + "</td>\n");
            for (YearMonth yearMonth : yearMonthRange) {

                if (yearMonthData == null) {
                    sb.append("<td style='border:1px solid black;padding: 5px'>-</td>\n");

                } else {
                    List<HygieneData> hygieneList = yearMonthData.get(yearMonth);

                    if (hygieneList != null) {
                        sb.append("<td style='border:1px solid black;padding: 5px'>" + hygieneList.size() + "</td>\n");

                    } else {
                        sb.append("<td style='border:1px solid black;padding: 5px'>-</td>\n");

                    }
                }
            }

            for (LocalDate localDate : dateRange) {

                if (dateWiseData == null) {
                    sb.append("<td style='border:1px solid black;padding: 5px'>-</td>\n");

                } else {
                    List<HygieneData> hygieneList = dateWiseData.get(localDate);

                    if (hygieneList != null) {
                        sb.append("<td style='border:1px solid black;padding: 5px'>" + hygieneList.size() + "</td>\n");

                    } else {
                        sb.append("<td style='border:1px solid black;padding: 5px'>-</td>\n");

                    }
                }
            }
            sb.append("</tr>");

        }

        sb.append("</tbody></table></body></html>");

        return sb.toString();
    }

    @Autowired
    private MonthlyTargetRepository monthlyTargetRepository;

    public void monthlyTargetForPartner() throws MessagingException, ProfitMandiBusinessException, IOException {

        LocalDateTime curDate = LocalDateTime.now();
        List<MonthlyTarget> monthlyTargets = monthlyTargetRepository.selectByDate(YearMonth.now());

        Map<Integer, MonthlyTarget> partnersMonthlyTarget = monthlyTargets.stream().collect(Collectors.toMap(x -> x.getFofoId(), x -> x));

        Map<Integer, Double> secondaryMtd = orderRepository.selectBillingDatesBetweenSumGroupByRetailerId(curDate.withDayOfMonth(1), curDate.with(LocalTime.MAX));
        Map<Integer, Double> todaytertiary = fofoOrderItemRepository.selectSumMopGroupByRetailer(curDate, curDate.with(LocalTime.MAX), 0, false);

        String subject = "Monthly Target Vs Achievement";

        for (Entry<Integer, MonthlyTarget> partnerMonthlyTargetEntry : partnersMonthlyTarget.entrySet()) {

            int fofoId = partnerMonthlyTargetEntry.getKey();

            CustomRetailer customRetailer = retailerService.getFofoRetailer(fofoId);

            Double purchaseTarget = partnerMonthlyTargetEntry.getValue().getPurchaseTarget();

            Double saleTarget = partnerMonthlyTargetEntry.getValue().getSaleTarget();

            if (purchaseTarget > 0) {
                Double purchase = secondaryMtd.get(fofoId);

                if (purchase == null) {
                    purchase = 0.0;
                }

                Double balanceTarget = purchaseTarget - purchase;

                if (balanceTarget <= 0) {
                    balanceTarget = 0.0;
                }

                StringBuilder sb = new StringBuilder();
                sb.append("<html><body><p>Dear Partner,\n");
                sb.append("<p>Pls note your agreed monthly target is Rs." + purchaseTarget + " and your achievement till date is Rs." + purchase + "</p>");
                sb.append("<p>Pls note that your balance target is Rs." + balanceTarget + "</p>\n");
                sb.append(
                        "<p>We wish you all the best and we are confident that this focused approach towards our outlet sales will help us take our outlet to the best SmartDukaan in your city.\n" + "</p>\n");

                this.sendMailOfHtmlFormat(mailSender, new String[]{customRetailer.getEmail()}, sb.toString(), null, subject);
            }

        }

    }


    public void findAllLiveDemoBrandItemsByBillingDate() throws
            MessagingException, ProfitMandiBusinessException, IOException {
        LocalDateTime endDate = LocalDate.now().atStartOfDay().minusDays(8);

        List<LiveDemoOrderLineItemModel> liveDemoOrderLineItemModels = orderRepository.findAllLiveDemoBrandItemsByBillingDate(endDate.minusDays(60), endDate);
        LOGGER.info("liveDemoOrderLineItemModels {}", liveDemoOrderLineItemModels);
        String subject = "Billed Live Demo Model";
        StringBuilder sb = new StringBuilder();

        sb.append("<html><body><p>Hi,\n");
        sb.append("<p>Please check live demo billed order :</p>\n" + " <table style='border:1px solid black ;padding: 5px; border-collapse: collapse;'>");

        sb.append("<th style='border:1px solid black;padding: 5px'>Item Id</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Invoice Number</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Brand</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Model Number</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Model Name</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Serial Number</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Retailer Name</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Retailer City</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Retailer Pincode</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Retailer State</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Billing Date</th>\n");
        sb.append("</tr>\n");
        for (LiveDemoOrderLineItemModel liveDemoModel : liveDemoOrderLineItemModels) {
            sb.append("<tr>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + liveDemoModel.getItemId() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + liveDemoModel.getInvoiceNumber() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + liveDemoModel.getBrand() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + liveDemoModel.getModelNumber() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + liveDemoModel.getModelName() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + liveDemoModel.getSerialNumber() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + liveDemoModel.getRetailerName() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + liveDemoModel.getRetailerCity() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + liveDemoModel.getRetailerPincode() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + liveDemoModel.getState() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + liveDemoModel.getFormattedBillingDate() + "</td>\n");

            sb.append("</tr>\n");

        }
        List<String> sendTo = Arrays.asList("kamini.sharma@smartdukaan.com", "niranjan.kala@smartdukaan.com",
                "mohit.gulati@smartdukaan.com", "vinay.p@smartdukaan.com", "shivam.gupta@smartdukaan.com");


        String[] email = sendTo.toArray(new String[sendTo.size()]);
        this.sendMailOfHtmlFormat(mailSender, email, sb.toString(), null, subject);

    }

    public void findTodayPocoBillingItems() throws MessagingException, ProfitMandiBusinessException, IOException {
        LocalDateTime endDate = LocalDate.now().atStartOfDay().plusDays(1);
        LocalDateTime startDate = LocalDate.now().atStartOfDay().minusDays(1);

        List<LiveDemoOrderLineItemModel> pocoBillingOrderLineItemModels = orderRepository.findTodayPocoBillingItems(startDate, endDate);
        LOGGER.info("pocoBillingOrderLineItemModels {}", pocoBillingOrderLineItemModels);
        String subject = "Poco Billing";
        StringBuilder sb = new StringBuilder();

        sb.append("<html><body><p>Hi,\n");
        sb.append("<p>Please check poco billed order :</p>\n" + " <table style='border:1px solid black ;padding: 5px; border-collapse: collapse;'>");

        sb.append("<th style='border:1px solid black;padding: 5px'>Brand</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Model Number</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Model Name</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Serial Number</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Retailer State</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Billing Date</th>\n");
        sb.append("</tr>\n");
        for (LiveDemoOrderLineItemModel pocoModel : pocoBillingOrderLineItemModels) {
            sb.append("<tr>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + pocoModel.getBrand() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + pocoModel.getModelNumber() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + pocoModel.getModelName() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + pocoModel.getSerialNumber() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + pocoModel.getState() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + pocoModel.getFormattedBillingDate() + "</td>\n");

            sb.append("</tr>\n");

        }
        List<String> sendTo = Arrays.asList("uday.singh@smartdukaan.com", "kuldeep.kumar@smartdukaan.com", "nishant.ohri@smartdukaan.com", "ritesh.chauhan1@smartdukaan.com");

        String[] email = sendTo.toArray(new String[sendTo.size()]);
        this.sendMailOfHtmlFormat(mailSender, email, sb.toString(), null, subject);

    }


    public void monthlyTargetForInternalTeam() throws MessagingException, ProfitMandiBusinessException, IOException {

        LocalDateTime curDate = LocalDateTime.now();

        List<Integer> categoryIds = Arrays.asList(ProfitMandiConstants.TICKET_CATEGORY_CATEGORY, ProfitMandiConstants.TICKET_CATEGORY_RBM, ProfitMandiConstants.TICKET_CATEGORY_SALES, ProfitMandiConstants.TICKET_CATEGORY_ABM, ProfitMandiConstants.TICKET_CATEGORY_MARKETING, ProfitMandiConstants.TICKET_CATEGORY_ACCOUNTS, ProfitMandiConstants.TICKET_CATEGORY_BUSINESSINTELLIGENT);

        Map<String, Set<Integer>> storeGuyMap = csService.getAuthUserPartnerIdMappingByCategoryIds(categoryIds, false);

        List<MonthlyTarget> monthlyTargets = monthlyTargetRepository.selectByDate(YearMonth.now());

        Map<Integer, CustomRetailer> customeRetailerMap = retailerService.getAllFofoRetailers();

        Map<Integer, MonthlyTarget> partnersMonthlyTarget = monthlyTargets.stream().collect(Collectors.toMap(x -> x.getFofoId(), x -> x));

        Map<Integer, Double> secondaryMtd = orderRepository.selectBillingDatesBetweenSumGroupByRetailerId(curDate.withDayOfMonth(1), curDate.with(LocalTime.MAX));
        Map<Integer, Double> todaytertiary = fofoOrderItemRepository.selectSumMopGroupByRetailer(curDate, curDate.with(LocalTime.MAX), 0, false);

        String subject = "Monthly Target Vs Achievement";

        for (Entry<String, Set<Integer>> storeGuyEntry : storeGuyMap.entrySet()) {

            String email = storeGuyEntry.getKey();

            List<Integer> fofoIds = new ArrayList<>(storeGuyEntry.getValue());
            Map<Integer, MonthlyTarget> monthlyTargetAchievement = new HashMap<>();
            for (Integer fofoId : fofoIds) {

                MonthlyTarget monthlyTarget = partnersMonthlyTarget.get(fofoId);
                if (monthlyTarget != null) {
                    Double purchaseTarget = monthlyTarget.getPurchaseTarget();

                    Double saleTarget = monthlyTarget.getSaleTarget();

                    if (purchaseTarget != null && purchaseTarget > 0) {
                        Double purchase = secondaryMtd.get(fofoId);
                        if (purchase == null) {
                            purchase = 0.0;
                        }
                        monthlyTarget.setPurchaseAchievement(purchase);
                        monthlyTargetAchievement.put(fofoId, monthlyTarget);

                    }
                }
            }

            if (!monthlyTargetAchievement.isEmpty()) {

                StringBuilder sb = new StringBuilder();

                sb.append("<html><body><p>Hi,\n");
                sb.append("<p>Pls note your Partners agreed monthly target foolowing are :</p>\n" + " <table style='border:1px solid black ;padding: 5px';>");
                sb.append("<tbody>\n" + "       " + "                                   " + "<tr>\n");
                sb.append("<th style='border:1px solid black;padding: 5px'>Partner Name</th>\n");

                sb.append("<th style='border:1px solid black;padding: 5px'>Purchase Target</th>\n");

                sb.append("<th style='border:1px solid black;padding: 5px'>Purchase Achievement</th>\n");
                sb.append("</tr>\n");

                for (Entry<Integer, MonthlyTarget> monthlyTargetAchievementEntry : monthlyTargetAchievement.entrySet()) {
                    sb.append("<tr>");
                    int fofoId = monthlyTargetAchievementEntry.getKey();

                    MonthlyTarget monthlyTarget = monthlyTargetAchievementEntry.getValue();
                    sb.append("<td style='border:1px solid black;padding: 5px'>" + customeRetailerMap.get(fofoId).getBusinessName() + "</td>\n");

                    sb.append("<td style='border:1px solid black;padding: 5px'>" + monthlyTarget.getPurchaseTarget() + "</td>\n");

                    sb.append("<td style='border:1px solid black;padding: 5px'>" + monthlyTarget.getPurchaseAchievement() + "</td>\n");

                    sb.append("</tr>\n");

                }

                this.sendMailOfHtmlFormat(googleMailSender, new String[]{email}, sb.toString(), null, subject);
                // this.sendMailOfHtmlFormat("tejbeer.kaur@smartdukaan.com", sb.toString(),
                // null, subject);

            }

        }

    }

    @Autowired
    private PerformanceService performanceService;


    public void sendFilteredRetailerPerformance() throws Exception {
        RetailerFilterModel retailerFilterModel = performanceService.getRetailerFilterModel();

        Map<Integer, CustomRetailer> customRetailers = retailerService.getFofoRetailers(true);


        List<RetailerPeformanceFilterModel> partnersPerformance = performanceService.getPerformance(retailerFilterModel, customRetailers);

        Map<Integer, RetailerPeformanceFilterModel> partnersPerformanceMap = new HashMap<>();

        if (!partnersPerformance.isEmpty()) {
            partnersPerformanceMap = partnersPerformance.stream().collect(Collectors.toMap(x -> x.getFofoId(), x -> x));
            performanceService.sendPartnerPerformanceEmail(partnersPerformanceMap, retailerFilterModel, customRetailers);

        }


    }

    @Autowired
    private VendorItemPricingRepository vendorItemPricingRepository;


    @Autowired
    private VendorCatalogPricingService vendorCatalogPricingService;


    public void migrateVendorItemPricing() throws ProfitMandiBusinessException {

        List<VendorItemPricing> vendorItemPricings = vendorItemPricingRepository.selectAll();
        LOGGER.info("At migrate VendorItemPricing");
        Map<Integer, Item> items = itemRepository.selectByIds(vendorItemPricings.stream().map(x -> x.getItemId()).collect(Collectors.toSet())).stream().collect(Collectors.toMap(x -> x.getId(), x -> x));
        for (VendorItemPricing vendorItemPricing : vendorItemPricings) {
            Item item = null;
            if (items.get(vendorItemPricing.getItemId()) != null) {
                item = items.get(vendorItemPricing.getItemId());
            } else {
                continue;
            }

            VendorCatalogPricingModel vendorCatalogPricingModel = new VendorCatalogPricingModel();
            vendorCatalogPricingModel.setCatalogId(item.getCatalogItemId());
            vendorCatalogPricingModel.setAuthId(13);
            vendorCatalogPricingModel.setDp(vendorItemPricing.getDp());

            vendorCatalogPricingModel.setTp(vendorItemPricing.getTp());
            vendorCatalogPricingModel.setMop(vendorItemPricing.getMop());
            vendorCatalogPricingModel.setEffectedOn(LocalDate.of(2023, 8, 22));
            vendorCatalogPricingModel.setVendorId(vendorItemPricing.getVendorId());


            try {
                vendorCatalogPricingService.createVendorCatalogPricingLog(vendorCatalogPricingModel);
            } catch (ProfitMandiBusinessException e) {
                continue;
            }

        }

    }

    public void approveVendorCatalogPricing() {
        List<VendorCatalogPricingLog> vendorCatalogPricingLogs = vendorCatalogPricingLogRepository.selectByStatus(VendorCatalogPricingStatus.PENDING);


        for (VendorCatalogPricingLog vendorCatalogPricingLog : vendorCatalogPricingLogs) {
            vendorCatalogPricingLog.setStatus(VendorCatalogPricingStatus.APPROVED);
            vendorCatalogPricingLog.setUpdatedTimestamp(LocalDateTime.now());
            vendorCatalogPricingLog.setApprovedBy(13);
            vendorCatalogPricingLog.setApprovedTimestamp(LocalDateTime.now());
            vendorCatalogPricingService.createVendorCatalogPricing(vendorCatalogPricingLog);
        }

    }

    public void sendMailWhatsAppAfterLoanDueDate() throws Exception {
        sdCreditService.updateRisk();
        List<Loan> defaultLoans = sdCreditService.getDefaultLoans();
        if (!defaultLoans.isEmpty()) {
            this.sendLoanAlert(defaultLoans);
        }


    }

    public void sendLoanAlert(List<Loan> defaultLoans) throws
            Exception {

        List<Integer> categoryIds = Arrays.asList(
                ProfitMandiConstants.TICKET_CATEGORY_CATEGORY,
                ProfitMandiConstants.TICKET_CATEGORY_RBM,
                ProfitMandiConstants.TICKET_CATEGORY_SALES,
                ProfitMandiConstants.TICKET_CATEGORY_ABM,
                ProfitMandiConstants.TICKET_CATEGORY_ACCOUNTS,
                ProfitMandiConstants.TICKET_CATEGORY_BUSINESSINTELLIGENT);

        /*for (Map.Entry<String, Set<Integer>> storeGuyEntry : csService.getAuthUserPartnerIdMappingByCategoryIds(categoryIds, false).entrySet()) {
            List<Loan> filteredRows = defaultLoans.stream()
                    .filter(loan -> storeGuyEntry.getValue().contains(loan.getFofoId()))
                    .collect(Collectors.toList());

            if (!filteredRows.isEmpty()) {
                for (Loan defaultLoan : filteredRows) {
                    List<LoanStatement> loanStatements = loanStatementRepository.selectByLoanId(defaultLoan.getId());
                    double amount = loanStatements.stream().map(LoanStatement::getAmount).collect(Collectors.summingDouble(BigDecimal::doubleValue));
                    String messageText = getMessageForDueDateExtend(defaultLoan,amount);
                   // String[] email = new String[]{storeGuyEntry.getKey()};

                    sendMailOfHtmlFormat(mailSender, email, messageText, null, subject);

                }
            }
        }*/
        //Send single whatsapp to tv
        String title = "SD Credit Alert!";
        boolean notYetSent = true;
        for (Loan defaultLoan : defaultLoans) {
            List<LoanStatement> loanStatements = loanStatementRepository.selectByLoanId(defaultLoan.getId());

            double amount = loanStatements.stream().map(LoanStatement::getAmount).collect(Collectors.summingDouble(BigDecimal::doubleValue));

            com.spice.profitmandi.dao.entity.user.User user = userUserRepository.selectById(defaultLoan.getFofoId());

            Address address = addressRepository.selectById(user.getAddressId());

            //String url = "http://app.smartdukaan.com/pages/home/credit";

            String message = getMessageForDueDateExtend(defaultLoan, amount);
            String whatsAppMessage = getWhatsAppMessageForDueDateExtend(defaultLoan, amount);
            if (whatsAppMessage != null) {
                notificationService.sendWhatsappMessage(whatsAppMessage, title, address.getPhoneNumber());
                if (notYetSent) {
                    notificationService.sendWhatsappMessage(whatsAppMessage, title, "9911565032");
                    notYetSent = false;
                }
            }

            String[] email = {user.getEmailId()};
            sendMailOfHtmlFormat(mailSender, email, message, null, "Loan Alert - SmartDukaan");
        }
    }

    private String getMessageForDueDateExtend(Loan defaultLoan, double amount) throws ProfitMandiBusinessException {

        double absoluteAmount = Math.abs(amount);
        long roundedAmount = Math.round(absoluteAmount);
        String formattedAmount = (amount < 0) ? "" + roundedAmount : "" + roundedAmount;

        LocalDate dueDate = defaultLoan.getDueDate().toLocalDate();
        LocalDate defaultDate = defaultLoan.getDueDate().toLocalDate().plusDays(15);

        // Retrieve the name of the user associated with the loan
        String name = userUserRepository.selectById(defaultLoan.getFofoId()).getName();

        // Initialize a StringBuilder to construct the HTML message
        StringBuilder sb = new StringBuilder();

        if (LocalDate.now().isBefore(dueDate)) {
            long noOfdaysBetween = ChronoUnit.DAYS.between(LocalDate.now(), defaultLoan.getDueDate().toLocalDate());
            String payDate = defaultLoan.getDueDate().toLocalDate().format(DateTimeFormatter.ofPattern("dd-MM-yyyy"));
            // 5 Days Before Due Date - daily
            if (noOfdaysBetween <= 5) {
                sb.append("<html><body><p><b>Loan Repayment Alert !</b></p></br><p>Dear " + name + "</p>");
                sb.append("<p>This is a friendly reminder that your loan payment is due in " + noOfdaysBetween + "days, on " +
                        payDate +
                        ". Please ensure that the payment is made on time to avoid any late fees or penalties.</p>");
                sb.append("<p>As per the terms of your loan agreement, the total amount due is Rs. " + formattedAmount + ". " +
                        "You can make the payment by adding money to your wallet. If you have any questions or need assistance, " +
                        "our customer service team is available to help you.</p>");
                sb.append("<p>Thank you for your cooperation and prompt attention to this matter.</p></br><p> Smart Dukaan Team !!</p>");
            }
        } else if (LocalDate.now().isBefore(defaultDate)) {
            long noOfdaysBetween = ChronoUnit.DAYS.between(defaultDate, LocalDate.now());

            // After Due Date – Alternate days
            if ((noOfdaysBetween % 2 == 0) && (noOfdaysBetween > 5)) {
                sb.append("<html><body><p><b>Loan Repayment Alert !</b></p></br><p>Dear " + name + "</p>");
                sb.append("<p>We would like to remind you that your payment for the outstanding balance on your account is now overdue. " +
                        "As per our terms and conditions, a higher interest rate will be levied on the overdue amount Rs. " + formattedAmount + ".</p>");
                sb.append("<p>To avoid further charges and any negative impact on your Credit score, we kindly request that you settle the " +
                        "outstanding balance as soon as possible.</p>");
                sb.append("<p>Thank you for your attention to this matter.</p></br><p> Smart Dukaan Team !!</p>");
            }
            // 5 Days before default - daily
            if (noOfdaysBetween <= 5) {
                sb.append("<html><body><p><b>Loan Repayment Alert !</b></p></br>");
                sb.append("<p>Attention: High Alert</p>");
                sb.append("<p>Dear " + name + "</p>");
                sb.append("<p>This is to inform you that the due date for your payment of Rs. " + formattedAmount + " has been crossed, " +
                        "and you are approaching the default date on " + defaultDate.format(DateTimeFormatter.ofPattern("dd-MM-yyyy")) + ". " +
                        "Please be aware that this will have a significant impact on your Credit score and may result in substantial penalties as well.</p>");
                sb.append("<p>To rectify this situation, it is imperative that you take immediate action. " +
                        "Please make the necessary payment immediately to avoid further consequences.</p>");
                sb.append("<p>Thank you for your attention to this matter.</p></br><p> Smart Dukaan Team !!</p>");
            }
        } else {
            // Post Default - Daily
            sb.append("<html><body><p><b>Loan Repayment Alert !</b></p></br><p>Dear " + name + "</p>");
            sb.append("<p>We regret to inform you that you are currently in loan default. As a result, " +
                    "higher penalties are being charged, which is also negatively impacting your Credit score. " +
                    "It is imperative that you make an immediate payment to rectify this situation.</p>");
            sb.append("<p>Please note that failing to make the payment promptly will result in further consequences, " +
                    "including potential legal action and additional financial penalties. " +
                    "We strongly urge you to take this matter seriously and settle the outstanding amount as soon as possible.</p>");
            sb.append("<p>To make the payment, please add Rs. " + formattedAmount + " to your wallet. We are here to help you resolve this issue and get back on track.</p>");
            sb.append("<p>Thank you for your attention to this matter.</p></br><p> Smart Dukaan Team !!</p>");

        }

        // Return the constructed HTML message
        sb.append("</body></html>");
        return sb.toString();
    }

    private String getWhatsAppMessageForDueDateExtend(Loan defaultLoan, double amount) throws
            ProfitMandiBusinessException {

        double absoluteAmount = Math.abs(amount);
        long roundedAmount = Math.round(absoluteAmount);
        String formattedAmount = (amount < 0) ? "" + roundedAmount : "" + roundedAmount;

        LocalDate dueDate = defaultLoan.getDueDate().toLocalDate();
        LocalDate defaultDate = defaultLoan.getDueDate().toLocalDate().plusDays(15);

        // Retrieve the name of the user associated with the loan
        String name = userUserRepository.selectById(defaultLoan.getFofoId()).getName();

        // Initialize a StringBuilder to construct the HTML message
        String message = null;

        if (LocalDate.now().isBefore(dueDate)) {
            long noOfdaysBetween = ChronoUnit.DAYS.between(LocalDate.now(), defaultLoan.getDueDate().toLocalDate());
            String payDate = defaultLoan.getDueDate().toLocalDate().format(DateTimeFormatter.ofPattern("dd-MM-yyyy"));
            // 5 Days Before Due Date - daily
            if (noOfdaysBetween <= 5) {

                message = "Dear %s, This is a friendly reminder that your outstanding credit payment is due in %d days, on %s.\n" +
                        "\n" +
                        "Please ensure that the payment is made on time to avoid any late fees or penalties.As per the terms of your loan agreement, the total amount due is Rs. %s. You can make the payment by adding money to wallet.\n" +
                        "\n" +
                        "If you have any questions or need assistance, our customer service team is available to help you.\n" +
                        "Thank you for your cooperation and prompt attention to this matter.\n" +
                        "\n" +
                        "Team SmartDukaan";
                message = String.format(message, name, noOfdaysBetween, payDate, formattedAmount);
            }
        } else if (LocalDate.now().isBefore(defaultDate)) {
            long noOfdaysBetween = ChronoUnit.DAYS.between(defaultDate, LocalDate.now());
            // After Due Date – Alternate days
            if ((noOfdaysBetween % 2 == 0) && (noOfdaysBetween > 5)) {
                message = "Dear %s, We would like to remind you that your payment for the outstanding balance on your account is now overdue. \n" +
                        "As per our terms and conditions, a higher interest rate will be levied on the overdue amount Rs.%s.\n" +
                        "To avoid further charges and any negative impact on your Credit ratings, we kindly request that you settle the outstanding balance as soon as possible.\n" +
                        "Thank you for your attention to this matter. \n" +
                        "\n" +
                        "Team SmartDukaan";
                message = String.format(message, name, formattedAmount);
            }
            // 5 Days before default - daily
            if (noOfdaysBetween <= 5) {
                String defaultPayDate = defaultDate.format(DateTimeFormatter.ofPattern("dd-MM-yyyy"));
                message = "Attention: High Alert!! Dear %s, \n" +
                        "\n" +
                        "This is to inform you that the due date for your payment of Rs. %s has been crossed, and as a result, you are now about to reach default date %s.\n" +
                        "Please be aware that this will have a significant impact on your Credit score and may result in substantial penalties as well. To rectify this situation, it is imperative that you take immediate action.\n" +
                        "\n" +
                        "Please make the necessary payment immediately to avoid further consequences. Thank you for your attention to this matter.\n" +
                        "\n" +
                        "Team SmartDukaan";
                message = String.format(message, name, formattedAmount, defaultPayDate);
            }
        } else {
            // Post Default - Daily
            message = "Dear %s, We regret to inform you that you are currently in Credit default. As a result, higher penalties are being charged, which is also negatively impacting your Credit score. \n" +
                    "\n" +
                    "It is imperative that you make an immediate payment to rectify this situation. \n" +
                    "\n" +
                    "Please note that failing to make the payment promptly will result in further consequences, including potential legal action and additional financial penalties. \n" +
                    "\n" +
                    "We strongly urge you to take this matter seriously and settle the outstanding amount as soon as possible. \n" +
                    "\n" +
                    "To make the payment, please add Rs. %s  to wallet. We are here to help you resolve this issue and get back on track. Thank you for your attention to this matter. \n" +
                    "\n" +
                    "Team SmartDukaan";
            message = String.format(message, name, formattedAmount);

        }
        // Return the constructed HTML message
        return message;
    }

    @Autowired
    private FofoOpeningStockRepository fofoOpeningStockRepository;
    @Autowired
    private SmartCartService smartCartService;

    public void createOpeningStockPartyWise() throws Exception {
        // Get all partner stock data
        Map<Integer, List<PartnerOpeningStockByTodayModel>> partnerOpeningStockByTodayInStockMap = currentInventorySnapshotRepository.getAllPartnerTodayInstock();
        Map<Integer, List<PartnerOpeningStockByTodayModel>> partnerOpeningStockByTodayGrnPending = currentInventorySnapshotRepository.getAllPartnerTodayGrnPending();

        Map<Integer, List<PartnerOpeningStockByTodayModel>> partnerOpeningStockByTodayMap =
                new HashMap<>();

// STEP 1: Copy in-stock data
        partnerOpeningStockByTodayInStockMap.forEach((fofoId, list) ->
                partnerOpeningStockByTodayMap.put(
                        fofoId,
                        new ArrayList<>(list)   // deep list copy
                )
        );

// STEP 2: Merge GRN pending using streams
        partnerOpeningStockByTodayGrnPending.forEach((fofoId, grnList) -> {

            // If FOFO missing → simply insert GRN list (deep copy)
            partnerOpeningStockByTodayMap
                    .computeIfAbsent(fofoId, x -> new ArrayList<>())
                    .addAll(
                            grnList.stream()
                                    .map(g -> new PartnerOpeningStockByTodayModel(
                                            g.getFofoId(),
                                            g.getCatalogId(),
                                            g.getModelNumber(),
                                            g.getOpeningQty()
                                    ))
                                    .collect(Collectors.toList())
                    );
        });

// STEP 3: Now merge duplicates per catalogId using grouping
        partnerOpeningStockByTodayMap.replaceAll((fofoId, list) ->
                list.stream()
                        .collect(Collectors.toMap(
                                PartnerOpeningStockByTodayModel::getCatalogId,
                                m -> new PartnerOpeningStockByTodayModel(
                                        m.getFofoId(),
                                        m.getCatalogId(),
                                        m.getModelNumber(),
                                        m.getOpeningQty()
                                ),
                                // merge function: sum openingQty
                                (m1, m2) -> {
                                    m1.setOpeningQty(m1.getOpeningQty() + m2.getOpeningQty());
                                    return m1;
                                }
                        ))
                        .values()
                        .stream()
                        .collect(Collectors.toList())
        );


        Map<Integer, Map<Integer, Integer>> activatedButNotBilledCatalogQtyMap = smartCartService.activatedButNotBilledCatalogQtyMap();
        LOGGER.info("PartnerOpeningStockByTodayModel {}", partnerOpeningStockByTodayMap);
        for (Map.Entry<Integer, List<PartnerOpeningStockByTodayModel>> entry : partnerOpeningStockByTodayMap.entrySet()) {
            int fofoId = entry.getKey();
            List<PartnerOpeningStockByTodayModel> partnerStockList = entry.getValue();
            // Get the sold (activated-but-not-billed) quantity for this fofo and catalog
            Map<Integer, Integer> activatedButNotBilledQtyMap = activatedButNotBilledCatalogQtyMap.getOrDefault(fofoId, Collections.emptyMap());

            //  if stock is avaialble in snapshot
            for (PartnerOpeningStockByTodayModel partnerStock : partnerStockList) {
                int catalogId = partnerStock.getCatalogId();
                int stockCurrentQty = partnerStock.getOpeningQty();
                LocalDate openingDate = partnerStock.getOpeningDate();

                int soldButNotBilledQty = activatedButNotBilledQtyMap.getOrDefault(catalogId, 0);

                if (catalogId == 1025656 && fofoId == 175140232) {
                    LOGGER.info("catalogId {}", catalogId);
                    LOGGER.info("fofoId {}", fofoId);
                    LOGGER.info("soldButNotBilledQty {}", soldButNotBilledQty);
                    LOGGER.info("stockCurrentQty {}", stockCurrentQty);
                }

                // Subtract soldQty from partnerCurrentQty
                int partnerCurrentQty = Math.max(stockCurrentQty - soldButNotBilledQty, 0); // Ensure it doesn't go negative


                // Check if an entry already exists in the FofoOpeningStock table
                FofoOpeningStock existingStock = fofoOpeningStockRepository.findByFofoIdAndCatalogId(fofoId, catalogId);


                if (existingStock != null) {
                    if (partnerCurrentQty < 1 && existingStock.getOpeningDate().isBefore(LocalDate.now().minusDays(5))) {
                        fofoOpeningStockRepository.delete(existingStock);
                    }
                    if (existingStock.getOpeningQty() < partnerCurrentQty) {
                        existingStock.setOpeningQty(partnerCurrentQty);
                        existingStock.setCurrentQty(partnerCurrentQty);
                        existingStock.setOpeningDate(openingDate);
                        LOGGER.info("if condition exist {}");
                    } else if (existingStock.getCurrentQty() >= existingStock.getOpeningQty()) {
                        existingStock.setOpeningQty(existingStock.getCurrentQty());
                        existingStock.setCurrentQty(partnerCurrentQty);
                        existingStock.setOpeningDate(LocalDate.now());
                    } else if (existingStock.getOpeningDate().isBefore(LocalDate.now().minusDays(5))) {
                        int averageQty = Math.round((existingStock.getCurrentQty() + existingStock.getOpeningQty()) / 2.0f);

                        if (averageQty == 1 && existingStock.getOpeningQty() == 1 && existingStock.getCurrentQty() == 0) {
                            // Delete the existing stock entry from the table
                            fofoOpeningStockRepository.delete(existingStock);
                            LOGGER.info("Deleted record with fofoId: {}, catalogId: {} because averageQty, openingQty, and currentQty meet deletion criteria", fofoId, catalogId);
                        } else {
                            // Update the existing stock with the new average quantity
                            existingStock.setOpeningQty(averageQty);
                            existingStock.setCurrentQty(partnerCurrentQty);
                            existingStock.setOpeningDate(LocalDate.now());
                            LOGGER.info("Updated due to date being older than 5 days with average quantity: {}", averageQty);
                        }
                    } else {
                        existingStock.setCurrentQty(partnerCurrentQty);
                    }
                } else {
                    LOGGER.info("else condition exist");
                    FofoOpeningStock newStock = new FofoOpeningStock();
                    newStock.setFofoId(fofoId);
                    newStock.setCatalogId(catalogId);
                    newStock.setOpeningQty(partnerCurrentQty);
                    newStock.setCurrentQty(partnerCurrentQty);
                    newStock.setOpeningDate(openingDate);
                    fofoOpeningStockRepository.persist(newStock);

                }
            }

//            if stock is not available in snapshot but available in opening stock
            List<FofoOpeningStock> fofoOpeningStockList = fofoOpeningStockRepository.selectAllByFofoId(fofoId);

            for (FofoOpeningStock fofoOpeningStock : fofoOpeningStockList) {

                if (!partnerStockList.stream().anyMatch(partnerStock -> partnerStock.getCatalogId() == fofoOpeningStock.getCatalogId())) {
                    if (fofoOpeningStock.getOpeningDate().isBefore(LocalDate.now().minusDays(5))) {
                        LOGGER.info("not match stock{}", fofoOpeningStock);
                        fofoOpeningStockRepository.delete(fofoOpeningStock);
                    } else {
                        LOGGER.info("not match stock2{}", fofoOpeningStock);
                        fofoOpeningStock.setCurrentQty(0);
                    }

                }
            }

        }
    }


    @Autowired
    private CategorisedCatalogRepository categorisedCatalogRepository;

    public void groupHidCatalogsByWeeks() throws ProfitMandiBusinessException, MessagingException, IOException {
        List<CategorisedCatalogListModel> categorisedCatalogs = categorisedCatalogRepository.getListByStatus(CatalogMovingEnum.HID);
        LocalDate currentDate = LocalDate.now();
        LocalDate oneWeekAgo = currentDate.minusDays(7);
        LocalDate twoWeeksAgo = currentDate.minusDays(14);
        LocalDate threeWeeksAgo = currentDate.minusDays(21);

        // This Week (inclusive of the current date)
        List<CategorisedCatalogListModel> thisWeekCatalogs = categorisedCatalogs.stream()
                .filter(catalog -> !catalog.getStatusCreatedDate().isBefore(oneWeekAgo))
                .collect(Collectors.toList());

        // Last Week (from one week ago to two weeks ago, exclusive of this week)
        List<CategorisedCatalogListModel> lastWeekCatalogs = categorisedCatalogs.stream()
                .filter(catalog -> catalog.getStatusCreatedDate().isBefore(oneWeekAgo) && !catalog.getStatusCreatedDate().isBefore(twoWeeksAgo))
                .collect(Collectors.toList());

        // Two Weeks Ago (from two weeks ago to three weeks ago, exclusive of last week)
        List<CategorisedCatalogListModel> twoWeeksAgoCatalogs = categorisedCatalogs.stream()
                .filter(catalog -> catalog.getStatusCreatedDate().isBefore(twoWeeksAgo) && !catalog.getStatusCreatedDate().isBefore(threeWeeksAgo))
                .collect(Collectors.toList());

        // Older than Two Weeks Ago (anything older than three weeks ago)
        List<CategorisedCatalogListModel> olderCatalogs = categorisedCatalogs.stream()
                .filter(catalog -> catalog.getStatusCreatedDate().isBefore(threeWeeksAgo))
                .collect(Collectors.toList());


        String subject = "Hid Catalogs";
        StringBuilder sb = new StringBuilder();

        sb.append("<html><body><p>Hi Catagory Team,\n");
        sb.append("<p style='font-weight:500;'>Please review the HID catalogs for this week:</p>\n" + "<table style='border:1px solid black ;padding: 5px; border-collapse: collapse;'>");

        sb.append("<th style='border:1px solid black;padding: 5px'>Brand</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Model Number</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Model Name</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Catalog Id</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Start Date</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>End Date</th>\n");
        sb.append("</tr>\n");

        for (CategorisedCatalogListModel catalog : thisWeekCatalogs) {
            sb.append("<tr>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getBrand() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getModelNumber() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getModelId() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getCurrentStatus() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getStatusCreatedDate() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getStatusEndDate() + "</td>\n");

            sb.append("</tr>\n");

        }

        sb.append("</table>");

        sb.append("</br>");
        sb.append("</br>");
        sb.append("</br>");
        sb.append("<p style='font-weight:500;'>Please review the HID catalogs for last week:</p>\n" + "<table style='border:1px solid black ;padding: 5px; border-collapse: collapse;'>");

        sb.append("<th style='border:1px solid black;padding: 5px'>Brand</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Model Number</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Model Name</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Catalog Id</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Start Date</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>End Date</th>\n");
        sb.append("</tr>\n");

        for (CategorisedCatalogListModel catalog : lastWeekCatalogs) {
            sb.append("<tr>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getBrand() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getModelNumber() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getModelId() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getCurrentStatus() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getStatusCreatedDate() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getStatusEndDate() + "</td>\n");

            sb.append("</tr>\n");

        }
        sb.append("</table>");

        sb.append("</br>");
        sb.append("</br>");
        sb.append("</br>");
        sb.append("<p style='font-weight:500;'>Please review the HID catalogs for two week ago:</p>\n" + "<table style='border:1px solid black ;padding: 5px; border-collapse: collapse;'>");

        sb.append("<th style='border:1px solid black;padding: 5px'>Brand</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Model Number</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Model Name</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Catalog Id</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Start Date</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>End Date</th>\n");
        sb.append("</tr>\n");

        for (CategorisedCatalogListModel catalog : twoWeeksAgoCatalogs) {
            sb.append("<tr>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getBrand() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getModelNumber() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getModelId() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getCurrentStatus() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getStatusCreatedDate() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getStatusEndDate() + "</td>\n");

            sb.append("</tr>\n");

        }
        sb.append("</table>");

        sb.append("</br>");
        sb.append("</br>");
        sb.append("</br>");
        sb.append("<p style='font-weight:500;'>Please review the HID catalogs for older than two weeks ago:</p>\n" + "<table style='border:1px solid black ;padding: 5px; border-collapse: collapse;'>");

        sb.append("<th style='border:1px solid black;padding: 5px'>Brand</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Model Number</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Model Name</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Catalog Id</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>Start Date</th>\n");
        sb.append("<th style='border:1px solid black;padding: 5px'>End Date</th>\n");
        sb.append("</tr>\n");

        for (CategorisedCatalogListModel catalog : olderCatalogs) {
            sb.append("<tr>");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getBrand() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getModelNumber() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getModelId() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getCurrentStatus() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getStatusCreatedDate() + "</td>\n");
            sb.append("<td style='border:1px solid black;padding: 5px'>" + catalog.getStatusEndDate() + "</td>\n");

            sb.append("</tr>\n");

        }
        sb.append("</table>");
        sb.append("</body></html>");

        List<String> sendTo = Arrays.asList("uday.singh@smartdukaan.com", "kuldeep.kumar@smartdukaan.com", "nishant.ohri@smartdukaan.com", "ritesh.chauhan1@smartdukaan.com", "shobhit.tandon@smartdukaan.com", "tarun.verma@smartdukaan.com", "niranjan.kala@smartdukaan.com");


        String[] email = sendTo.toArray(new String[sendTo.size()]);

        this.sendMailOfHtmlFormat(mailSender, email, sb.toString(), null, subject);
    }

    public void send10OrMoreOlderStockReport() throws Exception {
        System.out.print("testSTart");
        purSaleService.moreThen10DaysEgedStockReport();
        System.out.print("testEnd");

    }

    public void sendUpdateMobileBrandMarketShareQuaterlyMail() throws Exception {
        String subject = "Reminder: Please Update Brand-wise Market Share";

        String body = "Dear Team,\n\n" +
                "This is a gentle reminder to kindly update the brand-wise market share data for the current quarter.\n\n" +
                "Best regards,\n" +
                "SmartDukaan Team";

        Utils.sendMailWithAttachments(
                googleMailSender,
                new String[]{"sm.@smartdukaan.com"},
                new String[]{"tech@smartdukaan.com"},
                subject,
                body
        );
    }


}
// 2284"